Dynamics GP - Script to Reconcile AP Apply links

https://community.dynamics.com/gp/f/32/t/146495


/*

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

4 temp tables are created in the temp db

*/

/*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

Use tempdb

GO

/* Use this script to drop the temp tables if they persist after

the session closes. You have to close the query window

to end the session */

Select 'DROP TABLE ' + name from sys.objects where name like '%#temp%' and type = 'U'

Select * from sys.objects where name like '%#temp%'