[Home] [Help]
PACKAGE BODY: APPS.PQH_GSP_RATES
Source
1 package body pqh_gsp_rates as
2 /* $Header: pqgsprat.pkb 120.3.12010000.1 2008/07/28 12:57:52 appldev ship $ */
3 procedure delete_grrate(p_copy_entity_txn_id in number) is
4 begin
5 delete from ben_copy_entity_results
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
15 delete from ben_copy_entity_results
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,
25 p_crset_id in number,
26 p_effective_start_date in date,
27 p_effective_end_date in date,
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
37 into p_point_value,p_point_old_value
38 from ben_copy_entity_results
39 where table_alias = 'CRRATE'
40 and copy_entity_txn_id = p_copy_entity_txn_id
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
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,
56 p_effective_start_date in date,
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
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;
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,
83 p_effective_date in date,
84 p_business_group_id in number) is
85 l_grr_tr_id number;
86 l_grr_tr_name varchar2(80);
87 l_grr_cer_id number;
88 l_grr_cer_ovn number;
89 l_point1_value number default null;
90 l_point2_value number default null;
91 l_point3_value number default null;
92 l_point4_value number default null;
93 l_point5_value number default null;
94 l_point1_old_value number default null;
95 l_point2_old_value number default null;
96 l_point3_old_value number default null;
97 l_point4_old_value number default null;
98 l_point5_old_value number default null;
99
100 begin
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,
110 p_crset_id => p_gr_rate_matx(i).crset_id,
111 p_effective_start_date => p_gr_rate_matx(i).esd,
112 p_effective_end_date => p_gr_rate_matx(i).eed,
113 p_point_value => l_point1_value,
114 p_point_old_value => l_point1_old_value);
115 else
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,
125 p_effective_end_date => p_gr_rate_matx(i).eed,
126 p_point_value => l_point2_value,
127 p_point_old_value => l_point2_old_value);
128 else
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,
138 p_effective_end_date => p_gr_rate_matx(i).eed,
139 p_point_value => l_point3_value,
140 p_point_old_value => l_point3_old_value);
141 else
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,
151 p_effective_end_date => p_gr_rate_matx(i).eed,
152 p_point_value => l_point4_value,
153 p_point_old_value => l_point4_old_value);
154 else
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,
164 p_effective_end_date => p_gr_rate_matx(i).eed,
165 p_point_value => l_point5_value,
166 p_point_old_value => l_point5_old_value);
167 else
168 l_point5_value := null;
169 l_point5_old_value := null;
170 end if;
171 begin
172 ben_copy_entity_results_api.create_copy_entity_results(
173 p_effective_date => p_effective_date
174 ,p_copy_entity_txn_id => p_copy_entity_txn_id
175 ,p_result_type_cd => 'DISPLAY'
176 ,p_table_name => l_grr_tr_name
177 ,p_table_route_id => l_grr_tr_id
178 ,p_table_alias => 'GRRATE'
179 ,p_information2 => p_gr_rate_matx(i).esd
180 ,p_information3 => p_gr_rate_matx(i).eed
181 ,p_information4 => p_business_group_id
182 ,p_information160 => p_gr_rate_matx(i).grade_cer_id
183 ,p_information162 => p_gr_rate_matx(i).plip_cer_id
184 ,p_information161 => p_gr_rate_matx(i).crset_id
185 ,p_information229 => p_gr_rate_matx(i).point1_cer_id
186 ,p_information231 => p_gr_rate_matx(i).point2_cer_id
187 ,p_information174 => p_gr_rate_matx(i).point3_cer_id
188 ,p_information178 => p_gr_rate_matx(i).point4_cer_id
189 ,p_information222 => p_gr_rate_matx(i).point5_cer_id
190 ,p_information228 => p_gr_rate_matx(i).range
191 ,p_information287 => l_point1_value
192 ,p_information288 => l_point2_value
193 ,p_information289 => l_point3_value
194 ,p_information290 => l_point4_value
195 ,p_information291 => l_point5_value
196 ,p_information297 => l_point1_old_value
197 ,p_information298 => l_point2_old_value
198 ,p_information299 => l_point3_old_value
199 ,p_information300 => l_point4_old_value
200 ,p_information301 => l_point5_old_value
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;
210 procedure build_gsrate(p_copy_entity_txn_id in number,
211 p_gs_rate_matx in t_gs_rate_matx,
212 p_effective_date in date,
213 p_business_group_id in number) is
214 l_gsr_tr_id number;
215 l_gsr_tr_name varchar2(80);
216 l_gsr_cer_id number;
217 l_gsr_cer_ovn number;
218 l_point1_value number default null;
219 l_point2_value number default null;
220 l_point3_value number default null;
221 l_point4_value number default null;
222 l_point5_value number default null;
223 l_point1_old_value number default null;
224 l_point2_old_value number default null;
225 l_point3_old_value number default null;
226 l_point4_old_value number default null;
227 l_point5_old_value number default null;
228 begin
229 pqh_gsp_hr_to_stage.get_table_route_details(p_table_alias => 'GSRATE',
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,
239 p_point_value =>l_point1_value,
240 p_point_old_value => l_point1_old_value);
241 else
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,
251 p_point_value =>l_point2_value,
252 p_point_old_value => l_point2_old_value);
253 else
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,
263 p_point_value =>l_point3_value,
264 p_point_old_value => l_point3_old_value);
265 else
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,
275 p_point_value =>l_point4_value,
276 p_point_old_value => l_point4_old_value);
277 else
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,
287 p_point_value =>l_point5_value,
288 p_point_old_value => l_point5_old_value);
289 else
290 l_point5_value :=null;
291 l_point5_old_value :=null;
292 end if;
293 begin
294 ben_copy_entity_results_api.create_copy_entity_results(
295 p_effective_date => p_effective_date
296 ,p_copy_entity_txn_id => p_copy_entity_txn_id
297 ,p_result_type_cd => 'DISPLAY'
298 ,p_table_name => l_gsr_tr_name
299 ,p_table_route_id => l_gsr_tr_id
300 ,p_table_alias => 'GSRATE'
301 ,p_dml_operation => '' -- hrrate has the values
302 -- ,p_information1 => p_oipl_id -- new ben object
303 ,p_information2 => p_gs_rate_matx(i).esd
304 ,p_information3 => p_gs_rate_matx(i).eed
305 ,p_information4 => p_business_group_id
306 ,p_information160 => p_gs_rate_matx(i).grade_cer_id
307 ,p_information229 => p_gs_rate_matx(i).point1_cer_id
308 ,p_information231 => p_gs_rate_matx(i).point2_cer_id
309 ,p_information174 => p_gs_rate_matx(i).point3_cer_id
310 ,p_information178 => p_gs_rate_matx(i).point4_cer_id
311 ,p_information222 => p_gs_rate_matx(i).point5_cer_id
312 ,p_information228 => p_gs_rate_matx(i).range
313 ,p_information287 => l_point1_value
314 ,p_information288 => l_point2_value
315 ,p_information289 => l_point3_value
316 ,p_information290 => l_point4_value
317 ,p_information291 => l_point5_value
318 ,p_information297 => l_point1_old_value
319 ,p_information298 => l_point2_old_value
320 ,p_information299 => l_point3_old_value
321 ,p_information300 => l_point4_old_value
322 ,p_information301 => l_point5_old_value
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;
332 procedure build_gs_rate_matrix(p_dt_matx in t_pt_matx
333 ,p_gs_matx in t_gs_matx
334 ,p_gs_rate_matx out nocopy t_gs_rate_matx
335 ,p_business_group_id in number
336 ,p_copy_entity_txn_id in number ) is
337 gs_cnt number := 1;
338 l_esd date;
339 l_eed date;
340 l_sot date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
341 l_eot date := hr_general.end_of_time;
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
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;
360 p_gs_rate_matx(gs_cnt).crset_id := p_gs_matx(j).crset_id;
361 p_gs_rate_matx(gs_cnt).range := p_gs_matx(j).range;
362 p_gs_rate_matx(gs_cnt).point1_cer_id := p_gs_matx(j).point1_cer_id;
363 p_gs_rate_matx(gs_cnt).point2_cer_id := p_gs_matx(j).point2_cer_id;
364 p_gs_rate_matx(gs_cnt).point3_cer_id := p_gs_matx(j).point3_cer_id;
365 p_gs_rate_matx(gs_cnt).point4_cer_id := p_gs_matx(j).point4_cer_id;
366 p_gs_rate_matx(gs_cnt).point5_cer_id := p_gs_matx(j).point5_cer_id;
367 p_gs_rate_matx(gs_cnt).esd := l_esd;
368 p_gs_rate_matx(gs_cnt).eed := l_eed;
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;
378 p_gs_rate_matx(j).plip_cer_id := p_gs_matx(j).plip_cer_id;
379 p_gs_rate_matx(j).num_steps := p_gs_matx(j).num_steps;
380 p_gs_rate_matx(j).crset_id := p_gs_matx(j).crset_id;
381 p_gs_rate_matx(j).range := p_gs_matx(j).range;
382 p_gs_rate_matx(j).point1_cer_id := p_gs_matx(j).point1_cer_id;
383 p_gs_rate_matx(j).point2_cer_id := p_gs_matx(j).point2_cer_id;
384 p_gs_rate_matx(j).point3_cer_id := p_gs_matx(j).point3_cer_id;
385 p_gs_rate_matx(j).point4_cer_id := p_gs_matx(j).point4_cer_id;
386 p_gs_rate_matx(j).point5_cer_id := p_gs_matx(j).point5_cer_id;
387 p_gs_rate_matx(j).esd := l_esd;
388 p_gs_rate_matx(j).eed := l_eed;
389 end loop;
390 end if;
391 end build_gs_rate_matrix;
392 procedure update_date_ranges(p_start_date in date,
393 p_dt_matx in out nocopy t_pt_matx) is
394 l_exists boolean := false;
395 l_count number;
396 begin
397 for i in 1..p_dt_matx.count loop
398 if p_dt_matx(i)= p_start_date then
399 l_exists := true;
400 end if;
401 end loop;
402 if not l_exists then
403 l_count := nvl(p_dt_matx.last,0) + 1;
404 p_dt_matx(l_count):= p_start_date;
405 end if;
406 end;
407 procedure build_hrr_dt_matx(p_point_cer_id in number,
408 p_copy_entity_txn_id in number,
409 p_effective_date in date,
410 p_business_group_id in number,
411 p_dt_matx in out nocopy t_pt_matx) is
412 cursor csr_dates is
413 select information2 start_date
414 from ben_copy_entity_results
415 where copy_entity_txn_id = p_copy_entity_txn_id
416 and result_type_cd ='DISPLAY' -- which are displayed
417 and table_alias = 'HRRATE' -- check oipl row
418 and information278 = p_point_cer_id -- check rate of point row
419 order by 1 ;
420 l_rate_st_dt date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
421 l_abr_cer_id number;
422 l_hrr_cer_id number;
423 l_num_pt_rates number;
424 l_min_st_dt date;
425 l_max_end_dt date;
426 begin
427 select count(*),min(information2),max(information3)
428 into l_num_pt_rates,l_min_st_dt,l_max_end_dt
429 from ben_copy_entity_results
430 where copy_entity_txn_id = p_copy_entity_txn_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,
440 p_business_group_id => p_business_group_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,
450 p_business_group_id => p_business_group_id,
451 p_abr_cer_id => l_abr_cer_id,
452 p_point_cer_id => p_point_cer_id,
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;
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,
470 p_dt_matx => p_dt_matx);
471 end loop;
472 end build_hrr_dt_matx;
473 procedure build_crr_dt_matx(p_point_cer_id in number,
474 p_crset_id in number,
475 p_effective_date in date,
476 p_copy_entity_txn_id in number,
477 p_business_group_id in number,
478 p_dt_matx in out nocopy t_pt_matx) is
479 cursor csr_dates is
480 select information2 start_date
481 from ben_copy_entity_results
482 where copy_entity_txn_id = p_copy_entity_txn_id
483 and result_type_cd ='DISPLAY' -- which are displayed
484 and table_alias = 'CRRATE' -- check oipl row
485 and information169 = p_point_cer_id -- check rate of point row
486 and information160 = p_crset_id
487 order by 1 ;
488 l_rate_st_dt date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
489 l_abr_cer_id number;
490 l_hrr_cer_id number;
491 l_crr_cer_id number;
492 l_num_pt_rates number;
493 l_min_st_dt date;
494 l_max_end_dt date;
495 begin
496 select count(*),min(information2),max(information3)
497 into l_num_pt_rates,l_min_st_dt,l_max_end_dt
498 from ben_copy_entity_results
499 where copy_entity_txn_id = p_copy_entity_txn_id
500 and result_type_cd ='DISPLAY' -- which are displayed
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,
510 p_business_group_id => p_business_group_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,
520 p_business_group_id => p_business_group_id,
521 p_abr_cer_id => l_abr_cer_id,
522 p_point_cer_id => p_point_cer_id,
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,
532 p_effective_date => l_rate_st_dt,
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;
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
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;
556 procedure build_gs_matx(p_copy_entity_txn_id in number,
557 p_effective_date in date,
558 p_business_group_id in number,
559 p_context in varchar2,
560 p_crset_id in number default null,
561 p_grd_matx out nocopy t_gs_matx,
562 p_dt_matx out nocopy t_pt_matx) is
563 cursor csr_grds is
564 select copy_entity_result_id,information252,information253
565 -- into p_plip_cer_id,p_grade_cer_id,p_grade_id
566 from ben_copy_entity_results
567 where copy_entity_txn_id = p_copy_entity_txn_id
568 and result_type_cd ='DISPLAY' -- which are displayed
569 and information104 = 'LINK' -- checked linked rows
570 and table_alias = 'CPP' -- check plip row
571 order by information263 ; -- for getting the order of grades correct
572 cursor csr_grd_stps(p_plip_cer_id number) is
573 select information262
574 -- into p_point_cer_id
575 from ben_copy_entity_results
576 where copy_entity_txn_id = p_copy_entity_txn_id
577 and result_type_cd ='DISPLAY' -- which are displayed
578 and information104 = 'LINK' -- checked linked rows
579 and table_alias = 'COP' -- check oipl row
580 and gs_parent_entity_result_id = p_plip_cer_id -- check child of plip row
581 order by information263 ; -- step sequence
582
583 grd_counter number := 0;
584 l_grd_num_steps number;
585 l_num_ranges number;
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(*)
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);
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 ;
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;
617 range_counter := range_counter + 1;
618 elsif stp_counter = 2 then
619 stp_counter := 3;
620 p_grd_matx(grd_counter).point2_cer_id := step.information262;
621 elsif stp_counter = 3 then
622 stp_counter := 4;
623 p_grd_matx(grd_counter).point3_cer_id := step.information262;
624 elsif stp_counter = 4 then
625 stp_counter := 5;
626 p_grd_matx(grd_counter).point4_cer_id := step.information262;
627 elsif stp_counter = 5 then
628 stp_counter := 1;
629 p_grd_matx(grd_counter).point5_cer_id := step.information262;
630 end if;
631 if p_context ='HRR' then
632 build_hrr_dt_matx(p_point_cer_id => step.information262,
633 p_copy_entity_txn_id => p_copy_entity_txn_id,
634 p_effective_date => p_effective_date,
635 p_business_group_id => p_business_group_id,
636 p_dt_matx => p_dt_matx);
637 elsif p_context ='CRR' then
638 build_crr_dt_matx(p_point_cer_id => step.information262,
639 p_copy_entity_txn_id => p_copy_entity_txn_id,
640 p_effective_date => p_effective_date,
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
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,
656 p_business_group_id in number) is
657
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');
667 pqh_gsp_hr_to_stage.g_master_txn_id := p_copy_entity_txn_id;
668 pqh_gsp_hr_to_stage.g_txn_id := p_copy_entity_txn_id;
669 build_gs_matx(p_copy_entity_txn_id => p_copy_entity_txn_id,
670 p_effective_date => p_effective_date,
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
680 ,p_copy_entity_txn_id => p_copy_entity_txn_id);
681 delete_gsrate(p_copy_entity_txn_id => p_copy_entity_txn_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,
691 p_crset_id in number,
692 p_business_group_id in number) is
693
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');
703 pqh_gsp_hr_to_stage.g_master_txn_id := p_copy_entity_txn_id;
704 pqh_gsp_hr_to_stage.g_txn_id := p_copy_entity_txn_id;
705 build_gs_matx(p_copy_entity_txn_id => p_copy_entity_txn_id,
706 p_effective_date => p_effective_date,
707 p_business_group_id => p_business_group_id,
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);
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
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);
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,
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,
752 p_effective_date in date,
753 p_new_value in number) is
754 begin
755 update ben_copy_entity_results
756 set INFORMATION297 = p_new_value,
757 dml_operation = nvl(dml_operation,'UPDATE')
758 where information278 = p_point_cer_id
759 and table_alias ='HRRATE'
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;
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,
775 p_datetrack_mode in varchar2,
776 p_effective_date in date,
777 p_new_value in number) is
778 l_old_hrr_cer_id number;
779 l_new_hrr_cer_id number;
780 begin
781 begin
782 select copy_entity_result_id
783 into l_old_hrr_cer_id
784 from ben_copy_entity_results
785 where information278 = p_point_cer_id
786 and table_alias ='HRRATE'
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;
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;
811 procedure update_hrrate(p_old_hrrate_cer_id in number,
812 p_effective_date in date,
813 p_value in number,
814 p_datetrack_mode in varchar2,
815 p_grd_min_value in number default null,
816 p_grd_mid_value in number default null,
817 p_grd_max_value in number default null,
818 p_new_hrrate_cer_id out nocopy number) is
819 l_eot date := to_date('31/12/4712','dd/mm/RRRR');
820 l_hrr_cer_ovn number;
821 l_cet_id number;
822 l_table_alias varchar2(30);
823 l_table_name varchar2(60);
824 l_table_route_id number;
825 l_pk number;
826 l_esd date;
827 l_eed date;
828 l_bg number;
829 l_grade_cer_id number;
830 l_point_cer_id number;
831 l_payrate_id number;
832 l_abr_cer_id number;
833 l_dml_oper varchar2(30);
834 l_dt_mode varchar2(30);
835 l_new_dml_oper varchar2(30);
836 l_new_dt_mode varchar2(30);
837 l_ins_row varchar2(30);
838 l_del_future varchar2(30);
839 l_upd_curr varchar2(30);
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,
849 l_pk,l_esd,l_eed,l_bg,l_grade_cer_id,l_point_cer_id,
850 l_payrate_id,l_abr_cer_id,l_hrr_ovn
851 from ben_copy_entity_results
852 where copy_entity_result_id = p_old_hrrate_cer_id;
853 if nvl(l_dml_oper,'REUSE') = 'REUSE' then
854 l_new_dml_oper := 'UPDATE';
855 else
856 l_new_dml_oper := l_dml_oper;
857 end if;
858 if l_dt_mode is null then
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';
868 l_del_future := 'N';
869 l_upd_curr := 'Y';
870 l_upd_effdt := 'N';
871 l_hrr_eed := l_eed;
872 else
873 if l_esd = p_effective_date then
874 l_upd_curr := 'Y';
875 l_upd_effdt := 'N';
876 l_ins_row := 'N';
877 l_hrr_eed := l_eot;
878 if l_eed <> l_eot then
879 l_del_future := 'Y';
880 else
881 l_del_future := 'N';
882 end if;
883 else
884 l_hrr_eed := p_effective_date - 1;
885 l_ins_row := 'Y';
886 l_del_future := 'Y';
887 l_upd_curr := 'N';
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,
897 information296 = p_grd_mid_value,
898 information297 = p_value,
899 dml_operation = l_new_dml_oper,
900 datetrack_mode = l_new_dt_mode,
901 information3 = l_hrr_eed
902 where copy_entity_result_id = p_old_hrrate_cer_id;
903 --ggnanagu
904
905 update ben_copy_entity_results
906 set information287 = p_value
907 where copy_entity_result_id = p_old_hrrate_cer_id
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);
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'
926 and (information277 is null or information277 = l_grade_cer_id)
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
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;
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
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
954 (p_effective_date => p_effective_date
955 ,p_copy_entity_txn_id => l_cet_id
956 ,p_result_type_cd => 'DISPLAY'
957 ,p_table_name => l_table_name
958 ,p_table_alias => l_table_alias
959 ,p_table_route_id => l_table_route_id
960 ,p_dml_operation => 'INSERT'
961 ,p_datetrack_mode => 'UPDATE_REPLACE'
962 ,p_information1 => l_pk
963 ,p_information2 => p_effective_date
964 ,p_information3 => l_eot
965 ,p_information4 => l_bg
966 ,p_information277 => l_grade_cer_id
967 ,p_information278 => l_point_cer_id
968 ,p_information293 => l_payrate_id
969 ,p_information294 => p_grd_min_value
970 ,p_information288 => p_grd_min_value
971 ,p_information295 => p_grd_max_value
972 ,p_information289 => p_grd_max_value
973 ,p_information296 => p_grd_mid_value
974 ,p_information290 => p_grd_mid_value
975 ,p_information297 => p_value
976 ,p_information287 => p_value
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;
986 end;
987 end if;
988 end update_hrrate;
989 procedure update_hgrid_data(p_copy_entity_txn_id in number,
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
999 where table_alias ='CPP'
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
1009 update ben_copy_entity_results
1010 set information298 = p_value
1011 where table_alias ='COP'
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);
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,
1025 p_business_group_id in number,
1026 p_hrrate_cer_id in out nocopy number,
1027 p_grade_cer_id in number,
1028 p_grd_value in number,
1029 p_grd_min_value in number,
1030 p_grd_mid_value in number,
1031 p_grd_max_value in number,
1032 p_datetrack_mode in varchar2) is
1033 l_abr_cer_id number;
1034 l_start_date date ;
1035 l_dml_operation varchar2(30);
1036 l_datetrack_mode varchar2(30);
1037 l_old_hrrate_cer_id number;
1038 l_new_hrrate_cer_id number;
1039 l_esd date;
1040 l_eed date;
1041 l_eot date := to_date('31/12/4712','dd/mm/RRRR');
1042 begin
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
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,
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,
1071 p_effective_date => p_rt_effective_date,
1072 p_start_date => l_start_date,
1073 p_business_group_id => p_business_group_id,
1074 p_abr_cer_id => l_abr_cer_id,
1075 p_grade_cer_id => p_grade_cer_id,
1076 p_grd_value => p_grd_value,
1077 p_grd_min_value => p_grd_min_value,
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);
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,
1094 p_new_hrrate_cer_id => l_new_hrrate_cer_id,
1095 p_value => p_grd_value,
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);
1105 elsif p_datetrack_mode = 'UPDATE_REPLACE' and p_gl_effective_date >= p_rt_effective_date then
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,
1115 p_rt_effective_date in date,
1116 p_gl_effective_date in date,
1117 p_business_group_id in number,
1118 p_hrrate_cer_id in out nocopy number,
1119 p_point_cer_id in number,
1120 p_point_value in number,
1121 p_datetrack_mode in varchar2) is
1122 l_abr_cer_id number;
1123 l_start_date date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
1124 l_dml_operation varchar2(30);
1125 l_datetrack_mode varchar2(30);
1126 l_old_hrrate_cer_id number;
1127 l_new_hrrate_cer_id number;
1128 l_esd date;
1129 l_eed date;
1130 l_eot date := to_date('31/12/4712','dd/mm/RRRR');
1131 begin
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,
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,
1159 p_start_date => l_start_date,
1160 p_effective_date => p_rt_effective_date,
1161 p_business_group_id => p_business_group_id,
1162 p_abr_cer_id => l_abr_cer_id,
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);
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,
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);
1187 elsif p_datetrack_mode = 'UPDATE_REPLACE' and p_gl_effective_date >= p_rt_effective_date then
1188 update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
1189 p_point_cer_id => p_point_cer_id,
1190 p_value => p_point_value);
1191 end if;
1192 end if;
1193 end update_point_hrrate;
1194 procedure create_grade_hrrate(p_copy_entity_txn_id in number,
1195 p_effective_date in date,
1196 p_abr_id in number,
1197 p_abr_cer_id in number,
1198 p_pay_rule_id in number,
1199 p_grade_id in number) is
1200 cursor csr_grd_rate is
1201 select EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, BUSINESS_GROUP_ID,
1202 RATE_ID, MAXIMUM, MID_VALUE, MINIMUM,VALUE, OBJECT_VERSION_NUMBER
1203 from pay_grade_rules_f
1204 where grade_rule_id = p_pay_rule_id
1205 and rate_type ='G'
1206 and GRADE_OR_SPINAL_POINT_ID = p_grade_id
1207 order by effective_start_date;
1208 l_hrr_tr_name varchar2(30);
1209 l_hrr_tr_id number;
1210 l_hrrate_exists boolean;
1211 l_hrrate_cer_id number;
1212 l_hrr_cer_ovn number;
1213 l_grd_cer_id number;
1214 l_continue boolean := TRUE;
1215 begin
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,
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
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;
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
1246 begin
1247 -- These mappings have been taken from hrben_to_stage document
1248 -- call to create ben_cer is made here using api.
1249 ben_copy_entity_results_api.create_copy_entity_results
1250 (p_effective_date => p_effective_date
1251 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1252 ,p_result_type_cd => 'DISPLAY'
1253 ,p_table_name => l_hrr_tr_name
1254 ,p_table_alias => 'HRRATE'
1255 ,p_table_route_id => l_hrr_tr_id
1256 ,p_dml_operation => ''
1257 ,p_datetrack_mode => ''
1258 ,p_information1 => p_pay_rule_id
1259 ,p_information2 => rec.effective_start_date
1260 ,p_information3 => rec.effective_end_date
1261 ,p_information4 => rec.business_group_id
1262 ,p_information255 => p_grade_id
1263 ,p_information277 => l_grd_cer_id
1264 ,p_information293 => rec.rate_id
1265 ,p_information294 => rec.minimum
1266 ,p_information288 => rec.minimum
1267 ,p_information295 => rec.maximum
1268 ,p_information289 => rec.maximum
1269 ,p_information296 => rec.mid_value
1270 ,p_information290 => rec.mid_value
1271 ,p_information297 => rec.value
1272 ,p_information287 => rec.value
1273 ,p_information298 => rec.object_version_number
1274 ,p_information299 => p_abr_id
1275 ,p_information300 => p_abr_cer_id
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
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,
1290 p_abr_id in number,
1291 p_abr_cer_id in number,
1292 p_pay_rule_id in number,
1293 p_point_id in number) is
1294 cursor csr_point_rate is
1295 select EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, BUSINESS_GROUP_ID,
1296 RATE_ID,VALUE, OBJECT_VERSION_NUMBER
1297 from pay_grade_rules_f
1298 where grade_rule_id = p_pay_rule_id
1299 and rate_type ='SP'
1300 and GRADE_OR_SPINAL_POINT_ID = p_point_id
1301 order by effective_start_date;
1302 l_hrr_tr_name varchar2(30);
1303 l_hrr_tr_id number;
1304 l_hrrate_exists boolean;
1305 l_hrrate_cer_id number;
1306 l_hrr_cer_ovn number;
1307 l_continue boolean := TRUE;
1308 l_point_cer_id number;
1309 begin
1310 l_hrrate_exists := pqh_gsp_hr_to_stage.is_hrrate_for_abr_exists
1311 (p_copy_entity_txn_id => p_copy_entity_txn_id,
1312 p_abr_id => p_abr_id);
1313 if not l_hrrate_exists then
1314 -- get the table route id and table alias
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
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;
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
1339 begin
1340 -- These mappings have been taken from hrben_to_stage document
1341 -- call to create ben_cer is made here using api.
1342 ben_copy_entity_results_api.create_copy_entity_results
1343 (p_effective_date => p_effective_date
1344 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1345 ,p_result_type_cd => 'DISPLAY'
1346 ,p_table_name => l_hrr_tr_name
1347 ,p_table_alias => 'HRRATE'
1348 ,p_dml_operation => ''
1349 ,p_datetrack_mode => ''
1350 ,p_table_route_id => l_hrr_tr_id
1351 ,p_information1 => p_pay_rule_id
1352 ,p_information2 => rec.effective_start_date
1353 ,p_information3 => rec.effective_end_date
1354 ,p_information4 => rec.business_group_id
1355 ,p_information276 => p_point_id
1356 ,p_information278 => l_point_cer_id
1357 ,p_information293 => rec.rate_id
1358 ,p_information297 => rec.value
1359 ,p_information287 => rec.value
1360 ,p_information298 => rec.object_version_number
1361 ,p_information299 => p_abr_id
1362 ,p_information300 => p_abr_cer_id
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
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,
1377 p_copy_entity_txn_id in number,
1378 p_datetrack_mode in varchar2,
1379 p_grade_cer_id in number default null,
1380 p_point_cer_id in number default null,
1381 p_new_value in number) is
1382 cursor csr_crrate is
1383 select *
1384 from ben_copy_entity_results
1385 where copy_entity_txn_id = p_copy_entity_txn_id
1386 and table_alias = 'CRRATE'
1387 and (information230 is null or information230 = p_grade_cer_id)
1388 and (information169 is null or information169 = p_point_cer_id)
1392 l_dml_operation varchar2(30);
1389 and information160 = p_crset_id
1390 and p_effective_date between information2 and information3;
1391 l_continue boolean := TRUE;
1393 l_upd_curr varchar2(30);
1394 l_del_future varchar2(30);
1395 l_crrate_eed date;
1396 l_upd_effdt varchar2(30);
1397 l_ins_row varchar2(30);
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);
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;
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;
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';
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
1456 and (information230 is null or information230 = p_grade_cer_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;
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
1472 where copy_entity_result_id = crrate_rec.copy_entity_result_id;
1473 --ggnanagu
1474 update ben_copy_entity_results
1475 set INFORMATION287 = p_new_value
1476 where copy_entity_result_id = crrate_rec.copy_entity_result_id
1477 and nvl(information287,0) =0 ;
1478 --ggnanagu
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,
1488 p_point_cer_id => p_point_cer_id,
1489 p_business_group_id => crrate_rec.information4,
1490 p_abr_cer_id => crrate_rec.information161,
1491 p_vpf_esd => p_effective_date,
1492 p_vpf_eed => l_eot,
1493 p_vpf_ovn => crrate_rec.information298,
1494 p_vpf_value => p_new_value,
1495 p_datetrack_mode => 'UPDATE_REPLACE',
1496 p_vpf_cer_id => crrate_rec.information162,
1497 p_vpf_name => crrate_rec.information170,
1498 p_vpf_id => crrate_rec.information278,
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,
1508 p_start_date in date,
1509 p_grade_cer_id in number,
1510 p_point1_cer_id in number,
1511 p_point2_cer_id in number,
1512 p_point3_cer_id in number,
1513 p_point4_cer_id in number,
1514 p_point5_cer_id in number,
1515 p_point1_value in number,
1516 p_point2_value in number,
1517 p_point3_value in number,
1518 p_point4_value in number,
1519 p_point5_value in number,
1520 p_business_group_id in number,
1521 p_effective_date in date,
1522 p_step_range in number,
1523 p_gsr_cer_id out nocopy number) is
1524 l_eot date := to_date('31/12/4712','dd/mm/RRRR');
1525 l_gsr_tr_id number;
1526 l_gsr_tr_name varchar2(30);
1527 l_gsr_cer_ovn number;
1528 l_continue boolean := TRUE;
1529 begin
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;
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);
1545 l_continue := FALSE;
1546 end if;
1547 if l_continue then
1548 begin
1549 -- These mappings have been taken from hrben_to_stage document
1550 -- call to create ben_cer is made here using api.
1551 ben_copy_entity_results_api.create_copy_entity_results
1552 (p_effective_date => p_effective_date
1553 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1554 ,p_result_type_cd => 'DISPLAY'
1555 ,p_table_name => l_gsr_tr_name
1556 ,p_table_alias => 'GSRATE'
1557 ,p_table_route_id => l_gsr_tr_id
1558 ,p_dml_operation => ''
1559 ,p_information2 => p_start_date
1560 ,p_information3 => l_eot
1561 ,p_information4 => p_business_group_id
1562 ,p_INFORMATION160 => p_grade_cer_id
1563 ,p_INFORMATION229 => p_point1_cer_id
1564 ,p_INFORMATION231 => p_point2_cer_id
1565 ,p_INFORMATION174 => p_point3_cer_id
1566 ,p_INFORMATION178 => p_point4_cer_id
1567 ,p_INFORMATION222 => p_point5_cer_id
1568 ,p_INFORMATION287 => p_point1_value
1569 ,p_INFORMATION288 => p_point2_value
1570 ,p_INFORMATION289 => p_point3_value
1571 ,p_INFORMATION290 => p_point4_value
1572 ,p_INFORMATION291 => p_point5_value
1573 ,p_INFORMATION297 => p_point1_value
1574 ,p_INFORMATION298 => p_point2_value
1575 ,p_INFORMATION299 => p_point3_value
1576 ,p_INFORMATION300 => p_point4_value
1577 ,p_INFORMATION301 => p_point5_value
1578 ,p_INFORMATION228 => p_step_range
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;
1590 p_plip_cer_id in number,
1587 procedure create_grr_row(p_copy_entity_txn_id in number,
1588 p_start_date in date,
1589 p_grade_cer_id in number,
1591 p_crset_id in number,
1592 p_point1_cer_id in number,
1593 p_point2_cer_id in number,
1594 p_point3_cer_id in number,
1595 p_point4_cer_id in number,
1596 p_point5_cer_id in number,
1597 p_point1_value in number,
1598 p_point2_value in number,
1599 p_point3_value in number,
1600 p_point4_value in number,
1601 p_point5_value in number,
1602 p_business_group_id in number,
1603 p_effective_date in date,
1604 p_step_range in number,
1605 p_grr_cer_id out nocopy number) is
1606 l_eot date := to_date('31/12/4712','dd/mm/RRRR');
1607 l_grr_tr_id number;
1608 l_grr_tr_name varchar2(30);
1609 l_grr_cer_ovn number;
1610 l_continue boolean := TRUE;
1611 begin
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;
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);
1627 l_continue := FALSE;
1628 end if;
1629 if l_continue then
1630 begin
1631 -- These mappings have been taken from hrben_to_stage document
1632 -- call to create ben_cer is made here using api.
1633 ben_copy_entity_results_api.create_copy_entity_results
1634 (p_effective_date => p_effective_date
1635 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1636 ,p_result_type_cd => 'DISPLAY'
1637 ,p_table_name => l_grr_tr_name
1638 ,p_table_alias => 'GRRATE'
1639 ,p_table_route_id => l_grr_tr_id
1640 ,p_dml_operation => ''
1641 ,p_information2 => p_start_date
1642 ,p_information3 => l_eot
1643 ,p_information4 => p_business_group_id
1644 ,p_INFORMATION160 => p_grade_cer_id
1645 ,p_INFORMATION162 => p_plip_cer_id
1646 ,p_INFORMATION161 => p_crset_id
1647 ,p_INFORMATION229 => p_point1_cer_id
1648 ,p_INFORMATION231 => p_point2_cer_id
1649 ,p_INFORMATION174 => p_point3_cer_id
1650 ,p_INFORMATION178 => p_point4_cer_id
1651 ,p_INFORMATION222 => p_point5_cer_id
1652 ,p_INFORMATION287 => p_point1_value
1653 ,p_INFORMATION288 => p_point2_value
1654 ,p_INFORMATION289 => p_point3_value
1655 ,p_INFORMATION290 => p_point4_value
1656 ,p_INFORMATION291 => p_point5_value
1657 ,p_INFORMATION297 => p_point1_value
1658 ,p_INFORMATION298 => p_point2_value
1659 ,p_INFORMATION299 => p_point3_value
1660 ,p_INFORMATION300 => p_point4_value
1661 ,p_INFORMATION301 => p_point5_value
1662 ,p_INFORMATION228 => p_step_range
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,
1672 p_gsr_cer_id in number,
1673 p_effective_date in date,
1674 p_business_group_id in number,
1675 p_value1 in number,
1676 p_value2 in number,
1677 p_value3 in number,
1678 p_value4 in number,
1679 p_value5 in number,
1680 p_datetrack_mode in varchar2) is
1681 l_grade_cer_id number;
1682 l_point1_cer_id number;
1683 l_point2_cer_id number;
1684 l_point3_cer_id number;
1685 l_point4_cer_id number;
1686 l_point5_cer_id number;
1687 l_point1_value number;
1688 l_point2_value number;
1689 l_point3_value number;
1690 l_point4_value number;
1691 l_point5_value number;
1692 l_step_range number;
1693 l_esd date;
1694 l_eed date;
1695 l_eot date := to_date('31-12-4712','dd-mm-RRRR');
1696 l_gsr_cer_id number;
1697 l_upd_curr varchar2(30);
1698 l_del_future varchar2(30);
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,
1708 information290, information291, information228, information2, information3
1709 into l_grade_cer_id, l_point1_cer_id, l_point2_cer_id, l_point3_cer_id,
1710 l_point4_cer_id, l_point5_cer_id, l_point1_value, l_point2_value, l_point3_value,
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;
1720 l_upd_effdt := 'N';
1721 l_ins_row := 'N';
1722 else
1723 if l_esd = p_effective_date then
1724 l_ins_row := 'N';
1725 l_upd_effdt := 'N';
1726 l_upd_curr := 'Y';
1727 l_crrate_eed := l_eot;
1728 if l_eed = l_eot then
1729 l_del_future := 'N';
1730 else
1731 l_del_future := 'Y';
1732 end if;
1733 else
1734 l_upd_curr := 'N';
1735 l_del_future := 'Y';
1736 l_crrate_eed := l_eed - 1;
1737 l_upd_effdt := 'Y';
1738 l_ins_row := 'Y';
1739 end if;
1740 end if;
1741 if l_upd_curr ='Y' then
1742 -- correct the gsrate row
1743 update ben_copy_entity_results
1744 set information287 = p_value1,
1745 information288 = p_value2,
1746 information289 = p_value3,
1747 information290 = p_value4,
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
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,
1773 p_start_date => p_effective_date,
1774 p_grade_cer_id => l_grade_cer_id,
1775 p_point1_cer_id => l_point1_cer_id,
1776 p_point2_cer_id => l_point2_cer_id,
1777 p_point3_cer_id => l_point3_cer_id,
1778 p_point4_cer_id => l_point4_cer_id,
1779 p_point5_cer_id => l_point5_cer_id,
1780 p_point1_value => p_value1,
1781 p_point2_value => p_value2,
1782 p_point3_value => p_value3,
1783 p_point4_value => p_value4,
1784 p_point5_value => p_value5,
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,
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,
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,
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,
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,
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,
1833 p_effective_date in date,
1834 p_business_group_id in number,
1835 p_value1 in number,
1836 p_value2 in number,
1837 p_value3 in number,
1838 p_value4 in number,
1839 p_value5 in number,
1840 p_datetrack_mode in varchar2) is
1841 l_grade_cer_id number;
1842 l_plip_cer_id number;
1843 l_crset_id number;
1844 l_point1_cer_id number;
1845 l_point2_cer_id number;
1846 l_point3_cer_id number;
1847 l_point4_cer_id number;
1848 l_point5_cer_id number;
1849 l_point1_value number;
1850 l_point2_value number;
1851 l_point3_value number;
1852 l_point4_value number;
1853 l_point5_value number;
1854 l_step_range number;
1855 l_esd date;
1856 l_eed date;
1857 l_eot date := to_date('31-12-4712','dd-mm-RRRR');
1858 l_grr_cer_id number;
1859 l_upd_curr varchar2(30);
1860 l_del_future varchar2(30);
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,
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,
1872 l_point4_cer_id, l_point5_cer_id, l_point1_value, l_point2_value, l_point3_value,
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;
1882 l_upd_effdt := 'N';
1883 l_ins_row := 'N';
1884 else
1885 if l_esd = p_effective_date then
1886 l_ins_row := 'N';
1887 l_upd_effdt := 'N';
1888 l_upd_curr := 'Y';
1889 l_crrate_eed := l_eot;
1890 if l_eed = l_eot then
1891 l_del_future := 'N';
1892 else
1893 l_del_future := 'Y';
1894 end if;
1895 else
1896 l_upd_curr := 'N';
1897 l_del_future := 'Y';
1898 l_crrate_eed := l_eed - 1;
1899 l_upd_effdt := 'Y';
1900 l_ins_row := 'Y';
1901 end if;
1902 end if;
1903 if l_upd_curr ='Y' then
1904 -- correct the grrate row
1905 update ben_copy_entity_results
1906 set information287 = p_value1,
1907 information288 = p_value2,
1908 information289 = p_value3,
1909 information290 = p_value4,
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
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,
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,
1938 p_crset_id => l_crset_id,
1939 p_point1_cer_id => l_point1_cer_id,
1940 p_point2_cer_id => l_point2_cer_id,
1941 p_point3_cer_id => l_point3_cer_id,
1942 p_point4_cer_id => l_point4_cer_id,
1943 p_point5_cer_id => l_point5_cer_id,
1944 p_point1_value => p_value1,
1945 p_point2_value => p_value2,
1946 p_point3_value => p_value3,
1947 p_point4_value => p_value4,
1948 p_point5_value => p_value5,
1949 p_business_group_id => p_business_group_id,
1950 p_effective_date => p_effective_date,
1951 p_step_range => l_step_range,
1952 p_grr_cer_id => l_grr_cer_id);
1953 end if;
1954 -- update the crrate rows
1955 update_crrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1956 p_point_cer_id => l_point1_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,
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,
2001 p_copy_entity_txn_id in number,
2002 p_value in number) is
2003 cursor csr_crr is
2004 select * from ben_copy_entity_results
2005 where copy_entity_txn_id = p_copy_entity_txn_id
2006 and information160 is null
2007 and table_alias = 'CRRATE'
2008 and information169 = p_point_cer_id;
2009 l_dml_operation varchar2(30);
2010 begin
2011 for crr_rec in csr_crr loop
2012 if crr_rec.information278 is null then
2013 if nvl(crr_rec.dml_operation,'REUSE') = 'REUSE' then
2014 l_dml_operation := 'INSERT';
2015 else
2016 l_dml_operation := crr_rec.dml_operation;
2017 end if;
2018 else
2019 if nvl(crr_rec.dml_operation,'REUSE') = 'REUSE' then
2020 l_dml_operation := 'UPDATE';
2021 else
2022 l_dml_operation := crr_rec.dml_operation;
2023 end if;
2024 end if;
2025 update ben_copy_entity_results
2026 set information293 = p_value,
2027 information160 = p_crset_id,
2028 dml_operation = l_dml_operation
2029 where copy_entity_txn_id = p_copy_entity_txn_id
2030 and copy_entity_result_id = crr_rec.copy_entity_result_id
2031 and information160 is null
2032 and table_alias = 'CRRATE'
2033 and information169 = p_point_cer_id;
2034 --ggnanagu
2035 update ben_copy_entity_results
2036 set INFORMATION287 = p_value
2037 where copy_entity_result_id = crr_rec.copy_entity_result_id
2038 and nvl(information287,0) =0 ;
2039 --ggnanagu
2040
2041 end loop;
2042 end sync_crrate;
2043 procedure sync_grrate(p_crset_id in number,
2044 p_copy_entity_txn_id in number) is
2045 cursor csr_grr is
2046 select * from ben_copy_entity_results
2047 where copy_entity_txn_id = p_copy_entity_txn_id
2048 and information161 is null
2049 and table_alias = 'GRRATE';
2050 begin
2051 -- this routine will be called from create grrate page
2052 -- we have to take values from grrate rows and update crrate rows so that
2053 -- next time matrix build can take care of it
2054 for grr_rec in csr_grr loop
2055 if grr_rec.information229 is not null then
2056 sync_crrate(p_crset_id => p_crset_id,
2057 p_point_cer_id => grr_rec.information229,
2058 p_copy_entity_txn_id => p_copy_entity_txn_id,
2059 p_value => grr_rec.information287);
2060 end if;
2061 if grr_rec.information231 is not null then
2062 sync_crrate(p_crset_id => p_crset_id,
2063 p_point_cer_id => grr_rec.information231,
2064 p_copy_entity_txn_id => p_copy_entity_txn_id,
2065 p_value => grr_rec.information288);
2066 end if;
2067 if grr_rec.information174 is not null then
2068 sync_crrate(p_crset_id => p_crset_id,
2069 p_point_cer_id => grr_rec.information174,
2070 p_copy_entity_txn_id => p_copy_entity_txn_id,
2071 p_value => grr_rec.information289);
2072 end if;
2073 if grr_rec.information178 is not null then
2074 sync_crrate(p_crset_id => p_crset_id,
2075 p_point_cer_id => grr_rec.information178,
2076 p_copy_entity_txn_id => p_copy_entity_txn_id,
2077 p_value => grr_rec.information290);
2078 end if;
2079 if grr_rec.information222 is not null then
2080 sync_crrate(p_crset_id => p_crset_id,
2081 p_point_cer_id => grr_rec.information222,
2082 p_copy_entity_txn_id => p_copy_entity_txn_id,
2083 p_value => grr_rec.information291);
2084 end if;
2085 end loop;
2086 end sync_grrate;
2087
2088 procedure populate_old_values(p_copy_entity_txn_id in number)
2089 is
2090 l_status varchar2(2);
2091
2092 Cursor csr_grades
2093 Is
2094 select copy_entity_result_id
2095 from ben_copy_entity_results
2096 where table_alias = 'PLN'
2097 and copy_entity_txn_id = p_copy_entity_txn_id;
2098
2099
2100 Cursor csr_grade_std_rates(p_grade_cer_id in number)
2101 IS
2102 select copy_entity_result_id,information1,information2,information3,information294,information295,information296,information297,dml_operation
2103 from ben_copy_entity_results
2104 where table_alias = 'HRRATE'
2105 and information277= p_grade_cer_id
2106 and copy_entity_txn_id = p_copy_entity_txn_id;
2107
2108 Cursor csr_grade_cri_rates(p_grade_cer_id in number)
2109 IS
2110 select copy_entity_result_id,information1,information2,information3,information293,dml_operation
2111 from ben_copy_entity_results
2112 where table_alias = 'CRRATE'
2113 and information230= p_grade_cer_id
2114 and copy_entity_txn_id = p_copy_entity_txn_id;
2115
2116 Cursor csr_grd_db_values(p_grade_rule_id in number,p_effective_start_date in date)
2117 Is
2118 select value,minimum,maximum,mid_value
2119 from pay_grade_rules_f
2120 where grade_rule_id = p_grade_rule_id
2121 and effective_start_date = p_effective_start_date;
2122
2123 Cursor csr_Points
2124 Is
2125 select copy_entity_result_id
2126 from ben_copy_entity_results
2127 where table_alias = 'OPT'
2128 and copy_entity_txn_id = p_copy_entity_txn_id;
2129
2130
2131 Cursor csr_point_std_rates(p_point_cer_id in number)
2132 IS
2133 select copy_entity_result_id,information1,information2,information3,information297,dml_operation
2134 from ben_copy_entity_results
2135 where table_alias = 'HRRATE'
2136 and information278= p_point_cer_id
2137 and copy_entity_txn_id = p_copy_entity_txn_id;
2138
2139 Cursor csr_point_cri_rates(p_point_cer_id in number)
2140 IS
2141 select copy_entity_result_id,information1,information2,information3,information293,dml_operation
2142 from ben_copy_entity_results
2143 where table_alias = 'CRRATE'
2144 and information169= p_point_cer_id
2145 and copy_entity_txn_id = p_copy_entity_txn_id;
2146
2147 Cursor csr_pnt_db_values(p_grade_rule_id in number,p_effective_start_date in date)
2148 Is
2149 select value
2150 from pay_grade_rules_f
2151 where grade_rule_id = p_grade_rule_id
2152 and effective_start_date = p_effective_start_date;
2153
2154 Cursor csr_gsp_task_list is
2155 select copy_entity_result_id
2156 from ben_copy_entity_results
2157 where information109 is null --nvl(information109,0) = 0
2158 and table_alias = 'PQH_GSP_TASK_LIST'
2159 and copy_entity_txn_id = p_copy_entity_txn_id
2160 and rownum < 2 ;
2161
2162 l_value number;
2163 l_grd_min_value number;
2164 l_grd_max_value number;
2168
2165 l_grd_mid_value number;
2166
2167 begin
2169 for rec_gsp_task_list in csr_gsp_task_list
2170 loop
2171 update ben_copy_entity_results
2172 set information109 = 'Y'
2173 where copy_entity_result_id = rec_gsp_task_list.copy_entity_result_id;
2174
2175 for grades_rec in csr_grades loop
2176
2177 for grade_std_rates_rec in csr_grade_std_rates(grades_rec.copy_entity_result_id) loop
2178 if grade_std_rates_rec.dml_operation = 'INSERT' then
2179 update ben_copy_entity_results
2180 set information287 = information297
2181 ,information288 = information294
2182 ,information289 = information295
2183 ,information290 = information296
2184 where copy_entity_result_id = grade_std_rates_rec.copy_entity_result_id;
2185 else
2186 Open csr_grd_db_values(grade_std_rates_rec.information1,grade_std_rates_rec.information2);
2187 fetch csr_grd_db_values into l_value,l_grd_min_value,l_grd_max_value,l_grd_mid_value;
2188 close csr_grd_db_values;
2189 update ben_copy_entity_results
2190 set information287 = l_value
2191 ,information288 = l_grd_min_value
2192 ,information289 = l_grd_max_value
2193 ,information290 = l_grd_mid_value
2194 where copy_entity_result_id = grade_std_rates_rec.copy_entity_result_id;
2195 end if;
2196 end loop; -- csr_grade_std_rates
2197
2198 for grade_cri_rates_rec in csr_grade_cri_rates(grades_rec.copy_entity_result_id) loop
2199 update ben_copy_entity_results
2200 set information287 = information293
2201 where copy_entity_result_id = grade_cri_rates_rec.copy_entity_result_id;
2202 end loop; -- csr_grade_cri_rates
2203
2204 end loop; --csr_grades
2205
2206 for pnt in csr_points loop
2207
2208 for pnt_rate in csr_point_std_rates(pnt.copy_entity_result_id) loop
2209 if pnt_rate.dml_operation = 'INSERT' then
2210 update ben_copy_entity_results
2211 set information287 = information297
2212 where copy_entity_result_id = pnt_rate.copy_entity_result_id;
2213 else
2214 Open csr_pnt_db_values(pnt_rate.information1,pnt_rate.information2);
2215 fetch csr_pnt_db_values into l_value;
2216 close csr_pnt_db_values;
2217 update ben_copy_entity_results
2218 set information287 = l_value
2219 where copy_entity_result_id = pnt_rate.copy_entity_result_id;
2220 end if;
2221 end loop; -- csr_point_std_rates
2222
2223 for pnt_cri_rate in csr_point_cri_rates(pnt.copy_entity_result_id) loop
2224 update ben_copy_entity_results
2225 set information287 = information293
2226 where copy_entity_result_id = pnt_cri_rate.copy_entity_result_id;
2227 end loop; -- csr_point_cri_rates
2228
2229 end loop; --csr_points
2230 end loop ; -- csr_gsp_task_list
2231
2232 end populate_old_values;
2233
2234
2235 end pqh_gsp_rates;