SQL script to find PM transactions with missing apply details in Dynamics GP

Hi Folks


Every now and then you get posting interruptions in Dynamics GP at exactly the wrong time. Normally the Batch recovery routine under tools>routines>Batch Recovery, picks these up. However sometimes this is not the case and you then need to determine what state your data is in within the Dynamics GP product.

The below script is designed to help in these circumstances. It is run against the Dynamics GP company database and is designed to find those payables transactions with missing apply details.

I would recommend running this script if you have found a PM transaction with missing apply details to ascertain the extent of the problem.

If you do have a posting interruption or if you find that you do have missing apply info please contact the helpdesk@touchstone.co.uk and we can investigate further.

If you have any questions about the script etc please feel free to contact me andrew.hall@touchstone.co.uk

Here is the script:

/* Before running this query go to query on the menu bar and select results in text */

/* This query is designed to find open transactions that have missing apply info Open & the info is

in history

The first part of the script selects transactions from

the PM20000 which have apply info in the PM10200 */

select b.VCHRNMBR, b.VENDORID

into #temp1

from PM10200 a, PM20000 b

where b.DOCNUMBR = a.APTODCNM

and a.VENDORID = b.VENDORID

go

/* The second part of the script selects transactions from

the PM20000 which should have apply info but they did not appear in the

results of the first script */

select VCHRNMBR, VENDORID

into #temp2

from PM20000

where DOCAMNT >CURTRXAM

and CURTRXAM>'0.00000'

and VCHRNMBR NOT IN

(select VCHRNMBR

from #temp1)

order by VENDORID

go

/* The third part of the script selects transactions from

the results of the second script which have apply info in the

PM30300 History table */

select a.VCHRNMBR, a.VENDORID

into #temp3

from #temp2 a, PM30300 b

where a.VENDORID = b.VENDORID

and a.VCHRNMBR = b.VCHRNMBR

go

/* The Fourth part of the script selects transactions from

the PM20000 that do not appear in the results of the third part or the first part

( open apply info and History apply info) */

select VCHRNMBR, VENDORID, DOCNUMBR

into #temp4

from PM20000

where DOCAMNT >CURTRXAM

and CURTRXAM>'0.00000'

and VCHRNMBR NOT IN

(select VCHRNMBR

from #temp3)

and VCHRNMBR NOT IN

(select VCHRNMBR

from #temp1)

order by VENDORID

go

/* This part of the script displays the results*/

print 'Transactions from PM20000 missing apply info in PM10200 & PM30300'

select * from #temp4

go