用友U8物料最后一次出入库日期及计算实际库龄

--物料采购期初入库日期
ALTER VIEW V_Quick_PoQc
WITH ENCRYPTION
AS
SELECT Temp.cInvCode,
       Temp.cWhCode,
       Temp.dDate AS PoQcDate,
       DATEDIFF(dd, Temp.dDate, GETDATE()) AS 期初入库库龄
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY rdrecords01.cInvCode,
                                           dbo.RdRecord01.cWhCode
                              ORDER BY RdRecord01.dDate DESC
                             ) AS id,
           rdrecords01.cInvCode,
           dbo.RdRecord01.cWhCode,
           CONVERT(VARCHAR(100), RdRecord01.dDate, 23) AS dDate
    FROM dbo.RdRecord01
        JOIN dbo.rdrecords01
            ON rdrecords01.ID = RdRecord01.ID
               AND RdRecord01.cSource IN ( '采购' )
) Temp
WHERE Temp.id = 1;
GO
--物料最有后一次采购入库日期
ALTER VIEW V_Quick_PoRd
WITH ENCRYPTION
AS
SELECT Temp.cInvCode,
       Temp.cWhCode,
       Temp.dDate AS PoDate,
       DATEDIFF(dd, Temp.dDate, GETDATE()) AS 采购入库库龄
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY rdrecords01.cInvCode,
                                           dbo.RdRecord01.cWhCode
                              ORDER BY RdRecord01.dDate DESC
                             ) AS id,
           rdrecords01.cInvCode,
           dbo.RdRecord01.cWhCode,
           CONVERT(VARCHAR(100), RdRecord01.dDate, 23) AS dDate
    FROM dbo.RdRecord01
        JOIN dbo.rdrecords01
            ON rdrecords01.ID = RdRecord01.ID
               AND RdRecord01.cSource IN ( '采购订单', '采购到货单' )
) Temp
WHERE Temp.id = 1;

GO

--物料最后一次委外入库日期
ALTER VIEW V_Quick_OmRd
WITH ENCRYPTION
AS
SELECT Temp.cInvCode,
       Temp.cWhCode,
       Temp.dDate AS OmDate,
       DATEDIFF(dd, Temp.dDate, GETDATE()) AS 委外入库库龄
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY rdrecords01.cInvCode,
                                           dbo.RdRecord01.cWhCode
                              ORDER BY RdRecord01.dDate DESC
                             ) AS id,
           rdrecords01.cInvCode,
           dbo.RdRecord01.cWhCode,
           CONVERT(VARCHAR(100), RdRecord01.dDate, 23) AS dDate
    FROM dbo.RdRecord01
        JOIN dbo.rdrecords01
            ON rdrecords01.ID = RdRecord01.ID
               AND RdRecord01.cSource IN ( '委外订单', '委外到货单' )
) Temp
WHERE Temp.id = 1;

GO
--物料最后一次其他入库日期
ALTER VIEW V_Quick_Rd
WITH ENCRYPTION
AS
SELECT Temp.cInvCode,
       Temp.cWhCode,
       Temp.dDate AS RdDate,
       DATEDIFF(dd, Temp.dDate, GETDATE()) AS 其他入库库龄
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY rdrecords08.cInvCode,
                                           dbo.RdRecord08.cWhCode
                              ORDER BY RdRecord08.dDate DESC
                             ) AS id,
           rdrecords08.cInvCode,
           dbo.RdRecord08.cWhCode,
           CONVERT(VARCHAR(100), RdRecord08.dDate, 23) AS dDate
    FROM dbo.RdRecord08
        JOIN dbo.rdrecords08
            ON rdrecords08.ID = RdRecord08.ID
) Temp
WHERE Temp.id = 1;
GO
--物料最后一次出库日期
ALTER VIEW V_Quick_LastDate
WITH ENCRYPTION
AS
SELECT Tem.cInvCode,
       Tem.cWhCode,
       Tem.dDate AS LastDate,
       DATEDIFF(dd, Tem.dDate, GETDATE()) AS 最后出库天数
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY TEMP.cInvCode, TEMP.cWhCode ORDER BY TEMP.dDate DESC) AS id,
           TEMP.cInvCode,
           TEMP.cWhCode,
           TEMP.dDate
    FROM
    (
        SELECT *
        FROM
        (
            SELECT ROW_NUMBER() OVER (PARTITION BY rdrecords01.cInvCode,
                                                   dbo.RdRecord01.cWhCode
                                      ORDER BY RdRecord01.dDate DESC
                                     ) AS id,
                   rdrecords01.cInvCode,
                   dbo.RdRecord01.cWhCode,
                   CONVERT(VARCHAR(100), RdRecord01.dDate, 23) AS dDate
            FROM dbo.RdRecord01
                JOIN dbo.rdrecords01
                    ON rdrecords01.ID = RdRecord01.ID
                       AND RdRecord01.cSource = '库存' --采购入库单中退货部分
            UNION ALL
            SELECT ROW_NUMBER() OVER (PARTITION BY rdrecords11.cInvCode,
                                                   dbo.rdrecord11.cWhCode
                                      ORDER BY rdrecord11.dDate DESC
                                     ) AS id,
                   rdrecords11.cInvCode,
                   rdrecord11.cWhCode,
                   CONVERT(VARCHAR(100), rdrecord11.dDate, 23) AS dDate
            FROM dbo.rdrecord11
                JOIN dbo.rdrecords11
                    ON rdrecords11.ID = rdrecord11.ID
            UNION ALL
            SELECT ROW_NUMBER() OVER (PARTITION BY rdrecords32.cInvCode,
                                                   dbo.rdrecord32.cWhCode
                                      ORDER BY rdrecord32.dDate DESC
                                     ) AS id,
                   rdrecords32.cInvCode,
                   rdrecord32.cWhCode,
                   CONVERT(VARCHAR(100), rdrecord32.dDate, 23) AS dDate
            FROM rdrecord32
                JOIN rdrecords32
                    ON rdrecords32.ID = rdrecord32.ID
            UNION ALL
            SELECT ROW_NUMBER() OVER (PARTITION BY rdrecords09.cInvCode,
                                                   dbo.RdRecord09.cWhCode
                                      ORDER BY RdRecord09.dDate DESC
                                     ) AS id,
                   rdrecords09.cInvCode,
                   RdRecord09.cWhCode,
                   CONVERT(VARCHAR(100), RdRecord09.dDate, 23) AS dDate
            FROM RdRecord09
                JOIN rdrecords09
                    ON rdrecords09.ID = RdRecord09.ID
        ) A
        WHERE A.id = 1
    ) TEMP
) Tem
WHERE Tem.id = 1;

GO
--物料最后一次产成品入库日期
ALTER VIEW V_Quick_CpRd
WITH ENCRYPTION
AS
SELECT Temp.cInvCode,
       Temp.cWhCode,
       Temp.dDate AS CpRdDate,
       DATEDIFF(dd, Temp.dDate, GETDATE()) AS 产成品入库库龄
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY rdrecords10.cInvCode,
                                           rdrecord10.cWhCode
                              ORDER BY rdrecord10.dDate DESC
                             ) AS id,
           rdrecords10.cInvCode,
           rdrecord10.cWhCode,
           CONVERT(VARCHAR(100), rdrecord10.dDate, 23) AS dDate
    FROM rdrecord10
        JOIN rdrecords10
            ON rdrecords10.ID = rdrecord10.ID
) Temp
WHERE Temp.id = 1;