Oracle eBS經驗分享

DECLARE
   p_organization_id   mtl_system_items_interface.organization_id%TYPE   := 107;
   p_user_id           fnd_user.user_id%TYPE;
   p_set_process_id    mtl_system_items_interface.set_process_id%TYPE    := 2000;

   CURSOR cur IS
      SELECT inventory_item_id
        FROM mtl_system_items_b
       WHERE organization_id = p_organization_id AND segment1 = 'MBR10150';
BEGIN
   SELECT user_id
     INTO p_user_id
     FROM fnd_user
    WHERE user_name = 'TTTT';

   FOR rec IN cur LOOP
      INSERT INTO mtl_system_items_interface
                  (
                   process_flag , set_process_id
                 , transaction_type, inventory_item_id
                 , organization_id , inventory_item_status_code
                 , end_date_active  , creation_date
                 , created_by   , last_update_date
                 , last_updated_by , last_update_login
                 , check_shortages_flag  , mrp_planning_code
                 , atp_flag, atp_components_flag
                  )
           VALUES (
                   1 , p_set_process_id
                 , 'UPDATE'  rec.inventory_item_id
                 , p_organization_id  , 'Inactive'
                 , SYSDATE, SYSDATE
                 , p_user_id   , SYSDATE
                 , p_user_id   , -1
                 , 'N'  , 6
                 , 'N'  'N'
                  );
   END LOOP;

   COMMIT;
END;