SELECT mp.organization_id,
         MSIB.inventory_item_id,
         TO_CHAR (msib.CREATION_DATE, 'DD-Mon-YY') Creation_date,
         (SELECT description
            FROM fnd_user
           WHERE user_id = msib.created_by)
            created_by1,
         mp.organization_code ORG_CODE,
         msib.SEGMENT1 item_code,
         item_type,
         INVENTORY_ITEM_STATUS_CODE Item_status,
         CAT1.CATEGORY_CONCAT_SEGS INVentory_CATEGORY,
         CAT2.CATEGORY_CONCAT_SEGS PLANNING_CATEGORY,
         CAT2.CATEGORY_ID,
         msib.INVENTORY_ITEM_STATUS_CODE status,
         INVENTORY_ITEM_FLAG INVENTORY_ITEM,
         STOCK_ENABLED_FLAG STOCK_ENABLED,
         DECODE (msib.BOM_ITEM_TYPE, 4, 'Std') BOM_TYPE,
         (SELECT FULL_NAMe
            FROM hr_employees
           WHERE EMPLOYEE_ID = BUYER_id)
            buyer_name,
         msib.LIST_PRICE_PER_UNIT list_price,
         DECODE (msib.INVENTORY_PLANNING_CODE,
                 1, 'Reorder point planning',
                 2, 'Min-max planning',
                 6, 'Not planned')
            inv_planning,
         msib.PLANNER_CODE planner,
         msib.MINIMUM_ORDER_QUANTITY min_order_qty,
         MaxIMUM_ORDER_QUANTITY max_order_qty,
         msib.FIXED_DAYS_SUPPLY,
         msib.FIXED_LOT_MULTIPLIER flm,
         DECODE (msib.MRP_PLANNING_CODE,
                 3, 'MRP planning',
                 4, 'MPS planning',
                 6, 'Not planned',
                 7, 'MRP and DRP planning',
                 8, 'MPS and DRP planning',
                 9, 'DRP planning')
            planning_method,
         DECODE (msib.ATO_FORECAST_CONTROL,
                 1, 'Consume',
                 2, 'Consume and derive',
                 3, 'None')
            FC_control,
         DECODE (msib.END_ASSEMBLY_PEGGING_FLAG,
                 'A', 'Full Pegging',
                 'B', 'End Assembly/Soft Pegging',
                 'I', 'Net by Project/Ignore Excess',
                 'N', 'None',
                 'X', 'Net by Project/Net Excess',
                 'Y', 'End Assembly Pegging')
            pegging,
         DECODE (PLANNING_TIME_FENCE_CODE, 4, 'User-defined time fence')
            Planning_time_fence,
         PLANNING_TIME_FENCE_DAYS PTF_days,
         DECODE (DEMAND_TIME_FENCE_CODE, 4, 'User-defined time fence')
            demand_time_fence,
         DEMAND_TIME_FENCE_days dtf_days,
         DECODE (RELEASE_TIME_FENCE_CODE, 4, 'User-defined time fence')
            rel_time_fence,
         RELEASE_TIME_FENCE_days rtf_days,
         PREPROCESSING_LEAD_TIME pre_proc_lt,
         FULL_LEAD_TIME proc_lt,
         POSTPROCESSING_LEAD_TIME post_proc_lt,
         FIXED_LEAD_TIME fixed_lt,
         VARIABLE_LEAD_TIME var_lt,
         ATP_FLAG check_atp,
         (SELECT    mar.rule_name
                 || DECODE (mar.INFINITE_SUPPLY_FENCE_CODE,
                            4, '->User defined TF = ')
                 || NVL (mar.INFINITE_SUPPLY_TIME_FENCE, '0')
            FROM apps.MTL_ATP_RULES mar
           WHERE mar.rule_id = msib.ATP_RULE_id)
            ATP_infinite_TF,
         ATP_COMPONENTS_FLAG ATP_COMPONENTS,
         DECODE (PLANNING_MAKE_BUY_CODE,  1, 'Make',  2, 'Buy') MAKE_BUY,
         (SELECT DISTINCT 'Yes'
            FROM APPS.BOM_INVENTORY_COMPONENTS bic
           WHERE bic.BILL_SEQUENCE_ID =
                    (SELECT bom.BILL_SEQUENCE_ID
                       FROM APPS.BOM_BILL_OF_MATERIALS bom
                      WHERE     bom.ASSEMBLY_ITEM_ID = MSIB.inventory_item_id
                            AND bom.ORGANIZATION_ID = mp.organization_id))
            BOM_Exist,
         (SELECT sr1.SOURCING_RULE_NAME
            FROM apps.MRP_SR_ASSIGNMENTS_V sr1
           WHERE     sr1.ASSIGNMENT_SET_ID = 2
                 AND sr1.INVENTORY_ITEM_ID IS NOT NULL
                 AND sr1.INVENTORY_ITEM_ID = MSIB.inventory_item_id
                 AND sr1.ORGANIZATION_ID = mp.organization_id)
            item_org_sr,
         (SELECT sr2.SOURCING_RULE_NAME
            FROM apps.MRP_SR_ASSIGNMENTS_V sr2
           WHERE     sr2.ASSIGNMENT_SET_ID = 2
                 AND sr2.CATEGORY_ID = CAT2.CATEGORY_ID
                 --AND  sr2.INVENTORY_ITEM_ID IS  NULL

                 AND sr2.ORGANIZATION_CODE = mp.organization_code)
            cate_org_sr,
         (SELECT ss.EFFECTIVITY_DATE || ' = ' || ss.SAFETY_STOCK_QUANTITY
            FROM APPS.MTL_SAFETY_STOCKS SS
           WHERE     SS.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
                 AND SS.ORGANIZATION_ID = MP.ORGANIZATION_ID)
            SAFETY_STOCK
    FROM apps.MTL_SYSTEM_ITEMS_B msib,
         apps.mtl_parameters mp,
         apps.MTL_ITEM_CATEGORIES_V CAT1,
         apps.MTL_ITEM_CATEGORIES_V CAT2
   WHERE     mp.organization_id = msib.organization_id
         AND mp.organization_code IN ('PGH', 'LIS')
         AND CAT1.CATEGORY_SET_ID = 1
         AND CAT1.INVENTORY_ITEM_ID = MSIB.inventory_item_id
         AND CAT1.ORGANIZATION_ID = mp.organization_id
         AND CAT2.CATEGORY_SET_ID = 1100000022
         AND CAT2.INVENTORY_ITEM_ID = MSIB.inventory_item_id
         AND CAT2.ORGANIZATION_ID = mp.organization_id
ORDER BY msib.SEGMENT1, organization_code;