Oracle ERP R12. 使用interface批量變更品號的operation code . (Mass changes item's operation code use BOM_OP_SEQUENCES_INTERFACE. )

Sample code :


DECLARE
  --
  T_OLD_OP VARCHAR2(10) := '2T02';  --**** old operation code
  T_NEW_OP VARCHAR2(10) := '2S02'; --**** new operation code
  T_NEW_OP_ID NUMBER;
  T_ORGANIZATION_ID NUMBER := 85; --organization id
  T_DISABLE_DATE DATE := SYSDATE;
  T_EFFECT_DATE DATE := SYSDATE+1/86400;  
  --
  CURSOR C_LIST  --item list ==> user define 
  IS
  SELECT D.ORGANIZATION_ID,
         D.INVENTORY_ITEM_ID,
         A.ROUTING_SEQUENCE_ID,
         B.OPERATION_SEQUENCE_ID,
         B.OPERATION_SEQ_NUM,
         C.STANDARD_OPERATION_ID
    FROM BOM_OPERATIONAL_ROUTINGS A
    JOIN BOM_OPERATION_SEQUENCES_V B--BOM_OPERATION_SEQUENCES B
      ON A.COMMON_ROUTING_SEQUENCE_ID = B.ROUTING_SEQUENCE_ID
    LEFT JOIN BOM_STANDARD_OPERATIONS C
      ON B.STANDARD_OPERATION_ID = C.STANDARD_OPERATION_ID
    JOIN MTL_SYSTEM_ITEMS_B D
      ON A.ORGANIZATION_ID = D.ORGANIZATION_ID
     AND A.ASSEMBLY_ITEM_ID = D.INVENTORY_ITEM_ID
    JOIN BOM_DEPARTMENTS F
      ON F.DEPARTMENT_ID = B.DEPARTMENT_ID
     AND F.ORGANIZATION_ID = A.ORGANIZATION_ID
   WHERE A.ORGANIZATION_ID = T_ORGANIZATION_ID
     AND C.OPERATION_CODE = T_OLD_OP
     AND (B.DISABLE_DATE IS NULL OR
          B.DISABLE_DATE >= SYSDATE);
BEGIN
  --Find the new operation id
  SELECT BSO.STANDARD_OPERATION_ID
    INTO T_NEW_OP_ID
    FROM BOM_STANDARD_OPERATIONS BSO
   WHERE BSO.ORGANIZATION_ID = T_ORGANIZATION_ID
     AND BSO.OPERATION_CODE = T_NEW_OP;
   
  FOR C1 IN C_LIST LOOP
    --STEP 1. Disable old operation
    BEGIN
      INSERT INTO BOM_OP_SEQUENCES_INTERFACE
      (ASSEMBLY_ITEM_ID,  
       ORGANIZATION_ID,
       ROUTING_SEQUENCE_ID,
       OPERATION_SEQUENCE_ID,
       DISABLE_DATE,
       TRANSACTION_TYPE,
       PROCESS_FLAG,
       BATCH_ID)
      VALUES(C1.INVENTORY_ITEM_ID,
             C1.ORGANIZATION_ID,
             C1.ROUTING_SEQUENCE_ID,
             C1.OPERATION_SEQUENCE_ID,
             T_DISABLE_DATE,
             'Update',
             1,
             1);
    END;
 
    --STEP 2. Add new operation
    BEGIN
      INSERT INTO BOM_OP_SEQUENCES_INTERFACE(
       ASSEMBLY_ITEM_ID,  
       ORGANIZATION_ID,
       ROUTING_SEQUENCE_ID,
       OPERATION_SEQUENCE_ID,
       OPERATION_SEQ_NUM,    
       EFFECTIVITY_DATE,
       STANDARD_OPERATION_ID,
       REFERENCE_FLAG,
       TRANSACTION_TYPE,
       PROCESS_FLAG,
       BATCH_ID)
      VALUES(C1.INVENTORY_ITEM_ID,
             C1.ORGANIZATION_ID,
             C1.ROUTING_SEQUENCE_ID,
             C1.OPERATION_SEQUENCE_ID,
             C1.OPERATION_SEQ_NUM,
             T_EFFECT_DATE,
             T_NEW_OP_ID,
             1,
             'Create',
             1,
             2);
    END;  
  END LOOP;
END;

Step 3. Run BOM & Routing Import (Bill and Routing Interface) ==> batch id is set to 1 (For disable old operation code)

Step 4. Run BOM & Routing Import (Bill and Routing Interface) ==> batch id is set to 2 (For add new operation code)