Category Archives: Receivables Management

Average Days to Pay Calculation (SQL Code)

Posted on by

Average Days To Pay (ADTP) is a very crucial information for us to assess a customer or group of customers and their nature of paying the invoices.

In GP, we have this calculated and displayed when we select a customer in Customer Credit Summary window (shown below):

The following is the definition & formula taken from GP Receivables Management manual, which clearly explains how this is calculated:

After a customer has paid his or her first invoice, the average days to pay (ADTP) is calculated based on the number of invoices a customer has, the time taken to pay the first invoice, and the time taken to pay the most recent invoice.

The formula for calculating the average days to pay is:

ADTP = (Current ADTP) x (Number of Invoices) + (Number of Days Taken to Pay Most Recent Invoice) / (Number of Invoices + 1)

The time it took to pay the first invoice would provide the initial value for the Current ADTP. Any later invoices paid by this customer will provide the values for the number of invoices and the number of days taken to pay the most recent invoice. The ADTP calculated on the customer’s initial invoices then becomes the “Current ADTP.” You can use this value when you recalculate the ADTP for later invoices.

With this as basis, I have written a SQL script (link for which is provided at the end of this post) which will calculate ADTP group by a Customer ID.

Parameters of this SQL Procedure:

1. @FROMDATE – Self-Explanatory

2. @TODATE – Self-Explanatory

So pass two dates and the results are shown based on invoices between those two dates.

I have heard from several GP users that the result what GP shows on Customer Credit Summary window is not correct at times. This SQL script will give you a chance to compare between two and decide which one’s correct.

As always, your feedbacks are welcome.

RM_AverageDaysToPay.sql

VAIDY

Stuck Receivables Transaction due to Incorrect Amounts

Posted on by

Yesterday, I received a support request from one of our users who tried posting a batch full of receivables transactions and received an error message for one transaction whose distribution amounts did not match document amount.

It was suppose to be a simple task to release this transaction for edit, but due to some reason this transaction got stuck in between. It did not come back to it’s original batch and quite obviously it did not get posted either due to that error.

Following are the steps involved in releasing that transaction:

CHECKS

1. Check whether the transaction is still in RM10301 (RM Sales Work) table.

In my case it was still in RM10301 table.

2. Check the fields BACHNUMB (Batch Number) and BCHSOURC (Batch Source) in RM10301 table for that transaction.

In my case, BACHNUMB contained the USER ID who tried posting the batch; BCHSOURC contained XRM_Sales instead of RM_Sales, since it was in the middle of posting process and got stuck.

3. Check whether a batch with that user’s USER ID is created in SY00500 (Batch Header) table.

In my case, there was indeed a batch with that User ID. Because of which, this user could not open Receivables Transaction Entry window and was receiving the (in)famous error message “A previous transaction level posting… … …”.

CORRECTION STEPS

1. I first created a Receivables batch (I named it as RM-RECOV).

2. Through SQL Server Management Studio, I updated RM10301 table fields BACHNUMB and BCHSOURC as follows:

BACHNUMB = ‘RM-RECOV’
BCHSOURC = ‘RM_Sales’

3. Deleted the batch [USER ID] from SY00500 table, to remove user’s transaction posting session lock.

4. Tried opening that transaction on Receivables Transaction Entry form. Since the batch number RM-RECOV was updated through backend, it did not obviously update batch information. Due to which, I still could not open this transaction.

5. As a final step, I ran Receivables Reconcile Batch Information (Tools => Utilities => Sales => Reconcile). This step updated the amount from that erroneous transaction on to batch RM-RECOV.

After above steps, user was then able to reopen that transaction and edit it.

Bottomline: SQL approach is not always the only option for consultants to clear such issues. We need to perform some crucial application level tasks that would compliment SQL methods.

VAIDY

Void OR Return? – Belinda’s Advise

Posted on by

Belinda advises on what needs to be done when we want to reverse a wrong SOP Invoice. Read it here: Void an Invoice or enter a Return.

Now, let me add to that advise. As she has pointed out, if you have posted an SOP Invoice and if it involves an Inventory Item, it’s a thumb rule that we must pass a Return.

Return does the following:

1. Returns Inventory Items to our Stock.
2. Returns Serial / Lot Numbers for the respective Item as well.
3. Reduces also the Customer Receivables.
4. Passes a reverse GL.

So your Inventory is correct, Receivables is correct & is your GL.

VAIDY