VIEW: APPS.MSD_NPI_RELATED_PROD_DTLS_V1

Source

View Text - Preformatted

SELECT med.rowid row_id, med.instance, med.event_id, med.seq_id, med.detail_id, mpr.relation_id, mpr.product_lvl_id, msd_common_utilities.get_level_name(med.product_lvl_id), mpr.product_lvl_val, msd_common_utilities.get_item_key(mpr.instance, mpr.sr_product_lvl_pk, mpr.product_lvl_val, mpr.product_lvl_id), mpr.sr_product_lvl_pk, (100 - mpr.qty_modification_factor), mpr.lag, mpr.start_time, mpr.end_time, mpr.QTY_MODIFICATION_TYPE, flv1.MEANING, mpr.qty_modification_factor, mpr.npi_prod_relationship, flv.meaning, med.geography_lvl_id, msd_common_utilities.get_level_name(med.geography_lvl_id), med.geography_lvl_val, msd_common_utilities.get_loc_key(med.instance, med.sr_geography_lvl_pk, med.geography_lvl_val, med.geography_lvl_id), med.sr_geography_lvl_pk, med.saleschannel_lvl_id, msd_common_utilities.get_level_name(med.saleschannel_lvl_id), med.saleschannel_lvl_val, msd_common_utilities.get_sc_key(med.instance,med.sr_saleschannel_lvl_pk,med.saleschannel_lvl_val, med.saleschannel_lvl_id), med.sr_saleschannel_lvl_pk, med.salesrep_lvl_id, msd_common_utilities.get_level_name(med.salesrep_lvl_id), med.salesrep_lvl_val, msd_common_utilities.get_salesrep_key(med.instance, med.sr_salesrep_lvl_pk, med.salesrep_lvl_val, med.salesrep_lvl_id), med.sr_salesrep_lvl_pk, med.organization_lvl_id, msd_common_utilities.get_level_name(med.organization_lvl_id), med.organization_lvl_val, msd_common_utilities.get_org_key(med.instance, med.sr_organization_lvl_pk, med.organization_lvl_val,med.organization_lvl_id), med.sr_organization_lvl_pk, med.user_defined1_lvl_id, msd_common_utilities.get_level_name(med.user_defined1_lvl_id), med.user_defined1_lvl_val, msd_common_utilities.get_level_value_pk(med.instance,med.sr_user_defined1_lvl_pk, med.user_defined1_lvl_val,med.user_defined1_lvl_id), med.sr_user_defined1_lvl_pk, med.user_defined2_lvl_id, msd_common_utilities.get_level_name(med.user_defined2_lvl_id), med.user_defined2_lvl_val, msd_common_utilities.get_level_value_pk(med.instance,med.sr_user_defined2_lvl_pk, med.user_defined2_lvl_val,med.user_defined2_lvl_id), med.sr_user_defined2_lvl_pk, med.last_update_date, med.last_updated_by, med.creation_date, med.created_by, med.last_update_login, med.request_id, med.program_application_id, med.program_id, med.program_update_date, med.demand_class_lvl_id, msd_common_utilities.get_level_name(med.demand_class_lvl_id), med.demand_class_lvl_val, msd_common_utilities.get_loc_key(med.instance, med.sr_demand_class_lvl_pk, med.demand_class_lvl_val, med.demand_class_lvl_id), med.sr_demand_class_lvl_pk, decode(nvl(msd_common_utilities.get_dp_enabled_flag(mpr.instance, mpr.sr_product_lvl_pk, mpr.product_lvl_val, mpr.product_lvl_id), 1), 1, msd_common_utilities.get_dp_enabled_flag(med.instance, med.sr_geography_lvl_pk, med.geography_lvl_val, med.geography_lvl_id), 2) FROM fnd_lookup_values_vl flv1, fnd_lookup_values_vl flv, msd_events me, msd_event_products mep, msd_evt_product_details med, MSD_EVT_PROD_RELATIONSHIPS mpr WHERE flv1.lookup_code (+) = mpr.qty_modification_type AND flv1.lookup_type (+) = 'MSD_MODIFICATION_TYPE' AND me.event_id = mep.event_id AND mpr.event_id = mep.event_id AND mpr.seq_id = mep.seq_id AND med.relation_id = mpr.relation_id AND med.event_id = mpr.event_id AND med.seq_id = mpr.seq_id AND med.relation_id is not null AND flv.lookup_code = mpr.NPI_PROD_RELATIONSHIP AND flv.lookup_type = 'MSD_RELATIONSHIP_TYPE'

View Text - HTML Formatted

SELECT MED.ROWID ROW_ID
, MED.INSTANCE
, MED.EVENT_ID
, MED.SEQ_ID
, MED.DETAIL_ID
, MPR.RELATION_ID
, MPR.PRODUCT_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.PRODUCT_LVL_ID)
, MPR.PRODUCT_LVL_VAL
, MSD_COMMON_UTILITIES.GET_ITEM_KEY(MPR.INSTANCE
, MPR.SR_PRODUCT_LVL_PK
, MPR.PRODUCT_LVL_VAL
, MPR.PRODUCT_LVL_ID)
, MPR.SR_PRODUCT_LVL_PK
, (100 - MPR.QTY_MODIFICATION_FACTOR)
, MPR.LAG
, MPR.START_TIME
, MPR.END_TIME
, MPR.QTY_MODIFICATION_TYPE
, FLV1.MEANING
, MPR.QTY_MODIFICATION_FACTOR
, MPR.NPI_PROD_RELATIONSHIP
, FLV.MEANING
, MED.GEOGRAPHY_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.GEOGRAPHY_LVL_ID)
, MED.GEOGRAPHY_LVL_VAL
, MSD_COMMON_UTILITIES.GET_LOC_KEY(MED.INSTANCE
, MED.SR_GEOGRAPHY_LVL_PK
, MED.GEOGRAPHY_LVL_VAL
, MED.GEOGRAPHY_LVL_ID)
, MED.SR_GEOGRAPHY_LVL_PK
, MED.SALESCHANNEL_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.SALESCHANNEL_LVL_ID)
, MED.SALESCHANNEL_LVL_VAL
, MSD_COMMON_UTILITIES.GET_SC_KEY(MED.INSTANCE
, MED.SR_SALESCHANNEL_LVL_PK
, MED.SALESCHANNEL_LVL_VAL
, MED.SALESCHANNEL_LVL_ID)
, MED.SR_SALESCHANNEL_LVL_PK
, MED.SALESREP_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.SALESREP_LVL_ID)
, MED.SALESREP_LVL_VAL
, MSD_COMMON_UTILITIES.GET_SALESREP_KEY(MED.INSTANCE
, MED.SR_SALESREP_LVL_PK
, MED.SALESREP_LVL_VAL
, MED.SALESREP_LVL_ID)
, MED.SR_SALESREP_LVL_PK
, MED.ORGANIZATION_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.ORGANIZATION_LVL_ID)
, MED.ORGANIZATION_LVL_VAL
, MSD_COMMON_UTILITIES.GET_ORG_KEY(MED.INSTANCE
, MED.SR_ORGANIZATION_LVL_PK
, MED.ORGANIZATION_LVL_VAL
, MED.ORGANIZATION_LVL_ID)
, MED.SR_ORGANIZATION_LVL_PK
, MED.USER_DEFINED1_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.USER_DEFINED1_LVL_ID)
, MED.USER_DEFINED1_LVL_VAL
, MSD_COMMON_UTILITIES.GET_LEVEL_VALUE_PK(MED.INSTANCE
, MED.SR_USER_DEFINED1_LVL_PK
, MED.USER_DEFINED1_LVL_VAL
, MED.USER_DEFINED1_LVL_ID)
, MED.SR_USER_DEFINED1_LVL_PK
, MED.USER_DEFINED2_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.USER_DEFINED2_LVL_ID)
, MED.USER_DEFINED2_LVL_VAL
, MSD_COMMON_UTILITIES.GET_LEVEL_VALUE_PK(MED.INSTANCE
, MED.SR_USER_DEFINED2_LVL_PK
, MED.USER_DEFINED2_LVL_VAL
, MED.USER_DEFINED2_LVL_ID)
, MED.SR_USER_DEFINED2_LVL_PK
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
, MED.REQUEST_ID
, MED.PROGRAM_APPLICATION_ID
, MED.PROGRAM_ID
, MED.PROGRAM_UPDATE_DATE
, MED.DEMAND_CLASS_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.DEMAND_CLASS_LVL_ID)
, MED.DEMAND_CLASS_LVL_VAL
, MSD_COMMON_UTILITIES.GET_LOC_KEY(MED.INSTANCE
, MED.SR_DEMAND_CLASS_LVL_PK
, MED.DEMAND_CLASS_LVL_VAL
, MED.DEMAND_CLASS_LVL_ID)
, MED.SR_DEMAND_CLASS_LVL_PK
, DECODE(NVL(MSD_COMMON_UTILITIES.GET_DP_ENABLED_FLAG(MPR.INSTANCE
, MPR.SR_PRODUCT_LVL_PK
, MPR.PRODUCT_LVL_VAL
, MPR.PRODUCT_LVL_ID)
, 1)
, 1
, MSD_COMMON_UTILITIES.GET_DP_ENABLED_FLAG(MED.INSTANCE
, MED.SR_GEOGRAPHY_LVL_PK
, MED.GEOGRAPHY_LVL_VAL
, MED.GEOGRAPHY_LVL_ID)
, 2)
FROM FND_LOOKUP_VALUES_VL FLV1
, FND_LOOKUP_VALUES_VL FLV
, MSD_EVENTS ME
, MSD_EVENT_PRODUCTS MEP
, MSD_EVT_PRODUCT_DETAILS MED
, MSD_EVT_PROD_RELATIONSHIPS MPR
WHERE FLV1.LOOKUP_CODE (+) = MPR.QTY_MODIFICATION_TYPE
AND FLV1.LOOKUP_TYPE (+) = 'MSD_MODIFICATION_TYPE'
AND ME.EVENT_ID = MEP.EVENT_ID
AND MPR.EVENT_ID = MEP.EVENT_ID
AND MPR.SEQ_ID = MEP.SEQ_ID
AND MED.RELATION_ID = MPR.RELATION_ID
AND MED.EVENT_ID = MPR.EVENT_ID
AND MED.SEQ_ID = MPR.SEQ_ID
AND MED.RELATION_ID IS NOT NULL
AND FLV.LOOKUP_CODE = MPR.NPI_PROD_RELATIONSHIP
AND FLV.LOOKUP_TYPE = 'MSD_RELATIONSHIP_TYPE'