Updates to SQL view to show all GL distributions for AP transactions

June 25, 2010 by Victoria Yudin 8 Comments

I have made a number of updates to my SQL view to show all GL distributions for AP transactions since I first published it. Some of these were in response to comments asking for additional fields, others were added when I came across new data to test with. Rather than publish another post with the latest revisions, I have updated the code in my original blog post, but I wanted to let everyone know it’s there. Some of the updates are:

  • Added Due To and Due From distribution types for anyone using Intercompany transactions.
  • Added Realized Gain and Realized Loss distribution types for Multicurrency transactions.
  • Added Currency ID, Exchange Rate and Originating Debit/Credit fields.
  • Added Distribution Reference, Batch ID and Transaction Description fields.
  • Added Voucher Number.

As always, if you find any issues or would like any additional updates, please let me know.

There are also enough Payables SQL scripts that I have moved them to their own page. The page can also be accessed from the main navigation menu on this blog under GP Reports and on my GP Reports page.

Filed under Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts, Payables 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 posted GL transactions in Dynamics GP

August 11, 2009 by Victoria Yudin 17 Comments

Below is a SQL script to create a view showing all posted General Ledger transactions in Dynamics GP. This has the common columns asked for on reports, but you can certainly add your own as needed. For additional SQL code, please visit my GP Reports page. A few notes on this view:

  • Year-end close transactions are excluded
  • Only functional amounts are brought in
  • Unposted transactions will not be shown

~~~~~

CREATE VIEW view_Posted_GL_Trx
AS

/*******************************************************************
view_Posted_GL_Trx
Created Aug 11, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
- Returns all lines for posted GL transactions
- Excludes year-end closing entries
- Returns Functional amounts only
GL20000 - Open Year Trx
GL30000 - Historical Trx
GL00100 - Account Master
GL00105 - Account Index Master
*******************************************************************/

SELECT	YEAR1 Trx_Year,
	TRXDATE Trx_Date,
	JRNENTRY Journal_Entry,
	ORTRXSRC Originating_TRX_Source,
	REFRENCE Reference,
	ORMSTRID Originating_Master_ID,
	ORMSTRNM Originating_Master_Name,
	ORDOCNUM Originating_Doc_Number,
	DEBITAMT Debit_Amount,
	CRDTAMNT Credit_Amount,
	ACTNUMST Account_Number,
	ACTDESCR Account_Description,
	CURNCYID Currency_ID
FROM
(SELECT ACTINDX, OPENYEAR YEAR1, TRXDATE,
	JRNENTRY, ORTRXSRC, REFRENCE,
	ORDOCNUM, ORMSTRID, ORMSTRNM,
	DEBITAMT, CRDTAMNT, CURNCYID
 FROM	GL20000
 WHERE	SOURCDOC not in ('BBF','P/L')
 UNION ALL
 SELECT ACTINDX, HSTYEAR YEAR1, TRXDATE,
	JRNENTRY, ORTRXSRC, REFRENCE,
	ORDOCNUM, ORMSTRID, ORMSTRNM,
	DEBITAMT, CRDTAMNT,CURNCYID
 FROM	GL30000
 WHERE   SOURCDOC not in ('BBF','P/L')) GL
INNER JOIN GL00105 GM
     ON GL.ACTINDX = GM.ACTINDX
INNER JOIN GL00100 GA
     ON GL.ACTINDX = GA.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_Posted_GL_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, GL SQL code, GP Reports code, GP SQL scripts Tagged with , , ,

Dynamics GP Trial Balance in Excel using FRx

July 17, 2009 by Victoria Yudin 17 Comments

While I work with SQL and Crystal Reports every day, there are some reports that are just easier created elsewhere. One example is a GL Trial Balance that you want to export to Excel. There are many ways of handling this requirement, I like using FRx for it.  Below are steps by step instructions on how to set this up, from start to finish it should take no more than 10 minutes:

  • Create a new Row format with one line encompassing all your accounts:FRx TB row

If you have any unit accounts, make sure to exclude them unless you want to see them on the Trial Balance.

  • Create a Column format to show the columns you want, here is an example showing the beginning balance, net change and ending balance:FRx TB column

You can do a lot of different things here, for example, I sometimes use a version that will show the net change for each month individually.

  • Create a new Catalog and change the following default settings:
    • Set Detail level to Financial & Account
    • On the Report Options tab check:
      • Display rows with no amounts
      • Display reports with no active rows
  • Generate the report, you will get something like the following in the DrillDown Viewer: FRx TB display
  • Now to get this into Excel:
    • Go to File > Export > Worksheet File > Formatted Excel
    • Select a file name and destination
    • On the Export Selection window uncheck Financial report and check Detail (supporting) report
    • Click OK and FRx will automatically open Excel with your detailed Trial Balance:

    Excel TB

  • You can also set this up to print to Excel automatically (skipping the steps above to have to Export from the DrillDown Viewer). To do this:
    • Go to the Output tab, then the Output Options tab
    • Change the drop-down option to Formatted Excel and pick a destination and the other settings as desired

 

Update on August 27, 2009:

I have been asked a few times recently how to show debits and credits in separate columns on a Trial Balance in FRx. To accomplish that, change the column layout I suggested above to look like the following:

FRx DR CR

Filed under Dynamics GP, FRx, GP 10.0, GP 2010, GP 8.0, GP 9.0 Tagged with , ,

← Older posts