1 PACKAGE BODY psp_enc_assignment_changes AS
2 /* $Header: PSPENETB.pls 120.11.12020000.2 2012/07/04 09:06:59 amnaraya ship $ */
3
4 /* Commenting the code for Bug 3075435 as this profile option will be Endated Instead call to
5 start_captiring_Updates procedure in psp_general package will be made
6
7 -- use_ld_enc varchar2(1) := FND_PROFILE.VALUE('PSP_ENC_ENABLE_QKUPD');
8
9 End of commenting for Bug 3075435 */
10
11
12
13 bg_id number := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
14
15 use_ld_enc varchar2(1) :=PSP_GENERAL.start_capturing_updates(bg_id);
16 -- For Bug 3075435 call to replace PSP_ENC_ENABLE_QKUPD profile call
17
18 /* Changed Signature of the Procedure added p_effective_date Parameter for bug 3451760 */
19
20 PROCEDURE element_entries_inserts
21 (p_assignment_id IN NUMBER,
22 p_element_link_id IN NUMBER,
23 p_effective_date IN DATE)
24 IS
25 CURSOR element_cur IS
26 SELECT pel.element_type_id
27 FROM pay_element_links_f pel
28 WHERE pel.element_link_id = p_element_link_id
29 AND EXISTS (SELECT pee.element_type_id
30 FROM psp_enc_elements pee
31 WHERE pee.element_type_id = pel.element_type_id
32 AND pee.business_group_id = bg_id)
33 AND ROWNUM = 1;
34
35 CURSOR PAYROLL_ID_CUR IS
36 SELECT PAYROLL_ID
37 FROM per_all_assignments_f
38 WHERE assignment_id = p_assignment_id
39 --AND SYSDATE BETWEEN effective_start_date AND effective_end_date; Commented For Bug 3451760
40 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
41
42 l_element_type_id NUMBER DEFAULT NULL;
43 l_payroll_id NUMBER DEFAULT NULL;
44
45 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table START
46 CURSOR check_enc_run_csr(p_payroll_id NUMBER) IS
47 SELECT 'Y'
48 FROM psp_enc_summary_lines
49 WHERE payroll_id = p_payroll_id
50 AND assignment_id = p_assignment_id
51 AND status_code IN ('A', 'N')
52 AND ROWNUM = 1;
53
54 check_enc_run_flag Varchar2(1);
55 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table END
56
57 BEGIN
58 IF (use_ld_enc = 'Y') THEN
59 OPEN element_cur;
60 FETCH element_cur INTO l_element_type_id;
61 CLOSE element_cur;
62 IF (l_element_type_id IS NOT NULL) THEN
63 OPEN PAYROLL_ID_CUR;
64 FETCH PAYROLL_ID_CUR INTO l_payroll_id;
65 CLOSE PAYROLL_ID_CUR;
66 /* Commented the following code for Bug 3451760 */
67 -- INSERT INTO psp_enc_changed_assignments
68 -- (assignment_id, payroll_id, change_type, processed_flag)
69 -- VALUES (p_assignment_id, l_payroll_id, 'ET', NULL);
70
71 /* Added the the Following code for Bug 3451760 */
72
73
74 IF(l_payroll_id IS NOT NULL) THEN
75 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table
76 OPEN check_enc_run_csr(l_payroll_id);
77 FETCH check_enc_run_csr INTO check_enc_run_flag;
78 CLOSE check_enc_run_csr;
79 IF check_enc_run_flag = 'Y' THEN
80 INSERT INTO psp_enc_changed_assignments
81 (assignment_id, payroll_id, change_type, processed_flag)
82 VALUES (p_assignment_id, l_payroll_id, 'ET', NULL);
83 END IF;
84 END IF;
85
86 END IF;
87 END IF;
88
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 NULL;
92 WHEN OTHERS THEN
93 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- raised this fnd call instead of NULL for Bug 3075435
94 END element_entries_inserts;
95
96
97 /* Changed Signature of the Procedure added p_effective_date Parameter for bug 3451760 */
98 PROCEDURE element_entries_updates
99 (p_assignment_id_o IN NUMBER,
100 p_element_link_id_o IN NUMBER,
101 p_effective_date IN DATE)
102 IS
103 l_element_type_id NUMBER DEFAULT NULL;
104 l_payroll_id NUMBER DEFAULT NULL;
105
106 CURSOR element_cur IS
107 SELECT pel.element_type_id
108 FROM pay_element_links_f pel
109 WHERE pel.element_link_id = p_element_link_id_o
110 AND EXISTS (SELECT pee.element_type_id
111 FROM psp_enc_elements pee
112 WHERE pee.element_type_id = pel.element_type_id
113 AND pee.business_group_id = bg_id )
114 AND ROWNUM = 1;
115
116 CURSOR payroll_id_cur IS
117 SELECT payroll_id
118 FROM per_all_assignments_f
119 WHERE assignment_id = p_assignment_id_o
120 --AND SYSDATE BETWEEN effective_start_date AND effective_end_date; Commented for Bug 3451760
121 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
122
123 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table START
124 CURSOR check_enc_run_csr(p_payroll_id NUMBER) IS
125 SELECT 'Y'
126 FROM psp_enc_summary_lines
127 WHERE payroll_id = p_payroll_id
128 AND assignment_id = p_assignment_id_o
129 AND status_code IN ('A', 'N')
130 AND ROWNUM = 1;
131
132 check_enc_run_flag Varchar2(1);
133 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table END
134
135 BEGIN
136
137 IF (use_ld_enc = 'Y') THEN
138 OPEN element_cur;
139 FETCH element_cur INTO l_element_type_id;
140 CLOSE element_cur;
141
142 IF l_element_type_id IS NOT NULL THEN
143 OPEN PAYROLL_ID_CUR;
144 FETCH PAYROLL_ID_CUR INTO l_payroll_id;
145 CLOSE PAYROLL_ID_CUR;
146
147 /* Commented the following code for Bug 3451760 */
148 -- INSERT INTO psp_enc_changed_assignments
149 -- (assignment_id, payroll_id, change_type, processed_flag)
150 -- VALUES (p_assignment_id_o, l_payroll_id, 'ET', NULL);
151
152 /* Added the the Following code for Bug 3451760 */
153 IF(l_payroll_id IS NOT NULL) THEN
154 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table
155 OPEN check_enc_run_csr(l_payroll_id);
156 FETCH check_enc_run_csr INTO check_enc_run_flag;
157 CLOSE check_enc_run_csr;
158 IF check_enc_run_flag = 'Y' THEN
159 INSERT INTO psp_enc_changed_assignments
160 (assignment_id, payroll_id, change_type, processed_flag)
161 VALUES (p_assignment_id_o, l_payroll_id, 'ET', NULL);
162 END IF;
163 END IF;
164 END IF;
165 END IF;
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168 NULL;
169 WHEN OTHERS THEN
170 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- raised Fnd_api call instead of NULL for bug 3075435
171 END element_Entries_updates;
172
173 PROCEDURE element_entries_deletes
174 (p_assignment_id_o IN NUMBER,
175 p_element_link_id_o IN NUMBER,
176 p_effective_date IN DATE)
177 IS
178
179 l_element_type_id NUMBER DEFAULT NULL;
180 l_payroll_id NUMBER DEFAULT NULL;
181
182 CURSOR element_cur IS
183 SELECT pel.element_type_id
184 FROM pay_element_links_f pel
185 WHERE pel.element_link_id = p_element_link_id_o
186 AND EXISTS (SELECT pee.element_type_id
187 FROM psp_enc_elements pee
188 WHERE pee.element_type_id = pel.element_type_id
189 AND pee.business_group_id = bg_id )
190 AND ROWNUM = 1;
191
192 CURSOR PAYROLL_ID_CUR IS
193 SELECT payroll_id
194 FROM per_all_assignments_f
195 WHERE assignment_id = p_assignment_id_o
196 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
197
198 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table START
199 CURSOR check_enc_run_csr(p_payroll_id NUMBER) IS
200 SELECT 'Y'
201 FROM psp_enc_summary_lines
202 WHERE payroll_id = p_payroll_id
203 AND assignment_id = p_assignment_id_o
204 AND status_code IN ('A', 'N')
205 AND ROWNUM = 1;
206
207 check_enc_run_flag Varchar2(1);
208 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table END
209
210 BEGIN
211 IF (use_ld_enc = 'Y') THEN
212 OPEN element_cur;
213 FETCH element_cur INTO l_element_type_id;
214 CLOSE element_cur;
215 IF l_element_type_id IS NOT NULL THEN
216 OPEN payroll_id_cur;
217 FETCH payroll_id_cur INTO l_payroll_id;
218 CLOSE payroll_id_cur;
219
220 /* Commented the following Code for Bug 3451760 */
221 /* INSERT INTO psp_enc_changed_assignments
222 (assignment_id, payroll_id, change_type, processed_flag)
223 VALUES (p_assignment_id_o, l_payroll_id, 'ET', NULL); */
224
225 /* Added the follwoing Check for Bug 3451760 */
226 IF ( l_payroll_id IS NOT NULL ) THEN
227 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table
228 OPEN check_enc_run_csr(l_payroll_id);
229 FETCH check_enc_run_csr INTO check_enc_run_flag;
230 CLOSE check_enc_run_csr;
231 IF check_enc_run_flag = 'Y' THEN
232 INSERT INTO psp_enc_changed_assignments
233 (assignment_id, payroll_id, change_type, processed_flag)
234 VALUES (p_assignment_id_o, l_payroll_id, 'ET', NULL);
235 END IF;
236 END IF;
237
238 END IF;
239 END IF;
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242 NULL;
243 WHEN OTHERS THEN
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;-- raised fnd call Instead of Null for Bug 3075435
245
246 END element_entries_deletes;
247
248 /***********************************************************************************
249 Function Name :assignment_updates
250 Purpose :Dynamic Trigger Implementation, this function is called from After Row trigger
251 Update dynamic trigger PSP_ASG_CHANGES_ARU.
252 Date Of Creation:23-07-2003
253 Bug:3075435
254 *******************************************************************************/
255
256 PROCEDURE assignment_updates
257 (p_old_payroll_id IN NUMBER,
258 p_new_payroll_id IN NUMBER,
259 p_old_organization_id IN NUMBER,
260 p_new_organization_id IN NUMBER,
261 p_old_asg_status_type_id IN NUMBER,
262 p_new_asg_status_type_id IN NUMBER,
263 p_new_assignment_id IN NUMBER,
264 p_new_period_of_service_id IN NUMBER,
265 p_new_effective_end_date IN DATE,
266 p_new_primary_flag IN VARCHAR2,
267 p_new_person_id IN NUMBER,
268 p_old_grade_id IN NUMBER, -- for bug 4719330
269 p_new_grade_id IN NUMBER) -- for bug 4719330 )
270
271 IS
272
273 p_actual_date DATE;
274
275 l_count integer; --- added 4 vars for 3184075
276
277 --added for bug 5977888
278 l_count1 integer;
279 l_count2 integer;
280
281 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table START
282 /*
283 chk_old_pay_flg varchar2(1) := 'N';
284 chk_new_pay_flg varchar2(1) := 'N';
285
286 cursor chck_payroll_cur(l_payroll_id number) is
287 select 'Y'
288 from psp_enc_payrolls
289 where payroll_id = l_payroll_id ;
290 */
291
292 CURSOR check_enc_run_csr(p_payroll_id NUMBER) IS
293 SELECT 'Y'
294 FROM psp_enc_summary_lines
295 WHERE payroll_id = p_payroll_id
296 AND assignment_id = p_new_assignment_id
297 AND status_code IN ('A', 'N')
298 AND ROWNUM = 1;
299
300 check_old_enc_run_flag Varchar2(1) := 'N';
301 check_new_enc_run_flag Varchar2(1) := 'N';
302 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table END
303
304
305
306
307 Begin
308
309 hr_utility.trace('LD1 Entering assignment_updates PROC');
310
311 hr_utility.trace('LD1 p_old_payroll_id = '||p_old_payroll_id);
312 hr_utility.trace('LD1 p_new_payroll_id = '||p_new_payroll_id);
313
314 hr_utility.trace('LD1 p_old_organization_id = '|| p_old_organization_id);
315 hr_utility.trace('LD1 p_new_organization_id = '|| p_new_organization_id);
316
317 hr_utility.trace('LD1 p_old_asg_status_type_id = '|| p_old_asg_status_type_id);
318 hr_utility.trace ('LD1 p_new_asg_status_type_id = '|| p_new_asg_status_type_id);
319
320 hr_utility.trace ('LD1 p_new_assignment_id = '|| p_new_assignment_id);
321 hr_utility.trace ('LD1 p_new_period_of_service_id = '|| p_new_period_of_service_id);
322 hr_utility.trace ('LD1 p_new_effective_end_date = '|| p_new_effective_end_date);
323 hr_utility.trace ('LD1 p_new_primary_flag = '|| p_new_primary_flag);
324 hr_utility.trace ('LD1 p_new_person_id = '|| p_new_person_id );
325
326 hr_utility.trace ('LD1 p_old_grade_id = '|| p_old_grade_id );
327 hr_utility.trace ('LD1 p_new_grade_id = '|| p_new_grade_id );
328
329 hr_utility.trace ('LD1 use_ld_enc = '|| use_ld_enc);
330
331
332
333 IF ( use_ld_enc = 'Y' ) THEN
334
335 /* introduced the code to check only chages for those payroll selected in Encumbrance payroll
336 form are stored */
337 IF ( p_new_payroll_id is not null ) THEN
338 OPEN check_enc_run_csr(p_new_payroll_id);
339 FETCH check_enc_run_csr INTO check_new_enc_run_flag;
340 CLOSE check_enc_run_csr;
341 END IF;
342
343 IF ( p_old_payroll_id is not null) THEN
344 OPEN check_enc_run_csr(p_old_payroll_id);
345 FETCH check_enc_run_csr INTO check_old_enc_run_flag;
346 CLOSE check_enc_run_csr;
347 END IF ;
348
349
350
351
352 /* Check if Old and new payroll_id's are NULL, and there is no change in Organization or
353 assignment_status,theb do not insert. If old and new payroll values are different or if the
354 organization or assignment_status is different insert a record. Any update of a date tracked
355 record in per_all_assignments_f results in both in an INSERT as well as UPDATE operation */
356
357 /* Commented the following code and Break the If condition into if elsif conditions
358
359 IF (NVL(p_old_payroll_id,0) <> NVL(p_new_payroll_id,0) OR
360 p_old_organization_id <> p_new_organization_id OR
361 p_old_asg_status_type_id <> p_new_asg_status_type_id )
362 THEN
363
364 End of commenting for Bug 3466753 */
365
366 IF (NVL(p_old_payroll_id,0) <> NVL(p_new_payroll_id,0)) THEN
367
368 IF (check_new_enc_run_flag = 'Y') THEN
369 INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
370 VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL);
371 END IF;
372
373 IF (check_old_enc_run_flag = 'Y') then
374 INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
375 VALUES (p_new_assignment_id,p_old_payroll_id,'AS',NULL);
376 END IF;
377
378 -- Else if for p_old_payroll_id <> p_new_payroll_id
379
380 /************** Added for bug -- for bug 4719330 *****************/
381 ELSIF (NVL(p_old_grade_id,0) <> NVL(p_new_grade_id,0)) THEN
382 IF (check_new_enc_run_flag = 'Y') then
383
384 INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
385 VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL);
386
387 ElSIF (check_old_enc_run_flag = 'Y') then
388
389 INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
390 VALUES (p_new_assignment_id,p_old_payroll_id,'AS',NULL);
391
392 END if;
393
394 /************** End of Addition for bug -- for bug 4719330 *****************/
395
396 ELSIF (p_old_organization_id <> p_new_organization_id OR
397 p_old_asg_status_type_id <> p_new_asg_status_type_id )
398 THEN
399
400 ---- added following "IF-END IF" for 3184075
401 IF p_old_asg_status_type_id <> p_new_asg_status_type_id and
402 p_new_primary_flag = 'Y'
403
404 THEN
405
406 select count(*)
407 into l_count
408 from per_assignment_status_types
409 where p_new_asg_status_type_id = assignment_status_type_id
410 and per_system_status = 'TERM_ASSIGN';
411
412 IF l_count = 1
413
414 THEN
415
416 select count(*)
417 into l_count1
418 from psp_enc_summary_lines
419 where status_code = 'A'
420 and person_id = p_new_person_id
421 and award_id is not null
422 and effective_date > p_new_effective_end_date; --- added date check for 3413373
423
424
425 hr_utility.trace ('LD2 STATUS : A l_count1 = '||l_count1);
426
427
428 select count(*)
429 into l_count2
430 from psp_enc_summary_lines
431 where status_code = 'N'
432 and person_id = p_new_person_id
433 and award_id is not null
434 and effective_date > p_new_effective_end_date; --bug 5977888
435
436 hr_utility.trace ('LD2 STATUS : N l_count2 = '||l_count2);
437
438 IF l_count2 > 0
439
440 THEN
441
442 hr_utility.set_message(8403,'PSP_ENC_EMP_DELETE');
443 hr_utility.raise_error;
444
445 END IF;
446
447 IF l_count1 > 0
448
449 THEN
450
451 hr_utility.set_message(8403,'PSP_ENC_LIQ_BEFORE_TERM');
452 hr_utility.raise_error;
453
454 END IF;
455
456 END IF;
457
458 END IF;
459 /* commented the following code as the insert did nit have a check
460 whether payroll id is null for Bug 3432995 */
461 -- INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
462 -- VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL);
463
464 /* Introduced the following code for Bug 3432995 */
465
466 IF (check_new_enc_run_flag = 'Y') then
467
468 INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
469 VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL);
470
471 ElSIF (check_old_enc_run_flag = 'Y') then
472
473 INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
474 VALUES (p_new_assignment_id,p_old_payroll_id,'AS',NULL);
475
476 END if;
477
478 ELSE
479
480 BEGIN
481
482 SELECT actual_termination_date into p_actual_date
483 FROM PER_PERIODS_OF_SERVICE
484 WHERE person_id =p_new_person_id --- replaced for 3184075 to resolve ORA-4091
485 /* (SELECT paf.person_id
486 FROM per_assignments_f paf
487 WHERE paf.assignment_id = p_new_assignment_id) -- Introduced for bug fix 3263333 */
488 AND period_of_service_id = p_new_period_of_service_id ----replaced p_new_asg_status_type_id and also replaced <> with =
489 AND p_new_effective_end_date = actual_termination_date;
490
491 hr_utility.trace ('LD3 p_actual_date = '||p_actual_date);
492
493 IF (p_actual_date IS NOT NULL ) THEN
494
495 select count(*)
496 into l_count1
497 from psp_enc_summary_lines
498 where status_code = 'A'
499 and person_id = p_new_person_id
500 and award_id is not null
501 and effective_date > p_new_effective_end_date; --- added date check for 3413373
502
503
504 hr_utility.trace ('LD3 STATUS : A l_count1 = '||l_count1);
505
506 select count(*)
507 into l_count2
508 from psp_enc_summary_lines
509 where status_code = 'N'
510 and person_id = p_new_person_id
511 and award_id is not null
512 and effective_date > p_new_effective_end_date; --bug 5977888
513
514 hr_utility.trace ('LD3 STATUS : N l_count2 = '||l_count2);
515
516
517 IF l_count2 > 0
518
519 THEN
520
521 hr_utility.set_message(8403,'PSP_ENC_EMP_DELETE');
522 hr_utility.raise_error;
523
524 END IF;
525
526
527 IF l_count1 > 0
528
529 THEN
530
531 hr_utility.set_message(8403,'PSP_ENC_LIQ_BEFORE_TERM');
532 hr_utility.raise_error;
533
534 END IF;
535
536
537
538 /* Commenetd the following code for Bug 3432995 */
539 /* INSERT INTO psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
540 VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL); */
541
542 /* Introduced the following code for Bug 3432995 */
543 IF ( check_new_enc_run_flag = 'Y') THEN
544
545 INSERT INTO psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
546 VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL);
547
548 ElSIF (check_old_enc_run_flag = 'Y') then
549
550 INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
551 VALUES (p_new_assignment_id,p_old_payroll_id,'AS',NULL);
552
553 END IF;
554
555
556
557 END IF;
558
559 EXCEPTION
560 WHEN NO_DATA_FOUND THEN
561 NULL;
562 END;
563
564 -- Added this code to track the status change to END for Bug 4203036
565 IF ( check_new_enc_run_flag = 'Y') THEN
566 INSERT INTO psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag,chk_asg_end_date_flag)
567 VALUES (p_new_assignment_id,p_new_payroll_id,'AS',NULL,'Y');
568 ElSIF (check_old_enc_run_flag = 'Y') then
569 INSERT INTO psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag,chk_asg_end_date_flag)
570 VALUES (p_new_assignment_id,p_old_payroll_id,'AS',NULL,'Y');
571 end if ;
572
573
574 END IF; -- end if for payroll_id,assignment_id,organization_id check
575
576 END IF; -- end if for Use_ld_enc check
577
578 End assignment_updates;
579
580 /***********************************************************************************
581 Function Name :assignment_deletes
582 Purpose :Dynamic Trigger Implementation, this function is called from After Row
583 Delete dynamic trigger PSP_ASG_CHANGES_ARD.
584 Date Of Creation:23-07-2003
585 BUg: 3075435
586 *******************************************************************************/
587
588 PROCEDURE assignment_deletes
589 (p_new_assignment_id IN NUMBER,
590 p_old_assignment_id IN NUMBER,
591 p_old_payroll_id IN NUMBER,
592 p_old_effective_start_date IN DATE,
593 p_old_person_id IN NUMBER,
594 p_old_effective_end_date IN DATE --Bug 12969737
595 )
596
597 IS
598 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table START
599 CURSOR check_enc_run_csr(p_payroll_id NUMBER) IS
600 SELECT 'Y'
601 FROM psp_enc_summary_lines
602 WHERE payroll_id = p_payroll_id
603 AND assignment_id = p_old_assignment_id
604 AND status_code IN ('A', 'N')
605 AND ROWNUM = 1;
606
607 check_enc_run_flag Varchar2(1);
608 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table END
609
610 -- Bug 5526742
611
612 CURSOR chk_asg_count is
613 SELECT count(CURRENT_EMPLOYEE_FLAG)
614 FROM per_all_people_f where person_id = p_old_person_id
615 AND current_employee_flag = 'Y'
616 and effective_start_date = (select max(effective_start_date)
617 FROM per_all_people_f
618 where effective_start_date < p_old_effective_start_date
619 and person_id = p_old_person_id);
620
621 l_count number;
622 l_count1 number;
623 l_count2 number;
624
625 -- End of chages for bug 5526742
626
627
628
629 --Bug 12969737 Begin
630
631 cursor cancel_hire_asg_csr(p_person_id number) is
632 select min(papf.EFFECTIVE_START_DATE),max(papf.EFFECTIVE_END_DATE)
633 from per_all_people_f papf, per_person_types ppt
634 where papf.person_id = p_person_id
635 and papf.PERSON_TYPE_ID = ppt.PERSON_TYPE_ID
636 and ppt.SYSTEM_PERSON_TYPE = 'EMP';
637
638 l_eff_start_date date;
639 l_eff_end_Date date;
640
641 --Bug 12969737 End
642
643 BEGIN
644 /* Insert the old value of assignment_id and payroll_id. Multiple records would be inserted here
645 if multiple date tracked records existed before. */
646
647
648
649 hr_utility.trace('LD10 Entering assignment_deletes PROC');
650 hr_utility.trace('LD10 p_old_payroll_id = '||p_old_payroll_id);
651
652 hr_utility.trace ('LD10 p_new_assignment_id = '|| p_new_assignment_id);
653 hr_utility.trace ('LD10 p_old_assignment_id = '|| p_old_assignment_id);
654
655 hr_utility.trace ('LD10 p_old_effective_start_date = '|| p_old_effective_start_date);
656 hr_utility.trace ('LD10 p_old_person_id = '|| p_old_person_id );
657
658 hr_utility.trace ('LD10 use_ld_enc = '|| use_ld_enc);
659
660
661
662
663 IF(use_ld_enc = 'Y') THEN
664 --Bug 3432995 Included the And condition to check whether the p_old_payroll_id arameter is Not Null.
665 IF (p_new_assignment_id IS NULL AND p_old_payroll_id IS NOT NULL) THEN
666
667 -- introduced the following to check the existence of any unliquidated
668 -- encumbrance before cance-hire an applicant or deleting an employee
669 -- with no payroll runs, and encumbrance being run.
670
671 open chk_asg_count;
672 fetch chk_asg_count into l_count ;
673 close chk_asg_count;
674
675 hr_utility.trace ('LD10 l_count = '|| l_count );
676
677 IF l_count =0 THEN
678
679 -- Bug 12969737
680 -- In case of cancel hire there will not be multiple asg records but only one which
681 -- will have effective dates same as person record with person_type 'EMP'.
682
683 open cancel_hire_asg_csr(p_old_person_id);
684 fetch cancel_hire_asg_csr into l_eff_start_Date, l_eff_end_date;
685 close cancel_hire_asg_csr;
686
687 if(trunc(l_eff_start_Date)=trunc(p_old_effective_start_date)
688 and trunc(l_eff_end_date)=trunc(p_old_effective_end_date))
689 then
690
691 select count(*)
692 into l_count1
693 from psp_enc_summary_lines
694 where status_code = 'A'
695 and person_id = p_old_person_id
696 and award_id is not null
697 and effective_date > p_old_effective_start_date;
698
699 hr_utility.trace ('LD10 l_count1 = '|| l_count1);
700
701 IF l_count1 > 0 THEN
702
703 hr_utility.set_message(8403,'PSP_ENC_LIQ_BEFORE_DELETE');
704 hr_utility.raise_error;
705 END IF;
706
707 select count(*)
708 into l_count2
709 from psp_enc_summary_lines
710 where status_code = 'N'
711 AND person_id = p_old_person_id
712 and award_id is not null
713 and effective_date > p_old_effective_start_date;
714
715 hr_utility.trace ('LD10 l_count2 = '|| l_count2 );
716
717 IF l_count2 > 0 THEN
718 hr_utility.set_message(8403,'PSP_ENC_EMP_DELETE');
719 hr_utility.raise_error;
720 END IF;
721
722 End if; --Bug 12969737
723
724 END IF ;
725
726 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table
727 OPEN check_enc_run_csr(p_old_payroll_id);
728 FETCH check_enc_run_csr INTO check_enc_run_flag;
729 CLOSE check_enc_run_csr;
730 IF check_enc_run_flag = 'Y' THEN
731 INSERT INTO psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
732 VALUES(p_old_assignment_id,p_old_payroll_id,'AS',NULL);
733 END IF;
734 END IF;
735 END IF;
736
737 END assignment_deletes;
738
739 /* Introduced the following for bug 4719330 */
740
741 Procedure Asig_grade_point_update
742 (p_assignment_id IN NUMBER,
743 p_new_effective_start_date IN DATE,
744 p_new_effective_end_date IN DATE ,
745 p_old_effective_end_date IN DATE)
746 IS
747
748 Cursor get_asg_payroll IS
749 select payroll_id
750 from per_all_assignments_f
751 where assignment_id = p_assignment_id
752 and effective_end_date >= p_new_effective_start_date
753 and effective_start_date <= p_old_effective_end_date ;
754
755 CURSOR check_enc_run_csr(p_payroll_id NUMBER) IS
756 SELECT 'Y'
757 FROM psp_enc_summary_lines
758 WHERE payroll_id = p_payroll_id
759 AND assignment_id = p_assignment_id
760 AND status_code IN ('A', 'N')
761 AND ROWNUM = 1;
762
763 check_new_enc_run_flag Varchar2(1) := 'N';
764
765 l_payroll_id NUMBER;
766
767 BEGIN
768
769 IF ( use_ld_enc = 'Y' ) THEN
770
771
772 IF (NVL(p_old_effective_end_date,to_date('31/12/4712','DD/MM/RRRR'))
773 <> NVL(p_new_effective_end_date,to_date('31/12/4712','DD/MM/RRRR'))) then
774
775 FOR pay_rec in get_asg_payroll
776 loop
777 OPEN check_enc_run_csr(pay_rec.payroll_id);
778 FETCH check_enc_run_csr INTO check_new_enc_run_flag;
779 CLOSE check_enc_run_csr;
780
781 IF (check_new_enc_run_flag = 'Y') THEN
782 INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
783 VALUES (p_assignment_id,pay_rec.payroll_id,'AS',NULL);
784 END IF;
785
786
787 END LOOP;
788
789
790 END if ; -- end of p_old_placement_id , p_new_placement_id check
791
792 END IF; -- end if for Use_ld_enc check
793
794 END Asig_grade_point_update ;
795
796
797
798 END psp_enc_assignment_changes;