SAP B1 库龄分析报表（HANA版本&非批次管理）

在实施交付的项目上，难免会存在一些物料没有启动批次管理，但是需要进行库龄分析，所以需要单独写SQL语句，如下为：HANA版本，非批次管理的库龄分析报表是SQL语句执行的结果。

存储过程的SQL语句为：
后面再使用CALL语法进行调用此存储过程即可。

CREATE    PROCEDURE "IPS_MM_StockAge"
(
    in BPLId nvarchar(50),                 --分支
    in ItmsGrpCod NVARCHAR(20),            -- 开始日期
    in EndDate Date            -- 结束日期

)
LANGUAGE SQLSCRIPT
AS
BEGIN
    
     ---01 查询库存不为0的数据
    StockList=
    SELECT T0."ItemCode",SUM(T0."InQty"-T0."OutQty") AS "Qty"
    FROM OIVL T0
    INNER JOIN OITM T1 ON T0."ItemCode"= T1."ItemCode" 
    INNER JOIN OWHS T2 ON T0."LocCode"= T2."WhsCode"
    WHERE T0."DocDate"<=:EndDate AND (T1."ItmsGrpCod"=:ItmsGrpCod OR IFNULL(:ItmsGrpCod,'')='') AND T2."BPLid"=:BPLId
    GROUP BY T0."ItemCode"
    HAVING SUM(T0."InQty"-T0."OutQty")<>0;


    ---02 查询入库数据
DATA1=
    SELECT T0."ItemCode",T0."DocDate",T0."TransType",T0."CreatedBy",T0."BASE_REF",T0."DocLineNum",
           T0."InQty",
           ROW_NUMBER() OVER(PARTITION BY T0."ItemCode" ORDER BY T0."DocDate" DESC,T0."TransSeq" DESC  ) AS "ID",
           SUM(T0."InQty") OVER(PARTITION BY T0."ItemCode" ORDER BY T0."ItemCode",T0."DocDate" DESC,T0."TransSeq" DESC ) AS "SumQty",
           T1."Qty"
    FROM OIVL T0
    INNER JOIN :StockList T1 ON T0."ItemCode"= T1."ItemCode"
    INNER JOIN OWHS T2 ON T0."LocCode"= T2."WhsCode" AND T2."BPLid"=:BPLId
    WHERE EXISTS(SELECT 1 FROM :StockList U0 WHERE T0."ItemCode"=U0."ItemCode" ) AND T0."InQty">0 AND T0."TransType"<>'67' AND T0."DocDate"<:EndDate;
  
  MinID=
   SELECT T0."ItemCode",MIN(T0.ID) AS "ID",MAX(T0."DocDate") AS "MinDocDate"
   FROM :DATA1 T0
   WHERE T0."SumQty">=T0."Qty"
   GROUP BY T0."ItemCode";
  
  DATA=
   SELECT T0."ItemCode",T0."DocDate",T0."TransType",T0."CreatedBy",T0."BASE_REF",T0."DocLineNum",T0."InQty",T0."Qty",
          T0."SumQty",T0."ID",T1."ID" AS "MinID",
          CASE WHEN T0."ID"1096 THEN T0."AgaQty" ELSE 0 END) AS "三年以上"
   FROM :DATA T0
   INNER JOIN OITM T1 ON T0."ItemCode"= T1."ItemCode"
   INNER JOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod"
   GROUP BY T0."ItemCode",T1."ItemName",T2."ItmsGrpCod",T2."ItmsGrpNam"
   ORDER BY 3,1;
END