MTL_ONHAND_QUANTITIES中的数据的来龙去脉？

chendianxi

这两个表不是一回事？你确定？

wfly2004

前边菠菜 说得是对的,问题是有没有这方面的权威资料啊,或者给个TRIGGER之类的,看看它的数据

jacobliang888

原帖由 smilefish 于 2006-12-4 08:13 发表
入库为正，出库为负，然后累加

我是海坭

关于这俩表，我最近也研究过
其实MTL_onhand_quantity_detail是在11.5.6以后才出现的(确切的说是在Oracle Applications Discrete Manufacturing Family Pack G
Section 2.1:Features added for this family pack Oracle Inventory (INV).以下是官方的解释：
The MTL_ONHAND_QUANTITIES table has been replaced with the new  MTL_ONHAND_QUANTITIES_DETAIL tableand a view has instead been created with            MTL_ONHAND_QUANTITIES on top of the new table. These changes were made for future projects in the area of Consigned Inventory and Vendor Managed Inventory to store more information about the onhand inventory based on who is responsible to plan/replenish it, who owns it and where  it physically belongs.
Right now no logic has been built on thesecolumns and only default values are being used for these columns. For the organization_id columns, we are populating the same organization_id column and for the type of organization columns, we are populating the default value of 2.  

       Also earlier the QUANTITY stored in the table was the primary quantity  and now that is stored in the
           PRIMARY_QUANTITY column and by default it  is also stored in the TRANSACTION_QUANTITY column  with the  PRIMARY_UOM_CODE from MTL_SYSTEM_ITEMS stored in the TRANSACTION_UOM_CODEcolumn.  

           These changes should be transparent to the end user unless some DDL  operations were being done directly on MTL_ONHAND_QUANTITIES table by  the customer.

sunbath_sun

mtl_onhand_quantities 是 mtl_onhand_quantities_detail 的一个视图，只是多这了么一个条件

FROM　　 mtl_onhand_quantities_detail moqd
WHERE　 moqd.is_consigned = 2;

不知这是个什么意思？？？库存现有量到底应该用哪一个表呢？？？在TRM中没有找到 mtl_onhand_quantities_detail 相关说明，只有 mtl_onhand_quantities 的

另外，在相同组织，相同物料，相同子库存的情况下：sum(mtl_onhand_quantities_detail.primary_quantity) 应该等于 sum(mtl_material_transactions.primary_quantity) 对么，可是我们公司的啥就不等呢？？？

这个表我做了两星期了，卡死了。。。
　

gaojian2008cnn

1. CREATE OR REPLACE VIEW ADD_INV_ONHAND_DATE_V_ORG2 AS
   
2. SELECT /*+ RULE */
   
3.        v.org_id,
   
4.        v.transaction_date AS inv_date,
   
5.        v.subinventory_code AS sub_inv,
   
6.        m.segment1,
   
7.        m.primary_uom_code AS uom,
   
8.        v.transaction_quantity AS oh_qty,
   
9.        m.description,
   
10.        m.ITEM_TYPE,
    
11.        V.inventory_item_id inventory_item_id
    
12.   FROM (SELECT moq.organization_id AS org_id,
    
13.                (SYSDATE + 1) AS transaction_date,
    
14.                moq.subinventory_code AS subinventory_code,
    
15.                moq.inventory_item_id AS inventory_item_id,
    
16.                moq.revision AS revision,
    
17.                moq.transaction_quantity,
    
18.                                  '' amount
    
19.           FROM apps.mtl_onhand_quantities moq
    
20.         WHERE  moq.organization_id = '102'
    
21.         UNION ALL
    
22.         SELECT MMT.organization_id org_id,
    
23.                      MMT.transaction_date transaction_date,
    
24.                MMT.subinventory_code subinventory_code,
    
25.                MMT.inventory_item_id inventory_item_id,
    
26.                MMT.revision revision,
    
27.                MMT.transaction_quantity * -1 transaction_quantity,
    
28.                '' amount
    
29.           FROM mtl_material_transactions mmt
    
30.          WHERE mmt.organization_id = '102'
    
31.        ) v,
    
32.        inv.mtl_system_items_b m
    
33. WHERE  v.inventory_item_id = m.inventory_item_id
    
34.    AND v.org_id = m.organization_id;

复制代码

mtl_onhand_quantities是此时此刻的库存数量，mtl_material_transactions是历史出入库记录。