msgbartop
oracle e-iş yönetim sistemi üretim ve bakım modül danışmanı (oracle e-business suite manufacturing and maintenance consultant)
msgbarbottom

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!!!)

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)) ;

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

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');

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.

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);

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.

Yorum Yaz