Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Identifying Payables Transactions with Missing Apply Records

Identifying Payables Transactions with Missing Apply Records

  • Comments 4

David MeegoFrom the Useful SQL Scripts Series.

I was assisting on a support case recently where we needed to identify transactions in Payables Management history which had missing Apply records.  We wanted to add up the total of the Apply records and make sure that they matched the value of the original document amount.

I was given a script which could look for missing Apply records on debit documents only.  Using this script as a starting point, I updated it to use local temp tables in the tempdb (which means that mulitple users can execute the code without causing concurrency and deadlock issues), added indexes for performance, set it up to handle debit and credit documents as well as multi-currency realized gain/loss and allowing for a 0.01 exchange rate currency rounding difference per applied multi-currency document.

I worked with my friend Robert Cavill to test the code and fine tuned it to work on live data.  The final code was able to identify a few transactions which did have issues due to rounding differences beyond the allowance, without returning a large number of false positives.

SQL PM Find Missing Apply Records Script

/* SQL PM Find Missing Apply Records */
/* Updated by David Musgrave, 24-March-2009 */

/* Now handles debit and credit documents and multi-currency (including round errors) */

set QUOTED_IDENTIFIER OFF
set
nocount on

print "Deleting Temp tables (if they exist)"
go
if exists (select * from tempdb.dbo.sysobjects
    where id = Object_id('tempdb..#PMTrans') and type = 'U')
        drop table #PMTrans

go
if exists (select * from tempdb.dbo.sysobjects
    where id = Object_id('tempdb..#PMApply') and type = 'U')
        drop table #PMApply

/* Create the table used to hold PM30200 Info */
print "Creating Temp tables"
go
create table #PMTrans (
    BillInternal# char (21) null,
   
BillDoc# char (21) null,
   
BillDocType# smallint null,
   
BillMode# tinyint null,
   
Vendor char (15) null, 
    OriginalAmtAfterWriteOff
numeric(19, 5) null,
   
DocDate datetime null
   
)
create index #PMTrans_PK on #PMTrans (BillInternal#, BillDocType#, BillMode#, Vendor, OriginalAmtAfterWriteOff)

/* Create the table used to hold Sum of Apply Records from PM30300 */
go
create table #PMApply (
   
BillInternal# char (21) null,
   
BillDocType# smallint null,
   
BillMode# tinyint null,
   
Vendor char (15) null,
   
TotalAppliedAmt numeric(19,5) null,
   
WriteOffAmt numeric(19,5) null,
   
DiscountAmt numeric(19,5) null,
    
RealizedAmt numeric(19,5) null, 
    OrigCurrency
int null
   
)
create index #PMApply_PK on #PMApply (BillInternal#, BillDocType#, BillMode#, Vendor, TotalAppliedAmt, WriteOffAmt, DiscountAmt, RealizedAmt, OrigCurrency)

print "Selecting Bills in History"
go
insert #PMTrans ( BillInternal#, BillDoc#, BillDocType#, BillMode#, Vendor, OriginalAmtAfterWriteOff, DocDate )
    select VCHRNMBR, DOCNUMBR, DOCTYPE, 0, VENDORID, (DOCAMNT - DISTKNAM), DOCDATE 
        from PM30200
        where DOCTYPE < 4 and VOIDED =
        and (DOCAMNT - DISTKNAM) <> 0.0 -- Zero Value Documents
        order by VCHRNMBR

insert #PMTrans ( BillInternal#, BillDoc#, BillDocType#, BillMode#, Vendor, OriginalAmtAfterWriteOff, DocDate ) 
    select VCHRNMBR, DOCNUMBR, DOCTYPE, 1, VENDORID, (DOCAMNT - DISTKNAM), DOCDATE 
        from PM30200
        where DOCTYPE >= 4 and VOIDED =
        and (DOCAMNT - DISTKNAM) <> 0.0 -- Zero Value Documents
        and not (DOCTYPE = 4 and TTLPYMTS = DOCAMNT) -- Remove fully paid returns
        order by VCHRNMBR

print "Selecting Apply Records and Totally Apply Credits in History"
go

/* Get Functional Currency ID */
declare @FUNC varchar(5)
select top 1 @FUNC = FUNLCURR from MC40000

insert #PMApply ( BillInternal#, BillDocType#, BillMode#, Vendor, TotalAppliedAmt, WriteOffAmt, DiscountAmt, RealizedAmt, OrigCurrency ) 
    select APTVCHNM, APTODCTY, 0, VENDORID, sum(APPLDAMT), sum(WROFAMNT), 0, 0 , sum ( case when @FUNC !='' and @FUNC != CURNCYID then 1 else 0 end ) 
        from PM30300 
        group by APTVCHNM, APTODCTY, VENDORID 
        order by APTVCHNM

insert #PMApply ( BillInternal#, BillDocType#, BillMode#, Vendor, TotalAppliedAmt, WriteOffAmt, DiscountAmt, RealizedAmt, OrigCurrency ) 
    select VCHRNMBR, DOCTYPE, 1, VENDORID, sum(APPLDAMT), 0, sum(DISTKNAM), sum(RLGANLOS), sum ( case when @FUNC !='' and @FUNC != CURNCYID then 1 else 0 end ) 
        from PM30300 
        group by VCHRNMBR, DOCTYPE, VENDORID 
        order by VCHRNMBR

print "Entries that are missing apply records"
go

select a.BillMode#, a.BillInternal#, a.BillDoc#, a.BillDocType#, a.Vendor, a.DocDate, a.OriginalAmtAfterWriteOff, 
    isnull(b.TotalAppliedAmt,0) as TotalAppliedAmt,
    isnull((a.OriginalAmtAfterWriteOff - (b.TotalAppliedAmt + b.WriteOffAmt - b.DiscountAmt - b.RealizedAmt)),0) as Differ
    from #PMTrans a 
    left outer join #PMApply b on 
        a
.BillInternal# = b.BillInternal# and a.BillDocType# = b.BillDocType#
        and a.BillMode# = b.BillMode# and a.Vendor = b.Vendor
    where ((isnull((a.OriginalAmtAfterWriteOff - (b.TotalAppliedAmt + b.WriteOffAmt - b.DiscountAmt - b.RealizedAmt)),0) <> 0)
        or (b.Vendor is null))
        and ( a.BillMode# = 0 or isnull(b.OrigCurrency,0) = 0 or (abs(isnull((a.OriginalAmtAfterWriteOff - (b.TotalAppliedAmt + b.WriteOffAmt - b.DiscountAmt - b.RealizedAmt)),0)) > (isnull(b.OrigCurrency,0) / 100.0) ) ) -- Rounding on Credit Documents
    order by a.Vendor

print "Removing Temp tables"
go
drop table #PMTrans
drop table #PMApply

 

The script is also available as an attachment at the bottom of this post.

Since writing this script I have obtained another script which performs a similar check.  Both scripts are in the attached archive. There are some differences in the scripts, the script I wrote works only with History and has allowances for rounding differences on multi-currency transactions, when the other script works on Open and History but does not handle the rounding differences. I would suggest running both scripts.

Let me know if you find this useful.

David

Attachment: SQL PM Find Missing Apply Records.zip
  • Hi David,

    Thanks you so much for sharing this script.

    By the way is there any change you come accross this situation to know why Historical transaction is missing in PM table ( Iguess PM30300 table)?

    Regards,

    Santosh

  • Hi Dave - your script has been helpful for us to identify our own missing apply records, but having found these records, what are our next steps to resolve them...please?

    Thanks,

    Kristie

  • Hi Kristie

    I would suggest logging a support incident to get help to fix the data.

    David

  • Hi David:

    any chance of getting similar script for RM?

    thks.

Page 1 of 1 (4 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post