[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.12010000.2 2009/03/13 09:48:12 lbodired ship $ */
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 and copy_entity_result_id = r_cpp.gs_parent_entity_result_id; -- 7610624
2030 else
2031 l_pgm_id := r_CPP.INFORMATION260;
2032 end if;
2033 if r_CPP.INFORMATION261 is null then
2034 select information1
2035 into l_pl_id
2036 from ben_copy_entity_results
2037 where copy_entity_txn_id = p_copy_entity_txn_id
2038 and table_alias = 'PLN'
2039 and copy_entity_result_id = r_CPP.INFORMATION252;
2040 else
2041 l_pl_id := r_CPP.INFORMATION261;
2042 end if;
2043 hr_utility.set_location('for cpp_id:'||l_plip_id ||'dml '||r_cpp.dml_operation,20);
2044 hr_utility.set_location('pgm_id: '||l_pgm_id,20);
2045 hr_utility.set_location('pl_id: '||l_pl_id,20);
2046 --Added by kgowripe for bug#3532412
2047 if r_cpp.information291 IS NULL AND p_business_area = 'PQH_CORPS_TASK_LIST' THEN
2048 SELECT information1
2049 INTO l_corps_definition_id
2050 FROM ben_copy_entity_results
2051 WHERE copy_entity_txn_id = p_copy_entity_txn_id
2052 AND table_alias = 'CPD';
2053 else
2054 l_corps_definition_id := r_cpp.information291;
2055 end if;
2056 --End changes for bug#3532412
2057 l_effective_date := r_cpp.information2;
2058 begin
2059 if l_plip_id is null and r_cpp.dml_operation = 'INSERT'
2060 and l_pgm_id is not null and l_pl_id is not null then
2061 hr_utility.set_location(' BEN_PLIP_F CREATE_PLAN_IN_PROGRAM ',20);
2062 BEN_PLAN_IN_PROGRAM_API.CREATE_PLAN_IN_PROGRAM(
2063 P_EFFECTIVE_DATE => l_effective_date
2064 ,P_BUSINESS_GROUP_ID => p_business_group_id
2065 ,P_ALWS_UNRSTRCTD_ENRT_FLAG => nvl(r_CPP.INFORMATION15,'N')
2066 ,P_AUTO_ENRT_MTHD_RL => r_CPP.INFORMATION266
2067 ,P_BNFT_OR_OPTION_RSTRCTN_CD => r_CPP.INFORMATION36
2068 ,P_CMBN_PLIP_ID => r_CPP.INFORMATION239
2069 ,P_CVG_INCR_R_DECR_ONLY_CD => r_CPP.INFORMATION28
2070 ,P_DFLT_ENRT_CD => r_CPP.INFORMATION21
2071 ,P_DFLT_ENRT_DET_RL => r_CPP.INFORMATION264
2072 ,P_DFLT_FLAG => nvl(r_CPP.INFORMATION13,'N')
2073 ,P_DFLT_TO_ASN_PNDG_CTFN_CD => r_CPP.INFORMATION29
2074 ,P_DFLT_TO_ASN_PNDG_CTFN_RL => r_CPP.INFORMATION264
2075 ,P_DRVBL_FCTR_APLS_RTS_FLAG => nvl(r_CPP.INFORMATION16,'N')
2076 ,P_DRVBL_FCTR_PRTN_ELIG_FLAG => nvl(r_CPP.INFORMATION17,'N')
2077 ,P_ELIG_APLS_FLAG => nvl(r_CPP.INFORMATION18,'N')
2078 ,P_ENRT_CD => r_CPP.INFORMATION22
2079 ,P_ENRT_CVG_END_DT_CD => r_CPP.INFORMATION25
2080 ,P_ENRT_CVG_END_DT_RL => r_CPP.INFORMATION269
2081 ,P_ENRT_CVG_STRT_DT_CD => r_CPP.INFORMATION24
2082 ,P_ENRT_CVG_STRT_DT_RL => r_CPP.INFORMATION268
2083 ,P_ENRT_MTHD_CD => r_CPP.INFORMATION23
2084 ,P_ENRT_RL => r_CPP.INFORMATION267
2085 ,P_IVR_IDENT => r_CPP.INFORMATION141
2086 ,P_MN_CVG_AMT => r_CPP.INFORMATION293
2087 ,P_MN_CVG_RL => r_CPP.INFORMATION273
2088 ,P_MX_CVG_ALWD_AMT => r_CPP.INFORMATION294
2089 ,P_MX_CVG_INCR_ALWD_AMT => r_CPP.INFORMATION295
2090 ,P_MX_CVG_INCR_WCF_ALWD_AMT => r_CPP.INFORMATION296
2091 ,P_MX_CVG_MLT_INCR_NUM => r_CPP.INFORMATION274
2092 ,P_MX_CVG_MLT_INCR_WCF_NUM => r_CPP.INFORMATION275
2093 ,P_MX_CVG_RL => r_CPP.INFORMATION276
2094 ,P_MX_CVG_WCFN_AMT => r_CPP.INFORMATION297
2095 ,P_MX_CVG_WCFN_MLT_NUM => r_CPP.INFORMATION277
2096 ,P_NO_MN_CVG_AMT_APLS_FLAG => nvl(r_CPP.INFORMATION30,'N')
2097 ,P_NO_MN_CVG_INCR_APLS_FLAG => nvl(r_CPP.INFORMATION31,'N')
2098 ,P_NO_MX_CVG_AMT_APLS_FLAG => nvl(r_CPP.INFORMATION32,'N')
2099 ,P_NO_MX_CVG_INCR_APLS_FLAG => nvl(r_CPP.INFORMATION33,'N')
2100 ,P_ORDR_NUM => r_CPP.INFORMATION263
2101 ,P_PER_CVRD_CD => r_CPP.INFORMATION38
2102 ,P_PGM_ID => l_pgm_id
2103 ,P_PLIP_ID => l_plip_id
2104 ,P_PLIP_STAT_CD => 'A'
2105 ,P_PL_ID => l_pl_id
2106 ,P_POSTELCN_EDIT_RL => r_CPP.INFORMATION257
2107 ,P_PRORT_PRTL_YR_CVG_RSTRN_CD => r_CPP.INFORMATION35
2108 ,P_PRORT_PRTL_YR_CVG_RSTRN_RL => r_CPP.INFORMATION278
2109 ,P_PRTN_ELIG_OVRID_ALWD_FLAG => nvl(r_CPP.INFORMATION19,'N')
2110 ,P_RT_END_DT_CD => r_CPP.INFORMATION27
2111 ,P_RT_END_DT_RL => r_CPP.INFORMATION271
2112 ,P_RT_STRT_DT_CD => r_CPP.INFORMATION26
2113 ,P_RT_STRT_DT_RL => r_CPP.INFORMATION270
2114 ,P_SHORT_CODE => r_CPP.INFORMATION11
2115 ,P_SHORT_NAME => r_CPP.INFORMATION12
2116 ,P_TRK_INELIG_PER_FLAG => nvl(r_CPP.INFORMATION20,'N')
2117 ,P_UNSSPND_ENRT_CD => r_CPP.INFORMATION34
2118 ,P_URL_REF_NAME => r_CPP.INFORMATION185
2119 ,P_VRFY_FMLY_MMBR_CD => r_CPP.INFORMATION37
2120 ,P_VRFY_FMLY_MMBR_RL => r_CPP.INFORMATION279
2121 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2122 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2123 ,P_OBJECT_VERSION_NUMBER => l_ovn
2124 );
2125 hr_utility.set_location('After plip ins'||l_plip_id,22);
2126 plip_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
2127 p_plip_id => l_plip_id,
2128 p_pl_id => l_pl_id,
2129 p_plip_cer_id => r_CPP.copy_entity_result_id);
2130 hr_utility.set_location('plip wrtback comp'||l_plip_id,222);
2131 if p_business_area = 'PQH_CORPS_TASK_LIST' then
2132 hr_utility.set_location('going for quota cr'||l_plip_id,222);
2133 pqh_cpd_hr_to_stage.grd_quota_update(p_effective_date => l_effective_date,
2134 p_grade_id => r_cpp.information253,
2135 p_corps_definition_id => l_corps_definition_id,
2136 p_corps_extra_info_id => r_cpp.information290,
2137 p_perc_quota => r_cpp.information287,
2138 p_population_cd => r_cpp.information99,
2139 p_comb_grades => r_cpp.information219,
2140 p_max_speed_quota => r_cpp.information288,
2141 p_avg_speed_quota => r_cpp.information289);
2142 end if;
2143 elsif l_plip_id is not null and r_cpp.dml_operation = 'UPDATE'
2144 and l_ovn is not null and l_pgm_id is not null
2145 and l_pl_id is not null then
2146 hr_utility.set_location(' BEN_PLIP_F UPDATE_PLAN_IN_PROGRAM ',30);
2147 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2148 if p_datetrack_mode <> 'CORRECTION' then
2149 l_dt_mode := get_update_mode(p_table_name => 'BEN_PLIP_F',
2150 p_key_column_name => 'PLIP_ID',
2151 p_key_column_value => l_plip_id,
2152 p_effective_date => l_effective_date);
2153 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2154 else
2155 l_dt_mode := p_datetrack_mode;
2156 end if;
2157 l_db_ovn := get_ovn(p_table_name => 'BEN_PLIP_F',
2158 p_key_column_name => 'PLIP_ID',
2159 p_key_column_value => l_plip_id,
2160 p_effective_date => l_effective_date);
2161 hr_utility.set_location(' ovn is '||l_db_ovn,30);
2162 if l_db_ovn <> l_ovn then
2163 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','PLIP');
2164 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2165 fnd_message.set_token('OBJECT ',l_object);
2166 fnd_message.set_token('OBJECT_NAME ','PL :'||l_pl_id);
2167 fnd_message.raise_error;
2168 else
2169 BEN_PLAN_IN_PROGRAM_API.UPDATE_PLAN_IN_PROGRAM(
2170 P_EFFECTIVE_DATE => l_effective_date
2171 ,P_BUSINESS_GROUP_ID => p_business_group_id
2172 ,P_ORDR_NUM => r_CPP.INFORMATION263
2173 ,P_PGM_ID => l_pgm_id
2174 ,P_PLIP_ID => l_plip_id
2175 ,P_PL_ID => l_pl_id
2176 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2177 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2178 ,P_OBJECT_VERSION_NUMBER => l_ovn
2179 ,P_DATETRACK_MODE => l_dt_mode
2180 );
2181 end if;
2182 if p_business_area = 'PQH_CORPS_TASK_LIST' then
2183 pqh_cpd_hr_to_stage.grd_quota_update(p_effective_date => l_effective_date,
2184 p_grade_id => r_cpp.information253,
2185 p_corps_definition_id => l_corps_definition_id,
2186 p_corps_extra_info_id => r_cpp.information290,
2187 p_perc_quota => r_cpp.information287,
2188 p_population_cd => r_cpp.information99,
2189 p_comb_grades => r_cpp.information219,
2190 p_max_speed_quota => r_cpp.information288,
2191 p_avg_speed_quota => r_cpp.information289);
2192 end if;
2193 else
2194 l_message_text := 'invalid plip dml_oper: '||r_CPP.dml_operation
2195 ||' plip_id: '||l_plip_id
2196 ||' ovn: '||l_ovn
2197 ||' pgm_id: '||l_pgm_id
2198 ||' pl_id: '||l_pl_id;
2199 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2200 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2201 P_TXN_ID => nvl(l_plip_id,p_copy_entity_txn_id),
2202 P_MODULE_CD => 'PQH_GSP_STGBEN',
2203 p_context => 'PLIP',
2204 P_MESSAGE_TYPE_CD => 'E',
2205 P_MESSAGE_TEXT => l_message_text,
2206 p_effective_date => p_effective_date);
2207 end if;
2208 exception when others then
2209 hr_utility.set_location('issues in writing cpp, skipping'||l_proc,100);
2210 raise;
2211 end;
2212 end loop;
2213 hr_utility.set_location('leaving '||l_proc,100);
2214 exception
2215 when others then
2216 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2217 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2218 P_TXN_ID => p_copy_entity_txn_id,
2219 P_MODULE_CD => 'PQH_GSP_STGBEN',
2220 p_context => 'PLIP',
2221 P_MESSAGE_TYPE_CD => 'E',
2222 P_MESSAGE_TEXT => 'PLIP',
2223 p_effective_date => p_effective_date);
2224 raise;
2225 end stage_to_plip;
2226 procedure stage_to_elp(p_copy_entity_txn_id in number,
2227 p_business_group_id in number,
2228 p_effective_date in date,
2229 p_datetrack_mode in varchar2) is
2230 cursor c_elp is
2231 select *
2232 from ben_copy_entity_results
2233 where copy_entity_txn_id = p_copy_entity_txn_id
2234 and table_alias = 'CRSET'
2235 and dml_operation in ('INSERT','UPDATE') -- only insert/ updates should be there
2236 order by information161,information2;
2237 --
2238 r_elp c_elp%rowtype;
2239 l_proc varchar2(61) :='stage_to_elp';
2240 l_elp_id number ;
2241 l_object varchar2(80);
2242 l_elp_ovn number ;
2243 l_old_elp_id number ;
2244 l_old_crset_id number ;
2245 l_old_elp_ovn number ;
2246 l_effective_start_date date ;
2247 l_effective_end_date date ;
2248 l_message_text varchar2(2000);
2249 l_effective_date date;
2250 l_dt_mode varchar2(30);
2251 l_db_ovn number;
2252 l_loc_flag varchar2(30);
2253 l_org_flag varchar2(30);
2254 l_job_flag varchar2(30);
2255 l_pt_flag varchar2(30);
2256 l_sa_flag varchar2(30);
2257 l_pr_flag varchar2(30);
2258 l_fp_flag varchar2(30);
2259 l_rl_flag varchar2(30);
2260 l_bu_flag varchar2(30);
2261 l_old_loc_flag varchar2(30);
2262 l_old_org_flag varchar2(30);
2263 l_old_job_flag varchar2(30);
2264 l_old_pt_flag varchar2(30);
2265 l_old_sa_flag varchar2(30);
2266 l_old_pr_flag varchar2(30);
2267 l_old_fp_flag varchar2(30);
2268 l_old_rl_flag varchar2(30);
2269 l_old_bu_flag varchar2(30);
2270 l_dml_operation varchar2(30);
2271 l_ovn number;
2272 l_pk number;
2273 l_pt_cd varchar2(30);
2274 l_esd date;
2275 l_eed date;
2276 begin
2277 hr_utility.set_location('inside '||l_proc,10);
2278 for r_elp in c_elp loop
2279 l_elp_id := r_elp.information161;
2280 hr_utility.set_location('for elp_id:'||l_elp_id ||'dml '||r_elp.dml_operation,20);
2281 l_effective_date := r_elp.information2;
2282 if r_elp.information277 is null and r_elp.information161 = l_old_crset_id then
2283 hr_utility.set_location('reusing earlier values ',20);
2284 -- reuse old values
2285 l_elp_id := l_old_elp_id;
2286 l_elp_ovn := l_old_elp_ovn;
2287 else
2288 hr_utility.set_location('earlier values cannot be reused',20);
2289 -- new crset_id is to be entered
2290 l_elp_id := r_elp.information277;
2291 l_elp_ovn := r_elp.information265;
2292 l_old_elp_id := '';
2293 l_old_elp_ovn := '';
2294 l_old_crset_id := '';
2295 l_old_sa_flag := '';
2296 l_old_fp_flag := '';
2297 l_old_pt_flag := '';
2298 l_old_rl_flag := '';
2299 l_old_bu_flag := '';
2300 l_old_pr_flag := '';
2301 l_old_loc_flag := '';
2302 l_old_org_flag := '';
2303 l_old_job_flag := '';
2304 end if;
2305 if r_elp.dml_operation = 'INSERT' and l_elp_id is null then
2306 l_dml_operation := 'INSERT';
2307 elsif r_elp.dml_operation in ('INSERT','UPDATE') and l_elp_id is not null then
2308 l_dml_operation := 'UPDATE';
2309 else
2310 l_dml_operation := '';
2311 end if;
2312 hr_utility.set_location('dml_operation is '||l_dml_operation,4);
2313 if r_elp.information232 is not null then
2314 l_loc_flag := 'Y';
2315 else
2316 l_loc_flag := 'N';
2317 end if;
2318 if r_elp.information233 is not null then
2319 l_job_flag := 'Y';
2320 else
2321 l_job_flag := 'N';
2322 end if;
2323 if r_elp.information234 is not null then
2324 l_org_flag := 'Y';
2325 else
2326 l_org_flag := 'N';
2327 end if;
2328 if r_elp.information235 is not null then
2329 l_rl_flag := 'Y';
2330 else
2331 l_rl_flag := 'N';
2332 end if;
2333 if r_elp.information236 is not null then
2334 l_pt_flag := 'Y';
2335 else
2336 l_pt_flag := 'N';
2337 end if;
2338 if r_elp.information237 is not null then
2339 l_sa_flag := 'Y';
2340 else
2341 l_sa_flag := 'N';
2342 end if;
2343 if r_elp.information101 is not null then
2344 l_bu_flag := 'Y';
2345 else
2346 l_bu_flag := 'N';
2347 end if;
2348 if r_elp.information102 is not null then
2349 l_fp_flag := 'Y';
2350 else
2351 l_fp_flag := 'N';
2352 end if;
2353 if r_elp.information103 is not null then
2354 l_pr_flag := 'Y';
2355 else
2356 l_pr_flag := 'N';
2357 end if;
2358 if l_dml_operation = 'INSERT' then
2359 hr_utility.set_location(' BEN_ELIGY_PRFL_F CREATE_ELIGY_PROFILE ',20);
2360 begin
2361 BEN_ELIGY_PROFILE_API.CREATE_ELIGY_PROFILE(
2362 P_EFFECTIVE_DATE => l_effective_date
2363 ,P_BUSINESS_GROUP_ID => p_business_group_id
2364 ,P_ASMT_TO_USE_CD => 'ANY'
2365 ,P_BNFT_CAGR_PRTN_CD => 'BNFT'
2366 ,P_DESCRIPTION => r_ELP.INFORMATION151
2367 ,P_ELIGY_PRFL_ID => l_elp_id
2368 ,P_ELIGY_PRFL_RL_FLAG => 'N'
2369 ,P_ELIG_BRGNG_UNIT_FLAG => 'N'
2370 ,P_ELIG_FL_TM_PT_TM_FLAG => 'N'
2371 ,P_ELIG_JOB_FLAG => 'N'
2372 ,P_ELIG_ORG_UNIT_FLAG => 'N'
2373 ,P_ELIG_PERF_RTNG_FLAG => 'N'
2374 ,P_ELIG_PER_TYP_FLAG => 'N'
2375 ,P_ELIG_SVC_AREA_FLAG => 'N'
2376 ,P_ELIG_WK_LOC_FLAG => 'N'
2377 ,P_NAME => r_ELP.INFORMATION151
2378 ,P_STAT_CD => 'A'
2379 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2380 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2381 ,P_OBJECT_VERSION_NUMBER => l_elp_ovn
2382 );
2383 elp_writeback(p_crset_id => r_elp.information161,
2384 p_elp_id => l_elp_id,
2385 p_copy_entity_txn_id => p_copy_entity_txn_id);
2386 hr_utility.set_location('effdt is '||to_char(l_effective_date,'DD/MM/RRRR'),20);
2387 if l_rl_flag = 'Y' then
2388 hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2389 hr_utility.set_location('rule id is'||r_elp.information235,20);
2390 BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2391 P_EFFECTIVE_DATE => l_effective_date
2392 ,P_BUSINESS_GROUP_ID => p_business_group_id
2393 ,P_DRVBL_FCTR_APLS_FLAG => 'N'
2394 ,P_ELIGY_PRFL_ID => l_elp_id
2395 ,P_ELIGY_PRFL_RL_ID => l_pk
2396 ,P_FORMULA_ID => r_elp.information235
2397 ,P_ORDR_TO_APLY_NUM => 1
2398 ,P_EFFECTIVE_START_DATE => l_esd
2399 ,P_EFFECTIVE_END_DATE => l_eed
2400 ,P_OBJECT_VERSION_NUMBER => l_ovn
2401 );
2402 end if;
2403 if l_bu_flag = 'Y' then
2404 hr_utility.set_location(' BEN_ELIG_BRGNG_UNIT_PRTE_F CREATE_ELIG_BRGNG_UNIT_PRTE ',20);
2405 hr_utility.set_location('bargaining unit cd is'||r_elp.information101,20);
2406 BEN_ELIG_BRGNG_UNIT_PRTE_API.CREATE_ELIG_BRGNG_UNIT_PRTE(
2407 P_EFFECTIVE_DATE => l_effective_date
2408 ,P_BUSINESS_GROUP_ID => p_business_group_id
2409 ,P_BRGNG_UNIT_CD => r_elp.information101
2410 ,P_ELIGY_PRFL_ID => l_elp_id
2411 ,P_ELIG_BRGNG_UNIT_PRTE_ID => l_pk
2412 ,P_EXCLD_FLAG => 'N'
2413 ,P_ORDR_NUM => 1
2414 ,P_EFFECTIVE_START_DATE => l_esd
2415 ,P_EFFECTIVE_END_DATE => l_eed
2416 ,P_OBJECT_VERSION_NUMBER => l_ovn
2417 );
2418 end if;
2419 if l_fp_flag = 'Y' then
2420 hr_utility.set_location(' BEN_ELIG_FL_TM_PT_TM_PRTE_F CREATE_ELIG_FL_TM_PT_TM_PRTE ',20);
2421 hr_utility.set_location('fulltime cd is'||r_elp.information102,20);
2422 BEN_ELIG_FL_TM_PT_TM_PRTE_API.CREATE_ELIG_FL_TM_PT_TM_PRTE(
2423 P_EFFECTIVE_DATE => l_effective_date
2424 ,P_BUSINESS_GROUP_ID => p_business_group_id
2425 ,P_ELIGY_PRFL_ID => l_elp_id
2426 ,P_ELIG_FL_TM_PT_TM_PRTE_ID => l_pk
2427 ,P_EXCLD_FLAG => 'N'
2428 ,P_FL_TM_PT_TM_CD => r_elp.INFORMATION102
2429 ,P_ORDR_NUM => 1
2430 ,P_EFFECTIVE_START_DATE => l_esd
2431 ,P_EFFECTIVE_END_DATE => l_eed
2432 ,P_OBJECT_VERSION_NUMBER => l_ovn
2433 );
2434 end if;
2435 if l_pr_flag = 'Y' then
2436 hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2437 hr_utility.set_location('event type is'||r_elp.information103,20);
2438 hr_utility.set_location('perf_rtng_cd is'||r_elp.information104,20);
2439 BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2440 P_EFFECTIVE_DATE => l_effective_date
2441 ,P_BUSINESS_GROUP_ID => p_business_group_id
2442 ,P_ELIGY_PRFL_ID => l_elp_id
2443 ,P_ELIG_PERF_RTNG_PRTE_ID => l_pk
2444 ,P_EVENT_TYPE => r_elp.information103
2445 ,P_EXCLD_FLAG => 'N'
2446 ,P_ORDR_NUM => 1
2447 ,P_PERF_RTNG_CD => r_elp.information104
2448 ,P_EFFECTIVE_START_DATE => l_esd
2449 ,P_EFFECTIVE_END_DATE => l_eed
2450 ,P_OBJECT_VERSION_NUMBER => l_ovn
2451 );
2452 end if;
2453 if l_pt_flag = 'Y' then
2454 l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2455 hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2456 hr_utility.set_location('per type id is'||r_elp.information236,20);
2457 hr_utility.set_location('per type cd is'||l_pt_cd,20);
2458 BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2459 P_EFFECTIVE_DATE => l_effective_date
2460 ,P_BUSINESS_GROUP_ID => p_business_group_id
2461 ,P_ELIGY_PRFL_ID => l_elp_id
2462 ,P_ELIG_PER_TYP_PRTE_ID => l_pk
2463 ,P_EXCLD_FLAG => 'N'
2464 ,P_ORDR_NUM => 1
2465 ,P_PERSON_TYPE_ID => r_elp.information236
2466 ,P_PER_TYP_CD => l_pt_cd
2467 ,P_EFFECTIVE_START_DATE => l_esd
2468 ,P_EFFECTIVE_END_DATE => l_eed
2469 ,P_OBJECT_VERSION_NUMBER => l_ovn
2470 );
2471 end if;
2472 if l_sa_flag = 'Y' then
2473 hr_utility.set_location(' BEN_ELIG_SVC_AREA_PRTE_F CREATE_ELIG_SVC_AREA_PRTE ',20);
2474 hr_utility.set_location('serv area id is'||r_elp.information237,20);
2475 BEN_ELIG_SVC_AREA_PRTE_API.CREATE_ELIG_SVC_AREA_PRTE(
2476 P_EFFECTIVE_DATE => l_effective_date
2477 ,P_BUSINESS_GROUP_ID => p_business_group_id
2478 ,P_ELIGY_PRFL_ID => l_elp_id
2479 ,P_ELIG_SVC_AREA_PRTE_ID => l_pk
2480 ,P_EXCLD_FLAG => 'N'
2481 ,P_ORDR_NUM => 1
2482 ,P_SVC_AREA_ID => r_elp.information237
2483 ,P_EFFECTIVE_START_DATE => l_esd
2484 ,P_EFFECTIVE_END_DATE => l_eed
2485 ,P_OBJECT_VERSION_NUMBER => l_ovn
2486 );
2487 end if;
2488 if l_loc_flag = 'Y' then
2489 hr_utility.set_location(' BEN_ELIG_WK_LOC_PRTE_F CREATE_ELIG_WK_LOC_PRTE ',20);
2490 hr_utility.set_location('location id is'||r_elp.information232,20);
2491 BEN_ELIG_WK_LOC_PRTE_API.CREATE_ELIG_WK_LOC_PRTE(
2492 P_EFFECTIVE_DATE => l_effective_date
2493 ,P_BUSINESS_GROUP_ID => p_business_group_id
2494 ,P_ELIGY_PRFL_ID => l_elp_id
2495 ,P_ELIG_WK_LOC_PRTE_ID => l_pk
2496 ,P_EXCLD_FLAG => 'N'
2497 ,P_LOCATION_ID => r_elp.information232
2498 ,P_ORDR_NUM => 1
2499 ,P_EFFECTIVE_START_DATE => l_esd
2500 ,P_EFFECTIVE_END_DATE => l_eed
2501 ,P_OBJECT_VERSION_NUMBER => l_ovn
2502 );
2503 end if;
2504 if l_org_flag = 'Y' then
2505 hr_utility.set_location(' BEN_ELIG_ORG_UNIT_PRTE_F CREATE_ELIG_ORG_UNIT_PRTE ',20);
2506 hr_utility.set_location('org id is'||r_elp.information234,20);
2507 BEN_ELIG_ORG_UNIT_PRTE_API.CREATE_ELIG_ORG_UNIT_PRTE(
2508 P_EFFECTIVE_DATE => l_effective_date
2509 ,P_BUSINESS_GROUP_ID => p_business_group_id
2510 ,P_ELIGY_PRFL_ID => l_elp_id
2511 ,P_ELIG_ORG_UNIT_PRTE_ID => l_pk
2512 ,P_EXCLD_FLAG => 'N'
2513 ,P_ORDR_NUM => 1
2514 ,P_ORGANIZATION_ID => r_elp.information234
2515 ,P_EFFECTIVE_START_DATE => l_esd
2516 ,P_EFFECTIVE_END_DATE => l_eed
2517 ,P_OBJECT_VERSION_NUMBER => l_ovn
2518 );
2519 end if;
2520 if l_job_flag = 'Y' then
2521 hr_utility.set_location(' BEN_ELIG_JOB_PRTE_F CREATE_ELIGY_JOB_PRTE ',20);
2522 hr_utility.set_location('org id is'||r_elp.information233,20);
2523 BEN_ELIGY_JOB_PRTE_API.CREATE_ELIGY_JOB_PRTE(
2524 P_EFFECTIVE_DATE => l_effective_date
2525 ,P_BUSINESS_GROUP_ID => p_business_group_id
2526 ,P_ELIGY_PRFL_ID => l_elp_id
2527 ,P_ELIG_JOB_PRTE_ID => l_pk
2528 ,P_EXCLD_FLAG => 'N'
2529 ,P_JOB_ID => r_elp.information233
2530 ,P_ORDR_NUM => 1
2531 ,P_EFFECTIVE_START_DATE => l_esd
2532 ,P_EFFECTIVE_END_DATE => l_eed
2533 ,P_OBJECT_VERSION_NUMBER => l_ovn
2534 );
2535 end if;
2536 exception when others then
2537 hr_utility.set_location('issues in writing elp, skipping'||l_proc,100);
2538 raise;
2539 end;
2540 hr_utility.set_location('After plsql table ',222);
2541 elsif l_dml_operation = 'UPDATE' then
2542 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2543 if p_datetrack_mode <> 'CORRECTION' then
2544 l_dt_mode := get_update_mode(p_table_name => 'BEN_ELIGY_PRFL_F',
2545 p_key_column_name => 'ELIGY_PRFL_ID',
2546 p_key_column_value => l_elp_id,
2547 p_effective_date => l_effective_date);
2548 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2549 else
2550 l_dt_mode := p_datetrack_mode;
2551 end if;
2552 l_db_ovn := get_ovn(p_table_name => 'BEN_ELIGY_PRFL_F',
2553 p_key_column_name => 'ELIGY_PRFL_ID',
2554 p_key_column_value => l_elp_id,
2555 p_effective_date => l_effective_date);
2556 hr_utility.set_location(' ovn is '||l_db_ovn,30);
2557 if l_db_ovn <> l_ovn then
2558 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','ELP');
2559 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2560 fnd_message.set_token('OBJECT ',l_object);
2561 fnd_message.set_token('OBJECT_NAME ',r_ELP.INFORMATION151);
2562 fnd_message.raise_error;
2563 else
2564 hr_utility.set_location(' BEN_ELIGY_PRFL_F UPDATE_ELIGY_PROFILE ',30);
2565 begin
2566 BEN_ELIGY_PROFILE_API.UPDATE_ELIGY_PROFILE(
2567 P_EFFECTIVE_DATE => l_effective_date
2568 ,P_BUSINESS_GROUP_ID => p_business_group_id
2569 ,P_DESCRIPTION => r_ELP.INFORMATION151
2570 ,P_ELIGY_PRFL_ID => l_elp_id
2571 ,P_ELIGY_PRFL_RL_FLAG => 'N'
2572 ,P_ELIG_BRGNG_UNIT_FLAG => 'N'
2573 ,P_ELIG_FL_TM_PT_TM_FLAG => 'N'
2574 ,P_ELIG_JOB_FLAG => 'N'
2575 ,P_ELIG_ORG_UNIT_FLAG => 'N'
2576 ,P_ELIG_PERF_RTNG_FLAG => 'N'
2577 ,P_ELIG_PER_TYP_FLAG => 'N'
2578 ,P_ELIG_SVC_AREA_FLAG => 'N'
2579 ,P_ELIG_WK_LOC_FLAG => 'N'
2580 ,P_NAME => r_ELP.INFORMATION151
2581 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2582 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2583 ,P_OBJECT_VERSION_NUMBER => l_elp_ovn
2584 ,P_DATETRACK_MODE => l_dt_mode
2585 );
2586 exception when others then
2587 hr_utility.set_location('issues in writing elp, skipping'||l_proc,100);
2588 raise;
2589 end;
2590 end if;
2591 if l_rl_flag = 'Y' and l_old_rl_flag is null and r_elp.information225 is null then
2592 hr_utility.set_location(' BEN_ELIGY_PRFL_RL_F CREATE_ELIGY_PROFILE_RULE ',20);
2593 BEN_ELIGY_PROFILE_RULE_API.CREATE_ELIGY_PROFILE_RULE(
2594 P_EFFECTIVE_DATE => l_effective_date
2595 ,P_BUSINESS_GROUP_ID => p_business_group_id
2596 ,P_DRVBL_FCTR_APLS_FLAG => 'N'
2597 ,P_ELIGY_PRFL_ID => l_elp_id
2598 ,P_ELIGY_PRFL_RL_ID => l_pk
2599 ,P_FORMULA_ID => r_elp.information235
2600 ,P_ORDR_TO_APLY_NUM => 1
2601 ,P_EFFECTIVE_START_DATE => l_esd
2602 ,P_EFFECTIVE_END_DATE => l_eed
2603 ,P_OBJECT_VERSION_NUMBER => l_ovn
2604 );
2605 elsif l_rl_flag is null and l_old_rl_flag = 'Y' and r_elp.information225 is null then
2606 end_date_crit(p_elig_prfl_id => l_elp_id,
2607 p_crit_type => 'RL',
2608 p_effective_date => l_effective_date);
2609 end if;
2610 if l_bu_flag = 'Y' and l_old_bu_flag is null and r_elp.information228 is null then
2611 hr_utility.set_location(' BEN_ELIG_BRGNG_UNIT_PRTE_F CREATE_ELIG_BRGNG_UNIT_PRTE ',20);
2612 BEN_ELIG_BRGNG_UNIT_PRTE_API.CREATE_ELIG_BRGNG_UNIT_PRTE(
2613 P_EFFECTIVE_DATE => l_effective_date
2614 ,P_BUSINESS_GROUP_ID => p_business_group_id
2615 ,P_BRGNG_UNIT_CD => r_elp.information101
2616 ,P_ELIGY_PRFL_ID => l_elp_id
2617 ,P_ELIG_BRGNG_UNIT_PRTE_ID => l_pk
2618 ,P_EXCLD_FLAG => 'N'
2619 ,P_ORDR_NUM => 1
2620 ,P_EFFECTIVE_START_DATE => l_esd
2621 ,P_EFFECTIVE_END_DATE => l_eed
2622 ,P_OBJECT_VERSION_NUMBER => l_ovn
2623 );
2624 elsif l_bu_flag is null and l_old_bu_flag = 'Y' and r_elp.information228 is null then
2625 end_date_crit(p_elig_prfl_id => l_elp_id,
2626 p_crit_type => 'BU',
2627 p_effective_date => l_effective_date);
2628 end if;
2629 if l_fp_flag = 'Y' and l_old_fp_flag is null and r_elp.information229 is null then
2630 hr_utility.set_location(' BEN_ELIG_FL_TM_PT_TM_PRTE_F CREATE_ELIG_FL_TM_PT_TM_PRTE ',20);
2631 BEN_ELIG_FL_TM_PT_TM_PRTE_API.CREATE_ELIG_FL_TM_PT_TM_PRTE(
2632 P_EFFECTIVE_DATE => l_effective_date
2633 ,P_BUSINESS_GROUP_ID => p_business_group_id
2634 ,P_ELIGY_PRFL_ID => l_elp_id
2635 ,P_ELIG_FL_TM_PT_TM_PRTE_ID => l_pk
2636 ,P_EXCLD_FLAG => 'N'
2637 ,P_FL_TM_PT_TM_CD => r_elp.INFORMATION102
2638 ,P_ORDR_NUM => 1
2639 ,P_EFFECTIVE_START_DATE => l_esd
2640 ,P_EFFECTIVE_END_DATE => l_eed
2641 ,P_OBJECT_VERSION_NUMBER => l_ovn
2642 );
2643 elsif l_fp_flag is null and l_old_fp_flag = 'Y' and r_elp.information229 is null then
2644 end_date_crit(p_elig_prfl_id => l_elp_id,
2645 p_crit_type => 'FP',
2646 p_effective_date => l_effective_date);
2647 end if;
2648 if l_pr_flag = 'Y' and l_old_pr_flag is null and r_elp.information230 is null then
2649 hr_utility.set_location(' BEN_ELIG_PERF_RTNG_PRTE_F CREATE_ELIG_PERF_RTNG_PRTE ',20);
2650 BEN_ELIG_PERF_RTNG_PRTE_API.CREATE_ELIG_PERF_RTNG_PRTE(
2651 P_EFFECTIVE_DATE => l_effective_date
2652 ,P_BUSINESS_GROUP_ID => p_business_group_id
2653 ,P_ELIGY_PRFL_ID => l_elp_id
2654 ,P_ELIG_PERF_RTNG_PRTE_ID => l_pk
2655 ,P_EVENT_TYPE => r_elp.information104
2656 ,P_EXCLD_FLAG => 'N'
2657 ,P_ORDR_NUM => 1
2658 ,P_PERF_RTNG_CD => r_elp.information103
2659 ,P_EFFECTIVE_START_DATE => l_esd
2660 ,P_EFFECTIVE_END_DATE => l_eed
2661 ,P_OBJECT_VERSION_NUMBER => l_ovn
2662 );
2663 elsif l_pr_flag is null and l_old_pr_flag = 'Y' and r_elp.information230 is null then
2664 end_date_crit(p_elig_prfl_id => l_elp_id,
2665 p_crit_type => 'PR',
2666 p_effective_date => l_effective_date);
2667 end if;
2668 if l_pt_flag = 'Y' and l_old_pt_flag is null and r_elp.information226 is null then
2669 l_pt_cd := get_per_typ_cd(P_PERSON_TYPE_ID => r_elp.information236);
2670 hr_utility.set_location(' BEN_ELIG_PER_TYP_PRTE_F CREATE_ELIG_PER_TYP_PRTE ',20);
2671 BEN_ELIG_PER_TYP_PRTE_API.CREATE_ELIG_PER_TYP_PRTE(
2672 P_EFFECTIVE_DATE => l_effective_date
2673 ,P_BUSINESS_GROUP_ID => p_business_group_id
2674 ,P_ELIGY_PRFL_ID => l_elp_id
2675 ,P_ELIG_PER_TYP_PRTE_ID => l_pk
2676 ,P_EXCLD_FLAG => 'N'
2677 ,P_ORDR_NUM => 1
2678 ,P_PERSON_TYPE_ID => r_elp.information236
2679 ,P_PER_TYP_CD => l_pt_cd
2680 ,P_EFFECTIVE_START_DATE => l_esd
2681 ,P_EFFECTIVE_END_DATE => l_eed
2682 ,P_OBJECT_VERSION_NUMBER => l_ovn
2683 );
2684 elsif l_pt_flag is null and l_old_pt_flag = 'Y' and r_elp.information226 is null then
2685 end_date_crit(p_elig_prfl_id => l_elp_id,
2686 p_crit_type => 'PT',
2687 p_effective_date => l_effective_date);
2688 end if;
2689 if l_sa_flag = 'Y' and l_old_sa_flag is null and r_elp.information227 is null then
2690 hr_utility.set_location(' BEN_ELIG_SVC_AREA_PRTE_F CREATE_ELIG_SVC_AREA_PRTE ',20);
2691 BEN_ELIG_SVC_AREA_PRTE_API.CREATE_ELIG_SVC_AREA_PRTE(
2692 P_EFFECTIVE_DATE => l_effective_date
2693 ,P_BUSINESS_GROUP_ID => p_business_group_id
2694 ,P_ELIGY_PRFL_ID => l_elp_id
2695 ,P_ELIG_SVC_AREA_PRTE_ID => l_pk
2696 ,P_EXCLD_FLAG => 'N'
2697 ,P_ORDR_NUM => 1
2698 ,P_SVC_AREA_ID => r_elp.information237
2699 ,P_EFFECTIVE_START_DATE => l_esd
2700 ,P_EFFECTIVE_END_DATE => l_eed
2701 ,P_OBJECT_VERSION_NUMBER => l_ovn
2702 );
2703 elsif l_sa_flag is null and l_old_sa_flag = 'Y' and r_elp.information227 is null then
2704 end_date_crit(p_elig_prfl_id => l_elp_id,
2705 p_crit_type => 'SA',
2706 p_effective_date => l_effective_date);
2707 end if;
2708 if l_loc_flag = 'Y' and l_old_loc_flag is null and r_elp.information222 is null then
2709 hr_utility.set_location(' BEN_ELIG_WK_LOC_PRTE_F CREATE_ELIG_WK_LOC_PRTE ',20);
2710 BEN_ELIG_WK_LOC_PRTE_API.CREATE_ELIG_WK_LOC_PRTE(
2711 P_EFFECTIVE_DATE => l_effective_date
2712 ,P_BUSINESS_GROUP_ID => p_business_group_id
2713 ,P_ELIGY_PRFL_ID => l_elp_id
2714 ,P_ELIG_WK_LOC_PRTE_ID => l_pk
2715 ,P_EXCLD_FLAG => 'N'
2716 ,P_LOCATION_ID => r_elp.information232
2717 ,P_ORDR_NUM => 1
2718 ,P_EFFECTIVE_START_DATE => l_esd
2719 ,P_EFFECTIVE_END_DATE => l_eed
2720 ,P_OBJECT_VERSION_NUMBER => l_ovn
2721 );
2722 elsif l_loc_flag is null and l_old_loc_flag = 'Y' and r_elp.information222 is null then
2723 end_date_crit(p_elig_prfl_id => l_elp_id,
2724 p_crit_type => 'LOC',
2725 p_effective_date => l_effective_date);
2726 end if;
2727 if l_org_flag = 'Y' and l_old_org_flag is null and r_elp.information224 is null then
2728 hr_utility.set_location(' BEN_ELIG_ORG_UNIT_PRTE_F CREATE_ELIG_ORG_UNIT_PRTE ',20);
2729 BEN_ELIG_ORG_UNIT_PRTE_API.CREATE_ELIG_ORG_UNIT_PRTE(
2730 P_EFFECTIVE_DATE => l_effective_date
2731 ,P_BUSINESS_GROUP_ID => p_business_group_id
2732 ,P_ELIGY_PRFL_ID => l_elp_id
2733 ,P_ELIG_ORG_UNIT_PRTE_ID => l_pk
2734 ,P_EXCLD_FLAG => 'N'
2735 ,P_ORDR_NUM => 1
2736 ,P_ORGANIZATION_ID => r_elp.information234
2737 ,P_EFFECTIVE_START_DATE => l_esd
2738 ,P_EFFECTIVE_END_DATE => l_eed
2739 ,P_OBJECT_VERSION_NUMBER => l_ovn
2740 );
2741 elsif l_org_flag is null and l_old_org_flag = 'Y' and r_elp.information224 is null then
2742 end_date_crit(p_elig_prfl_id => l_elp_id,
2743 p_crit_type => 'ORG',
2744 p_effective_date => l_effective_date);
2745 end if;
2746 if l_job_flag = 'Y' and l_old_job_flag is null and r_elp.information223 is null then
2747 hr_utility.set_location(' BEN_ELIG_JOB_PRTE_F CREATE_ELIGY_JOB_PRTE ',20);
2748 BEN_ELIGY_JOB_PRTE_API.CREATE_ELIGY_JOB_PRTE(
2749 P_EFFECTIVE_DATE => l_effective_date
2750 ,P_BUSINESS_GROUP_ID => p_business_group_id
2751 ,P_ELIGY_PRFL_ID => l_elp_id
2752 ,P_ELIG_JOB_PRTE_ID => l_pk
2753 ,P_EXCLD_FLAG => 'N'
2754 ,P_JOB_ID => r_elp.information233
2755 ,P_ORDR_NUM => 1
2756 ,P_EFFECTIVE_START_DATE => l_esd
2757 ,P_EFFECTIVE_END_DATE => l_eed
2758 ,P_OBJECT_VERSION_NUMBER => l_ovn
2759 );
2760 elsif l_job_flag is null and l_old_job_flag = 'Y' and r_elp.information223 is null then
2761 end_date_crit(p_elig_prfl_id => l_elp_id,
2762 p_crit_type => 'JOB',
2763 p_effective_date => l_effective_date);
2764 end if;
2765 else
2766 l_message_text := 'invalid dml_oper'||l_dml_operation
2767 ||' elp_ovn'||l_elp_ovn
2768 ||' elp_id'||l_elp_id;
2769 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2770 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2771 P_TXN_ID => nvl(l_elp_id,p_copy_entity_txn_id),
2772 P_MODULE_CD => 'PQH_GSP_STGBEN',
2773 p_context => 'ELP',
2774 P_MESSAGE_TYPE_CD => 'E',
2775 P_MESSAGE_TEXT => l_message_text,
2776 p_effective_date => p_effective_date);
2777 end if;
2778 l_old_elp_id := l_elp_id;
2779 l_old_elp_ovn := l_elp_ovn;
2780 l_old_crset_id := r_elp.information161;
2781 l_old_sa_flag := l_sa_flag;
2782 l_old_fp_flag := l_fp_flag;
2783 l_old_pt_flag := l_pt_flag;
2784 l_old_rl_flag := l_rl_flag;
2785 l_old_bu_flag := l_bu_flag;
2786 l_old_pr_flag := l_pr_flag;
2787 l_old_loc_flag := l_loc_flag;
2788 l_old_org_flag := l_org_flag;
2789 l_old_job_flag := l_job_flag;
2790 end loop;
2791 hr_utility.set_location('leaving '||l_proc,100);
2792 exception
2793 when others then
2794 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2795 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2796 P_TXN_ID => p_copy_entity_txn_id,
2797 P_MODULE_CD => 'PQH_GSP_STGBEN',
2798 p_context => 'ELP',
2799 P_MESSAGE_TYPE_CD => 'E',
2800 P_MESSAGE_TEXT => 'ELP',
2801 p_effective_date => p_effective_date);
2802 raise;
2803 end stage_to_elp;
2804 procedure stage_to_cep(p_copy_entity_txn_id in number,
2805 p_business_group_id in number,
2806 p_effective_date in date,
2807 p_datetrack_mode in varchar2) is
2808 cursor c_cep is
2809 select *
2810 from ben_copy_entity_results
2811 where copy_entity_txn_id = p_copy_entity_txn_id
2812 and table_alias = 'CEP'
2813 and dml_operation in ('INSERT','UPDATE') ; -- only insert/update should be there
2814 --
2815 r_cep c_cep%rowtype;
2816 l_proc varchar2(61) :='stage_to_cep';
2817 l_cep_id number ;
2818 l_ovn number ;
2819 l_effective_start_date date ;
2820 l_effective_end_date date ;
2821 l_effective_date date;
2822 l_message_text varchar2(2000);
2823 l_dt_mode varchar2(30);
2824 l_object varchar2(80);
2825 l_db_ovn varchar2(30);
2826 l_epa_id number;
2827 l_pk number;
2828 l_tab varchar2(30);
2829 begin
2830 hr_utility.set_location('inside '||l_proc,10);
2831 for r_cep in c_cep loop
2832 l_cep_id := r_cep.information1;
2833 l_ovn := r_cep.information265;
2834 hr_utility.set_location('for cep_id:'||l_cep_id ||'dml '||r_cep.dml_operation,20);
2835 if r_cep.gs_mirror_src_entity_result_id is not null then
2836 select information1,table_alias
2837 into l_pk, l_tab
2838 from ben_copy_entity_results
2839 where copy_entity_result_id = r_cep.gs_mirror_src_entity_result_id;
2840 hr_utility.set_location('parent tab is'||l_tab ||' pk is '||l_pk,5);
2841 if l_tab ='EPA' then
2842 l_epa_id := l_pk;
2843 else
2844 l_epa_id := null;
2845 end if;
2846 else
2847 l_epa_id := null;
2848 end if;
2849 l_effective_date := r_cep.information2;
2850 begin
2851 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
2852 hr_utility.set_location(' BEN_PRTN_ELIG_PRFL_F CREATE_PRTN_ELIG_PRFL ',20);
2853 BEN_PRTN_ELIG_PRFL_API.CREATE_PRTN_ELIG_PRFL(
2854 P_EFFECTIVE_DATE => l_effective_date
2855 ,P_BUSINESS_GROUP_ID => p_business_group_id
2856 ,P_ELIGY_PRFL_ID => r_CEP.INFORMATION263
2857 ,P_ELIG_PRFL_TYPE_CD => r_CEP.INFORMATION11
2858 ,P_MNDTRY_FLAG => nvl(r_CEP.INFORMATION12,'N')
2859 ,P_COMPUTE_SCORE_FLAG => r_CEP.INFORMATION13
2860 ,P_PRTN_ELIG_ID => l_epa_id
2861 ,P_PRTN_ELIG_PRFL_ID => l_cep_id
2862 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2863 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2864 ,P_OBJECT_VERSION_NUMBER => l_ovn
2865 );
2866 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
2867 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
2868 if p_datetrack_mode <> 'CORRECTION' then
2869 l_dt_mode := get_update_mode(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2870 p_key_column_name => 'PRTN_ELIG_PRFL_ID',
2871 p_key_column_value => l_cep_id,
2872 p_effective_date => l_effective_date);
2873 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2874 else
2875 l_dt_mode := p_datetrack_mode;
2876 end if;
2877 l_db_ovn := get_ovn(p_table_name => 'BEN_PRTN_ELIG_PRFL_F',
2878 p_key_column_name => 'PRTN_ELIG_PRFL_ID',
2879 p_key_column_value => l_cep_id,
2880 p_effective_date => l_effective_date);
2881 hr_utility.set_location(' ovn is '||l_db_ovn,30);
2882 if l_db_ovn <> l_ovn then
2883 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','CEP');
2884 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2885 fnd_message.set_token('OBJECT ',l_object);
2886 fnd_message.set_token('OBJECT_NAME ','CEP :'||l_cep_id);
2887 fnd_message.raise_error;
2888 else
2889 hr_utility.set_location(' BEN_PRTN_ELIG_PRFL_F UPDATE_PRTN_ELIG_PRFL ',30);
2890 BEN_PRTN_ELIG_PRFL_API.UPDATE_PRTN_ELIG_PRFL(
2891 P_EFFECTIVE_DATE => l_effective_date
2892 ,P_BUSINESS_GROUP_ID => p_business_group_id
2893 ,P_ELIGY_PRFL_ID => r_CEP.INFORMATION263
2894 ,P_MNDTRY_FLAG => r_CEP.INFORMATION12
2895 ,P_COMPUTE_SCORE_FLAG => r_CEP.INFORMATION13
2896 ,P_PRTN_ELIG_ID => l_epa_id
2897 ,P_PRTN_ELIG_PRFL_ID => l_cep_id
2898 ,P_EFFECTIVE_START_DATE => l_effective_start_date
2899 ,P_EFFECTIVE_END_DATE => l_effective_end_date
2900 ,P_OBJECT_VERSION_NUMBER => l_ovn
2901 ,P_DATETRACK_MODE => l_dt_mode);
2902 end if;
2903 else
2904 l_message_text := 'invalid dml_oper'||r_cep.dml_operation
2905 ||' cep_id '||l_cep_id
2906 ||' cep_ovn '||l_ovn
2907 ||' epa_id '||l_epa_id;
2908 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2909 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2910 P_TXN_ID => nvl(l_cep_id,p_copy_entity_txn_id),
2911 P_MODULE_CD => 'PQH_GSP_STGBEN',
2912 p_context => 'CEP',
2913 P_MESSAGE_TYPE_CD => 'E',
2914 P_MESSAGE_TEXT => l_message_text,
2915 p_effective_date => p_effective_date);
2916 end if;
2917 exception when others then
2918 hr_utility.set_location('issues in writing cep, skipping'||l_proc,100);
2919 raise;
2920 end;
2921 end loop;
2922 hr_utility.set_location('leaving '||l_proc,100);
2923 exception
2924 when others then
2925 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2926 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2927 P_TXN_ID => p_copy_entity_txn_id,
2928 P_MODULE_CD => 'PQH_GSP_STGBEN',
2929 p_context => 'CEP',
2930 P_MESSAGE_TYPE_CD => 'E',
2931 P_MESSAGE_TEXT => 'CEP',
2932 p_effective_date => p_effective_date);
2933 raise;
2934 end stage_to_cep;
2935 procedure stage_to_abr(p_copy_entity_txn_id in number,
2936 p_business_group_id in number,
2937 p_effective_date in date,
2938 p_datetrack_mode in varchar2) is
2939 cursor c_abr is
2940 select *
2941 from ben_copy_entity_results
2942 where copy_entity_txn_id = p_copy_entity_txn_id
2943 and table_alias = 'ABR'
2944 and dml_operation = 'INSERT' ;-- only insert should be there
2945 --
2946 r_abr c_abr%rowtype;
2947 l_proc varchar2(61) :='stage_to_abr';
2948 l_abr_id number ;
2949 l_pl_id number ;
2950 l_message_text varchar2(2000);
2951 l_opt_id number ;
2952 l_ovn number ;
2953 l_effective_start_date date ;
2954 l_effective_end_date date ;
2955 l_effective_date date;
2956 begin
2957 hr_utility.set_location('inside '||l_proc,10);
2958 for r_abr in c_abr loop
2959 l_pl_id := null;
2960 l_opt_id := null;
2961 l_abr_id := r_abr.information1;
2962 hr_utility.set_location('for abr_id:'||l_abr_id ||'dml '||r_abr.dml_operation,20);
2963 l_effective_date := r_abr.information2;
2964 hr_utility.set_location('effdt is'||to_char(l_effective_date,'DD/MM/RRRR'),21);
2965 hr_utility.set_location('hrr id is '||r_ABR.INFORMATION266,22);
2966 if r_ABR.INFORMATION277 is not null and r_ABR.INFORMATION261 is null then
2967 hr_utility.set_location('pl_cer_id :'||r_ABR.INFORMATION277,3);
2968 begin
2969 select information1
2970 into l_pl_id
2971 from ben_copy_entity_results
2972 where copy_entity_result_id = r_ABR.INFORMATION277;
2973 exception
2974 when others then
2975 l_pl_id := '';
2976 end;
2977 elsif r_ABR.INFORMATION261 is not null then
2978 l_pl_id := r_ABR.INFORMATION261;
2979 elsif r_ABR.INFORMATION247 is not null then
2980 l_opt_id := r_ABR.INFORMATION247;
2981 elsif r_ABR.INFORMATION278 is not null and r_ABR.INFORMATION247 is null then
2982 hr_utility.set_location('opt_cer_id :'||r_ABR.INFORMATION278,3);
2983 begin
2984 select information1
2985 into l_opt_id
2986 from ben_copy_entity_results
2987 where copy_entity_result_id = r_ABR.INFORMATION278;
2988 exception
2989 when others then
2990 l_opt_id := '';
2991 end;
2992 else
2993 hr_utility.set_location('pl id is '||r_ABR.INFORMATION261,3);
2994 hr_utility.set_location('pl cer id is '||r_ABR.INFORMATION277,3);
2995 hr_utility.set_location('opt id is '||r_ABR.INFORMATION247,3);
2996 hr_utility.set_location('opt cer id is '||r_ABR.INFORMATION278,3);
2997 end if;
2998 /*
2999 l_message_text := 'oper is'||r_ABR.dml_operation
3000 ||' pl id is '||r_ABR.INFORMATION261
3001 ||' abr id is '||l_abr_id
3002 ||' hrr id is '||r_abr.information266
3003 ||' abr ovn is '||l_ovn
3004 ||' opt id is '||r_ABR.INFORMATION247;
3005 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3006 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3007 P_TXN_ID => nvl(l_abr_id,p_copy_entity_txn_id),
3008 P_MODULE_CD => 'PQH_GSP_STGBEN',
3009 p_context => 'ABR',
3010 P_MESSAGE_TYPE_CD => 'C',
3011 P_MESSAGE_TEXT => l_message_text,
3012 p_effective_date => p_effective_date);
3013 */
3014 begin
3015 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
3016 hr_utility.set_location(' BEN_ACTY_BASE_RT_F CREATE_ACTY_BASE_RATE ',20);
3017 BEN_ACTY_BASE_RATE_API.CREATE_ACTY_BASE_RATE(
3018 P_EFFECTIVE_DATE => l_effective_date
3019 ,P_BUSINESS_GROUP_ID => p_business_group_id
3020 ,P_ABV_MX_ELCN_VAL_ALWD_FLAG => nvl(r_ABR.INFORMATION27,'N')
3021 ,P_ACTL_PREM_ID => r_ABR.information250
3022 ,P_ACTY_BASE_RT_ID => l_abr_id
3023 ,P_ACTY_BASE_RT_STAT_CD => 'A'
3024 ,P_ACTY_TYP_CD => 'GSPSA'
3025 ,P_ALWS_CHG_CD => r_ABR.INFORMATION11
3026 ,P_ANN_MN_ELCN_VAL => r_ABR.INFORMATION298
3027 ,P_ANN_MX_ELCN_VAL => r_ABR.INFORMATION299
3028 ,P_ASMT_TO_USE_CD => r_ABR.INFORMATION23
3029 ,P_ASN_ON_ENRT_FLAG => nvl(r_ABR.INFORMATION26,'N')
3030 ,P_BLW_MN_ELCN_ALWD_FLAG => nvl(r_ABR.INFORMATION28,'N')
3031 ,P_BNFT_RT_TYP_CD => r_ABR.INFORMATION51
3032 ,P_CLM_COMP_LVL_FCTR_ID => r_ABR.information273
3033 -- ,P_CMBN_PLIP_ID => r_ABR.information239
3034 -- ,P_CMBN_PTIP_ID => r_ABR.information236
3035 -- ,P_CMBN_PTIP_OPT_ID => r_ABR.information249
3036 ,P_COMP_LVL_FCTR_ID => r_ABR.information254
3037 ,P_COST_ALLOCATION_KEYFLEX_ID => r_ABR.information262
3038 ,P_DET_PL_YTD_CNTRS_CD => r_ABR.INFORMATION24
3039 ,P_DFLT_FLAG => nvl(r_ABR.INFORMATION39,'N')
3040 ,P_DFLT_VAL => r_ABR.INFORMATION297
3041 ,P_DSPLY_ON_ENRT_FLAG => nvl(r_ABR.INFORMATION29,'N')
3042 ,P_ELEMENT_TYPE_ID => r_ABR.information174
3043 ,P_ELE_ENTRY_VAL_CD => r_ABR.INFORMATION12
3044 ,P_ELE_RQD_FLAG => nvl(r_ABR.INFORMATION45,'N')
3045 ,P_ENTR_ANN_VAL_FLAG => nvl(r_ABR.INFORMATION44,'N')
3046 ,P_ENTR_VAL_AT_ENRT_FLAG => nvl(r_ABR.INFORMATION41,'N')
3047 ,P_FRGN_ERG_DED_IDENT => r_ABR.INFORMATION141
3048 ,P_FRGN_ERG_DED_NAME => r_ABR.INFORMATION185
3049 ,P_FRGN_ERG_DED_TYP_CD => r_ABR.INFORMATION19
3050 ,P_INCRMT_ELCN_VAL => r_ABR.INFORMATION296
3051 ,P_INPUT_VALUE_ID => r_ABR.information178
3052 ,P_INPUT_VA_CALC_RL => r_ABR.information263
3053 ,P_LWR_LMT_CALC_RL => r_ABR.information268
3054 ,P_LWR_LMT_VAL => r_ABR.INFORMATION300
3055 ,P_MN_ELCN_VAL => r_ABR.INFORMATION293
3056 ,P_MX_ELCN_VAL => r_ABR.INFORMATION294
3057 ,P_NAME => r_ABR.INFORMATION170
3058 ,P_NNMNTRY_UOM => r_ABR.INFORMATION14
3059 ,P_NO_MN_ELCN_VAL_DFND_FLAG => nvl(r_ABR.INFORMATION42,'N')
3060 ,P_NO_MX_ELCN_VAL_DFND_FLAG => nvl(r_ABR.INFORMATION40,'N')
3061 ,P_NO_STD_RT_USED_FLAG => nvl(r_ABR.INFORMATION36,'N')
3062 -- ,P_OIPLIP_ID => r_ABR.information227
3063 -- ,P_OIPL_ID => r_ABR.information258
3064 ,P_ONE_ANN_PYMT_CD => r_ABR.INFORMATION46
3065 ,P_ONLY_ONE_BAL_TYP_ALWD_FLAG => nvl(r_ABR.INFORMATION43,'N')
3066 ,P_OPT_ID => l_opt_id
3067 ,P_ORDR_NUM => r_ABR.INFORMATION264
3068 ,P_PARNT_ACTY_BASE_RT_ID => r_ABR.information267
3069 ,P_PARNT_CHLD_CD => r_ABR.INFORMATION53
3070 ,P_PAY_RATE_GRADE_RULE_ID => r_ABR.INFORMATION266
3071 -- ,P_PGM_ID => r_ABR.information260
3072 -- ,P_PLIP_ID => r_ABR.information256
3073 ,P_PL_ID => l_pl_id
3074 ,P_PRDCT_FLX_CR_WHEN_ELIG_FLAG => nvl(r_ABR.INFORMATION35,'N')
3075 ,P_PROCG_SRC_CD => r_ABR.INFORMATION18
3076 ,P_PROC_EACH_PP_DFLT_FLAG => nvl(r_ABR.INFORMATION34,'N')
3077 ,P_PRORT_MN_ANN_ELCN_VAL_CD => r_ABR.INFORMATION47
3078 ,P_PRORT_MN_ANN_ELCN_VAL_RL => r_ABR.information274
3079 ,P_PRORT_MX_ANN_ELCN_VAL_CD => r_ABR.INFORMATION48
3080 ,P_PRORT_MX_ANN_ELCN_VAL_RL => r_ABR.information275
3081 ,P_PRTL_MO_DET_MTHD_CD => r_ABR.INFORMATION16
3082 ,P_PRTL_MO_DET_MTHD_RL => r_ABR.information281
3083 ,P_PRTL_MO_EFF_DT_DET_CD => r_ABR.INFORMATION20
3084 ,P_PRTL_MO_EFF_DT_DET_RL => r_ABR.information280
3085 ,P_PTD_COMP_LVL_FCTR_ID => r_ABR.information272
3086 -- ,P_PTIP_ID => r_ABR.information259
3087 ,P_RCRRG_CD => r_ABR.INFORMATION13
3088 ,P_RNDG_CD => r_ABR.INFORMATION15
3089 ,P_RNDG_RL => r_ABR.information279
3090 ,P_RT_MLT_CD => 'PRV' -- use payrate value
3091 ,P_RT_TYP_CD => r_ABR.INFORMATION50
3092 ,P_RT_USG_CD => 'STD'
3093 ,P_SUBJ_TO_IMPTD_INCM_FLAG => nvl(r_ABR.INFORMATION22,'N')
3094 ,P_TTL_COMP_LVL_FCTR_ID => r_ABR.information257
3095 ,P_TX_TYP_CD => 'PRETAX'
3096 ,P_UPR_LMT_CALC_RL => r_ABR.information269
3097 ,P_UPR_LMT_VAL => r_ABR.INFORMATION301
3098 ,P_USES_DED_SCHED_FLAG => nvl(r_ABR.INFORMATION31,'N')
3099 ,P_USES_PYMT_SCHED_FLAG => nvl(r_ABR.INFORMATION37,'N')
3100 ,P_USES_VARBL_RT_FLAG => 'N' -- uses variable rate
3101 ,P_USE_CALC_ACTY_BS_RT_FLAG => 'Y' -- value is to be computed
3102 ,P_USE_TO_CALC_NET_FLX_CR_FLAG => nvl(r_ABR.INFORMATION25,'N')
3103 ,P_VAL => r_ABR.INFORMATION295
3104 ,P_VAL_CALC_RL => r_ABR.information282
3105 ,P_VAL_OVRID_ALWD_FLAG => nvl(r_ABR.INFORMATION38,'N')
3106 ,P_VSTG_FOR_ACTY_RT_ID => r_ABR.information271
3107 ,P_VSTG_SCHED_APLS_FLAG => nvl(r_ABR.INFORMATION33,'N')
3108 ,P_WSH_RL_DY_MO_NUM => r_ABR.INFORMATION270
3109 ,P_EFFECTIVE_START_DATE => l_effective_start_date
3110 ,P_EFFECTIVE_END_DATE => l_effective_end_date
3111 ,P_OBJECT_VERSION_NUMBER => l_ovn
3112 );
3113 hr_utility.set_location('After plsql table ',222);
3114 update ben_copy_entity_results
3115 set information1 = l_abr_id
3116 where copy_entity_result_id = r_abr.copy_entity_result_id;
3117 hr_utility.set_location('abr id updated '||l_abr_id,222);
3118 else
3119 l_message_text := 'invalid oper'||r_ABR.dml_operation
3120 ||' pl id is '||r_ABR.INFORMATION261
3121 ||' abr id is '||l_abr_id
3122 ||' hrr id is '||r_abr.information266
3123 ||' abr ovn is '||l_ovn
3124 ||' opt id is '||r_ABR.INFORMATION247;
3125 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3126 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3127 P_TXN_ID => nvl(l_abr_id,p_copy_entity_txn_id),
3128 P_MODULE_CD => 'PQH_GSP_STGBEN',
3129 p_context => 'ABR',
3130 P_MESSAGE_TYPE_CD => 'E',
3131 P_MESSAGE_TEXT => l_message_text,
3132 p_effective_date => p_effective_date);
3133 end if;
3134 exception when others then
3135 hr_utility.set_location('issues in writing abr, skipping'||l_proc,100);
3136 raise;
3137 end;
3138 end loop;
3139 hr_utility.set_location('leaving '||l_proc,100);
3140 exception
3141 when others then
3142 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3143 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3144 P_TXN_ID => p_copy_entity_txn_id,
3145 P_MODULE_CD => 'PQH_GSP_STGBEN',
3146 p_context => 'ABR',
3147 P_MESSAGE_TYPE_CD => 'E',
3148 P_MESSAGE_TEXT => 'ABR',
3149 p_effective_date => p_effective_date);
3150 raise;
3151 end stage_to_abr;
3152 procedure stage_to_epa(p_copy_entity_txn_id in number,
3153 p_business_group_id in number,
3154 p_effective_date in date,
3155 p_datetrack_mode in varchar2) is
3156 cursor c_epa is
3157 select *
3158 from ben_copy_entity_results
3159 where copy_entity_txn_id = p_copy_entity_txn_id
3160 and table_alias = 'EPA'
3161 and dml_operation = 'INSERT' ; -- only insert should be there
3162 --
3163 r_epa c_epa%rowtype;
3164 l_proc varchar2(61) :='stage_to_epa';
3165 l_epa_id number ;
3166 l_ovn number ;
3167 l_effective_start_date date ;
3168 l_effective_end_date date ;
3169 l_message_text varchar2(2000);
3170 l_effective_date date;
3171 l_tab varchar2(30);
3172 l_pk number;
3173 l_plip_id number;
3174 l_pl_id number;
3175 l_oipl_id number;
3176 l_pgm_id number;
3177 begin
3178 hr_utility.set_location('inside '||l_proc,10);
3179 for r_epa in c_epa loop
3180 l_epa_id := r_epa.information1;
3181 hr_utility.set_location('for epa_id:'||l_epa_id ||'dml '||r_epa.dml_operation,20);
3182 hr_utility.set_location('epa_cer_id:'||r_epa.copy_entity_result_id,20);
3183 if r_epa.gs_mirror_src_entity_result_id is not null then
3184 begin
3185 select information1,table_alias
3186 into l_pk, l_tab
3187 from ben_copy_entity_results
3188 where copy_entity_result_id = r_epa.gs_mirror_src_entity_result_id;
3189 exception
3190 when others then
3191 raise;
3192 end;
3193 hr_utility.set_location('parent tab is'||l_tab ||' pk is '||l_pk,5);
3194 if l_tab ='PGM' then
3195 l_pgm_id := l_pk;
3196 l_plip_id := null;
3197 l_oipl_id := null;
3198 l_pl_id := null;
3199 elsif l_tab = 'CPP' then
3200 l_plip_id := l_pk;
3201 l_pgm_id := null;
3202 l_oipl_id := null;
3203 l_pl_id := null;
3204 elsif l_tab = 'PLN' then
3205 l_pl_id := l_pk;
3206 l_plip_id := null;
3207 l_oipl_id := null;
3208 l_pgm_id := null;
3209 elsif l_tab = 'COP' then
3210 -- we may not have oipl id in information1 when step api creates the oipl
3211 l_oipl_id := l_pk;
3212 l_plip_id := null;
3213 l_pgm_id := null;
3214 l_pl_id := null;
3215 else
3216 l_oipl_id := null;
3217 l_plip_id := null;
3218 l_pgm_id := null;
3219 l_pl_id := null;
3220 l_pk := null;
3221 end if;
3222 end if;
3223 l_effective_date := r_epa.information2;
3224 begin
3225 if l_epa_id is null and l_pk is not null and r_epa.dml_operation = 'INSERT' then
3226 hr_utility.set_location(' BEN_PRTN_ELIG_F CREATE_PARTICIPATION_ELIG ',20);
3227 BEN_PARTICIPATION_ELIG_API.CREATE_PARTICIPATION_ELIG(
3228 P_EFFECTIVE_DATE => l_effective_date
3229 ,P_BUSINESS_GROUP_ID => p_business_group_id
3230 ,P_MX_POE_APLS_CD => r_EPA.INFORMATION17
3231 ,P_MX_POE_DET_DT_CD => r_EPA.INFORMATION13
3232 ,P_MX_POE_DET_DT_RL => r_EPA.INFORMATION269
3233 ,P_MX_POE_RL => r_EPA.INFORMATION267
3234 ,P_MX_POE_UOM => r_EPA.INFORMATION11
3235 ,P_MX_POE_VAL => r_EPA.INFORMATION266
3236 ,P_OIPL_ID => l_oipl_id
3237 ,P_PGM_ID => l_pgm_id
3238 ,P_PLIP_ID => l_plip_id
3239 ,P_PL_ID => l_pl_id
3240 ,P_PRTN_EFF_END_DT_CD => r_EPA.INFORMATION16
3241 ,P_PRTN_EFF_END_DT_RL => r_EPA.INFORMATION271
3242 ,P_PRTN_EFF_STRT_DT_CD => r_EPA.INFORMATION15
3243 ,P_PRTN_EFF_STRT_DT_RL => r_EPA.INFORMATION270
3244 ,P_PRTN_ELIG_ID => l_epa_id
3245 ,P_PTIP_ID => r_EPA.INFORMATION259
3246 ,P_WAIT_PERD_DT_TO_USE_CD => r_EPA.INFORMATION12
3247 ,P_WAIT_PERD_DT_TO_USE_RL => r_EPA.INFORMATION264
3248 ,P_WAIT_PERD_RL => r_EPA.INFORMATION268
3249 ,P_WAIT_PERD_UOM => r_EPA.INFORMATION14
3250 ,P_WAIT_PERD_VAL => r_EPA.INFORMATION287
3251 ,P_EFFECTIVE_START_DATE => l_effective_start_date
3252 ,P_EFFECTIVE_END_DATE => l_effective_end_date
3253 ,P_OBJECT_VERSION_NUMBER => l_ovn
3254 );
3255 update ben_copy_entity_results
3256 set information1 = l_epa_id
3257 where copy_entity_result_id = r_epa.copy_entity_result_id;
3258 else
3259 l_message_text := 'invalid oper'||r_epa.dml_operation
3260 ||' epa_id is'||l_epa_id
3261 ||' epa_ovn is'||l_ovn
3262 ||' l_tab is'||l_tab
3263 ||' l_pk is'||l_pk;
3264 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3265 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3266 P_TXN_ID => nvl(l_epa_id,p_copy_entity_txn_id),
3267 P_MODULE_CD => 'PQH_GSP_STGBEN',
3268 p_context => 'EPA',
3269 P_MESSAGE_TYPE_CD => 'E',
3270 P_MESSAGE_TEXT => l_message_text,
3271 p_effective_date => p_effective_date);
3272 end if;
3273 exception when others then
3274 hr_utility.set_location('issues in writing epa, skipping'||l_proc,100);
3275 raise;
3276 end;
3277 end loop;
3278 hr_utility.set_location('leaving '||l_proc,100);
3279 exception
3280 when others then
3281 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3282 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3283 P_TXN_ID => p_copy_entity_txn_id,
3284 P_MODULE_CD => 'PQH_GSP_STGBEN',
3285 p_context => 'EPA',
3286 P_MESSAGE_TYPE_CD => 'E',
3287 P_MESSAGE_TEXT => 'EPA',
3288 p_effective_date => p_effective_date);
3289 raise;
3290 end stage_to_epa;
3291 procedure stage_to_vpf(p_copy_entity_txn_id in number,
3292 p_business_group_id in number,
3293 p_effective_date in date,
3294 p_datetrack_mode in varchar2) is
3295 cursor c_crr is
3296 select *
3297 from ben_copy_entity_results
3298 where copy_entity_txn_id = p_copy_entity_txn_id
3299 and table_alias = 'CRRATE'
3300 and dml_operation in ('INSERT','UPDATE') -- only insert/ updates should be there
3301 order by information230,information169,information160,information2;
3302 --
3303 l_proc varchar2(61) :='stage_to_crr';
3304 l_crr_id number ;
3305 l_abr_id number ;
3306 l_avr_id number ;
3307 l_message_text varchar2(2000);
3308 l_object varchar2(80);
3309 l_vep_id number ;
3310 l_crr_ovn number ;
3311 l_ovn number ;
3312 l_db_ovn number;
3313 l_avr_num number ;
3314 l_esd date ;
3315 l_eed date ;
3316 l_effective_date date;
3317 dummy char(1);
3318 l_elp_id number;
3319 l_old_grd_cer_id number;
3320 l_old_pnt_cer_id number;
3321 l_old_abr_id number;
3322 l_old_crr_id number;
3323 l_old_crset_id number;
3324 l_old_crr_ovn number;
3325 l_old_crr_name varchar2(240);
3326 l_crr_name varchar2(240);
3327 l_dml_operation varchar2(30);
3328 l_dt_mode varchar2(30);
3329 begin
3330 hr_utility.set_location('inside '||l_proc,10);
3331 for crr_rec in c_crr loop
3332 l_effective_date := crr_rec.information2;
3333 if crr_rec.information278 is null then
3334 hr_utility.set_location('new variable rate is being created'||l_proc,10);
3335 if (crr_rec.information230 is null or crr_rec.information230 = l_old_grd_cer_id)
3336 and (crr_rec.information169 is null or crr_rec.information169 = l_old_pnt_cer_id)
3337 and crr_rec.information160 = l_old_crset_id
3338 and l_old_crr_id is not null then
3339 hr_utility.set_location('reusing prev row pk and ovn',16);
3340 l_crr_id := l_old_crr_id; -- previous row created id can be used
3341 l_crr_ovn := l_old_crr_ovn;
3342 l_abr_id := l_old_abr_id;
3343 l_crr_name := l_old_crr_name;
3344 else
3345 hr_utility.set_location('nothing to reuse'||l_proc,10);
3346 l_crr_id := crr_rec.information278;
3347 l_crr_ovn := crr_rec.information298;
3348 l_abr_id := '';
3349 l_avr_num := '';
3350 l_crr_name := build_vpf_name(p_crset_id => crr_rec.information160,
3351 p_point_cer_id => crr_rec.information169,
3352 p_grade_cer_id => crr_rec.information230,
3353 p_copy_entity_txn_id => p_copy_entity_txn_id);
3354 end if;
3355 else
3356 hr_utility.set_location('existing vpf is being updated'||l_proc,10);
3357 l_crr_id := crr_rec.information278;
3358 l_crr_ovn := crr_rec.information298;
3359 l_abr_id := '';
3360 l_avr_num := '';
3361 l_crr_name := crr_rec.information170;
3362 end if;
3363 if crr_rec.dml_operation = 'INSERT'
3364 and nvl(crr_rec.datetrack_mode,'CORRECTION') <> 'UPDATE_REPLACE' then
3365 l_dml_operation := 'INSERT';
3366 elsif crr_rec.dml_operation = 'INSERT' and crr_rec.datetrack_mode = 'UPDATE_REPLACE' then
3367 l_dml_operation := 'UPDATE';
3368 elsif crr_rec.dml_operation = 'UPDATE' then
3369 l_dml_operation := 'UPDATE';
3370 end if;
3371 if l_abr_id is null and crr_rec.information161 is not null then
3372 hr_utility.set_location('abr_id is null ,getting it'||l_proc,10);
3373 begin
3374 select information1
3375 into l_abr_id
3376 from ben_copy_entity_results
3377 where copy_entity_txn_id = p_copy_entity_txn_id
3378 and copy_entity_result_id = crr_rec.information161;
3379 exception
3380 when no_data_found then
3381 hr_utility.set_location('abr id not found'||l_proc,100);
3382 when others then
3383 hr_utility.set_location('issues in getting abr'||l_proc,100);
3384 raise;
3385 end;
3386 else
3387 hr_utility.set_location('abr_id is '||l_abr_id,10);
3388 end if;
3389 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
3390 begin
3391 hr_utility.set_location(' BEN_VRBL_RT_PRFL_F CREATE_VRBL_RATE_PROFILE ',20);
3392 BEN_VRBL_RATE_PROFILE_API.CREATE_VRBL_RATE_PROFILE(
3393 P_EFFECTIVE_DATE => l_effective_date
3394 ,P_BUSINESS_GROUP_ID => p_business_group_id
3395 ,P_ACTY_TYP_CD => 'GSPSA'
3396 ,P_NAME => l_crr_name
3397 ,P_VAL => nvl(crr_rec.INFORMATION293,0)
3398 ,P_VRBL_RT_PRFL_ID => l_crr_id
3399 ,P_VRBL_RT_PRFL_STAT_CD => 'A'
3400 ,P_ACTY_REF_PERD_CD => 'MO'
3401 ,P_VRBL_RT_TRTMT_CD => 'RPLC'
3402 ,P_VRBL_USG_CD => 'RT'
3403 ,P_RT_ELIG_PRFL_FLAG => 'N'
3404 ,P_RT_AGE_FLAG => 'N'
3405 ,P_RT_ASNT_SET_FLAG => 'N'
3406 ,P_RT_BENFTS_GRP_FLAG => 'N'
3407 ,P_RT_BRGNG_UNIT_FLAG => 'N'
3408 ,P_RT_CBR_QUALD_BNF_FLAG => 'N'
3409 ,P_RT_CMBN_AGE_LOS_FLAG => 'N'
3410 ,P_RT_CNTNG_PRTN_PRFL_FLAG => 'N'
3411 ,P_RT_COMPTNCY_FLAG => 'N'
3412 ,P_RT_COMP_LVL_FLAG => 'N'
3413 ,P_RT_DPNT_CVRD_PGM_FLAG => 'N'
3414 ,P_RT_DPNT_CVRD_PLIP_FLAG => 'N'
3415 ,P_RT_DPNT_CVRD_PL_FLAG => 'N'
3416 ,P_RT_DPNT_CVRD_PTIP_FLAG => 'N'
3417 ,P_RT_DPNT_OTHR_PTIP_FLAG => 'N'
3418 ,P_RT_DSBLD_FLAG => 'N'
3419 ,P_RT_EE_STAT_FLAG => 'N'
3420 ,P_RT_ENRLD_OIPL_FLAG => 'N'
3421 ,P_RT_ENRLD_PGM_FLAG => 'N'
3422 ,P_RT_ENRLD_PLIP_FLAG => 'N'
3423 ,P_RT_ENRLD_PL_FLAG => 'N'
3424 ,P_RT_ENRLD_PTIP_FLAG => 'N'
3425 ,P_RT_FL_TM_PT_TM_FLAG => 'N'
3426 ,P_RT_GNDR_FLAG => 'N'
3427 ,P_RT_GRD_FLAG => 'N'
3428 ,P_RT_HLTH_CVG_FLAG => 'N'
3429 ,P_RT_HRLY_SLRD_FLAG => 'N'
3430 ,P_RT_HRS_WKD_FLAG => 'N'
3431 ,P_RT_JOB_FLAG => 'N'
3432 ,P_RT_LBR_MMBR_FLAG => 'N'
3433 ,P_RT_LGL_ENTY_FLAG => 'N'
3434 ,P_RT_LOA_RSN_FLAG => 'N'
3435 ,P_RT_LOS_FLAG => 'N'
3436 ,P_RT_LVG_RSN_FLAG => 'N'
3437 ,P_RT_NO_OTHR_CVG_FLAG => 'N'
3438 ,P_RT_OPTD_MDCR_FLAG => 'N'
3439 ,P_RT_ORG_UNIT_FLAG => 'N'
3440 ,P_RT_OTHR_PTIP_FLAG => 'N'
3441 ,P_RT_PCT_FL_TM_FLAG => 'N'
3442 ,P_RT_PERF_RTNG_FLAG => 'N'
3443 ,P_RT_PER_TYP_FLAG => 'N'
3444 ,P_RT_POE_FLAG => 'N'
3445 ,P_RT_PPL_GRP_FLAG => 'N'
3446 ,P_RT_PRFL_RL_FLAG => 'N'
3447 ,P_RT_PRTT_ANTHR_PL_FLAG => 'N'
3448 ,P_RT_PRTT_PL_FLAG => 'N'
3449 ,P_RT_PSTL_CD_FLAG => 'N'
3450 ,P_RT_PSTN_FLAG => 'N'
3451 ,P_RT_PYRL_FLAG => 'N'
3452 ,P_RT_PY_BSS_FLAG => 'N'
3453 ,P_RT_QUAL_TITL_FLAG => 'N'
3454 ,P_RT_QUA_IN_GR_FLAG => 'N'
3455 ,P_RT_SCHEDD_HRS_FLAG => 'N'
3456 ,P_RT_SVC_AREA_FLAG => 'N'
3457 ,P_RT_TBCO_USE_FLAG => 'N'
3458 ,P_RT_TTL_CVG_VOL_FLAG => 'N'
3459 ,P_RT_TTL_PRTT_FLAG => 'N'
3460 ,P_RT_WK_LOC_FLAG => 'N'
3461 ,P_ASMT_TO_USE_CD => 'ANY'
3462 ,P_TX_TYP_CD => 'PRETAX'
3463 ,P_MLT_CD => 'FLFX'
3464 ,P_EFFECTIVE_START_DATE => l_esd
3465 ,P_EFFECTIVE_END_DATE => l_eed
3466 ,P_OBJECT_VERSION_NUMBER => l_crr_ovn
3467 );
3468 hr_utility.set_location('after vpf insert ',222);
3469 begin
3470 if l_avr_num is null then
3471 hr_utility.set_location('1st crr for abr',222);
3472 begin
3473 select null
3474 into dummy
3475 from ben_acty_base_rt_f
3476 where acty_base_rt_id = l_abr_id
3477 and USES_VARBL_RT_FLAG = 'Y'
3478 and l_effective_date between effective_start_date
3479 and effective_end_date;
3480 exception
3481 when no_data_found then
3482 hr_utility.set_location('abr has vrbl flag No',222);
3483 begin
3484 update ben_acty_base_rt_f
3485 set USES_VARBL_RT_FLAG = 'Y'
3486 where acty_base_rt_id = l_abr_id;
3487 hr_utility.set_location('updated to Yes',223);
3488 exception
3489 when others then
3490 hr_utility.set_location('issues in updating abr flag to Y',225);
3491 raise;
3492 end;
3493 when others then
3494 hr_utility.set_location('issues in gettting abr row ',226);
3495 raise;
3496 end;
3497 select nvl(max(ordr_num),0) + 1
3498 into l_avr_num
3499 from ben_acty_vrbl_rt_f
3500 where acty_base_rt_id = l_abr_id;
3501 else
3502 l_avr_num := l_avr_num + 1;
3503 end if;
3504 hr_utility.set_location(' BEN_ACTY_VRBL_RT_F CREATE_ACTY_VRBL_RATE ',20);
3505 BEN_ACTY_VRBL_RATE_API.CREATE_ACTY_VRBL_RATE(
3506 P_EFFECTIVE_DATE => l_effective_date
3507 ,P_BUSINESS_GROUP_ID => p_business_group_id
3508 ,P_ACTY_BASE_RT_ID => l_abr_id
3509 ,P_ACTY_VRBL_RT_ID => l_avr_id
3510 ,P_ORDR_NUM => l_avr_num
3511 ,P_VRBL_RT_PRFL_ID => l_crr_id
3512 ,P_EFFECTIVE_START_DATE => l_esd
3513 ,P_EFFECTIVE_END_DATE => l_eed
3514 ,P_OBJECT_VERSION_NUMBER => l_ovn
3515 );
3516 exception when others then
3517 hr_utility.set_location('issues in writing avr'||l_proc,100);
3518 raise;
3519 end;
3520 if crr_rec.information279 is null and crr_rec.information160 is not null then
3521 -- elp id is null for crrate while crset id is there
3522 begin
3523 select information277
3524 into l_elp_id
3525 from ben_copy_entity_results
3526 where copy_entity_txn_id = p_copy_entity_txn_id
3527 and table_alias = 'CRSET'
3528 and information161 = crr_rec.information160;
3529 exception
3530 when others then
3531 l_elp_id := '';
3532 end;
3533 else
3534 l_elp_id := crr_rec.information279;
3535 end if;
3536 if l_elp_id is null or l_crr_id is null then
3537 l_message_text := 'fks not there for creating vep row'
3538 ||' elp_id is '||l_elp_id
3539 ||' effdt is '||to_char(p_effective_date,'DD/MM/RRRR')
3540 ||' vpf id is '||l_crr_id;
3541 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3542 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3543 P_TXN_ID => nvl(l_crr_id,p_copy_entity_txn_id),
3544 P_MODULE_CD => 'PQH_GSP_STGBEN',
3545 p_context => 'VPF',
3546 P_MESSAGE_TYPE_CD => 'E',
3547 P_MESSAGE_TEXT => l_message_text,
3548 p_effective_date => p_effective_date);
3549 else
3550 begin
3551 hr_utility.set_location(' BEN_VRBL_RT_ELIG_PRFL_F CREATE_VRBL_RT_ELIG_PRFL ',20);
3552 BEN_VRBL_RT_ELIG_PRFL_API.CREATE_VRBL_RT_ELIG_PRFL(
3553 P_EFFECTIVE_DATE => p_effective_date -- vep will be created as of Grade ladder effdt
3554 ,P_BUSINESS_GROUP_ID => p_business_group_id
3555 ,P_ELIGY_PRFL_ID => l_elp_id
3556 ,P_MNDTRY_FLAG => 'Y'
3557 ,P_VRBL_RT_ELIG_PRFL_ID => l_vep_id
3558 ,P_VRBL_RT_PRFL_ID => l_crr_id
3559 ,P_EFFECTIVE_START_DATE => l_esd
3560 ,P_EFFECTIVE_END_DATE => l_eed
3561 ,P_OBJECT_VERSION_NUMBER => l_ovn
3562 );
3563 exception when others then
3564 l_message_text := 'issues in writing vep '
3565 ||' elp_id is '||l_elp_id
3566 ||' GL effdt is '||to_char(p_effective_date,'DD/MM/RRRR')
3567 ||' VR effdt is '||to_char(l_effective_date,'DD/MM/RRRR')
3568 ||' vpf id is '||l_crr_id;
3569 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3570 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3571 P_TXN_ID => nvl(l_crr_id,p_copy_entity_txn_id),
3572 P_MODULE_CD => 'PQH_GSP_STGBEN',
3573 p_context => 'VPF',
3574 P_MESSAGE_TYPE_CD => 'E',
3575 P_MESSAGE_TEXT => l_message_text,
3576 p_effective_date => p_effective_date);
3577 raise;
3578 end;
3579 end if;
3580 exception when others then
3581 hr_utility.set_location('issues in writing var'||l_proc,100);
3582 raise;
3583 end;
3584 elsif l_dml_operation = 'UPDATE'
3585 and l_abr_id is not null
3586 and l_crr_id is not null
3587 and l_crr_ovn is not null then
3588 hr_utility.set_location(' BEN_VRBL_RT_PRFL_F UPDATE_VRBL_RATE_PROFILE ',30);
3589 hr_utility.set_location(' dt mode is '||p_datetrack_mode,30);
3590 --if p_datetrack_mode <> 'CORRECTION' then /* Commented out to fix Bug:3964291 */
3591 l_dt_mode := get_update_mode(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3592 p_key_column_name => 'VRBL_RT_PRFL_ID',
3593 p_key_column_value => l_crr_id,
3594 p_effective_date => l_effective_date);
3595 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
3596 /*else
3597 l_dt_mode := p_datetrack_mode;
3598 end if;*/
3599 l_db_ovn := get_ovn(p_table_name => 'BEN_VRBL_RT_PRFL_F',
3600 p_key_column_name => 'VRBL_RT_PRFL_ID',
3601 p_key_column_value => l_crr_id,
3602 p_effective_date => l_effective_date);
3603 hr_utility.set_location(' ovn is '||l_db_ovn,30);
3604 if l_db_ovn <> l_crr_ovn then
3605 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','VPF');
3606 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
3607 fnd_message.set_token('OBJECT ',l_object);
3608 fnd_message.set_token('OBJECT_NAME ','VPF : '||l_crr_id);
3609 fnd_message.raise_error;
3610 else
3611 begin
3612 BEN_VRBL_RATE_PROFILE_API.UPDATE_VRBL_RATE_PROFILE(
3613 P_EFFECTIVE_DATE => l_effective_date
3614 ,P_VAL => crr_rec.INFORMATION293
3615 ,P_VRBL_RT_PRFL_ID => l_crr_id
3616 ,P_EFFECTIVE_START_DATE => l_esd
3617 ,P_EFFECTIVE_END_DATE => l_eed
3618 ,P_OBJECT_VERSION_NUMBER => l_crr_ovn
3619 ,P_DATETRACK_MODE => l_dt_mode
3620 );
3621 exception when others then
3622 hr_utility.set_location('issues in updating var'||l_proc,100);
3623 raise;
3624 end;
3625 end if;
3626 else
3627 l_message_text := 'invalid operation '||l_dml_operation
3628 ||' abr_id is '||l_abr_id
3629 ||' crr_ovn is '||l_crr_ovn
3630 ||' crr_name is '||l_crr_name
3631 ||' val is '||crr_rec.INFORMATION293
3632 ||' crr_id is '||l_crr_id;
3633 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3634 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3635 P_TXN_ID => nvl(l_crr_id,p_copy_entity_txn_id),
3636 P_MODULE_CD => 'PQH_GSP_STGBEN',
3637 p_context => 'VPF',
3638 P_MESSAGE_TYPE_CD => 'E',
3639 P_MESSAGE_TEXT => l_message_text,
3640 p_effective_date => p_effective_date);
3641 end if;
3642 l_old_crr_ovn := l_crr_ovn;
3643 l_old_abr_id := l_abr_id;
3644 l_old_crr_id := l_crr_id;
3645 l_old_crr_name := l_crr_name;
3646 l_old_grd_cer_id := crr_rec.information230;
3647 l_old_pnt_cer_id := crr_rec.information169;
3648 l_old_crset_id := crr_rec.information160;
3649 end loop;
3650 hr_utility.set_location('leaving '||l_proc,100);
3651 exception
3652 when others then
3653 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3654 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
3655 P_TXN_ID => p_copy_entity_txn_id,
3656 P_MODULE_CD => 'PQH_GSP_STGBEN',
3657 p_context => 'VPF',
3658 P_MESSAGE_TYPE_CD => 'E',
3659 P_MESSAGE_TEXT => 'VPF',
3660 p_effective_date => p_effective_date);
3661 raise;
3662 end stage_to_vpf;
3663 FUNCTION get_pgm_name (p_pgm_id IN NUMBER, p_effective_date IN DATE)
3664 RETURN VARCHAR2
3665 IS
3666 l_pgm_name ben_pgm_f.NAME%TYPE;
3667 BEGIN
3668 SELECT NAME
3669 INTO l_pgm_name
3670 FROM ben_pgm_f
3671 WHERE pgm_id = p_pgm_id
3672 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
3673
3674 RETURN l_pgm_name;
3675 END get_pgm_name;
3676
3677 PROCEDURE create_le_for_pgm (
3678 p_pgm_id IN NUMBER,
3679 p_business_group_id IN NUMBER,
3680 p_ler_id IN NUMBER,
3681 p_effective_date IN DATE
3682 )
3683 IS
3684 l_pet_id NUMBER;
3685 l_lee_rsn_id NUMBER;
3686 l_continue BOOLEAN DEFAULT FALSE;
3687 l_esd DATE;
3688 l_eed DATE;
3689 l_ovn NUMBER;
3690
3691 CURSOR csr_pgm_enrl (p_pgm_id IN NUMBER)
3692 IS
3693 SELECT popl_enrt_typ_cycl_id
3694 FROM ben_popl_enrt_typ_cycl_f
3695 WHERE pgm_id = p_pgm_id;
3696
3697 CURSOR csr_lee_rsns (p_popl_enrt_typ_cycl_id IN NUMBER)
3698 IS
3699 SELECT NULL
3700 FROM ben_lee_rsn_f
3701 WHERE ler_id = p_ler_id
3702 AND business_group_id = p_business_group_id
3703 AND popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id;
3704 BEGIN
3705 OPEN csr_pgm_enrl (p_pgm_id);
3706
3707 FETCH csr_pgm_enrl
3708 INTO l_pet_id;
3709
3710 CLOSE csr_pgm_enrl;
3711
3712 OPEN csr_lee_rsns (l_pet_id);
3713
3714 FETCH csr_lee_rsns
3715 INTO l_lee_rsn_id;
3716
3717 IF csr_lee_rsns%NOTFOUND
3718 THEN
3719 l_continue := TRUE;
3720 END IF;
3721
3722 CLOSE csr_lee_rsns;
3723
3724 IF l_continue
3725 THEN
3726 ben_life_event_enroll_rsn_api.create_life_event_enroll_rsn
3727 (p_effective_date => p_effective_date,
3728 p_business_group_id => p_business_group_id,
3729 p_lee_rsn_id => l_lee_rsn_id,
3730 p_ler_id => p_ler_id,
3731 p_popl_enrt_typ_cycl_id => l_pet_id,
3732 p_effective_start_date => l_esd,
3733 p_effective_end_date => l_eed,
3734 p_object_version_number => l_ovn,
3735 p_cls_enrt_dt_to_use_cd => 'ELCNSMADE',
3736 p_enrt_cvg_end_dt_cd => 'ODBED',
3737 p_enrt_cvg_strt_dt_cd => 'AED',
3738 p_enrt_perd_end_dt_cd => 'ALDCPPY',
3739 p_enrt_perd_strt_dt_cd => 'AED',
3740 p_rt_end_dt_cd => 'ODBED',
3741 p_rt_strt_dt_cd => 'AED'
3742 );
3743 fnd_file.put_line (which => fnd_file.LOG,
3744 buff => 'Program Name : '
3745 || get_pgm_name (p_pgm_id,
3746 p_effective_date
3747 )
3748 );
3749 fnd_file.put_line (which => fnd_file.LOG,
3750 buff => 'Program Id : ' || p_pgm_id
3751 );
3752 END IF;
3753 EXCEPTION
3754 WHEN OTHERS
3755 THEN
3756 fnd_file.put_line
3757 (which => fnd_file.LOG,
3758 buff => 'Error while creating Program Enrollement Reasons..exiting'
3759 );
3760 ROLLBACK;
3761 END create_le_for_pgm;
3762
3763 PROCEDURE create_pgm_le (
3764 errbuf OUT NOCOPY VARCHAR2,
3765 retcode OUT NOCOPY NUMBER,
3766 p_effective_date IN VARCHAR2,
3767 p_business_group_id IN VARCHAR2,
3768 p_pgm_id IN NUMBER DEFAULT NULL
3769 )
3770 IS
3771 CURSOR csr_ler_id
3772 IS
3773 SELECT ler_id
3774 FROM ben_ler_f
3775 WHERE typ_cd = 'GSP'
3776 AND lf_evt_oper_cd = 'SYNC'
3777 AND business_group_id = p_business_group_id
3778 AND effective_start_date = pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id, null);
3779
3780 CURSOR csr_pgm_details (eff_date IN DATE)
3781 IS
3782 SELECT pgm_id
3783 FROM ben_pgm_f
3784 WHERE business_group_id = p_business_group_id
3785 AND pgm_typ_cd = 'GSP'
3786 AND eff_date BETWEEN effective_start_date AND effective_end_date;
3787
3788 Cursor csr_pgm_start_date(p_program_id in number)
3789 is
3790 select min(effective_start_date)
3791 from ben_pgm_f
3792 where pgm_id = p_program_id;
3793
3794 l_ler_id NUMBER;
3795 l_effective_date DATE;
3796 l_pgm_effective_start_date DATE;
3797 BEGIN
3798 fnd_file.put_line (which => fnd_file.LOG,buff => 'Entering create_pgm_le');
3799 l_effective_date := TO_DATE (p_effective_date, 'YYYY/MM/DD HH24:MI:SS');
3800 l_effective_date :=TO_DATE (TO_CHAR (TRUNC (l_effective_date), 'DD/MM/RRRR'),'DD/MM/RRRR');
3801
3802 OPEN csr_ler_id;
3803 FETCH csr_ler_id
3804 INTO l_ler_id;
3805
3806 IF csr_ler_id%NOTFOUND
3807 THEN
3808 errbuf :='No Life Event of Type Grade/Step Progression with Operator Code Synchronization found. Exiting';
3809 retcode := -20;
3810 RETURN;
3811 ELSE
3812 fnd_file.put_line (which => fnd_file.LOG,buff => 'Life Event Exists');
3813 END IF;
3814
3815 CLOSE csr_ler_id;
3816 fnd_file.put_line
3817 (which => fnd_file.LOG,
3818 buff => 'Created Program Enrolment Reason for the following Programs '
3819 );
3820 fnd_file.put_line
3821 (which => fnd_file.LOG,
3822 buff => '==========================================================='
3823 );
3824 IF p_pgm_id IS NULL
3825 THEN
3826 FOR i IN csr_pgm_details (l_effective_date)
3827 LOOP
3828 OPEN csr_pgm_start_date(i.pgm_id);
3829 FETCH csr_pgm_start_date into l_pgm_effective_start_date;
3830 CLOSE csr_pgm_start_date;
3831 create_le_for_pgm (p_pgm_id => i.pgm_id,
3832 p_business_group_id => p_business_group_id,
3833 p_ler_id => l_ler_id,
3834 p_effective_date => l_pgm_effective_start_date
3835 );
3836 END LOOP;
3837 ELSE
3838 OPEN csr_pgm_start_date(p_pgm_id);
3839 FETCH csr_pgm_start_date into l_pgm_effective_start_date;
3840 CLOSE csr_pgm_start_date;
3841 create_le_for_pgm (p_pgm_id => p_pgm_id,
3842 p_business_group_id => p_business_group_id,
3843 p_ler_id => l_ler_id,
3844 p_effective_date => l_pgm_effective_start_date
3845 );
3846 END IF;
3847 COMMIT;
3848 fnd_file.put_line (which => fnd_file.LOG,
3849 buff => 'Leaving create_pgm_le'
3850 );
3851 END create_pgm_le;
3852
3853 procedure upd_stg_elig_prfl_id(
3854 p_copy_entity_txn_id number
3855 ,p_business_group_id number
3856 ,p_effective_date date
3857 )
3858 is
3859 begin
3860 hr_utility.set_location('Entering pqh_gsp_stage_to_ben.upd_stg_elig_prfl_id',99);
3861
3862 update ben_copy_entity_results cer
3863 set information263 =
3864 ( select ELIGY_PRFL_ID
3865 from BEN_ELIGY_PRFL_F elp
3866 where elp.BUSINESS_GROUP_ID = p_business_group_id
3867 and elp.name = cer.information5
3868 and p_effective_date between
3869 elp.effective_start_date and elp.effective_end_date)
3870 where cer.copy_entity_txn_id = p_copy_entity_txn_id
3871 and cer.information4 = p_business_group_id
3872 and cer.table_alias = 'CEP'
3873 and p_effective_date between
3874 cer.information2 and nvl(cer.information3,to_date('4712/12/31','YYYY/MM/DD'))
3875 and exists ( select ELIGY_PRFL_ID
3876 from BEN_ELIGY_PRFL_F elp
3877 where elp.BUSINESS_GROUP_ID = p_business_group_id
3878 and elp.name = cer.information5
3879 and p_effective_date between
3880 elp.effective_start_date and elp.effective_end_date);
3881
3882 hr_utility.set_location('No of staging rows updated :'||sql%rowcount||':',99);
3883 hr_utility.set_location('Leaving pqh_gsp_stage_to_ben.upd_stg_elig_prfl_id',99);
3884 end upd_stg_elig_prfl_id ;
3885
3886 procedure cre_update_elig_prfl(
3887 p_copy_entity_txn_id in number
3888 ,p_effective_date in date
3889 ,p_business_group_id in number
3890 ,p_business_area in varchar2 default 'PQH_GSP_TASK_LIST')
3891 is
3892 l_delete_failed varchar2(10);
3893 begin
3894 hr_utility.set_location('Entering pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
3895 hr_utility.set_location('Copy_Entity_Txn_Id passed is:'||p_copy_entity_txn_id,800);
3896 hr_utility.set_location('Effective Date Passed is:'||p_effective_date,801);
3897 hr_utility.set_location('Business Group Id passed is:'||p_business_group_id,902);
3898 hr_utility.set_location('Business Area Passed is:'||p_business_area,903);
3899
3900 hr_utility.set_location('Calling purge:'||p_business_area,903);
3901
3902 pqh_gsp_prgrules.purge_duplicate_elp_tree(p_copy_entity_txn_id => p_copy_entity_txn_id);
3903
3904 hr_utility.set_location('Done with purge:'||p_business_area,903);
3905
3906 -- update the dml operation of those records which have already been
3907 -- taken care by gsp pre push code
3908
3909 UPDATE ben_copy_entity_results cer
3910 set dml_operation = 'GSPDEL'
3911 where cer.copy_entity_txn_id = p_copy_entity_txn_id
3912 and cer.dml_operation = 'DELETE'
3913 and table_alias in ('CPP','CEP','EPA','COP','OPT','ABR');
3914
3915 -- Set for same Business Group
3916 BEN_PD_COPY_TO_BEN_ONE.g_mapping_done := false ;
3917
3918 -- Copied the following 4 calls from ben_plan_design_copy_process.process
3919 -- Populate table_route_id in staging table
3920 ben_plan_design_wizard_api.write_route_and_hierarchy(p_copy_entity_txn_id);
3921 ben_plan_design_wizard_api.update_result_rows(p_copy_entity_txn_id => p_copy_entity_txn_id);
3922 ben_plan_design_delete_api.call_delete_apis(
3923 p_copy_entity_txn_id => p_copy_entity_txn_id
3924 ,p_delete_failed => l_delete_failed
3925 );
3926
3927 UPDATE ben_copy_entity_results cer
3928 set number_of_copies = 0
3929 where cer.copy_entity_txn_id = p_copy_entity_txn_id
3930 and p_effective_date between nvl(information2,p_effective_date)
3931 and nvl(information3,p_effective_date)
3932 and cer.dml_operation = 'DELETE';
3933
3934 -- Initialise
3935 ben_pd_copy_to_ben_one.init_table_data_in_cer(p_copy_entity_txn_id);
3936
3937 BEN_PD_COPY_TO_BEN_ONE.g_pk_tbl.delete;
3938 BEN_PD_COPY_TO_BEN_ONE.g_pk_tbl(0) := null ;
3939 BEN_PD_COPY_TO_BEN_ONE.g_count := 1 ;
3940
3941 -- Create all derived factors first
3942
3943 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('CLF') then
3944 hr_utility.set_location(' Calling create CLF rows ',999);
3945 BEN_PD_COPY_TO_BEN_ONE.create_CLF_rows(
3946 p_copy_entity_txn_id => p_copy_entity_txn_id
3947 ,p_effective_date => p_effective_date
3948 ,p_reuse_object_flag => 'Y'
3949 ,p_target_business_group_id => p_business_group_id
3950 );
3951 end if;
3952 --
3953 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('HWF') then
3954 hr_utility.set_location(' Calling create HWF rows ',999);
3955 BEN_PD_COPY_TO_BEN_ONE.create_HWF_rows(
3956 p_copy_entity_txn_id => p_copy_entity_txn_id
3957 ,p_effective_date => p_effective_date
3958 ,p_reuse_object_flag => 'Y'
3959 ,p_target_business_group_id => p_business_group_id
3960 );
3961 end if;
3962 --
3963 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('AGF') then
3964 hr_utility.set_location(' Calling create AGF rows ',999);
3965 BEN_PD_COPY_TO_BEN_ONE.create_AGF_rows(
3966 p_copy_entity_txn_id => p_copy_entity_txn_id
3967 ,p_effective_date => p_effective_date
3968 ,p_reuse_object_flag => 'Y'
3969 ,p_target_business_group_id => p_business_group_id
3970 );
3971 end if;
3972 --
3973 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('LSF') then
3974 hr_utility.set_location(' Calling create LSF rows ',999);
3975 BEN_PD_COPY_TO_BEN_ONE.create_LSF_rows(
3976 p_copy_entity_txn_id => p_copy_entity_txn_id
3977 ,p_effective_date => p_effective_date
3978 ,p_reuse_object_flag => 'Y'
3979 ,p_target_business_group_id => p_business_group_id
3980 );
3981 end if;
3982 --
3983 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('PFF') then
3984 hr_utility.set_location(' Calling create PFF rows ',999);
3985 BEN_PD_COPY_TO_BEN_ONE.create_PFF_rows(
3986 p_copy_entity_txn_id => p_copy_entity_txn_id
3987 ,p_effective_date => p_effective_date
3988 ,p_reuse_object_flag => 'Y'
3989 ,p_target_business_group_id => p_business_group_id
3990 );
3991 end if;
3992 --
3993 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('CLA') then
3994 hr_utility.set_location(' Calling create CLA rows ',999);
3995 BEN_PD_COPY_TO_BEN_ONE.create_CLA_rows(
3996 p_copy_entity_txn_id => p_copy_entity_txn_id
3997 ,p_effective_date => p_effective_date
3998 ,p_reuse_object_flag => 'Y'
3999 ,p_target_business_group_id => p_business_group_id
4000 );
4001 end if;
4002
4003 --
4004 -- Create ELP Row
4005 --
4006
4007 if BEN_PD_COPY_TO_BEN_ONE.data_exists_for_table('ELP') then
4008 hr_utility.set_location(' Calling create ELP rows ',999);
4009 BEN_PD_COPY_TO_BEN_ONE.create_ELP_rows(
4010 p_copy_entity_txn_id => p_copy_entity_txn_id
4011 ,p_effective_date => p_effective_date
4012 ,p_reuse_object_flag => 'Y'
4013 ,p_target_business_group_id => p_business_group_id
4014 );
4015 end if;
4016
4017 --
4018 -- Create elig prf ben rows
4019 --
4020
4021 hr_utility.set_location(' Calling create all elig prf ben rows',999);
4022 BEN_PD_COPY_TO_BEN_FOUR.create_all_elig_prf_ben_rows(
4023 p_copy_entity_txn_id => p_copy_entity_txn_id
4024 ,p_effective_date => p_effective_date
4025 ,p_reuse_object_flag => 'Y'
4026 ,p_target_business_group_id => p_business_group_id
4027 );
4028
4029 --
4030 -- Update elig_prfl_id of staging records
4031 --
4032 hr_utility.set_location(' Update elig_prfl_id in staging area ',999);
4033 upd_stg_elig_prfl_id(
4034 p_copy_entity_txn_id => p_copy_entity_txn_id
4035 ,p_business_group_id => p_business_group_id
4036 ,p_effective_date => p_effective_date
4037 );
4038
4039 UPDATE ben_copy_entity_results cer
4040 set dml_operation = 'DELETE'
4041 where cer.copy_entity_txn_id = p_copy_entity_txn_id
4042 and cer.dml_operation = 'GSPDEL';
4043
4044 hr_utility.set_location('Leaving pqh_gsp_stage_to_ben.cre_update_elig_prfl ',999);
4045 end cre_update_elig_prfl ;
4046
4047
4048 end pqh_gsp_stage_to_ben;