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
Created Date: 05/08/2012
Created by Samuel Mathew - Eclipse Computing
SOP30200 : Alias= SH | SOP _HDR_HIST - Sales Transaction History
RM20101 : Alias = RO | RM_OPEN - RM Open File
SY03300 : Alias = PT | SY_Payment_Terms_MSTR - Payment Terms Master
SELECT CASE SH.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,
RO.CURTRXAM AS [Current Balance],
WHEN '01/01/1900' THEN Dateadd(day, PT.DUEDTDS, SH.GLPOSTDT)
ELSE Dateadd(day, PT.DUEDTDS, SH.ACTLSHIP)
END AS [Estimated Payment Date],
WHEN '01/01/1900' THEN Datediff(day, SH.GLPOSTDT, Getdate())
ELSE Datediff(day, SH.ACTLSHIP, Getdate())
END AS [Over Due]
FROM SOP30200 SH
INNER JOIN SY03300 PT
ON SH.PYMTRMID = PT.PYMTRMID
INNER JOIN RM20101 RO
ON SH.CUSTNMBR = RO.CUSTNMBR
AND SH.SOPNUMBE = RO.DOCNUMBR
WHERE ( SH.SOPTYPE NOT IN ( 5, 6 ) )
AND ( SH.VOIDSTTS = 0 )
AND ( SH.DOCAMNT > 0 )
AND ( RO.CURTRXAM <> 0 )
Grant permission to this view to DYNGRP
GRANT SELECT ON view_unpaidsoptransaction_with_overdue TO dyngrp