on 02-02-2016 9:36 PM
The Query for Profit and Loss Statemen works but I'm missing Credit Memo
SELECT T0.[FormatCode], T0.[AcctName], SUM(T1.[Credit]-T1.[Debit]) Balance
FROM OACT T0
INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
INNER JOIN OJDT T2 ON T1.TransId = T2.TransId
WHERE T0.[ActType] in ('I','E') and DateDiff(YY,T2.[RefDate],GetDate()) = 0 AND T1.[Debit] != T1.[Credit]
GROUP BY T0.[FormatCode], T0.[AcctName]
Having SUM(T1.[Debit]-T1.[Credit]) != 0
ORDER BY T0.[FormatCode]
Hello Ashwin,
A bit unsure about what you mean by credit notes. However, some other tips that I hope will help.
Personally I prefer using the OACT.groupmask to indicate which accounts to take. The groupmask number indicates the drawer of the account. 1 = Assets, 2 Liabilities etc. If your balance sheet has three drawers then use:
where groupmask > 3 (that is to get all the P&L accounts)
Further, to avoid that NULL values in debit or credit column messes up the calculation you can use:
sum(debit) - sum(credit)
Using the acttype I would say is risky as a user easily can change this to type 'Other'.
I hope that is useful for you.
Jesper
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.