on 03-05-2015 8:02 AM
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.