Skip to Content
0

Stored Procedure Report

Apr 10, 2017 at 11:55 AM

20

avatar image

Dear Experts,

I want to add BP partner Code instead of AcctntCode how to add in the stored procedure report. Kindly guide the same.

ALTER PROCEDURE [dbo].[Sp_DRI] @FDate as Datetime, @TDate as Datetime AS BEGIN select *, CASE when(((opening-Debit)+Credit) <> 0) And opening<Sales And OS=0 THEN ((opening-Debit+Credit) * 3 /100) else 0 END [3% DRI] from (SELECT AccntntCod,AcctName,[Loc] ,sum(opening) opening,sum(Debit) Debit,sum(Credit) Credit,(sum(opening)-sum(Debit)+sum(Credit)) [Closing] ,isnull(( SELECT sum(Doctotal) [Invoice] from OCRD,OINV where ocrd.CardCode=AccntntCod And Oinv.DocDate between @FDate and @TDate --t1.shortname and oinv.objtype=t1.Transtype and oinv.DocEntry=t1.Createdby --in (Select oact.AccntntCod from OACT Where oact.AccntntCod=t2.AccntntCod) ),0) Sales ,isnull((SELECT sum(isnull(JDT1.BalDueDeb,0)-isnull(JDT1.BalDueCred,0)) from JDT1 where shortname =AccntntCod and JDT1.[RefDate] <= (@TDate) AND (JDT1.[BalDueCred] <> JDT1.[BalDueDeb] OR JDT1.[BalFcCred] <> JDT1.[BalFcDeb] )),0) [OS] from (SELECT t2.AccntntCod,t2.AcctName, isnull((select Top 1 City from CRD1 left outer join OACT on crd1.CardCode=OACT.AccntntCod where OACT.AccntntCod= T2.AccntntCod And CRD1.AdresType='B'),'')[Loc], sum(Debit)-sum(Credit) opening ,0 Debit,0 Credit ,0 Sales,0 [OS] FROM ojdt t0 inner JOIN JDT1 t1 on t0.TransId =t1.TransId inner JOIN OACT t2 ON t2.AcctCode =t1.Account AND AccntntCod is NOT null and AccntntCod <>'' left outer join OCRD t3 on t3.cardcode=t2.AccntntCod WHERE t0.refdate <= @FDate group by t2.AcctName,t2.AccntntCod --UNION all --Select '' AccntntCod,'' AcctName,'' [Loc],0 opening ,0 Debit,0 Credit, -- (Select sum(oinv.DocTotal)[Sales] from oinv Where oinv.DocEntry=t8.Docentry group by oinv.Cardcode) -- ,0 [OS] -- from oinv t8 left outer join --ojdt t0 on t0.transid=t8.transid and t0.createdby=t8.docentry left outer join --jdt1 t1 on t0.transid=t1.transid --where t0.refdate BETWEEN @FDate and @TDate ----Group by oinv.carcode UNION all SELECT t2.AccntntCod,t2.AcctName, isnull((select Top 1 City from CRD1 left outer join OACT on crd1.CardCode=OACT.AccntntCod where OACT.AccntntCod= T2.AccntntCod And CRD1.AdresType='B'),'')[Loc], 0 opening ,sum(Debit) Debit,-sum(Credit) Credit, 0 [Sales], 0 [OS] FROM ojdt t0 inner JOIN JDT1 t1 on t0.TransId =t1.TransId inner JOIN OACT t2 ON t2.AcctCode =t1.Account AND AccntntCod is NOT null and AccntntCod <>'' left JOIN [dbo].[ITR1] t3 ON t3.[TransId] = t1.[TransId] AND t3.[TransRowId] = t1.[Line_ID] left JOIN [dbo].[OITR] t4 ON t4.[ReconNum] = t3.[ReconNum] WHERE t0.refdate BETWEEN @FDate and @TDate group by t2.AcctName,t2.AccntntCod,t4.[ReconDate],t1.Shortname,t1.transtype,t1.createdby) Deposit group by AcctName ,AccntntCod,[Loc] ) [T0] order BY AccntntCod END --Exec [dbo].[Sp_DRI] '20141101','20141130'

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers