Inventory Part Numbers with Catalog Values Details Report :
SELECT UNIQUE
OOD.ORGANIZATION_NAME "ORGANIZATION NAME #",
MSI.SEGMENT1 "PART NUMBER #",
MIAV.ELEMENT_SEQUENCE "ELEMENT SEQUENCE #",
MIAV.ELEMENT_NAME "ELEMENT NAME #”,
MIAV.ELEMENT_VALUE "ELEMENT VALUES #",
FU.USER_NAME "ELEMENT CREATED BY",
TO_CHAR (MIAV.CREATION_DATE,'DY DD-MON-YYYY HH:MI:SS AM')
"ELEMENT CREATION DATE",
FU1.USER_NAME "ELEMENT LAST UPDATED BY",
TO_CHAR (MIAV.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH:MM:SS AM')
"ELEMENT LAST UPDATED DATE" ,
MSI.ITEM_CATALOG_GROUP_ID "CATALOG GROUP ID #",
MICGB.SEGMENT1||' ' || MICGB.SEGMENT2||' '||MICGB.SEGMENT3
"CATALOG GROUP NAME #"
FROM
APPS.MTL_SYSTEM_ITEMS MSI,
APPS.MTL_DESCR_ELEMENT_VALUES MIAV,
APPS.ORG_ORGANIZATION_DEFINITIONS OOD,
APPS.FND_USER FU,
APPS.FND_USER FU1,
APPS.MTL_ITEM_CATALOG_GROUPS_B MICGB
WHERE
MSI.INVENTORY_ITEM_ID=MIAV.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=121
AND MSI.ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND FU.USER_ID=MIAV.CREATED_BY
AND FU1.USER_ID=MIAV.LAST_UPDATED_BY
AND MSI.ITEM_CATALOG_GROUP_ID = MICGB.ITEM_CATALOG_GROUP_ID (+)
AND MSI.SEGMENT1 IN ( ‘PART NUMBER / OR LIST OF PART NUMBERS’)
ORDER BY
MSI.ITEM_CATALOG_GROUP_ID,
MSI.SEGMENT1,
MIAV.ELEMENT_SEQUENCE
