Need selection criteria

[Former Member]

Hii Experts,

I am new in this (sap) , I am having one query in which i need selection criteria as a business partner name .

this is query........

declare @fromdate as Datetime

Declare @Todate as Datetime

set @fromdate = ( select Min(ta.refdate) from OJDT ta where ta.refdate = [%0])

set @Todate = ( select Max(tb.refdate) from OJDT tb where tb.refdate = [%1])

SELECT DISTINCT T0.[RefDate], T0.[TransId],   T1.[ShortName]'GLAccount/BPCode', T2.[AcctName] 'GLName/BPName'

, T1.ContraAct'OffsetAccount',T1.[LineMemo]'Details',

(T1.[credit] - (isnull((select BcgSum from ORCT where TransId= T0.[TransId] AND CardCode= T1.[ShortName] ),0))) as 'Debit',

(-T1.[debit] - (isnull((select Bcgsum from OVPM where TransId = T0.[TransId] AND CardCode= T1.[ShortName]),0))) as 'Credit',

T3.[AcctName] AS 'Cash / Bank'

FROM OJDT T0 INNER JOIN JDT1 T1 ON  T0.[TransId] = T1.[TransId] INNER JOIN OACT T2 ON  T1.[Account] = T2.[AcctCode] INNER JOIN

oact t3 ON t3.AcctCode=t1.ContraAct

WHERE T0.[RefDate] BETWEEN @fromdate AND @todate  

ORDER BY T0.[RefDate]

Thank you in advance

[former_member212181 · Accepted solution]

HI Neelima,

Please try below query

Declare @fromdate as Datetime

Declare @Todate as Datetime

set @fromdate = ( select Min(ta.refdate) from OJDT ta where ta.refdate = [%0])

set @Todate = ( select Max(tb.refdate) from OJDT tb where tb.refdate = [%1])

SELECT DISTINCT T0.[RefDate], T0.[TransId],   T1.[ShortName]'GLAccount/BPCode', T2.[AcctName] 'GLName/BPName'

, T1.ContraAct'OffsetAccount',T1.[LineMemo]'Details',

(T1.[credit] - (isnull((select BcgSum from ORCT where TransId= T0.[TransId] AND CardCode= T1.[ShortName] ),0))) as 'Debit',

(-T1.[debit] - (isnull((select Bcgsum from OVPM where TransId = T0.[TransId] AND CardCode= T1.[ShortName]),0))) as 'Credit',

T3.[AcctName] AS 'Cash / Bank'

FROM OJDT

T0 INNER JOIN JDT1 T1 ON  T0.[TransId] = T1.[TransId]

INNER JOIN OACT T2 ON  T1.[Account] = T2.[AcctCode]

INNER JOIN oact t3 ON t3.AcctCode=t1.ContraAct

Left Outer Join OCRD T4 on T1.shortName = T4.CardCode

WHERE T0.[RefDate] BETWEEN @fromdate AND @todate  and T4.CardName =N'[%2]'

ORDER BY T0.[RefDate]

Thanks

Unnikrishnan

[Former Member]

thanks to every one now report is working

[former_member211473]

hello neelima

you can try this query and reply me back if u were looking for same result..

SELECT DISTINCT T0.[RefDate], T0.[TransId],   T1.[ShortName]'GLAccount/BPCode', T2.[AcctName] 'GLName/BPName'

, T1.ContraAct'OffsetAccount',T1.[LineMemo]'Details',

(T1.[credit] - (isnull((select $[orct.BcgSum] ),0))) as 'Debit',

(-T1.[debit] - (isnull((select $[ovpm.Bcgsum]),0))) as 'Credit',

T.[AcctName] AS 'Cash / Bank' from OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode INNER JOIN

oact t ON t.AcctCode=t1.ContraAct INNER JOIN OCRD T3 ON T2.AcctCode = T3.DebPayAcct

WHERE T3.[CardName] =[%0] and T0.[RefDate] >[%1] and  T0.[RefDate] <[%2]

ORDER BY T0.[RefDate]

[Former Member]

Hi Neelam,

Try below Query.

declare @fromdate as Datetime

Declare @Todate as Datetime

Declare @BPName as NVARCHAR (200)

set @fromdate = ( select Min(ta.refdate) from OJDT ta where ta.refdate >= '[%3]')

set @Todate = ( select Max(tb.refdate) from OJDT tb where tb.refdate <= '[%4]')

set @BPName = ( select Distinct tc.ShortName from JDT1 tc where tc.ShortName = '[%0]')

SELECT DISTINCT T0.[RefDate], T0.[TransId],   T1.[ShortName]'GLAccount/BPCode', T2.[AcctName] 'GLName/BPName'

, T1.ContraAct'OffsetAccount',T1.[LineMemo]'Details',

(T1.[credit] - (isnull((select BcgSum from ORCT where TransId= T0.[TransId] AND CardCode= T1.[ShortName] ),0))) as 'Debit',

(-T1.[debit] - (isnull((select Bcgsum from OVPM where TransId = T0.[TransId] AND CardCode= T1.[ShortName]),0))) as 'Credit',

T3.[AcctName] AS 'Cash / Bank'

FROM OJDT T0

INNER JOIN JDT1 T1 ON  T0.[TransId] = T1.[TransId]

INNER JOIN OACT T2 ON  T1.Account = T2.[AcctCode]

INNER JOIN oact t3 ON t3.AcctCode=t1.ContraAct

WHERE T0.[RefDate] >= @fromdate AND T0.[RefDate] <= @todate  AND T1.ShortName = @BPName

ORDER BY T0.[RefDate]

Hope this Helps

Thanks'

--

--

Regards::::

Atul Chakraborty