物料失效验证的待定事务

最近一个项目MTL_SYSTEM_ITEMS_B表有70,000,000条记录。 用API inv_item_grp.update_item失效起来巨慢，平均2分钟一个物料。

为此看了看oracle验证的东西，想直接把他验证的事务处理处理完毕后直接更新表或者更改标准API，去掉更新验证的东西，让失效能很快的达到目的。

通过研究，在处理完待定事物处理之后，以下是2个快速失效方案：

1. 直接更新MTL_SYSTEM_ITEMS_B，    确定可行率：80%   ，需更新11个字段， 效率：最快捷

2. 更改INVUPD2B.update_validations，在begin后添加代码 return 0;， 确定可行率：99%， 效率： 较为快捷

详细的研究过程就不发了，复制到CSDN超级困难。

发一下oracle对于失效检测的待定事务处理(版本R12.1.3)：

--前台后台都判断，参见inv_attribute_control_pvt.transactable_uncheck

SELECT COUNT(1)

 INTO l_org_count

 FROM wsh_delivery_details

 WHERE inventory_item_id = p_item_id

  AND pickable_flag = 'Y'

     --Bug 4643978 - Perf fix

  AND inv_interfaced_flag IN ('N', 'P')

     --and source_code = 'OE'

  AND released_status <> 'D'

  AND organization_id = p_org_id

  AND rownum = 1;

SELECT COUNT(1)

 INTO l_org_count

 FROM oe_order_lines_all

 WHERE source_type_code = 'EXTERNAL'

  AND open_flag = 'Y'

  AND nvl(shipped_quantity, 0) = 0

  AND item_type_code IN ('MODEL', 'STANDARD', 'OPTION')

  AND flow_status_code = 'AWAITING_RECEIPT'

  AND inventory_item_id = p_item_id

  AND ship_from_org_id = p_org_id

  AND rownum = 1;

SELECT COUNT(1)

 INTO l_org_count

 FROM oe_order_lines_all l

 WHERE booked_flag = 'Y'

  AND nvl(shipped_quantity, 0) = 0

  AND inventory_item_id = p_item_id

  AND open_flag = 'Y'

  AND ship_from_org_id = p_org_id

  AND EXISTS (SELECT 1

         FROM mtl_transactions_interface

        WHERE trx_source_line_id = l.line_id

          AND transaction_source_type_id IN (2, 8)

          AND source_code = 'ORDER ENTRY')

  AND rownum = 1;

SELECT COUNT(1)

 INTO l_org_count

 FROM mtl_supply

 WHERE item_id = p_item_id

  AND to_organization_id = p_org_id

  AND rownum = 1;

 

 

--仅仅前台，参见INVIDIT3.table_queries

mtl_onhand_quantities_detail

mtl_material_transactions_temp

mtl_demand

 

 

 

 

--前后台都检查，参见INV_ATTRIBUTE_CONTROL_PVT.check_pending_adjustments

SELECT COUNT(1)

 INTO l_org_count

 FROM mtl_cycle_count_entries

 WHERE inventory_item_id = p_item_id

  AND organization_id = p_org_id

  AND

     /*Change for bug 8208397*/ /*Check should be for status1-Uncounted/2-Pending Approval/3-Recount*/

     /*entry_status_code = 2 and*/

      entry_status_code IN (1, 2, 3)

  AND rownum = 1;

 

IF (l_org_count <> 1) THEN

 SELECT COUNT(1)

   INTO l_org_count

   FROM mtl_physical_adjustments

  WHERE inventory_item_id = p_item_id

    AND organization_id = p_org_id

    AND approval_status = 1

    AND adjustment_quantity <> 0

    AND rownum = 1;

END IF;

 

 

--INVUPD2B.update_validations的5171，失效也检查这个

SELECT 'Y'

 INTO l_vmiorconsign_flag

 FROM po_approved_supplier_list pasl,

      po_asl_attributes         paa,

      po_asl_status_rules       pasr

 WHERE pasl.item_id = p_item_id

  AND pasl.using_organization_id IN (-1, p_organization_id)

  AND pasl.asl_id = paa.asl_id

  AND pasr.business_rule = '2_SOURCING'

  AND pasr.allow_action_flag = 'Y'

  AND pasr.status_id = pasl.asl_status_id

  AND (disable_flag IS NULL OR disable_flag = 'N')

  AND paa.using_organization_id =

      (SELECT MAX(paa2.using_organization_id)

         FROM po_asl_attributes paa2

        WHERE paa2.asl_id = pasl.asl_id

          AND paa2.using_organization_id IN (-1, p_organization_id))

  AND (paa.consigned_from_supplier_flag = 'Y' OR paa.enable_vmi_flag ='Y')

  AND rownum = 1;

 

bom_item_type 不能等于 5