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
。查询可能会慢些，但是能接受。