eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.QPR_PRICE_SALES_MEAS_V

Source

View Text - Preformatted


SELECT a.instance_id, a.psg_level_value, a.ord_level_value, a.prd_level_value, a.geo_level_value, a.cus_level_value, a.org_level_value, a.rep_level_value, a.chn_level_value, null, null, null, null, a.time_level_value, null, null, null, null, null, qpr_sr_util.ods_uom_conv(a.prd_level_value, a.measure_uom, qpr_maintain_aw.get_base_uom, a.instance_id) * a.measure1_number, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * a.measure2_number, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * (nvl(a.measure20_number,0) + a.measure2_number), qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * a.measure3_number/ qpr_sr_util.ods_uom_conv(a.prd_level_value, a.measure_uom, qpr_maintain_aw.get_base_uom, a.instance_id), qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * a.measure4_number/ qpr_sr_util.ods_uom_conv(a.prd_level_value, a.measure_uom, qpr_maintain_aw.get_base_uom, a.instance_id), qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * a.measure5_number/qpr_sr_util.ods_uom_conv(a.prd_level_value, a.measure_uom, qpr_maintain_aw.get_base_uom, a.instance_id), a.measure6_number, a.measure13_number, a.measure21_number, a.measure22_number, a.measure23_number, a.measure24_number, a.measure25_number, a.measure26_number, a.measure27_number, a.measure28_number, a.measure29_number, a.measure30_number, a.measure1_char, a.measure2_char, a.measure3_char, a.measure4_char, a.measure5_char, b.pass_exceptions, b.fail_exceptions, b.na_exceptions, b.hi_sever_thre, b.me_sever_thre, b.lo_sever_thre, b.hi_pol_imp_rank, b.me_pol_imp_rank, b.lo_pol_imp_rank, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * b.gross_rev_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * b.gross_rev_non_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * b.rev_at_lis_price, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * b.rev_at_pol_limit, c.pass_exceptions, c.fail_exceptions, c.na_exceptions, c.hi_sever_thre, c.me_sever_thre, c.lo_sever_thre, c.hi_pol_imp_rank, c.me_pol_imp_rank, c.lo_pol_imp_rank, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * c.gross_rev_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * c.gross_rev_non_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * c.rev_at_lis_price, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * c.rev_at_pol_limit, d.pass_exceptions, d.fail_exceptions, d.na_exceptions, d.hi_sever_thre, d.me_sever_thre, d.lo_sever_thre, d.hi_pol_imp_rank, d.me_pol_imp_rank, d.lo_pol_imp_rank, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * d.gross_rev_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * d.gross_rev_non_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * d.rev_at_lis_price, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * d.rev_at_pol_limit, e.pass_exceptions, e.fail_exceptions, e.na_exceptions, e.hi_sever_thre, e.me_sever_thre, e.lo_sever_thre, e.hi_pol_imp_rank, e.me_pol_imp_rank, e.lo_pol_imp_rank, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * e.gross_rev_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * e.gross_rev_non_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * e.rev_at_lis_price, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * e.rev_at_pol_limit, f.pass_exceptions, f.fail_exceptions, f.na_exceptions, f.hi_sever_thre, f.me_sever_thre, f.lo_sever_thre, f.hi_pol_imp_rank, f.me_pol_imp_rank, f.lo_pol_imp_rank, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * f.gross_rev_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * f.gross_rev_non_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * f.rev_at_lis_price, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * f.rev_at_pol_limit, g.pass_exceptions, g.fail_exceptions, g.na_exceptions, g.hi_sever_thre, g.me_sever_thre, g.lo_sever_thre, g.hi_pol_imp_rank, g.me_pol_imp_rank, g.lo_pol_imp_rank, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * g.gross_rev_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * g.gross_rev_non_comp, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * g.rev_at_lis_price, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * g.rev_at_pol_limit, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * (case when ( (nvl(b.gross_rev_non_comp,0) + nvl(c.gross_rev_non_comp,0) + nvl(d.gross_rev_non_comp,0) + nvl(e.gross_rev_non_comp,0) + nvl(f.gross_rev_non_comp,0) + nvl(g.gross_rev_non_comp,0)) <> 0) then measure2_number else 0 end), qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * (case when coalesce(b.fail_exceptions , c.fail_exceptions, d.fail_exceptions , e.fail_exceptions , f.fail_exceptions , g.fail_exceptions, -1) = -1 then 0 when (nvl(b.gross_rev_non_comp,0) + nvl(c.gross_rev_non_comp,0) + nvl(d.gross_rev_non_comp,0) + nvl(e.gross_rev_non_comp,0) + nvl(f.gross_rev_non_comp,0) + nvl(g.gross_rev_non_comp,0)) <> 0 then 0 else measure2_number end), measure_uom, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * a.measure5_number/qpr_sr_util.ods_uom_conv(a.prd_level_value, a.measure_uom, qpr_maintain_aw.get_base_uom, a.instance_id), qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * a.measure14_number, qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) * a.measure15_number, measure17_number, decode(nvl(measure1_char, '*'), 'MODEL', qpr_sr_util.ods_uom_conv(a.prd_level_value, a.measure_uom, qpr_maintain_aw.get_base_uom, a.instance_id) * a.measure1_number, decode(nvl(measure1_char, '*'), 'KIT', qpr_sr_util.ods_uom_conv(a.prd_level_value, a.measure_uom, qpr_maintain_aw.get_base_uom, a.instance_id) * a.measure1_number,0),0) from qpr_measure_data a, qpr_policy_meas_v b, qpr_policy_meas_v c, qpr_policy_meas_v d, qpr_policy_meas_v e, qpr_policy_meas_v f, qpr_policy_meas_v g WHERE a.measure_type_code = 'SALESDATA' and b.policy_type(+) = 'QPR_CEILING_POLICY_MEASURES' and c.policy_type(+) = 'QPR_FIELD_USER_POLICY_MEASURES' and d.policy_type(+) = 'QPR_TARGET_POLICY_MEASURES' and e.policy_type(+) = 'QPR_REGIONAL_POLICY_MEASURES' and f.policy_type(+) = 'QPR_CORPORATE_POLICY_MEASURES' and g.policy_type(+) = 'QPR_GSA_POLICY_MEASURES' and a.time_level_value between nvl(qpr_maintain_aw.get_start_date, a.time_level_value) and nvl(qpr_maintain_aw.get_end_date, a.time_level_value) and a.instance_id=qpr_maintain_aw.get_instance and a.instance_id = b.instance_id(+) and a.ord_level_value = b.ord_level_value(+) and a.instance_id = c.instance_id(+) and a.ord_level_value = c.ord_level_value(+) and a.instance_id = d.instance_id(+) and a.ord_level_value = d.ord_level_value(+) and a.instance_id = e.instance_id(+) and a.ord_level_value = e.ord_level_value(+) and a.instance_id = f.instance_id(+) and a.ord_level_value = f.ord_level_value(+) and a.instance_id = g.instance_id(+) and a.ord_level_value = g.ord_level_value(+) and qpr_sr_util.ods_uom_conv(a.prd_level_value, a.measure_uom, qpr_maintain_aw.get_base_uom, a.instance_id) > 0 and qpr_sr_util.ods_curr_conversion(null, qpr_maintain_aw.get_currency_code, null, a.time_level_value, a.instance_id) > 0 and (qpr_maintain_aw.get_ord_line is null or a.ord_level_value in (select qpm.attribute_1 from qpr_plan_measures qpm where qpm.price_plan_id = qpr_maintain_aw.get_price_plan_id and qpm.price_plan_meas_grp_id = 1 and qpm.run_number = qpr_maintain_aw.get_run_number)) and (qpr_maintain_aw.get_item is null or a.prd_level_value in ( select qpm2.attribute_1 from qpr_plan_measures qpm2 where qpm2.price_plan_id = qpr_maintain_aw.get_price_plan_id and qpm2.price_plan_meas_grp_id = 2 and qpm2.run_number=qpr_maintain_aw.get_run_number)) and (qpr_maintain_aw.get_tp_site is null or a.geo_level_value in (select qpm3.attribute_1 from qpr_plan_measures qpm3 where qpm3.price_plan_id = qpr_maintain_aw.get_price_plan_id and qpm3.price_plan_meas_grp_id = 3 and qpm3.run_number=qpr_maintain_aw.get_run_number)) and (qpr_maintain_aw.get_cus is null or a.cus_level_value in (select qpm4.attribute_1 from qpr_plan_measures qpm4 where qpm4.price_plan_id = qpr_maintain_aw.get_price_plan_id and qpm4.price_plan_meas_grp_id = 4 and qpm4.run_number = qpr_maintain_aw.get_run_number)) and (qpr_maintain_aw.get_ou is null or a.org_level_value in (select qpm5.attribute_1 from qpr_plan_measures qpm5 where qpm5.price_plan_id = qpr_maintain_aw.get_price_plan_id and qpm5.price_plan_meas_grp_id = 5 and qpm5.run_number = qpr_maintain_aw.get_run_number)) and (qpr_maintain_aw.get_sr is null or a.rep_level_value in (select qpm6.attribute_1 from qpr_plan_measures qpm6 where qpm6.price_plan_id = qpr_maintain_aw.get_price_plan_id and qpm6.price_plan_meas_grp_id = 6 and qpm6.run_number = qpr_maintain_aw.get_run_number)) and (qpr_maintain_aw.get_chn is null or a.chn_level_value in (select qpm7.attribute_1 from qpr_plan_measures qpm7 where qpm7.price_plan_id=qpr_maintain_aw.get_price_plan_id and qpm7.price_plan_meas_grp_id=7 and qpm7.run_number=qpr_maintain_aw.get_run_number)) and (qpr_maintain_aw.get_psg is null or a.psg_level_value in (select qpm8.attribute_1 from qpr_plan_measures qpm8 where qpm8.price_plan_id=qpr_maintain_aw.get_price_plan_id and qpm8.price_plan_meas_grp_id=8 and qpm8.run_number=qpr_maintain_aw.get_run_number))

View Text - HTML Formatted

SELECT A.INSTANCE_ID
, A.PSG_LEVEL_VALUE
, A.ORD_LEVEL_VALUE
, A.PRD_LEVEL_VALUE
, A.GEO_LEVEL_VALUE
, A.CUS_LEVEL_VALUE
, A.ORG_LEVEL_VALUE
, A.REP_LEVEL_VALUE
, A.CHN_LEVEL_VALUE
, NULL
, NULL
, NULL
, NULL
, A.TIME_LEVEL_VALUE
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.ODS_UOM_CONV(A.PRD_LEVEL_VALUE
, A.MEASURE_UOM
, QPR_MAINTAIN_AW.GET_BASE_UOM
, A.INSTANCE_ID) * A.MEASURE1_NUMBER
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * A.MEASURE2_NUMBER
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * (NVL(A.MEASURE20_NUMBER
, 0) + A.MEASURE2_NUMBER)
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * A.MEASURE3_NUMBER/ QPR_SR_UTIL.ODS_UOM_CONV(A.PRD_LEVEL_VALUE
, A.MEASURE_UOM
, QPR_MAINTAIN_AW.GET_BASE_UOM
, A.INSTANCE_ID)
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * A.MEASURE4_NUMBER/ QPR_SR_UTIL.ODS_UOM_CONV(A.PRD_LEVEL_VALUE
, A.MEASURE_UOM
, QPR_MAINTAIN_AW.GET_BASE_UOM
, A.INSTANCE_ID)
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * A.MEASURE5_NUMBER/QPR_SR_UTIL.ODS_UOM_CONV(A.PRD_LEVEL_VALUE
, A.MEASURE_UOM
, QPR_MAINTAIN_AW.GET_BASE_UOM
, A.INSTANCE_ID)
, A.MEASURE6_NUMBER
, A.MEASURE13_NUMBER
, A.MEASURE21_NUMBER
, A.MEASURE22_NUMBER
, A.MEASURE23_NUMBER
, A.MEASURE24_NUMBER
, A.MEASURE25_NUMBER
, A.MEASURE26_NUMBER
, A.MEASURE27_NUMBER
, A.MEASURE28_NUMBER
, A.MEASURE29_NUMBER
, A.MEASURE30_NUMBER
, A.MEASURE1_CHAR
, A.MEASURE2_CHAR
, A.MEASURE3_CHAR
, A.MEASURE4_CHAR
, A.MEASURE5_CHAR
, B.PASS_EXCEPTIONS
, B.FAIL_EXCEPTIONS
, B.NA_EXCEPTIONS
, B.HI_SEVER_THRE
, B.ME_SEVER_THRE
, B.LO_SEVER_THRE
, B.HI_POL_IMP_RANK
, B.ME_POL_IMP_RANK
, B.LO_POL_IMP_RANK
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * B.GROSS_REV_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * B.GROSS_REV_NON_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * B.REV_AT_LIS_PRICE
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * B.REV_AT_POL_LIMIT
, C.PASS_EXCEPTIONS
, C.FAIL_EXCEPTIONS
, C.NA_EXCEPTIONS
, C.HI_SEVER_THRE
, C.ME_SEVER_THRE
, C.LO_SEVER_THRE
, C.HI_POL_IMP_RANK
, C.ME_POL_IMP_RANK
, C.LO_POL_IMP_RANK
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * C.GROSS_REV_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * C.GROSS_REV_NON_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * C.REV_AT_LIS_PRICE
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * C.REV_AT_POL_LIMIT
, D.PASS_EXCEPTIONS
, D.FAIL_EXCEPTIONS
, D.NA_EXCEPTIONS
, D.HI_SEVER_THRE
, D.ME_SEVER_THRE
, D.LO_SEVER_THRE
, D.HI_POL_IMP_RANK
, D.ME_POL_IMP_RANK
, D.LO_POL_IMP_RANK
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * D.GROSS_REV_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * D.GROSS_REV_NON_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * D.REV_AT_LIS_PRICE
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * D.REV_AT_POL_LIMIT
, E.PASS_EXCEPTIONS
, E.FAIL_EXCEPTIONS
, E.NA_EXCEPTIONS
, E.HI_SEVER_THRE
, E.ME_SEVER_THRE
, E.LO_SEVER_THRE
, E.HI_POL_IMP_RANK
, E.ME_POL_IMP_RANK
, E.LO_POL_IMP_RANK
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * E.GROSS_REV_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * E.GROSS_REV_NON_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * E.REV_AT_LIS_PRICE
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * E.REV_AT_POL_LIMIT
, F.PASS_EXCEPTIONS
, F.FAIL_EXCEPTIONS
, F.NA_EXCEPTIONS
, F.HI_SEVER_THRE
, F.ME_SEVER_THRE
, F.LO_SEVER_THRE
, F.HI_POL_IMP_RANK
, F.ME_POL_IMP_RANK
, F.LO_POL_IMP_RANK
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * F.GROSS_REV_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * F.GROSS_REV_NON_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * F.REV_AT_LIS_PRICE
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * F.REV_AT_POL_LIMIT
, G.PASS_EXCEPTIONS
, G.FAIL_EXCEPTIONS
, G.NA_EXCEPTIONS
, G.HI_SEVER_THRE
, G.ME_SEVER_THRE
, G.LO_SEVER_THRE
, G.HI_POL_IMP_RANK
, G.ME_POL_IMP_RANK
, G.LO_POL_IMP_RANK
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * G.GROSS_REV_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * G.GROSS_REV_NON_COMP
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * G.REV_AT_LIS_PRICE
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * G.REV_AT_POL_LIMIT
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * (CASE WHEN ( (NVL(B.GROSS_REV_NON_COMP
, 0) + NVL(C.GROSS_REV_NON_COMP
, 0) + NVL(D.GROSS_REV_NON_COMP
, 0) + NVL(E.GROSS_REV_NON_COMP
, 0) + NVL(F.GROSS_REV_NON_COMP
, 0) + NVL(G.GROSS_REV_NON_COMP
, 0)) <> 0) THEN MEASURE2_NUMBER ELSE 0 END)
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * (CASE WHEN COALESCE(B.FAIL_EXCEPTIONS
, C.FAIL_EXCEPTIONS
, D.FAIL_EXCEPTIONS
, E.FAIL_EXCEPTIONS
, F.FAIL_EXCEPTIONS
, G.FAIL_EXCEPTIONS
, -1) = -1 THEN 0 WHEN (NVL(B.GROSS_REV_NON_COMP
, 0) + NVL(C.GROSS_REV_NON_COMP
, 0) + NVL(D.GROSS_REV_NON_COMP
, 0) + NVL(E.GROSS_REV_NON_COMP
, 0) + NVL(F.GROSS_REV_NON_COMP
, 0) + NVL(G.GROSS_REV_NON_COMP
, 0)) <> 0 THEN 0 ELSE MEASURE2_NUMBER END)
, MEASURE_UOM
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * A.MEASURE5_NUMBER/QPR_SR_UTIL.ODS_UOM_CONV(A.PRD_LEVEL_VALUE
, A.MEASURE_UOM
, QPR_MAINTAIN_AW.GET_BASE_UOM
, A.INSTANCE_ID)
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * A.MEASURE14_NUMBER
, QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) * A.MEASURE15_NUMBER
, MEASURE17_NUMBER
, DECODE(NVL(MEASURE1_CHAR
, '*')
, 'MODEL'
, QPR_SR_UTIL.ODS_UOM_CONV(A.PRD_LEVEL_VALUE
, A.MEASURE_UOM
, QPR_MAINTAIN_AW.GET_BASE_UOM
, A.INSTANCE_ID) * A.MEASURE1_NUMBER
, DECODE(NVL(MEASURE1_CHAR
, '*')
, 'KIT'
, QPR_SR_UTIL.ODS_UOM_CONV(A.PRD_LEVEL_VALUE
, A.MEASURE_UOM
, QPR_MAINTAIN_AW.GET_BASE_UOM
, A.INSTANCE_ID) * A.MEASURE1_NUMBER
, 0)
, 0)
FROM QPR_MEASURE_DATA A
, QPR_POLICY_MEAS_V B
, QPR_POLICY_MEAS_V C
, QPR_POLICY_MEAS_V D
, QPR_POLICY_MEAS_V E
, QPR_POLICY_MEAS_V F
, QPR_POLICY_MEAS_V G
WHERE A.MEASURE_TYPE_CODE = 'SALESDATA'
AND B.POLICY_TYPE(+) = 'QPR_CEILING_POLICY_MEASURES'
AND C.POLICY_TYPE(+) = 'QPR_FIELD_USER_POLICY_MEASURES'
AND D.POLICY_TYPE(+) = 'QPR_TARGET_POLICY_MEASURES'
AND E.POLICY_TYPE(+) = 'QPR_REGIONAL_POLICY_MEASURES'
AND F.POLICY_TYPE(+) = 'QPR_CORPORATE_POLICY_MEASURES'
AND G.POLICY_TYPE(+) = 'QPR_GSA_POLICY_MEASURES'
AND A.TIME_LEVEL_VALUE BETWEEN NVL(QPR_MAINTAIN_AW.GET_START_DATE
, A.TIME_LEVEL_VALUE)
AND NVL(QPR_MAINTAIN_AW.GET_END_DATE
, A.TIME_LEVEL_VALUE)
AND A.INSTANCE_ID=QPR_MAINTAIN_AW.GET_INSTANCE
AND A.INSTANCE_ID = B.INSTANCE_ID(+)
AND A.ORD_LEVEL_VALUE = B.ORD_LEVEL_VALUE(+)
AND A.INSTANCE_ID = C.INSTANCE_ID(+)
AND A.ORD_LEVEL_VALUE = C.ORD_LEVEL_VALUE(+)
AND A.INSTANCE_ID = D.INSTANCE_ID(+)
AND A.ORD_LEVEL_VALUE = D.ORD_LEVEL_VALUE(+)
AND A.INSTANCE_ID = E.INSTANCE_ID(+)
AND A.ORD_LEVEL_VALUE = E.ORD_LEVEL_VALUE(+)
AND A.INSTANCE_ID = F.INSTANCE_ID(+)
AND A.ORD_LEVEL_VALUE = F.ORD_LEVEL_VALUE(+)
AND A.INSTANCE_ID = G.INSTANCE_ID(+)
AND A.ORD_LEVEL_VALUE = G.ORD_LEVEL_VALUE(+)
AND QPR_SR_UTIL.ODS_UOM_CONV(A.PRD_LEVEL_VALUE
, A.MEASURE_UOM
, QPR_MAINTAIN_AW.GET_BASE_UOM
, A.INSTANCE_ID) > 0
AND QPR_SR_UTIL.ODS_CURR_CONVERSION(NULL
, QPR_MAINTAIN_AW.GET_CURRENCY_CODE
, NULL
, A.TIME_LEVEL_VALUE
, A.INSTANCE_ID) > 0
AND (QPR_MAINTAIN_AW.GET_ORD_LINE IS NULL OR A.ORD_LEVEL_VALUE IN (SELECT QPM.ATTRIBUTE_1
FROM QPR_PLAN_MEASURES QPM
WHERE QPM.PRICE_PLAN_ID = QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID
AND QPM.PRICE_PLAN_MEAS_GRP_ID = 1
AND QPM.RUN_NUMBER = QPR_MAINTAIN_AW.GET_RUN_NUMBER))
AND (QPR_MAINTAIN_AW.GET_ITEM IS NULL OR A.PRD_LEVEL_VALUE IN ( SELECT QPM2.ATTRIBUTE_1
FROM QPR_PLAN_MEASURES QPM2
WHERE QPM2.PRICE_PLAN_ID = QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID
AND QPM2.PRICE_PLAN_MEAS_GRP_ID = 2
AND QPM2.RUN_NUMBER=QPR_MAINTAIN_AW.GET_RUN_NUMBER))
AND (QPR_MAINTAIN_AW.GET_TP_SITE IS NULL OR A.GEO_LEVEL_VALUE IN (SELECT QPM3.ATTRIBUTE_1
FROM QPR_PLAN_MEASURES QPM3
WHERE QPM3.PRICE_PLAN_ID = QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID
AND QPM3.PRICE_PLAN_MEAS_GRP_ID = 3
AND QPM3.RUN_NUMBER=QPR_MAINTAIN_AW.GET_RUN_NUMBER))
AND (QPR_MAINTAIN_AW.GET_CUS IS NULL OR A.CUS_LEVEL_VALUE IN (SELECT QPM4.ATTRIBUTE_1
FROM QPR_PLAN_MEASURES QPM4
WHERE QPM4.PRICE_PLAN_ID = QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID
AND QPM4.PRICE_PLAN_MEAS_GRP_ID = 4
AND QPM4.RUN_NUMBER = QPR_MAINTAIN_AW.GET_RUN_NUMBER))
AND (QPR_MAINTAIN_AW.GET_OU IS NULL OR A.ORG_LEVEL_VALUE IN (SELECT QPM5.ATTRIBUTE_1
FROM QPR_PLAN_MEASURES QPM5
WHERE QPM5.PRICE_PLAN_ID = QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID
AND QPM5.PRICE_PLAN_MEAS_GRP_ID = 5
AND QPM5.RUN_NUMBER = QPR_MAINTAIN_AW.GET_RUN_NUMBER))
AND (QPR_MAINTAIN_AW.GET_SR IS NULL OR A.REP_LEVEL_VALUE IN (SELECT QPM6.ATTRIBUTE_1
FROM QPR_PLAN_MEASURES QPM6
WHERE QPM6.PRICE_PLAN_ID = QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID
AND QPM6.PRICE_PLAN_MEAS_GRP_ID = 6
AND QPM6.RUN_NUMBER = QPR_MAINTAIN_AW.GET_RUN_NUMBER))
AND (QPR_MAINTAIN_AW.GET_CHN IS NULL OR A.CHN_LEVEL_VALUE IN (SELECT QPM7.ATTRIBUTE_1
FROM QPR_PLAN_MEASURES QPM7
WHERE QPM7.PRICE_PLAN_ID=QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID
AND QPM7.PRICE_PLAN_MEAS_GRP_ID=7
AND QPM7.RUN_NUMBER=QPR_MAINTAIN_AW.GET_RUN_NUMBER))
AND (QPR_MAINTAIN_AW.GET_PSG IS NULL OR A.PSG_LEVEL_VALUE IN (SELECT QPM8.ATTRIBUTE_1
FROM QPR_PLAN_MEASURES QPM8
WHERE QPM8.PRICE_PLAN_ID=QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID
AND QPM8.PRICE_PLAN_MEAS_GRP_ID=8
AND QPM8.RUN_NUMBER=QPR_MAINTAIN_AW.GET_RUN_NUMBER))