[Home] [Help]
PACKAGE BODY: APPS.BEN_PERSON_LIFE_EVENT_API
Source
4 -- Package Variables
1 Package Body ben_Person_Life_Event_api as
2 /* $Header: bepilapi.pkb 120.0 2005/05/28 10:49:37 appldev noship $ */
3 --
5 --
6 g_package varchar2(33) := ' ben_Person_Life_Event_api.';
10 -- ----------------------------------------------------------------------------
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< derive_PIL_statcd_dates >-----------------------|
11 --
12 procedure derive_PIL_statcd_dates
13 (p_per_in_ler_stat_cd in varchar2
14 ,p_effective_date in date
15 ,p_procd_dt in out NOCOPY date
16 ,p_strtd_dt in out NOCOPY date
17 ,p_voidd_dt in out NOCOPY date
18 ,p_bckt_dt in out NOCOPY date
19 ,p_clsd_dt in out NOCOPY date) is
20 --
21 -- Declare cursors and local variables
22 --
23 l_proc varchar2(72) := g_package||'derive_PIL_statcd_dates';
24 --
25 begin
26 --
27 hr_utility.set_location('Entering:'|| l_proc, 10);
28 --
29 -- Set OUT parameters
30 --
31 if p_per_in_ler_stat_cd = 'PROCD' then
32 --
33 p_procd_dt := p_effective_date;
34 elsif p_per_in_ler_stat_cd = 'STRTD' then
35 p_strtd_dt := p_effective_date;
36 elsif p_per_in_ler_stat_cd = 'VOIDD' then
37 p_voidd_dt := p_effective_date;
38 elsif p_per_in_ler_stat_cd = 'BCKDT' then
39 --
40 -- Bug : 5231 : Set the backed out date to sysdate
41 -- rather than p_effective_date.
42 --
43 p_bckt_dt := trunc(sysdate);
44 elsif p_per_in_ler_stat_cd = 'CLSD' then
45 p_clsd_dt := p_effective_date;
46 end if;
47 --
48 hr_utility.set_location(' Leaving:'||l_proc, 70);
49 --
50 end derive_PIL_statcd_dates;
51 --
52 --
53 -- ----------------------------------------------------------------------------
54 -- |------------------------< create_Person_Life_Event >----------------------|
55 -- ----------------------------------------------------------------------------
56 --
57 procedure create_Person_Life_Event
58 (p_validate in boolean default false
59 ,p_per_in_ler_id out NOCOPY number
60 ,p_per_in_ler_stat_cd in varchar2 default null
61 ,p_prvs_stat_cd in varchar2 default null
62 ,p_lf_evt_ocrd_dt in date default null
63 ,p_trgr_table_pk_id in number default null --ABSE changes
64 ,p_procd_dt out NOCOPY date
65 ,p_strtd_dt out NOCOPY date
66 ,p_voidd_dt out NOCOPY date
67 ,p_bckt_dt in date default null
68 ,p_clsd_dt in date default null
69 ,p_ntfn_dt in date default null
70 ,p_ptnl_ler_for_per_id in number default null
71 ,p_bckt_per_in_ler_id in number default null
72 ,p_ler_id in number default null
73 ,p_person_id in number default null
74 ,p_business_group_id in number default null
75 ,p_ASSIGNMENT_ID in number default null
76 ,p_WS_MGR_ID in number default null
77 ,p_GROUP_PL_ID in number default null
78 ,p_MGR_OVRID_PERSON_ID in number default null
79 ,p_MGR_OVRID_DT in date default null
80 ,p_pil_attribute_category in varchar2 default null
81 ,p_pil_attribute1 in varchar2 default null
82 ,p_pil_attribute2 in varchar2 default null
83 ,p_pil_attribute3 in varchar2 default null
84 ,p_pil_attribute4 in varchar2 default null
85 ,p_pil_attribute5 in varchar2 default null
86 ,p_pil_attribute6 in varchar2 default null
87 ,p_pil_attribute7 in varchar2 default null
88 ,p_pil_attribute8 in varchar2 default null
89 ,p_pil_attribute9 in varchar2 default null
90 ,p_pil_attribute10 in varchar2 default null
91 ,p_pil_attribute11 in varchar2 default null
92 ,p_pil_attribute12 in varchar2 default null
93 ,p_pil_attribute13 in varchar2 default null
94 ,p_pil_attribute14 in varchar2 default null
95 ,p_pil_attribute15 in varchar2 default null
96 ,p_pil_attribute16 in varchar2 default null
97 ,p_pil_attribute17 in varchar2 default null
98 ,p_pil_attribute18 in varchar2 default null
99 ,p_pil_attribute19 in varchar2 default null
100 ,p_pil_attribute20 in varchar2 default null
101 ,p_pil_attribute21 in varchar2 default null
102 ,p_pil_attribute22 in varchar2 default null
103 ,p_pil_attribute23 in varchar2 default null
104 ,p_pil_attribute24 in varchar2 default null
105 ,p_pil_attribute25 in varchar2 default null
106 ,p_pil_attribute26 in varchar2 default null
107 ,p_pil_attribute27 in varchar2 default null
108 ,p_pil_attribute28 in varchar2 default null
109 ,p_pil_attribute29 in varchar2 default null
110 ,p_pil_attribute30 in varchar2 default null
111 ,p_request_id in number default null
112 ,p_program_application_id in number default null
113 ,p_program_id in number default null
114 ,p_program_update_date in date default null
115 ,p_object_version_number out NOCOPY number
116 ,p_effective_date in date) is
117 --
118 -- Declare cursors and local variables
119 --
120 -- Cursor to pick suspended enrollment results for the person.
124 pen.object_version_number,
121 --
122 cursor c_sspndd_rslt is
123 select pen.prtt_enrt_rslt_id,
125 pil.per_in_ler_id
126 from ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
127 where pil.person_id = p_person_id
128 and pil.per_in_ler_id = pen.per_in_ler_id
129 --Start Bug 2688172
130 and pil.lf_evt_ocrd_dt <= p_lf_evt_ocrd_dt
131 --End Bug 2688172
132 and pen.sspndd_flag = 'Y'
133 and pen.enrt_cvg_thru_dt = hr_api.g_eot
134 and pen.effective_end_date = hr_api.g_eot
135 and pen.prtt_enrt_rslt_stat_cd is null
136 and p_effective_date
137 between pen.effective_start_date
138 and pen.effective_end_date;
139 --
140 l_rslt_ovn number(15);
141 l_dummy_dt date;
142 l_dummy_bool boolean;
143 l_still_sspndd varchar2(30);
144 --
145 l_proc varchar2(72) := g_package||'create_Person_Life_Event';
146 --
147 l_object_version_number number;
148 l_per_in_ler_id number;
149 --
150 l_procd_dt date := null;
151 l_strtd_dt date := null;
152 l_voidd_dt date := null;
153 l_bckt_dt date := null;
154 l_clsd_dt date := null;
155 --
156 begin
157 --
158 hr_utility.set_location('Entering:'|| l_proc, 10);
159 --
160 -- Issue a savepoint if operating in validation only mode
161 --
162 savepoint create_Person_Life_Event;
163 --
164 hr_utility.set_location(l_proc, 20);
165 --
166 -- Process Logic
167 --
168 begin
169 --
170 -- Start of API User Hook for the before hook of create_Person_Life_Event
171 --
172 ben_Person_Life_Event_bk1.create_Person_Life_Event_b
173 (p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
174 ,p_prvs_stat_cd => p_prvs_stat_cd
175 ,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
176 ,p_trgr_table_pk_id => p_trgr_table_pk_id --ABSE changes
177 ,p_procd_dt => trunc(p_procd_dt)
178 ,p_strtd_dt => trunc(p_strtd_dt)
179 ,p_voidd_dt => trunc(p_voidd_dt)
180 ,p_bckt_dt => trunc(p_bckt_dt)
181 ,p_clsd_dt => trunc(p_clsd_dt)
182 ,p_ntfn_dt => trunc(p_ntfn_dt)
183 ,p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
184 ,p_bckt_per_in_ler_id => p_bckt_per_in_ler_id
185 ,p_ler_id => p_ler_id
186 ,p_person_id => p_person_id
187 ,p_business_group_id => p_business_group_id
188 ,p_ASSIGNMENT_ID => p_ASSIGNMENT_ID
189 ,p_WS_MGR_ID => p_WS_MGR_ID
190 ,p_GROUP_PL_ID => p_GROUP_PL_ID
191 ,p_MGR_OVRID_PERSON_ID => p_MGR_OVRID_PERSON_ID
192 ,p_MGR_OVRID_DT => p_MGR_OVRID_DT
193 ,p_pil_attribute_category => p_pil_attribute_category
194 ,p_pil_attribute1 => p_pil_attribute1
195 ,p_pil_attribute2 => p_pil_attribute2
196 ,p_pil_attribute3 => p_pil_attribute3
197 ,p_pil_attribute4 => p_pil_attribute4
198 ,p_pil_attribute5 => p_pil_attribute5
199 ,p_pil_attribute6 => p_pil_attribute6
200 ,p_pil_attribute7 => p_pil_attribute7
201 ,p_pil_attribute8 => p_pil_attribute8
202 ,p_pil_attribute9 => p_pil_attribute9
203 ,p_pil_attribute10 => p_pil_attribute10
204 ,p_pil_attribute11 => p_pil_attribute11
205 ,p_pil_attribute12 => p_pil_attribute12
206 ,p_pil_attribute13 => p_pil_attribute13
207 ,p_pil_attribute14 => p_pil_attribute14
208 ,p_pil_attribute15 => p_pil_attribute15
209 ,p_pil_attribute16 => p_pil_attribute16
210 ,p_pil_attribute17 => p_pil_attribute17
211 ,p_pil_attribute18 => p_pil_attribute18
212 ,p_pil_attribute19 => p_pil_attribute19
213 ,p_pil_attribute20 => p_pil_attribute20
214 ,p_pil_attribute21 => p_pil_attribute21
215 ,p_pil_attribute22 => p_pil_attribute22
216 ,p_pil_attribute23 => p_pil_attribute23
217 ,p_pil_attribute24 => p_pil_attribute24
218 ,p_pil_attribute25 => p_pil_attribute25
219 ,p_pil_attribute26 => p_pil_attribute26
220 ,p_pil_attribute27 => p_pil_attribute27
221 ,p_pil_attribute28 => p_pil_attribute28
222 ,p_pil_attribute29 => p_pil_attribute29
223 ,p_pil_attribute30 => p_pil_attribute30
224 ,p_request_id => p_request_id
225 ,p_program_application_id => p_program_application_id
226 ,p_program_id => p_program_id
227 ,p_program_update_date => trunc(p_program_update_date)
228 ,p_effective_date => trunc(p_effective_date));
229 --
230 exception
231 --
232 when hr_api.cannot_find_prog_unit then
233 --
234 hr_api.cannot_find_prog_unit_error
235 (p_module_name => 'CREATE_Person_Life_Event'
236 ,p_hook_type => 'BP');
237 --
238 -- End of API User Hook for the before hook of create_Person_Life_Event
239 --
240 end;
241 --
242 -- MM. deleted the action items cleanup code for CFW
243 --
247 (p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
244 -- Derive date column values for the per in ler status code
245 --
246 derive_PIL_statcd_dates
248 ,p_effective_date => p_effective_date
249 ,p_procd_dt => l_procd_dt
250 ,p_strtd_dt => l_strtd_dt
251 ,p_voidd_dt => l_voidd_dt
252 ,p_bckt_dt => l_bckt_dt
253 ,p_clsd_dt => l_clsd_dt);
254 --
255 ben_pil_ins.ins
256 (p_per_in_ler_id => l_per_in_ler_id
257 ,p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
258 ,p_prvs_stat_cd => p_prvs_stat_cd
259 ,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
260 ,p_trgr_table_pk_id => p_trgr_table_pk_id --ABSE changes
261 ,p_procd_dt => l_procd_dt
262 ,p_strtd_dt => l_strtd_dt
263 ,p_voidd_dt => l_voidd_dt
264 ,p_bckt_dt => l_bckt_dt
265 ,p_clsd_dt => l_clsd_dt
266 ,p_ntfn_dt => trunc(p_ntfn_dt)
267 ,p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
268 ,p_bckt_per_in_ler_id => p_bckt_per_in_ler_id
269 ,p_ler_id => p_ler_id
270 ,p_person_id => p_person_id
271 ,p_business_group_id => p_business_group_id
272 ,p_ASSIGNMENT_ID => p_ASSIGNMENT_ID
273 ,p_WS_MGR_ID => p_WS_MGR_ID
274 ,p_GROUP_PL_ID => p_GROUP_PL_ID
275 ,p_MGR_OVRID_PERSON_ID => p_MGR_OVRID_PERSON_ID
276 ,p_MGR_OVRID_DT => p_MGR_OVRID_DT
277 ,p_pil_attribute_category => p_pil_attribute_category
278 ,p_pil_attribute1 => p_pil_attribute1
279 ,p_pil_attribute2 => p_pil_attribute2
280 ,p_pil_attribute3 => p_pil_attribute3
281 ,p_pil_attribute4 => p_pil_attribute4
282 ,p_pil_attribute5 => p_pil_attribute5
283 ,p_pil_attribute6 => p_pil_attribute6
284 ,p_pil_attribute7 => p_pil_attribute7
285 ,p_pil_attribute8 => p_pil_attribute8
286 ,p_pil_attribute9 => p_pil_attribute9
287 ,p_pil_attribute10 => p_pil_attribute10
288 ,p_pil_attribute11 => p_pil_attribute11
289 ,p_pil_attribute12 => p_pil_attribute12
290 ,p_pil_attribute13 => p_pil_attribute13
291 ,p_pil_attribute14 => p_pil_attribute14
292 ,p_pil_attribute15 => p_pil_attribute15
293 ,p_pil_attribute16 => p_pil_attribute16
294 ,p_pil_attribute17 => p_pil_attribute17
295 ,p_pil_attribute18 => p_pil_attribute18
296 ,p_pil_attribute19 => p_pil_attribute19
297 ,p_pil_attribute20 => p_pil_attribute20
298 ,p_pil_attribute21 => p_pil_attribute21
299 ,p_pil_attribute22 => p_pil_attribute22
300 ,p_pil_attribute23 => p_pil_attribute23
301 ,p_pil_attribute24 => p_pil_attribute24
302 ,p_pil_attribute25 => p_pil_attribute25
303 ,p_pil_attribute26 => p_pil_attribute26
304 ,p_pil_attribute27 => p_pil_attribute27
305 ,p_pil_attribute28 => p_pil_attribute28
306 ,p_pil_attribute29 => p_pil_attribute29
307 ,p_pil_attribute30 => p_pil_attribute30
308 ,p_request_id => p_request_id
309 ,p_program_application_id => p_program_application_id
310 ,p_program_id => p_program_id
311 ,p_program_update_date => trunc(p_program_update_date)
312 ,p_object_version_number => l_object_version_number
313 ,p_effective_date => trunc(p_effective_date));
314 --
315 begin
316 --
317 -- Start of API User Hook for the after hook of create_Person_Life_Event
318 --
319 ben_Person_Life_Event_bk1.create_Person_Life_Event_a
320 (p_per_in_ler_id => l_per_in_ler_id
321 ,p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
322 ,p_prvs_stat_cd => p_prvs_stat_cd
323 ,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
324 ,p_trgr_table_pk_id => p_trgr_table_pk_id --ABSE changes
325 ,p_procd_dt => l_procd_dt
326 ,p_strtd_dt => l_strtd_dt
327 ,p_voidd_dt => l_voidd_dt
328 ,p_bckt_dt => l_bckt_dt
329 ,p_clsd_dt => l_clsd_dt
330 ,p_ntfn_dt => trunc(p_ntfn_dt)
331 ,p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
332 ,p_bckt_per_in_ler_id => p_bckt_per_in_ler_id
333 ,p_ler_id => p_ler_id
334 ,p_person_id => p_person_id
335 ,p_business_group_id => p_business_group_id
336 ,p_ASSIGNMENT_ID => p_ASSIGNMENT_ID
337 ,p_WS_MGR_ID => p_WS_MGR_ID
338 ,p_GROUP_PL_ID => p_GROUP_PL_ID
339 ,p_MGR_OVRID_PERSON_ID => p_MGR_OVRID_PERSON_ID
340 ,p_MGR_OVRID_DT => p_MGR_OVRID_DT
341 ,p_pil_attribute_category => p_pil_attribute_category
342 ,p_pil_attribute1 => p_pil_attribute1
343 ,p_pil_attribute2 => p_pil_attribute2
344 ,p_pil_attribute3 => p_pil_attribute3
345 ,p_pil_attribute4 => p_pil_attribute4
346 ,p_pil_attribute5 => p_pil_attribute5
347 ,p_pil_attribute6 => p_pil_attribute6
351 ,p_pil_attribute10 => p_pil_attribute10
348 ,p_pil_attribute7 => p_pil_attribute7
349 ,p_pil_attribute8 => p_pil_attribute8
350 ,p_pil_attribute9 => p_pil_attribute9
352 ,p_pil_attribute11 => p_pil_attribute11
353 ,p_pil_attribute12 => p_pil_attribute12
354 ,p_pil_attribute13 => p_pil_attribute13
355 ,p_pil_attribute14 => p_pil_attribute14
356 ,p_pil_attribute15 => p_pil_attribute15
357 ,p_pil_attribute16 => p_pil_attribute16
358 ,p_pil_attribute17 => p_pil_attribute17
359 ,p_pil_attribute18 => p_pil_attribute18
360 ,p_pil_attribute19 => p_pil_attribute19
361 ,p_pil_attribute20 => p_pil_attribute20
362 ,p_pil_attribute21 => p_pil_attribute21
363 ,p_pil_attribute22 => p_pil_attribute22
364 ,p_pil_attribute23 => p_pil_attribute23
365 ,p_pil_attribute24 => p_pil_attribute24
366 ,p_pil_attribute25 => p_pil_attribute25
367 ,p_pil_attribute26 => p_pil_attribute26
368 ,p_pil_attribute27 => p_pil_attribute27
369 ,p_pil_attribute28 => p_pil_attribute28
370 ,p_pil_attribute29 => p_pil_attribute29
371 ,p_pil_attribute30 => p_pil_attribute30
372 ,p_request_id => p_request_id
373 ,p_program_application_id => p_program_application_id
374 ,p_program_id => p_program_id
375 ,p_program_update_date => trunc(p_program_update_date)
376 ,p_object_version_number => l_object_version_number
377 ,p_effective_date => trunc(p_effective_date));
378 --
379 exception
380 --
381 when hr_api.cannot_find_prog_unit then
382 --
383 hr_api.cannot_find_prog_unit_error
384 (p_module_name => 'CREATE_Person_Life_Event'
385 ,p_hook_type => 'AP');
386 --
387 -- End of API User Hook for the after hook of create_Person_Life_Event
388 --
389 end;
390 --
391 hr_utility.set_location(l_proc, 60);
392 --
393 -- When in validation only mode raise the Validate_Enabled exception
394 --
395 if p_validate then
396 raise hr_api.validate_enabled;
397 end if;
398 --
399 -- Set all output arguments
400 --
401 p_per_in_ler_id := l_per_in_ler_id;
402 p_object_version_number := l_object_version_number;
403 p_procd_dt := l_procd_dt;
404 p_strtd_dt := l_strtd_dt;
405 p_voidd_dt := l_voidd_dt;
406 --
407 hr_utility.set_location(' Leaving:'||l_proc, 70);
408 --
409 exception
410 --
411 when hr_api.validate_enabled then
412 --
413 -- As the Validate_Enabled exception has been raised
414 -- we must rollback to the savepoint
415 --
416 ROLLBACK TO create_Person_Life_Event;
417 --
418 -- Only set output warning arguments
419 -- (Any key or derived arguments must be set to null
420 -- when validation only mode is being used.)
421 --
422 p_per_in_ler_id := null;
423 p_object_version_number := null;
424 p_procd_dt := null;
425 p_strtd_dt := null;
426 p_voidd_dt := null;
427 hr_utility.set_location(' Leaving:'||l_proc, 80);
428 --
429 when others then
430 --
431 -- A validation or unexpected error has occured
432 --
433 ROLLBACK TO create_Person_Life_Event;
434 raise;
435 --
436 end create_Person_Life_Event;
437 --
438 -- ----------------------------------------------------------------------------
439 -- |------------------------< create_Person_Life_Event_perf >-----------------|
440 -- ----------------------------------------------------------------------------
441 --
442 procedure create_Person_Life_Event_perf
443 (p_validate in boolean default false
444 ,p_per_in_ler_id out NOCOPY number
445 ,p_per_in_ler_stat_cd in varchar2 default null
446 ,p_prvs_stat_cd in varchar2 default null
447 ,p_lf_evt_ocrd_dt in date default null
448 ,p_trgr_table_pk_id in number default null --ABSE changes
449 ,p_procd_dt out NOCOPY date
450 ,p_strtd_dt out NOCOPY date
451 ,p_voidd_dt out NOCOPY date
452 ,p_bckt_dt in date default null
453 ,p_clsd_dt in date default null
454 ,p_ntfn_dt in date default null
455 ,p_ptnl_ler_for_per_id in number default null
456 ,p_bckt_per_in_ler_id in number default null
457 ,p_ler_id in number default null
458 ,p_person_id in number default null
459 ,p_business_group_id in number default null
460 ,p_ASSIGNMENT_ID in number default null
461 ,p_WS_MGR_ID in number default null
462 ,p_GROUP_PL_ID in number default null
463 ,p_MGR_OVRID_PERSON_ID in number default null
464 ,p_MGR_OVRID_DT in date default null
465 ,p_pil_attribute_category in varchar2 default null
466 ,p_pil_attribute1 in varchar2 default null
467 ,p_pil_attribute2 in varchar2 default null
471 ,p_pil_attribute6 in varchar2 default null
468 ,p_pil_attribute3 in varchar2 default null
469 ,p_pil_attribute4 in varchar2 default null
470 ,p_pil_attribute5 in varchar2 default null
472 ,p_pil_attribute7 in varchar2 default null
473 ,p_pil_attribute8 in varchar2 default null
474 ,p_pil_attribute9 in varchar2 default null
475 ,p_pil_attribute10 in varchar2 default null
476 ,p_pil_attribute11 in varchar2 default null
477 ,p_pil_attribute12 in varchar2 default null
478 ,p_pil_attribute13 in varchar2 default null
479 ,p_pil_attribute14 in varchar2 default null
480 ,p_pil_attribute15 in varchar2 default null
481 ,p_pil_attribute16 in varchar2 default null
482 ,p_pil_attribute17 in varchar2 default null
483 ,p_pil_attribute18 in varchar2 default null
484 ,p_pil_attribute19 in varchar2 default null
485 ,p_pil_attribute20 in varchar2 default null
486 ,p_pil_attribute21 in varchar2 default null
487 ,p_pil_attribute22 in varchar2 default null
488 ,p_pil_attribute23 in varchar2 default null
489 ,p_pil_attribute24 in varchar2 default null
490 ,p_pil_attribute25 in varchar2 default null
491 ,p_pil_attribute26 in varchar2 default null
492 ,p_pil_attribute27 in varchar2 default null
493 ,p_pil_attribute28 in varchar2 default null
494 ,p_pil_attribute29 in varchar2 default null
495 ,p_pil_attribute30 in varchar2 default null
496 ,p_request_id in number default null
497 ,p_program_application_id in number default null
498 ,p_program_id in number default null
499 ,p_program_update_date in date default null
500 ,p_object_version_number out NOCOPY number
501 ,p_effective_date in date) is
502 --
503 -- Declare cursors and local variables
504 --
505 -- Cursor to pick suspended enrollment results for the person.
506 --
507 cursor c_sspndd_rslt is
508 select pen.prtt_enrt_rslt_id,
509 pen.object_version_number,
510 pil.per_in_ler_id
511 from ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
512 where pil.person_id = p_person_id
513 and pil.per_in_ler_id = pen.per_in_ler_id
514 --Start Bug 2688172
515 and pil.lf_evt_ocrd_dt <= p_lf_evt_ocrd_dt
516 -- End Bug 2688172
517 and pen.sspndd_flag = 'Y'
518 and pen.enrt_cvg_thru_dt = hr_api.g_eot
519 and pen.effective_end_date = hr_api.g_eot
520 and pen.prtt_enrt_rslt_stat_cd is null
521 and pen.business_group_id +0 = p_business_group_id
522 and p_effective_date
523 between pen.effective_start_date
524 and pen.effective_end_date;
525 --
526 l_rslt_ovn number(15);
527 l_dummy_dt date;
528 l_dummy_bool boolean;
529 l_still_sspndd varchar2(30);
530 --
531 l_proc varchar2(72) := g_package||'create_Person_Life_Event';
532 --
533 l_object_version_number number;
534 l_per_in_ler_id number;
535 --
536 l_procd_dt date := null;
537 l_strtd_dt date := null;
538 l_voidd_dt date := null;
539 l_bckt_dt date := null;
540 l_clsd_dt date := null;
541 --
542 begin
543 --
544 hr_utility.set_location('Entering:'|| l_proc, 10);
545 --
546 -- Issue a savepoint if operating in validation only mode
547 --
548 savepoint create_Person_Life_Event;
549 --
550 hr_utility.set_location(l_proc, 20);
551 --
552 -- Process Logic
553 --
554 begin
555 --
556 -- Start of API User Hook for the before hook of create_Person_Life_Event
557 --
558 ben_Person_Life_Event_bk1.create_Person_Life_Event_b
559 (p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
560 ,p_prvs_stat_cd => p_prvs_stat_cd
561 ,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
562 ,p_trgr_table_pk_id => p_trgr_table_pk_id --ABSE changes
563 ,p_procd_dt => trunc(p_procd_dt)
564 ,p_strtd_dt => trunc(p_strtd_dt)
565 ,p_voidd_dt => trunc(p_voidd_dt)
566 ,p_bckt_dt => trunc(p_bckt_dt)
567 ,p_clsd_dt => trunc(p_clsd_dt)
568 ,p_ntfn_dt => trunc(p_ntfn_dt)
569 ,p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
570 ,p_bckt_per_in_ler_id => p_bckt_per_in_ler_id
571 ,p_ler_id => p_ler_id
572 ,p_person_id => p_person_id
573 ,p_business_group_id => p_business_group_id
574 ,p_ASSIGNMENT_ID => p_ASSIGNMENT_ID
575 ,p_WS_MGR_ID => p_WS_MGR_ID
576 ,p_GROUP_PL_ID => p_GROUP_PL_ID
577 ,p_MGR_OVRID_PERSON_ID => p_MGR_OVRID_PERSON_ID
578 ,p_MGR_OVRID_DT => p_MGR_OVRID_DT
579 ,p_pil_attribute_category => p_pil_attribute_category
580 ,p_pil_attribute1 => p_pil_attribute1
581 ,p_pil_attribute2 => p_pil_attribute2
582 ,p_pil_attribute3 => p_pil_attribute3
586 ,p_pil_attribute7 => p_pil_attribute7
583 ,p_pil_attribute4 => p_pil_attribute4
584 ,p_pil_attribute5 => p_pil_attribute5
585 ,p_pil_attribute6 => p_pil_attribute6
587 ,p_pil_attribute8 => p_pil_attribute8
588 ,p_pil_attribute9 => p_pil_attribute9
589 ,p_pil_attribute10 => p_pil_attribute10
590 ,p_pil_attribute11 => p_pil_attribute11
591 ,p_pil_attribute12 => p_pil_attribute12
592 ,p_pil_attribute13 => p_pil_attribute13
593 ,p_pil_attribute14 => p_pil_attribute14
594 ,p_pil_attribute15 => p_pil_attribute15
595 ,p_pil_attribute16 => p_pil_attribute16
596 ,p_pil_attribute17 => p_pil_attribute17
597 ,p_pil_attribute18 => p_pil_attribute18
598 ,p_pil_attribute19 => p_pil_attribute19
599 ,p_pil_attribute20 => p_pil_attribute20
600 ,p_pil_attribute21 => p_pil_attribute21
601 ,p_pil_attribute22 => p_pil_attribute22
602 ,p_pil_attribute23 => p_pil_attribute23
603 ,p_pil_attribute24 => p_pil_attribute24
604 ,p_pil_attribute25 => p_pil_attribute25
605 ,p_pil_attribute26 => p_pil_attribute26
606 ,p_pil_attribute27 => p_pil_attribute27
607 ,p_pil_attribute28 => p_pil_attribute28
608 ,p_pil_attribute29 => p_pil_attribute29
609 ,p_pil_attribute30 => p_pil_attribute30
610 ,p_request_id => p_request_id
611 ,p_program_application_id => p_program_application_id
612 ,p_program_id => p_program_id
613 ,p_program_update_date => trunc(p_program_update_date)
614 ,p_effective_date => trunc(p_effective_date));
615 --
616 exception
617 --
618 when hr_api.cannot_find_prog_unit then
619 --
620 hr_api.cannot_find_prog_unit_error
621 (p_module_name => 'CREATE_Person_Life_Event'
622 ,p_hook_type => 'BP');
623 --
624 -- End of API User Hook for the before hook of create_Person_Life_Event
625 --
626 end;
627 --
628 -- BEGIN CODE FOR CLEANING UP SUSPENDED ENROLLMENTS PRIOR TO CREATING A NEW
629 -- PER IN LER
630 --
631 -- Loop through all the suspended enrollments for the person.
632 --
633 /* for l_rec in c_sspndd_rslt loop
634 --
635 -- Initialize variables for this iteration.
636 --
637 l_rslt_ovn := l_rec.object_version_number;
638 l_still_sspndd := 'N';
639 --
640 -- Call determine action items close open required action items and
641 -- unsuspend the enrollment.
642 --
643 ben_enrollment_action_items.determine_action_items
644 (p_prtt_enrt_rslt_id => l_rec.prtt_enrt_rslt_id
645 ,p_effective_date => p_effective_date
646 ,p_business_group_id => p_business_group_id
647 ,p_validate => FALSE
648 ,p_rslt_object_version_number => l_rslt_ovn
649 ,p_suspend_flag => l_still_sspndd
650 ,p_dpnt_actn_warning => l_dummy_bool
651 ,p_bnf_actn_warning => l_dummy_bool
652 ,p_ctfn_actn_warning => l_dummy_bool);
653 --
654 -- If the enrollment is still suspended after the call above, then delete
655 -- the enrollment, else leave it alone.
656 --
657 if l_still_sspndd = 'Y' then
658 --
659 ben_prtt_enrt_result_api.delete_enrollment
660 (p_validate => FALSE
661 ,p_per_in_ler_id => l_rec.per_in_ler_id
662 ,p_prtt_enrt_rslt_id => l_rec.prtt_enrt_rslt_id
663 ,p_business_group_id => p_business_group_id
664 ,p_effective_start_date => l_dummy_dt
665 ,p_effective_end_date => l_dummy_dt
666 ,p_object_version_number => l_rec.object_version_number
667 ,p_effective_date => p_effective_date
668 ,p_datetrack_mode => hr_api.g_delete
669 ,p_multi_row_validate => TRUE
670 ,p_source => 'bepilapi');
671 --
672 end if;
673 --
674 end loop;
675 --
676 -- END CODE FOR CLEANUP
677 */
678 --
679 -- Derive date column values for the per in ler status code
680 --
681 derive_PIL_statcd_dates
682 (p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
683 ,p_effective_date => p_effective_date
684 ,p_procd_dt => l_procd_dt
685 ,p_strtd_dt => l_strtd_dt
686 ,p_voidd_dt => l_voidd_dt
687 ,p_bckt_dt => l_bckt_dt
688 ,p_clsd_dt => l_clsd_dt);
689 --
690 select ben_per_in_ler_s.nextval
691 into l_per_in_ler_id
692 from sys.dual;
693 --
694 l_object_version_number := 1;
695 --
696 insert into ben_per_in_ler
697 (per_in_ler_id
698 ,per_in_ler_stat_cd
699 ,prvs_stat_cd
700 ,lf_evt_ocrd_dt
701 ,trgr_table_pk_id --ABSE changes
702 ,procd_dt
703 ,strtd_dt
704 ,voidd_dt
705 ,bckt_dt
706 ,clsd_dt
707 ,ntfn_dt
708 ,ptnl_ler_for_per_id
709 ,bckt_per_in_ler_id
710 ,ler_id
711 ,person_id
712 ,business_group_id
713 ,ASSIGNMENT_ID
714 ,WS_MGR_ID
715 ,GROUP_PL_ID
719 ,pil_attribute1
716 ,MGR_OVRID_PERSON_ID
717 ,MGR_OVRID_DT
718 ,pil_attribute_category
720 ,pil_attribute2
721 ,pil_attribute3
722 ,pil_attribute4
723 ,pil_attribute5
724 ,pil_attribute6
725 ,pil_attribute7
726 ,pil_attribute8
727 ,pil_attribute9
728 ,pil_attribute10
729 ,pil_attribute11
730 ,pil_attribute12
731 ,pil_attribute13
732 ,pil_attribute14
733 ,pil_attribute15
734 ,pil_attribute16
735 ,pil_attribute17
736 ,pil_attribute18
737 ,pil_attribute19
738 ,pil_attribute20
739 ,pil_attribute21
740 ,pil_attribute22
741 ,pil_attribute23
742 ,pil_attribute24
743 ,pil_attribute25
744 ,pil_attribute26
745 ,pil_attribute27
746 ,pil_attribute28
747 ,pil_attribute29
748 ,pil_attribute30
749 ,request_id
750 ,program_application_id
751 ,program_id
752 ,program_update_date
753 ,object_version_number)
754 values
755 (l_per_in_ler_id
756 ,p_per_in_ler_stat_cd
757 ,p_prvs_stat_cd
758 ,trunc(p_lf_evt_ocrd_dt)
759 ,p_trgr_table_pk_id --ABSE changes
760 ,l_procd_dt
761 ,l_strtd_dt
762 ,l_voidd_dt
763 ,l_bckt_dt
764 ,l_clsd_dt
765 ,trunc(p_ntfn_dt)
766 ,p_ptnl_ler_for_per_id
767 ,p_bckt_per_in_ler_id
768 ,p_ler_id
769 ,p_person_id
770 ,p_business_group_id
771 ,p_ASSIGNMENT_ID
772 ,p_WS_MGR_ID
773 ,p_GROUP_PL_ID
774 ,p_MGR_OVRID_PERSON_ID
775 ,p_MGR_OVRID_DT
776 ,p_pil_attribute_category
777 ,p_pil_attribute1
778 ,p_pil_attribute2
779 ,p_pil_attribute3
780 ,p_pil_attribute4
781 ,p_pil_attribute5
782 ,p_pil_attribute6
783 ,p_pil_attribute7
784 ,p_pil_attribute8
785 ,p_pil_attribute9
786 ,p_pil_attribute10
787 ,p_pil_attribute11
788 ,p_pil_attribute12
789 ,p_pil_attribute13
790 ,p_pil_attribute14
791 ,p_pil_attribute15
792 ,p_pil_attribute16
793 ,p_pil_attribute17
794 ,p_pil_attribute18
795 ,p_pil_attribute19
796 ,p_pil_attribute20
797 ,p_pil_attribute21
798 ,p_pil_attribute22
799 ,p_pil_attribute23
800 ,p_pil_attribute24
801 ,p_pil_attribute25
802 ,p_pil_attribute26
803 ,p_pil_attribute27
804 ,p_pil_attribute28
805 ,p_pil_attribute29
806 ,p_pil_attribute30
807 ,p_request_id
808 ,p_program_application_id
809 ,p_program_id
810 ,trunc(p_program_update_date)
811 ,l_object_version_number);
812 --
813 -- Start of API User Hook for post_insert.
814 --
815 begin
816 --
817 ben_pil_rki.after_insert
818 (p_per_in_ler_id =>l_per_in_ler_id
819 ,p_per_in_ler_stat_cd =>p_per_in_ler_stat_cd
820 ,p_prvs_stat_cd =>p_prvs_stat_cd
821 ,p_lf_evt_ocrd_dt =>trunc(p_lf_evt_ocrd_dt)
822 ,p_trgr_table_pk_id =>p_trgr_table_pk_id --ABSE changes
823 ,p_procd_dt =>l_procd_dt
824 ,p_strtd_dt =>l_strtd_dt
825 ,p_voidd_dt =>l_voidd_dt
826 ,p_bckt_dt =>l_bckt_dt
827 ,p_clsd_dt =>l_clsd_dt
828 ,p_ntfn_dt =>trunc(p_ntfn_dt)
829 ,p_ptnl_ler_for_per_id =>p_ptnl_ler_for_per_id
830 ,p_bckt_per_in_ler_id =>p_bckt_per_in_ler_id
831 ,p_ler_id =>p_ler_id
832 ,p_person_id =>p_person_id
833 ,p_business_group_id =>p_business_group_id
834 ,p_ASSIGNMENT_ID => p_ASSIGNMENT_ID
835 ,p_WS_MGR_ID => p_WS_MGR_ID
836 ,p_GROUP_PL_ID => p_GROUP_PL_ID
837 ,p_MGR_OVRID_PERSON_ID => p_MGR_OVRID_PERSON_ID
838 ,p_MGR_OVRID_DT => p_MGR_OVRID_DT
839 ,p_pil_attribute_category =>p_pil_attribute_category
840 ,p_pil_attribute1 =>p_pil_attribute1
841 ,p_pil_attribute2 =>p_pil_attribute2
842 ,p_pil_attribute3 =>p_pil_attribute3
843 ,p_pil_attribute4 =>p_pil_attribute4
844 ,p_pil_attribute5 =>p_pil_attribute5
845 ,p_pil_attribute6 =>p_pil_attribute6
846 ,p_pil_attribute7 =>p_pil_attribute7
847 ,p_pil_attribute8 =>p_pil_attribute8
848 ,p_pil_attribute9 =>p_pil_attribute9
849 ,p_pil_attribute10 =>p_pil_attribute10
850 ,p_pil_attribute11 =>p_pil_attribute11
851 ,p_pil_attribute12 =>p_pil_attribute12
852 ,p_pil_attribute13 =>p_pil_attribute13
853 ,p_pil_attribute14 =>p_pil_attribute14
854 ,p_pil_attribute15 =>p_pil_attribute15
855 ,p_pil_attribute16 =>p_pil_attribute16
856 ,p_pil_attribute17 =>p_pil_attribute17
857 ,p_pil_attribute18 =>p_pil_attribute18
858 ,p_pil_attribute19 =>p_pil_attribute19
859 ,p_pil_attribute20 =>p_pil_attribute20
860 ,p_pil_attribute21 =>p_pil_attribute21
861 ,p_pil_attribute22 =>p_pil_attribute22
862 ,p_pil_attribute23 =>p_pil_attribute23
866 ,p_pil_attribute27 =>p_pil_attribute27
863 ,p_pil_attribute24 =>p_pil_attribute24
864 ,p_pil_attribute25 =>p_pil_attribute25
865 ,p_pil_attribute26 =>p_pil_attribute26
867 ,p_pil_attribute28 =>p_pil_attribute28
868 ,p_pil_attribute29 =>p_pil_attribute29
869 ,p_pil_attribute30 =>p_pil_attribute30
870 ,p_request_id =>p_request_id
871 ,p_program_application_id =>p_program_application_id
872 ,p_program_id =>p_program_id
873 ,p_program_update_date =>trunc(p_program_update_date)
874 ,p_object_version_number =>l_object_version_number
875 ,p_effective_date =>trunc(p_effective_date));
876 --
877 exception
878 --
879 when hr_api.cannot_find_prog_unit then
880 --
881 hr_api.cannot_find_prog_unit_error
882 (p_module_name => 'ben_per_in_ler'
883 ,p_hook_type => 'AI');
884 --
885 end;
886 --
887 -- End of API User Hook for post_insert.
888 --
889 begin
890 --
891 -- Start of API User Hook for the after hook of create_Person_Life_Event
892 --
893 ben_Person_Life_Event_bk1.create_Person_Life_Event_a
894 (p_per_in_ler_id => l_per_in_ler_id
895 ,p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
896 ,p_prvs_stat_cd => p_prvs_stat_cd
897 ,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
898 ,p_trgr_table_pk_id => p_trgr_table_pk_id --ABSE changes
899 ,p_procd_dt => l_procd_dt
900 ,p_strtd_dt => l_strtd_dt
901 ,p_voidd_dt => l_voidd_dt
902 ,p_bckt_dt => l_bckt_dt
903 ,p_clsd_dt => l_clsd_dt
904 ,p_ntfn_dt => trunc(p_ntfn_dt)
905 ,p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
906 ,p_bckt_per_in_ler_id => p_bckt_per_in_ler_id
907 ,p_ler_id => p_ler_id
908 ,p_person_id => p_person_id
909 ,p_business_group_id => p_business_group_id
910 ,p_ASSIGNMENT_ID => p_ASSIGNMENT_ID
911 ,p_WS_MGR_ID => p_WS_MGR_ID
912 ,p_GROUP_PL_ID => p_GROUP_PL_ID
913 ,p_MGR_OVRID_PERSON_ID => p_MGR_OVRID_PERSON_ID
914 ,p_MGR_OVRID_DT => p_MGR_OVRID_DT
915 ,p_pil_attribute_category => p_pil_attribute_category
916 ,p_pil_attribute1 => p_pil_attribute1
917 ,p_pil_attribute2 => p_pil_attribute2
918 ,p_pil_attribute3 => p_pil_attribute3
919 ,p_pil_attribute4 => p_pil_attribute4
920 ,p_pil_attribute5 => p_pil_attribute5
921 ,p_pil_attribute6 => p_pil_attribute6
922 ,p_pil_attribute7 => p_pil_attribute7
923 ,p_pil_attribute8 => p_pil_attribute8
924 ,p_pil_attribute9 => p_pil_attribute9
925 ,p_pil_attribute10 => p_pil_attribute10
926 ,p_pil_attribute11 => p_pil_attribute11
927 ,p_pil_attribute12 => p_pil_attribute12
928 ,p_pil_attribute13 => p_pil_attribute13
929 ,p_pil_attribute14 => p_pil_attribute14
930 ,p_pil_attribute15 => p_pil_attribute15
931 ,p_pil_attribute16 => p_pil_attribute16
932 ,p_pil_attribute17 => p_pil_attribute17
933 ,p_pil_attribute18 => p_pil_attribute18
934 ,p_pil_attribute19 => p_pil_attribute19
935 ,p_pil_attribute20 => p_pil_attribute20
936 ,p_pil_attribute21 => p_pil_attribute21
937 ,p_pil_attribute22 => p_pil_attribute22
938 ,p_pil_attribute23 => p_pil_attribute23
939 ,p_pil_attribute24 => p_pil_attribute24
940 ,p_pil_attribute25 => p_pil_attribute25
941 ,p_pil_attribute26 => p_pil_attribute26
942 ,p_pil_attribute27 => p_pil_attribute27
943 ,p_pil_attribute28 => p_pil_attribute28
944 ,p_pil_attribute29 => p_pil_attribute29
945 ,p_pil_attribute30 => p_pil_attribute30
946 ,p_request_id => p_request_id
947 ,p_program_application_id => p_program_application_id
948 ,p_program_id => p_program_id
949 ,p_program_update_date => trunc(p_program_update_date)
950 ,p_object_version_number => l_object_version_number
951 ,p_effective_date => trunc(p_effective_date));
952 --
953 exception
954 --
955 when hr_api.cannot_find_prog_unit then
956 --
957 hr_api.cannot_find_prog_unit_error
958 (p_module_name => 'CREATE_Person_Life_Event'
959 ,p_hook_type => 'AP');
960 --
961 -- End of API User Hook for the after hook of create_Person_Life_Event
962 --
963 end;
964 --
965 hr_utility.set_location(l_proc, 60);
966 --
967 -- When in validation only mode raise the Validate_Enabled exception
968 --
969 if p_validate then
970 raise hr_api.validate_enabled;
971 end if;
972 --
973 -- Set all output arguments
974 --
975 p_per_in_ler_id := l_per_in_ler_id;
976 p_object_version_number := l_object_version_number;
977 p_procd_dt := l_procd_dt;
978 p_strtd_dt := l_strtd_dt;
982 --
979 p_voidd_dt := l_voidd_dt;
980 --
981 hr_utility.set_location(' Leaving:'||l_proc, 70);
983 exception
984 --
985 when hr_api.validate_enabled then
986 --
987 -- As the Validate_Enabled exception has been raised
988 -- we must rollback to the savepoint
989 --
990 ROLLBACK TO create_Person_Life_Event;
991 --
992 -- Only set output warning arguments
993 -- (Any key or derived arguments must be set to null
994 -- when validation only mode is being used.)
995 --
996 p_per_in_ler_id := null;
997 p_object_version_number := null;
998 p_procd_dt := null;
999 p_strtd_dt := null;
1000 p_voidd_dt := null;
1001 hr_utility.set_location(' Leaving:'||l_proc, 80);
1002 --
1003 when others then
1004 --
1005 -- A validation or unexpected error has occured
1006 --
1007 ROLLBACK TO create_Person_Life_Event;
1008 raise;
1009 --
1010 end create_Person_Life_Event_perf;
1011 --
1012 -- ----------------------------------------------------------------------------
1013 -- |------------------------< update_Person_Life_Event >----------------------|
1014 -- ----------------------------------------------------------------------------
1015 --
1016 procedure update_Person_Life_Event
1017 (p_validate in boolean default false
1018 ,p_per_in_ler_id in number
1019 ,p_per_in_ler_stat_cd in varchar2 default hr_api.g_varchar2
1020 ,p_prvs_stat_cd in varchar2 default hr_api.g_varchar2
1021 ,p_lf_evt_ocrd_dt in date default hr_api.g_date
1022 ,p_trgr_table_pk_id in number default hr_api.g_number --ABSE changes
1023 ,p_procd_dt out NOCOPY date
1024 ,p_strtd_dt out NOCOPY date
1025 ,p_voidd_dt out NOCOPY date
1026 ,p_bckt_dt in date default hr_api.g_date
1027 ,p_clsd_dt in date default hr_api.g_date
1028 ,p_ntfn_dt in date default hr_api.g_date
1029 ,p_ptnl_ler_for_per_id in number default hr_api.g_number
1030 ,p_bckt_per_in_ler_id in number default hr_api.g_number
1031 ,p_ler_id in number default hr_api.g_number
1032 ,p_person_id in number default hr_api.g_number
1033 ,p_business_group_id in number default hr_api.g_number
1034 ,p_ASSIGNMENT_ID in number default hr_api.g_number
1035 ,p_WS_MGR_ID in number default hr_api.g_number
1036 ,p_GROUP_PL_ID in number default hr_api.g_number
1037 ,p_MGR_OVRID_PERSON_ID in number default hr_api.g_number
1038 ,p_MGR_OVRID_DT in date default hr_api.g_date
1039 ,p_pil_attribute_category in varchar2 default hr_api.g_varchar2
1040 ,p_pil_attribute1 in varchar2 default hr_api.g_varchar2
1041 ,p_pil_attribute2 in varchar2 default hr_api.g_varchar2
1042 ,p_pil_attribute3 in varchar2 default hr_api.g_varchar2
1043 ,p_pil_attribute4 in varchar2 default hr_api.g_varchar2
1044 ,p_pil_attribute5 in varchar2 default hr_api.g_varchar2
1045 ,p_pil_attribute6 in varchar2 default hr_api.g_varchar2
1046 ,p_pil_attribute7 in varchar2 default hr_api.g_varchar2
1047 ,p_pil_attribute8 in varchar2 default hr_api.g_varchar2
1048 ,p_pil_attribute9 in varchar2 default hr_api.g_varchar2
1049 ,p_pil_attribute10 in varchar2 default hr_api.g_varchar2
1050 ,p_pil_attribute11 in varchar2 default hr_api.g_varchar2
1051 ,p_pil_attribute12 in varchar2 default hr_api.g_varchar2
1052 ,p_pil_attribute13 in varchar2 default hr_api.g_varchar2
1053 ,p_pil_attribute14 in varchar2 default hr_api.g_varchar2
1054 ,p_pil_attribute15 in varchar2 default hr_api.g_varchar2
1055 ,p_pil_attribute16 in varchar2 default hr_api.g_varchar2
1056 ,p_pil_attribute17 in varchar2 default hr_api.g_varchar2
1057 ,p_pil_attribute18 in varchar2 default hr_api.g_varchar2
1058 ,p_pil_attribute19 in varchar2 default hr_api.g_varchar2
1059 ,p_pil_attribute20 in varchar2 default hr_api.g_varchar2
1060 ,p_pil_attribute21 in varchar2 default hr_api.g_varchar2
1061 ,p_pil_attribute22 in varchar2 default hr_api.g_varchar2
1062 ,p_pil_attribute23 in varchar2 default hr_api.g_varchar2
1063 ,p_pil_attribute24 in varchar2 default hr_api.g_varchar2
1064 ,p_pil_attribute25 in varchar2 default hr_api.g_varchar2
1065 ,p_pil_attribute26 in varchar2 default hr_api.g_varchar2
1066 ,p_pil_attribute27 in varchar2 default hr_api.g_varchar2
1067 ,p_pil_attribute28 in varchar2 default hr_api.g_varchar2
1068 ,p_pil_attribute29 in varchar2 default hr_api.g_varchar2
1069 ,p_pil_attribute30 in varchar2 default hr_api.g_varchar2
1070 ,p_request_id in number default hr_api.g_number
1071 ,p_program_application_id in number default hr_api.g_number
1072 ,p_program_id in number default hr_api.g_number
1073 ,p_program_update_date in date default hr_api.g_date
1074 ,p_object_version_number in out NOCOPY number
1075 ,p_effective_date in date) is
1076 --
1077 -- Declare cursors and local variables
1078 --
1079 l_proc varchar2(72) := g_package||'update_Person_Life_Event';
1080 l_object_version_number ben_per_in_ler.object_version_number%TYPE;
1081 --
1085 l_bckt_dt date := hr_api.g_date;
1082 l_procd_dt date := hr_api.g_date;
1083 l_strtd_dt date := hr_api.g_date;
1084 l_voidd_dt date := hr_api.g_date;
1086 l_clsd_dt date := hr_api.g_date;
1087 l_ntfn_dt date := hr_api.g_date;
1088 --
1089 begin
1090 --
1091 hr_utility.set_location('Entering:'|| l_proc, 10);
1092 --
1093 -- Issue a savepoint if operating in validation only mode
1094 --
1095 savepoint update_Person_Life_Event;
1096 --
1097 hr_utility.set_location(l_proc, 20);
1098 --
1099 -- Process Logic
1100 --
1101 l_object_version_number := p_object_version_number;
1102 --
1103 begin
1104 --
1105 -- Start of API User Hook for the before hook of update_Person_Life_Event
1106 --
1107 ben_Person_Life_Event_bk2.update_Person_Life_Event_b
1108 (p_per_in_ler_id => p_per_in_ler_id
1109 ,p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
1110 ,p_prvs_stat_cd => p_prvs_stat_cd
1111 ,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
1112 ,p_trgr_table_pk_id => p_trgr_table_pk_id --ABSE changes
1113 ,p_procd_dt => trunc(p_procd_dt)
1114 ,p_strtd_dt => trunc(p_strtd_dt)
1115 ,p_voidd_dt => trunc(p_voidd_dt)
1116 ,p_bckt_dt => trunc(p_bckt_dt)
1117 ,p_clsd_dt => trunc(p_clsd_dt)
1118 ,p_ntfn_dt => trunc(p_ntfn_dt)
1119 ,p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
1120 ,p_bckt_per_in_ler_id => p_bckt_per_in_ler_id
1121 ,p_ler_id => p_ler_id
1122 ,p_person_id => p_person_id
1123 ,p_business_group_id => p_business_group_id
1124 ,p_ASSIGNMENT_ID => p_ASSIGNMENT_ID
1125 ,p_WS_MGR_ID => p_WS_MGR_ID
1126 ,p_GROUP_PL_ID => p_GROUP_PL_ID
1127 ,p_MGR_OVRID_PERSON_ID => p_MGR_OVRID_PERSON_ID
1128 ,p_MGR_OVRID_DT => p_MGR_OVRID_DT
1129 ,p_pil_attribute_category => p_pil_attribute_category
1130 ,p_pil_attribute1 => p_pil_attribute1
1131 ,p_pil_attribute2 => p_pil_attribute2
1132 ,p_pil_attribute3 => p_pil_attribute3
1133 ,p_pil_attribute4 => p_pil_attribute4
1134 ,p_pil_attribute5 => p_pil_attribute5
1135 ,p_pil_attribute6 => p_pil_attribute6
1136 ,p_pil_attribute7 => p_pil_attribute7
1137 ,p_pil_attribute8 => p_pil_attribute8
1138 ,p_pil_attribute9 => p_pil_attribute9
1139 ,p_pil_attribute10 => p_pil_attribute10
1140 ,p_pil_attribute11 => p_pil_attribute11
1141 ,p_pil_attribute12 => p_pil_attribute12
1142 ,p_pil_attribute13 => p_pil_attribute13
1143 ,p_pil_attribute14 => p_pil_attribute14
1144 ,p_pil_attribute15 => p_pil_attribute15
1145 ,p_pil_attribute16 => p_pil_attribute16
1146 ,p_pil_attribute17 => p_pil_attribute17
1147 ,p_pil_attribute18 => p_pil_attribute18
1148 ,p_pil_attribute19 => p_pil_attribute19
1149 ,p_pil_attribute20 => p_pil_attribute20
1150 ,p_pil_attribute21 => p_pil_attribute21
1151 ,p_pil_attribute22 => p_pil_attribute22
1152 ,p_pil_attribute23 => p_pil_attribute23
1153 ,p_pil_attribute24 => p_pil_attribute24
1154 ,p_pil_attribute25 => p_pil_attribute25
1155 ,p_pil_attribute26 => p_pil_attribute26
1156 ,p_pil_attribute27 => p_pil_attribute27
1157 ,p_pil_attribute28 => p_pil_attribute28
1158 ,p_pil_attribute29 => p_pil_attribute29
1159 ,p_pil_attribute30 => p_pil_attribute30
1160 ,p_request_id => p_request_id
1161 ,p_program_application_id => p_program_application_id
1162 ,p_program_id => p_program_id
1163 ,p_program_update_date => trunc(p_program_update_date)
1164 ,p_object_version_number => p_object_version_number
1165 ,p_effective_date => trunc(p_effective_date));
1166 --
1167 exception
1168 --
1169 when hr_api.cannot_find_prog_unit then
1170 --
1171 hr_api.cannot_find_prog_unit_error
1172 (p_module_name => 'UPDATE_Person_Life_Event'
1173 ,p_hook_type => 'BP');
1174 --
1175 -- End of API User Hook for the before hook of update_Person_Life_Event
1176 --
1177 end;
1178 --
1179 -- Derive date column values for the per in ler status code
1180 --
1181 derive_PIL_statcd_dates
1182 (p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
1183 ,p_effective_date => p_effective_date
1184 ,p_procd_dt => l_procd_dt
1185 ,p_strtd_dt => l_strtd_dt
1186 ,p_voidd_dt => l_voidd_dt
1187 ,p_bckt_dt => l_bckt_dt
1188 ,p_clsd_dt => l_clsd_dt);
1189 --
1190 hr_utility.set_location(l_proc, 20);
1191 hr_utility.set_location(l_proc||' l_procd_dt: '||l_procd_dt, 20);
1192 --
1193 ben_pil_upd.upd
1194 (p_per_in_ler_id => p_per_in_ler_id
1195 ,p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
1196 ,p_prvs_stat_cd => p_prvs_stat_cd
1200 ,p_strtd_dt => l_strtd_dt
1197 ,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
1198 ,p_trgr_table_pk_id => p_trgr_table_pk_id --ABSE changes
1199 ,p_procd_dt => l_procd_dt
1201 ,p_voidd_dt => l_voidd_dt
1202 ,p_bckt_dt => l_bckt_dt
1203 ,p_clsd_dt => l_clsd_dt
1204 ,p_ntfn_dt => trunc(p_ntfn_dt)
1205 ,p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
1206 ,p_bckt_per_in_ler_id => p_bckt_per_in_ler_id
1207 ,p_ler_id => p_ler_id
1208 ,p_person_id => p_person_id
1209 ,p_business_group_id => p_business_group_id
1210 ,p_ASSIGNMENT_ID => p_ASSIGNMENT_ID
1211 ,p_WS_MGR_ID => p_WS_MGR_ID
1212 ,p_GROUP_PL_ID => p_GROUP_PL_ID
1213 ,p_MGR_OVRID_PERSON_ID => p_MGR_OVRID_PERSON_ID
1214 ,p_MGR_OVRID_DT => p_MGR_OVRID_DT
1215 ,p_pil_attribute_category => p_pil_attribute_category
1216 ,p_pil_attribute1 => p_pil_attribute1
1217 ,p_pil_attribute2 => p_pil_attribute2
1218 ,p_pil_attribute3 => p_pil_attribute3
1219 ,p_pil_attribute4 => p_pil_attribute4
1220 ,p_pil_attribute5 => p_pil_attribute5
1221 ,p_pil_attribute6 => p_pil_attribute6
1222 ,p_pil_attribute7 => p_pil_attribute7
1223 ,p_pil_attribute8 => p_pil_attribute8
1224 ,p_pil_attribute9 => p_pil_attribute9
1225 ,p_pil_attribute10 => p_pil_attribute10
1226 ,p_pil_attribute11 => p_pil_attribute11
1227 ,p_pil_attribute12 => p_pil_attribute12
1228 ,p_pil_attribute13 => p_pil_attribute13
1229 ,p_pil_attribute14 => p_pil_attribute14
1230 ,p_pil_attribute15 => p_pil_attribute15
1231 ,p_pil_attribute16 => p_pil_attribute16
1232 ,p_pil_attribute17 => p_pil_attribute17
1233 ,p_pil_attribute18 => p_pil_attribute18
1234 ,p_pil_attribute19 => p_pil_attribute19
1235 ,p_pil_attribute20 => p_pil_attribute20
1236 ,p_pil_attribute21 => p_pil_attribute21
1237 ,p_pil_attribute22 => p_pil_attribute22
1238 ,p_pil_attribute23 => p_pil_attribute23
1239 ,p_pil_attribute24 => p_pil_attribute24
1240 ,p_pil_attribute25 => p_pil_attribute25
1241 ,p_pil_attribute26 => p_pil_attribute26
1242 ,p_pil_attribute27 => p_pil_attribute27
1243 ,p_pil_attribute28 => p_pil_attribute28
1244 ,p_pil_attribute29 => p_pil_attribute29
1245 ,p_pil_attribute30 => p_pil_attribute30
1246 ,p_request_id => p_request_id
1247 ,p_program_application_id => p_program_application_id
1248 ,p_program_id => p_program_id
1249 ,p_program_update_date => trunc(p_program_update_date)
1250 ,p_object_version_number => l_object_version_number
1251 ,p_effective_date => trunc(p_effective_date));
1252 --
1253 begin
1254 --
1255 -- Start of API User Hook for the after hook of update_Person_Life_Event
1259 ,p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
1256 --
1257 ben_Person_Life_Event_bk2.update_Person_Life_Event_a
1258 (p_per_in_ler_id => p_per_in_ler_id
1260 ,p_prvs_stat_cd => p_prvs_stat_cd
1261 ,p_lf_evt_ocrd_dt => trunc(p_lf_evt_ocrd_dt)
1262 ,p_trgr_table_pk_id => p_trgr_table_pk_id --ABSE changes
1263 ,p_procd_dt => l_procd_dt
1264 ,p_strtd_dt => l_strtd_dt
1265 ,p_voidd_dt => l_voidd_dt
1266 ,p_bckt_dt => l_bckt_dt
1267 ,p_clsd_dt => l_clsd_dt
1268 ,p_ntfn_dt => trunc(p_ntfn_dt)
1269 ,p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
1270 ,p_bckt_per_in_ler_id => p_bckt_per_in_ler_id
1271 ,p_ler_id => p_ler_id
1272 ,p_person_id => p_person_id
1273 ,p_business_group_id => p_business_group_id
1274 ,p_ASSIGNMENT_ID => p_ASSIGNMENT_ID
1275 ,p_WS_MGR_ID => p_WS_MGR_ID
1279 ,p_pil_attribute_category => p_pil_attribute_category
1276 ,p_GROUP_PL_ID => p_GROUP_PL_ID
1277 ,p_MGR_OVRID_PERSON_ID => p_MGR_OVRID_PERSON_ID
1278 ,p_MGR_OVRID_DT => p_MGR_OVRID_DT
1280 ,p_pil_attribute1 => p_pil_attribute1
1281 ,p_pil_attribute2 => p_pil_attribute2
1282 ,p_pil_attribute3 => p_pil_attribute3
1283 ,p_pil_attribute4 => p_pil_attribute4
1284 ,p_pil_attribute5 => p_pil_attribute5
1285 ,p_pil_attribute6 => p_pil_attribute6
1286 ,p_pil_attribute7 => p_pil_attribute7
1287 ,p_pil_attribute8 => p_pil_attribute8
1288 ,p_pil_attribute9 => p_pil_attribute9
1289 ,p_pil_attribute10 => p_pil_attribute10
1290 ,p_pil_attribute11 => p_pil_attribute11
1291 ,p_pil_attribute12 => p_pil_attribute12
1292 ,p_pil_attribute13 => p_pil_attribute13
1293 ,p_pil_attribute14 => p_pil_attribute14
1294 ,p_pil_attribute15 => p_pil_attribute15
1295 ,p_pil_attribute16 => p_pil_attribute16
1296 ,p_pil_attribute17 => p_pil_attribute17
1297 ,p_pil_attribute18 => p_pil_attribute18
1298 ,p_pil_attribute19 => p_pil_attribute19
1299 ,p_pil_attribute20 => p_pil_attribute20
1300 ,p_pil_attribute21 => p_pil_attribute21
1301 ,p_pil_attribute22 => p_pil_attribute22
1302 ,p_pil_attribute23 => p_pil_attribute23
1303 ,p_pil_attribute24 => p_pil_attribute24
1304 ,p_pil_attribute25 => p_pil_attribute25
1305 ,p_pil_attribute26 => p_pil_attribute26
1306 ,p_pil_attribute27 => p_pil_attribute27
1307 ,p_pil_attribute28 => p_pil_attribute28
1308 ,p_pil_attribute29 => p_pil_attribute29
1309 ,p_pil_attribute30 => p_pil_attribute30
1310 ,p_request_id => p_request_id
1311 ,p_program_application_id => p_program_application_id
1312 ,p_program_id => p_program_id
1313 ,p_program_update_date => trunc(p_program_update_date)
1314 ,p_object_version_number => l_object_version_number
1315 ,p_effective_date => trunc(p_effective_date));
1316 --
1317 exception
1318 --
1319 when hr_api.cannot_find_prog_unit then
1320 --
1321 hr_api.cannot_find_prog_unit_error
1322 (p_module_name => 'UPDATE_Person_Life_Event'
1323 ,p_hook_type => 'AP');
1324 --
1325 -- End of API User Hook for the after hook of update_Person_Life_Event
1326 --
1327 end;
1328 --
1329 hr_utility.set_location(l_proc, 60);
1330 --
1331 -- When in validation only mode raise the Validate_Enabled exception
1332 --
1333 if p_validate then
1334 raise hr_api.validate_enabled;
1335 end if;
1336 --
1337 -- Set all output arguments
1338 --
1339 p_object_version_number := l_object_version_number;
1340 p_procd_dt := l_procd_dt;
1341 p_strtd_dt := l_strtd_dt;
1342 p_voidd_dt := l_voidd_dt;
1343 --
1344 hr_utility.set_location(' Leaving:'||l_proc, 70);
1345 --
1346 exception
1347 --
1348 when hr_api.validate_enabled then
1349 --
1350 -- As the Validate_Enabled exception has been raised
1351 -- we must rollback to the savepoint
1352 --
1353 ROLLBACK TO update_Person_Life_Event;
1354 --
1355 -- Only set output warning arguments
1356 -- (Any key or derived arguments must be set to null
1357 -- when validation only mode is being used.)
1358 --
1359 hr_utility.set_location(' Leaving:'||l_proc, 80);
1360 --
1361 when others then
1362 --
1363 -- A validation or unexpected error has occured
1364 --
1365 ROLLBACK TO update_Person_Life_Event;
1366 /* Inserted for nocopy changes */
1367 p_object_version_number := l_object_version_number;
1368 p_procd_dt := null;
1369 p_strtd_dt := null;
1370 p_voidd_dt := null;
1371 raise;
1372 --
1373 end update_Person_Life_Event;
1374 --
1375 -- ----------------------------------------------------------------------------
1376 -- |------------------------< delete_Person_Life_Event >----------------------|
1377 -- ----------------------------------------------------------------------------
1378 --
1379 procedure delete_Person_Life_Event
1380 (p_validate in boolean default false
1381 ,p_per_in_ler_id in number
1382 ,p_object_version_number in out NOCOPY number
1383 ,p_effective_date in date) is
1384 --
1385 -- Declare cursors and local variables
1386 --
1387 l_proc varchar2(72) := g_package||'update_Person_Life_Event';
1388 l_object_version_number ben_per_in_ler.object_version_number%TYPE;
1389 --
1390 begin
1391 --
1392 hr_utility.set_location('Entering:'|| l_proc, 10);
1393 --
1394 -- Issue a savepoint if operating in validation only mode
1395 --
1396 savepoint delete_Person_Life_Event;
1397 --
1398 hr_utility.set_location(l_proc, 20);
1399 --
1400 -- Process Logic
1401 --
1402 l_object_version_number := p_object_version_number;
1403 --
1404 --
1405 begin
1406 --
1410 (p_per_in_ler_id => p_per_in_ler_id
1407 -- Start of API User Hook for the before hook of delete_Person_Life_Event
1408 --
1409 ben_Person_Life_Event_bk3.delete_Person_Life_Event_b
1411 ,p_object_version_number => p_object_version_number
1412 ,p_effective_date => trunc(p_effective_date));
1413 --
1414 exception
1415 --
1416 when hr_api.cannot_find_prog_unit then
1417 --
1418 hr_api.cannot_find_prog_unit_error
1419 (p_module_name => 'DELETE_Person_Life_Event'
1420 ,p_hook_type => 'BP');
1421 --
1422 -- End of API User Hook for the before hook of delete_Person_Life_Event
1423 --
1424 end;
1425 --
1426 ben_pil_del.del
1427 (p_per_in_ler_id => p_per_in_ler_id
1428 ,p_object_version_number => l_object_version_number
1429 ,p_effective_date => p_effective_date);
1430 --
1431 begin
1432 --
1433 -- Start of API User Hook for the after hook of delete_Person_Life_Event
1434 --
1435 ben_Person_Life_Event_bk3.delete_Person_Life_Event_a
1436 (p_per_in_ler_id => p_per_in_ler_id
1437 ,p_object_version_number => l_object_version_number
1438 ,p_effective_date => trunc(p_effective_date));
1439 --
1440 exception
1441 --
1442 when hr_api.cannot_find_prog_unit then
1443 --
1444 hr_api.cannot_find_prog_unit_error
1445 (p_module_name => 'DELETE_Person_Life_Event'
1446 ,p_hook_type => 'AP');
1447 --
1448 -- End of API User Hook for the after hook of delete_Person_Life_Event
1449 --
1450 end;
1451 --
1452 hr_utility.set_location(l_proc, 60);
1453 --
1454 -- When in validation only mode raise the Validate_Enabled exception
1455 --
1456 if p_validate then
1457 raise hr_api.validate_enabled;
1458 end if;
1459 --
1460 hr_utility.set_location(' Leaving:'||l_proc, 70);
1461 --
1462 exception
1463 --
1464 when hr_api.validate_enabled then
1465 --
1466 -- As the Validate_Enabled exception has been raised
1467 -- we must rollback to the savepoint
1468 --
1469 ROLLBACK TO delete_Person_Life_Event;
1470 --
1471 -- Only set output warning arguments
1472 -- (Any key or derived arguments must be set to null
1473 -- when validation only mode is being used.)
1474 --
1475 --
1476 when others then
1477 --
1478 -- A validation or unexpected error has occured
1479 --
1480 ROLLBACK TO delete_Person_Life_Event;
1481 /* Inserted for nocopy changes */
1482 p_object_version_number := l_object_version_number;
1483 raise;
1484 --
1485 end delete_Person_Life_Event;
1486 --
1487 -- ----------------------------------------------------------------------------
1488 -- |-------------------------------< lck >------------------------------------|
1489 -- ----------------------------------------------------------------------------
1490 --
1491 procedure lck
1492 (p_per_in_ler_id in number
1493 ,p_object_version_number in number) is
1494 --
1495 --
1496 -- Declare cursors and local variables
1497 --
1498 l_proc varchar2(72) := g_package||'lck';
1499 --
1500 begin
1501 --
1502 hr_utility.set_location('Entering:'|| l_proc, 10);
1503 --
1504 ben_pil_shd.lck
1505 (p_per_in_ler_id => p_per_in_ler_id
1506 ,p_object_version_number => p_object_version_number);
1507 --
1508 hr_utility.set_location(' Leaving:'||l_proc, 70);
1509 --
1510 end lck;
1511 --
1512 end ben_Person_Life_Event_api;