Script for the Item Master Query with Category in Oracle Apps EBS R12

---- Script for the Item Master Query with Category in Oracle Apps EBS R12  ----


SELECT
    Rownum "Sl no"
,msib.ORGANIZATION_ID "oraganization"
,msib.SEGMENT1 "Item code"
,msib.DESCRIPTION "Item description"
,msib.ITEM_TYPE "UserItem Type"
,msib.PRIMARY_UNIT_OF_MEASURE "UOM"
,msib.INVENTORY_ITEM_FLAG "Inventory Item"
,msib.STOCK_ENABLED_FLAG "Stockable"
,msib.MTL_TRANSACTIONS_ENABLED_FLAG "Transactable"
,msib.RESERVABLE_TYPE "Reservable"
,msib.PURCHASING_ITEM_FLAG "Purchased"
,msib.PURCHASING_ENABLED_FLAG "Purchasable"
,msib.MUST_USE_APPROVED_VENDOR_FLAG "Use Approve Supplier List"
,msib.OUTSIDE_OPERATION_FLAG "OSP item"
,msib.OUTSIDE_OPERATION_UOM_TYPE "Unit Type"
,(select FIRST_NAME || ' '|| LAST_NAME name from per_all_people_f where person_id=msib.BUYER_ID) "Defult buyer"
,msib.LIST_PRICE_PER_UNIT "List price"
,msib.RECEIVING_ROUTING_ID "Receive Routing"
,(select  misd.SUBINVENTORY_CODE from mtl_item_sub_defaults misd
                                    where misd.ORGANIZATION_ID =85
                                    and   misd.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
                                    and   misd.DEFAULT_TYPE =2) "Receiving Subinventory"
,msib.INVENTORY_PLANNING_CODE "Inventory Planning method"
,msib.MIN_MINMAX_QUANTITY "Min Max(Minimum Qty)"
,msib.MAX_MINMAX_QUANTITY "Min Max(Maximum Qty)"
,msib.MINIMUM_ORDER_QUANTITY "Order Qty (Minimum Qty)"
,msib.MAXIMUM_ORDER_QUANTITY "Order Qty (Maximum Qty)"
,msib.SOURCE_TYPE "Source Type"
,msib.SOURCE_SUBINVENTORY "Source Sub inventory"
,msib.PLANNING_MAKE_BUY_CODE "MAKE BUY"
,msib.FIXED_LOT_MULTIPLIER "Fixed lot multiplier"
,msib.PLANNER_CODE "Planner"
,msib.MRP_PLANNING_CODE "Planning method"
,msib.ATO_FORECAST_CONTROL "Forecast control"
,msib.END_ASSEMBLY_PEGGING_FLAG "Pegging"
,msib.RELEASE_TIME_FENCE_CODE "Release Time Fence"
,msib.PLANNING_TIME_FENCE_DAYS "Planning time fence"
,msib.PREPROCESSING_LEAD_TIME "Preprocessing"
,msib.FULL_LEAD_TIME "Processing"
,msib.POSTPROCESSING_LEAD_TIME "Post processing"
,msib.INVENTORY_ITEM_STATUS_CODE "Item Status"
,(select nvl((micv.CATEGORY_CONCAT_SEGS),null) from mtl_item_categories_v micv where micv.CATEGORY_SET_ID=1 and micv.ORGANIZATION_ID=85 and micv.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID and rownum<=1 )    Inventory
,(select nvl((micv.CATEGORY_CONCAT_SEGS),null) from mtl_item_categories_v micv where micv.CATEGORY_SET_ID=1100000041 and micv.ORGANIZATION_ID=85 and micv.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID and rownum<=1 )   Purchase
,(select nvl((micv.CATEGORY_CONCAT_SEGS),null) from mtl_item_categories_v micv where micv.CATEGORY_SET_ID=1100000042 and micv.ORGANIZATION_ID=85 and micv.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID and rownum<=1 )   Planning
FROM  mtl_system_items_b msib
     -- mtl_item_sub_defaults misd
where msib.ORGANIZATION_ID=85
--and   msib.INVENTORY_ITEM_ID = misd.INVENTORY_ITEM_ID
--and   msib.ORGANIZATION_ID = misd.ORGANIZATION_ID