cancel
Showing results for 
Search instead for 
Did you mean: 

Query for bank reconciliation statement.

former_member487237
Participant
0 Kudos

Hi all,

I'm using SAP B1 9.0 PL08 and Cystal reports 2008. I want a query that gives me the non-reconciled data.

From 1) payments

          2) Bank transfers

         3) Deposits not cleared

         4) cheques received not deposited

          5) post dated cheques.

i have some sample query which i tried. i want to add 4 and 5 also in this query.

Query:

Declare @fromdate date={?@fromdate}

Declare @todate date={?@todate}

select '1',C.AcctCode,A.Transid,A.BaseRef ,D.CardCode,D.CardName,A.RefDate,B.Ref3Line ,sum(B.Credit ) from OJDT A,JDT1 B, OACT C ,OVPM D

where    A.TransId=B.TransId and C.AcctCode=B.Account  and D.DocNum=A.BaseRef and D.Canceled='N'

and A.TransType =46 and b.ExtrMatch=0 and A.RefDate between @fromdate and @todate

group by

C.AcctCode,A.Transid,A.BaseRef ,D.CardCode,D.CardName,A.RefDate,B.Ref3Line

union

select '2',C.AcctCode,A.Transid,A.BaseRef ,D.CardCode,D.CardName,A.RefDate, B.Ref3Line ,sum(B.Debit  ) from OJDT A,JDT1 B, OACT C,ORCT D

where    A.TransId=B.TransId and C.AcctCode=B.Account

and A.TransType =24 and b.ExtrMatch=0 and D.DocNum=A.BaseRef and D.Canceled='N' and A.RefDate between @fromdate and @todate

group by

C.AcctCode,A.Transid,A.BaseRef ,D.CardCode,D.CardName,A.RefDate, B.Ref3Line

union

select '3',D.BanckAcct ,A.Transid,A.BaseRef ,E.CardCode,F.CardName,

A.RefDate, B.Ref3Line ,sum(B.Debit ) from OJDT A,JDT1 B, OACT C,ODPS D ,OCHH E,OCRD F

where   E.CardCode=F.CardCode and D.DeposId=e.DpstAbs and  A.TransId=B.TransId and C.AcctCode=B.Account  and D.BanckAcct=B.Account

and A.TransType =25 and b.ExtrMatch=0 and D.DeposNum=A.BaseRef and D.Canceled='N' and A.RefDate between @fromdate and @todate

group by

D.BanckAcct,A.Transid,E.CardCode,F.CardName,A.BaseRef ,A.RefDate, B.Ref3Line

Can anyone alter this query and help me in this.

Thanks & Regards,

Saikrishna.

Accepted Solutions (0)

Answers (1)

Answers (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Sai,

Please repost to the space.

-Abhilash

former_member487237
Participant
0 Kudos

Hi Abhilash,

I have reposted in SAP Business One Application.

Regards,

Saikrishna.