Creation of Item using Item Interface

Item can be created by using Item Import Program.

Prerequistic is to insert record in Mtl_System_Items_Interface table.

Following are the important columns in Interface table
a. Segment1 
b. Organization_id
c. set_process_id
d. Process_flag
e. Transaction_type

Segment1 -- It holds the Item Name.
Organization_id -- To which Organization an item belongs to. Normally items will be added to the Master Organization. Then it will be assigned to Inventory Organization.
set_process_id -- Used to speed up the Import Program process.
Process_flag -- Process_flag=1 will be picked by the Item import program. If it is error out due to any diffrence, value of process_flag will be changed to 3.
Transaction_type -- It can be either 'CREATE' or 'UPDATE'


Code to Populate Item Interface Table
---------------------------------------------------------

declare
v_organization_id NUMBER := 0;
Begin
--Getting the Organization id
BEGIN
SELECT Organization_id
INTO v_organization_id 
FROM mtl_parameters mp
WHERE mp.organization_code = 'V1'; --V1 is the Master Organization Code
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in getting the Organization id for Organization code V1 and error is '||SUBSTR(SQLERRM,1,200));
END;

--Inserting into Item interface table
BEGIN
INSERT INTO mtl_system_items_interface
(segment1,
organization_id,
process_flag,
set_process_id,
transaction_type
)
values
('New Item Name',
v_organization_id,
1,
1,
'CREATE'
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in inserting record in interface table and error is '||SUBSTR(SQLERRM,1,200));
END;
END;

Once item is inserted into inteface table, item import program needs to be called to Create item. Following is the code used to call the item import program

Code to Call Item Import Program
--------------------------------------------------

DECLARE
v_organization_id NUMBER := 0;
v_request_id NUMBER := 0;
v_phase VARCHAR2(240);
v_status VARCHAR2(240);
v_request_phase VARCHAR2(240);
v_request_status VARCHAR2(240);
v_finished BOOLEAN;
v_message VARCHAR2(240);
BEGIN

--Submit the item import program in Create Mode to Create New Item
BEGIN

dbms_output.put_line('--Submitting Item Import Program for Item--'); 
v_request_id := Fnd_Request.submit_request (
application => 'INV',
program => 'INCOIN',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 1,
argument2 => 1, 
argument3 => 1, --Group ID option (All)
argument4 => 1, -- Group ID Dummy
argument5 => 1, -- Delete processed Record 
argument6 => 1, -- Set Process id 
argument7 => 1 -- Create item 
);
COMMIT;
dbms_output.put_line('Item Import Program submitted');

IF ( v_request_id = 0 ) THEN 
dbms_output.put_line( 'Item Import Program Not Submitted'); 
END IF;

-- Wait for request to run the import Program to Finish
v_finished := fnd_concurrent.wait_for_request (request_id => v_request_id,
interval => 0,
max_wait => 0,
phase => v_phase,
status => v_status,
dev_phase => v_request_phase,
dev_status => v_request_status,
message => v_message);

dbms_output.put_line('Request Phase : '|| v_request_phase );
dbms_output.put_line('Request Status : ' || v_request_status );
dbms_output.put_line('Request id : '||v_request_id );

--Testing end status
IF ( UPPER(v_request_status) = 'NORMAL') THEN
dbms_output.put_line( 'Item Import Program Completed Normally'); 
ELSE 
dbms_output.put_line( 'Item Import Program completed with error. Check Mtl_interface_error table for the transaction_id'); 
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in Submitting Item Import Program and error is '||SUBSTR(SQLERRM,1,200));
END;
END;