APPS.QPR_POLICY_EVAL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 24

    select policy_line_id, policy_id,
    policy_type_code, policy_measure_type_code,
    limit_value_type_code, ref_limit_value,
    effective_date_from, effective_date_to
    from qpr_policy_lines
    where policy_id = p_policy_id
    and policy_measure_type_code = nvl(i_policy_meas_type,
                                  policy_measure_type_code)
    and policy_type_code = nvl(i_policy_type, policy_type_code)
    and i_time_level_value between
        nvl(effective_date_from, i_time_level_value)
    and nvl(effective_date_to, i_time_level_value)
    and (vlb_level_value is null or
    vlb_level_value = i_vlb_level_value)
    order by policy_measure_type_code, policy_type_code, vlb_level_value;

Line: 45

      select DEFAULT_POLICY_ID into l_policy_id
      from qpr_pr_segments_b
      where PR_SEGMENT_ID = i_psg_id;

Line: 102

  select p.pr_segment_id, p.policy_importance_code
  into o_pr_segment_id, o_pol_importance_code
  from
      (select default_policy_id, pr_segment_id, policy_importance_code
       from qpr_pr_segments_b
       where (pr_segment_id in (
              select a.parent_id
              from qpr_scopes a,
                (select s.parent_id, s.dim_code
                from qpr_dimension_values dv,qpr_scopes s,
                qpr_hierarchies h, qpr_hier_levels l,
                qpr_pr_segments_b psg
                where  s.parent_entity_type = 'PRICINGSEGMENT'
                and s.parent_id = psg.pr_segment_id
                and psg.instance_id = i_instance_id
                and s.DIM_CODE = dv.DIM_CODE
                and s.HIERARCHY_ID = h.HIERARCHY_ID
                and s.LEVEL_ID = L.HIERARCHY_LEVEL_ID
                and h.HIERARCHY_PPA_CODE = dv.HIERARCHY_CODE
                and s.SCOPE_VALUE = decode(l.LEVEL_SEQ_NUM,
                                            1, dv.LEVEL1_VALUE,
                                            2, dv.LEVEL2_VALUE,
                                            3, dv.LEVEL3_VALUE,
                                            4, dv.LEVEL4_VALUE,
                                            5, dv.LEVEL5_VALUE,
                                            6, dv.LEVEL6_VALUE,
                                            7, dv.LEVEL7_VALUE,
                                            8, dv.LEVEL8_VALUE)
                and dv.LEVEL1_VALUE = decode(s.DIM_CODE,
                                        'PRD',nvl(i_prd_level_value, '*') ,
                                        'CUS', nvl(i_cus_level_value, '*'),
                                        'ORD', nvl(i_ord_level_value, '*'),
                                        'GEO', nvl(i_geo_level_value,'*'),
                                        'ORG',nvl(i_org_level_value, '*') ,
                                        'REP',nvl(i_rep_level_value,'*') ,
                                        'CHN',nvl(i_chn_level_value, '*') )
                and dv.INSTANCE_ID = i_instance_id) b
            where a.parent_id = b.parent_id(+)
            and a.dim_code = b.dim_code(+)
            and a.parent_entity_type = 'PRICINGSEGMENT'
            group by a.parent_id
            having count(distinct a.dim_code) = count(distinct b.dim_code)
            )
       or pr_segment_id not in (select distinct parent_id from qpr_scopes
          where parent_entity_type = 'PRICINGSEGMENT'))
       and instance_id = i_instance_id
       order by policy_precedence
    ) p
  where rownum < 2;

Line: 173

  select language,source_lang, name, description
  from qpr_policies_tl
  where policy_id = p_policy_id;

Line: 178

  select policy_type_code,policy_measure_type_code,
  limit_value_type_code,ref_limit_value,
  effective_date_from,effective_date_to,
  vlb_level_value,vlb_level_value_desc
  from qpr_policy_lines
  where policy_id = p_policy_id;

Line: 187

  select active_flag into l_active_flag
  from qpr_policies_b
  where policy_id = p_policy_id
  and rownum < 2;

Line: 192

  insert into qpr_policies_b(policy_id,
                            active_flag,
                            creation_date,
                            created_by,
                            last_update_date,
                            last_updated_by,
                            last_update_login)
  values(qpr_policies_s.nextval, 'N',
        sysdate,
        fnd_global.user_id,
        sysdate,
        fnd_global.user_id,
        fnd_global.login_id)
  returning POLICY_ID into l_new_pol_id;

Line: 216

    insert into qpr_policies_tl(policy_id,
                                language,
                                source_lang,
                                name,
                                description,
                                creation_date,
                                created_by,
                                last_update_date,
                                last_updated_by,
                                last_update_login)
     values(l_new_pol_id,
            rec_pol.language,
            rec_pol.source_lang,
            l_pol_name,
            rec_pol.description,
            sysdate,
            fnd_global.user_id,
            sysdate,
            fnd_global.user_id,
            fnd_global.login_id);

Line: 239

    insert into qpr_policy_lines(policy_line_id,
                                 policy_id,
                                 policy_type_code,
                                 policy_measure_type_code,
                                 limit_value_type_code,
                                 ref_limit_value,
                                 effective_date_from,
                                 effective_date_to,
                                 vlb_level_value,
                                 vlb_level_value_desc,
                                creation_date,
                                created_by,
                                last_update_date,
                                last_updated_by,
                                last_update_login)
    values(qpr_policy_lines_s.nextval,
           l_new_pol_id,
           rec_line.policy_type_code,
           rec_line.policy_measure_type_code,
           rec_line.limit_value_type_code,
           rec_line.ref_limit_value,
           rec_line.effective_date_from,
           rec_line.effective_date_to,
           rec_line.vlb_level_value,
           rec_line.vlb_level_value_desc,
            sysdate,
            fnd_global.user_id,
            sysdate,
            fnd_global.user_id,
            fnd_global.login_id);

Line: 303

SELECT
instance_id,
ord_level_value, prd_level_value, geo_level_value, cus_level_value,
org_level_value, rep_level_value, chn_level_value, vlb_level_value,
dsb_level_value, time_level_value,
(measure1_number*measure3_number - measure2_number),
measure13_number, measure3_number, measure1_number, measure2_number
FROM qpr_measure_data
WHERE instance_id = p_instance_id
and measure_type_code = 'SALESDATA'
and time_level_value between date_from and date_to;

Line: 325

procedure insert_pol_measures is
begin
  log_debug('Policy eval count'|| c_policy_data_rec.ord_sr_level_value_pk.count);

Line: 332

        delete qpr_measure_data
        where instance_id=p_instance_id
        and measure_type_code = decode(c_policy_data_rec.policy_type_code(I),
				'CEILING', 'QPR_CEILING_POLICY_MEASURES',
				'CORPORATE', 'QPR_CORPORATE_POLICY_MEASURES',
				'FIELD', 'QPR_FIELD_USER_POLICY_MEASURES',
				'GSA', 'QPR_GSA_POLICY_MEASURES',
				'REGIONAL', 'QPR_REGIONAL_POLICY_MEASURES',
				'TARGET', 'QPR_TARGET_POLICY_MEASURES')
        and ord_level_value=c_policy_data_rec.ord_sr_level_value_pk(I);

Line: 342

        log_debug('Deleted '|| sql%rowcount ||' records');

Line: 349

      log_debug('Policy eval inserting');

Line: 351

        insert into QPR_MEASURE_DATA(
        MEASURE_VALUE_ID,
        MEASURE_TYPE_CODE,
        INSTANCE_ID,
        ORD_LEVEL_VALUE,
        PRD_LEVEL_VALUE,
        GEO_LEVEL_VALUE,
        CUS_LEVEL_VALUE,
        ORG_LEVEL_VALUE,
        REP_LEVEL_VALUE,
        CHN_LEVEL_VALUE,
        VLB_LEVEL_VALUE,
        DSB_LEVEL_VALUE,
        TIME_LEVEL_VALUE,
        MEASURE1_NUMBER ,
        MEASURE2_NUMBER ,
        MEASURE3_NUMBER ,
        MEASURE4_NUMBER ,
        MEASURE5_NUMBER ,
        MEASURE6_NUMBER ,
        MEASURE7_NUMBER ,
        MEASURE8_NUMBER ,
        MEASURE9_NUMBER ,
        MEASURE10_NUMBER ,
        MEASURE11_NUMBER ,
        MEASURE12_NUMBER ,
        MEASURE13_NUMBER ,
        CREATION_DATE ,
        CREATED_BY ,
        LAST_UPDATE_DATE ,
        LAST_UPDATED_BY ,
        LAST_UPDATE_LOGIN ,
        REQUEST_ID) values
        (QPR_MEASURE_DATA_S.nextval,
        decode(c_policy_data_rec.policy_type_code(I),
		'CEILING', 'QPR_CEILING_POLICY_MEASURES',
		'CORPORATE', 'QPR_CORPORATE_POLICY_MEASURES',
		'FIELD', 'QPR_FIELD_USER_POLICY_MEASURES',
		'GSA', 'QPR_GSA_POLICY_MEASURES',
		'REGIONAL', 'QPR_REGIONAL_POLICY_MEASURES',
		'TARGET', 'QPR_TARGET_POLICY_MEASURES',
		null),
        c_policy_data_rec.instance(I),
        c_policy_data_rec.ord_sr_level_value_pk(I),
        c_policy_data_rec.prd_sr_level_value_pk(I),
        c_policy_data_rec.geo_sr_level_value_pk(I),
        c_policy_data_rec.cus_sr_level_value_pk(I),
        c_policy_data_rec.org_sr_level_value_pk(I),
        c_policy_data_rec.rep_sr_level_value_pk(I),
        c_policy_data_rec.chn_sr_level_value_pk(I),
        c_policy_data_rec.vlb_sr_level_value_pk(I),
        c_policy_data_rec.dsb_sr_level_value_pk(I),
        c_policy_data_rec.tim_sr_level_value_pk(I),
        c_policy_data_rec.rev_at_pol_limit(I),
        c_policy_data_rec.pass_exceptions(I),
        c_policy_data_rec.fail_exceptions(I),
        c_policy_data_rec.na_exceptions(I),
        c_policy_data_rec.gross_rev_comp(I),
        c_policy_data_rec.gross_rev_non_comp(I),
        c_policy_data_rec.hi_sever_thre(I),
        c_policy_data_rec.me_sever_thre(I),
        c_policy_data_rec.lo_sever_thre(I),
        c_policy_data_rec.hi_pol_imp_rank(I),
        c_policy_data_rec.me_pol_imp_rank(I),
        c_policy_data_rec.lo_pol_imp_rank(I),
        c_policy_data_rec.rev_at_lis_price(I),
        sysdate,
        FND_GLOBAL.USER_ID,
        sysdate,
        FND_GLOBAL.USER_ID,
        FND_GLOBAL.LOGIN_ID,
        FND_GLOBAL.CONC_REQUEST_ID);

Line: 423

      log_debug('Inserted '|| sql%rowcount ||' records');

Line: 432

end; --procedure insert_pol_measures

Line: 436

c_meas_data_rec.instance.delete;

Line: 437

c_meas_data_rec.prd_sr_level_value_pk.delete;

Line: 438

c_meas_data_rec.geo_sr_level_value_pk.delete;

Line: 439

c_meas_data_rec.cus_sr_level_value_pk.delete;

Line: 440

c_meas_data_rec.ord_sr_level_value_pk.delete;

Line: 441

c_meas_data_rec.org_sr_level_value_pk.delete;

Line: 442

c_meas_data_rec.chn_sr_level_value_pk.delete;

Line: 443

c_meas_data_rec.rep_sr_level_value_pk.delete;

Line: 444

c_meas_data_rec.tim_sr_level_value_pk.delete;

Line: 445

c_meas_data_rec.vlb_sr_level_value_pk.delete;

Line: 446

c_meas_data_rec.dsb_sr_level_value_pk.delete;

Line: 447

c_meas_data_rec.DISC_AMOUNT.delete;

Line: 448

c_meas_data_rec.DISC_PERC.delete;

Line: 449

c_meas_data_rec.LIST_PRICE.delete;

Line: 450

c_meas_data_rec.QUANTITY.delete;

Line: 451

c_meas_data_rec.GROSS_REVENUE.delete;

Line: 456

c_policy_data_rec.instance.delete;

Line: 457

c_policy_data_rec.prd_sr_level_value_pk.delete;

Line: 458

c_policy_data_rec.geo_sr_level_value_pk.delete;

Line: 459

c_policy_data_rec.cus_sr_level_value_pk.delete;

Line: 460

c_policy_data_rec.ord_sr_level_value_pk.delete;

Line: 461

c_policy_data_rec.org_sr_level_value_pk.delete;

Line: 462

c_policy_data_rec.chn_sr_level_value_pk.delete;

Line: 463

c_policy_data_rec.rep_sr_level_value_pk.delete;

Line: 464

c_policy_data_rec.tim_sr_level_value_pk.delete;

Line: 465

c_policy_data_rec.vlb_sr_level_value_pk.delete;

Line: 466

c_policy_data_rec.dsb_sr_level_value_pk.delete;

Line: 467

c_policy_data_rec.rev_at_pol_limit.delete;

Line: 468

c_policy_data_rec.pass_exceptions.delete;

Line: 469

c_policy_data_rec.fail_exceptions.delete;

Line: 470

c_policy_data_rec.na_exceptions.delete;

Line: 471

c_policy_data_rec.gross_rev_comp.delete;

Line: 472

c_policy_data_rec.gross_rev_non_comp.delete;

Line: 473

c_policy_data_rec.hi_sever_thre.delete;

Line: 474

c_policy_data_rec.me_sever_thre.delete;

Line: 475

c_policy_data_rec.lo_sever_thre.delete;

Line: 476

c_policy_data_rec.hi_pol_imp_rank.delete;

Line: 477

c_policy_data_rec.me_pol_imp_rank.delete;

Line: 478

c_policy_data_rec.lo_pol_imp_rank.delete;

Line: 479

c_policy_data_rec.rev_at_lis_price.delete;

Line: 674

    insert_pol_measures;