cancel
Showing results for 
Search instead for 
Did you mean: 

Profit and Loss Statement Query with Credit Memo

Former Member
0 Kudos

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]

Accepted Solutions (1)

Accepted Solutions (1)

JesperB1
Advisor
Advisor
0 Kudos

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

Former Member
0 Kudos

Thanks Jesper for quick response but i'm new to SAP B1 and how can i put it in to Query?

Also, Actually i'm trying to automate report using scheduler, Do you know any other product to schedule system reports in B1?

Ash

Answers (0)