Dynamics GP - SQL View - Aged AP Payments, GL, Invoices, POs - AP Aging by Payment Date

/****** Object:  View [dbo].[BI_AP_Apply]    Script Date: 13/09/2017 03:49:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_AP_Apply]
AS
SELECT        P.VENDORID AS Vendor_ID, PM.VENDNAME AS Vendor_Name, CASE P.DOCTYPE WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Finance Charge' WHEN 3 THEN 'Misc Charge' END AS Document_Type,
                         P.DOCDATE AS Document_Date, P.VCHRNMBR AS Voucher_Number, P.DOCNUMBR AS Document_Number, P.DOCAMNT AS Document_Amount, P.TEN99AMNT AS [1099_Amount],
                         P.TEN99BOXNUMBER AS [1099_Box],
                         CASE P.TEN99TYPE WHEN 1 THEN 'Not a 1099 Vendor' WHEN 2 THEN 'Divident' WHEN 3 THEN 'Interest' WHEN 4 THEN 'Miscellaneous' WHEN 5 THEN 'Withholding' ELSE '' END AS [1099_Type],
                         COALESCE (PA.APPLDAMT, 0) AS Applied_Amount, COALESCE (PA.VCHRNMBR, '') AS Payment_Voucher_Number, COALESCE (P2.DOCNUMBR, '') AS Payment_Doc_Number, COALESCE (P2.DOCDATE, '1/1/1900')
                         AS Payment_Date, COALESCE (PA.DATE1, '1/1/1900') AS Apply_Date, COALESCE (PA.GLPOSTDT, '1/1/1900') AS Apply_GL_Posting_Date,
                         CASE PA.DOCTYPE WHEN 4 THEN 'Return' WHEN 5 THEN 'Credit Memo' WHEN 6 THEN 'Payment' ELSE '' END AS Payment_Type, COALESCE (PA.POSTED, 'Unpaid') AS Payment_Status, P.PORDNMBR,
                         PO.DOCDATE AS PODate, PO.CURNCYID, P.DUEDATE, PM.VNDCLSID AS VendorClass, PO.POPCONTNUM AS ReqNo, P.CURTRXAM, CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) < 31 AND
                          P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) < 31 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [Current], CASE WHEN datediff(d, COALESCE (P2.DOCDATE,
                         getdate()), getdate()) BETWEEN 31 AND 60 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 31 AND 60 AND
                         P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [31_to_60_Days], CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) BETWEEN 61 AND 90 AND
                         P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 61 AND 90 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [61_to_90_Days], CASE WHEN datediff(d,
                         COALESCE (P2.DOCDATE, getdate()), getdate()) BETWEEN 91 AND 120 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 91 AND 120 AND
                         P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [91_to_120_Days], CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) > 90 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d,
                          P.DOCDATE, getdate()) > 120 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [120_and_Over], DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS AgingDate, DATEDIFF(d, COALESCE (P2.DOCDATE,
                          GETDATE()), GETDATE()) AS DaysOld, ISNULL(PO.SUBTOTAL, 0) AS POSubTot, ISNULL(PO.TAXAMNT, 0) AS POTaxAmt, ISNULL(PO.SUBTOTAL + PO.TAXAMNT, 0) AS POTotal
FROM            (SELECT        VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE, PORDNMBR, DUEDATE, CURTRXAM
                          FROM            dbo.PM30200
                          UNION ALL
                          SELECT        VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE, PORDNMBR, DUEDATE, CURTRXAM
                          FROM            dbo.PM20000) AS P LEFT OUTER JOIN
                         dbo.POP10100 AS PO ON P.PORDNMBR = PO.PONUMBER LEFT OUTER JOIN
                             (SELECT        VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT, CASE POSTED WHEN 0 THEN 'Unposted' ELSE 'Posted' END AS POSTED
                               FROM            dbo.PM10200
                               UNION
                               SELECT        VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT, 'Posted' AS POSTED
                               FROM            dbo.PM30300) AS PA ON P.VCHRNMBR = PA.APTVCHNM AND P.VENDORID = PA.VENDORID AND P.DOCTYPE = PA.APTODCTY LEFT OUTER JOIN
                             (SELECT        VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
                               FROM            dbo.PM20000 AS PM20000_1
                               UNION ALL
                               SELECT        VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
                               FROM            dbo.PM30200 AS PM30200_1) AS P2 ON P2.VCHRNMBR = PA.VCHRNMBR AND P2.DOCTYPE = PA.DOCTYPE LEFT OUTER JOIN
                         dbo.PM00200 AS PM ON P.VENDORID = PM.VENDORID
WHERE        (P.DOCTYPE IN (1, 2, 3)) AND (P.VOIDED = 0)

GO