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_ALL_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, a.measure1_number, a.measure2_number, (nvl(a.measure20_number,0) + a.measure2_number), a.measure3_number, a.measure4_number, a.measure5_number, 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, b.gross_rev_comp, b.gross_rev_non_comp, b.rev_at_lis_price, 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, c.gross_rev_comp, c.gross_rev_non_comp, c.rev_at_lis_price, 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, d.gross_rev_comp, d.gross_rev_non_comp, d.rev_at_lis_price, 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, e.gross_rev_comp, e.gross_rev_non_comp, e.rev_at_lis_price, 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, f.gross_rev_comp, f.gross_rev_non_comp, f.rev_at_lis_price, 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, b.me_pol_imp_rank, g.lo_pol_imp_rank, g.gross_rev_comp, g.gross_rev_non_comp, g.rev_at_lis_price, g.rev_at_pol_limit, (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), (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, a.measure5_number, a.measure14_number, 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.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(+)

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
, A.MEASURE1_NUMBER
, A.MEASURE2_NUMBER
, (NVL(A.MEASURE20_NUMBER
, 0) + A.MEASURE2_NUMBER)
, A.MEASURE3_NUMBER
, A.MEASURE4_NUMBER
, A.MEASURE5_NUMBER
, 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
, B.GROSS_REV_COMP
, B.GROSS_REV_NON_COMP
, B.REV_AT_LIS_PRICE
, 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
, C.GROSS_REV_COMP
, C.GROSS_REV_NON_COMP
, C.REV_AT_LIS_PRICE
, 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
, D.GROSS_REV_COMP
, D.GROSS_REV_NON_COMP
, D.REV_AT_LIS_PRICE
, 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
, E.GROSS_REV_COMP
, E.GROSS_REV_NON_COMP
, E.REV_AT_LIS_PRICE
, 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
, F.GROSS_REV_COMP
, F.GROSS_REV_NON_COMP
, F.REV_AT_LIS_PRICE
, 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
, B.ME_POL_IMP_RANK
, G.LO_POL_IMP_RANK
, G.GROSS_REV_COMP
, G.GROSS_REV_NON_COMP
, G.REV_AT_LIS_PRICE
, G.REV_AT_POL_LIMIT
, (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)
, (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
, A.MEASURE5_NUMBER
, A.MEASURE14_NUMBER
, 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.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(+)