Removing Child Records from a National Account

This is an actual problem I faced at an actual customer of mine a few months aback and posted today on the Google's Dynamics GP board. It seems that more often than one could assert, a customer's payment records get assigned to the wrong national account (or parent account). This can cause problems down the road when incorrect statements or aging reports are submitted to the corporate customer.

In light of this issue, I developed a two fold solution:

1) The following query identifies the credit documents posted and applied by the parent customer on behalf of the child account.

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode

DECLARE @ChildCustomerNumber CHAR(21), @ParentCustomerNumber CHAR(21);
SET @ChildCustomerNumber = '{child_customer}';
SET @ParentCustomerNumber ='{parent_customer}';

with ApplyInfo as (
 select 
    custnmbr as ChildCustomerId
  , cprcstnm as ParentCustomerId
  , trxsorce as TransactionSource
  , date1 as ApplyDate
  , glpostdt as GLPostingDate
  , aptodcnm as InvoiceNumber
  , aptodcty as DocumentType
  , aptodcdt as InvoiceDate
  , apptoamt as InvoiceAmount
  , apfrdcnm as PaymentNumber
  , apfrdcty as PaymentType
  , apfrdcdt as PaymentDate
  , apfrmaplyamt as ApplyAmount
  , actualapplytoamount as ActualAmountApplied
 from rm20201
 union all
 select 
    custnmbr as ChildCustomerId
  , cprcstnm as ParentCustomerId
  , trxsorce as TransactionSource
  , date1 as ApplyDate
  , glpostdt as GLPostingDate
  , aptodcnm as InvoiceNumber
  , aptodcty as DocumentType
  , aptodcdt as InvoiceDate
  , apptoamt as InvoiceAmount
  , apfrdcnm as PaymentNumber
  , apfrdcty as PaymentType
  , apfrdcdt as PaymentDate
  , apfrmaplyamt as ApplyAmount
  , actualapplytoamount as ActualAmountApplied
 from rm30201
)
select * from ApplyInfo where ChildCustomerId = @ChildCustomerNumber and ParentCustomerId = @ParentCustomerNumber;
GO 

2) You will then need to use Dynamics GP's Professional Services Tools Library (PSTL) to unapply the invoices associated to the child record, which in turn will unapply the credit documents posted under the national account. Make sure you print all reports generated by Professional Tools as you will need them to reapply the credit documents.

Well, hope this helps in solving a major headache faced by many AR departments around the globe.

Until next post!

MG.-
Mariano Gomez, MVP
Intelligent Partnerships, LLC
http://www.IntelligentPartnerships.com/

Edit:

09/25/2014: The previous script has been replaced with this new script which directly retrieves the parent customer column from the apply table, thus improving the results. This script has been tested with Microsoft Dynamics GP 2010 and above. Support for common table expressions (CTEs) were introduced with SQL Server 2005, thus this script may not work with prior versions of SQL Server.