Skip to Content
avatar image
Former Member

Query Report for Account balance in Business partner Master Data

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.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Aug 21, 2015 at 10:38 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 21, 2015 at 07:32 PM

    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

    Add comment
    10|10000 characters needed characters exceeded