Group Team
Whenever item is created, default Revision will be assigned to item.

We can also modify the item Revision of an item.

Base table for Item Revision is Mtl_Item_Revisions.

To Modify the item revision, we need to insert new record in Mtl_item_revisions_interface.

Following are the important columns in Mtl_Item_Revisions_Interface

a. Inventory_item_id
b. Organization_id
c. Revision
d. Process_Flag
e. set_process_id
f. Transaction_type

Below code helps you to unserstand how to create a Item Revision.

Code to Import Item Revision
-------------------------------------

DECLARE
l_error                              VARCHAR2(1000) :=NULL;
l_error_message               VARCHAR2(1000) :=NULL;
v_flag                               NUMBER :=0;
v_date                              DATE := SYSDATE;
v_user_id                         fnd_user.user_id%TYPE := FND_PROFILE.VALUE('USER_ID');
v_request_id                    NUMBER :=0;
v_rev_org_id                   NUMBER :=0;
v_rev_item_id                  NUMBER :=0;
l_request_id                     NUMBER :=0;
v_revision                        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
      BEGIN
---------Getting Organization Id for Item Revision----------------
             BEGIN
                    SELECT organization_id
                    INTO v_rev_org_id
                    FROM mtl_parameters
                    WHERE organization_code = c_item_revision_rec.organization_code;
             EXCEPTION
                   WHEN OTHERS THEN
                          l_error := c_item_revision_rec.organization_code' does not exist for the item ''Item Name';
                          l_error_message := l_error_messagel_error ;
                         v_flag :=1;
                         dbms_output.put_line( l_error);
              END;
------Checking whether Item exists in Oracl 11i for Item revision ---------------
              BEGIN
                     SELECT inventory_item_id
                     INTO v_rev_item_id
                     FROM mtl_system_items_b msi,
                                 mtl_parameters mp
                     WHERE 1 = 1
                     AND msi.organization_id = mp.organization_id
                     AND msi.segment1 = 'Item_Name'
                     AND mp.organization_code = 'Org_Code';
               EXCEPTION
                     WHEN OTHERS THEN
                            l_error := 'Item Name''--''Item Does not exist in Oracle 11i';
                            l_error_message := l_error_messagel_error ;
                           v_flag :=1;
                          dbms_output.put_line( l_error);
               END;
------Getting Item count inserted in the interface table mainly used for Synchronizng the Effectivity date----
              BEGIN
                      SELECT count(1)
                      INTO v_count
                      FROM MTL_ITEM_REVISIONS
                      WHERE inventory_item_id = v_rev_item_id;
              EXCEPTION
                    WHEN OTHERS THEN
                              dbms_output.put_line( 'Error in counting the Item revision available in Interface');
                              v_flag :=1;
              END;

             IF v_flag = 0 THEN
                    BEGIN
---------Inserting into Revision Table---------
                          v_revision := (0.003472 * ( 1 + v_count ));

                         INSERT INTO MTL_ITEM_REVISIONS_INTERFACE
                                                 ( INVENTORY_ITEM_ID
                                                  ,ORGANIZATION_ID
                                                  ,REVISION
                                                  ,LAST_UPDATE_DATE
                                                  ,LAST_UPDATED_BY
                                                  ,CREATION_DATE
                                                  ,CREATED_BY
                                                  ,IMPLEMENTATION_DATE
                                                  ,EFFECTIVITY_DATE
                                                  ,PROCESS_FLAG
                                                  ,TRANSACTION_TYPE
                                                  ,SET_PROCESS_ID
                                                 )
                                     VALUES
                                                 ( v_rev_item_id
                                                  ,v_rev_org_id
                                                  ,'Revision Number'
                                                  ,v_date
                                                  ,v_user_id
                                                  ,v_date
                                                  ,v_user_id
                                                  ,(SYSDATE+v_revision)
                                                  ,(SYSDATE+v_revision)
                                                  ,1 --PROCESS_FLAG(1 for pending)
                                                  ,'CREATE'
                                                  ,1 --SET_PROCESS_ID
                                                  );
                                           COMMIT;

                    EXCEPTION
                          WHEN OTHERS THEN
                                   l_error := 'Error in inserting Item Revision to Interface table ''and error is  'SQLERRM;
                                  l_error_message := l_error_messagel_error;
                                 dbms_output.put_line( l_error);
                    END;
             ELSE
                       dbms_output.put_line( 'Program failed in Initial validation ');
             END IF; --End of If l_flag=0

      EXCEPTION
           WHEN OTHERS THEN
                dbms_output.put_line( 'No Revision Exists for any Item');
       END;

-- Calling Item Import Program for Revision----
      BEGIN
             dbms_output.put_line('--Submitting Item Import Program for assigning revision to the Item--');
              l_request_id := apps.Fnd_Request.submit_request ( 'INV',
                                                                                             'INCOIN',
                                                                                              NULL, -- Description
                                                                                              NULL, -- Start Time
                                                                                              FALSE, -- Sub Request
                                                                                              1, -- All Organizations
                                                                                              1, -- Validate Items,
                                                                                              1, -- Process Items
                                                                                              1, -- Delete Process Rows
                                                                                              1, -- Process Set
                                                                                              1 ); -- CREATE

               COMMIT;
               DBMS_output.Put_line('Item Import Program submitted');

              IF ( l_request_id = 0 ) THEN
                      dbms_output.put_line( 'Submission of Import failed');
             END IF;

-- Wait for request to run the import Program to Finish
             v_finished := fnd_concurrent.wait_for_request (request_id => l_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('Item Import Program for Revision is Waiting');
          DBMS_output.Put_line('Request Phase : ' v_request_phase);
          DBMS_output.Put_line('Request Status : ' v_request_status );
          DBMS_output.Put_line('Request ID : ' l_request_id );


         IF ( UPPER(v_request_status) = 'NORMAL') THEN
               DBMS_output.Put_line('Item Import Program completed succesfully');
         ELSE
                 DBMS_output.Put_line('Item Import Program completed with error. Check Mtl_interface_errors table for Knowing about the error');
         END IF;

      EXCEPTION
            WHEN OTHERS THEN
                   DBMS_output.Put_line('Error Occured during calling Item Import Program for Revision. 'SQLERRM);
      END;

commit;
dbms_output.put_line('End of Revision');

EXCEPTION
      WHEN OTHERS THEN
                dbms_output.put_line( 'Error in Item Revision Program and error is :'SQLERRM);
END;
Group Team
API EGO_ITEM_PUB.Process_Items can be used to create or Update Items.

In this we will see how to create a new Item 'NEWITEM' using an API.

Below code can be used to create it.


SET SERVEROUTPUT ON
DECLARE
l_item_table EGO_Item_PUB.Item_Tbl_Type;
x_item_table EGO_Item_PUB.Item_Tbl_Type;
x_return_status VARCHAR2(1);
x_msg_count NUMBER(10);
x_msg_data VARCHAR2(1000);
x_message_list Error_Handler.Error_Tbl_Type;
BEGIN

--Apps Initialization

FND_GLOBAL.APPS_INITIALIZE(USER_ID=>&userid,RESP_ID=>&RESP_ID,RESP_APPL_ID=>&RESP_APPL_ID);

--FIRST Item definition
l_item_table(1).Transaction_Type := 'CREATE'; -- Replace this with 'UPDATE' for update transaction.
l_item_table(1).Segment1 := 'NEWITEM';
l_item_table(1).Description := 'NEWITEM';
l_item_table(1).Organization_Code := '&masterorg';
l_item_table(1).Template_Name := '&template';

DBMS_OUTPUT.PUT_LINE('Calling API to Create Item');

EGO_ITEM_PUB.Process_Items(
p_api_version => 1.0
,p_init_msg_list => FND_API.g_TRUE
,p_commit => FND_API.g_TRUE
,p_Item_Tbl => l_item_table
,x_Item_Tbl => x_item_table
,x_return_status => x_return_status
,x_msg_count => x_msg_count);


DBMS_OUTPUT.PUT_LINE('Return Status ==>'x_return_status);

IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
FOR i IN 1..x_item_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Inventory Item Id Created:'to_char(x_item_table(i).Inventory_Item_Id));
DBMS_OUTPUT.PUT_LINE('Organization Id :'to_char(x_item_table(i).Organization_Id));
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('Error Messages :');
Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
FOR i IN 1..x_message_list.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(x_message_list(i).message_text);
END LOOP;
END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error has Occured and error is 'SUBSTR(SQLERRM,1,200));
END;

Debugging Errors
---------------------------

Suppose if any error has occured during the process, we can check the details of the error using Mtl_system_items_interface and Mtl_interace_errors table.

If any error occured, then Process_flag value of the record in the Mtl_system_items_interface will be 3. Also for this record transaction_id will be populated.

Using the transaction_id we can check the error in Mtl_interface_error table and get the exact error_mesage on which the record is failing.

Use the below script to debug error in case item 'NEWITEM' is not created.


SELECT msii.segment1,msii.description,msii.process_flag,msii.transaction_id,mie.error_message
FROM mtl_system_items_interface msii,
mtl_interface_errors mie
WHERE msii.transaction_id = mie.transaction_id
AND msii.process_flag = 3
AND msii.segment1 = 'NEWITEM';
Labels: 1 comments | edit post
Group Team
API 'EGO_ITEM_PUB.Assign_Item_To_Org' can be used to Assign Item to Child Org.

Below code helps Us to Understand more on the API

declare
l_mesg VARCHAR2(1000);
l_count number;

begin

--Getting the Organization id
BEGIN
SELECT Organization_id,master_organization_id
INTO v_organization_id,v_master_org
FROM mtl_parameters mp
WHERE mp.organization_code = 'C1'; --C1 is the Child 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;

--Getting the Inventory Item id of the Item which is available in Master Organization
SELECT inventory_item_id
INTO v_item_id
from mtl_system_items_b
where segment1 = 'Existing Item Name'
and organization_id = v_master_org;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in getting the inventory item id for Item and error is '||SUBSTR(SQLERRM,1,200));
END;

EGO_ITEM_PUB.Assign_Item_To_Org(
p_api_version => 1.0 ,
,p_Item_Number => v_item_id
,p_Organization_Id => v_organization_id
,x_return_status => l_mesg
,x_msg_count => l_count);

Commit;

IF l_count =1 THEN
dbms_output.put_line( 'Error in API is '|| l_mesg);
ELSIF l_count >1 THEN
LOOP
l_count := l_count+1;
l_mesg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
if l_mesg is NULL Then
EXIT;
END IF;
dbms_output.put_line('Message' || l_count ||' ---'||l_mesg);
END LOOP;
END IF;

Exception
dbms_output.put_line('Error in the API and error is '||SUBSTR(SQLERRM,1,200));
END;
Labels: 1 comments | edit post
Group Team
Below code can be used to assign item from Master org to Child Org

declare
v_organization_id NUMBER := 0;
v_master_org 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);
v_item_id NUMBER := 0;

Begin
--Getting the Organization id
BEGIN
SELECT Organization_id,master_organization_id
INTO v_organization_id,v_master_org
FROM mtl_parameters mp
WHERE mp.organization_code = 'C1'; --C1 is the Child 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;

--Getting the Inventory Item id of the Item which is available in Master Organization
SELECT inventory_item_id
INTO v_item_id
from mtl_system_items_b
where segment1 = 'Existing Item Name'
and organization_id = v_master_org;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in getting the inventory item id for Item and error is '||SUBSTR(SQLERRM,1,200));
END;


--Inserting into Item interface table
BEGIN
INSERT INTO mtl_system_items_interface
(inventory_item_id,
organization_id,
process_flag,
set_process_id,
transaction_type
)
values
( v_item_id,
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;


--Submit the item import program in Create Mode to Assign existing 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;
Group Team
Description of the Item based on Item catalog group can be got by using the below API

invicgds.inv_get_icg_desc
(inv_item_id,
first_elem_break,
use_name_as_first_elem,
description_for_item,
Delimiter,
show_all_delim,
ERROR_TEXT
);

• API accept Inventory item id as the input.
• Based on the inventory item id, it gets the new description from the Item catalog group Screen.
• If any delimiter is specified, API uses the delimiter in the description definition. Else by default it take dot (‘.’) delimiter to get the description.
• If any error is encounter during the call of the API, the information will be available in ERROR_TEXT.
Group Team
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;
Group Team
In simple terms it is a stock of items that your business is selling in order to make a living. Almost every non-IT related business has some physical items to sell and hence Inventory is at the heart of almost any business and is central to the planning process.
The considerations in setting it up are at least as complex as those for any other single application. Commonly, Inventory will be installed during the implementation of Order Entry, Purchasing, Manufacturing, or Supply Chain Management but it is an extremely complex module in its own right with several important setup considerations.
Click here