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