[Home] [Help]
PACKAGE BODY: APPS.PQH_PA_WHATIF_PROCESS
Source
1 PACKAGE BODY pqh_pa_whatif_process AS
2 /* $Header: pqwifswi.pkb 120.5 2008/10/28 03:05:40 sagnanas noship $ */
3
4 TYPE hierarchy_tab IS TABLE OF pqh_pa_whatif_results%ROWTYPE INDEX BY BINARY_INTEGER;
5
6 g_hierarchy hierarchy_tab;
7
8 g_debug boolean := hr_utility.debug_enabled;
9 g_package varchar2(72) := 'pqh_pa_whatif_process';
10 g_role_id number :=-1;
11 g_effective_date DATE;
12 -----------------------------------------------------------------------------------------------
13 FUNCTION get_uom ( p_uom IN VARCHAR2
14 ,p_nnmntry_uom IN VARCHAR2
15 ,p_effective_date IN DATE)
16 RETURN VARCHAR2 IS
17 --
18 CURSOR csr_get_uom ( p_uom VARCHAR2, p_effective_date DATE ) IS
19 SELECT name
20 FROM fnd_currencies_vl
21 WHERE currency_code = p_uom
22 AND enabled_flag = 'Y'
23 AND p_effective_date BETWEEN nvl(start_date_active,p_effective_date) AND nvl(end_date_active,p_effective_date);
24
25 CURSOR csr_get_nnmntry_uom ( p_nnmntry_uom VARCHAR2, p_effective_date DATE ) IS
26 SELECT meaning
27 FROM hr_lookups
28 WHERE lookup_type= 'BEN_NNMNTRY_UOM'
29 AND lookup_code=p_nnmntry_uom
30 AND enabled_flag = 'Y'
31 AND p_effective_date BETWEEN nvl(start_date_active,p_effective_date) AND nvl(end_date_active,p_effective_date);
32 --
33 l_proc varchar2(72);
34 l_uom_name fnd_currencies_tl.name%TYPE;
35 --
36 BEGIN
37 --
38 g_debug := hr_utility.debug_enabled;
39
40 if g_debug then
41 l_proc := g_package || 'get_uom';
42 hr_utility.set_location('Entering: ' || l_proc,10);
43 end if;
44 IF p_nnmntry_uom is not null THEN
45 OPEN csr_get_nnmntry_uom(p_nnmntry_uom => p_nnmntry_uom, p_effective_date => p_effective_date);
46 FETCH csr_get_nnmntry_uom INTO l_uom_name;
47 CLOSE csr_get_nnmntry_uom;
48 if g_debug then
49 hr_utility.set_location('Non Monetary UOM: ' || l_uom_name,15);
50 end if;
51 ELSE
52 OPEN csr_get_uom(p_uom => p_uom, p_effective_date => p_effective_date);
53 FETCH csr_get_uom INTO l_uom_name;
54 CLOSE csr_get_uom;
55 if g_debug then
56 hr_utility.set_location('Monetary UOM: ' || l_uom_name,15);
57 end if;
58 END IF;
59 if g_debug then
60 hr_utility.set_location('Leaving: ' || l_proc,20);
61 end if;
62
63 RETURN l_uom_name;
64
65 END get_uom;
66
67 -----------------------------------------------------------------------------------------------
68
69 FUNCTION chk_potential_life_events( p_person_id IN NUMBER
70 ,p_business_group_id IN NUMBER
71 ,p_lf_evt_ocrd_dt OUT NOCOPY DATE)
72 RETURN VARCHAR2 IS
73 --
74 CURSOR csr_chk_ptnl_ler(p_person_id NUMBER, p_business_group_id NUMBER) IS
75 SELECT lf_evt_ocrd_dt
76 FROM ben_ptnl_ler_for_per ptn
77 WHERE ptn.person_id = p_person_id
78 AND ptn.business_group_id = p_business_group_id
79 AND ptn.ler_id IN ( SELECT ler_id FROM ben_ler_f ler WHERE ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO'))
80 AND ptn.ptnl_ler_for_per_stat_cd NOT IN('VOIDD','PROCD');
81 --
82 l_exists VARCHAR2(2) := 'N';
83 l_lf_evt_ocrd_dt DATE;
84 l_proc VARCHAR2(72);
85 --
86 BEGIN
87 --
88 if g_debug then
89 l_proc := g_package || 'chk_potential_life_events';
90 hr_utility.set_location('Entering: ' || l_proc,10);
91 end if;
92
93 OPEN csr_chk_ptnl_ler(p_person_id => p_person_id, p_business_group_id => p_business_group_id);
94 FETCH csr_chk_ptnl_ler INTO l_lf_evt_ocrd_dt;
95 CLOSE csr_chk_ptnl_ler;
96
97 IF (l_lf_evt_ocrd_dt IS NOT NULL) THEN
98 p_lf_evt_ocrd_dt := l_lf_evt_ocrd_dt;
99 l_exists := 'Y';
100 END IF;
101
102 if g_debug then
103 hr_utility.set_location('Leaving: ' || l_proc,20);
104 end if;
105
106 RETURN l_exists;
107 --
108 END chk_potential_life_events;
109 -----------------------------------------------------------------------------------------------
110 --SSBEN: Function to check if data changes resulted in conflicting LE's
111 -----------------------------------------------------------------------------------------------
112 FUNCTION chk_conflict_life_events( p_person_id IN NUMBER
113 ,p_business_group_id IN NUMBER
114 ,p_effective_date IN DATE
115 ,p_flag OUT NOCOPY VARCHAR2 )
116 RETURN BOOLEAN IS
117 --
118 CURSOR csr_chk_cnflt_ler IS
119 SELECT ptn.ler_id,ler.name,ler.ovridg_le_flag
120 FROM ben_ptnl_ler_for_per ptn,
121 ben_ler_f ler
122 WHERE ptn.person_id = p_person_id
123 AND ptn.business_group_id = p_business_group_id
124 AND ptn.ler_id =ler.ler_id
125 AND p_effective_date between ler.effective_start_date and effective_end_date
126 AND ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO')
127 AND ptn.ptnl_ler_for_per_stat_cd NOT IN('VOIDD','PROCD');
128 --
129 l_proc VARCHAR2(72);
130 l_counter NUMBER := 1;
131 l_ovridg_le_count NUMBER :=0;
132 l_non_ovridg_le_count NUMBER :=0;
133 l_ret_status BOOLEAN :=false;
134 --
135 BEGIN
136 --
137 if g_debug then
138 l_proc := g_package || '.chk_conflict_life_events';
139 hr_utility.set_location('Entering: ' || l_proc,10);
140 end if;
141 g_hierarchy.DELETE;
142
143 FOR ler_rec IN csr_chk_cnflt_ler LOOP
144 if g_debug then
145 hr_utility.set_location('Detected Le :' ||ler_rec.name|| to_char(ler_rec.ler_id)||ler_rec.ovridg_le_flag,20);
146
147 end if;
148 g_hierarchy(l_counter).ler_id := ler_rec.ler_id;
149 g_hierarchy(l_counter).name := ler_rec.name;
150 g_hierarchy(l_counter).hierarchy_type :=ler_rec.ovridg_le_flag;
151 l_counter:=l_counter+1;
152 IF ler_rec.ovridg_le_flag='N' THEN l_non_ovridg_le_count :=l_non_ovridg_le_count +1;
153 ELSIF ler_rec.ovridg_le_flag='Y' THEN l_ovridg_le_count :=l_ovridg_le_count +1;
154 END IF;
155 END LOOP;
156
157 if g_debug then
158 hr_utility.set_location('Number of OVERRDG LE triggered: ' || to_char(l_ovridg_le_count),30);
159 hr_utility.set_location('Number of non OVERRDG LE triggered: ' || to_char(l_non_ovridg_le_count),35);
160 hr_utility.set_location('Leaving: ' || l_proc,40);
161 end if;
162 IF l_ovridg_le_count+l_non_ovridg_le_count=0 THEN
163 fnd_message.set_name('BEN','BEN_92540_NOONE_TO_PROCESS_CM');
164 fnd_message.raise_error;
165 END IF;
166 IF l_ovridg_le_count >1 THEN l_ret_status :=true ;p_flag :='Y';
167 ELSIF l_ovridg_le_count =0 AND l_non_ovridg_le_count>1 THEN l_ret_status :=true ;p_flag :='N';
168 END IF;
169 RETURN l_ret_status;
170 --
171 END chk_conflict_life_events;
172 --------------------------------------------------------------------------------------------------------------
173
174 PROCEDURE void_potential_life_events(
175 p_person_id in number
176 ,p_business_group_id in number
177 ,p_effective_date in date
178 )
179 IS
180 --
181 l_proc VARCHAR2(72);
182 BEGIN
183 --
184 if g_debug then
185 l_proc := g_package || 'void_potential_life_events';
186 hr_utility.set_location('Entering: ' || l_proc,10);
187 end if;
188
189 UPDATE ben_ptnl_ler_for_per
190 SET ptnl_ler_for_per_stat_cd = 'VOIDD'
191 WHERE person_id = p_person_id
192 AND business_group_id = p_business_group_id
193 AND ler_id in ( select ler_id from ben_ler_f ler where ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO'))
194 AND lf_evt_ocrd_dt <= p_effective_date
195 AND ptnl_ler_for_per_stat_cd IN ('UNPROCD', 'DTCTD'); -- 5763776 Removed 'PROCD'. Should not require this.
196
197 if g_debug then
198 hr_utility.set_location('Leaving: ' || l_proc,20);
199 end if;
200 --
201 END void_potential_life_events;
202 -----------------------------------------------------------------------------------------------
203 PROCEDURE void_active_life_events(
204 p_person_id in number
205 ,p_business_group_id in number
206 ,p_effective_date in date
207 )
208 IS
209 --
210 l_proc VARCHAR2(72);
211 BEGIN
212 --
213 if g_debug then
214 l_proc := g_package || 'void_active_life_events';
215 hr_utility.set_location('Entering: ' || l_proc,10);
216 end if;
217
218 UPDATE ben_per_in_ler
219 SET per_in_ler_stat_cd = 'VOIDD'
220 ,voidd_dt = p_effective_date
221 WHERE person_id = p_person_id
222 AND business_group_id = p_business_group_id
223 AND ler_id in ( select ler_id from ben_ler_f ler where ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO'))
224 AND per_in_ler_stat_cd = 'STRTD';
225
226 if g_debug then
227 hr_utility.set_location('Leaving: ' || l_proc,20);
228 end if;
229 --
230 END void_active_life_events;
231 -----------------------------------------------------------------------------------------------
232
233 PROCEDURE void_conflict_life_events(
234 p_person_id in number
235 ,p_business_group_id in number
236 ,p_winning_ler_id in number
237 ,p_effective_date in date
238 )
239 IS
240 --
241 l_proc VARCHAR2(72);
242 BEGIN
243 --
244 if g_debug then
245 l_proc := g_package || '.void_conflict_life_events';
246 hr_utility.set_location('Entering: ' || l_proc,10);
247 end if;
248
249 UPDATE ben_ptnl_ler_for_per
250 SET ptnl_ler_for_per_stat_cd = 'VOIDD'
251 WHERE person_id = p_person_id
252 AND business_group_id = p_business_group_id
253 AND ler_id in ( select ler_id from ben_ler_f ler where ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO')
254 and ler.ler_id <> p_winning_ler_id)
255 AND lf_evt_ocrd_dt <= p_effective_date
256 AND ptnl_ler_for_per_stat_cd IN ('UNPROCD', 'DTCTD');
257
258 if g_debug then
259 hr_utility.set_location('Leaving: ' || l_proc,20);
260 end if;
261 --
262 END void_conflict_life_events;
263 -----------------------------------------------------------------------------------------------
264 PROCEDURE process_api_call(p_transaction_step_id in number
265 ,p_api_name in varchar2
266 )
267 IS
268 l_sqlstr varchar2(1000);
269 l_proc VARCHAR2(72);
270
271 BEGIN
272 --
273 if g_debug then
274 l_proc := g_package || 'process_api_call';
275 hr_utility.set_location('Entering: ' || l_proc,10);
276 end if;
277
278 l_sqlstr := 'BEGIN '||
279 p_api_name ||
280 '(p_transaction_step_id => :transaction_step_id);'||
281 'END;';
282 EXECUTE IMMEDIATE l_sqlstr USING p_transaction_step_id;
283
284 if g_debug then
285 hr_utility.set_location('Leaving: ' || l_proc,20);
286 end if;
287 --
288 END process_api_call;
289
290 -----------------------------------------------------------------------------------------------
291 PROCEDURE post_data_changes(p_transaction_id in number
292 ,p_effective_date in date
293 ,p_person_id OUT NOCOPY number
294 ,p_business_group_id OUT NOCOPY number
295 )
296 IS
297 --
298 Cursor csr_trs is
299 select trs.transaction_step_id
300 ,trs.api_name
301 from hr_api_transaction_steps trs
302 where trs.transaction_id = p_transaction_id
303 and trs.object_type is null
304 and trs.api_name not in ('BEN_PROCESS_COMPENSATION_W.PROCESS_API')
305 order by trs.processing_order, trs.transaction_step_id;
306 --
307 l_proc VARCHAR2(72);
308 l_return_status varchar2(10);
309
310 BEGIN
311 --
312 if g_debug then
313 l_proc := g_package || 'post_data_changes';
314 hr_utility.set_location('Entering: ' || l_proc,10);
315 end if;
316
317 -- select each transaction steps to process
318
319 -- Call SWI's commit Transaction
320 l_return_status :=hr_transaction_swi.commit_transaction
321 (p_transaction_id => p_transaction_id
322 ,p_effective_date => p_effective_date);
323
324 FOR csr_rec IN csr_trs LOOP
325 -- call the API for the transaction step
326 process_api_call
327 (p_transaction_step_id => csr_rec.transaction_step_id
328 ,p_api_name => csr_rec.api_name);
329
330 IF (csr_rec.api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API') THEN
331 BEGIN
332 IF ((hr_process_person_ss.g_person_id IS NOT NULL) AND
333 (hr_process_person_ss.g_session_id = ICX_SEC.G_SESSION_ID)) THEN
334 p_person_id := hr_process_person_ss.g_person_id;
335 END IF;
336 p_business_group_id := hr_transaction_api.get_number_value(
337 p_transaction_step_id => csr_rec.transaction_step_id
338 ,p_name => 'P_BUSINESS_GROUP_ID');
339 END;
340 END IF;
341
342 END LOOP;
343
344 if g_debug then
345 hr_utility.set_location('Leaving: ' || l_proc,20);
346 end if;
347 --
348 END post_data_changes;
349 -----------------------------------------------------------------------------------------------
350 -- SSBEN : Method to post the selected data changes
351 -----------------------------------------------------------------------------------------------
352 FUNCTION p_get_lf_evt_ocrd_dt(p_date in date,
353 p_uom in varchar2, p_value in number)
354 RETURN date IS
355 p_ret_date date;
356 BEGIN
357 --
358 if p_uom = 'DY' then
359 p_ret_date := p_date + p_value;
360 elsif p_uom = 'MO' then
361 p_ret_date := add_months(p_date, p_value);
362 else -- if p_uom = 'YR' then
363 p_ret_date := add_months(p_date, p_value*12);
364 end if;
365
366 return p_ret_date;
367 --
368 END p_get_lf_evt_ocrd_dt;
369
370 FUNCTION p_min_dt(p_date in date,
371 p_date1 in date)
372 RETURN date IS
373 p_ret_date date;
374 BEGIN
375 --
376 if p_date >p_date1 then
377 p_ret_date := p_date1;
378 else
379 p_ret_date := p_date;
380 end if;
381
382 return p_ret_date;
383 --
384 END p_min_dt;
385
386 PROCEDURE post_ben_changes(p_transaction_id IN NUMBER
387 ,p_person_id IN NUMBER
388 ,p_business_group_id IN NUMBER
389 ,p_effective_date IN DATE
390 ,p_session_date IN DATE
391 ,p_lf_evt_ocrd_dt OUT NOCOPY DATE
392 )
393 IS
394 Cursor csr_trs is
395 select transaction_step_id
396 ,api_name
397 from hr_api_transaction_steps
398 where transaction_id = p_transaction_id
399 order by transaction_step_id, api_name;
400
401 Cursor csr_trs_values(c_transaction_step_id in NUMBER) is
402 select datatype
403 ,name
404 ,varchar2_value
405 ,number_value
406 ,date_value
407 from hr_api_transaction_values
408 where transaction_step_id = c_transaction_step_id
409 order by transaction_value_id, datatype;
410
411 Cursor c_ler (p_typ_cd in varchar2) is
412 select ler.ler_id
413 from ben_ler_f ler
414 where ler.typ_cd = p_typ_cd
415 and ler.business_group_id = p_business_group_id
416 and p_effective_date between
417 ler.effective_start_date and ler.effective_end_date;
418
419 Cursor c_person_data is
420 select date_of_birth,original_date_of_hire hire_date
421 from per_all_people_f
422 where person_id = p_person_id
423 and business_group_id = p_business_group_id
424 and p_effective_date between
425 effective_start_date and effective_end_date;
426
427 Cursor csr_uom(c_lookup_cd VARCHAR2) IS
428 Select meaning
429 From hr_lookups
430 Where lookup_code=c_lookup_cd
431 and lookup_type='BEN_TM_UOM';
432 --------------------Variables------------------
433 l_proc VARCHAR2(72);
434 l_field_val varchar2(500);
435 --
436 -- Columns for table PER_ASSIGNMENT_BUDGET_VALUES_F
437 --
438 l_ASSIGNMENT_BUDGET_VALUE_ID number;
439 l_VALUE number;
440 --
441 -- Columns for table BEN_CRT_ORDR
442 --
443 l_CRT_ORDR_TYP_CD varchar2(60);
444 l_APLS_PERD_STRTG_DT date;
445 l_APLS_PERD_ENDG_DT date;
446 l_pl_id number;
447 --
448 -- Columns for table BEN_PER_BNFTS_BAL_F
449 --
450 l_VAL NUMBER;
451 l_BNFTS_BAL_ID NUMBER;
452 l_effective_start_date date;
453 l_effective_end_date date;
454 --
455 -- Columns for table PER_ABSENCE_ATTENDANCES
456 --
457 l_ABSENCE_ATTENDANCE_TYPE_ID varchar2(60);
458 l_ABS_ATTENDANCE_REASON_ID varchar2(60);
459 l_DATE_END varchar2(60);
460 l_DATE_START varchar2(60);
461 --
462 -- Columns for table PER_ADDRESSES
463 --
464 l_POSTAL_CODE varchar2(60);
465 l_PRIMARY_FLAG varchar2(60);
466 l_DATE_FROM varchar2(60);
467 l_DATE_TO varchar2(60);
468 -- UTF8 changes
469 -- l_REGION_2 varchar2(60);
470 l_REGION_2 varchar2(120);
471 l_ADDRESS_TYPE varchar2(60);
472
473 --
474 -- Columns for table PER_ALL_ASSIGNMENTS_F
475 --
476 l_PAY_BASIS_ID number;
477 l_EMPLOYMENT_CATEGORY varchar2(60);
478 l_LABOUR_UNION_MEMBER_FLAG varchar2(60);
479 l_JOB_ID number;
480 l_PAYROLL_ID number;
481 l_PRIMARY_FLAG1 varchar2(60);
482 l_LOCATION_ID number;
483 l_CHANGE_REASON varchar2(60);
484 l_ASSIGNMENT_TYPE varchar2(60);
485 l_ORGANIZATION_ID number;
486 l_POSITION_ID number;
487 l_BARGAINING_UNIT_CODE varchar2(60);
488 l_NORMAL_HOURS number;
489 l_FREQUENCY varchar2(60);
490 l_ASSIGNMENT_STATUS_TYPE_ID number;
491 l_GRADE_ID number;
492 l_PEOPLE_GROUP_ID NUMBER;
493 l_HOURLY_SALARIED_CODE varchar2(30);
494 l_ASS_ATTRIBUTE_CATEGORY varchar2(30);
495 l_ASS_ATTRIBUTE1 VARCHAR2(150);
496 l_ASS_ATTRIBUTE10 VARCHAR2(150);
497 l_ASS_ATTRIBUTE11 VARCHAR2(150);
498 l_ASS_ATTRIBUTE12 VARCHAR2(150);
499 l_ASS_ATTRIBUTE13 VARCHAR2(150);
500 l_ASS_ATTRIBUTE14 VARCHAR2(150);
501 l_ASS_ATTRIBUTE15 VARCHAR2(150);
502 l_ASS_ATTRIBUTE16 VARCHAR2(150);
503 l_ASS_ATTRIBUTE17 VARCHAR2(150);
504 l_ASS_ATTRIBUTE18 VARCHAR2(150);
505 l_ASS_ATTRIBUTE19 VARCHAR2(150);
506 l_ASS_ATTRIBUTE2 VARCHAR2(150);
507 l_ASS_ATTRIBUTE20 VARCHAR2(150);
508 l_ASS_ATTRIBUTE21 VARCHAR2(150);
509 l_ASS_ATTRIBUTE22 VARCHAR2(150);
510 l_ASS_ATTRIBUTE23 VARCHAR2(150);
511 l_ASS_ATTRIBUTE24 VARCHAR2(150);
512 l_ASS_ATTRIBUTE25 VARCHAR2(150);
513 l_ASS_ATTRIBUTE26 VARCHAR2(150);
514 l_ASS_ATTRIBUTE27 VARCHAR2(150);
515 l_ASS_ATTRIBUTE28 VARCHAR2(150);
516 l_ASS_ATTRIBUTE29 VARCHAR2(150);
517 l_ASS_ATTRIBUTE3 VARCHAR2(150);
518 l_ASS_ATTRIBUTE30 VARCHAR2(150);
519 l_ASS_ATTRIBUTE4 VARCHAR2(150);
520 l_ASS_ATTRIBUTE5 VARCHAR2(150);
521 l_ASS_ATTRIBUTE6 VARCHAR2(150);
522 l_ASS_ATTRIBUTE7 VARCHAR2(150);
523 l_ASS_ATTRIBUTE8 VARCHAR2(150);
524 l_ASS_ATTRIBUTE9 VARCHAR2(150);
525
526 --
527 -- Columns for table PER_ALL_PEOPLE_F
528 --
529 l_STUDENT_STATUS varchar2(60);
530 l_MARITAL_STATUS varchar2(60);
531 l_DATE_OF_DEATH date;
532 l_DATE_OF_BIRTH date;
533 l_COORD_BEN_NO_CVG_FLAG varchar2(60);
534 l_COORD_BEN_MED_PLN_NO varchar2(60);
535 l_ON_MILITARY_SERVICE varchar2(60);
536 l_REGISTERED_DISABLED_FLAG varchar2(60);
537 l_USES_TOBACCO_FLAG varchar2(60);
538 l_BENEFIT_GROUP_ID number;
539 l_ATTRIBUTE1 VARCHAR2(150);
540 l_ATTRIBUTE10 VARCHAR2(150);
541 l_ATTRIBUTE11 VARCHAR2(150);
542 l_ATTRIBUTE12 VARCHAR2(150);
543 l_ATTRIBUTE13 VARCHAR2(150);
544 l_ATTRIBUTE14 VARCHAR2(150);
545 l_ATTRIBUTE15 VARCHAR2(150);
546 l_ATTRIBUTE16 VARCHAR2(150);
547 l_ATTRIBUTE17 VARCHAR2(150);
548 l_ATTRIBUTE18 VARCHAR2(150);
549 l_ATTRIBUTE19 VARCHAR2(150);
550 l_ATTRIBUTE2 VARCHAR2(150);
551 l_ATTRIBUTE20 VARCHAR2(150);
552 l_ATTRIBUTE21 VARCHAR2(150);
553 l_ATTRIBUTE22 VARCHAR2(150);
554 l_ATTRIBUTE23 VARCHAR2(150);
555 l_ATTRIBUTE24 VARCHAR2(150);
556 l_ATTRIBUTE25 VARCHAR2(150);
557 l_ATTRIBUTE26 VARCHAR2(150);
558 l_ATTRIBUTE27 VARCHAR2(150);
559 l_ATTRIBUTE28 VARCHAR2(150);
560 l_ATTRIBUTE29 VARCHAR2(150);
561 l_ATTRIBUTE3 VARCHAR2(150);
562 l_ATTRIBUTE30 VARCHAR2(150);
563 l_ATTRIBUTE4 VARCHAR2(150);
564 l_ATTRIBUTE5 VARCHAR2(150);
565 l_ATTRIBUTE6 VARCHAR2(150);
566 l_ATTRIBUTE7 VARCHAR2(150);
567 l_ATTRIBUTE8 VARCHAR2(150);
568 l_ATTRIBUTE9 VARCHAR2(150);
569 l_DPDNT_VLNTRY_SVCE_FLAG VARCHAR2(150);
570 l_per_information10 varchar2(150);
571 l_RECEIPT_OF_DEATH_CERT_DATE DATE;
572 l_sex varchar2(30);
573
574 --
575 -- Columns for table PER_CONTACT_RELATIONSHIPS
576 --
577 l_contact_person_id number;
578 l_DATE_END1 date;
579 l_DATE_START1 date;
580 l_CONTACT_TYPE VARCHAR2(150);
581 l_PERSONAL_FLAG VARCHAR2(150);
582 l_START_LIFE_REASON_ID NUMBER;
583 l_END_LIFE_REASON_ID NUMBER;
584 l_RLTD_PER_RSDS_W_DSGNTR_FLAG VARCHAR2(150);
585 --
586 -- Columns for table PER_PERIODS_OF_SERVICE
587 --
588 l_DATE_START2 date;
589 l_LEAVING_REASON varchar2(60);
590 l_ADJUSTED_SVC_DATE date;
591 l_ACTUAL_TERMINATION_DATE date;
592 l_FINAL_PROCESS_DATE DATE;
593 l_PDS_ATTRIBUTE1 VARCHAR2(150);
594 l_PDS_ATTRIBUTE2 VARCHAR2(150);
595 l_PDS_ATTRIBUTE3 VARCHAR2(150);
596 l_PDS_ATTRIBUTE4 VARCHAR2(150);
597 l_PDS_ATTRIBUTE5 VARCHAR2(150);
598 l_PDS_ATTRIBUTE6 VARCHAR2(150);
599 l_PDS_ATTRIBUTE7 VARCHAR2(150);
600 l_PDS_ATTRIBUTE8 VARCHAR2(150);
601 l_PDS_ATTRIBUTE9 VARCHAR2(150);
602 l_PDS_ATTRIBUTE10 VARCHAR2(150);
603 l_PDS_ATTRIBUTE11 VARCHAR2(150);
604 l_PDS_ATTRIBUTE12 VARCHAR2(150);
605 l_PDS_ATTRIBUTE13 VARCHAR2(150);
606 l_PDS_ATTRIBUTE14 VARCHAR2(150);
607 l_PDS_ATTRIBUTE15 VARCHAR2(150);
608 l_PDS_ATTRIBUTE16 VARCHAR2(150);
609 l_PDS_ATTRIBUTE17 VARCHAR2(150);
610 l_PDS_ATTRIBUTE18 VARCHAR2(150);
611 l_PDS_ATTRIBUTE19 VARCHAR2(150);
612 l_PDS_ATTRIBUTE20 VARCHAR2(150);
613 --
614 -- Columns for table PER_PERSON_TYPE_USAGES_F
615 --
616 l_PERSON_TYPE_ID varchar2(60);
617 --
618 -- Select unique table names for selected LER
619 -- for each of the table call the appropriate API
620 --
621 l_agf_ler_id number ;
622 l_los_ler_id number ;
623 l_cmp_ler_id number ;
624 l_cal_ler_id number ;
625 l_hrw_ler_id number ;
626 l_tpf_ler_id number ;
627 l_ler_id number ;
628 --
629 --
630 -- Columns for Age Change temporal life event.
631 --
632 l_age_val number;
633 l_cmp_val number;
634 l_cmp_bnft_val number;
635 l_cmp_bal_val number;
636 l_tpf_val number;
637 l_hrw_val number;
638 l_hrw_bnft_val number;
639 l_uom varchar2(30);
640 l_lf_evt_ocrd_dt date;
641 l_lf_evt_ocrd_dt1 date;
642 --
643 l_date date;
644 l_errbuf varchar2(1000);
645 L_RETCODE number;
646
647
648 -- Columns for table PER_QUALIFICATIONS
649
650 l_qual_type_id number;
651 l_qual_title varchar2(120);
652 l_qual_start_date date;
653 l_qual_end_date date;
654 l_qual_attribute1 varchar2(150);
655 l_qual_attribute2 varchar2(150);
656 l_qual_attribute3 varchar2(150);
657 l_qual_attribute4 varchar2(150);
658 l_qual_attribute5 varchar2(150);
659 l_qual_attribute6 varchar2(150);
660 l_qual_attribute7 varchar2(150);
661 l_qual_attribute8 varchar2(150);
662 l_qual_attribute9 varchar2(150);
663 l_qual_attribute10 varchar2(150);
664 l_qual_attribute11 varchar2(150);
665 l_qual_attribute12 varchar2(150);
666 l_qual_attribute13 varchar2(150);
667 l_qual_attribute14 varchar2(150);
668 l_qual_attribute15 varchar2(150);
669 l_qual_attribute16 varchar2(150);
670 l_qual_attribute17 varchar2(150);
671 l_qual_attribute18 varchar2(150);
672 l_qual_attribute19 varchar2(150);
673 l_qual_attribute20 varchar2(150);
674
675 -- Columns for table PER_COMPETENCE_ELEMENTS
676
677 l_comp_id number;
678 l_prof_lvl_id number;
679 l_comp_eff_date_from date;
680 l_comp_eff_date_to date;
681 l_comp_attribute1 varchar2(150);
682 l_comp_attribute2 varchar2(150);
683 l_comp_attribute3 varchar2(150);
684 l_comp_attribute4 varchar2(150);
685 l_comp_attribute5 varchar2(150);
686 l_comp_attribute6 varchar2(150);
687 l_comp_attribute7 varchar2(150);
688 l_comp_attribute8 varchar2(150);
689 l_comp_attribute9 varchar2(150);
690 l_comp_attribute10 varchar2(150);
691 l_comp_attribute11 varchar2(150);
692 l_comp_attribute12 varchar2(150);
693 l_comp_attribute13 varchar2(150);
694 l_comp_attribute14 varchar2(150);
695 l_comp_attribute15 varchar2(150);
696 l_comp_attribute16 varchar2(150);
697 l_comp_attribute17 varchar2(150);
698 l_comp_attribute18 varchar2(150);
699 l_comp_attribute19 varchar2(150);
700 l_comp_attribute20 varchar2(150);
701
702 -- Columns for table PER_PERFORMANCE_REVIEWS
703
704 l_perf_rating varchar2(30);
705 l_review_date date;
706 l_event_id number;
707 l_perf_attribute1 varchar2(150);
708 l_perf_attribute2 varchar2(150);
709 l_perf_attribute3 varchar2(150);
710 l_perf_attribute4 varchar2(150);
711 l_perf_attribute5 varchar2(150);
712 l_perf_attribute6 varchar2(150);
713 l_perf_attribute7 varchar2(150);
714 l_perf_attribute8 varchar2(150);
715 l_perf_attribute9 varchar2(150);
716 l_perf_attribute10 varchar2(150);
717 l_perf_attribute11 varchar2(150);
718 l_perf_attribute12 varchar2(150);
719 l_perf_attribute13 varchar2(150);
720 l_perf_attribute14 varchar2(150);
721 l_perf_attribute15 varchar2(150);
722 l_perf_attribute16 varchar2(150);
723 l_perf_attribute17 varchar2(150);
724 l_perf_attribute18 varchar2(150);
725 l_perf_attribute19 varchar2(150);
726 l_perf_attribute20 varchar2(150);
727 l_perf_attribute21 varchar2(150);
728 l_perf_attribute22 varchar2(150);
729 l_perf_attribute23 varchar2(150);
730 l_perf_attribute24 varchar2(150);
731 l_perf_attribute25 varchar2(150);
732 l_perf_attribute26 varchar2(150);
733 l_perf_attribute27 varchar2(150);
734 l_perf_attribute28 varchar2(150);
735 l_perf_attribute29 varchar2(150);
736 l_perf_attribute30 varchar2(150);
737 -- Columns for PER_PAY_PROPOSALS
738
739 l_change_date date;
740 l_approved varchar2(1);
741 l_forced_ranking number;
742 l_last_change_date date;
743 l_multiple_components varchar2(30);
744 l_next_sal_review_date date;
745 l_next_perf_review_date date;
746 l_performance_rating varchar2(30);
747 l_performance_review_id number;
748 l_proposal_reason varchar2(30);
749 l_proposed_salary_n number;
750 l_pay_review_date date;
751 l_pay_attribute1 varchar2(150);
752 l_pay_attribute2 varchar2(150);
753 l_pay_attribute3 varchar2(150);
754 l_pay_attribute4 varchar2(150);
755 l_pay_attribute5 varchar2(150);
756 l_pay_attribute6 varchar2(150);
757 l_pay_attribute7 varchar2(150);
758 l_pay_attribute8 varchar2(150);
759 l_pay_attribute9 varchar2(150);
760 l_pay_attribute10 varchar2(150);
761 l_pay_attribute11 varchar2(150);
762 l_pay_attribute12 varchar2(150);
763 l_pay_attribute13 varchar2(150);
764 l_pay_attribute14 varchar2(150);
765 l_pay_attribute15 varchar2(150);
766 l_pay_attribute16 varchar2(150);
767 l_pay_attribute17 varchar2(150);
768 l_pay_attribute18 varchar2(150);
769 l_pay_attribute19 varchar2(150);
770 l_pay_attribute20 varchar2(150);
771
772 BEGIN
773 if g_debug then
774 l_proc := g_package || '.post_ben_changes';
775 hr_utility.set_location('Entering: ' || l_proc,10);
776 end if;
777 p_lf_evt_ocrd_dt := p_effective_date; -- lf_evt_ocrd_dt will be different only for DRVDAGE ,DRVDLOS,DRVDCAL
778 FOR C1 in csr_trs
779 LOOP
780 FOR C2 in csr_trs_values(C1.transaction_step_id)
781 LOOP
782 IF C2.datatype='NUMBER' THEN
783 l_field_val :=C2.number_value;
784 ELSIF C2.datatype='DATE' THEN
785 l_field_val :=C2.date_value;
786 ELSIF C2.datatype='VARCHAR2' THEN
787 l_field_val :=C2.varchar2_value;
788 END IF;
789 if g_debug then
790 hr_utility.set_location('Table and Column: ' ||C1.api_name|| C2.name || l_field_val,15);
791 end if;
792
793 IF C1.api_name = 'PER_ABSENCE_ATTENDANCES' and
794 C2.name = 'ABSENCE_ATTENDANCE_TYPE_ID' then
795 l_ABSENCE_ATTENDANCE_TYPE_ID := l_field_val;
796 ELSIF C1.api_name = 'PER_ABSENCE_ATTENDANCES' and
797 C2.name = 'ABS_ATTENDANCE_REASON_ID' then
798 l_ABS_ATTENDANCE_REASON_ID := l_field_val;
799 ELSIF C1.api_name = 'PER_ABSENCE_ATTENDANCES' and
800 C2.name = 'DATE_END' then
801 l_DATE_END := l_field_val;
802 ELSIF C1.api_name = 'PER_ABSENCE_ATTENDANCES' and
803 C2.name = 'DATE_START' then
804 l_DATE_START := l_field_val;
805 ELSIF C1.api_name = 'PER_ADDRESSES' and
806 C2.name = 'DATE_FROM' then
807 l_DATE_FROM := l_field_val;
808 ELSIF C1.api_name = 'PER_ADDRESSES' and
809 C2.name = 'DATE_TO' then
810 l_DATE_TO := l_field_val;
811 ELSIF C1.api_name = 'PER_ADDRESSES' and
812 C2.name = 'POSTAL_CODE' then
813 l_POSTAL_CODE := l_field_val;
814 ELSIF C1.api_name = 'PER_ADDRESSES' and
815 C2.name = 'PRIMARY_FLAG' then
816 l_PRIMARY_FLAG := l_field_val;
817 ELSIF C1.api_name = 'PER_ADDRESSES' and
818 C2.name = 'REGION_2' then
819 l_REGION_2 := l_field_val;
820 ELSIF C1.api_name = 'PER_ADDRESSES' and
821 C2.name = 'ADDRESS_TYPE' then
822 l_ADDRESS_TYPE := l_field_val;
823 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
824 C2.name = 'ASSIGNMENT_STATUS_TYPE_ID' then
825 l_ASSIGNMENT_STATUS_TYPE_ID := l_field_val;
826 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
827 C2.name = 'ASSIGNMENT_TYPE' then
828 l_ASSIGNMENT_TYPE := l_field_val;
829 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
830 C2.name = 'BARGAINING_UNIT_CODE' then
831 l_BARGAINING_UNIT_CODE := l_field_val;
832 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
833 C2.name = 'CHANGE_REASON' then
834 l_CHANGE_REASON := l_field_val;
835 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
836 C2.name = 'EMPLOYMENT_CATEGORY' then
837 l_EMPLOYMENT_CATEGORY := l_field_val;
838 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
839 C2.name = 'FREQUENCY' then
840 l_FREQUENCY := l_field_val;
841 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
842 C2.name = 'GRADE_ID' then
843 l_GRADE_ID := l_field_val;
844 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
845 C2.name = 'JOB_ID' then
846 l_JOB_ID := l_field_val;
847 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
848 C2.name = 'LABOUR_UNION_MEMBER_FLAG' then
849 l_LABOUR_UNION_MEMBER_FLAG := l_field_val;
850 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
851 C2.name = 'LOCATION_ID' then
852 l_LOCATION_ID := l_field_val;
853 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
854 C2.name = 'NORMAL_HOURS' then
855 l_NORMAL_HOURS := l_field_val;
856 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
857 C2.name = 'ORGANIZATION_ID' then
858 l_ORGANIZATION_ID := l_field_val;
859 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
860 C2.name = 'PAYROLL_ID' then
861 l_PAYROLL_ID := l_field_val;
862 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
863 C2.name = 'PAY_BASIS_ID' then
864 l_PAY_BASIS_ID := l_field_val;
865 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
866 C2.name = 'POSITION_ID' then
867 l_POSITION_ID := l_field_val;
868 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
869 C2.name = 'PRIMARY_FLAG' then
870 l_PRIMARY_FLAG1 := l_field_val;
871
872 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
873 C2.name = 'HOURLY_SALARIED_CODE' then
874 l_HOURLY_SALARIED_CODE := l_field_val;
875
876 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
877 C2.name = 'PEOPLE_GROUP_ID' then
878 l_PEOPLE_GROUP_ID := l_field_val;
879
880 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
881 C2.name = 'ASS_ATTRIBUTE_CATEGORY' then
882 l_ASS_ATTRIBUTE_CATEGORY := l_field_val;
883
884 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
885 C2.name = 'ASS_ATTRIBUTE1' then
886 l_ASS_ATTRIBUTE1 := l_field_val;
887
888 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
889 C2.name = 'ASS_ATTRIBUTE10' then
890 l_ASS_ATTRIBUTE10 := l_field_val;
891
892 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
893 C2.name = 'ASS_ATTRIBUTE11' then
894 l_ASS_ATTRIBUTE11 := l_field_val;
895
896 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
897 C2.name = 'ASS_ATTRIBUTE12' then
898 l_ASS_ATTRIBUTE12 := l_field_val;
899
900 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
901 C2.name = 'ASS_ATTRIBUTE13' then
902 l_ASS_ATTRIBUTE13 := l_field_val;
903
904 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
905 C2.name = 'ASS_ATTRIBUTE14' then
906 l_ASS_ATTRIBUTE14 := l_field_val;
907
908 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
909 C2.name = 'ASS_ATTRIBUTE15' then
910 l_ASS_ATTRIBUTE15 := l_field_val;
911
912 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
913 C2.name = 'ASS_ATTRIBUTE16' then
914 l_ASS_ATTRIBUTE16 := l_field_val;
915
916 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
917 C2.name = 'ASS_ATTRIBUTE17' then
918 l_ASS_ATTRIBUTE17 := l_field_val;
919
920 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
921 C2.name = 'ASS_ATTRIBUTE18' then
922 l_ASS_ATTRIBUTE18 := l_field_val;
923
924 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
925 C2.name = 'ASS_ATTRIBUTE19' then
926 l_ASS_ATTRIBUTE19 := l_field_val;
927
928 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
929 C2.name = 'ASS_ATTRIBUTE2' then
930 l_ASS_ATTRIBUTE2 := l_field_val;
931
932 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
933 C2.name = 'ASS_ATTRIBUTE20' then
934 l_ASS_ATTRIBUTE20 := l_field_val;
935
936 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
937 C2.name = 'ASS_ATTRIBUTE21' then
938 l_ASS_ATTRIBUTE21 := l_field_val;
939
940 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
941 C2.name = 'ASS_ATTRIBUTE22' then
942 l_ASS_ATTRIBUTE22 := l_field_val;
943
944 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
945 C2.name = 'ASS_ATTRIBUTE23' then
946 l_ASS_ATTRIBUTE23 := l_field_val;
947
948 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
949 C2.name = 'ASS_ATTRIBUTE24' then
950 l_ASS_ATTRIBUTE24 := l_field_val;
951
952 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
953 C2.name = 'ASS_ATTRIBUTE25' then
954 l_ASS_ATTRIBUTE25 := l_field_val;
955
956 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
957 C2.name = 'ASS_ATTRIBUTE26' then
958 l_ASS_ATTRIBUTE26 := l_field_val;
959
960 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
961 C2.name = 'ASS_ATTRIBUTE27' then
962 l_ASS_ATTRIBUTE27 := l_field_val;
963
964 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
965 C2.name = 'ASS_ATTRIBUTE28' then
966 l_ASS_ATTRIBUTE28 := l_field_val;
967
968 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
969 C2.name = 'ASS_ATTRIBUTE29' then
970 l_ASS_ATTRIBUTE29 := l_field_val;
971
972 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
973 C2.name = 'ASS_ATTRIBUTE3' then
974 l_ASS_ATTRIBUTE3 := l_field_val;
975
976 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
977 C2.name = 'ASS_ATTRIBUTE30' then
978 l_ASS_ATTRIBUTE30 := l_field_val;
979
980 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
981 C2.name = 'ASS_ATTRIBUTE4' then
982 l_ASS_ATTRIBUTE4 := l_field_val;
983
984 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
985 C2.name = 'ASS_ATTRIBUTE5' then
986 l_ASS_ATTRIBUTE5 := l_field_val;
987
988 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
989 C2.name = 'ASS_ATTRIBUTE6' then
990 l_ASS_ATTRIBUTE6 := l_field_val;
991
992 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
993 C2.name = 'ASS_ATTRIBUTE7' then
994 l_ASS_ATTRIBUTE7 := l_field_val;
995
996 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
997 C2.name = 'ASS_ATTRIBUTE8' then
998 l_ASS_ATTRIBUTE8 := l_field_val;
999
1000 ELSIF C1.api_name = 'PER_ALL_ASSIGNMENTS_F' and
1001 C2.name = 'ASS_ATTRIBUTE9' then
1002 l_ASS_ATTRIBUTE9 := l_field_val;
1003
1004 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1005 C2.name = 'BENEFIT_GROUP_ID' then
1006 l_BENEFIT_GROUP_ID := l_field_val;
1007 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1008 C2.name = 'COORD_BEN_MED_PLN_NO' then
1009 l_COORD_BEN_MED_PLN_NO := l_field_val;
1010 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1011 C2.name = 'COORD_BEN_NO_CVG_FLAG' then
1012 l_COORD_BEN_NO_CVG_FLAG := l_field_val;
1013 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1014 C2.name = 'DATE_OF_BIRTH' then
1015 l_DATE_OF_BIRTH := l_field_val;
1016 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1017 C2.name = 'DATE_OF_DEATH' then
1018 l_DATE_OF_DEATH := l_field_val;
1019 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1020 C2.name = 'MARITAL_STATUS' then
1021 l_MARITAL_STATUS := l_field_val;
1022 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1023 C2.name = 'ON_MILITARY_SERVICE' then
1024 l_ON_MILITARY_SERVICE := l_field_val;
1025 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1026 C2.name = 'REGISTERED_DISABLED_FLAG' then
1027 l_REGISTERED_DISABLED_FLAG := l_field_val;
1028 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1029 C2.name = 'STUDENT_STATUS' then
1030 l_STUDENT_STATUS := l_field_val;
1031 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1032 C2.name = 'USES_TOBACCO_FLAG' then
1033 l_USES_TOBACCO_FLAG := l_field_val;
1034 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1035 C2.name = 'PER_INFORMATION10' then
1036 l_PER_INFORMATION10 := l_field_val;
1037
1038 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1039 C2.name = 'DPDNT_VLNTRY_SVCE_FLAG' then
1040 l_DPDNT_VLNTRY_SVCE_FLAG := l_field_val;
1041
1042 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1043 C2.name = 'RECEIPT_OF_DEATH_CERT_DATE' then
1044 l_RECEIPT_OF_DEATH_CERT_DATE := l_field_val;
1045
1046 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1047 C2.name = 'SEX' then
1048 l_SEX := l_field_val;
1049
1050 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1051 C2.name = 'ATTRIBUTE1' then
1052 l_ATTRIBUTE1 := l_field_val;
1053
1054 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1055 C2.name = 'ATTRIBUTE10' then
1056 l_ATTRIBUTE10 := l_field_val;
1057
1058 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1059 C2.name = 'ATTRIBUTE11' then
1060 l_ATTRIBUTE11 := l_field_val;
1061
1062 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1063 C2.name = 'ATTRIBUTE12' then
1064 l_ATTRIBUTE12 := l_field_val;
1065
1066 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1067 C2.name = 'ATTRIBUTE13' then
1068 l_ATTRIBUTE13 := l_field_val;
1069
1070 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1071 C2.name = 'ATTRIBUTE14' then
1072 l_ATTRIBUTE14 := l_field_val;
1073
1074 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1075 C2.name = 'ATTRIBUTE15' then
1076 l_ATTRIBUTE15 := l_field_val;
1077
1078 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1079 C2.name = 'ATTRIBUTE16' then
1080 l_ATTRIBUTE16 := l_field_val;
1081
1082 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1083 C2.name = 'ATTRIBUTE17' then
1084 l_ATTRIBUTE17 := l_field_val;
1085
1086 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1087 C2.name = 'ATTRIBUTE18' then
1088 l_ATTRIBUTE18 := l_field_val;
1089
1090 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1091 C2.name = 'ATTRIBUTE19' then
1092 l_ATTRIBUTE19 := l_field_val;
1093
1094 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1095 C2.name = 'ATTRIBUTE2' then
1096 l_ATTRIBUTE2 := l_field_val;
1097
1098 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1099 C2.name = 'ATTRIBUTE20' then
1100 l_ATTRIBUTE20 := l_field_val;
1101
1102 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1103 C2.name = 'ATTRIBUTE21' then
1104 l_ATTRIBUTE21 := l_field_val;
1105
1106 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1107 C2.name = 'ATTRIBUTE22' then
1108 l_ATTRIBUTE22 := l_field_val;
1109
1110 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1111 C2.name = 'ATTRIBUTE23' then
1112 l_ATTRIBUTE23 := l_field_val;
1113
1114 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1115 C2.name = 'ATTRIBUTE24' then
1116 l_ATTRIBUTE24 := l_field_val;
1117
1118 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1119 C2.name = 'ATTRIBUTE25' then
1120 l_ATTRIBUTE25 := l_field_val;
1121
1122 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1123 C2.name = 'ATTRIBUTE26' then
1124 l_ATTRIBUTE26 := l_field_val;
1125
1126 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1127 C2.name = 'ATTRIBUTE27' then
1128 l_ATTRIBUTE27 := l_field_val;
1129
1130 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1131 C2.name = 'ATTRIBUTE28' then
1132 l_ATTRIBUTE28 := l_field_val;
1133
1134 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1135 C2.name = 'ATTRIBUTE29' then
1136 l_ATTRIBUTE29 := l_field_val;
1137
1138 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1139 C2.name = 'ATTRIBUTE3' then
1140 l_ATTRIBUTE3 := l_field_val;
1141
1142 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1143 C2.name = 'ATTRIBUTE30' then
1144 l_ATTRIBUTE30 := l_field_val;
1145
1146 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1147 C2.name = 'ATTRIBUTE4' then
1148 l_ATTRIBUTE4 := l_field_val;
1149
1150 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1151 C2.name = 'ATTRIBUTE5' then
1152 l_ATTRIBUTE5 := l_field_val;
1153
1154 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1155 C2.name = 'ATTRIBUTE6' then
1156 l_ATTRIBUTE6 := l_field_val;
1157
1158 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1159 C2.name = 'ATTRIBUTE7' then
1160 l_ATTRIBUTE7 := l_field_val;
1161
1162 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1163 C2.name = 'ATTRIBUTE8' then
1164 l_ATTRIBUTE8 := l_field_val;
1165
1166 ELSIF C1.api_name = 'PER_ALL_PEOPLE_F' and
1167 C2.name = 'ATTRIBUTE9' then
1168 l_ATTRIBUTE9 := l_field_val;
1169
1170 ELSIF C1.api_name = 'PER_CONTACT_RELATIONSHIPS' and
1171 C2.name = 'DATE_END' then
1172 l_DATE_END1 := l_field_val;
1173 ELSIF C1.api_name = 'PER_CONTACT_RELATIONSHIPS' and
1174 C2.name = 'DATE_START' then
1175 l_DATE_START1 := l_field_val;
1176
1177 ELSIF C1.api_name = 'PER_CONTACT_RELATIONSHIPS' and
1178 C2.name = 'CONTACT_PERSON_ID' then
1179 l_CONTACT_PERSON_ID := l_field_val;
1180
1181
1182 ELSIF C1.api_name = 'PER_CONTACT_RELATIONSHIPS' and
1183 C2.name = 'CONTACT_TYPE' then
1184 l_CONTACT_TYPE := l_field_val;
1185
1186 ELSIF C1.api_name = 'PER_CONTACT_RELATIONSHIPS' and
1187 C2.name = 'END_LIFE_REASON_ID' then
1188 l_END_LIFE_REASON_ID := l_field_val;
1189
1190 ELSIF C1.api_name = 'PER_CONTACT_RELATIONSHIPS' and
1191 C2.name = 'PERSONAL_FLAG' then
1192 l_PERSONAL_FLAG := l_field_val;
1193
1194 ELSIF C1.api_name = 'PER_CONTACT_RELATIONSHIPS' and
1195 C2.name = 'START_LIFE_REASON_ID' then
1196 l_START_LIFE_REASON_ID := l_field_val;
1197
1198 ELSIF C1.api_name = 'PER_CONTACT_RELATIONSHIPS' and
1199 C2.name = 'RLTD_PER_RSDS_W_DSGNTR_FLAG' then
1200 l_RLTD_PER_RSDS_W_DSGNTR_FLAG := l_field_val;
1201
1202 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1203 C2.name = 'ACTUAL_TERMINATION_DATE' then
1204 l_ACTUAL_TERMINATION_DATE := l_field_val;
1205 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1206 C2.name = 'ADJUSTED_SVC_DATE' then
1207 l_ADJUSTED_SVC_DATE :=
1208 l_field_val;
1209 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1210 C2.name = 'DATE_START' then
1211 l_DATE_START2 := l_field_val;
1212 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1213 C2.name = 'FINAL_PROCESS_DATE' then
1214 l_FINAL_PROCESS_DATE := l_field_val;
1215 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1216 C2.name = 'ATTRIBUTE1' then
1217 l_PDS_ATTRIBUTE1 := l_field_val;
1218 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1219 C2.name = 'ATTRIBUTE2' then
1220 l_PDS_ATTRIBUTE2 := l_field_val;
1221 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1222 C2.name = 'ATTRIBUTE3' then
1223 l_PDS_ATTRIBUTE3 := l_field_val;
1224 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1225 C2.name = 'ATTRIBUTE4' then
1226 l_PDS_ATTRIBUTE4 := l_field_val;
1227 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1228 C2.name = 'ATTRIBUTE5' then
1229 l_PDS_ATTRIBUTE5 := l_field_val;
1230 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1231 C2.name = 'ATTRIBUTE6' then
1232 l_PDS_ATTRIBUTE6 := l_field_val;
1233 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1234 C2.name = 'ATTRIBUTE7' then
1235 l_PDS_ATTRIBUTE7 := l_field_val;
1236 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1237 C2.name = 'ATTRIBUTE8' then
1238 l_PDS_ATTRIBUTE8 := l_field_val;
1239 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1240 C2.name = 'ATTRIBUTE9' then
1241 l_PDS_ATTRIBUTE9 := l_field_val;
1242 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1243 C2.name = 'ATTRIBUTE10' then
1244 l_PDS_ATTRIBUTE10 := l_field_val;
1245 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1246 C2.name = 'ATTRIBUTE11' then
1247 l_PDS_ATTRIBUTE11 := l_field_val;
1248 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1249 C2.name = 'ATTRIBUTE12' then
1250 l_PDS_ATTRIBUTE12 := l_field_val;
1251 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1252 C2.name = 'ATTRIBUTE13' then
1253 l_PDS_ATTRIBUTE13 := l_field_val;
1254 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1255 C2.name = 'ATTRIBUTE14' then
1256 l_PDS_ATTRIBUTE14 := l_field_val;
1257 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1258 C2.name = 'ATTRIBUTE15' then
1259 l_PDS_ATTRIBUTE15 := l_field_val;
1260 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1261 C2.name = 'ATTRIBUTE16' then
1262 l_PDS_ATTRIBUTE16 := l_field_val;
1263 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1264 C2.name = 'ATTRIBUTE17' then
1265 l_PDS_ATTRIBUTE17 := l_field_val;
1266 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1267 C2.name = 'ATTRIBUTE18' then
1268 l_PDS_ATTRIBUTE18 := l_field_val;
1269 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1270 C2.name = 'ATTRIBUTE19' then
1271 l_PDS_ATTRIBUTE19 := l_field_val;
1272 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1273 C2.name = 'ATTRIBUTE20' then
1274 l_PDS_ATTRIBUTE20 := l_field_val;
1275 ELSIF C1.api_name = 'PER_PERIODS_OF_SERVICE' and
1276 C2.name = 'LEAVING_REASON' then
1277 l_LEAVING_REASON := l_field_val;
1278 ELSIF C1.api_name = 'PER_PERSON_TYPE_USAGES_F' and
1279 C2.name = 'PERSON_TYPE_ID' then
1280 l_PERSON_TYPE_ID := l_field_val;
1281 ELSIF C1.api_name = 'BEN_PER_BNFTS_BAL_F' and
1282 C2.name = 'BNFTS_BAL_ID' then
1283 l_BNFTS_BAL_ID := l_field_val;
1284 ELSIF C1.api_name = 'BEN_PER_BNFTS_BAL_F' and
1285 C2.name = 'VAL' then
1286 l_VAL := l_field_val;
1287 ELSIF C1.api_name = 'BEN_PER_BNFTS_BAL_F' and
1288 C2.name = 'EFFECTIVE_START_DATE' then
1289 l_EFFECTIVE_START_DATE := l_field_val;
1290 ELSIF C1.api_name = 'BEN_PER_BNFTS_BAL_F' and
1291 C2.name = 'EFFECTIVE_END_DATE' then
1292 l_EFFECTIVE_END_DATE := l_field_val;
1293 ELSIF C1.api_name = 'BEN_CRT_ORDR' and
1294 C2.name = 'CRT_ORDR_TYP_CD' then
1295 l_CRT_ORDR_TYP_CD := l_field_val;
1296 ELSIF C1.api_name = 'BEN_CRT_ORDR' and
1297 C2.name = 'PL_ID' then
1298 l_pl_id := l_field_val;
1299 ELSIF C1.api_name = 'BEN_CRT_ORDR' and
1300 C2.name = 'APLS_PERD_STRTG_DT' then
1301 l_APLS_PERD_STRTG_DT := l_field_val;
1302 ELSIF C1.api_name = 'BEN_CRT_ORDR' and
1303 C2.name = 'APLS_PERD_ENDG_DT' then
1304 l_APLS_PERD_ENDG_DT := l_field_val;
1305 ELSIF C1.api_name = 'PER_ASSIGNMENT_BUDGET_VALUES_F' and
1306 C2.name = 'ASSIGNMENT_BUDGET_VALUE_ID' then
1307 l_ASSIGNMENT_BUDGET_VALUE_ID := l_field_val;
1308 ELSIF C1.api_name = 'PER_ASSIGNMENT_BUDGET_VALUES_F' and
1309 C2.name = 'VALUE' then
1310 l_value := l_field_val;
1311 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1312 C2.name = 'QUALIFICATION_TYPE_ID' then
1313 l_qual_type_id := l_field_val;
1314 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1315 C2.name = 'TITLE' then
1316 l_qual_title := l_field_val;
1317 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1318 C2.name = 'START_DATE' then
1319 l_qual_start_date := l_field_val;
1320 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1321 C2.name = 'END_DATE' then
1322 l_qual_end_date := l_field_val;
1323 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1324 C2.name = 'ATTRIBUTE1' then
1325 l_qual_attribute1 := l_field_val;
1326 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1327 C2.name = 'ATTRIBUTE2' then
1328 l_qual_attribute2 := l_field_val;
1329 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1330 C2.name = 'ATTRIBUTE3' then
1331 l_qual_attribute3 := l_field_val;
1332 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1333 C2.name = 'ATTRIBUTE4' then
1334 l_qual_attribute4 := l_field_val;
1335 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1336 C2.name = 'ATTRIBUTE5' then
1337 l_qual_attribute5 := l_field_val;
1338 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1339 C2.name = 'ATTRIBUTE6' then
1340 l_qual_attribute6 := l_field_val;
1341 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1342 C2.name = 'ATTRIBUTE7' then
1343 l_qual_attribute7 := l_field_val;
1344 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1345 C2.name = 'ATTRIBUTE8' then
1346 l_qual_attribute8 := l_field_val;
1347 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1348 C2.name = 'ATTRIBUTE9' then
1349 l_qual_attribute9 := l_field_val;
1350 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1351 C2.name = 'ATTRIBUTE10' then
1352 l_qual_attribute10 := l_field_val;
1353 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1354 C2.name = 'ATTRIBUTE11' then
1355 l_qual_attribute11 := l_field_val;
1356 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1357 C2.name = 'ATTRIBUTE12' then
1358 l_qual_attribute12 := l_field_val;
1359 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1360 C2.name = 'ATTRIBUTE13' then
1361 l_qual_attribute13 := l_field_val;
1362 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1363 C2.name = 'ATTRIBUTE14' then
1364 l_qual_attribute14 := l_field_val;
1365 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1366 C2.name = 'ATTRIBUTE15' then
1367 l_qual_attribute15 := l_field_val;
1368 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1369 C2.name = 'ATTRIBUTE16' then
1370 l_qual_attribute16 := l_field_val;
1371 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1372 C2.name = 'ATTRIBUTE17' then
1373 l_qual_attribute17 := l_field_val;
1374 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1375 C2.name = 'ATTRIBUTE18' then
1376 l_qual_attribute18 := l_field_val;
1377 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1378 C2.name = 'ATTRIBUTE19' then
1379 l_qual_attribute19 := l_field_val;
1380 ELSIF C1.api_name = 'PER_QUALIFICATIONS' and
1381 C2.name = 'ATTRIBUTE20' then
1382 l_qual_attribute20 := l_field_val;
1383 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1384 C2.name = 'COMPETENCE_ID' then
1385 l_comp_id := l_field_val;
1386 /*ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1387 C2.name = 'PROFICIENCY_LEVEL_ID' then
1388 begin
1389 l_prof_lvl_id := l_field_val;
1390 l_comp_id := :GLOBAL.CMN_LOV_VAL1;
1391 end; */
1392 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1393 C2.name = 'EFFECTIVE_DATE_FROM' then
1394 l_comp_eff_date_from := l_field_val;
1395 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1396 C2.name = 'EFFECTIVE_DATE_TO' then
1397 l_comp_eff_date_to := l_field_val;
1398 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1399 C2.name = 'ATTRIBUTE1' then
1400 l_comp_attribute1 := l_field_val;
1401 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1402 C2.name = 'ATTRIBUTE2' then
1403 l_comp_attribute2 := l_field_val;
1404 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1405 C2.name = 'ATTRIBUTE3' then
1406 l_comp_attribute3 := l_field_val;
1407 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1408 C2.name = 'ATTRIBUTE4' then
1409 l_comp_attribute4 := l_field_val;
1410 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1411 C2.name = 'ATTRIBUTE5' then
1412 l_comp_attribute5 := l_field_val;
1413 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1414 C2.name = 'ATTRIBUTE6' then
1415 l_comp_attribute6 := l_field_val;
1416 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1417 C2.name = 'ATTRIBUTE7' then
1418 l_comp_attribute7 := l_field_val;
1419 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1420 C2.name = 'ATTRIBUTE8' then
1421 l_comp_attribute8 := l_field_val;
1422 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1423 C2.name = 'ATTRIBUTE9' then
1424 l_comp_attribute9 := l_field_val;
1425 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1426 C2.name = 'ATTRIBUTE10' then
1427 l_comp_attribute10 := l_field_val;
1428 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1429 C2.name = 'ATTRIBUTE11' then
1430 l_comp_attribute11 := l_field_val;
1431 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1432 C2.name = 'ATTRIBUTE12' then
1433 l_comp_attribute12 := l_field_val;
1434 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1435 C2.name = 'ATTRIBUTE13' then
1436 l_comp_attribute13 := l_field_val;
1437 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1438 C2.name = 'ATTRIBUTE14' then
1439 l_comp_attribute14 := l_field_val;
1440 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1441 C2.name = 'ATTRIBUTE15' then
1442 l_comp_attribute15 := l_field_val;
1443 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1444 C2.name = 'ATTRIBUTE16' then
1445 l_comp_attribute16 := l_field_val;
1446 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1447 C2.name = 'ATTRIBUTE17' then
1448 l_comp_attribute17 := l_field_val;
1449 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1450 C2.name = 'ATTRIBUTE18' then
1451 l_comp_attribute18 := l_field_val;
1452 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1453 C2.name = 'ATTRIBUTE19' then
1454 l_comp_attribute19 := l_field_val;
1455 ELSIF C1.api_name = 'PER_COMPETENCE_ELEMENTS' and
1456 C2.name = 'ATTRIBUTE20' then
1457 l_comp_attribute20 := l_field_val;
1458 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1459 C2.name = 'PERFORMANCE_RATING' then
1460 l_perf_rating := l_field_val;
1461 /* ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1462 C2.name = 'EVENT_ID' then
1463 begin
1464 l_event_id := l_field_val;
1465 l_review_date := field_to_date('global.CMN_LOV_VAL1');
1466 end; */
1467 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1468 C2.name = 'ATTRIBUTE1' then
1469 l_perf_attribute1 := l_field_val;
1470 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1471 C2.name = 'ATTRIBUTE2' then
1472 l_perf_attribute2 := l_field_val;
1473 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1474 C2.name = 'ATTRIBUTE3' then
1475 l_perf_attribute3 := l_field_val;
1476 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1477 C2.name = 'ATTRIBUTE4' then
1478 l_perf_attribute4 := l_field_val;
1479 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1480 C2.name = 'ATTRIBUTE5' then
1481 l_perf_attribute5 := l_field_val;
1482 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1483 C2.name = 'ATTRIBUTE6' then
1484 l_perf_attribute6 := l_field_val;
1485 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1486 C2.name = 'ATTRIBUTE7' then
1487 l_perf_attribute7 := l_field_val;
1488 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1489 C2.name = 'ATTRIBUTE8' then
1490 l_perf_attribute8 := l_field_val;
1491 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1492 C2.name = 'ATTRIBUTE9' then
1493 l_perf_attribute9 := l_field_val;
1494 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1495 C2.name = 'ATTRIBUTE10' then
1496 l_perf_attribute10 := l_field_val;
1497 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1498 C2.name = 'ATTRIBUTE11' then
1499 l_perf_attribute11 := l_field_val;
1500 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1501 C2.name = 'ATTRIBUTE12' then
1502 l_perf_attribute12 := l_field_val;
1503 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1504 C2.name = 'ATTRIBUTE13' then
1505 l_perf_attribute13 := l_field_val;
1506 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1507 C2.name = 'ATTRIBUTE14' then
1508 l_perf_attribute14 := l_field_val;
1509 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1510 C2.name = 'ATTRIBUTE15' then
1511 l_perf_attribute15 := l_field_val;
1512 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1513 C2.name = 'ATTRIBUTE16' then
1514 l_perf_attribute16 := l_field_val;
1515 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1516 C2.name = 'ATTRIBUTE17' then
1517 l_perf_attribute17 := l_field_val;
1518 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1519 C2.name = 'ATTRIBUTE18' then
1520 l_perf_attribute18 := l_field_val;
1521 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1522 C2.name = 'ATTRIBUTE19' then
1523 l_perf_attribute19 := l_field_val;
1524 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1525 C2.name = 'ATTRIBUTE20' then
1526 l_perf_attribute20 := l_field_val;
1527 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1528 C2.name = 'ATTRIBUTE21' then
1529 l_perf_attribute21 := l_field_val;
1530 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1531 C2.name = 'ATTRIBUTE22' then
1532 l_perf_attribute22 := l_field_val;
1533 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1534 C2.name = 'ATTRIBUTE23' then
1535 l_perf_attribute23 := l_field_val;
1536 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1537 C2.name = 'ATTRIBUTE24' then
1538 l_perf_attribute24 := l_field_val;
1539 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1540 C2.name = 'ATTRIBUTE25' then
1541 l_perf_attribute25 := l_field_val;
1542 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1543 C2.name = 'ATTRIBUTE26' then
1544 l_perf_attribute26 := l_field_val;
1545 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1546 C2.name = 'ATTRIBUTE27' then
1547 l_perf_attribute27 := l_field_val;
1548 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1549 C2.name = 'ATTRIBUTE28' then
1550 l_perf_attribute28 := l_field_val;
1551 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1552 C2.name = 'ATTRIBUTE29' then
1553 l_perf_attribute29 := l_field_val;
1554 ELSIF C1.api_name = 'PER_PERFORMANCE_REVIEWS' and
1555 C2.name = 'ATTRIBUTE30' then
1556 l_perf_attribute30 := l_field_val;
1557 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1558 C2.name = 'APPROVED' then
1559 l_approved := l_field_val;
1560 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1561 C2.name = 'CHANGE_DATE' then
1562 l_change_date := l_field_val;
1563 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1564 C2.name = 'PROPOSED_SALARY_N' then
1565 l_proposed_salary_n := l_field_val;
1566 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1567 C2.name = 'FORCED_RANKING' then
1568 l_forced_ranking := l_field_val;
1569 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1570 C2.name = 'PERFORMANCE_REVIEW_ID' then
1571 l_performance_review_id := l_field_val;
1572 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1573 C2.name = 'NEXT_SAL_REVIEW_DATE' then
1574 l_next_sal_review_date := l_field_val;
1575 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1576 C2.name = 'ATTRIBUTE1' then
1577 l_pay_attribute1 := l_field_val;
1578 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1579 C2.name = 'ATTRIBUTE2' then
1580 l_pay_attribute2 := l_field_val;
1581 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1582 C2.name = 'ATTRIBUTE3' then
1583 l_pay_attribute3 := l_field_val;
1584 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1585 C2.name = 'ATTRIBUTE4' then
1586 l_pay_attribute4 := l_field_val;
1587 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1588 C2.name = 'ATTRIBUTE5' then
1589 l_pay_attribute5 := l_field_val;
1590 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1591 C2.name = 'ATTRIBUTE6' then
1592 l_pay_attribute6 := l_field_val;
1593 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1594 C2.name = 'ATTRIBUTE7' then
1595 l_pay_attribute7 := l_field_val;
1596 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1597 C2.name = 'ATTRIBUTE8' then
1598 l_pay_attribute8 := l_field_val;
1599 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1600 C2.name = 'ATTRIBUTE9' then
1601 l_pay_attribute9 := l_field_val;
1602 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1603 C2.name = 'ATTRIBUTE10' then
1604 l_pay_attribute10 := l_field_val;
1605 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1606 C2.name = 'ATTRIBUTE11' then
1607 l_pay_attribute11 := l_field_val;
1608 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1609 C2.name = 'ATTRIBUTE12' then
1610 l_pay_attribute12 := l_field_val;
1611 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1612 C2.name = 'ATTRIBUTE13' then
1613 l_pay_attribute13 := l_field_val;
1614 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1615 C2.name = 'ATTRIBUTE14' then
1616 l_pay_attribute14 := l_field_val;
1617 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1618 C2.name = 'ATTRIBUTE15' then
1619 l_pay_attribute15 := l_field_val;
1620 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1621 C2.name = 'ATTRIBUTE16' then
1622 l_pay_attribute16 := l_field_val;
1623 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1624 C2.name = 'ATTRIBUTE17' then
1625 l_pay_attribute17 := l_field_val;
1626 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1627 C2.name = 'ATTRIBUTE18' then
1628 l_pay_attribute18 := l_field_val;
1629 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1630 C2.name = 'ATTRIBUTE19' then
1631 l_pay_attribute19 := l_field_val;
1632 ELSIF C1.api_name = 'PER_PAY_PROPOSALS' and
1633 C2.name = 'ATTRIBUTE20' then
1634 l_pay_attribute20 := l_field_val;
1635 -- derived data factors
1636 ELSIF C1.api_name = 'DRVDAGE' then
1637 l_age_val := C2.number_value;
1638 l_uom := C2.varchar2_value;
1639 FOR p_rec IN c_person_data LOOP
1640 l_date := p_rec.date_of_birth;
1641
1642 l_lf_evt_ocrd_dt := p_get_lf_evt_ocrd_dt(
1643 p_date => l_date,
1644 p_uom => l_uom,
1645 p_value => l_age_val);
1646 if(l_lf_evt_ocrd_dt < p_rec.hire_date) then
1647 OPEN csr_uom(l_uom);
1648 FETCH csr_uom into l_uom;
1649 CLOSE csr_uom;
1650 fnd_message.set_name('BEN', 'BEN_93194_AGE_VALUE_ERROR');
1651 fnd_message.set_token('AGE',l_age_val);
1652 fnd_message.set_token('UOM',l_uom);
1653 fnd_message.set_token('HIRE_DATE',to_char(p_rec.hire_date,'DD-MM-RRRR'));
1654 fnd_message.set_token('DOB',to_char(l_date,'DD-MM-RRRR'));
1655 fnd_message.raise_error;
1656 end if;
1657 END LOOP;
1658 ELSIF C1.api_name = 'DRVDLOS' then
1659 l_age_val := C2.number_value;
1660 l_uom := C2.varchar2_value;
1661 FOR p_rec IN c_person_data LOOP
1662 l_date := p_rec.hire_date;
1663 END LOOP;
1664 l_lf_evt_ocrd_dt := p_get_lf_evt_ocrd_dt(
1665 p_date => l_date,
1666 p_uom => l_uom,
1667 p_value => l_age_val);
1668 ELSIF C1.api_name = 'DRVDCAL' and
1669 C2.name = 'AGE' then
1670 l_age_val := C2.number_value;
1671 l_uom := C2.varchar2_value;
1672 FOR p_rec IN c_person_data LOOP
1673 l_date := p_rec.date_of_birth;
1674 END LOOP;
1675 l_lf_evt_ocrd_dt := p_get_lf_evt_ocrd_dt(
1676 p_date => l_date,
1677 p_uom => l_uom,
1678 p_value => l_age_val);
1679 ELSIF C1.api_name = 'DRVDCAL' and
1680 C2.name = 'LOS' then
1681 l_age_val := C2.number_value;
1682 l_uom := C2.varchar2_value;
1683 FOR p_rec IN c_person_data LOOP
1684 l_date := p_rec.hire_date;
1685 END LOOP;
1686 l_lf_evt_ocrd_dt1 := p_get_lf_evt_ocrd_dt(
1687 p_date => l_date,
1688 p_uom => l_uom,
1689 p_value => l_age_val);
1690 ELSIF C1.api_name = 'DRVDCMP' and
1691 C2.name = 'COMP' then
1692 l_cmp_val := l_field_val;
1693 ELSIF C1.api_name = 'DRVDCMP' and
1694 C2.name = 'COMPBAL' then
1695 l_cmp_bnft_val := l_field_val;
1696 ELSIF C1.api_name = 'DRVDCMP' and
1697 C2.name = 'COMPBBAL' then
1698 l_cmp_bal_val := l_field_val;
1699 ELSIF C1.api_name = 'DRVDHRW' and
1700 C2.name = 'HRW' then
1701 l_hrw_val := l_field_val;
1702 ELSIF C1.api_name = 'DRVDHRW' and
1703 C2.name = 'HRWBAL' then
1704 l_hrw_bnft_val := l_field_val;
1705 ELSIF C1.api_name = 'DRVDTPF' then
1706 l_tpf_val := l_field_val;
1707
1708 END IF; --if else ladder
1709 END LOOP;
1710 /*IF C1.api_name= 'PER_ALL_ASSIGNMENTS_F' THEN
1711 --
1712 ben_whatif_elig.WATIF_ALL_ASSIGNMENTS_F_API(
1713 p_person_id => p_person_id
1714 ,p_PAY_BASIS_ID => l_PAY_BASIS_ID
1715 ,p_LABOUR_UNION_MEMBER_FLAG => l_LABOUR_UNION_MEMBER_FLAG
1716 ,p_JOB_ID => l_JOB_ID
1717 ,p_PAYROLL_ID => l_PAYROLL_ID
1718 ,p_PRIMARY_FLAG => l_PRIMARY_FLAG1
1719 ,p_LOCATION_ID => l_LOCATION_ID
1720 ,p_CHANGE_REASON => l_CHANGE_REASON
1721 ,p_ASSIGNMENT_TYPE => l_ASSIGNMENT_TYPE
1722 ,p_ORGANIZATION_ID => l_ORGANIZATION_ID
1723 ,p_POSITION_ID => l_POSITION_ID
1724 ,p_BARGAINING_UNIT_CODE => l_BARGAINING_UNIT_CODE
1725 ,p_NORMAL_HOURS => l_NORMAL_HOURS
1726 ,p_FREQUENCY => l_FREQUENCY
1727 ,p_ASSIGNMENT_STATUS_TYPE_ID => l_ASSIGNMENT_STATUS_TYPE_ID
1728 ,p_GRADE_ID => l_GRADE_ID
1729 ,p_EMPLOYMENT_CATEGORY => l_EMPLOYMENT_CATEGORY
1730 ,p_PEOPLE_GROUP_ID => l_PEOPLE_GROUP_ID
1731 ,p_HOURLY_SALARIED_CODE => l_HOURLY_SALARIED_CODE
1732 ,p_ASS_ATTRIBUTE_CATEGORY => l_ASS_ATTRIBUTE_CATEGORY
1733 ,p_ASS_ATTRIBUTE1 => l_ASS_ATTRIBUTE1
1734 ,p_ASS_ATTRIBUTE10 => l_ASS_ATTRIBUTE10
1735 ,p_ASS_ATTRIBUTE11 => l_ASS_ATTRIBUTE11
1736 ,p_ASS_ATTRIBUTE12 => l_ASS_ATTRIBUTE12
1737 ,p_ASS_ATTRIBUTE13 => l_ASS_ATTRIBUTE13
1738 ,p_ASS_ATTRIBUTE14 => l_ASS_ATTRIBUTE14
1739 ,p_ASS_ATTRIBUTE15 => l_ASS_ATTRIBUTE15
1740 ,p_ASS_ATTRIBUTE16 => l_ASS_ATTRIBUTE16
1741 ,p_ASS_ATTRIBUTE17 => l_ASS_ATTRIBUTE17
1742 ,p_ASS_ATTRIBUTE18 => l_ASS_ATTRIBUTE18
1743 ,p_ASS_ATTRIBUTE19 => l_ASS_ATTRIBUTE19
1744 ,p_ASS_ATTRIBUTE2 => l_ASS_ATTRIBUTE2
1745 ,p_ASS_ATTRIBUTE20 => l_ASS_ATTRIBUTE20
1746 ,p_ASS_ATTRIBUTE21 => l_ASS_ATTRIBUTE21
1747 ,p_ASS_ATTRIBUTE22 => l_ASS_ATTRIBUTE22
1748 ,p_ASS_ATTRIBUTE23 => l_ASS_ATTRIBUTE23
1749 ,p_ASS_ATTRIBUTE24 => l_ASS_ATTRIBUTE24
1750 ,p_ASS_ATTRIBUTE25 => l_ASS_ATTRIBUTE25
1751 ,p_ASS_ATTRIBUTE26 => l_ASS_ATTRIBUTE26
1752 ,p_ASS_ATTRIBUTE27 => l_ASS_ATTRIBUTE27
1753 ,p_ASS_ATTRIBUTE28 => l_ASS_ATTRIBUTE28
1754 ,p_ASS_ATTRIBUTE29 => l_ASS_ATTRIBUTE29
1755 ,p_ASS_ATTRIBUTE3 => l_ASS_ATTRIBUTE3
1756 ,p_ASS_ATTRIBUTE30 => l_ASS_ATTRIBUTE30
1757 ,p_ASS_ATTRIBUTE4 => l_ASS_ATTRIBUTE4
1758 ,p_ASS_ATTRIBUTE5 => l_ASS_ATTRIBUTE5
1759 ,p_ASS_ATTRIBUTE6 => l_ASS_ATTRIBUTE6
1760 ,p_ASS_ATTRIBUTE7 => l_ASS_ATTRIBUTE7
1761 ,p_ASS_ATTRIBUTE8 => l_ASS_ATTRIBUTE8
1762 ,p_ASS_ATTRIBUTE9 => l_ASS_ATTRIBUTE9
1763 ,p_business_group_id => p_business_group_id
1764 ,p_effective_date => p_effective_date
1765 );*/
1766 IF C1.api_name= 'PER_ABSENCE_ATTENDANCES' THEN
1767 --
1768 ben_whatif_elig.WATIF_ABSENCE_ATTENDANCES_API(
1769 p_person_id => p_person_id
1770 ,p_ABSENCE_ATTENDANCE_TYPE_ID => l_ABSENCE_ATTENDANCE_TYPE_ID
1771 ,p_ABS_ATTENDANCE_REASON_ID => l_ABS_ATTENDANCE_REASON_ID
1772 ,p_DATE_END => l_DATE_END
1773 ,p_DATE_START => l_DATE_START
1774 ,p_business_group_id => p_business_group_id
1775 ,p_effective_date => p_effective_date
1776 );
1777 --
1778 ELSIF C1.api_name= 'PER_ADDRESSES' THEN
1779 --
1780
1781 ben_whatif_elig.WATIF_ADDRESSES_API(
1782 p_person_id => p_person_id
1783 ,p_POSTAL_CODE => l_POSTAL_CODE
1784 ,p_PRIMARY_FLAG => l_PRIMARY_FLAG
1785 ,p_REGION_2 => l_region_2
1786 ,p_ADDRESS_TYPE => l_address_type
1787 ,p_DATE_FROM => l_DATE_FROM
1788 ,p_DATE_TO => l_DATE_TO
1789 ,p_effective_date => p_effective_date
1790 );
1791
1792 --
1793 ELSIF C1.api_name= 'PER_ALL_ASSIGNMENTS_F' THEN
1794 --
1795 ben_whatif_elig.WATIF_ALL_ASSIGNMENTS_F_API(
1796 p_person_id => p_person_id
1797 ,p_PAY_BASIS_ID => l_PAY_BASIS_ID
1798 ,p_LABOUR_UNION_MEMBER_FLAG => l_LABOUR_UNION_MEMBER_FLAG
1799 ,p_JOB_ID => l_JOB_ID
1800 ,p_PAYROLL_ID => l_PAYROLL_ID
1801 ,p_PRIMARY_FLAG => l_PRIMARY_FLAG1
1802 ,p_LOCATION_ID => l_LOCATION_ID
1803 ,p_CHANGE_REASON => l_CHANGE_REASON
1804 ,p_ASSIGNMENT_TYPE => l_ASSIGNMENT_TYPE
1805 ,p_ORGANIZATION_ID => l_ORGANIZATION_ID
1806 ,p_POSITION_ID => l_POSITION_ID
1807 ,p_BARGAINING_UNIT_CODE => l_BARGAINING_UNIT_CODE
1808 ,p_NORMAL_HOURS => l_NORMAL_HOURS
1809 ,p_FREQUENCY => l_FREQUENCY
1810 ,p_ASSIGNMENT_STATUS_TYPE_ID => l_ASSIGNMENT_STATUS_TYPE_ID
1811 ,p_GRADE_ID => l_GRADE_ID
1812 ,p_EMPLOYMENT_CATEGORY => l_EMPLOYMENT_CATEGORY
1813 ,p_PEOPLE_GROUP_ID => l_PEOPLE_GROUP_ID
1814 ,p_HOURLY_SALARIED_CODE => l_HOURLY_SALARIED_CODE
1815 ,p_ASS_ATTRIBUTE_CATEGORY => l_ASS_ATTRIBUTE_CATEGORY
1816 ,p_ASS_ATTRIBUTE1 => l_ASS_ATTRIBUTE1
1817 ,p_ASS_ATTRIBUTE10 => l_ASS_ATTRIBUTE10
1818 ,p_ASS_ATTRIBUTE11 => l_ASS_ATTRIBUTE11
1819 ,p_ASS_ATTRIBUTE12 => l_ASS_ATTRIBUTE12
1820 ,p_ASS_ATTRIBUTE13 => l_ASS_ATTRIBUTE13
1821 ,p_ASS_ATTRIBUTE14 => l_ASS_ATTRIBUTE14
1822 ,p_ASS_ATTRIBUTE15 => l_ASS_ATTRIBUTE15
1823 ,p_ASS_ATTRIBUTE16 => l_ASS_ATTRIBUTE16
1824 ,p_ASS_ATTRIBUTE17 => l_ASS_ATTRIBUTE17
1825 ,p_ASS_ATTRIBUTE18 => l_ASS_ATTRIBUTE18
1826 ,p_ASS_ATTRIBUTE19 => l_ASS_ATTRIBUTE19
1827 ,p_ASS_ATTRIBUTE2 => l_ASS_ATTRIBUTE2
1828 ,p_ASS_ATTRIBUTE20 => l_ASS_ATTRIBUTE20
1829 ,p_ASS_ATTRIBUTE21 => l_ASS_ATTRIBUTE21
1830 ,p_ASS_ATTRIBUTE22 => l_ASS_ATTRIBUTE22
1831 ,p_ASS_ATTRIBUTE23 => l_ASS_ATTRIBUTE23
1832 ,p_ASS_ATTRIBUTE24 => l_ASS_ATTRIBUTE24
1833 ,p_ASS_ATTRIBUTE25 => l_ASS_ATTRIBUTE25
1834 ,p_ASS_ATTRIBUTE26 => l_ASS_ATTRIBUTE26
1835 ,p_ASS_ATTRIBUTE27 => l_ASS_ATTRIBUTE27
1836 ,p_ASS_ATTRIBUTE28 => l_ASS_ATTRIBUTE28
1837 ,p_ASS_ATTRIBUTE29 => l_ASS_ATTRIBUTE29
1838 ,p_ASS_ATTRIBUTE3 => l_ASS_ATTRIBUTE3
1839 ,p_ASS_ATTRIBUTE30 => l_ASS_ATTRIBUTE30
1840 ,p_ASS_ATTRIBUTE4 => l_ASS_ATTRIBUTE4
1841 ,p_ASS_ATTRIBUTE5 => l_ASS_ATTRIBUTE5
1842 ,p_ASS_ATTRIBUTE6 => l_ASS_ATTRIBUTE6
1843 ,p_ASS_ATTRIBUTE7 => l_ASS_ATTRIBUTE7
1844 ,p_ASS_ATTRIBUTE8 => l_ASS_ATTRIBUTE8
1845 ,p_ASS_ATTRIBUTE9 => l_ASS_ATTRIBUTE9
1846 ,p_business_group_id => p_business_group_id
1847 ,p_effective_date => p_effective_date
1848 );
1849 --
1850 ELSIF C1.api_name= 'PER_ALL_PEOPLE_F' THEN
1851 --
1852 ben_whatif_elig.WATIF_ALL_PEOPLE_F_API(
1853 p_person_id => p_person_id
1854 ,p_STUDENT_STATUS => l_STUDENT_STATUS
1855 ,p_DATE_OF_DEATH => l_DATE_OF_DEATH
1856 ,p_DATE_OF_BIRTH => l_DATE_OF_BIRTH
1857 ,p_COORD_BEN_NO_CVG_FLAG => l_COORD_BEN_NO_CVG_FLAG
1858 ,p_COORD_BEN_MED_PLN_NO => l_COORD_BEN_MED_PLN_NO
1859 ,p_PER_INFORMATION10 => l_per_information10
1860 ,p_ON_MILITARY_SERVICE => l_ON_MILITARY_SERVICE
1861 ,p_REGISTERED_DISABLED_FLAG => l_REGISTERED_DISABLED_FLAG
1862 ,p_USES_TOBACCO_FLAG => l_USES_TOBACCO_FLAG
1863 ,p_BENEFIT_GROUP_ID => l_BENEFIT_GROUP_ID
1864 ,p_MARITAL_STATUS => l_MARITAL_STATUS
1865 ,p_ATTRIBUTE1 => l_ATTRIBUTE1
1866 ,p_ATTRIBUTE10 => l_ATTRIBUTE10
1867 ,p_ATTRIBUTE11 => l_ATTRIBUTE11
1868 ,p_ATTRIBUTE12 => l_ATTRIBUTE12
1869 ,p_ATTRIBUTE13 => l_ATTRIBUTE13
1870 ,p_ATTRIBUTE14 => l_ATTRIBUTE14
1871 ,p_ATTRIBUTE15 => l_ATTRIBUTE15
1872 ,p_ATTRIBUTE16 => l_ATTRIBUTE16
1873 ,p_ATTRIBUTE17 => l_ATTRIBUTE17
1874 ,p_ATTRIBUTE18 => l_ATTRIBUTE18
1875 ,p_ATTRIBUTE19 => l_ATTRIBUTE19
1876 ,p_ATTRIBUTE2 => l_ATTRIBUTE2
1877 ,p_ATTRIBUTE20 => l_ATTRIBUTE20
1878 ,p_ATTRIBUTE21 => l_ATTRIBUTE21
1879 ,p_ATTRIBUTE22 => l_ATTRIBUTE22
1880 ,p_ATTRIBUTE23 => l_ATTRIBUTE23
1881 ,p_ATTRIBUTE24 => l_ATTRIBUTE24
1882 ,p_ATTRIBUTE25 => l_ATTRIBUTE25
1883 ,p_ATTRIBUTE26 => l_ATTRIBUTE26
1884 ,p_ATTRIBUTE27 => l_ATTRIBUTE27
1885 ,p_ATTRIBUTE28 => l_ATTRIBUTE28
1886 ,p_ATTRIBUTE29 => l_ATTRIBUTE29
1887 ,p_ATTRIBUTE3 => l_ATTRIBUTE3
1888 ,p_ATTRIBUTE30 => l_ATTRIBUTE30
1889 ,p_ATTRIBUTE4 => l_ATTRIBUTE4
1890 ,p_ATTRIBUTE5 => l_ATTRIBUTE5
1891 ,p_ATTRIBUTE6 => l_ATTRIBUTE6
1892 ,p_ATTRIBUTE7 => l_ATTRIBUTE7
1893 ,p_ATTRIBUTE8 => l_ATTRIBUTE8
1894 ,p_ATTRIBUTE9 => l_ATTRIBUTE9
1895 ,p_DPDNT_VLNTRY_SVCE_FLAG => l_DPDNT_VLNTRY_SVCE_FLAG
1896 ,p_RECEIPT_OF_DEATH_CERT_DATE => l_RECEIPT_OF_DEATH_CERT_DATE
1897 ,p_SEX => l_sex
1898 ,p_business_group_id => p_business_group_id
1899 ,p_effective_date => p_effective_date
1900 );
1901 --
1902 ELSIF C1.api_name= 'PER_CONTACT_RELATIONSHIPS' THEN
1903 ben_whatif_elig.WATIF_CONTACT_RELATIONSHIP_API(
1904 p_person_id => p_person_id
1905 ,p_contact_person_id => l_contact_person_id
1906 ,p_DATE_END => l_DATE_END1
1907 ,p_DATE_START => nvl(l_DATE_START1,p_effective_date)
1908 ,p_CONTACT_TYPE => l_CONTACT_TYPE
1909 ,p_END_LIFE_REASON_ID => l_END_LIFE_REASON_ID
1910 ,p_PERSONAL_FLAG => l_PERSONAL_FLAG
1911 ,p_START_LIFE_REASON_ID => l_START_LIFE_REASON_ID
1912 ,p_RLTD_PER_RSDS_W_DSGNTR_FLAG => nvl(l_RLTD_PER_RSDS_W_DSGNTR_FLAG,'Y')
1913 ,p_business_group_id => p_business_group_id
1914 ,p_effective_date => p_effective_date
1915 );
1916 --
1917 ELSIF C1.api_name= 'PER_PERIODS_OF_SERVICE' THEN
1918 --
1919 if l_LEAVING_REASON is not null and
1920 l_ACTUAL_TERMINATION_DATE is null then
1921 l_ACTUAL_TERMINATION_DATE := p_session_date;
1922 end if;
1923 if l_ACTUAL_TERMINATION_DATE is not null then
1924 p_lf_evt_ocrd_dt := l_ACTUAL_TERMINATION_DATE;
1925 end if;
1926 --
1927 ben_whatif_elig.WATIF_PERIODS_OF_SERVICE_API(
1928 p_person_id =>p_person_id
1929 ,p_per_object_version_number => null
1930 ,p_DATE_START => l_DATE_START2
1931 ,p_LEAVING_REASON => l_LEAVING_REASON
1932 ,p_ADJUSTED_SVC_DATE => l_ADJUSTED_SVC_DATE
1933 ,p_ACTUAL_TERMINATION_DATE => l_ACTUAL_TERMINATION_DATE
1934 ,p_FINAL_PROCESS_DATE => l_FINAL_PROCESS_DATE
1935 ,p_ATTRIBUTE1 => l_PDS_ATTRIBUTE1
1936 ,p_ATTRIBUTE2 => l_PDS_ATTRIBUTE2
1937 ,p_ATTRIBUTE3 => l_PDS_ATTRIBUTE3
1938 ,p_ATTRIBUTE4 => l_PDS_ATTRIBUTE4
1939 ,p_ATTRIBUTE5 => l_PDS_ATTRIBUTE5
1940 ,p_ATTRIBUTE6 => l_ATTRIBUTE6
1941 ,p_ATTRIBUTE7 => l_ATTRIBUTE7
1942 ,p_ATTRIBUTE8 => l_ATTRIBUTE8
1943 ,p_ATTRIBUTE9 => l_ATTRIBUTE9
1944 ,p_ATTRIBUTE10 => l_ATTRIBUTE10
1945 ,p_ATTRIBUTE11 => l_ATTRIBUTE11
1946 ,p_ATTRIBUTE12 => l_ATTRIBUTE12
1947 ,p_ATTRIBUTE13 => l_ATTRIBUTE13
1948 ,p_ATTRIBUTE14 => l_ATTRIBUTE14
1949 ,p_ATTRIBUTE15 => l_ATTRIBUTE15
1950 ,p_ATTRIBUTE16 => l_ATTRIBUTE16
1951 ,p_ATTRIBUTE17 => l_ATTRIBUTE17
1952 ,p_ATTRIBUTE18 => l_ATTRIBUTE18
1953 ,p_ATTRIBUTE19 => l_ATTRIBUTE19
1954 ,p_ATTRIBUTE20 => l_ATTRIBUTE20
1955 ,p_business_group_id => p_business_group_id
1956 ,p_effective_date => p_effective_date
1957 );
1958 --
1959 ELSIF C1.api_name='PER_PERSON_TYPE_USAGES_F' THEN
1960 --
1961 ben_whatif_elig.WATIF_PERSON_TYPE_USAGES_F_API(
1962 p_person_id => p_person_id
1963 ,p_PERSON_TYPE_ID => l_PERSON_TYPE_ID
1964 ,p_business_group_id => p_business_group_id
1965 ,p_effective_date => p_effective_date
1966 );
1967 --
1968 ELSIF C1.api_name= 'BEN_PER_BNFTS_BAL_F' then
1969 --
1970 ben_whatif_elig.WATIF_PER_BNFTS_BAL_F_API(
1971 p_person_id => p_person_id
1972 ,p_business_group_id => p_business_group_id
1973 ,p_effective_date => p_effective_date
1974 ,p_BNFTS_BAL_ID => l_BNFTS_BAL_ID
1975 ,p_VAL => l_val
1976 ,p_EFFECTIVE_START_DATE => l_effective_start_date
1977 ,p_EFFECTIVE_END_DATE => l_effective_end_date
1978 );
1979 --
1980 ELSIF C1.api_name= 'BEN_CRT_ORDR' THEN
1981 --
1982 ben_whatif_elig.WATIF_CRT_ORDR_API(
1983 p_person_id => p_person_id
1984 ,p_pl_id => l_pl_id
1985 ,p_business_group_id => p_business_group_id
1986 ,p_effective_date => p_effective_date
1987 ,p_CRT_ORDR_TYP_CD => l_CRT_ORDR_TYP_CD
1988 ,p_APLS_PERD_STRTG_DT => l_APLS_PERD_STRTG_DT
1989 ,p_APLS_PERD_ENDG_DT => l_APLS_PERD_ENDG_DT
1990 );
1991 --
1992 ELSIF C1.api_name='PER_ASSIG_BUDGET_VALUES_F' THEN
1993 --
1994 ben_whatif_elig.WATIF_PER_ASG_BUDG_VAL_F_API(
1995 p_person_id => p_person_id
1996 ,p_ASSIGNMENT_BUDGET_VALUE_ID => l_ASSIGNMENT_BUDGET_VALUE_ID
1997 ,p_VALUE => l_value
1998 ,p_business_group_id => p_business_group_id
1999 ,p_effective_date => p_effective_date
2000 );
2001 ELSIF C1.api_name= 'PER_QUALIFICATIONS' THEN
2002
2003 ben_whatif_elig.WATIF_PER_QUALIFICATIONS_API(
2004 p_person_id => p_person_id
2005 ,p_qualification_type_id => l_qual_type_id
2006 ,p_title => l_qual_title
2007 ,p_start_date => nvl(l_qual_start_date, p_effective_date)
2008 ,p_end_date => l_qual_end_date
2009 ,p_attribute1 => l_qual_attribute1
2010 ,p_attribute2 => l_qual_attribute2
2011 ,p_attribute3 => l_qual_attribute3
2012 ,p_attribute4 => l_qual_attribute4
2013 ,p_attribute5 => l_qual_attribute5
2014 ,p_attribute6 => l_qual_attribute6
2015 ,p_attribute7 => l_qual_attribute7
2016 ,p_attribute8 => l_qual_attribute8
2017 ,p_attribute9 => l_qual_attribute9
2018 ,p_attribute10 => l_qual_attribute10
2019 ,p_attribute11 => l_qual_attribute11
2020 ,p_attribute12 => l_qual_attribute12
2021 ,p_attribute13 => l_qual_attribute13
2022 ,p_attribute14 => l_qual_attribute14
2023 ,p_attribute15 => l_qual_attribute15
2024 ,p_attribute16 => l_qual_attribute16
2025 ,p_attribute17 => l_qual_attribute17
2026 ,p_attribute18 => l_qual_attribute18
2027 ,p_attribute19 => l_qual_attribute19
2028 ,p_attribute20 => l_qual_attribute20
2029 ,p_business_group_id => p_business_group_id
2030 ,p_effective_date => p_effective_date
2031 );
2032 ELSIF C1.api_name='PER_COMPETENCE_ELEMENTS' THEN
2033
2034 ben_whatif_elig.WATIF_PER_COMPETENCE_API(
2035 p_person_id => p_person_id
2036 ,p_competence_id => l_comp_id
2037 ,p_proficiency_level_id => l_prof_lvl_id
2038 ,p_effective_date_from => nvl(l_comp_eff_date_from, p_effective_date)
2039 ,p_effective_date_to => l_comp_eff_date_to
2040 ,p_attribute1 => l_comp_attribute1
2041 ,p_attribute2 => l_comp_attribute2
2042 ,p_attribute3 => l_comp_attribute3
2043 ,p_attribute4 => l_comp_attribute4
2044 ,p_attribute5 => l_comp_attribute5
2045 ,p_attribute6 => l_comp_attribute6
2046 ,p_attribute7 => l_comp_attribute7
2047 ,p_attribute8 => l_comp_attribute8
2048 ,p_attribute9 => l_comp_attribute9
2049 ,p_attribute10 => l_comp_attribute10
2050 ,p_attribute11 => l_comp_attribute11
2051 ,p_attribute12 => l_comp_attribute12
2052 ,p_attribute13 => l_comp_attribute13
2053 ,p_attribute14 => l_comp_attribute14
2054 ,p_attribute15 => l_comp_attribute15
2055 ,p_attribute16 => l_comp_attribute16
2056 ,p_attribute17 => l_comp_attribute17
2057 ,p_attribute18 => l_comp_attribute18
2058 ,p_attribute19 => l_comp_attribute19
2059 ,p_attribute20 => l_comp_attribute20
2060 ,p_business_group_id => p_business_group_id
2061 ,p_effective_date => p_effective_date
2062 );
2063 ELSIF C1.api_name= 'PER_PERFORMANCE_REVIEWS' THEN
2064
2065 ben_whatif_elig.WATIF_PER_PERFORMANCE_API(
2066 p_person_id => p_person_id
2067 ,p_performance_rating => l_perf_rating
2068 ,p_event_id => l_event_id
2069 ,p_review_date => l_review_date
2070 ,p_attribute1 => l_perf_attribute1
2071 ,p_attribute2 => l_perf_attribute2
2072 ,p_attribute3 => l_perf_attribute3
2073 ,p_attribute4 => l_perf_attribute4
2074 ,p_attribute5 => l_perf_attribute5
2075 ,p_attribute6 => l_perf_attribute6
2076 ,p_attribute7 => l_perf_attribute7
2077 ,p_attribute8 => l_perf_attribute8
2078 ,p_attribute9 => l_perf_attribute9
2079 ,p_attribute10 => l_perf_attribute10
2080 ,p_attribute11 => l_perf_attribute11
2081 ,p_attribute12 => l_perf_attribute12
2082 ,p_attribute13 => l_perf_attribute13
2083 ,p_attribute14 => l_perf_attribute14
2084 ,p_attribute15 => l_perf_attribute15
2085 ,p_attribute16 => l_perf_attribute16
2086 ,p_attribute17 => l_perf_attribute17
2087 ,p_attribute18 => l_perf_attribute18
2088 ,p_attribute19 => l_perf_attribute19
2089 ,p_attribute20 => l_perf_attribute20
2090 ,p_attribute21 => l_perf_attribute21
2091 ,p_attribute22 => l_perf_attribute22
2092 ,p_attribute23 => l_perf_attribute23
2093 ,p_attribute24 => l_perf_attribute24
2094 ,p_attribute25 => l_perf_attribute25
2095 ,p_attribute26 => l_perf_attribute26
2096 ,p_attribute27 => l_perf_attribute27
2097 ,p_attribute28 => l_perf_attribute28
2098 ,p_attribute29 => l_perf_attribute29
2099 ,p_attribute30 => l_perf_attribute30
2100 );
2101 ELSIF C1.api_name= 'PER_PAY_PROPOSALS' THEN
2102
2103 if l_proposed_salary_n is null then
2104 fnd_message.set_name('PER','HR_52401_PYP_NO_PROPOSED_AMT');
2105 fnd_message.raise_error;
2106
2107 end if;
2108 ben_whatif_elig.WATIF_PAY_PROPOSAL_API(
2109 p_person_id => p_person_id
2110 ,p_approved => nvl(l_approved , 'N')
2111 ,p_change_date => nvl(l_change_date,p_effective_date) --6282219
2112 ,p_event_id => null
2113 ,p_forced_ranking => l_forced_ranking
2114 ,p_last_change_date => l_last_change_date
2115 ,p_multiple_components => 'N'
2116 ,p_next_sal_review_date => l_next_sal_review_date
2117 ,p_next_perf_review_date => l_next_perf_review_date
2118 ,p_performance_rating => l_performance_rating
2119 ,p_performance_review_id => l_performance_review_id
2120 ,p_proposal_reason => l_proposal_reason
2121 ,p_proposed_salary_n => l_proposed_salary_n
2122 ,p_review_date => l_pay_review_date
2123 ,p_attribute1 => l_attribute1
2124 ,p_attribute2 => l_attribute2
2125 ,p_attribute3 => l_attribute3
2126 ,p_attribute4 => l_attribute4
2127 ,p_attribute5 => l_attribute5
2128 ,p_attribute6 => l_attribute6
2129 ,p_attribute7 => l_attribute7
2130 ,p_attribute8 => l_attribute8
2131 ,p_attribute9 => l_attribute9
2132 ,p_attribute10 => l_attribute10
2133 ,p_attribute11 => l_attribute11
2134 ,p_attribute12 => l_attribute12
2135 ,p_attribute13 => l_attribute13
2136 ,p_attribute14 => l_attribute14
2137 ,p_attribute15 => l_attribute15
2138 ,p_attribute16 => l_attribute16
2139 ,p_attribute17 => l_attribute17
2140 ,p_attribute18 => l_attribute18
2141 ,p_attribute19 => l_attribute19
2142 ,p_attribute20 => l_attribute20
2143 ,p_business_group_id => p_business_group_id
2144 ,p_effective_date => p_effective_date
2145 );
2146 -- derived data factors
2147 ELSIF C1.api_name = 'DRVDAGE' then
2148 FOR l_rec IN c_ler(p_typ_cd =>'DRVDAGE') LOOP
2149 l_agf_ler_id := l_rec.ler_id;
2150 END LOOP;
2151 p_lf_evt_ocrd_dt := l_lf_evt_ocrd_dt;
2152 ben_whatif_elig.watif_temporal_lf_evt_API(
2153 p_person_id => p_person_id
2154 ,p_ler_ID => l_agf_ler_ID
2155 ,p_temporal_lf_evt => 'AGE'
2156 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2157 ,p_business_group_id => p_business_group_id
2158 ,p_effective_date => l_lf_evt_ocrd_dt
2159 ,p_tpf_val => null
2160 );
2161 ELSIF C1.api_name = 'DRVDLOS' then
2162 FOR l_rec IN c_ler(p_typ_cd =>'DRVDLOS') LOOP
2163 l_los_ler_ID := l_rec.ler_id;
2164 END LOOP;
2165 p_lf_evt_ocrd_dt := l_lf_evt_ocrd_dt;
2166 ben_whatif_elig.watif_temporal_lf_evt_API(
2167 p_person_id => p_person_id
2168 ,p_ler_ID => l_los_ler_ID
2169 ,p_temporal_lf_evt => 'LOS'
2170 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2171 ,p_business_group_id => p_business_group_id
2172 ,p_effective_date => l_lf_evt_ocrd_dt
2173 ,p_tpf_val => null
2174 );
2175 ELSIF C1.api_name = 'DRVDCAL' then
2176 FOR l_rec IN c_ler(p_typ_cd =>'DRVDCAL') LOOP
2177 l_cal_ler_ID := l_rec.ler_id;
2178 END LOOP;
2179 l_lf_evt_ocrd_dt := trunc(p_min_dt(l_lf_evt_ocrd_dt, l_lf_evt_ocrd_dt1)
2180 + abs((l_lf_evt_ocrd_dt - l_lf_evt_ocrd_dt1)/2));
2181 p_lf_evt_ocrd_dt := l_lf_evt_ocrd_dt;
2182 ben_whatif_elig.watif_temporal_lf_evt_API(
2183 p_person_id => p_person_id
2184 ,p_ler_ID => l_cal_ler_ID
2185 ,p_temporal_lf_evt => 'CAL'
2186 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2187 ,p_business_group_id => p_business_group_id
2188 ,p_effective_date => l_lf_evt_ocrd_dt
2189 ,p_tpf_val => null
2190 );
2191 ELSIF C1.api_name = 'DRVDCMP' then
2192 FOR l_rec IN c_ler(p_typ_cd =>'DRVDCMP') LOOP
2193 l_cmp_ler_ID := l_rec.ler_id;
2194 END LOOP;
2195
2196 ben_whatif_elig.watif_temporal_lf_evt_API(
2197 p_person_id => p_person_id
2198 ,p_ler_ID => l_cmp_ler_ID
2199 ,p_temporal_lf_evt => 'CMP'
2200 ,p_lf_evt_ocrd_dt => p_effective_date
2201 ,p_business_group_id => p_business_group_id
2202 ,p_effective_date => p_effective_date
2203 ,p_tpf_val => null
2204 ,p_cmp_val => l_cmp_val
2205 ,p_cmp_bnft_val => l_cmp_bnft_val
2206 ,p_cmp_bal_val => l_cmp_bal_val
2207 );
2208 ELSIF C1.api_name = 'DRVDHRW' then
2209 FOR l_rec IN c_ler(p_typ_cd =>'DRVDHRW') LOOP
2210 l_hrw_ler_ID := l_rec.ler_id;
2211 END LOOP;
2212 ben_whatif_elig.watif_temporal_lf_evt_API(
2213 p_person_id => p_person_id
2214 ,p_ler_ID => l_hrw_ler_ID
2215 ,p_temporal_lf_evt => 'HRW'
2216 ,p_lf_evt_ocrd_dt => p_effective_date
2217 ,p_business_group_id => p_business_group_id
2218 ,p_effective_date => p_effective_date
2219 ,p_tpf_val => null
2220 ,p_hwf_val => l_hrw_val
2221 ,p_hwf_bnft_val => l_hrw_bnft_val
2222 );
2223 ELSIF C1.api_name = 'DRVDTPF' then
2224 FOR l_rec IN c_ler(p_typ_cd =>'DRVDTPF') LOOP
2225 l_tpf_ler_ID:= l_rec.ler_id;
2226 END LOOP;
2227 ben_whatif_elig.watif_temporal_lf_evt_API(
2228 p_person_id => p_person_id
2229 ,p_ler_ID => l_tpf_ler_ID
2230 ,p_temporal_lf_evt => 'TPF'
2231 ,p_lf_evt_ocrd_dt => p_effective_date
2232 ,p_business_group_id => p_business_group_id
2233 ,p_effective_date => p_effective_date
2234 ,p_tpf_val => l_tpf_val
2235 );
2236 END IF; --if else ladder
2237 END LOOP;
2238 if g_debug then
2239 hr_utility.set_location('Leaving: ' || l_proc,100);
2240 end if;
2241 END post_ben_changes;
2242 -----------------------------------------------------------------------------------------------
2243
2244 FUNCTION get_coverage_amt(p_elig_per_elctbl_chc_id IN NUMBER
2245 ,p_business_group_id IN NUMBER)
2246 RETURN NUMBER IS
2247 CURSOR csr_get_cvg_amt IS
2248 SELECT val
2249 FROM ben_enrt_bnft
2250 WHERE elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2251 AND business_group_id = p_business_group_id
2252 AND bnft_typ_cd = 'CVG' ;
2253
2254 l_coverage_amount ben_enrt_bnft.val%TYPE;
2255 l_proc VARCHAR2(72);
2256
2257 BEGIN
2258 --
2259 if g_debug then
2260 l_proc := g_package || 'get_coverage_amt';
2261 hr_utility.set_location('Entering: ' || l_proc,10);
2262 end if;
2263
2264 OPEN csr_get_cvg_amt;
2265 FETCH csr_get_cvg_amt INTO l_coverage_amount;
2266 CLOSE csr_get_cvg_amt;
2267
2268 if g_debug then
2269 hr_utility.set_location('Leaving: ' || l_proc,20);
2270 end if;
2271
2272 RETURN l_coverage_amount;
2273 --
2274 END get_coverage_amt;
2275 -----------------------------------------------------------------------------------------------
2276 PROCEDURE populate_hierarchy(p_person_id IN NUMBER
2277 ,p_business_group_id IN NUMBER
2278 ,p_effective_date IN DATE
2279 ,p_ler_id IN NUMBER) IS
2280 --------------root node label-----------
2281 Cursor csr_root_label IS
2282 Select Meaning
2283 From hr_lookups
2284 Where lookup_type='BEN_SS_DRVD_LABELS'
2285 and lookup_code='COBJ';
2286 ---------------per in ler---------------
2287 CURSOR csr_ler IS
2288 Select pil.per_in_ler_id,
2289 ler.name name,
2290 ler.ler_id
2291 From ben_per_in_ler pil,
2292 ben_ler_f ler
2293 Where pil.person_id = p_person_id
2294 AND ler.ler_id = pil.ler_id
2295 AND p_effective_date BETWEEN ler.effective_start_date and ler.effective_end_date
2296 AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
2297 AND pil.lf_evt_ocrd_dt <= p_effective_date
2298 AND pil.business_group_id = p_business_group_id
2299 AND ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO')
2300 ORDER BY pil.lf_evt_ocrd_dt desc, 1 desc;
2301
2302 --------------------Program------------------
2303 CURSOR csr_pgms(p_per_in_ler_id NUMBER) IS
2304 Select pgm.pgm_id
2305 ,pgm.name
2306 ,pgm.pgm_uom uom
2307 ,pgm.acty_ref_perd_cd acty_ref_perd_cd
2308 ,popl.pil_elctbl_chc_popl_id
2309 From ben_pil_elctbl_chc_popl popl,
2310 ben_pgm_f pgm
2311 Where popl.per_in_ler_id = p_per_in_ler_id
2312 and popl.pgm_id =pgm.pgm_id
2313 and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;
2314
2315 --------------------------Plan type not in program------------------
2316 CURSOR csr_pl_types_not_in_program(p_per_in_ler_id NUMBER) IS
2317 Select distinct pt.pl_typ_id,
2318 pt.name
2319 From ben_pl_typ_f pt,
2320 ben_elig_per_elctbl_chc epe,
2321 ben_pil_elctbl_chc_popl popl
2322 Where pt.pl_typ_id = epe.pl_typ_id
2323 and popl.per_in_ler_id = p_per_in_ler_id
2324 and popl.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
2325 and epe.comp_lvl_cd = 'PLAN'
2326 and popl.pl_id = epe.pl_id
2327 and p_effective_date between pt.effective_start_date and pt.effective_end_date;
2328
2329 ----------------------plan type in program-------------------
2330 /*CURSOR csr_pl_types_in_program(p_pil_elctbl_chc_popl_id NUMBER) IS
2331 Select pt.pl_typ_id
2332 ,pt.name
2333 ,epe.ptip_id
2334 From ben_elig_per_elctbl_chc epe,
2335 ben_pl_typ_f pt
2336 Where epe.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
2337 and epe.pl_typ_id = pt.pl_typ_id
2338 and epe.comp_lvl_cd = 'PLAN'
2339 and p_effective_date between pt.effective_start_date and pt.effective_end_date;*/
2340 CURSOR csr_pl_types_in_program(p_pil_elctbl_chc_popl_id NUMBER) IS
2341 Select pt.pl_typ_id,
2342 pt.name,
2343 ptip.ptip_id
2344 From ben_pl_typ_f pt,
2345 ben_ptip_f ptip
2346 Where
2347 ptip.pl_typ_id=pt.pl_typ_id
2348 and pt.pl_typ_id in ( Select epe.pl_typ_id
2349 From ben_elig_per_elctbl_chc epe
2350 Where epe.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
2351 and epe.comp_lvl_cd = 'PLAN'
2352 and epe.pgm_id=ptip.pgm_id
2353 )
2354 and p_effective_date between pt.effective_start_date and pt.effective_end_date
2355 and p_effective_date between ptip.effective_start_date and ptip.effective_end_date;
2356
2357 ----------------plans----------------(IN pgm_id and pl_typ_id)------------
2358 CURSOR csr_plans(p_per_in_ler_id NUMBER
2359 ,p_pl_typ_id NUMBER
2360 ,p_pgm_id NUMBER) IS
2361 Select epe.pl_id,
2362 epe.plip_id,
2363 pl.name name,
2364 pl.nip_pl_uom uom,
2365 pl.nip_acty_ref_perd_cd acty_ref_perd_cd,
2366 epe.elctbl_flag,
2367 epe.elig_per_elctbl_chc_id
2368 From ben_pl_f pl,
2369 ben_elig_per_elctbl_chc epe
2370 Where
2371 epe.per_in_ler_id = p_per_in_ler_id
2372 and epe.comp_lvl_cd = 'PLAN'
2373 and epe.pl_id = pl.pl_id
2374 and epe.pl_typ_id = p_pl_typ_id
2375 and nvl(epe.pgm_id ,-1) = p_pgm_id
2376 and p_effective_date between pl.effective_start_date and effective_end_date;
2377 ----------------------------options-------------------------------------
2378 CURSOR csr_options_in_plip(p_per_in_ler_id NUMBER, p_plip_id NUMBER) IS
2379 Select opt.opt_id,
2380 oipl.oipl_id,
2381 opt.name,
2382 epe.elctbl_flag,
2383 epe.elig_per_elctbl_chc_id
2384 From ben_elig_per_elctbl_chc epe,
2385 ben_oipl_f oipl,
2386 ben_opt_f opt
2387 Where epe.per_in_ler_id = p_per_in_ler_id
2388 AND epe.plip_id = p_plip_id
2389 AND epe.comp_lvl_cd = 'OIPL'
2390 AND epe.oipl_id = oipl.oipl_id
2391 AND oipl.opt_id = opt.opt_id
2392 AND p_effective_date between oipl.effective_start_date and oipl.effective_end_date
2393 AND p_effective_date between opt.effective_start_date and opt.effective_end_date;
2394
2395 CURSOR csr_options_in_pnip(p_per_in_ler_id NUMBER, p_pl_id NUMBER) IS
2396 Select opt.opt_id,
2397 oipl.oipl_id,
2398 opt.name,
2399 epe.elctbl_flag,
2400 epe.elig_per_elctbl_chc_id
2401 From ben_elig_per_elctbl_chc epe,
2402 ben_oipl_f oipl,
2403 ben_opt_f opt
2404 Where epe.per_in_ler_id = p_per_in_ler_id
2405 AND epe.plip_id is NULL and epe.pl_id=p_pl_id
2406 AND epe.comp_lvl_cd = 'OIPL'
2407 AND epe.oipl_id = oipl.oipl_id
2408 AND oipl.opt_id = opt.opt_id
2409 AND p_effective_date between oipl.effective_start_date and oipl.effective_end_date
2410 AND p_effective_date between opt.effective_start_date and opt.effective_end_date;
2411 -----------------------------------rates------------------------------------------------
2412 CURSOR csr_rates (p_elig_per_elctbl_chc_id NUMBER) IS
2413 Select abr.name,
2414 abr.acty_base_rt_id,
2415 ecr.enrt_rt_id,
2416 decode(ecr.entr_val_at_enrt_flag, 'Y', ecr.dflt_val, ecr.val) val,
2417 ecr.cmcd_val,
2418 ecr.cmcd_acty_ref_perd_cd,
2419 ecr.nnmntry_uom
2420 From ben_enrt_rt ecr,
2421 ben_acty_base_rt_f abr
2422 Where ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2423 AND ecr.dsply_on_enrt_flag = 'Y'
2424 AND ecr.enrt_bnft_id IS NULL
2425 AND abr.acty_base_rt_id = ecr.acty_base_rt_id
2426 AND p_effective_date BETWEEN abr.effective_start_date AND abr.effective_end_date
2427
2428 UNION ALL
2429 Select abr.name,
2430 abr.acty_base_rt_id,
2431 ecr.enrt_rt_id,
2432 decode(ecr.entr_val_at_enrt_flag, 'Y', ecr.dflt_val, ecr.val) val,
2433 ecr.cmcd_val,
2434 ecr.cmcd_acty_ref_perd_cd,
2435 ecr.nnmntry_uom
2436 From ben_enrt_rt ecr,
2437 ben_enrt_bnft enb,
2438 ben_acty_base_rt_f abr
2439 Where enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2440 AND ecr.dsply_on_enrt_flag = 'Y'
2441 AND enb.enrt_bnft_id = ecr.enrt_bnft_id
2442 AND abr.acty_base_rt_id = ecr.acty_base_rt_id
2443 AND p_effective_date between abr.effective_start_date and abr.effective_end_date;
2444
2445
2446 --
2447 l_per_in_ler_id NUMBER;
2448 l_counter NUMBER := 1;
2449 l_hierarchy_id NUMBER;
2450 l_proc VARCHAR2(72);
2451
2452 BEGIN
2453 --
2454 if g_debug then
2455 l_proc := g_package || '.populate_hierarchy';
2456 hr_utility.set_location('Entering: ' || l_proc,10);
2457 end if;
2458
2459
2460 OPEN csr_ler ;
2461 FETCH csr_ler INTO l_per_in_ler_id
2462 ,g_hierarchy(l_counter).name
2463 ,l_hierarchy_id ;
2464 CLOSE csr_ler;
2465 OPEN csr_root_label;
2466 FETCH csr_root_label into g_hierarchy(l_counter).name;
2467 CLOSE csr_root_label;
2468
2469 g_hierarchy(l_counter).person_id := p_person_id;
2470 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2471 g_hierarchy(l_counter).ler_id := p_ler_id;
2472 g_hierarchy(l_counter).hierarchy_id := l_hierarchy_id;
2473 g_hierarchy(l_counter).hierarchy_type := 'LE';
2474 g_hierarchy(l_counter).parent_hierarchy_id := -9999;
2475 g_hierarchy(l_counter).parent_hierarchy_type := NULL;
2476
2477 FOR csr_pgm_rec IN csr_pgms(l_per_in_ler_id) LOOP
2478 l_counter := l_counter + 1;
2479 g_hierarchy(l_counter).hierarchy_id := csr_pgm_rec.pgm_id;
2480 g_hierarchy(l_counter).name := csr_pgm_rec.name;
2481 g_hierarchy(l_counter).parent_hierarchy_id := l_hierarchy_id;
2482 g_hierarchy(l_counter).parent_hierarchy_type := 'LE';
2483 g_hierarchy(l_counter).hierarchy_type := 'PGM';
2484 g_hierarchy(l_counter).person_id := p_person_id;
2485 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2486 g_hierarchy(l_counter).ler_id := p_ler_id;
2487 g_hierarchy(l_counter).uom := csr_pgm_rec.uom;
2488 g_hierarchy(l_counter).acty_ref_perd_cd := csr_pgm_rec.acty_ref_perd_cd;
2489 FOR csr_pgm_pt_rec IN csr_pl_types_in_program(csr_pgm_rec.pil_elctbl_chc_popl_id) LOOP
2490 l_counter := l_counter + 1;
2491 g_hierarchy(l_counter).hierarchy_type := 'PTIP';
2492 g_hierarchy(l_counter).person_id := p_person_id;
2493 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2494 g_hierarchy(l_counter).ler_id := p_ler_id;
2495 g_hierarchy(l_counter).hierarchy_id := csr_pgm_pt_rec.ptip_id;
2496 g_hierarchy(l_counter).name := csr_pgm_pt_rec.name;
2497 g_hierarchy(l_counter).parent_hierarchy_id := csr_pgm_rec.pgm_id;
2498 g_hierarchy(l_counter).parent_hierarchy_type := 'PGM';
2499
2500 FOR csr_plans_in_program_rec IN csr_plans(l_per_in_ler_id, csr_pgm_pt_rec.pl_typ_id, csr_pgm_rec.pgm_id ) LOOP
2501 l_counter := l_counter + 1;
2502 g_hierarchy(l_counter).hierarchy_type := 'PLIP';
2503 g_hierarchy(l_counter).person_id := p_person_id;
2504 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2505 g_hierarchy(l_counter).ler_id := p_ler_id;
2506 g_hierarchy(l_counter).hierarchy_id := csr_plans_in_program_rec.plip_id;
2507 g_hierarchy(l_counter).name := csr_plans_in_program_rec.name;
2508 g_hierarchy(l_counter).parent_hierarchy_id := csr_pgm_pt_rec.ptip_id;
2509 g_hierarchy(l_counter).parent_hierarchy_type := 'PTIP';
2510 g_hierarchy(l_counter).uom := csr_pgm_rec.uom;
2511 g_hierarchy(l_counter).acty_ref_perd_cd := NULL;
2512 g_hierarchy(l_counter).crrnt_elctbl_flag := csr_plans_in_program_rec.elctbl_flag;
2513 g_hierarchy(l_counter).crrnt_cvg_val := get_coverage_amt(csr_plans_in_program_rec.elig_per_elctbl_chc_id,p_business_group_id);
2514 FOR csr_rates_rec IN csr_rates(csr_plans_in_program_rec.elig_per_elctbl_chc_id) LOOP
2515 l_counter := l_counter + 1;
2516 g_hierarchy(l_counter).hierarchy_type := 'ERT';
2517 g_hierarchy(l_counter).person_id := p_person_id;
2518 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2519 g_hierarchy(l_counter).ler_id := p_ler_id;
2520 g_hierarchy(l_counter).hierarchy_id := csr_rates_rec.acty_base_rt_id;
2521 g_hierarchy(l_counter).name := csr_rates_rec.name;
2522 g_hierarchy(l_counter).parent_hierarchy_id := csr_plans_in_program_rec.plip_id;
2523 g_hierarchy(l_counter).parent_hierarchy_type := 'PLIP';
2524 g_hierarchy(l_counter).uom := csr_pgm_rec.uom;
2525 g_hierarchy(l_counter).nnmntry_uom := csr_rates_rec.nnmntry_uom;
2526 g_hierarchy(l_counter).crrnt_val := csr_rates_rec.val;
2527 g_hierarchy(l_counter).crrnt_cmcd_val := csr_rates_rec.cmcd_val;
2528 g_hierarchy(l_counter).crrnt_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
2529 END LOOP;--rate in plan in program
2530 FOR csr_options_plip_rec IN csr_options_in_plip(l_per_in_ler_id, csr_plans_in_program_rec.plip_id) LOOP
2531 l_counter := l_counter + 1;
2532 g_hierarchy(l_counter).hierarchy_type := 'OIPLIP';
2533 g_hierarchy(l_counter).person_id := p_person_id;
2534 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2535 g_hierarchy(l_counter).ler_id := p_ler_id;
2536 g_hierarchy(l_counter).hierarchy_id := csr_options_plip_rec.oipl_id;
2537 g_hierarchy(l_counter).name := csr_options_plip_rec.name;
2538 g_hierarchy(l_counter).parent_hierarchy_id := csr_plans_in_program_rec.plip_id;
2539 g_hierarchy(l_counter).parent_hierarchy_type := 'PLIP';
2540 g_hierarchy(l_counter).uom := csr_pgm_rec.uom;
2541 g_hierarchy(l_counter).crrnt_elctbl_flag := csr_options_plip_rec.elctbl_flag;
2542 g_hierarchy(l_counter).crrnt_cvg_val := get_coverage_amt(csr_options_plip_rec.elig_per_elctbl_chc_id,p_business_group_id);
2543 FOR csr_rates_rec IN csr_rates( csr_options_plip_rec.elig_per_elctbl_chc_id) LOOP
2544 l_counter := l_counter + 1;
2545 g_hierarchy(l_counter).hierarchy_type := 'ERT';
2546 g_hierarchy(l_counter).person_id := p_person_id;
2547 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2548 g_hierarchy(l_counter).ler_id := p_ler_id;
2549 g_hierarchy(l_counter).hierarchy_id := csr_rates_rec.acty_base_rt_id;
2550 g_hierarchy(l_counter).name := csr_rates_rec.name;
2551 g_hierarchy(l_counter).parent_hierarchy_id := csr_options_plip_rec.oipl_id;
2552 g_hierarchy(l_counter).parent_hierarchy_type := 'OIPLIP';
2553 g_hierarchy(l_counter).uom := csr_pgm_rec.uom;
2554 g_hierarchy(l_counter).nnmntry_uom := csr_rates_rec.nnmntry_uom;
2555 g_hierarchy(l_counter).crrnt_val := csr_rates_rec.val;
2556 g_hierarchy(l_counter).crrnt_cmcd_val := csr_rates_rec.cmcd_val;
2557 g_hierarchy(l_counter).crrnt_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
2558 END LOOP;-- rate in option in plan in program
2559 END LOOP; -- option in plan in program
2560
2561 END LOOP; -- plan in program
2562 END LOOP; -- plan type in program
2563 END LOOP; -- program
2564 FOR csr_pt_not_in_pgm_rec IN csr_pl_types_not_in_program(l_per_in_ler_id ) LOOP
2565 l_counter := l_counter + 1;
2566 g_hierarchy(l_counter).hierarchy_type := 'PT';
2567 g_hierarchy(l_counter).person_id := p_person_id;
2568 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2569 g_hierarchy(l_counter).ler_id := p_ler_id;
2570 g_hierarchy(l_counter).hierarchy_id := csr_pt_not_in_pgm_rec.pl_typ_id;
2571 g_hierarchy(l_counter).name := csr_pt_not_in_pgm_rec.name;
2572 g_hierarchy(l_counter).parent_hierarchy_id := l_hierarchy_id;
2573 g_hierarchy(l_counter).parent_hierarchy_type := 'LE';
2574 FOR csr_plans_not_in_program_rec IN csr_plans(l_per_in_ler_id , csr_pt_not_in_pgm_rec.pl_typ_id,-1) LOOP
2575 l_counter := l_counter + 1;
2576 g_hierarchy(l_counter).hierarchy_type := 'PNIP';
2577 g_hierarchy(l_counter).person_id := p_person_id;
2578 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2579 g_hierarchy(l_counter).ler_id := p_ler_id;
2580 g_hierarchy(l_counter).hierarchy_id := csr_plans_not_in_program_rec.pl_id;
2581 g_hierarchy(l_counter).name := csr_plans_not_in_program_rec.name;
2582 g_hierarchy(l_counter).parent_hierarchy_id := csr_pt_not_in_pgm_rec.pl_typ_id;
2583 g_hierarchy(l_counter).parent_hierarchy_type := 'PT';
2584 g_hierarchy(l_counter).uom := csr_plans_not_in_program_rec.uom;
2585 g_hierarchy(l_counter).acty_ref_perd_cd := csr_plans_not_in_program_rec.acty_ref_perd_cd;
2586 g_hierarchy(l_counter).crrnt_elctbl_flag := csr_plans_not_in_program_rec.elctbl_flag;
2587 g_hierarchy(l_counter).crrnt_cvg_val := get_coverage_amt(csr_plans_not_in_program_rec.elig_per_elctbl_chc_id,p_business_group_id);
2588 FOR csr_rates_rec IN csr_rates( csr_plans_not_in_program_rec.elig_per_elctbl_chc_id) LOOP
2589 l_counter := l_counter + 1;
2590 g_hierarchy(l_counter).hierarchy_type := 'ERT';
2591 g_hierarchy(l_counter).person_id := p_person_id;
2592 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2593 g_hierarchy(l_counter).ler_id := p_ler_id;
2594 g_hierarchy(l_counter).hierarchy_id := csr_rates_rec.acty_base_rt_id;
2595 g_hierarchy(l_counter).name := csr_rates_rec.name;
2596 g_hierarchy(l_counter).parent_hierarchy_id := csr_plans_not_in_program_rec.pl_id;
2597 g_hierarchy(l_counter).parent_hierarchy_type := 'PNIP';
2598 g_hierarchy(l_counter).uom := csr_plans_not_in_program_rec.uom;
2599 g_hierarchy(l_counter).nnmntry_uom := csr_rates_rec.nnmntry_uom;
2600 g_hierarchy(l_counter).crrnt_val := csr_rates_rec.val;
2601 g_hierarchy(l_counter).crrnt_cmcd_val := csr_rates_rec.cmcd_val;
2602 g_hierarchy(l_counter).crrnt_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
2603 END LOOP;
2604 FOR csr_options_pnip_rec IN csr_options_in_pnip(l_per_in_ler_id ,csr_plans_not_in_program_rec.pl_id) LOOP
2605 l_counter := l_counter + 1;
2606 g_hierarchy(l_counter).hierarchy_type := 'OIPNIP';
2607 g_hierarchy(l_counter).person_id := p_person_id;
2608 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2609 g_hierarchy(l_counter).ler_id := p_ler_id;
2610 g_hierarchy(l_counter).hierarchy_id := csr_options_pnip_rec.oipl_id;
2611 g_hierarchy(l_counter).name := csr_options_pnip_rec.name;
2612 g_hierarchy(l_counter).parent_hierarchy_id := csr_plans_not_in_program_rec.pl_id;
2613 g_hierarchy(l_counter).parent_hierarchy_type := 'PNIP';
2614 g_hierarchy(l_counter).uom := csr_plans_not_in_program_rec.uom;
2615 g_hierarchy(l_counter).crrnt_elctbl_flag := csr_options_pnip_rec.elctbl_flag;
2616 g_hierarchy(l_counter).crrnt_cvg_val := get_coverage_amt(csr_options_pnip_rec.elig_per_elctbl_chc_id,p_business_group_id);
2617 FOR csr_rates_rec IN csr_rates( csr_options_pnip_rec.elig_per_elctbl_chc_id) LOOP
2618 l_counter := l_counter + 1;
2619 g_hierarchy(l_counter).hierarchy_type := 'ERT';
2620 g_hierarchy(l_counter).person_id := p_person_id;
2621 g_hierarchy(l_counter).business_group_id := p_business_group_id;
2622 g_hierarchy(l_counter).ler_id := p_ler_id;
2623 g_hierarchy(l_counter).hierarchy_id := csr_rates_rec.acty_base_rt_id;
2624 g_hierarchy(l_counter).name := csr_rates_rec.name;
2625 g_hierarchy(l_counter).parent_hierarchy_id := csr_options_pnip_rec.oipl_id;
2626 g_hierarchy(l_counter).parent_hierarchy_type := 'OIPNIP';
2627 g_hierarchy(l_counter).uom := csr_plans_not_in_program_rec.uom;
2628 g_hierarchy(l_counter).nnmntry_uom := csr_rates_rec.nnmntry_uom;
2629 g_hierarchy(l_counter).crrnt_val := csr_rates_rec.val;
2630 g_hierarchy(l_counter).crrnt_cmcd_val := csr_rates_rec.cmcd_val;
2631 g_hierarchy(l_counter).crrnt_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
2632 END LOOP;-- rate in option in plan not in program
2633 END LOOP; --option in plan not in program
2634
2635 END LOOP; -- plan not in program
2636 END LOOP; -- plan type not in program
2637
2638 if g_debug then
2639 hr_utility.set_location('Leaving: ' || l_proc,20);
2640 end if;
2641 --
2642 END populate_hierarchy;
2643 -----------------------------------------------------------------------------------------------
2644
2645 FUNCTION get_ler_index RETURN NUMBER IS
2646 l_index NUMBER := 1;
2647 l_proc VARCHAR2(72);
2648 BEGIN
2649 --
2650 if g_debug then
2651 l_proc := g_package || 'get_ler_index';
2652 hr_utility.set_location('Entering: ' || l_proc,10);
2653 end if;
2654
2655 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
2656 IF g_hierarchy(i).hierarchy_type = 'LE' THEN
2657 l_index := i;
2658 EXIT;
2659 END IF;
2660 END LOOP;
2661
2662 if g_debug then
2663 hr_utility.set_location('Leaving: ' || l_proc,20);
2664 end if;
2665
2666 RETURN l_index;
2667 END;
2668 -----------------------------------------------------------------------------------------------
2669
2670 FUNCTION chk_pl_typ_exists(p_pl_typ_id IN NUMBER)
2671 RETURN NUMBER IS
2672 l_index NUMBER := 0;
2673 l_proc VARCHAR2(72);
2674 BEGIN
2675 --
2676 if g_debug then
2677 l_proc := g_package || 'chk_pl_typ_exists';
2678 hr_utility.set_location('Entering: ' || l_proc,10);
2679 end if;
2680
2681 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
2682 IF g_hierarchy(i).hierarchy_id = p_pl_typ_id AND g_hierarchy(i).hierarchy_type = 'PT'
2683 AND g_hierarchy(i).parent_hierarchy_type = 'LE' THEN
2684 l_index := i;
2685 EXIT;
2686 END IF;
2687 END LOOP;
2688
2689 if g_debug then
2690 hr_utility.set_location('Leaving: ' || l_proc,20);
2691 end if;
2692
2693 RETURN l_index;
2694 END;
2695 -----------------------------------------------------------------------------------------------
2696
2697 FUNCTION chk_pgm_exists(p_pgm_id IN NUMBER)
2698 RETURN NUMBER IS
2699 --
2700 l_index NUMBER := 0;
2701 l_proc VARCHAR2(72);
2702 BEGIN
2703 --
2704 if g_debug then
2705 l_proc := g_package || 'chk_pgm_exists';
2706 hr_utility.set_location('Entering: ' || l_proc,10);
2707 end if;
2708
2709 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
2710 IF g_hierarchy(i).parent_hierarchy_type = 'LE'
2711 AND g_hierarchy(i).hierarchy_id = p_pgm_id AND g_hierarchy(i).hierarchy_type = 'PGM' THEN
2712 l_index := i;
2713 EXIT;
2714 END IF;
2715 END LOOP;
2716
2717 if g_debug then
2718 hr_utility.set_location('Leaving: ' || l_proc,20);
2719 end if;
2720
2721 RETURN l_index;
2722 END;
2723 -----------------------------------------------------------------------------------------------
2724
2725 FUNCTION chk_pt_in_pgm_exists(p_pgm_id IN NUMBER
2726 ,p_ptip_id IN NUMBER
2727 )
2728 RETURN NUMBER IS
2729 l_index NUMBER := 0;
2730 l_proc VARCHAR2(72);
2731 BEGIN
2732 --
2733 if g_debug then
2734 l_proc := g_package || '.chk_pt_in_pgm_exists';
2735 hr_utility.set_location('Entering: ' || l_proc,10);
2736 end if;
2737
2738 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
2739 IF g_hierarchy(i).hierarchy_id = p_ptip_id AND g_hierarchy(i).hierarchy_type = 'PTIP'
2740 AND g_hierarchy(i).parent_hierarchy_type = 'PGM' AND g_hierarchy(i).parent_hierarchy_id = p_pgm_id THEN
2741 l_index := i;
2742 EXIT;
2743 END IF;
2744 END LOOP;
2745
2746 if g_debug then
2747 hr_utility.set_location('Leaving: ' || l_proc,20);
2748 end if;
2749
2750 RETURN l_index;
2751 END;
2752 -----------------------------------------------------------------------------------------------
2753
2754 FUNCTION chk_plip_exists(p_ptip_id IN NUMBER
2755 ,p_pl_id IN NUMBER)
2756 RETURN NUMBER IS
2757 --
2758 l_index NUMBER := 0;
2759 l_proc VARCHAR2(72);
2760 BEGIN
2761 --
2762 if g_debug then
2763 l_proc := g_package || 'chk_plip_exists';
2764 hr_utility.set_location('Entering: ' || l_proc,10);
2765 end if;
2766
2767 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
2768 IF g_hierarchy(i).parent_hierarchy_id = p_ptip_id AND g_hierarchy(i).parent_hierarchy_type = 'PTIP'
2769 AND g_hierarchy(i).hierarchy_id = p_pl_id AND g_hierarchy(i).hierarchy_type = 'PLIP' THEN
2770 l_index := i;
2771 EXIT;
2772 END IF;
2773 END LOOP;
2774
2775 if g_debug then
2776 hr_utility.set_location('Leaving: ' || l_proc,20);
2777 end if;
2778
2779 RETURN l_index;
2780 END;
2781 -----------------------------------------------------------------------------------------------
2782
2783 FUNCTION chk_pnip_exists(p_pl_typ_id IN NUMBER
2784 ,p_pl_id IN NUMBER)
2785 RETURN NUMBER IS
2786 l_index NUMBER := 0;
2787 l_proc VARCHAR2(72);
2788
2789 BEGIN
2790 --
2791 if g_debug then
2792 l_proc := g_package || 'chk_pnip_exists';
2793 hr_utility.set_location('Entering: ' || l_proc,10);
2794 end if;
2795
2796 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
2797 IF g_hierarchy(i).parent_hierarchy_id = p_pl_typ_id AND g_hierarchy(i).parent_hierarchy_type = 'PT'
2798 AND g_hierarchy(i).hierarchy_id = p_pl_id AND g_hierarchy(i).hierarchy_type = 'PNIP' THEN
2799 l_index := i;
2800 EXIT;
2801 END IF;
2802 END LOOP;
2803
2804 if g_debug then
2805 hr_utility.set_location('Leaving: ' || l_proc,20);
2806 end if;
2807
2808 RETURN l_index;
2809 END;
2810 -----------------------------------------------------------------------------------------------
2811
2812 FUNCTION chk_opt_in_plip_exists(p_pl_id IN NUMBER
2813 ,p_opt_id IN NUMBER)
2814 RETURN NUMBER IS
2815 --
2816 l_index NUMBER := 0;
2817 l_proc VARCHAR2(72);
2818
2819 BEGIN
2820 --
2821 if g_debug then
2822 l_proc := g_package || 'chk_opt_in_plip_exists';
2823 hr_utility.set_location('Entering: ' || l_proc,10);
2824 end if;
2825
2826 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
2827 IF g_hierarchy(i).parent_hierarchy_id = p_pl_id AND g_hierarchy(i).parent_hierarchy_type = 'PLIP'
2828 AND g_hierarchy(i).hierarchy_id = p_opt_id AND g_hierarchy(i).hierarchy_type = 'OIPLIP' THEN
2829 l_index := i;
2830 EXIT;
2831 END IF;
2832 END LOOP;
2833
2834 if g_debug then
2835 hr_utility.set_location('Leaving: ' || l_proc,20);
2836 end if;
2837
2838 RETURN l_index;
2839 END;
2840 -----------------------------------------------------------------------------------------------
2841
2842 FUNCTION chk_opt_in_pnip_exists(p_pl_id IN NUMBER
2843 ,p_opt_id IN NUMBER)
2844 RETURN NUMBER IS
2845 --
2846 l_index NUMBER := 0;
2847 l_proc VARCHAR2(72);
2848
2849 BEGIN
2850 --
2851 if g_debug then
2852 l_proc := g_package || 'chk_opt_in_pnip_exists';
2853 hr_utility.set_location('Entering: ' || l_proc,10);
2854 end if;
2855
2856 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
2857 IF g_hierarchy(i).parent_hierarchy_id = p_pl_id AND g_hierarchy(i).parent_hierarchy_type = 'PNIP'
2858 AND g_hierarchy(i).hierarchy_id = p_opt_id AND g_hierarchy(i).hierarchy_type = 'OIPNIP' THEN
2859 l_index := i;
2860 EXIT;
2861 END IF;
2862 END LOOP;
2863
2864 if g_debug then
2865 hr_utility.set_location('Leaving: ' || l_proc,20);
2866 end if;
2867
2868 RETURN l_index;
2869 END;
2870 -----------------------------------------------------------------------------------------------
2871
2872 FUNCTION chk_rate_exists(p_opt_id IN NUMBER
2873 ,p_acty_base_rt_id IN NUMBER )
2874 RETURN NUMBER IS
2875 --
2876 l_index NUMBER := 0;
2877 l_proc VARCHAR2(72);
2878
2879 BEGIN
2880 --
2881 if g_debug then
2882 l_proc := g_package || 'chk_rate_exists';
2883 hr_utility.set_location('Entering: ' || l_proc,10);
2884 end if;
2885
2886 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
2887 IF g_hierarchy(i).parent_hierarchy_id = p_opt_id AND (g_hierarchy(i).parent_hierarchy_type = 'OIPLIP' OR g_hierarchy(i).parent_hierarchy_type = 'OIPNIP')
2888 AND g_hierarchy(i).hierarchy_id = p_acty_base_rt_id AND g_hierarchy(i).hierarchy_type = 'ERT' THEN
2889 l_index := i;
2890 EXIT;
2891 END IF;
2892 END LOOP;
2893
2894 if g_debug then
2895 hr_utility.set_location('Leaving: ' || l_proc,20);
2896 end if;
2897
2898 RETURN l_index;
2899 END;
2900 -----------------------------------------------------------------------------------------------
2901
2902 FUNCTION chk_pl_rate_exists(p_pl_id IN NUMBER
2903 ,p_acty_base_rt_id IN NUMBER )
2904 RETURN NUMBER IS
2905
2906 l_index NUMBER := 0;
2907 l_proc VARCHAR2(72);
2908
2909 BEGIN
2910 --
2911 if g_debug then
2912 l_proc := g_package || 'chk_pl_rate_exists';
2913 hr_utility.set_location('Entering: ' || l_proc,10);
2914 end if;
2915
2916 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
2917 IF g_hierarchy(i).parent_hierarchy_id = p_pl_id AND (g_hierarchy(i).parent_hierarchy_type = 'PLIP' OR g_hierarchy(i).parent_hierarchy_type = 'PNIP')
2918 AND g_hierarchy(i).hierarchy_id = p_acty_base_rt_id AND g_hierarchy(i).hierarchy_type = 'ERT' THEN
2919 l_index := i;
2920 EXIT;
2921 END IF;
2922 END LOOP;
2923
2924 if g_debug then
2925 hr_utility.set_location('Leaving: ' || l_proc,20);
2926 end if;
2927
2928 RETURN l_index;
2929 END;
2930 -----------------------------------------------------------------------------------------------
2931 PROCEDURE populate_proposed_hierarchy(p_person_id IN NUMBER
2932 ,p_business_group_id IN NUMBER
2933 ,p_effective_date IN DATE
2934 ,p_ler_id IN NUMBER) IS
2935 --------------root node label-----------
2936 Cursor csr_root_label IS
2937 Select Meaning
2938 From hr_lookups
2939 Where lookup_type='BEN_SS_DRVD_LABELS'
2940 and lookup_code='COBJ';
2941 ---------------per in ler---------------
2942 CURSOR csr_ler IS
2943 Select pil.per_in_ler_id,
2944 ler.name name,
2945 ler.ler_id
2946 From ben_per_in_ler pil,
2947 ben_ler_f ler
2948 Where pil.person_id = p_person_id
2949 AND ler.ler_id = pil.ler_id
2950 AND p_effective_date BETWEEN ler.effective_start_date and ler.effective_end_date
2951 AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
2952 AND pil.lf_evt_ocrd_dt <= p_effective_date
2953 AND pil.business_group_id = p_business_group_id
2954 AND ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO')
2955 ORDER BY pil.lf_evt_ocrd_dt desc, 1 desc;
2956
2957 --------------------Program------------------
2958 CURSOR csr_pgms(p_per_in_ler_id NUMBER) IS
2959 Select pgm.pgm_id
2960 ,pgm.name
2961 ,pgm.pgm_uom uom
2962 ,pgm.acty_ref_perd_cd acty_ref_perd_cd
2963 ,popl.pil_elctbl_chc_popl_id
2964 From ben_pil_elctbl_chc_popl popl,
2965 ben_pgm_f pgm
2966 Where popl.per_in_ler_id = p_per_in_ler_id
2967 and popl.pgm_id =pgm.pgm_id
2968 and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;
2969
2970 --------------------------Plan type not in program------------------
2971 CURSOR csr_pl_types_not_in_program(p_per_in_ler_id NUMBER) IS
2972 Select pt.pl_typ_id,
2973 pt.name
2974 From ben_pl_typ_f pt
2975 Where pt.pl_typ_id IN ( Select epe.pl_typ_id
2976 From ben_elig_per_elctbl_chc epe,
2977 ben_pil_elctbl_chc_popl popl
2978 Where popl.per_in_ler_id = p_per_in_ler_id
2979 and popl.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
2980 and epe.comp_lvl_cd = 'PLAN'
2981 and popl.pl_id = epe.pl_id
2982 )
2983 and p_effective_date between pt.effective_start_date and pt.effective_end_date;
2984
2985 ----------------------plan type in program-------------------
2986 /*CURSOR csr_pl_types_in_program(p_pil_elctbl_chc_popl_id NUMBER) IS
2987 Select pt.pl_typ_id
2988 ,pt.name
2989 ,epe.ptip_id
2990 From ben_elig_per_elctbl_chc epe,
2991 ben_pl_typ_f pt
2992 Where epe.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
2993 and epe.pl_typ_id = pt.pl_typ_id
2994 and epe.comp_lvl_cd = 'PLAN'
2995 and p_effective_date between pt.effective_start_date and pt.effective_end_date;*/
2996 CURSOR csr_pl_types_in_program(p_pil_elctbl_chc_popl_id NUMBER) IS
2997 Select pt.pl_typ_id,
2998 pt.name,
2999 ptip.ptip_id
3000 From ben_pl_typ_f pt,
3001 ben_ptip_f ptip
3002 Where
3003 ptip.pl_typ_id=pt.pl_typ_id
3004 and pt.pl_typ_id in ( Select epe.pl_typ_id
3005 From ben_elig_per_elctbl_chc epe
3006 Where epe.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
3007 and epe.comp_lvl_cd = 'PLAN'
3008 and epe.pgm_id=ptip.pgm_id
3009 )
3010 and p_effective_date between pt.effective_start_date and pt.effective_end_date
3011 and p_effective_date between ptip.effective_start_date and ptip.effective_end_date;
3012
3013 ----------------plans----------------(IN pgm_id and pl_typ_id)------------
3014 CURSOR csr_plans(p_per_in_ler_id NUMBER
3015 ,p_pl_typ_id NUMBER
3016 ,p_pgm_id NUMBER) IS
3017 Select epe.pl_id,
3018 epe.plip_id,
3019 pl.name name,
3020 pl.nip_pl_uom uom,
3021 pl.nip_acty_ref_perd_cd acty_ref_perd_cd,
3022 epe.elctbl_flag,
3023 epe.elig_per_elctbl_chc_id
3024 From ben_pl_f pl,
3025 ben_elig_per_elctbl_chc epe
3026 Where
3027 epe.per_in_ler_id = p_per_in_ler_id
3028 and epe.comp_lvl_cd = 'PLAN'
3029 and epe.pl_id = pl.pl_id
3030 and epe.pl_typ_id = p_pl_typ_id
3031 and nvl(epe.pgm_id ,-1) = p_pgm_id
3032 and p_effective_date between pl.effective_start_date and effective_end_date;
3033 ----------------------------options-------------------------------------
3034 CURSOR csr_options_in_plip(p_per_in_ler_id NUMBER, p_plip_id NUMBER) IS
3035 Select opt.opt_id,
3036 oipl.oipl_id,
3037 opt.name,
3038 epe.elctbl_flag,
3039 epe.elig_per_elctbl_chc_id
3040 From ben_elig_per_elctbl_chc epe,
3041 ben_oipl_f oipl,
3042 ben_opt_f opt
3043 Where epe.per_in_ler_id = p_per_in_ler_id
3044 AND epe.plip_id = p_plip_id
3045 AND epe.comp_lvl_cd = 'OIPL'
3046 AND epe.oipl_id = oipl.oipl_id
3047 AND oipl.opt_id = opt.opt_id
3048 AND p_effective_date between oipl.effective_start_date and oipl.effective_end_date
3049 AND p_effective_date between opt.effective_start_date and opt.effective_end_date;
3050
3051 CURSOR csr_options_in_pnip(p_per_in_ler_id NUMBER, p_pl_id NUMBER) IS
3052 Select opt.opt_id,
3053 oipl.oipl_id,
3054 opt.name,
3055 epe.elctbl_flag,
3056 epe.elig_per_elctbl_chc_id
3057 From ben_elig_per_elctbl_chc epe,
3058 ben_oipl_f oipl,
3059 ben_opt_f opt
3060 Where epe.per_in_ler_id = p_per_in_ler_id
3061 AND epe.plip_id is NULL and epe.pl_id=p_pl_id
3062 AND epe.comp_lvl_cd = 'OIPL'
3063 AND epe.oipl_id = oipl.oipl_id
3064 AND oipl.opt_id = opt.opt_id
3065 AND p_effective_date between oipl.effective_start_date and oipl.effective_end_date
3066 AND p_effective_date between opt.effective_start_date and opt.effective_end_date;
3067 -----------------------------------rates------------------------------------------------
3068 CURSOR csr_rates (p_elig_per_elctbl_chc_id NUMBER) IS
3069 Select abr.name,
3070 abr.acty_base_rt_id,
3071 ecr.enrt_rt_id,
3072 decode(ecr.entr_val_at_enrt_flag, 'Y', ecr.dflt_val, ecr.val) val,
3073 ecr.cmcd_val,
3074 ecr.cmcd_acty_ref_perd_cd,
3075 ecr.nnmntry_uom
3076 From ben_enrt_rt ecr,
3077 ben_acty_base_rt_f abr
3078 Where ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
3079 AND ecr.dsply_on_enrt_flag = 'Y'
3080 AND ecr.enrt_bnft_id IS NULL
3081 AND abr.acty_base_rt_id = ecr.acty_base_rt_id
3082 AND p_effective_date BETWEEN abr.effective_start_date AND abr.effective_end_date
3083
3084 UNION ALL
3085 Select abr.name,
3086 abr.acty_base_rt_id,
3087 ecr.enrt_rt_id,
3088 decode(ecr.entr_val_at_enrt_flag, 'Y', ecr.dflt_val, ecr.val) val,
3089 ecr.cmcd_val,
3090 ecr.cmcd_acty_ref_perd_cd,
3091 ecr.nnmntry_uom
3092 From ben_enrt_rt ecr,
3093 ben_enrt_bnft enb,
3094 ben_acty_base_rt_f abr
3095 Where enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
3096 AND ecr.dsply_on_enrt_flag = 'Y'
3097 AND enb.enrt_bnft_id = ecr.enrt_bnft_id
3098 AND abr.acty_base_rt_id = ecr.acty_base_rt_id
3099 AND p_effective_date between abr.effective_start_date and abr.effective_end_date;
3100
3101 --
3102 l_proc VARCHAR2(72);
3103 l_per_in_ler_id NUMBER;
3104 l_hierarchy_id NUMBER;
3105 l_name VARCHAR2(240);
3106
3107 l_counter NUMBER := g_hierarchy.LAST;
3108
3109 l_index NUMBER;
3110
3111
3112 BEGIN
3113 --
3114 if g_debug then
3115 l_proc := g_package || 'populate_proposed_hierarchy';
3116 hr_utility.set_location('Entering: ' || l_proc,10);
3117 end if;
3118
3119 OPEN csr_ler ;
3120 FETCH csr_ler INTO l_per_in_ler_id
3121 ,l_name
3122 ,l_hierarchy_id ;
3123 CLOSE csr_ler;
3124 OPEN csr_root_label;
3125 FETCH csr_root_label into l_name;
3126 CLOSE csr_root_label;
3127 l_index := get_ler_index;
3128
3129 IF l_hierarchy_id <> nvl(g_hierarchy(l_index).hierarchy_id,-1) THEN
3130
3131 g_hierarchy(l_index).person_id := p_person_id;
3132 g_hierarchy(l_index).business_group_id := p_business_group_id;
3133 g_hierarchy(l_index).ler_id := p_ler_id;
3134 g_hierarchy(l_index).hierarchy_id := nvl(g_hierarchy(l_index).hierarchy_id,l_hierarchy_id);
3135 g_hierarchy(l_index).hierarchy_type := 'LE';
3136 g_hierarchy(l_index).name := l_name;
3137 g_hierarchy(l_index).parent_hierarchy_id := -9999;
3138 g_hierarchy(l_index).parent_hierarchy_type := NULL;
3139 l_hierarchy_id :=g_hierarchy(l_index).hierarchy_id; -- added by me
3140 /*
3141 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
3142 IF g_hierarchy(i).hierarchy_type = 'PT' AND g_hierarchy(i).parent_hierarchy_type = 'LE' THEN
3143 g_hierarchy(i).parent_hierarchy_id := l_hierarchy_id;
3144 END IF;
3145 END LOOP;*/
3146 END IF;
3147 FOR csr_pgms_rec IN csr_pgms(l_per_in_ler_id) LOOP
3148 l_index := chk_pgm_exists(p_pgm_id => csr_pgms_rec.pgm_id);
3149 IF l_index = 0 THEN
3150 l_counter := l_counter + 1;
3151 g_hierarchy(l_counter).hierarchy_type := 'PGM';
3152 g_hierarchy(l_counter).person_id := p_person_id;
3153 g_hierarchy(l_counter).business_group_id := p_business_group_id;
3154 g_hierarchy(l_counter).ler_id := p_ler_id;
3155 g_hierarchy(l_counter).hierarchy_id := csr_pgms_rec.pgm_id;
3156 g_hierarchy(l_counter).name := csr_pgms_rec.name;
3157 g_hierarchy(l_counter).parent_hierarchy_id := l_hierarchy_id;
3158 g_hierarchy(l_counter).parent_hierarchy_type := 'LE';
3159 g_hierarchy(l_counter).uom := csr_pgms_rec.uom;
3160 g_hierarchy(l_counter).acty_ref_perd_cd := csr_pgms_rec.acty_ref_perd_cd;
3161 ELSE
3162 g_hierarchy(l_index).name := csr_pgms_rec.name;
3163 g_hierarchy(l_index).uom := csr_pgms_rec.uom;
3164 g_hierarchy(l_index).acty_ref_perd_cd := csr_pgms_rec.acty_ref_perd_cd;
3165 END IF;
3166
3167 FOR csr_pgm_pt_rec IN csr_pl_types_in_program(csr_pgms_rec.pil_elctbl_chc_popl_id) LOOP
3168 l_index := chk_pt_in_pgm_exists(p_pgm_id => csr_pgms_rec.pgm_id
3169 ,p_ptip_id =>csr_pgm_pt_rec.ptip_id);
3170 IF l_index = 0 THEN
3171 l_counter := l_counter + 1;
3172 g_hierarchy(l_counter).person_id := p_person_id;
3173 g_hierarchy(l_counter).business_group_id := p_business_group_id;
3174 g_hierarchy(l_counter).ler_id := p_ler_id;
3175 g_hierarchy(l_counter).hierarchy_id := csr_pgm_pt_rec.ptip_id;
3176 g_hierarchy(l_counter).hierarchy_type := 'PTIP';
3177 g_hierarchy(l_counter).name := csr_pgm_pt_rec.name;
3178 g_hierarchy(l_counter).parent_hierarchy_id := csr_pgms_rec.pgm_id;
3179 g_hierarchy(l_counter).parent_hierarchy_type := 'PGM';
3180 ELSE
3181 g_hierarchy(l_index).name := csr_pgm_pt_rec.name;
3182 END IF;
3183
3184 FOR csr_plans_in_program_rec IN csr_plans(l_per_in_ler_id, csr_pgm_pt_rec.pl_typ_id, csr_pgms_rec.pgm_id ) LOOP
3185 l_index := chk_plip_exists(p_ptip_id => csr_pgm_pt_rec.ptip_id
3186 ,p_pl_id => csr_plans_in_program_rec.plip_id);
3187 IF l_index = 0 THEN
3188 l_counter := l_counter + 1;
3189 g_hierarchy(l_counter).hierarchy_type := 'PLIP';
3190 g_hierarchy(l_counter).person_id := p_person_id;
3191 g_hierarchy(l_counter).business_group_id := p_business_group_id;
3192 g_hierarchy(l_counter).ler_id := p_ler_id;
3193 g_hierarchy(l_counter).hierarchy_id := csr_plans_in_program_rec.plip_id;
3194 g_hierarchy(l_counter).name := csr_plans_in_program_rec.name;
3195 g_hierarchy(l_counter).parent_hierarchy_id := csr_pgm_pt_rec.ptip_id;
3196 g_hierarchy(l_counter).parent_hierarchy_type := 'PTIP';
3197 g_hierarchy(l_counter).uom := csr_pgms_rec.uom;
3198 g_hierarchy(l_counter).acty_ref_perd_cd := NULL;
3199 g_hierarchy(l_counter).watif_elctbl_flag := csr_plans_in_program_rec.elctbl_flag;
3200 g_hierarchy(l_counter).watif_cvg_val := get_coverage_amt(csr_plans_in_program_rec.elig_per_elctbl_chc_id,p_business_group_id);
3201 ELSE
3202 g_hierarchy(l_index).name := csr_plans_in_program_rec.name;
3203 g_hierarchy(l_index).watif_elctbl_flag := csr_plans_in_program_rec.elctbl_flag;
3204 g_hierarchy(l_index).watif_cvg_val := get_coverage_amt(csr_plans_in_program_rec.elig_per_elctbl_chc_id,p_business_group_id);
3205 END IF;
3206 FOR csr_rates_rec IN csr_rates( csr_plans_in_program_rec.elig_per_elctbl_chc_id) LOOP
3207 l_index := chk_pl_rate_exists(p_pl_id => csr_plans_in_program_rec.plip_id
3208 ,p_acty_base_rt_id => csr_rates_rec.acty_base_rt_id);
3209 IF l_index = 0 THEN
3210 l_counter := l_counter + 1;
3211 g_hierarchy(l_counter).hierarchy_type := 'ERT';
3212 g_hierarchy(l_counter).person_id := p_person_id;
3213 g_hierarchy(l_counter).business_group_id := p_business_group_id;
3214 g_hierarchy(l_counter).ler_id := p_ler_id;
3215 g_hierarchy(l_counter).hierarchy_id := csr_rates_rec.acty_base_rt_id;
3216 g_hierarchy(l_counter).name := csr_rates_rec.name;
3217 g_hierarchy(l_counter).parent_hierarchy_id := csr_plans_in_program_rec.plip_id;
3218 g_hierarchy(l_counter).parent_hierarchy_type := 'PLIP';
3219 g_hierarchy(l_counter).uom := csr_pgms_rec.uom;
3220 g_hierarchy(l_counter).nnmntry_uom := csr_rates_rec.nnmntry_uom;
3221 g_hierarchy(l_counter).watif_val := csr_rates_rec.val;
3222 g_hierarchy(l_counter).watif_cmcd_val := csr_rates_rec.cmcd_val;
3223 g_hierarchy(l_counter).watif_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
3224 ELSE
3225 g_hierarchy(l_index).name := csr_rates_rec.name;
3226 g_hierarchy(l_index).watif_val := csr_rates_rec.val;
3227 g_hierarchy(l_index).watif_cmcd_val := csr_rates_rec.cmcd_val;
3228 g_hierarchy(l_index).watif_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
3229 END IF;
3230 END LOOP;
3231 FOR csr_options_plip_rec IN csr_options_in_plip(l_per_in_ler_id, csr_plans_in_program_rec.plip_id) LOOP
3232 l_index := chk_opt_in_plip_exists(p_pl_id => csr_plans_in_program_rec.plip_id
3233 ,p_opt_id => csr_options_plip_rec.oipl_id );
3234 IF l_index = 0 THEN
3235 l_counter := l_counter + 1;
3236 g_hierarchy(l_counter).hierarchy_type := 'OIPLIP';
3237 g_hierarchy(l_counter).person_id := p_person_id;
3238 g_hierarchy(l_counter).business_group_id := p_business_group_id;
3239 g_hierarchy(l_counter).ler_id := p_ler_id;
3240 g_hierarchy(l_counter).hierarchy_id := csr_options_plip_rec.oipl_id;
3241 g_hierarchy(l_counter).name := csr_options_plip_rec.name;
3242 g_hierarchy(l_counter).parent_hierarchy_id := csr_plans_in_program_rec.plip_id;
3243 g_hierarchy(l_counter).parent_hierarchy_type := 'PLIP';
3244 g_hierarchy(l_counter).uom := csr_pgms_rec.uom;
3245 g_hierarchy(l_counter).watif_elctbl_flag := csr_options_plip_rec.elctbl_flag;
3246 g_hierarchy(l_counter).watif_cvg_val := get_coverage_amt(csr_options_plip_rec.elig_per_elctbl_chc_id,p_business_group_id);
3247 ELSE
3248 g_hierarchy(l_index).name := csr_options_plip_rec.name;
3249 g_hierarchy(l_index).watif_elctbl_flag := csr_options_plip_rec.elctbl_flag;
3250 g_hierarchy(l_index).watif_cvg_val := get_coverage_amt(csr_options_plip_rec.elig_per_elctbl_chc_id,p_business_group_id);
3251 END IF;
3252 FOR csr_rates_rec IN csr_rates(csr_options_plip_rec.elig_per_elctbl_chc_id) LOOP
3253 l_index := chk_rate_exists(p_opt_id => csr_options_plip_rec.oipl_id
3254 ,p_acty_base_rt_id => csr_rates_rec.acty_base_rt_id);
3255 IF l_index = 0 THEN
3256 l_counter := l_counter + 1;
3257 g_hierarchy(l_counter).hierarchy_type := 'ERT';
3258 g_hierarchy(l_counter).person_id := p_person_id;
3259 g_hierarchy(l_counter).business_group_id := p_business_group_id;
3260 g_hierarchy(l_counter).ler_id := p_ler_id;
3261 g_hierarchy(l_counter).hierarchy_id := csr_rates_rec.acty_base_rt_id;
3262 g_hierarchy(l_counter).name := csr_rates_rec.name;
3263 g_hierarchy(l_counter).parent_hierarchy_id := csr_options_plip_rec.oipl_id;
3264 g_hierarchy(l_counter).parent_hierarchy_type := 'OIPLIP';
3265 g_hierarchy(l_counter).uom := csr_pgms_rec.uom;
3266 g_hierarchy(l_counter).nnmntry_uom := csr_rates_rec.nnmntry_uom;
3267 g_hierarchy(l_counter).watif_val := csr_rates_rec.val;
3268 g_hierarchy(l_counter).watif_cmcd_val := csr_rates_rec.cmcd_val;
3269 g_hierarchy(l_counter).watif_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
3270 ELSE
3271 g_hierarchy(l_index).name := csr_rates_rec.name;
3272 g_hierarchy(l_index).watif_val := csr_rates_rec.val;
3273 g_hierarchy(l_index).watif_cmcd_val := csr_rates_rec.cmcd_val;
3274 g_hierarchy(l_index).watif_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
3275 END IF;
3276 END LOOP; -- rate in option in plan in plan type in pgm
3277 END LOOP; -- option in plan in plan type in pgm
3278
3279 END LOOP; -- plan in plan type in pgm
3280 END LOOP; -- plan type in pgm
3281 END LOOP;--program
3282 FOR csr_pt_not_in_pgm_rec IN csr_pl_types_not_in_program(l_per_in_ler_id ) LOOP
3283 l_index := chk_pl_typ_exists(p_pl_typ_id => csr_pt_not_in_pgm_rec.pl_typ_id);
3284 IF l_index = 0 THEN
3285 l_counter := l_counter + 1;
3286 g_hierarchy(l_counter).person_id := p_person_id;
3287 g_hierarchy(l_counter).business_group_id := p_business_group_id;
3288 g_hierarchy(l_counter).ler_id := p_ler_id;
3289 g_hierarchy(l_counter).hierarchy_id := csr_pt_not_in_pgm_rec.pl_typ_id;
3290 g_hierarchy(l_counter).hierarchy_type := 'PT';
3291 g_hierarchy(l_counter).name := csr_pt_not_in_pgm_rec.name;
3292 g_hierarchy(l_counter).parent_hierarchy_id := l_hierarchy_id;
3293 g_hierarchy(l_counter).parent_hierarchy_type := 'LE';
3294 ELSE
3295 g_hierarchy(l_index).name := csr_pt_not_in_pgm_rec.name;
3296 END IF;
3297 FOR csr_plans_not_in_program_rec IN csr_plans(l_per_in_ler_id , csr_pt_not_in_pgm_rec.pl_typ_id,-1) LOOP
3298 l_index := chk_pnip_exists(p_pl_typ_id => csr_pt_not_in_pgm_rec.pl_typ_id
3299 ,p_pl_id => csr_plans_not_in_program_rec.pl_id );
3300 IF l_index = 0 THEN
3301 l_counter := l_counter + 1;
3302 g_hierarchy(l_counter).person_id := p_person_id;
3303 g_hierarchy(l_counter).business_group_id := p_business_group_id;
3304 g_hierarchy(l_counter).ler_id := p_ler_id;
3305 g_hierarchy(l_counter).hierarchy_id := csr_plans_not_in_program_rec.pl_id;
3306 g_hierarchy(l_counter).hierarchy_type := 'PNIP';
3307 g_hierarchy(l_counter).name := csr_plans_not_in_program_rec.name;
3308 g_hierarchy(l_counter).parent_hierarchy_id := csr_pt_not_in_pgm_rec.pl_typ_id;
3309 g_hierarchy(l_counter).parent_hierarchy_type := 'PT';
3310 g_hierarchy(l_counter).uom := csr_plans_not_in_program_rec.uom;
3311 g_hierarchy(l_counter).acty_ref_perd_cd := csr_plans_not_in_program_rec.acty_ref_perd_cd;
3312 g_hierarchy(l_counter).watif_elctbl_flag := csr_plans_not_in_program_rec.elctbl_flag;
3313 g_hierarchy(l_counter).watif_cvg_val := get_coverage_amt(csr_plans_not_in_program_rec.elig_per_elctbl_chc_id,p_business_group_id);
3314 ELSE
3315 g_hierarchy(l_index).name := csr_plans_not_in_program_rec.name;
3316 g_hierarchy(l_index).uom := csr_plans_not_in_program_rec.uom;
3317 g_hierarchy(l_index).acty_ref_perd_cd := csr_plans_not_in_program_rec.acty_ref_perd_cd;
3318 g_hierarchy(l_index).watif_elctbl_flag := csr_plans_not_in_program_rec.elctbl_flag;
3319 g_hierarchy(l_index).watif_cvg_val := get_coverage_amt(csr_plans_not_in_program_rec.elig_per_elctbl_chc_id,p_business_group_id);
3320 END IF;
3321 FOR csr_rates_rec IN csr_rates( csr_plans_not_in_program_rec.elig_per_elctbl_chc_id) LOOP
3322 l_index := chk_pl_rate_exists(p_pl_id => csr_plans_not_in_program_rec.pl_id
3323 ,p_acty_base_rt_id => csr_rates_rec.acty_base_rt_id);
3324 IF l_index = 0 THEN
3325 l_counter := l_counter + 1;
3326 g_hierarchy(l_counter).person_id := p_person_id;
3327 g_hierarchy(l_counter).business_group_id := p_business_group_id;
3328 g_hierarchy(l_counter).ler_id := p_ler_id;
3329 g_hierarchy(l_counter).hierarchy_id := csr_rates_rec.acty_base_rt_id;
3330 g_hierarchy(l_counter).hierarchy_type := 'ERT';
3331 g_hierarchy(l_counter).name := csr_rates_rec.name;
3332 g_hierarchy(l_counter).parent_hierarchy_id := csr_plans_not_in_program_rec.pl_id;
3333 g_hierarchy(l_counter).parent_hierarchy_type := 'PNIP';
3334 g_hierarchy(l_counter).uom := csr_plans_not_in_program_rec.uom;
3335 g_hierarchy(l_counter).nnmntry_uom := csr_rates_rec.nnmntry_uom;
3336 g_hierarchy(l_counter).watif_val := csr_rates_rec.val;
3337 g_hierarchy(l_counter).watif_cmcd_val := csr_rates_rec.cmcd_val;
3338 g_hierarchy(l_counter).watif_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
3339 ELSE
3340 g_hierarchy(l_index).name := csr_rates_rec.name;
3341 g_hierarchy(l_index).watif_val := csr_rates_rec.val;
3342 g_hierarchy(l_index).watif_cmcd_val := csr_rates_rec.cmcd_val;
3343 g_hierarchy(l_index).watif_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
3344 END IF;
3345 END LOOP;
3346 FOR csr_options_pnip_rec IN csr_options_in_pnip(l_per_in_ler_id ,csr_plans_not_in_program_rec.pl_id) LOOP
3347 l_index := chk_opt_in_pnip_exists(p_pl_id => csr_plans_not_in_program_rec.pl_id
3348 ,p_opt_id => csr_options_pnip_rec.oipl_id );
3349 IF l_index = 0 THEN
3350 l_counter := l_counter + 1;
3351 g_hierarchy(l_counter).person_id := p_person_id;
3352 g_hierarchy(l_counter).business_group_id := p_business_group_id;
3353 g_hierarchy(l_counter).ler_id := p_ler_id;
3354 g_hierarchy(l_counter).hierarchy_id := csr_options_pnip_rec.oipl_id;
3355 g_hierarchy(l_counter).hierarchy_type := 'OIPNIP';
3356 g_hierarchy(l_counter).name := csr_options_pnip_rec.name;
3357 g_hierarchy(l_counter).parent_hierarchy_id := csr_plans_not_in_program_rec.pl_id;
3358 g_hierarchy(l_counter).parent_hierarchy_type := 'PNIP';
3359 g_hierarchy(l_counter).uom := csr_plans_not_in_program_rec.uom;
3360 g_hierarchy(l_counter).watif_elctbl_flag := csr_options_pnip_rec.elctbl_flag;
3361 g_hierarchy(l_counter).watif_cvg_val := get_coverage_amt(csr_options_pnip_rec.elig_per_elctbl_chc_id,p_business_group_id);
3362 ELSE
3363 g_hierarchy(l_index).name := csr_options_pnip_rec.name;
3364 g_hierarchy(l_index).watif_elctbl_flag := csr_options_pnip_rec.elctbl_flag;
3365 g_hierarchy(l_index).watif_cvg_val := get_coverage_amt(csr_options_pnip_rec.elig_per_elctbl_chc_id,p_business_group_id);
3366 END IF;
3367 FOR csr_rates_rec IN csr_rates(csr_options_pnip_rec.elig_per_elctbl_chc_id) LOOP
3368 l_index := chk_rate_exists(p_opt_id => csr_options_pnip_rec.oipl_id
3369 ,p_acty_base_rt_id => csr_rates_rec.acty_base_rt_id);
3370 IF l_index = 0 THEN
3371 l_counter := l_counter + 1;
3372 g_hierarchy(l_counter).person_id := p_person_id;
3373 g_hierarchy(l_counter).business_group_id := p_business_group_id;
3374 g_hierarchy(l_counter).ler_id := p_ler_id;
3375 g_hierarchy(l_counter).hierarchy_id := csr_rates_rec.acty_base_rt_id;
3376 g_hierarchy(l_counter).hierarchy_type := 'ERT';
3377 g_hierarchy(l_counter).name := csr_rates_rec.name;
3378 g_hierarchy(l_counter).parent_hierarchy_id := csr_options_pnip_rec.oipl_id;
3379 g_hierarchy(l_counter).parent_hierarchy_type := 'OIPNIP';
3380 g_hierarchy(l_counter).uom := csr_plans_not_in_program_rec.uom;
3381 g_hierarchy(l_counter).nnmntry_uom := csr_rates_rec.nnmntry_uom;
3382 g_hierarchy(l_counter).watif_val := csr_rates_rec.val;
3383 g_hierarchy(l_counter).watif_cmcd_val := csr_rates_rec.cmcd_val;
3384 g_hierarchy(l_counter).watif_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
3385 ELSE
3386 g_hierarchy(l_index).name := csr_rates_rec.name;
3387 g_hierarchy(l_index).watif_val := csr_rates_rec.val;
3388 g_hierarchy(l_index).watif_cmcd_val := csr_rates_rec.cmcd_val;
3389 g_hierarchy(l_index).watif_cmcd_acty_ref_perd_cd := csr_rates_rec.cmcd_acty_ref_perd_cd;
3390 END IF;
3391 END LOOP; -- rate in option in plan in plan type not in prog
3392 END LOOP; -- option in plan in plan type not in prog
3393
3394 END LOOP; --- plan in plan type not in prog
3395 END LOOP;-- Plan type not in prog
3396
3397 if g_debug then
3398 hr_utility.set_location('Leaving: ' || l_proc,20);
3399 end if;
3400 --
3401 END populate_proposed_hierarchy;
3402 -----------------------------------------------------------------------------------------------
3403 -- Code for Role Based Plan Restriction Starts here
3404 --
3405 -----------------------------------------------------------------------------------------------
3406 -- Program Hierarchy
3407 -----------------------------------------------------------------------------------------------
3408 FUNCTION check_oiplip(p_plip_id in number
3409 ,p_parent_exclude IN BOOLEAN
3410 ,p_whatif_results_batch_id IN NUMBER
3411 )
3412 RETURN BOOLEAN is
3413 l_parent_include BOOLEAN :=false;
3414 l_include VARCHAR2(2);
3415
3416 Cursor csr_oiplip IS
3417 Select hierarchy_id from
3418 pqh_pa_whatif_results
3419 where whatif_results_batch_id=p_whatif_results_batch_id
3420 and parent_hierarchy_id=p_plip_id
3421 and hierarchy_type='OIPLIP';
3422
3423 Cursor check_setup(p_oipl_id NUMBER) IS
3424 Select information3
3425 From pqh_role_extra_info ,
3426 ben_oiplip_f
3427 Where oipl_id=p_oipl_id
3428 and plip_id=p_plip_id
3429 and g_effective_date between effective_start_date and effective_end_date
3430 and role_id=g_role_id
3431 and information_type='BEN_SS_ROLE_COMP_OBJECTS'
3432 and information1='OIPLIP'
3433 and information2=to_char(oiplip_id);
3434
3435 BEGIN
3436 For C1 in csr_oiplip LOOP
3437 OPEN check_setup(C1.hierarchy_id);
3438 FETCH check_setup into l_include;
3439 IF check_setup%NOTFOUND THEN l_include :='IE'; END IF;
3440 CLOSE check_setup;
3441 IF l_include='E'or (p_parent_exclude and l_include='IE') Then
3442 Delete from pqh_pa_whatif_results
3443 where whatif_results_batch_id=p_whatif_results_batch_id
3444 and hierarchy_id=C1.hierarchy_id and hierarchy_type='OIPLIP';
3445 ELSIF l_include='I' THEN l_parent_include :=true;
3446 END IF;
3447 END LOOP;
3448 return l_parent_include;
3449 END;
3450 ------------------------------------------------------------------------------------------------
3451 FUNCTION check_plip(p_ptip_id in number
3452 ,p_parent_exclude IN BOOLEAN
3453 ,p_whatif_results_batch_id IN NUMBER
3454 )
3455 RETURN BOOLEAN is
3456 l_parent_include BOOLEAN :=false;
3457 l_dummy BOOLEAN;
3458 l_include VARCHAR2(2);
3459
3460 Cursor csr_plip IS
3461 Select hierarchy_id from
3462 pqh_pa_whatif_results
3463 where whatif_results_batch_id=p_whatif_results_batch_id
3464 and parent_hierarchy_id=p_ptip_id
3465 and hierarchy_type='PLIP';
3466
3467 Cursor check_setup(p_plip_id NUMBER) IS
3468 Select information3
3469 From pqh_role_extra_info
3470 Where role_id=g_role_id
3471 and information_type='BEN_SS_ROLE_COMP_OBJECTS'
3472 and information1='PLIP'
3473 and information2=to_char(p_plip_id);
3474
3475 BEGIN
3476 For C1 in csr_plip LOOP
3477 OPEN check_setup(C1.hierarchy_id);
3478 FETCH check_setup into l_include;
3479 IF check_setup%NOTFOUND THEN l_include :='IE'; END IF;
3480 CLOSE check_setup;
3481 IF l_include='E' OR (p_parent_exclude and l_include ='IE')Then
3482 IF NOT check_oiplip(C1.hierarchy_id,true,p_whatif_results_batch_id) THEN
3483 Delete from pqh_pa_whatif_results
3484 where whatif_results_batch_id=p_whatif_results_batch_id
3485 and hierarchy_id=C1.hierarchy_id and hierarchy_type='PLIP';
3486 ELSE l_parent_include :=true;
3487 END IF;
3488 ELSIF l_include='I' THEN l_parent_include :=true; l_dummy := check_oiplip(C1.hierarchy_id,false,p_whatif_results_batch_id);
3489 ELSIF check_oiplip(C1.hierarchy_id,p_parent_exclude,p_whatif_results_batch_id) THEN l_parent_include:=true;
3490 END IF;
3491 END LOOP;
3492 return l_parent_include;
3493 END;
3494 -----------------------------------------------------------------------------------------------
3495
3496
3497 FUNCTION check_ptip(p_pgm_id in number
3498 ,p_parent_exclude IN BOOLEAN
3499 ,p_whatif_results_batch_id IN NUMBER
3500 )
3501 RETURN BOOLEAN is
3502 l_parent_include BOOLEAN :=false;
3503 l_dummy BOOLEAN;
3504 l_include VARCHAR2(2);
3505
3506 Cursor csr_ptip IS
3507 Select hierarchy_id from
3508 pqh_pa_whatif_results
3509 where whatif_results_batch_id=p_whatif_results_batch_id
3510 and parent_hierarchy_id=p_pgm_id
3511 and hierarchy_type='PTIP';
3512
3513 Cursor check_setup(p_ptip_id NUMBER) IS
3514 Select information3
3515 From pqh_role_extra_info
3516 Where role_id=g_role_id
3517 and information_type='BEN_SS_ROLE_COMP_OBJECTS'
3518 and information1='PTIP'
3519 and information2=to_char(p_ptip_id);
3520
3521 BEGIN
3522 For C1 in csr_ptip LOOP
3523 OPEN check_setup(C1.hierarchy_id);
3524 FETCH check_setup into l_include;
3525 IF check_setup%NOTFOUND THEN l_include :='IE'; END IF;
3526 CLOSE check_setup;
3527 IF l_include='E' OR (p_parent_exclude and l_include ='IE')Then
3528 IF NOT check_plip(C1.hierarchy_id,true,p_whatif_results_batch_id) THEN
3529 Delete from pqh_pa_whatif_results
3530 where whatif_results_batch_id=p_whatif_results_batch_id
3531 and hierarchy_id=C1.hierarchy_id and hierarchy_type='PTIP';
3532 ELSE l_parent_include :=true;
3533 END IF;
3534 ELSIF l_include='I' THEN l_parent_include :=true; l_dummy := check_plip(C1.hierarchy_id,false,p_whatif_results_batch_id);
3535 ELSIF check_plip(C1.hierarchy_id,p_parent_exclude,p_whatif_results_batch_id) THEN l_parent_include:=true;
3536 END IF;
3537 END LOOP;
3538 return l_parent_include;
3539 END;
3540 -----------------------------------------------------------------------------------------------
3541
3542 PROCEDURE check_pgm(
3543 p_whatif_results_batch_id in number
3544 ) IS
3545
3546 l_include VARCHAR2(2);
3547 l_dummy BOOLEAN;
3548
3549 Cursor csr_pgm IS
3550 Select hierarchy_id from
3551 pqh_pa_whatif_results
3552 where whatif_results_batch_id=p_whatif_results_batch_id
3553 and hierarchy_type='PGM';
3554
3555 Cursor check_setup(p_pgm_id NUMBER) IS
3556 Select information3
3557 From pqh_role_extra_info
3558 Where role_id=g_role_id
3559 and information_type='BEN_SS_ROLE_COMP_OBJECTS'
3560 and information1='PGM'
3561 and information2=p_pgm_id;
3562
3563 BEGIN
3564 For C1 in csr_pgm LOOP
3565 OPEN check_setup(C1.hierarchy_id);
3566 FETCH check_setup into l_include;
3567 IF check_setup%NOTFOUND THEN l_include :='IE'; END IF;
3568 CLOSE check_setup;
3569 IF l_include='E' Then
3570 IF NOT check_ptip(C1.hierarchy_id,true,p_whatif_results_batch_id) THEN
3571 Delete from pqh_pa_whatif_results
3572 where whatif_results_batch_id=p_whatif_results_batch_id
3573 and hierarchy_id=C1.hierarchy_id and hierarchy_type='PGM';
3574 END IF;
3575 ELSE l_dummy :=check_ptip(C1.hierarchy_id,false,p_whatif_results_batch_id);
3576 END IF;
3577 END LOOP;
3578 END;
3579 ----------------------------------------------------------------------------------------------
3580 -- Not in Program Hierarchy
3581 -----------------------------------------------------------------------------------------------
3582 FUNCTION check_oipnip(p_pnip_id in number
3583 ,p_parent_exclude IN BOOLEAN
3584 ,p_whatif_results_batch_id IN NUMBER
3585 )
3586 RETURN BOOLEAN is
3587 l_parent_include BOOLEAN :=false;
3588 l_include VARCHAR2(2);
3589
3590 Cursor csr_oipnip IS
3591 Select hierarchy_id from
3592 pqh_pa_whatif_results
3593 where whatif_results_batch_id=p_whatif_results_batch_id
3594 and parent_hierarchy_id=p_pnip_id
3595 and hierarchy_type='OIPNIP';
3596
3597 Cursor check_setup(p_oipnip_id NUMBER) IS
3598 Select information3
3599 From pqh_role_extra_info
3600 Where role_id=g_role_id
3601 and information_type='BEN_SS_ROLE_COMP_OBJECTS'
3602 and information1='OIPNIP'
3603 and information2=to_char(p_oipnip_id);
3604
3605 BEGIN
3606 For C1 in csr_oipnip LOOP
3607 OPEN check_setup(C1.hierarchy_id);
3608 FETCH check_setup into l_include;
3609 IF check_setup%NOTFOUND THEN l_include :='IE'; END IF;
3610 CLOSE check_setup;
3611 IF l_include='E'or (p_parent_exclude and l_include='IE') Then
3612 Delete from pqh_pa_whatif_results
3613 where whatif_results_batch_id=p_whatif_results_batch_id
3614 and hierarchy_id=C1.hierarchy_id and hierarchy_type='OIPNIP';
3615 ELSIF l_include='I' THEN l_parent_include :=true;
3616 END IF;
3617 END LOOP;
3618 return l_parent_include;
3619 END;
3620 ------------------------------------------------------------------------------------------------
3621
3622 FUNCTION check_pnip(p_pl_typ_id in number
3623 ,p_parent_exclude IN BOOLEAN
3624 ,p_whatif_results_batch_id IN NUMBER
3625 )
3626 RETURN BOOLEAN is
3627 l_parent_include BOOLEAN :=false;
3628 l_dummy BOOLEAN;
3629 l_include VARCHAR2(2);
3630
3631 Cursor csr_pnip IS
3632 Select hierarchy_id from
3633 pqh_pa_whatif_results
3634 where whatif_results_batch_id=p_whatif_results_batch_id
3635 and parent_hierarchy_id=p_pl_typ_id
3636 and hierarchy_type='PNIP';
3637
3638 Cursor check_setup(p_pnip_id NUMBER) IS
3639 Select information3
3640 From pqh_role_extra_info
3641 Where role_id=g_role_id
3642 and information_type='BEN_SS_ROLE_COMP_OBJECTS'
3643 and information1='PNIP'
3644 and information2=to_char(p_pnip_id);
3645
3646 BEGIN
3647 For C1 in csr_pnip LOOP
3648 OPEN check_setup(C1.hierarchy_id);
3649 FETCH check_setup into l_include;
3650 IF check_setup%NOTFOUND THEN l_include :='IE'; END IF;
3651 CLOSE check_setup;
3652 IF l_include='E' OR (p_parent_exclude and l_include ='IE')Then
3653 IF NOT check_oipnip(C1.hierarchy_id,true,p_whatif_results_batch_id) THEN
3654 Delete from pqh_pa_whatif_results
3655 where whatif_results_batch_id=p_whatif_results_batch_id
3656 and hierarchy_id=C1.hierarchy_id and hierarchy_type='PNIP';
3657 ELSE l_parent_include :=true;
3658 END IF;
3659 ELSIF l_include='I' THEN l_parent_include :=true; l_dummy := check_oipnip(C1.hierarchy_id,false,p_whatif_results_batch_id);
3660 ELSIF check_oipnip(C1.hierarchy_id,p_parent_exclude,p_whatif_results_batch_id) THEN l_parent_include:=true;
3661 END IF;
3662 END LOOP;
3663 return l_parent_include;
3664 END;
3665 -----------------------------------------------------------------------------------------------
3666
3667 PROCEDURE check_pt(
3668 p_whatif_results_batch_id in number
3669 ) IS
3670
3671 l_include VARCHAR2(2);
3672 l_dummy BOOLEAN;
3673
3674 Cursor csr_pt IS
3675 Select hierarchy_id from
3676 pqh_pa_whatif_results
3677 where whatif_results_batch_id=p_whatif_results_batch_id
3678 and hierarchy_type='PT';
3679
3680 Cursor check_setup(p_pl_typ_id NUMBER) IS
3681 Select information3
3682 From pqh_role_extra_info
3683 Where role_id=g_role_id
3684 and information_type='BEN_SS_ROLE_COMP_OBJECTS'
3685 and information1='PT'
3686 and information2=p_pl_typ_id;
3687
3688 BEGIN
3689 For C1 in csr_pt LOOP
3690 OPEN check_setup(C1.hierarchy_id);
3691 FETCH check_setup into l_include;
3692 IF check_setup%NOTFOUND THEN l_include :='IE'; END IF;
3693 CLOSE check_setup;
3694 IF l_include='E' Then
3695 IF NOT check_pnip(C1.hierarchy_id,true,p_whatif_results_batch_id) THEN
3696 Delete from pqh_pa_whatif_results
3697 where whatif_results_batch_id=p_whatif_results_batch_id
3698 and hierarchy_id=C1.hierarchy_id and hierarchy_type='PT';
3699 END IF;
3700 ELSE l_dummy :=check_pnip(C1.hierarchy_id,false,p_whatif_results_batch_id);
3701 END IF;
3702 END LOOP;
3703 END;
3704 -----------------------------------------------------------------------------------------------
3705
3706 PROCEDURE RESTRICT_BY_ROLE(p_whatif_results_batch_id IN NUMBER)
3707 IS
3708 l_dummy NUMBER :=0;
3709 Cursor csr_verify_setup IS
3710 Select 1
3711 From pqh_role_extra_info
3712 Where role_id=g_role_id
3713 and information_type='BEN_SS_ROLE_COMP_OBJECTS'
3714 and information1 <> 'LE';
3715 BEGIN
3716 --If EIT is not configured then there is nothing to restrict
3717 IF g_role_id <> -1 THEN
3718 OPEN csr_verify_setup;
3719 Fetch csr_verify_setup into l_dummy;
3720 IF csr_verify_setup%FOUND THEN
3721 check_pgm(p_whatif_results_batch_id);
3722 check_pt(p_whatif_results_batch_id);
3723 null;
3724 END IF;
3725 CLOSE csr_verify_setup;
3726 END IF;
3727 END RESTRICT_BY_ROLE;
3728 -----------------------------------------------------------------------------------------------
3729 -- Code for Role Based Plan Restriction Ends here
3730 --
3731 ------------------------------------------------------------------------------------------------
3732
3733
3734 PROCEDURE populate_table ( p_person_id IN NUMBER
3735 ,p_business_group_id IN NUMBER
3736 ,p_transaction_id IN NUMBER
3737 ,p_ler_id IN NUMBER
3738 ,p_whatif_results_batch_id OUT NOCOPY NUMBER
3739 ) IS
3740
3741 CURSOR csr_sequence_val IS
3742 SELECT pqh_pa_whatif_results_s.nextval
3743 FROM sys.dual;
3744 --
3745 l_proc VARCHAR2(72);
3746
3747 BEGIN
3748 --
3749 if g_debug then
3750 l_proc := g_package || 'populate_table';
3751 hr_utility.set_location('Entering: ' || l_proc,10);
3752 end if;
3753
3754 OPEN csr_sequence_val;
3755 FETCH csr_sequence_val INTO p_whatif_results_batch_id;
3756 CLOSE csr_sequence_val;
3757
3758 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST LOOP
3759 INSERT INTO PQH_PA_WHATIF_RESULTS (
3760 WHATIF_RESULTS_BATCH_ID
3761 ,PERSON_ID
3762 ,BUSINESS_GROUP_ID
3763 ,TRANSACTION_ID
3764 ,LER_ID
3765 ,HIERARCHY_ID
3766 ,HIERARCHY_TYPE
3767 ,PARENT_HIERARCHY_ID
3768 ,PARENT_HIERARCHY_TYPE
3769 ,NAME
3770 ,UOM
3771 ,NNMNTRY_UOM
3772 ,ACTY_REF_PERD_CD
3773 ,CRRNT_ELCTBL_FLAG
3774 ,CRRNT_CVG_VAL
3775 ,CRRNT_VAL
3776 ,CRRNT_CMCD_VAL
3777 ,CRRNT_CMCD_ACTY_REF_PERD_CD
3778 ,WATIF_ELCTBL_FLAG
3779 ,WATIF_CVG_VAL
3780 ,WATIF_VAL
3781 ,WATIF_CMCD_VAL
3782 ,WATIF_CMCD_ACTY_REF_PERD_CD
3783 ,OBJECT_VERSION_NUMBER)
3784 VALUES (
3785 p_whatif_results_batch_id
3786 ,p_person_id
3787 ,p_business_group_id
3788 ,p_transaction_id
3789 ,p_ler_id
3790 ,g_hierarchy(i).HIERARCHY_ID
3791 ,g_hierarchy(i).HIERARCHY_TYPE
3792 ,g_hierarchy(i).PARENT_HIERARCHY_ID
3793 ,g_hierarchy(i).PARENT_HIERARCHY_TYPE
3794 ,g_hierarchy(i).NAME
3795 ,g_hierarchy(i).UOM
3796 ,g_hierarchy(i).NNMNTRY_UOM
3797 ,g_hierarchy(i).ACTY_REF_PERD_CD
3798 ,g_hierarchy(i).CRRNT_ELCTBL_FLAG
3799 ,g_hierarchy(i).CRRNT_CVG_VAL
3800 ,g_hierarchy(i).CRRNT_VAL
3801 ,g_hierarchy(i).CRRNT_CMCD_VAL
3802 ,g_hierarchy(i).CRRNT_CMCD_ACTY_REF_PERD_CD
3803 ,g_hierarchy(i).WATIF_ELCTBL_FLAG
3804 ,g_hierarchy(i).WATIF_CVG_VAL
3805 ,g_hierarchy(i).WATIF_VAL
3806 ,g_hierarchy(i).WATIF_CMCD_VAL
3807 ,g_hierarchy(i).WATIF_CMCD_ACTY_REF_PERD_CD
3808 ,1 );
3809
3810 END LOOP;
3811 --
3812 -- Roles based plan restriction
3813 --
3814 RESTRICT_BY_ROLE(p_whatif_results_batch_id);
3815
3816 COMMIT;
3817
3818 if g_debug then
3819 hr_utility.set_location('Leaving: ' || l_proc,20);
3820 end if;
3821 --
3822 END populate_table;
3823 ----------------------------------------------------------------------------------------------
3824 PROCEDURE purge_table_data
3825 IS
3826 PRAGMA AUTONOMOUS_TRANSACTION;
3827
3828 CURSOR csr_purge_data IS
3829 SELECT DISTINCT whatif_results_batch_id
3830 FROM pqh_pa_whatif_results
3831 WHERE CREATION_DATE < trunc(SYSDATE);
3832 --
3833 l_proc VARCHAR2(72);
3834
3835 BEGIN
3836 --
3837 if g_debug then
3838 l_proc := g_package || 'purge_table_data';
3839 hr_utility.set_location('Entering: ' || l_proc,10);
3840 end if;
3841
3842 FOR csr_purge_data_rec IN csr_purge_data LOOP
3843 DELETE PQH_PA_WHATIF_RESULTS
3844 WHERE WHATIF_RESULTS_BATCH_ID = csr_purge_data_rec.whatif_results_batch_id;
3845 END LOOP;
3846 COMMIT;
3847
3848 if g_debug then
3849 hr_utility.set_location('Leaving: ' || l_proc,20);
3850 end if;
3851 --
3852 EXCEPTION
3853 WHEN OTHERS THEN ROLLBACK;
3854 END purge_table_data;
3855 -----------------------------------------------------------------------------------------------
3856 PROCEDURE get_user_role(
3857 p_user_id IN NUMBER
3858 ,p_user_type IN VARCHAR2
3859 ,p_business_group_id IN NUMBER
3860 ,p_role_id OUT NOCOPY NUMBER
3861 ) IS
3862 Cursor csr_user_role IS
3863 Select rls.role_id
3864 From pqh_roles rls
3865 ,per_people_extra_info pei
3866 ,fnd_user usr
3867 Where rls.role_type_cd=p_user_type
3868 and nvl(rls.enable_flag,'N')='Y'
3869 and rls.role_id =to_number(pei_information3)
3870 and pei.information_type='PQH_ROLE_USERS'
3871 and nvl(pei_information5,'N')='Y'
3872 and nvl(pei_information9,'N')='Y'
3873 and pei.person_id=usr.employee_id
3874 and usr.user_id=p_user_id;
3875
3876 Cursor csr_default_role IS
3877 Select role_id
3878 From pqh_roles
3879 Where role_type_cd=p_user_type
3880 and role_name='XXXX';
3881
3882 l_proc VARCHAR2(72);
3883 BEGIN
3884 if g_debug then
3885 l_proc := g_package || '.get_user_role';
3886 hr_utility.set_location('Entering: ' || l_proc,10);
3887 hr_utility.set_location('p_user_type : ' || p_user_type,15);
3888 hr_utility.set_location('p_user_id : ' || to_char(p_user_id),20);
3889 end if;
3890
3891 OPEN csr_user_role;
3892 FETCH csr_user_role into p_role_id;
3893 /*IF csr_user_role%NOTFOUND
3894 THEN
3895 if g_debug then
3896 hr_utility.set_location('Using default role....',25);
3897 end if;
3898 OPEN csr_default_role;
3899 FETCH csr_default_role into p_role_id;
3900 CLOSE csr_default_role;
3901 END IF;*/
3902 CLOSE csr_user_role;
3903 if g_debug then
3904 hr_utility.set_location('p_role_id : ' || to_char(p_role_id),40);
3905 hr_utility.set_location('Leaving: ' || l_proc,50);
3906 end if;
3907 END get_user_role;
3908
3909 -----------------------------------------------------------------------------------------------
3910
3911 PROCEDURE ss_whatif_process(
3912 p_called_from IN VARCHAR2
3913 ,p_login_id IN NUMBER
3914 ,p_login_type IN VARCHAR2
3915 ,p_person_id IN NUMBER
3916 ,p_business_group_id IN NUMBER
3917 ,p_effective_date IN DATE
3918 ,p_session_date IN DATE
3919 ,p_transaction_id IN NUMBER
3920 ,p_ler_id IN OUT NOCOPY NUMBER
3921 ,p_whatif_results_batch_id OUT NOCOPY NUMBER
3922 )
3923 IS
3924
3925 cursor c_ptnl_le(l_lf_evt_ocrd_dt date) is
3926 select ptn.ptnl_ler_for_per_id,
3927 ptn.ptnl_ler_for_per_stat_cd,
3928 ptn.lf_evt_ocrd_dt lf_evt_ocrd_dt
3929 from ben_ptnl_ler_for_per ptn,
3930 ben_ler_f ler
3931 where ptn.person_id = p_person_id
3932 and ptn.business_group_id = p_business_group_id
3933 and ptn.lf_evt_ocrd_dt > l_lf_evt_ocrd_dt
3934 and ptn.ler_id = ler.ler_id
3935 and ptn.lf_evt_ocrd_dt between
3936 ler.effective_start_date and ler.effective_end_date
3937 and ler.typ_cd not in ('SCHEDDU','COMP','GSP','ABS')
3938 and ptn.ptnl_ler_for_per_stat_cd in ('UNPROCD', 'DTCTD', 'MNL', 'MNLO')
3939 order by lf_evt_ocrd_dt desc;
3940
3941 l_prog_count number;
3942 l_plan_count number;
3943 l_oipl_count number;
3944 l_person_count number;
3945 l_plan_nip_count number;
3946 l_oipl_nip_count number;
3947 l_errbuf varchar2(1000);
3948 l_retcode number;
3949 l_proc VARCHAR2(72);
3950 l_lf_evt_ocrd_dt DATE;
3951 l_crrnt_per_in_ler_id number;
3952 l_role_id number;
3953
3954 l_new_person_id number;
3955 l_new_business_group_id number;
3956
3957 l_life_evt_ocrd_dt DATE;
3958 l_lf_evt_exists VARCHAR2(2);
3959 l_ptnl_le c_ptnl_le%rowtype;
3960
3961
3962 BEGIN
3963 --
3964 --
3965 if g_debug then
3966 l_proc := g_package || '.ss_whatif_process';
3967 hr_utility.set_location('Entering: ' || l_proc,10);
3968 hr_utility.set_location('p_called_from : ' || p_called_from,15);
3969 hr_utility.set_location('p_person_id : ' || to_char(p_person_id),20);
3970 end if;
3971 -- Date used for role based restriction;
3972 g_effective_date := p_effective_date;
3973 -- Populate Current Benefits
3974 hr_utility.set_location('BKKKKK Entering '|| l_proc,10);
3975
3976 hr_utility.set_location('BKKKKK populate_hierarchy ',10);
3977
3978 populate_hierarchy(p_person_id => p_person_id
3979 ,p_effective_date => p_effective_date
3980 ,p_business_group_id => p_business_group_id
3981 ,p_ler_id => NULL);
3982
3983 hr_utility.set_location('BKKKKK set current_ben ',10);
3984
3985 savepoint current_benefits;
3986
3987 BEGIN
3988 --
3989 fnd_msg_pub.initialize;
3990
3991 -- Ignore the already detected/unprocessed life event for the person as of the effective date
3992 hr_utility.set_location('BKKKKK void potential ',10);
3993 void_potential_life_events(p_person_id => p_person_id
3994 ,p_business_group_id => p_business_group_id
3995 ,p_effective_date => p_effective_date
3996 );
3997
3998 -- Ignore the already active life events for the person
3999 hr_utility.set_location('BKKKKK set current_ben ',10);
4000 void_active_life_events(p_person_id => p_person_id
4001 ,p_business_group_id => p_business_group_id
4002 ,p_effective_date => p_effective_date
4003 );
4004
4005 -- Post the data changes for the particular transaction
4006 hr_utility.set_location('BKKKKK p_called_from '||p_called_from,10);
4007 IF p_called_from='SSHR' THEN
4008 --
4009 post_data_changes(p_transaction_id => p_transaction_id
4010 ,p_effective_date => p_effective_date
4011 ,p_person_id => l_new_person_id
4012 ,p_business_group_id => l_new_business_group_id);
4013
4014 ELSIF p_called_from ='SSBEN' THEN
4015 --
4016 post_ben_changes(p_transaction_id => p_transaction_id
4017 ,p_person_id => p_person_id
4018 ,p_business_group_id => p_business_group_id
4019 ,p_effective_date => p_effective_date
4020 ,p_session_date => p_session_date
4021 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
4022 );
4023
4024 open c_ptnl_le(l_lf_evt_ocrd_dt);
4025 fetch c_ptnl_le into l_ptnl_le;
4026 if (nvl(l_ptnl_le.lf_evt_ocrd_dt,(l_lf_evt_ocrd_dt-1)) > l_lf_evt_ocrd_dt) then
4027 l_lf_evt_ocrd_dt := l_ptnl_le.lf_evt_ocrd_dt;
4028 end if;
4029 close c_ptnl_le;
4030
4031 -- p_effective_date:= l_lf_evt_ocrd_dt ;
4032 IF p_ler_id <>0 THEN
4033 hr_utility.set_location('BKKKKK void_conflict_life_events ',10);
4034 void_conflict_life_events(
4035 p_person_id => p_person_id
4036 ,p_business_group_id => p_business_group_id
4037 ,p_winning_ler_id => p_ler_id
4038 ,p_effective_date => p_effective_date
4039 ) ;
4040 p_ler_id :=null;
4041 END IF;
4042 END IF;
4043
4044 -- Check whether potential life events have been detected
4045 hr_utility.set_location('BKKKKK chk_potential_life_events ',10);
4046 l_lf_evt_exists := chk_potential_life_events(p_person_id => nvl(l_new_person_id,p_person_id)
4047 ,p_business_group_id => nvl(l_new_business_group_id,p_business_group_id)
4048 ,p_lf_evt_ocrd_dt => l_life_evt_ocrd_dt);
4049
4050
4051 IF (l_lf_evt_exists = 'Y') THEN
4052 -- Set Life Event Occurred Date
4053 IF ( p_called_from = 'SSHR' ) AND (l_life_evt_ocrd_dt > p_effective_date) THEN
4054 l_lf_evt_ocrd_dt := l_life_evt_ocrd_dt ;
4055 END IF;
4056
4057 -- Call the benmngle
4058 ben_manage_life_events.g_modified_mode := null;
4059 hr_utility.set_location('BKKKKK p_watif_manage_life_events ',10);
4060 ben_on_line_lf_evt.p_watif_manage_life_events(
4061 p_person_id => nvl(l_new_person_id,p_person_id)
4062 ,p_effective_date => nvl(l_lf_evt_ocrd_dt,p_effective_date)
4063 ,p_business_group_id => nvl(l_new_business_group_id,p_business_group_id)
4064 ,p_pgm_id => null
4065 ,p_pl_id => null
4066 ,p_mode => 'L'
4067 ,p_derivable_factors => 'Y'
4068 ,p_prog_count => l_prog_count
4069 ,p_plan_count => l_plan_count
4070 ,p_oipl_count => l_oipl_count
4071 ,p_person_count => l_person_count
4072 ,p_plan_nip_count => l_plan_nip_count
4073 ,p_oipl_nip_count => l_oipl_nip_count
4074 ,p_ler_id => p_ler_id
4075 ,p_errbuf => l_errbuf
4076 ,p_retcode => l_retcode);
4077
4078 -- Compare and populate proposed benefits
4079 populate_proposed_hierarchy(p_person_id => nvl(l_new_person_id,p_person_id)
4080 ,p_effective_date => nvl(l_lf_evt_ocrd_dt,p_effective_date)
4081 ,p_business_group_id => nvl(l_new_business_group_id,p_business_group_id)
4082 ,p_ler_id => p_ler_id);
4083 ELSE
4084 -- fnd_message.set_name('PQH','PQH_PA_WHTF_NO_PTNL_LER');
4085 fnd_message.set_name('BEN','BEN_92540_NOONE_TO_PROCESS_CM');
4086 fnd_message.raise_error;
4087 END IF;
4088
4089
4090 EXCEPTION
4091 --
4092 WHEN OTHERS THEN fnd_msg_pub.add;
4093
4094 END;
4095
4096 -- Rollback Posting to APIs and BENMNGLE run
4097 hr_utility.set_location('BKKKKK rollback current_benefits ',10);
4098
4099 rollback to current_benefits;
4100
4101 -- Purge Data from the table
4102
4103 purge_table_data;
4104 -- Get role_id of user
4105 get_user_role(
4106 p_user_id =>p_login_id
4107 ,p_user_type =>p_login_type
4108 ,p_business_group_id =>p_business_group_id
4109 ,p_role_id =>l_role_id
4110 );
4111 IF l_role_id is not null then g_role_id :=l_role_id; END IF;
4112 -- Dump data from PL / SQL table to pqh_pa_watif_results;
4113
4114 populate_table(p_person_id => nvl(l_new_person_id,p_person_id)
4115 ,p_business_group_id => nvl(l_new_business_group_id,p_business_group_id)
4116 ,p_transaction_id => p_transaction_id
4117 ,p_ler_id => p_ler_id
4118 ,p_whatif_results_batch_id => p_whatif_results_batch_id);
4119
4120 -- Clear the PL/SQL Table instance
4121
4122 g_hierarchy.DELETE;
4123
4124 if g_debug then
4125 hr_utility.set_location('Leaving: ' || l_proc,20);
4126 end if;
4127 --
4128 EXCEPTION
4129 --
4130 WHEN OTHERS THEN
4131 BEGIN
4132 --
4133 g_hierarchy.DELETE;
4134 RAISE;
4135 --
4136 END;
4137 --
4138 END ss_whatif_process;
4139 -------------------------------------------------------------------------------------------------
4140
4141 PROCEDURE validate_data_changes(
4142 p_person_id IN NUMBER
4143 ,p_business_group_id IN NUMBER
4144 ,p_effective_date IN DATE
4145 ,p_session_date IN DATE
4146 ,p_transaction_id IN NUMBER
4147 ,p_whatif_results_batch_id OUT NOCOPY NUMBER
4148 ) IS
4149 l_proc VARCHAR2(72);
4150 l_num_detected_ler NUMBER :=0;
4151 l_conflict_life_events BOOLEAN :=false;
4152 l_flag VARCHAR2(1);
4153 l_lf_evt_ocrd_dt DATE;
4154 BEGIN
4155 if g_debug then
4156 l_proc := g_package || '.validate_data_changes';
4157 hr_utility.set_location('Entering: ' || l_proc,10);
4158 hr_utility.set_location('p_person_id : ' || to_char(p_person_id),15);
4159 end if;
4160
4161 savepoint current_life_events;
4162
4163 fnd_msg_pub.initialize;
4164
4165 -- Ignore the already detected/unprocessed life event for the person as of the effective date
4166 if g_debug then
4167 hr_utility.set_location('Call to void Potential LE',20);
4168 end if;
4169 void_potential_life_events(p_person_id => p_person_id
4170 ,p_business_group_id => p_business_group_id
4171 ,p_effective_date => p_effective_date
4172 );
4173 -- Post the data changes for the particular transaction
4174 if g_debug then
4175 hr_utility.set_location('Call to post ben changes ' ,25);
4176 end if;
4177 post_ben_changes(p_transaction_id => p_transaction_id
4178 ,p_person_id => p_person_id
4179 ,p_business_group_id => p_business_group_id
4180 ,p_effective_date => p_effective_date
4181 ,p_session_date => p_session_date
4182 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
4183 );
4184
4185
4186 -- Check whether potential life events have been detected
4187 if g_debug then
4188 hr_utility.set_location('Call to Check conflict LE ' || l_proc,30);
4189 end if;
4190 l_conflict_life_events :=chk_conflict_life_events( p_person_id =>p_person_id
4191 ,p_business_group_id =>p_business_group_id
4192 ,p_effective_date =>p_effective_date
4193 ,p_flag =>l_flag);
4194 if g_debug and l_conflict_life_events then
4195
4196 hr_utility.set_location('cnflt le detected ' ,25);
4197 end if;
4198
4199 -- Rollback Posting to APIs
4200
4201 rollback to current_life_events;
4202
4203 -- Dump data from PL / SQL table to pqh_pa_watif_results;
4204 IF l_conflict_life_events THEN
4205 -- Conflicting LE's are there
4206 BEGIN
4207 SELECT pqh_pa_whatif_results_s.nextval INTO p_whatif_results_batch_id from dual;
4208 EXCEPTION
4209 WHEN OTHERS THEN
4210 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4211 hr_utility.set_message_token('ROUTINE', l_proc);
4212 hr_utility.set_message_token('REASON', SQLERRM);
4213 raise;
4214 END;
4215 FOR i IN g_hierarchy.FIRST .. g_hierarchy.LAST
4216 LOOP
4217 IF g_hierarchy(i).hierarchy_type =l_flag THEN
4218 INSERT INTO PQH_PA_WHATIF_RESULTS (
4219 WHATIF_RESULTS_BATCH_ID
4220 ,PERSON_ID
4221 ,BUSINESS_GROUP_ID
4222 ,LER_ID
4223 ,NAME
4224 )
4225 VALUES (
4226 p_whatif_results_batch_id
4227 ,p_person_id
4228 ,p_business_group_id
4229 ,g_hierarchy(i).ler_id
4230 ,g_hierarchy(i).NAME
4231 );
4232 END IF;
4233 END LOOP;
4234 ELSE
4235 p_whatif_results_batch_id := 0;
4236
4237 END IF;
4238 -- Clear the PL/SQL Table instance
4239
4240 g_hierarchy.DELETE;
4241
4242 if g_debug then
4243 hr_utility.set_location('Leaving: ' || l_proc,100);
4244 end if;
4245 --
4246
4247 EXCEPTION
4248 WHEN OTHERS THEN
4249 if g_debug then
4250 hr_utility.set_location('Exception: ' ,50);
4251 end if;
4252 g_hierarchy.DELETE;
4253 p_whatif_results_batch_id :=-2;
4254 fnd_msg_pub.add;
4255 END validate_data_changes;
4256 -----------------------------------------------------------------------------------------------------------------
4257 PROCEDURE prepare_transaction(
4258 p_person_id IN NUMBER
4259 ,p_txn_id IN OUT NOCOPY NUMBER
4260 ) IS
4261 CURSOR csr_trans_steps(txn_id IN NUMBER) IS
4262 SELECT transaction_step_id
4263 FROM hr_api_transaction_steps
4264 WHERE transaction_id=txn_id;
4265
4266 l_proc VARCHAR2(72);
4267 BEGIN
4268
4269 if g_debug then
4270 l_proc := g_package || '.prepare_transaction';
4271 hr_utility.set_location('Entering: ' || l_proc,10);
4272 hr_utility.set_location('p_txn_id : ' || to_char(p_txn_id),15);
4273 end if;
4274 if (p_txn_id=0) THEN
4275 hr_transaction_api.create_transaction (
4276 p_creator_person_id =>p_person_id
4277 ,p_transaction_privilege =>'PRIVATE'
4278 ,p_function_id => 1
4279 ,p_selected_person_id =>p_person_id
4280 ,p_transaction_effective_date =>sysdate
4281 ,p_process_name =>'SSBENWHATIF'
4282 ,p_status =>'ACTIVE'
4283 ,p_transaction_id =>p_txn_id);
4284 ELSE
4285 FOR rec_typ IN csr_trans_steps(p_txn_id)
4286 LOOP
4287 DELETE FROM hr_api_transaction_values
4288 WHERE transaction_step_id=rec_typ.transaction_step_id;
4289 END LOOP;
4290 DELETE FROM hr_api_transaction_steps
4291 WHERE transaction_id=p_txn_id;
4292 END IF;
4293 if g_debug then
4294 hr_utility.set_location('Leaving: ' || l_proc,50);
4295 end if;
4296 END prepare_transaction;
4297 -------------------------------------------------------------------------------------------------------------
4298 FUNCTION get_first_label(
4299 p_ler_id IN NUMBER
4300 ,p_effective_date IN DATE
4301 )
4302 RETURN VARCHAR2 IS
4303 Cursor csr_watif_labels IS
4304 Select whatif_lbl_txt label
4305 From ben_per_info_chg_cs_ler_f a,
4306 ben_ler_per_info_cs_ler_f b
4307 Where a.PER_INFO_CHG_CS_LER_ID=b.PER_INFO_CHG_CS_LER_ID
4308 and b.ler_id=p_ler_id
4309 and a.WHATIF_LBL_TXT is not null
4310 and p_effective_date between a.effective_start_date and a.effective_end_date
4311 and p_effective_date between b.effective_start_date and b.effective_end_date;
4312
4313 l_label ben_per_info_chg_cs_ler_f.WHATIF_LBL_TXT%TYPE;
4314
4315 BEGIN
4316 OPEN csr_watif_labels;
4317 FETCH csr_watif_labels into l_label;
4318 CLOSE csr_watif_labels;
4319 RETURN l_label;
4320 END get_first_label;
4321 END pqh_pa_whatif_process;