GP Guru Level Up: Episode 3 - How to bring your out of the box Aged Trial Balance into Smartlist

 We had a great session at our Dynamics User Group Virtual with our guest speaker Marion Palaming discussing techniques in using SQL Views to create  PM and RM Aged Trial Balance into Smartlist.

Here are the SQL Views used in the presentation:

Disclaimer: did some minor updates from Victoria Yudin's SQL Views

RECEIVABLES MODULE

USE ['yourDB]

GO

/****** Object:  View [dbo].[RM_SMAging_CurrentDate]    Script Date: 4/21/2023 10:54:48 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[RM_SMAging_CurrentDate]

AS

SELECT CM.CUSTNMBR AS Customer_ID, CM.CUSTNAME AS Customer_Name, CM.PYMTRMID AS Customer_Terms, CM.CUSTCLAS AS Customer_Class, CM.PRCLEVEL AS Price_Level, RM.CURNCYID, 

             CASE RM.RMDTYPAL WHEN 1 THEN 'Sale / Invoice' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service Repair' WHEN 6 THEN 'Warranty' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' ELSE 'Other' END AS Document_Type,

              RM.DOCNUMBR AS Document_Number, RM.DOCDATE AS Document_Date, RM.DUEDATE AS Due_Date, S.LASTPYDT AS Last_Payment_Date, CASE WHEN RM.RMDTYPAL < 7 THEN RM.ORTRXAMT ELSE RM.ORTRXAMT * - 1 END AS Document_Amount, 

             CASE WHEN RM.RMDTYPAL < 7 THEN RM.CURTRXAM ELSE RM.CURTRXAM * - 1 END AS Unapplied_Amount, CASE WHEN DATEDIFF(d, RM.DUEDATE, getdate()) <= 0 AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM WHEN DATEDIFF(d, RM.DOCDATE, getdate()) <= 0 AND 

             RM.RMDTYPAL > 6 THEN RM.CURTRXAM * - 1 ELSE 0 END AS [Current], CASE WHEN DATEDIFF(d, RM.DUEDATE, getdate()) BETWEEN 1 AND 30 AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM WHEN DATEDIFF(d, RM.DOCDATE, getdate()) BETWEEN 1 AND 30 AND 

             RM.RMDTYPAL > 6 THEN RM.CURTRXAM * - 1 ELSE 0 END AS [1_to_30_Days], CASE WHEN DATEDIFF(d, RM.DUEDATE, getdate()) BETWEEN 31 AND 60 AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM WHEN DATEDIFF(d, RM.DOCDATE, getdate()) BETWEEN 31 AND 60 AND 

             RM.RMDTYPAL > 6 THEN RM.CURTRXAM * - 1 ELSE 0 END AS [31_to_60_Days], CASE WHEN DATEDIFF(d, RM.DUEDATE, getdate()) BETWEEN 61 AND 90 AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM WHEN DATEDIFF(d, RM.DOCDATE, getdate()) BETWEEN 61 AND 90 AND 

             RM.RMDTYPAL > 6 THEN RM.CURTRXAM * - 1 ELSE 0 END AS [61_to_90_Days], CASE WHEN DATEDIFF(d, RM.DUEDATE, getdate()) BETWEEN 91 AND 120 AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM WHEN DATEDIFF(d, RM.DOCDATE, getdate()) BETWEEN 91 AND 120 AND 

             RM.RMDTYPAL > 6 THEN RM.CURTRXAM * - 1 ELSE 0 END AS [91_to_120_Days], CASE WHEN DATEDIFF(d, RM.DUEDATE, getdate()) BETWEEN 121 AND 183 AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM WHEN DATEDIFF(d, RM.DOCDATE, getdate()) BETWEEN 121 AND 183 AND 

             RM.RMDTYPAL > 6 THEN RM.CURTRXAM * - 1 ELSE 0 END AS [121_to_183_Days], CASE WHEN DATEDIFF(d, RM.DUEDATE, getdate()) BETWEEN 184 AND 365 AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM WHEN DATEDIFF(d, RM.DOCDATE, getdate()) BETWEEN 184 AND 365 AND 

             RM.RMDTYPAL > 6 THEN RM.CURTRXAM * - 1 ELSE 0 END AS [184_to_365_Days], CASE WHEN DATEDIFF(d, RM.DUEDATE, getdate()) > 365 AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM WHEN DATEDIFF(d, RM.DOCDATE, getdate()) > 365 AND 

             RM.RMDTYPAL > 6 THEN RM.CURTRXAM * - 1 ELSE 0 END AS [365_and_Over]

FROM   dbo.RM20101 AS RM INNER JOIN

             dbo.RM00101 AS CM ON RM.CUSTNMBR = CM.CUSTNMBR LEFT OUTER JOIN

             dbo.RM00103 AS S ON RM.CUSTNMBR = S.CUSTNMBR

WHERE (RM.VOIDSTTS = 0) AND (RM.CURTRXAM <> 0)

GO

grant select on RM_SMAging_CurrentDate to DYNGRP

PAYABLES MODULE

USE [yourDB]

GO

/****** Object:  View [dbo].[PM_SMAgingReport]    Script Date: 4/21/2023 10:53:37 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[PM_SMAgingReport]

AS

SELECT 'SMARTLIST' AS LINX, VM.VENDORID AS Vendor_ID, VM.VENDNAME AS Vendor_Name, VM.VNDCLSID AS Vendor_Class, VM.PYMTRMID AS Vendor_Terms, 

             CASE P.DOCTYPE WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Finance Charge' WHEN 3 THEN 'Misc Charge' WHEN 4 THEN 'Return' WHEN 5 THEN 'Credit Memo' WHEN 6 THEN 'Payment' END AS Document_Type, P.DOCNUMBR AS Document_Number, P.DOCDATE AS Document_Date, 

             P.PSTGDATE AS GL_Posting_Date, P.DUEDATE AS Due_Date, P.TRXDSCRN AS Description, CASE WHEN P.DOCTYPE < 4 THEN P.DOCAMNT ELSE P.DOCAMNT * - 1 END AS Document_Amount, 

             CASE WHEN P.DOCTYPE < 4 THEN P.CURTRXAM ELSE P.CURTRXAM * - 1 END AS Unapplied_Amount, P.CURNCYID, a.AGNGDATE,

                 (SELECT CAST(GETDATE() AS datetime) AS Expr1) AS [Current Date], DATEDIFF(d, P.DUEDATE, a.AGNGDATE) AS AGE, CASE WHEN datediff(d, P.DUEDATE, (a.AGNGDATE)) < 1 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, (a.AGNGDATE)) < 1 AND 

             P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [Current], CASE WHEN datediff(d, P.DUEDATE, (a.AGNGDATE)) BETWEEN 1 AND 30 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, (a.AGNGDATE)) BETWEEN 1 AND 30 AND 

             P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [1_to_30_Days], CASE WHEN datediff(d, P.DUEDATE, (a.AGNGDATE)) BETWEEN 31 AND 60 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, (a.AGNGDATE)) BETWEEN 31 AND 60 AND 

             P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [31_to_60_Days], CASE WHEN datediff(d, P.DUEDATE, (a.AGNGDATE)) BETWEEN 61 AND 90 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, (a.AGNGDATE)) BETWEEN 61 AND 90 AND 

             P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [61_to_90_Days], CASE WHEN datediff(d, P.DUEDATE, (a.AGNGDATE)) BETWEEN 91 AND 150 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, (a.AGNGDATE)) BETWEEN 91 AND 150 AND 

             P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [91_to_150_Days], CASE WHEN datediff(d, P.DUEDATE, (a.AGNGDATE)) BETWEEN 151 AND 365 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, (a.AGNGDATE)) BETWEEN 151 AND 365 AND 

             P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [151_to_365_Days], CASE WHEN datediff(d, P.DUEDATE, (a.AGNGDATE)) > 365 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, (a.AGNGDATE)) > 365 AND 

             P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [365_and_Over]

FROM   dbo.PM00200 AS VM INNER JOIN

                 (SELECT 'SMARTLIST' AS LINX, VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DISTKNAM, DISCAMNT, DSCDLRAM, BACHNUMB, TRXSORCE, BCHSOURC, DISCDATE, DUEDATE, PORDNMBR, TEN99AMNT, WROFAMNT, DISAMTAV, 

                              TRXDSCRN, UN1099AM, BKTPURAM, BKTFRTAM, BKTMSCAM, VOIDED, HOLD, CHEKBKID, DINVPDOF, PPSAMDED, PPSTAXRT, PGRAMSBJ, GSTDSAMT, POSTEDDT, PTDUSRID, MODIFDT, MDFUSRID, PYENTTYP, CARDNAME, PRCHAMNT, TRDISAMT, MSCCHAMT, 

                              FRTAMNT, TAXAMNT, TTLPYMTS, CURNCYID, PYMTRMID, SHIPMTHD, TAXSCHID, PCHSCHID, FRTSCHID, MSCSCHID, PSTGDATE, DISAVTKN, CNTRLTYP, NOTEINDX, PRCTDISC, RETNAGAM, ICTRX, Tax_Date, PRCHDATE, CORRCTN, SIMPLIFD, BNKRCAMT, APLYWITH, 

                              Electronic, ECTRX, DocPrinted, TaxInvReqd, VNDCHKNM, BackoutTradeDisc, CBVAT, VADCDTRO, TEN99TYPE, TEN99BOXNUMBER, PONUMBER, Workflow_Status, InvoiceReceiptDate, LNGDESC, DEX_ROW_TS, DEX_ROW_ID

                 FROM    dbo.PM20000) AS P ON P.VENDORID = VM.VENDORID INNER JOIN

                 (SELECT 'SMARTLIST' AS LINX, RPTGRIND, RTPACHIN, RTGRSBIN, ASKECHTM, EXPTTYPE, FILEXPNM, IFFILXST, FINRPTNM, PRNTOFIL, PRTOPRTR, PRTOSCRN, SORTBY, SORTBY2, HISTTYPE, PYMNTDAT, VOIDONLY, EXCLVOID, Include_Canceled, DATEOVERDUE, 

                              Days_Overdue, POP_Date_Selection, Include_Closed, Include_Released, AGEBY, COMPTYPE, STVNDRID, STVNDNAM, STVNDCLS, STTSTATE, STRTZPCD, STRTPHON, STRTUDEF, STTCNTCT, Start_PO_Date, STTSHNAM, STTLOCID, Start_Promised_Date, 

                              Start_Required_Date, STVNSTUS, STT1099T, STPERIOD, STRTYEAR, STTABYTD, STRTPRTY, STTDUEDT, STDISCDT, ALORANGE, STTACNCD, STDOCNUM, STTDOCDT, STDOCTYP, STVCHNUM, STTITNUM, STCHKNUM, STTCHKDT, STCHBKID, STBCHSRC, STBCHNUM, 

                              STPSTDDT, STCLSDSC, ENDVNDID, ENDVNDNM, ENDVNCLS, ENDSTATE, ENDZIPCD, ENPHONBR, End_PO_Date, ENUSRDEF, ENDLOCID, ENITMNBR, End_Required_Date, End_Promised_Date, ENDVNDCT, ENDSHNAM, ENVNSTTS, E1099TYP, ENDPEROD, ENDYEAR, 

                              ENAMBYTD, ENPRYRTY, ENDDUEDT, ENDISCDT, ALORNGE2, ENAUCNCD, ENDOCNUM, ENDDOCDT, ENDOCTYP, ENVCHNUM, ENCHKNUM, ENDCHKDT, ENCHBKID, ENBCHSRC, ENDBNMBR, ENPSTDDT, ENCLDSCR, AGNGDATE, PBSDCDTK, PBSDUDTK, PBSDSDTK, 

                              PBSCHDTK, PBSPTDTK, PBEDCDTK, PBEDUDTK, PBEDSDTK, PBECHDTK, PBEPDTKN, PBCDTTKN, DETLDRPT, SINGACT, STTACNUM_1, STTACNUM_2, STTACNUM_3, STTACNUM_4, STTACNUM_5, EACCNBR_1, EACCNBR_2, EACCNBR_3, EACCNBR_4, EACCNBR_5, 

                              STRPONUM, ENDPONUM, EndPOPRcptNum, StartPOPRcptNum, STRCREAT, STRCTDT, ENRCTDT, STRCTNUM, ENRCTNBR, ENDCREATE, STRPOSTA, ENDPOSTA, INCRCPTS, DISSRLOT, STRXSRC, ENTRXSRC, CRBLNCCB, FLLYPDCB, ZROBALCB, EXCLZBAL, 

                              NOACTVCB, Unposted_Applied_CR_Docs, Multicurrency_Information, Include_Distribution_Typ, RPTXRATE, RPRTCLMD, PRTCURIN, INCLFUNC, STARTLINEORIGIN, ENDLINEORIGIN, STARTBUYERID, ENDBUYERID, INCLUDEHOLDPO, INCLUDEHOLDPOONLY, 

                              STARTRELEASEBYDATE, ENDRELEASEBYDATE, Include_New, Include_Change_Order, Include_Received, SummaryYear, DateSensitivitySumSelect, StartProjNum, EndProjNum, StartCostCatID, EndCostCatID, IncludeApprovedPOsOnly, MISC, INTEREST, DIVIDEND, 

                              DEX_ROW_ID

                 FROM    dbo.PM70500

                 WHERE (RTPACHIN = 2) AND (RPTGRIND = 3) AND (ASKECHTM = 0) AND (RTGRSBIN = 131)) AS a ON P.LINX = a.LINX

WHERE (P.CURTRXAM <> 0) AND (P.VOIDED = 0)

GO

grant select on PM_SMAgingReport to DYNGRP