EBS:没有启用批次号下,库存物料属于哪个采购单号呢

 

在没有启用批次号记录物料入库的EBS里，如何知道采购收料后，采购单对应的物料是否在仓库里？

在表INV.MTL_ONHAND_QUANTITIES_DETAIL.CREATE_TRANSACTION_ID 关联物料事务处理表

直接上代码

SELECT /*+index(MTL_MATERIAL_TRANSACTIONS_N2) */
       MSI.SEGMENT1 AS ITEM_CODE, 
       MSI.DESCRIPTION AS ITEM_DESCRIPTION,
     /*  MTT.TRANSACTION_TYPE_NAME, 
       MMT.ATTRIBUTE15 AS PROJECT_CODE,
       MMT.PRIMARY_QUANTITY,
       MMT.TRANSACTION_UOM,
       MMT.TRANSACTION_DATE,
      -- MMT.PERIOD_COSTED_QUANTITY,
      -- MMT.PERIOD_PRIMARY_QUANTITY,
       MMT.COST_GROUP_ID,
       MMT.OWNING_ORGANIZATION_ID,
       */
       pha.segment1 as po_number,
       PLA.LINE_NUM AS PO_LINE_NUM,
       PLA.UNIT_PRICE,
       PLA.ATTRIBUTE10 AS PO_UNIT_PRICE_WITH_TAX,
       MMT.ACTUAL_COST,
       MOQD.PRIMARY_TRANSACTION_QUANTITY,  --在库数量  
       MOQD.SUBINVENTORY_CODE  -- 子库存码
       --,rt.* 
 FROM MTL_MATERIAL_TRANSACTIONS MMT,
      MTL_TRANSACTION_TYPES MTT,
      MTL_SYSTEM_ITEMS_B MSI,
      RCV_TRANSACTIONS RT,
      PO_HEADERS_ALL PHA,
      PO_LINES_ALL PLA,
      PO_LINE_LOCATIONS_all pll,
      MTL_ONHAND_QUANTITIES_DETAIL MOQD  -- 库存量
WHERE MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID= MSI.ORGANIZATION_ID 
--AND MMT.INVENTORY_ITEM_ID =2416266 -- 1956951
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND MTT.TRANSACTION_TYPE_ID = 18 -- 33: Sales order issue ; 18:PO Receipt ; 10008:COGS Recognition
-- AND MMT.ORGANIZATION_ID = 301 
--AND MMT.TRANSACTION_ID = 10618006756
 AND MMT.TRANSACTION_DATE> TO_DATE('2022-09-01','YYYY-MM-DD')
 AND MMT.TRANSACTION_DATE<= TO_DATE('2022-10-22','YYYY-MM-DD')
-- AND MMT.TRANSACTION_ID = '10612277005' -- 10599832504 --10601351478 -- 10550709345  -- 10602786934 
-- AND MMT.TRANSACTION_SOURCE_ID = POH.PO_HEADER_ID  6116627
--AND MMT.TRANSACTION_SOURCE_ID = 14526353 -- OOH.HEADER_ID 
 AND MMT.SOURCE_CODE = 'RCV' 
 AND MMT.SOURCE_LINE_ID = RT.TRANSACTION_ID 
 AND RT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
 AND RT.TRANSACTION_TYPE = 'DELIVER'
 AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID 
 AND RT.po_line_id = PLA.po_line_id 
 AND MMT.TRANSACTION_ID = MOQD.CREATE_TRANSACTION_ID(+) 
 AND MMT.ORGANIZATION_ID = MOQD.ORGANIZATION_ID(+)
 AND MSI.SEGMENT1 LIKE 'QW0001'
 -- AND PHA.SEGMENT1 ='52378' -- '536935'  -- 采购单号
-- and pla.line_num = 3 
 --AND PHA.SEGMENT1 = '513688'   -- PO_NUMBER  PO订单号
 --AND PLA.LINE_NUM = 1    -- PO_LINE_NUM PO行号
-- and mmt.transaction_id = 10612277005
ORDER BY MMT.TRANSACTION_ID