Item Master Setup Query - ASCP

SELECT
  msib.segment1 Item_Code
, msib.description Item_Description
, msib.primary_unit_of_measure uom
, (
    SELECT
      mcb.segment1
    FROM
      mtl_categories_b mcb
    WHERE
      mcb.category_id =
      (
        SELECT
          category_id
        FROM
          mtl_item_categories_v
        WHERE
          category_set_name  ='Inventory'
        AND inventory_item_id=msib.inventory_item_id
        AND organization_id  =msib.organization_id
      )
  )
  inventory_category
  , (
    SELECT
      mcb.segment1
    FROM
      mtl_categories_b mcb
    WHERE
      mcb.category_id =
      (
        SELECT
          category_id
        FROM
          mtl_item_categories_v
        WHERE
          category_set_name  ='Purchasing'
        AND inventory_item_id=msib.inventory_item_id
        AND organization_id  =msib.organization_id
      )
  )
  purchasing_category
  ,(SELECT
          category_concat_segs
        FROM
          mtl_item_categories_v
        WHERE
          category_set_name  ='Inventory'
        AND inventory_item_id=msib.inventory_item_id
        AND organization_id  =msib.organization_id) Inventory
        ,(SELECT
          category_concat_segs
        FROM
          mtl_item_categories_v
        WHERE
          category_set_name  ='Purchasing'
        AND inventory_item_id=msib.inventory_item_id
        AND organization_id  =msib.organization_id) Purchasing
  ,msib.ITEM_TYPE "UserItem Type"
, (
    SELECT
      full_name
    FROM
      per_all_people_f
    WHERE
      person_id= msib.buyer_id
    AND rownum =1
  )
  buyer_name
  ,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"
, m.PREPROCESSING_LEAD_TIME
, m.FULL_LEAD_TIME
, m.MINIMUM_ORDER_QUANTITY
, m.MAXIMUM_ORDER_QUANTITY
, (
    SELECT
      meaning
    FROM
      fnd_lookup_values_vl
    WHERE
      lookup_type  = 'MRP_PLANNING_CODE'
    AND lookup_code=m.mrp_planning_code
  )
  MRP_PLANNING_VALUE
,(
    SELECT
      meaning
    FROM
      fnd_lookup_values_vl
    WHERE
      lookup_type ='MRP_SAFETY_STOCK'
    and
      lookup_code= m.mrp_safety_stock_code
  )
  Safety_Stock_value
  , (
    SELECT
      meaning
    FROM
      fnd_lookup_values_vl
    WHERE
      lookup_type ='ASSEMBLY_PEGGING_CODE'
    and
      lookup_code= m.END_ASSEMBLY_PEGGING_FLAG
  )
  Pegging_Flag
  ,decode ( m.ATO_FORECAST_CONTROL, 3, 'None', 2, 'Consume and derive' , 1, 'Consume') Forecast
  ,msib.LIST_PRICE_PER_UNIT "List price"
,m.MRP_PLANNING_CODE
,m.FIXED_ORDER_QUANTITY
,m.FIXED_DAYS_SUPPLY
,m.SAFETY_STOCK_BUCKET_DAYS
,m.MRP_SAFETY_STOCK_PERCENT
,m.END_ASSEMBLY_PEGGING_FLAG
,m.PLANNING_TIME_FENCE_DAYS
,m.DEMAND_TIME_FENCE_DAYS
,m.RELEASE_TIME_FENCE_DAYS
,m.PLANNING_EXCEPTION_SET
,m.purchasing_enabled_flag
,m.purchasing_item_flag
,m.purchasing_tax_code
  /*--, micv.category_set_name*/
  /*--, mcb.segment1*/
, msib.organization_id
, msib.source_subinventory "Source: Sub Inventory"
, msib.safety_stock_bucket_days "Safety Stock: Bucket Days"
, msib.mrp_safety_stock_percent "Safety Stock: Percent"
, msib.fixed_order_quantity "OM:Fixed Order Quantity"
, msib.fixed_days_supply "OM:Fixed days Supply"
, msib.fixed_lot_multiplier "OM:Fixed Lot Multiplier"
, msib.vmi_minimum_units "VM:Minimum Quantity"
, msib.vmi_maximum_units "VM:Maximum Quantity"
, msib.vmi_minimum_days "VM:Minimum Days Of Supply"
, msib.vmi_maximum_days "VM:Maximum Days Of Supply"
, msib.vmi_fixed_order_quantity "VM:Fixed Quantity"
, msib.so_authorization_flag "VM:Release Authorization Req"
, msib.consigned_flag "VM:Consigned"
, msib.asn_autoexpire_flag "VM:Auto Expire ASN"
, msib.vmi_forecast_type "VM:Forecast Type"
, msib.forecast_horizon "VM:Window Days"
FROM
  MTL_SYSTEM_ITEMS_FVL m
, mtl_system_items_b msib
  /*--, mtl_category_sets_v mcsv*/
  /*--, mtl_item_categories_v micv*/
  /*--, mtl_categories_b mcb*/
WHERE
  m.inventory_item_id=msib.inventory_item_id
AND m.organization_id=msib.organization_id
  /*--and mcsv.category_set_id = micv.category_set_id*/
  /*--and micv.inventory_item_id= msib.inventory_item_id*/
  /*--and msib.organization_id= micv.organization_id*/
  /*--and micv.category_set_name in ('Purchasing','Inventory')*/
  /*--and mcb.category_id=micv.category_id*/
  /*--and mcb.structure_id= micv.structure_id*/
AND msib.organization_id=:P_ORG_ID
ORDER BY
  msib.organization_id
, msib.segment1 ;