11 Jan 11 Creating Maintenance Activities with MTL_SYSTEM_ITEMS_INTERFACE

We create custom table for inserting datas into the mtl_system_items_iterface table. I create an excel sheet and it has same columns with “xx_item_create” table. Then for inserting data into the XX_ITEM_CREATE table, I use the program “Toad“. The path is Database > Import > Import Table Data. You can insert the datas from excel to database table using this Import Table Data function. (Don’t use this function for the standard oracle tables!!!)

[sql]CREATE TABLE APPS.XX_ITEM_CREATE(
ORG_CODE VARCHAR2(3 BYTE),
ACTIVITY_CODE VARCHAR2(40 BYTE),
ACTIVITY_DESCRIPTION VARCHAR2(240 BYTE),
TEMPLATE VARCHAR2(30 BYTE),
PRIMARY_UOM_CODE VARCHAR2(25 BYTE),
EAM_ACTIVITY_TYPE_CODE VARCHAR2(30 BYTE),
EAM_ACTIVITY_SOURCE_CODE VARCHAR2(30 BYTE),
EAM_ACTIVITY_CAUSE_CODE VARCHAR2(30 BYTE),
EAM_ACT_SHUTDOWN_STATUS VARCHAR2(30 BYTE),
ITEM_CATEGORY VARCHAR2(240 BYTE)) ;[/sql]

Then we insert the data into the mtl_system_items_iterface using above table.

[sql]INSERT INTO inv.mtl_system_items_interface msii
(MSII.ORGANIZATION_CODE,
MSII.SET_PROCESS_ID,
MSII.TRANSACTION_TYPE,
MSII.PROCESS_FLAG,
MSII.SEGMENT1,
MSII.DESCRIPTION,
MSII.TEMPLATE_ID,
MSII.PRIMARY_UOM_CODE,
MSII.EAM_ACTIVITY_CAUSE_CODE,
MSII.EAM_ACTIVITY_TYPE_CODE,
MSII.EAM_ACTIVITY_SOURCE_CODE,
MSII.EAM_ACT_SHUTDOWN_STATUS,
MSII.CREATE_SUPPLY_FLAG,
MSII.TRACKING_QUANTITY_IND,
MSII.ONT_PRICING_QTY_SOURCE,
MSII.DUAL_UOM_DEVIATION_HIGH,
MSII.DUAL_UOM_DEVIATION_LOW)
(SELECT xxinn.org_code organization_code,
123456 set_process_id,
‘CREATE’ transaction_type,
1 process_flag,
xxinn.ACTIVITY_CODE segment1,
xxinn.ACTIVITY_DESCRIPTION DESCRIPTION,
19 TEMPLATE_ID,–@Activity template id
XXINN.PRIMARY_UOM_CODE PRIMARY_UNIT_OF_MEASURE,
FLV.LOOKUP_CODE ACTIVITY_CAUSE_CODE,
FLV1.LOOKUP_CODE ACTIVITY_TYPE_CODE,
FLV2.LOOKUP_CODE ACTIVITY_SOURCE_CODE,
DECODE (xxinn.EAM_ACT_SHUTDOWN_STATUS,
‘Gerekli Değil’, 1, ‘Gerekli’, 2, NULL) EAM_ACT_SHUTDOWN_STATUS,
‘Y’ CREATE_SUPPLY_FLAG,
‘P’ TRACKING_QUANTITY_IND,
‘P’ ONT_PRICING_QTY_SOURCE,
0,
0
FROM APPS.XX_ITEM_CREATE XXINN,
applsys.fnd_lookup_values flv,
applsys.fnd_lookup_values flv1,
applsys.fnd_lookup_values flv2
WHERE 1 = 1
AND flv.lookup_type(+) = ‘MTL_EAM_ACTIVITY_CAUSE’
AND XXINN.EAM_ACTIVITY_CAUSE_CODE = flv.meaning(+)
AND flv.LANGUAGE(+) = ‘TR’
AND flv1.lookup_type(+) = ‘MTL_EAM_ACTIVITY_TYPE’
AND XXINN. EAM_ACTIVITY_TYPE_CODE = flv1.meaning(+)
AND flv1.LANGUAGE(+) = ‘TR’
AND flv2.lookup_type(+) = ‘MTL_EAM_ACTIVITY_SOURCE’
AND XXINN.EAM_ACTIVITY_SOURCE_CODE = flv2.meaning(+)
AND flv2.LANGUAGE(+) = ‘TR’);
[/sql]

Then I run the request ‘Import Items‘. It’s in Enterprise Asset Management responsibility. The path is Interfaces > Import Items. Don’t forget the insert datas for the master organization if you use multi org!!! If you don’t assign a cost category for activities at organization level, you can probably face with a cost error. I use mtl_item_categories_interface for these category assignments.

Here is the code.

[sql]INSERT INTO inv.mtl_item_categories_interface mici(
MICI.ITEM_NUMBER,
MICI.ORGANIZATION_CODE,
MICI.TRANSACTION_TYPE,
MICI.CATEGORY_SET_ID,
MICI.CATEGORY_ID,
MICI.PROCESS_FLAG,
MICI.SET_PROCESS_ID)
(SELECT XXINN.ACTIVITY_CODE ITEM_NUMBER,
XXINN.ORG_CODE ORGANIZATION_CODE,
‘CREATE’ TRANSACTION_TYPE,
‘1100000042’ CATEGORY_SET_ID, –Category set id which you want to assign
‘2124’ CATEGORY_ID, —TANIMSIZ-TANIMSIZ-TANIMSIZ category which you want to assign
‘1’ PROCESS_FLAG,
123456 SET_PROCESS_ID
FROM APPS.XX_ITEM_CREATE XXINN);
[/sql]

After the inserting process I run the “Import Item Category Assignments” request. You can find this request in an inventory responsibility. The path is Items > Import > Import Item Category Assignments.