You’re offline. This is a read only version of the page.
395
It is possible to generate en GP a Report including Debit & Credit Transactions for a specific Account?. I need a Report of all transactions for the account 200-19005-10 (doesn't matter if its in Dr of Cr) and the other accounts in that transactions.
Example:
| Account | Debit | Credit | |
| Trx_#1 | 200-19005-10 | 50.00 | |
| Trx_#1 | 430-19005-10 | 45.00 | |
| Trx_#1 | 550-34000-10 | 5.00 | |
| Trx_#2 | 350-44100-10 | 20.90 | |
| Trx_#2 | 200-19005-10 | 20.90 |
*This post is locked for comments
i run this SQL Script for the accountants regularly. You'll just need to change the ACTNUMBR_1 values to something more meaningful to you. It includes all the lines of the JE where the JE includes the account(s) selected. -Trevor
SELECT A.JRNENTRY [JE]
, CASE A.SERIES
WHEN 1 THEN 'All'
WHEN 2 THEN 'Financial'
WHEN 3 THEN 'Sales'
WHEN 4 THEN 'Purchasing'
WHEN 5 THEN 'Inventory'
WHEN 6 THEN 'Payroll - USA'
WHEN 7 THEN 'Project'
WHEN 10 THEN '3rd Party'
ELSE '#Error'
END [Series]
, RTRIM(A.SOURCDOC) [Source Doc]
, A.TRXDATE [Trx Date]
, RTRIM(C.ACTNUMST) [Account]
, RTRIM(B.ACTDESCR) [Acct Descr]
, A.DEBITAMT [DR]
, A.CRDTAMNT [CR]
, RTRIM(A.ORMSTRID) [Orig Master ID]
, RTRIM(A.ORMSTRNM) [Orig Master Name]
, RTRIM(A.ORDOCNUM) [Originating Doc Num]
, RTRIM(A.ORCTRNUM) [Originating Control Num]
, A.ORPSTDDT [Origin Post Date]
, RTRIM(A.REFRENCE) [Reference]
, RTRIM(A.DSCRIPTN) [Dist Ref]
, RTRIM(A.SOURCDOC) [Source Doc]
FROM GL20000 A
JOIN GL00100 B ON A.ACTINDX = B.ACTINDX
JOIN GL00105 C ON A.ACTINDX = C.ACTINDX
WHERE JRNENTRY IN
(SELECT DISTINCT JRNENTRY FROM GL20000 WHERE ACTINDX IN
(SELECT DISTINCT ACTINDX FROM GL00100 WHERE ACTNUMBR_1 in ('19023', '19024', '39110', '87005')))
ORDER BY JRNENTRY
You could also run an Account Transactions SmartList and filter for the desired account.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (