Create Item Catalogs
Interface Tables used:
- MTL_DESC_ELEM_VAL_INTERFACE
Base Tables Affected:
- MTL_ITEM_CATALOG_GROUPS_B
- MTL_DESCR_ELEMENT_VALUES
Prerequisites
- Items need to be created.
For demo purpose we have created 2 Items
- Iphone 6
- Samsung Galaxy Grand Prime
- Catalog and its Elements need to define
- How to Create catalog and assign elements to it
Creating Catalog
(N) Inventory >> Setup >> items >> Catalog Groups
Click on Details button and we need to add the values for the descriptive elements
- Catalog and its Elements should be attached to the Item
(N) inventory >> Items >> Master Items
Query for Iphone 6
Then click on Tools >> Catalog
Search for Phone Specifications catalog in the field Catalog Group
It will automatically Populates the Element Names
Save
Similarly Query for 2nd Item “Samsung Galaxy Grand Prime”
And follow the above steps
General Tips for Running the script which will assign values to Catalogs against the Item:
- Save the script file and then run the script from the SQL Prompt (Example : RUN insertvaluestosatge.sql)
- This script will insert below Values which we need to convert , we need to Insert this into Staging table along with the Item details.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
CREATE TABLE xxaoa_item_catalog_stg ( item_name VARCHAR2(40) ,catalog_name VARCHAR2(40) ,element_sequence NUMBER ,element_name VARCHAR2(30) ,element_value VARCHAR2(30) ,default_element_flag VARCHAR2(1) ,inventory_item_id NUMBER ,item_catalog_group_id NUMBER ,last_update_date DATE ,last_updated_by NUMBER ,creation_date DATE ,created_by NUMBER ,last_update_login NUMBER ,set_process_id NUMBER ,status VARCHAR2(20) ,error_message VARCHAR2(2000) ); --1 INSERT INTO xxaoa_item_catalog_stg ( item_name, catalog_name, element_sequence, element_name ,element_value, default_element_flag ,last_update_date, last_updated_by, creation_date, created_by ,last_update_login, set_process_id ) VALUES ( 'Iphone 6', 'Phone Specifications', 10, 'Operating System' ,SUBSTR ( 'iOS 8, upgradable to iOS 8.1.2', 1, 30 ), 'Y' ,SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id ,fnd_global.login_id, 1 ); --2 INSERT INTO xxaoa_item_catalog_stg ( item_name, catalog_name, element_sequence, element_name ,element_value, default_element_flag, last_update_date ,last_updated_by, creation_date, created_by ,last_update_login, set_process_id ) VALUES ( 'Iphone 6', 'Phone Specifications', 20, 'Internal Memory' ,SUBSTR ( '16/64/128 GB, 1 GB RAM', 1, 30 ), 'Y', SYSDATE ,fnd_global.user_id, SYSDATE, fnd_global.user_id ,fnd_global.login_id, 1 ); --3 INSERT INTO xxaoa_item_catalog_stg ( item_name, catalog_name, element_sequence, element_name ,element_value, default_element_flag, last_update_date ,last_updated_by, creation_date, created_by ,last_update_login, set_process_id ) VALUES ( 'Iphone 6', 'Phone Specifications', 30, 'Expandable Memory','No', 'Y', SYSDATE ,fnd_global.user_id, SYSDATE, fnd_global.user_id ,fnd_global.login_id, 1); --4 INSERT INTO xxaoa_item_catalog_stg ( item_name, catalog_name, element_sequence, element_name ,element_value,default_element_flag, last_update_date, last_updated_by ,creation_date, created_by, last_update_login, set_process_id ) VALUES ( 'Iphone 6', 'Phone Specifications', 40, 'Camera Pixels' ,SUBSTR ( '8 MP, 3264 x 2448 pixels, phase detection autofocus' ,1 ,30 ) ,'Y', SYSDATE, fnd_global.user_id ,SYSDATE, fnd_global.user_id, fnd_global.login_id, 1 ); --5 INSERT INTO xxaoa_item_catalog_stg ( item_name, catalog_name, element_sequence, element_name ,element_value, default_element_flag ,last_update_date, last_updated_by, creation_date, created_by ,last_update_login, set_process_id ) VALUES ( 'Iphone 6', 'Phone Specifications', 50, 'Battery' ,SUBSTR ( 'Non-removable Li-Po 1810 mAh battery', 1, 30 ), 'Y' ,SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id ,fnd_global.login_id, 1 ); --2.1 INSERT INTO xxaoa_item_catalog_stg ( item_name, catalog_name, element_sequence,element_name,element_value, default_element_flag, last_update_date ,last_updated_by, creation_date, created_by,last_update_login, set_process_id ) VALUES ( 'Samsung Galaxy Grand Prime', 'Phone Specifications', 10 ,'Operating System' ,SUBSTR ( 'Android OS, v4.4.2 (KitKat)', 1, 30 ), 'Y', SYSDATE ,fnd_global.user_id, SYSDATE, fnd_global.user_id ,fnd_global.login_id, 1); --2.2 INSERT INTO xxaoa_item_catalog_stg ( item_name, catalog_name, element_sequence ,element_name, element_value, default_element_flag ,last_update_date, last_updated_by, creation_date, created_by ,last_update_login, set_process_id ) VALUES ( 'Samsung Galaxy Grand Prime', 'Phone Specifications', 20 ,'Internal Memory', SUBSTR ( '8 GB, 1 GB RAM', 1, 30 ), 'Y' ,SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id ,fnd_global.login_id, 1 ); --2.3 INSERT INTO xxaoa_item_catalog_stg ( item_name, catalog_name, element_sequence ,element_name, element_value ,default_element_flag, last_update_date, last_updated_by ,creation_date, created_by, last_update_login, set_process_id ) VALUES ( 'Samsung Galaxy Grand Prime', 'Phone Specifications', 30 ,'Expandable Memory', SUBSTR ( 'microSD, up to 64 GB', 1, 30 ) ,'Y', SYSDATE, fnd_global.user_id ,SYSDATE, fnd_global.user_id, fnd_global.login_id, 1 ); --2.4 INSERT INTO xxaoa_item_catalog_stg ( item_name, catalog_name, element_sequence ,element_name,element_value, default_element_flag ,last_update_date, last_updated_by, creation_date, created_by ,last_update_login, set_process_id ) VALUES ( 'Samsung Galaxy Grand Prime', 'Phone Specifications', 40 ,'Camera Pixels' ,SUBSTR ( '8 MP, 3264 x 2448 pixels, autofocus,', 1, 30 ), 'Y' ,SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id ,fnd_global.login_id, 1 ); --2.5 INSERT INTO xxaoa_item_catalog_stg ( item_name, catalog_name, element_sequence ,element_name, element_value, default_element_flag ,last_update_date, last_updated_by, creation_date, created_by ,last_update_login, set_process_id ) VALUES ( 'Samsung Galaxy Grand Prime', 'Phone Specifications', 50 ,'Battery', SUBSTR ( 'Li-Ion 2600 mAh battery', 1, 30 ), 'Y' ,SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id ,fnd_global.login_id, 1 ); COMMIT ; |
Above script will Insert the data into staging table xxaoa_item_catalog_stg
- Save the below script file and then run the script from the SQL Prompt (Example : processstagerecords.sql).It will load the records from staging table (xxaoa_item_catalog_stg ) to Interface table (mtl_desc_elem_val_interface)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
DECLARE CURSOR curinsertinterface IS SELECT xics.ROWID AS row_id , xics.item_name , xics.catalog_name , xics.element_sequence , xics.element_name , xics.element_value , xics.default_element_flag , xics.inventory_item_id , xics.created_by , xics.last_update_login , xics.set_process_id FROM xxaoa_item_catalog_stg xics; TYPE tprocessstgtbl IS TABLE OF curinsertinterface%ROWTYPE; vprocessstagingtable tprocessstgtbl; vmessage VARCHAR2 ( 500 ); verrormessage VARCHAR2 ( 2000 ); vinventoryitemid NUMBER; BEGIN OPEN curinsertinterface; LOOP FETCH curinsertinterface BULK COLLECT INTO vprocessstagingtable LIMIT 15000; FOR i IN 1 .. vprocessstagingtable.COUNT LOOP BEGIN SELECT inventory_item_id INTO vinventoryitemid FROM mtl_system_items_b WHERE UPPER ( segment1 ) = UPPER ( vprocessstagingtable ( i ).item_name ); DBMS_OUTPUT.put_line ( 'Fetched Inventory Item Id :' || vinventoryitemid ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( 'Exception while fetching Inventory Item Id :' || SQLERRM ); END; BEGIN INSERT INTO mtl_desc_elem_val_interface ( inventory_item_id ,element_name ,last_update_date ,last_updated_by ,creation_date ,created_by ,last_update_login ,element_value ,request_id ,program_application_id ,program_id ,program_update_date ,default_element_flag ,element_sequence ,transaction_id ,process_flag ,item_number ,set_process_id ) VALUES ( vinventoryitemid ,vprocessstagingtable ( i ).element_name ,SYSDATE ,fnd_global.user_id ,SYSDATE ,fnd_global.user_id ,vprocessstagingtable ( i ).last_update_login ,vprocessstagingtable ( i ).element_value ,NULL ,NULL ,NULL ,NULL ,vprocessstagingtable ( i ).default_element_flag ,vprocessstagingtable ( i ).element_sequence ,NULL ,1 ,vprocessstagingtable ( i ).item_name ,vprocessstagingtable ( i ).set_process_id ); END; END LOOP; COMMIT; EXIT WHEN curinsertinterface%NOTFOUND; END LOOP; CLOSE curinsertinterface; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( 'Main Exception :' || SQLERRM ); END; / |
After successfully run, we need to submit the standard Program
Item Catalog Group Element Open Interface (INV_ITEM_CAT_GRP_ELE_OI)
Parameters:
When you run the Item Catalog Group Element Open Interface, you are prompted for report parameters. These are runtime options for the Item Interface:
Record set Id:
Used to group the rows, should be set to value of “set_process_id” in mtl_desc_elem_val_interface
Upload Processed Records:
Whether the rows in interface table are to be uploaded to database
Delete Processed Rows:
Yes: Delete successfully processed items from the interface table.
No: Leave all rows in the interface tables.
Note :
Set_process_id column is required in this table it will be any numeric value, used to group records to be imported
Log File:
Script to verify whether the Assign Catalog to Items is successful:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT d.segment1 "Item Name" , c.segment1 "Catalog Name", b.element_sequence , a.element_name , a.element_value FROM mtl_descr_element_values a ,mtl_descriptive_elements b ,mtl_item_catalog_groups c ,mtl_system_items_b d WHERE a.element_name = b.element_name AND a.inventory_item_id = d.inventory_item_id AND b.item_catalog_group_id = d.item_catalog_group_id AND b.item_catalog_group_id = c.item_catalog_group_id AND a.element_name = b.element_name AND a.element_value IS NOT NULL AND c.segment1 = 'Phone Specifications' ORDER BY d.segment1, b.element_sequence; |
Navigation to find the above created in Application
(N) Inventory >> Items >> Master Items
Query for Iphone 6
Then click on Tools >> Catalog
Query for another Item “Samsung Galaxy Grand Prime”
Thanks Khadeer Tadipatri for sharing.
