1 PACKAGE BODY psp_enc_assignment_changes AS
2 /* $Header: PSPENETB.pls 120.5.12010000.7 2008/08/05 10:10:52 ubhat 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
595 IS
596 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table START
597 CURSOR check_enc_run_csr(p_payroll_id NUMBER) IS
598 SELECT 'Y'
599 FROM psp_enc_summary_lines
600 WHERE payroll_id = p_payroll_id
601 AND assignment_id = p_old_assignment_id
602 AND status_code IN ('A', 'N')
603 AND ROWNUM = 1;
604
605 check_enc_run_flag Varchar2(1);
606 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table END
607
608 -- Bug 5526742
609
610 CURSOR chk_asg_count is
611 SELECT count(CURRENT_EMPLOYEE_FLAG)
612 FROM per_all_people_f where person_id = p_old_person_id
613 AND current_employee_flag = 'Y'
614 and effective_start_date = (select max(effective_start_date)
615 FROM per_all_people_f
616 where effective_start_date < p_old_effective_start_date
617 and person_id = p_old_person_id);
618
619 l_count number;
620 l_count1 number;
621 l_count2 number;
622
623 -- End of chages for bug 5526742
624
625 BEGIN
626 /* Insert the old value of assignment_id and payroll_id. Multiple records would be inserted here
627 if multiple date tracked records existed before. */
628
629
630
631 hr_utility.trace('LD10 Entering assignment_deletes PROC');
632 hr_utility.trace('LD10 p_old_payroll_id = '||p_old_payroll_id);
633
634 hr_utility.trace ('LD10 p_new_assignment_id = '|| p_new_assignment_id);
635 hr_utility.trace ('LD10 p_old_assignment_id = '|| p_old_assignment_id);
636
637 hr_utility.trace ('LD10 p_old_effective_start_date = '|| p_old_effective_start_date);
638 hr_utility.trace ('LD10 p_old_person_id = '|| p_old_person_id );
639
640 hr_utility.trace ('LD10 use_ld_enc = '|| use_ld_enc);
641
642
643
644
645 IF(use_ld_enc = 'Y') THEN
646 --Bug 3432995 Included the And condition to check whether the p_old_payroll_id arameter is Not Null.
647 IF (p_new_assignment_id IS NULL AND p_old_payroll_id IS NOT NULL) THEN
648
649 -- introduced the following to check the existence of any unliquidated
650 -- encumbrance before cance-hire an applicant or deleting an employee
651 -- with no payroll runs, and encumbrance being run.
652
653 open chk_asg_count;
654 fetch chk_asg_count into l_count ;
655 close chk_asg_count;
656
657 hr_utility.trace ('LD10 l_count = '|| l_count );
658
659 IF l_count =0 THEN
660
661 select count(*)
662 into l_count1
663 from psp_enc_summary_lines
664 where status_code = 'A'
665 and person_id = p_old_person_id
666 and award_id is not null
667 and effective_date > p_old_effective_start_date;
668
669 hr_utility.trace ('LD10 l_count1 = '|| l_count1);
670
671 IF l_count1 > 0 THEN
672
673 hr_utility.set_message(8403,'PSP_ENC_LIQ_BEFORE_DELETE');
674 hr_utility.raise_error;
675 END IF;
676
677 select count(*)
678 into l_count2
679 from psp_enc_summary_lines
680 where status_code = 'N'
681 AND person_id = p_old_person_id
682 and award_id is not null
683 and effective_date > p_old_effective_start_date;
684
685 hr_utility.trace ('LD10 l_count2 = '|| l_count2 );
686
687 IF l_count2 > 0 THEN
688 hr_utility.set_message(8403,'PSP_ENC_EMP_DELETE');
689 hr_utility.raise_error;
690 END IF;
691
692
693 END IF ;
694
695 -- Bug 4072324: Changes to avoid Extra Loging in psp_enc_changed_assignments Table
696 OPEN check_enc_run_csr(p_old_payroll_id);
697 FETCH check_enc_run_csr INTO check_enc_run_flag;
698 CLOSE check_enc_run_csr;
699 IF check_enc_run_flag = 'Y' THEN
700 INSERT INTO psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
701 VALUES(p_old_assignment_id,p_old_payroll_id,'AS',NULL);
702 END IF;
703 END IF;
704 END IF;
705
706 END assignment_deletes;
707
708 /* Introduced the following for bug 4719330 */
709
710 Procedure Asig_grade_point_update
711 (p_assignment_id IN NUMBER,
712 p_new_effective_start_date IN DATE,
713 p_new_effective_end_date IN DATE ,
714 p_old_effective_end_date IN DATE)
715 IS
716
717 Cursor get_asg_payroll IS
718 select payroll_id
719 from per_all_assignments_f
720 where assignment_id = p_assignment_id
721 and effective_end_date >= p_new_effective_start_date
722 and effective_start_date <= p_old_effective_end_date ;
723
724 CURSOR check_enc_run_csr(p_payroll_id NUMBER) IS
725 SELECT 'Y'
726 FROM psp_enc_summary_lines
727 WHERE payroll_id = p_payroll_id
728 AND assignment_id = p_assignment_id
729 AND status_code IN ('A', 'N')
730 AND ROWNUM = 1;
731
732 check_new_enc_run_flag Varchar2(1) := 'N';
733
734 l_payroll_id NUMBER;
735
736 BEGIN
737
738 IF ( use_ld_enc = 'Y' ) THEN
739
740
741 IF (NVL(p_old_effective_end_date,to_date('31/12/4712','DD/MM/RRRR'))
742 <> NVL(p_new_effective_end_date,to_date('31/12/4712','DD/MM/RRRR'))) then
743
744 FOR pay_rec in get_asg_payroll
745 loop
746 OPEN check_enc_run_csr(pay_rec.payroll_id);
747 FETCH check_enc_run_csr INTO check_new_enc_run_flag;
748 CLOSE check_enc_run_csr;
749
750 IF (check_new_enc_run_flag = 'Y') THEN
751 INSERT into psp_enc_changed_assignments(assignment_id,payroll_id,change_type,processed_flag)
752 VALUES (p_assignment_id,pay_rec.payroll_id,'AS',NULL);
753 END IF;
754
755
756 END LOOP;
757
758
759 END if ; -- end of p_old_placement_id , p_new_placement_id check
760
761 END IF; -- end if for Use_ld_enc check
762
763 END Asig_grade_point_update ;
764
765
766
767 END psp_enc_assignment_changes;
[Home] [Help]