无批号物料库龄报表

无批号物料库龄报表

注意

不分仓库，只考虑生产入库、采购入库、库存收货

DECLARE @DocDate DATETIME
DECLARE @t INT

SELECT @t       = 1
FROM OJDT P1
WHERE P1.REFDATE <= '[%0]'
SET @DocDate = '[%0]';

WITH doc(indx, ItemCode, TransSeq, Qty) AS (SELECT ROW_NUMBER() OVER (PARTITION BY t0.ItemCode ORDER BY t0.DocDate DESC, t0.TransSeq DESC) indx,
                                                   t0.ItemCode,
                                                   t0.TransSeq,
                                                   t0.InQty
                                            FROM   OIVL t0
                                                   INNER JOIN OILM t1
                                                     ON t0.MessageID = t1.MessageID
                                                   INNER JOIN OITM t2
                                                     ON t0.ItemCode = t2.ItemCode
                                            WHERE  t0.DocDate <= @DocDate AND ((t0.TransType = 59 AND t1.AppObjType = 'P') OR (t0.
                                                   TransType = 59 AND t1.ApplObj = -1) OR t0.TransType = 20))
SELECT   t.ItemCode 物料,
         oitm.ItemName 描述,
         t.Inv 库存数,
         oitm.InvntryUom 库存单位,
         oitb.ItmsGrpNam 物料组,
         oivl.DocDate 入库时间,
         oivl.CreatedBy 入库单号,
         oivl.TransType 入库类型,
         CASE WHEN oilm.ApplObj = 202 THEN N'生产入库' ELSE N'' END 是否生产入库,
         CASE WHEN DATEDIFF(DAY, oivl.DocDate, @DocDate) < 30 THEN t.Allocation ELSE 0 END [0~30],
         CASE WHEN DATEDIFF(DAY, oivl.DocDate, @DocDate) >= 30 AND DATEDIFF(DAY, oivl.DocDate, @DocDate) < 60 THEN t.Allocation ELSE 0 END [30~60],
         CASE WHEN DATEDIFF(DAY, oivl.DocDate, @DocDate) >= 60 AND DATEDIFF(DAY, oivl.DocDate, @DocDate) < 90 THEN t.Allocation ELSE 0 END [60~90],
         CASE WHEN DATEDIFF(DAY, oivl.DocDate, @DocDate) >= 90 AND DATEDIFF(DAY, oivl.DocDate, @DocDate) < 120 THEN t.Allocation ELSE 0 END [90~120],
         CASE WHEN DATEDIFF(DAY, oivl.DocDate, @DocDate) >= 120 THEN t.Allocation ELSE 0 END [120+]
FROM     (SELECT t2.ItemCode, t2.Inv, t4.indx, t4.TransSeq,
                 CASE WHEN t2.Inv - t4.Balance < t4.Qty THEN t2.Inv - t4.Balance ELSE t4.Qty END Allocation
          FROM     (SELECT   t3.ItemCode, sum(t3.InQty - t3.OutQty) Inv
                    FROM     oivl t3 INNER JOIN OITM t4 ON t3.ItemCode = t4.ItemCode
                    WHERE    t3.DocDate <= @DocDate
                    GROUP BY t3.ItemCode
                    HAVING   sum(t3.InQty - t3.OutQty) > 0) t2
                 LEFT OUTER JOIN
                   (SELECT   t0.indx, t0.ItemCode, t0.TransSeq, t0.Qty, sum(isnull(t1.Qty, 0)) Balance
                    FROM     doc t0 LEFT OUTER JOIN doc t1 ON t0.ItemCode = t1.ItemCode AND t1.indx < t0.indx
                    GROUP BY t0.indx, t0.ItemCode, t0.TransSeq, t0.Qty) t4
                 ON t2.ItemCode = t4.ItemCode
          WHERE  t4.Balance < t2.Inv) t
         INNER JOIN oitm
           ON t.ItemCode = oitm.ItemCode
         INNER JOIN OITB
           ON oitm.ItmsGrpCod = oitb.ItmsGrpCod
         INNER JOIN oivl
           ON t.TransSeq = oivl.TransSeq
         INNER JOIN OILM
           ON oivl.MessageID = oilm.MessageID
ORDER BY t.ItemCode, t.indx DESC