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

• 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, 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