VIEW: APPS.MSD_NPI_RELATED_PROD_DETAILS_V

Source

View Text - Preformatted

SELECT row_id, instance, event_id, seq_id, detail_id, relation_id, product_lvl_id, product_lvl_name, product_lvl_val, product_lvl_pk, sr_product_lvl_pk, product_weight, lag_time, start_time, end_time, qty_modification_type_id, qty_modification_type, qty_modification_factor, npi_prod_relationship_code, npi_prod_relationship, 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, mpr.relation_id, mpr.product_lvl_id, msd_common_utilities.get_level_name(mep.product_lvl_id) product_lvl_name, prd.level_value product_lvl_val, prd.level_pk product_lvl_pk, mpr.sr_product_lvl_pk, prd.sr_level_pk prd_sr_level_pk, (100 - mpr.qty_modification_factor) product_weight, mpr.lag lag_time, mpr.start_time, mpr.end_time, mpr.QTY_MODIFICATION_TYPE qty_modification_type_id, flv1.MEANING qty_modification_type, mpr.qty_modification_factor, mpr.npi_prod_relationship npi_prod_relationship_code, flv.meaning npi_prod_relationship, 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, 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, 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 flv, msd_events me, msd_event_products mep, msd_evt_product_details med, MSD_EVT_PROD_RELATIONSHIPS mpr, msd_level_values prd, msd_level_values org, msd_level_values geo, msd_level_values dcs, msd_level_values chn, msd_level_values rep, msd_level_values ud1, msd_level_values ud2 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' AND prd.instance (+) = mpr.instance AND prd.level_id (+) = mpr.product_lvl_id AND prd.sr_level_pk (+) = mpr.sr_product_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 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 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
, RELATION_ID
, PRODUCT_LVL_ID
, PRODUCT_LVL_NAME
, PRODUCT_LVL_VAL
, PRODUCT_LVL_PK
, SR_PRODUCT_LVL_PK
, PRODUCT_WEIGHT
, LAG_TIME
, START_TIME
, END_TIME
, QTY_MODIFICATION_TYPE_ID
, QTY_MODIFICATION_TYPE
, QTY_MODIFICATION_FACTOR
, NPI_PROD_RELATIONSHIP_CODE
, NPI_PROD_RELATIONSHIP
, 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
, MPR.RELATION_ID
, MPR.PRODUCT_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MEP.PRODUCT_LVL_ID) PRODUCT_LVL_NAME
, PRD.LEVEL_VALUE PRODUCT_LVL_VAL
, PRD.LEVEL_PK PRODUCT_LVL_PK
, MPR.SR_PRODUCT_LVL_PK
, PRD.SR_LEVEL_PK PRD_SR_LEVEL_PK
, (100 - MPR.QTY_MODIFICATION_FACTOR) PRODUCT_WEIGHT
, MPR.LAG LAG_TIME
, MPR.START_TIME
, MPR.END_TIME
, MPR.QTY_MODIFICATION_TYPE QTY_MODIFICATION_TYPE_ID
, FLV1.MEANING QTY_MODIFICATION_TYPE
, MPR.QTY_MODIFICATION_FACTOR
, MPR.NPI_PROD_RELATIONSHIP NPI_PROD_RELATIONSHIP_CODE
, FLV.MEANING NPI_PROD_RELATIONSHIP
, 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
, 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
, 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 FLV
, MSD_EVENTS ME
, MSD_EVENT_PRODUCTS MEP
, MSD_EVT_PRODUCT_DETAILS MED
, MSD_EVT_PROD_RELATIONSHIPS MPR
, MSD_LEVEL_VALUES PRD
, MSD_LEVEL_VALUES ORG
, MSD_LEVEL_VALUES GEO
, MSD_LEVEL_VALUES DCS
, MSD_LEVEL_VALUES CHN
, MSD_LEVEL_VALUES REP
, MSD_LEVEL_VALUES UD1
, MSD_LEVEL_VALUES UD2
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'
AND PRD.INSTANCE (+) = MPR.INSTANCE
AND PRD.LEVEL_ID (+) = MPR.PRODUCT_LVL_ID
AND PRD.SR_LEVEL_PK (+) = MPR.SR_PRODUCT_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 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 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 )