cancel
Showing results for 
Search instead for 
Did you mean: 

BP Balance query?

Former Member
0 Kudos

Hi experts,

I need a query that will help me design a report for a customer.

The query should show the journal entry transactions for business partners, that is their debit and credit transactions.

Also the query should help me to design the report and group by the business partners and secondly group by the account name as well.

This query should help me to be able to choose a business partner and the report will show all the business partner's transactions and show all it's

Debit and credit per the account name.

NB: the query I want to see the bp name,the bp code,the account name,the account code, the transition id,debit,credit,remarks,ref1,ref2,ref3.

Please assist me experts.

Urgent help is needed.

Regards,

Justice

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi JD,

Try this ..

select t0.ShortName,t2.CardName ,t0.Account,t1.AcctName  ,t0.TransId ,t0.RefDate ,t0.Credit ,t0.Debit ,t0.Ref1 ,t0.Ref2 from jdt1 t0 left outer join OACT t1 on t0.Account=t1.AcctCode left outer join OCRD t2 on t0.ShortName=t2.CardCode  where t0.ShortName in (select cardcode from OCRD )

-Rajesh NS

Answers (1)

Answers (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query:

SELECT T3.[CardCode], T3.[CardName], T0.[Debit], T0.[Credit], T0.[Account], T2.[FormatCode], T0.[TransId], T0.[Debit], T0.[Credit], T1.[Memo], T0.[Ref1], T0.[Ref2] FROM JDT1 T0  INNER JOIN OJDT T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T0.Account = T2.AcctCode INNER JOIN OCRD T3 ON  T0.[ShortName]  = t3.cardcode WHERE T0.[ShortName] = [%0]

Thanks & Regards,

Nagarajan