Oracle EBS-SQL (INV-4):检查负库存记录数.sql

DEFINE DATE1="01/15/20** 23:59:59"      /*输入指定日期*/
DEFINE CODE="%"                                 /*输入指定子库存*/
SELECT *
FROM(
SELECT 项目编码,描述,子库,单位,(-SUM(接收数量)-SUM(发出数量)+SUM(现有库存)) 指定日期库存,
SUM(接收数量) 接收数量,SUM(发出数量) 发出数量,SUM(现有库存) 当前库存
FROM (select MSI.SEGMENT1              项目编码,
          MSI.DESCRIPTION                        描述,
          MMT.SUBINVENTORY_CODE          子库,
           MUM.UNIT_OF_MEASURE_TL         单位,
           SUM(nvl(mmt.transaction_quantity,0)) 接收数量 ,
           0                                           发出数量,
           0                                           现有库存
from
       inv.Mtl_Material_Transactions            MMT,
       INV.MTL_SYSTEM_ITEMS_B               MSI,
       INV.MTL_UNITS_OF_MEASURE_TL     MUM
WHERE
           MSI.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID
    AND MSI.ORGANIZATION_ID=MMT.ORGANIZATION_ID
    AND MUM.UOM_CODE=MMT.TRANSACTION_UOM
    AND MSI.ORGANIZATION_ID=Y
    AND mmt.transaction_quantity > 0
    AND (mmt.transaction_date BETWEEN '&DATE1' AND SYSDATE)
    AND MMT.SUBINVENTORY_CODE LIKE '&CODE'
GROUP BY
       MSI.SEGMENT1 ,
       MSI.DESCRIPTION ,
       MMT.SUBINVENTORY_CODE,
       MUM.UNIT_OF_MEASURE_TL
union all
select MSI.SEGMENT1              物料编码,
       MSI.DESCRIPTION           物料描述,
       MMT.SUBINVENTORY_CODE    子库,
       MUM.UNIT_OF_MEASURE_TL   单位,
       0                                     接收数量,
       SUM(nvl(mmt.transaction_quantity,0)) 发出数量 ,
       0                                    现有库存
from
       inv.Mtl_Material_Transactions           MMT,
       INV.MTL_SYSTEM_ITEMS_B               MSI,
       INV.MTL_UNITS_OF_MEASURE_TL     MUM
WHERE
            MSI.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID
  AND   MSI.ORGANIZATION_ID=MMT.ORGANIZATION_ID
  AND    MUM.UOM_CODE=MMT.TRANSACTION_UOM
  AND   MSI.ORGANIZATION_ID=Y
  AND   mmt.transaction_quantity < 0
--AND (MSI.SEGMENT1 BETWEEN '&ITEM1' AND '&ITEM2')
  AND (mmt.transaction_date BETWEEN '&DATE1' AND SYSDATE)
  AND  MMT.SUBINVENTORY_CODE LIKE '&CODE'
GROUP BY
       MSI.SEGMENT1,
       MSI.DESCRIPTION,
       MMT.SUBINVENTORY_CODE,
       MUM.UNIT_OF_MEASURE_TL
union all
SELECT
     b.segment1                                                     编码
    ,b.description                                                    描述
    ,a.subinventory_code                                         子库
    ,MUM.UNIT_OF_MEASURE_TL                             单位
    ,0                                                               接收数量
    ,0                                                               发出数量
    ,SUM(nvl(a.primary_transaction_quantity,0)) 现有库存
FROM
    INV.MTL_ONHAND_QUANTITIES_DETAIL A,
    INV.Mtl_System_Items_b B,
    INV.MTL_UNITS_OF_MEASURE_TL     MUM
WHERE
       a.ORGANIZATION_ID = Y
AND a.organization_id=b.organization_id
AND MUM.UOM_CODE=a.TRANSACTION_UOM_CODE
AND a.inventory_item_id=b.inventory_item_id
AND a.subinventory_code LIKE '&CODE'
--AND (B.SEGMENT1 BETWEEN '&ITEM1' AND '&ITEM2')
GROUP BY
    b.segment1
   ,b.description
   ,a.subinventory_code
   ,MUM.UNIT_OF_MEASURE_TL)
   GROUP BY 项目编码,描述,子库,单位
)
WHERE 指定日期库存 < 0