[Oracle EBS] 离散任务报错 APP-BOM-20516 的排查与修复（附后台刷数脚本）

-- 备份示例：将受影响的工序数据备份到以今天日期结尾的临时表中
CREATE TABLE bom_op_seq_bak_20240101 AS
SELECT a.* FROM bom_operation_sequences a
 WHERE a.routing_sequence_id = (SELECT routing_sequence_id 
                                  FROM bom_operational_routings 
                                 WHERE organization_id = 123 
                                   AND assembly_item_id = (SELECT inventory_item_id FROM mtl_system_items_b WHERE segment1 = 'DEMO_ITEM_001' AND organization_id = 123));

SET SERVEROUTPUT ON;

DECLARE
  -- ==========================================
  -- 1. 业务变量配置区 (执行前请替换为实际数据)
  -- ==========================================
  v_org_id    NUMBER := 123;  -- 替换为实际的库存组织 ID
  v_item_name VARCHAR2(100) := 'DEMO_ITEM_001'; -- 替换为报错的装配件编码
  v_date      DATE := TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'); -- 替换为需要提前到的目标日期
  
  v_item_id   NUMBER;

  -- 日志输出程序
  PROCEDURE log_result(p_table VARCHAR2, p_count NUMBER) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('   > 表 ' || RPAD(p_table, 25, ' ') || ': 成功匹配并更新 ' || p_count || ' 行数据');
  END;

BEGIN
  -- 获取物料内部 ID
  SELECT inventory_item_id INTO v_item_id 
    FROM mtl_system_items_b 
   WHERE segment1 = v_item_name AND organization_id = v_org_id;

  DBMS_OUTPUT.PUT_LINE('>>> 开始处理物料: ' || v_item_name || ' | 目标生效日期: ' || TO_CHAR(v_date, 'YYYY-MM-DD'));

  -- 第 1 步：更新工艺版本 (关联字段: inventory_item_id)
  UPDATE mtl_rtg_item_revisions 
     SET effectivity_date = v_date, implementation_date = v_date
   WHERE organization_id = v_org_id AND inventory_item_id = v_item_id;
  log_result('MTL_RTG_ITEM_REVISIONS', SQL%ROWCOUNT);

  -- 第 2 步：更新工序 (注意避坑，关联字段: assembly_item_id)
  UPDATE bom_operation_sequences
     SET effectivity_date = v_date, implementation_date = v_date
   WHERE routing_sequence_id = (SELECT routing_sequence_id 
                                  FROM bom_operational_routings 
                                 WHERE organization_id = v_org_id 
                                   AND assembly_item_id = v_item_id);
  log_result('BOM_OPERATION_SEQUENCES', SQL%ROWCOUNT);

  -- 第 3 步：更新 BOM 清单组件 (注意避坑，关联字段: assembly_item_id)
  UPDATE bom_inventory_components
     SET effectivity_date = v_date, implementation_date = v_date
   WHERE bill_sequence_id = (SELECT bill_sequence_id 
                               FROM bom_bill_of_materials 
                              WHERE organization_id = v_org_id 
                                AND assembly_item_id = v_item_id);
  log_result('BOM_INV_COMPONENTS', SQL%ROWCOUNT);

  -- ==========================================
  -- 事务控制 (安全起见，首次测试可改为 ROLLBACK;)
  -- ==========================================
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('>>> 所有日期同步完毕，事务已提交 (COMMIT)。');

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('!!! 发生未知错误，已回滚: ' || SQLERRM);
END;
/