[Home] [Help]
PACKAGE BODY: APPS.PQH_GSP_STAGE_TO_BEN
Source
1 package body pqh_gsp_stage_to_ben as
2 /* $Header: pqgspsbe.pkb 120.9 2006/05/19 22:35:47 hmehta noship $ */
3 function get_scl_name(p_scl_id in number) return varchar2 is
4 l_scl_name varchar2(30);
5 begin
6 select name
7 into l_scl_name
8 from per_parent_spines
9 where parent_spine_id = p_scl_id;
10 return l_scl_name;
11 end get_scl_name;
12 function build_opt_name(p_opt_cer_id in number) return varchar2 is
13 l_scl_id number;
14 l_point_name varchar2(30);
15 l_scl_name varchar2(240);
16 l_opt_name varchar2(240);
17 begin
18 -- opt_name is option name which is combination of point and scale
19 begin
20 select information255,substr(information98,1,30),substr(information5,1,61)
21 into l_scl_id,l_point_name,l_opt_name
22 from ben_copy_entity_results
23 where copy_entity_result_id = p_opt_cer_id;
24 if l_scl_id is not null then
25 l_scl_name := get_scl_name(p_scl_id => l_scl_id);
26 if l_point_name is not null then
27 l_opt_name := substr(l_scl_name,1,30) ||':'|| l_point_name;
28 end if;
29 else
30 hr_utility.set_location('invalid scale id '||l_scl_id,3);
31 l_opt_name := nvl(l_opt_name,l_point_name);
32 end if;
33 exception
34 when no_data_found then
35 l_point_name := '';
36 when others then
37 raise;
38 end;
39 return l_opt_name;
40 exception
41 when others then
42 hr_utility.set_location('issues in building opt name',11);
43 raise;
44 end build_opt_name;
45 procedure elp_writeback(p_crset_id in number,
46 p_elp_id in number,
47 p_copy_entity_txn_id in number) is
48 begin
49 hr_utility.set_location('crset id is '||p_crset_id,20);
50 hr_utility.set_location('cet id is '||p_copy_entity_txn_id,20);
51 hr_utility.set_location('elp id is '||p_elp_id,20);
52 update ben_copy_entity_results
53 set information279 = p_elp_id
54 where copy_entity_txn_id = p_copy_entity_txn_id
55 and table_alias = 'CRRATE'
56 and information160 = p_crset_id;
57 hr_utility.set_location('num of crrs updated'||sql%rowcount,20);
58 end elp_writeback;
59 procedure end_date_crit(p_elig_prfl_id in number,
60 p_crit_type in varchar2,
61 p_effective_date in date) is
62 l_pk number;
63 l_ovn number;
64 l_esd date;
65 l_eed date;
66 begin
67 hr_utility.set_location('crit passed is'||p_crit_type,100);
68 if p_crit_type ='RL' then
69 select eligy_prfl_rl_id,object_version_number
70 into l_pk,l_ovn
71 from BEN_ELIGY_PRFL_RL_F
72 where eligy_prfl_id = p_elig_prfl_id
73 and p_effective_date between effective_start_date and effective_end_date;
74 hr_utility.set_location('pk selected is'||l_pk,100);
75
76 BEN_ELIGY_PROFILE_RULE_API.delete_ELIGY_PROFILE_RULE(
77 p_eligy_prfl_rl_id => l_pk
78 ,p_effective_start_date => l_esd
79 ,p_effective_end_date => l_eed
80 ,p_object_version_number => l_ovn
81 ,p_effective_date => p_effective_date
82 ,p_datetrack_mode => hr_api.g_delete
83 );
84 elsif p_crit_type ='PR' then
85 select elig_perf_rtng_prte_id,object_version_number
86 into l_pk,l_ovn
87 from BEN_ELIG_PERF_RTNG_PRTE_F
88 where eligy_prfl_id = p_elig_prfl_id
89 and p_effective_date between effective_start_date and effective_end_date;
90 hr_utility.set_location('pk selected is'||l_pk,100);
91
92 BEN_ELIG_PERF_RTNG_PRTE_API.delete_ELIG_PERF_RTNG_PRTE(
93 p_elig_perf_rtng_prte_id => l_pk
94 ,p_effective_start_date => l_esd
95 ,p_effective_end_date => l_eed
96 ,p_object_version_number => l_ovn
97 ,p_effective_date => p_effective_date
98 ,p_datetrack_mode => hr_api.g_delete
99 );
100 elsif p_crit_type ='PT' then
101 select elig_per_typ_prte_id,object_version_number
102 into l_pk,l_ovn
103 from BEN_ELIG_PER_TYP_PRTE_F
104 where eligy_prfl_id = p_elig_prfl_id
105 and p_effective_date between effective_start_date and effective_end_date;
106 hr_utility.set_location('pk selected is'||l_pk,100);
107
108 BEN_ELIG_PER_TYP_PRTE_API.delete_ELIG_PER_TYP_PRTE(
109 p_elig_per_typ_prte_id => l_pk
110 ,p_effective_start_date => l_esd
111 ,p_effective_end_date => l_eed
112 ,p_object_version_number => l_ovn
113 ,p_effective_date => p_effective_date
114 ,p_datetrack_mode => hr_api.g_delete
115 );
116 elsif p_crit_type ='FP' then
117 select elig_fl_tm_pt_tm_prte_id,object_version_number
118 into l_pk,l_ovn
119 from BEN_ELIG_FL_TM_PT_TM_PRTE_F
120 where eligy_prfl_id = p_elig_prfl_id
121 and p_effective_date between effective_start_date and effective_end_date;
122 hr_utility.set_location('pk selected is'||l_pk,100);
123
124 BEN_ELIG_FL_TM_PT_TM_PRTE_API.delete_ELIG_FL_TM_PT_TM_PRTE(
125 p_elig_fl_tm_pt_tm_prte_id => l_pk
126 ,p_effective_start_date => l_esd
127 ,p_effective_end_date => l_eed
128 ,p_object_version_number => l_ovn
129 ,p_effective_date => p_effective_date
130 ,p_datetrack_mode => hr_api.g_delete
131 );
132 elsif p_crit_type ='BU' then
133 select elig_brgng_unit_prte_id,object_version_number
134 into l_pk,l_ovn
135 from BEN_ELIG_BRGNG_UNIT_PRTE_F
136 where eligy_prfl_id = p_elig_prfl_id
137 and p_effective_date between effective_start_date and effective_end_date;
138 hr_utility.set_location('pk selected is'||l_pk,100);
139
140 BEN_ELIG_BRGNG_UNIT_PRTE_API.delete_ELIG_BRGNG_UNIT_PRTE(
141 p_elig_brgng_unit_prte_id => l_pk
142 ,p_effective_start_date => l_esd
143 ,p_effective_end_date => l_eed
144 ,p_object_version_number => l_ovn
145 ,p_effective_date => p_effective_date
146 ,p_datetrack_mode => hr_api.g_delete
147 );
148 elsif p_crit_type ='SA' then
149 select elig_svc_area_prte_id,object_version_number
150 into l_pk,l_ovn
151 from BEN_ELIG_SVC_AREA_PRTE_F
152 where eligy_prfl_id = p_elig_prfl_id
153 and p_effective_date between effective_start_date and effective_end_date;
154 hr_utility.set_location('pk selected is'||l_pk,100);
155
156 BEN_ELIG_SVC_AREA_PRTE_API.delete_ELIG_SVC_AREA_PRTE(
157 p_elig_svc_area_prte_id => l_pk
158 ,p_effective_start_date => l_esd
159 ,p_effective_end_date => l_eed
160 ,p_object_version_number => l_ovn
161 ,p_effective_date => p_effective_date
162 ,p_datetrack_mode => hr_api.g_delete
163 );
164 elsif p_crit_type ='LOC' then
165 select elig_wk_loc_prte_id,object_version_number
166 into l_pk,l_ovn
167 from BEN_ELIG_WK_LOC_PRTE_F
168 where eligy_prfl_id = p_elig_prfl_id
169 and p_effective_date between effective_start_date and effective_end_date;
170 hr_utility.set_location('pk selected is'||l_pk,100);
171
172 BEN_ELIG_WK_LOC_PRTE_API.delete_ELIG_WK_LOC_PRTE(
173 p_elig_wk_loc_prte_id => l_pk
174 ,p_effective_start_date => l_esd
175 ,p_effective_end_date => l_eed
176 ,p_object_version_number => l_ovn
177 ,p_effective_date => p_effective_date
178 ,p_datetrack_mode => hr_api.g_delete
179 );
180 elsif p_crit_type ='ORG' then
181 select elig_org_unit_prte_id,object_version_number
182 into l_pk,l_ovn
183 from BEN_ELIG_ORG_UNIT_PRTE_F
184 where eligy_prfl_id = p_elig_prfl_id
185 and p_effective_date between effective_start_date and effective_end_date;
186 hr_utility.set_location('pk selected is'||l_pk,100);
187
188 BEN_ELIG_ORG_UNIT_PRTE_API.delete_ELIG_ORG_UNIT_PRTE(
189 p_elig_org_unit_prte_id => l_pk
190 ,p_effective_start_date => l_esd
191 ,p_effective_end_date => l_eed
192 ,p_object_version_number => l_ovn
193 ,p_effective_date => p_effective_date
194 ,p_datetrack_mode => hr_api.g_delete
195 );
196 elsif p_crit_type ='JOB' then
197 select elig_job_prte_id,object_version_number
198 into l_pk,l_ovn
199 from BEN_ELIG_JOB_PRTE_F
200 where eligy_prfl_id = p_elig_prfl_id
201 and p_effective_date between effective_start_date and effective_end_date;
202 hr_utility.set_location('pk selected is'||l_pk,100);
203
204 BEN_ELIGY_JOB_PRTE_API.delete_ELIGY_JOB_PRTE(
205 p_elig_job_prte_id => l_pk
206 ,p_effective_start_date => l_esd
207 ,p_effective_end_date => l_eed
208 ,p_object_version_number => l_ovn
209 ,p_effective_date => p_effective_date
210 ,p_datetrack_mode => hr_api.g_delete
211 );
212 else
213 hr_utility.set_location('invalid crit passed',100);
214 end if;
215 end end_date_crit;
216 function get_per_typ_cd(P_PERSON_TYPE_ID in number) return varchar2 is
217 l_per_typ_cd varchar2(30);
218 begin
219 select system_person_type
220 into l_per_typ_cd
221 from per_person_types
222 where person_type_id = P_PERSON_TYPE_ID;
223 return l_per_typ_cd;
224 end get_per_typ_cd;
225 function build_vpf_name(p_crset_id in number,
226 p_grade_cer_id in number,
227 p_point_cer_id in number,
228 p_copy_entity_txn_id in number) return varchar2 is
229 cursor csr_crset is
230 select substr(information151,1,150)
231 from ben_copy_entity_results
232 where copy_entity_txn_id = p_copy_entity_txn_id
233 and table_alias = 'CRSET'
234 and information161 = p_crset_id
235 order by information2;
236 l_crset_name varchar2(240);
237 l_vpf_name varchar2(240);
238 l_grd_name varchar2(80);
239 l_opt_name varchar2(80);
240 begin
241 if p_crset_id is not null then
242 open csr_crset;
243 fetch csr_crset into l_crset_name;
244 if csr_crset%notfound then
245 close csr_crset;
246 hr_utility.set_location('criteria set doesnot exist',10);
247 else
248 close csr_crset;
249 end if;
250 else
251 hr_utility.set_location('crset passed is null',11);
252 return l_vpf_name;
253 end if;
254 if p_grade_cer_id is not null then
255 hr_utility.set_location('grd short name to be pulled',11);
256 begin
257 select substr(information102,1,30)
258 into l_grd_name
259 from ben_copy_entity_results
260 where copy_entity_result_id = p_grade_cer_id;
261 exception
262 when others then
263 hr_utility.set_location('issue in selecting grd_name',11);
264 raise;
265 end;
266 l_vpf_name := l_grd_name ||'-'||l_crset_name;
267 return l_vpf_name;
268 elsif p_point_cer_id is not null then
269 hr_utility.set_location('opt name to be pulled',11);
270 l_opt_name := build_opt_name(p_opt_cer_id => p_point_cer_id);
271 l_vpf_name := l_opt_name ||'-'||l_crset_name;
272 return l_vpf_name;
273 else
274 hr_utility.set_location('grd and pnt cer null',11);
275 return l_vpf_name;
276 end if;
277 end build_vpf_name;
278 function get_ovn(p_table_name in varchar2,
279 p_key_column_name in varchar2,
280 p_key_column_value in number,
281 p_effective_date in date default null) return number is
282 query_str varchar2(2000);
283 l_ovn number;
284 begin
285 query_str := 'select object_version_number from '
286 || p_table_name
287 || ' where '
288 || p_key_column_name || '= :key_column_value' ;
289 hr_utility.set_location('query is '||substr(query_str,1,50),10);
290 hr_utility.set_location('query1 is '||substr(query_str,51,50),10);
291 if p_effective_date is not null then
292 query_str := query_str || ' and :effective_date'
293 || ' between effective_start_date and effective_end_date';
294 hr_utility.set_location('query1 is '||substr(query_str,51,50),10);
295 EXECUTE IMMEDIATE query_str
296 INTO l_ovn
297 USING p_key_column_value, p_effective_date;
298 else
299 EXECUTE IMMEDIATE query_str
300 INTO l_ovn
301 USING p_key_column_value;
302 end if;
303 return l_ovn;
304 end get_ovn;
305 function get_update_mode(p_table_name varchar2,
306 p_key_column_name varchar2,
307 p_key_column_value number,
308 p_effective_date in date) return varchar2 is
309 query_str varchar2(2000);
310 l_dt_mode varchar2(30);
311 l_min_esd date;
312 begin
313 query_str := 'select min(effective_start_date) from '
314 || p_table_name
315 || ' where '
316 || p_key_column_name || '= :key_column_value'
317 || ' and effective_start_date >= :effective_date';
318
319 EXECUTE IMMEDIATE query_str
320 INTO l_min_esd
321 USING p_key_column_value, p_effective_date;
322
323 if l_min_esd is null then
324 -- we r working on last row
325 l_dt_mode := 'UPDATE';
326 elsif l_min_esd > p_effective_date then
327 -- future row exist
328 l_dt_mode := 'UPDATE_OVERRIDE';
329 elsif l_min_esd = p_effective_date then
330 -- row was created as of today
331 l_dt_mode := 'CORRECTION';
332 end if;
333 return l_dt_mode;
334 end get_update_mode;
335 function is_oipl_exists(p_effective_date in date,
336 p_pl_id in number,
337 p_opt_id in number) return number is
338 l_oipl_id number;
339 begin
340 hr_utility.set_location('opt id is '||p_opt_id,1);
341 hr_utility.set_location('pl id is '||p_pl_id,2);
342 select oipl_id
343 into l_oipl_id
344 from ben_oipl_f
345 where pl_id = p_pl_id
346 and opt_id = p_opt_id
347 and p_effective_date between effective_start_date and effective_end_date;
348 hr_utility.set_location('oipl is '||l_oipl_id,3);
349 return l_oipl_id;
350 exception
351 when no_data_found then
352 hr_utility.set_location('oipl doesnot exist ',3);
353 return l_oipl_id;
354 when others then
355 hr_utility.set_location('issues in getting oipl ',4);
356 raise;
357 end is_oipl_exists;
358 function get_gsp_pt(p_business_group_id in number,
359 p_effective_date in date) return number is
360 l_pt_id number;
361 cursor c1 is
362 select pl_typ_id
363 from ben_pl_typ_f
364 where opt_typ_cd ='GSP'
365 and business_group_id = p_business_group_id
366 and pl_typ_stat_cd ='A'
367 and p_effective_date between effective_start_date and effective_end_date;
368 begin
369 open c1;
370 fetch c1 into l_pt_id;
371 if c1%notfound then
372 close c1;
373 hr_utility.set_location('pl_typ not defined ',4);
374 else
375 close c1;
376 end if;
377 return l_pt_id;
378 end get_gsp_pt;
379 procedure plip_writeback(p_copy_entity_txn_id in number,
380 p_plip_id in number,
381 p_pl_id in number,
382 p_plip_cer_id in number) is
383 begin
384 hr_utility.set_location('plip id is '||p_plip_id,1);
385 hr_utility.set_location('pl id is '||p_pl_id,1);
386 hr_utility.set_location('plip cer id is '||p_plip_cer_id,2);
387 hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
388 -- update plip row with plip id
389 begin
390 hr_utility.set_location('updating oipl for pl:'||p_pl_id,4);
391 update ben_copy_entity_results
392 set information1 = p_plip_id
393 where copy_entity_result_id = p_plip_cer_id;
394 hr_utility.set_location('num of plips updated'||sql%rowcount,20);
395 exception
396 when others then
397 hr_utility.set_location('issues in updating plip ',10);
398 raise;
399 end;
400 -- update oipl rows with pl id
401 begin
402 hr_utility.set_location('updating oipl for pl:'||p_pl_id,4);
403 update ben_copy_entity_results
404 set information261 = p_pl_id
405 where gs_parent_entity_result_id = p_plip_cer_id
406 and table_alias ='COP'
407 and copy_entity_txn_id = p_copy_entity_txn_id;
408 hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
409 exception
410 when others then
411 hr_utility.set_location('issues in updating plip ',10);
412 raise;
413 end;
414 -- update epa rows with plip id
415 begin
416 hr_utility.set_location('updating epa for plip:'||p_plip_id,4);
417 update ben_copy_entity_results
418 set information1 = p_plip_id
419 where copy_entity_result_id = p_plip_cer_id;
420 hr_utility.set_location('num of epas updated'||sql%rowcount,20);
421 exception
422 when others then
423 hr_utility.set_location('issues in updating plip ',10);
424 raise;
425 end;
426 hr_utility.set_location('plip writeback comp ',5);
427 end plip_writeback;
428 procedure oipl_writeback(p_copy_entity_txn_id in number,
429 p_oipl_id in number,
430 p_oipl_cer_id in number) is
431 begin
432 hr_utility.set_location('oipl id is '||p_oipl_id,1);
433 hr_utility.set_location('oipl cer id is '||p_oipl_cer_id,2);
434 hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
435 begin
436 hr_utility.set_location('updating oipl:'||p_oipl_id,4);
437 update ben_copy_entity_results
438 set information1 = p_oipl_id
439 where copy_entity_result_id = p_oipl_cer_id;
440 hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
441 exception
442 when others then
443 hr_utility.set_location('issues in updating oipl ',10);
444 raise;
445 end;
446 hr_utility.set_location('oipl writeback comp ',5);
447 end oipl_writeback;
448 procedure opt_writeback(p_copy_entity_txn_id in number,
449 p_opt_id in number,
450 p_opt_name in varchar2,
451 p_opt_cer_id in number) is
452 begin
453 hr_utility.set_location('opt id is '||p_opt_id,1);
454 hr_utility.set_location('opt cer id is '||p_opt_cer_id,2);
455 hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
456 -- update oipl rows with opt id
457 begin
458 -- oipl row is updated with opt id
459 hr_utility.set_location('updating oipl for opt :'||p_opt_id,4);
460 update ben_copy_entity_results
461 set information247 = p_opt_id
462 where copy_entity_txn_id = p_copy_entity_txn_id
463 and table_alias = 'COP'
464 and information262 = p_opt_cer_id;
465 hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
466 exception
467 when others then
468 hr_utility.set_location('issues in updating opt to oipl',10);
469 raise;
470 end;
471 -- update abr rows with opt id
472 begin
473 hr_utility.set_location('updating abr for opt:'||p_opt_id,4);
474 update ben_copy_entity_results
475 set information247 = p_opt_id,
476 information170 = p_opt_name
477 where copy_entity_txn_id = p_copy_entity_txn_id
478 and table_alias = 'ABR'
479 and information278 = p_opt_cer_id;
480 hr_utility.set_location('num of opts updated'||sql%rowcount,20);
481 exception
482 when others then
483 hr_utility.set_location('issues in updating opt to abr',10);
484 raise;
485 end;
486 hr_utility.set_location('opt writeback comp ',5);
487 end opt_writeback;
488 procedure pln_writeback(p_copy_entity_txn_id in number,
489 p_pl_id in number,
490 p_pl_name in varchar2,
491 p_pl_cer_id in number,
492 p_plip_cer_id in number) is
493 begin
494 hr_utility.set_location('pln id is '||p_pl_id,1);
495 hr_utility.set_location('pln cer id is '||p_pl_cer_id,2);
496 hr_utility.set_location('cet id is '||p_copy_entity_txn_id,3);
497 -- update plip rows with plan id
498 begin
499 -- plip row is updated with pl id
500 hr_utility.set_location('updating plips for pl :'||p_pl_id,4);
501 update ben_copy_entity_results
502 set information261 = p_pl_id
503 where copy_entity_txn_id = p_copy_entity_txn_id
504 and table_alias = 'CPP'
505 and copy_entity_result_id = p_plip_cer_id;
506 hr_utility.set_location('num of plips updated'||sql%rowcount,20);
507 exception
508 when others then
509 hr_utility.set_location('issues in updating pl to plip',10);
510 raise;
511 end;
512 -- update abr rows with pl id
513 begin
514 hr_utility.set_location('updating abr for pl:'||p_pl_id,4);
515 update ben_copy_entity_results
516 set information261 = p_pl_id,
517 information170 = p_pl_name
518 where copy_entity_txn_id = p_copy_entity_txn_id
519 and table_alias = 'ABR'
520 and information277 = p_pl_cer_id;
521 hr_utility.set_location('num of abrs updated'||sql%rowcount,20);
522 exception
523 when others then
524 hr_utility.set_location('issues in updating pl to abr',10);
525 raise;
526 end;
527 -- update oipl rows with plan id
528 begin
529 -- oipl row is updated with pl id
530 hr_utility.set_location('updating oipls for pl :'||p_pl_id,4);
531 update ben_copy_entity_results
532 set information261 = p_pl_id
533 where copy_entity_txn_id = p_copy_entity_txn_id
534 and table_alias = 'COP'
535 and gs_parent_entity_result_id = p_plip_cer_id;
536 hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
537 exception
538 when others then
539 hr_utility.set_location('issues in updating pl to oipl',10);
540 raise;
541 end;
542 -- update pln row
543 begin
544 update ben_copy_entity_results
545 set information1 = p_pl_id
546 where copy_entity_result_id = p_pl_cer_id;
547 hr_utility.set_location('num of plans updated'||sql%rowcount,20);
548 exception
549 when others then
550 hr_utility.set_location('issues in updating pln ',10);
551 raise;
552 end;
553 hr_utility.set_location('pln writeback comp ',5);
554 end pln_writeback;
555 procedure pgm_writeback(p_copy_entity_txn_id in number,
556 p_pgm_id in number,
557 p_pgm_cer_id in number,
558 p_ptip_id in number) is
559 begin
560 hr_utility.set_location('pgm id is '||p_pgm_id,1);
561 hr_utility.set_location('pgm cer id is '||p_pgm_cer_id,2);
562 hr_utility.set_location('ptip id is '||p_ptip_id,3);
563 hr_utility.set_location('cet id is '||p_copy_entity_txn_id,4);
564 -- update plip rows with program id
565 begin
566 -- plip row is updated with pgm id
567 hr_utility.set_location('updating plips for pgm :'||p_pgm_id,4);
568 update ben_copy_entity_results
569 set information260 = p_pgm_id
570 where copy_entity_txn_id = p_copy_entity_txn_id
571 and table_alias = 'CPP';
572 hr_utility.set_location('num of plips updated'||sql%rowcount,20);
573 exception
574 when others then
575 hr_utility.set_location('issues in updating pgm to plip',10);
576 raise;
577 end;
578 -- update cpd rows with program id
579 begin
580 -- cpd row is updated with pgm id
581 hr_utility.set_location('updating cpd for pgm :'||p_pgm_id,4);
582 update ben_copy_entity_results
583 set information260 = p_pgm_id
584 where copy_entity_txn_id = p_copy_entity_txn_id
585 and table_alias = 'CPD';
586 hr_utility.set_location('num of cpd updated'||sql%rowcount,20);
587 exception
588 when others then
589 hr_utility.set_location('issues in updating pgm to cpd',10);
590 raise;
591 end;
592 -- update pgm row for information1
593 begin
594 update ben_copy_entity_results
595 set information1 = p_pgm_id
596 where copy_entity_result_id = p_pgm_cer_id;
597 hr_utility.set_location('num of pgms updated'||sql%rowcount,20);
598 exception
599 when others then
600 hr_utility.set_location('issues in updating pgm ',10);
601 raise;
602 end;
603 hr_utility.set_location('pgm and ptip writeback comp ',5);
604 end pgm_writeback;
605 function get_gsp_le(p_oper_code in varchar2,
606 p_bg_id in number,
607 p_effective_date in date) return number is
608 l_ler_id number;
609 begin
610 select ler_id
611 into l_ler_id
612 from ben_ler_f
613 where typ_cd = 'GSP'
614 and lf_evt_oper_cd = p_oper_code
615 and business_group_id = p_bg_id
616 and p_effective_date between effective_start_date and effective_end_date;
617 return l_ler_id;
618 exception
619 when others then
620 hr_utility.set_location('issues in selecting ler',2);
621 raise;
622 end get_gsp_le;
623 procedure pgm_enrl(p_pgm_id in number,
624 p_bg_id in number,
625 p_effective_date in date,
626 p_pet_id out nocopy number) is
627 l_ovn number;
628 l_esd date;
629 l_eed date;
630 l_pet_id number;
631 begin
632 hr_utility.set_location('creating pet for pgm'||p_pgm_id,1);
633 hr_utility.set_location(' BEN_POPL_ENRT_TYP_CYCL_F CREATE_POPL_ENRT_TYP_CYCL ',20);
634 BEN_POPL_ENRT_TYP_CYCL_API.CREATE_POPL_ENRT_TYP_CYCL(
635 P_EFFECTIVE_DATE => p_effective_date
636 ,P_BUSINESS_GROUP_ID => p_bg_id
637 ,P_ENRT_TYP_CYCL_CD => 'L'
638 ,P_PGM_ID => p_PGM_ID
639 ,P_POPL_ENRT_TYP_CYCL_ID => l_pet_id
640 ,P_EFFECTIVE_START_DATE => l_esd
641 ,P_EFFECTIVE_END_DATE => l_eed
642 ,P_OBJECT_VERSION_NUMBER => l_ovn
643 );
644 hr_utility.set_location('After per insert ',2);
645 p_pet_id := l_pet_id;
646 end pgm_enrl;
647 procedure upd_pgm_le(p_pgm_id in number,
648 p_cet_id in number,
649 p_effective_date in date,
650 p_bg_id in number,
651 p_pro_cvg_st_dt in varchar2,
652 p_pro_rt_st_dt in varchar2,
653 p_syn_rt_st_dt in varchar2) is
654 l_pet_id number;
655 l_ler_id number;
656 begin
657 hr_utility.set_location('updating le_enrl for pgm'||p_pgm_id,1);
658 begin
659 select popl_enrt_typ_cycl_id
660 into l_pet_id
661 from ben_popl_enrt_typ_cycl_f
662 where pgm_id = p_pgm_id
663 and p_effective_date between effective_start_date and effective_end_date;
664 hr_utility.set_location('pet is'||l_pet_id,2);
665 exception
666 when others then
667 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
668 (P_MASTER_TXN_ID => p_cet_id,
669 P_TXN_ID => p_cet_id,
670 P_MODULE_CD => 'PQH_GSP_STGBEN',
671 p_context => 'UPD_LE_sel_pet',
672 P_MESSAGE_TYPE_CD => 'E',
673 P_MESSAGE_TEXT => 'UPD_LE',
674 p_effective_date => p_effective_date);
675 raise;
676 end;
677 l_ler_id := get_gsp_le (p_bg_id => p_bg_id,
678 p_effective_date => p_effective_date,
679 p_oper_code => 'PROG');
680 hr_utility.set_location('prog le is'||l_ler_id,2);
681 if l_ler_id is not null then
682 begin
683 update ben_lee_rsn_f
684 set ENRT_CVG_STRT_DT_CD = p_pro_cvg_st_dt,
685 RT_STRT_DT_CD = p_pro_rt_st_dt
686 where POPL_ENRT_TYP_CYCL_ID = l_pet_id
687 and ler_id = l_ler_id
688 and p_effective_date between effective_start_date and effective_end_date;
689 hr_utility.set_location('prog le enrl updated ',4);
690 exception
691 when others then
692 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
693 (P_MASTER_TXN_ID => p_cet_id,
694 P_TXN_ID => p_cet_id,
695 P_MODULE_CD => 'PQH_GSP_STGBEN',
696 p_context => 'UPD_LE',
697 P_MESSAGE_TYPE_CD => 'E',
698 P_MESSAGE_TEXT => 'UPD_LE',
699 p_effective_date => p_effective_date);
700 raise;
701 end;
702 end if;
703 l_ler_id := get_gsp_le (p_bg_id => p_bg_id,
704 p_effective_date => p_effective_date,
705 p_oper_code => 'SYNC');
706 hr_utility.set_location('sync le is'||l_ler_id,2);
707 if l_ler_id is not null then
708 begin
709 update ben_lee_rsn_f
710 set ENRT_CVG_STRT_DT_CD = p_syn_rt_st_dt,
711 RT_STRT_DT_CD = p_syn_rt_st_dt
712 where POPL_ENRT_TYP_CYCL_ID = l_pet_id
713 and ler_id = l_ler_id
714 and p_effective_date between effective_start_date and effective_end_date;
715 exception
716 when others then
717 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
718 (P_MASTER_TXN_ID => p_cet_id,
719 P_TXN_ID => p_cet_id,
720 P_MODULE_CD => 'PQH_GSP_STGBEN',
721 p_context => 'UPD_LE',
722 P_MESSAGE_TYPE_CD => 'E',
723 P_MESSAGE_TEXT => 'UPD_LE2',
724 p_effective_date => p_effective_date);
725 raise;
726 end;
727 end if;
728 end upd_pgm_le;
729 procedure pgm_le(p_pgm_id in number,
730 p_bg_id in number,
731 p_effective_date in date,
732 p_pet_id in number,
733 p_pro_cvg_st_dt in varchar2,
734 p_pro_rt_st_dt in varchar2,
735 p_syn_rt_st_dt in varchar2,
736 p_lee_rsn_id out nocopy number) is
737 l_ovn number;
738 l_esd date;
739 l_eed date;
740 l_ler_id number;
741 l_lee_rsn_id number;
742 begin
743 hr_utility.set_location('creating pet for pgm'||p_pgm_id,1);
744 l_ler_id := get_gsp_le (p_bg_id => p_bg_id,
745 p_effective_date => p_effective_date,
746 p_oper_code => 'PROG');
747 if l_ler_id is not null and p_pet_id is not null then
748 hr_utility.set_location(' BEN_LEE_RSN_F CREATE_LIFE_EVENT_ENROLL_RSN ',20);
749 BEN_LIFE_EVENT_ENROLL_RSN_API.CREATE_LIFE_EVENT_ENROLL_RSN(
750 P_EFFECTIVE_DATE => p_effective_date
751 ,P_BUSINESS_GROUP_ID => p_bg_id
752 ,P_LEE_RSN_ID => p_lee_rsn_id
753 ,P_LER_ID => l_ler_id
754 ,P_POPL_ENRT_TYP_CYCL_ID => p_pet_id
755 ,P_EFFECTIVE_START_DATE => l_esd
756 ,P_EFFECTIVE_END_DATE => l_eed
757 ,P_OBJECT_VERSION_NUMBER => l_ovn
758 ,P_CLS_ENRT_DT_TO_USE_CD => 'ELCNSMADE'
759 ,P_ENRT_CVG_END_DT_CD => 'ODBED'
760 ,P_ENRT_CVG_STRT_DT_CD => p_pro_cvg_st_dt
761 ,P_ENRT_PERD_END_DT_CD => 'ALDCPPY'
762 ,P_ENRT_PERD_STRT_DT_CD => 'AED'
763 ,P_RT_END_DT_CD => 'ODBED'
764 ,P_RT_STRT_DT_CD => p_pro_rt_st_dt
765 );
766 end if;
767 l_ler_id := get_gsp_le (p_bg_id => p_bg_id,
768 p_effective_date => p_effective_date,
769 p_oper_code => 'SYNC');
770 if l_ler_id is not null and p_pet_id is not null then
771 hr_utility.set_location(' BEN_LEE_RSN_F CREATE_LIFE_EVENT_ENROLL_RSN ',20);
772 BEN_LIFE_EVENT_ENROLL_RSN_API.CREATE_LIFE_EVENT_ENROLL_RSN(
773 P_EFFECTIVE_DATE => p_effective_date
774 ,P_BUSINESS_GROUP_ID => p_bg_id
775 ,P_LEE_RSN_ID => p_lee_rsn_id
776 ,P_LER_ID => l_ler_id
777 ,P_POPL_ENRT_TYP_CYCL_ID => p_pet_id
778 ,P_EFFECTIVE_START_DATE => l_esd
779 ,P_EFFECTIVE_END_DATE => l_eed
780 ,P_OBJECT_VERSION_NUMBER => l_ovn
781 ,P_CLS_ENRT_DT_TO_USE_CD => 'ELCNSMADE'
782 ,P_ENRT_CVG_END_DT_CD => 'ODBED'
783 ,P_ENRT_CVG_STRT_DT_CD => p_syn_rt_st_dt
784 ,P_ENRT_PERD_END_DT_CD => 'ALDCPPY'
785 ,P_ENRT_PERD_STRT_DT_CD => 'AED'
786 ,P_RT_END_DT_CD => 'ODBED'
787 ,P_RT_STRT_DT_CD => p_syn_rt_st_dt
788 );
789 end if;
790 end pgm_le;
791 procedure create_ptip(p_pgm_id in number,
792 p_pl_typ_id in number,
793 p_bg_id in number,
794 p_effective_date in date,
795 p_ptip_id out nocopy number) is
796 l_ovn number;
797 l_esd date;
798 l_eed date;
799 l_ptip_id number;
800 begin
801 hr_utility.set_location('creating ptip for pgm'||p_pgm_id,1);
802 hr_utility.set_location('pl_typ'||p_pl_typ_id,2);
803 BEN_PLAN_TYPE_IN_PROGRAM_API.CREATE_PLAN_TYPE_IN_PROGRAM(
804 P_EFFECTIVE_DATE => p_effective_date
805 ,P_BUSINESS_GROUP_ID => p_bg_id
806 ,P_PGM_ID => p_PGM_ID
807 ,P_PL_TYP_ID => p_PL_TYP_ID
808 ,P_PTIP_ID => l_ptip_id
809 ,P_PTIP_STAT_CD => 'A'
810 ,P_EFFECTIVE_START_DATE => l_esd
811 ,P_EFFECTIVE_END_DATE => l_eed
812 ,P_OBJECT_VERSION_NUMBER => l_ovn
813 ,P_ORDR_NUM => 1
814 /*
815 ,P_COORD_CVG_FOR_ALL_PLS_FLAG => 'N'
816 ,P_CRS_THIS_PL_TYP_ONLY_FLAG => 'N'
817 ,P_DPNT_ADRS_RQD_FLAG => 'N'
818 ,P_DPNT_CVG_NO_CTFN_RQD_FLAG => 'N'
819 ,P_DPNT_DOB_RQD_FLAG => 'N'
820 ,P_DPNT_LEGV_ID_RQD_FLAG => 'N'
821 ,P_DRVBL_FCTR_APLS_RTS_FLAG => 'N'
822 ,P_DRVBL_FCTR_PRTN_ELIG_FLAG => 'N'
823 ,P_DRVD_FCTR_DPNT_CVG_FLAG => 'N'
824 ,P_ELIG_APLS_FLAG => 'N'
825 ,P_NO_MN_PL_TYP_OVERID_FLAG => 'N'
826 ,P_NO_MX_PL_TYP_OVRID_FLAG => 'N'
827 ,P_PRTN_ELIG_OVRID_ALWD_FLAG => 'N'
828 ,P_PRVDS_CR_FLAG => 'N'
829 ,P_SBJ_TO_DPNT_LF_INS_MX_FLAG => 'N'
830 ,P_SBJ_TO_SPS_LF_INS_MX_FLAG => 'N'
831 ,P_TRK_INELIG_PER_FLAG => 'N'
832 ,P_USE_TO_SUM_EE_LF_INS_FLAG => 'N'
833 ,P_WVBL_FLAG => 'N'
834 ,P_ACRS_PTIP_CVG_ID => l_ACRS_PTIP_CVG_ID
835 ,P_AUTO_ENRT_MTHD_RL => l_AUTO_ENRT_MTHD_RL
836 ,P_CMBN_PTIP_ID => l_CMBN_PTIP_ID
837 ,P_CMBN_PTIP_OPT_ID => l_CMBN_PTIP_OPT_ID
838 ,P_DFLT_ENRT_CD => r_CTP.INFORMATION45
839 ,P_DFLT_ENRT_DET_RL => l_DFLT_ENRT_DET_RL
840 ,P_DPNT_CVG_END_DT_CD => r_CTP.INFORMATION36
841 ,P_DPNT_CVG_END_DT_RL => l_DPNT_CVG_END_DT_RL
842 ,P_DPNT_CVG_STRT_DT_CD => r_CTP.INFORMATION35
843 ,P_DPNT_CVG_STRT_DT_RL => l_DPNT_CVG_STRT_DT_RL
844 ,P_DPNT_DSGN_CD => r_CTP.INFORMATION34
845 ,P_ENRT_CD => r_CTP.INFORMATION44
846 ,P_ENRT_CVG_END_DT_CD => r_CTP.INFORMATION40
847 ,P_ENRT_CVG_END_DT_RL => l_ENRT_CVG_END_DT_RL
848 ,P_ENRT_CVG_STRT_DT_CD => r_CTP.INFORMATION39
849 ,P_ENRT_CVG_STRT_DT_RL => l_ENRT_CVG_STRT_DT_RL
850 ,P_ENRT_MTHD_CD => r_CTP.INFORMATION43
851 ,P_ENRT_RL => l_ENRT_RL
852 ,P_IVR_IDENT => r_CTP.INFORMATION141
853 ,P_MN_ENRD_RQD_OVRID_NUM => r_CTP.INFORMATION266
854 ,P_MX_CVG_ALWD_AMT => r_CTP.INFORMATION293
855 ,P_MX_ENRD_ALWD_OVRID_NUM => r_CTP.INFORMATION267
856 ,P_PER_CVRD_CD => r_CTP.INFORMATION11
857 ,P_POSTELCN_EDIT_RL => l_POSTELCN_EDIT_RL
858 ,P_RQD_ENRT_PERD_TCO_CD => r_CTP.INFORMATION38
859 ,P_RQD_PERD_ENRT_NENRT_RL => l_RQD_PERD_ENRT_NENRT_RL
860 ,P_RQD_PERD_ENRT_NENRT_TM_UOM => r_CTP.INFORMATION37
861 ,P_RQD_PERD_ENRT_NENRT_VAL => r_CTP.INFORMATION287
862 ,P_RT_END_DT_CD => r_CTP.INFORMATION42
863 ,P_RT_END_DT_RL => l_RT_END_DT_RL
864 ,P_RT_STRT_DT_CD => r_CTP.INFORMATION41
865 ,P_RT_STRT_DT_RL => l_RT_STRT_DT_RL
866 ,P_URL_REF_NAME => r_CTP.INFORMATION185
867 ,P_VRFY_FMLY_MMBR_CD => r_CTP.INFORMATION46
868 ,P_VRFY_FMLY_MMBR_RL => l_VRFY_FMLY_MMBR_RL
869 ,P_SHORT_CODE => r_CTP.INFORMATION12
870 ,P_SHORT_NAME => r_CTP.INFORMATION13
871 */
872 );
873 p_ptip_id := l_ptip_id;
874 hr_utility.set_location('ptip is'||p_ptip_id,3);
875 end create_ptip;
876
877 procedure stage_to_pgi(p_copy_entity_txn_id in number,
878 p_business_group_id in number,
879 p_effective_date in date
880 ) is
881 cursor c_pgi is
882 select *
883 from ben_copy_entity_results
884 where copy_entity_txn_id = p_copy_entity_txn_id
885 and table_alias = 'PGI'
886 and dml_operation in ('INSERT','UPDATE') ;-- only insert/ updates should be there
887 --
888 r_pgi c_pgi%rowtype;
889
890 l_peit_ovn number;
891 l_pgm_extra_info_id number;
892 l_pgm_id NUMBER;
893 l_pgm_esd DATE;
894 l_pgm_name VARCHAR2 (240);
895 begin
896 hr_utility.set_location('inside pgm_extra_info_update',10);
897
898 BEGIN
899 SELECT information1, information5, information2
900 INTO l_pgm_id, l_pgm_name, l_pgm_esd
901 FROM ben_copy_entity_results
902 WHERE copy_entity_txn_id = p_copy_entity_txn_id
903 and table_alias = 'PGM'
904 and result_type_cd='DISPLAY';
905 EXCEPTION
906 WHEN OTHERS
907 THEN
908 hr_utility.set_location ('issues in getting pgm name', 10);
909 RAISE;
910 END;
911
912 open c_pgi;
913 loop
914 fetch c_pgi into r_pgi;
915 exit when c_pgi%notfound;
916
917
918 if r_pgi.information174 is null then
919 hr_utility.set_location('insert pgm extra info ',10);
920 ben_pgm_extra_info_api.create_pgm_extra_info
921 ( p_pgm_id => l_pgm_id
922 ,p_information_type => 'PQH_GSP_EXTRA_INFO'
923 ,p_pgi_information_category => 'PQH_GSP_EXTRA_INFO'
924 ,p_pgi_information1 => r_pgi.information11
925 ,p_pgi_information2 => r_pgi.information12
926 ,p_pgi_information3 => r_pgi.information13
927 ,p_pgi_information4 => r_pgi.information14
928 ,p_pgm_extra_info_id => l_pgm_extra_info_id
929 ,p_object_version_number => l_peit_ovn
930 );
931 else
932 hr_utility.set_location('update pgm extra info',10);
933 l_peit_ovn := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'BEN_PGM_EXTRA_INFO',
934 p_key_column_name => 'PGM_EXTRA_INFO_ID',
935 p_key_column_value => r_pgi.information174);
936 hr_utility.set_location(' ovn is '||l_peit_ovn,30);
937 ben_pgm_extra_info_api.update_pgm_extra_info
938 ( p_pgm_extra_info_id => r_pgi.information174
939 ,p_object_version_number => l_peit_ovn
940 ,p_pgi_information1 => r_pgi.information11
941 ,p_pgi_information2 => r_pgi.information12
942 ,p_pgi_information3 => r_pgi.information13
943 ,p_pgi_information4 => r_pgi.information14
944 );
945 end if;
946 end loop;
947 close c_pgi;
948 hr_utility.set_location('leaving pgm_extra_info_update',10);
949 exception
950 when others then
951 raise;
952 end stage_to_pgi;
953
954 procedure stage_to_ben(p_copy_entity_txn_id in number,
955 p_effective_date in date,
956 p_business_group_id in number,
957 p_datetrack_mode in varchar2,
958 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
959 l_proc varchar2(61) := 'stage_to_ben' ;
960 l_effective_date date := p_effective_date;
961 l_pl_typ_id number;
962 /*
963 order of writing data should be
964 1) Eligibility profile
965 2) elig criteria
966 3) options
967 4) plans
968 5) program and ptip and LE linkage
969 6) oipl
970 7) plip
971 8) abr
972 9) var
973 10) elig prof linkage with objects
974 */
975 begin
976 hr_utility.set_location('inside '||l_proc,10);
977 l_pl_typ_id := get_gsp_pt(p_business_group_id => p_business_group_id,
978 p_effective_date => p_effective_date);
979 hr_utility.set_location('pl typ is '||l_pl_typ_id,1);
980 stage_to_elp(p_copy_entity_txn_id => p_copy_entity_txn_id,
981 p_effective_date => l_effective_date,
982 p_business_group_id => p_business_group_id,
983 p_datetrack_mode => p_datetrack_mode);
984 hr_utility.set_location('elp row update',30);
985 stage_to_opt(p_copy_entity_txn_id => p_copy_entity_txn_id,
986 p_effective_date => l_effective_date,
987 p_business_group_id => p_business_group_id,
988 p_pl_typ_id => l_pl_typ_id,
989 p_datetrack_mode => p_datetrack_mode);
990 hr_utility.set_location('opt row updated',40);
991 stage_to_plan(p_copy_entity_txn_id => p_copy_entity_txn_id,
992 p_effective_date => l_effective_date,
993 p_business_group_id => p_business_group_id,
994 p_pl_typ_id => l_pl_typ_id,
995 p_datetrack_mode => p_datetrack_mode);
996 hr_utility.set_location('plan row updated',50);
997 stage_to_pgm(p_copy_entity_txn_id => p_copy_entity_txn_id,
998 p_effective_date => l_effective_date,
999 p_business_group_id => p_business_group_id,
1000 p_pl_typ_id => l_pl_typ_id,
1001 p_datetrack_mode => p_datetrack_mode);
1002 hr_utility.set_location('pgm row updated',60);
1003 stage_to_pgi(p_copy_entity_txn_id => p_copy_entity_txn_id,
1004 p_business_group_id => p_business_group_id,
1005 p_effective_date => l_effective_date
1006 ) ;
1007 hr_utility.set_location('pgi row updated',70);
1008 if p_business_area ='PQH_CORPS_TASK_LIST' then
1009 pqh_cpd_hr_to_stage.stage_to_corps(p_copy_entity_txn_id => p_copy_entity_txn_id,
1010 p_effective_date => l_effective_date,
1011 p_business_group_id => p_business_group_id,
1012 p_datetrack_mode => p_datetrack_mode);
1013 hr_utility.set_location('cpd row updated',60);
1014 end if;
1015 stage_to_oipl(p_copy_entity_txn_id => p_copy_entity_txn_id,
1016 p_effective_date => l_effective_date,
1017 p_business_group_id => p_business_group_id,
1018 p_datetrack_mode => p_datetrack_mode);
1019 hr_utility.set_location('oipl row updated',70);
1020 stage_to_plip(p_copy_entity_txn_id => p_copy_entity_txn_id,
1021 p_effective_date => l_effective_date,
1022 p_business_group_id => p_business_group_id,
1023 p_datetrack_mode => p_datetrack_mode,
1024 p_business_area => p_business_area);
1025 hr_utility.set_location('plip row updated',70);
1026 stage_to_abr(p_copy_entity_txn_id => p_copy_entity_txn_id,
1027 p_effective_date => l_effective_date,
1028 p_business_group_id => p_business_group_id,
1029 p_datetrack_mode => p_datetrack_mode);
1030 hr_utility.set_location('abr row updated',70);
1031 stage_to_vpf(p_copy_entity_txn_id => p_copy_entity_txn_id,
1032 p_effective_date => l_effective_date,
1033 p_business_group_id => p_business_group_id,
1034 p_datetrack_mode => p_datetrack_mode);
1035 hr_utility.set_location('var row updated',70);
1036 stage_to_epa(p_copy_entity_txn_id => p_copy_entity_txn_id,
1037 p_effective_date => l_effective_date,
1038 p_business_group_id => p_business_group_id,
1039 p_datetrack_mode => p_datetrack_mode);
1040 hr_utility.set_location('epa row updated',70);
1041 stage_to_cep(p_copy_entity_txn_id => p_copy_entity_txn_id,
1042 p_effective_date => l_effective_date,
1043 p_business_group_id => p_business_group_id,
1044 p_datetrack_mode => p_datetrack_mode);
1045 hr_utility.set_location('cep row updated',70);
1046 exception
1047 when others then
1048 hr_utility.set_location('error encountered',420);
1049 raise;
1050 end stage_to_ben;
1051 procedure stage_to_opt(p_copy_entity_txn_id in number,
1052 p_business_group_id in number,
1053 p_effective_date in date,
1054 p_pl_typ_id in number,
1055 p_datetrack_mode in varchar2) is
1056 cursor c_OPT is
1057 select *
1058 from ben_copy_entity_results
1059 where copy_entity_txn_id = p_copy_entity_txn_id
1060 and table_alias = 'OPT'
1061 and dml_operation <> 'REUSE' ;
1062 r_OPT c_OPT%rowtype;
1063 l_proc varchar2(61) :='stage_to_opt';
1064 l_opt_id number;
1065 l_opt_name varchar2(240);
1066 l_dt_mode varchar2(30);
1067 l_ovn number;
1068 l_db_ovn number;
1069 l_effective_start_date date;
1070 l_pk number;
1071 l_object varchar2(80);
1072 l_effective_end_date date;
1073 l_effective_date date;
1074 l_message_text varchar2(2000);
1075 l_scl_name varchar2(30);
1076 begin
1077 hr_utility.set_location('inside '||l_proc,1);
1078 for r_opt in c_opt loop
1079 l_opt_id := r_OPT.information1;
1080 l_ovn := r_OPT.information265;
1081 hr_utility.set_location(' l_opt_id '||l_opt_id,2);
1082 hr_utility.set_location(' point id '||r_opt.information257,3);
1083 if r_opt.dml_operation in ('INSERT','COPIED','UPD_INS') then
1084 l_effective_date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
1085 else
1086 l_effective_date := r_OPT.information2;
1087 end if;
1088 l_opt_name := build_opt_name(p_opt_cer_id => r_opt.copy_entity_result_id);
1089 begin
1090 if r_opt.dml_operation in ('INSERT','COPIED','UPD_INS')
1091 and l_opt_id is null
1092 and r_opt.information257 is not null
1093 and l_opt_name is not null then
1094 -- option may have been created thru step api. get the opt id
1095 l_opt_id := pqh_gsp_hr_to_stage.get_opt_for_point
1096 (p_point_id => r_opt.information257,
1097 p_effective_date => l_effective_date);
1098 hr_utility.set_location('opt found'||l_opt_id,5);
1099 if l_opt_id is null then
1100 hr_utility.set_location(' BEN_OPT_F CREATE_OPTION_DEFINITION ',4);
1101 BEN_OPTION_DEFINITION_API.CREATE_OPTION_DEFINITION(
1102 P_EFFECTIVE_DATE => l_effective_date
1103 ,P_BUSINESS_GROUP_ID => p_business_group_id
1104 ,P_CMBN_PTIP_OPT_ID => r_OPT.INFORMATION249
1105 ,P_COMPONENT_REASON => r_OPT.INFORMATION13
1106 ,P_INVK_WV_OPT_FLAG => nvl(r_OPT.INFORMATION14,'N')
1107 ,P_MAPPING_TABLE_NAME => 'PER_SPINAL_POINTS'
1108 ,P_MAPPING_TABLE_PK_ID => r_opt.information257
1109 ,P_NAME => l_opt_name
1110 ,P_OPT_ID => l_opt_id
1111 ,P_RQD_PERD_ENRT_NENRT_RL => '' -- r_OPT.INFORMATION258
1112 ,P_RQD_PERD_ENRT_NENRT_UOM => r_OPT.INFORMATION15
1113 ,P_RQD_PERD_ENRT_NENRT_VAL => r_OPT.INFORMATION259
1114 ,P_SHORT_CODE => r_OPT.INFORMATION11
1115 ,P_SHORT_NAME => r_OPT.INFORMATION12
1116 ,P_EFFECTIVE_START_DATE => l_effective_start_date
1117 ,P_EFFECTIVE_END_DATE => l_effective_end_date
1118 ,P_OBJECT_VERSION_NUMBER => l_ovn
1119 );
1120 hr_utility.set_location('opt created'||l_opt_id,5);
1121 ben_plan_type_option_type_api.create_plan_type_option_type
1122 (p_pl_typ_opt_typ_id => l_pk
1123 ,p_effective_start_date => l_effective_start_date
1124 ,p_effective_end_date => l_effective_end_date
1125 ,p_pl_typ_opt_typ_cd => 'GSP'
1126 ,p_opt_id => l_opt_id
1127 ,p_pl_typ_id => p_pl_typ_id
1128 ,p_business_group_id => p_Business_Group_id
1129 ,p_object_version_number => l_ovn
1130 ,p_effective_date => l_effective_date);
1131 end if;
1132 opt_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1133 p_opt_id => l_opt_id,
1134 p_opt_name => l_opt_name,
1135 p_opt_cer_id => r_OPT.copy_entity_result_id);
1136 hr_utility.set_location('opt wrtback comp',8);
1137 elsif l_opt_id is not null
1138 and r_opt.dml_operation = 'UPDATE'
1139 and r_opt.information257 is not null
1140 and l_opt_name is not null then
1141 hr_utility.set_location(' BEN_OPT_F UPDATE_OPTION_DEFINITION ',30);
1142 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1143 if p_datetrack_mode <> 'CORRECTION' then
1144 l_dt_mode := get_update_mode('BEN_OPT_F','OPT_ID', l_opt_id, l_effective_date) ;
1145 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
1146 else
1147 l_dt_mode := p_datetrack_mode;
1148 end if;
1149 l_db_ovn := get_ovn(p_table_name => 'BEN_OPT_F',
1150 p_key_column_name => 'OPT_ID',
1151 p_key_column_value => l_opt_id,
1152 p_effective_date => l_effective_date);
1153 hr_utility.set_location(' ovn is '||l_db_ovn,30);
1154 if l_db_ovn <> l_ovn then
1155 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','OPT');
1156 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1157 fnd_message.set_token('OBJECT ',l_object);
1158 fnd_message.set_token('OBJECT_NAME ',l_opt_name);
1159 fnd_message.raise_error;
1160 else
1161 BEN_OPTION_DEFINITION_API.UPDATE_OPTION_DEFINITION(
1162 P_EFFECTIVE_DATE => l_effective_date
1163 ,P_BUSINESS_GROUP_ID => p_business_group_id
1164 ,P_COMPONENT_REASON => r_OPT.INFORMATION13
1165 ,P_INVK_WV_OPT_FLAG => r_OPT.INFORMATION14
1166 ,P_MAPPING_TABLE_NAME => r_OPT.INFORMATION141
1167 ,P_MAPPING_TABLE_PK_ID => r_opt.information257
1168 ,P_NAME => l_opt_name
1169 ,P_OPT_ID => l_opt_id
1170 ,P_RQD_PERD_ENRT_NENRT_RL => ''
1171 ,P_RQD_PERD_ENRT_NENRT_UOM => r_OPT.INFORMATION15
1172 ,P_RQD_PERD_ENRT_NENRT_VAL => r_OPT.INFORMATION259
1173 ,P_SHORT_CODE => r_OPT.INFORMATION11
1174 ,P_SHORT_NAME => r_OPT.INFORMATION12
1175 ,P_EFFECTIVE_START_DATE => l_effective_start_date
1176 ,P_EFFECTIVE_END_DATE => l_effective_end_date
1177 ,P_OBJECT_VERSION_NUMBER => l_ovn
1178 ,P_DATETRACK_MODE => l_dt_mode
1179 );
1180 end if;
1181 elsif r_opt.dml_operation in ('DELETE') then
1182 hr_utility.set_location('nothing needs to be done',100);
1183 else
1184 l_message_text := 'invalid dml_oper is '||r_opt.dml_operation
1185 ||' opt id is '||l_opt_id
1186 ||' opt name is '||l_opt_name
1187 ||' opt ovn is '||l_ovn
1188 ||' point id is '||r_opt.information257;
1189 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1190 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1191 P_TXN_ID => nvl(l_opt_id,p_copy_entity_txn_id),
1192 P_MODULE_CD => 'PQH_GSP_STGBEN',
1193 p_context => 'OPT',
1194 P_MESSAGE_TYPE_CD => 'E',
1195 P_MESSAGE_TEXT => l_message_text,
1196 p_effective_date => p_effective_date);
1197 end if;
1198 exception when others then
1199 hr_utility.set_location('issues in writing opt, skipping'||l_proc,100);
1200 raise;
1201 end;
1202 end loop;
1203 hr_utility.set_location('leaving '||l_proc,100);
1204 exception
1205 when others then
1206 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1207 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1208 P_TXN_ID => nvl(l_opt_id,p_copy_entity_txn_id),
1209 P_MODULE_CD => 'PQH_GSP_STGBEN',
1210 p_context => 'OPT',
1211 P_MESSAGE_TYPE_CD => 'E',
1212 P_MESSAGE_TEXT => 'OPT',
1213 p_effective_date => p_effective_date);
1214 raise;
1215 end stage_to_opt;
1216 procedure stage_to_plan(p_copy_entity_txn_id in number,
1217 p_business_group_id in number,
1218 p_effective_date in date,
1219 p_pl_typ_id in number,
1220 p_datetrack_mode in varchar2) is
1221 cursor c_pln is
1222 select *
1223 from ben_copy_entity_results
1224 where copy_entity_txn_id = p_copy_entity_txn_id
1225 and table_alias = 'PLN'
1226 and dml_operation <> 'REUSE' ;
1227 r_pln c_pln%rowtype;
1228 l_proc varchar2(61) :='stage_to_pln';
1229 l_pl_id number ;
1230 l_pl_name varchar2(240);
1231 l_ovn number ;
1232 l_object varchar2(80);
1233 l_db_ovn number;
1234 l_effective_start_date date ;
1235 l_effective_end_date date ;
1236 l_effective_date date;
1237 l_message_text varchar2(2000);
1238 l_dt_mode varchar2(30);
1239 begin
1240 hr_utility.set_location('inside '||l_proc,1);
1241 for r_pln in c_pln loop
1242 l_pl_id := r_pln.information1;
1243 l_ovn := r_pln.information265;
1244 hr_utility.set_location('for pln_id:'||l_pl_id ||'dml '||r_pln.dml_operation,2);
1245 if r_pln.dml_operation in ('INSERT','COPIED','UPD_INS') then
1246 l_effective_date := pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
1247 else
1248 l_effective_date := r_pln.information2;
1249 end if;
1250 hr_utility.set_location('effdt is :'||to_char(l_effective_date,'DD/MM/RRRR'),3);
1251 hr_utility.set_location('pl typ id is :'||p_pl_typ_id,4);
1252 hr_utility.set_location('grd id is :'||r_pln.information294,5);
1253 l_pl_name := nvl(r_PLN.INFORMATION170,r_PLN.INFORMATION5);
1254 begin
1255 if l_pl_id is null and r_pln.dml_operation in ('INSERT','COPIED','UPD_INS')
1256 and r_pln.information294 is not null then
1257 hr_utility.set_location(' BEN_PL_F CREATE_PLAN ',4);
1258 BEN_PLAN_API.CREATE_PLAN(
1259 P_EFFECTIVE_DATE => l_effective_date
1260 ,P_BUSINESS_GROUP_ID => p_business_group_id
1261 ,P_ACTL_PREM_ID => r_PLN.INFORMATION250
1262 ,P_ALWS_QDRO_FLAG => nvl(r_PLN.INFORMATION36,'N')
1263 ,P_ALWS_QMCSO_FLAG => nvl(r_PLN.INFORMATION37,'N')
1264 ,P_ALWS_REIMBMTS_FLAG => nvl(r_PLN.INFORMATION51,'N')
1265 ,P_ALWS_TMPRY_ID_CRD_FLAG => nvl(r_PLN.INFORMATION24,'N')
1266 ,P_ALWS_UNRSTRCTD_ENRT_FLAG => nvl(r_PLN.INFORMATION52,'N')
1267 ,P_AUTO_ENRT_MTHD_RL => r_PLN.INFORMATION281
1268 ,P_BNDRY_PERD_CD => r_PLN.INFORMATION101
1269 ,P_BNFT_OR_OPTION_RSTRCTN_CD => r_PLN.INFORMATION77
1270 ,P_BNFT_PRVDR_POOL_ID => r_PLN.INFORMATION235
1271 ,P_BNF_ADDL_INSTN_TXT_ALWD_FLAG => nvl(r_PLN.INFORMATION53,'N')
1272 ,P_BNF_ADRS_RQD_FLAG => nvl(r_PLN.INFORMATION54,'N')
1273 ,P_BNF_CNTNGT_BNFS_ALWD_FLAG => nvl(r_PLN.INFORMATION56,'N')
1274 ,P_BNF_CTFN_RQD_FLAG => nvl(r_PLN.INFORMATION55,'N')
1275 ,P_BNF_DFLT_BNF_CD => r_PLN.INFORMATION82
1276 ,P_BNF_DOB_RQD_FLAG => nvl(r_PLN.INFORMATION66,'N')
1277 ,P_BNF_DSGE_MNR_TTEE_RQD_FLAG => nvl(r_PLN.INFORMATION60,'N')
1278 ,P_BNF_DSGN_CD => r_PLN.INFORMATION89
1279 ,P_BNF_INCRMT_AMT => r_PLN.INFORMATION302
1280 ,P_BNF_LEGV_ID_RQD_FLAG => nvl(r_PLN.INFORMATION57,'N')
1281 ,P_BNF_MAY_DSGT_ORG_FLAG => nvl(r_PLN.INFORMATION58,'N')
1282 ,P_BNF_MN_DSGNTBL_AMT => r_PLN.INFORMATION303
1283 ,P_BNF_MN_DSGNTBL_PCT_VAL => r_PLN.INFORMATION290
1284 ,P_BNF_PCT_AMT_ALWD_CD => r_PLN.INFORMATION83
1285 ,P_BNF_PCT_INCRMT_VAL => r_PLN.INFORMATION293
1286 ,P_BNF_QDRO_RL_APLS_FLAG => nvl(r_PLN.INFORMATION59,'N')
1287 ,P_CMPR_CLMS_TO_CVG_OR_BAL_CD => r_PLN.INFORMATION84
1288 ,P_COBRA_PYMT_DUE_DY_NUM => r_PLN.INFORMATION285
1289 ,P_COST_ALLOC_KEYFLEX_1_ID => r_PLN.INFORMATION287
1290 ,P_COST_ALLOC_KEYFLEX_2_ID => r_PLN.INFORMATION288
1291 ,P_CVG_INCR_R_DECR_ONLY_CD => r_PLN.INFORMATION68
1292 ,P_DFLT_TO_ASN_PNDG_CTFN_CD => r_PLN.INFORMATION91
1293 ,P_DFLT_TO_ASN_PNDG_CTFN_RL => r_PLN.INFORMATION272
1294 ,P_DPNT_ADRS_RQD_FLAG => nvl(r_PLN.INFORMATION30,'N')
1295 ,P_DPNT_CVD_BY_OTHR_APLS_FLAG => nvl(r_PLN.INFORMATION29,'N')
1296 ,P_DPNT_CVG_END_DT_CD => r_PLN.INFORMATION85
1297 ,P_DPNT_CVG_END_DT_RL => r_PLN.INFORMATION258
1298 ,P_DPNT_CVG_STRT_DT_CD => r_PLN.INFORMATION86
1299 ,P_DPNT_CVG_STRT_DT_RL => r_PLN.INFORMATION259
1300 ,P_DPNT_DOB_RQD_FLAG => nvl(r_PLN.INFORMATION32,'N')
1301 ,P_DPNT_DSGN_CD => r_PLN.INFORMATION87
1302 ,P_DPNT_LEG_ID_RQD_FLAG => nvl(r_PLN.INFORMATION31,'N')
1303 ,P_DPNT_NO_CTFN_RQD_FLAG => nvl(r_PLN.INFORMATION27,'N')
1304 ,P_DRVBL_DPNT_ELIG_FLAG => nvl(r_PLN.INFORMATION25,'N')
1305 ,P_DRVBL_FCTR_APLS_RTS_FLAG => nvl(r_PLN.INFORMATION33,'N')
1306 ,P_DRVBL_FCTR_PRTN_ELIG_FLAG => nvl(r_PLN.INFORMATION26,'N')
1307 ,P_ELIG_APLS_FLAG => nvl(r_PLN.INFORMATION34,'N')
1308 ,P_ENRT_CD => r_PLN.INFORMATION17
1309 ,P_ENRT_CVG_END_DT_CD => r_PLN.INFORMATION21
1310 ,P_ENRT_CVG_END_DT_RL => r_PLN.INFORMATION260
1311 ,P_ENRT_CVG_STRT_DT_CD => r_PLN.INFORMATION20
1312 ,P_ENRT_CVG_STRT_DT_RL => r_PLN.INFORMATION262
1313 ,P_ENRT_MTHD_CD => r_PLN.INFORMATION92
1314 ,P_ENRT_PL_OPT_FLAG => nvl(r_PLN.INFORMATION39,'N')
1315 ,P_ENRT_RL => r_PLN.INFORMATION274
1316 ,P_FRFS_APLY_FLAG => nvl(r_PLN.INFORMATION40,'N')
1317 ,P_FRFS_CNTR_DET_CD => r_PLN.INFORMATION96
1318 ,P_FRFS_DISTR_DET_CD => r_PLN.INFORMATION97
1319 ,P_FRFS_DISTR_MTHD_CD => r_PLN.INFORMATION13
1320 ,P_FRFS_DISTR_MTHD_RL => r_PLN.INFORMATION257
1321 ,P_FRFS_MX_CRYFWD_VAL => r_PLN.INFORMATION304
1322 ,P_FRFS_PORTION_DET_CD => r_PLN.INFORMATION100
1323 ,P_FRFS_VAL_DET_CD => r_PLN.INFORMATION99
1324 ,P_FUNCTION_CODE => r_PLN.INFORMATION95
1325 ,P_HC_PL_SUBJ_HCFA_APRVL_FLAG => nvl(r_PLN.INFORMATION47,'N')
1326 ,P_HC_SVC_TYP_CD => r_PLN.INFORMATION15
1327 ,P_HGHLY_CMPD_RL_APLS_FLAG => nvl(r_PLN.INFORMATION38,'N')
1328 ,P_IMPTD_INCM_CALC_CD => r_PLN.INFORMATION73
1329 ,P_INCPTN_DT => r_PLN.INFORMATION306
1330 ,P_INVK_DCLN_PRTN_PL_FLAG => nvl(r_PLN.INFORMATION50,'N')
1331 ,P_INVK_FLX_CR_PL_FLAG => nvl(r_PLN.INFORMATION49,'N')
1332 ,P_IVR_IDENT => r_PLN.INFORMATION142
1333 ,P_MAPPING_TABLE_NAME => 'PER_GRADES'
1334 ,P_MAPPING_TABLE_PK_ID => r_PLN.INFORMATION294
1335 ,P_MAY_ENRL_PL_N_OIPL_FLAG => nvl(r_PLN.INFORMATION28,'N')
1336 ,P_MN_CVG_RL => r_PLN.INFORMATION283
1337 ,P_MN_CVG_RQD_AMT => r_PLN.INFORMATION300
1338 ,P_MN_OPTS_RQD_NUM => r_PLN.INFORMATION269
1339 ,P_MX_CVG_ALWD_AMT => r_PLN.INFORMATION299
1340 ,P_MX_CVG_INCR_ALWD_AMT => r_PLN.INFORMATION297
1341 ,P_MX_CVG_INCR_WCF_ALWD_AMT => r_PLN.INFORMATION298
1342 ,P_MX_CVG_MLT_INCR_NUM => r_PLN.INFORMATION271
1343 ,P_MX_CVG_MLT_INCR_WCF_NUM => r_PLN.INFORMATION273
1344 ,P_MX_CVG_RL => r_PLN.INFORMATION284
1345 ,P_MX_CVG_WCFN_AMT => r_PLN.INFORMATION295
1346 ,P_MX_CVG_WCFN_MLT_NUM => r_PLN.INFORMATION267
1347 ,P_MX_OPTS_ALWD_NUM => r_PLN.INFORMATION270
1348 ,P_MX_WTG_DT_TO_USE_CD => r_PLN.INFORMATION80
1349 ,P_MX_WTG_DT_TO_USE_RL => r_PLN.INFORMATION275
1350 ,P_MX_WTG_PERD_PRTE_UOM => r_PLN.INFORMATION79
1351 ,P_MX_WTG_PERD_PRTE_VAL => r_PLN.INFORMATION289
1352 ,P_MX_WTG_PERD_RL => r_PLN.INFORMATION282
1353 ,P_NAME => l_pl_name
1354 ,P_NIP_ACTY_REF_PERD_CD => r_PLN.INFORMATION16
1355 ,P_NIP_DFLT_ENRT_CD => r_PLN.INFORMATION88
1356 ,P_NIP_DFLT_ENRT_DET_RL => r_PLN.INFORMATION286
1357 ,P_NIP_DFLT_FLAG => nvl(r_PLN.INFORMATION12,'N')
1358 ,P_NIP_ENRT_INFO_RT_FREQ_CD => r_PLN.INFORMATION22
1359 ,P_NIP_PL_UOM => r_PLN.INFORMATION81
1360 ,P_NO_MN_CVG_AMT_APLS_FLAG => nvl(r_PLN.INFORMATION61,'N')
1361 ,P_NO_MN_CVG_INCR_APLS_FLAG => nvl(r_PLN.INFORMATION63,'N')
1362 ,P_NO_MN_OPTS_NUM_APLS_FLAG => nvl(r_PLN.INFORMATION65,'N')
1363 ,P_NO_MX_CVG_AMT_APLS_FLAG => nvl(r_PLN.INFORMATION62,'N')
1364 ,P_NO_MX_CVG_INCR_APLS_FLAG => nvl(r_PLN.INFORMATION64,'N')
1365 ,P_NO_MX_OPTS_NUM_APLS_FLAG => nvl(r_PLN.INFORMATION35,'N')
1366 ,P_ORDR_NUM => r_PLN.INFORMATION266
1367 ,P_PER_CVRD_CD => r_PLN.INFORMATION76
1368 ,P_PL_CD => 'MSTBPGM'
1369 ,P_PL_ID => l_pl_id
1370 ,P_PL_STAT_CD => 'A'
1371 ,P_PL_TYP_ID => p_pl_typ_id
1372 ,P_PL_YR_NOT_APPLCBL_FLAG => nvl(r_PLN.INFORMATION14,'N')
1373 ,P_POSTELCN_EDIT_RL => r_PLN.INFORMATION279
1374 ,P_POST_TO_GL_FLAG => nvl(r_PLN.INFORMATION98,'N')
1375 ,P_PRMRY_FNDG_MTHD_CD => r_PLN.INFORMATION90
1376 ,P_PRORT_PRTL_YR_CVG_RSTRN_CD => r_PLN.INFORMATION18
1377 ,P_PRORT_PRTL_YR_CVG_RSTRN_RL => r_PLN.INFORMATION268
1378 ,P_PRTN_ELIG_OVRID_ALWD_FLAG => nvl(r_PLN.INFORMATION46,'N')
1379 ,P_RQD_PERD_ENRT_NENRT_RL => r_PLN.INFORMATION276
1380 ,P_RQD_PERD_ENRT_NENRT_UOM => r_PLN.INFORMATION69
1381 ,P_RQD_PERD_ENRT_NENRT_VAL => r_PLN.INFORMATION301
1382 ,P_RT_END_DT_CD => r_PLN.INFORMATION74
1383 ,P_RT_END_DT_RL => r_PLN.INFORMATION277
1384 ,P_RT_STRT_DT_CD => r_PLN.INFORMATION75
1385 ,P_RT_STRT_DT_RL => r_PLN.INFORMATION278
1386 ,P_SHORT_CODE => r_PLN.INFORMATION93
1387 ,P_SHORT_NAME => r_PLN.INFORMATION94
1388 ,P_SUBJ_TO_IMPTD_INCM_TYP_CD => r_PLN.INFORMATION71
1389 ,P_SVGS_PL_FLAG => nvl(r_PLN.INFORMATION41,'N')
1390 ,P_TRK_INELIG_PER_FLAG => nvl(r_PLN.INFORMATION42,'N')
1391 ,P_UNSSPND_ENRT_CD => r_PLN.INFORMATION72
1392 ,P_URL_REF_NAME => r_PLN.INFORMATION185
1393 ,P_USE_ALL_ASNTS_ELIG_FLAG => nvl(r_PLN.INFORMATION43,'N')
1394 ,P_USE_ALL_ASNTS_FOR_RT_FLAG => nvl(r_PLN.INFORMATION44,'N')
1395 ,P_VRFY_FMLY_MMBR_CD => r_PLN.INFORMATION23
1396 ,P_VRFY_FMLY_MMBR_RL => r_PLN.INFORMATION264
1397 ,P_VSTG_APLS_FLAG => nvl(r_PLN.INFORMATION45,'N')
1398 ,P_WVBL_FLAG => nvl(r_PLN.INFORMATION48,'N')
1399 ,P_EFFECTIVE_START_DATE => l_effective_start_date
1400 ,P_EFFECTIVE_END_DATE => l_effective_end_date
1401 ,P_OBJECT_VERSION_NUMBER => l_ovn
1402 );
1403 hr_utility.set_location('After pl insert'||l_pl_id,12);
1404 pln_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1405 p_pl_id => l_pl_id,
1406 p_pl_name => l_pl_name,
1407 p_pl_cer_id => r_PLN.copy_entity_result_id,
1408 p_plip_cer_id => r_PLN.gs_mirror_src_entity_result_id);
1409 hr_utility.set_location('pl wrtback comp ',15);
1410 elsif r_pln.dml_operation = 'UPDATE' and l_pl_id is not null
1411 and r_pln.information294 is not null then
1412 hr_utility.set_location(' BEN_PL_F UPDATE_PLAN ',30);
1413 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1414 if p_datetrack_mode <> 'CORRECTION' then
1415 l_dt_mode := get_update_mode(p_table_name => 'BEN_PL_F',
1416 p_key_column_name => 'PL_ID',
1417 p_key_column_value => l_pl_id,
1418 p_effective_date => l_effective_date);
1419 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
1420 else
1421 l_dt_mode := p_datetrack_mode;
1422 end if;
1423 l_db_ovn := get_ovn(p_table_name => 'BEN_PL_F',
1424 p_key_column_name => 'PL_ID',
1425 p_key_column_value => l_pl_id,
1426 p_effective_date => l_effective_date);
1427 hr_utility.set_location(' ovn is '||l_db_ovn,30);
1428 if l_db_ovn <> l_ovn then
1429 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','PLN');
1430 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1431 fnd_message.set_token('OBJECT ',l_object);
1432 fnd_message.set_token('OBJECT_NAME ',l_pl_name);
1433 fnd_message.raise_error;
1434 else
1435 BEN_PLAN_API.UPDATE_PLAN(
1436 P_EFFECTIVE_DATE => l_effective_date
1437 ,P_BUSINESS_GROUP_ID => p_business_group_id
1438 ,P_NAME => l_pl_name
1439 ,P_SHORT_CODE => r_PLN.INFORMATION93
1440 ,P_SHORT_NAME => r_PLN.INFORMATION94
1441 ,P_PL_ID => l_pl_id
1442 ,P_RT_STRT_DT_RL => ''
1443 ,P_VRFY_FMLY_MMBR_RL => ''
1444 ,P_EFFECTIVE_START_DATE => l_effective_start_date
1445 ,P_EFFECTIVE_END_DATE => l_effective_end_date
1446 ,P_OBJECT_VERSION_NUMBER => l_ovn
1447 ,P_DATETRACK_MODE => l_dt_mode
1448 );
1449 end if;
1450 else
1451 l_message_text := 'invalid dml_oper is'||r_pln.dml_operation
1452 ||' pl_id is'||l_pl_id
1453 ||' pl_ovn is'||l_ovn
1454 ||' grd_id is'||r_pln.information294;
1455 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1456 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1457 P_TXN_ID => nvl(l_pl_id,p_copy_entity_txn_id),
1458 P_MODULE_CD => 'PQH_GSP_STGBEN',
1459 p_context => 'PLN',
1460 P_MESSAGE_TYPE_CD => 'E',
1461 P_MESSAGE_TEXT => l_message_text,
1462 p_effective_date => p_effective_date);
1463 end if;
1464 exception when others then
1465 hr_utility.set_location('issues in writing pln'||l_proc,100);
1466 raise;
1467 end;
1468 end loop;
1469 hr_utility.set_location('leaving '||l_proc,100);
1470 exception
1471 when others then
1472 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1473 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1474 P_TXN_ID => p_copy_entity_txn_id,
1475 P_MODULE_CD => 'PQH_GSP_STGBEN',
1476 p_context => 'PLN',
1477 P_MESSAGE_TYPE_CD => 'E',
1478 P_MESSAGE_TEXT => 'PLN',
1479 p_effective_date => p_effective_date);
1480 raise;
1481 end stage_to_plan;
1482 procedure stage_to_pgm(p_copy_entity_txn_id in number,
1483 p_business_group_id in number,
1484 p_effective_date in date,
1485 p_pl_typ_id in number,
1486 p_datetrack_mode in varchar2) is
1487 cursor c_pgm is
1488 select *
1489 from ben_copy_entity_results
1490 where copy_entity_txn_id = p_copy_entity_txn_id
1491 and table_alias = 'PGM'
1492 and dml_operation in ('INSERT','UPDATE') ;-- only insert/ updates should be there
1493 --
1494 r_pgm c_pgm%rowtype;
1495 l_proc varchar2(61) :='stage_to_pgm';
1496 l_pgm_id number ;
1497 l_pet_id number ;
1498 l_pgm_cer_id number ;
1499 l_lee_rsn_id number ;
1500 l_object varchar2(80);
1501 l_ovn number ;
1502 l_message_text varchar2(2000);
1503 l_db_ovn number;
1504 l_effective_start_date date ;
1505 l_effective_end_date date ;
1506 l_effective_date date;
1507 l_ptip_id number;
1508 l_sal_upd_cd varchar2(30);
1509 l_dflt_step_cd varchar2(30);
1510 l_dt_mode varchar2(30);
1511 begin
1512 hr_utility.set_location('inside '||l_proc,10);
1513 for r_pgm in c_pgm loop
1514 l_pgm_id := r_pgm.information1;
1515 l_ovn := r_pgm.information265;
1516 hr_utility.set_location('for pgm_id:'||l_pgm_id ||'dml '||r_pgm.dml_operation,20);
1517 l_effective_date := r_pgm.information2;
1518 l_pgm_cer_id := r_pgm.copy_entity_result_id;
1519 if r_pgm.information16 = 'N' then
1520 l_sal_upd_cd := 'NO_UPDATE' ;
1521 else
1522 l_sal_upd_cd := r_pgm.information71;
1523 end if;
1524 if r_pgm.information51 in ('PQH_GSP_GP','PQH_GSP_SP','PQH_GSP_NP') then
1525 l_dflt_step_cd := r_pgm.information51;
1526 else
1527 l_dflt_step_cd := nvl(r_pgm.information14,'MINSTEP');
1528 end if;
1529 hr_utility.set_location('dflt_step_cd is'||l_dflt_step_cd,3);
1530 hr_utility.set_location('l_sal_upd_cd is'||l_sal_upd_cd,4);
1531 if l_pgm_id is null and r_pgm.dml_operation = 'INSERT' then
1532 hr_utility.set_location('dflt_step '||l_dflt_step_cd,1);
1533 hr_utility.set_location('sal_upd '||l_sal_upd_cd,2);
1534 hr_utility.set_location(' BEN_PGM_F CREATE_PROGRAM ',20);
1535 begin
1536 BEN_PROGRAM_API.CREATE_PROGRAM(
1537 P_EFFECTIVE_DATE => l_effective_date
1538 ,P_BUSINESS_GROUP_ID => p_business_group_id
1539 ,P_ACTY_REF_PERD_CD => r_PGM.INFORMATION41
1540 ,P_ALWS_UNRSTRCTD_ENRT_FLAG => nvl(r_PGM.INFORMATION36,'N')
1541 ,P_AUTO_ENRT_MTHD_RL => r_PGM.INFORMATION272
1542 ,P_COORD_CVG_FOR_ALL_PLS_FLG => nvl(r_PGM.INFORMATION30,'N')
1543 ,P_DFLT_ELEMENT_TYPE_ID => r_PGM.INFORMATION257
1544 ,P_DFLT_INPUT_VALUE_ID => r_PGM.INFORMATION258
1545 ,P_DFLT_PGM_FLAG => nvl(r_PGM.INFORMATION13,'N')
1546 ,P_DFLT_STEP_CD => l_dflt_step_cd
1547 ,P_PGM_STAT_CD => 'A'
1548 ,P_UPDATE_SALARY_CD => l_sal_upd_cd
1549 ,P_ENRT_CD => 'CCKCNCC'
1550 ,P_DFLT_STEP_RL => r_PGM.INFORMATION259
1551 ,P_DPNT_ADRS_RQD_FLAG => nvl(r_PGM.INFORMATION21,'N')
1552 ,P_DPNT_CVG_END_DT_CD => r_PGM.INFORMATION43
1553 ,P_DPNT_CVG_END_DT_RL => r_PGM.INFORMATION269
1554 ,P_DPNT_CVG_STRT_DT_CD => r_PGM.INFORMATION44
1555 ,P_DPNT_CVG_STRT_DT_RL => r_PGM.INFORMATION268
1556 ,P_DPNT_DOB_RQD_FLAG => nvl(r_PGM.INFORMATION23,'N')
1557 ,P_DPNT_DSGN_CD => r_PGM.INFORMATION40
1558 ,P_DPNT_DSGN_LVL_CD => r_PGM.INFORMATION37
1559 ,P_DPNT_DSGN_NO_CTFN_RQD_FLAG => nvl(r_PGM.INFORMATION31,'N')
1560 ,P_DPNT_LEGV_ID_RQD_FLAG => nvl(r_PGM.INFORMATION25,'N')
1561 ,P_DRVBL_FCTR_APLS_RTS_FLAG => nvl(r_PGM.INFORMATION34,'N')
1562 ,P_DRVBL_FCTR_DPNT_ELIG_FLAG => nvl(r_PGM.INFORMATION32,'N')
1563 ,P_DRVBL_FCTR_PRTN_ELIG_FLAG => nvl(r_PGM.INFORMATION33,'N')
1564 ,P_ELIG_APLS_FLAG => nvl(r_PGM.INFORMATION26,'N')
1565 ,P_ENRT_CVG_END_DT_CD => 'ODBED'
1566 ,P_ENRT_CVG_END_DT_RL => r_PGM.INFORMATION266
1567 ,P_ENRT_CVG_STRT_DT_CD => nvl(r_PGM.INFORMATION45,'AED')
1568 ,P_ENRT_CVG_STRT_DT_RL => r_PGM.INFORMATION267
1569 ,P_ENRT_INFO_RT_FREQ_CD => 'MO'
1570 ,P_ENRT_MTHD_CD => r_PGM.INFORMATION52
1571 ,P_ENRT_RL => r_PGM.INFORMATION273
1572 ,P_IVR_IDENT => r_PGM.INFORMATION141
1573 ,P_MX_DPNT_PCT_PRTT_LF_AMT => r_PGM.INFORMATION287
1574 ,P_MX_SPS_PCT_PRTT_LF_AMT => r_PGM.INFORMATION288
1575 ,P_NAME => r_PGM.INFORMATION170
1576 ,P_PER_CVRD_CD => r_PGM.INFORMATION20
1577 ,P_PGM_DESC => r_PGM.INFORMATION219
1578 ,P_PGM_GRP_CD => r_PGM.INFORMATION49
1579 ,P_PGM_ID => l_pgm_id
1580 ,P_PGM_PRVDS_NO_AUTO_ENRT_FLAG => nvl(r_PGM.INFORMATION22,'N')
1581 ,P_PGM_PRVDS_NO_DFLT_ENRT_FLAG => nvl(r_PGM.INFORMATION24,'N')
1582 ,P_PGM_TYP_CD => r_PGM.INFORMATION39
1583 ,P_PGM_UOM => r_PGM.INFORMATION50
1584 ,P_PGM_USE_ALL_ASNTS_ELIG_FLAG => nvl(r_PGM.INFORMATION29,'N')
1585 -- ,P_POE_LVL_CD => r_PGM.INFORMATION53
1586 ,P_PRTN_ELIG_OVRID_ALWD_FLAG => nvl(r_PGM.INFORMATION28,'N')
1587 ,P_RT_END_DT_CD => nvl(r_PGM.INFORMATION48,'ODBED')
1588 ,P_RT_END_DT_RL => r_PGM.INFORMATION271
1589 ,P_RT_STRT_DT_CD => nvl(r_PGM.INFORMATION47,'AED')
1590 ,P_RT_STRT_DT_RL => r_PGM.INFORMATION270
1591 ,P_SCORES_CALC_MTHD_CD => r_PGM.INFORMATION15
1592 ,P_SCORES_CALC_RL => r_PGM.INFORMATION261
1593 ,P_SHORT_CODE => r_PGM.INFORMATION11
1594 ,P_SHORT_NAME => r_PGM.INFORMATION12
1595 ,P_TRK_INELIG_PER_FLAG => nvl(r_PGM.INFORMATION35,'N')
1596 ,P_URL_REF_NAME => r_PGM.INFORMATION185
1597 ,P_USES_ALL_ASMTS_FOR_RTS_FLAG => nvl(r_PGM.INFORMATION27,'N')
1598 ,P_USE_MULTI_PAY_RATES_FLAG => nvl(r_PGM.INFORMATION17,'N')
1599 ,P_USE_PROG_POINTS_FLAG => nvl(r_PGM.INFORMATION18,'N')
1600 ,P_USE_SCORES_CD => r_PGM.INFORMATION19
1601 ,P_VRFY_FMLY_MMBR_CD => r_PGM.INFORMATION54
1602 ,P_VRFY_FMLY_MMBR_RL => r_PGM.INFORMATION274
1603 ,P_USE_VARIABLE_RATES_FLAG => NVL(r_PGM.INFORMATION69,'N')
1604 ,P_SALARY_CALC_MTHD_CD => r_PGM.INFORMATION70
1605 ,P_GSP_ALLOW_OVERRIDE_FLAG => NVL(r_PGM.INFORMATION72,'N')
1606 ,P_SALARY_CALC_MTHD_RL => r_PGM.INFORMATION293
1607 ,P_EFFECTIVE_START_DATE => l_effective_start_date
1608 ,P_EFFECTIVE_END_DATE => l_effective_end_date
1609 ,P_OBJECT_VERSION_NUMBER => l_ovn
1610 );
1611 exception when others then
1612 hr_utility.set_location('issues in creating pgm, skipping',100);
1613 raise;
1614 end;
1615 hr_utility.set_location('After pgm insert '||l_pgm_id,1);
1616 create_ptip(p_pgm_id => l_pgm_id,
1617 p_pl_typ_id => p_pl_typ_id,
1618 p_bg_id => p_business_group_id,
1619 p_effective_date => p_effective_date,
1620 p_ptip_id => l_ptip_id);
1621 hr_utility.set_location('ptip id is '||l_ptip_id,2);
1622 pgm_enrl(p_pgm_id => l_pgm_id,
1623 p_bg_id => p_business_group_id,
1624 p_effective_date => p_effective_date,
1625 p_pet_id => l_pet_id);
1626 hr_utility.set_location('pet id is '||l_pet_id,2);
1627 if l_pet_id is not null then
1628 pgm_le(p_pgm_id => l_pgm_id,
1629 p_bg_id => p_business_group_id,
1630 p_effective_date => p_effective_date,
1631 p_pet_id => l_pet_id,
1632 p_pro_cvg_st_dt => nvl(r_PGM.INFORMATION45,'AED'),
1633 p_pro_rt_st_dt => nvl(r_PGM.INFORMATION47,'AED'),
1634 p_syn_rt_st_dt => nvl(r_PGM.INFORMATION53,'AED'),
1635 p_lee_rsn_id => l_lee_rsn_id) ;
1636 hr_utility.set_location('lee_rsn id is '||l_lee_rsn_id,3);
1637 end if;
1638 pgm_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1639 p_pgm_id => l_pgm_id,
1640 p_pgm_cer_id => l_pgm_cer_id,
1641 p_ptip_id => l_ptip_id);
1642 hr_utility.set_location('pgm writeback comp ',3);
1643 --
1644 elsif r_pgm.dml_operation ='UPDATE' and l_pgm_id is not null then
1645 hr_utility.set_location(' BEN_PGM_F UPDATE_PROGRAM ',30);
1646 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1647 if p_datetrack_mode <> 'CORRECTION' then
1648 l_dt_mode := get_update_mode(p_table_name => 'BEN_PGM_F',
1649 p_key_column_name => 'PGM_ID',
1650 p_key_column_value => l_pgm_id,
1651 p_effective_date => l_effective_date);
1652 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
1653 else
1654 l_dt_mode := p_datetrack_mode;
1655 end if;
1656 l_db_ovn := get_ovn(p_table_name => 'BEN_PGM_F',
1657 p_key_column_name => 'PGM_ID',
1658 p_key_column_value => l_pgm_id,
1659 p_effective_date => l_effective_date);
1660 hr_utility.set_location(' ovn is '||l_db_ovn,30);
1661 if l_db_ovn <> l_ovn then
1662 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','PGM');
1663 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1664 fnd_message.set_token('OBJECT ',l_object);
1665 fnd_message.set_token('OBJECT_NAME ',r_pgm.information170);
1666 fnd_message.raise_error;
1667 else
1668 BEN_PROGRAM_API.UPDATE_PROGRAM(
1669 P_EFFECTIVE_DATE => l_effective_date
1670 ,P_BUSINESS_GROUP_ID => p_business_group_id
1671 ,P_ENRT_CD => 'CCKCNCC'
1672 ,P_ACTY_REF_PERD_CD => r_PGM.INFORMATION41
1673 ,P_ALWS_UNRSTRCTD_ENRT_FLAG => r_PGM.INFORMATION36
1674 ,P_AUTO_ENRT_MTHD_RL => r_PGM.INFORMATION272
1675 ,P_COORD_CVG_FOR_ALL_PLS_FLG => r_PGM.INFORMATION30
1676 ,P_DFLT_ELEMENT_TYPE_ID => r_PGM.INFORMATION257
1677 ,P_DFLT_INPUT_VALUE_ID => r_PGM.INFORMATION258
1678 ,P_DFLT_PGM_FLAG => r_PGM.INFORMATION13
1679 ,P_DFLT_STEP_CD => l_dflt_step_cd
1680 ,P_DFLT_STEP_RL => r_PGM.INFORMATION259
1681 ,P_DPNT_ADRS_RQD_FLAG => r_PGM.INFORMATION21
1682 ,P_DPNT_CVG_END_DT_CD => r_PGM.INFORMATION43
1683 ,P_DPNT_CVG_END_DT_RL => r_PGM.INFORMATION269
1684 ,P_DPNT_CVG_STRT_DT_CD => r_PGM.INFORMATION44
1685 ,P_DPNT_CVG_STRT_DT_RL => r_PGM.INFORMATION268
1686 ,P_DPNT_DOB_RQD_FLAG => r_PGM.INFORMATION23
1687 ,P_DPNT_DSGN_CD => r_PGM.INFORMATION40
1688 ,P_DPNT_DSGN_LVL_CD => r_PGM.INFORMATION37
1689 ,P_DPNT_DSGN_NO_CTFN_RQD_FLAG => r_PGM.INFORMATION31
1690 ,P_DPNT_LEGV_ID_RQD_FLAG => r_PGM.INFORMATION25
1691 ,P_DRVBL_FCTR_APLS_RTS_FLAG => r_PGM.INFORMATION34
1692 ,P_DRVBL_FCTR_DPNT_ELIG_FLAG => r_PGM.INFORMATION32
1693 ,P_DRVBL_FCTR_PRTN_ELIG_FLAG => r_PGM.INFORMATION33
1694 ,P_ELIG_APLS_FLAG => r_PGM.INFORMATION26
1695 ,P_ENRT_CVG_END_DT_CD => nvl(r_PGM.INFORMATION42,'ODBED')
1696 ,P_ENRT_CVG_END_DT_RL => r_PGM.INFORMATION266
1697 ,P_ENRT_CVG_STRT_DT_CD => nvl(r_PGM.INFORMATION45,'AED')
1698 ,P_ENRT_CVG_STRT_DT_RL => r_PGM.INFORMATION267
1699 ,P_ENRT_INFO_RT_FREQ_CD => r_PGM.INFORMATION46
1700 ,P_ENRT_MTHD_CD => r_PGM.INFORMATION52
1701 ,P_ENRT_RL => r_PGM.INFORMATION273
1702 ,P_IVR_IDENT => r_PGM.INFORMATION141
1703 ,P_MX_DPNT_PCT_PRTT_LF_AMT => r_PGM.INFORMATION287
1704 ,P_MX_SPS_PCT_PRTT_LF_AMT => r_PGM.INFORMATION288
1705 ,P_NAME => r_PGM.INFORMATION170
1706 ,P_PER_CVRD_CD => r_PGM.INFORMATION20
1707 ,P_PGM_DESC => r_PGM.INFORMATION219
1708 ,P_PGM_GRP_CD => r_PGM.INFORMATION49
1709 ,P_PGM_ID => l_pgm_id
1710 ,P_PGM_PRVDS_NO_AUTO_ENRT_FLAG => r_PGM.INFORMATION22
1711 ,P_PGM_PRVDS_NO_DFLT_ENRT_FLAG => r_PGM.INFORMATION24
1712 ,P_PGM_STAT_CD => r_PGM.INFORMATION38
1713 ,P_PGM_TYP_CD => r_PGM.INFORMATION39
1714 ,P_PGM_UOM => r_PGM.INFORMATION50
1715 ,P_PGM_USE_ALL_ASNTS_ELIG_FLAG => r_PGM.INFORMATION29
1716 -- ,P_POE_LVL_CD => r_PGM.INFORMATION53
1717 ,P_PRTN_ELIG_OVRID_ALWD_FLAG => r_PGM.INFORMATION28
1718 ,P_RT_END_DT_CD => nvl(r_PGM.INFORMATION48,'ODBED')
1719 ,P_RT_END_DT_RL => r_PGM.INFORMATION271
1720 ,P_RT_STRT_DT_CD => nvl(r_PGM.INFORMATION47,'AED')
1721 ,P_RT_STRT_DT_RL => r_PGM.INFORMATION270
1722 ,P_SCORES_CALC_MTHD_CD => r_PGM.INFORMATION15
1723 ,P_SCORES_CALC_RL => r_PGM.INFORMATION261
1724 ,P_SHORT_CODE => r_PGM.INFORMATION11
1725 ,P_SHORT_NAME => r_PGM.INFORMATION12
1726 ,P_TRK_INELIG_PER_FLAG => r_PGM.INFORMATION35
1727 ,P_UPDATE_SALARY_CD => l_sal_upd_cd
1728 ,P_URL_REF_NAME => r_PGM.INFORMATION185
1729 ,P_USES_ALL_ASMTS_FOR_RTS_FLAG => r_PGM.INFORMATION27
1730 ,P_USE_MULTI_PAY_RATES_FLAG => r_PGM.INFORMATION17
1731 ,P_USE_PROG_POINTS_FLAG => r_PGM.INFORMATION18
1732 ,P_USE_SCORES_CD => r_PGM.INFORMATION19
1733 ,P_VRFY_FMLY_MMBR_CD => r_PGM.INFORMATION54
1734 ,P_VRFY_FMLY_MMBR_RL => r_PGM.INFORMATION274
1735 ,P_USE_VARIABLE_RATES_FLAG => NVL(r_PGM.INFORMATION69,'N')
1736 ,P_SALARY_CALC_MTHD_CD => r_PGM.INFORMATION70
1737 ,P_GSP_ALLOW_OVERRIDE_FLAG => NVL(r_PGM.INFORMATION72,'N')
1738 ,P_SALARY_CALC_MTHD_RL => r_PGM.INFORMATION293
1739 ,P_EFFECTIVE_START_DATE => l_effective_start_date
1740 ,P_EFFECTIVE_END_DATE => l_effective_end_date
1741 ,P_OBJECT_VERSION_NUMBER => l_ovn
1742 ,P_DATETRACK_MODE => l_dt_mode
1743 );
1744 upd_pgm_le(p_pgm_id => l_pgm_id,
1745 p_cet_id => p_copy_entity_txn_id,
1746 p_effective_date => p_effective_date,
1747 p_bg_id => p_business_group_id,
1748 p_pro_cvg_st_dt => nvl(r_PGM.INFORMATION45,'AED'),
1749 p_pro_rt_st_dt => nvl(r_PGM.INFORMATION47,'AED'),
1750 p_syn_rt_st_dt => nvl(r_PGM.INFORMATION53,'AED'));
1751 end if;
1752 else
1753 l_message_text := 'invalid dml_oper'||r_pgm.dml_operation
1754 ||' pgm_ovn:'||l_ovn
1755 ||' pgm_dt_mode:'||l_dt_mode
1756 ||' for pgm_id:'||l_pgm_id;
1757 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1758 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1759 P_TXN_ID => nvl(l_pgm_id,p_copy_entity_txn_id),
1760 P_MODULE_CD => 'PQH_GSP_STGBEN',
1761 p_context => 'PGM',
1762 P_MESSAGE_TYPE_CD => 'E',
1763 P_MESSAGE_TEXT => l_message_text,
1764 p_effective_date => p_effective_date);
1765 end if;
1766 end loop;
1767 hr_utility.set_location('leaving '||l_proc,100);
1768 exception
1769 when others then
1770 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1771 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1772 P_TXN_ID => p_copy_entity_txn_id,
1773 P_MODULE_CD => 'PQH_GSP_STGBEN',
1774 p_context => 'PGM',
1775 P_MESSAGE_TYPE_CD => 'E',
1776 P_MESSAGE_TEXT => 'PGM',
1777 p_effective_date => p_effective_date);
1778 raise;
1779 end stage_to_pgm;
1780 procedure stage_to_oipl(p_copy_entity_txn_id in number,
1781 p_business_group_id in number,
1782 p_effective_date in date,
1783 p_datetrack_mode in varchar2) is
1784 cursor c_cop is
1785 select *
1786 from ben_copy_entity_results
1787 where copy_entity_txn_id = p_copy_entity_txn_id
1788 and table_alias = 'COP'
1789 and dml_operation <> 'REUSE' ;
1790 --
1791 r_cop c_cop%rowtype;
1792 l_proc varchar2(61) :='stage_to_cop';
1793 l_oipl_id number ;
1794 l_ovn number ;
1795 l_pl_id number;
1796 l_object varchar2(80);
1797 l_opt_id number;
1798 l_db_ovn number;
1799 l_effective_start_date date ;
1800 l_message_text varchar2(2000);
1801 l_effective_end_date date ;
1802 l_effective_date date;
1803 l_dt_mode varchar2(30);
1804 l_grd_effstdt date; --DN code for BugId: 3242976
1805 begin
1806 hr_utility.set_location('inside '||l_proc,10);
1807 for r_cop in c_cop loop
1808 l_ovn := r_cop.information265;
1809 l_effective_date := r_cop.information2;
1810 if r_cop.INFORMATION261 is null and r_cop.gs_parent_entity_result_id is not null then
1811 select information261
1812 into l_pl_id
1813 from ben_copy_entity_results
1814 where copy_entity_result_id = r_cop.gs_parent_entity_result_id;
1815 else
1816 l_pl_id := r_cop.INFORMATION261;
1817 end if;
1818 if r_cop.INFORMATION247 is null and r_cop.information262 is not null then
1819 select information1
1820 into l_opt_id
1821 from ben_copy_entity_results
1822 where copy_entity_result_id = r_cop.information262;
1823 else
1824 l_opt_id := r_cop.INFORMATION247;
1825 end if;
1826 if l_pl_id is not null and l_opt_id is not null and r_cop.information1 is null then
1827 -- oipl may have been created by step api call.
1828 l_oipl_id := is_oipl_exists(p_effective_date => l_effective_date,
1829 p_pl_id => l_pl_id,
1830 p_opt_id => l_opt_id);
1831 else
1832 l_oipl_id := r_cop.information1;
1833 end if;
1834 if l_oipl_id is null
1835 and r_cop.dml_operation in ('INSERT','COPIED','UPD_INS')
1836 and l_pl_id is not null
1837 and l_ovn is null
1838 and l_opt_id is not null then
1839 if l_oipl_id is null then
1840 hr_utility.set_location(' BEN_OIPL_F CREATE_OPTION_IN_PLAN ',20);
1841 --DN: Start code for BugId: 3242976
1842 begin
1843 SELECT grd.date_from
1844 INTO l_grd_effstdt
1845 FROM per_grades grd,
1846 ben_pl_f pln
1847 WHERE pln.pl_id = l_pl_id
1848 AND p_effective_date BETWEEN pln.effective_start_date
1849 AND pln.effective_end_date
1850 AND grd.grade_id = pln.mapping_table_pk_id;
1851 exception
1852 WHEN OTHERS THEN
1853 l_grd_effstdt := l_effective_date;
1854 end;
1855 --End code for BugId: 3242976
1856 BEN_OPTION_IN_PLAN_API.CREATE_OPTION_IN_PLAN(
1857 P_EFFECTIVE_DATE => l_grd_effstdt --l_effective_date --DN code for BugId: 3242976
1858 ,P_BUSINESS_GROUP_ID => p_business_group_id
1859 ,P_ACTL_PREM_ID => r_COP.INFORMATION250
1860 ,P_AUTO_ENRT_FLAG => nvl(r_COP.INFORMATION25,'N')
1861 ,P_AUTO_ENRT_MTHD_RL => r_COP.INFORMATION264
1862 ,P_DFLT_ENRT_CD => r_COP.INFORMATION26
1863 ,P_DFLT_ENRT_DET_RL => r_COP.INFORMATION266
1864 ,P_DFLT_FLAG => nvl(r_COP.INFORMATION18,'N')
1865 ,P_DRVBL_FCTR_APLS_RTS_FLAG => nvl(r_COP.INFORMATION24,'N')
1866 ,P_DRVBL_FCTR_PRTN_ELIG_FLAG => nvl(r_COP.INFORMATION22,'N')
1867 ,P_ELIG_APLS_FLAG => nvl(r_COP.INFORMATION20,'N')
1868 ,P_ENRT_CD => r_COP.INFORMATION14
1869 ,P_ENRT_RL => r_COP.INFORMATION257
1870 ,P_HIDDEN_FLAG => nvl(r_COP.INFORMATION13,'N')
1871 ,P_IVR_IDENT => r_COP.INFORMATION141
1872 ,P_MNDTRY_FLAG => nvl(r_COP.INFORMATION17,'N')
1873 ,P_MNDTRY_RL => r_COP.INFORMATION268
1874 ,P_OIPL_ID => l_oipl_id
1875 ,P_OIPL_STAT_CD => 'A'
1876 ,P_OPT_ID => l_opt_id
1877 ,P_ORDR_NUM => r_COP.INFORMATION263
1878 ,P_PCP_DPNT_DSGN_CD => r_COP.INFORMATION16
1879 ,P_PCP_DSGN_CD => r_COP.INFORMATION15
1880 ,P_PER_CVRD_CD => r_COP.INFORMATION27
1881 ,P_PL_ID => l_pl_id
1882 ,P_POSTELCN_EDIT_RL => r_COP.INFORMATION269
1883 ,P_PRTN_ELIG_OVRID_ALWD_FLAG => nvl(r_COP.INFORMATION23,'N')
1884 ,P_RQD_PERD_ENRT_NENRT_RL => r_COP.INFORMATION267
1885 ,P_RQD_PERD_ENRT_NENRT_UOM => r_COP.INFORMATION29
1886 ,P_RQD_PERD_ENRT_NENRT_VAL => r_COP.INFORMATION293
1887 ,P_SHORT_CODE => r_COP.INFORMATION11
1888 ,P_SHORT_NAME => r_COP.INFORMATION12
1889 ,P_TRK_INELIG_PER_FLAG => nvl(r_COP.INFORMATION21,'N')
1890 ,P_URL_REF_NAME => r_COP.INFORMATION185
1891 ,P_VRFY_FMLY_MMBR_CD => r_COP.INFORMATION28
1892 ,P_VRFY_FMLY_MMBR_RL => r_COP.INFORMATION270
1893 ,P_EFFECTIVE_START_DATE => l_effective_start_date
1894 ,P_EFFECTIVE_END_DATE => l_effective_end_date
1895 ,P_OBJECT_VERSION_NUMBER => l_ovn
1896 );
1897 hr_utility.set_location('After oipl ins '||l_oipl_id,222);
1898 end if;
1899 oipl_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1900 p_oipl_id => l_oipl_id,
1901 p_oipl_cer_id => r_COP.copy_entity_result_id);
1902 hr_utility.set_location('oipl wrtback comp'||l_oipl_id,222);
1903 elsif l_oipl_id is not null and r_cop.dml_operation ='UPDATE'
1904 and l_ovn is not null
1905 and l_pl_id is not null
1906 and l_opt_id is not null then
1907 hr_utility.set_location(' BEN_OIPL_F UPDATE_OPTION_IN_PLAN ',30);
1908 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
1909 if p_datetrack_mode <> 'CORRECTION' then
1910 l_dt_mode := get_update_mode(p_table_name => 'BEN_OIPL_F',
1911 p_key_column_name => 'OIPL_ID',
1912 p_key_column_value => l_oipl_id,
1913 p_effective_date => l_effective_date);
1914 else
1915 l_dt_mode := p_datetrack_mode;
1916 end if;
1917 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
1918 l_db_ovn := get_ovn(p_table_name => 'BEN_OIPL_F',
1919 p_key_column_name => 'OIPL_ID',
1920 p_key_column_value => l_oipl_id,
1921 p_effective_date => l_effective_date);
1922 hr_utility.set_location(' ovn is '||l_db_ovn,30);
1923 if l_db_ovn <> l_ovn then
1924 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','OIPL');
1925 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1926 fnd_message.set_token('OBJECT ',l_object);
1927 fnd_message.set_token('OBJECT_NAME ','PL :'||l_pl_id||' OPT :'||l_opt_id);
1928 fnd_message.raise_error;
1929 else
1930 BEN_OPTION_IN_PLAN_API.UPDATE_OPTION_IN_PLAN(
1931 P_EFFECTIVE_DATE => l_effective_date
1932 ,P_BUSINESS_GROUP_ID => p_business_group_id
1933 ,P_OIPL_ID => l_oipl_id
1934 ,P_ORDR_NUM => r_COP.INFORMATION263
1935 ,P_OPT_ID => l_opt_id
1936 ,P_PL_ID => l_pl_id
1937 ,P_EFFECTIVE_START_DATE => l_effective_start_date
1938 ,P_EFFECTIVE_END_DATE => l_effective_end_date
1939 ,P_OBJECT_VERSION_NUMBER => l_ovn
1940 ,P_DATETRACK_MODE => l_dt_mode
1941 );
1942 end if;
1943 elsif l_oipl_id is not null and r_cop.dml_operation in ('INSERT','COPIED','UPD_INS') then
1944 hr_utility.set_location('step api call created oipl'||l_proc,100);
1945 elsif l_oipl_id is not null and r_cop.dml_operation in ('DELETE') then
1946 hr_utility.set_location('oipl is being deleted '||l_proc,100);
1947 else
1948 l_message_text := 'invalid dml_operation is'||r_cop.dml_operation
1949 ||' oipl_id is'||l_oipl_id
1950 ||' pl_id is'||l_pl_id
1951 ||' opt_id is'||l_opt_id
1952 ||' ovn is'||l_ovn;
1953 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1954 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1955 P_TXN_ID => nvl(l_oipl_id,p_copy_entity_txn_id),
1956 P_MODULE_CD => 'PQH_GSP_STGBEN',
1957 p_context => 'OIPL',
1958 P_MESSAGE_TYPE_CD => 'E',
1959 P_MESSAGE_TEXT => l_message_text,
1960 p_effective_date => p_effective_date);
1961 end if;
1962 end loop;
1963 hr_utility.set_location('leaving '||l_proc,100);
1964 exception
1965 when others then
1966 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1967 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1968 P_TXN_ID => p_copy_entity_txn_id,
1969 P_MODULE_CD => 'PQH_GSP_STGBEN',
1970 p_context => 'OIPL',
1971 P_MESSAGE_TYPE_CD => 'E',
1972 P_MESSAGE_TEXT => 'OIPL',
1973 p_effective_date => p_effective_date);
1974 raise;
1975 end stage_to_oipl;
1976 procedure stage_to_plip(p_copy_entity_txn_id in number,
1977 p_business_group_id in number,
1978 p_effective_date in date,
1979 p_datetrack_mode in varchar2,
1980 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
1981 cursor c_updated_cpp is
1982 select *
1983 from ben_copy_entity_results
1984 where copy_entity_txn_id = p_copy_entity_txn_id
1985 and table_alias = 'CPP'
1986 and information1 is not null
1987 and dml_operation = 'UPDATE' ; -- only updated plips should be selected
1988
1989 cursor c_cpp is
1990 select *
1991 from ben_copy_entity_results
1992 where copy_entity_txn_id = p_copy_entity_txn_id
1993 and table_alias = 'CPP'
1994 and dml_operation in ('INSERT','UPDATE') -- only insert/ updates should be there
1995 order by information263 desc;-- order by highest seq 1st
1996 --
1997 r_cpp c_cpp%rowtype;
1998 l_proc varchar2(61) :='stage_to_cpp';
1999 l_plip_id number ;
2000 l_pgm_id number ;
2001 l_pl_id number ;
2002 l_dt_mode varchar2(30);
2003 l_corps_definition_id number(15);
2004 l_object varchar2(80);
2005 l_ovn number ;
2006 l_db_ovn number;
2007 l_message_text varchar2(2000);
2008 l_effective_start_date date ;
2009 l_effective_end_date date ;
2010 l_effective_date date;
2011 begin
2012 hr_utility.set_location('inside '||l_proc,10);
2013 hr_utility.set_location('bus_area is '||p_business_area,10);
2014 for r_upd_cpp in c_updated_cpp loop
2015 -- api is not called as we don't want to update ovn
2016 update ben_plip_f
2017 set ordr_num = null
2018 where plip_id = r_upd_cpp.information1;
2019 end loop;
2020 for r_cpp in c_cpp loop
2021 l_plip_id := r_cpp.information1;
2022 l_ovn := r_cpp.information265;
2023 if r_CPP.INFORMATION260 is null then
2024 select information1
2025 into l_pgm_id
2026 from ben_copy_entity_results
2027 where copy_entity_txn_id = p_copy_entity_txn_id
2028 and table_alias = 'PGM' ;
2029 else
2030 l_pgm_id := r_CPP.INFORMATION260;
2031 end if;
2032 if r_CPP.INFORMATION261 is null then
2033 select information1
2034 into l_pl_id
2035 from ben_copy_entity_results
2036 where copy_entity_txn_id = p_copy_entity_txn_id
2037 and table_alias = 'PLN'
2038 and copy_entity_result_id = r_CPP.INFORMATION252;
2039 else
2040 l_pl_id := r_CPP.INFORMATION261;
2041 end if;
2042 hr_utility.set_location('for cpp_id:'||l_plip_id ||'dml '||r_cpp.dml_operation,20);
2043 hr_utility.set_location('pgm_id: '||l_pgm_id,20);
2044 hr_utility.set_location('pl_id: '||l_pl_id,20);
2045 --Added by kgowripe for bug#3532412
2046 if r_cpp.information291 IS NULL AND p_business_area = 'PQH_CORPS_TASK_LIST' THEN
2047 SELECT information1
2048 INTO l_corps_definition_id
2049 FROM ben_copy_entity_results
2050 WHERE copy_entity_txn_id = p_copy_entity_txn_id
2051 AND table_alias = 'CPD';
2052 else
2053 l_corps_definition_id := r_cpp.information291;
2054 end if;
2055 --End changes for bug#3532412
2056 l_effective_date := r_cpp.information2;
2057 begin
2058 if l_plip_id is null and r_cpp.dml_operation = 'INSERT'
2059 and l_pgm_id is not null and l_pl_id is not null then
2060 hr_utility.set_location(' BEN_PLIP_F CREATE_PLAN_IN_PROGRAM ',20);
2061 BEN_PLAN_IN_PROGRAM_API.CREATE_PLAN_IN_PROGRAM(
2062 P_EFFECTIVE_DATE => l_effective_date
2063 ,P_BUSINESS_GROUP_ID => p_business_group_id
2064 ,P_ALWS_UNRSTRCTD_ENRT_FLAG => nvl(r_CPP.INFORMATION15,'N')
2065 ,P_AUTO_ENRT_MTHD_RL => r_CPP.INFORMATION266
2066 ,P_BNFT_OR_OPTION_RSTRCTN_CD => r_CPP.INFORMATION36
2067 ,P_CMBN_PLIP_ID => r_CPP.INFORMATION239
2068 ,P_CVG_INCR_R_DECR_ONLY_CD => r_CPP.INFORMATION28
2069 ,P_DFLT_ENRT_CD => r_CPP.INFORMATION21
2070 ,P_DFLT_ENRT_DET_RL => r_CPP.INFORMATION264
2071 ,P_DFLT_FLAG => nvl(r_CPP.INFORMATION13,'N')
2072 ,P_DFLT_TO_ASN_PNDG_CTFN_CD => r_CPP.INFORMATION29
2073 ,P_DFLT_TO_ASN_PNDG_CTFN_RL => r_CPP.INFORMATION264
2074 ,P_DRVBL_FCTR_APLS_RTS_FLAG => nvl(r_CPP.INFORMATION16,'N')
2075 ,P_DRVBL_FCTR_PRTN_ELIG_FLAG => nvl(r_CPP.INFORMATION17,'N')
2076 ,P_ELIG_APLS_FLAG => nvl(r_CPP.INFORMATION18,'N')
2077 ,P_ENRT_CD => r_CPP.INFORMATION22
2078 ,P_ENRT_CVG_END_DT_CD => r_CPP.INFORMATION25
2079 ,P_ENRT_CVG_END_DT_RL => r_CPP.INFORMATION269
2080 ,P_ENRT_CVG_STRT_DT_CD => r_CPP.INFORMATION24
2081 ,P_ENRT_CVG_STRT_DT_RL => r_CPP.INFORMATION268
2082 ,P_ENRT_MTHD_CD => r_CPP.INFORMATION23
2083 ,P_ENRT_RL => r_CPP.INFORMATION267
2084 ,P_IVR_IDENT => r_CPP.INFORMATION141
2085 ,P_MN_CVG_AMT => r_CPP.INFORMATION293
2086 ,P_MN_CVG_RL => r_CPP.INFORMATION273
2087 ,P_MX_CVG_ALWD_AMT => r_CPP.INFORMATION294
2088 ,P_MX_CVG_INCR_ALWD_AMT => r_CPP.INFORMATION295
2089 ,P_MX_CVG_INCR_WCF_ALWD_AMT => r_CPP.INFORMATION296
2090 ,P_MX_CVG_MLT_INCR_NUM => r_CPP.INFORMATION274
2091 ,P_MX_CVG_MLT_INCR_WCF_NUM => r_CPP.INFORMATION275
2092 ,P_MX_CVG_RL => r_CPP.INFORMATION276
2093 ,P_MX_CVG_WCFN_AMT => r_CPP.INFORMATION297
2094 ,P_MX_CVG_WCFN_MLT_NUM => r_CPP.INFORMATION277
2095 ,P_NO_MN_CVG_AMT_APLS_FLAG => nvl(r_CPP.INFORMATION30,'N')
2096 ,P_NO_MN_CVG_INCR_APLS_FLAG => nvl(r_CPP.INFORMATION31,'N')
2097 ,P_NO_MX_CVG_AMT_APLS_FLAG => nvl(r_CPP.INFORMATION32,'N')
2098 ,P_NO_MX_CVG_INCR_APLS_FLAG => nvl(r_CPP.INFORMATION33,'N')
2099 ,P_ORDR_NUM => r_CPP.INFORMATION263
2100 ,P_PER_CVRD_CD => r_CPP.INFORMATION38
2101 ,P_PGM_ID => l_pgm_id
2102 ,P_PLIP_ID => l_plip_id
2103 ,P_PLIP_STAT_CD => 'A'
2104 ,P_PL_ID => l_pl_id
2105 ,P_POSTELCN_EDIT_RL => r_CPP.INFORMATION257
2106 ,P_PRORT_PRTL_YR_CVG_RSTRN_CD => r_CPP.INFORMATION35
2107 ,P_PRORT_PRTL_YR_CVG_RSTRN_RL => r_CPP.INFORMATION278
2108 ,P_PRTN_ELIG_OVRID_ALWD_FLAG => nvl(r_CPP.INFORMATION19,'N')
2109 ,P_RT_END_DT_CD => r_CPP.INFORMATION27
2110 ,P_RT_END_DT_RL => r_CPP.INFORMATION271
2111 ,P_RT_STRT_DT_CD => r_CPP.INFORMATION26
2112 ,P_RT_STRT_DT_RL => r_CPP.INFORMATION270
2113 ,P_SHORT_CODE => r_CPP.INFORMATION11
2114 ,P_SHORT_NAME => r_CPP.INFORMATION12
2115 ,P_TRK_INELIG_PER_FLAG => nvl(r_CPP.INFORMATION20,'N')
2116 ,P_UNSSPND_ENRT_CD => r_CPP.INFORMATION34
2117 ,P_URL_REF_NAME => r_CPP.INFORMATION185
2118 ,P_VRFY_FMLY_MMBR_CD => r_CPP.INFORMATION37
2119 ,P_VRFY_FMLY_MMBR_RL => r_CPP.INFORMATION279
2120 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2121 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2122 ,P_OBJECT_VERSION_NUMBER => l_ovn
2123 );
2124 hr_utility.set_location('After plip ins'||l_plip_id,22);
2125 plip_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
2126 p_plip_id => l_plip_id,
2127 p_pl_id => l_pl_id,
2128 p_plip_cer_id => r_CPP.copy_entity_result_id);
2129 hr_utility.set_location('plip wrtback comp'||l_plip_id,222);
2130 if p_business_area = 'PQH_CORPS_TASK_LIST' then
2131 hr_utility.set_location('going for quota cr'||l_plip_id,222);
2132 pqh_cpd_hr_to_stage.grd_quota_update(p_effective_date => l_effective_date,
2133 p_grade_id => r_cpp.information253,
2134 p_corps_definition_id => l_corps_definition_id,
2135 p_corps_extra_info_id => r_cpp.information290,
2136 p_perc_quota => r_cpp.information287,
2137 p_population_cd => r_cpp.information99,
2138 p_comb_grades => r_cpp.information219,
2139 p_max_speed_quota => r_cpp.information288,
2140 p_avg_speed_quota => r_cpp.information289);
2141 end if;
2142 elsif l_plip_id is not null and r_cpp.dml_operation = 'UPDATE'
2143 and l_ovn is not null and l_pgm_id is not null
2144 and l_pl_id is not null then
2145 hr_utility.set_location(' BEN_PLIP_F UPDATE_PLAN_IN_PROGRAM ',30);
2146 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2147 if p_datetrack_mode <> 'CORRECTION' then
2148 l_dt_mode := get_update_mode(p_table_name => 'BEN_PLIP_F',
2149 p_key_column_name => 'PLIP_ID',
2150 p_key_column_value => l_plip_id,
2151 p_effective_date => l_effective_date);
2152 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2153 else
2154 l_dt_mode := p_datetrack_mode;
2155 end if;
2156 l_db_ovn := get_ovn(p_table_name => 'BEN_PLIP_F',
2157 p_key_column_name => 'PLIP_ID',
2158 p_key_column_value => l_plip_id,
2159 p_effective_date => l_effective_date);
2160 hr_utility.set_location(' ovn is '||l_db_ovn,30);
2161 if l_db_ovn <> l_ovn then
2162 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','PLIP');
2163 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2164 fnd_message.set_token('OBJECT ',l_object);
2165 fnd_message.set_token('OBJECT_NAME ','PL :'||l_pl_id);
2166 fnd_message.raise_error;
2167 else
2168 BEN_PLAN_IN_PROGRAM_API.UPDATE_PLAN_IN_PROGRAM(
2169 P_EFFECTIVE_DATE => l_effective_date
2170 ,P_BUSINESS_GROUP_ID => p_business_group_id
2171 ,P_ORDR_NUM => r_CPP.INFORMATION263
2172 ,P_PGM_ID => l_pgm_id
2173 ,P_PLIP_ID => l_plip_id
2174 ,P_PL_ID => l_pl_id
2175 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2176 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2177 ,P_OBJECT_VERSION_NUMBER => l_ovn
2178 ,P_DATETRACK_MODE => l_dt_mode
2179 );
2180 end if;
2181 if p_business_area = 'PQH_CORPS_TASK_LIST' then
2182 pqh_cpd_hr_to_stage.grd_quota_update(p_effective_date => l_effective_date,
2183 p_grade_id => r_cpp.information253,
2184 p_corps_definition_id => l_corps_definition_id,
2185 p_corps_extra_info_id => r_cpp.information290,
2186 p_perc_quota => r_cpp.information287,
2187 p_population_cd => r_cpp.information99,
2188 p_comb_grades => r_cpp.information219,
2189 p_max_speed_quota => r_cpp.information288,
2190 p_avg_speed_quota => r_cpp.information289);
2191 end if;
2192 else
2193 l_message_text := 'invalid plip dml_oper: '||r_CPP.dml_operation
2194 ||' plip_id: '||l_plip_id
2195 ||' ovn: '||l_ovn
2196 ||' pgm_id: '||l_pgm_id
2197 ||' pl_id: '||l_pl_id;
2198 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2199 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2200 P_TXN_ID => nvl(l_plip_id,p_copy_entity_txn_id),
2201 P_MODULE_CD => 'PQH_GSP_STGBEN',
2202 p_context => 'PLIP',
2203 P_MESSAGE_TYPE_CD => 'E',
2204 P_MESSAGE_TEXT => l_message_text,
2205 p_effective_date => p_effective_date);
2206 end if;
2207 exception when others then
2208 hr_utility.set_location('issues in writing cpp, skipping'||l_proc,100);
2209 raise;
2210 end;
2211 end loop;
2212 hr_utility.set_location('leaving '||l_proc,100);
2213 exception
2214 when others then
2215 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2216 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2217 P_TXN_ID => p_copy_entity_txn_id,
2218 P_MODULE_CD => 'PQH_GSP_STGBEN',
2219 p_context => 'PLIP',
2220 P_MESSAGE_TYPE_CD => 'E',
2221 P_MESSAGE_TEXT => 'PLIP',
2222 p_effective_date => p_effective_date);
2223 raise;
2224 end stage_to_plip;
2225 procedure stage_to_elp(p_copy_entity_txn_id in number,
2226 p_business_group_id in number,
2227 p_effective_date in date,
2228 p_datetrack_mode in varchar2) is
2229 cursor c_elp is
2230 select *
2231 from ben_copy_entity_results
2232 where copy_entity_txn_id = p_copy_entity_txn_id
2233 and table_alias = 'CRSET'
2234 and dml_operation in ('INSERT','UPDATE') -- only insert/ updates should be there
2235 order by information161,information2;
2236 --
2237 r_elp c_elp%rowtype;
2238 l_proc varchar2(61) :='stage_to_elp';
2239 l_elp_id number ;
2240 l_object varchar2(80);
2241 l_elp_ovn number ;
2242 l_old_elp_id number ;
2243 l_old_crset_id number ;
2244 l_old_elp_ovn number ;
2245 l_effective_start_date date ;
2246 l_effective_end_date date ;
2247 l_message_text varchar2(2000);
2248 l_effective_date date;
2249 l_dt_mode varchar2(30);
2250 l_db_ovn number;
2251 l_loc_flag varchar2(30);
2252 l_org_flag varchar2(30);
2253 l_job_flag varchar2(30);
2254 l_pt_flag varchar2(30);
2255 l_sa_flag varchar2(30);
2256 l_pr_flag varchar2(30);
2257 l_fp_flag varchar2(30);
2258 l_rl_flag varchar2(30);
2259 l_bu_flag varchar2(30);
2260 l_old_loc_flag varchar2(30);
2261 l_old_org_flag varchar2(30);
2262 l_old_job_flag varchar2(30);
2263 l_old_pt_flag varchar2(30);
2264 l_old_sa_flag varchar2(30);
2265 l_old_pr_flag varchar2(30);
2266 l_old_fp_flag varchar2(30);
2267 l_old_rl_flag varchar2(30);
2268 l_old_bu_flag varchar2(30);
2269 l_dml_operation varchar2(30);
2270 l_ovn number;
2271 l_pk number;
2272 l_pt_cd varchar2(30);
2273 l_esd date;
2274 l_eed date;
2275 begin
2276 hr_utility.set_location('inside '||l_proc,10);
2277 for r_elp in c_elp loop
2278 l_elp_id := r_elp.information161;
2279 hr_utility.set_location('for elp_id:'||l_elp_id ||'dml '||r_elp.dml_operation,20);
2280 l_effective_date := r_elp.information2;
2281 if r_elp.information277 is null and r_elp.information161 = l_old_crset_id then
2282 hr_utility.set_location('reusing earlier values ',20);
2283 -- reuse old values
2284 l_elp_id := l_old_elp_id;
2285 l_elp_ovn := l_old_elp_ovn;
2286 else
2287 hr_utility.set_location('earlier values cannot be reused',20);
2288 -- new crset_id is to be entered
2289 l_elp_id := r_elp.information277;
2290 l_elp_ovn := r_elp.information265;
2291 l_old_elp_id := '';
2292 l_old_elp_ovn := '';
2293 l_old_crset_id := '';
2294 l_old_sa_flag := '';
2295 l_old_fp_flag := '';
2296 l_old_pt_flag := '';
2297 l_old_rl_flag := '';
2298 l_old_bu_flag := '';
2299 l_old_pr_flag := '';
2300 l_old_loc_flag := '';
2301 l_old_org_flag := '';
2302 l_old_job_flag := '';
2303 end if;
2304 if r_elp.dml_operation = 'INSERT' and l_elp_id is null then
2305 l_dml_operation := 'INSERT';
2306 elsif r_elp.dml_operation in ('INSERT','UPDATE') and l_elp_id is not null then
2307 l_dml_operation := 'UPDATE';
2308 else
2309 l_dml_operation := '';
2310 end if;
2311 hr_utility.set_location('dml_operation is '||l_dml_operation,4);
2312 if r_elp.information232 is not null then
2313 l_loc_flag := 'Y';
2314 else
2315 l_loc_flag := 'N';
2316 end if;
2317 if r_elp.information233 is not null then
2318 l_job_flag := 'Y';
2319 else
2320 l_job_flag := 'N';
2321 end if;
2322 if r_elp.information234 is not null then
2323 l_org_flag := 'Y';
2324 else
2325 l_org_flag := 'N';
2326 end if;
2327 if r_elp.information235 is not null then
2328 l_rl_flag := 'Y';
2329 else
2330 l_rl_flag := 'N';
2331 end if;
2332 if r_elp.information236 is not null then
2333 l_pt_flag := 'Y';
2334 else
2335 l_pt_flag := 'N';
2336 end if;
2337 if r_elp.information237 is not null then
2338 l_sa_flag := 'Y';
2339 else
2340 l_sa_flag := 'N';
2341 end if;
2342 if r_elp.information101 is not null then
2343 l_bu_flag := 'Y';
2344 else
2345 l_bu_flag := 'N';
2346 end if;
2347 if r_elp.information102 is not null then
2348 l_fp_flag := 'Y';
2349 else
2350 l_fp_flag := 'N';
2351 end if;
2352 if r_elp.information103 is not null then
2353 l_pr_flag := 'Y';
2354 else
2355 l_pr_flag := 'N';
2356 end if;
2357 if l_dml_operation = 'INSERT' then
2358 hr_utility.set_location(' BEN_ELIGY_PRFL_F CREATE_ELIGY_PROFILE ',20);
2359 begin
2360 BEN_ELIGY_PROFILE_API.CREATE_ELIGY_PROFILE(
2361 P_EFFECTIVE_DATE => l_effective_date
2362 ,P_BUSINESS_GROUP_ID => p_business_group_id
2363 ,P_ASMT_TO_USE_CD => 'ANY'
2364 ,P_BNFT_CAGR_PRTN_CD => 'BNFT'
2365 ,P_DESCRIPTION => r_ELP.INFORMATION151
2366 ,P_ELIGY_PRFL_ID => l_elp_id
2367 ,P_ELIGY_PRFL_RL_FLAG => 'N'
2368 ,P_ELIG_BRGNG_UNIT_FLAG => 'N'
2369 ,P_ELIG_FL_TM_PT_TM_FLAG => 'N'
2370 ,P_ELIG_JOB_FLAG => 'N'
2371 ,P_ELIG_ORG_UNIT_FLAG => 'N'
2372 ,P_ELIG_PERF_RTNG_FLAG => 'N'
2373 ,P_ELIG_PER_TYP_FLAG => 'N'
2374 ,P_ELIG_SVC_AREA_FLAG => 'N'
2375 ,P_ELIG_WK_LOC_FLAG => 'N'
2376 ,P_NAME => r_ELP.INFORMATION151
2377 ,P_STAT_CD => 'A'
2378 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2379 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2380 ,P_OBJECT_VERSION_NUMBER => l_elp_ovn
2381 );
2382 elp_writeback(p_crset_id => r_elp.information161,
2383 p_elp_id => l_elp_id,
2384 p_copy_entity_txn_id => p_copy_entity_txn_id);
2385 hr_utility.set_location('effdt is '||to_char(l_effective_date,'DD/MM/RRRR'),20);
2386 if l_rl_flag = 'Y' then
2387 hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2388 hr_utility.set_location('rule id is'||r_elp.information235,20);
2389 BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2390 P_EFFECTIVE_DATE => l_effective_date
2391 ,P_BUSINESS_GROUP_ID => p_business_group_id
2392 ,P_DRVBL_FCTR_APLS_FLAG => 'N'
2393 ,P_ELIGY_PRFL_ID => l_elp_id
2394 ,P_ELIGY_PRFL_RL_ID => l_pk
2395 ,P_FORMULA_ID => r_elp.information235
2396 ,P_ORDR_TO_APLY_NUM => 1
2397 ,P_EFFECTIVE_START_DATE => l_esd
2398 ,P_EFFECTIVE_END_DATE => l_eed
2399 ,P_OBJECT_VERSION_NUMBER => l_ovn
2400 );
2401 end if;
2402 if l_bu_flag = 'Y' then
2403 hr_utility.set_location(' BEN_ELIG_BRGNG_UNIT_PRTE_F CREATE_ELIG_BRGNG_UNIT_PRTE ',20);
2404 hr_utility.set_location('bargaining unit cd is'||r_elp.information101,20);
2405 BEN_ELIG_BRGNG_UNIT_PRTE_API.CREATE_ELIG_BRGNG_UNIT_PRTE(
2406 P_EFFECTIVE_DATE => l_effective_date
2407 ,P_BUSINESS_GROUP_ID => p_business_group_id
2408 ,P_BRGNG_UNIT_CD => r_elp.information101
2409 ,P_ELIGY_PRFL_ID => l_elp_id
2410 ,P_ELIG_BRGNG_UNIT_PRTE_ID => l_pk
2411 ,P_EXCLD_FLAG => 'N'
2412 ,P_ORDR_NUM => 1
2413 ,P_EFFECTIVE_START_DATE => l_esd
2414 ,P_EFFECTIVE_END_DATE => l_eed
2415 ,P_OBJECT_VERSION_NUMBER => l_ovn
2416 );
2417 end if;
2418 if l_fp_flag = 'Y' then
2419 hr_utility.set_location(' BEN_ELIG_FL_TM_PT_TM_PRTE_F CREATE_ELIG_FL_TM_PT_TM_PRTE ',20);
2420 hr_utility.set_location('fulltime cd is'||r_elp.information102,20);
2421 BEN_ELIG_FL_TM_PT_TM_PRTE_API.CREATE_ELIG_FL_TM_PT_TM_PRTE(
2422 P_EFFECTIVE_DATE => l_effective_date
2423 ,P_BUSINESS_GROUP_ID => p_business_group_id
2424 ,P_ELIGY_PRFL_ID => l_elp_id
2425 ,P_ELIG_FL_TM_PT_TM_PRTE_ID => l_pk
2426 ,P_EXCLD_FLAG => 'N'
2427 ,P_FL_TM_PT_TM_CD => r_elp.INFORMATION102
2428 ,P_ORDR_NUM => 1
2429 ,P_EFFECTIVE_START_DATE => l_esd
2430 ,P_EFFECTIVE_END_DATE => l_eed
2431 ,P_OBJECT_VERSION_NUMBER => l_ovn
2432 );
2433 end if;
2434 if l_pr_flag = 'Y' then
2435 hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2436 hr_utility.set_location('event type is'||r_elp.information103,20);
2437 hr_utility.set_location('perf_rtng_cd is'||r_elp.information104,20);
2438 BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2439 P_EFFECTIVE_DATE => l_effective_date
2440 ,P_BUSINESS_GROUP_ID => p_business_group_id
2441 ,P_ELIGY_PRFL_ID => l_elp_id
2442 ,P_ELIG_PERF_RTNG_PRTE_ID => l_pk
2443 ,P_EVENT_TYPE => r_elp.information103
2444 ,P_EXCLD_FLAG => 'N'
2445 ,P_ORDR_NUM => 1
2446 ,P_PERF_RTNG_CD => r_elp.information104
2447 ,P_EFFECTIVE_START_DATE => l_esd
2448 ,P_EFFECTIVE_END_DATE => l_eed
2449 ,P_OBJECT_VERSION_NUMBER => l_ovn
2450 );
2451 end if;
2452 if l_pt_flag = 'Y' then
2453 l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2454 hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2455 hr_utility.set_location('per type id is'||r_elp.information236,20);
2456 hr_utility.set_location('per type cd is'||l_pt_cd,20);
2457 BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2458 P_EFFECTIVE_DATE => l_effective_date
2459 ,P_BUSINESS_GROUP_ID => p_business_group_id
2460 ,P_ELIGY_PRFL_ID => l_elp_id
2461 ,P_ELIG_PER_TYP_PRTE_ID => l_pk
2462 ,P_EXCLD_FLAG => 'N'
2463 ,P_ORDR_NUM => 1
2464 ,P_PERSON_TYPE_ID => r_elp.information236
2465 ,P_PER_TYP_CD => l_pt_cd
2466 ,P_EFFECTIVE_START_DATE => l_esd
2467 ,P_EFFECTIVE_END_DATE => l_eed
2468 ,P_OBJECT_VERSION_NUMBER => l_ovn
2469 );
2470 end if;
2471 if l_sa_flag = 'Y' then
2472 hr_utility.set_location(' BEN_ELIG_SVC_AREA_PRTE_F CREATE_ELIG_SVC_AREA_PRTE ',20);
2473 hr_utility.set_location('serv area id is'||r_elp.information237,20);
2474 BEN_ELIG_SVC_AREA_PRTE_API.CREATE_ELIG_SVC_AREA_PRTE(
2475 P_EFFECTIVE_DATE => l_effective_date
2476 ,P_BUSINESS_GROUP_ID => p_business_group_id
2477 ,P_ELIGY_PRFL_ID => l_elp_id
2478 ,P_ELIG_SVC_AREA_PRTE_ID => l_pk
2479 ,P_EXCLD_FLAG => 'N'
2480 ,P_ORDR_NUM => 1
2481 ,P_SVC_AREA_ID => r_elp.information237
2482 ,P_EFFECTIVE_START_DATE => l_esd
2483 ,P_EFFECTIVE_END_DATE => l_eed
2484 ,P_OBJECT_VERSION_NUMBER => l_ovn
2485 );
2486 end if;
2487 if l_loc_flag = 'Y' then
2488 hr_utility.set_location(' BEN_ELIG_WK_LOC_PRTE_F CREATE_ELIG_WK_LOC_PRTE ',20);
2489 hr_utility.set_location('location id is'||r_elp.information232,20);
2490 BEN_ELIG_WK_LOC_PRTE_API.CREATE_ELIG_WK_LOC_PRTE(
2491 P_EFFECTIVE_DATE => l_effective_date
2492 ,P_BUSINESS_GROUP_ID => p_business_group_id
2493 ,P_ELIGY_PRFL_ID => l_elp_id
2494 ,P_ELIG_WK_LOC_PRTE_ID => l_pk
2495 ,P_EXCLD_FLAG => 'N'
2496 ,P_LOCATION_ID => r_elp.information232
2497 ,P_ORDR_NUM => 1
2498 ,P_EFFECTIVE_START_DATE => l_esd
2499 ,P_EFFECTIVE_END_DATE => l_eed
2500 ,P_OBJECT_VERSION_NUMBER => l_ovn
2501 );
2502 end if;
2503 if l_org_flag = 'Y' then
2504 hr_utility.set_location(' BEN_ELIG_ORG_UNIT_PRTE_F CREATE_ELIG_ORG_UNIT_PRTE ',20);
2505 hr_utility.set_location('org id is'||r_elp.information234,20);
2506 BEN_ELIG_ORG_UNIT_PRTE_API.CREATE_ELIG_ORG_UNIT_PRTE(
2507 P_EFFECTIVE_DATE => l_effective_date
2508 ,P_BUSINESS_GROUP_ID => p_business_group_id
2509 ,P_ELIGY_PRFL_ID => l_elp_id
2510 ,P_ELIG_ORG_UNIT_PRTE_ID => l_pk
2511 ,P_EXCLD_FLAG => 'N'
2512 ,P_ORDR_NUM => 1
2513 ,P_ORGANIZATION_ID => r_elp.information234
2514 ,P_EFFECTIVE_START_DATE => l_esd
2515 ,P_EFFECTIVE_END_DATE => l_eed
2516 ,P_OBJECT_VERSION_NUMBER => l_ovn
2517 );
2518 end if;
2519 if l_job_flag = 'Y' then
2520 hr_utility.set_location(' BEN_ELIG_JOB_PRTE_F CREATE_ELIGY_JOB_PRTE ',20);
2521 hr_utility.set_location('org id is'||r_elp.information233,20);
2522 BEN_ELIGY_JOB_PRTE_API.CREATE_ELIGY_JOB_PRTE(
2523 P_EFFECTIVE_DATE => l_effective_date
2524 ,P_BUSINESS_GROUP_ID => p_business_group_id
2525 ,P_ELIGY_PRFL_ID => l_elp_id
2526 ,P_ELIG_JOB_PRTE_ID => l_pk
2527 ,P_EXCLD_FLAG => 'N'
2528 ,P_JOB_ID => r_elp.information233
2529 ,P_ORDR_NUM => 1
2530 ,P_EFFECTIVE_START_DATE => l_esd
2531 ,P_EFFECTIVE_END_DATE => l_eed
2532 ,P_OBJECT_VERSION_NUMBER => l_ovn
2533 );
2534 end if;
2535 exception when others then
2536 hr_utility.set_location('issues in writing elp, skipping'||l_proc,100);
2537 raise;
2538 end;
2539 hr_utility.set_location('After plsql table ',222);
2540 elsif l_dml_operation = 'UPDATE' then
2541 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2542 if p_datetrack_mode <> 'CORRECTION' then
2543 l_dt_mode := get_update_mode(p_table_name => 'BEN_ELIGY_PRFL_F',
2544 p_key_column_name => 'ELIGY_PRFL_ID',
2545 p_key_column_value => l_elp_id,
2546 p_effective_date => l_effective_date);
2547 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2548 else
2549 l_dt_mode := p_datetrack_mode;
2550 end if;
2551 l_db_ovn := get_ovn(p_table_name => 'BEN_ELIGY_PRFL_F',
2552 p_key_column_name => 'ELIGY_PRFL_ID',
2553 p_key_column_value => l_elp_id,
2554 p_effective_date => l_effective_date);
2555 hr_utility.set_location(' ovn is '||l_db_ovn,30);
2556 if l_db_ovn <> l_ovn then
2557 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','ELP');
2558 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2559 fnd_message.set_token('OBJECT ',l_object);
2560 fnd_message.set_token('OBJECT_NAME ',r_ELP.INFORMATION151);
2561 fnd_message.raise_error;
2562 else
2563 hr_utility.set_location(' BEN_ELIGY_PRFL_F UPDATE_ELIGY_PROFILE ',30);
2564 begin
2565 BEN_ELIGY_PROFILE_API.UPDATE_ELIGY_PROFILE(
2566 P_EFFECTIVE_DATE => l_effective_date
2567 ,P_BUSINESS_GROUP_ID => p_business_group_id
2568 ,P_DESCRIPTION => r_ELP.INFORMATION151
2569 ,P_ELIGY_PRFL_ID => l_elp_id
2570 ,P_ELIGY_PRFL_RL_FLAG => 'N'
2571 ,P_ELIG_BRGNG_UNIT_FLAG => 'N'
2572 ,P_ELIG_FL_TM_PT_TM_FLAG => 'N'
2573 ,P_ELIG_JOB_FLAG => 'N'
2574 ,P_ELIG_ORG_UNIT_FLAG => 'N'
2575 ,P_ELIG_PERF_RTNG_FLAG => 'N'
2576 ,P_ELIG_PER_TYP_FLAG => 'N'
2577 ,P_ELIG_SVC_AREA_FLAG => 'N'
2578 ,P_ELIG_WK_LOC_FLAG => 'N'
2579 ,P_NAME => r_ELP.INFORMATION151
2580 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2581 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2582 ,P_OBJECT_VERSION_NUMBER => l_elp_ovn
2583 ,P_DATETRACK_MODE => l_dt_mode
2584 );
2585 exception when others then
2586 hr_utility.set_location('issues in writing elp, skipping'||l_proc,100);
2587 raise;
2588 end;
2589 end if;
2590 if l_rl_flag = 'Y' and l_old_rl_flag is null and r_elp.information225 is null then
2591 hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2592 BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2593 P_EFFECTIVE_DATE => l_effective_date
2594 ,P_BUSINESS_GROUP_ID => p_business_group_id
2595 ,P_DRVBL_FCTR_APLS_FLAG => 'N'
2596 ,P_ELIGY_PRFL_ID => l_elp_id
2597 ,P_ELIGY_PRFL_RL_ID => l_pk
2598 ,P_FORMULA_ID => r_elp.information235
2599 ,P_ORDR_TO_APLY_NUM => 1
2600 ,P_EFFECTIVE_START_DATE => l_esd
2601 ,P_EFFECTIVE_END_DATE => l_eed
2602 ,P_OBJECT_VERSION_NUMBER => l_ovn
2603 );
2604 elsif l_rl_flag is null and l_old_rl_flag = 'Y' and r_elp.information225 is null then
2605 end_date_crit(p_elig_prfl_id => l_elp_id,
2606 p_crit_type => 'RL',
2607 p_effective_date => l_effective_date);
2608 end if;
2609 if l_bu_flag = 'Y' and l_old_bu_flag is null and r_elp.information228 is null then
2610 hr_utility.set_location(' BEN_ELIG_BRGNG_UNIT_PRTE_F CREATE_ELIG_BRGNG_UNIT_PRTE ',20);
2611 BEN_ELIG_BRGNG_UNIT_PRTE_API.CREATE_ELIG_BRGNG_UNIT_PRTE(
2612 P_EFFECTIVE_DATE => l_effective_date
2613 ,P_BUSINESS_GROUP_ID => p_business_group_id
2614 ,P_BRGNG_UNIT_CD => r_elp.information101
2615 ,P_ELIGY_PRFL_ID => l_elp_id
2616 ,P_ELIG_BRGNG_UNIT_PRTE_ID => l_pk
2617 ,P_EXCLD_FLAG => 'N'
2618 ,P_ORDR_NUM => 1
2619 ,P_EFFECTIVE_START_DATE => l_esd
2620 ,P_EFFECTIVE_END_DATE => l_eed
2621 ,P_OBJECT_VERSION_NUMBER => l_ovn
2622 );
2623 elsif l_bu_flag is null and l_old_bu_flag = 'Y' and r_elp.information228 is null then
2624 end_date_crit(p_elig_prfl_id => l_elp_id,
2625 p_crit_type => 'BU',
2626 p_effective_date => l_effective_date);
2627 end if;
2628 if l_fp_flag = 'Y' and l_old_fp_flag is null and r_elp.information229 is null then
2629 hr_utility.set_location(' BEN_ELIG_FL_TM_PT_TM_PRTE_F CREATE_ELIG_FL_TM_PT_TM_PRTE ',20);
2630 BEN_ELIG_FL_TM_PT_TM_PRTE_API.CREATE_ELIG_FL_TM_PT_TM_PRTE(
2631 P_EFFECTIVE_DATE => l_effective_date
2632 ,P_BUSINESS_GROUP_ID => p_business_group_id
2633 ,P_ELIGY_PRFL_ID => l_elp_id
2634 ,P_ELIG_FL_TM_PT_TM_PRTE_ID => l_pk
2635 ,P_EXCLD_FLAG => 'N'
2636 ,P_FL_TM_PT_TM_CD => r_elp.INFORMATION102
2637 ,P_ORDR_NUM => 1
2638 ,P_EFFECTIVE_START_DATE => l_esd
2639 ,P_EFFECTIVE_END_DATE => l_eed
2640 ,P_OBJECT_VERSION_NUMBER => l_ovn
2641 );
2642 elsif l_fp_flag is null and l_old_fp_flag = 'Y' and r_elp.information229 is null then
2643 end_date_crit(p_elig_prfl_id => l_elp_id,
2644 p_crit_type => 'FP',
2645 p_effective_date => l_effective_date);
2646 end if;
2647 if l_pr_flag = 'Y' and l_old_pr_flag is null and r_elp.information230 is null then
2648 hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2649 BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2650 P_EFFECTIVE_DATE => l_effective_date
2651 ,P_BUSINESS_GROUP_ID => p_business_group_id
2652 ,P_ELIGY_PRFL_ID => l_elp_id
2653 ,P_ELIG_PERF_RTNG_PRTE_ID => l_pk
2654 ,P_EVENT_TYPE => r_elp.information104
2655 ,P_EXCLD_FLAG => 'N'
2656 ,P_ORDR_NUM => 1
2657 ,P_PERF_RTNG_CD => r_elp.information103
2658 ,P_EFFECTIVE_START_DATE => l_esd
2659 ,P_EFFECTIVE_END_DATE => l_eed
2660 ,P_OBJECT_VERSION_NUMBER => l_ovn
2661 );
2662 elsif l_pr_flag is null and l_old_pr_flag = 'Y' and r_elp.information230 is null then
2663 end_date_crit(p_elig_prfl_id => l_elp_id,
2664 p_crit_type => 'PR',
2665 p_effective_date => l_effective_date);
2666 end if;
2667 if l_pt_flag = 'Y' and l_old_pt_flag is null and r_elp.information226 is null then
2668 l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2669 hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2670 BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2671 P_EFFECTIVE_DATE => l_effective_date
2672 ,P_BUSINESS_GROUP_ID => p_business_group_id
2673 ,P_ELIGY_PRFL_ID => l_elp_id
2674 ,P_ELIG_PER_TYP_PRTE_ID => l_pk
2675 ,P_EXCLD_FLAG => 'N'
2676 ,P_ORDR_NUM => 1
2677 ,P_PERSON_TYPE_ID => r_elp.information236
2678 ,P_PER_TYP_CD => l_pt_cd
2679 ,P_EFFECTIVE_START_DATE => l_esd
2680 ,P_EFFECTIVE_END_DATE => l_eed
2681 ,P_OBJECT_VERSION_NUMBER => l_ovn
2682 );
2683 elsif l_pt_flag is null and l_old_pt_flag = 'Y' and r_elp.information226 is null then
2684 end_date_crit(p_elig_prfl_id => l_elp_id,
2685 p_crit_type => 'PT',
2686 p_effective_date => l_effective_date);
2687 end if;
2688 if l_sa_flag = 'Y' and l_old_sa_flag is null and r_elp.information227 is null then
2689 hr_utility.set_location(' BEN_ELIG_SVC_AREA_PRTE_F CREATE_ELIG_SVC_AREA_PRTE ',20);
2690 BEN_ELIG_SVC_AREA_PRTE_API.CREATE_ELIG_SVC_AREA_PRTE(
2691 P_EFFECTIVE_DATE => l_effective_date
2692 ,P_BUSINESS_GROUP_ID => p_business_group_id
2693 ,P_ELIGY_PRFL_ID => l_elp_id
2694 ,P_ELIG_SVC_AREA_PRTE_ID => l_pk
2695 ,P_EXCLD_FLAG => 'N'
2696 ,P_ORDR_NUM => 1
2697 ,P_SVC_AREA_ID => r_elp.information237
2698 ,P_EFFECTIVE_START_DATE => l_esd
2699 ,P_EFFECTIVE_END_DATE => l_eed
2700 ,P_OBJECT_VERSION_NUMBER => l_ovn
2701 );
2702 elsif l_sa_flag is null and l_old_sa_flag = 'Y' and r_elp.information227 is null then
2703 end_date_crit(p_elig_prfl_id => l_elp_id,
2704 p_crit_type => 'SA',
2705 p_effective_date => l_effective_date);
2706 end if;
2707 if l_loc_flag = 'Y' and l_old_loc_flag is null and r_elp.information222 is null then
2708 hr_utility.set_location(' BEN_ELIG_WK_LOC_PRTE_F CREATE_ELIG_WK_LOC_PRTE ',20);
2709 BEN_ELIG_WK_LOC_PRTE_API.CREATE_ELIG_WK_LOC_PRTE(
2710 P_EFFECTIVE_DATE => l_effective_date
2711 ,P_BUSINESS_GROUP_ID => p_business_group_id
2712 ,P_ELIGY_PRFL_ID => l_elp_id
2713 ,P_ELIG_WK_LOC_PRTE_ID => l_pk
2714 ,P_EXCLD_FLAG => 'N'
2715 ,P_LOCATION_ID => r_elp.information232
2716 ,P_ORDR_NUM => 1
2717 ,P_EFFECTIVE_START_DATE => l_esd
2718 ,P_EFFECTIVE_END_DATE => l_eed
2719 ,P_OBJECT_VERSION_NUMBER => l_ovn
2720 );
2721 elsif l_loc_flag is null and l_old_loc_flag = 'Y' and r_elp.information222 is null then
2722 end_date_crit(p_elig_prfl_id => l_elp_id,
2723 p_crit_type => 'LOC',
2724 p_effective_date => l_effective_date);
2725 end if;
2726 if l_org_flag = 'Y' and l_old_org_flag is null and r_elp.information224 is null then
2727 hr_utility.set_location(' BEN_ELIG_ORG_UNIT_PRTE_F CREATE_ELIG_ORG_UNIT_PRTE ',20);
2728 BEN_ELIG_ORG_UNIT_PRTE_API.CREATE_ELIG_ORG_UNIT_PRTE(
2729 P_EFFECTIVE_DATE => l_effective_date
2730 ,P_BUSINESS_GROUP_ID => p_business_group_id
2731 ,P_ELIGY_PRFL_ID => l_elp_id
2732 ,P_ELIG_ORG_UNIT_PRTE_ID => l_pk
2733 ,P_EXCLD_FLAG => 'N'
2734 ,P_ORDR_NUM => 1
2735 ,P_ORGANIZATION_ID => r_elp.information234
2736 ,P_EFFECTIVE_START_DATE => l_esd
2737 ,P_EFFECTIVE_END_DATE => l_eed
2738 ,P_OBJECT_VERSION_NUMBER => l_ovn
2739 );
2740 elsif l_org_flag is null and l_old_org_flag = 'Y' and r_elp.information224 is null then
2741 end_date_crit(p_elig_prfl_id => l_elp_id,
2742 p_crit_type => 'ORG',
2743 p_effective_date => l_effective_date);
2744 end if;
2745 if l_job_flag = 'Y' and l_old_job_flag is null and r_elp.information223 is null then
2746 hr_utility.set_location(' BEN_ELIG_JOB_PRTE_F CREATE_ELIGY_JOB_PRTE ',20);
2747 BEN_ELIGY_JOB_PRTE_API.CREATE_ELIGY_JOB_PRTE(
2748 P_EFFECTIVE_DATE => l_effective_date
2749 ,P_BUSINESS_GROUP_ID => p_business_group_id
2750 ,P_ELIGY_PRFL_ID => l_elp_id
2751 ,P_ELIG_JOB_PRTE_ID => l_pk
2752 ,P_EXCLD_FLAG => 'N'
2753 ,P_JOB_ID => r_elp.information233
2754 ,P_ORDR_NUM => 1
2755 ,P_EFFECTIVE_START_DATE => l_esd
2756 ,P_EFFECTIVE_END_DATE => l_eed
2757 ,P_OBJECT_VERSION_NUMBER => l_ovn
2758 );
2759 elsif l_job_flag is null and l_old_job_flag = 'Y' and r_elp.information223 is null then
2760 end_date_crit(p_elig_prfl_id => l_elp_id,
2761 p_crit_type => 'JOB',
2762 p_effective_date => l_effective_date);
2763 end if;
2764 else
2765 l_message_text := 'invalid dml_oper'||l_dml_operation
2766 ||' elp_ovn'||l_elp_ovn
2767 ||' elp_id'||l_elp_id;
2768 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2769 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2770 P_TXN_ID => nvl(l_elp_id,p_copy_entity_txn_id),
2771 P_MODULE_CD => 'PQH_GSP_STGBEN',
2772 p_context => 'ELP',
2773 P_MESSAGE_TYPE_CD => 'E',
2774 P_MESSAGE_TEXT => l_message_text,
2775 p_effective_date => p_effective_date);
2776 end if;
2777 l_old_elp_id := l_elp_id;
2778 l_old_elp_ovn := l_elp_ovn;
2779 l_old_crset_id := r_elp.information161;
2780 l_old_sa_flag := l_sa_flag;
2781 l_old_fp_flag := l_fp_flag;
2782 l_old_pt_flag := l_pt_flag;
2783 l_old_rl_flag := l_rl_flag;
2784 l_old_bu_flag := l_bu_flag;
2785 l_old_pr_flag := l_pr_flag;
2786 l_old_loc_flag := l_loc_flag;
2787 l_old_org_flag := l_org_flag;
2788 l_old_job_flag := l_job_flag;
2789 end loop;
2790 hr_utility.set_location('leaving '||l_proc,100);
2791 exception
2792 when others then
2793 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2794 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2795 P_TXN_ID => p_copy_entity_txn_id,
2796 P_MODULE_CD => 'PQH_GSP_STGBEN',
2797 p_context => 'ELP',
2798 P_MESSAGE_TYPE_CD => 'E',
2799 P_MESSAGE_TEXT => 'ELP',
2800 p_effective_date => p_effective_date);
2801 raise;
2802 end stage_to_elp;
2803 procedure stage_to_cep(p_copy_entity_txn_id in number,
2804 p_business_group_id in number,
2805 p_effective_date in date,
2806 p_datetrack_mode in varchar2) is
2807 cursor c_cep is
2808 select *
2809 from ben_copy_entity_results
2810 where copy_entity_txn_id = p_copy_entity_txn_id
2811 and table_alias = 'CEP'
2812 and dml_operation in ('INSERT','UPDATE') ; -- only insert/update should be there
2813 --
2814 r_cep c_cep%rowtype;
2815 l_proc varchar2(61) :='stage_to_cep';
2816 l_cep_id number ;
2817 l_ovn number ;
2818 l_effective_start_date date ;
2819 l_effective_end_date date ;
2820 l_effective_date date;
2821 l_message_text varchar2(2000);
2822 l_dt_mode varchar2(30);
2823 l_object varchar2(80);
2824 l_db_ovn varchar2(30);
2825 l_epa_id number;
2826 l_pk number;
2827 l_tab varchar2(30);
2828 begin
2829 hr_utility.set_location('inside '||l_proc,10);
2830 for r_cep in c_cep loop
2831 l_cep_id := r_cep.information1;
2832 l_ovn := r_cep.information265;
2833 hr_utility.set_location('for cep_id:'||l_cep_id ||'dml '||r_cep.dml_operation,20);
2834 if r_cep.gs_mirror_src_entity_result_id is not null then
2835 select information1,table_alias
2836 into l_pk, l_tab
2837 from ben_copy_entity_results
2838 where copy_entity_result_id = r_cep.gs_mirror_src_entity_result_id;
2839 hr_utility.set_location('parent tab is'||l_tab ||' pk is '||l_pk,5);
2840 if l_tab ='EPA' then
2841 l_epa_id := l_pk;
2842 else
2843 l_epa_id := null;
2844 end if;
2845 else
2846 l_epa_id := null;
2847 end if;
2848 l_effective_date := r_cep.information2;
2849 begin
2850 if l_cep_id is null and l_epa_id is not null and l_ovn is null and r_cep.dml_operation = 'INSERT' then
2851 hr_utility.set_location(' BEN_PRTN_ELIG_PRFL_F CREATE_PRTN_ELIG_PRFL ',20);
2852 BEN_PRTN_ELIG_PRFL_API.CREATE_PRTN_ELIG_PRFL(
2853 P_EFFECTIVE_DATE => l_effective_date
2854 ,P_BUSINESS_GROUP_ID => p_business_group_id
2855 ,P_ELIGY_PRFL_ID => r_CEP.INFORMATION263
2856 ,P_ELIG_PRFL_TYPE_CD => r_CEP.INFORMATION11
2857 ,P_MNDTRY_FLAG => nvl(r_CEP.INFORMATION12,'N')
2858 ,P_COMPUTE_SCORE_FLAG => r_CEP.INFORMATION13
2859 ,P_PRTN_ELIG_ID => l_epa_id
2860 ,P_PRTN_ELIG_PRFL_ID => l_cep_id
2861 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2862 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2863 ,P_OBJECT_VERSION_NUMBER => l_ovn
2864 );
2865 elsif l_cep_id is not null and l_epa_id is not null and l_ovn is not null and r_cep.dml_operation = 'UPDATE' then
2866 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2867 if p_datetrack_mode <> 'CORRECTION' then
2868 l_dt_mode := get_update_mode(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2869 p_key_column_name => 'PRTN_ELIG_PRFL_ID',
2870 p_key_column_value => l_cep_id,
2871 p_effective_date => l_effective_date);
2872 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2873 else
2874 l_dt_mode := p_datetrack_mode;
2875 end if;
2876 l_db_ovn := get_ovn(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2877 p_key_column_name => 'PRTN_ELIG_PRFL_ID',
2878 p_key_column_value => l_cep_id,
2879 p_effective_date => l_effective_date);
2880 hr_utility.set_location(' ovn is '||l_db_ovn,30);
2881 if l_db_ovn <> l_ovn then
2882 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','CEP');
2883 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2884 fnd_message.set_token('OBJECT ',l_object);
2885 fnd_message.set_token('OBJECT_NAME ','CEP :'||l_cep_id);
2886 fnd_message.raise_error;
2887 else
2888 hr_utility.set_location(' BEN_PRTN_ELIG_PRFL_F UPDATE_PRTN_ELIG_PRFL ',30);
2889 BEN_PRTN_ELIG_PRFL_API.UPDATE_PRTN_ELIG_PRFL(
2890 P_EFFECTIVE_DATE => l_effective_date
2891 ,P_BUSINESS_GROUP_ID => p_business_group_id
2892 ,P_ELIGY_PRFL_ID => r_CEP.INFORMATION263
2893 ,P_MNDTRY_FLAG => r_CEP.INFORMATION12
2894 ,P_COMPUTE_SCORE_FLAG => r_CEP.INFORMATION13
2895 ,P_PRTN_ELIG_ID => l_epa_id
2896 ,P_PRTN_ELIG_PRFL_ID => l_cep_id
2897 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2898 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2899 ,P_OBJECT_VERSION_NUMBER => l_ovn
2900 ,P_DATETRACK_MODE => l_dt_mode);
2901 end if;
2902 else
2903 l_message_text := 'invalid dml_oper'||r_cep.dml_operation
2904 ||' cep_id '||l_cep_id
2905 ||' cep_ovn '||l_ovn
2906 ||' epa_id '||l_epa_id;
2907 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2908 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2909 P_TXN_ID => nvl(l_cep_id,p_copy_entity_txn_id),
2910 P_MODULE_CD => 'PQH_GSP_STGBEN',
2911 p_context => 'CEP',
2912 P_MESSAGE_TYPE_CD => 'E',
2913 P_MESSAGE_TEXT => l_message_text,
2914 p_effective_date => p_effective_date);
2915 end if;
2916 exception when others then
2917 hr_utility.set_location('issues in writing cep, skipping'||l_proc,100);
2918 raise;
2919 end;
2920 end loop;
2921 hr_utility.set_location('leaving '||l_proc,100);
2922 exception
2923 when others then
2924 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2925 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2926 P_TXN_ID => p_copy_entity_txn_id,
2927 P_MODULE_CD => 'PQH_GSP_STGBEN',
2928 p_context => 'CEP',
2929 P_MESSAGE_TYPE_CD => 'E',
2930 P_MESSAGE_TEXT => 'CEP',
2931 p_effective_date => p_effective_date);
2932 raise;
2933 end stage_to_cep;
2934 procedure stage_to_abr(p_copy_entity_txn_id in number,
2935 p_business_group_id in number,
2936 p_effective_date in date,
2937 p_datetrack_mode in varchar2) is
2938 cursor c_abr is
2939 select *
2940 from ben_copy_entity_results
2941 where copy_entity_txn_id = p_copy_entity_txn_id
2942 and table_alias = 'ABR'
2943 and dml_operation = 'INSERT' ;-- only insert should be there
2944 --
2945 r_abr c_abr%rowtype;
2946 l_proc varchar2(61) :='stage_to_abr';
2947 l_abr_id number ;
2948 l_pl_id number ;
2949 l_message_text varchar2(2000);
2950 l_opt_id number ;
2951 l_ovn number ;
2952 l_effective_start_date date ;
2953 l_effective_end_date date ;
2954 l_effective_date date;
2955 begin
2956 hr_utility.set_location('inside '||l_proc,10);
2957 for r_abr in c_abr loop
2958 l_pl_id := null;
2959 l_opt_id := null;
2960 l_abr_id := r_abr.information1;
2961 hr_utility.set_location('for abr_id:'||l_abr_id ||'dml '||r_abr.dml_operation,20);
2962 l_effective_date := r_abr.information2;
2963 hr_utility.set_location('effdt is'||to_char(l_effective_date,'DD/MM/RRRR'),21);
2964 hr_utility.set_location('hrr id is '||r_ABR.INFORMATION266,22);
2965 if r_ABR.INFORMATION277 is not null and r_ABR.INFORMATION261 is null then
2966 hr_utility.set_location('pl_cer_id :'||r_ABR.INFORMATION277,3);
2967 begin
2968 select information1
2969 into l_pl_id
2970 from ben_copy_entity_results
2971 where copy_entity_result_id = r_ABR.INFORMATION277;
2972 exception
2973 when others then
2974 l_pl_id := '';
2975 end;
2976 elsif r_ABR.INFORMATION261 is not null then
2977 l_pl_id := r_ABR.INFORMATION261;
2978 elsif r_ABR.INFORMATION247 is not null then
2979 l_opt_id := r_ABR.INFORMATION247;
2980 elsif r_ABR.INFORMATION278 is not null and r_ABR.INFORMATION247 is null then
2981 hr_utility.set_location('opt_cer_id :'||r_ABR.INFORMATION278,3);
2982 begin
2983 select information1
2984 into l_opt_id
2985 from ben_copy_entity_results
2986 where copy_entity_result_id = r_ABR.INFORMATION278;
2987 exception
2988 when others then
2989 l_opt_id := '';
2990 end;
2991 else
2992 hr_utility.set_location('pl id is '||r_ABR.INFORMATION261,3);
2993 hr_utility.set_location('pl cer id is '||r_ABR.INFORMATION277,3);
2994 hr_utility.set_location('opt id is '||r_ABR.INFORMATION247,3);
2995 hr_utility.set_location('opt cer id is '||r_ABR.INFORMATION278,3);
2996 end if;
2997 /*
2998 l_message_text := 'oper is'||r_ABR.dml_operation
2999 ||' pl id is '||r_ABR.INFORMATION261
3000 ||' abr id is '||l_abr_id
3001 ||' hrr id is '||r_abr.information266
3002 ||' abr ovn is '||l_ovn
3003 ||' opt id is '||r_ABR.INFORMATION247;
3004 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3005 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3006 P_TXN_ID => nvl(l_abr_id,p_copy_entity_txn_id),
3007 P_MODULE_CD => 'PQH_GSP_STGBEN',
3008 p_context => 'ABR',
3009 P_MESSAGE_TYPE_CD => 'C',
3010 P_MESSAGE_TEXT => l_message_text,
3011 p_effective_date => p_effective_date);
3012 */
3013 begin
3014 if l_abr_id is null and (l_pl_id is not null or l_opt_id is not null) and r_abr.information266 is not null then
3015 hr_utility.set_location(' BEN_ACTY_BASE_RT_F CREATE_ACTY_BASE_RATE ',20);
3016 BEN_ACTY_BASE_RATE_API.CREATE_ACTY_BASE_RATE(
3017 P_EFFECTIVE_DATE => l_effective_date
3018 ,P_BUSINESS_GROUP_ID => p_business_group_id
3019 ,P_ABV_MX_ELCN_VAL_ALWD_FLAG => nvl(r_ABR.INFORMATION27,'N')
3020 ,P_ACTL_PREM_ID => r_ABR.information250
3021 ,P_ACTY_BASE_RT_ID => l_abr_id
3022 ,P_ACTY_BASE_RT_STAT_CD => 'A'
3023 ,P_ACTY_TYP_CD => 'GSPSA'
3024 ,P_ALWS_CHG_CD => r_ABR.INFORMATION11
3025 ,P_ANN_MN_ELCN_VAL => r_ABR.INFORMATION298
3026 ,P_ANN_MX_ELCN_VAL => r_ABR.INFORMATION299
3027 ,P_ASMT_TO_USE_CD => r_ABR.INFORMATION23
3028 ,P_ASN_ON_ENRT_FLAG => nvl(r_ABR.INFORMATION26,'N')
3029 ,P_BLW_MN_ELCN_ALWD_FLAG => nvl(r_ABR.INFORMATION28,'N')
3030 ,P_BNFT_RT_TYP_CD => r_ABR.INFORMATION51
3031 ,P_CLM_COMP_LVL_FCTR_ID => r_ABR.information273
3032 -- ,P_CMBN_PLIP_ID => r_ABR.information239
3033 -- ,P_CMBN_PTIP_ID => r_ABR.information236
3034 -- ,P_CMBN_PTIP_OPT_ID => r_ABR.information249
3035 ,P_COMP_LVL_FCTR_ID => r_ABR.information254
3036 ,P_COST_ALLOCATION_KEYFLEX_ID => r_ABR.information262
3037 ,P_DET_PL_YTD_CNTRS_CD => r_ABR.INFORMATION24
3038 ,P_DFLT_FLAG => nvl(r_ABR.INFORMATION39,'N')
3039 ,P_DFLT_VAL => r_ABR.INFORMATION297
3040 ,P_DSPLY_ON_ENRT_FLAG => nvl(r_ABR.INFORMATION29,'N')
3041 ,P_ELEMENT_TYPE_ID => r_ABR.information174
3042 ,P_ELE_ENTRY_VAL_CD => r_ABR.INFORMATION12
3043 ,P_ELE_RQD_FLAG => nvl(r_ABR.INFORMATION45,'N')
3044 ,P_ENTR_ANN_VAL_FLAG => nvl(r_ABR.INFORMATION44,'N')
3045 ,P_ENTR_VAL_AT_ENRT_FLAG => nvl(r_ABR.INFORMATION41,'N')
3046 ,P_FRGN_ERG_DED_IDENT => r_ABR.INFORMATION141
3047 ,P_FRGN_ERG_DED_NAME => r_ABR.INFORMATION185
3048 ,P_FRGN_ERG_DED_TYP_CD => r_ABR.INFORMATION19
3049 ,P_INCRMT_ELCN_VAL => r_ABR.INFORMATION296
3050 ,P_INPUT_VALUE_ID => r_ABR.information178
3051 ,P_INPUT_VA_CALC_RL => r_ABR.information263
3052 ,P_LWR_LMT_CALC_RL => r_ABR.information268
3053 ,P_LWR_LMT_VAL => r_ABR.INFORMATION300
3054 ,P_MN_ELCN_VAL => r_ABR.INFORMATION293
3055 ,P_MX_ELCN_VAL => r_ABR.INFORMATION294
3056 ,P_NAME => r_ABR.INFORMATION170
3057 ,P_NNMNTRY_UOM => r_ABR.INFORMATION14
3058 ,P_NO_MN_ELCN_VAL_DFND_FLAG => nvl(r_ABR.INFORMATION42,'N')
3059 ,P_NO_MX_ELCN_VAL_DFND_FLAG => nvl(r_ABR.INFORMATION40,'N')
3060 ,P_NO_STD_RT_USED_FLAG => nvl(r_ABR.INFORMATION36,'N')
3061 -- ,P_OIPLIP_ID => r_ABR.information227
3062 -- ,P_OIPL_ID => r_ABR.information258
3063 ,P_ONE_ANN_PYMT_CD => r_ABR.INFORMATION46
3064 ,P_ONLY_ONE_BAL_TYP_ALWD_FLAG => nvl(r_ABR.INFORMATION43,'N')
3065 ,P_OPT_ID => l_opt_id
3066 ,P_ORDR_NUM => r_ABR.INFORMATION264
3067 ,P_PARNT_ACTY_BASE_RT_ID => r_ABR.information267
3068 ,P_PARNT_CHLD_CD => r_ABR.INFORMATION53
3069 ,P_PAY_RATE_GRADE_RULE_ID => r_ABR.INFORMATION266
3070 -- ,P_PGM_ID => r_ABR.information260
3071 -- ,P_PLIP_ID => r_ABR.information256
3072 ,P_PL_ID => l_pl_id
3073 ,P_PRDCT_FLX_CR_WHEN_ELIG_FLAG => nvl(r_ABR.INFORMATION35,'N')
3074 ,P_PROCG_SRC_CD => r_ABR.INFORMATION18
3075 ,P_PROC_EACH_PP_DFLT_FLAG => nvl(r_ABR.INFORMATION34,'N')
3076 ,P_PRORT_MN_ANN_ELCN_VAL_CD => r_ABR.INFORMATION47
3077 ,P_PRORT_MN_ANN_ELCN_VAL_RL => r_ABR.information274
3078 ,P_PRORT_MX_ANN_ELCN_VAL_CD => r_ABR.INFORMATION48
3079 ,P_PRORT_MX_ANN_ELCN_VAL_RL => r_ABR.information275
3080 ,P_PRTL_MO_DET_MTHD_CD => r_ABR.INFORMATION16
3081 ,P_PRTL_MO_DET_MTHD_RL => r_ABR.information281
3082 ,P_PRTL_MO_EFF_DT_DET_CD => r_ABR.INFORMATION20
3083 ,P_PRTL_MO_EFF_DT_DET_RL => r_ABR.information280
3084 ,P_PTD_COMP_LVL_FCTR_ID => r_ABR.information272
3085 -- ,P_PTIP_ID => r_ABR.information259
3086 ,P_RCRRG_CD => r_ABR.INFORMATION13
3087 ,P_RNDG_CD => r_ABR.INFORMATION15
3088 ,P_RNDG_RL => r_ABR.information279
3089 ,P_RT_MLT_CD => 'PRV' -- use payrate value
3090 ,P_RT_TYP_CD => r_ABR.INFORMATION50
3091 ,P_RT_USG_CD => 'STD'
3092 ,P_SUBJ_TO_IMPTD_INCM_FLAG => nvl(r_ABR.INFORMATION22,'N')
3093 ,P_TTL_COMP_LVL_FCTR_ID => r_ABR.information257
3094 ,P_TX_TYP_CD => 'PRETAX'
3095 ,P_UPR_LMT_CALC_RL => r_ABR.information269
3096 ,P_UPR_LMT_VAL => r_ABR.INFORMATION301
3097 ,P_USES_DED_SCHED_FLAG => nvl(r_ABR.INFORMATION31,'N')
3098 ,P_USES_PYMT_SCHED_FLAG => nvl(r_ABR.INFORMATION37,'N')
3099 ,P_USES_VARBL_RT_FLAG => 'N' -- uses variable rate
3100 ,P_USE_CALC_ACTY_BS_RT_FLAG => 'Y' -- value is to be computed
3101 ,P_USE_TO_CALC_NET_FLX_CR_FLAG => nvl(r_ABR.INFORMATION25,'N')
3102 ,P_VAL => r_ABR.INFORMATION295
3103 ,P_VAL_CALC_RL => r_ABR.information282
3104 ,P_VAL_OVRID_ALWD_FLAG => nvl(r_ABR.INFORMATION38,'N')
3105 ,P_VSTG_FOR_ACTY_RT_ID => r_ABR.information271
3106 ,P_VSTG_SCHED_APLS_FLAG => nvl(r_ABR.INFORMATION33,'N')
3107 ,P_WSH_RL_DY_MO_NUM => r_ABR.INFORMATION270
3108 ,P_EFFECTIVE_START_DATE => l_effective_start_date
3109 ,P_EFFECTIVE_END_DATE => l_effective_end_date
3110 ,P_OBJECT_VERSION_NUMBER => l_ovn
3111 );
3112 hr_utility.set_location('After plsql table ',222);
3113 update ben_copy_entity_results
3114 set information1 = l_abr_id
3115 where copy_entity_result_id = r_abr.copy_entity_result_id;
3116 hr_utility.set_location('abr id updated '||l_abr_id,222);
3117 else
3118 l_message_text := 'invalid oper'||r_ABR.dml_operation
3119 ||' pl id is '||r_ABR.INFORMATION261
3120 ||' abr id is '||l_abr_id
3121 ||' hrr id is '||r_abr.information266
3122 ||' abr ovn is '||l_ovn
3123 ||' opt id is '||r_ABR.INFORMATION247;
3124 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3125 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3126 P_TXN_ID => nvl(l_abr_id,p_copy_entity_txn_id),
3127 P_MODULE_CD => 'PQH_GSP_STGBEN',
3128 p_context => 'ABR',
3129 P_MESSAGE_TYPE_CD => 'E',
3130 P_MESSAGE_TEXT => l_message_text,
3131 p_effective_date => p_effective_date);
3132 end if;
3133 exception when others then
3134 hr_utility.set_location('issues in writing abr, skipping'||l_proc,100);
3135 raise;
3136 end;
3137 end loop;
3138 hr_utility.set_location('leaving '||l_proc,100);
3139 exception
3140 when others then
3141 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3142 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3143 P_TXN_ID => p_copy_entity_txn_id,
3144 P_MODULE_CD => 'PQH_GSP_STGBEN',
3145 p_context => 'ABR',
3146 P_MESSAGE_TYPE_CD => 'E',
3147 P_MESSAGE_TEXT => 'ABR',
3148 p_effective_date => p_effective_date);
3149 raise;
3150 end stage_to_abr;
3151 procedure stage_to_epa(p_copy_entity_txn_id in number,
3152 p_business_group_id in number,
3153 p_effective_date in date,
3154 p_datetrack_mode in varchar2) is
3155 cursor c_epa is
3156 select *
3157 from ben_copy_entity_results
3158 where copy_entity_txn_id = p_copy_entity_txn_id
3159 and table_alias = 'EPA'
3160 and dml_operation = 'INSERT' ; -- only insert should be there
3161 --
3162 r_epa c_epa%rowtype;
3163 l_proc varchar2(61) :='stage_to_epa';
3164 l_epa_id number ;
3165 l_ovn number ;
3166 l_effective_start_date date ;
3167 l_effective_end_date date ;
3168 l_message_text varchar2(2000);
3169 l_effective_date date;
3170 l_tab varchar2(30);
3171 l_pk number;
3172 l_plip_id number;
3173 l_pl_id number;
3174 l_oipl_id number;
3175 l_pgm_id number;
3176 begin
3177 hr_utility.set_location('inside '||l_proc,10);
3178 for r_epa in c_epa loop
3179 l_epa_id := r_epa.information1;
3180 hr_utility.set_location('for epa_id:'||l_epa_id ||'dml '||r_epa.dml_operation,20);
3181 hr_utility.set_location('epa_cer_id:'||r_epa.copy_entity_result_id,20);
3182 if r_epa.gs_mirror_src_entity_result_id is not null then
3183 begin
3184 select information1,table_alias
3185 into l_pk, l_tab
3186 from ben_copy_entity_results
3187 where copy_entity_result_id = r_epa.gs_mirror_src_entity_result_id;
3188 exception
3189 when others then
3190 raise;
3191 end;
3192 hr_utility.set_location('parent tab is'||l_tab ||' pk is '||l_pk,5);
3193 if l_tab ='PGM' then
3194 l_pgm_id := l_pk;
3195 l_plip_id := null;
3196 l_oipl_id := null;
3197 l_pl_id := null;
3198 elsif l_tab = 'CPP' then
3199 l_plip_id := l_pk;
3200 l_pgm_id := null;
3201 l_oipl_id := null;
3202 l_pl_id := null;
3203 elsif l_tab = 'PLN' then
3204 l_pl_id := l_pk;
3205 l_plip_id := null;
3206 l_oipl_id := null;
3207 l_pgm_id := null;
3208 elsif l_tab = 'COP' then
3209 -- we may not have oipl id in information1 when step api creates the oipl
3210 l_oipl_id := l_pk;
3211 l_plip_id := null;
3212 l_pgm_id := null;
3213 l_pl_id := null;
3214 else
3215 l_oipl_id := null;
3216 l_plip_id := null;
3217 l_pgm_id := null;
3218 l_pl_id := null;
3219 l_pk := null;
3220 end if;
3221 end if;
3222 l_effective_date := r_epa.information2;
3223 begin
3224 if l_epa_id is null and l_pk is not null and r_epa.dml_operation = 'INSERT' then
3225 hr_utility.set_location(' BEN_PRTN_ELIG_F CREATE_PARTICIPATION_ELIG ',20);
3226 BEN_PARTICIPATION_ELIG_API.CREATE_PARTICIPATION_ELIG(
3227 P_EFFECTIVE_DATE => l_effective_date
3228 ,P_BUSINESS_GROUP_ID => p_business_group_id
3229 ,P_MX_POE_APLS_CD => r_EPA.INFORMATION17
3230 ,P_MX_POE_DET_DT_CD => r_EPA.INFORMATION13
3231 ,P_MX_POE_DET_DT_RL => r_EPA.INFORMATION269
3232 ,P_MX_POE_RL => r_EPA.INFORMATION267
3233 ,P_MX_POE_UOM => r_EPA.INFORMATION11
3234 ,P_MX_POE_VAL => r_EPA.INFORMATION266
3235 ,P_OIPL_ID => l_oipl_id
3236 ,P_PGM_ID => l_pgm_id
3237 ,P_PLIP_ID => l_plip_id
3238 ,P_PL_ID => l_pl_id
3239 ,P_PRTN_EFF_END_DT_CD => r_EPA.INFORMATION16
3240 ,P_PRTN_EFF_END_DT_RL => r_EPA.INFORMATION271
3241 ,P_PRTN_EFF_STRT_DT_CD => r_EPA.INFORMATION15
3242 ,P_PRTN_EFF_STRT_DT_RL => r_EPA.INFORMATION270
3243 ,P_PRTN_ELIG_ID => l_epa_id
3244 ,P_PTIP_ID => r_EPA.INFORMATION259
3245 ,P_WAIT_PERD_DT_TO_USE_CD => r_EPA.INFORMATION12
3246 ,P_WAIT_PERD_DT_TO_USE_RL => r_EPA.INFORMATION264
3247 ,P_WAIT_PERD_RL => r_EPA.INFORMATION268
3248 ,P_WAIT_PERD_UOM => r_EPA.INFORMATION14
3249 ,P_WAIT_PERD_VAL => r_EPA.INFORMATION287
3250 ,P_EFFECTIVE_START_DATE => l_effective_start_date
3251 ,P_EFFECTIVE_END_DATE => l_effective_end_date
3252 ,P_OBJECT_VERSION_NUMBER => l_ovn
3253 );
3254 update ben_copy_entity_results
3255 set information1 = l_epa_id
3256 where copy_entity_result_id = r_epa.copy_entity_result_id;
3257 else
3258 l_message_text := 'invalid oper'||r_epa.dml_operation
3259 ||' epa_id is'||l_epa_id
3260 ||' epa_ovn is'||l_ovn
3261 ||' l_tab is'||l_tab
3262 ||' l_pk is'||l_pk;
3263 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3264 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3265 P_TXN_ID => nvl(l_epa_id,p_copy_entity_txn_id),
3266 P_MODULE_CD => 'PQH_GSP_STGBEN',
3267 p_context => 'EPA',
3268 P_MESSAGE_TYPE_CD => 'E',
3269 P_MESSAGE_TEXT => l_message_text,
3270 p_effective_date => p_effective_date);
3271 end if;
3272 exception when others then
3273 hr_utility.set_location('issues in writing epa, skipping'||l_proc,100);
3274 raise;
3275 end;
3276 end loop;
3277 hr_utility.set_location('leaving '||l_proc,100);
3278 exception
3279 when others then
3280 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3281 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3282 P_TXN_ID => p_copy_entity_txn_id,
3283 P_MODULE_CD => 'PQH_GSP_STGBEN',
3284 p_context => 'EPA',
3285 P_MESSAGE_TYPE_CD => 'E',
3286 P_MESSAGE_TEXT => 'EPA',
3287 p_effective_date => p_effective_date);
3288 raise;
3289 end stage_to_epa;
3290 procedure stage_to_vpf(p_copy_entity_txn_id in number,
3291 p_business_group_id in number,
3292 p_effective_date in date,
3293 p_datetrack_mode in varchar2) is
3294 cursor c_crr is
3295 select *
3296 from ben_copy_entity_results
3297 where copy_entity_txn_id = p_copy_entity_txn_id
3298 and table_alias = 'CRRATE'
3299 and dml_operation in ('INSERT','UPDATE') -- only insert/ updates should be there
3300 order by information230,information169,information160,information2;
3301 --
3302 l_proc varchar2(61) :='stage_to_crr';
3303 l_crr_id number ;
3304 l_abr_id number ;
3305 l_avr_id number ;
3306 l_message_text varchar2(2000);
3307 l_object varchar2(80);
3308 l_vep_id number ;
3309 l_crr_ovn number ;
3310 l_ovn number ;
3311 l_db_ovn number;
3312 l_avr_num number ;
3313 l_esd date ;
3314 l_eed date ;
3315 l_effective_date date;
3316 dummy char(1);
3317 l_elp_id number;
3318 l_old_grd_cer_id number;
3319 l_old_pnt_cer_id number;
3320 l_old_abr_id number;
3321 l_old_crr_id number;
3322 l_old_crset_id number;
3323 l_old_crr_ovn number;
3324 l_old_crr_name varchar2(240);
3325 l_crr_name varchar2(240);
3326 l_dml_operation varchar2(30);
3327 l_dt_mode varchar2(30);
3328 begin
3329 hr_utility.set_location('inside '||l_proc,10);
3330 for crr_rec in c_crr loop
3331 l_effective_date := crr_rec.information2;
3332 if crr_rec.information278 is null then
3333 hr_utility.set_location('new variable rate is being created'||l_proc,10);
3334 if (crr_rec.information230 is null or crr_rec.information230 = l_old_grd_cer_id)
3335 and (crr_rec.information169 is null or crr_rec.information169 = l_old_pnt_cer_id)
3336 and crr_rec.information160 = l_old_crset_id
3337 and l_old_crr_id is not null then
3338 hr_utility.set_location('reusing prev row pk and ovn',16);
3339 l_crr_id := l_old_crr_id; -- previous row created id can be used
3340 l_crr_ovn := l_old_crr_ovn;
3341 l_abr_id := l_old_abr_id;
3342 l_crr_name := l_old_crr_name;
3343 else
3344 hr_utility.set_location('nothing to reuse'||l_proc,10);
3345 l_crr_id := crr_rec.information278;
3346 l_crr_ovn := crr_rec.information298;
3347 l_abr_id := '';
3348 l_avr_num := '';
3349 l_crr_name := build_vpf_name(p_crset_id => crr_rec.information160,
3350 p_point_cer_id => crr_rec.information169,
3351 p_grade_cer_id => crr_rec.information230,
3352 p_copy_entity_txn_id => p_copy_entity_txn_id);
3353 end if;
3354 else
3355 hr_utility.set_location('existing vpf is being updated'||l_proc,10);
3356 l_crr_id := crr_rec.information278;
3357 l_crr_ovn := crr_rec.information298;
3358 l_abr_id := '';
3359 l_avr_num := '';
3360 l_crr_name := crr_rec.information170;
3361 end if;
3362 if crr_rec.dml_operation = 'INSERT'
3363 and nvl(crr_rec.datetrack_mode,'CORRECTION') <> 'UPDATE_REPLACE' then
3364 l_dml_operation := 'INSERT';
3365 elsif crr_rec.dml_operation = 'INSERT' and crr_rec.datetrack_mode = 'UPDATE_REPLACE' then
3366 l_dml_operation := 'UPDATE';
3367 elsif crr_rec.dml_operation = 'UPDATE' then
3368 l_dml_operation := 'UPDATE';
3369 end if;
3370 if l_abr_id is null and crr_rec.information161 is not null then
3371 hr_utility.set_location('abr_id is null ,getting it'||l_proc,10);
3372 begin
3373 select information1
3374 into l_abr_id
3375 from ben_copy_entity_results
3376 where copy_entity_txn_id = p_copy_entity_txn_id
3377 and copy_entity_result_id = crr_rec.information161;
3378 exception
3379 when no_data_found then
3380 hr_utility.set_location('abr id not found'||l_proc,100);
3381 when others then
3382 hr_utility.set_location('issues in getting abr'||l_proc,100);
3383 raise;
3384 end;
3385 else
3386 hr_utility.set_location('abr_id is '||l_abr_id,10);
3387 end if;
3388 if l_dml_operation = 'INSERT' and l_abr_id is not null and l_crr_id is null and l_crr_ovn is null then
3389 begin
3390 hr_utility.set_location(' BEN_VRBL_RT_PRFL_F CREATE_VRBL_RATE_PROFILE ',20);
3391 BEN_VRBL_RATE_PROFILE_API.CREATE_VRBL_RATE_PROFILE(
3392 P_EFFECTIVE_DATE => l_effective_date
3393 ,P_BUSINESS_GROUP_ID => p_business_group_id
3394 ,P_ACTY_TYP_CD => 'GSPSA'
3395 ,P_NAME => l_crr_name
3396 ,P_VAL => nvl(crr_rec.INFORMATION293,0)
3397 ,P_VRBL_RT_PRFL_ID => l_crr_id
3398 ,P_VRBL_RT_PRFL_STAT_CD => 'A'
3399 ,P_ACTY_REF_PERD_CD => 'MO'
3400 ,P_VRBL_RT_TRTMT_CD => 'RPLC'
3401 ,P_VRBL_USG_CD => 'RT'
3402 ,P_RT_ELIG_PRFL_FLAG => 'N'
3403 ,P_RT_AGE_FLAG => 'N'
3404 ,P_RT_ASNT_SET_FLAG => 'N'
3405 ,P_RT_BENFTS_GRP_FLAG => 'N'
3406 ,P_RT_BRGNG_UNIT_FLAG => 'N'
3407 ,P_RT_CBR_QUALD_BNF_FLAG => 'N'
3408 ,P_RT_CMBN_AGE_LOS_FLAG => 'N'
3409 ,P_RT_CNTNG_PRTN_PRFL_FLAG => 'N'
3410 ,P_RT_COMPTNCY_FLAG => 'N'
3411 ,P_RT_COMP_LVL_FLAG => 'N'
3412 ,P_RT_DPNT_CVRD_PGM_FLAG => 'N'
3413 ,P_RT_DPNT_CVRD_PLIP_FLAG => 'N'
3414 ,P_RT_DPNT_CVRD_PL_FLAG => 'N'
3415 ,P_RT_DPNT_CVRD_PTIP_FLAG => 'N'
3416 ,P_RT_DPNT_OTHR_PTIP_FLAG => 'N'
3417 ,P_RT_DSBLD_FLAG => 'N'
3418 ,P_RT_EE_STAT_FLAG => 'N'
3419 ,P_RT_ENRLD_OIPL_FLAG => 'N'
3420 ,P_RT_ENRLD_PGM_FLAG => 'N'
3421 ,P_RT_ENRLD_PLIP_FLAG => 'N'
3422 ,P_RT_ENRLD_PL_FLAG => 'N'
3423 ,P_RT_ENRLD_PTIP_FLAG => 'N'
3424 ,P_RT_FL_TM_PT_TM_FLAG => 'N'
3425 ,P_RT_GNDR_FLAG => 'N'
3426 ,P_RT_GRD_FLAG => 'N'
3427 ,P_RT_HLTH_CVG_FLAG => 'N'
3428 ,P_RT_HRLY_SLRD_FLAG => 'N'
3429 ,P_RT_HRS_WKD_FLAG => 'N'
3430 ,P_RT_JOB_FLAG => 'N'
3431 ,P_RT_LBR_MMBR_FLAG => 'N'
3432 ,P_RT_LGL_ENTY_FLAG => 'N'
3433 ,P_RT_LOA_RSN_FLAG => 'N'
3434 ,P_RT_LOS_FLAG => 'N'
3435 ,P_RT_LVG_RSN_FLAG => 'N'
3436 ,P_RT_NO_OTHR_CVG_FLAG => 'N'
3437 ,P_RT_OPTD_MDCR_FLAG => 'N'
3438 ,P_RT_ORG_UNIT_FLAG => 'N'
3439 ,P_RT_OTHR_PTIP_FLAG => 'N'
3440 ,P_RT_PCT_FL_TM_FLAG => 'N'
3441 ,P_RT_PERF_RTNG_FLAG => 'N'
3442 ,P_RT_PER_TYP_FLAG => 'N'
3443 ,P_RT_POE_FLAG => 'N'
3444 ,P_RT_PPL_GRP_FLAG => 'N'
3445 ,P_RT_PRFL_RL_FLAG => 'N'
3446 ,P_RT_PRTT_ANTHR_PL_FLAG => 'N'
3447 ,P_RT_PRTT_PL_FLAG => 'N'
3448 ,P_RT_PSTL_CD_FLAG => 'N'
3449 ,P_RT_PSTN_FLAG => 'N'
3450 ,P_RT_PYRL_FLAG => 'N'
3451 ,P_RT_PY_BSS_FLAG => 'N'
3452 ,P_RT_QUAL_TITL_FLAG => 'N'
3453 ,P_RT_QUA_IN_GR_FLAG => 'N'
3454 ,P_RT_SCHEDD_HRS_FLAG => 'N'
3455 ,P_RT_SVC_AREA_FLAG => 'N'
3456 ,P_RT_TBCO_USE_FLAG => 'N'
3457 ,P_RT_TTL_CVG_VOL_FLAG => 'N'
3458 ,P_RT_TTL_PRTT_FLAG => 'N'
3459 ,P_RT_WK_LOC_FLAG => 'N'
3460 ,P_ASMT_TO_USE_CD => 'ANY'
3461 ,P_TX_TYP_CD => 'PRETAX'
3462 ,P_MLT_CD => 'FLFX'
3463 ,P_EFFECTIVE_START_DATE => l_esd
3464 ,P_EFFECTIVE_END_DATE => l_eed
3465 ,P_OBJECT_VERSION_NUMBER => l_crr_ovn
3466 );
3467 hr_utility.set_location('after vpf insert ',222);
3468 begin
3469 if l_avr_num is null then
3470 hr_utility.set_location('1st crr for abr',222);
3471 begin
3472 select null
3473 into dummy
3474 from ben_acty_base_rt_f
3475 where acty_base_rt_id = l_abr_id
3476 and USES_VARBL_RT_FLAG = 'Y'
3477 and l_effective_date between effective_start_date
3478 and effective_end_date;
3479 exception
3480 when no_data_found then
3481 hr_utility.set_location('abr has vrbl flag No',222);
3482 begin
3483 update ben_acty_base_rt_f
3484 set USES_VARBL_RT_FLAG = 'Y'
3485 where acty_base_rt_id = l_abr_id;
3486 hr_utility.set_location('updated to Yes',223);
3487 exception
3488 when others then
3489 hr_utility.set_location('issues in updating abr flag to Y',225);
3490 raise;
3491 end;
3492 when others then
3493 hr_utility.set_location('issues in gettting abr row ',226);
3494 raise;
3495 end;
3496 select nvl(max(ordr_num),0) + 1
3497 into l_avr_num
3498 from ben_acty_vrbl_rt_f
3499 where acty_base_rt_id = l_abr_id;
3500 else
3501 l_avr_num := l_avr_num + 1;
3502 end if;
3503 hr_utility.set_location(' BEN_ACTY_VRBL_RT_F CREATE_ACTY_VRBL_RATE ',20);
3504 BEN_ACTY_VRBL_RATE_API.CREATE_ACTY_VRBL_RATE(
3505 P_EFFECTIVE_DATE => l_effective_date
3506 ,P_BUSINESS_GROUP_ID => p_business_group_id
3507 ,P_ACTY_BASE_RT_ID => l_abr_id
3508 ,P_ACTY_VRBL_RT_ID => l_avr_id
3509 ,P_ORDR_NUM => l_avr_num
3510 ,P_VRBL_RT_PRFL_ID => l_crr_id
3511 ,P_EFFECTIVE_START_DATE => l_esd
3512 ,P_EFFECTIVE_END_DATE => l_eed
3513 ,P_OBJECT_VERSION_NUMBER => l_ovn
3514 );
3515 exception when others then
3516 hr_utility.set_location('issues in writing avr'||l_proc,100);
3517 raise;
3518 end;
3519 if crr_rec.information279 is null and crr_rec.information160 is not null then
3520 -- elp id is null for crrate while crset id is there
3521 begin
3522 select information277
3523 into l_elp_id
3524 from ben_copy_entity_results
3525 where copy_entity_txn_id = p_copy_entity_txn_id
3526 and table_alias = 'CRSET'
3527 and information161 = crr_rec.information160;
3528 exception
3529 when others then
3530 l_elp_id := '';
3531 end;
3532 else
3533 l_elp_id := crr_rec.information279;
3534 end if;
3535 if l_elp_id is null or l_crr_id is null then
3536 l_message_text := 'fks not there for creating vep row'
3537 ||' elp_id is '||l_elp_id
3538 ||' effdt is '||to_char(p_effective_date,'DD/MM/RRRR')
3539 ||' vpf id is '||l_crr_id;
3540 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3541 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3542 P_TXN_ID => nvl(l_crr_id,p_copy_entity_txn_id),
3543 P_MODULE_CD => 'PQH_GSP_STGBEN',
3544 p_context => 'VPF',
3545 P_MESSAGE_TYPE_CD => 'E',
3546 P_MESSAGE_TEXT => l_message_text,
3547 p_effective_date => p_effective_date);
3548 else
3549 begin
3550 hr_utility.set_location(' BEN_VRBL_RT_ELIG_PRFL_F CREATE_VRBL_RT_ELIG_PRFL ',20);
3551 BEN_VRBL_RT_ELIG_PRFL_API.CREATE_VRBL_RT_ELIG_PRFL(
3552 P_EFFECTIVE_DATE => p_effective_date -- vep will be created as of Grade ladder effdt
3553 ,P_BUSINESS_GROUP_ID => p_business_group_id
3554 ,P_ELIGY_PRFL_ID => l_elp_id
3555 ,P_MNDTRY_FLAG => 'Y'
3556 ,P_VRBL_RT_ELIG_PRFL_ID => l_vep_id
3557 ,P_VRBL_RT_PRFL_ID => l_crr_id
3558 ,P_EFFECTIVE_START_DATE => l_esd
3559 ,P_EFFECTIVE_END_DATE => l_eed
3560 ,P_OBJECT_VERSION_NUMBER => l_ovn
3561 );
3562 exception when others then
3563 l_message_text := 'issues in writing vep '
3564 ||' elp_id is '||l_elp_id
3565 ||' GL effdt is '||to_char(p_effective_date,'DD/MM/RRRR')
3566 ||' VR effdt is '||to_char(l_effective_date,'DD/MM/RRRR')
3567 ||' vpf id is '||l_crr_id;
3568 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3569 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3570 P_TXN_ID => nvl(l_crr_id,p_copy_entity_txn_id),
3571 P_MODULE_CD => 'PQH_GSP_STGBEN',
3572 p_context => 'VPF',
3573 P_MESSAGE_TYPE_CD => 'E',
3574 P_MESSAGE_TEXT => l_message_text,
3575 p_effective_date => p_effective_date);
3576 raise;
3577 end;
3578 end if;
3579 exception when others then
3580 hr_utility.set_location('issues in writing var'||l_proc,100);
3581 raise;
3582 end;
3583 elsif l_dml_operation = 'UPDATE'
3584 and l_abr_id is not null
3585 and l_crr_id is not null
3586 and l_crr_ovn is not null then
3587 hr_utility.set_location(' BEN_VRBL_RT_PRFL_F UPDATE_VRBL_RATE_PROFILE ',30);
3588 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
3589 --if p_datetrack_mode <> 'CORRECTION' then /* Commented out to fix Bug:3964291 */
3590 l_dt_mode := get_update_mode(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3591 p_key_column_name => 'VRBL_RT_PRFL_ID',
3592 p_key_column_value => l_crr_id,
3593 p_effective_date => l_effective_date);
3594 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
3595 /*else
3596 l_dt_mode := p_datetrack_mode;
3597 end if;*/
3598 l_db_ovn := get_ovn(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3599 p_key_column_name => 'VRBL_RT_PRFL_ID',
3600 p_key_column_value => l_crr_id,
3601 p_effective_date => l_effective_date);
3602 hr_utility.set_location(' ovn is '||l_db_ovn,30);
3603 if l_db_ovn <> l_crr_ovn then
3604 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','VPF');
3605 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
3606 fnd_message.set_token('OBJECT ',l_object);
3607 fnd_message.set_token('OBJECT_NAME ','VPF : '||l_crr_id);
3608 fnd_message.raise_error;
3609 else
3610 begin
3611 BEN_VRBL_RATE_PROFILE_API.UPDATE_VRBL_RATE_PROFILE(
3612 P_EFFECTIVE_DATE => l_effective_date
3613 ,P_VAL => crr_rec.INFORMATION293
3614 ,P_VRBL_RT_PRFL_ID => l_crr_id
3615 ,P_EFFECTIVE_START_DATE => l_esd
3616 ,P_EFFECTIVE_END_DATE => l_eed
3617 ,P_OBJECT_VERSION_NUMBER => l_crr_ovn
3618 ,P_DATETRACK_MODE => l_dt_mode
3619 );
3620 exception when others then
3621 hr_utility.set_location('issues in updating var'||l_proc,100);
3622 raise;
3623 end;
3624 end if;
3625 else
3626 l_message_text := 'invalid operation '||l_dml_operation
3627 ||' abr_id is '||l_abr_id
3628 ||' crr_ovn is '||l_crr_ovn
3629 ||' crr_name is '||l_crr_name
3630 ||' val is '||crr_rec.INFORMATION293
3631 ||' crr_id is '||l_crr_id;
3632 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3633 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3634 P_TXN_ID => nvl(l_crr_id,p_copy_entity_txn_id),
3635 P_MODULE_CD => 'PQH_GSP_STGBEN',
3636 p_context => 'VPF',
3637 P_MESSAGE_TYPE_CD => 'E',
3638 P_MESSAGE_TEXT => l_message_text,
3639 p_effective_date => p_effective_date);
3640 end if;
3641 l_old_crr_ovn := l_crr_ovn;
3642 l_old_abr_id := l_abr_id;
3643 l_old_crr_id := l_crr_id;
3644 l_old_crr_name := l_crr_name;
3645 l_old_grd_cer_id := crr_rec.information230;
3646 l_old_pnt_cer_id := crr_rec.information169;
3647 l_old_crset_id := crr_rec.information160;
3648 end loop;
3649 hr_utility.set_location('leaving '||l_proc,100);
3650 exception
3651 when others then
3652 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3653 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3654 P_TXN_ID => p_copy_entity_txn_id,
3655 P_MODULE_CD => 'PQH_GSP_STGBEN',
3656 p_context => 'VPF',
3657 P_MESSAGE_TYPE_CD => 'E',
3658 P_MESSAGE_TEXT => 'VPF',
3659 p_effective_date => p_effective_date);
3660 raise;
3661 end stage_to_vpf;
3662 FUNCTION get_pgm_name (p_pgm_id IN NUMBER, p_effective_date IN DATE)
3663 RETURN VARCHAR2
3664 IS
3665 l_pgm_name ben_pgm_f.NAME%TYPE;
3666 BEGIN
3667 SELECT NAME
3668 INTO l_pgm_name
3669 FROM ben_pgm_f
3670 WHERE pgm_id = p_pgm_id
3671 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
3672
3673 RETURN l_pgm_name;
3674 END get_pgm_name;
3675
3676 PROCEDURE create_le_for_pgm (
3677 p_pgm_id IN NUMBER,
3678 p_business_group_id IN NUMBER,
3679 p_ler_id IN NUMBER,
3680 p_effective_date IN DATE
3681 )
3682 IS
3683 l_pet_id NUMBER;
3684 l_lee_rsn_id NUMBER;
3685 l_continue BOOLEAN DEFAULT FALSE;
3686 l_esd DATE;
3687 l_eed DATE;
3688 l_ovn NUMBER;
3689
3690 CURSOR csr_pgm_enrl (p_pgm_id IN NUMBER)
3691 IS
3692 SELECT popl_enrt_typ_cycl_id
3693 FROM ben_popl_enrt_typ_cycl_f
3694 WHERE pgm_id = p_pgm_id;
3695
3696 CURSOR csr_lee_rsns (p_popl_enrt_typ_cycl_id IN NUMBER)
3697 IS
3698 SELECT NULL
3699 FROM ben_lee_rsn_f
3700 WHERE ler_id = p_ler_id
3701 AND business_group_id = p_business_group_id
3702 AND popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id;
3703 BEGIN
3704 OPEN csr_pgm_enrl (p_pgm_id);
3705
3706 FETCH csr_pgm_enrl
3707 INTO l_pet_id;
3708
3709 CLOSE csr_pgm_enrl;
3710
3711 OPEN csr_lee_rsns (l_pet_id);
3712
3713 FETCH csr_lee_rsns
3714 INTO l_lee_rsn_id;
3715
3716 IF csr_lee_rsns%NOTFOUND
3717 THEN
3718 l_continue := TRUE;
3719 END IF;
3720
3721 CLOSE csr_lee_rsns;
3722
3723 IF l_continue
3724 THEN
3725 ben_life_event_enroll_rsn_api.create_life_event_enroll_rsn
3726 (p_effective_date => p_effective_date,
3727 p_business_group_id => p_business_group_id,
3728 p_lee_rsn_id => l_lee_rsn_id,
3729 p_ler_id => p_ler_id,
3730 p_popl_enrt_typ_cycl_id => l_pet_id,
3731 p_effective_start_date => l_esd,
3732 p_effective_end_date => l_eed,
3733 p_object_version_number => l_ovn,
3734 p_cls_enrt_dt_to_use_cd => 'ELCNSMADE',
3735 p_enrt_cvg_end_dt_cd => 'ODBED',
3736 p_enrt_cvg_strt_dt_cd => 'AED',
3737 p_enrt_perd_end_dt_cd => 'ALDCPPY',
3738 p_enrt_perd_strt_dt_cd => 'AED',
3739 p_rt_end_dt_cd => 'ODBED',
3740 p_rt_strt_dt_cd => 'AED'
3741 );
3742 fnd_file.put_line (which => fnd_file.LOG,
3743 buff => 'Program Name : '
3744 || get_pgm_name (p_pgm_id,
3745 p_effective_date
3746 )
3747 );
3748 fnd_file.put_line (which => fnd_file.LOG,
3749 buff => 'Program Id : ' || p_pgm_id
3750 );
3751 END IF;
3752 EXCEPTION
3753 WHEN OTHERS
3754 THEN
3755 fnd_file.put_line
3756 (which => fnd_file.LOG,
3757 buff => 'Error while creating Program Enrollement Reasons..exiting'
3758 );
3759 ROLLBACK;
3760 END create_le_for_pgm;
3761
3762 PROCEDURE create_pgm_le (
3763 errbuf OUT NOCOPY VARCHAR2,
3764 retcode OUT NOCOPY NUMBER,
3765 p_effective_date IN VARCHAR2,
3766 p_business_group_id IN VARCHAR2,
3767 p_pgm_id IN NUMBER DEFAULT NULL
3768 )
3769 IS
3770 CURSOR csr_ler_id
3771 IS
3772 SELECT ler_id
3773 FROM ben_ler_f
3774 WHERE typ_cd = 'GSP'
3775 AND lf_evt_oper_cd = 'SYNC'
3776 AND business_group_id = p_business_group_id
3777 AND effective_start_date = pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id, null);
3778
3779 CURSOR csr_pgm_details (eff_date IN DATE)
3780 IS
3781 SELECT pgm_id
3782 FROM ben_pgm_f
3783 WHERE business_group_id = p_business_group_id
3784 AND pgm_typ_cd = 'GSP'
3785 AND eff_date BETWEEN effective_start_date AND effective_end_date;
3786
3787 Cursor csr_pgm_start_date(p_program_id in number)
3788 is
3789 select min(effective_start_date)
3790 from ben_pgm_f
3791 where pgm_id = p_program_id;
3792
3793 l_ler_id NUMBER;
3794 l_effective_date DATE;
3795 l_pgm_effective_start_date DATE;
3796 BEGIN
3797 fnd_file.put_line (which => fnd_file.LOG,buff => 'Entering create_pgm_le');
3798 l_effective_date := TO_DATE (p_effective_date, 'YYYY/MM/DD HH24:MI:SS');
3799 l_effective_date :=TO_DATE (TO_CHAR (TRUNC (l_effective_date), 'DD/MM/RRRR'),'DD/MM/RRRR');
3800
3801 OPEN csr_ler_id;
3802 FETCH csr_ler_id
3803 INTO l_ler_id;
3804
3805 IF csr_ler_id%NOTFOUND
3806 THEN
3807 errbuf :='No Life Event of Type Grade/Step Progression with Operator Code Synchronization found. Exiting';
3808 retcode := -20;
3809 RETURN;
3810 ELSE
3811 fnd_file.put_line (which => fnd_file.LOG,buff => 'Life Event Exists');
3812 END IF;
3813
3814 CLOSE csr_ler_id;
3815 fnd_file.put_line
3816 (which => fnd_file.LOG,
3817 buff => 'Created Program Enrolment Reason for the following Programs '
3818 );
3819 fnd_file.put_line
3820 (which => fnd_file.LOG,
3821 buff => '==========================================================='
3822 );
3823 IF p_pgm_id IS NULL
3824 THEN
3825 FOR i IN csr_pgm_details (l_effective_date)
3826 LOOP
3827 OPEN csr_pgm_start_date(i.pgm_id);
3828 FETCH csr_pgm_start_date into l_pgm_effective_start_date;
3829 CLOSE csr_pgm_start_date;
3830 create_le_for_pgm (p_pgm_id => i.pgm_id,
3831 p_business_group_id => p_business_group_id,
3832 p_ler_id => l_ler_id,
3833 p_effective_date => l_pgm_effective_start_date
3834 );
3835 END LOOP;
3836 ELSE
3837 OPEN csr_pgm_start_date(p_pgm_id);
3838 FETCH csr_pgm_start_date into l_pgm_effective_start_date;
3839 CLOSE csr_pgm_start_date;
3840 create_le_for_pgm (p_pgm_id => p_pgm_id,
3841 p_business_group_id => p_business_group_id,
3842 p_ler_id => l_ler_id,
3843 p_effective_date => l_pgm_effective_start_date
3844 );
3845 END IF;
3846 COMMIT;
3847 fnd_file.put_line (which => fnd_file.LOG,
3848 buff => 'Leaving create_pgm_le'
3849 );
3850 END create_pgm_le;
3851
3852 procedure upd_stg_elig_prfl_id(
3853 p_copy_entity_txn_id number
3854 ,p_business_group_id number
3855 ,p_effective_date date
3856 )
3857 is
3858 begin
3859 hr_utility.set_location('Entering pqh_gsp_stage_to_ben.upd_stg_elig_prfl_id',99);
3860
3861 update ben_copy_entity_results cer
3862 set information263 =
3863 ( select ELIGY_PRFL_ID
3864 from BEN_ELIGY_PRFL_F elp
3865 where elp.BUSINESS_GROUP_ID = p_business_group_id
3866 and elp.name = cer.information5
3867 and p_effective_date between
3868 elp.effective_start_date and elp.effective_end_date)
3869 where cer.copy_entity_txn_id = p_copy_entity_txn_id
3870 and cer.information4 = p_business_group_id
3871 and cer.table_alias = 'CEP'
3872 and p_effective_date between
3873 cer.information2 and nvl(cer.information3,to_date('4712/12/31','YYYY/MM/DD'))
3874 and exists ( select ELIGY_PRFL_ID
3875 from BEN_ELIGY_PRFL_F elp
3876 where elp.BUSINESS_GROUP_ID = p_business_group_id
3877 and elp.name = cer.information5
3878 and p_effective_date between
3879 elp.effective_start_date and elp.effective_end_date);
3880
3881 hr_utility.set_location('No of staging rows updated :'||sql%rowcount||':',99);
3882 hr_utility.set_location('Leaving pqh_gsp_stage_to_ben.upd_stg_elig_prfl_id',99);
3883 end upd_stg_elig_prfl_id ;
3884
3885 procedure cre_update_elig_prfl(
3886 p_copy_entity_txn_id in number
3887 ,p_effective_date in date
3888 ,p_business_group_id in number
3889 ,p_business_area in varchar2 default 'PQH_GSP_TASK_LIST')
3890 is
3891 l_delete_failed varchar2(10);
3892 begin
3893 hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3894 hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3895 hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3896 hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3897 hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3898
3899 hr_utility.set_location('Calling purge:'||p_business_area,903);
3900
3901 pqh_gsp_prgrules.purge_duplicate_elp_tree(p_copy_entity_txn_id => p_copy_entity_txn_id);
3902
3903 hr_utility.set_location('Done with purge:'||p_business_area,903);
3904
3905 -- update the dml operation of those records which have already been
3906 -- taken care by gsp pre push code
3907
3908 UPDATE ben_copy_entity_results cer
3909 set dml_operation = 'GSPDEL'
3910 where cer.copy_entity_txn_id = p_copy_entity_txn_id
3911 and cer.dml_operation = 'DELETE'
3912 and table_alias in ('CPP','CEP','EPA','COP','OPT','ABR');
3913
3914 -- Set for same Business Group
3915 BEN_PD_COPY_TO_BEN_ONE.g_mapping_done := false ;
3916
3917 -- Copied the following 4 calls from ben_plan_design_copy_process.process
3918 -- Populate table_route_id in staging table
3919 ben_plan_design_wizard_api.write_route_and_hierarchy(p_copy_entity_txn_id);
3920 ben_plan_design_wizard_api.update_result_rows(p_copy_entity_txn_id => p_copy_entity_txn_id);
3921 ben_plan_design_delete_api.call_delete_apis(
3922 p_copy_entity_txn_id => p_copy_entity_txn_id
3923 ,p_delete_failed => l_delete_failed
3924 );
3925
3926 UPDATE ben_copy_entity_results cer
3927 set number_of_copies = 0
3928 where cer.copy_entity_txn_id = p_copy_entity_txn_id
3929 and p_effective_date between nvl(information2,p_effective_date)
3930 and nvl(information3,p_effective_date)
3931 and cer.dml_operation = 'DELETE';
3932
3933 -- Initialise
3934 ben_pd_copy_to_ben_one.init_table_data_in_cer(p_copy_entity_txn_id);
3935
3936 BEN_PD_COPY_TO_BEN_ONE.g_pk_tbl.delete;
3937 BEN_PD_COPY_TO_BEN_ONE.g_pk_tbl(0) := null ;
3938 BEN_PD_COPY_TO_BEN_ONE.g_count := 1 ;
3939
3940 -- Create all derived factors first
3941
3942 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('CLF') then
3943 hr_utility.set_location(' Calling create CLF rows ',999);
3944 BEN_PD_COPY_TO_BEN_ONE.create_CLF_rows(
3945 p_copy_entity_txn_id => p_copy_entity_txn_id
3946 ,p_effective_date => p_effective_date
3947 ,p_reuse_object_flag => 'Y'
3948 ,p_target_business_group_id => p_business_group_id
3949 );
3950 end if;
3951 --
3952 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('HWF') then
3953 hr_utility.set_location(' Calling create HWF rows ',999);
3954 BEN_PD_COPY_TO_BEN_ONE.create_HWF_rows(
3955 p_copy_entity_txn_id => p_copy_entity_txn_id
3956 ,p_effective_date => p_effective_date
3957 ,p_reuse_object_flag => 'Y'
3958 ,p_target_business_group_id => p_business_group_id
3959 );
3960 end if;
3961 --
3962 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('AGF') then
3963 hr_utility.set_location(' Calling create AGF rows ',999);
3964 BEN_PD_COPY_TO_BEN_ONE.create_AGF_rows(
3965 p_copy_entity_txn_id => p_copy_entity_txn_id
3966 ,p_effective_date => p_effective_date
3967 ,p_reuse_object_flag => 'Y'
3968 ,p_target_business_group_id => p_business_group_id
3969 );
3970 end if;
3971 --
3972 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('LSF') then
3973 hr_utility.set_location(' Calling create LSF rows ',999);
3974 BEN_PD_COPY_TO_BEN_ONE.create_LSF_rows(
3975 p_copy_entity_txn_id => p_copy_entity_txn_id
3976 ,p_effective_date => p_effective_date
3977 ,p_reuse_object_flag => 'Y'
3978 ,p_target_business_group_id => p_business_group_id
3979 );
3980 end if;
3981 --
3982 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('PFF') then
3983 hr_utility.set_location(' Calling create PFF rows ',999);
3984 BEN_PD_COPY_TO_BEN_ONE.create_PFF_rows(
3985 p_copy_entity_txn_id => p_copy_entity_txn_id
3986 ,p_effective_date => p_effective_date
3987 ,p_reuse_object_flag => 'Y'
3988 ,p_target_business_group_id => p_business_group_id
3989 );
3990 end if;
3991 --
3992 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('CLA') then
3993 hr_utility.set_location(' Calling create CLA rows ',999);
3994 BEN_PD_COPY_TO_BEN_ONE.create_CLA_rows(
3995 p_copy_entity_txn_id => p_copy_entity_txn_id
3996 ,p_effective_date => p_effective_date
3997 ,p_reuse_object_flag => 'Y'
3998 ,p_target_business_group_id => p_business_group_id
3999 );
4000 end if;
4001
4002 --
4003 -- Create ELP Row
4004 --
4005
4006 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('ELP') then
4007 hr_utility.set_location(' Calling create ELP rows ',999);
4008 BEN_PD_COPY_TO_BEN_ONE.create_ELP_rows(
4009 p_copy_entity_txn_id => p_copy_entity_txn_id
4010 ,p_effective_date => p_effective_date
4011 ,p_reuse_object_flag => 'Y'
4012 ,p_target_business_group_id => p_business_group_id
4013 );
4014 end if;
4015
4016 --
4017 -- Create elig prf ben rows
4018 --
4019
4020 hr_utility.set_location(' Calling create all elig prf ben rows',999);
4021 BEN_PD_COPY_TO_BEN_FOUR.create_all_elig_prf_ben_rows(
4022 p_copy_entity_txn_id => p_copy_entity_txn_id
4023 ,p_effective_date => p_effective_date
4024 ,p_reuse_object_flag => 'Y'
4025 ,p_target_business_group_id => p_business_group_id
4026 );
4027
4028 --
4029 -- Update elig_prfl_id of staging records
4030 --
4031 hr_utility.set_location(' Update elig_prfl_id in staging area ',999);
4032 upd_stg_elig_prfl_id(
4033 p_copy_entity_txn_id => p_copy_entity_txn_id
4034 ,p_business_group_id => p_business_group_id
4035 ,p_effective_date => p_effective_date
4036 );
4037
4038 UPDATE ben_copy_entity_results cer
4039 set dml_operation = 'DELETE'
4040 where cer.copy_entity_txn_id = p_copy_entity_txn_id
4041 and cer.dml_operation = 'GSPDEL';
4042
4043 hr_utility.set_location('Leaving pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
4044 end cre_update_elig_prfl ;
4045
4046
4047 end pqh_gsp_stage_to_ben;