Oracle Apps Query : Query To get item details

 

select segment1, description,  item_type,inventory_item_status_code,

INVENTORY_ITEM_FLAG,STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, NULL MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

 INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT,

       -- category_set_name,

        max(Inventory) Inventory ,

        max(Product_Family) Product_Family,

        max(MRP) MRP,

        max(CST) CST,

        max(PUR) PUR,

        max(PRAMAC_PRODUCT_LINE) PRAMAC_PRODUCT_LINE,

        max(QUALITY) QUALITY,

        max(SALES) SALES,

        max(INTRASTAT_Classification)  INTRASTAT_Classification ,

        max(Sales_Category_Subcategory ) Sales_Category_Subcategory,

        max(PRAMAC_CLASS_MAPPING ) PRAMAC_CLASS_MAPPING,

        max(MANUFACTURED_IN )MANUFACTURED_IN ,

        max(LEAN) LEAN,

        max(R&D_MAINTENANCE_BOM) R&D_MAINTENANCE_BOM,

        max(Product_Categories) Product_Categories,

        max(Sequence_of_Events)Sequence_of_Events,

        max(Product_Family1) Product_Family1

from 

(

SELECT distinct null source, A.ORGANIZATION_ID, a.segment1, a.description,  NULL long_description, a.PRIMARY_UNIT_OF_MEASURE, null templates,

a.item_type,inventory_item_status_code,A.INVENTORY_ITEM_FLAG,A.STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, NULL MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

 INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT

        ,b.category_set_name,a.inventory_item_id,

        decode ( b.category_set_name,'Inventory',b.SEGMENT1) Inventory,

        decode ( b.category_set_name,'Product Family',b.SEGMENT1) Product_Family,

        decode ( b.category_set_name,'MRP',b.SEGMENT1) as "MRP",

        decode ( b.category_set_name,'CST',b.SEGMENT1) as "CST",

        decode ( b.category_set_name,'PUR',b.SEGMENT1) as "PUR",

        decode ( b.category_set_name,'PRAMAC PRODUCT LINE',b.SEGMENT1) as "PRAMAC_PRODUCT_LINE",

        decode ( b.category_set_name,'QUALITY',b.SEGMENT1) as "QUALITY",

        decode ( b.category_set_name,'SALES',b.SEGMENT1) as "SALES",

        decode ( b.category_set_name,'INTRASTAT Classification',b.SEGMENT1) INTRASTAT_Classification,

        decode ( b.category_set_name,'Sales Category & Subcategory',b.SEGMENT1) Sales_Category_Subcategory,

        decode ( b.category_set_name,'PRAMAC_CLASS_MAPPING',b.SEGMENT1) PRAMAC_CLASS_MAPPING,

        decode ( b.category_set_name,'MANUFACTURED IN',b.SEGMENT1) MANUFACTURED_IN,

        decode ( b.category_set_name,'LEAN',b.SEGMENT1) LEAN,

        decode ( b.category_set_name,'R&D MAINTENANCE BOM',b.SEGMENT1) R&D_MAINTENANCE_BOM,

        decode ( b.category_set_name,'Product Categories',b.SEGMENT1) Product_Categories,

        decode ( b.category_set_name,'Sequence of Events',b.SEGMENT1) Sequence_of_Events,

        decode ( b.category_set_name,'Product Family',b.SEGMENT2) Product_Family1        

  FROM mtl_system_items_b a, mtl_item_categories_v b

 WHERE 1 = 1

   AND a.inventory_item_status_code = 'Active'

   AND a.inventory_item_id = b.inventory_item_id

   AND a.organization_id = b.organization_id

   AND a.organization_id = 11

   ) xx

group by segment1, description, xx.inventory_item_id, item_type,inventory_item_status_code,

INVENTORY_ITEM_FLAG,STOCK_ENABLED_FLAG,LOT_CONTROL_CODE, LOCATION_CONTROL_CODE,

LOT_DIVISIBLE_FLAG, LOT_SPLIT_ENABLED, BOM_ENABLED_FLAG, COST_OF_SALES_ACCOUNT,

PURCHASING_item_FLAG, EXPENSE_ACCOUNT, MAKE_OR_BUY

, MIN_MINMAX_QUANTITY, MINIMUM_ORDER_QUANTITY, MAX_MINMAX_QUANTITY, MAXIMUM_ORDER_QUANTITY, BUILD_IN_WIP_FLAG,CUSTOMER_ORDER_FLAG,

 INTERNAL_ORDER_FLAG,INVOICEABLE_ITEM_FLAG ,SALES_ACCOUNT