SQL view for unpaid SOP Transaction with no. overdue days

Recently one of my customer asked me to create a script to shows all open (unpaid) sales transaction with the total number of days overdue based on the current system date and customer’s payment terms. Customer also wanted to see a column that shows the estimated payment date based on the actual payment terms and the posting/actual ship date.

The view below links SOP30200 (SOP _HDR_HIST – Sales Transaction  History) and RM20101 (RM_OPEN – RM Open File) and SY03300 (SY_Payment_Terms_MSTR – Payment Terms Master). The overdue days is the difference between the current date and the GLPOSTDT (GL Posting Date) or ACTLSHIP (Actual Ship Date). The “Estimated Payment Date” is calculated by adding the DUEDTDS (Due Date in Days) from SY03300 table with the GL Posting date or actual ship date.

CREATE VIEW view_unpaidsoptransaction_with_overdue
AS
/*
Created Date: 05/08/2012
Created by Samuel Mathew – Eclipse Computing
Tables Used:
SOP30200 : SOP _HDR_HIST – Sales Transaction History
RM20101 : RM_OPEN – RM Open File
SY03300 : SY_Payment_Terms_MSTR – Payment Terms Master
*/
SELECT CASE SOP30200.SOPTYPE
WHEN 1 THEN ‘Quote’
WHEN 2 THEN ‘Order’
WHEN 3 THEN ‘Invoice’
WHEN 4 THEN ‘Return’
WHEN 5 THEN ‘Back Order’
WHEN 6 THEN ‘Fulfillment Order’
END AS SOPTYPE,
SOP30200.SOPNUMBE,
SOP30200.CUSTNMBR,
SOP30200.CUSTNAME,
SOP30200.GLPOSTDT,
SOP30200.DOCDATE,
SOP30200.ACTLSHIP,
SOP30200.ReqShipDate,
SOP30200.CSTPONBR,
SOP30200.MSTRNUMB,
SOP30200.DOCAMNT,
SOP30200.ORDOCAMT,
SOP30200.CURNCYID,
SOP30200.PYMTRMID,
SY03300.DUEDTDS,
RM20101.CURTRXAM AS Current Balance,
CASE SOP30200.ACTLSHIP WHEN ’01/01/1900′ THEN Dateadd(day, SY03300.DUEDTDS,
SOP30200.GLPOSTDT)
ELSE Dateadd(day, SY03300.DUEDTDS, SOP30200.ACTLSHIP)
END AS Estimated Payment Date,
CASE SOP30200.ACTLSHIP WHEN ’01/01/1900′ THEN Datediff(day, SOP30200.GLPOSTDT, Getdate())
ELSE Datediff(day, SOP30200.ACTLSHIP, Getdate())
END AS Over Due
FROM SOP30200
INNER JOIN SY03300
ON SOP30200.PYMTRMID = SY03300.PYMTRMID
INNER JOIN RM20101
ON SOP30200.CUSTNMBR = RM20101.CUSTNMBR
AND SOP30200.SOPNUMBE = RM20101.DOCNUMBR
WHERE ( SOP30200.SOPTYPE NOT IN ( 5, 6 ) )
AND ( SOP30200.VOIDSTTS = 0 )
AND ( SOP30200.DOCAMNT > 0 )
AND ( RM20101.CURTRXAM <> 0 )
GO

/* Grant permission to this view to DYNGRP */
GRANT SELECT ON view_unpaidsoptransaction_with_overdue TO DYNGRP

See the article here:
SQL view for unpaid SOP Transaction with no. overdue days

May 8, 2012 · admin · No Comments
Tags: , , , , , ,  · Posted in: Blogs I Follow Total Views: 640

Customer Credit Summary: Average Days To Pay

I received an email today with a query on GP’s “Average Days To Pay” on a Customer’s Credit Summary.

Query is: How GP calculates Average Days To Pay for a customer?

According to GP’s Receivables Management user manual:

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.

There are two ADTPs; LTD (Life To Date) and YTD (Year To Date).

The important point that you may have to remember is that Average Days To Pay YTD will be calculated only based on Amounts Since Last Close.

VAIDY

Visit site:
Customer Credit Summary: Average Days To Pay

February 21, 2012 · Vaidyanathan Mohan · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 481

Refund Cash to customer

Refund Cash to customer

In Dynamics GP have option for “Refund to Customer” (Transactions–>Sales–>Refund Checks) but  in small scale company it is not possible to make payment through checks for every customers.

When we create Refund from Dynamics GP it transfer Receivable balance to Payables Management creating checks for customer.

Scenarios:

1) Received access payment from customer through credit card and need to return cash back to him.

2) Access Payment Received from customer and need to pay back cash.

Make Debit Memo from Transaction–>Sales–>Receivable Transaction–>Debit Memo and apply to Invoice.

Distributions:

Debit : Accounts Receivable (Refund Amount)
Credit : Accounts Receivable Misc

With above distribution your Receivable will be get zero.

To Bank:

Transaction–>Financial –>Bank Transactions

Change entry to “Decrease Adjustment” and enter the amount for Refund.

Distribution:

Debit: Accounts Receivable Misc (Refund Amount)

Credit: Cash A/c

Simple Cash Refund entry without Checks(Cheques)

Thanks
Sandip Jadhav



Original post:
Refund Cash to customer

October 17, 2010 · Sandip Jadhav · No Comments
Tags: , , , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 598