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
转载于:https://www.cnblogs.com/st-sun/p/3780442.html