كود:
I have been asked a few times now to add unposted General Ledger transactions to my Posted GL Transactions view. Here it is. This view also excludes voided transactions and introduces new columns for transaction status and source doc.
To see more information about GL tables in Dynamics GP, visit my GL Tables page. For more Dynamics GP SQL code, take a look at my GP Reports page.
~~~~~
CREATE VIEW view_GL_Trx
AS
/******************************************************************
view_GL_Trx
Created Apr 27, 2011 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
- Returns all lines for all GL transactions
- Excludes year-end closing entries
- Excludes voided transactions
- Returns Functional amounts only
Tables used: GL10000
- Work Trx header GL10001
- Work Trx detail GL20000
- Open Year Trx GL30000
- Historical Trx GL00100
- Account Master GL00105
- Account Index Master
******************************************************************/
SELECT
Trx_Status,
TRXDATE Trx_Date,
JRNENTRY Journal_Entry,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
REFRENCE Reference,
SOURCDOC Source_Document,
ORTRXSRC Originating_TRX_Source,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
CURNCYID Currency_ID
FROM
(SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
Trx_Status = 'Open'
FROM GL20000
WHERE SOURCDOC not in ('BBF','P/L')
AND VOIDED = 0
UNION ALL
SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
Trx_Status = 'History'
FROM GL30000
WHERE SOURCDOC not in ('BBF','P/L')
AND VOIDED = 0
UNION ALL
SELECT GD.ACTINDX, GH.TRXDATE, GH.SOURCDOC, GH.JRNENTRY, GH.ORTRXSRC,
GH.REFRENCE, GD.ORDOCNUM, GD.ORMSTRID, GD.ORMSTRNM, GD.DEBITAMT,
GD.CRDTAMNT, GH.CURNCYID, Trx_Status = 'Work'
FROM GL10000 GH
INNER JOIN GL10001 GD
ON GH.JRNENTRY = GD.JRNENTRY
WHERE VOIDED = 0) 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_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