DBA Data[Home] [Help]

VIEW: APPS.MSD_SR_PRD_CAT_V

Source

View Text - Preformatted

SELECT distinct
    msiv.inventory_item_id inventory_item_id , msiv.concatenated_segments,
    msiv_tl.description,
    decode( MCS.MULT_ITEM_CAT_ASSIGN_FLAG,
            'Y', msd_sr_util.get_null_pk,
            decode(msiv.organization_id,
                   morg.parameter_value, decode(para.parameter_value,
                                                null, msd_sr_util.get_null_pk,
                                                decode(micv.category_id,
                                                       null, msd_sr_util.get_null_pk,
                                                       micv.category_id)
                                                       ),
                   msd_sr_util.get_null_pk)
            ) category_id,
    decode( MCS.MULT_ITEM_CAT_ASSIGN_FLAG,
            'Y', msd_sr_util.get_null_desc,
            decode( msiv.organization_id,
                    morg.parameter_value, decode( para.parameter_value,
                                                  null, msd_sr_util.get_null_desc,
                                                  decode( micv.category_id,
                                                          null, msd_sr_util.get_null_desc,
                                                          MC.CONCATENATED_SEGMENTS)
                                                 ),
                    msd_sr_util.get_null_desc )
           ),
    decode( MCS.MULT_ITEM_CAT_ASSIGN_FLAG,
            'Y', msd_sr_util.get_null_desc,
            decode( msiv.organization_id,
                    morg.parameter_value, decode( para.parameter_value,
                                                  null, msd_sr_util.get_null_desc,
                                                  decode( micv.category_id,
                                                          null, msd_sr_util.get_null_desc,
                                                          MC.CONCATENATED_SEGMENTS)
                                                 ),
                    msd_sr_util.get_null_desc )
           ),
    null, null, null, null, null, null, null, null
from
    msd_app_instance_orgs vorg,
    msd_setup_parameters MORG,
    msd_setup_parameters para,
    mtl_item_categories  micv,
    MTL_CATEGORIES_B_KFV MC,
    MTL_CATEGORY_SETS_B MCS,
    mtl_system_items_kfv msiv,
    mtl_system_items_tl  msiv_tl,
    msd_setup_parameters morg2,
    msd_setup_parameters msp
WHERE
 msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and
 morg2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and
 MICV.CATEGORY_ID = MC.CATEGORY_ID and
 MICV.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID and
 msiv.inventory_item_id = msiv_tl.inventory_item_id and
 msiv.organization_id = msiv_tl.organization_id and
 msiv_tl.language = USERENV('LANG') and
 decode( nvl(msp.parameter_value, '2'),
         '1', decode(nvl(msiv.ato_forecast_control,3),
                     3, decode(nvl(morg2.parameter_value, '1'),
                               '4', decode(msiv.bom_item_type,
                                           2, 1,
                                           msd_sr_util.is_product_family_forecastable(morg.parameter_value, msiv.inventory_item_id, 1)
                                           ),
                                msd_sr_util.is_product_family_forecastable(morg.parameter_value, msiv.inventory_item_id, 2)),
                     1),
         decode( nvl(msiv.ato_forecast_control,3),
                 3, decode(nvl(morg2.parameter_value,'1'),
                              '4', decode(msiv.bom_item_type,
                                          2, 1,
                                          MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msiv.inventory_item_id)
                                          ),
                               2 ),
                 1)
        ) = 1 and
 decode( nvl(morg2.parameter_value, '1') ,
         '3', decode( msiv.bom_item_type,
                      2, 2,
                      1),
         1) = 1 and
 msiv.organization_id = vorg.organization_id and
 micv.inventory_item_id (+) = msiv.inventory_item_id and
 micv.organization_id (+) = msiv.organization_id
 and (msiv.mrp_planning_code <> 6 or (msiv.mrp_planning_code = 6 and msiv.pick_components_flag = 'Y'))
 and msiv.bom_item_type <> 5
 and to_char(micv.category_set_id) = para.parameter_value (+)
 and 'MSD_CATEGORY_SET_NAME'= para.parameter_name (+)
 and morg.parameter_name = 'MSD_MASTER_ORG'
 and
  not exists (select 1
                from msd_setup_parameters
               where parameter_name = 'MSD_ITEM_ORG'
                 and parameter_value = 'MSD_MASTER_ORG') and
 ((to_char(micv.category_set_id) = para.parameter_value and
  msiv.organization_id = morg.parameter_value ) or
  not exists (select 1 from MTL_SYSTEM_ITEMS msiv2,
                            mtl_item_categories  cat1,
                            msd_setup_parameters para2,
                            msd_setup_parameters morg3,
                            msd_setup_parameters msp1
                       where msiv2.inventory_item_id = cat1.inventory_item_id and
                       msiv2.organization_id = cat1.organization_id and
                       to_char(cat1.category_set_id) = para2.parameter_value and
                       para2.parameter_name = 'MSD_CATEGORY_SET_NAME' and
                       msiv2.inventory_item_id = msiv.inventory_item_id and
                       msiv2.organization_id = morg.parameter_value and
                       morg3.parameter_name = 'MSD_PLANNING_PERCENTAGE' and
                       msp1.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and
                       decode( nvl(msp1.parameter_value, '2'),
                               '1', decode(nvl( msiv2.ato_forecast_control,3),
                                           3, decode(nvl(morg3.parameter_value, '1'),
                                                  '4', decode(msiv2.bom_item_type,
                                                              2, 1,
                                                              msd_sr_util.is_product_family_forecastable(morg.parameter_value, msiv2.inventory_item_id, 1)
                                                              ),
                                                   msd_sr_util.is_product_family_forecastable(morg.parameter_value, msiv2.inventory_item_id, 2)
                                                   ),
                                           1),
                               decode(nvl(msiv2.ato_forecast_control,3),
                                        3, decode(nvl(morg3.parameter_value,'1'),
                                                   '4', decode(msiv2.bom_item_type,
                                                               2, 1,
                                                               MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msiv2.inventory_item_id)
                                                               ),
                                                    2 ),
                                        1)
                                ) = 1 and
                       decode( nvl(morg3.parameter_value, '1'),
                               '3', decode( msiv2.bom_item_type,
                                            2, 2,
                                            1),
                                1) = 1
                       and (msiv2.mrp_planning_code <> 6 or (msiv2.mrp_planning_code = 6 and msiv2.pick_components_flag = 'Y'))
                       and msiv2.bom_item_type <> 5 ) )
union all
SELECT /*+ ORDERED */
    distinct
    msiv.inventory_item_id inventory_item_id , msiv.concatenated_segments,
    msiv_tl.description,
    decode( MCS.MULT_ITEM_CAT_ASSIGN_FLAG,
            'Y', msd_sr_util.get_null_pk,
            decode(msiv.organization_id,
                   morg.parameter_value, decode(para.parameter_value,
                                                null, msd_sr_util.get_null_pk,
                                                decode(micv.category_id,
                                                       null, msd_sr_util.get_null_pk,
                                                       micv.category_id)
                                                       ),
                   msd_sr_util.get_null_pk)
            ) category_id,
    decode( MCS.MULT_ITEM_CAT_ASSIGN_FLAG,
            'Y', msd_sr_util.get_null_desc,
            decode( msiv.organization_id,
                    morg.parameter_value, decode( para.parameter_value,
                                                  null, msd_sr_util.get_null_desc,
                                                  decode( micv.category_id,
                                                          null, msd_sr_util.get_null_desc,
                                                          MC.CONCATENATED_SEGMENTS)
                                                 ),
                    msd_sr_util.get_null_desc )
           ),
    decode( MCS.MULT_ITEM_CAT_ASSIGN_FLAG,
            'Y', msd_sr_util.get_null_desc,
            decode( msiv.organization_id,
                    morg.parameter_value, decode( para.parameter_value,
                                                  null, msd_sr_util.get_null_desc,
                                                  decode( micv.category_id,
                                                          null, msd_sr_util.get_null_desc,
                                                          MC.CONCATENATED_SEGMENTS)
                                                 ),
                    msd_sr_util.get_null_desc )
           ),
    null, null, null, null, null, null, null, null
from
   msd_setup_parameters MORG,
   msd_setup_parameters morg2,
   msd_setup_parameters msp,
   msd_setup_parameters para,
   mtl_system_items_kfv msiv,
   mtl_item_categories  micv,
   MTL_CATEGORIES_B_KFV MC,
   MTL_CATEGORY_SETS_B MCS,
   mtl_system_items_tl  msiv_tl
WHERE
 msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and
 morg2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and
 MICV.CATEGORY_ID = MC.CATEGORY_ID and
 MICV.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID and
 msiv.inventory_item_id = msiv_tl.inventory_item_id and
 msiv.organization_id = msiv_tl.organization_id and
 msiv_tl.language = USERENV('LANG') and
 decode( nvl(msp.parameter_value, '2'),
         '1', decode(nvl(msiv.ato_forecast_control,3),
                     3, decode(nvl(morg2.parameter_value, '1'),
                               '4', decode(msiv.bom_item_type,
                                           2, 1,
                                           msd_sr_util.is_product_family_forecastable(morg.parameter_value, msiv.inventory_item_id, 1)
                                           ),
                                msd_sr_util.is_product_family_forecastable(morg.parameter_value, msiv.inventory_item_id, 2)),
                     1),
         decode( nvl(msiv.ato_forecast_control,3),
                 3, decode(nvl(morg2.parameter_value,'1'),
                              '4', decode(msiv.bom_item_type,
                                          2, 1,
                                          MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msiv.inventory_item_id, morg.parameter_value)
                                          ),
                               2 ),
                 1)
        ) = 1 and
 decode( nvl(morg2.parameter_value, '1') ,
         '3', decode( msiv.bom_item_type,
                      2, 2,
                      1),
         1) = 1 and
 micv.inventory_item_id = msiv.inventory_item_id and
 micv.organization_id = msiv.organization_id
 and (msiv.mrp_planning_code <> 6 or (msiv.mrp_planning_code = 6 and msiv.pick_components_flag = 'Y'))
 and msiv.bom_item_type <> 5
 and to_char(micv.category_set_id) = para.parameter_value (+)
 and 'MSD_CATEGORY_SET_NAME'= para.parameter_name (+)
 and morg.parameter_name = 'MSD_MASTER_ORG' and
 to_char(micv.category_set_id) = para.parameter_value and
 msiv.organization_id = morg.parameter_value and
 exists (select 1
                from msd_setup_parameters
               where parameter_name = 'MSD_ITEM_ORG'
                 and parameter_value = 'MSD_MASTER_ORG')
View Text - HTML Formatted

SELECT DISTINCT MSIV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSIV.CONCATENATED_SEGMENTS
, MSIV_TL.DESCRIPTION
, DECODE( MCS.MULT_ITEM_CAT_ASSIGN_FLAG
, 'Y'
, MSD_SR_UTIL.GET_NULL_PK
, DECODE(MSIV.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, DECODE(PARA.PARAMETER_VALUE
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, DECODE(MICV.CATEGORY_ID
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, MICV.CATEGORY_ID) )
, MSD_SR_UTIL.GET_NULL_PK) ) CATEGORY_ID
, DECODE( MCS.MULT_ITEM_CAT_ASSIGN_FLAG
, 'Y'
, MSD_SR_UTIL.GET_NULL_DESC
, DECODE( MSIV.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, DECODE( PARA.PARAMETER_VALUE
, NULL
, MSD_SR_UTIL.GET_NULL_DESC
, DECODE( MICV.CATEGORY_ID
, NULL
, MSD_SR_UTIL.GET_NULL_DESC
, MC.CONCATENATED_SEGMENTS) )
, MSD_SR_UTIL.GET_NULL_DESC ) )
, DECODE( MCS.MULT_ITEM_CAT_ASSIGN_FLAG
, 'Y'
, MSD_SR_UTIL.GET_NULL_DESC
, DECODE( MSIV.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, DECODE( PARA.PARAMETER_VALUE
, NULL
, MSD_SR_UTIL.GET_NULL_DESC
, DECODE( MICV.CATEGORY_ID
, NULL
, MSD_SR_UTIL.GET_NULL_DESC
, MC.CONCATENATED_SEGMENTS) )
, MSD_SR_UTIL.GET_NULL_DESC ) )
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL FROM MSD_APP_INSTANCE_ORGS VORG
, MSD_SETUP_PARAMETERS MORG
, MSD_SETUP_PARAMETERS PARA
, MTL_ITEM_CATEGORIES MICV
, MTL_CATEGORIES_B_KFV MC
, MTL_CATEGORY_SETS_B MCS
, MTL_SYSTEM_ITEMS_KFV MSIV
, MTL_SYSTEM_ITEMS_TL MSIV_TL
, MSD_SETUP_PARAMETERS MORG2
, MSD_SETUP_PARAMETERS MSP WHERE MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING' AND MORG2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE' AND MICV.CATEGORY_ID = MC.CATEGORY_ID AND MICV.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID AND MSIV.INVENTORY_ITEM_ID = MSIV_TL.INVENTORY_ITEM_ID AND MSIV.ORGANIZATION_ID = MSIV_TL.ORGANIZATION_ID AND MSIV_TL.LANGUAGE = USERENV('LANG') AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSIV.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG2.PARAMETER_VALUE
, '1')
, '4'
, DECODE(MSIV.BOM_ITEM_TYPE
, 2
, 1
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSIV.INVENTORY_ITEM_ID
, 1) )
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSIV.INVENTORY_ITEM_ID
, 2))
, 1)
, DECODE( NVL(MSIV.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG2.PARAMETER_VALUE
, '1')
, '4'
, DECODE(MSIV.BOM_ITEM_TYPE
, 2
, 1
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSIV.INVENTORY_ITEM_ID) )
, 2 )
, 1) ) = 1 AND DECODE( NVL(MORG2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSIV.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1 AND MSIV.ORGANIZATION_ID = VORG.ORGANIZATION_ID AND MICV.INVENTORY_ITEM_ID (+) = MSIV.INVENTORY_ITEM_ID AND MICV.ORGANIZATION_ID (+) = MSIV.ORGANIZATION_ID
AND (MSIV.MRP_PLANNING_CODE <> 6 OR (MSIV.MRP_PLANNING_CODE = 6
AND MSIV.PICK_COMPONENTS_FLAG = 'Y'))
AND MSIV.BOM_ITEM_TYPE <> 5
AND TO_CHAR(MICV.CATEGORY_SET_ID) = PARA.PARAMETER_VALUE (+)
AND 'MSD_CATEGORY_SET_NAME'= PARA.PARAMETER_NAME (+)
AND MORG.PARAMETER_NAME = 'MSD_MASTER_ORG' AND NOT EXISTS (SELECT 1
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_ITEM_ORG'
AND PARAMETER_VALUE = 'MSD_MASTER_ORG') AND ((TO_CHAR(MICV.CATEGORY_SET_ID) = PARA.PARAMETER_VALUE AND MSIV.ORGANIZATION_ID = MORG.PARAMETER_VALUE ) OR NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS MSIV2
, MTL_ITEM_CATEGORIES CAT1
, MSD_SETUP_PARAMETERS PARA2
, MSD_SETUP_PARAMETERS MORG3
, MSD_SETUP_PARAMETERS MSP1
WHERE MSIV2.INVENTORY_ITEM_ID = CAT1.INVENTORY_ITEM_ID AND MSIV2.ORGANIZATION_ID = CAT1.ORGANIZATION_ID AND TO_CHAR(CAT1.CATEGORY_SET_ID) = PARA2.PARAMETER_VALUE AND PARA2.PARAMETER_NAME = 'MSD_CATEGORY_SET_NAME' AND MSIV2.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID AND MSIV2.ORGANIZATION_ID = MORG.PARAMETER_VALUE AND MORG3.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE' AND MSP1.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING' AND DECODE( NVL(MSP1.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL( MSIV2.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG3.PARAMETER_VALUE
, '1')
, '4'
, DECODE(MSIV2.BOM_ITEM_TYPE
, 2
, 1
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSIV2.INVENTORY_ITEM_ID
, 1) )
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSIV2.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE(NVL(MSIV2.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG3.PARAMETER_VALUE
, '1')
, '4'
, DECODE(MSIV2.BOM_ITEM_TYPE
, 2
, 1
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSIV2.INVENTORY_ITEM_ID) )
, 2 )
, 1) ) = 1 AND DECODE( NVL(MORG3.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSIV2.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND (MSIV2.MRP_PLANNING_CODE <> 6 OR (MSIV2.MRP_PLANNING_CODE = 6
AND MSIV2.PICK_COMPONENTS_FLAG = 'Y'))
AND MSIV2.BOM_ITEM_TYPE <> 5 ) ) UNION ALL SELECT /*+ ORDERED */ DISTINCT MSIV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSIV.CONCATENATED_SEGMENTS
, MSIV_TL.DESCRIPTION
, DECODE( MCS.MULT_ITEM_CAT_ASSIGN_FLAG
, 'Y'
, MSD_SR_UTIL.GET_NULL_PK
, DECODE(MSIV.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, DECODE(PARA.PARAMETER_VALUE
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, DECODE(MICV.CATEGORY_ID
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, MICV.CATEGORY_ID) )
, MSD_SR_UTIL.GET_NULL_PK) ) CATEGORY_ID
, DECODE( MCS.MULT_ITEM_CAT_ASSIGN_FLAG
, 'Y'
, MSD_SR_UTIL.GET_NULL_DESC
, DECODE( MSIV.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, DECODE( PARA.PARAMETER_VALUE
, NULL
, MSD_SR_UTIL.GET_NULL_DESC
, DECODE( MICV.CATEGORY_ID
, NULL
, MSD_SR_UTIL.GET_NULL_DESC
, MC.CONCATENATED_SEGMENTS) )
, MSD_SR_UTIL.GET_NULL_DESC ) )
, DECODE( MCS.MULT_ITEM_CAT_ASSIGN_FLAG
, 'Y'
, MSD_SR_UTIL.GET_NULL_DESC
, DECODE( MSIV.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, DECODE( PARA.PARAMETER_VALUE
, NULL
, MSD_SR_UTIL.GET_NULL_DESC
, DECODE( MICV.CATEGORY_ID
, NULL
, MSD_SR_UTIL.GET_NULL_DESC
, MC.CONCATENATED_SEGMENTS) )
, MSD_SR_UTIL.GET_NULL_DESC ) )
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL FROM MSD_SETUP_PARAMETERS MORG
, MSD_SETUP_PARAMETERS MORG2
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS PARA
, MTL_SYSTEM_ITEMS_KFV MSIV
, MTL_ITEM_CATEGORIES MICV
, MTL_CATEGORIES_B_KFV MC
, MTL_CATEGORY_SETS_B MCS
, MTL_SYSTEM_ITEMS_TL MSIV_TL WHERE MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING' AND MORG2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE' AND MICV.CATEGORY_ID = MC.CATEGORY_ID AND MICV.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID AND MSIV.INVENTORY_ITEM_ID = MSIV_TL.INVENTORY_ITEM_ID AND MSIV.ORGANIZATION_ID = MSIV_TL.ORGANIZATION_ID AND MSIV_TL.LANGUAGE = USERENV('LANG') AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSIV.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG2.PARAMETER_VALUE
, '1')
, '4'
, DECODE(MSIV.BOM_ITEM_TYPE
, 2
, 1
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSIV.INVENTORY_ITEM_ID
, 1) )
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, MSIV.INVENTORY_ITEM_ID
, 2))
, 1)
, DECODE( NVL(MSIV.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MORG2.PARAMETER_VALUE
, '1')
, '4'
, DECODE(MSIV.BOM_ITEM_TYPE
, 2
, 1
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSIV.INVENTORY_ITEM_ID
, MORG.PARAMETER_VALUE) )
, 2 )
, 1) ) = 1 AND DECODE( NVL(MORG2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSIV.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1 AND MICV.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID AND MICV.ORGANIZATION_ID = MSIV.ORGANIZATION_ID
AND (MSIV.MRP_PLANNING_CODE <> 6 OR (MSIV.MRP_PLANNING_CODE = 6
AND MSIV.PICK_COMPONENTS_FLAG = 'Y'))
AND MSIV.BOM_ITEM_TYPE <> 5
AND TO_CHAR(MICV.CATEGORY_SET_ID) = PARA.PARAMETER_VALUE (+)
AND 'MSD_CATEGORY_SET_NAME'= PARA.PARAMETER_NAME (+)
AND MORG.PARAMETER_NAME = 'MSD_MASTER_ORG' AND TO_CHAR(MICV.CATEGORY_SET_ID) = PARA.PARAMETER_VALUE AND MSIV.ORGANIZATION_ID = MORG.PARAMETER_VALUE AND EXISTS (SELECT 1
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_ITEM_ORG'
AND PARAMETER_VALUE = 'MSD_MASTER_ORG')