SQL query for viewing Saved Quick Journal Entries in Dynamics GP

The below query used to retrieve the saved Quick Journal Entries in Dynamics GP.

SELECT * FROM (
SELECT
QH.JRNENTRY ‘Journal Entry’,
‘Finacial’ ‘Series’ ,
QH.TRXDATE ‘Transaction Date’,
AI.ACTNUMST ‘Account Number’,
AM.ACTDESCR ‘Account Description’,
QH.TRXBALNC ‘Credit Amount’,
0.000000 ‘Debit Amount’
FROM GL10100 QH
–RIGHT JOIN GL10101 QL ON QH.JRNENTRY = QL.JRNENTRY
LEFT OUTER JOIN GL00105 AI ON QH.OFFINDX = AI.ACTINDX
LEFT OUTER JOIN GL00100 AM ON QH.OFFINDX = AM.ACTINDX
WHERE QH.PSTGSTUS =1
UNION ALL
SELECT
QL.JRNENTRY ‘Journal Entry’,
‘Finacial’ ‘Series’ ,
QHH.TRXDATE ‘Transaction Date’,
AI.ACTNUMST ‘Account Number’,
AM.ACTDESCR ‘Account Description’,
CASE WHEN QL.TRXAMNT < 0.00000 THEN
QL.TRXAMNT
ELSE 0 END ‘Credit Amount’,
CASE WHEN QL.TRXAMNT > 0.00000 THEN
QL.TRXAMNT
ELSE 0 END ‘Debit Amount’
FROM GL10101 QL
LEFT OUTER JOIN GL10100 QHH ON QHH.JRNENTRY = QL.JRNENTRY
LEFT OUTER JOIN GL00105 AI ON QL.ACTINDX = AI.ACTINDX
LEFT OUTER JOIN GL00100 AM ON QL.ACTINDX = AM.ACTINDX
WHERE QL.TRXAMNT <> 0.0000
) QUICKJOURNAL ORDER BY [Journal Entry]

Hope this helps!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s