SAP B1 中查看已分配批次明细

SAP B1 中查看已分配批次明细

-- =============================================
-- Author:  袁英杰
-- Create date: 2023-10-03
-- Description: 传入锁货类型（比如销售订单的锁货，-99时返回所有类型的锁货），返回该类型的所有未清的锁货行明细
-- =============================================
CREATE FUNCTION getBatchAllocDetail (@AllocateTP INT)
   RETURNS TABLE
AS
RETURN (SELECT t3.AllocateTp,
               t3.AllocatEnt,
               t3.AllocateLn,
               t3.ItemCode,
               Sum (AllocQty) AllocQty,
               t2.DistNumber,
               t2.AbsEntry,
               t3.LocCode
        FROM ITL1 (NOLOCK) t1
             INNER JOIN OBTN (NOLOCK) t2 ON t1.MdAbsEntry = t2.AbsEntry
             INNER JOIN OITL (NOLOCK) t3 ON t1.LogEntry = t3.LogEntry
        WHERE (t3.AllocateTp = @AllocateTP OR @AllocateTP = -99)
        GROUP BY AllocateTp,
                 AllocatEnt,
                 AllocateLn,
                 t3.ItemCode,
                 t2.DistNumber,
                 t3.LocCode,
                 t2.AbsEntry
        HAVING Sum (AllocQty) > 0)