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"<T1."ID" THEN T0."InQty" ELSE T0."Qty"-IFNULL(T2."SumQty",0) END AS "AgaQty",
      T1."MinDocDate",
      CASE WHEN T0."ID"=1 THEN T0."DocDate" ELSE '19000101' END "MAXDocDate"

FROM :DATA1 T0
INNER JOIN :MinID T1 ON T0."ItemCode"= T1."ItemCode"
LEFT JOIN :DATA1 T2 ON T0."ItemCode"= T2."ItemCode" AND T2."ID"=T1."ID"-1
WHERE T0."ID"<=T1."ID"
ORDER BY T0."ItemCode",T0."ID";

SELECT T0."ItemCode" AS "物料编码",T1."ItemName" AS "物料描述",T2."ItmsGrpCod" AS "物料组",T2."ItmsGrpNam" AS "物料组名称",MAX(T0."Qty") AS "当前库存总数",

     MAX(T0."MinDocDate") AS "最早入库日期",
     MAX(T0."MAXDocDate") AS "最近入库日期",
     ROUND(IFNULL(SUM(T0."AgaQty"*DAYS_BETWEEN(T0."DocDate",:EndDate))/NULLIF(MAX(T0."Qty"),0),0),2) AS "平均库龄",
     SUM(CASE WHEN DAYS_BETWEEN(T0."DocDate",:EndDate)<=30 THEN T0."AgaQty" ELSE 0 END) AS "0-30天",
     SUM(CASE WHEN DAYS_BETWEEN(T0."DocDate",:EndDate) BETWEEN 31 AND 60 THEN T0."AgaQty" ELSE 0 END) AS "31-60天",
     SUM(CASE WHEN DAYS_BETWEEN(T0."DocDate",:EndDate) BETWEEN 61 AND 90 THEN T0."AgaQty" ELSE 0 END) AS "61-90天",
     SUM(CASE WHEN DAYS_BETWEEN(T0."DocDate",:EndDate) BETWEEN 91 AND 180 THEN T0."AgaQty" ELSE 0 END) AS "91-180天",
     SUM(CASE WHEN DAYS_BETWEEN(T0."DocDate",:EndDate) BETWEEN 181 AND 365 THEN T0."AgaQty" ELSE 0 END) AS "181天-1年内",
     SUM(CASE WHEN DAYS_BETWEEN(T0."DocDate",:EndDate) BETWEEN 366 AND 730 THEN T0."AgaQty" ELSE 0 END) AS "一年到二年内",
     SUM(CASE WHEN DAYS_BETWEEN(T0."DocDate",:EndDate) BETWEEN 731 AND 1095 THEN T0."AgaQty" ELSE 0 END) AS "二年到三年内",
      SUM(CASE WHEN DAYS_BETWEEN(T0."DocDate",:EndDate) >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