怎么查询MTL_ONHAND_QUANTITIES_DETAIL与mtl_material_transactions中的库存量持平？-CSDN博客

网上的一篇文章：
Oracle Applications 提供了计算库存的表 MTL_ONHAND_QUANTITIES_DETAIL，开发人员只要按要求对表进行分组求和即可得到库存的现有量，这个数量就是我们在系统中查询的数量。但是如果用户要开发人员提供上个月或上个季度的库存就麻烦了。因为表MTL_ONHAND_QUANTITIES_DETAIL不提供追溯库存计算的方法。那怎么解决这个问题？
我们先来看看表MTL_ONHAND_QUANTITIES_DETAIL和 mtl_material_transactions 表的关系。mtl_material_transactions 是 Oracle Applications 库存的基表，凡是和库存有关的数据都必须存入这个表，如初期的库存导入，采购的接受入库。销售，制造的完工入库。子库转移等。mtl_material_transactions 的分组求和应该等于MTL_ONHAND_QUANTITIES_DETAIL表的分组求和，但表mtl_material_transactions 的记录一般数据庞大，不会在这个表中求现有量。mtl_material_transactions表中每笔的数据异动都会影响到MTL_ONHAND_QUANTITIES_DETAIL 的最终结果。我们通过研究发现。mtl_material_transactions 每笔入库的记录都会相应的在MTL_ONHAND_QUANTITIES_DETAI 表中增加此记录。在库存未消失前我们可以通过 mtl_material_transactions表的TRANSACTION_ID字段和表MTL_ONHAND_QUANTITIES_DETAIL的字段CREATE_TRANSACTION_ID 建立一一对应关系。
现在的问题是我在系统里通过MTL_ONHAND_QUANTITIES_DETAIL中的CREATE_TRANSACTION_ID字段关联mtl_material_transactions只能关联到一小部分，有很多都是空的，mtl_material_transactions就没有CREATE_TRANSACTION_ID 的记录，求解

这个是按年份计算历史库存状态的查询，可以按自己要求改写出你要的语句。
select organization_id,
   to_char(lastdate, 'yyyy'),
   count(*) cnt,
   sum(amount) value
  from (

      select tran.organization_id,
            tran.inventory_item_id,
            cict.item_number,
            cict.description,
            cict.primary_uom_code,
            round(item_cost, 2) item_cost,
            onhand,
            round(onhand * item_cost, 2) amount,
            lastdate,
            planning_make_buy_code
      from (select sum(primary_transaction_quantity) onhand,
                     inventory_item_id,
                     organization_id
               from mtl_onhand_quantities_detail
               where organization_id = 1
               group by inventory_item_id, organization_id) moq,

            (select max(transaction_date) lastDate,
                     inventory_item_id,
                     organization_id
               from inv.mtl_material_transactions
               where transaction_type_id not in (10008, 2, 90, 24)
                  and organization_id = 1
               group by inventory_item_id, organization_id) tran,
            (select inventory_item_id,
                     item_number,
                     description,
                     primary_uom_code,
                     planning_make_buy_code,
                     max(item_cost) item_cost
               from CST_ITEM_COST_TYPE_V
               where (organization_id in (1, 2, 3)
                  and cost_type_id = 1 or organization_id =4
                  and cost_type_id = 2)
                  and item_cost > 0
               group by inventory_item_id,
                        item_number,
                        description,
                        primary_uom_code,
                        planning_make_buy_code) cict

      where tran.inventory_item_id = moq.inventory_item_id
         and tran.organization_id = moq.organization_id
         and cict.inventory_item_id(+) = moq.inventory_item_id
         and lastdate < to_date('2011-01-01', 'yyyy-mm-dd')

      order by lastdate, item_number

      )
group by organization_id, to_char(lastdate, 'yyyy')
order by organization_id, to_char(lastdate, 'yyyy')

查历史库存不能使用MTL_ONHAND_QUANTITIES之类的表，只能做核对工作，语句核心是transaction_type_id not in (10008, 2, 90, 24)，要排除这几种事务类型。留下的都是影响库存数量的事务。
注意的是当历史上事务处理出现异常时，最后计算出来的现有量会与MTL_ONHAND_QUANTITIES表中不一致，可以用当前现有量核对不一致的物料。使用这个语句
select * from (select sum(primary_quantity) qty,
                  inventory_item_id,
                  organization_id
            from inv.mtl_material_transactions
            where transaction_type_id not in(10008,2,90,24)
            and organization_id = 2
            group by inventory_item_id, organization_id) tran,
         (select organization_id,
                  inventory_item_id,
                  sum(on_hand) quantity
            from MTL_ONHAND_TOTAL_V where  organization_id = 2
            group by organization_id, inventory_item_id) Mot
            where tran.inventory_item_id=mot.inventory_item_id
            and tran.organization_id=mot.organization_id
            and tran.qty<>mot.quantity
。查询可能会慢些，但是能接受。