# Oracle EBS Item Import Script Generator: MTL_SYSTEM_ITEMS_INTERFACE and INCOIN

> Source: https://netray.co/tools/oracle-ebs-item-import-interface-script-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generates interface inserts for a master and child organization, the Import Items (INCOIN) submission, error SQL against MTL_INTERFACE_ERRORS and a check query for created items.

## Category

developer

## Erp

Oracle E-Business Suite

## Audience

- Oracle Apps technical consultant
- Inventory functional consultant
- Data migration lead

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Master organization code

**Key:** master_org

**Type:** text

**Default:** V1

#### Child organization code

**Key:** child_org

**Type:** text

**Default:** M1

**Help:** Delete the child insert if the item is only needed in the master.

#### Item number (SEGMENT1)

**Key:** item

**Type:** text

**Default:** XX-BRKT-1001

#### Description

**Key:** desc

**Type:** text

**Default:** Mounting bracket, 6061-T6, anodised

#### Primary UOM code

**Key:** uom

**Type:** text

**Default:** Ea

#### Item template name

**Key:** template

**Type:** text

**Default:** Purchased Item

#### Item type (lookup code)

**Key:** item_type

**Type:** text

**Default:** P

**Help:** Lookup ITEM_TYPE, for example P purchased item, FG finished good.

#### Transaction type

**Key:** txn

**Type:** select

**Options:**

- CREATE
- UPDATE

**Default:** CREATE

#### SET_PROCESS_ID

**Key:** set_id

**Type:** number

**Default:** 90001

#### USER_ID

**Key:** user_id

**Type:** number

**Default:** 1318

#### RESPONSIBILITY_ID

**Key:** resp_id

**Type:** number

**Default:** 50583

**Help:** An Inventory responsibility with access to the master organization.

#### Responsibility APPLICATION_ID

**Key:** resp_appl_id

**Type:** number

**Default:** 401

**Help:** 401 is Inventory (INV).

**Blocks:**

#### Block 1: insert interface rows (master first, then child)

**Body:** 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, {{set_id}}, '{{txn}}', '{{master_org}}',
   '{{item}}', '{{desc}}', '{{uom}}', '{{template}}', '{{item_type}}',
   {{user_id}}, SYSDATE, {{user_id}}, 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, {{set_id}}, 'CREATE', '{{child_org}}', '{{item}}',
   {{user_id}}, SYSDATE, {{user_id}}, SYSDATE);

COMMIT;

#### Block 2: confirm INCOIN parameters, then submit Import Items

**Body:** -- 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({{user_id}}, {{resp_id}}, {{resp_appl_id}});

  SELECT organization_id INTO l_org_id
  FROM   mtl_parameters
  WHERE  organization_code = '{{master_org}}';

  -- 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 {{set_id}}',
                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   => '{{set_id}}',       -- Process set (verify)
                argument7   => CASE '{{txn}}' 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;
/

#### Block 3: errors for this set

**Body:** 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 = {{set_id}}
AND    msii.process_flag   = 3
ORDER  BY msii.segment1, msii.organization_code;

#### Block 4: check the created item, and the API alternative

**Body:** 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 = '{{item}}'
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;

**Notes:** Verify: the INCOIN parameter order with the $SRS$ query (it changed across releases), UOM code case (Ea vs EA) in MTL_UNITS_OF_MEASURE, the template name in MTL_ITEM_TEMPLATES, and that the responsibility can access the master organization. Rows left at process_flag 3 are not reprocessed until you fix and reset them to 1.

## Content

**Intro:** This generator writes a complete Oracle E-Business Suite item load: inserts into MTL_SYSTEM_ITEMS_INTERFACE for the master and a child organization, the Import Items (INCOIN) submission, the error query against MTL_INTERFACE_ERRORS and a check of MTL_SYSTEM_ITEMS_B. It is written for EBS 12.1 and 12.2 Inventory.

**Sections:**

#### How to use it

**Body:** Enter the master and child organization codes, item number, description, primary UOM code, item template name and item type, and pick CREATE or UPDATE. Choose a SET_PROCESS_ID that no other load is using so you can run, inspect and purge your batch alone. Run Block 1, then the parameter query in Block 2 to confirm the INCOIN argument order on your release, adjust if needed, and submit. When the request completes, run Block 3 for errors and Block 4 to see the item in each organization. For a mass load, generate the inserts from a spreadsheet with the same column list.

**Bullets:**

- Master row must import before or with child rows
- Use one SET_PROCESS_ID per batch
- Run Import Items from a responsibility with access to the master organization

#### How Item Import processes rows

**Body:** PROCESS_FLAG drives the interface: 1 pending, 2 assigned, 3 error, 4 validated, 7 processed. Import Items picks rows with process_flag 1 for the set, resolves organization_code to organization_id, applies the template (template_name or template_id), defaults the remaining attributes, validates and inserts into MTL_SYSTEM_ITEMS_B and the translation table. A child organization row creates the assignment; master-controlled attributes are copied from the master, so you only supply org-controlled ones. Errors are written to MTL_INTERFACE_ERRORS keyed by TRANSACTION_ID, with the failing column and message. With delete processed rows set, successful rows are removed from the interface after import.

#### Worked example and reading errors

**Body:** With the defaults, XX-BRKT-1001 is created in V1 with the Purchased Item template and assigned to M1 in the same set 90001. A typical first-run error is an invalid UOM, because codes are case sensitive (Ea versus EA), or a template name that does not exist in MTL_ITEM_TEMPLATES. Block 3 shows the column name and message for every failed row. Fix the data, then either reset process_flag to 1 and clear the related MTL_INTERFACE_ERRORS rows, or delete and reinsert. Block 4 should then return one row per organization with the same INVENTORY_ITEM_ID.

#### Gotchas

**Body:** UPDATE rows need the item to exist in that organization, and with templates the update can overwrite attributes you did not intend to change, so supply only what changes. Large loads benefit from splitting into several sets and gathering statistics on the interface before import. If your instance uses Product Information Management (EGO), check whether new item request or change management rules apply before bypassing them with the interface. For single items from an integration, EGO_ITEM_PUB.PROCESS_ITEM is the supported API; list its signature on your instance first.

**Cta:** If you want item requests checked against your EBS templates and staged into the interface for a planner to approve, on your own servers, we can set that up.

**Faq:**

#### What is the item interface table in Oracle EBS?

**Answer:** MTL_SYSTEM_ITEMS_INTERFACE. You insert rows with process_flag 1, a set_process_id and transaction_type CREATE or UPDATE, then run Import Items (INCOIN). Errors go to MTL_INTERFACE_ERRORS, and the created items land in MTL_SYSTEM_ITEMS_B. Revisions use MTL_ITEM_REVISIONS_INTERFACE and categories MTL_ITEM_CATEGORIES_INTERFACE.

#### How do I mass create items in Oracle EBS?

**Answer:** Load MTL_SYSTEM_ITEMS_INTERFACE with one row per item per organization, master rows plus child assignments, under one set_process_id, and run Import Items for that set. Use an item template to default most attributes so each row only needs item number, description, UOM and template.

#### What do the process_flag values mean?

**Answer:** 1 pending, 2 assigned, 3 error, 4 validated, 7 processed successfully. Rows stuck at 3 have entries in MTL_INTERFACE_ERRORS for the same transaction_id. Rows stuck at 2 or 4 usually mean the import request ended abnormally and the set should be investigated before resubmitting.

#### Where are item templates stored?

**Answer:** Templates are in MTL_ITEM_TEMPLATES (name and ID) with their attribute values in MTL_ITEM_TEMPL_ATTRIBUTES. In the interface you can pass template_name or template_id. Applying a template at import sets the enabled attributes it contains; attributes you supply on the row override the template.

## Search terms

oracle ebs item interface table, mtl_system_items_interface, oracle mass item create ebs inventory, how to create item in oracle ebs, incoin import items, mtl_interface_errors query, oracle ebs inventory api, oracle ebs item template table

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json