Inventory Part Number Report with Catalog Group Name

Inventory Part Number Report with Catalog Group Name :- 

 

SELECT  UNIQUE OOD.ORGANIZATION_NAME "ORGANIZATION NAME #", 
MSI.INVENTORY_ITEM_ID "INVENTORY_ITEM_ID #",

MSI.SEGMENT1 "PART NUMBER#",

MSI.DESCRIPTION "DESCRIPTION #",

TL.LONG_DESCRIPTION  "LONG DESCRIPTION #",

MSI.INVENTORY_ITEM_STATUS_CODE "INVENTORY ITEM STATUS CODE #",

MSI.PRIMARY_UNIT_OF_MEASURE "PRIMARY_UOM #",

MSI.ITEM_CATALOG_GROUP_ID "CATALOG GROUP ID #",

MICGB.SEGMENT1 "CATALOG GROUP NAME #",              

TO_CHAR(FU.USER_NAME) "ITEM CREATED BY #",       

TO_CHAR(MSI.CREATION_DATE,'DD-MON-YYYY') "ITEM CREATION DATE#" ,

TO_CHAR(FU1.USER_NAME) "ITEM LAST UPDATED BY #",

TO_CHAR(MSI.LAST_UPDATE_DATE,'DD-MON-YYYY ') "ITEM LAST UPDATED DATE #"

FROM
INV.MTL_SYSTEM_ITEMS_B MSI,

APPS.MTL_SYSTEM_ITEMS_TL TL,

APPS.ORG_ORGANIZATION_DEFINITIONS OOD,

APPS.FND_USER FU,

APPS.FND_USER FU1,

APPS.MTL_ITEM_CATALOG_GROUPS_B MICGB

WHERE
               OOD.ORGANIZATION_ID=MSI.ORGANIZATION_ID

AND       MSI.CREATED_BY=FU.USER_ID

AND       MSI.LAST_UPDATED_BY=FU1.USER_ID

AND       OOD.ORGANIZATION_ID = 122

AND       MSI.ITEM_CATALOG_GROUP_ID =  MICGB.ITEM_CATALOG_GROUP_ID (+)

AND       MSI.INVENTORY_ITEM_ID=TL.INVENTORY_ITEM_ID

AND       MSI.ORGANIZATION_ID=TL.ORGANIZATION_ID

AND       MSI.ORGANIZATION_ID=122

AND       MSI.SEGMENT1 = ‘PART NUMBER’

ORDER BY MSI.ITEM_CATALOG_GROUP_ID ,MSI.INVENTORY_ITEM_ID