物料失效验证的待定事务
最近一个项目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