how to reqwrite a customized stock summary report in sap b1

[wale_adegbola]

Dear All,

I have a client that wrote a customized stock summary report and when the report is run in SAP, according to the month it gives them the one for the present month and not the previous month.

This is the query below

DECLARE TODATE DATE;
SELECT MIN(T."DocDate") into TODATE from OINM T WHERE T."DocDate" <= '[%0]';

SELECT T2."BalInvntAc", T0."Warehouse",
T1."ItemCode", T1."ItemName",T1."InvntryUom",
sum(T0."OpenQty"+ T0."InQty"- T0."OutQty") as "Balance Qty In UOM",T1."SWeight1" as "Unit Conversion In Grams",
map(upper(T1."InvntryUom"),'KGS',sum(T0."OpenQty"+ T0."InQty"- T0."OutQty"),(sum(T0."OpenQty"+ T0."InQty"- T0."OutQty")*T1."SWeight1"/1000)) as "Closing Qty Kgs",
Case when sum(T0."OpenQty"+ T0."InQty"- T0."OutQty")<>0 then
round(SUM(T0."TransValue")/sum(T0."OpenQty"+ T0."InQty"- T0."OutQty"),2) else 0 end AS "Item Price",
SUM(T0."TransValue") Amount
FROM OINM T0 INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
INNER JOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod"
WHERE T0."DocDate"<=(select Max(C."DocDate") from OINM C
where C."ItemCode" = T0."ItemCode" and C."Warehouse"=T0."Warehouse" AND C."DocDate" <= [%0])
GROUP BY T1."ItemCode", T1."ItemName", T0."Warehouse",T2."BalInvntAc",T1."InvntryUom",T1."SWeight1";
--HAVING sum(T0."OpenQty"+ T0."InQty"- T0."OutQty")<>0;

I'll appreciate it if it is rewritten in such a way that when they run the report it will show the report for any month ran.

[MD1]

Hi,

You can try the date-wise stock query report.

BEGIN DECLARE FromDate DATE;

DECLARE ToDate DATE;

SELECT (SELECT MIN(S0."DocDate") FROM OINM S0 WHERE S0."DocDate" >= '[%0]') INTO FromDate FROM DUMMY;

SELECT (SELECT MAX(S1."DocDate") FROM OINM S1 WHERE S1."DocDate" <= '[%1]') INTO ToDate FROM DUMMY;

select * from (

SELECT distinct

T2."BalInvntAc",

T0."Warehouse",

T1."ItemCode",

T1."ItemName",

T1."InvntryUom",

(IFNULL((SELECT

SUM(IFNULL("InQty", 0))

FROM OINM O1

WHERE O1."ItemCode" = T1."ItemCode" AND O1."InQty" > 0

AND O1."DocDate" < :FromDate

), 0) - IFNULL((SELECT

SUM(IFNULL("OutQty", 0))

FROM OINM O1

WHERE O1."ItemCode" = T1."ItemCode" AND O1."OutQty" > 0

AND O1."DocDate" < :FromDate

), 0)) AS "Opening Stock" ,

T1."SWeight1" as "Unit Conversion In Grams",

map(upper(T1."InvntryUom"),'KGS',sum(T0."OpenQty"+ T0."InQty"- T0."OutQty"),

(sum(T0."OpenQty"+ T0."InQty"- T0."OutQty")*T1."SWeight1"/1000)) as "Closing Qty Kgs"

,

Case when sum(T0."OpenQty"+ T0."InQty"- T0."OutQty")<>0 then

round(SUM(T0."TransValue")/sum(T0."OpenQty"+ T0."InQty"- T0."OutQty"),2) else 0 end AS "Item Price",

(IFNULL((SELECT

SUM(IFNULL("InQty", 0))

FROM OINM O1

WHERE O1."ItemCode" = T1."ItemCode"

AND O1."DocDate" <= :ToDate AND O1."InQty" > 0

), 0) - IFNULL((SELECT

SUM(IFNULL("OutQty", 0))

FROM OINM O1

WHERE O1."ItemCode" = T1."ItemCode"

AND O1."DocDate" <= :ToDate AND O1."OutQty" > 0

), 0)) AS "Closing Stock" ,

(IFNULL((SELECT SUM(IFNULL("TransValue", 0))

FROM OINM O1

WHERE O1."ItemCode" = T1."ItemCode"

AND O1."DocDate" <= :ToDate

), 0) ) AS "Closing Value"

FROM OINM T0

INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"

INNER JOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod"

WHERE t1."ItemName" is not null

GROUP BY T1."ItemCode", T1."ItemName", T0."Warehouse",T2."BalInvntAc",T1."InvntryUom",T1."SWeight1") as a

where ("Opening Stock"+"Closing Stock")>'0'

;

end;

Mohd Danish