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

May 18
09:14

2005

Andrew Karasev

Andrew Karasev

  • Share this article on Facebook
  • Share this article on Twitter
  • Share this article on Linkedin

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.

mediaimage
  • 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.SLPRSNID<>b.SLPRSNID or a.SALSTERR<>b.SALSTERR

  • The second scripts unlocks Project Accounting user,SQL scripts for Project Accounting: Microsoft Great Plains series – overview for developer Articles 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