Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 5900 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 900 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

ID:10417
Title:SQL script to find PM transactions with missing apply details in Dynamics GP
URL:http://intouchdynamicsgp.blogspot.com/2010/04/sql-script-to-find-pm-transactions-with.html
Description: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
Category:SQL SCRIPTS: Payables Management (SQL)
Link Owner:
Date Added:June 17, 2010 06:07:01 PM
Number Hits:96
RatingsAverage rating: (0 votes)
Reviews

No Reviews Yet.

 
GPWindow.com

Thank you for your support for GPWindow. It helps us cover part of the hosting costs for GPWindow.