Skip to Content
0
Former Member
Mar 05, 2015 at 08:02 AM

Query for bank reconciliation statement.

53 Views

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.