Unable to pass date parameter to sub-query through query generator - sap b1

782 Views

I have a query for which i have to pass date as a parameter to subquery to generate report. I am unable to do this. I am using SAP B1 hana 9.1 PL05. Please revert back with your suggestion.... I want this to be done using query itself. The coding is as follows:

Select A."PrjCode", A."PrjName", A."CardName", (sum(A."Current") + sum(A."Payments")) as "Current", sum(A."Retention") as "Retention", sum(A."Advance") as "Advance"

from

(

SELECT ifnull(T1."PrjCode", '04012') as "PrjCode", ifnull(T1."PrjName",'General') as "PrjName", T2."CardCode", T2."CardName",

(Case when T0."TransType" = '46' and ifnull(T0."Ref3Line",'0') <> '2' then sum(T0."Debit" - T0."Credit") else 0 end) as "Payments",

(Case when ifnull(T0."Ref3Line",'0') <> '2' and T0."TransType" <> '46' then sum(T0."Debit" - T0."Credit") else 0 end) as "Current",

(Case when ifnull(T0."Ref3Line",'0') = '2' then sum(T0."Debit" - T0."Credit") else 0 end) as "Retention",

(Case when (T0."TransType" = '204' or T0."TransType" = '19') and T0."ContraAct" ='114050001' then sum(T0."Debit" - T0."Credit") else 0 end) as "Advance"

FROM JDT1 T0

Left JOIN OPRJ T1 ON T0."Project" = T1."PrjCode"

INNER JOIN OCRD T2 ON T2."CardCode" = T0."ShortName"

INNER JOIN OCRG T3 ON T2."GroupCode" = T3."GroupCode"

--WHERE T2."CardType" = 'S' AND T3."GroupName" like '%Supplier%'

WHERE T2."CardCode" = 'V0015'

GROUP BY T1."PrjCode", T1."PrjName", T2."CardCode", T2."CardName", T0."Ref3Line", T0."TransType", T0."RefDate", T0."ContraAct"

) A group by A."PrjCode", A."PrjName", A."CardName"

having ((sum(A."Current") + sum(A."Payments"))+sum(A."Retention") +sum(A."Advance")) <> 0

order by A."PrjCode", A."PrjName", A."CardName"

Thanks,

Santhosh