[Home] [Help]
PACKAGE BODY: APPS.PER_PERIODS_OF_SERVICE_PKG
Source
1 PACKAGE BODY PER_PERIODS_OF_SERVICE_PKG AS
2 /* $Header: pepds01t.pkb 120.2 2011/09/16 10:04:54 srannama ship $ */
3 ----------------------------------------------------------------------------
4
5 ----------------------------------------------------------------------------
6 procedure delete_per_pay_proposals(p_period_of_service_id number
7 ,p_actual_termination_date date) is
8 --
9 cursor ass1 is select pa.assignment_id
10 from per_assignments pa
11 where pa.period_of_service_id = p_period_of_service_id;
12 --
13 -- VT #464380 05/14/97
14 cursor ppr1 (p_assignment_id_in number) is
15 select pp.pay_proposal_id
16 from per_pay_proposals pp
17 where pp.assignment_id = p_assignment_id_in
18 and pp.change_date > p_actual_termination_date;
19 --
20 p_pay_proposal_id number;
21 --
22 p_assignment_id number;
23 --
24 begin
25 open ass1;
26 fetch ass1 into p_assignment_id;
27 loop
28 exit when ass1%notfound;
29 -- VT #464380 05/14/97
30 open ppr1(p_assignment_id);
31 fetch ppr1 into p_pay_proposal_id;
32 loop
33 exit when ppr1%notfound;
34 delete from per_pay_proposal_components ppc
35 where ppc.pay_proposal_id = p_pay_proposal_id;
36 fetch ppr1 into p_pay_proposal_id;
37 end loop;
38 close ppr1;
39 --
40 delete from per_pay_proposals
41 where assignment_id = p_assignment_id
42 and change_date > p_actual_termination_date;
43 --
44 fetch ass1 into p_assignment_id;
45 end loop;
46 close ass1;
47 end delete_per_pay_proposals;
48 ----------------------------------------------------------------------------
49 procedure get_years_months(p_session_date IN DATE,
50 p_period_of_service_id IN NUMBER,
51 p_business_group_id IN NUMBER,
52 p_person_id IN NUMBER,
53 p_tp_years IN OUT NOCOPY NUMBER,
54 p_tp_months IN OUT NOCOPY NUMBER,
55 p_total_years IN OUT NOCOPY NUMBER,
56 p_total_months IN OUT NOCOPY NUMBER) is
57 --
58 cursor C_TP1 is
59 select trunc(months_between(least(nvl(ACTUAL_TERMINATION_DATE + 1, p_session_date + 1),
60 p_session_date+ 1), DATE_START) / 12, 0) tp_years,
61 trunc(mod(months_between(least(nvl(ACTUAL_TERMINATION_DATE + 1, p_session_date + 1),
62 p_session_date + 1), DATE_START), 12) ,0) tp_months
63 from PER_PERIODS_OF_SERVICE
64 where DATE_START <= p_session_date
65 and PERIOD_OF_SERVICE_ID = p_period_of_service_id;
66 --
67 -- Bug 12780960
68 cursor C_TY1 is
69 select trunc(sum(months_between(least(nvl(ACTUAL_TERMINATION_DATE + 1, p_session_date + 1),
70 p_session_date + 1), DATE_START)) / 12 ,0) total_years,
71 trunc(mod(sum(months_between(least(nvl(ACTUAL_TERMINATION_DATE + 1, p_session_date + 1),
72 p_session_date + 1), DATE_START)) , 12),0) total_months
73 from PER_PERIODS_OF_SERVICE
74 where PERSON_ID = p_person_id
75 and business_group_id + 0 = p_business_group_id
76 and DATE_START <= p_session_date;
77 -- and PERIOD_OF_SERVICE_ID <= p_period_of_service_id;
78 --
79 begin
80 --
81 open C_TP1;
82 fetch C_TP1 into p_tp_years,
83 p_tp_months;
84 close C_TP1;
85 --
86 open C_TY1;
87 fetch C_TY1 into p_total_years,
88 p_total_months;
89 close C_TY1;
90 --
91 end get_years_months;
92 ----------------------------------------------------------------------------
93 procedure get_final_dates(p_period_of_service_id NUMBER,
94 p_person_id NUMBER,
95 p_actual_termination_date DATE,
96 p_no_payrolls IN OUT NOCOPY NUMBER,
97 p_final_process_date IN OUT NOCOPY DATE,
98 p_last_standard_process_date IN OUT NOCOPY DATE) is
99 --
100 -- Get the number of payrolls person is assigned to
101 -- and the maximum last dates of the current time period
102 -- for the person's assignment.
103 --
104 begin
105 select count(distinct(payroll_id))
106 into p_no_payrolls
107 from per_assignments pa
108 where pa.period_of_service_id = p_period_of_service_id
109 and pa.person_id = p_person_id;
110 --
111 --
112 select max(end_date),max(end_date)
113 into p_final_process_date
114 , p_last_standard_process_date
115 from per_time_periods
116 where p_actual_termination_date between
117 start_date and end_date
118 and payroll_id in (select payroll_id
119 from per_assignments pa
120 where pa.period_of_service_id = p_period_of_service_id
121 and pa.person_id = p_person_id);
122 --
123 --
124 end get_final_dates;
125 ----------------------------------------------------------------------------
126 procedure delete_row(p_row_id VARCHAR2) is
127 --
128 begin
129 --
130 delete from per_periods_of_service
131 where rowid=chartorowid(p_row_id);
132 --
133 end;
134 --
135 procedure insert_row(p_row_id in out nocopy VARCHAR2
136 ,p_period_of_service_id in out nocopy NUMBER
137 ,p_business_group_id NUMBER
138 ,p_person_id NUMBER
139 ,p_date_start DATE
140 ,p_termination_accepted_per_id NUMBER
141 ,p_accepted_termination_date DATE
142 ,p_actual_termination_date DATE
143 ,p_comments VARCHAR2
144 ,p_final_process_date DATE
145 ,p_last_standard_process_date DATE
146 ,p_leaving_reason VARCHAR2
147 ,p_notified_termination_date DATE
148 ,p_projected_termination_date DATE
149 ,p_request_id NUMBER
150 ,p_program_application_id NUMBER
151 ,p_program_id NUMBER
152 ,p_program_update_date DATE
153 ,p_attribute_category VARCHAR2
154 ,p_attribute1 VARCHAR2
155 ,p_attribute2 VARCHAR2
156 ,p_attribute3 VARCHAR2
157 ,p_attribute4 VARCHAR2
158 ,p_attribute5 VARCHAR2
159 ,p_attribute6 VARCHAR2
160 ,p_attribute7 VARCHAR2
161 ,p_attribute8 VARCHAR2
162 ,p_attribute9 VARCHAR2
163 ,p_attribute10 VARCHAR2
164 ,p_attribute11 VARCHAR2
165 ,p_attribute12 VARCHAR2
166 ,p_attribute13 VARCHAR2
167 ,p_attribute14 VARCHAR2
168 ,p_attribute15 VARCHAR2
169 ,p_attribute16 VARCHAR2
170 ,p_attribute17 VARCHAR2
171 ,p_attribute18 VARCHAR2
172 ,p_attribute19 VARCHAR2
173 ,p_attribute20 VARCHAR2
174 ,p_adjusted_svc_date DATE
175 ) is
176 l_period_of_service_id NUMBER(15);
177 --
178 -- START WWBUG fix for 1390173
179 --
180 l_old ben_pps_ler.g_pps_ler_rec;
181 l_new ben_pps_ler.g_pps_ler_rec;
182 --
183 -- END WWBUG fix for 1390173
184 --
185 begin
186 begin
187 select per_periods_of_service_s.nextval
188 into l_period_of_service_id
189 from sys.dual;
190 end;
191 insert into per_periods_of_service (
192 period_of_service_id
193 ,business_group_id
194 ,person_id
195 ,date_start
196 ,termination_accepted_person_id
197 ,accepted_termination_date
198 ,actual_termination_date
199 ,comments
200 ,final_process_date
201 ,last_standard_process_date
202 ,leaving_reason
203 ,notified_termination_date
204 ,projected_termination_date
205 ,request_id
206 ,program_application_id
207 ,program_id
208 ,program_update_date
209 ,attribute_category
210 ,attribute1
211 ,attribute2
212 ,attribute3
213 ,attribute4
214 ,attribute5
215 ,attribute6
216 ,attribute7
217 ,attribute8
218 ,attribute9
219 ,attribute10
220 ,attribute11
221 ,attribute12
222 ,attribute13
223 ,attribute14
224 ,attribute15
225 ,attribute16
226 ,attribute17
227 ,attribute18
228 ,attribute19
229 ,attribute20
230 ,adjusted_svc_date
231 )
232 values(
233 l_period_of_service_id
234 ,p_business_group_id
235 ,p_person_id
236 ,p_date_start
237 ,p_termination_accepted_per_id
238 ,p_accepted_termination_date
239 ,p_actual_termination_date
240 ,p_comments
241 ,p_final_process_date
242 ,p_last_standard_process_date
243 ,p_leaving_reason
244 ,p_notified_termination_date
245 ,p_projected_termination_date
246 ,p_request_id
247 ,p_program_application_id
248 ,p_program_id
249 ,p_program_update_date
250 ,p_attribute_category
251 ,p_attribute1
252 ,p_attribute2
253 ,p_attribute3
254 ,p_attribute4
255 ,p_attribute5
256 ,p_attribute6
257 ,p_attribute7
258 ,p_attribute8
259 ,p_attribute9
260 ,p_attribute10
261 ,p_attribute11
262 ,p_attribute12
263 ,p_attribute13
264 ,p_attribute14
265 ,p_attribute15
266 ,p_attribute16
267 ,p_attribute17
268 ,p_attribute18
269 ,p_attribute19
270 ,p_attribute20
271 ,p_adjusted_svc_date
272 );
273 --
274 -- START WWBUG fix for 1390173
275 --
276 l_new.PERSON_ID := p_person_id;
277 l_new.BUSINESS_GROUP_ID := p_business_group_id;
278 l_new.DATE_START := p_date_start;
279 l_new.ACTUAL_TERMINATION_DATE := p_actual_termination_date;
280 l_new.LEAVING_REASON := p_leaving_reason;
281 l_new.ADJUSTED_SVC_DATE := p_adjusted_svc_date;
282 l_new.ATTRIBUTE1 := p_attribute1;
283 l_new.ATTRIBUTE2 := p_attribute2;
284 l_new.ATTRIBUTE3 := p_attribute3;
285 l_new.ATTRIBUTE4 := p_attribute4;
286 l_new.ATTRIBUTE5 := p_attribute5;
287 l_new.final_process_date := p_final_process_date;
288 --
289 ben_pps_ler.ler_chk(p_old => l_old
290 ,p_new => l_new
291 ,p_event => 'INSERTING'
292 ,p_effective_date => p_date_start);
293 --
294 -- END WWBUG fix for 1390173
295 --
296 --
297 p_period_of_service_id := l_period_of_service_id;
298 end;
299 ----------------------------------------------------------------------------
300 procedure lock_row(p_row_id VARCHAR2
301 ,p_period_of_service_id NUMBER
302 ,p_business_group_id NUMBER
303 ,p_person_id NUMBER
304 ,p_date_start DATE
305 ,p_termination_accepted_per_id NUMBER
306 ,p_accepted_termination_date DATE
307 ,p_actual_termination_date DATE
308 ,p_comments VARCHAR2
309 ,p_final_process_date DATE
310 ,p_last_standard_process_date DATE
311 ,p_leaving_reason VARCHAR2
312 ,p_notified_termination_date DATE
313 ,p_projected_termination_date DATE
314 ,p_request_id NUMBER
315 ,p_program_application_id NUMBER
316 ,p_program_id NUMBER
317 ,p_program_update_date DATE
318 ,p_attribute_category VARCHAR2
319 ,p_attribute1 VARCHAR2
320 ,p_attribute2 VARCHAR2
321 ,p_attribute3 VARCHAR2
322 ,p_attribute4 VARCHAR2
323 ,p_attribute5 VARCHAR2
324 ,p_attribute6 VARCHAR2
325 ,p_attribute7 VARCHAR2
326 ,p_attribute8 VARCHAR2
327 ,p_attribute9 VARCHAR2
328 ,p_attribute10 VARCHAR2
329 ,p_attribute11 VARCHAR2
330 ,p_attribute12 VARCHAR2
331 ,p_attribute13 VARCHAR2
332 ,p_attribute14 VARCHAR2
333 ,p_attribute15 VARCHAR2
334 ,p_attribute16 VARCHAR2
335 ,p_attribute17 VARCHAR2
336 ,p_attribute18 VARCHAR2
337 ,p_attribute19 VARCHAR2
338 ,p_attribute20 VARCHAR2
339 ,p_pds_information_category VARCHAR2
340 ,p_pds_information1 VARCHAR2
341 ,p_pds_information2 VARCHAR2
342 ,p_pds_information3 VARCHAR2
343 ,p_pds_information4 VARCHAR2
344 ,p_pds_information5 VARCHAR2
345 ,p_pds_information6 VARCHAR2
346 ,p_pds_information7 VARCHAR2
347 ,p_pds_information8 VARCHAR2
348 ,p_pds_information9 VARCHAR2
349 ,p_pds_information10 VARCHAR2
350 ,p_pds_information11 VARCHAR2
351 ,p_pds_information12 VARCHAR2
352 ,p_pds_information13 VARCHAR2
353 ,p_pds_information14 VARCHAR2
354 ,p_pds_information15 VARCHAR2
355 ,p_pds_information16 VARCHAR2
356 ,p_pds_information17 VARCHAR2
357 ,p_pds_information18 VARCHAR2
358 ,p_pds_information19 VARCHAR2
359 ,p_pds_information20 VARCHAR2
360 ,p_pds_information21 VARCHAR2
361 ,p_pds_information22 VARCHAR2
362 ,p_pds_information23 VARCHAR2
363 ,p_pds_information24 VARCHAR2
364 ,p_pds_information25 VARCHAR2
365 ,p_pds_information26 VARCHAR2
366 ,p_pds_information27 VARCHAR2
367 ,p_pds_information28 VARCHAR2
368 ,p_pds_information29 VARCHAR2
369 ,p_pds_information30 VARCHAR2
370 ,p_adjusted_svc_date DATE
371 ) is
372 cursor pps is select *
373 from per_periods_of_service
374 where rowid = chartorowid(p_row_id)
375 for update nowait;
376 pps_rec pps%rowtype;
377 begin
378 /*
379 hr_utility.trace_on(1,'james');
380 */
381 open pps;
382 fetch pps into pps_rec;
383 close pps;
384 --
385 -- Rtrim all character fields
386 --
387 pps_rec.attribute10 := rtrim(pps_rec.attribute10);
388 pps_rec.attribute11 := rtrim(pps_rec.attribute11);
389 pps_rec.attribute12 := rtrim(pps_rec.attribute12);
390 pps_rec.attribute13 := rtrim(pps_rec.attribute13);
391 pps_rec.attribute14 := rtrim(pps_rec.attribute14);
392 pps_rec.attribute15 := rtrim(pps_rec.attribute15);
393 pps_rec.attribute16 := rtrim(pps_rec.attribute16);
394 pps_rec.attribute17 := rtrim(pps_rec.attribute17);
395 pps_rec.attribute18 := rtrim(pps_rec.attribute18);
396 pps_rec.attribute19 := rtrim(pps_rec.attribute19);
397 pps_rec.attribute20 := rtrim(pps_rec.attribute20);
398 pps_rec.comments := rtrim(pps_rec.comments);
399 pps_rec.leaving_reason := rtrim(pps_rec.leaving_reason);
400 pps_rec.attribute_category := rtrim(pps_rec.attribute_category);
401 pps_rec.attribute1 := rtrim(pps_rec.attribute1);
402 pps_rec.attribute2 := rtrim(pps_rec.attribute2);
403 pps_rec.attribute3 := rtrim(pps_rec.attribute3);
404 pps_rec.attribute4 := rtrim(pps_rec.attribute4);
405 pps_rec.attribute5 := rtrim(pps_rec.attribute5);
406 pps_rec.attribute6 := rtrim(pps_rec.attribute6);
407 pps_rec.attribute7 := rtrim(pps_rec.attribute7);
408 pps_rec.attribute8 := rtrim(pps_rec.attribute8);
409 pps_rec.attribute9 := rtrim(pps_rec.attribute9);
410 pps_rec.pds_information_category := rtrim(pps_rec.pds_information_category);
411 pps_rec.pds_information1 := rtrim(pps_rec.pds_information1);
412 pps_rec.pds_information2 := rtrim(pps_rec.pds_information2);
413 pps_rec.pds_information3 := rtrim(pps_rec.pds_information3);
414 pps_rec.pds_information4 := rtrim(pps_rec.pds_information4);
415 pps_rec.pds_information5 := rtrim(pps_rec.pds_information5);
416 pps_rec.pds_information6 := rtrim(pps_rec.pds_information6);
417 pps_rec.pds_information7 := rtrim(pps_rec.pds_information7);
418 pps_rec.pds_information8 := rtrim(pps_rec.pds_information8);
419 pps_rec.pds_information9 := rtrim(pps_rec.pds_information9);
420 pps_rec.pds_information10 := rtrim(pps_rec.pds_information10);
421 pps_rec.pds_information11 := rtrim(pps_rec.pds_information11);
422 pps_rec.pds_information12 := rtrim(pps_rec.pds_information12);
423 pps_rec.pds_information13 := rtrim(pps_rec.pds_information13);
424 pps_rec.pds_information14 := rtrim(pps_rec.pds_information14);
425 pps_rec.pds_information15 := rtrim(pps_rec.pds_information15);
426 pps_rec.pds_information16 := rtrim(pps_rec.pds_information16);
427 pps_rec.pds_information17 := rtrim(pps_rec.pds_information17);
428 pps_rec.pds_information18 := rtrim(pps_rec.pds_information18);
429 pps_rec.pds_information19 := rtrim(pps_rec.pds_information19);
430 pps_rec.pds_information20 := rtrim(pps_rec.pds_information20);
431 pps_rec.pds_information21 := rtrim(pps_rec.pds_information21);
432 pps_rec.pds_information22 := rtrim(pps_rec.pds_information22);
433 pps_rec.pds_information23 := rtrim(pps_rec.pds_information23);
434 pps_rec.pds_information24 := rtrim(pps_rec.pds_information24);
435 pps_rec.pds_information25 := rtrim(pps_rec.pds_information25);
436 pps_rec.pds_information26 := rtrim(pps_rec.pds_information26);
437 pps_rec.pds_information27 := rtrim(pps_rec.pds_information27);
438 pps_rec.pds_information28 := rtrim(pps_rec.pds_information28);
439 pps_rec.pds_information29 := rtrim(pps_rec.pds_information29);
440 pps_rec.pds_information30 := rtrim(pps_rec.pds_information30);
441 pps_rec.adjusted_svc_date := rtrim(pps_rec.adjusted_svc_date);
442 --
443 if( (( p_period_of_service_id = pps_rec.period_of_service_id)
444 or (pps_rec.period_of_service_id is null
445 and (p_period_of_service_id is null)))
446 and (( p_business_group_id = pps_rec.business_group_id)
447 or (pps_rec.business_group_id is null
448 and (p_business_group_id is null)))
449 and (( p_person_id = pps_rec.person_id)
450 or (pps_rec.person_id is null
451 and (p_person_id is null)))
452 and (( p_date_start = pps_rec.date_start)
453 or (pps_rec.date_start is null
454 and (p_date_start is null)))
455 and (( p_termination_accepted_per_id =
456 pps_rec.termination_accepted_person_id)
457 or (pps_rec.termination_accepted_person_id is null
458 and (p_termination_accepted_per_id is null)))
459 and (( p_accepted_termination_date = pps_rec.accepted_termination_date)
460 or (pps_rec.accepted_termination_date is null
461 and (p_accepted_termination_date is null)))
462 and (( p_actual_termination_date = pps_rec.actual_termination_date)
463 or (pps_rec.actual_termination_date is null
464 and (p_actual_termination_date is null)))
465 and (( p_comments = pps_rec.comments)
466 or (pps_rec.comments is null
467 and (p_comments is null)))
468 and (( p_final_process_date = pps_rec.final_process_date)
469 or (pps_rec.final_process_date is null
470 and (p_final_process_date is null)))
471 and (( p_last_standard_process_date = pps_rec.last_standard_process_date)
472 or (pps_rec.last_standard_process_date is null
473 and (p_last_standard_process_date is null)))
474 and (( p_leaving_reason = pps_rec.leaving_reason)
475 or (pps_rec.leaving_reason is null
476 and (p_leaving_reason is null)))
477 and (( p_notified_termination_date = pps_rec.notified_termination_date)
478 or (pps_rec.notified_termination_date is null
479 and (p_notified_termination_date is null)))
480 and (( p_projected_termination_date = pps_rec.projected_termination_date)
481 or (pps_rec.projected_termination_date is null
482 and (p_projected_termination_date is null)))
483 and (( p_request_id = pps_rec.request_id)
484 or (pps_rec.request_id is null
485 and (p_request_id is null)))
486 and (( p_program_application_id = pps_rec.program_application_id)
487 or (pps_rec.program_application_id is null
488 and (p_program_application_id is null)))
489 and (( p_program_id = pps_rec.program_id)
490 or (pps_rec.program_id is null
491 and (p_program_id is null)))
492 and (( p_program_update_date = pps_rec.program_update_date)
493 or (pps_rec.program_update_date is null
494 and (p_program_update_date is null)))
495 and (( p_attribute_category = pps_rec.attribute_category)
496 or (pps_rec.attribute_category is null
497 and (p_attribute_category is null)))
498 and (( p_attribute1 = pps_rec.attribute1)
499 or (pps_rec.attribute1 is null
500 and (p_attribute1 is null)))
501 and (( p_attribute2 = pps_rec.attribute2)
502 or (pps_rec.attribute2 is null
503 and (p_attribute2 is null)))
504 and (( p_attribute3 = pps_rec.attribute3)
505 or (pps_rec.attribute3 is null
506 and (p_attribute3 is null)))
507 and (( p_attribute4 = pps_rec.attribute4)
508 or (pps_rec.attribute4 is null
509 and (p_attribute4 is null)))
510 and (( p_attribute5 = pps_rec.attribute5)
511 or (pps_rec.attribute5 is null
512 and (p_attribute5 is null)))
513 and (( p_attribute6 = pps_rec.attribute6)
514 or (pps_rec.attribute6 is null
515 and (p_attribute6 is null)))
516 and (( p_attribute7 = pps_rec.attribute7)
517 or (pps_rec.attribute7 is null
518 and (p_attribute7 is null)))
519 and (( p_attribute8 = pps_rec.attribute8)
520 or (pps_rec.attribute8 is null
521 and (p_attribute8 is null)))
522 and (( p_attribute9 = pps_rec.attribute9)
523 or (pps_rec.attribute9 is null
524 and (p_attribute9 is null)))
525 and (( p_attribute10 = pps_rec.attribute10)
526 or (pps_rec.attribute10 is null
527 and (p_attribute10 is null)))
528 and (( p_attribute11 = pps_rec.attribute11)
529 or (pps_rec.attribute11 is null
530 and (p_attribute11 is null)))
531 and (( p_attribute12 = pps_rec.attribute12)
532 or (pps_rec.attribute12 is null
533 and (p_attribute12 is null)))
534 and (( p_attribute13 = pps_rec.attribute13)
535 or (pps_rec.attribute13 is null
536 and (p_attribute13 is null)))
537 and (( p_attribute14 = pps_rec.attribute14)
538 or (pps_rec.attribute14 is null
539 and (p_attribute14 is null)))
540 and (( p_attribute15 = pps_rec.attribute15)
541 or (pps_rec.attribute15 is null
542 and (p_attribute15 is null)))
543 and (( p_attribute16 = pps_rec.attribute16)
544 or (pps_rec.attribute16 is null
545 and (p_attribute16 is null)))
546 and (( p_attribute17 = pps_rec.attribute17)
547 or (pps_rec.attribute17 is null
548 and (p_attribute17 is null)))
549 and (( p_attribute18 = pps_rec.attribute18)
550 or (pps_rec.attribute18 is null
551 and (p_attribute18 is null)))
552 and (( p_attribute19 = pps_rec.attribute19)
553 or (pps_rec.attribute19 is null
554 and (p_attribute19 is null)))
555 and (( p_attribute20 = pps_rec.attribute20)
556 or (pps_rec.attribute20 is null
557 and (p_attribute20 is null)))
558 and (( p_pds_information_category = pps_rec.pds_information_category)
559 or (pps_rec.pds_information_category is null
560 and (p_pds_information_category is null)))
561 and (( p_pds_information1 = pps_rec.pds_information1)
562 or (pps_rec.pds_information1 is null
563 and (p_pds_information1 is null)))
564 and (( p_pds_information2 = pps_rec.pds_information2)
565 or (pps_rec.pds_information2 is null
566 and (p_pds_information2 is null)))
567 and (( p_pds_information3 = pps_rec.pds_information3)
568 or (pps_rec.pds_information3 is null
569 and (p_pds_information3 is null)))
570 and (( p_pds_information4 = pps_rec.pds_information4)
571 or (pps_rec.pds_information4 is null
572 and (p_pds_information4 is null)))
573 and (( p_pds_information5 = pps_rec.pds_information5)
574 or (pps_rec.pds_information5 is null
575 and (p_pds_information5 is null)))
576 and (( p_pds_information6 = pps_rec.pds_information6)
577 or (pps_rec.pds_information6 is null
578 and (p_pds_information6 is null)))
579 and (( p_pds_information7 = pps_rec.pds_information7)
580 or (pps_rec.pds_information7 is null
581 and (p_pds_information7 is null)))
582 and (( p_pds_information8 = pps_rec.pds_information8)
583 or (pps_rec.pds_information8 is null
584 and (p_pds_information8 is null)))
585 and (( p_pds_information9 = pps_rec.pds_information9)
586 or (pps_rec.pds_information9 is null
587 and (p_pds_information9 is null)))
588 and (( p_pds_information10 = pps_rec.pds_information10)
589 or (pps_rec.pds_information10 is null
590 and (p_pds_information10 is null)))
591 and (( p_pds_information11 = pps_rec.pds_information11)
592 or (pps_rec.pds_information11 is null
593 and (p_pds_information11 is null)))
594 and (( p_pds_information12 = pps_rec.pds_information12)
595 or (pps_rec.pds_information12 is null
596 and (p_pds_information12 is null)))
597 and (( p_pds_information13 = pps_rec.pds_information13)
598 or (pps_rec.pds_information13 is null
599 and (p_pds_information13 is null)))
600 and (( p_pds_information14 = pps_rec.pds_information14)
601 or (pps_rec.pds_information14 is null
602 and (p_pds_information14 is null)))
603 and (( p_pds_information15 = pps_rec.pds_information15)
604 or (pps_rec.pds_information15 is null
605 and (p_pds_information15 is null)))
606 and (( p_pds_information16 = pps_rec.pds_information16)
607 or (pps_rec.pds_information16 is null
608 and (p_pds_information16 is null)))
609 and (( p_pds_information17 = pps_rec.pds_information17)
610 or (pps_rec.pds_information17 is null
611 and (p_pds_information17 is null)))
612 and (( p_pds_information18 = pps_rec.pds_information18)
613 or (pps_rec.pds_information18 is null
614 and (p_pds_information18 is null)))
615 and (( p_pds_information19 = pps_rec.pds_information19)
616 or (pps_rec.pds_information19 is null
617 and (p_pds_information19 is null)))
618 and (( p_pds_information20 = pps_rec.pds_information20)
619 or (pps_rec.pds_information20 is null
620 and (p_pds_information20 is null)))
621 and (( p_pds_information21 = pps_rec.pds_information21)
622 or (pps_rec.pds_information21 is null
623 and (p_pds_information21 is null)))
624 and (( p_pds_information22 = pps_rec.pds_information22)
625 or (pps_rec.pds_information22 is null
626 and (p_pds_information22 is null)))
627 and (( p_pds_information23 = pps_rec.pds_information23)
628 or (pps_rec.pds_information23 is null
629 and (p_pds_information23 is null)))
630 and (( p_pds_information24 = pps_rec.pds_information24)
631 or (pps_rec.pds_information24 is null
632 and (p_pds_information24 is null)))
633 and (( p_pds_information25 = pps_rec.pds_information25)
634 or (pps_rec.pds_information25 is null
635 and (p_pds_information25 is null)))
636 and (( p_pds_information26 = pps_rec.pds_information26)
637 or (pps_rec.pds_information26 is null
638 and (p_pds_information26 is null)))
639 and (( p_pds_information27 = pps_rec.pds_information27)
640 or (pps_rec.pds_information27 is null
641 and (p_pds_information27 is null)))
642 and (( p_pds_information28 = pps_rec.pds_information28)
643 or (pps_rec.pds_information28 is null
644 and (p_pds_information28 is null)))
645 and (( p_pds_information29 = pps_rec.pds_information29)
646 or (pps_rec.pds_information29 is null
647 and (p_pds_information29 is null)))
648 and (( p_pds_information30 = pps_rec.pds_information30)
649 or (pps_rec.pds_information30 is null
650 and (p_pds_information30 is null)))
651 and ((p_adjusted_svc_date = pps_rec.adjusted_svc_date)
652 or (pps_rec.adjusted_svc_date is null
653 and (p_adjusted_svc_date is null)))
654 ) then
655 return;
656 end if;
657 /*
658 hr_utility.trace_off;
659 */
660 -- Record chaged by anothers user.
661 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
662 app_exception.raise_exception ;
663 exception when no_data_found then
664 raise;
665 when others then raise;
666 end lock_row;
667 ----------------------------------------------------------------------------
668 procedure update_term_row(p_row_id VARCHAR2
669 ,p_period_of_service_id NUMBER
670 ,p_business_group_id NUMBER
671 ,p_person_id NUMBER
672 ,p_date_start DATE
673 ,p_termination_accepted_per_id NUMBER
674 ,p_accepted_termination_date DATE
675 ,p_actual_termination_date DATE
676 ,p_comments VARCHAR2
677 ,p_final_process_date DATE
678 ,p_last_standard_process_date DATE
679 ,p_leaving_reason VARCHAR2
680 ,p_notified_termination_date DATE
681 ,p_projected_termination_date DATE
682 ,p_request_id NUMBER
683 ,p_program_application_id NUMBER
684 ,p_program_id NUMBER
685 ,p_program_update_date DATE
686 ,p_attribute_category VARCHAR2
687 ,p_attribute1 VARCHAR2
688 ,p_attribute2 VARCHAR2
689 ,p_attribute3 VARCHAR2
690 ,p_attribute4 VARCHAR2
691 ,p_attribute5 VARCHAR2
692 ,p_attribute6 VARCHAR2
693 ,p_attribute7 VARCHAR2
694 ,p_attribute8 VARCHAR2
695 ,p_attribute9 VARCHAR2
696 ,p_attribute10 VARCHAR2
697 ,p_attribute11 VARCHAR2
698 ,p_attribute12 VARCHAR2
699 ,p_attribute13 VARCHAR2
700 ,p_attribute14 VARCHAR2
701 ,p_attribute15 VARCHAR2
702 ,p_attribute16 VARCHAR2
703 ,p_attribute17 VARCHAR2
704 ,p_attribute18 VARCHAR2
705 ,p_attribute19 VARCHAR2
706 ,p_attribute20 VARCHAR2
707 ,p_initiate_cancellation VARCHAR2
708 ,p_s_final_process_date IN OUT NOCOPY DATE
709 ,p_s_actual_termination_date IN OUT NOCOPY DATE
710 ,p_c_assignment_status_type_id IN OUT NOCOPY NUMBER
711 ,p_d_status VARCHAR2
712 ,p_requery_required IN OUT NOCOPY VARCHAR2
713 ,p_clear_details VARCHAR2 DEFAULT 'N'
714 ,p_legislation_code VARCHAR2
715 ,p_pds_information_category VARCHAR2
716 ,p_pds_information1 VARCHAR2
717 ,p_pds_information2 VARCHAR2
718 ,p_pds_information3 VARCHAR2
719 ,p_pds_information4 VARCHAR2
720 ,p_pds_information5 VARCHAR2
721 ,p_pds_information6 VARCHAR2
722 ,p_pds_information7 VARCHAR2
723 ,p_pds_information8 VARCHAR2
724 ,p_pds_information9 VARCHAR2
725 ,p_pds_information10 VARCHAR2
726 ,p_pds_information11 VARCHAR2
727 ,p_pds_information12 VARCHAR2
728 ,p_pds_information13 VARCHAR2
729 ,p_pds_information14 VARCHAR2
730 ,p_pds_information15 VARCHAR2
731 ,p_pds_information16 VARCHAR2
732 ,p_pds_information17 VARCHAR2
733 ,p_pds_information18 VARCHAR2
734 ,p_pds_information19 VARCHAR2
735 ,p_pds_information20 VARCHAR2
736 ,p_pds_information21 VARCHAR2
737 ,p_pds_information22 VARCHAR2
738 ,p_pds_information23 VARCHAR2
739 ,p_pds_information24 VARCHAR2
740 ,p_pds_information25 VARCHAR2
741 ,p_pds_information26 VARCHAR2
742 ,p_pds_information27 VARCHAR2
743 ,p_pds_information28 VARCHAR2
744 ,p_pds_information29 VARCHAR2
745 ,p_pds_information30 VARCHAR2
746 ,p_adjusted_svc_date DATE) is
747
748 p_dodwarning varchar2(1);
749
750 begin update_term_row
751 (p_row_id
752 ,p_period_of_service_id
753 ,p_business_group_id
754 ,p_person_id
755 ,p_date_start
756 ,p_termination_accepted_per_id
757 ,p_accepted_termination_date
758 ,p_actual_termination_date
759 ,p_comments
760 ,p_final_process_date
761 ,p_last_standard_process_date
762 ,p_leaving_reason
763 ,p_notified_termination_date
764 ,p_projected_termination_date
765 ,p_request_id
766 ,p_program_application_id
767 ,p_program_id
768 ,p_program_update_date
769 ,p_attribute_category
770 ,p_attribute1
771 ,p_attribute2
772 ,p_attribute3
773 ,p_attribute4
774 ,p_attribute5
775 ,p_attribute6
776 ,p_attribute7
777 ,p_attribute8
778 ,p_attribute9
779 ,p_attribute10
780 ,p_attribute11
781 ,p_attribute12
782 ,p_attribute13
783 ,p_attribute14
784 ,p_attribute15
785 ,p_attribute16
786 ,p_attribute17
787 ,p_attribute18
788 ,p_attribute19
789 ,p_attribute20
790 ,p_initiate_cancellation
791 ,p_s_final_process_date
792 ,p_s_actual_termination_date
793 ,p_c_assignment_status_type_id
794 ,p_d_status
795 ,p_requery_required
796 ,p_clear_details
797 ,p_legislation_code
798 ,p_pds_information_category
799 ,p_pds_information1
800 ,p_pds_information2
801 ,p_pds_information3
802 ,p_pds_information4
803 ,p_pds_information5
804 ,p_pds_information6
805 ,p_pds_information7
806 ,p_pds_information8
807 ,p_pds_information9
808 ,p_pds_information10
809 ,p_pds_information11
810 ,p_pds_information12
811 ,p_pds_information13
812 ,p_pds_information14
813 ,p_pds_information15
814 ,p_pds_information16
815 ,p_pds_information17
816 ,p_pds_information18
817 ,p_pds_information19
818 ,p_pds_information20
819 ,p_pds_information21
820 ,p_pds_information22
821 ,p_pds_information23
822 ,p_pds_information24
823 ,p_pds_information25
824 ,p_pds_information26
825 ,p_pds_information27
826 ,p_pds_information28
827 ,p_pds_information29
828 ,p_pds_information30
829 ,p_adjusted_svc_date
830 ,p_dodwarning
831 );
832 --
833 end;
834 --
835 procedure update_term_row(p_row_id VARCHAR2
836 ,p_period_of_service_id NUMBER
837 ,p_business_group_id NUMBER
838 ,p_person_id NUMBER
839 ,p_date_start DATE
840 ,p_termination_accepted_per_id NUMBER
841 ,p_accepted_termination_date DATE
842 ,p_actual_termination_date DATE
843 ,p_comments VARCHAR2
844 ,p_final_process_date DATE
845 ,p_last_standard_process_date DATE
846 ,p_leaving_reason VARCHAR2
847 ,p_notified_termination_date DATE
848 ,p_projected_termination_date DATE
849 ,p_request_id NUMBER
850 ,p_program_application_id NUMBER
851 ,p_program_id NUMBER
852 ,p_program_update_date DATE
853 ,p_attribute_category VARCHAR2
854 ,p_attribute1 VARCHAR2
855 ,p_attribute2 VARCHAR2
856 ,p_attribute3 VARCHAR2
857 ,p_attribute4 VARCHAR2
858 ,p_attribute5 VARCHAR2
859 ,p_attribute6 VARCHAR2
860 ,p_attribute7 VARCHAR2
861 ,p_attribute8 VARCHAR2
862 ,p_attribute9 VARCHAR2
863 ,p_attribute10 VARCHAR2
864 ,p_attribute11 VARCHAR2
865 ,p_attribute12 VARCHAR2
866 ,p_attribute13 VARCHAR2
867 ,p_attribute14 VARCHAR2
868 ,p_attribute15 VARCHAR2
869 ,p_attribute16 VARCHAR2
870 ,p_attribute17 VARCHAR2
871 ,p_attribute18 VARCHAR2
872 ,p_attribute19 VARCHAR2
873 ,p_attribute20 VARCHAR2
874 ,p_initiate_cancellation VARCHAR2
875 ,p_s_final_process_date IN OUT NOCOPY DATE
876 ,p_s_actual_termination_date IN OUT NOCOPY DATE
877 ,p_c_assignment_status_type_id IN OUT NOCOPY NUMBER
878 ,p_d_status VARCHAR2
879 ,p_requery_required IN OUT NOCOPY VARCHAR2
880 ,p_clear_details VARCHAR2 DEFAULT 'N'
881 ,p_legislation_code VARCHAR2
882 ,p_pds_information_category VARCHAR2
883 ,p_pds_information1 VARCHAR2
884 ,p_pds_information2 VARCHAR2
885 ,p_pds_information3 VARCHAR2
886 ,p_pds_information4 VARCHAR2
887 ,p_pds_information5 VARCHAR2
888 ,p_pds_information6 VARCHAR2
889 ,p_pds_information7 VARCHAR2
890 ,p_pds_information8 VARCHAR2
891 ,p_pds_information9 VARCHAR2
892 ,p_pds_information10 VARCHAR2
893 ,p_pds_information11 VARCHAR2
894 ,p_pds_information12 VARCHAR2
895 ,p_pds_information13 VARCHAR2
896 ,p_pds_information14 VARCHAR2
897 ,p_pds_information15 VARCHAR2
898 ,p_pds_information16 VARCHAR2
899 ,p_pds_information17 VARCHAR2
900 ,p_pds_information18 VARCHAR2
901 ,p_pds_information19 VARCHAR2
902 ,p_pds_information20 VARCHAR2
903 ,p_pds_information21 VARCHAR2
904 ,p_pds_information22 VARCHAR2
905 ,p_pds_information23 VARCHAR2
906 ,p_pds_information24 VARCHAR2
907 ,p_pds_information25 VARCHAR2
908 ,p_pds_information26 VARCHAR2
909 ,p_pds_information27 VARCHAR2
910 ,p_pds_information28 VARCHAR2
911 ,p_pds_information29 VARCHAR2
912 ,p_pds_information30 VARCHAR2
913 ,p_adjusted_svc_date DATE
914 ,p_dodwarning OUT NOCOPY VARCHAR2) is
915 --
916 l_proc varchar2(30) := 'update_term_row';
917 l_old_date_start date;
918 l_old_leaving_reason varchar2(30);
919 l_old_final_process_date date;
920 l_old_actual_termination_date date;
921 l_localization_action boolean := FALSE;
922 l_action varchar2(20);
923 --
924 -- Amended for Bug 1293835
925 cursor get_old_pds_values is
926 select date_start,
927 actual_termination_date,
928 final_process_date,
929 leaving_reason
930 from per_periods_of_service
931 where period_of_service_id = p_period_of_service_id;
932
933 -- Added for Bug 1150185
934 --
935 cursor c1(p_date date) is
936 select rowid,
937 business_group_id,
938 person_id,
939 effective_start_date,
940 effective_end_date,
941 date_of_birth,
942 date_of_death,
943 marital_status,
944 on_military_service,
945 registered_disabled_flag,
946 sex,
947 student_status,
948 coord_ben_med_pln_no,
949 coord_ben_no_cvg_flag,
950 uses_tobacco_flag,
951 benefit_group_id,
952 per_information10,
953 original_date_of_hire,
954 dpdnt_vlntry_svce_flag,
955 receipt_of_death_cert_date,
956 attribute1,
957 attribute2,
958 attribute3,
959 attribute4,
960 attribute5,
961 attribute6,
962 attribute7,
963 attribute8,
964 attribute9,
965 attribute10,
966 attribute11,
967 attribute12,
968 attribute13,
969 attribute14,
970 attribute15,
971 attribute16,
972 attribute17,
973 attribute18,
974 attribute19,
975 attribute20,
976 attribute21,
977 attribute22,
978 attribute23,
979 attribute24,
980 attribute25,
981 attribute26,
982 attribute27,
983 attribute28,
984 attribute29,
985 attribute30
986 from per_all_people_f
987 where person_id = p_person_id
988 and p_date
989 between effective_start_date
990 and effective_end_date;
991 --
992 l_c1 c1%rowtype;
993 l_c2 c1%rowtype;
994 --
995 -- End Addition for Bug 1150185
996 --
997 l_person_type_usage_id NUMBER(15);
998 l_object_version_number NUMBER(15);
999 l_effective_start_date DATE;
1000 l_effective_end_date DATE;
1001
1002 begin
1003 --
1004 -- PRE-UPDATE processing
1005 --
1006 hr_utility.set_location('Entering '||l_proc,0);
1007 --
1008 -- Get the current values for the PDS so that we can
1009 -- maintain the PTU information.
1010 --
1011 open get_old_pds_values;
1012 fetch get_old_pds_values into l_old_date_start, l_old_actual_termination_date
1013 , l_old_final_process_date, l_old_leaving_reason;
1014 close get_old_pds_values;
1015
1016 -- Checking to see if we need to only update pds
1017 -- i.e. no termination/rev term/ leav_reas needs to be done
1018
1019 if (nvl(p_leaving_reason , hr_api.g_varchar2) = nvl(l_old_leaving_reason,hr_api.g_varchar2))
1020 and (nvl(p_actual_termination_date,hr_api.g_date) = nvl(l_old_actual_termination_date,hr_api.g_date))
1021 and (nvl(p_final_process_date,hr_api.g_date) = nvl(l_old_final_process_date,hr_api.g_date))
1022 and nvl(p_initiate_cancellation,'N') <> 'Y' then
1023 hr_utility.set_location('Localization Action - True',99);
1024 l_localization_action := TRUE;
1025
1026 else
1027 hr_utility.set_location('Localization Action - False',99);
1028 l_localization_action := FALSE;
1029 end if;
1030 --
1031 -- Localization do not want this processing to take place.
1032 --
1033 if not l_localization_action THEN
1034
1035 --
1036 -- Set the maintain PTU action based on the p_initiate_cancellation
1037 -- and whether the leaving reason has changed.
1038 --
1039 if p_initiate_cancellation = 'Y' then
1040 l_action := 'REV_TERM';
1041 elsif p_leaving_reason <> l_old_leaving_reason then
1042 l_action := 'LEAV_REAS';
1043 else
1044 l_action := 'TERM';
1045 end if;
1046 --
1047 -- Fix for bug 3889294 starts here.
1048 -- Commented out the code as it duplicate call.
1049 --
1050 /*
1051 if p_initiate_cancellation = 'Y' then
1052 hrempter.cancel_termination(p_person_id
1053 ,p_actual_termination_date
1054 ,p_clear_details);
1055 els
1056 */
1057 --
1058 -- And add following if condition.
1059 --
1060 IF p_initiate_cancellation <> 'Y' THEN
1061 --
1062 if p_actual_termination_date is not null THEN
1063 if (p_last_standard_process_date IS null
1064 and p_legislation_code <> 'US') THEN
1065 fnd_message.set_name('PAY','HR_7576_ALL_MAN_PRO_FIELD');
1066 app_exception.raise_exception;
1067 end if;
1068 if p_actual_termination_date > p_last_standard_process_date then
1069 fnd_message.set_name('PAY','HR_6158_EMP_DATE_CHECK');
1070 app_exception.raise_exception;
1071 end if;
1072 if p_s_final_process_date is null then
1073 if p_actual_termination_date is not null then
1074 if p_final_process_date is null then
1075 if p_d_status is null then
1076 fnd_message.set_name('PAY','HR_6735_EMP_TERM_NO_STATUS');
1077 app_exception.raise_exception;
1078 end if;
1079 end if;
1080 -- do the ref int stuff
1081 if p_s_actual_termination_date IS null then
1082 hrempter.terminate_employee('PRE_UPDATE'
1083 ,p_business_group_id
1084 ,p_person_id
1085 ,p_c_assignment_status_type_id
1086 ,p_actual_termination_date
1087 ,p_last_standard_process_date
1088 ,p_final_process_date);
1089 elsif p_final_process_date is not null then
1090 hrempter.employee_shutdown('PRE_UPDATE'
1091 ,p_person_id
1092 ,p_final_process_date);
1093 end if;
1094 end if;
1095 end if;
1096 end if;
1097 --
1098 END IF;
1099 --
1100 end if;
1101 --
1102 --
1103 -- Fix for bug 3889294 starts here. Moved the following code
1104 -- to procedure hrempter.cancle_termination.
1105 --
1106 /*
1107 --
1108 -- Added to support reverse termination processing for legislations
1109 --
1110 if p_initiate_cancellation = 'Y' then
1111 --
1112 open csr_leg_code;
1113 fetch csr_leg_code into l_leg_code;
1114 --
1115 if csr_leg_code%found then
1116 --
1117 -- If one exists then we must check whether there exists a legislation
1118 -- specific Validate_Delete procedure. This should be named in the format
1119 -- PER_XX_TERMINATION.REVERSE
1120 -- If it does exist then construct an anonymous PL/SQL block to call
1121 -- the procedure.
1122 --
1123 l_package_name := 'PER_'||l_leg_code||'_TERMINATION';
1124 l_procedure_name := 'REVERSE';
1125 --
1126 -- Close Cursor added a part of fix for bug 1858597
1127 --
1128 close csr_leg_code;
1129 --
1130 -- Check package exists
1131 --
1132 open csr_leg_pkg(l_package_name);
1133 fetch csr_leg_pkg into l_dummy;
1134 --
1135 if csr_leg_pkg%found then
1136 --
1137 close csr_leg_pkg;
1138 --
1139 -- Added as part of fix for bug 1858597
1140 --
1141 EXECUTE IMMEDIATE 'BEGIN '||
1142 l_package_name||'.'||
1143 l_procedure_name||
1144 '(:P_PERIOD_OF_SERVICE_ID,'||
1145 ':P_ACTUAL_TERMINATION_DATE,'||
1146 ':P_LEAVING_REASON); END;'
1147 USING p_period_of_service_id
1148 ,p_actual_termination_date
1149 ,l_old_leaving_reason;
1150 --
1151 end if;
1152 --
1153 end if;
1154 end if;
1155 */
1156 --
1157 -- Fix for bug 3889294 ends here.
1158 --
1159 --
1160 -- VT #1364630 08/23/00
1161 -- 3665620 Removed the code to clear of DFF during Reverse Termination
1162 per_periods_of_service_pkg.update_row(p_row_id => p_row_id
1163 ,p_period_of_service_id => p_period_of_service_id
1164 ,p_business_group_id => p_business_group_id
1165 ,p_person_id => p_person_id
1166 ,p_date_start => p_date_start
1167 ,p_termination_accepted_per_id => p_termination_accepted_per_id
1168 ,p_accepted_termination_date => p_accepted_termination_date
1169 ,p_actual_termination_date => p_actual_termination_date
1170 ,p_comments => p_comments
1171 ,p_final_process_date => p_final_process_date
1172 ,p_last_standard_process_date => p_last_standard_process_date
1173 ,p_leaving_reason => p_leaving_reason
1174 ,p_notified_termination_date => p_notified_termination_date
1175 ,p_projected_termination_date => p_projected_termination_date
1176 ,p_request_id => p_request_id
1177 ,p_program_application_id => p_program_application_id
1178 ,p_program_id => p_program_id
1179 ,p_program_update_date => p_program_update_date
1180 ,p_attribute_category => p_attribute_category
1181 ,p_attribute1 => p_attribute1
1182 ,p_attribute2 => p_attribute2
1183 ,p_attribute3 => p_attribute3
1184 ,p_attribute4 => p_attribute4
1185 ,p_attribute5 => p_attribute5
1186 ,p_attribute6 => p_attribute6
1187 ,p_attribute7 => p_attribute7
1188 ,p_attribute8 => p_attribute8
1189 ,p_attribute9 => p_attribute9
1190 ,p_attribute10 => p_attribute10
1191 ,p_attribute11 => p_attribute11
1192 ,p_attribute12 => p_attribute12
1193 ,p_attribute13 => p_attribute13
1194 ,p_attribute14 => p_attribute14
1195 ,p_attribute15 => p_attribute15
1196 ,p_attribute16 => p_attribute16
1197 ,p_attribute17 => p_attribute17
1198 ,p_attribute18 => p_attribute18
1199 ,p_attribute19 => p_attribute19
1200 ,p_attribute20 => p_attribute20
1201 ,p_pds_information_category => p_pds_information_category
1202 ,p_pds_information1 => p_pds_information1
1203 ,p_pds_information2 => p_pds_information2
1204 ,p_pds_information3 => p_pds_information3
1205 ,p_pds_information4 => p_pds_information4
1206 ,p_pds_information5 => p_pds_information5
1207 ,p_pds_information6 => p_pds_information6
1208 ,p_pds_information7 => p_pds_information7
1209 ,p_pds_information8 => p_pds_information8
1210 ,p_pds_information9 => p_pds_information9
1211 ,p_pds_information10 => p_pds_information10
1212 ,p_pds_information11 => p_pds_information11
1213 ,p_pds_information12 => p_pds_information12
1214 ,p_pds_information13 => p_pds_information13
1215 ,p_pds_information14 => p_pds_information14
1216 ,p_pds_information15 => p_pds_information15
1217 ,p_pds_information16 => p_pds_information16
1218 ,p_pds_information17 => p_pds_information17
1219 ,p_pds_information18 => p_pds_information18
1220 ,p_pds_information19 => p_pds_information19
1221 ,p_pds_information20 => p_pds_information20
1222 ,p_pds_information21 => p_pds_information21
1223 ,p_pds_information22 => p_pds_information22
1224 ,p_pds_information23 => p_pds_information23
1225 ,p_pds_information24 => p_pds_information24
1226 ,p_pds_information25 => p_pds_information25
1227 ,p_pds_information26 => p_pds_information26
1228 ,p_pds_information27 => p_pds_information27
1229 ,p_pds_information28 => p_pds_information28
1230 ,p_pds_information29 => p_pds_information29
1231 ,p_pds_information30 => p_pds_information30
1232 ,p_adjusted_svc_date => p_adjusted_svc_date
1233 );
1234 --
1235 -- Post Update processing
1236 --
1237 -- Localization do not want this processing to take place.
1238 --
1239 if not l_localization_action THEN
1240
1241 -- fix bug 1234721
1242 -- parameter should remain null
1243 -- p_s_final_process_date := p_final_process_date;
1244 hr_utility.set_location('Entering: hrempter.cancel_termination'|| l_proc, 5);
1245 if p_initiate_cancellation ='Y' then
1246 hrempter.cancel_termination(p_person_id
1247 ,p_actual_termination_date
1248 ,p_clear_details);
1249 p_requery_required := 'Y';
1250 hr_utility.set_location('After: hrempter.cancel_termination'|| l_proc, 10);
1251 /* This code is added to raise BE on reverse termination of employee */
1252
1253 hr_ex_employee_be4.reverse_terminate_employee_a(p_person_id
1254 ,p_actual_termination_date
1255 ,p_clear_details);
1256 hr_utility.set_location('After: hr_ex_employee_be4.reverse_terminate_employee_a'|| l_proc, 15);
1257 /* End of code added to raise BE */
1258 elsif p_actual_termination_date is not null then
1259 if p_s_actual_termination_date is null then
1260 -- fix bug 1234721
1261 -- parameter should remain null
1262 -- p_s_actual_termination_date := p_actual_termination_date;
1263 --
1264 --
1265 /* This delete is now doen in hrempter.terminate_employee so that the
1266 deletion of pay proposals can be kept in step with the deletion
1267 of elements.
1268
1269 per_periods_of_service_pkg.delete_per_pay_proposals(
1270 p_period_of_service_id => p_period_of_service_id
1271 ,p_actual_termination_date => p_actual_termination_date);
1272 */
1273 hrempter.terminate_employee('POST_UPDATE'
1274 ,p_business_group_id
1275 ,p_person_id
1276 ,p_c_assignment_status_type_id
1277 ,p_actual_termination_date
1278 ,p_last_standard_process_date
1279 ,p_final_process_date);
1280 p_requery_required := 'Y';
1281 --
1282 elsif p_final_process_date is not null then
1283 hrempter.employee_shutdown ('POST_UPDATE'
1284 ,p_person_id
1285 ,p_final_process_date);
1286 p_requery_required := 'Y';
1287 end if;
1288 end if;
1289 --
1290 if p_actual_termination_date is not null and
1291 p_leaving_reason = 'D' then
1292 update per_people_f
1293 set date_of_death = p_actual_termination_date
1294 where person_id = p_person_id
1295 and effective_start_date >= p_actual_termination_date +1
1296 and date_of_death is null;
1297 if SQL%FOUND then
1298 p_dodwarning := 'Y';
1299 end if;
1300 --
1301 -- Fixed for WWBUG 1150185.
1302 -- Call benefit dt handler when date of death changed
1303 --
1304 -- First get old stuff
1305 --
1306 open c1(p_actual_termination_date);
1307 fetch c1 into l_c1;
1308 close c1;
1309 --
1310 -- Now get new stuff following update above
1311 --
1312 open c1(p_actual_termination_date+1);
1313 fetch c1 into l_c2;
1314 close c1;
1315 --
1316 ben_dt_trgr_handle.person
1317 (p_rowid => l_c1.rowid
1318 ,p_business_group_id => l_c2.business_group_id
1319 ,p_person_id => l_c2.person_id
1320 ,p_effective_start_date => l_c2.effective_start_date
1321 ,p_effective_end_date => l_c2.effective_start_date
1322 ,p_date_of_birth => l_c2.date_of_birth
1323 ,p_date_of_death => l_c2.date_of_death
1324 ,p_marital_status => l_c2.marital_status
1325 ,p_on_military_service => l_c2.on_military_service
1326 ,p_registered_disabled_flag => l_c2.registered_disabled_flag
1327 ,p_sex => l_c2.sex
1328 ,p_student_status => l_c2.student_status
1329 ,p_coord_ben_med_pln_no => l_c2.coord_ben_med_pln_no
1330 ,p_coord_ben_no_cvg_flag => l_c2.coord_ben_no_cvg_flag
1331 ,p_uses_tobacco_flag => l_c2.uses_tobacco_flag
1332 ,p_benefit_group_id => l_c2.benefit_group_id
1333 ,p_per_information10 => l_c2.per_information10
1334 ,p_original_date_of_hire => l_c2.original_date_of_hire
1335 ,p_dpdnt_vlntry_svce_flag => l_c2.dpdnt_vlntry_svce_flag
1336 ,p_receipt_of_death_cert_date => l_c2.receipt_of_death_cert_date
1337 ,p_attribute1 => l_c2.attribute1
1338 ,p_attribute2 => l_c2.attribute2
1339 ,p_attribute3 => l_c2.attribute3
1340 ,p_attribute4 => l_c2.attribute4
1341 ,p_attribute5 => l_c2.attribute5
1342 ,p_attribute6 => l_c2.attribute6
1343 ,p_attribute7 => l_c2.attribute7
1344 ,p_attribute8 => l_c2.attribute8
1345 ,p_attribute9 => l_c2.attribute9
1346 ,p_attribute10 => l_c2.attribute10
1347 ,p_attribute11 => l_c2.attribute11
1348 ,p_attribute12 => l_c2.attribute12
1349 ,p_attribute13 => l_c2.attribute13
1350 ,p_attribute14 => l_c2.attribute14
1351 ,p_attribute15 => l_c2.attribute15
1352 ,p_attribute16 => l_c2.attribute16
1353 ,p_attribute17 => l_c2.attribute17
1354 ,p_attribute18 => l_c2.attribute18
1355 ,p_attribute19 => l_c2.attribute19
1356 ,p_attribute20 => l_c2.attribute20
1357 ,p_attribute21 => l_c2.attribute21
1358 ,p_attribute22 => l_c2.attribute22
1359 ,p_attribute23 => l_c2.attribute23
1360 ,p_attribute24 => l_c2.attribute24
1361 ,p_attribute25 => l_c2.attribute25
1362 ,p_attribute26 => l_c2.attribute26
1363 ,p_attribute27 => l_c2.attribute27
1364 ,p_attribute28 => l_c2.attribute28
1365 ,p_attribute29 => l_c2.attribute29
1366 ,p_attribute30 => l_c2.attribute30);
1367 --
1368 end if;
1369 --
1370 -- Process the maintenance of the PTU records
1371 -- This is required for OAB BD1/BD2 work and will
1372 -- be required in 11.5 of HR until the new person
1373 -- type model gets fully incorporated.
1374 --
1375 hr_utility.set_location(l_proc,40);
1376 hr_utility.set_location('Cancel? : '||p_initiate_cancellation,07);
1377 -- hr_per_type_usage_internal.maintain_ptu(
1378 -- p_person_id => p_person_id,
1379 -- p_action => l_action,
1380 -- p_period_of_service_id => p_period_of_service_id,
1381 -- p_actual_termination_date => p_actual_termination_date,
1382 -- p_business_group_id => p_business_group_id,
1383 -- p_date_start => p_date_start,
1384 -- p_leaving_reason => p_leaving_reason,
1385 -- p_old_date_start => l_old_date_start,
1386 -- p_old_leaving_reason => l_old_leaving_reason);
1387 hr_utility.set_location('Leaving '||l_proc,60);
1388 --
1389 -- Fix for bug 3889294 starts here.
1390 -- Moved the PTU changes code to procedure hrempter.cancel_termination
1391 -- for reverse termination case.
1392 --
1393 /*
1394 -- start of PTU Changes
1395 if l_action = 'REV_TERM'
1396 then -- Cancel Person Type Usage record
1397 hr_utility.set_location('REV_TERM '||l_proc,65);
1398 if p_leaving_reason = 'R' then
1399 hr_utility.set_location('REV_TERM '||l_proc,67);
1400 hr_per_type_usage_internal.cancel_person_type_usage
1401 (p_effective_date => p_actual_termination_date+1
1402 ,p_person_id => p_person_id
1403 ,p_system_person_type => 'RETIREE');
1404 -- end if; fix Bug 2048953
1405 else
1406 hr_utility.set_location('REV_TERM '||l_proc,68);
1407 hr_per_type_usage_internal.cancel_person_type_usage
1408 (p_effective_date => p_actual_termination_date+1
1409 ,p_person_id => p_person_id
1410 ,p_system_person_type => 'EX_EMP');
1411 end if;
1412 els
1413 */
1414 --
1415 -- Fix for bug 3889294 ends here.
1416 --
1417 if (l_action = 'TERM'
1418 and l_old_actual_termination_date is null
1419 and p_actual_termination_date is not null ) -- Bug 2189611
1420 then -- terminate
1421 hr_utility.set_location('TERM '||l_proc,70);
1422 hr_per_type_usage_internal.maintain_person_type_usage
1423 (p_effective_date => p_actual_termination_date+1
1424 ,p_person_id => p_person_id
1425 ,p_person_type_id =>
1426 hr_person_type_usage_info.get_default_person_type_id
1427 (p_business_group_id => p_business_group_id
1428 ,p_system_person_type => 'EX_EMP')
1429 ,p_datetrack_update_mode => 'UPDATE');
1430
1431 if p_leaving_reason = 'R'
1432 then
1433 hr_per_type_usage_internal.create_person_type_usage
1434 (p_person_id => p_person_id
1435 ,p_person_type_id =>
1436 hr_person_type_usage_info.get_default_person_type_id
1437 (p_business_group_id => p_business_group_id
1438 ,p_system_person_type => 'RETIREE')
1439 ,p_effective_date => p_actual_termination_date+1
1440 ,p_person_type_usage_id => l_person_type_usage_id
1441 ,p_object_version_number=> l_object_version_number
1442 ,p_effective_start_date => l_effective_start_date
1443 ,p_effective_end_date => l_effective_end_date);
1444 end if;
1445 elsif (l_action = 'LEAV_REAS'
1446 and p_actual_termination_date is not null ) -- Bug 2189611
1447 -- then if l_old_leaving_reason 'R' the remove Retiree record for the person
1448 -- else if p_leaving_reason is 'R' the create a Retiree record for the person
1449 then
1450 hr_utility.set_location('LEAV_REAS '||l_proc,75);
1451 if l_old_leaving_reason = 'R' then
1452 hr_utility.set_location('LEAV_REAS '||l_proc,80);
1453 hr_per_type_usage_internal.maintain_person_type_usage
1454 (p_effective_date => p_actual_termination_date+1
1455 ,p_person_id => p_person_id
1456 ,p_person_type_id =>
1457 hr_person_type_usage_info.get_default_person_type_id
1458 (p_business_group_id => p_business_group_id
1459 ,p_system_person_type => 'RETIREE')
1460 ,p_datetrack_delete_mode => 'ZAP');
1461 elsif p_leaving_reason = 'R' then
1462 hr_per_type_usage_internal.create_person_type_usage
1463 (p_person_id => p_person_id
1464 ,p_person_type_id =>
1465 hr_person_type_usage_info.get_default_person_type_id
1466 (p_business_group_id => p_business_group_id
1467 ,p_system_person_type => 'RETIREE')
1468 ,p_effective_date => p_actual_termination_date+1
1469 ,p_person_type_usage_id => l_person_type_usage_id
1470 ,p_object_version_number=> l_object_version_number
1471 ,p_effective_start_date => l_effective_start_date
1472 ,p_effective_end_date => l_effective_end_date);
1473 end if;
1474 end if;
1475
1476 --
1477 end if;
1478 --
1479 end update_term_row;
1480 ------------------------------------------------------------------------------
1481 procedure update_row(p_row_id VARCHAR2
1482 ,p_period_of_service_id NUMBER
1483 ,p_business_group_id NUMBER
1484 ,p_person_id NUMBER
1485 ,p_date_start DATE
1486 ,p_termination_accepted_per_id NUMBER
1487 ,p_accepted_termination_date DATE
1488 ,p_actual_termination_date DATE
1489 ,p_comments VARCHAR2
1490 ,p_final_process_date DATE
1491 ,p_last_standard_process_date DATE
1492 ,p_leaving_reason VARCHAR2
1493 ,p_notified_termination_date DATE
1494 ,p_projected_termination_date DATE
1495 ,p_request_id NUMBER
1496 ,p_program_application_id NUMBER
1497 ,p_program_id NUMBER
1498 ,p_program_update_date DATE
1499 ,p_attribute_category VARCHAR2
1500 ,p_attribute1 VARCHAR2
1501 ,p_attribute2 VARCHAR2
1502 ,p_attribute3 VARCHAR2
1503 ,p_attribute4 VARCHAR2
1504 ,p_attribute5 VARCHAR2
1505 ,p_attribute6 VARCHAR2
1506 ,p_attribute7 VARCHAR2
1507 ,p_attribute8 VARCHAR2
1508 ,p_attribute9 VARCHAR2
1509 ,p_attribute10 VARCHAR2
1510 ,p_attribute11 VARCHAR2
1511 ,p_attribute12 VARCHAR2
1512 ,p_attribute13 VARCHAR2
1513 ,p_attribute14 VARCHAR2
1514 ,p_attribute15 VARCHAR2
1515 ,p_attribute16 VARCHAR2
1516 ,p_attribute17 VARCHAR2
1517 ,p_attribute18 VARCHAR2
1518 ,p_attribute19 VARCHAR2
1519 ,p_attribute20 VARCHAR2
1520 ,p_pds_information_category VARCHAR2
1521 ,p_pds_information1 VARCHAR2
1522 ,p_pds_information2 VARCHAR2
1523 ,p_pds_information3 VARCHAR2
1524 ,p_pds_information4 VARCHAR2
1525 ,p_pds_information5 VARCHAR2
1526 ,p_pds_information6 VARCHAR2
1527 ,p_pds_information7 VARCHAR2
1528 ,p_pds_information8 VARCHAR2
1529 ,p_pds_information9 VARCHAR2
1530 ,p_pds_information10 VARCHAR2
1531 ,p_pds_information11 VARCHAR2
1532 ,p_pds_information12 VARCHAR2
1533 ,p_pds_information13 VARCHAR2
1534 ,p_pds_information14 VARCHAR2
1535 ,p_pds_information15 VARCHAR2
1536 ,p_pds_information16 VARCHAR2
1537 ,p_pds_information17 VARCHAR2
1538 ,p_pds_information18 VARCHAR2
1539 ,p_pds_information19 VARCHAR2
1540 ,p_pds_information20 VARCHAR2
1541 ,p_pds_information21 VARCHAR2
1542 ,p_pds_information22 VARCHAR2
1543 ,p_pds_information23 VARCHAR2
1544 ,p_pds_information24 VARCHAR2
1545 ,p_pds_information25 VARCHAR2
1546 ,p_pds_information26 VARCHAR2
1547 ,p_pds_information27 VARCHAR2
1548 ,p_pds_information28 VARCHAR2
1549 ,p_pds_information29 VARCHAR2
1550 ,p_pds_information30 VARCHAR2
1551 ,p_adjusted_svc_date DATE
1552 ) is
1553 --
1554 --
1555 -- START WWBUG fix for 1390173
1556 --
1557 cursor c1 is
1558 select *
1559 from per_periods_of_service
1560 where rowid = chartorowid(p_row_id);
1561 --
1562 l_c1 c1%rowtype;
1563 --
1564 l_old ben_pps_ler.g_pps_ler_rec;
1565 l_new ben_pps_ler.g_pps_ler_rec;
1566 --
1567 --
1568 -- END WWBUG fix for 1390173
1569 --
1570 begin
1571 --
1572 --
1573 -- START WWBUG fix for 1390173
1574 --
1575 open c1;
1576 fetch c1 into l_c1;
1577 if c1%found then
1578 --
1579 l_old.PERSON_ID := l_c1.person_id;
1580 l_old.BUSINESS_GROUP_ID := l_c1.business_group_id;
1581 l_old.DATE_START := l_c1.date_start;
1582 l_old.ACTUAL_TERMINATION_DATE := l_c1.actual_termination_date;
1583 l_old.LEAVING_REASON := l_c1.leaving_reason;
1584 l_old.ADJUSTED_SVC_DATE := l_c1.adjusted_svc_date;
1585 l_old.ATTRIBUTE1 := l_c1.attribute1;
1586 l_old.ATTRIBUTE2 := l_c1.attribute2;
1587 l_old.ATTRIBUTE3 := l_c1.attribute3;
1588 l_old.ATTRIBUTE4 := l_c1.attribute4;
1589 l_old.ATTRIBUTE5 := l_c1.attribute5;
1590 l_old.final_process_date := l_c1.final_process_date;
1591 l_new.PERSON_ID := p_person_id;
1592 l_new.BUSINESS_GROUP_ID := p_business_group_id;
1593 l_new.DATE_START := p_date_start;
1594 l_new.ACTUAL_TERMINATION_DATE := p_actual_termination_date;
1595 l_new.LEAVING_REASON := p_leaving_reason;
1596 l_new.ADJUSTED_SVC_DATE := p_adjusted_svc_date;
1597 l_new.ATTRIBUTE1 := p_attribute1;
1598 l_new.ATTRIBUTE2 := p_attribute2;
1599 l_new.ATTRIBUTE3 := p_attribute3;
1600 l_new.ATTRIBUTE4 := p_attribute4;
1601 l_new.ATTRIBUTE5 := p_attribute5;
1602 l_new.final_process_date := p_final_process_date;
1603 --
1604 ben_pps_ler.ler_chk(p_old => l_old
1605 ,p_new => l_new
1606 ,p_event => 'UPDATING'
1607 ,p_effective_date => p_date_start);
1608
1609 --
1610 end if;
1611 close c1;
1612 --
1613 --
1614 -- END WWBUG fix for 1390173
1615 --
1616 update per_periods_of_service pps
1617 set pps.period_of_service_id = p_period_of_service_id
1618 ,pps.business_group_id = p_business_group_id
1619 ,pps.person_id = p_person_id
1620 ,pps.date_start = p_date_start
1621 ,pps.termination_accepted_person_id = p_termination_accepted_per_id
1622 ,pps.accepted_termination_date = p_accepted_termination_date
1623 ,pps.actual_termination_date = p_actual_termination_date
1624 ,pps.comments = p_comments
1625 ,pps.final_process_date = p_final_process_date
1626 ,pps.last_standard_process_date = p_last_standard_process_date
1627 ,pps.leaving_reason = p_leaving_reason
1628 ,pps.notified_termination_date = p_notified_termination_date
1629 ,pps.projected_termination_date = p_projected_termination_date
1630 ,pps.request_id = p_request_id
1631 ,pps.program_application_id = p_program_application_id
1632 ,pps.program_id = p_program_id
1633 ,pps.program_update_date = p_program_update_date
1634 ,pps.attribute_category = p_attribute_category
1635 ,pps.attribute1 = p_attribute1
1636 ,pps.attribute2 = p_attribute2
1637 ,pps.attribute3 = p_attribute3
1638 ,pps.attribute4 = p_attribute4
1639 ,pps.attribute5 = p_attribute5
1640 ,pps.attribute6 = p_attribute6
1641 ,pps.attribute7 = p_attribute7
1642 ,pps.attribute8 = p_attribute8
1643 ,pps.attribute9 = p_attribute9
1644 ,pps.attribute10 = p_attribute10
1645 ,pps.attribute11 = p_attribute11
1646 ,pps.attribute12 = p_attribute12
1647 ,pps.attribute13 = p_attribute13
1648 ,pps.attribute14 = p_attribute14
1649 ,pps.attribute15 = p_attribute15
1650 ,pps.attribute16 = p_attribute16
1651 ,pps.attribute17 = p_attribute17
1652 ,pps.attribute18 = p_attribute18
1653 ,pps.attribute19 = p_attribute19
1654 ,pps.attribute20 = p_attribute20
1655 ,pps.pds_information_category = p_pds_information_category
1656 ,pps.pds_information1 = p_pds_information1
1657 ,pps.pds_information2 = p_pds_information2
1658 ,pps.pds_information3 = p_pds_information3
1659 ,pps.pds_information4 = p_pds_information4
1660 ,pps.pds_information5 = p_pds_information5
1661 ,pps.pds_information6 = p_pds_information6
1662 ,pps.pds_information7 = p_pds_information7
1663 ,pps.pds_information8 = p_pds_information8
1664 ,pps.pds_information9 = p_pds_information9
1665 ,pps.pds_information10 = p_pds_information10
1666 ,pps.pds_information11 = p_pds_information11
1667 ,pps.pds_information12 = p_pds_information12
1668 ,pps.pds_information13 = p_pds_information13
1669 ,pps.pds_information14 = p_pds_information14
1670 ,pps.pds_information15 = p_pds_information15
1671 ,pps.pds_information16 = p_pds_information16
1672 ,pps.pds_information17 = p_pds_information17
1673 ,pps.pds_information18 = p_pds_information18
1674 ,pps.pds_information19 = p_pds_information19
1675 ,pps.pds_information20 = p_pds_information20
1676 ,pps.pds_information21 = p_pds_information21
1677 ,pps.pds_information22 = p_pds_information22
1678 ,pps.pds_information23 = p_pds_information23
1679 ,pps.pds_information24 = p_pds_information24
1680 ,pps.pds_information25 = p_pds_information25
1681 ,pps.pds_information26 = p_pds_information26
1682 ,pps.pds_information27 = p_pds_information27
1683 ,pps.pds_information28 = p_pds_information28
1684 ,pps.pds_information29 = p_pds_information29
1685 ,pps.pds_information30 = p_pds_information30
1686 ,pps.adjusted_svc_date = p_adjusted_svc_date
1687 where rowid = chartorowid(p_row_id);
1688 --
1689
1690 ben_dt_trgr_handle.periods_of_service
1691 (p_rowid => null
1692 ,p_person_id => p_person_id
1693 ,p_pds_atd => p_actual_termination_date
1694 ,p_pds_leaving_reason => p_leaving_reason
1695 ,p_pds_fpd => p_final_process_date
1696 -- Bug 1854968
1697 ,p_pds_old_atd => l_old.actual_termination_date
1698 );
1699 --
1700 end update_row;
1701 ----------------------------------------------------------------------------
1702 procedure populate_status(p_person_id NUMBER
1703 ,p_status in out nocopy VARCHAR2
1704 ,p_assignment_status_id in out nocopy number) is
1705 cursor st1 is SELECT NVL(STATL.USER_STATUS,STTTL.USER_STATUS)
1706 , STT.ASSIGNMENT_STATUS_TYPE_ID
1707 FROM PER_ASSIGNMENT_STATUS_TYPES_TL STTTL
1708 , PER_ASSIGNMENT_STATUS_TYPES STT
1709 , PER_ASS_STATUS_TYPE_AMENDS_TL STATL
1710 , PER_ASS_STATUS_TYPE_AMENDS STA
1711 , PER_ALL_ASSIGNMENTS_F ASG
1712 WHERE ASG.PERSON_ID = p_person_id
1713 AND STT.ASSIGNMENT_STATUS_TYPE_ID = STTTL.ASSIGNMENT_STATUS_TYPE_ID
1714 AND STA.ASS_STATUS_TYPE_AMEND_ID =
1715 STATL.ASS_STATUS_TYPE_AMEND_ID(+)
1716 AND STT.ASSIGNMENT_STATUS_TYPE_ID =
1717 STA.ASSIGNMENT_STATUS_TYPE_ID (+)
1718 AND ASG.ASSIGNMENT_STATUS_TYPE_ID = STT.ASSIGNMENT_STATUS_TYPE_ID
1719 AND NVL(STA.ACTIVE_FLAG,STT.ACTIVE_FLAG) = 'Y'
1720 AND NVL(STT.DEFAULT_FLAG,STA.DEFAULT_FLAG) = 'Y'
1721 AND ASG.PRIMARY_FLAG = 'Y'
1722 AND NVL(STA.PER_SYSTEM_STATUS,STT.PER_SYSTEM_STATUS) =
1723 'TERM_ASSIGN'
1724 AND decode(STATL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', STATL.LANGUAGE)
1725 = decode(STATL.ASS_STATUS_TYPE_AMEND_ID, NULL, '1', userenv('LANG'))
1726 AND STTTL.LANGUAGE = USERENV('LANG');
1727 begin
1728 open st1;
1729 fetch st1 into p_status,p_assignment_status_id;
1730 close st1;
1731 end;
1732 ------------------------------------------------------------------------------
1733 procedure form_post_query(p_session_date DATE
1734 ,p_period_of_service_id NUMBER
1735 ,p_business_group_id NUMBER
1736 ,p_person_id NUMBER
1737 ,p_tp_years IN OUT NOCOPY NUMBER
1738 ,p_tp_months IN OUT NOCOPY NUMBER
1739 ,p_total_years IN OUT NOCOPY NUMBER
1740 ,p_total_months IN OUT NOCOPY NUMBER
1741 ,p_actual_termination_date DATE
1742 ,p_status IN OUT NOCOPY VARCHAR2
1743 ,p_termination_accepted_id IN NUMBER
1744 ,p_terminated_name IN OUT NOCOPY VARCHAR2
1745 ,p_terminated_number IN OUT NOCOPY VARCHAR2
1746 ,p_assignment_status_id IN OUT NOCOPY NUMBER) is
1747 --
1748 -- Cursor Terminated Per_id
1749 --
1750 cursor terminated_by is
1751 select p.full_name,nvl(p.employee_number, p.npw_number)
1752 from per_all_people_f p,
1753 per_periods_of_service pps
1754 where p.person_id = pps.termination_accepted_person_id
1755 and (p.business_group_id = p_business_group_id OR
1756 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'Y')
1757 and pps.period_of_service_id = p_period_of_service_id
1758 and nvl(pps.accepted_termination_date,p_session_date)
1759 between p.effective_start_date and p.effective_end_date ;
1760 --
1761 begin
1762 per_periods_of_service_pkg.get_years_months(p_session_date => p_session_date
1763 ,p_period_of_service_id => p_period_of_service_id
1764 ,p_business_group_id => p_business_group_id
1765 ,p_person_id => p_person_id
1766 ,p_tp_years => p_tp_years
1767 ,p_tp_months => p_tp_months
1768 ,p_total_years => p_total_years
1769 ,p_total_months => p_total_months);
1770 if p_actual_termination_date is not null then
1771 per_periods_of_service_pkg.populate_status(p_person_id => p_person_id
1772 ,p_status => p_status
1773 ,p_assignment_status_id => p_assignment_status_id);
1774 end if;
1775 if p_termination_accepted_id is not null then
1776 open terminated_by;
1777 fetch terminated_by into p_terminated_name, p_terminated_number;
1778 close terminated_by;
1779 end if;
1780 end form_post_query;
1781
1782 END PER_PERIODS_OF_SERVICE_PKG;