Query Report for Account balance in Business partner Master Data

[Former Member]

Hi Experts,

One of my client requirement is one query report for account balance of business partner master data . Where I need that query to design a crystal report.

I need your help for the below query , where I need some fields that is BP CODE , BP NAME, BP ADDRESS (block, city, country, mobile no, zip code , landline number) , SERIES POSTING DATE, ORIGIN , ORIGIN NO, DETAILS, DEBIT,CREDIT,CUMULATIVE BALANCE, FROM DATE AND TO DATE.

Actually I am unable to find the field names for ORIGIN, CUMULATIVE BALANCE, FROM DATE AND TO DATE.

Kindly help me for write the query for account balance.

EXAMPLE :

SELECT T0.[CardName], T0.[CardType], T1.[Address], T1.[Street], T1.[CardCode], T1.[Block], T1.[ZipCode], T1.[City], T1.[County], T2.[RefDate], T3.[Debit], T3.[Credit], T3.[LineMemo] FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode], OJDT T2 INNER JOIN JDT1 T3 ON T2.[TransId] = T3.[TransId] WHERE T0.[CardCode] ='c20000'

Thanks and Regards,

Nagendra Prasad.

[Former Member]

Hi Nagendra,

ORIGIN is referring to ObjectType. You may find a complete list by searching through this forum.

CUMULATIVE BALANCE is a calculated field. It may only be calculated within run time.

FROM DATE AND TO DATE are 2 parameter fields you need to setup to allow user input.

Thanks,

Gordon

[Former Member]

Hi Nagendra,

Try this one. This is basically BP ledger.

select  a.taxdate,

b.Credit as credit,

b.Debit AS debit,

b.ShortName,

a.transtype,

a.baseref,

a.transid,

d.SeriesName,

(Select top 1 OINV.NumAtCard from OINV Where OINV.ObjType = b.TransType and OINV.DocEntry = b.CreatedBy )as custref,

(Select top 1 OINV.NumAtCard from OINV Where OINV.ObjType = b.TransType and OINV.DocEntry = b.CreatedBy ) as hoinvcustref,

(Select top 1 OPCH.NumAtCard from OPCH Where OPCH.ObjType = b.TransType and OPCH.DocEntry = b.CreatedBy ) as vendref,

(Select top 1 OPCH.NumAtCard from OPCH Where OPCH.ObjType = b.TransType and OPCH.DocEntry = b.CreatedBy ) as iopchvendref,

(Select top 1 OINV.Docdate from OINV Where OINV.ObjType = b.TransType and OINV.DocEntry = b.CreatedBy ) as custdocdate,

(Select top 1 OINV.Docdate from OINV Where OINV.ObjType = b.TransType and OINV.DocEntry = b.CreatedBy ) as hoinvcustdocdate,

(Select top 1 OPCH.Docdate from OPCH Where OPCH.ObjType = b.TransType and OPCH.DocEntry = b.CreatedBy ) as venddocdate,

(Select top 1 OPCH.Docdate from OPCH Where OPCH.ObjType = b.TransType and OPCH.DocEntry = b.CreatedBy ) as iopchvenddocdate,

b.account,

(Select top 1 OCRD.CardName from OCRD Where OCRD.CardCode = b.ShortName ) as CardName,

(Select top 1 ORCT.Comments from ORCT Where ORCT.ObjType = B.TransType and ORCT.DocEntry = B.CreatedBy ) as IncomingComent ,

(Select top 1 OVPM.Comments from OVPM Where OVPM.ObjType = B.TransType and OVPM.DocEntry = B.CreatedBy ) as OutgoingComent,

(Select top 1 ORCT.JrnlMemo from ORCT Where ORCT.ObjType = B.TransType and ORCT.DocEntry = B.CreatedBy ) as OCRTJrMemo ,

(Select top 1 OVPM.JrnlMemo from OVPM Where OVPM.ObjType = B.TransType and OVPM.DocEntry = B.CreatedBy ) as OVPMJrMemo,

(Select top 1 OACT.AcctName from OACT Where OACT.AcctCode = b.Account ) as AcctName,

--h.u_old_So_No,

--i.U_Old_PO_No,

(Select top 1 VPM4.AcctName from VPM4 where VPM4.DocNum = B.CreatedBy and B.TransType = 46 ) as AcctName,

(Select top 1 RCT4.Descrip from RCT4 where RCT4.DocNum = B.CreatedBy and B.TransType = 24 ) as Descrip,

(Select top 1 VPM4.Descrip from VPM4 where VPM4.DocNum = B.CreatedBy and B.TransType = 46 )  as Descrip,

b.ContraAct,

(Select top 1 OACT.AcctName from OACT Where OACT.AcctCode = b.ContraAct) as contraccname,

(Select top 1 OCRD.CardName from OCRD where OCRD.CardCode = b.ContraAct) as CardName,

a.Memo,

b.DebCred as DEBITCREDITSIGN,

(Select top 1 A.Memo from VPM2 Where VPM2.DocNum = B.CreatedBy and b.TransType = 46) as ovpm_memo,

(Select top 1 A.Memo from RCT2 Where RCT2.DocNum = B.CreatedBy and b.TransType = 24) as orct_memo,

--(isnull(r.SumApplied,0)+isnull(t.SumApplied,0)) as FinalSUMAPPLIED

FinalSUMAPPLIED = ( b.debit+b.credit)

,

( Select top 1 RCT1.CheckNum from RCT1 Where RCT1.Docnum = B.CreatedBy and b.TransType = 24) as  ovpmChqno,

( Select top 1 VPM1.CheckNum from VPM1 Where VPM1.Docnum = B.CreatedBy and b.TransType = 46) as orctChqno,

(Select top 1 OVPM.TrsfrRef from OVPM Where OVPM.DocEntry = B.CreatedBy and ovpm.ObjType = b.transtype ) as TrsfrRef,

(Select top 1 ORCT.TrsfrRef from ORCT Where ORCT.DocEntry = B.CreatedBy and ORCT.ObjType = b.transtype ) as TrsfrRef,

(Select OINV.Comments From OINV where OINV.DocEntry = b.CreatedBy and OINV.ObjType = b.TransType )as OINV_Rmk,

(Select ODPI.Comments From ODPI where ODPI.DocEntry = b.CreatedBy and ODPI.ObjType = b.TransType  )as ODPI_Rmk,

(Select ORIN.Comments From ORIN where ORIN.DocEntry = b.CreatedBy and ORIN.ObjType = b.TransType  )as ORIN_Rmk,

(Select OPCH.Comments From OPCH where OPCH.DocEntry = b.CreatedBy and OPCH.ObjType = b.TransType )as OPCH_Rmk,

(Select ODPO.Comments From ODPO where ODPO.DocEntry = b.CreatedBy and ODPO.ObjType = b.TransType  )as ODPO_Rmk,

(Select ORPC.Comments From ORPC where ORPC.DocEntry = b.CreatedBy and ORPC.ObjType = b.TransType  )as ORPC_Rmk,

b.TransType ,

a.Ref1 as JV_BillNo,

a.Ref2 as Jv_BillDate

--isnull(r1.SumApplied,0) as vpm4_SumApplied_1

--Opening = CASE

--WHEN b.TransType = '-2' then

--(b.Debit+b.Credit)

--else

--0

--end

from OJDT a

inner join JDT1 b on a.transid=b.transid

Left outer join NNM1 d on a.DocSeries=d.Series

where (Select top 1 OCRD.Cardtype from OCRD Where OCRD.CardCode = b.ShortName ) = 'S'

and (Select top 1 OCRD.CardName from OCRD Where OCRD.CardCode = b.ShortName ) like (case when '{?CardName@ Select 'All' as CardName from OCRD union Select Distinct CardName from  OCRD where CardType='S'}' = 'ALL' then '%' else '{?CardName@ Select 'All' as CardName from OCRD union Select Distinct CardName from  OCRD where CardType='S'}' end)

and (Select OACT.Formatcode from OACT where OACT.AcctCode = b.ContraAct ) like (case when '{?CntrlAcct@ Select 'All' as FormatCode,'All' as AcctName union SELECT  distinct oact.FormatCode,AcctName FROM JDT1 INNER JOIN OACT ON JDT1.Account = OACT.AcctCode where LocManTran = 'Y'}' = 'All' then '%' else '{?CntrlAcct@ Select 'All' as FormatCode,'All' as AcctName union SELECT  distinct oact.FormatCode,AcctName FROM JDT1 INNER JOIN OACT ON JDT1.Account = OACT.AcctCode where LocManTran = 'Y'}' end )

and a.TaxDate >={?FromDate}

and a.TaxDate <={?ToDate}

order by a.taxdate,a.transid

Thanks,

Harshal