VIEW: APPS.MSD_EVT_PRODUCT_DETAILS_V

Source

View Text - Preformatted

SELECT row_id, instance, event_id, seq_id, detail_id, time_lvl_val_from, time_lvl_val_to, product_lvl_id, product_lvl_name, product_lvl_val, product_lvl_pk, sr_product_lvl_pk, qty_modification_type_id, price_modification_type_id, qty_modification_type, price_modification_type, qty_modification_factor, price_modification_factor, geography_lvl_id, geography_lvl_name, geography_lvl_val, geography_lvl_pk, sr_geography_lvl_pk, saleschannel_lvl_id, saleschannel_lvl_name, saleschannel_lvl_val, saleschannel_lvl_pk, sr_saleschannel_lvl_pk, salesrep_lvl_id, salesrep_lvl_name, salesrep_lvl_val, salesrep_lvl_pk, sr_salesrep_lvl_pk, organization_lvl_id, organization_lvl_name, organization_lvl_val, organization_lvl_pk, sr_organization_lvl_pk, user_defined1_lvl_id, user_defined1_lvl_name, user_defined1_lvl_val, user_defined1_lvl_pk, sr_user_defined1_lvl_pk, user_defined2_lvl_id, user_defined2_lvl_name, user_defined2_lvl_val, user_defined2_lvl_pk, sr_user_defined2_lvl_pk, last_update_date, last_updated_by, creation_date, created_by, last_update_login, request_id, program_application_id, program_id, program_update_date, demand_class_lvl_id, demand_class_lvl_name, demand_class_lvl_val, demand_class_lvl_pk, sr_demand_class_lvl_pk FROM ( SELECT med.rowid row_id, med.instance, med.event_id, med.seq_id, med.detail_id, med.time_lvl_val_from, med.time_lvl_val_to, med.product_lvl_id, msd_common_utilities.get_level_name(med.product_lvl_id) product_lvl_name, prd.level_value product_lvl_val, prd.level_pk product_lvl_pk, med.sr_product_lvl_pk, prd.sr_level_pk prd_sr_level_pk, med.QTY_MODIFICATION_TYPE qty_modification_type_id, med.PRICE_MODIFICATION_TYPE price_modification_type_id, flv1.MEANING qty_modification_type, flv2.MEANING price_modification_type, med.qty_modification_factor, med.price_modification_factor, med.geography_lvl_id, msd_common_utilities.get_level_name(med.geography_lvl_id) geography_lvl_name, geo.level_value geography_lvl_val, geo.level_pk geography_lvl_pk, med.sr_geography_lvl_pk, geo.sr_level_pk geo_sr_level_pk, med.saleschannel_lvl_id, msd_common_utilities.get_level_name(med.saleschannel_lvl_id) saleschannel_lvl_name, chn.level_value saleschannel_lvl_val, chn.level_pk saleschannel_lvl_pk, med.sr_saleschannel_lvl_pk, chn.sr_level_pk chn_sr_level_pk, med.salesrep_lvl_id, msd_common_utilities.get_level_name(med.salesrep_lvl_id) salesrep_lvl_name, rep.level_value salesrep_lvl_val, rep.level_pk salesrep_lvl_pk, med.sr_salesrep_lvl_pk, rep.sr_level_pk rep_sr_level_pk, med.organization_lvl_id, msd_common_utilities.get_level_name(med.organization_lvl_id) organization_lvl_name, org.level_value organization_lvl_val, org.level_pk organization_lvl_pk, med.sr_organization_lvl_pk, org.sr_level_pk org_sr_level_pk, med.user_defined1_lvl_id, msd_common_utilities.get_level_name(med.user_defined1_lvl_id) user_defined1_lvl_name, ud1.level_value user_defined1_lvl_val, ud1.level_pk user_defined1_lvl_pk, med.sr_user_defined1_lvl_pk sr_user_defined1_lvl_pk, ud1.sr_level_pk ud1_sr_level_pk, med.user_defined2_lvl_id, msd_common_utilities.get_level_name(med.user_defined2_lvl_id) user_defined2_lvl_name, ud2.level_value user_defined2_lvl_val, ud2.level_pk user_defined2_lvl_pk, med.sr_user_defined2_lvl_pk sr_user_defined2_lvl_pk, ud2.sr_level_pk ud2_sr_level_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) demand_class_lvl_name, dcs.level_value demand_class_lvl_val, dcs.level_pk demand_class_lvl_pk, med.sr_demand_class_lvl_pk, dcs.sr_level_pk dcs_sr_level_pk FROM fnd_lookup_values_vl flv1, fnd_lookup_values_vl flv2, msd_events me, msd_event_products mep, MSD_EVT_PRODUCT_DETAILS med, msd_level_values prd, msd_level_values org, msd_level_values dcs, msd_level_values geo, msd_level_values chn, msd_level_values rep, msd_level_values ud1, msd_level_values ud2 WHERE flv1.lookup_code (+) = med.qty_modification_type AND flv1.lookup_type (+) = 'MSD_MODIFICATION_TYPE' AND flv2.lookup_code (+) = med.price_modification_type AND flv2.lookup_type (+) = 'MSD_MODIFICATION_TYPE' AND me.event_id = mep.event_id AND mep.event_id = med.event_id AND mep.seq_id = med.seq_id AND med.relation_id IS NULL AND prd.instance (+) = med.instance AND prd.level_id (+) = med.product_lvl_id AND prd.sr_level_pk(+) = med.sr_product_lvl_pk AND dcs.instance (+) = med.instance AND dcs.level_id (+) = med.demand_class_lvl_id AND dcs.sr_level_pk(+) = med.sr_demand_class_lvl_pk AND org.instance (+) = med.instance AND org.level_id(+) = med.organization_lvl_id AND org.sr_level_pk(+) = med.sr_organization_lvl_pk AND geo.instance (+) = med.instance AND geo.level_id(+) = med.geography_lvl_id AND geo.sr_level_pk(+) = med.sr_geography_lvl_pk AND chn.instance (+) = med.instance AND chn.level_id(+) = med.saleschannel_lvl_id AND chn.sr_level_pk(+) = med.sr_saleschannel_lvl_pk AND rep.instance (+) = med.instance AND rep.level_id(+) = med.salesrep_lvl_id AND rep.sr_level_pk(+) = med.sr_salesrep_lvl_pk AND ud1.instance (+) = med.instance AND ud1.level_id(+) = med.user_defined1_lvl_id AND ud1.sr_level_pk(+) = med.sr_user_defined1_lvl_pk AND ud2.instance (+) = med.instance AND ud2.level_id(+) = med.user_defined2_lvl_id AND ud2.sr_level_pk(+) = med.sr_user_defined2_lvl_pk)

View Text - HTML Formatted

SELECT ROW_ID
, INSTANCE
, EVENT_ID
, SEQ_ID
, DETAIL_ID
, TIME_LVL_VAL_FROM
, TIME_LVL_VAL_TO
, PRODUCT_LVL_ID
, PRODUCT_LVL_NAME
, PRODUCT_LVL_VAL
, PRODUCT_LVL_PK
, SR_PRODUCT_LVL_PK
, QTY_MODIFICATION_TYPE_ID
, PRICE_MODIFICATION_TYPE_ID
, QTY_MODIFICATION_TYPE
, PRICE_MODIFICATION_TYPE
, QTY_MODIFICATION_FACTOR
, PRICE_MODIFICATION_FACTOR
, GEOGRAPHY_LVL_ID
, GEOGRAPHY_LVL_NAME
, GEOGRAPHY_LVL_VAL
, GEOGRAPHY_LVL_PK
, SR_GEOGRAPHY_LVL_PK
, SALESCHANNEL_LVL_ID
, SALESCHANNEL_LVL_NAME
, SALESCHANNEL_LVL_VAL
, SALESCHANNEL_LVL_PK
, SR_SALESCHANNEL_LVL_PK
, SALESREP_LVL_ID
, SALESREP_LVL_NAME
, SALESREP_LVL_VAL
, SALESREP_LVL_PK
, SR_SALESREP_LVL_PK
, ORGANIZATION_LVL_ID
, ORGANIZATION_LVL_NAME
, ORGANIZATION_LVL_VAL
, ORGANIZATION_LVL_PK
, SR_ORGANIZATION_LVL_PK
, USER_DEFINED1_LVL_ID
, USER_DEFINED1_LVL_NAME
, USER_DEFINED1_LVL_VAL
, USER_DEFINED1_LVL_PK
, SR_USER_DEFINED1_LVL_PK
, USER_DEFINED2_LVL_ID
, USER_DEFINED2_LVL_NAME
, USER_DEFINED2_LVL_VAL
, USER_DEFINED2_LVL_PK
, SR_USER_DEFINED2_LVL_PK
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, DEMAND_CLASS_LVL_ID
, DEMAND_CLASS_LVL_NAME
, DEMAND_CLASS_LVL_VAL
, DEMAND_CLASS_LVL_PK
, SR_DEMAND_CLASS_LVL_PK
FROM ( SELECT MED.ROWID ROW_ID
, MED.INSTANCE
, MED.EVENT_ID
, MED.SEQ_ID
, MED.DETAIL_ID
, MED.TIME_LVL_VAL_FROM
, MED.TIME_LVL_VAL_TO
, MED.PRODUCT_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.PRODUCT_LVL_ID) PRODUCT_LVL_NAME
, PRD.LEVEL_VALUE PRODUCT_LVL_VAL
, PRD.LEVEL_PK PRODUCT_LVL_PK
, MED.SR_PRODUCT_LVL_PK
, PRD.SR_LEVEL_PK PRD_SR_LEVEL_PK
, MED.QTY_MODIFICATION_TYPE QTY_MODIFICATION_TYPE_ID
, MED.PRICE_MODIFICATION_TYPE PRICE_MODIFICATION_TYPE_ID
, FLV1.MEANING QTY_MODIFICATION_TYPE
, FLV2.MEANING PRICE_MODIFICATION_TYPE
, MED.QTY_MODIFICATION_FACTOR
, MED.PRICE_MODIFICATION_FACTOR
, MED.GEOGRAPHY_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.GEOGRAPHY_LVL_ID) GEOGRAPHY_LVL_NAME
, GEO.LEVEL_VALUE GEOGRAPHY_LVL_VAL
, GEO.LEVEL_PK GEOGRAPHY_LVL_PK
, MED.SR_GEOGRAPHY_LVL_PK
, GEO.SR_LEVEL_PK GEO_SR_LEVEL_PK
, MED.SALESCHANNEL_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.SALESCHANNEL_LVL_ID) SALESCHANNEL_LVL_NAME
, CHN.LEVEL_VALUE SALESCHANNEL_LVL_VAL
, CHN.LEVEL_PK SALESCHANNEL_LVL_PK
, MED.SR_SALESCHANNEL_LVL_PK
, CHN.SR_LEVEL_PK CHN_SR_LEVEL_PK
, MED.SALESREP_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.SALESREP_LVL_ID) SALESREP_LVL_NAME
, REP.LEVEL_VALUE SALESREP_LVL_VAL
, REP.LEVEL_PK SALESREP_LVL_PK
, MED.SR_SALESREP_LVL_PK
, REP.SR_LEVEL_PK REP_SR_LEVEL_PK
, MED.ORGANIZATION_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.ORGANIZATION_LVL_ID) ORGANIZATION_LVL_NAME
, ORG.LEVEL_VALUE ORGANIZATION_LVL_VAL
, ORG.LEVEL_PK ORGANIZATION_LVL_PK
, MED.SR_ORGANIZATION_LVL_PK
, ORG.SR_LEVEL_PK ORG_SR_LEVEL_PK
, MED.USER_DEFINED1_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.USER_DEFINED1_LVL_ID) USER_DEFINED1_LVL_NAME
, UD1.LEVEL_VALUE USER_DEFINED1_LVL_VAL
, UD1.LEVEL_PK USER_DEFINED1_LVL_PK
, MED.SR_USER_DEFINED1_LVL_PK SR_USER_DEFINED1_LVL_PK
, UD1.SR_LEVEL_PK UD1_SR_LEVEL_PK
, MED.USER_DEFINED2_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.USER_DEFINED2_LVL_ID) USER_DEFINED2_LVL_NAME
, UD2.LEVEL_VALUE USER_DEFINED2_LVL_VAL
, UD2.LEVEL_PK USER_DEFINED2_LVL_PK
, MED.SR_USER_DEFINED2_LVL_PK SR_USER_DEFINED2_LVL_PK
, UD2.SR_LEVEL_PK UD2_SR_LEVEL_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) DEMAND_CLASS_LVL_NAME
, DCS.LEVEL_VALUE DEMAND_CLASS_LVL_VAL
, DCS.LEVEL_PK DEMAND_CLASS_LVL_PK
, MED.SR_DEMAND_CLASS_LVL_PK
, DCS.SR_LEVEL_PK DCS_SR_LEVEL_PK
FROM FND_LOOKUP_VALUES_VL FLV1
, FND_LOOKUP_VALUES_VL FLV2
, MSD_EVENTS ME
, MSD_EVENT_PRODUCTS MEP
, MSD_EVT_PRODUCT_DETAILS MED
, MSD_LEVEL_VALUES PRD
, MSD_LEVEL_VALUES ORG
, MSD_LEVEL_VALUES DCS
, MSD_LEVEL_VALUES GEO
, MSD_LEVEL_VALUES CHN
, MSD_LEVEL_VALUES REP
, MSD_LEVEL_VALUES UD1
, MSD_LEVEL_VALUES UD2
WHERE FLV1.LOOKUP_CODE (+) = MED.QTY_MODIFICATION_TYPE
AND FLV1.LOOKUP_TYPE (+) = 'MSD_MODIFICATION_TYPE'
AND FLV2.LOOKUP_CODE (+) = MED.PRICE_MODIFICATION_TYPE
AND FLV2.LOOKUP_TYPE (+) = 'MSD_MODIFICATION_TYPE'
AND ME.EVENT_ID = MEP.EVENT_ID
AND MEP.EVENT_ID = MED.EVENT_ID
AND MEP.SEQ_ID = MED.SEQ_ID
AND MED.RELATION_ID IS NULL
AND PRD.INSTANCE (+) = MED.INSTANCE
AND PRD.LEVEL_ID (+) = MED.PRODUCT_LVL_ID
AND PRD.SR_LEVEL_PK(+) = MED.SR_PRODUCT_LVL_PK
AND DCS.INSTANCE (+) = MED.INSTANCE
AND DCS.LEVEL_ID (+) = MED.DEMAND_CLASS_LVL_ID
AND DCS.SR_LEVEL_PK(+) = MED.SR_DEMAND_CLASS_LVL_PK
AND ORG.INSTANCE (+) = MED.INSTANCE
AND ORG.LEVEL_ID(+) = MED.ORGANIZATION_LVL_ID
AND ORG.SR_LEVEL_PK(+) = MED.SR_ORGANIZATION_LVL_PK
AND GEO.INSTANCE (+) = MED.INSTANCE
AND GEO.LEVEL_ID(+) = MED.GEOGRAPHY_LVL_ID
AND GEO.SR_LEVEL_PK(+) = MED.SR_GEOGRAPHY_LVL_PK
AND CHN.INSTANCE (+) = MED.INSTANCE
AND CHN.LEVEL_ID(+) = MED.SALESCHANNEL_LVL_ID
AND CHN.SR_LEVEL_PK(+) = MED.SR_SALESCHANNEL_LVL_PK
AND REP.INSTANCE (+) = MED.INSTANCE
AND REP.LEVEL_ID(+) = MED.SALESREP_LVL_ID
AND REP.SR_LEVEL_PK(+) = MED.SR_SALESREP_LVL_PK
AND UD1.INSTANCE (+) = MED.INSTANCE
AND UD1.LEVEL_ID(+) = MED.USER_DEFINED1_LVL_ID
AND UD1.SR_LEVEL_PK(+) = MED.SR_USER_DEFINED1_LVL_PK
AND UD2.INSTANCE (+) = MED.INSTANCE
AND UD2.LEVEL_ID(+) = MED.USER_DEFINED2_LVL_ID
AND UD2.SR_LEVEL_PK(+) = MED.SR_USER_DEFINED2_LVL_PK)