SQL view for unpaid SOP Transaction with No. of 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 : 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,
         SH.SOPNUMBE,
         SH.CUSTNMBR,
         SH.CUSTNAME,
         SH.GLPOSTDT,
         SH.DOCDATE,
         SH.ACTLSHIP,
         SH.ReqShipDate,
         SH.CSTPONBR,
         SH.MSTRNUMB,
         SH.DOCAMNT,
         SH.ORDOCAMT,
         SH.CURNCYID,
         SH.PYMTRMID,
         PT.DUEDTDS,
         RO.CURTRXAM AS [Current Balance],
         CASE SH.ACTLSHIP
           WHEN '01/01/1900' THEN Dateadd(day, PT.DUEDTDS, SH.GLPOSTDT)
           ELSE Dateadd(day, PT.DUEDTDS, SH.ACTLSHIP)
         END         AS [Estimated Payment Date],
         CASE SH.ACTLSHIP
           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 )

go

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