[Home] [Help]
6: where copy_entity_txn_id = p_copy_entity_txn_id
7: and table_alias ='GRRATE';
8: exception
9: when others then
10: hr_utility.set_location('issues in deleteing gr rates',10);
11: raise;
12: end delete_grrate;
13: procedure delete_gsrate(p_copy_entity_txn_id in number) is
14: begin
16: where copy_entity_txn_id = p_copy_entity_txn_id
17: and table_alias ='GSRATE';
18: exception
19: when others then
20: hr_utility.set_location('issues in deleteing gs rates',10);
21: raise;
22: end delete_gsrate;
23: procedure get_point_value(p_point_cer_id in number,
24: p_copy_entity_txn_id in number,
28: p_point_value out nocopy number,
29: p_point_old_value out nocopy number)
30: IS
31: begin
32: hr_utility.set_location('point cer is '||p_point_cer_id,1);
33: hr_utility.set_location('crset is '||p_crset_id,1);
34: hr_utility.set_location('stdt is '||to_char(p_effective_start_date,'dd-mm-RRRR'),2);
35: hr_utility.set_location('endt is '||to_char(p_effective_end_date,'dd-mm-RRRR'),3);
36: select information293,information287
29: p_point_old_value out nocopy number)
30: IS
31: begin
32: hr_utility.set_location('point cer is '||p_point_cer_id,1);
33: hr_utility.set_location('crset is '||p_crset_id,1);
34: hr_utility.set_location('stdt is '||to_char(p_effective_start_date,'dd-mm-RRRR'),2);
35: hr_utility.set_location('endt is '||to_char(p_effective_end_date,'dd-mm-RRRR'),3);
36: select information293,information287
37: into p_point_value,p_point_old_value
30: IS
31: begin
32: hr_utility.set_location('point cer is '||p_point_cer_id,1);
33: hr_utility.set_location('crset is '||p_crset_id,1);
34: hr_utility.set_location('stdt is '||to_char(p_effective_start_date,'dd-mm-RRRR'),2);
35: hr_utility.set_location('endt is '||to_char(p_effective_end_date,'dd-mm-RRRR'),3);
36: select information293,information287
37: into p_point_value,p_point_old_value
38: from ben_copy_entity_results
31: begin
32: hr_utility.set_location('point cer is '||p_point_cer_id,1);
33: hr_utility.set_location('crset is '||p_crset_id,1);
34: hr_utility.set_location('stdt is '||to_char(p_effective_start_date,'dd-mm-RRRR'),2);
35: hr_utility.set_location('endt is '||to_char(p_effective_end_date,'dd-mm-RRRR'),3);
36: select information293,information287
37: into p_point_value,p_point_old_value
38: from ben_copy_entity_results
39: where table_alias = 'CRRATE'
41: and information169 = p_point_cer_id
42: and information160 = p_crset_id
43: and p_effective_start_date between information2 and information3
44: and p_effective_end_date between information2 and information3;
45: hr_utility.set_location('rate is '||p_point_value,10);
46: exception
47: when no_data_found then
48: hr_utility.set_location('null value returned ',10);
49: when others then
44: and p_effective_end_date between information2 and information3;
45: hr_utility.set_location('rate is '||p_point_value,10);
46: exception
47: when no_data_found then
48: hr_utility.set_location('null value returned ',10);
49: when others then
50: hr_utility.set_location('issues in getting rate for'||p_point_cer_id,50);
51: hr_utility.set_location('crset is '||p_crset_id,51);
52: raise;
46: exception
47: when no_data_found then
48: hr_utility.set_location('null value returned ',10);
49: when others then
50: hr_utility.set_location('issues in getting rate for'||p_point_cer_id,50);
51: hr_utility.set_location('crset is '||p_crset_id,51);
52: raise;
53: end get_point_value;
54: procedure get_point_value(p_point_cer_id in number,
47: when no_data_found then
48: hr_utility.set_location('null value returned ',10);
49: when others then
50: hr_utility.set_location('issues in getting rate for'||p_point_cer_id,50);
51: hr_utility.set_location('crset is '||p_crset_id,51);
52: raise;
53: end get_point_value;
54: procedure get_point_value(p_point_cer_id in number,
55: p_copy_entity_txn_id in number,
57: p_effective_end_date in date,
58: p_point_value out nocopy number,
59: p_point_old_value out nocopy number)is
60: begin
61: hr_utility.set_location('point cer is '||p_point_cer_id,1);
62: hr_utility.set_location('stdt is '||to_char(p_effective_start_date,'dd-mm-RRRR'),2);
63: hr_utility.set_location('endt is '||to_char(p_effective_end_date,'dd-mm-RRRR'),3);
64: select information297,information287
65: into p_point_value,p_point_old_value
58: p_point_value out nocopy number,
59: p_point_old_value out nocopy number)is
60: begin
61: hr_utility.set_location('point cer is '||p_point_cer_id,1);
62: hr_utility.set_location('stdt is '||to_char(p_effective_start_date,'dd-mm-RRRR'),2);
63: hr_utility.set_location('endt is '||to_char(p_effective_end_date,'dd-mm-RRRR'),3);
64: select information297,information287
65: into p_point_value,p_point_old_value
66: from ben_copy_entity_results
59: p_point_old_value out nocopy number)is
60: begin
61: hr_utility.set_location('point cer is '||p_point_cer_id,1);
62: hr_utility.set_location('stdt is '||to_char(p_effective_start_date,'dd-mm-RRRR'),2);
63: hr_utility.set_location('endt is '||to_char(p_effective_end_date,'dd-mm-RRRR'),3);
64: select information297,information287
65: into p_point_value,p_point_old_value
66: from ben_copy_entity_results
67: where table_alias = 'HRRATE'
68: and copy_entity_txn_id = p_copy_entity_txn_id
69: and information278 = p_point_cer_id
70: and p_effective_start_date between information2 and information3
71: and p_effective_end_date between information2 and information3;
72: hr_utility.set_location('rate is '||p_point_value,10);
73: exception
74: when no_data_found then
75: hr_utility.set_location('null value returned ',10);
76: p_point_value := null;
71: and p_effective_end_date between information2 and information3;
72: hr_utility.set_location('rate is '||p_point_value,10);
73: exception
74: when no_data_found then
75: hr_utility.set_location('null value returned ',10);
76: p_point_value := null;
77: when others then
78: hr_utility.set_location('issues in getting rate'||p_point_cer_id,50);
79: raise;
74: when no_data_found then
75: hr_utility.set_location('null value returned ',10);
76: p_point_value := null;
77: when others then
78: hr_utility.set_location('issues in getting rate'||p_point_cer_id,50);
79: raise;
80: end get_point_value;
81: procedure build_grrate(p_copy_entity_txn_id in number,
82: p_gr_rate_matx in t_gs_rate_matx,
101: pqh_gsp_hr_to_stage.get_table_route_details(p_table_alias => 'GRRATE',
102: p_table_route_id => l_grr_tr_id,
103: p_table_name => l_grr_tr_name);
104: for i in 1..p_gr_rate_matx.count loop
105: hr_utility.set_location('crset id is '||p_gr_rate_matx(i).crset_id,1);
106: if p_gr_rate_matx(i).point1_cer_id is not null then
107: hr_utility.set_location('going for value ',1);
108: get_point_value(p_point_cer_id => p_gr_rate_matx(i).point1_cer_id,
109: p_copy_entity_txn_id => p_copy_entity_txn_id,
103: p_table_name => l_grr_tr_name);
104: for i in 1..p_gr_rate_matx.count loop
105: hr_utility.set_location('crset id is '||p_gr_rate_matx(i).crset_id,1);
106: if p_gr_rate_matx(i).point1_cer_id is not null then
107: hr_utility.set_location('going for value ',1);
108: get_point_value(p_point_cer_id => p_gr_rate_matx(i).point1_cer_id,
109: p_copy_entity_txn_id => p_copy_entity_txn_id,
110: p_crset_id => p_gr_rate_matx(i).crset_id,
111: p_effective_start_date => p_gr_rate_matx(i).esd,
116: l_point1_value := null;
117: l_point1_old_value := null;
118: end if;
119: if p_gr_rate_matx(i).point2_cer_id is not null then
120: hr_utility.set_location('going for value ',1);
121: get_point_value(p_point_cer_id => p_gr_rate_matx(i).point2_cer_id,
122: p_copy_entity_txn_id => p_copy_entity_txn_id,
123: p_crset_id => p_gr_rate_matx(i).crset_id,
124: p_effective_start_date => p_gr_rate_matx(i).esd,
129: l_point2_value := null;
130: l_point2_old_value := null;
131: end if;
132: if p_gr_rate_matx(i).point3_cer_id is not null then
133: hr_utility.set_location('going for value ',1);
134: get_point_value(p_point_cer_id => p_gr_rate_matx(i).point3_cer_id,
135: p_copy_entity_txn_id => p_copy_entity_txn_id,
136: p_crset_id => p_gr_rate_matx(i).crset_id,
137: p_effective_start_date => p_gr_rate_matx(i).esd,
142: l_point3_value := null;
143: l_point3_old_value := null;
144: end if;
145: if p_gr_rate_matx(i).point4_cer_id is not null then
146: hr_utility.set_location('going for value ',1);
147: get_point_value(p_point_cer_id => p_gr_rate_matx(i).point4_cer_id,
148: p_copy_entity_txn_id => p_copy_entity_txn_id,
149: p_crset_id => p_gr_rate_matx(i).crset_id,
150: p_effective_start_date => p_gr_rate_matx(i).esd,
155: l_point4_value := null;
156: l_point4_old_value := null;
157: end if;
158: if p_gr_rate_matx(i).point5_cer_id is not null then
159: hr_utility.set_location('going for value ',1);
160: get_point_value(p_point_cer_id => p_gr_rate_matx(i).point5_cer_id,
161: p_copy_entity_txn_id => p_copy_entity_txn_id,
162: p_crset_id => p_gr_rate_matx(i).crset_id,
163: p_effective_start_date => p_gr_rate_matx(i).esd,
201: ,p_copy_entity_result_id => l_grr_cer_id
202: ,p_object_version_number => l_grr_cer_ovn);
203: exception
204: when others then
205: hr_utility.set_location('issue in creation grrate cer ',400);
206: raise;
207: end;
208: end loop;
209: end build_grrate;
230: p_table_route_id => l_gsr_tr_id,
231: p_table_name => l_gsr_tr_name);
232: for i in 1..p_gs_rate_matx.count loop
233: if p_gs_rate_matx(i).point1_cer_id is not null then
234: hr_utility.set_location('going for value ',1);
235: get_point_value(p_point_cer_id => p_gs_rate_matx(i).point1_cer_id,
236: p_copy_entity_txn_id => p_copy_entity_txn_id,
237: p_effective_start_date => p_gs_rate_matx(i).esd,
238: p_effective_end_date => p_gs_rate_matx(i).eed,
242: l_point1_value :=null;
243: l_point1_old_value :=null;
244: end if;
245: if p_gs_rate_matx(i).point2_cer_id is not null then
246: hr_utility.set_location('going for value ',1);
247: get_point_value(p_point_cer_id => p_gs_rate_matx(i).point2_cer_id,
248: p_copy_entity_txn_id => p_copy_entity_txn_id,
249: p_effective_start_date => p_gs_rate_matx(i).esd,
250: p_effective_end_date => p_gs_rate_matx(i).eed,
254: l_point2_value :=null;
255: l_point2_old_value :=null;
256: end if;
257: if p_gs_rate_matx(i).point3_cer_id is not null then
258: hr_utility.set_location('going for value ',1);
259: get_point_value(p_point_cer_id => p_gs_rate_matx(i).point3_cer_id,
260: p_copy_entity_txn_id => p_copy_entity_txn_id,
261: p_effective_start_date => p_gs_rate_matx(i).esd,
262: p_effective_end_date => p_gs_rate_matx(i).eed,
266: l_point3_value :=null;
267: l_point3_old_value :=null;
268: end if;
269: if p_gs_rate_matx(i).point4_cer_id is not null then
270: hr_utility.set_location('going for value ',1);
271: get_point_value(p_point_cer_id => p_gs_rate_matx(i).point4_cer_id,
272: p_copy_entity_txn_id => p_copy_entity_txn_id,
273: p_effective_start_date => p_gs_rate_matx(i).esd,
274: p_effective_end_date => p_gs_rate_matx(i).eed,
278: l_point4_value :=null;
279: l_point4_old_value :=null;
280: end if;
281: if p_gs_rate_matx(i).point5_cer_id is not null then
282: hr_utility.set_location('going for value ',1);
283: get_point_value(p_point_cer_id => p_gs_rate_matx(i).point5_cer_id,
284: p_copy_entity_txn_id => p_copy_entity_txn_id,
285: p_effective_start_date => p_gs_rate_matx(i).esd,
286: p_effective_end_date => p_gs_rate_matx(i).eed,
323: ,p_copy_entity_result_id => l_gsr_cer_id
324: ,p_object_version_number => l_gsr_cer_ovn);
325: exception
326: when others then
327: hr_utility.set_location('issue in creation gsrate cer ',400);
328: raise;
329: end;
330: end loop;
331: end build_gsrate;
342: l_num_rec number;
343: begin
344: l_num_rec := p_dt_matx.count;
345: if l_num_rec >1 then
346: hr_utility.set_location('num_rec is'||l_num_rec,10);
347: for i in 1..p_dt_matx.count loop
348: l_esd := p_dt_matx(i);
349: hr_utility.set_location('start date is'||to_char(l_esd,'dd-mm-RRRR'),10);
350: if p_dt_matx.exists(i+1) then
345: if l_num_rec >1 then
346: hr_utility.set_location('num_rec is'||l_num_rec,10);
347: for i in 1..p_dt_matx.count loop
348: l_esd := p_dt_matx(i);
349: hr_utility.set_location('start date is'||to_char(l_esd,'dd-mm-RRRR'),10);
350: if p_dt_matx.exists(i+1) then
351: l_eed := p_dt_matx(i+1) - 1;
352: else
353: l_eed := l_eot;
351: l_eed := p_dt_matx(i+1) - 1;
352: else
353: l_eed := l_eot;
354: end if;
355: hr_utility.set_location('end date is'||to_char(l_eed,'dd-mm-RRRR'),20);
356: for j in 1..p_gs_matx.count loop
357: p_gs_rate_matx(gs_cnt).grade_cer_id := p_gs_matx(j).grade_cer_id;
358: p_gs_rate_matx(gs_cnt).plip_cer_id := p_gs_matx(j).plip_cer_id;
359: p_gs_rate_matx(gs_cnt).num_steps := p_gs_matx(j).num_steps;
369: gs_cnt := gs_cnt + 1;
370: end loop;
371: end loop;
372: else
373: hr_utility.set_location('num_rec is'||l_num_rec,10);
374: l_esd := l_sot;
375: l_eed := l_eot;
376: for j in 1..p_gs_matx.count loop
377: p_gs_rate_matx(j).grade_cer_id := p_gs_matx(j).grade_cer_id;
431: and result_type_cd ='DISPLAY' -- which are displayed
432: and table_alias = 'HRRATE' -- check hrr row
433: and information278 = p_point_cer_id;
434: if nvl(l_num_pt_rates,0) = 0 then
435: hr_utility.set_location('creating abr for pt',5);
436: pqh_gsp_hr_to_stage.create_abr_row
437: (p_copy_entity_txn_id => p_copy_entity_txn_id,
438: p_start_date => l_rate_st_dt,
439: p_opt_cer_id => p_point_cer_id,
441: p_effective_date => p_effective_date,
442: p_abr_cer_id => l_abr_cer_id,
443: p_dml_oper => '');
444: if l_abr_cer_id is not null then
445: hr_utility.set_location('creating hrr for pt',5);
446: pqh_gsp_hr_to_stage.create_hrrate_row
447: (p_copy_entity_txn_id => p_copy_entity_txn_id,
448: p_effective_date => p_effective_date,
449: p_start_date => l_rate_st_dt,
453: p_point_value => 0,
454: p_dml_oper => '',
455: p_hrrate_cer_id => l_hrr_cer_id);
456: if l_hrr_cer_id is null then
457: hr_utility.set_location('issue in creating hrr',10);
458: end if;
459: else
460: hr_utility.set_location('issue in creating abr',9);
461: end if;
456: if l_hrr_cer_id is null then
457: hr_utility.set_location('issue in creating hrr',10);
458: end if;
459: else
460: hr_utility.set_location('issue in creating abr',9);
461: end if;
462: else
463: hr_utility.set_location('num of rates :'||l_num_pt_rates||'for point '||p_point_cer_id,10);
464: hr_utility.set_location('min st date is '||to_char(l_min_st_dt,'dd-mm-RRRR'),10);
459: else
460: hr_utility.set_location('issue in creating abr',9);
461: end if;
462: else
463: hr_utility.set_location('num of rates :'||l_num_pt_rates||'for point '||p_point_cer_id,10);
464: hr_utility.set_location('min st date is '||to_char(l_min_st_dt,'dd-mm-RRRR'),10);
465: hr_utility.set_location('max end date is '||to_char(l_max_end_dt,'dd-mm-RRRR'),10);
466: end if;
467: for rate in csr_dates loop
460: hr_utility.set_location('issue in creating abr',9);
461: end if;
462: else
463: hr_utility.set_location('num of rates :'||l_num_pt_rates||'for point '||p_point_cer_id,10);
464: hr_utility.set_location('min st date is '||to_char(l_min_st_dt,'dd-mm-RRRR'),10);
465: hr_utility.set_location('max end date is '||to_char(l_max_end_dt,'dd-mm-RRRR'),10);
466: end if;
467: for rate in csr_dates loop
468: hr_utility.set_location('date is '||to_char(rate.start_date,'dd-mm-RRRR'),10);
461: end if;
462: else
463: hr_utility.set_location('num of rates :'||l_num_pt_rates||'for point '||p_point_cer_id,10);
464: hr_utility.set_location('min st date is '||to_char(l_min_st_dt,'dd-mm-RRRR'),10);
465: hr_utility.set_location('max end date is '||to_char(l_max_end_dt,'dd-mm-RRRR'),10);
466: end if;
467: for rate in csr_dates loop
468: hr_utility.set_location('date is '||to_char(rate.start_date,'dd-mm-RRRR'),10);
469: update_date_ranges(p_start_date => rate.start_date,
464: hr_utility.set_location('min st date is '||to_char(l_min_st_dt,'dd-mm-RRRR'),10);
465: hr_utility.set_location('max end date is '||to_char(l_max_end_dt,'dd-mm-RRRR'),10);
466: end if;
467: for rate in csr_dates loop
468: hr_utility.set_location('date is '||to_char(rate.start_date,'dd-mm-RRRR'),10);
469: update_date_ranges(p_start_date => rate.start_date,
470: p_dt_matx => p_dt_matx);
471: end loop;
472: end build_hrr_dt_matx;
501: and table_alias = 'CRRATE' -- check crr row
502: and information160 = p_crset_id
503: and information278 = p_point_cer_id;
504: if nvl(l_num_pt_rates,0) = 0 then
505: hr_utility.set_location('creating abr for pt',5);
506: pqh_gsp_hr_to_stage.create_abr_row
507: (p_copy_entity_txn_id => p_copy_entity_txn_id,
508: p_start_date => l_rate_st_dt,
509: p_opt_cer_id => p_point_cer_id,
511: p_effective_date => p_effective_date,
512: p_abr_cer_id => l_abr_cer_id,
513: p_dml_oper => '');
514: if l_abr_cer_id is not null then
515: hr_utility.set_location('creating hrr for pt',5);
516: pqh_gsp_hr_to_stage.create_hrrate_row
517: (p_copy_entity_txn_id => p_copy_entity_txn_id,
518: p_effective_date => p_effective_date,
519: p_start_date => l_rate_st_dt,
523: p_point_value => 0,
524: p_dml_oper => '',
525: p_hrrate_cer_id => l_hrr_cer_id);
526: if l_hrr_cer_id is not null then
527: hr_utility.set_location('creating crr for pt',5);
528: pqh_gsp_hr_to_stage.create_crrate_row
529: (p_point_cer_id => p_point_cer_id,
530: p_copy_entity_txn_id => p_copy_entity_txn_id,
531: p_business_group_id => p_business_group_id,
533: p_vpf_value => 0,
534: p_crset_id => p_crset_id,
535: p_crr_cer_id => l_crr_cer_id);
536: if l_crr_cer_id is null then
537: hr_utility.set_location('issue in creating crr',10);
538: end if;
539: else
540: hr_utility.set_location('issue in creating hrr',10);
541: end if;
536: if l_crr_cer_id is null then
537: hr_utility.set_location('issue in creating crr',10);
538: end if;
539: else
540: hr_utility.set_location('issue in creating hrr',10);
541: end if;
542: else
543: hr_utility.set_location('issue in creating abr',9);
544: end if;
539: else
540: hr_utility.set_location('issue in creating hrr',10);
541: end if;
542: else
543: hr_utility.set_location('issue in creating abr',9);
544: end if;
545: else
546: hr_utility.set_location('num of rates :'||l_num_pt_rates||'for point '||p_point_cer_id,10);
547: hr_utility.set_location('min st date is '||to_char(l_min_st_dt,'dd-mm-RRRR'),10);
542: else
543: hr_utility.set_location('issue in creating abr',9);
544: end if;
545: else
546: hr_utility.set_location('num of rates :'||l_num_pt_rates||'for point '||p_point_cer_id,10);
547: hr_utility.set_location('min st date is '||to_char(l_min_st_dt,'dd-mm-RRRR'),10);
548: hr_utility.set_location('max end date is '||to_char(l_max_end_dt,'dd-mm-RRRR'),10);
549: end if;
550: for rate in csr_dates loop
543: hr_utility.set_location('issue in creating abr',9);
544: end if;
545: else
546: hr_utility.set_location('num of rates :'||l_num_pt_rates||'for point '||p_point_cer_id,10);
547: hr_utility.set_location('min st date is '||to_char(l_min_st_dt,'dd-mm-RRRR'),10);
548: hr_utility.set_location('max end date is '||to_char(l_max_end_dt,'dd-mm-RRRR'),10);
549: end if;
550: for rate in csr_dates loop
551: hr_utility.set_location('date is '||to_char(rate.start_date,'dd-mm-RRRR'),10);
544: end if;
545: else
546: hr_utility.set_location('num of rates :'||l_num_pt_rates||'for point '||p_point_cer_id,10);
547: hr_utility.set_location('min st date is '||to_char(l_min_st_dt,'dd-mm-RRRR'),10);
548: hr_utility.set_location('max end date is '||to_char(l_max_end_dt,'dd-mm-RRRR'),10);
549: end if;
550: for rate in csr_dates loop
551: hr_utility.set_location('date is '||to_char(rate.start_date,'dd-mm-RRRR'),10);
552: update_date_ranges(p_start_date => rate.start_date,
547: hr_utility.set_location('min st date is '||to_char(l_min_st_dt,'dd-mm-RRRR'),10);
548: hr_utility.set_location('max end date is '||to_char(l_max_end_dt,'dd-mm-RRRR'),10);
549: end if;
550: for rate in csr_dates loop
551: hr_utility.set_location('date is '||to_char(rate.start_date,'dd-mm-RRRR'),10);
552: update_date_ranges(p_start_date => rate.start_date,
553: p_dt_matx => p_dt_matx);
554: end loop;
555: end build_crr_dt_matx;
586: stp_counter number := 1;
587: range_counter number := 1;
588: l_continue varchar2(30) := 'Y';
589: begin
590: hr_utility.set_location('inside gs matrix build',1);
591:
592: for grd in csr_grds loop
593: hr_utility.set_location('grd_cer_id is '||grd.copy_entity_result_id,2);
594: select count(*)
589: begin
590: hr_utility.set_location('inside gs matrix build',1);
591:
592: for grd in csr_grds loop
593: hr_utility.set_location('grd_cer_id is '||grd.copy_entity_result_id,2);
594: select count(*)
595: into l_grd_num_steps
596: from ben_copy_entity_results
597: where copy_entity_txn_id = p_copy_entity_txn_id
598: and result_type_cd ='DISPLAY' -- which are displayed
599: and information104 = 'LINK' -- checked linked rows
600: and table_alias = 'COP' -- check oipl row
601: and gs_parent_entity_result_id = grd.copy_entity_result_id; -- check child of plip row
602: hr_utility.set_location('# of steps'||l_grd_num_steps,10);
603: l_num_ranges := ceil(nvl(l_grd_num_steps,0)/5);
604: hr_utility.set_location('# of ranges'||l_num_ranges,4);
605: for step in csr_grd_stps(grd.copy_entity_result_id) loop
606: hr_utility.set_location('pt used in step is'||step.information262,5);
600: and table_alias = 'COP' -- check oipl row
601: and gs_parent_entity_result_id = grd.copy_entity_result_id; -- check child of plip row
602: hr_utility.set_location('# of steps'||l_grd_num_steps,10);
603: l_num_ranges := ceil(nvl(l_grd_num_steps,0)/5);
604: hr_utility.set_location('# of ranges'||l_num_ranges,4);
605: for step in csr_grd_stps(grd.copy_entity_result_id) loop
606: hr_utility.set_location('pt used in step is'||step.information262,5);
607: if stp_counter = 1 then
608: grd_counter := grd_counter + 1;
602: hr_utility.set_location('# of steps'||l_grd_num_steps,10);
603: l_num_ranges := ceil(nvl(l_grd_num_steps,0)/5);
604: hr_utility.set_location('# of ranges'||l_num_ranges,4);
605: for step in csr_grd_stps(grd.copy_entity_result_id) loop
606: hr_utility.set_location('pt used in step is'||step.information262,5);
607: if stp_counter = 1 then
608: grd_counter := grd_counter + 1;
609: p_grd_matx(grd_counter).grade_cer_id := grd.information252 ;
610: p_grd_matx(grd_counter).plip_cer_id := grd.copy_entity_result_id ;
608: grd_counter := grd_counter + 1;
609: p_grd_matx(grd_counter).grade_cer_id := grd.information252 ;
610: p_grd_matx(grd_counter).plip_cer_id := grd.copy_entity_result_id ;
611: p_grd_matx(grd_counter).crset_id := p_crset_id;
612: hr_utility.set_location('grd cer is'||grd.information252,4);
613: p_grd_matx(grd_counter).num_steps := nvl(l_grd_num_steps,0);
614: p_grd_matx(grd_counter).point1_cer_id := step.information262;
615: p_grd_matx(grd_counter).range := range_counter;
616: stp_counter := 2;
641: p_business_group_id => p_business_group_id,
642: p_crset_id => p_crset_id,
643: p_dt_matx => p_dt_matx);
644: else
645: hr_utility.set_location('invalid context '||p_context,98);
646: end if;
647: hr_utility.set_location('going for next oipl',98);
648: end loop;
649: range_counter := 1; -- resetting range counter for next grade
643: p_dt_matx => p_dt_matx);
644: else
645: hr_utility.set_location('invalid context '||p_context,98);
646: end if;
647: hr_utility.set_location('going for next oipl',98);
648: end loop;
649: range_counter := 1; -- resetting range counter for next grade
650: stp_counter := 1 ;-- resetting step counter for next plip;
651: hr_utility.set_location('going for next plip',99);
647: hr_utility.set_location('going for next oipl',98);
648: end loop;
649: range_counter := 1; -- resetting range counter for next grade
650: stp_counter := 1 ;-- resetting step counter for next plip;
651: hr_utility.set_location('going for next plip',99);
652: end loop;
653: end build_gs_matx;
654: procedure build_gs_matrix(p_copy_entity_txn_id in number,
655: p_effective_date in date,
658: l_grd_matx t_gs_matx;
659: l_dt_matx t_pt_matx;
660: l_gs_rate_matx t_gs_rate_matx;
661: begin
662: hr_utility.set_location('inside gs matrix build',1);
663: PQH_GSP_PROCESS_LOG.START_LOG
664: (P_TXN_ID => p_copy_entity_txn_id,
665: P_TXN_NAME => 'CET_gs_matx : '||p_copy_entity_txn_id,
666: P_MODULE_CD => 'PQH_GSP_BENSTG');
671: p_business_group_id => p_business_group_id,
672: p_context => 'HRR',
673: p_grd_matx => l_grd_matx,
674: p_dt_matx => l_dt_matx);
675: hr_utility.set_location('grd and dt matrix build',2);
676: build_gs_rate_matrix(p_dt_matx => l_dt_matx
677: ,p_gs_matx => l_grd_matx
678: ,p_gs_rate_matx => l_gs_rate_matx
679: ,p_business_group_id => p_business_group_id
682: build_gsrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
683: p_gs_rate_matx => l_gs_rate_matx,
684: p_effective_date => p_effective_date,
685: p_business_group_id => p_business_group_id);
686: hr_utility.set_location('leaving gs matrix build',420);
687: PQH_PROCESS_BATCH_LOG.END_LOG;
688: end build_gs_matrix;
689: procedure build_gr_matrix(p_copy_entity_txn_id in number,
690: p_effective_date in date,
694: l_grd_matx t_gs_matx;
695: l_dt_matx t_pt_matx;
696: l_gr_rate_matx t_gs_rate_matx;
697: begin
698: hr_utility.set_location('inside gr matrix build',1);
699: PQH_GSP_PROCESS_LOG.START_LOG
700: (P_TXN_ID => p_copy_entity_txn_id,
701: P_TXN_NAME => 'CET_gr_matx : '||p_copy_entity_txn_id,
702: P_MODULE_CD => 'PQH_GSP_BENSTG');
708: p_context => 'CRR',
709: p_crset_id => p_crset_id,
710: p_grd_matx => l_grd_matx,
711: p_dt_matx => l_dt_matx);
712: hr_utility.set_location('grd and dt matrix build',2);
713: for j in 1..l_grd_matx.count loop
714: hr_utility.set_location('grade cer id is'||l_grd_matx(j).grade_cer_id,15);
715: hr_utility.set_location('crset id is'||l_grd_matx(j).crset_id,15);
716: hr_utility.set_location('# steps is'||l_grd_matx(j).num_steps,20);
710: p_grd_matx => l_grd_matx,
711: p_dt_matx => l_dt_matx);
712: hr_utility.set_location('grd and dt matrix build',2);
713: for j in 1..l_grd_matx.count loop
714: hr_utility.set_location('grade cer id is'||l_grd_matx(j).grade_cer_id,15);
715: hr_utility.set_location('crset id is'||l_grd_matx(j).crset_id,15);
716: hr_utility.set_location('# steps is'||l_grd_matx(j).num_steps,20);
717: hr_utility.set_location('range is'||l_grd_matx(j).range,20);
718: hr_utility.set_location('point1_cer_id is '||l_grd_matx(j).point1_cer_id,25);
711: p_dt_matx => l_dt_matx);
712: hr_utility.set_location('grd and dt matrix build',2);
713: for j in 1..l_grd_matx.count loop
714: hr_utility.set_location('grade cer id is'||l_grd_matx(j).grade_cer_id,15);
715: hr_utility.set_location('crset id is'||l_grd_matx(j).crset_id,15);
716: hr_utility.set_location('# steps is'||l_grd_matx(j).num_steps,20);
717: hr_utility.set_location('range is'||l_grd_matx(j).range,20);
718: hr_utility.set_location('point1_cer_id is '||l_grd_matx(j).point1_cer_id,25);
719: hr_utility.set_location('point2_cer_id is '||l_grd_matx(j).point2_cer_id,25);
712: hr_utility.set_location('grd and dt matrix build',2);
713: for j in 1..l_grd_matx.count loop
714: hr_utility.set_location('grade cer id is'||l_grd_matx(j).grade_cer_id,15);
715: hr_utility.set_location('crset id is'||l_grd_matx(j).crset_id,15);
716: hr_utility.set_location('# steps is'||l_grd_matx(j).num_steps,20);
717: hr_utility.set_location('range is'||l_grd_matx(j).range,20);
718: hr_utility.set_location('point1_cer_id is '||l_grd_matx(j).point1_cer_id,25);
719: hr_utility.set_location('point2_cer_id is '||l_grd_matx(j).point2_cer_id,25);
720: hr_utility.set_location('point3_cer_id is '||l_grd_matx(j).point3_cer_id,25);
713: for j in 1..l_grd_matx.count loop
714: hr_utility.set_location('grade cer id is'||l_grd_matx(j).grade_cer_id,15);
715: hr_utility.set_location('crset id is'||l_grd_matx(j).crset_id,15);
716: hr_utility.set_location('# steps is'||l_grd_matx(j).num_steps,20);
717: hr_utility.set_location('range is'||l_grd_matx(j).range,20);
718: hr_utility.set_location('point1_cer_id is '||l_grd_matx(j).point1_cer_id,25);
719: hr_utility.set_location('point2_cer_id is '||l_grd_matx(j).point2_cer_id,25);
720: hr_utility.set_location('point3_cer_id is '||l_grd_matx(j).point3_cer_id,25);
721: hr_utility.set_location('point4_cer_id is '||l_grd_matx(j).point4_cer_id,25);
714: hr_utility.set_location('grade cer id is'||l_grd_matx(j).grade_cer_id,15);
715: hr_utility.set_location('crset id is'||l_grd_matx(j).crset_id,15);
716: hr_utility.set_location('# steps is'||l_grd_matx(j).num_steps,20);
717: hr_utility.set_location('range is'||l_grd_matx(j).range,20);
718: hr_utility.set_location('point1_cer_id is '||l_grd_matx(j).point1_cer_id,25);
719: hr_utility.set_location('point2_cer_id is '||l_grd_matx(j).point2_cer_id,25);
720: hr_utility.set_location('point3_cer_id is '||l_grd_matx(j).point3_cer_id,25);
721: hr_utility.set_location('point4_cer_id is '||l_grd_matx(j).point4_cer_id,25);
722: hr_utility.set_location('point5_cer_id is '||l_grd_matx(j).point5_cer_id,25);
715: hr_utility.set_location('crset id is'||l_grd_matx(j).crset_id,15);
716: hr_utility.set_location('# steps is'||l_grd_matx(j).num_steps,20);
717: hr_utility.set_location('range is'||l_grd_matx(j).range,20);
718: hr_utility.set_location('point1_cer_id is '||l_grd_matx(j).point1_cer_id,25);
719: hr_utility.set_location('point2_cer_id is '||l_grd_matx(j).point2_cer_id,25);
720: hr_utility.set_location('point3_cer_id is '||l_grd_matx(j).point3_cer_id,25);
721: hr_utility.set_location('point4_cer_id is '||l_grd_matx(j).point4_cer_id,25);
722: hr_utility.set_location('point5_cer_id is '||l_grd_matx(j).point5_cer_id,25);
723: end loop;
716: hr_utility.set_location('# steps is'||l_grd_matx(j).num_steps,20);
717: hr_utility.set_location('range is'||l_grd_matx(j).range,20);
718: hr_utility.set_location('point1_cer_id is '||l_grd_matx(j).point1_cer_id,25);
719: hr_utility.set_location('point2_cer_id is '||l_grd_matx(j).point2_cer_id,25);
720: hr_utility.set_location('point3_cer_id is '||l_grd_matx(j).point3_cer_id,25);
721: hr_utility.set_location('point4_cer_id is '||l_grd_matx(j).point4_cer_id,25);
722: hr_utility.set_location('point5_cer_id is '||l_grd_matx(j).point5_cer_id,25);
723: end loop;
724: build_gs_rate_matrix(p_dt_matx => l_dt_matx
717: hr_utility.set_location('range is'||l_grd_matx(j).range,20);
718: hr_utility.set_location('point1_cer_id is '||l_grd_matx(j).point1_cer_id,25);
719: hr_utility.set_location('point2_cer_id is '||l_grd_matx(j).point2_cer_id,25);
720: hr_utility.set_location('point3_cer_id is '||l_grd_matx(j).point3_cer_id,25);
721: hr_utility.set_location('point4_cer_id is '||l_grd_matx(j).point4_cer_id,25);
722: hr_utility.set_location('point5_cer_id is '||l_grd_matx(j).point5_cer_id,25);
723: end loop;
724: build_gs_rate_matrix(p_dt_matx => l_dt_matx
725: ,p_gs_matx => l_grd_matx
718: hr_utility.set_location('point1_cer_id is '||l_grd_matx(j).point1_cer_id,25);
719: hr_utility.set_location('point2_cer_id is '||l_grd_matx(j).point2_cer_id,25);
720: hr_utility.set_location('point3_cer_id is '||l_grd_matx(j).point3_cer_id,25);
721: hr_utility.set_location('point4_cer_id is '||l_grd_matx(j).point4_cer_id,25);
722: hr_utility.set_location('point5_cer_id is '||l_grd_matx(j).point5_cer_id,25);
723: end loop;
724: build_gs_rate_matrix(p_dt_matx => l_dt_matx
725: ,p_gs_matx => l_grd_matx
726: ,p_gs_rate_matx => l_gr_rate_matx
726: ,p_gs_rate_matx => l_gr_rate_matx
727: ,p_business_group_id => p_business_group_id
728: ,p_copy_entity_txn_id => p_copy_entity_txn_id);
729:
730: hr_utility.set_location('grd rate matrix build',2);
731: for k in 1..l_gr_rate_matx.count loop
732: hr_utility.set_location('grade cer id is'||l_gr_rate_matx(k).grade_cer_id,15);
733: hr_utility.set_location('crset id is'||l_gr_rate_matx(k).crset_id,15);
734: hr_utility.set_location('# steps is'||l_gr_rate_matx(k).num_steps,20);
728: ,p_copy_entity_txn_id => p_copy_entity_txn_id);
729:
730: hr_utility.set_location('grd rate matrix build',2);
731: for k in 1..l_gr_rate_matx.count loop
732: hr_utility.set_location('grade cer id is'||l_gr_rate_matx(k).grade_cer_id,15);
733: hr_utility.set_location('crset id is'||l_gr_rate_matx(k).crset_id,15);
734: hr_utility.set_location('# steps is'||l_gr_rate_matx(k).num_steps,20);
735: hr_utility.set_location('range is'||l_gr_rate_matx(k).range,20);
736: hr_utility.set_location('point1_cer_id is '||l_gr_rate_matx(k).point1_cer_id,25);
729:
730: hr_utility.set_location('grd rate matrix build',2);
731: for k in 1..l_gr_rate_matx.count loop
732: hr_utility.set_location('grade cer id is'||l_gr_rate_matx(k).grade_cer_id,15);
733: hr_utility.set_location('crset id is'||l_gr_rate_matx(k).crset_id,15);
734: hr_utility.set_location('# steps is'||l_gr_rate_matx(k).num_steps,20);
735: hr_utility.set_location('range is'||l_gr_rate_matx(k).range,20);
736: hr_utility.set_location('point1_cer_id is '||l_gr_rate_matx(k).point1_cer_id,25);
737: hr_utility.set_location('point2_cer_id is '||l_gr_rate_matx(k).point2_cer_id,25);
730: hr_utility.set_location('grd rate matrix build',2);
731: for k in 1..l_gr_rate_matx.count loop
732: hr_utility.set_location('grade cer id is'||l_gr_rate_matx(k).grade_cer_id,15);
733: hr_utility.set_location('crset id is'||l_gr_rate_matx(k).crset_id,15);
734: hr_utility.set_location('# steps is'||l_gr_rate_matx(k).num_steps,20);
735: hr_utility.set_location('range is'||l_gr_rate_matx(k).range,20);
736: hr_utility.set_location('point1_cer_id is '||l_gr_rate_matx(k).point1_cer_id,25);
737: hr_utility.set_location('point2_cer_id is '||l_gr_rate_matx(k).point2_cer_id,25);
738: hr_utility.set_location('point3_cer_id is '||l_gr_rate_matx(k).point3_cer_id,25);
731: for k in 1..l_gr_rate_matx.count loop
732: hr_utility.set_location('grade cer id is'||l_gr_rate_matx(k).grade_cer_id,15);
733: hr_utility.set_location('crset id is'||l_gr_rate_matx(k).crset_id,15);
734: hr_utility.set_location('# steps is'||l_gr_rate_matx(k).num_steps,20);
735: hr_utility.set_location('range is'||l_gr_rate_matx(k).range,20);
736: hr_utility.set_location('point1_cer_id is '||l_gr_rate_matx(k).point1_cer_id,25);
737: hr_utility.set_location('point2_cer_id is '||l_gr_rate_matx(k).point2_cer_id,25);
738: hr_utility.set_location('point3_cer_id is '||l_gr_rate_matx(k).point3_cer_id,25);
739: hr_utility.set_location('point4_cer_id is '||l_gr_rate_matx(k).point4_cer_id,25);
732: hr_utility.set_location('grade cer id is'||l_gr_rate_matx(k).grade_cer_id,15);
733: hr_utility.set_location('crset id is'||l_gr_rate_matx(k).crset_id,15);
734: hr_utility.set_location('# steps is'||l_gr_rate_matx(k).num_steps,20);
735: hr_utility.set_location('range is'||l_gr_rate_matx(k).range,20);
736: hr_utility.set_location('point1_cer_id is '||l_gr_rate_matx(k).point1_cer_id,25);
737: hr_utility.set_location('point2_cer_id is '||l_gr_rate_matx(k).point2_cer_id,25);
738: hr_utility.set_location('point3_cer_id is '||l_gr_rate_matx(k).point3_cer_id,25);
739: hr_utility.set_location('point4_cer_id is '||l_gr_rate_matx(k).point4_cer_id,25);
740: hr_utility.set_location('point5_cer_id is '||l_gr_rate_matx(k).point5_cer_id,25);
733: hr_utility.set_location('crset id is'||l_gr_rate_matx(k).crset_id,15);
734: hr_utility.set_location('# steps is'||l_gr_rate_matx(k).num_steps,20);
735: hr_utility.set_location('range is'||l_gr_rate_matx(k).range,20);
736: hr_utility.set_location('point1_cer_id is '||l_gr_rate_matx(k).point1_cer_id,25);
737: hr_utility.set_location('point2_cer_id is '||l_gr_rate_matx(k).point2_cer_id,25);
738: hr_utility.set_location('point3_cer_id is '||l_gr_rate_matx(k).point3_cer_id,25);
739: hr_utility.set_location('point4_cer_id is '||l_gr_rate_matx(k).point4_cer_id,25);
740: hr_utility.set_location('point5_cer_id is '||l_gr_rate_matx(k).point5_cer_id,25);
741: end loop;
734: hr_utility.set_location('# steps is'||l_gr_rate_matx(k).num_steps,20);
735: hr_utility.set_location('range is'||l_gr_rate_matx(k).range,20);
736: hr_utility.set_location('point1_cer_id is '||l_gr_rate_matx(k).point1_cer_id,25);
737: hr_utility.set_location('point2_cer_id is '||l_gr_rate_matx(k).point2_cer_id,25);
738: hr_utility.set_location('point3_cer_id is '||l_gr_rate_matx(k).point3_cer_id,25);
739: hr_utility.set_location('point4_cer_id is '||l_gr_rate_matx(k).point4_cer_id,25);
740: hr_utility.set_location('point5_cer_id is '||l_gr_rate_matx(k).point5_cer_id,25);
741: end loop;
742: delete_grrate(p_copy_entity_txn_id => p_copy_entity_txn_id);
735: hr_utility.set_location('range is'||l_gr_rate_matx(k).range,20);
736: hr_utility.set_location('point1_cer_id is '||l_gr_rate_matx(k).point1_cer_id,25);
737: hr_utility.set_location('point2_cer_id is '||l_gr_rate_matx(k).point2_cer_id,25);
738: hr_utility.set_location('point3_cer_id is '||l_gr_rate_matx(k).point3_cer_id,25);
739: hr_utility.set_location('point4_cer_id is '||l_gr_rate_matx(k).point4_cer_id,25);
740: hr_utility.set_location('point5_cer_id is '||l_gr_rate_matx(k).point5_cer_id,25);
741: end loop;
742: delete_grrate(p_copy_entity_txn_id => p_copy_entity_txn_id);
743: build_grrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
736: hr_utility.set_location('point1_cer_id is '||l_gr_rate_matx(k).point1_cer_id,25);
737: hr_utility.set_location('point2_cer_id is '||l_gr_rate_matx(k).point2_cer_id,25);
738: hr_utility.set_location('point3_cer_id is '||l_gr_rate_matx(k).point3_cer_id,25);
739: hr_utility.set_location('point4_cer_id is '||l_gr_rate_matx(k).point4_cer_id,25);
740: hr_utility.set_location('point5_cer_id is '||l_gr_rate_matx(k).point5_cer_id,25);
741: end loop;
742: delete_grrate(p_copy_entity_txn_id => p_copy_entity_txn_id);
743: build_grrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
744: p_gr_rate_matx => l_gr_rate_matx,
743: build_grrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
744: p_gr_rate_matx => l_gr_rate_matx,
745: p_effective_date => p_effective_date,
746: p_business_group_id => p_business_group_id);
747: hr_utility.set_location('leaving gr matrix build',420);
748: PQH_PROCESS_BATCH_LOG.END_LOG;
749: end build_gr_matrix;
750: procedure correct_hrrate(p_copy_entity_txn_id in number,
751: p_point_cer_id in number,
760: and p_effective_date between information2 and information3
761: and copy_entity_txn_id = p_copy_entity_txn_id;
762: exception
763: when no_data_found then
764: hr_utility.set_location('no hrrate row found',10);
765: raise;
766: when too_many_rows then
767: hr_utility.set_location('2 or more hrrate row found',15);
768: raise;
763: when no_data_found then
764: hr_utility.set_location('no hrrate row found',10);
765: raise;
766: when too_many_rows then
767: hr_utility.set_location('2 or more hrrate row found',15);
768: raise;
769: when others then
770: hr_utility.set_location('issues in correcting hrrate row',20);
771: raise;
766: when too_many_rows then
767: hr_utility.set_location('2 or more hrrate row found',15);
768: raise;
769: when others then
770: hr_utility.set_location('issues in correcting hrrate row',20);
771: raise;
772: end correct_hrrate;
773: procedure update_hrrate(p_copy_entity_txn_id in number,
774: p_point_cer_id in number,
787: and p_effective_date between information2 and information3
788: and copy_entity_txn_id = p_copy_entity_txn_id;
789: exception
790: when no_data_found then
791: hr_utility.set_location('no hrrate row found',10);
792: raise;
793: when too_many_rows then
794: hr_utility.set_location('2 or more hrrate rows found',15);
795: raise;
790: when no_data_found then
791: hr_utility.set_location('no hrrate row found',10);
792: raise;
793: when too_many_rows then
794: hr_utility.set_location('2 or more hrrate rows found',15);
795: raise;
796: when others then
797: hr_utility.set_location('issues in correcting hrrate row',20);
798: raise;
793: when too_many_rows then
794: hr_utility.set_location('2 or more hrrate rows found',15);
795: raise;
796: when others then
797: hr_utility.set_location('issues in correcting hrrate row',20);
798: raise;
799: end;
800: if l_old_hrr_cer_id is not null then
801: update_hrrate(p_old_hrrate_cer_id => l_old_hrr_cer_id,
802: p_effective_date => p_effective_date,
803: p_datetrack_mode => p_datetrack_mode,
804: p_new_hrrate_cer_id => l_new_hrr_cer_id,
805: p_value => p_new_value);
806: hr_utility.set_location('hrrate cer replaced '||l_new_hrr_cer_id,40);
807: else
808: hr_utility.set_location('old hrrate not found ',50);
809: end if;
810: end update_hrrate;
804: p_new_hrrate_cer_id => l_new_hrr_cer_id,
805: p_value => p_new_value);
806: hr_utility.set_location('hrrate cer replaced '||l_new_hrr_cer_id,40);
807: else
808: hr_utility.set_location('old hrrate not found ',50);
809: end if;
810: end update_hrrate;
811: procedure update_hrrate(p_old_hrrate_cer_id in number,
812: p_effective_date in date,
840: l_upd_effdt varchar2(30);
841: l_hrr_eed date;
842: l_hrr_ovn number;
843: begin
844: hr_utility.set_location('inside update_hrrate '||p_old_hrrate_cer_id,10);
845: select copy_entity_txn_id,table_alias,table_name,table_route_id,dml_operation,datetrack_mode,
846: information1,information2,information3,information4,information277,information278,
847: information293,information300,information298
848: into l_cet_id,l_table_alias,l_table_name,l_table_route_id,l_dml_oper,l_dt_mode,
859: l_new_dt_mode := 'CORRECTION';
860: else
861: l_new_dt_mode := l_dt_mode;
862: end if;
863: hr_utility.set_location('dt_mode is'||p_datetrack_mode,10);
864: hr_utility.set_location('effdt is'||to_char(p_effective_date,'dd-mm-RRRR'),10);
865: hr_utility.set_location('esd is'||to_char(l_esd,'dd-mm-RRRR'),10);
866: if p_datetrack_mode ='CORRECTION' then
867: l_ins_row := 'N';
860: else
861: l_new_dt_mode := l_dt_mode;
862: end if;
863: hr_utility.set_location('dt_mode is'||p_datetrack_mode,10);
864: hr_utility.set_location('effdt is'||to_char(p_effective_date,'dd-mm-RRRR'),10);
865: hr_utility.set_location('esd is'||to_char(l_esd,'dd-mm-RRRR'),10);
866: if p_datetrack_mode ='CORRECTION' then
867: l_ins_row := 'N';
868: l_del_future := 'N';
861: l_new_dt_mode := l_dt_mode;
862: end if;
863: hr_utility.set_location('dt_mode is'||p_datetrack_mode,10);
864: hr_utility.set_location('effdt is'||to_char(p_effective_date,'dd-mm-RRRR'),10);
865: hr_utility.set_location('esd is'||to_char(l_esd,'dd-mm-RRRR'),10);
866: if p_datetrack_mode ='CORRECTION' then
867: l_ins_row := 'N';
868: l_del_future := 'N';
869: l_upd_curr := 'Y';
888: l_upd_effdt := 'Y';
889: end if;
890: end if;
891: if l_upd_curr ='Y' then
892: hr_utility.set_location('updating current row with values',10);
893: begin
894: update ben_copy_entity_results
895: set information294 = p_grd_min_value,
896: information295 = p_grd_max_value,
908: and nvl(information287,0) =0 ;
909:
910: --ggnanagu
911:
912: hr_utility.set_location('old hrrate updated '||p_old_hrrate_cer_id,10);
913: p_new_hrrate_cer_id := p_old_hrrate_cer_id;
914: exception
915: when others then
916: hr_utility.set_location('some issue in updating hrrate row ',80);
912: hr_utility.set_location('old hrrate updated '||p_old_hrrate_cer_id,10);
913: p_new_hrrate_cer_id := p_old_hrrate_cer_id;
914: exception
915: when others then
916: hr_utility.set_location('some issue in updating hrrate row ',80);
917: raise;
918: end;
919: end if;
920: if l_del_future = 'Y' then
917: raise;
918: end;
919: end if;
920: if l_del_future = 'Y' then
921: hr_utility.set_location('deleting future rows ',10);
922: begin
923: delete from ben_copy_entity_results
924: where copy_entity_txn_id = l_cet_id
925: and table_alias = 'HRRATE'
927: and (information278 is null or information278 = l_point_cer_id)
928: and information2 > p_effective_date;
929: exception
930: when others then
931: hr_utility.set_location('some issue in deleting hrrate row ',100);
932: raise;
933: end;
934: end if;
935: if l_upd_effdt = 'Y' then
932: raise;
933: end;
934: end if;
935: if l_upd_effdt = 'Y' then
936: hr_utility.set_location('updating effdt of curr row ',10);
937: begin
938: update ben_copy_entity_results
939: set information3 = l_hrr_eed
940: where copy_entity_result_id = p_old_hrrate_cer_id;
937: begin
938: update ben_copy_entity_results
939: set information3 = l_hrr_eed
940: where copy_entity_result_id = p_old_hrrate_cer_id;
941: hr_utility.set_location('old hrrate updated '||p_old_hrrate_cer_id,10);
942: exception
943: when others then
944: hr_utility.set_location('some issue in updating hrrate row ',80);
945: raise;
940: where copy_entity_result_id = p_old_hrrate_cer_id;
941: hr_utility.set_location('old hrrate updated '||p_old_hrrate_cer_id,10);
942: exception
943: when others then
944: hr_utility.set_location('some issue in updating hrrate row ',80);
945: raise;
946: end;
947: end if;
948: if l_ins_row = 'Y' then
945: raise;
946: end;
947: end if;
948: if l_ins_row = 'Y' then
949: hr_utility.set_location('inserting new row ',10);
950: begin
951: -- These mappings have been taken from hrben_to_stage document
952: -- call to create ben_cer is made here using api.
953: ben_copy_entity_results_api.create_copy_entity_results
977: ,p_information298 => l_hrr_ovn
978: ,p_information300 => l_abr_cer_id
979: ,p_copy_entity_result_id => p_new_hrrate_cer_id
980: ,p_object_version_number => l_hrr_cer_ovn);
981: hr_utility.set_location('new hrrate created '||p_new_hrrate_cer_id,10);
982: exception
983: when others then
984: hr_utility.set_location('some issue in creating hrrate row ',120);
985: raise;
980: ,p_object_version_number => l_hrr_cer_ovn);
981: hr_utility.set_location('new hrrate created '||p_new_hrrate_cer_id,10);
982: exception
983: when others then
984: hr_utility.set_location('some issue in creating hrrate row ',120);
985: raise;
986: end;
987: end if;
988: end update_hrrate;
990: p_pl_cer_id in number default null,
991: p_point_cer_id in number default null,
992: p_value in number) is
993: begin
994: hr_utility.set_location('applying data to hgrid',10);
995: if p_pl_cer_id is not null then
996: begin
997: update ben_copy_entity_results
998: set information298 = p_value
1000: and information252 = p_pl_cer_id
1001: and copy_entity_txn_id = p_copy_entity_txn_id;
1002: exception
1003: when others then
1004: hr_utility.set_location('issues in updating plip ',20);
1005: raise;
1006: end;
1007: else
1008: begin
1012: and information262 = p_point_cer_id
1013: and copy_entity_txn_id = p_copy_entity_txn_id;
1014: exception
1015: when others then
1016: hr_utility.set_location('issues in updating oipl ',30);
1017: raise;
1018: end;
1019: end if;
1020: hr_utility.set_location('done applying data ',100);
1016: hr_utility.set_location('issues in updating oipl ',30);
1017: raise;
1018: end;
1019: end if;
1020: hr_utility.set_location('done applying data ',100);
1021: end update_hgrid_data;
1022: procedure update_grade_hrrate(p_copy_entity_txn_id in number,
1023: p_rt_effective_date in date,
1024: p_gl_effective_date in date,
1043: -- if the hrrate cer is passed then abr exists else we may have to create it
1044: -- find the hrrate row which lies on this effective date for this grade cer
1045: if p_hrrate_cer_id is null then
1046: -- hrrate doesnot exist, we have to create it, check abr for the Grade whether that exists or not
1047: hr_utility.set_location('hrrate doesnot exist ',10);
1048: if p_grade_cer_id is not null then
1049: hr_utility.set_location('grade cer is '||p_grade_cer_id,20);
1050: l_start_date := pqh_gsp_hr_to_stage.get_grd_start_date(p_grade_cer_id);
1051: l_abr_cer_id := pqh_gsp_hr_to_stage.get_abr_cer
1045: if p_hrrate_cer_id is null then
1046: -- hrrate doesnot exist, we have to create it, check abr for the Grade whether that exists or not
1047: hr_utility.set_location('hrrate doesnot exist ',10);
1048: if p_grade_cer_id is not null then
1049: hr_utility.set_location('grade cer is '||p_grade_cer_id,20);
1050: l_start_date := pqh_gsp_hr_to_stage.get_grd_start_date(p_grade_cer_id);
1051: l_abr_cer_id := pqh_gsp_hr_to_stage.get_abr_cer
1052: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1053: p_pl_cer_id => p_grade_cer_id,
1051: l_abr_cer_id := pqh_gsp_hr_to_stage.get_abr_cer
1052: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1053: p_pl_cer_id => p_grade_cer_id,
1054: p_effective_date => p_rt_effective_date);
1055: hr_utility.set_location('abr cer is '||l_abr_cer_id,30);
1056: if l_abr_cer_id is null then
1057: hr_utility.set_location('going for abr row create ',40);
1058: pqh_gsp_hr_to_stage.create_abr_row
1059: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1053: p_pl_cer_id => p_grade_cer_id,
1054: p_effective_date => p_rt_effective_date);
1055: hr_utility.set_location('abr cer is '||l_abr_cer_id,30);
1056: if l_abr_cer_id is null then
1057: hr_utility.set_location('going for abr row create ',40);
1058: pqh_gsp_hr_to_stage.create_abr_row
1059: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1060: p_pl_cer_id => p_grade_cer_id,
1061: p_business_group_id => p_business_group_id,
1062: p_effective_date => p_rt_effective_date,
1063: p_start_date => l_start_date,
1064: p_abr_cer_id => l_abr_cer_id,
1065: p_dml_oper => 'INSERT');
1066: hr_utility.set_location('abr cer is '||l_abr_cer_id,50);
1067: end if;
1068: hr_utility.set_location('going for hrrate row create ',60);
1069: pqh_gsp_hr_to_stage.create_hrrate_row
1070: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1064: p_abr_cer_id => l_abr_cer_id,
1065: p_dml_oper => 'INSERT');
1066: hr_utility.set_location('abr cer is '||l_abr_cer_id,50);
1067: end if;
1068: hr_utility.set_location('going for hrrate row create ',60);
1069: pqh_gsp_hr_to_stage.create_hrrate_row
1070: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1071: p_effective_date => p_rt_effective_date,
1072: p_start_date => l_start_date,
1078: p_grd_mid_value => p_grd_mid_value,
1079: p_grd_max_value => p_grd_max_value,
1080: p_dml_oper => 'INSERT',
1081: p_hrrate_cer_id => p_hrrate_cer_id);
1082: hr_utility.set_location('hrrate cer is '||p_hrrate_cer_id,70);
1083: if p_gl_effective_date <= p_rt_effective_date then
1084: update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
1085: p_pl_cer_id => p_grade_cer_id,
1086: p_value => p_grd_value);
1085: p_pl_cer_id => p_grade_cer_id,
1086: p_value => p_grd_value);
1087: end if;
1088: else
1089: hr_utility.set_location('grade not in stage ',80);
1090: end if;
1091: else
1092: update_hrrate(p_old_hrrate_cer_id => p_hrrate_cer_id,
1093: p_effective_date => p_rt_effective_date,
1096: p_datetrack_mode => p_datetrack_mode,
1097: p_grd_min_value => p_grd_min_value,
1098: p_grd_mid_value => p_grd_mid_value,
1099: p_grd_max_value => p_grd_max_value);
1100: hr_utility.set_location('hrrate cer replaced '||l_new_hrrate_cer_id,200);
1101: if p_datetrack_mode ='CORRECTION' and p_gl_effective_date between l_esd and nvl(l_eed,l_eot) then
1102: update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
1103: p_pl_cer_id => p_grade_cer_id,
1104: p_value => p_grd_value);
1106: update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
1107: p_pl_cer_id => p_grade_cer_id,
1108: p_value => p_grd_value);
1109: else
1110: hr_utility.set_location('wrong datetrack mode passed ',200);
1111: end if;
1112: end if;
1113: end update_grade_hrrate;
1114: procedure update_point_hrrate(p_copy_entity_txn_id in number,
1132: -- if the hrrate cer is passed then abr exists else we may have to create it
1133: -- find the hrrate row which lies on this effective date for this grade cer
1134: if p_hrrate_cer_id is null then
1135: -- hrrate doesnot exist, we have to create it, check abr for the Grade whether that exists or not
1136: hr_utility.set_location('hrrate doesnot exist ',10);
1137: if p_point_cer_id is not null then
1138: hr_utility.set_location('grade cer is '||p_point_cer_id,20);
1139: l_abr_cer_id := pqh_gsp_hr_to_stage.get_abr_cer
1140: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1134: if p_hrrate_cer_id is null then
1135: -- hrrate doesnot exist, we have to create it, check abr for the Grade whether that exists or not
1136: hr_utility.set_location('hrrate doesnot exist ',10);
1137: if p_point_cer_id is not null then
1138: hr_utility.set_location('grade cer is '||p_point_cer_id,20);
1139: l_abr_cer_id := pqh_gsp_hr_to_stage.get_abr_cer
1140: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1141: p_opt_cer_id => p_point_cer_id,
1142: p_effective_date => p_rt_effective_date);
1139: l_abr_cer_id := pqh_gsp_hr_to_stage.get_abr_cer
1140: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1141: p_opt_cer_id => p_point_cer_id,
1142: p_effective_date => p_rt_effective_date);
1143: hr_utility.set_location('abr cer is '||l_abr_cer_id,30);
1144: if l_abr_cer_id is null then
1145: hr_utility.set_location('going for abr row create ',40);
1146: pqh_gsp_hr_to_stage.create_abr_row
1147: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1141: p_opt_cer_id => p_point_cer_id,
1142: p_effective_date => p_rt_effective_date);
1143: hr_utility.set_location('abr cer is '||l_abr_cer_id,30);
1144: if l_abr_cer_id is null then
1145: hr_utility.set_location('going for abr row create ',40);
1146: pqh_gsp_hr_to_stage.create_abr_row
1147: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1148: p_start_date => l_start_date,
1149: p_opt_cer_id => p_point_cer_id,
1150: p_business_group_id => p_business_group_id,
1151: p_effective_date => p_rt_effective_date,
1152: p_abr_cer_id => l_abr_cer_id,
1153: p_dml_oper => 'INSERT');
1154: hr_utility.set_location('abr cer is '||l_abr_cer_id,50);
1155: end if;
1156: hr_utility.set_location('going for hrrate row create ',60);
1157: pqh_gsp_hr_to_stage.create_hrrate_row
1158: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1152: p_abr_cer_id => l_abr_cer_id,
1153: p_dml_oper => 'INSERT');
1154: hr_utility.set_location('abr cer is '||l_abr_cer_id,50);
1155: end if;
1156: hr_utility.set_location('going for hrrate row create ',60);
1157: pqh_gsp_hr_to_stage.create_hrrate_row
1158: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1159: p_start_date => l_start_date,
1160: p_effective_date => p_rt_effective_date,
1163: p_point_cer_id => p_point_cer_id,
1164: p_point_value => p_point_value,
1165: p_dml_oper => 'INSERT',
1166: p_hrrate_cer_id => p_hrrate_cer_id);
1167: hr_utility.set_location('hrrate cer is '||p_hrrate_cer_id,70);
1168: if p_gl_effective_date <= p_rt_effective_date then
1169: update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
1170: p_point_cer_id => p_point_cer_id,
1171: p_value => p_point_value);
1170: p_point_cer_id => p_point_cer_id,
1171: p_value => p_point_value);
1172: end if;
1173: else
1174: hr_utility.set_location('point not in stage ',80);
1175: end if;
1176: else
1177: update_hrrate(p_old_hrrate_cer_id => p_hrrate_cer_id,
1178: p_effective_date => p_rt_effective_date,
1178: p_effective_date => p_rt_effective_date,
1179: p_datetrack_mode => p_datetrack_mode,
1180: p_new_hrrate_cer_id => l_new_hrrate_cer_id,
1181: p_value => p_point_value);
1182: hr_utility.set_location('hrrate cer replaced '||l_new_hrrate_cer_id,200);
1183: if p_datetrack_mode ='CORRECTION' and p_gl_effective_date between l_esd and nvl(l_eed,l_eot) then
1184: update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
1185: p_point_cer_id => p_point_cer_id,
1186: p_value => p_point_value);
1216: l_hrrate_exists := pqh_gsp_hr_to_stage.is_hrrate_for_abr_exists
1217: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1218: p_abr_id => p_abr_id);
1219: if not l_hrrate_exists then
1220: hr_utility.set_location('hrrate doesnot exist for abr'||p_abr_id,10);
1221: -- get the table route id and table alias
1222: pqh_gsp_hr_to_stage.get_table_route_details
1223: (p_table_alias => 'HRRATE',
1224: p_table_route_id => l_hrr_tr_id,
1222: pqh_gsp_hr_to_stage.get_table_route_details
1223: (p_table_alias => 'HRRATE',
1224: p_table_route_id => l_hrr_tr_id,
1225: p_table_name => l_hrr_tr_name);
1226: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,20);
1227: l_grd_cer_id := pqh_gsp_hr_to_stage.is_grd_exists_in_txn
1228: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1229: p_grd_id => p_grade_id);
1230: if l_grd_cer_id is null then
1227: l_grd_cer_id := pqh_gsp_hr_to_stage.is_grd_exists_in_txn
1228: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1229: p_grd_id => p_grade_id);
1230: if l_grd_cer_id is null then
1231: hr_utility.set_location('grade doesnot exist in stage'||p_grade_id,30);
1232: l_continue := FALSE;
1233: else
1234: hr_utility.set_location('grade in stage'||l_grd_cer_id,40);
1235: end if;
1230: if l_grd_cer_id is null then
1231: hr_utility.set_location('grade doesnot exist in stage'||p_grade_id,30);
1232: l_continue := FALSE;
1233: else
1234: hr_utility.set_location('grade in stage'||l_grd_cer_id,40);
1235: end if;
1236: if l_hrr_tr_name is null then
1237: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
1238: l_continue := FALSE;
1233: else
1234: hr_utility.set_location('grade in stage'||l_grd_cer_id,40);
1235: end if;
1236: if l_hrr_tr_name is null then
1237: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
1238: l_continue := FALSE;
1239: end if;
1240: if p_copy_entity_txn_id is null then
1241: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
1237: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
1238: l_continue := FALSE;
1239: end if;
1240: if p_copy_entity_txn_id is null then
1241: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
1242: l_continue := FALSE;
1243: end if;
1244: if l_continue then
1245: for rec in csr_grd_rate loop
1276: ,p_copy_entity_result_id => l_hrrate_cer_id
1277: ,p_object_version_number => l_hrr_cer_ovn);
1278: exception
1279: when others then
1280: hr_utility.set_location('some issue in creating hrrate row ',120);
1281: end;
1282: end loop;
1283: end if;
1284: else
1281: end;
1282: end loop;
1283: end if;
1284: else
1285: hr_utility.set_location('hrrate exists ',60);
1286: end if;
1287: end create_grade_hrrate;
1288: procedure create_point_hrrate(p_copy_entity_txn_id in number,
1289: p_effective_date in date,
1315: pqh_gsp_hr_to_stage.get_table_route_details
1316: (p_table_alias => 'HRRATE',
1317: p_table_route_id => l_hrr_tr_id,
1318: p_table_name => l_hrr_tr_name);
1319: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,50);
1320: l_point_cer_id := pqh_gsp_hr_to_stage.is_point_exists_in_txn
1321: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1322: p_point_id => p_point_id);
1323: if l_point_cer_id is null then
1320: l_point_cer_id := pqh_gsp_hr_to_stage.is_point_exists_in_txn
1321: (p_copy_entity_txn_id => p_copy_entity_txn_id,
1322: p_point_id => p_point_id);
1323: if l_point_cer_id is null then
1324: hr_utility.set_location('point doesnot exist in stage'||p_point_id,30);
1325: l_continue := FALSE;
1326: else
1327: hr_utility.set_location('point in stage'||l_point_cer_id,40);
1328: end if;
1323: if l_point_cer_id is null then
1324: hr_utility.set_location('point doesnot exist in stage'||p_point_id,30);
1325: l_continue := FALSE;
1326: else
1327: hr_utility.set_location('point in stage'||l_point_cer_id,40);
1328: end if;
1329: if l_hrr_tr_name is null then
1330: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
1331: l_continue := FALSE;
1326: else
1327: hr_utility.set_location('point in stage'||l_point_cer_id,40);
1328: end if;
1329: if l_hrr_tr_name is null then
1330: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
1331: l_continue := FALSE;
1332: end if;
1333: if p_copy_entity_txn_id is null then
1334: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
1330: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
1331: l_continue := FALSE;
1332: end if;
1333: if p_copy_entity_txn_id is null then
1334: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
1335: l_continue := FALSE;
1336: end if;
1337: if l_continue then
1338: for rec in csr_point_rate loop
1363: ,p_copy_entity_result_id => l_hrrate_cer_id
1364: ,p_object_version_number => l_hrr_cer_ovn);
1365: exception
1366: when others then
1367: hr_utility.set_location('some issue in creating point hrrate row ',120);
1368: end;
1369: end loop;
1370: end if;
1371: else
1368: end;
1369: end loop;
1370: end if;
1371: else
1372: hr_utility.set_location('hrrate exists ',60);
1373: end if;
1374: end create_point_hrrate;
1375: procedure update_crrate(p_crset_id in number,
1376: p_effective_date in date,
1398: l_crr_cer_id number;
1399: l_eot date := to_date('31-12-4712','dd-mm-RRRR');
1400: begin
1401: if p_datetrack_mode not in ('CORRECTION','UPDATE_REPLACE') then
1402: hr_utility.set_location('invalid dt mode '||p_datetrack_mode,1);
1403: l_continue := false;
1404: end if;
1405: if p_grade_cer_id is null and p_point_cer_id is null then
1406: hr_utility.set_location('grd/pr cer should be passed',2);
1402: hr_utility.set_location('invalid dt mode '||p_datetrack_mode,1);
1403: l_continue := false;
1404: end if;
1405: if p_grade_cer_id is null and p_point_cer_id is null then
1406: hr_utility.set_location('grd/pr cer should be passed',2);
1407: l_continue := false;
1408: end if;
1409: if l_continue then
1410: for crrate_rec in csr_crrate loop
1407: l_continue := false;
1408: end if;
1409: if l_continue then
1410: for crrate_rec in csr_crrate loop
1411: hr_utility.set_location('criteria rate row found'||crrate_rec.copy_entity_result_id,10);
1412: if nvl(crrate_rec.dml_operation,'REUSE') = 'REUSE' then
1413: l_dml_operation := 'UPDATE';
1414: else
1415: l_dml_operation := crrate_rec.dml_operation;
1413: l_dml_operation := 'UPDATE';
1414: else
1415: l_dml_operation := crrate_rec.dml_operation;
1416: end if;
1417: hr_utility.set_location('crrate dml_oper is'||l_dml_operation,3);
1418: if p_datetrack_mode = 'CORRECTION' then
1419: hr_utility.set_location('same row is to be updated',6);
1420: l_upd_curr := 'Y';
1421: l_del_future := 'N';
1415: l_dml_operation := crrate_rec.dml_operation;
1416: end if;
1417: hr_utility.set_location('crrate dml_oper is'||l_dml_operation,3);
1418: if p_datetrack_mode = 'CORRECTION' then
1419: hr_utility.set_location('same row is to be updated',6);
1420: l_upd_curr := 'Y';
1421: l_del_future := 'N';
1422: l_crrate_eed := crrate_rec.information3;
1423: l_upd_effdt := 'N';
1424: l_ins_row := 'N';
1425: else
1426: if crrate_rec.information2 = p_effective_date then
1427: -- row is getting updated on same date, so no insert only update
1428: hr_utility.set_location('row started today, so no ins',7);
1429: l_ins_row := 'N';
1430: l_upd_curr := 'Y';
1431: l_upd_effdt := 'N';
1432: l_crrate_eed := l_eot;
1431: l_upd_effdt := 'N';
1432: l_crrate_eed := l_eot;
1433: if crrate_rec.information3 <> l_eot then
1434: -- current row goes till end of time so no delete too
1435: hr_utility.set_location('row ending early , del fut',8);
1436: l_del_future := 'Y';
1437: else
1438: hr_utility.set_location('row going till eot , so no del',9);
1439: l_del_future := 'N';
1434: -- current row goes till end of time so no delete too
1435: hr_utility.set_location('row ending early , del fut',8);
1436: l_del_future := 'Y';
1437: else
1438: hr_utility.set_location('row going till eot , so no del',9);
1439: l_del_future := 'N';
1440: end if;
1441: else
1442: hr_utility.set_location('row started earlier, so upd_repl',10);
1438: hr_utility.set_location('row going till eot , so no del',9);
1439: l_del_future := 'N';
1440: end if;
1441: else
1442: hr_utility.set_location('row started earlier, so upd_repl',10);
1443: l_del_future := 'Y';
1444: l_upd_curr := 'N';
1445: l_ins_row := 'Y';
1446: l_upd_effdt := 'Y';
1447: l_crrate_eed := p_effective_date - 1;
1448: end if;
1449: end if;
1450: if l_del_future = 'Y' then
1451: hr_utility.set_location('fut rows being deleted',11);
1452: delete from ben_copy_entity_results
1453: where copy_entity_txn_id = p_copy_entity_txn_id
1454: and table_alias = 'CRRATE'
1455: and information160 = p_crset_id
1457: and (information169 is null or information169 = p_point_cer_id)
1458: and information2 > p_effective_date;
1459: end if;
1460: if l_upd_effdt ='Y' then
1461: hr_utility.set_location('effdt of curr_row being changed',12);
1462: update ben_copy_entity_results
1463: set INFORMATION3 = l_crrate_eed
1464: where copy_entity_result_id = crrate_rec.copy_entity_result_id;
1465: end if;
1463: set INFORMATION3 = l_crrate_eed
1464: where copy_entity_result_id = crrate_rec.copy_entity_result_id;
1465: end if;
1466: if l_upd_curr ='Y' then
1467: hr_utility.set_location('curr_row data being changed',13);
1468: update ben_copy_entity_results
1469: set dml_operation = l_dml_operation,
1470: INFORMATION293 = p_new_value,
1471: information3 = l_crrate_eed
1479:
1480:
1481: end if;
1482: if l_ins_row ='Y' then
1483: hr_utility.set_location('eot is '||to_char(l_eot,'dd-mm-RRRR'),14);
1484: pqh_gsp_hr_to_stage.create_crrate_row
1485: (p_effective_date => p_effective_date,
1486: p_copy_entity_txn_id => p_copy_entity_txn_id,
1487: p_grade_cer_id => p_grade_cer_id,
1499: p_crset_id => crrate_rec.information160,
1500: p_elp_id => crrate_rec.information279,
1501: p_crr_cer_id => l_crr_cer_id);
1502: end if;
1503: hr_utility.set_location('1 row should be processed ',15);
1504: end loop;
1505: end if;
1506: end update_crrate;
1507: procedure create_gsr_row(p_copy_entity_txn_id in number,
1530: pqh_gsp_hr_to_stage.get_table_route_details
1531: (p_table_alias => 'GSRATE',
1532: p_table_route_id => l_gsr_tr_id,
1533: p_table_name => l_gsr_tr_name);
1534: hr_utility.set_location('gsr tr name'||l_gsr_tr_name,20);
1535: if l_gsr_tr_name is null then
1536: hr_utility.set_location('gsr tr name'||l_gsr_tr_name,45);
1537: l_continue := FALSE;
1538: end if;
1532: p_table_route_id => l_gsr_tr_id,
1533: p_table_name => l_gsr_tr_name);
1534: hr_utility.set_location('gsr tr name'||l_gsr_tr_name,20);
1535: if l_gsr_tr_name is null then
1536: hr_utility.set_location('gsr tr name'||l_gsr_tr_name,45);
1537: l_continue := FALSE;
1538: end if;
1539: if p_copy_entity_txn_id is null then
1540: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
1536: hr_utility.set_location('gsr tr name'||l_gsr_tr_name,45);
1537: l_continue := FALSE;
1538: end if;
1539: if p_copy_entity_txn_id is null then
1540: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
1541: l_continue := FALSE;
1542: end if;
1543: if p_grade_cer_id is null then
1544: hr_utility.set_location('grade cer id is reqd',55);
1540: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
1541: l_continue := FALSE;
1542: end if;
1543: if p_grade_cer_id is null then
1544: hr_utility.set_location('grade cer id is reqd',55);
1545: l_continue := FALSE;
1546: end if;
1547: if l_continue then
1548: begin
1579: ,p_copy_entity_result_id => p_gsr_cer_id
1580: ,p_object_version_number => l_gsr_cer_ovn);
1581: exception
1582: when others then
1583: hr_utility.set_location('some issue in creating gsr row ',120);
1584: end;
1585: end if;
1586: end create_gsr_row;
1587: procedure create_grr_row(p_copy_entity_txn_id in number,
1612: pqh_gsp_hr_to_stage.get_table_route_details
1613: (p_table_alias => 'GRRATE',
1614: p_table_route_id => l_grr_tr_id,
1615: p_table_name => l_grr_tr_name);
1616: hr_utility.set_location('grr tr name'||l_grr_tr_name,20);
1617: if l_grr_tr_name is null then
1618: hr_utility.set_location('grr tr name'||l_grr_tr_name,45);
1619: l_continue := FALSE;
1620: end if;
1614: p_table_route_id => l_grr_tr_id,
1615: p_table_name => l_grr_tr_name);
1616: hr_utility.set_location('grr tr name'||l_grr_tr_name,20);
1617: if l_grr_tr_name is null then
1618: hr_utility.set_location('grr tr name'||l_grr_tr_name,45);
1619: l_continue := FALSE;
1620: end if;
1621: if p_copy_entity_txn_id is null then
1622: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
1618: hr_utility.set_location('grr tr name'||l_grr_tr_name,45);
1619: l_continue := FALSE;
1620: end if;
1621: if p_copy_entity_txn_id is null then
1622: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
1623: l_continue := FALSE;
1624: end if;
1625: if p_grade_cer_id is null then
1626: hr_utility.set_location('grade cer id is reqd',55);
1622: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
1623: l_continue := FALSE;
1624: end if;
1625: if p_grade_cer_id is null then
1626: hr_utility.set_location('grade cer id is reqd',55);
1627: l_continue := FALSE;
1628: end if;
1629: if l_continue then
1630: begin
1663: ,p_copy_entity_result_id => p_grr_cer_id
1664: ,p_object_version_number => l_grr_cer_ovn);
1665: exception
1666: when others then
1667: hr_utility.set_location('some issue in creating grr row ',120);
1668: end;
1669: end if;
1670: end create_grr_row;
1671: procedure update_gsrate(p_copy_entity_txn_id in number,
1699: l_crrate_eed date;
1700: l_upd_effdt varchar2(30);
1701: l_ins_row varchar2(30);
1702: begin
1703: hr_utility.set_location('cet is '||p_copy_entity_txn_id,1);
1704: hr_utility.set_location('gsr cer is '||p_gsr_cer_id,2);
1705: hr_utility.set_location('dt mode is '||p_datetrack_mode,3);
1706: select information160, information229, information231, information174,
1707: information178, information222, information287, information288, information289,
1700: l_upd_effdt varchar2(30);
1701: l_ins_row varchar2(30);
1702: begin
1703: hr_utility.set_location('cet is '||p_copy_entity_txn_id,1);
1704: hr_utility.set_location('gsr cer is '||p_gsr_cer_id,2);
1705: hr_utility.set_location('dt mode is '||p_datetrack_mode,3);
1706: select information160, information229, information231, information174,
1707: information178, information222, information287, information288, information289,
1708: information290, information291, information228, information2, information3
1701: l_ins_row varchar2(30);
1702: begin
1703: hr_utility.set_location('cet is '||p_copy_entity_txn_id,1);
1704: hr_utility.set_location('gsr cer is '||p_gsr_cer_id,2);
1705: hr_utility.set_location('dt mode is '||p_datetrack_mode,3);
1706: select information160, information229, information231, information174,
1707: information178, information222, information287, information288, information289,
1708: information290, information291, information228, information2, information3
1709: into l_grade_cer_id, l_point1_cer_id, l_point2_cer_id, l_point3_cer_id,
1711: l_point4_value, l_point5_value, l_step_range, l_esd, l_eed
1712: from ben_copy_entity_results
1713: where copy_entity_result_id = p_gsr_cer_id
1714: and copy_entity_txn_id = p_copy_entity_txn_id;
1715: hr_utility.set_location('values pulled',4);
1716: if p_datetrack_mode ='CORRECTION' then
1717: l_upd_curr := 'Y';
1718: l_del_future := 'N';
1719: l_crrate_eed := l_eed;
1748: information291 = p_value5,
1749: information3 = l_crrate_eed
1750: where copy_entity_result_id = p_gsr_cer_id
1751: and copy_entity_txn_id = p_copy_entity_txn_id;
1752: hr_utility.set_location('gsrate row corr',5);
1753: end if;
1754: if l_upd_effdt = 'Y' then
1755: update ben_copy_entity_results
1756: set INFORMATION3 = p_effective_date -1
1755: update ben_copy_entity_results
1756: set INFORMATION3 = p_effective_date -1
1757: where copy_entity_result_id = p_gsr_cer_id
1758: and copy_entity_txn_id = p_copy_entity_txn_id;
1759: hr_utility.set_location('curr row end dt',12);
1760: end if;
1761: if l_del_future = 'Y' then
1762: -- remove the future rows
1763: delete from ben_copy_entity_results
1764: where copy_entity_txn_id = p_copy_entity_txn_id
1765: and table_alias = 'GSRATE'
1766: and information160 = l_grade_cer_id
1767: and information2 > p_effective_date;
1768: hr_utility.set_location('fut row removed',13);
1769: end if;
1770: if l_ins_row = 'Y' then
1771: -- insert the new row
1772: create_gsr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
1785: p_business_group_id => p_business_group_id,
1786: p_effective_date => p_effective_date,
1787: p_step_range => l_step_range,
1788: p_gsr_cer_id => l_gsr_cer_id);
1789: hr_utility.set_location('new row inserted',14);
1790: end if;
1791: -- update the hrrate rows
1792: update_hrrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1793: p_point_cer_id => l_point1_cer_id,
1793: p_point_cer_id => l_point1_cer_id,
1794: p_datetrack_mode => p_datetrack_mode,
1795: p_effective_date => p_effective_date,
1796: p_new_value => p_value1);
1797: hr_utility.set_location('hrrate row upd_r'||l_point1_cer_id,15);
1798: if l_point2_cer_id is not null then
1799: update_hrrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1800: p_point_cer_id => l_point2_cer_id,
1801: p_datetrack_mode => p_datetrack_mode,
1800: p_point_cer_id => l_point2_cer_id,
1801: p_datetrack_mode => p_datetrack_mode,
1802: p_effective_date => p_effective_date,
1803: p_new_value => p_value2);
1804: hr_utility.set_location('hrrate row upd_r'||l_point2_cer_id,16);
1805: end if;
1806: if l_point3_cer_id is not null then
1807: update_hrrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1808: p_point_cer_id => l_point3_cer_id,
1808: p_point_cer_id => l_point3_cer_id,
1809: p_datetrack_mode => p_datetrack_mode,
1810: p_effective_date => p_effective_date,
1811: p_new_value => p_value3);
1812: hr_utility.set_location('hrrate row upd_r'||l_point3_cer_id,17);
1813: end if;
1814: if l_point4_cer_id is not null then
1815: update_hrrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1816: p_point_cer_id => l_point4_cer_id,
1816: p_point_cer_id => l_point4_cer_id,
1817: p_datetrack_mode => p_datetrack_mode,
1818: p_effective_date => p_effective_date,
1819: p_new_value => p_value4);
1820: hr_utility.set_location('hrrate row upd_r'||l_point4_cer_id,18);
1821: end if;
1822: if l_point5_cer_id is not null then
1823: update_hrrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1824: p_point_cer_id => l_point5_cer_id,
1824: p_point_cer_id => l_point5_cer_id,
1825: p_datetrack_mode => p_datetrack_mode,
1826: p_effective_date => p_effective_date,
1827: p_new_value => p_value5);
1828: hr_utility.set_location('hrrate row upd_r'||l_point5_cer_id,19);
1829: end if;
1830: end update_gsrate;
1831: procedure update_grrate(p_copy_entity_txn_id in number,
1832: p_grr_cer_id in number,
1861: l_crrate_eed date;
1862: l_upd_effdt varchar2(30);
1863: l_ins_row varchar2(30);
1864: begin
1865: hr_utility.set_location('cet is '||p_copy_entity_txn_id,1);
1866: hr_utility.set_location('grr cer is '||p_grr_cer_id,2);
1867: hr_utility.set_location('dt mode is '||p_datetrack_mode,3);
1868: select information160, information161,information162,information229, information231, information174,
1869: information178, information222, information287, information288, information289,
1862: l_upd_effdt varchar2(30);
1863: l_ins_row varchar2(30);
1864: begin
1865: hr_utility.set_location('cet is '||p_copy_entity_txn_id,1);
1866: hr_utility.set_location('grr cer is '||p_grr_cer_id,2);
1867: hr_utility.set_location('dt mode is '||p_datetrack_mode,3);
1868: select information160, information161,information162,information229, information231, information174,
1869: information178, information222, information287, information288, information289,
1870: information290, information291, information228, information2, information3
1863: l_ins_row varchar2(30);
1864: begin
1865: hr_utility.set_location('cet is '||p_copy_entity_txn_id,1);
1866: hr_utility.set_location('grr cer is '||p_grr_cer_id,2);
1867: hr_utility.set_location('dt mode is '||p_datetrack_mode,3);
1868: select information160, information161,information162,information229, information231, information174,
1869: information178, information222, information287, information288, information289,
1870: information290, information291, information228, information2, information3
1871: into l_grade_cer_id, l_crset_id, l_plip_cer_id,l_point1_cer_id, l_point2_cer_id, l_point3_cer_id,
1873: l_point4_value, l_point5_value, l_step_range, l_esd, l_eed
1874: from ben_copy_entity_results
1875: where copy_entity_result_id = p_grr_cer_id
1876: and copy_entity_txn_id = p_copy_entity_txn_id;
1877: hr_utility.set_location('values pulled',4);
1878: if p_datetrack_mode ='CORRECTION' then
1879: l_upd_curr := 'Y';
1880: l_del_future := 'N';
1881: l_crrate_eed := l_eed;
1910: information291 = p_value5,
1911: information3 = l_crrate_eed
1912: where copy_entity_result_id = p_grr_cer_id
1913: and copy_entity_txn_id = p_copy_entity_txn_id;
1914: hr_utility.set_location('grrate row corr',5);
1915: end if;
1916: if l_upd_effdt = 'Y' then
1917: update ben_copy_entity_results
1918: set INFORMATION3 = p_effective_date -1
1917: update ben_copy_entity_results
1918: set INFORMATION3 = p_effective_date -1
1919: where copy_entity_result_id = p_grr_cer_id
1920: and copy_entity_txn_id = p_copy_entity_txn_id;
1921: hr_utility.set_location('curr row end dt',12);
1922: end if;
1923: if l_del_future = 'Y' then
1924: -- remove the future rows
1925: delete from ben_copy_entity_results
1926: where copy_entity_txn_id = p_copy_entity_txn_id
1927: and table_alias = 'GRRATE'
1928: and information160 = l_grade_cer_id
1929: and information2 > p_effective_date;
1930: hr_utility.set_location('fut row removed',13);
1931: end if;
1932: if l_ins_row = 'Y' then
1933: hr_utility.set_location('new row inserted',14);
1934: create_grr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
1929: and information2 > p_effective_date;
1930: hr_utility.set_location('fut row removed',13);
1931: end if;
1932: if l_ins_row = 'Y' then
1933: hr_utility.set_location('new row inserted',14);
1934: create_grr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
1935: p_start_date => p_effective_date,
1936: p_grade_cer_id => l_grade_cer_id,
1937: p_plip_cer_id => l_plip_cer_id,
1957: p_crset_id => l_crset_id,
1958: p_datetrack_mode => p_datetrack_mode,
1959: p_effective_date => p_effective_date,
1960: p_new_value => p_value1);
1961: hr_utility.set_location('crrate row upd_r'||l_point1_cer_id,15);
1962: if l_point2_cer_id is not null then
1963: update_crrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1964: p_point_cer_id => l_point2_cer_id,
1965: p_crset_id => l_crset_id,
1965: p_crset_id => l_crset_id,
1966: p_datetrack_mode => p_datetrack_mode,
1967: p_effective_date => p_effective_date,
1968: p_new_value => p_value2);
1969: hr_utility.set_location('crrate row upd_r'||l_point2_cer_id,16);
1970: end if;
1971: if l_point3_cer_id is not null then
1972: update_crrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1973: p_point_cer_id => l_point3_cer_id,
1974: p_crset_id => l_crset_id,
1975: p_datetrack_mode => p_datetrack_mode,
1976: p_effective_date => p_effective_date,
1977: p_new_value => p_value3);
1978: hr_utility.set_location('crrate row upd_r'||l_point3_cer_id,17);
1979: end if;
1980: if l_point4_cer_id is not null then
1981: update_crrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1982: p_point_cer_id => l_point4_cer_id,
1983: p_crset_id => l_crset_id,
1984: p_datetrack_mode => p_datetrack_mode,
1985: p_effective_date => p_effective_date,
1986: p_new_value => p_value4);
1987: hr_utility.set_location('crrate row upd_r'||l_point4_cer_id,18);
1988: end if;
1989: if l_point5_cer_id is not null then
1990: update_crrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1991: p_point_cer_id => l_point5_cer_id,
1992: p_crset_id => l_crset_id,
1993: p_datetrack_mode => p_datetrack_mode,
1994: p_effective_date => p_effective_date,
1995: p_new_value => p_value5);
1996: hr_utility.set_location('crrate row upd_r'||l_point5_cer_id,19);
1997: end if;
1998: end update_grrate;
1999: procedure sync_crrate(p_crset_id in number,
2000: p_point_cer_id in number,