[Home] [Help]
PACKAGE BODY: APPS.QPR_POLICY_EVAL
Source
1 PACKAGE BODY QPR_POLICY_EVAL AS
2 /* $Header: QPRUPOLB.pls 120.4 2008/01/04 13:29:46 bhuchand noship $ */
3
4 procedure log_debug(text varchar2) is
5 begin
6 fnd_file.put_line( fnd_file.log, text);
7 end;
8
9 procedure get_policy_details(
10 i_instance_id in number,
11 i_psg_id in number,
12 i_policy_id in number,
13 i_time_level_value in date,
14 i_vlb_level_value in varchar2,
15 i_policy_meas_type in varchar2,
16 i_policy_type in varchar2 default null,
17 o_policy_det out nocopy policy_det_rec_type) is
18 l_policy_id number;
19 l_ctr number := 0;
20 l_prev_pol_type qpr_policy_lines.POLICY_TYPE_CODE%type := '';
21 l_prev_pol_meas qpr_policy_lines.POLICY_MEASURE_TYPE_CODE%type := '';
22
23 cursor c_pol(p_policy_id number) is
24 select policy_line_id, policy_id,
25 policy_type_code, policy_measure_type_code,
26 limit_value_type_code, ref_limit_value,
27 effective_date_from, effective_date_to
28 from qpr_policy_lines
29 where policy_id = p_policy_id
30 and policy_measure_type_code = nvl(i_policy_meas_type,
31 policy_measure_type_code)
32 and policy_type_code = nvl(i_policy_type, policy_type_code)
33 and i_time_level_value between
34 nvl(effective_date_from, i_time_level_value)
35 and nvl(effective_date_to, i_time_level_value)
36 and (vlb_level_value is null or
37 vlb_level_value = i_vlb_level_value)
38 order by policy_measure_type_code, policy_type_code, vlb_level_value;
39 begin
40
41 if i_policy_id is null then
42 if i_psg_id = qpr_sr_util.get_null_pk then
43 l_policy_id := fnd_profile.value('QPR_DEFAULT_POLICY');
44 else
45 select DEFAULT_POLICY_ID into l_policy_id
46 from qpr_pr_segments_b
47 where PR_SEGMENT_ID = i_psg_id;
48 end if;
49 else
50 l_policy_id := i_policy_id;
51 end if;
52
53 -- for a given policy_type_code, policy_measure_type_code
54 -- and date there can be 2 policies one with vlb and
55 -- another w/o vlb. Loop thro to fetch appr. values
56 -- cursor ordered by policy_measure_type_code, policy_type and vlb_level_val
57 -- So in a given policy_type_code, measure_type record with vlb will be first
58 -- if one is found- otherwise we take null record.
59
60 for r_pol in c_pol(l_policy_id) loop
61 if (nvl(l_prev_pol_type, '*') <> r_pol.POLICY_TYPE_CODE or
62 nvl(l_prev_pol_meas, '*') <> r_pol.POLICY_MEASURE_TYPE_CODE) then
63 if o_policy_det is null then
64 o_policy_det := policy_det_rec_type();
65 end if;
66 o_policy_det.extend;
67 l_ctr := l_ctr + 1;
68 o_policy_det(l_ctr).POLICY_LINE_ID := r_pol.POLICY_LINE_ID;
69 o_policy_det(l_ctr).POLICY_ID := r_pol.POLICY_ID;
70 o_policy_det(l_ctr).POLICY_TYPE_CODE := r_pol.POLICY_TYPE_CODE;
71 o_policy_det(l_ctr).POLICY_MEASURE_TYPE_CODE :=
72 r_pol.POLICY_MEASURE_TYPE_CODE;
73 o_policy_det(l_ctr).LIMIT_VALUE_TYPE_CODE := r_pol.LIMIT_VALUE_TYPE_CODE;
74 o_policy_det(l_ctr).REF_LIMIT_VALUE := r_pol.REF_LIMIT_VALUE;
75 o_policy_det(l_ctr).EFFECTIVE_DATE_FROM := r_pol.EFFECTIVE_DATE_FROM;
76 o_policy_det(l_ctr).EFFECTIVE_DATE_TO := r_pol.EFFECTIVE_DATE_TO;
77
78 l_prev_pol_type := r_pol.POLICY_TYPE_CODE;
79 l_prev_pol_meas := r_pol.POLICY_MEASURE_TYPE_CODE;
80 end if;
81 end loop;
82 exception
83 when NO_DATA_FOUND then
84 o_policy_det := null;
85 end get_policy_details;
86
87 procedure get_pricing_segment_id(
88 i_instance_id in number,
89 i_ord_level_value in varchar2,
90 i_time_level_value in date,
91 i_prd_level_value in varchar2,
92 i_geo_level_value in varchar2,
93 i_cus_level_value in varchar2,
94 i_org_level_value in varchar2,
95 i_rep_level_value in varchar2,
96 i_chn_level_value in varchar2,
97 i_vlb_level_value in varchar2,
98 o_pr_segment_id out nocopy number,
99 o_pol_importance_code out nocopy varchar2) is
100
101 begin
102 select p.pr_segment_id, p.policy_importance_code
103 into o_pr_segment_id, o_pol_importance_code
104 from
105 (select default_policy_id, pr_segment_id, policy_importance_code
106 from qpr_pr_segments_b
107 where (pr_segment_id in (
108 select a.parent_id
109 from qpr_scopes a,
110 (select s.parent_id, s.dim_code
111 from qpr_dimension_values dv,qpr_scopes s,
112 qpr_hierarchies h, qpr_hier_levels l,
113 qpr_pr_segments_b psg
114 where s.parent_entity_type = 'PRICINGSEGMENT'
115 and s.parent_id = psg.pr_segment_id
116 and psg.instance_id = i_instance_id
117 and s.DIM_CODE = dv.DIM_CODE
118 and s.HIERARCHY_ID = h.HIERARCHY_ID
119 and s.LEVEL_ID = L.HIERARCHY_LEVEL_ID
120 and h.HIERARCHY_PPA_CODE = dv.HIERARCHY_CODE
121 and s.SCOPE_VALUE = decode(l.LEVEL_SEQ_NUM,
122 1, dv.LEVEL1_VALUE,
123 2, dv.LEVEL2_VALUE,
124 3, dv.LEVEL3_VALUE,
125 4, dv.LEVEL4_VALUE,
126 5, dv.LEVEL5_VALUE,
127 6, dv.LEVEL6_VALUE,
128 7, dv.LEVEL7_VALUE,
129 8, dv.LEVEL8_VALUE)
130 and dv.LEVEL1_VALUE = decode(s.DIM_CODE,
131 'PRD',nvl(i_prd_level_value, '*') ,
132 'CUS', nvl(i_cus_level_value, '*'),
133 'ORD', nvl(i_ord_level_value, '*'),
134 'GEO', nvl(i_geo_level_value,'*'),
135 'ORG',nvl(i_org_level_value, '*') ,
136 'REP',nvl(i_rep_level_value,'*') ,
137 'CHN',nvl(i_chn_level_value, '*') )
138 and dv.INSTANCE_ID = i_instance_id) b
139 where a.parent_id = b.parent_id(+)
140 and a.dim_code = b.dim_code(+)
141 and a.parent_entity_type = 'PRICINGSEGMENT'
142 group by a.parent_id
143 having count(distinct a.dim_code) = count(distinct b.dim_code)
144 )
145 or pr_segment_id not in (select distinct parent_id from qpr_scopes
146 where parent_entity_type = 'PRICINGSEGMENT'))
147 and instance_id = i_instance_id
148 order by policy_precedence
149 ) p
150 where rownum < 2;
151
152 exception
153 when NO_DATA_FOUND then
154 o_pr_segment_id := qpr_sr_util.get_null_pk;
155 o_pol_importance_code := null;
156 when OTHERS then
157 o_pr_segment_id := null;
158 o_pol_importance_code := null;
159 end get_pricing_segment_id;
160
161
162 procedure copy_policy(p_policy_id in number,
163 p_new_policy_name in out nocopy varchar2,
164 p_new_pol_id out nocopy number,
165 retcode out nocopy number,
166 errbuf out nocopy varchar2) is
167
168 l_new_pol_id qpr_policies_b.POLICY_ID%type;
169 l_active_flag qpr_policies_b.ACTIVE_FLAG%type;
170 l_pol_name qpr_policies_tl.NAME%type;
171
172 cursor c_pol_tl is
173 select language,source_lang, name, description
174 from qpr_policies_tl
175 where policy_id = p_policy_id;
176
177 cursor c_pol_line is
178 select policy_type_code,policy_measure_type_code,
179 limit_value_type_code,ref_limit_value,
180 effective_date_from,effective_date_to,
181 vlb_level_value,vlb_level_value_desc
182 from qpr_policy_lines
183 where policy_id = p_policy_id;
184
185 begin
186
187 select active_flag into l_active_flag
188 from qpr_policies_b
189 where policy_id = p_policy_id
190 and rownum < 2;
191
192 insert into qpr_policies_b(policy_id,
193 active_flag,
194 creation_date,
195 created_by,
196 last_update_date,
197 last_updated_by,
198 last_update_login)
199 values(qpr_policies_s.nextval, 'N',
200 sysdate,
201 fnd_global.user_id,
202 sysdate,
203 fnd_global.user_id,
204 fnd_global.login_id)
205 returning POLICY_ID into l_new_pol_id;
206
207 for rec_pol in c_pol_tl loop
208 if p_new_policy_name is not null then
209 l_pol_name := p_new_policy_name;
210 else
211 fnd_message.set_name('QPR', 'QPR_COPY_OF');
212 fnd_message.set_token('OBJECT_NAME', rec_pol.name);
213 l_pol_name := fnd_message.get;
214 end if;
215
216 insert into qpr_policies_tl(policy_id,
217 language,
218 source_lang,
219 name,
220 description,
221 creation_date,
222 created_by,
223 last_update_date,
224 last_updated_by,
225 last_update_login)
226 values(l_new_pol_id,
227 rec_pol.language,
228 rec_pol.source_lang,
229 l_pol_name,
230 rec_pol.description,
231 sysdate,
232 fnd_global.user_id,
233 sysdate,
234 fnd_global.user_id,
235 fnd_global.login_id);
236 end loop;
237
238 for rec_line in c_pol_line loop
239 insert into qpr_policy_lines(policy_line_id,
240 policy_id,
241 policy_type_code,
242 policy_measure_type_code,
243 limit_value_type_code,
244 ref_limit_value,
245 effective_date_from,
246 effective_date_to,
247 vlb_level_value,
248 vlb_level_value_desc,
249 creation_date,
250 created_by,
251 last_update_date,
252 last_updated_by,
253 last_update_login)
254 values(qpr_policy_lines_s.nextval,
255 l_new_pol_id,
256 rec_line.policy_type_code,
257 rec_line.policy_measure_type_code,
258 rec_line.limit_value_type_code,
259 rec_line.ref_limit_value,
260 rec_line.effective_date_from,
261 rec_line.effective_date_to,
262 rec_line.vlb_level_value,
263 rec_line.vlb_level_value_desc,
264 sysdate,
265 fnd_global.user_id,
266 sysdate,
267 fnd_global.user_id,
268 fnd_global.login_id);
269 end loop;
270
271 p_new_pol_id := l_new_pol_id;
272 p_new_policy_name := l_pol_name;
273
274 exception
275 when others then
276 retcode := 2;
277 errbuf := sqlerrm;
278 p_new_pol_id := null;
279 end copy_policy;
280
281
282 procedure process(
283 errbuf OUT NOCOPY VARCHAR2,
284 retcode OUT NOCOPY VARCHAR2,
285 p_instance_id number,
286 p_from_date varchar2,
287 p_to_date varchar2) is
288
289 c_meas_data_rec measure_rec_type;
290 c_policy_data_rec policy_rec_type;
291 date_from date := FND_DATE.canonical_to_date(p_from_date);
292 date_to date := FND_DATE.canonical_to_date(p_to_date);
293 l_rows natural :=1000;
294 l_policy_counter number:=1;
295 l_policy_value qpr_measure_data.measure1_number%TYPE;
296 I number;
297 l_pric_at_pol_limit number;
298 l_med_sev_thre number;
299 l_high_sev_thre number;
300 l_sev_thre_perc number;
301
302 cursor c_measures is
303 SELECT
304 instance_id,
305 ord_level_value, prd_level_value, geo_level_value, cus_level_value,
306 org_level_value, rep_level_value, chn_level_value, vlb_level_value,
307 dsb_level_value, time_level_value,
308 (measure1_number*measure3_number - measure2_number),
309 measure13_number, measure3_number, measure1_number, measure2_number
310 FROM qpr_measure_data
311 WHERE instance_id = p_instance_id
312 and measure_type_code = 'SALESDATA'
313 and time_level_value between date_from and date_to;
314
315
316 l_pr_segment_id number;
317 t_pol_det_rec POLICY_DET_REC_TYPE;
318
319 l_policy_id number;
320 l_limit_value_type_code varchar2(30);
321 l_ref_limit_value number;
322 l_importance_rank qpr_pr_segments_b.policy_importance_code%TYPE;
323
324
325 procedure insert_pol_measures is
326 begin
327 log_debug('Policy eval count'|| c_policy_data_rec.ord_sr_level_value_pk.count);
328 if c_policy_data_rec.ord_sr_level_value_pk.count>0 then
329 begin
330 log_debug('Policy eval deleting');
331 forall I in 1..c_policy_data_rec.ord_sr_level_value_pk.count
332 delete qpr_measure_data
333 where instance_id=p_instance_id
334 and measure_type_code = decode(c_policy_data_rec.policy_type_code(I),
335 'CEILING', 'QPR_CEILING_POLICY_MEASURES',
336 'CORPORATE', 'QPR_CORPORATE_POLICY_MEASURES',
337 'FIELD', 'QPR_FIELD_USER_POLICY_MEASURES',
338 'GSA', 'QPR_GSA_POLICY_MEASURES',
339 'REGIONAL', 'QPR_REGIONAL_POLICY_MEASURES',
340 'TARGET', 'QPR_TARGET_POLICY_MEASURES')
341 and ord_level_value=c_policy_data_rec.ord_sr_level_value_pk(I);
342 log_debug('Deleted '|| sql%rowcount ||' records');
343 exception
344 when others then
345 log_debug('RETCODE = ' || RETCODE);
346 null;
347 end;
348 begin
349 log_debug('Policy eval inserting');
350 forall I in 1..c_policy_data_rec.ord_sr_level_value_pk.count
351 insert into QPR_MEASURE_DATA(
352 MEASURE_VALUE_ID,
353 MEASURE_TYPE_CODE,
354 INSTANCE_ID,
355 ORD_LEVEL_VALUE,
356 PRD_LEVEL_VALUE,
357 GEO_LEVEL_VALUE,
358 CUS_LEVEL_VALUE,
359 ORG_LEVEL_VALUE,
360 REP_LEVEL_VALUE,
361 CHN_LEVEL_VALUE,
362 VLB_LEVEL_VALUE,
363 DSB_LEVEL_VALUE,
364 TIME_LEVEL_VALUE,
365 MEASURE1_NUMBER ,
366 MEASURE2_NUMBER ,
367 MEASURE3_NUMBER ,
368 MEASURE4_NUMBER ,
369 MEASURE5_NUMBER ,
370 MEASURE6_NUMBER ,
371 MEASURE7_NUMBER ,
372 MEASURE8_NUMBER ,
373 MEASURE9_NUMBER ,
374 MEASURE10_NUMBER ,
375 MEASURE11_NUMBER ,
376 MEASURE12_NUMBER ,
377 MEASURE13_NUMBER ,
378 CREATION_DATE ,
379 CREATED_BY ,
380 LAST_UPDATE_DATE ,
381 LAST_UPDATED_BY ,
382 LAST_UPDATE_LOGIN ,
383 REQUEST_ID) values
384 (QPR_MEASURE_DATA_S.nextval,
385 decode(c_policy_data_rec.policy_type_code(I),
386 'CEILING', 'QPR_CEILING_POLICY_MEASURES',
387 'CORPORATE', 'QPR_CORPORATE_POLICY_MEASURES',
388 'FIELD', 'QPR_FIELD_USER_POLICY_MEASURES',
389 'GSA', 'QPR_GSA_POLICY_MEASURES',
390 'REGIONAL', 'QPR_REGIONAL_POLICY_MEASURES',
391 'TARGET', 'QPR_TARGET_POLICY_MEASURES',
392 null),
393 c_policy_data_rec.instance(I),
394 c_policy_data_rec.ord_sr_level_value_pk(I),
395 c_policy_data_rec.prd_sr_level_value_pk(I),
396 c_policy_data_rec.geo_sr_level_value_pk(I),
397 c_policy_data_rec.cus_sr_level_value_pk(I),
398 c_policy_data_rec.org_sr_level_value_pk(I),
399 c_policy_data_rec.rep_sr_level_value_pk(I),
400 c_policy_data_rec.chn_sr_level_value_pk(I),
401 c_policy_data_rec.vlb_sr_level_value_pk(I),
402 c_policy_data_rec.dsb_sr_level_value_pk(I),
403 c_policy_data_rec.tim_sr_level_value_pk(I),
404 c_policy_data_rec.rev_at_pol_limit(I),
405 c_policy_data_rec.pass_exceptions(I),
406 c_policy_data_rec.fail_exceptions(I),
407 c_policy_data_rec.na_exceptions(I),
408 c_policy_data_rec.gross_rev_comp(I),
409 c_policy_data_rec.gross_rev_non_comp(I),
410 c_policy_data_rec.hi_sever_thre(I),
411 c_policy_data_rec.me_sever_thre(I),
412 c_policy_data_rec.lo_sever_thre(I),
413 c_policy_data_rec.hi_pol_imp_rank(I),
414 c_policy_data_rec.me_pol_imp_rank(I),
415 c_policy_data_rec.lo_pol_imp_rank(I),
416 c_policy_data_rec.rev_at_lis_price(I),
417 sysdate,
418 FND_GLOBAL.USER_ID,
419 sysdate,
420 FND_GLOBAL.USER_ID,
421 FND_GLOBAL.LOGIN_ID,
422 FND_GLOBAL.CONC_REQUEST_ID);
423 log_debug('Inserted '|| sql%rowcount ||' records');
424 exception
425 when others then
426 errbuf := substr(SQLERRM,1,150);
427 retcode := -1;
428 log_debug(substr(SQLERRM, 1, 1000));
429 end;
430 commit;
431 end if; --c.policy_data.rec.ord_sr_level_value_pk.count>0 then
432 end; --procedure insert_pol_measures
433
434 procedure clean_meas_data is
435 begin
436 c_meas_data_rec.instance.delete;
437 c_meas_data_rec.prd_sr_level_value_pk.delete;
438 c_meas_data_rec.geo_sr_level_value_pk.delete;
439 c_meas_data_rec.cus_sr_level_value_pk.delete;
440 c_meas_data_rec.ord_sr_level_value_pk.delete;
441 c_meas_data_rec.org_sr_level_value_pk.delete;
442 c_meas_data_rec.chn_sr_level_value_pk.delete;
443 c_meas_data_rec.rep_sr_level_value_pk.delete;
444 c_meas_data_rec.tim_sr_level_value_pk.delete;
445 c_meas_data_rec.vlb_sr_level_value_pk.delete;
446 c_meas_data_rec.dsb_sr_level_value_pk.delete;
447 c_meas_data_rec.DISC_AMOUNT.delete;
448 c_meas_data_rec.DISC_PERC.delete;
449 c_meas_data_rec.LIST_PRICE.delete;
450 c_meas_data_rec.QUANTITY.delete;
451 c_meas_data_rec.GROSS_REVENUE.delete;
452 end clean_meas_data;
453
454 procedure clean_policy_data is
455 begin
456 c_policy_data_rec.instance.delete;
457 c_policy_data_rec.prd_sr_level_value_pk.delete;
458 c_policy_data_rec.geo_sr_level_value_pk.delete;
459 c_policy_data_rec.cus_sr_level_value_pk.delete;
460 c_policy_data_rec.ord_sr_level_value_pk.delete;
461 c_policy_data_rec.org_sr_level_value_pk.delete;
462 c_policy_data_rec.chn_sr_level_value_pk.delete;
463 c_policy_data_rec.rep_sr_level_value_pk.delete;
464 c_policy_data_rec.tim_sr_level_value_pk.delete;
465 c_policy_data_rec.vlb_sr_level_value_pk.delete;
466 c_policy_data_rec.dsb_sr_level_value_pk.delete;
467 c_policy_data_rec.rev_at_pol_limit.delete;
468 c_policy_data_rec.pass_exceptions.delete;
469 c_policy_data_rec.fail_exceptions.delete;
470 c_policy_data_rec.na_exceptions.delete;
471 c_policy_data_rec.gross_rev_comp.delete;
472 c_policy_data_rec.gross_rev_non_comp.delete;
473 c_policy_data_rec.hi_sever_thre.delete;
474 c_policy_data_rec.me_sever_thre.delete;
475 c_policy_data_rec.lo_sever_thre.delete;
476 c_policy_data_rec.hi_pol_imp_rank.delete;
477 c_policy_data_rec.me_pol_imp_rank.delete;
478 c_policy_data_rec.lo_pol_imp_rank.delete;
479 c_policy_data_rec.rev_at_lis_price.delete;
480 end clean_policy_data;
481
482 begin
483 log_debug('Start.. ');
484 l_med_sev_thre := to_number(nvl(qpr_sr_util.read_parameter(
485 'QPR_DEVIATION_SEVERE_MED'),0));
486 l_high_sev_thre := to_number(nvl(qpr_sr_util.read_parameter(
487 'QPR_DEVIATION_SEVERE_HIGH'),0));
488 open c_measures;
489 loop
490 clean_meas_data;
491 l_policy_counter:=0;
492 fetch c_measures bulk collect into
493 c_meas_data_rec.instance,
494 c_meas_data_rec.ord_sr_level_value_pk,
495 c_meas_data_rec.prd_sr_level_value_pk,
496 c_meas_data_rec.geo_sr_level_value_pk,
497 c_meas_data_rec.cus_sr_level_value_pk,
498 c_meas_data_rec.org_sr_level_value_pk,
499 c_meas_data_rec.rep_sr_level_value_pk,
500 c_meas_data_rec.chn_sr_level_value_pk,
501 c_meas_data_rec.vlb_sr_level_value_pk,
502 c_meas_data_rec.dsb_sr_level_value_pk,
503 c_meas_data_rec.tim_sr_level_value_pk,
504 c_meas_data_rec.DISC_AMOUNT,
505 c_meas_data_rec.DISC_PERC,
506 c_meas_data_rec.LIST_PRICE,
507 c_meas_data_rec.QUANTITY,
508 c_meas_data_rec.GROSS_REVENUE
509 limit l_rows;
510 exit when c_meas_data_rec.ord_sr_level_value_pk.count = 0;
511
512 -- log_debug('Populated arrays');
513 for I in 1..c_meas_data_rec.ord_sr_level_value_pk.count loop
514 get_pricing_segment_id(
515 p_instance_id,
516 c_meas_data_rec.ord_sr_level_value_pk(i),
517 c_meas_data_rec.tim_sr_level_value_pk(i),
518 c_meas_data_rec.prd_sr_level_value_pk(i),
519 c_meas_data_rec.geo_sr_level_value_pk(i),
520 c_meas_data_rec.cus_sr_level_value_pk(i),
521 c_meas_data_rec.org_sr_level_value_pk(i),
522 c_meas_data_rec.rep_sr_level_value_pk(i),
523 c_meas_data_rec.chn_sr_level_value_pk(i),
524 c_meas_data_rec.vlb_sr_level_value_pk(i),
525 l_pr_segment_id,
526 l_importance_rank);
527
528 get_policy_details(
529 p_instance_id,
530 l_pr_segment_id,
531 null,
532 c_meas_data_rec.tim_sr_level_value_pk(i),
533 c_meas_data_rec.vlb_sr_level_value_pk(i),
534 'ONINVOICE',
535 null,
536 t_pol_det_rec);
537
538 if l_pr_segment_id is not null then
539 for J in 1..t_pol_det_rec.count loop
540 l_policy_counter:=l_policy_counter+1;
541
542 l_policy_id := t_pol_det_rec(J).policy_id;
543 l_ref_limit_value := t_pol_det_rec(J).ref_limit_value;
544 l_limit_value_type_code := t_pol_det_rec(J).limit_value_type_code;
545
546 log_debug('Policy counter:'||l_policy_counter);
547 log_debug('Policy..'|| c_meas_data_rec.ord_sr_level_value_pk(I)||' - '||l_policy_id);
548 log_debug('Limit value Type..'|| l_limit_value_type_code);
549
550 c_policy_data_rec.instance(l_policy_counter) :=
551 c_meas_data_rec.instance(I);
552 c_policy_data_rec.ord_sr_level_value_pk(l_policy_counter) :=
553 c_meas_data_rec.ord_sr_level_value_pk(I);
554 c_policy_data_rec.prd_sr_level_value_pk(l_policy_counter) :=
555 c_meas_data_rec.prd_sr_level_value_pk(I);
556 c_policy_data_rec.geo_sr_level_value_pk(l_policy_counter) :=
557 c_meas_data_rec.geo_sr_level_value_pk(I);
558 c_policy_data_rec.cus_sr_level_value_pk(l_policy_counter) :=
559 c_meas_data_rec.cus_sr_level_value_pk(I);
560 c_policy_data_rec.org_sr_level_value_pk(l_policy_counter) :=
561 c_meas_data_rec.org_sr_level_value_pk(I);
562 c_policy_data_rec.rep_sr_level_value_pk(l_policy_counter) :=
563 c_meas_data_rec.rep_sr_level_value_pk(I);
564 c_policy_data_rec.chn_sr_level_value_pk(l_policy_counter) :=
565 c_meas_data_rec.chn_sr_level_value_pk(I);
566 c_policy_data_rec.vlb_sr_level_value_pk(l_policy_counter) :=
567 c_meas_data_rec.vlb_sr_level_value_pk(I);
568 c_policy_data_rec.dsb_sr_level_value_pk(l_policy_counter) :=
569 c_meas_data_rec.dsb_sr_level_value_pk(I);
570 c_policy_data_rec.tim_sr_level_value_pk(l_policy_counter) :=
571 c_meas_data_rec.tim_sr_level_value_pk(I);
572
573 c_policy_data_rec.policy_type_code(l_policy_counter) := t_pol_det_rec(J).policy_type_code;
574
575 if l_policy_id is null or c_meas_data_rec.list_price(I) = 0 then
576 log_debug('Policy is null ');
577 c_policy_data_rec.pass_exceptions(l_policy_counter):=0;
578 c_policy_data_rec.fail_exceptions(l_policy_counter):=0;
579 c_policy_data_rec.na_exceptions(l_policy_counter):=1;
580 c_policy_data_rec.hi_sever_thre(l_policy_counter):=0;
581 c_policy_data_rec.me_sever_thre(l_policy_counter):=0;
582 c_policy_data_rec.lo_sever_thre(l_policy_counter):=0;
583 c_policy_data_rec.hi_pol_imp_rank(l_policy_counter):=0;
584 c_policy_data_rec.me_pol_imp_rank(l_policy_counter):=0;
585 c_policy_data_rec.lo_pol_imp_rank(l_policy_counter):=0;
586 c_policy_data_rec.gross_rev_comp(l_policy_counter):=
587 c_meas_data_rec.gross_revenue(I);
588 c_policy_data_rec.gross_rev_non_comp(l_policy_counter):=0;
589 c_policy_data_rec.rev_at_lis_price(l_policy_counter):=0;
590 c_policy_data_rec.rev_at_pol_limit(l_policy_counter):=0;
591 else
592 if l_limit_value_type_code = 'AMOUNT' then
593 log_debug('Policy limit type is amount ');
594 l_policy_value := nvl(c_meas_data_rec.disc_amount(I),0);
595 l_pric_at_pol_limit := c_meas_data_rec.list_price(I)-
596 nvl(l_ref_limit_value, 0);
597 else
598 log_debug('Policy limit type is percent ');
599 l_policy_value := nvl(c_meas_data_rec.disc_perc(I),0);
600 l_pric_at_pol_limit := c_meas_data_rec.list_price(I)-
601 (c_meas_data_rec.list_price(I) *
602 nvl(l_ref_limit_value, 0)/100);
603 end if;
604 c_policy_data_rec.rev_at_lis_price(l_policy_counter):=
605 c_meas_data_rec.quantity(I)*
606 c_meas_data_rec.list_price(I);
607 c_policy_data_rec.rev_at_pol_limit(l_policy_counter) :=
608 c_meas_data_rec.quantity(I)* l_pric_at_pol_limit;
609 if l_policy_value <= l_ref_limit_value then
610 log_debug('Policy pass ');
611 c_policy_data_rec.pass_exceptions(l_policy_counter):=1;
612 c_policy_data_rec.fail_exceptions(l_policy_counter):=0;
613 c_policy_data_rec.na_exceptions(l_policy_counter):=0;
614 c_policy_data_rec.hi_sever_thre(l_policy_counter):=0;
615 c_policy_data_rec.me_sever_thre(l_policy_counter):=0;
616 c_policy_data_rec.lo_sever_thre(l_policy_counter):=0;
617 c_policy_data_rec.hi_pol_imp_rank(l_policy_counter):=0;
618 c_policy_data_rec.me_pol_imp_rank(l_policy_counter):=0;
619 c_policy_data_rec.lo_pol_imp_rank(l_policy_counter):=0;
620 c_policy_data_rec.gross_rev_comp(l_policy_counter):=
621 c_meas_data_rec.gross_revenue(I);
622 c_policy_data_rec.gross_rev_non_comp(l_policy_counter):=0;
623 else
624 log_debug('Policy failed ');
625 c_policy_data_rec.pass_exceptions(l_policy_counter):=0;
626 c_policy_data_rec.fail_exceptions(l_policy_counter):=1;
627 c_policy_data_rec.na_exceptions(l_policy_counter):=0;
628
629 if l_ref_limit_value > 0 then
630 l_sev_thre_perc := 100 * (l_policy_value - l_ref_limit_value)/
631 l_ref_limit_value ;
632 else
633 -- this case might not come if the exception is handled in UI
634 -- this is for other source uploads only.
635 l_sev_thre_perc := 100;
636 end if;
637
638 if l_sev_thre_perc <= l_med_sev_thre then
639 c_policy_data_rec.hi_sever_thre(l_policy_counter):=0;
640 c_policy_data_rec.me_sever_thre(l_policy_counter):=0;
641 c_policy_data_rec.lo_sever_thre(l_policy_counter):=1;
642 elsif l_sev_thre_perc <= l_high_sev_thre then
643 c_policy_data_rec.hi_sever_thre(l_policy_counter):=0;
644 c_policy_data_rec.me_sever_thre(l_policy_counter):=1;
645 c_policy_data_rec.lo_sever_thre(l_policy_counter):=0;
646 elsif l_sev_thre_perc > l_high_sev_thre then
647 c_policy_data_rec.hi_sever_thre(l_policy_counter):=1;
648 c_policy_data_rec.me_sever_thre(l_policy_counter):=0;
649 c_policy_data_rec.lo_sever_thre(l_policy_counter):=0;
650 end if; --medium,hi
651
652 if l_importance_rank = 'LOW' then
653 c_policy_data_rec.hi_pol_imp_rank(l_policy_counter):=0;
654 c_policy_data_rec.me_pol_imp_rank(l_policy_counter):=0;
655 c_policy_data_rec.lo_pol_imp_rank(l_policy_counter):=1;
656 elsif l_importance_rank = 'MEDIUM' then
657 c_policy_data_rec.hi_pol_imp_rank(l_policy_counter):=0;
658 c_policy_data_rec.me_pol_imp_rank(l_policy_counter):=1;
659 c_policy_data_rec.lo_pol_imp_rank(l_policy_counter):=0;
660 elsif l_importance_rank = 'HIGH' then
661 c_policy_data_rec.hi_pol_imp_rank(l_policy_counter):=1;
662 c_policy_data_rec.me_pol_imp_rank(l_policy_counter):=0;
663 c_policy_data_rec.lo_pol_imp_rank(l_policy_counter):=0;
664 end if;
665 c_policy_data_rec.gross_rev_non_comp(l_policy_counter):=
666 c_meas_data_rec.gross_revenue(I);
667 c_policy_data_rec.gross_rev_comp(l_policy_counter):=0;
668 end if; --l_policy_value <= l_low_limit_value
669 end if; --policy_id is null
670 end loop; -- loop through t_pol_det_rec
671 end if; -- l_pr_segment_id is not null
672 end loop; --c_measure_data_rec
673 if c_policy_data_rec.ord_sr_level_value_pk.count <> 0 then
674 insert_pol_measures;
675 clean_policy_data;
676 end if; -- check for c_policy_data_rec.ord_sr_level_value_pk.count <> 0
677 commit;
678 end loop; --c_measures
679
680 close c_measures;
681 exception
682 WHEN NO_DATA_FOUND THEN
683 retcode := 2;
684 errbuf := FND_MESSAGE.GET;
685 log_debug('Unexpected error '||substr(sqlerrm,1200));
686 end;
687
688 end; --package