SQL view with AR apply detail

February 15, 2010 by Victoria Yudin 7 Comments

This has been a hot topic in the newsgroups lately and several people have asked me if I have any code for Receivables (AR) apply information in Dynamics GP. Below is a view that should help you get started if you’re building a report to show AR apply information in Dynamics GP.

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_AR_Apply_Detail
AS

/*******************************************************************
view_AR_Apply_Detail
Created on Feb 15, 2010 by Victoria Yudin - Flexible Solutions, Inc.
For updates visit http://victoriayudin.com/gp-reports/
Returns apply detail for all posted receivables transactions.
Only shows functional currency amounts.
Credit documents applied to more than one debit document
	will return multiple lines.
Tables used:
RM00101 – Customer Master
RM20101 - Open Transactions
RM20201 – Open Transactions Apply
RM30101 – Historical Transactions
RM30201 – Historical Transactions Apply
Updated on Aug 12, 2010 to add original total of Applied to Doc and
     Applied To Doc Paid Off date.
*******************************************************************/

SELECT   T.CUSTNMBR Customer_ID,
	CM.CUSTNAME Customer_Name,
	T.DOCDATE Document_Date,
	T.GLPOSTDT GL_Posting_Date,
	CASE T.RMDTYPAL
	     WHEN 7 THEN 'Credit Memo'
              WHEN 8 THEN 'Return'
              WHEN 9 THEN 'Payment'
              END AS RM_Doc_Type,
	T.docTypeNum Document_Type_and_Number,
	T.DOCNUMBR Document_Number,
	T.ORTRXAMT Original_Trx_Amount,
	T.CURTRXAM Current_Trx_Amount,
	T.amountApplied Total_Applied_Amount,
	A.APPTOAMT Amount_Applied,
	A.APTODCTY Applied_to_Doc_Type,
	A.debitType Applied_to_Doc_Type_Name,
	A.APTODCNM  Applied_to_Doc_Number,
	A.APTODCDT Applied_to_Document_Date,
	A.ApplyToGLPostDate Applied_to_GL_Posting_Date,
	A.DISTKNAM Discount,
	A.WROFAMNT Writeoff,
	A.DATE1 Apply_Document_Date,
	A.GLPOSTDT Apply_GL_Posting_Date,
	D.ORTRXAMT Applied_To_Doc_Total,
	D.DINVPDOF Applied_To_Date_Paid_Off

FROM (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
	CASE RMDTYPAL
	  WHEN 7 THEN 'Credit Memo'
	  WHEN 8 THEN 'Return'
	  WHEN 9 THEN
	    CASE CSHRCTYP
	      WHEN 0 THEN 'Payment - Check ' +
   	        CASE CHEKNMBR
	          WHEN '' THEN ''
		 ELSE '#' + CHEKNMBR
		 END
	      WHEN 1 THEN 'Payment - Cash'
	      WHEN 2 THEN 'Payment - Credit Card'
	      END
	  END AS docTypeNum,
	DOCNUMBR, ORTRXAMT, CURTRXAM,
	ORTRXAMT - CURTRXAM amountApplied 

	FROM RM20101
	WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0) 

	UNION 

	SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
	CASE RMDTYPAL
	  WHEN 7 THEN 'Credit Memo'
	  WHEN 8 THEN 'Return'
	  WHEN 9 THEN
	    CASE CSHRCTYP
	      WHEN 0 THEN 'Payment - Check ' +
	        CASE CHEKNMBR
		 WHEN '' THEN ''
		 ELSE '#' + CHEKNMBR
		 END
	        WHEN 1 THEN 'Payment - Cash'
	        WHEN 2 THEN 'Payment - Credit Card'
	        END
	  END AS docTypeNum,
	DOCNUMBR, ORTRXAMT, CURTRXAM,
	ORTRXAMT - CURTRXAM amountApplied 

	FROM RM30101
	WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0)) T 

INNER JOIN RM00101 CM
	ON T.CUSTNMBR = CM.CUSTNMBR 

INNER JOIN
	(SELECT tO1.CUSTNMBR, APTODCTY, APTODCNM,
	APFRDCTY,APFRDCNM,
	CASE APTODCTY
	  WHEN 1 THEN 'Sale / Invoice'
	  WHEN 2 THEN 'Scheduled Payment'
	  WHEN 3 THEN 'Debit Memo'
	  WHEN 4 THEN 'Finance Charge'
	  WHEN 5 THEN 'Service Repair'
	  WHEN 6 THEN 'Warranty'
	  END as debitType,
	APPTOAMT, ApplyToGLPostDate, APTODCDT, tO2.DISTKNAM,
	tO2.WROFAMNT, tO2.DATE1, tO2.GLPOSTDT 

	FROM RM20201 tO2 

	INNER JOIN RM20101 tO1
	ON tO2.APTODCTY = tO1.RMDTYPAL
           AND tO2.APTODCNM = tO1.DOCNUMBR 

	UNION 

	SELECT tH1.CUSTNMBR, APTODCTY, APTODCNM,
	APFRDCTY, APFRDCNM,
	CASE APTODCTY
	  WHEN 1 THEN 'Sale / Invoice'
	  WHEN 2 THEN 'Scheduled Payment'
	  WHEN 3 THEN 'Debit Memo'
	  WHEN 4 THEN 'Finance Charge'
	  WHEN 5 THEN 'Service Repair'
	  WHEN 6 THEN 'Warranty'
	  END AS debitType,
	APPTOAMT, ApplyToGLPostDate, APTODCDT, tH2.DISTKNAM,
         tH2.WROFAMNT, tH2.DATE1, tH2.GLPOSTDT
	FROM RM30201 tH2 

	INNER JOIN RM30101 tH1
	ON tH2.APTODCTY = tH1.RMDTYPAL
	  AND tH2.APTODCNM = tH1.DOCNUMBR) A 

ON A.APFRDCTY = T.RMDTYPAL and A.APFRDCNM = T.DOCNUMBR

INNER JOIN
	(SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF
	 FROM RM20101

	UNION 

	 SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF
	 FROM RM30101) D

ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.DOCNUMBR

/** 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_AR_Apply_Detail 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.

Filed under Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code Tagged with , , ,

SQL view with all GL distributions for AR transactions

February 10, 2010 by Victoria Yudin 13 Comments

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.

Filed under Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts, Receivables SQL code Tagged with , , , ,

What’s with all these dates?

September 8, 2009 by Victoria Yudin 6 Comments

There are many different dates stored in Dynamics GP. This is typically a good thing, my philosophy is that it is always better to have more data than you need and not the other way around. However, it can get confusing rather quickly since a lot of the labels for the dates sound very similar. I will attempt to shed some light on the more common dates in a ‘generic’ way, so that it can be applied to just about any module in GP, although the primary modules I am looking at are: Payables, Receivables, Sales Order Processing (SOP), Bank Reconciliation, Purchase Order Processing (POP) and General Ledger. 

  • Document Date or Date: the subledger date. For most companies this is the actual date on the invoice that they receive from a vendor or send to a customer. In the Bank Reconciliation module this date is called Transaction Date or TRX Date.
  • Posting Date or GL Posting Date: the date the transaction will post to the General Ledger and thus the date that determines what period the transaction will show up on your financial statements. This becomes the Transaction Date in the General Ledger.
  • General Ledger Transaction Date: the date that determines what period the transaction will show up on your financial statements. This is the same as the Posting Date or GL Posting Date that is found in the subledgers.
  • Posted Date: the date the transaction was actually posted. This will be the system date, not the ‘GP User Date’ at the time of the posting.
  • Apply Date: the subledger apply date. Will be used when running aging reports using the Document Date, will be the subledger date for any transactions created during the apply process and will be used for the Date Invoice Paid Off (see below).
  • Apply Posting Date: the General Ledger apply date. Will be used when running aging reports using the GL Posting Date, will be the General Ledger Transaction Date for any transactions created during the apply process.
  • Date Invoice Paid Off: the date an invoice was fully applied (using the Apply Date).

 

Filed under Dynamics GP Tagged with , , , , , ,

SQL view for all unapplied Receivables transactions in Dynamics GP

September 5, 2009 by Victoria Yudin 2 Comments

Here is a SQL view that will return all unapplied Receivables transactions in Dynamics GP. This will calculate how many days overdue unapplied transaction are. If something is not overdue, or if it is a credit transaction (payment, credit or return) the Days_Past_Due will be zero. For other SQL code, please visit my GP Reports page.

~~~~~

CREATE VIEW view_Unapplied_AR_Trx
AS

/****************************************************************
view_Unapplied_AR_Trx
Created Sep 05, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Shows all unapplied Receivables transactions
     in Functional Currency only
Tables used:
     CM - RM00101 - Customer Master
     CS - RM00103 – Customer Master Summary
     RM - RM20101 - Open Transactions
****************************************************************/

SELECT CM.CUSTNMBR Customer_ID,
       CM.CUSTNAME Customer_Name,
       CM.PYMTRMID Customer_Terms,
       CM.CUSTCLAS Customer_Class,
       CM.PRCLEVEL Price_Level,
       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 Document_Type,
       RM.DOCNUMBR Document_Number,
       RM.DOCDATE Document_Date,
       RM.DUEDATE Due_Date,
       RM.ORTRXAMT Document_Amount,
       RM.CURTRXAM Unapplied_Amount,
       CASE
          WHEN RM.DUEDATE >= GETDATE() THEN 0
          WHEN RM.RMDTYPAL in (7,8,9) THEN 0
          ELSE DATEDIFF(DD, RM.DUEDATE, GETDATE())
          END Days_Past_Due,
       CS.LASTPYDT Last_Payment_Date,
       CS.LPYMTAMT Last_Payment_Amount

FROM   RM20101 RM
     INNER JOIN
       RM00101 CM
       ON RM.CUSTNMBR = CM.CUSTNMBR
     INNER JOIN
       RM00103 CS
       ON RM.CUSTNMBR = CS.CUSTNMBR
WHERE  RM.VOIDSTTS = 0 AND RM.CURTRXAM <> 0

/** 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_Unapplied_AR_Trx 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

Filed under Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code Tagged with , , ,

SQL view with all posted Receivables transactions

April 24, 2009 by Victoria Yudin 10 Comments

Below is code to create a SQL view that returns all the posted Receivables transactions in Dynamics GP. I have had an abbreviated version of this view on my GP Reports page for a while, but I cleaned it up a bit and gave it some more friendly column names. I also added values for the commonly used fields such as Void Status and Document Type. There may be a lot more fields than are needed for most reports in here, but it is difficult to know who is using what fields. If you have a large number of records in your database you can probably improve performance by taking out the fields you are not using.

This view will work with either SmartList Builder or Crystal Reports, however for SmartList Builder you may want to add spaces in the column names to make it more user-friendly.

~~~~~

CREATE VIEW view_RM_Transactions
AS
/*******************************************************************
view_RM_Transactions
Created on Apr. 24 2008 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Contains all posted (open and history) Receivables transactions.
Returns all fields that are the same from RM20101 and RM30101
	tables with friendlier column names.
*******************************************************************/

SELECT 	CUSTNMBR CustomerID,CPRCSTNM ParentCustomer,
	RMDTYPAL DocTypeNumber,
	CASE 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 DocType,
	DOCNUMBR DocumentNumber, CHEKNMBR CheckNumber,
	BACHNUMB BatchID, BCHSOURC BatchSource,
	TRXSORCE TrxSource,
	CASE CSHRCTYP
		WHEN 0 THEN 'Check'
		WHEN 1 THEN 'Cash'
		WHEN 2 THEN 'Credit Card'
		ELSE ''
		END CashReceiptType,
	DUEDATE DueDate, DOCDATE DocumentDate,
	POSTDATE PostedDate, PSTUSRID PostUserID,
	GLPOSTDT GLPostingDate, LSTEDTDT LastEditDate,
	LSTUSRED LastUserToEdit,ORTRXAMT OriginalTrxAmount,
	CURTRXAM CurrentTrxAmount, SLSAMNT SalesAmount,
	COSTAMNT CostAmount, FRTAMNT FreightAmount,
	MISCAMNT MiscAmount, TAXAMNT TaxAmount,
	COMDLRAM CommissionAmount, CASHAMNT CashAmount,
	DISTKNAM DiscountTakenAmount,
	DISAVAMT DiscountAvailAmount,
	DISCRTND DiscountReturned, DISCDATE DiscountDate,
	DSCDLRAM DiscountDollarAmount,
	DSCPCTAM DiscountPercentAmount,
	WROFAMNT WriteOffAmount, TRXDSCRN TrxDescription,
	CSPORNBR CustomerPO, SLPRSNID SalespersonID,
	SLSTERCD SalesTerritory, DINVPDOF DateInvPaidOff,
	PPSAMDED PPSAmountDeducted,
	GSTDSAMT GSTDiscountAmount, DELETE1 [Delete],
	CASE VOIDSTTS
		WHEN 0 THEN 'Not Voided'
		WHEN 1 THEN 'Voided'
		WHEN 2 THEN 'NSF check'
		WHEN 3 THEN 'Waived finance charge'
		ELSE ''
		END VoidStatus,
	VOIDDATE VoidDate, TAXSCHID TaxScheduleID,
	CURNCYID CurrencyID, PYMTRMID PaymentTermsID,
	SHIPMTHD ShippingMethod, TRDISAMT TradeDiscountAmount,
	SLSCHDID SalesScheduleID, FRTSCHID FreightScheduleID,
	MSCSCHID MiscScheduleID, NOTEINDX NoteIndex,
	Tax_Date TaxDate, APLYWITH ApplyWithholding,
	SALEDATE SaleDate, CORRCTN Correction,
	SIMPLIFD Simplified, Electronic, ECTRX ECTransaction,
	BKTSLSAM BackoutSalesAmount,
	BKTFRTAM BackoutFreightAmount,
	BKTMSCAM BackoutMiscAmount,
	BackoutTradeDisc BackoutTradeDiscAmount,
	Factoring, DIRECTDEBIT DirectDebit

FROM 	RM20101

UNION ALL

SELECT 	CUSTNMBR CustomerID,CPRCSTNM ParentCustomer,
	RMDTYPAL DocTypeNumber,
	CASE 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 DocType,
	DOCNUMBR DocumentNumber, CHEKNMBR CheckNumber,
	BACHNUMB BatchID, BCHSOURC BatchSource,
	TRXSORCE TrxSource,
	CASE CSHRCTYP
		WHEN 0 THEN 'Check'
		WHEN 1 THEN 'Cash'
		WHEN 2 THEN 'Credit Card'
		ELSE ''
		END CashReceiptType,
	DUEDATE DueDate, DOCDATE DocumentDate,
	POSTDATE PostedDate, PSTUSRID PostUserID,
	GLPOSTDT GLPostingDate, LSTEDTDT LastEditDate,
	LSTUSRED LastUserToEdit,ORTRXAMT OriginalTrxAmount,
	CURTRXAM CurrentTrxAmount, SLSAMNT SalesAmount,
	COSTAMNT CostAmount, FRTAMNT FreightAmount,
	MISCAMNT MiscAmount, TAXAMNT TaxAmount,
	COMDLRAM CommissionAmount, CASHAMNT CashAmount,
	DISTKNAM DiscountTakenAmount,
	DISAVAMT DiscountAvailAmount,
	DISCRTND DiscountReturned, DISCDATE DiscountDate,
	DSCDLRAM DiscountDollarAmount,
	DSCPCTAM DiscountPercentAmount,
	WROFAMNT WriteOffAmount, TRXDSCRN TrxDescription,
	CSPORNBR CustomerPO, SLPRSNID SalespersonID,
	SLSTERCD SalesTerritory, DINVPDOF DateInvPaidOff,
	PPSAMDED PPSAmountDeducted,
	GSTDSAMT GSTDiscountAmount, DELETE1 [Delete],
	CASE VOIDSTTS
		WHEN 0 THEN 'Not Voided'
		WHEN 1 THEN 'Voided'
		WHEN 2 THEN 'NSF check'
		WHEN 3 THEN 'Waived finance charge'
		ELSE ''
		END VoidStatus,
	VOIDDATE VoidDate, TAXSCHID TaxScheduleID,
	CURNCYID CurrencyID, PYMTRMID PaymentTermsID,
	SHIPMTHD ShippingMethod, TRDISAMT TradeDiscountAmount,
	SLSCHDID SalesScheduleID, FRTSCHID FreightScheduleID,
	MSCSCHID MiscScheduleID, NOTEINDX NoteIndex,
	Tax_Date TaxDate, APLYWITH ApplyWithholding,
	SALEDATE SaleDate, CORRCTN Correction,
	SIMPLIFD Simplified, Electronic, ECTRX ECTransaction,
	BKTSLSAM BackoutSalesAmount,
	BKTFRTAM BackoutFreightAmount,
	BKTMSCAM BackoutMiscAmount,
	BackoutTradeDisc BackoutTradeDiscAmount,
	Factoring, DIRECTDEBIT DirectDebit

FROM 	RM30101

/** 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_Transactions 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.

Filed under Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code Tagged with , , ,

← Older posts