Oracle EBS Item Import Script Generator: MTL_SYSTEM_ITEMS_INTERFACE and INCOIN

INSERT INTO mtl_system_items_interface
  (process_flag, set_process_id, transaction_type, organization_code,
   segment1, description, primary_uom_code, template_name, item_type,
   created_by, creation_date, last_updated_by, last_update_date)
VALUES
  (1, 90001, 'CREATE', 'V1',
   'XX-BRKT-1001', 'Mounting bracket, 6061-T6, anodised', 'Ea', 'Purchased Item', 'P',
   1318, SYSDATE, 1318, SYSDATE);

-- Child organization assignment: master-controlled attributes come from the master
INSERT INTO mtl_system_items_interface
  (process_flag, set_process_id, transaction_type, organization_code, segment1,
   created_by, creation_date, last_updated_by, last_update_date)
VALUES
  (1, 90001, 'CREATE', 'M1', 'XX-BRKT-1001',
   1318, SYSDATE, 1318, SYSDATE);

COMMIT;

-- Parameter order of Import Items on YOUR instance
SELECT u.column_seq_num, u.end_user_column_name, u.form_left_prompt, u.default_value
FROM   fnd_descr_flex_col_usage_vl u
WHERE  u.application_id = 401
AND    u.descriptive_flexfield_name = '$SRS$.INCOIN'
AND    u.enabled_flag = 'Y'
ORDER  BY u.column_seq_num;

SET SERVEROUTPUT ON
DECLARE
  l_org_id NUMBER;
  l_req_id NUMBER;
BEGIN
  FND_GLOBAL.APPS_INITIALIZE(1318, 50583, 401);

  SELECT organization_id INTO l_org_id
  FROM   mtl_parameters
  WHERE  organization_code = 'V1';

  -- Argument order below is the common R12 layout: verify against the query above
  l_req_id := FND_REQUEST.SUBMIT_REQUEST(
                application => 'INV',
                program     => 'INCOIN',
                description => 'Item import set 90001',
                start_time  => NULL,
                sub_request => FALSE,
                argument1   => TO_CHAR(l_org_id),  -- Organization (verify)
                argument2   => '1',                -- All organizations: 1 Yes, 2 No (verify)
                argument3   => '1',                -- Validate items (verify)
                argument4   => '1',                -- Process items (verify)
                argument5   => '1',                -- Delete processed rows (verify)
                argument6   => '90001',       -- Process set (verify)
                argument7   => CASE 'CREATE' WHEN 'CREATE' THEN '1' ELSE '2' END,  -- 1 create, 2 update (verify)
                argument8   => '2');               -- Gather statistics: 1 Yes, 2 No (verify; remove if absent)

  IF l_req_id = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Submit failed: ' || FND_MESSAGE.GET);
    ROLLBACK;
  ELSE
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Import Items request ' || l_req_id);
  END IF;
END;
/

SELECT msii.transaction_id,
       msii.organization_code,
       msii.segment1,
       msii.process_flag,
       mie.column_name,
       mie.message_name,
       mie.error_message
FROM   mtl_system_items_interface msii
JOIN   mtl_interface_errors mie
       ON  mie.transaction_id = msii.transaction_id
       AND mie.table_name     = 'MTL_SYSTEM_ITEMS_INTERFACE'
WHERE  msii.set_process_id = 90001
AND    msii.process_flag   = 3
ORDER  BY msii.segment1, msii.organization_code;

SELECT msi.inventory_item_id, msi.segment1, mp.organization_code,
       msi.description, msi.primary_uom_code, msi.item_type,
       msi.inventory_item_status_code, msi.creation_date
FROM   mtl_system_items_b msi
JOIN   mtl_parameters mp
       ON mp.organization_id = msi.organization_id
WHERE  msi.segment1 = 'XX-BRKT-1001'
ORDER  BY mp.organization_code;

-- Prefer an API for single items? List the EGO_ITEM_PUB.PROCESS_ITEM signature on your instance
-- before writing the call (it has several overloads):
SELECT overload, position, argument_name, data_type, in_out, defaulted
FROM   all_arguments
WHERE  owner = 'APPS'
AND    package_name = 'EGO_ITEM_PUB'
AND    object_name  = 'PROCESS_ITEM'
ORDER  BY overload, position;