Sunday, March 23, 2008

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.

7 comments:

Mariano Gomez said...

NOTE: This method can also be used to remove child accounts from a parent account.

Unknown said...

Mariano- Hello! Recently enjoyed one of your sessions at Convergence in ATL! Just wondering if this is still the only way to get this done. (We are on GP2010) Thanks, Kate

Mariano Gomez said...

Kate,

I'm afraid so. There's no straight UI method for this, so as it stands this method still applies - even on GP 2010 and 2013.

MG.-

Anonymous said...

Hello Mariano;
I've got some child accounts I need to remove from a couple national accounts, and in our test company I've gone through the steps you describe to un-apply the invoices from the parent accounts. I then removed the child from the parent, but now need to re-apply the payments to the child so they show as paid. How do I go about this when the payment was made by the parent of the national account, and now that parent doesn't have access to the child's invoices?

Thank you for your time.
Merri Gorrell

Mariano Gomez said...

Merri,

Since the child accounts no longer have an association to the parent account, you cannot apply payments from the parent to the child anymore.

Think of it as two separate customer records.

MG.-

Anonymous said...

Mariano,

I was able to find the invoice and unapply the payments using the PSTL. I still get the error: "You can't remove this child customer ID. Apply record exist between the parent Customer ID and this child customer ID" even though your script shows that there is nothing applied. Any help is appreciated.

Thank You

John

Johnm@calszone.com

Mariano Gomez said...

John,

The script has been fully re-hauled to support newer versions of Microsoft Dynamics GP and SQL Server. Please run and let me know how it goes for you.

MG.-