SQL view with all GL distributions for AR transactions

As a corollary to my SQL view with all posted Receivables transactions, below is a view that will add all the General Ledger distributions to the AR transactions. I made a few changes to the original AR transactions view, primarily to remove some columns that are typically not needed and add underscores to the column names so that this can be used with SmartList Builder without having to change column names.

For more views like this, check out my GP Reports page.

For help with using this in SmartList Builder, take a look at my post on How to use a SQL view in SmartList Builder.

~~~~~

CREATE VIEW view_RM_Trx_Distributions
AS

/*******************************************************************
view_RM_Trx_Distributions
Created on Feb 10, 2010 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Returns all posted (open and history) Receivables transactions
with their GL distributions. All amounts are functional.
Tables used:
RO: RM20101 - Open Transactions
RH: RM30101 – Historical Transactions
DO: RM10101 - Work and Open Distributions
DH: RM30301 - Historical Distributions
G: GL00105 - Account Index Master
*******************************************************************/

SELECT RO.CUSTNMBR Customer_ID,
       RO.CPRCSTNM Parent_Customer,
       RO.RMDTYPAL Doc_Type_Number,
       CASE RO.RMDTYPAL
         WHEN 0 THEN 'Reserved'
         WHEN 1 THEN 'Invoice'
         WHEN 2 THEN 'Scheduled Pmt'
         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 ''
         END Document_Type,
       RO.DOCNUMBR Document_Number,
       RO.CHEKNMBR Check_Number,
       RO.BACHNUMB Batch_ID,
       RO.BCHSOURC Batch_Source,
       RO.TRXSORCE Trx_Source,
       CASE RO.CSHRCTYP
         WHEN 0 THEN 'Check'
         WHEN 1 THEN 'Cash'
         WHEN 2 THEN 'Credit Card'
         ELSE ''
         END Cash_Receipt_Type,
       RO.DUEDATE Due_Date,
       RO.DOCDATE Document_Date,
       RO.POSTDATE Posted_Date,
       RO.PSTUSRID Post_User_ID,
       RO.GLPOSTDT GL_Posting_Date,
       RO.LSTEDTDT Last_Edit_Date,
       RO.LSTUSRED Last_User_To_Edit,
       RO.ORTRXAMT Original_Trx_Amount,
       RO.CURTRXAM Current_Trx_Amount,
       RO.SLSAMNT Sales_Amount,
       RO.COSTAMNT Cost_Amount,
       RO.FRTAMNT Freight_Amount,
       RO.MISCAMNT Misc_Amount,
       RO.TAXAMNT Tax_Amount,
       RO.COMDLRAM Commission_Amount,
       RO.CASHAMNT Cash_Amount,
       RO.DISTKNAM Discount_Taken_Amount,
       RO.DISAVAMT Discount_Avail_Amount,
       RO.DISCRTND Discount_Returned,
       RO.DISCDATE Discount_Date,
       RO.DSCDLRAM Discount_Dollar_Amount,
       RO.DSCPCTAM Discount_Percent_Amount,
       RO.WROFAMNT Write_Off_Amount,
       RO.TRXDSCRN Trx_Description,
       RO.CSPORNBR Customer_PO,
       RO.SLPRSNID Salesperson_ID,
       RO.SLSTERCD Sales_Territory,
       RO.DINVPDOF Date_Inv_Paid_Off,
       RO.PPSAMDED PPS_Amount_Deducted,
       RO.GSTDSAMT GST_Discount_Amount,
       CASE RO.VOIDSTTS
         WHEN 0 THEN 'Not Voided'
         WHEN 1 THEN 'Voided'
         WHEN 2 THEN 'NSF check'
         WHEN 3 THEN 'Waived finance charge'
         ELSE ''
         END VoidS_tatus,
       RO.VOIDDATE Void_Date,
       RO.TAXSCHID Tax_Schedule_ID,
       RO.CURNCYID Currency_ID,
       RO.PYMTRMID Payment_Terms_ID,
       RO.SHIPMTHD Shipping_Method,
       RO.TRDISAMT Trade_Discount_Amount,
       RO.NOTEINDX Note_Index,
       RO.Tax_Date Tax_Date,
       coalesce(G.ACTNUMST,'') Account_Number,
       CASE DO.DISTTYPE
         WHEN 1 THEN 'Cash'
         WHEN 2 THEN 'Terms Taken'
         WHEN 3 THEN 'Accounts Receivable'
         WHEN 4 THEN 'Writeoffs'
         WHEN 5 THEN 'Terms Available'
         WHEN 6 THEN 'GST'
         WHEN 7 THEN 'PPS'
         WHEN 8 THEN 'Other'
         WHEN 9 THEN 'Sales'
         WHEN 10 THEN 'Trade'
         WHEN 11 THEN 'Frieght'
         WHEN 12 THEN 'Miscellaneous'
         WHEN 13 THEN 'Taxes'
         WHEN 14 THEN 'COGS'
         WHEN 15 THEN 'Inventory'
         WHEN 16 THEN 'Finance Charges'
         WHEN 17 THEN 'Returns'
         WHEN 18 THEN 'Debit Memo'
         WHEN 19 THEN 'Credit Memo'
         WHEN 20 THEN 'Service'
         WHEN 21 THEN 'Warranty Expense'
         WHEN 22 THEN 'Warranty Sales'
         WHEN 23 THEN 'Commissions Expense'
         WHEN 24 THEN 'Commissions Payable'
         WHEN 25 THEN 'Unit Account'
         WHEN 26 THEN 'Rounding'
         WHEN 27 THEN 'Realized Gain'
         WHEN 28 THEN 'Realized Loss'
         WHEN 29 THEN 'Unrealized Gain'
         WHEN 30 THEN 'Unrealized Loss'
         ELSE ''
         END 'Distribution_Type',
       coalesce(DO.DEBITAMT,0) Debit_Amount,
       coalesce(DO.CRDTAMNT,0) Credit_Amount,
       coalesce(DO.DistRef,'') Distribution_Reference

FROM RM20101 RO
    LEFT OUTER JOIN
       RM10101 DO
       ON RO.RMDTYPAL = DO.RMDTYPAL
       AND RO.DOCNUMBR = DO.DOCNUMBR
    LEFT OUTER JOIN
       GL00105 G
       ON DO.DSTINDX = G.ACTINDX

UNION ALL

SELECT RH.CUSTNMBR Customer_ID,
       RH.CPRCSTNM Parent_Customer,
       RH.RMDTYPAL Doc_Type_Number,
       CASE RH.RMDTYPAL
         WHEN 0 THEN 'Reserved'
         WHEN 1 THEN 'Invoice'
         WHEN 2 THEN 'Scheduled Pmt'
         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 ''
         END Document_Type,
       RH.DOCNUMBR Document_Number,
       RH.CHEKNMBR Check_Number,
       RH.BACHNUMB Batch_ID,
       RH.BCHSOURC Batch_Source,
       RH.TRXSORCE Trx_Source,
       CASE RH.CSHRCTYP
         WHEN 0 THEN 'Check'
         WHEN 1 THEN 'Cash'
         WHEN 2 THEN 'Credit Card'
         ELSE ''
         END Cash_Receipt_Type,
       RH.DUEDATE Due_Date,
       RH.DOCDATE Document_Date,
       RH.POSTDATE Posted_Date,
       RH.PSTUSRID Post_User_ID,
       RH.GLPOSTDT GL_Posting_Date,
       RH.LSTEDTDT Last_Edit_Date,
       RH.LSTUSRED Last_User_To_Edit,
       RH.ORTRXAMT Original_Trx_Amount,
       RH.CURTRXAM Current_Trx_Amount,
       RH.SLSAMNT Sales_Amount,
       RH.COSTAMNT Cost_Amount,
       RH.FRTAMNT Freight_Amount,
       RH.MISCAMNT Misc_Amount,
       RH.TAXAMNT Tax_Amount,
       RH.COMDLRAM Commission_Amount,
       RH.CASHAMNT Cash_Amount,
       RH.DISTKNAM Discount_Taken_Amount,
       RH.DISAVAMT Discount_Avail_Amount,
       RH.DISCRTND Discount_Returned,
       RH.DISCDATE Discount_Date,
       RH.DSCDLRAM Discount_Dollar_Amount,
       RH.DSCPCTAM Discount_Percent_Amount,
       RH.WROFAMNT Write_Off_Amount,
       RH.TRXDSCRN Trx_Description,
       RH.CSPORNBR Customer_PO,
       RH.SLPRSNID Salesperson_ID,
       RH.SLSTERCD Sales_Territory,
       RH.DINVPDOF Date_Inv_Paid_Off,
       RH.PPSAMDED PPS_Amount_Deducted,
       RH.GSTDSAMT GST_Discount_Amount,
       CASE RH.VOIDSTTS
         WHEN 0 THEN 'Not Voided'
         WHEN 1 THEN 'Voided'
         WHEN 2 THEN 'NSF check'
         WHEN 3 THEN 'Waived finance charge'
         ELSE ''
         END Void_Status,
       RH.VOIDDATE Void_Date,
       RH.TAXSCHID Tax_Schedule_ID,
       RH.CURNCYID Currency_ID,
       RH.PYMTRMID Payment_Terms_ID,
       RH.SHIPMTHD Shipping_Method,
       RH.TRDISAMT Trade_Discount_Amount,
       RH.NOTEINDX Note_Index,
       RH.Tax_Date Tax_Date,
       coalesce(G.ACTNUMST,'') Account_Number,
       CASE DH.DISTTYPE
         WHEN 1 THEN 'Cash'
         WHEN 2 THEN 'Terms Taken'
         WHEN 3 THEN 'Accounts Receivable'
         WHEN 4 THEN 'Writeoffs'
         WHEN 5 THEN 'Terms Available'
         WHEN 6 THEN 'GST'
         WHEN 7 THEN 'PPS'
         WHEN 8 THEN 'Other'
         WHEN 9 THEN 'Sales'
         WHEN 10 THEN 'Trade'
         WHEN 11 THEN 'Frieght'
         WHEN 12 THEN 'Miscellaneous'
         WHEN 13 THEN 'Taxes'
         WHEN 14 THEN 'COGS'
         WHEN 15 THEN 'Inventory'
         WHEN 16 THEN 'Finance Charges'
         WHEN 17 THEN 'Returns'
         WHEN 18 THEN 'Debit Memo'
         WHEN 19 THEN 'Credit Memo'
         WHEN 20 THEN 'Service'
         WHEN 21 THEN 'Warranty Expense'
         WHEN 22 THEN 'Warranty Sales'
         WHEN 23 THEN 'Commissions Expense'
         WHEN 24 THEN 'Commissions Payable'
         WHEN 25 THEN 'Unit Account'
         WHEN 26 THEN 'Rounding'
         WHEN 27 THEN 'Realized Gain'
         WHEN 28 THEN 'Realized Loss'
         WHEN 29 THEN 'Unrealized Gain'
         WHEN 30 THEN 'Unrealized Loss'
         ELSE ''
         END 'Distribution_Type',
       coalesce(DH.DEBITAMT,0) Debit_Amount,
       coalesce(DH.CRDTAMNT,0) Credit_Amount,
       coalesce(DH.DistRef,'') Distribution_Reference

FROM RM30101 RH
    LEFT OUTER JOIN
       RM30301 DH
       ON RH.RMDTYPAL = DH.RMDTYPAL
       AND RH.DOCNUMBR = DH.DOCNUMBR
    LEFT OUTER JOIN
       GL00105 G
       ON DH.DSTINDX = G.ACTINDX

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_RM_Trx_Distributions TO DYNGRP

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.