| as_radwan | 23-May-2015 03:50 PM |
SQL view for all GL transactions in Dynamics GP
كود:
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
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
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
BY VICTORIA YUDIN COMMENTS OFF http://victoriayudin.com/2011/04/27/...n-dynamics-gp/ |
