[Home] [Help]
PACKAGE BODY: APPS.PQH_GSP_PRGRULES
Source
1 Package Body Pqh_Gsp_PrgRules As
2 /* $Header: pqgspelg.pkb 120.15.12000000.2 2007/06/11 09:27:10 sidsaxen noship $ */
3
4 type TabCerType is table of number ;
5
6 function create_bcer_rec (
7 p_bcer_rec ben_copy_entity_results%rowtype
8 )
9 return number ;
10
11
12
13 procedure update_child(p_child_cer_id in number,p_master_cer_id in number)
14 is
15 begin
16 update ben_copy_entity_results
17 set gs_parent_entity_result_id = p_master_cer_id
18 ,parent_entity_result_id = p_master_cer_id
19 where copy_entity_result_id = p_child_cer_id;
20 end update_child;
21
22
23 --<-------- procedure upd_info1 -------->
24 -- purpose -
25 -- accept -
26 -- do - create a forall version of upd
27 -- return -
28 --<------------------------- ----------->
29 procedure upd_info1(p_cerTab in TabCerType)
30 is
31 begin
32 -- nullify info1 of duplicate records.
33 forall i in 1..p_cerTab.COUNT
34 update ben_copy_entity_results
35 set information101 = information1
36 ,information1 = null
37 ,information263 = null
38 where copy_entity_result_id = p_cerTab(i);
39 end upd_info1;
40
41 procedure upd_info1(p_cer_id in number)
42 is
43 begin
44 -- nullify info1 of duplicate records.
45 update ben_copy_entity_results
46 set information101 = information1
47 ,information1 = null
48 ,information263 = null
49 where copy_entity_result_id = p_cer_id;
50
51 end upd_info1;
52
53
54
55 --<-------- procedure nullify_elp_rec -------->
56 -- purpose -
57 -- accept - cer_id
58 -- do - nullifys all info1 columns of ELP and its child records
59 -- return -
60 --<------------------------------------------------>
61 procedure nullify_elp_rec (
62 p_copy_entity_result_id number
63 ,p_copy_entity_txn_id number
64 )
65 is
66 -- get the elp record
67 cursor cur_elp is
68 select copy_entity_result_id
69 from ben_copy_entity_results
70 where copy_entity_result_id = p_copy_entity_result_id;
71
72 -- get the elp child records to be info1 nulled
73 cursor cur_elp_child(p_parent_cer_id number) is
74 select copy_entity_result_id
75 from ben_copy_entity_results
76 where parent_entity_result_id = p_parent_cer_id
77 and copy_entity_txn_id = p_copy_entity_txn_id;
78
79 -- get one more level down for EAP-AGF,ECP-CLA,ECL-CLF
80 -- ,EHW-HWF,ELS-LSF,EPF-PFF
81 cursor cur_drv_fct (p_parent_cer_id number) is
82 select copy_entity_result_id
83 from ben_copy_entity_results
84 where parent_entity_result_id = p_parent_cer_id
85 and copy_entity_txn_id = p_copy_entity_txn_id;
86
87 begin
88
89 for rec_elp in cur_elp
90 loop
91 upd_info1(rec_elp.copy_entity_result_id);
92 for rec_elp_child in cur_elp_child(rec_elp.copy_entity_result_id)
93 loop
94 upd_info1(rec_elp_child.copy_entity_result_id);
95 for rec_drv_fct in cur_drv_fct(rec_elp_child.copy_entity_result_id )
96 loop
97 upd_info1(rec_drv_fct.copy_entity_result_id);
98 end loop ;
99 end loop;
100 end loop;
101
102 end ;
103
104
105 --<-------- procedure prepare_elp_recs4pdw -------->
106 -- purpose - To prepare ELP record for pdw
107 -- accept - cet_id
108 -- do - nullifys all info1 columns of ELP and its child records
109 -- return -
110 --<------------------------------------------------>
111 procedure prepare_elp_recs4pdw (
112 p_copy_entity_txn_id number
113 ,p_business_group_id number
114 )
115 is
116 -- Keep only one record of ELP for one information1
117 -- Nullify the other's information1 and copy it to inforamtion101
118 -- Also do the same for all child records of this elp
119
120 -- get all elp records
121 cursor cur_elp is
122 select information1, copy_entity_result_id
123 from ben_copy_entity_results
124 where table_alias = 'ELP'
125 and copy_entity_txn_id = p_copy_entity_txn_id
126 and information1 is not null
127 order by information1 ;
128
129
130 -- get the elp child records to be info1 nulled
131 cursor cur_elp_child(p_parent_cer_id number) is
132 select *
133 from ben_copy_entity_results
134 where parent_entity_result_id = p_parent_cer_id
135 and copy_entity_txn_id = p_copy_entity_txn_id;
136
137 -- get one more level down for EAP-AGF,ECP-CLA,ECL-CLF
138 -- ,EHW-HWF,ELS-LSF,EPF-PFF
139 cursor cur_drv_fct (p_parent_cer_id number) is
140 select *
141 from ben_copy_entity_results
142 where parent_entity_result_id = p_parent_cer_id
143 and copy_entity_txn_id = p_copy_entity_txn_id;
144
145 prev_info1 number ;
146
147 begin
148
149 prev_info1 := -1 ;
150
151 -- for all "non null info1" elp records
152 for rec_elp in cur_elp
153 loop
154 -- update all but one
155 if (rec_elp.information1 = prev_info1)
156 then
157 upd_info1(rec_elp.copy_entity_result_id);
158 for rec_elp_child in cur_elp_child(rec_elp.copy_entity_result_id)
159 loop
160 upd_info1(rec_elp_child.copy_entity_result_id);
161 for rec_drv_fct in cur_drv_fct(rec_elp_child.copy_entity_result_id )
162 loop
163 upd_info1(rec_drv_fct.copy_entity_result_id);
164 end loop ;
165 end loop;
166 end if;
167 prev_info1 := rec_elp.information1 ;
168 end loop;
169 end prepare_elp_recs4pdw ;
170 procedure create_dup_elp_tree (
171 p_copy_entity_txn_id number
172 ,p_business_group_id number
173 ,p_eligy_prfl_id number
174 ,p_gs_per_id number
175 ,p_per_id number
176 ,p_gm_ser_id number
177 )
178 is
179 -- This function accepts eligy_prfl_id and copy_entity_txn_id
180 -- creates duplicate entry for this ELP (all date track rows)
181 -- and all its child records(EGN etc)
182
183 l_elp_cerid number;
184 l_child_cerid number;
185 l_gchild_cerid number;
186
187 -- get the elp record to be duplicated
188 cursor cur_elp is
189 select *
190 from ben_copy_entity_results
191 where table_alias = 'ELP'
192 and copy_entity_txn_id = p_copy_entity_txn_id
193 and information1 = p_eligy_prfl_id;
194
195 -- get the elp child records to be duplicated
196 cursor cur_elp_child(p_parent_cer_id number) is
197 select *
198 from ben_copy_entity_results
199 where parent_entity_result_id = p_parent_cer_id
200 and copy_entity_txn_id = p_copy_entity_txn_id;
201
202 -- get one more level down for EAP-AGF,ECP-CLA,ECL-CLF
203 -- ,EHW-HWF,ELS-LSF,EPF-PFF
204 cursor cur_drv_fct (p_parent_cer_id number) is
205 select *
206 from ben_copy_entity_results
207 where parent_entity_result_id = p_parent_cer_id
208 and copy_entity_txn_id = p_copy_entity_txn_id;
209
210 begin
211 hr_utility.set_location('Inside create_dup_elp_tree', 10);
212 hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id, 20);
213 hr_utility.set_location('p_business_group_id'||p_business_group_id, 30);
214 hr_utility.set_location('p_eligy_prfl_id'||p_eligy_prfl_id, 40);
215 for rec_elp in cur_elp
216 loop
217 -- create duplicate ELP record
218 l_elp_cerid := create_bcer_rec(rec_elp);
219 upd_info1(l_elp_cerid);
220
221 update ben_copy_entity_results
222 set gs_parent_entity_result_id = p_gs_per_id
223 ,parent_entity_result_id = p_per_id
224 ,Gs_Mirror_Src_Entity_Result_id = p_gm_ser_id
225 where copy_entity_result_id = l_elp_cerid;
226
227 for rec_elp_child in cur_elp_child(rec_elp.copy_entity_result_id)
228 loop
229 -- create duplicate child record
230 l_child_cerid:= create_bcer_rec(rec_elp_child);
231 upd_info1(l_child_cerid);
232 update_child(l_child_cerid,l_elp_cerid);
233
234 for rec_drv_fct in cur_drv_fct(rec_elp_child.copy_entity_result_id )
235 loop
236 l_gchild_cerid:= create_bcer_rec(rec_drv_fct);
237 upd_info1(l_gchild_cerid);
238 update_child(l_gchild_cerid,l_child_cerid);
239 end loop ;
240 end loop;
241 end loop ;
242 hr_utility.set_location('Leaving create_dup_elp_tree', 10);
243 end create_dup_elp_tree ;
244
245 procedure purge_dup_elp_tree (
246 p_copy_entity_txn_id number
247 ,p_eligy_prfl_id number
248 ,p_copy_entity_result_id number
249 ) is
250 cursor cur_elp is
251 select copy_entity_result_id
252 from ben_copy_entity_results
253 where copy_entity_result_id = p_copy_entity_result_id ;
254
255 -- get the elp child records to be deleted
256 cursor cur_elp_child(p_parent_cer_id number) is
257 select copy_entity_result_id
258 from ben_copy_entity_results
259 where gs_parent_entity_result_id = p_parent_cer_id
260 and copy_entity_txn_id = p_copy_entity_txn_id
261 and information1 is null;
262
263 -- get one more level down to delete EAP-AGF,ECP-CLA,ECL-CLF
264 -- ,EHW-HWF,ELS-LSF,EPF-PFF
265 cursor cur_drv_fct (p_parent_cer_id number) is
266 select copy_entity_result_id
267 from ben_copy_entity_results
268 where parent_entity_result_id = p_parent_cer_id
269 and copy_entity_txn_id = p_copy_entity_txn_id
270 and information1 is null;
271
272 tab_cer TabCerType := TabCerType();
273
274 begin
275 for rec_elp in cur_elp
276 loop
277 -- duplicate ELP record
278 tab_cer.extend;
279 tab_cer(tab_cer.count) := rec_elp.copy_entity_result_id;
280 for rec_elp_child in cur_elp_child(rec_elp.copy_entity_result_id)
281 loop
282 -- duplicate child record
283 tab_cer.extend;
284 tab_cer(tab_cer.count) := rec_elp_child.copy_entity_result_id;
285 for rec_drv_fct in cur_drv_fct(rec_elp_child.copy_entity_result_id )
286 loop
287 tab_cer.extend;
288 tab_cer(tab_cer.count) := rec_drv_fct.copy_entity_result_id;
289 end loop ;
290 end loop;
291 end loop ;
292
293 forall i in 1..tab_cer.COUNT
294 delete from ben_copy_entity_results
295 where copy_entity_result_id = tab_cer(i);
296
297 end purge_dup_elp_tree;
298
299 --<-------- procedure sync_elp_records -------->
300 -- purpose -
301 -- accept -
302 -- do -
303 -- return -
304 --<-------------------------------------------->
305 procedure sync_elp_records (
306 p_copy_entity_txn_id number
307 ,p_business_group_id number
308 ,p_eligy_prfl_id number
309 ) is
310 cursor cur_dup_elp is
311 select *
312 from ben_copy_entity_results
313 where table_alias = 'ELP'
314 and copy_entity_txn_id = p_copy_entity_txn_id
315 and information1 is null
316 and information101 = p_eligy_prfl_id;
317
318 cursor cur_org_elp is
319 select 'Y'
320 from ben_copy_entity_results
321 where table_alias = 'ELP'
322 and copy_entity_txn_id = p_copy_entity_txn_id
323 and information1 = p_eligy_prfl_id;
324
325 l_gs_per_id number;
326 l_per_id number;
327 l_cer_id number;
328 l_org_elp_exist varchar2(1);
329 l_gm_ser_id number;
330
331 begin
332 -- 1. for updated elp get all the other dup elp's
333 -- 2. save the GS_PARENT_ENTITY_RESULT_ID PARENT_ENTITY_RESULT_ID of this
334 -- 3. delete the whole dup hierarchy using cer_id of this dup elp
335 -- 4. recreate the whole hierarchy
336 -- 5. update the ELP record with variables saved in step 2
337 hr_utility.set_location('hm Inside sync_elp_rec',123) ;
338
339 open cur_org_elp;
340 fetch cur_org_elp into l_org_elp_exist ;
341 close cur_org_elp ;
342
343 if (l_org_elp_exist = 'Y')
344 then
345
346 for rec_dup_elp in cur_dup_elp
347 loop
348 l_gs_per_id := rec_dup_elp.gs_parent_entity_result_id ;
349 l_per_id := rec_dup_elp.parent_entity_result_id ;
350 l_gm_ser_id := rec_dup_elp.Gs_Mirror_Src_Entity_Result_id;
351
352 hr_utility.set_location('hm Inside for loop',123) ;
353 hr_utility.set_location('hm cerid-'||rec_dup_elp.copy_entity_result_id,123) ;
354
355 purge_dup_elp_tree
356 (p_copy_entity_txn_id => p_copy_entity_txn_id
357 ,p_eligy_prfl_id => rec_dup_elp.information101
358 ,p_copy_entity_result_id => rec_dup_elp.copy_entity_result_id
359 );
360
361 end loop;
362
363 create_dup_elp_tree
364 (p_copy_entity_txn_id => p_copy_entity_txn_id
365 ,p_business_group_id => p_business_group_id
366 ,p_eligy_prfl_id => p_eligy_prfl_id
367 ,p_gs_per_id => l_gs_per_id
368 ,p_per_id => l_per_id
369 ,p_gm_ser_id => l_gm_ser_id
370 );
371
372 end if;
373 hr_utility.set_location('leaving sync_elp_rec',123) ;
374 end sync_elp_records;
375
376 --<-------- function create_duplicate_elp_tree -------->
377 -- purpose - To create a duplicate hierarchy of an existing ELPRO in staging area
378 -- accept - a elig pro id
379 -- do - will create a duplicate hierarchy for this elpro
380 -- it will copy paste this elpro and its child and grand children
381 -- return - return cerid of the newly created duplicate elpro record
382 --<---------------------------------------------------->
383
384 function create_duplicate_elp_tree (
385 p_copy_entity_txn_id number
386 ,p_business_group_id number
387 ,p_eligy_prfl_id number
388 )
389 return number
390 is
391
392 -- This function accepts eligy_prfl_id and copy_entity_txn_id
393 -- creates duplicate entry for this ELP and all its child records(EGN etc)
394 -- returns copy_entity_result_id of the duplicate ELP record created
395
396 l_elp_cerid number;
397 l_child_cerid number;
398 l_gchild_cerid number;
399
400 -- get the elp record to be duplicated
401 cursor cur_elp is
402 select *
403 from ben_copy_entity_results
404 where table_alias = 'ELP'
405 and copy_entity_txn_id = p_copy_entity_txn_id
406 and information1 = p_eligy_prfl_id;
407
408 -- get the elp child records to be duplicated
409 cursor cur_elp_child(p_parent_cer_id number) is
410 select *
411 from ben_copy_entity_results
412 where parent_entity_result_id = p_parent_cer_id
413 and copy_entity_txn_id = p_copy_entity_txn_id;
414
415 -- get one more level down for EAP-AGF,ECP-CLA,ECL-CLF
416 -- ,EHW-HWF,ELS-LSF,EPF-PFF
417 cursor cur_drv_fct (p_parent_cer_id number) is
418 select *
419 from ben_copy_entity_results
420 where parent_entity_result_id = p_parent_cer_id
421 and copy_entity_txn_id = p_copy_entity_txn_id;
422
423 begin
424 hr_utility.set_location('Inside duplicate_elp_tree', 10);
425 hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id, 20);
426 hr_utility.set_location('p_business_group_id'||p_business_group_id, 30);
427 hr_utility.set_location('p_eligy_prfl_id'||p_eligy_prfl_id, 40);
428 for rec_elp in cur_elp
429 loop
430 -- create duplicate ELP record
431 l_elp_cerid := create_bcer_rec(rec_elp);
432 upd_info1(l_elp_cerid);
433 -- update_child(rec_elp.copy_entity_result_id
434 -- ,nvl(rec_elp.GS_PARENT_ENTITY_RESULT_ID,
435 -- rec_elp.PARENT_ENTITY_RESULT_IDg));
436 for rec_elp_child in cur_elp_child(rec_elp.copy_entity_result_id)
437 loop
438 -- create duplicate child record
439 l_child_cerid:= create_bcer_rec(rec_elp_child);
440 upd_info1(l_child_cerid);
441 update_child(l_child_cerid,l_elp_cerid);
442
443 for rec_drv_fct in cur_drv_fct(rec_elp_child.copy_entity_result_id )
444 loop
445 l_gchild_cerid:= create_bcer_rec(rec_drv_fct);
446 upd_info1(l_gchild_cerid);
447 update_child(l_gchild_cerid,l_child_cerid);
448 end loop ;
449 end loop;
450 end loop ;
451
452 hr_utility.set_location('Leaving duplicate_elp_tree', 10);
453 return l_elp_cerid;
454
455 end create_duplicate_elp_tree ;
456
457 --<-------- procedure purge_elp_tree -------->
458 -- purpose - To purge all duplicate records in the ELP tree created for pdw
459 -- accept - cet_id
460 -- do
461 -- if p_eligy_prfl_id and p_copy_entity_results_id is null then
462 -- this will delete all duplicate elp hierarchy records of all ELPs
463
464 -- if elpro is not null and cer id is null then
465 -- it will delete all duplicate elp hierarchy records of this ELP
466
467 -- if cer id is not null then
468 -- it will delete the hierarchy records of this single duplicate ELP
469 --<------------------------------------------>
470 procedure purge_duplicate_elp_tree (
471 p_copy_entity_txn_id number
472 ,p_eligy_prfl_id number default null
473 ,p_copy_entity_result_id number default null
474 ) is
475 -- There will be single ELP and its child records with info1 as not null
476 -- Duplicate ELP and its child will have info1 as null and not null info101
477
478 --cursor cur_elp is
479 --select copy_entity_result_id
480 -- from ben_copy_entity_results
481 -- where copy_entity_result_id = p_copy_entity_result_id ;
482
483 cursor cur_elp is
484 select copy_entity_result_id
485 from ben_copy_entity_results
486 where table_alias = 'ELP'
487 and copy_entity_txn_id = p_copy_entity_txn_id
488 and information1 is null
489 and nvl(p_eligy_prfl_id,information101) = information101
490 and nvl(p_copy_entity_result_id,copy_entity_result_id) = copy_entity_result_id ;
491
492 -- get the elp child records to be deleted
493 cursor cur_elp_child(p_parent_cer_id number) is
494 select copy_entity_result_id
495 from ben_copy_entity_results
496 where gs_parent_entity_result_id = p_parent_cer_id
497 and copy_entity_txn_id = p_copy_entity_txn_id
498 and information1 is null;
499
500 -- get one more level down to delete EAP-AGF,ECP-CLA,ECL-CLF
501 -- ,EHW-HWF,ELS-LSF,EPF-PFF
502 cursor cur_drv_fct (p_parent_cer_id number) is
503 select copy_entity_result_id
504 from ben_copy_entity_results
505 where parent_entity_result_id = p_parent_cer_id
506 and copy_entity_txn_id = p_copy_entity_txn_id
507 and information1 is null;
508
509 tab_cer TabCerType := TabCerType();
510
511 begin
512 for rec_elp in cur_elp
513 loop
514 -- duplicate ELP record
515 tab_cer.extend;
516 tab_cer(tab_cer.count) := rec_elp.copy_entity_result_id;
517 for rec_elp_child in cur_elp_child(rec_elp.copy_entity_result_id)
518 loop
519 -- duplicate child record
520 tab_cer.extend;
521 tab_cer(tab_cer.count) := rec_elp_child.copy_entity_result_id;
522 for rec_drv_fct in cur_drv_fct(rec_elp_child.copy_entity_result_id )
523 loop
524 tab_cer.extend;
525 tab_cer(tab_cer.count) := rec_drv_fct.copy_entity_result_id;
526 end loop ;
527 end loop;
528 end loop ;
529
530 forall i in 1..tab_cer.COUNT
531 delete from ben_copy_entity_results
532 where copy_entity_result_id = tab_cer(i);
533
534 end purge_duplicate_elp_tree;
535
536 --<-------- function create_bcer_rec -------->
537 -- purpose - To insert ELP and its child records in bcer
538 -- accept - bcer%rowtype
539 -- do - will insert this record in bcer
540 -- return - return cerid of the newly created bcer record
541 --<------------------------------------------>
542
543 function create_bcer_rec (
544 p_bcer_rec ben_copy_entity_results%rowtype
545 )
546 return number
547 is
548 l_grr_cer_id number;
549 l_grr_cer_ovn number;
550 l_effective_date date; -- ask GANESH
551 bcer_rec ben_copy_entity_results%rowtype ;
552 begin
553 hr_utility.set_location('Entering creating bcer rec', 10);
554 bcer_rec := p_bcer_rec ;
555 ben_copy_entity_results_api.create_copy_entity_results(
556 p_effective_date => l_effective_date
557 ,p_copy_entity_txn_id => bcer_rec.copy_entity_txn_id
558 ,p_result_type_cd => bcer_rec.result_type_cd
559 ,p_src_copy_entity_result_id => bcer_rec.src_copy_entity_result_id
560 ,p_number_of_copies => bcer_rec.number_of_copies
561 ,p_mirror_entity_result_id => bcer_rec.mirror_entity_result_id
562 ,p_mirror_src_entity_result_id => bcer_rec.mirror_src_entity_result_id
563 ,p_parent_entity_result_id => bcer_rec.parent_entity_result_id
564 -- ,p_pd_mr_src_entity_result_id => bcer_rec.pd_mr_src_entity_result_id
565 ,p_pd_parent_entity_result_id => bcer_rec.pd_parent_entity_result_id
566 -- ,p_gs_mr_src_entity_result_id => bcer_rec.gs_mr_src_entity_result_id
567 ,p_gs_parent_entity_result_id => bcer_rec.gs_parent_entity_result_id
568 ,p_table_name => bcer_rec.table_name
569 ,p_table_alias => bcer_rec.table_alias
570 ,p_table_route_id => bcer_rec.table_route_id
571 ,p_status => bcer_rec.status
572 ,p_dml_operation => bcer_rec.dml_operation
573 ,p_information_category => bcer_rec.information_category
574 ,p_information1 => bcer_rec.information1
575 ,p_information2 => bcer_rec.information2
576 ,p_information3 => bcer_rec.information3
577 ,p_information4 => bcer_rec.information4
578 ,p_information5 => bcer_rec.information5
579 ,p_information6 => bcer_rec.information6
580 ,p_information7 => bcer_rec.information7
581 ,p_information8 => bcer_rec.information8
582 ,p_information9 => bcer_rec.information9
583 ,p_information10 => bcer_rec.information10
584 ,p_information11 => bcer_rec.information11
585 ,p_information12 => bcer_rec.information12
586 ,p_information13 => bcer_rec.information13
587 ,p_information14 => bcer_rec.information14
588 ,p_information15 => bcer_rec.information15
589 ,p_information16 => bcer_rec.information16
590 ,p_information17 => bcer_rec.information17
591 ,p_information18 => bcer_rec.information18
592 ,p_information19 => bcer_rec.information19
593 ,p_information20 => bcer_rec.information20
594 ,p_information21 => bcer_rec.information21
595 ,p_information22 => bcer_rec.information22
596 ,p_information23 => bcer_rec.information23
597 ,p_information24 => bcer_rec.information24
598 ,p_information25 => bcer_rec.information25
599 ,p_information26 => bcer_rec.information26
600 ,p_information27 => bcer_rec.information27
601 ,p_information28 => bcer_rec.information28
602 ,p_information29 => bcer_rec.information29
603 ,p_information30 => bcer_rec.information30
604 ,p_information31 => bcer_rec.information31
605 ,p_information32 => bcer_rec.information32
606 ,p_information33 => bcer_rec.information33
607 ,p_information34 => bcer_rec.information34
608 ,p_information35 => bcer_rec.information35
609 ,p_information36 => bcer_rec.information36
610 ,p_information37 => bcer_rec.information37
611 ,p_information38 => bcer_rec.information38
612 ,p_information39 => bcer_rec.information39
613 ,p_information40 => bcer_rec.information40
614 ,p_information41 => bcer_rec.information41
615 ,p_information42 => bcer_rec.information42
616 ,p_information43 => bcer_rec.information43
617 ,p_information44 => bcer_rec.information44
618 ,p_information45 => bcer_rec.information45
619 ,p_information46 => bcer_rec.information46
620 ,p_information47 => bcer_rec.information47
621 ,p_information48 => bcer_rec.information48
622 ,p_information49 => bcer_rec.information49
623 ,p_information50 => bcer_rec.information50
624 ,p_information51 => bcer_rec.information51
625 ,p_information52 => bcer_rec.information52
626 ,p_information53 => bcer_rec.information53
627 ,p_information54 => bcer_rec.information54
628 ,p_information55 => bcer_rec.information55
629 ,p_information56 => bcer_rec.information56
630 ,p_information57 => bcer_rec.information57
631 ,p_information58 => bcer_rec.information58
632 ,p_information59 => bcer_rec.information59
633 ,p_information60 => bcer_rec.information60
634 ,p_information61 => bcer_rec.information61
635 ,p_information62 => bcer_rec.information62
636 ,p_information63 => bcer_rec.information63
637 ,p_information64 => bcer_rec.information64
638 ,p_information65 => bcer_rec.information65
639 ,p_information66 => bcer_rec.information66
640 ,p_information67 => bcer_rec.information67
641 ,p_information68 => bcer_rec.information68
642 ,p_information69 => bcer_rec.information69
643 ,p_information70 => bcer_rec.information70
644 ,p_information71 => bcer_rec.information71
645 ,p_information72 => bcer_rec.information72
646 ,p_information73 => bcer_rec.information73
647 ,p_information74 => bcer_rec.information74
648 ,p_information75 => bcer_rec.information75
649 ,p_information76 => bcer_rec.information76
650 ,p_information77 => bcer_rec.information77
651 ,p_information78 => bcer_rec.information78
652 ,p_information79 => bcer_rec.information79
653 ,p_information80 => bcer_rec.information80
654 ,p_information81 => bcer_rec.information81
655 ,p_information82 => bcer_rec.information82
656 ,p_information83 => bcer_rec.information83
657 ,p_information84 => bcer_rec.information84
658 ,p_information85 => bcer_rec.information85
659 ,p_information86 => bcer_rec.information86
660 ,p_information87 => bcer_rec.information87
661 ,p_information88 => bcer_rec.information88
662 ,p_information89 => bcer_rec.information89
663 ,p_information90 => bcer_rec.information90
664 ,p_information91 => bcer_rec.information91
665 ,p_information92 => bcer_rec.information92
666 ,p_information93 => bcer_rec.information93
667 ,p_information94 => bcer_rec.information94
668 ,p_information95 => bcer_rec.information95
669 ,p_information96 => bcer_rec.information96
670 ,p_information97 => bcer_rec.information97
671 ,p_information98 => bcer_rec.information98
672 ,p_information99 => bcer_rec.information99
673 ,p_information100 => bcer_rec.information100
674 ,p_information101 => bcer_rec.information101
675 ,p_information102 => bcer_rec.information102
676 ,p_information103 => bcer_rec.information103
677 ,p_information104 => bcer_rec.information104
678 ,p_information105 => bcer_rec.information105
679 ,p_information106 => bcer_rec.information106
680 ,p_information107 => bcer_rec.information107
681 ,p_information108 => bcer_rec.information108
682 ,p_information109 => bcer_rec.information109
683 ,p_information110 => bcer_rec.information110
684 ,p_information111 => bcer_rec.information111
685 ,p_information112 => bcer_rec.information112
686 ,p_information113 => bcer_rec.information113
687 ,p_information114 => bcer_rec.information114
688 ,p_information115 => bcer_rec.information115
689 ,p_information116 => bcer_rec.information116
690 ,p_information117 => bcer_rec.information117
691 ,p_information118 => bcer_rec.information118
692 ,p_information119 => bcer_rec.information119
693 ,p_information120 => bcer_rec.information120
694 ,p_information121 => bcer_rec.information121
695 ,p_information122 => bcer_rec.information122
696 ,p_information123 => bcer_rec.information123
697 ,p_information124 => bcer_rec.information124
698 ,p_information125 => bcer_rec.information125
699 ,p_information126 => bcer_rec.information126
700 ,p_information127 => bcer_rec.information127
701 ,p_information128 => bcer_rec.information128
702 ,p_information129 => bcer_rec.information129
703 ,p_information130 => bcer_rec.information130
704 ,p_information131 => bcer_rec.information131
705 ,p_information132 => bcer_rec.information132
706 ,p_information133 => bcer_rec.information133
707 ,p_information134 => bcer_rec.information134
708 ,p_information135 => bcer_rec.information135
709 ,p_information136 => bcer_rec.information136
710 ,p_information137 => bcer_rec.information137
711 ,p_information138 => bcer_rec.information138
712 ,p_information139 => bcer_rec.information139
713 ,p_information140 => bcer_rec.information140
714 ,p_information141 => bcer_rec.information141
715 ,p_information142 => bcer_rec.information142
716
717 /* Extra Reserved Columns
718 ,p_information143 => bcer_rec.information143
719 ,p_information144 => bcer_rec.information144
720 ,p_information145 => bcer_rec.information145
721 ,p_information146 => bcer_rec.information146
722 ,p_information147 => bcer_rec.information147
723 ,p_information148 => bcer_rec.information148
724 ,p_information149 => bcer_rec.information149
725 ,p_information150 => bcer_rec.information150
726 */
727 ,p_information151 => bcer_rec.information151
728 ,p_information152 => bcer_rec.information152
729 ,p_information153 => bcer_rec.information153
730
731 /* Extra Reserved Columns
732 ,p_information154 => bcer_rec.information154
733 ,p_information155 => bcer_rec.information155
734 ,p_information156 => bcer_rec.information156
735 ,p_information157 => bcer_rec.information157
736 ,p_information158 => bcer_rec.information158
737 ,p_information159 => bcer_rec.information159
738 */
739 ,p_information160 => bcer_rec.information160
740 ,p_information161 => bcer_rec.information161
741 ,p_information162 => bcer_rec.information162
742
743 /* Extra Reserved Columns
744 ,p_information163 => bcer_rec.information163
745 ,p_information164 => bcer_rec.information164
746 ,p_information165 => bcer_rec.information165
747 */
748 ,p_information166 => bcer_rec.information166
749 ,p_information167 => bcer_rec.information167
750 ,p_information168 => bcer_rec.information168
751 ,p_information169 => bcer_rec.information169
752 ,p_information170 => bcer_rec.information170
753
754 /* Extra Reserved Columns
755 ,p_information171 => bcer_rec.information171
756 ,p_information172 => bcer_rec.information172
757 */
758 ,p_information173 => bcer_rec.information173
759 ,p_information174 => bcer_rec.information174
760 ,p_information175 => bcer_rec.information175
761 ,p_information176 => bcer_rec.information176
762 ,p_information177 => bcer_rec.information177
763 ,p_information178 => bcer_rec.information178
764 ,p_information179 => bcer_rec.information179
765 ,p_information180 => bcer_rec.information180
766 ,p_information181 => bcer_rec.information181
767 ,p_information182 => bcer_rec.information182
768
769 /* Extra Reserved Columns
770 ,p_information183 => bcer_rec.information183
771 ,p_information184 => bcer_rec.information184
772 */
773 ,p_information185 => bcer_rec.information185
774 ,p_information186 => bcer_rec.information186
775 ,p_information187 => bcer_rec.information187
776 ,p_information188 => bcer_rec.information188
777
778 /* Extra Reserved Columns
779 ,p_information189 => bcer_rec.information189
780 */
781 ,p_information190 => bcer_rec.information190
782 ,p_information191 => bcer_rec.information191
783 ,p_information192 => bcer_rec.information192
784 ,p_information193 => bcer_rec.information193
785 ,p_information194 => bcer_rec.information194
786 ,p_information195 => bcer_rec.information195
787 ,p_information196 => bcer_rec.information196
788 ,p_information197 => bcer_rec.information197
789 ,p_information198 => bcer_rec.information198
790 ,p_information199 => bcer_rec.information199
791
792 /* Extra Reserved Columns
793 ,p_information200 => bcer_rec.information200
794 ,p_information201 => bcer_rec.information201
795 ,p_information202 => bcer_rec.information202
796 ,p_information203 => bcer_rec.information203
797 ,p_information204 => bcer_rec.information204
798 ,p_information205 => bcer_rec.information205
799 ,p_information206 => bcer_rec.information206
800 ,p_information207 => bcer_rec.information207
801 ,p_information208 => bcer_rec.information208
802 ,p_information209 => bcer_rec.information209
803 ,p_information210 => bcer_rec.information210
804 ,p_information211 => bcer_rec.information211
805 ,p_information212 => bcer_rec.information212
806 ,p_information213 => bcer_rec.information213
807 ,p_information214 => bcer_rec.information214
808 ,p_information215 => bcer_rec.information215
809 */
810 ,p_information216 => bcer_rec.information216
811 ,p_information217 => bcer_rec.information217
812 ,p_information218 => bcer_rec.information218
813 ,p_information219 => bcer_rec.information219
814 ,p_information220 => bcer_rec.information220
815 ,p_information221 => bcer_rec.information221
816 ,p_information222 => bcer_rec.information222
817 ,p_information223 => bcer_rec.information223
818 ,p_information224 => bcer_rec.information224
819 ,p_information225 => bcer_rec.information225
820 ,p_information226 => bcer_rec.information226
821 ,p_information227 => bcer_rec.information227
822 ,p_information228 => bcer_rec.information228
823 ,p_information229 => bcer_rec.information229
824 ,p_information230 => bcer_rec.information230
825 ,p_information231 => bcer_rec.information231
826 ,p_information232 => bcer_rec.information232
827 ,p_information233 => bcer_rec.information233
828 ,p_information234 => bcer_rec.information234
829 ,p_information235 => bcer_rec.information235
830 ,p_information236 => bcer_rec.information236
831 ,p_information237 => bcer_rec.information237
832 ,p_information238 => bcer_rec.information238
833 ,p_information239 => bcer_rec.information239
834 ,p_information240 => bcer_rec.information240
835 ,p_information241 => bcer_rec.information241
836 ,p_information242 => bcer_rec.information242
837 ,p_information243 => bcer_rec.information243
838 ,p_information244 => bcer_rec.information244
839 ,p_information245 => bcer_rec.information245
840 ,p_information246 => bcer_rec.information246
841 ,p_information247 => bcer_rec.information247
842 ,p_information248 => bcer_rec.information248
843 ,p_information249 => bcer_rec.information249
844 ,p_information250 => bcer_rec.information250
845 ,p_information251 => bcer_rec.information251
846 ,p_information252 => bcer_rec.information252
847 ,p_information253 => bcer_rec.information253
848 ,p_information254 => bcer_rec.information254
849 ,p_information255 => bcer_rec.information255
850 ,p_information256 => bcer_rec.information256
851 ,p_information257 => bcer_rec.information257
852 ,p_information258 => bcer_rec.information258
853 ,p_information259 => bcer_rec.information259
854 ,p_information260 => bcer_rec.information260
855 ,p_information261 => bcer_rec.information261
856 ,p_information262 => bcer_rec.information262
857 ,p_information263 => bcer_rec.information263
858 ,p_information264 => bcer_rec.information264
859 ,p_information265 => bcer_rec.information265
860 ,p_information266 => bcer_rec.information266
861 ,p_information267 => bcer_rec.information267
862 ,p_information268 => bcer_rec.information268
863 ,p_information269 => bcer_rec.information269
864 ,p_information270 => bcer_rec.information270
865 ,p_information271 => bcer_rec.information271
866 ,p_information272 => bcer_rec.information272
867 ,p_information273 => bcer_rec.information273
868 ,p_information274 => bcer_rec.information274
869 ,p_information275 => bcer_rec.information275
870 ,p_information276 => bcer_rec.information276
871 ,p_information277 => bcer_rec.information277
872 ,p_information278 => bcer_rec.information278
873 ,p_information279 => bcer_rec.information279
874 ,p_information280 => bcer_rec.information280
875 ,p_information281 => bcer_rec.information281
876 ,p_information282 => bcer_rec.information282
877 ,p_information283 => bcer_rec.information283
878 ,p_information284 => bcer_rec.information284
879 ,p_information285 => bcer_rec.information285
880 ,p_information286 => bcer_rec.information286
881 ,p_information287 => bcer_rec.information287
882 ,p_information288 => bcer_rec.information288
883 ,p_information289 => bcer_rec.information289
884 ,p_information290 => bcer_rec.information290
885 ,p_information291 => bcer_rec.information291
886 ,p_information292 => bcer_rec.information292
887 ,p_information293 => bcer_rec.information293
888 ,p_information294 => bcer_rec.information294
889 ,p_information295 => bcer_rec.information295
890 ,p_information296 => bcer_rec.information296
891 ,p_information297 => bcer_rec.information297
892 ,p_information298 => bcer_rec.information298
893 ,p_information299 => bcer_rec.information299
894 ,p_information300 => bcer_rec.information300
895 ,p_information301 => bcer_rec.information301
896 ,p_information302 => bcer_rec.information302
897 ,p_information303 => bcer_rec.information303
898 ,p_information304 => bcer_rec.information304
899
900 /* Extra Reserved Columns
901 ,p_information305 => bcer_rec.information305
902 */
903 ,p_information306 => bcer_rec.information306
904 ,p_information307 => bcer_rec.information307
905 ,p_information308 => bcer_rec.information308
906 ,p_information309 => bcer_rec.information309
907 ,p_information310 => bcer_rec.information310
908 ,p_information311 => bcer_rec.information311
909 ,p_information312 => bcer_rec.information312
910 ,p_information313 => bcer_rec.information313
911 ,p_information314 => bcer_rec.information314
912 ,p_information315 => bcer_rec.information315
913 ,p_information316 => bcer_rec.information316
914 ,p_information317 => bcer_rec.information317
915 ,p_information318 => bcer_rec.information318
916 ,p_information319 => bcer_rec.information319
917 ,p_information320 => bcer_rec.information320
918 /* Extra Reserved Columns
919 ,p_information321 => bcer_rec.information321
920 ,p_information322 => bcer_rec.information322
921 */
922 ,p_information323 => bcer_rec.information323
923 ,p_datetrack_mode => bcer_rec.datetrack_mode
924
925 ,p_copy_entity_result_id => l_grr_cer_id
926 ,p_object_version_number => l_grr_cer_ovn);
927 hr_utility.set_location('Leaving creating bcer rec', 10);
928 return l_grr_cer_id;
929 end create_bcer_rec ;
930
931
932 Function Get_Ref_Level
933 (P_Parent_Cer_Id IN Number,
934 P_prfl_Id IN Number)
935 Return Varchar2 Is
936
937 Cursor Ref_level is
938 Select Table_Alias
939 From ben_Copy_Entity_Results
940 Where Copy_Entity_Result_id in
941 (Select EPA.Gs_Mirror_Src_Entity_Result_id
942 From Ben_Copy_Entity_Results Elp,
943 Ben_Copy_Entity_Results Cep,
944 Ben_Copy_Entity_Results EPA
945 Where Elp.Gs_Parent_Entity_Result_id = P_Parent_Cer_Id
946 and Elp.Table_Alias = 'ELP'
947 and Cep.Copy_Entity_Result_id = Elp.Gs_Mirror_Src_Entity_Result_id
948 and Cep.Table_Alias = 'CEP'
949 and Cep.Information263 = P_Prfl_Id
950 and Epa.Copy_Entity_Result_id = Cep.Gs_Mirror_Src_Entity_Result_Id);
951
952 L_ref_Level Varchar2(5) := NULL;
953 Begin
954
955 Open Ref_level;
956 Fetch Ref_Level into L_Ref_Level;
957 Close Ref_Level;
958
959 If L_Ref_Level = 'CPP' Then
960 L_Ref_Level := 'PLIP';
961 ElsIf L_Ref_Level = 'PLN' Then
962 l_Ref_Level := 'PL';
963 End If;
964
965 Return L_Ref_Level;
966 End Get_Ref_Level;
967
968 Function Get_Dml_Operation
969 (P_Copy_Entity_Result_Id In Number
970 ,P_Opr_type IN Varchar2)
971 Return Varchar2 Is
972
973 Cursor Exist_Rec is
974 Select Information1
975 From ben_Copy_Entity_Results
976 Where Copy_Entity_Result_id = P_Copy_Entity_Result_Id;
977
978 L_Information1 Ben_Copy_Entity_Results.Information1%TYPE;
979 L_Dml_operation Varchar2(15);
980
981 Begin
982
983 Open Exist_Rec;
984 Fetch Exist_Rec into L_Information1;
985 Close Exist_Rec;
986
987 If P_Opr_type = 'U' Then
988 If L_Information1 is NULL Then
989 L_Dml_operation := 'INSERT';
990 Else
991 L_Dml_operation := 'UPDATE';
992 End If;
993 Elsif P_Opr_type = 'D' Then
994 If L_Information1 is NULL Then
995 L_Dml_operation := 'PURGE';
996 Else
997 L_Dml_operation := 'DELETE';
998 End If;
999 End If;
1000
1001 Return L_Dml_operation;
1002 End Get_Dml_Operation;
1003
1004 function is_elpro_in_stage (p_eligy_prfl_id in number,p_copy_entity_txn_id in number)
1005 return boolean
1006 is
1007 Cursor csr_elpro_in_stage
1008 is
1009 select null
1010 from ben_copy_entity_results
1011 where table_alias = 'ELP'
1012 and copy_entity_txn_id = p_copy_entity_txn_id
1013 and information1 = p_eligy_prfl_id;
1014 l_dummy varchar2(1);
1015 begin
1016 OPEN csr_elpro_in_stage;
1017 FETCH csr_elpro_in_stage into l_dummy;
1018 if csr_elpro_in_stage%FOUND then
1019 return true;
1020 else
1021 return false;
1022 end if;
1023 CLOSE csr_elpro_in_stage;
1024
1025 end is_elpro_in_stage;
1026
1027 Function is_elpro_created_in_stage(p_eligy_prfl_id in number,
1028 p_copy_entity_txn_id in number)
1029 return number
1030 is
1031 Cursor csr_eligy_prfl_in_txn
1032 is
1033 select copy_entity_result_id
1034 from ben_copy_entity_results
1035 where copy_entity_txn_id =p_copy_entity_txn_id
1036 and table_alias = 'ELP'
1037 and information1 = p_eligy_prfl_id
1038 and dml_operation <> 'REUSE';
1039 l_eligy_prfl_cer_id number;
1040 begin
1041 OPEN csr_eligy_prfl_in_txn;
1042 FETCH csr_eligy_prfl_in_txn into l_eligy_prfl_cer_id;
1043 if csr_eligy_prfl_in_txn%NOTFOUND then
1044 l_eligy_prfl_cer_id := -1;
1045 end if;
1046
1047 CLOSE csr_eligy_prfl_in_txn;
1048 return l_eligy_prfl_cer_id;
1049 end is_elpro_created_in_stage;
1050
1051 procedure update_crit_records_in_staging(p_copy_entity_txn_id in number)
1052 is
1053
1054 begin
1055
1056 null;
1057
1058 end update_crit_records_in_staging;
1059 /*
1060 procedure update_crit_records_in_staging(p_copy_entity_txn_id in number)
1061 is
1062 Cursor csr_eligy_prfl_cer
1063 is
1064 select copy_entity_result_id eligy_prfl_cer_id
1065 from ben_copy_entity_results
1066 where copy_entity_txn_id = p_copy_entity_txn_id
1067 and table_alias = 'ELP'
1068 and information1 is not null;
1069 Cursor csr_criteria(p_eligy_prfl_cer_id in number)
1070 is
1071 select copy_entity_result_id crit_cer_id
1072 from ben_copy_entity_results
1073 where copy_entity_txn_id = p_copy_entity_txn_id
1074 and parent_entity_result_id =p_eligy_prfl_cer_id
1075 and gs_parent_entity_result_id is null;
1076
1077 begin
1078
1079 for i in csr_eligy_prfl_cer loop
1080
1081 for j in csr_criteria(i.eligy_prfl_cer_id) loop
1082 update ben_copy_entity_results
1083 set gs_parent_entity_result_id = i.eligy_prfl_cer_id,
1084 GS_MIRROR_SRC_ENTITY_RESULT_ID = i.eligy_prfl_cer_id
1085 where copy_entity_result_id = j.crit_cer_id;
1086
1087 end loop;
1088 end loop;
1089
1090 end update_crit_records_in_staging;
1091 */
1092
1093 Procedure Create_Eligibility_Profile
1094 (p_Copy_Entity_txn_Id In Number,
1095 P_gs_Parent_Entity_Result_Id In Number,
1096 P_Effective_Date In Date,
1097 P_Prfl_Id In Number,
1098 P_Name In Varchar2,
1099 P_Txn_Type In Varchar2,
1100 p_Txn_Mode In Varchar2,
1101 P_Business_Group_Id In Number,
1102 P_Req_opt In Varchar2,
1103 P_Ref_level In Varchar2,
1104 P_Compute_Score_Flag In Varchar2) Is
1105
1106 L_PRTN_COPY_ENTITY_RSLT_ID BEN_COPY_ENTITY_RESULTS.Copy_Entity_Result_Id%TYPE;
1107 L_DELPRTN_COPY_ENTITY_RSLT_ID BEN_COPY_ENTITY_RESULTS.Copy_Entity_Result_Id%TYPE;
1108 l_Copy_Entity_Rslt_Id BEN_COPY_ENTITY_RESULTS.Copy_Entity_Result_Id%TYPE;
1109 l_PrtnPrfl_Copy_Entity_Rslt_Id BEN_COPY_ENTITY_RESULTS.Copy_Entity_Result_Id%TYPE;
1110 l_Object_version_number BEN_COPY_ENTITY_RESULTS.Object_version_number%TYPE;
1111 l_Prtn_Elig_Ovn BEN_COPY_ENTITY_RESULTS.Object_version_number%TYPE;
1112 l_Prtn_Elig_Prfl_Ovn BEN_COPY_ENTITY_RESULTS.Object_version_number%TYPE;
1113 L_PRTN_ELIG_PRFL_ID Ben_Prtn_Elig_Prfl_f.Prtn_Elig_Prfl_Id%TYPE;
1114 l_Table_Route_Id BEN_COPY_ENTITY_RESULTS.Table_Route_Id%TYPE;
1115 L_Table_Name Pqh_Table_Route.Display_Name%TYPE;
1116 l_Prtn_Elig_id Pqh_Copy_Entity_Results.Copy_Entity_Result_Id%TYPE;
1117 l_DelPrtn_Elig_id Pqh_Copy_Entity_Results.Copy_Entity_Result_Id%TYPE;
1118 l_InsYN Varchar2(1) := 'N';
1119 l_DelYN Varchar2(1) := 'N';
1120
1121 L_PLIP_ID Ben_PLIP_F.Plip_Id%TYPE;
1122 L_OIPL_ID Ben_OIPL_F.Oipl_Id%TYPE;
1123 L_PGM_ID Ben_Pgm_F.Pgm_Id%TYPE;
1124 L_PL_ID Ben_Pl_F.Pl_Id%TYPE;
1125 l_Ovn Ben_Copy_Entity_Results.Object_Version_Number%TYPE;
1126 l_Prtn_ovn Ben_Copy_Entity_Results.Object_Version_Number%TYPE;
1127 l_DelPrtn_ovn Ben_Copy_Entity_Results.Object_Version_Number%TYPE;
1128 L_Prtn_Mirror_result_Id BEN_COPY_ENTITY_RESULTS.Copy_Entity_Result_Id%TYPE;
1129 l_Pl_Cer_Id BEN_COPY_ENTITY_RESULTS.Copy_Entity_Result_Id%TYPE;
1130 l_Ref_Level Varchar2(5);
1131 l_Count Number;
1132 L_Del_Parent_Entity_Result_Id BEN_COPY_ENTITY_RESULTS.Parent_Entity_Result_Id%TYPE;
1133 L_Del_Cer_Id BEN_COPY_ENTITY_RESULTS.Copy_Entity_Result_Id%TYPE;
1134 l_Dml_Opr Varchar2(30);
1135 L_DML_OPERATION BEN_COPY_ENTITY_RESULTS.DML_OPERATION%TYPE;
1136 L_DML BEN_COPY_ENTITY_RESULTS.DML_OPERATION%TYPE;
1137 l_Elp_Cer_id BEN_COPY_ENTITY_RESULTS.Copy_Entity_Result_Id%TYPE;
1138 l_Cer_id BEN_COPY_ENTITY_RESULTS.Copy_Entity_Result_Id%TYPE;
1139 l_Elp_ovn Ben_Copy_Entity_Results.Object_Version_Number%TYPE;
1140 L_Business_Area Varchar2(255) := 'PQH_GSP_TASK_LIST';
1141 l_elp_count number ;
1142
1143 Cursor BusArea is
1144 Select Nvl(Information9,'PQH_GSP_TASK_LIST')
1145 from Pqh_Copy_Entity_Attribs
1146 where Copy_Entity_Txn_Id = p_Copy_Entity_txn_Id;
1147
1148 Cursor TablRoute (p_table_alias IN Varchar2) is
1149 Select Table_Route_id, Substr(Display_name,1,30)
1150 -- from Pqh_Table_Route bug5763511
1151 from Pqh_Table_Route_vl
1152 Where table_alias = p_table_alias;
1153
1154 Cursor Ovn is
1155 Select object_version_Number
1156 from Ben_Copy_Entity_Results
1157 where Copy_Entity_Result_id = l_Copy_Entity_Rslt_Id;
1158
1159 Cursor Csr_Dml_Operation(p_elp_id In number) is
1160 Select DML_OPERATION,copy_entity_result_id
1161 from Ben_Copy_Entity_Results
1162 where Information1 = p_elp_id
1163 and table_alias = 'ELP'
1164 and copy_entity_txn_id = p_copy_entity_txn_id;
1165
1166 Cursor Prtn(P_Mirror_result_Id In Number) Is
1167 Select Information1, Copy_Entity_Result_Id, Object_version_NUmber, DML_OPERATION
1168 from Ben_Copy_Entity_Results
1169 where Copy_Entity_Txn_Id = p_Copy_Entity_txn_Id
1170 and GS_MIRROR_SRC_ENTITY_RESULT_ID = P_Mirror_result_Id
1171 and Table_alias = 'EPA'
1172 and Nvl(Dml_operation,'XX') <> 'DELETE';
1173
1174 Cursor PrtnElig Is
1175 Select Cep.Copy_Entity_Result_id, Cep.Object_version_Number,
1176 Elp.Copy_Entity_Result_id, Elp.Object_Version_Number
1177 From BEN_Copy_Entity_Results CEP,
1178 Ben_Copy_Entity_Results ELP
1179 Where Elp.Copy_Entity_Txn_Id = p_Copy_Entity_txn_Id
1180 and elp.Table_alias = 'ELP'
1181 and elp.Gs_Parent_Entity_Result_Id = P_gs_Parent_Entity_Result_Id
1182 and Elp.Gs_Mirror_Src_Entity_Result_id = Cep.Copy_Entity_Result_id
1183 and Cep.Information263 = P_Prfl_Id
1184 and Nvl(Cep.Dml_Operation,'XX') <> 'DELETE';
1185
1186 Cursor Plip_Dtls is
1187 Select Plan.Copy_Entity_Result_Id
1188 From Ben_Copy_Entity_Results Plan
1189 where PLan.Gs_Mirror_Src_Entity_Result_id = P_Gs_Parent_Entity_Result_Id
1190 and Plan.table_Alias = 'PLN'
1191 and plan.copy_entity_txn_id = p_copy_entity_txn_id;
1192
1193 Cursor PrflCnt(P_Parent_Entity_Result_Id In Number) is
1194 Select Count(*)
1195 from Ben_Copy_Entity_Results Prtn,
1196 Ben_Copy_Entity_Results PrtnElig
1197 where Prtn.Copy_Entity_Txn_Id = p_Copy_Entity_txn_Id
1198 and Prtn.GS_Mirror_Src_ENTITY_RESULT_ID = P_Parent_Entity_Result_Id
1199 and Prtn.Table_alias = 'EPA'
1200 and PrtnElig.Copy_Entity_Txn_Id = Prtn.Copy_Entity_Txn_Id
1201 and Prtnelig.Gs_Mirror_Src_Entity_Result_id = Prtn.Copy_Entity_Result_Id
1202 and PrtnElig.INFORMATION263 <> P_Prfl_Id;
1203
1204 Cursor Csr_is_elp_present(p_elp_id In number) is
1205 Select Count(*)
1206 from Ben_Copy_Entity_Results
1207 where Information1 = p_elp_id
1208 and table_alias = 'ELP'
1209 and copy_entity_txn_id = p_copy_entity_txn_id;
1210
1211 Begin
1212 /* The Following Condition is used to Delete Ben_prtn_Elig_F
1213 if the Reference type is changed */
1214
1215 /* L_Ref_Level refers to the Old reference level ..
1216 P_ref_Level Implies the reference level the user tries to Change.
1217 As such reference level is applicable on ly Plan and Plan In program */
1218
1219 /* Initialize Multi Message Detection */
1220
1221 --hr_utility.trace_on(NULL,'GUN');
1222
1223 hr_multi_message.enable_message_list;
1224
1225 hr_utility.set_location('Inside Create Progression Rules ',10);
1226 hr_utility.set_location('p_Copy_Entity_txn_Id '||p_Copy_Entity_txn_Id,10);
1227 hr_utility.set_location('P_gs_Parent_Entity_Result_Id'||P_gs_Parent_Entity_Result_Id,10);
1228 hr_utility.set_location('P_Effective_Date'||P_Effective_Date,10);
1229 hr_utility.set_location('P_Prfl_Id'||P_Prfl_Id,10);
1230 hr_utility.set_location('P_Name '||P_Name,10);
1231 hr_utility.set_location('P_Txn_Type'||P_Txn_Type,10);
1232 hr_utility.set_location('p_Txn_Mode'||p_Txn_Mode,10);
1233 hr_utility.set_location(' P_Business_Group_Id'||P_Business_Group_Id,10);
1234 hr_utility.set_location('P_Req_opt'||P_Req_opt,19);
1235 hr_utility.set_location('P_Ref_level'||P_Ref_level,19);
1236 hr_utility.set_location('P_Compute_Score_Flag'||P_Compute_Score_Flag,19);
1237
1238
1239
1240 Open BusArea;
1241 Fetch BusArea into l_Business_Area;
1242 Close BusArea;
1243
1244 If P_Txn_Type <> 'GRD' Then
1245
1246 hr_utility.set_location('P_Txn_Type = ' || P_Txn_Type ,20);
1247
1248 Open Prtn(P_Gs_Parent_Entity_Result_Id);
1249 Fetch Prtn into l_Prtn_Elig_id, l_Prtn_Copy_Entity_Rslt_Id, l_Prtn_ovn, L_DML_OPERATION;
1250 If Prtn%FOUND Then
1251 l_InsYN := 'N';
1252 Else
1253 l_InsYN := 'Y';
1254 End If;
1255 Close Prtn;
1256 L_Prtn_Mirror_result_Id := P_Gs_Parent_Entity_Result_Id;
1257
1258 hr_utility.set_location('Insert EPA ' || l_InsYN ,30);
1259
1260 ElsIf P_Txn_Type = 'GRD' Then
1261
1262 If p_Txn_Mode = 'U' Then
1263 l_Ref_Level := Get_Ref_Level(P_gs_Parent_Entity_Result_Id,P_Prfl_Id);
1264 End If;
1265
1266 If Nvl(l_Ref_Level,'YY') = 'PL' or P_Ref_Level = 'PL' Then
1267 Open Plip_Dtls;
1268 Fetch Plip_Dtls into l_Pl_Cer_Id;
1269 Close Plip_Dtls;
1270 End If;
1271
1272 hr_utility.set_location('l_Ref_Level ' || l_Ref_Level ,40);
1273 hr_utility.set_location('p_Ref_Level ' || p_Ref_Level ,50);
1274
1275 If P_Txn_Mode = 'U' Then
1276 If l_Ref_Level <> P_Ref_Level Then
1277 If l_Ref_Level = 'PLIP' Then
1278 L_Del_Parent_Entity_Result_Id := P_Gs_Parent_Entity_Result_Id;
1279 ElsIf l_Ref_Level = 'PL' Then
1280 L_Del_Parent_Entity_Result_Id := l_Pl_Cer_Id;
1281 End If;
1282
1283 Open PrflCnt(L_Del_Parent_Entity_Result_Id);
1284 Fetch PrflCnt into l_Count;
1285 Close PrflCnt;
1286
1287 If l_Count = 0 Then
1288 L_DelYN := 'Y';
1289
1290 Open Prtn(L_Del_Parent_Entity_Result_Id);
1291 Fetch Prtn into l_DelPrtn_Elig_id, l_DelPrtn_Copy_Entity_Rslt_Id, l_DelPrtn_ovn, L_DML_OPERATION;
1292 Close Prtn;
1293 End If;
1294 End If;
1295 End If;
1296
1297 hr_utility.set_location('Delete Epa ' || L_DelYN ,60);
1298
1299 If p_Txn_Mode = 'U' and L_DelYN = 'Y' Then
1300
1301 -- Delete CEP Row ---
1302 Open PrtnElig;
1303 Fetch PrtnElig into l_PrtnPrfl_Copy_Entity_Rslt_Id, l_Prtn_Elig_Prfl_Ovn, L_Elp_Cer_Id, l_Elp_ovn;
1304 If PrtnElig%Found Then
1305 l_Dml_Opr := NULL;
1306 L_Dml_Opr := Get_Dml_Operation(l_PrtnPrfl_Copy_Entity_Rslt_Id, 'D');
1307
1308 if L_Dml_Opr = 'PURGE' then
1309 Ben_Copy_Entity_Results_Api.DELETE_COPY_ENTITY_RESULTS
1310 (P_COPY_ENTITY_RESULT_ID => l_PrtnPrfl_Copy_Entity_Rslt_Id,
1311 P_OBJECT_VERSION_NUMBER => l_Prtn_Elig_Prfl_ovn,
1312 P_EFFECTIVE_DATE => P_Effective_Date);
1313 Else
1314 Ben_Copy_Entity_Results_Api.UPDATE_COPY_ENTITY_RESULTS
1315 (P_EFFECTIVE_DATE => P_Effective_Date,
1316 P_COPY_ENTITY_TXN_ID => p_Copy_Entity_txn_Id,
1317 P_DML_OPERATION => 'DELETE',
1318 P_INFORMATION323 => NULL,
1319 P_COPY_ENTITY_RESULT_ID => l_PrtnPrfl_Copy_Entity_Rslt_Id,
1320 P_OBJECT_VERSION_NUMBER => l_Prtn_Elig_Prfl_ovn);
1321 End If;
1322 End If;
1323 Close PrtnElig;
1324
1325 hr_utility.set_location('Deleting Epa .. Cer_id is ' || l_DelPrtn_Copy_Entity_Rslt_Id ,70);
1326 l_Dml_Opr := NULL;
1327 L_Dml_Opr := Get_Dml_Operation(l_DelPrtn_Copy_Entity_Rslt_Id, 'D');
1328
1329 If L_Dml_Opr = 'PURGE' then
1330 Ben_Copy_Entity_Results_Api.DELETE_COPY_ENTITY_RESULTS
1331 (P_COPY_ENTITY_RESULT_ID => l_DelPrtn_Copy_Entity_Rslt_Id,
1332 P_OBJECT_VERSION_NUMBER => l_DelPrtn_ovn,
1333 P_EFFECTIVE_DATE => P_Effective_Date);
1334 Else
1335 Ben_Copy_Entity_Results_Api.UPDATE_COPY_ENTITY_RESULTS
1336 (P_EFFECTIVE_DATE => P_Effective_Date,
1337 P_COPY_ENTITY_TXN_ID => p_Copy_Entity_txn_Id,
1338 P_DML_OPERATION => 'DELETE',
1339 P_INFORMATION323 => NULL,
1340 P_COPY_ENTITY_RESULT_ID => l_DelPrtn_Copy_Entity_Rslt_Id,
1341 P_OBJECT_VERSION_NUMBER => l_DelPrtn_ovn);
1342 End If;
1343
1344 End if;
1345
1346 If P_Ref_Level = 'PL' Then
1347 l_Prtn_Mirror_result_Id := l_Pl_Cer_Id;
1348 Open Prtn(l_Pl_Cer_Id);
1349 ElsIf P_Ref_Level = 'PLIP' Then
1350 L_Prtn_Mirror_result_Id := P_Gs_Parent_Entity_Result_Id;
1351 Open Prtn(P_Gs_Parent_Entity_Result_Id);
1352 End If;
1353
1354 Fetch Prtn into l_Prtn_Elig_id, l_Prtn_Copy_Entity_Rslt_Id, l_Prtn_ovn, L_DML_OPERATION;
1355 If Prtn%FOUND Then
1356 l_InsYN := 'N';
1357 If L_DML_OPERATION = 'DELETE' Then
1358 Ben_Copy_Entity_Results_Api.UPDATE_COPY_ENTITY_RESULTS
1359 (P_EFFECTIVE_DATE => P_Effective_Date,
1360 P_COPY_ENTITY_TXN_ID => p_Copy_Entity_txn_Id,
1361 P_COPY_ENTITY_RESULT_ID => l_Prtn_Copy_Entity_Rslt_Id,
1362 P_DML_OPeration => 'UPDATE',
1363 P_INFORMATION323 => NULL,
1364 P_OBJECT_VERSION_NUMBER => l_Prtn_ovn);
1365 End If;
1366 Else
1367 l_InsYN := 'Y';
1368 End If;
1369 Close Prtn;
1370
1371 End If;
1372
1373 /* Populating Eligibility profiles and the associated criterias */
1374
1375 If ( p_Txn_Mode = 'I' ) Then
1376 --OPEN Csr_Dml_Operation(P_Prfl_Id);
1377 --FETCH Csr_Dml_Operation into l_dml,l_cer_id;
1378 --close Csr_Dml_Operation;
1379
1380 OPEN Csr_is_elp_present(P_Prfl_Id);
1381 FETCH Csr_is_elp_present into l_elp_count;
1382 close Csr_is_elp_present;
1383
1384 if l_elp_count > 0 then
1385 hr_utility.set_location('DML Operation is INSERT so this ELP is created now', 79);
1386
1387 l_Copy_Entity_Rslt_Id := create_duplicate_elp_tree (
1388 p_copy_entity_txn_id => p_Copy_Entity_txn_Id
1389 ,p_business_group_id => P_Business_Group_Id
1390 ,p_eligy_prfl_id => P_Prfl_Id
1391 );
1392 update ben_copy_entity_results
1393 set gs_parent_entity_result_id = P_gs_Parent_Entity_Result_Id
1394 ,parent_entity_result_id = P_gs_Parent_Entity_Result_Id
1395 where copy_entity_result_id = l_Copy_Entity_Rslt_Id;
1396 else
1397 hr_utility.set_location('DML Operation is not INSERT so this ELP is from PUI', 79);
1398
1399 Pqh_Gsp_Hr_To_Stage.Populate_Ep_Hierarchy
1400 (p_copy_entity_txn_id => p_Copy_Entity_txn_Id
1401 ,p_effective_date => P_Effective_Date
1402 ,p_business_group_id => P_BUSINESS_GROUP_ID
1403 ,p_ep_id => P_Prfl_Id
1404 ,P_BUSINESS_AREA => Nvl(l_Business_Area,'PQH_GSP_TASK_LIST')
1405 ,p_ep_cer_id => l_Copy_Entity_Rslt_Id);
1406
1407 End If;
1408 hr_utility.set_location('Cer Id of ELP is:'||l_Copy_Entity_Rslt_Id, 79);
1409 End If;
1410
1411
1412 Open TablRoute('EPA');
1413 fetch TablRoute into l_table_Route_Id, L_Table_Name;
1414 Close TablRoute;
1415
1416 If l_InsYN = 'Y' Then
1417
1418 /* This Insert Links the Ben Object with the Eligibility Profiles (CEP) */
1419 hr_utility.set_location('Inserting EPA .. L_Prtn_Mirror_result_Id ' || L_Prtn_Mirror_result_Id , 80);
1420
1421 Ben_Copy_Entity_Results_Api.CREATE_COPY_ENTITY_RESULTS
1422 (P_EFFECTIVE_DATE => P_Effective_Date,
1423 P_COPY_ENTITY_TXN_ID => p_Copy_Entity_txn_Id,
1424 P_RESULT_TYPE_CD => 'DISPLAY',
1425 P_NUMBER_OF_COPIES => 1,
1426 P_TABLE_NAME => l_Table_name,
1427 P_TABLE_ALIAS => 'EPA',
1428 P_TABLE_ROUTE_ID => l_table_Route_Id,
1429 P_STATUS => 'VALID',
1430 P_DML_OPERATION => 'INSERT',
1431 P_INFORMATION_CATEGORY => 'GSP',
1432 p_INFORMATION1 => l_Prtn_Elig_Id,
1433 P_INFORMATION2 => P_Effective_Date,
1434 P_INFORMATION4 => P_BUSINESS_GROUP_ID,
1435 p_Information5 => P_Name,
1436 P_INFORMATION256 => L_PLIP_ID,
1437 P_INFORMATION258 => L_OIPL_ID,
1438 P_INFORMATION260 => L_PGM_ID,
1439 P_INFORMATION261 => L_PL_ID,
1440 -- P_INFORMATION265 => 1,
1441 P_GS_MR_SRC_ENTITY_RESULT_ID => L_Prtn_Mirror_result_Id,
1442 P_COPY_ENTITY_RESULT_ID => l_Prtn_Copy_Entity_Rslt_Id,
1443 P_OBJECT_VERSION_NUMBER => l_Prtn_Elig_Ovn);
1444
1445 End If;
1446
1447 open TablRoute('CEP');
1448 fetch TablRoute into l_table_Route_Id, L_Table_Name;
1449 Close TablRoute;
1450
1451 Open PrtnElig;
1452 Fetch PrtnElig into l_PrtnPrfl_Copy_Entity_Rslt_Id, l_Prtn_Elig_Prfl_Ovn, l_Elp_Cer_id, l_Elp_Ovn;
1453
1454 If PrtnElig%FOUND Then
1455 hr_utility.set_location('Inserting CEP ..l_Prtn_Copy_Entity_Rslt_Id ' || l_Prtn_Copy_Entity_Rslt_Id , 90);
1456
1457 Ben_Copy_Entity_Results_Api.UPDATE_COPY_ENTITY_RESULTS
1458 (P_EFFECTIVE_DATE => P_Effective_Date,
1459 P_COPY_ENTITY_TXN_ID => p_Copy_Entity_txn_Id,
1460 P_RESULT_TYPE_CD => 'DISPLAY',
1461 P_NUMBER_OF_COPIES => 1,
1462 P_TABLE_NAME => l_Table_name,
1463 P_TABLE_ALIAS => 'CEP',
1464 P_TABLE_ROUTE_ID => l_table_Route_Id,
1465 P_STATUS => 'VALID',
1466 P_DML_OPERATION => Get_Dml_Operation(l_PrtnPrfl_Copy_Entity_Rslt_Id,'U'),
1467 P_INFORMATION_CATEGORY => 'GSP',
1468 P_INFORMATION2 => P_EFFECTIVE_DATE,
1469 P_INFORMATION4 => P_BUSINESS_GROUP_ID,
1470 p_Information5 => P_Name,
1471 p_Information13 => P_Compute_Score_Flag, -- Added for Rank Support
1472 P_Information12 => P_Req_opt,
1473 P_Information15 => P_Ref_level,
1474 P_INFORMATION229 => l_PRTN_ELIG_ID,
1475 P_INFORMATION263 => p_Prfl_Id,
1476 -- P_INFORMATION265 => 1,
1477 P_INFORMATION323 => NULL,
1478 P_COPY_ENTITY_RESULT_ID => l_PrtnPrfl_Copy_Entity_Rslt_Id,
1479 P_GS_MR_SRC_ENTITY_RESULT_ID => l_Prtn_Copy_Entity_Rslt_Id,
1480 P_OBJECT_VERSION_NUMBER => l_Prtn_Elig_Prfl_Ovn);
1481
1482 Else
1483
1484 hr_utility.set_location('Updating CEP ..l_Prtn_Copy_Entity_Rslt_Id ' || l_Prtn_Copy_Entity_Rslt_Id , 90);
1485
1486 Ben_Copy_Entity_Results_Api.CREATE_COPY_ENTITY_RESULTS
1487 (P_EFFECTIVE_DATE => P_Effective_Date,
1488 P_COPY_ENTITY_TXN_ID => p_Copy_Entity_txn_Id,
1489 P_RESULT_TYPE_CD => 'DISPLAY',
1490 P_NUMBER_OF_COPIES => 1,
1491 P_TABLE_NAME => l_Table_name,
1492 P_TABLE_ALIAS => 'CEP',
1493 P_TABLE_ROUTE_ID => l_table_Route_Id,
1494 P_STATUS => 'VALID',
1495 P_DML_OPERATION => 'INSERT',
1496 P_INFORMATION_CATEGORY => 'GSP',
1497 P_INFORMATION1 => l_PRTN_ELIG_PRFL_ID,
1498 P_INFORMATION2 => P_EFFECTIVE_DATE,
1499 P_INFORMATION4 => P_BUSINESS_GROUP_ID,
1500 p_Information5 => P_Name,
1501 p_Information13 => P_Compute_Score_Flag, -- Added for Rank Support
1502 P_Information12 => P_Req_opt,
1503 P_Information15 => P_Ref_level,
1504 P_INFORMATION229 => l_PRTN_ELIG_ID,
1505 P_INFORMATION263 => p_Prfl_Id,
1506 --P_INFORMATION265 => 1,
1507 P_COPY_ENTITY_RESULT_ID => l_PrtnPrfl_Copy_Entity_Rslt_Id,
1508 P_GS_MR_SRC_ENTITY_RESULT_ID => l_Prtn_Copy_Entity_Rslt_Id,
1509 P_OBJECT_VERSION_NUMBER => l_Prtn_Elig_Prfl_Ovn);
1510
1511 If p_Txn_Mode <> 'I' Then
1512 Ben_Copy_Entity_Results_Api.UPDATE_COPY_ENTITY_RESULTS
1513 (P_EFFECTIVE_DATE => P_Effective_Date,
1514 P_COPY_ENTITY_TXN_ID => p_Copy_Entity_txn_Id,
1515 P_Gs_Parent_Entity_Result_Id => P_Gs_Parent_Entity_Result_Id,
1516 P_GS_MR_SRC_ENTITY_RESULT_ID => l_PrtnPrfl_Copy_Entity_Rslt_Id,
1517 P_COPY_ENTITY_RESULT_ID => l_Elp_Cer_Id,
1518 P_INFORMATION323 => NULL,
1519 P_OBJECT_VERSION_NUMBER => l_Elp_ovn);
1520 End If;
1521
1522 End If;
1523 Close PrtnElig;
1524
1525 If p_Txn_Mode = 'I' Then
1526
1527 hr_utility.set_location('Txn Mode is I so going for update', 100);
1528
1529 /* this update is used to Link the ELP with the BEN Object */
1530 Open ovn;
1531 Fetch ovn into l_ovn;
1532 Close ovn;
1533
1534 Ben_Copy_Entity_Results_Api.UPDATE_COPY_ENTITY_RESULTS
1535 (P_EFFECTIVE_DATE => P_Effective_Date,
1536 P_COPY_ENTITY_TXN_ID => p_Copy_Entity_txn_Id,
1537 P_Gs_Parent_Entity_Result_Id => P_Gs_Parent_Entity_Result_Id,
1538 P_GS_MR_SRC_ENTITY_RESULT_ID => l_PrtnPrfl_Copy_Entity_Rslt_Id,
1539 P_COPY_ENTITY_RESULT_ID => l_Copy_Entity_Rslt_Id,
1540 P_INFORMATION323 => NULL,
1541 P_OBJECT_VERSION_NUMBER => l_ovn);
1542
1543 hr_utility.set_location('Updated gs_paernt successfully', 100);
1544
1545 pull_elpro_to_stage(p_copy_entity_txn_id => p_copy_entity_txn_id,
1546 p_eligy_prfl_id => p_Prfl_Id,
1547 p_effective_date => p_effective_date,
1548 p_business_group_id => p_business_group_id
1549 );
1550
1551 End If;
1552 --hr_utility.trace_off;
1553 /*
1554 Exception
1555 when hr_multi_message.error_message_exist then
1556
1557 hr_utility.set_location('Exception handled ', 100);
1558 --hr_utility.trace_off;
1559 rollback;
1560
1561 When others then
1562
1563 hr_utility.set_location('Unhandled Exception ', 110);
1564 --hr_utility.trace_off;
1565 Raise;
1566 */
1567 End Create_Eligibility_Profile;
1568
1569 Procedure Delete_Eligibility
1570 (P_Copy_Entity_txn_id IN Number
1571 ,P_Copy_Entity_result_id IN NUmber) Is
1572
1573 L_Cep_Cer_Id Ben_Copy_Entity_Results.Copy_Entity_Result_Id%TYPE;
1574 L_Cep_Ovn Ben_Copy_Entity_Results.Object_Version_Number%TYPE;
1575 L_Epa_Cer_Id Ben_Copy_Entity_Results.Copy_Entity_Result_Id%TYPE;
1576 L_Epa_Ovn Ben_Copy_Entity_Results.Object_Version_Number%TYPE;
1577 L_Dml_Opr Varchar2(30);
1578 L_EligPrfl_ID Ben_Eligy_Prfl_f.Eligy_Prfl_Id%TYPE;
1579 L_Cnt Number(15);
1580
1581 Cursor Del_Elp is
1582 Select Copy_Entity_Result_Id, Object_Version_Number
1583 from Ben_Copy_Entity_Results
1584 where Copy_Entity_txn_Id = P_Copy_Entity_Txn_Id
1585 Start With Copy_Entity_Result_Id = P_Copy_Entity_Result_Id
1586 Connect By Gs_Parent_Entity_Result_Id = Prior Copy_Entity_Result_Id;
1587
1588 Cursor Cep_id is
1589 Select Gs_Mirror_Src_Entity_Result_id, INFORMATION1
1590 From Ben_Copy_Entity_results
1591 Where Copy_Entity_Result_Id = P_Copy_Entity_result_id;
1592
1593 Cursor EPA_Id is
1594 Select Gs_Mirror_Src_Entity_Result_id
1595 from Ben_Copy_Entity_Results
1596 Where Copy_Entity_Result_Id = L_Cep_Cer_Id;
1597
1598 Cursor Ovn_Dtls (P_Cer_id IN Number) Is
1599 Select Object_Version_Number
1600 From Ben_Copy_Entity_Results
1601 Where Copy_Entity_Result_Id = P_Cer_id;
1602
1603 Begin
1604
1605 Open Cep_id;
1606 Fetch Cep_id into L_Cep_Cer_Id, L_EligPrfl_ID;
1607 Close Cep_id;
1608
1609 Open Ovn_Dtls(L_Cep_Cer_Id);
1610 Fetch Ovn_Dtls into L_Cep_Ovn;
1611 Close Ovn_Dtls;
1612
1613 Open Epa_Id;
1614 Fetch Epa_Id Into L_Epa_Cer_Id;
1615 Close Epa_Id;
1616
1617 Open Ovn_Dtls(L_Epa_Cer_Id);
1618 Fetch Ovn_Dtls into L_Epa_Ovn;
1619 Close Ovn_Dtls;
1620
1621 Select Count(*) into L_Cnt
1622 from Ben_Copy_Entity_Results Prtn,
1623 Ben_Copy_Entity_Results PrtnElig
1624 where Prtn.Copy_Entity_Txn_Id = p_Copy_Entity_txn_Id
1625 and Prtn.Copy_ENTITY_RESULT_ID = L_Epa_Cer_Id
1626 and Prtn.Table_alias = 'EPA'
1627 and PrtnElig.Copy_Entity_Txn_Id = Prtn.Copy_Entity_Txn_Id
1628 and Prtnelig.Gs_Mirror_Src_Entity_Result_id = Prtn.Copy_Entity_Result_Id
1629 and PrtnElig.INFORMATION263 <> L_EligPrfl_ID
1630 and PrtnElig.Dml_Operation <> 'DELETE';
1631
1632 If L_Cnt = 0 then
1633 L_Dml_Opr := Get_Dml_Operation(L_Epa_Cer_Id, 'D');
1634
1635 If L_Dml_Opr = 'PURGE' then
1636 Ben_Copy_Entity_Results_Api.DELETE_COPY_ENTITY_RESULTS
1637 (P_COPY_ENTITY_RESULT_ID => L_Epa_Cer_Id,
1638 P_OBJECT_VERSION_NUMBER => L_Epa_Ovn,
1639 P_EFFECTIVE_DATE => Trunc(Sysdate));
1640 Else
1641 Ben_Copy_Entity_Results_Api.UPDATE_COPY_ENTITY_RESULTS
1642 (P_EFFECTIVE_DATE => Trunc(Sysdate),
1643 P_COPY_ENTITY_TXN_ID => p_Copy_Entity_txn_Id,
1644 P_DML_OPERATION => 'DELETE',
1645 P_INFORMATION323 => NULL,
1646 P_COPY_ENTITY_RESULT_ID => L_Epa_Cer_Id,
1647 P_OBJECT_VERSION_NUMBER => L_Epa_Ovn);
1648 End If;
1649 End IF;
1650
1651 l_Dml_opr := NULL;
1652 L_Dml_Opr := Get_Dml_Operation(L_Cep_Cer_Id, 'D');
1653
1654 If L_Dml_Opr = 'PURGE' then
1655 Ben_Copy_Entity_Results_Api.DELETE_COPY_ENTITY_RESULTS
1656 (P_COPY_ENTITY_RESULT_ID => L_Cep_Cer_Id,
1657 P_OBJECT_VERSION_NUMBER => L_Cep_Ovn,
1658 P_EFFECTIVE_DATE => Trunc(Sysdate));
1659 Else
1660 Ben_Copy_Entity_Results_Api.UPDATE_COPY_ENTITY_RESULTS
1661 (P_EFFECTIVE_DATE => Trunc(Sysdate),
1662 P_COPY_ENTITY_TXN_ID => p_Copy_Entity_txn_Id,
1663 P_DML_OPERATION => 'DELETE',
1664 P_INFORMATION323 => NULL,
1665 P_COPY_ENTITY_RESULT_ID => L_Cep_Cer_Id,
1666 P_OBJECT_VERSION_NUMBER => L_Cep_Ovn);
1667 End If;
1668
1669 For Del_Elp_rec in Del_Elp
1670 Loop
1671 Ben_Copy_Entity_Results_Api.DELETE_COPY_ENTITY_RESULTS
1672 (P_COPY_ENTITY_RESULT_ID => Del_Elp_rec.Copy_Entity_result_id,
1673 P_OBJECT_VERSION_NUMBER => Del_Elp_rec.Object_version_number,
1674 P_EFFECTIVE_DATE => Trunc(Sysdate));
1675 End Loop;
1676 End;
1677 procedure pull_elpro_to_stage(p_copy_entity_txn_id in number,
1678 p_eligy_prfl_id in number,
1679 p_effective_date in date,
1680 p_business_group_id in number
1681 )
1682 is
1683
1684 l_proc varchar2(30) := 'pull_elpro_to_stage';
1685 l_parent_cer_id number;
1686 l_cer_ovn number;
1687 l_elp_orignal_in_stage varchar2(1);
1688 l_pull_elpro boolean:=true;
1689
1690 Cursor csr_elp_orignal_in_stage
1691 is
1692 select null
1693 from ben_copy_entity_results
1694 where copy_entity_txn_id = p_copy_entity_txn_id
1695 and table_alias = 'ELP'
1696 and information1 = p_eligy_prfl_id;
1697
1698 begin
1699 hr_utility.set_location('Entering '||l_proc,10);
1700 hr_utility.set_location('p_Copy_Entity_txn_Id '||p_Copy_Entity_txn_Id,10);
1701 hr_utility.set_location('P_Effective_Date'||P_Effective_Date,10);
1702 hr_utility.set_location('p_eligy_prfl_id'||p_eligy_prfl_id,10);
1703 hr_utility.set_location(' P_Business_Group_Id'||P_Business_Group_Id,10);
1704
1705 OPEN csr_elp_orignal_in_stage;
1706 Fetch csr_elp_orignal_in_stage into l_elp_orignal_in_stage;
1707
1708 if csr_elp_orignal_in_stage%FOUND then
1709 l_pull_elpro := false;
1710 else
1711 l_pull_elpro := true;
1712 end if;
1713
1714 close csr_elp_orignal_in_stage;
1715 if l_pull_elpro then
1716 hr_utility.set_location('copying ep hier',20);
1717
1718 ben_plan_design_elpro_module.create_elig_prfl_results
1719 (p_copy_entity_txn_id => p_copy_entity_txn_id
1720 ,p_mirror_src_entity_result_id => l_parent_cer_id
1721 ,p_parent_entity_result_id => l_parent_cer_id
1722 ,p_mndtry_flag => ''
1723 ,p_eligy_prfl_id => p_eligy_prfl_id
1724 ,p_business_group_id => p_business_group_id
1725 ,p_number_of_copies => 1
1726 ,p_object_version_number => l_cer_ovn
1727 ,p_effective_date => p_effective_date
1728 );
1729 hr_utility.set_location('copied ep hier',20);
1730
1731 BEN_PDW_COPY_BEN_TO_STG.populate_extra_mapping_ELP(
1732 p_copy_entity_txn_id => p_copy_entity_txn_id,
1733 p_effective_date => p_effective_date,
1734 p_elig_prfl_id =>p_eligy_prfl_id
1735 );
1736
1737 hr_utility.set_location('Done with the mapping',25);
1738 end if;
1739
1740 -- For elp/child_records which have future records , pdw needs the
1741 -- attribute FUTURE_DATA_EXISTS properly set so that they can properly
1742 -- set the datetrack_mode and dml_operation
1743 -- The following code is copied from BEN_PDW_COPY_BEN_TO_STG.mark_future_data_exists
1744
1745 update ben_copy_entity_results a
1746 set future_data_exists ='Y'
1747 where a.copy_entity_txn_id = p_copy_entity_txn_id
1748 and a.future_data_exists is null
1749 and a.information3 < to_date('4712/12/31','YYYY/MM/DD')
1750 and exists
1751 ( select 'Y' from ben_copy_entity_results b
1752 where b.copy_entity_txn_id = a.copy_entity_txn_id
1753 and b.table_alias = a.table_alias
1754 and b.information1 = a.information1
1755 and b.information2 = a.information3+1);
1756 hr_utility.set_location('Updated bcer records for future_data_exists flag',25);
1757
1758 hr_utility.set_location('Leaving '||l_proc,10);
1759 end pull_elpro_to_stage;
1760
1761 procedure upd_alias_of_dup
1762 (p_copy_entity_txn_id in number,
1763 p_business_group_id in number
1764 )
1765 is
1766 l_rows number;
1767 begin
1768 hr_utility.set_location('Inside upd_alias_of_dup',10);
1769 update ben_copy_entity_results
1770 set table_alias = table_alias||'-DUP'
1771 where copy_entity_txn_id = p_copy_entity_txn_id
1772 and information1 is null
1773 and table_alias in ('ELP'
1774 ,'CGP','EAI','EAN','EAP','EBN','EBU','ECL','ECP','ECQ','ECY'
1775 ,'EDB','EDG','EDI','EDP','EDT','EEG','EEI','EEP','EES','EET'
1776 ,'EFP','EGN','EGR','EHC','EHS','EHW','EJP','ELN','ELR','ELS'
1777 ,'ELU','ELV','ENO','EOM','EOP','EOU','EOY','EPB','EPF','EPG'
1778 ,'EPP','EPS','EPT','EPY','EPZ','EQG','EQT','ERG','ESA','ESH'
1779 ,'ETC','ETD','ETP','ETU','EWL'
1780 ,'ECV'
1781 ,'AGF','CLA','CLF','HWF','LSF','PFF','SVA','RZR','BNG','EGL' );
1782
1783 l_rows:= sql%rowcount ;
1784 hr_utility.set_location('Total-'||l_rows||'-table aliases updated',10);
1785 end upd_alias_of_dup;
1786
1787 procedure reset_alias_of_dup
1788 (p_copy_entity_txn_id in number,
1789 p_business_group_id in number
1790 )
1791 is
1792 l_rows number;
1793 begin
1794 hr_utility.set_location('Inside reset_alias_of_dup',10);
1795 update ben_copy_entity_results
1796 set table_alias = replace(table_alias,'-DUP',null)
1797 where copy_entity_txn_id = p_copy_entity_txn_id
1798 and information1 is null;
1799
1800 l_rows:= sql%rowcount ;
1801 hr_utility.set_location('Total-'||l_rows||'-table aliases updated',10);
1802 end reset_alias_of_dup;
1803
1804 procedure prepare_drv_fctr4pdw (
1805 p_copy_entity_txn_id number,
1806 p_business_group_id in number
1807 )
1808 is
1809 -- Keep only one record of Derieved factor with information1
1810
1811 -- get all Derieved factor records
1812 cursor cur_drv_fctr is
1813 select information101, copy_entity_result_id
1814 from ben_copy_entity_results
1815 where table_alias in ('AGF','CLA','CLF','HWF','LSF','PFF','SVA','RZR','BNG','EGL' )
1816 and copy_entity_txn_id = p_copy_entity_txn_id
1817 and (information1 is null or information1 = information101)
1818 and information101 is not null
1819 order by information101,information1 ;
1820
1821 prev_info101 number ;
1822
1823 begin
1824
1825 prev_info101 := -1 ;
1826
1827 -- for all "non null info101" Derieved factor records
1828 for rec_drv_fctr in cur_drv_fctr
1829 loop
1830 -- update all but one
1831 if (rec_drv_fctr.information101 <> prev_info101)
1832 then
1833
1834 update ben_copy_entity_results
1835 set information1 = information101
1836 where copy_entity_result_id = rec_drv_fctr.copy_entity_result_id;
1837
1838 end if;
1839 prev_info101 := rec_drv_fctr.information101 ;
1840 end loop;
1841
1842 upd_alias_of_dup(p_copy_entity_txn_id, p_business_group_id);
1843
1844 end prepare_drv_fctr4pdw ;
1845
1846 End Pqh_Gsp_PrgRules;