EBS 根据时间倒推库存现有量

1、未启用批次号

select dtl.organization_id 库存组织,
       dtl.inventory_item_id,
       dtl.segment1 物料,
       dtl.description 描述,
       decode(dtl.wip_supply_type,
              1,
              '推式',
              3,
              '工序拉式',
              2,
              '装配拉式',
              dtl.wip_supply_type),
       dtl.revision,
       dtl.subinventory_code 子库,
       dtl.primary_uom_code 主单位,
       sum(dtl.primary_transaction_quantity) 主数量
  from (
        select moq.organization_id,
                moq.inventory_item_id,
                mst.segment1,
                mst.description,
                mst.wip_supply_type,
                moq.revision,
                moq.subinventory_code,
                moq.locator_id,
                moq.lot_number,
                mst.primary_uom_code,
                moq.primary_transaction_quantity
          from inv.mtl_onhand_quantities_detail moq,
                inv.mtl_system_items_b           mst
         where moq.organization_id = mst.organization_id
           and moq.inventory_item_id = mst.inventory_item_id
           and moq.organization_id = 602
        --  and mst.segment1 = nvl('&item_no',mst.segment1)
        -- and moq.lot_number=nvl('&lot_no',moq.lot_number)
        union all
        --backflush quantity
        
        select mmt.organization_id,
                mmt.inventory_item_id,
                mst.segment1,
                mst.description,
                mst.wip_supply_type,
                mmt.revision,
                mmt.subinventory_code,
                mmt.locator_id,
                -- lot.lot_number,
                null,
                mst.primary_uom_code,
                mmt.primary_quantity * -1 primary_transaction_quantity
          from inv.mtl_material_transactions mmt,
                --inv.mtl_transaction_lot_numbers lot,
                inv.mtl_system_items_b mst
         where 1 = 1 --mmt.transaction_id = lot.transaction_id(+)
           and mmt.organization_id = mst.organization_id
           and mmt.inventory_item_id = mst.inventory_item_id
              --filter
           and mmt.organization_id = 602
              --and mst.segment1 =nvl('&item_no',mst.segment1)
              --and lot.lot_number=nvl('&lot_no',lot.lot_number)
           and mmt.transaction_date >= to_date('2024-12-31', 'YYYY-MM-DD')
        --to_date('&datetime'/*'2006-12-01 00:00:00'*/, 'YYYY-MM-DD HH24:MI:SS')
        ) dtl
 group by dtl.organization_id,
          dtl.inventory_item_id,
          dtl.segment1,
          dtl.description,
          dtl.wip_supply_type,
          dtl.revision,
          dtl.subinventory_code,
          dtl.locator_id,
          -- dtl.lot_number,
          dtl.primary_uom_code
having sum(dtl.primary_transaction_quantity) <> 0;

 

 

 

2、启用批次号的

SELECT dtl.organization_id库存组织,
       dtl.inventory_item_id,
       dtl.segment1物料,
       dtl.description描述,
       decode(dtl.WIP_SUPPLY_TYPE,
              1,
              '推式',
              3,
              '工序拉式',
              2,
              '装配拉式',
              dtl.WIP_SUPPLY_TYPE),
       dtl.revision,
       dtl.subinventory_code子库,
       dtl.locator_id货位,
       dtl.lot_number批次,
       dtl.primary_uom_code主单位,
       SUM(dtl.primary_transaction_quantity) 主数量
  FROM (
        --OnhandQuantity
        SELECT moq.organization_id,
                moq.inventory_item_id,
                mst.segment1,
                mst.description,
                mst.WIP_SUPPLY_TYPE,
                moq.revision,
                moq.subinventory_code,
                moq.locator_id,
                moq.lot_number,
                mst.primary_uom_code,
                moq.primary_transaction_quantity
          FROM inv.mtl_onhand_quantities_detailmoq,
                inv.mtl_system_items_b mst
         WHERE moq.organization_id = mst.organization_id
           AND moq.inventory_item_id = mst.inventory_item_id
              --AND moq.is_consigned = 2
              --Filter
           AND moq.organization_id = 102
        -- AND mst.segment1 = nvl('&item_no',mst.segment1)
        --and moq.lot_number=nvl('&lot_no',moq.lot_number)
        UNION ALL
        --BackflushQuantity
        
        SELECT mmt.organization_id,
                mmt.inventory_item_id,
                mst.segment1,
                mst.description,
                mst.WIP_SUPPLY_TYPE,
                mmt.revision,
                mmt.subinventory_code,
                mmt.locator_id,
                lot.lot_number,
                mst.primary_uom_code,
                -1 * nvl(lot.primary_quantity, mmt.primary_quantity) primary_transaction_quantity
          FROM inv.mtl_material_transactions      mmt,
                inv.mtl_transaction_lot_numberslot,
                inv.mtl_system_items_b             mst
         WHERE mmt.transaction_id = lot.transaction_id(+)
           AND mmt.organization_id = mst.organization_id
           AND mmt.inventory_item_id = mst.inventory_item_id
              --Filter
           AND mmt.organization_id = 102
              --ANDmst.segment1 =nvl('&item_no',mst.segment1)
              -- and lot.lot_number=nvl('&lot_no',lot.lot_number)
           AND mmt.transaction_date >=
               to_date('&datetime' /*'2006-12-0100:00:00'*/,
                       'YYYY-MM-DD HH24:MI:SS')) dtl
 GROUP BY dtl.organization_id,
          dtl.inventory_item_id,
          dtl.segment1,
          dtl.description,
          dtl.WIP_SUPPLY_TYPE,
          dtl.revision,
          dtl.subinventory_code,
          dtl.locator_id,
          dtl.lot_number,
          dtl.primary_uom_code
HAVING SUM(dtl.primary_transaction_quantity) <> 0;