I created an ageing report for 3 months but I could have three date parameters while viewing this report in SAP.
Please help me to correct my SP to make a three months Ageing report.
My SP is attached below.
CREATE PROCEDURE "Stock_Ageing_Report" ( IN FirstMonth DATE,
IN SecondMonth DATE,
IN ThirdMonth DATE,
IN WhsName NVARCHAR(200) ) AS
BEGIN SELECT
DISTINCT "ItemCode" "Item Code",
"First Month",
"Second Month",
"Third Month",
"LeadTime",
"Available qty",
"WhsName"
FROM ( SELECT
DISTINCT T0."ItemCode",
( SELECT
IFNULL(SUM(X."InQty"),
0) - IFNULL(SUM(X."OutQty") ,
0)
FROM OINM X
WHERE month(X."DocDate") = month(:FirstMonth )
AND X."ItemCode" = T0."ItemCode"
AND X."Warehouse" = t2."WhsCode"
and year(X."DocDate") = year(:FirstMonth ) )"First Month",
( SELECT
IFNULL(SUM(X."InQty"),
0) - IFNULL(SUM(X."OutQty") ,
0)
FROM OINM X
WHERE month(X."DocDate") = month(:SecondMonth )
AND X."ItemCode" = T0."ItemCode"
AND X."Warehouse" = t2."WhsCode"
and year(X."DocDate") = year(:SecondMonth ) )"Second Month",
( SELECT
IFNULL(SUM(X."InQty"),
0) - IFNULL(SUM(X."OutQty") ,
0)
FROM OINM X
WHERE month(X."DocDate") = month(:ThirdMonth )
AND X."ItemCode" = T0."ItemCode"
AND X."Warehouse" = t2."WhsCode"
and year(X."DocDate") = year(:ThirdMonth ) )"Third Month",
t1."LeadTime",
( SELECT
IFNULL(sum(t2."OnHand"),
0)-IFNULL(sum(t2."IsCommited"),
0)+IFNULL(sum(t2."OnOrder"),
0)
from "OITW" t2
where t2."ItemCode" = T1."ItemCode" )"Available qty",
T2."WhsName"
FROM "OINM" T0
left join "OITM" t1 on t0."ItemCode" = T1."ItemCode"
left join "OWHS" t2 on t0."Warehouse"= t2."WhsCode"
WHERE T2."WhsName" = :WhsName )
;
END
;