SQL scripts for Project Accounting: Microsoft Great Plains series ? overview for developer

Microsoft Business Solutions Great Plains has Project Accounting module where you can budget the project, assign it to customer contract and then log expenses, timesheets, inventory spending, and finally bill the customer against the project or contract. Microsoft Great Plains Project Accounting does excellent job, but there are cases when you need developer or MS SQL DB Admin touches.

? The first script transfers salesperson and sales territory from Customer master file to Project master table

update PA01101 set SLPRSNID=b.SLPRSNID, SALSTERR=b.SALSTERR from PA01101 a join RM00101 b on a.CUSTNMBR=b.CUSTNMBR where a.SLPRSNIDb.SLPRSNID or a.SALSTERRb.SALSTERR

? The second scripts unlocks Project Accounting user, this rare and funny one ? really hard to get it from Microsoft Business Solutions Tech Support site:

delete PA000001 where userid='put user id here'

? The third script is really advanced one ? it is for users trying to place billing address with contact info on the PA invoice form at the bottom section ? address comes from Contract ? not from the project

/*
Updates Primary Bill To Address Code for Billing invoice to be the one from Contract Master table, instead of Primary Billing Address Code from Customer
*/

update PA13100 set PRBTADCD=c.PRBTADCD from PA13100 a join PA13200 b on a.PADocnumber20=b.PADocnumber20 join PA01201 c on b.PAPROJNUMBER=c.PAPROJNUMBER

/* update PA13100 set PRBTADCD=c.PRBTADCD from PA13100 a join PA13200 b on a.PADocnumber20=b.PADocnumber20 join PA01101 c on b.PACONTNUMBER=c.PACONTNUMBER */ /* Fill user defined 1 on the Billing HDR to contact person from the appropriate customer master address rec. */ update PA13100 set PAUD1 = left(b.CNTCPRSN,20) from PA13100 a join RM00102 b on a.CUSTNMBR = b.CUSTNMBR and a.PRBTADCD = b.ADRSCODE

Good luck with customization and support, if you have issues or concerns ? we are here to help! If you want us to do the job - give us a call 1-630-961-5918 or 1-866-528-0577! help@albaspectrum.com

Andrew is a Great Plains specialist in Alba Spectrum Technologies ( http://www.albaspectrum.com ) ? USA nationwide Great Plains, Microsoft CRM customization company, serving clients in Mexico-city, Chicago, Houston, Atlanta, Phoenix, New York, Los Angeles, San Francisco, San Diego, Miami, New Orleans, Toronto, Montreal and having locations in multiple states and internationally

In The News:


pen paper and inkwell


cat break through


Microsoft Great Plains Project Accounting ? Overview For IT Director/Controller

Microsoft Business Solutions is now in process of creating so... Read More

Recent Studies Show that 9 out of 10 PCs Are Infected with Spyware

Spyware and malware are large problems for Internet users today... Read More

Great Plains Dexterity: Customizations & Source Code Programming

Great Plains Software Dynamics, Dynamics C/S+, eEnterprise were written on... Read More

20 Extra Hours Per Week: What Would You Do?

While I was in college, if you would have asked... Read More

10 Programming Tips

(1) Avoid using the same variable again and again for... Read More

Microsoft CRM Implementation - US Market Lessons

Microsoft CRM is CRM answer from Microsoft Business Solutions. If... Read More

Tools for Customizing Great Plains

Microsoft Business Solutions ? Great Plains has captured the US... Read More

Microsoft Great Plains: Interest Calculation Example ? Stored Procedure for Crystal Report

This is intermediate level SQL scripting article for DB Administrator,... Read More

Microsoft Small Business Manager eCommerce ? Overview

Microsoft Business Solutions Small Business Manager is scaled down Great... Read More

Did You Ever Want to Completely Erase Everything on Your Computer?

Did you ever want to erase everything on your computer?... Read More

The Dreaded Paper Label - Should it be Used?

While paper labeling CDs and DVDs may appear to be... Read More

QuarkXpress Tips: How to Move Content Between Print and Web Layouts

Since its release in 1987, QuarkXpress had made an immediate... Read More

Groupware and Version History: Collaboration Series #1

This article is the first of a series of articles... Read More

Cisco Certification: Introduction To ISDN, Part V

The major reason I recommend getting your hands on real... Read More

Microsoft Great Plains Security Setup - Overview For Consultant

Microsoft Business Solutions Great Plains is very good fit for... Read More

10 Ways to Learn a Software

Following tips help you to learn a software in lesser... Read More

Great Plains Custom Development: Dexterity, VBA, SQL, Crystal, eConnect ? Overview For Programmer

Microsoft Great Plains is main Microsoft Business Solutions accounting package... Read More

The Death of Windows

I have always regretted how Microsoft price gouges and rips... Read More

Microsoft CRM Implementation ? Fundamental CRM Principles Revision

Microsoft CRM is relatively new player on the now becoming... Read More

Benefits of Shareware

Shareware has been fighting the stigma of being misunderstood for... Read More

How to Evaluate Staffing Software

If you are in the market for new staffing software,... Read More

Introduction To ISDN, Part II

In the previous ISDN article, we looked at how and... Read More

Cross-Platform Custom Software Development & Integration ? IT Strategy for Large Corporation

Microsoft Business Solutions products: Great Plains, MS CRM, Navision, Axapta,... Read More

Microsoft Great Plains: If You are Orphan Client ? What to Do and FAQ

Microsoft Business Solutions Great Plains, former Great Plains Software eEnterprise,... Read More

Dreamweaver vs FrontPage

There are two major WYSIWYG(What You See Is What You... Read More

Theres Gold in Your Websites Server Log

Many Webmasters have never bothered to view their website's server... Read More

Microsoft Business Solutions - Navision Customization: C/SIDE, C/ODBC, C/FRONT, XBRL

Microsoft bought Navision, Denmark based software development company, along with... Read More

Microsoft Great Plains Integration Manager ? Working With Text File

Microsoft Business Solutions main middle market ERP application - Microsoft... Read More

Great Plains Dexterity Development: FAQ

Microsoft Business Solutions Great Plains is Dexterity-written application and currently... Read More

Performance Tuning of a Daffodil DB / One$DB -JDBC Application

This article illustrates the best practices to improve the performance... Read More

Imagine if Everyone Working In Your Office Was In Synch?

Microsoft-Outlook is a pretty amazing program. So much more than... Read More

Helping Newbies Understand Professional Software

The Windows registry is a huge database that ensures normal... Read More

Microsoft CRM Custom Design & Development: SDK, C#, SQL, Exchange, Integration, Crystal Reports

Microsoft CRM is new player on the CRM software... Read More