安全库存不足警报

代码示例

SELECT    OITM.ItemCode AS 物料编码, OITM.ItemName AS 物料名称, OITB.ItmsGrpNam AS 物料组, N'全公司' AS 仓库, OITM.MinLevel AS 安全库存, 
                      OITM.OnHand AS 当前库存, ISNULL(OITM.MinLevel, 0) - ISNULL(OITM.OnHand, 0) AS 库存不足
FROM         OITM LEFT OUTER JOIN
                      OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod
WHERE     (OITM.ByWh = 'N') AND (OITM.MinLevel >= OITM.OnHand) AND (ISNULL(OITM.MinLevel, 0) > 0)
UNION
SELECT     OITW.ItemCode AS 物料编码, OITM_2.ItemName AS 物料名称, OITB_1.ItmsGrpNam AS 物料组, OWHS.WhsName AS 仓库, 
                      OITW.MinStock AS 安全库存, OITW.OnHand AS 当前库存, OITW.MinStock - OITW.OnHand AS 库存不足
FROM         OWHS RIGHT OUTER JOIN
                      OITW ON OWHS.WhsCode = OITW.WhsCode LEFT OUTER JOIN
                      OITB AS OITB_1 RIGHT OUTER JOIN
                      OITM AS OITM_2 ON OITB_1.ItmsGrpCod = OITM_2.ItmsGrpCod ON OITW.ItemCode = OITM_2.ItemCode
WHERE     (ISNULL(OITW.MinStock, 0) > 0) AND (OITW.MinStock >= OITW.OnHand) AND (OITW.ItemCode IN
                          (SELECT     ItemCode
                            FROM          OITM AS OITM_1
                            WHERE      (ByWh = N'Y')))
ORDER BY 物料编码, 仓库