[Home] [Help]
5: --
6: g_package varchar2(33) := ' hr_assignment_internal.';
7: --
8: -- Start of 3335915
9: g_debug boolean := hr_utility.debug_enabled;
10: -- End of 3335915
11: -- ----------------------------------------------------------------------------
12: -- |---------------------< get_max_asg_fut_change_end_dt >--------------------|
13: -- ----------------------------------------------------------------------------
53: where asg.assignment_id = p_assignment_id
54: and asg.effective_start_date > p_effective_date;
55: --
56: begin
57: hr_utility.set_location('Entering:'|| l_proc, 1);
58: --
59: -- This function returns the maximum effective end date of any changes to
60: -- the specified assignment which start after the specified effective date.
61: -- If no future changes are found, then a NULL is returned.
64: fetch csr_get_max_asg_end_date
65: into l_max_asg_end_date;
66: close csr_get_max_asg_end_date;
67: --
68: hr_utility.set_location(l_proc, 10);
69: --
70: return l_max_asg_end_date;
71: --
72: hr_utility.set_location(' Leaving:'||l_proc, 200);
68: hr_utility.set_location(l_proc, 10);
69: --
70: return l_max_asg_end_date;
71: --
72: hr_utility.set_location(' Leaving:'||l_proc, 200);
73: end get_max_asg_fut_change_end_dt;
74: --
75: -- ----------------------------------------------------------------------------
76: -- |------------------------< actual_term_cwk_asg >---------------------------|
214: from per_pay_proposal_components ppc
215: where ppc.pay_proposal_id = l_proposal_id;
216: -- End of 3202260
217: begin
218: hr_utility.set_location('Entering:'|| l_proc, 1);
219: --
220: -- Process Logic
221: --
222: -- Determine the datetrack mode to use for the assignment table handler
229: ,p_update => l_update
230: ,p_update_override => l_update_override
231: ,p_update_change_insert => l_update_change_insert
232: );
233: hr_utility.set_location(l_proc, 10);
234: --
235: if l_update_change_insert then
236: --
237: -- This is the case where there is a future dated assignment and
242: l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
243: l_asg_future_changes_warning := TRUE;
244: l_object_version_number := p_object_version_number;
245: l_future_records_flag := TRUE;
246: hr_utility.set_location(l_proc, 20);
247: elsif l_update then
248: --
249: l_datetrack_mode := 'UPDATE';
250: l_object_version_number := p_object_version_number;
247: elsif l_update then
248: --
249: l_datetrack_mode := 'UPDATE';
250: l_object_version_number := p_object_version_number;
251: hr_utility.set_location(l_proc, 30);
252: --
253: elsif l_correction then
254: --
255: hr_utility.set_location(l_proc, 40);
251: hr_utility.set_location(l_proc, 30);
252: --
253: elsif l_correction then
254: --
255: hr_utility.set_location(l_proc, 40);
256: --
257: -- We have the OVN for the assignment record which starts on ATD+1
258: -- so lock this one for termination.
259: --
270: ,p_object_version_number => l_object_version_number
271: ,p_validation_start_date => l_cor_validation_start_date
272: ,p_validation_end_date => l_cor_validation_end_date
273: );
274: hr_utility.set_location(l_proc, 50);
275: --
276: -- Find out if there changes after, the day
277: -- after the actual termination date
278: --
279: l_max_asg_end_date := get_max_asg_fut_change_end_dt
280: (p_assignment_id => p_assignment_id
281: ,p_effective_date => p_actual_termination_date + 1
282: );
283: hr_utility.set_location(l_proc, 70);
284: --
285: if l_max_asg_end_date is not null then
286: --
287: l_future_records_flag := TRUE;
284: --
285: if l_max_asg_end_date is not null then
286: --
287: l_future_records_flag := TRUE;
288: hr_utility.set_location(l_proc, 80);
289: end if;
290: --
291: hr_utility.set_location(l_proc, 110);
292: --
287: l_future_records_flag := TRUE;
288: hr_utility.set_location(l_proc, 80);
289: end if;
290: --
291: hr_utility.set_location(l_proc, 110);
292: --
293: l_datetrack_mode := 'CORRECTION';
294: l_asg_future_changes_warning := TRUE;
295: -- For correction the object_version_number has already been derived.
292: --
293: l_datetrack_mode := 'CORRECTION';
294: l_asg_future_changes_warning := TRUE;
295: -- For correction the object_version_number has already been derived.
296: hr_utility.set_location(l_proc, 120);
297: else
298: --
299: -- No other datetrack modes are valid, and so should not occur.
300: --
297: else
298: --
299: -- No other datetrack modes are valid, and so should not occur.
300: --
301: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
302: hr_utility.set_message_token('PROCEDURE', l_proc);
303: hr_utility.set_message_token('STEP','130');
304: hr_utility.raise_error;
305: end if;
298: --
299: -- No other datetrack modes are valid, and so should not occur.
300: --
301: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
302: hr_utility.set_message_token('PROCEDURE', l_proc);
303: hr_utility.set_message_token('STEP','130');
304: hr_utility.raise_error;
305: end if;
306: hr_utility.set_location(l_proc, 140);
299: -- No other datetrack modes are valid, and so should not occur.
300: --
301: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
302: hr_utility.set_message_token('PROCEDURE', l_proc);
303: hr_utility.set_message_token('STEP','130');
304: hr_utility.raise_error;
305: end if;
306: hr_utility.set_location(l_proc, 140);
307: --
300: --
301: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
302: hr_utility.set_message_token('PROCEDURE', l_proc);
303: hr_utility.set_message_token('STEP','130');
304: hr_utility.raise_error;
305: end if;
306: hr_utility.set_location(l_proc, 140);
307: --
308: -- Update employee assignment.
302: hr_utility.set_message_token('PROCEDURE', l_proc);
303: hr_utility.set_message_token('STEP','130');
304: hr_utility.raise_error;
305: end if;
306: hr_utility.set_location(l_proc, 140);
307: --
308: -- Update employee assignment.
309: --
310: per_asg_upd.upd
325: ,p_datetrack_mode => l_datetrack_mode
326: ,p_validate => FALSE
327: ,p_hourly_salaried_warning => l_hourly_salaried_warning
328: );
329: hr_utility.set_location(l_proc, 150);
330: --
331: -- If there are future dated records then we need to process these and set the
332: -- right assignment status.
333: --
363: -- Lock the appropriate child rows for this assignment.
364: --
365: open csr_lock_alu; -- Locking ladder processing order 1110
366: close csr_lock_alu;
367: hr_utility.set_location(l_proc, 160);
368: --
369: open csr_lock_asa; -- Locking ladder processing order 1190
370: close csr_lock_asa;
371: hr_utility.set_location(l_proc,170);
367: hr_utility.set_location(l_proc, 160);
368: --
369: open csr_lock_asa; -- Locking ladder processing order 1190
370: close csr_lock_asa;
371: hr_utility.set_location(l_proc,170);
372: --
373:
374: -- fix for bug 5841180 starts here
375:
374: -- fix for bug 5841180 starts here
375:
376: /* open csr_lock_ele; -- Locking ladder processing order 1440
377: close csr_lock_ele;
378: hr_utility.set_location(l_proc, 180);
379: --
380: open csr_lock_eev; -- Locking ladder processing order 1450
381: close csr_lock_eev;
382: hr_utility.set_location(l_proc, 190); */
378: hr_utility.set_location(l_proc, 180);
379: --
380: open csr_lock_eev; -- Locking ladder processing order 1450
381: close csr_lock_eev;
382: hr_utility.set_location(l_proc, 190); */
383: --
384:
385: open csr_lock_ele(p_actual_termination_date); -- Locking ladder processing order 1440
386: close csr_lock_ele;
383: --
384:
385: open csr_lock_ele(p_actual_termination_date); -- Locking ladder processing order 1440
386: close csr_lock_ele;
387: hr_utility.set_location(l_proc, 180);
388: --
389: open csr_lock_eev(p_actual_termination_date); -- Locking ladder processing order 1450
390: close csr_lock_eev;
391: hr_utility.set_location(l_proc, 190);
387: hr_utility.set_location(l_proc, 180);
388: --
389: open csr_lock_eev(p_actual_termination_date); -- Locking ladder processing order 1450
390: close csr_lock_eev;
391: hr_utility.set_location(l_proc, 190);
392: --
393:
394: -- fix for bug 5841180 ends here
395:
394: -- fix for bug 5841180 ends here
395:
396: open csr_lock_pyp; -- Locking ladder processing order 1630
397: close csr_lock_pyp;
398: hr_utility.set_location(l_proc, 200);
399: --
400: -- Process any element entries and assignment_link_usages for this
401: -- assignment.
402: -- N.B. The procedure hrempter.terminate_entries_and_alus was procduced for
407: fetch csr_get_legislation_code into l_legislation_code;
408: if csr_get_legislation_code%NOTFOUND then
409: close csr_get_legislation_code;
410: -- This should never happen
411: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
412: hr_utility.set_message_token('PROCEDURE', l_proc);
413: hr_utility.set_message_token('STEP','210');
414: hr_utility.raise_error;
415: end if;
408: if csr_get_legislation_code%NOTFOUND then
409: close csr_get_legislation_code;
410: -- This should never happen
411: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
412: hr_utility.set_message_token('PROCEDURE', l_proc);
413: hr_utility.set_message_token('STEP','210');
414: hr_utility.raise_error;
415: end if;
416: close csr_get_legislation_code;
409: close csr_get_legislation_code;
410: -- This should never happen
411: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
412: hr_utility.set_message_token('PROCEDURE', l_proc);
413: hr_utility.set_message_token('STEP','210');
414: hr_utility.raise_error;
415: end if;
416: close csr_get_legislation_code;
417: hr_utility.set_location(l_proc, 220);
410: -- This should never happen
411: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
412: hr_utility.set_message_token('PROCEDURE', l_proc);
413: hr_utility.set_message_token('STEP','210');
414: hr_utility.raise_error;
415: end if;
416: close csr_get_legislation_code;
417: hr_utility.set_location(l_proc, 220);
418: --
413: hr_utility.set_message_token('STEP','210');
414: hr_utility.raise_error;
415: end if;
416: close csr_get_legislation_code;
417: hr_utility.set_location(l_proc, 220);
418: --
419: -- VT 10/07/96 bug #306710 added parameter in a call list
420: hrempter.terminate_entries_and_alus
421: (p_assignment_id => p_assignment_id
425: ,p_legislation_code => l_legislation_code
426: ,p_entries_changed_warning => l_entries_changed_warning
427: );
428: --
429: hr_utility.set_location(l_proc, 230);
430: --
431: -- Delete any pay proposals for this assignment that occur after the
432: -- actual termination date.
433: --
435: --
436: -- Start of fix 3202260
437: for rec_pay_prop in csr_pay_proposal loop
438: --
439: hr_utility.set_location(l_proc, 231);
440: --
441: for rec_prop_comp in csr_proposal_comp(rec_pay_prop.pay_proposal_id) loop
442: -- Calling the per_pay_proposal_components row handler to delete the
443: -- proposal components
441: for rec_prop_comp in csr_proposal_comp(rec_pay_prop.pay_proposal_id) loop
442: -- Calling the per_pay_proposal_components row handler to delete the
443: -- proposal components
444: --
445: hr_utility.set_location(l_proc, 232);
446: --
447: per_ppc_del.del(p_component_id => rec_prop_comp.component_id,
448: p_object_version_number => rec_prop_comp.object_version_number,
449: p_validation_strength => 'WEAK');
447: per_ppc_del.del(p_component_id => rec_prop_comp.component_id,
448: p_object_version_number => rec_prop_comp.object_version_number,
449: p_validation_strength => 'WEAK');
450: --
451: hr_utility.set_location(l_proc, 233);
452: --
453: end loop;
454: --
455: -- Now deleting the salary proposal
453: end loop;
454: --
455: -- Now deleting the salary proposal
456: --
457: hr_utility.set_location(l_proc, 234);
458: --
459: delete
460: from per_pay_proposals pyp
461: where pyp.pay_proposal_id = rec_pay_prop.pay_proposal_id;
467: l_pay_proposal_warning := TRUE;
468: end if;
469: --
470: --
471: hr_utility.set_location(l_proc, 235);
472: --
473: end loop;
474: -- End of fix 3202260
475: --
472: --
473: end loop;
474: -- End of fix 3202260
475: --
476: hr_utility.set_location(l_proc, 240);
477: --
478: if l_datetrack_mode = 'CORRECTION' then
479: --
480: -- Leave p_object_version_number set to its existing value, as it will
483: -- which were returned when the assignment row was locked.
484: --
485: p_effective_start_date := l_cor_validation_start_date;
486: p_effective_end_date := l_cor_validation_end_date;
487: hr_utility.set_location(l_proc, 250);
488: else
489: hr_utility.set_location(l_proc, 260);
490: --
491: -- When a different DateTrack mode is used, need to select the current
485: p_effective_start_date := l_cor_validation_start_date;
486: p_effective_end_date := l_cor_validation_end_date;
487: hr_utility.set_location(l_proc, 250);
488: else
489: hr_utility.set_location(l_proc, 260);
490: --
491: -- When a different DateTrack mode is used, need to select the current
492: -- object version number and effective dates. This is because the row
493: -- as of the actual termination date will have been modified.
498: , p_effective_end_date;
499: if csr_asg_values%notfound then
500: close csr_asg_values;
501: -- This should never happen.
502: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
503: hr_utility.set_message_token('PROCEDURE', l_proc);
504: hr_utility.set_message_token('STEP','270');
505: hr_utility.raise_error;
506: end if;
499: if csr_asg_values%notfound then
500: close csr_asg_values;
501: -- This should never happen.
502: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
503: hr_utility.set_message_token('PROCEDURE', l_proc);
504: hr_utility.set_message_token('STEP','270');
505: hr_utility.raise_error;
506: end if;
507: close csr_asg_values;
500: close csr_asg_values;
501: -- This should never happen.
502: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
503: hr_utility.set_message_token('PROCEDURE', l_proc);
504: hr_utility.set_message_token('STEP','270');
505: hr_utility.raise_error;
506: end if;
507: close csr_asg_values;
508: hr_utility.set_location(l_proc, 280);
501: -- This should never happen.
502: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
503: hr_utility.set_message_token('PROCEDURE', l_proc);
504: hr_utility.set_message_token('STEP','270');
505: hr_utility.raise_error;
506: end if;
507: close csr_asg_values;
508: hr_utility.set_location(l_proc, 280);
509: end if;
504: hr_utility.set_message_token('STEP','270');
505: hr_utility.raise_error;
506: end if;
507: close csr_asg_values;
508: hr_utility.set_location(l_proc, 280);
509: end if;
510: --
511: -- Set other output arguments
512: --
513: p_asg_future_changes_warning := l_asg_future_changes_warning;
514: p_entries_changed_warning := l_entries_changed_warning;
515: p_pay_proposal_warning := l_pay_proposal_warning;
516: --
517: hr_utility.set_location(' Leaving:'||l_proc, 300);
518: end actual_term_cwk_asg;
519: --
520: -- ----------------------------------------------------------------------------
521: -- |-----------------------< final_process_cwk_asg >--------------------------|
669: and p_final_process_Date between pgr.effective_start_date
670: and pgr.effective_end_date;
671: --
672: begin
673: hr_utility.set_location('Entering:'|| l_proc, 1);
674: --
675: l_object_version_number := p_object_version_number;
676: --
677: -- Validation in addition to Table Handlers
695: (p_assignment_id => p_assignment_id
696: ,p_effective_date => p_final_process_date
697: );
698: End if;
699: hr_utility.set_location(l_proc, 10);
700: --
701: if l_max_asg_end_date is not null then
702: --
703: l_asg_future_changes_warning := TRUE;
700: --
701: if l_max_asg_end_date is not null then
702: --
703: l_asg_future_changes_warning := TRUE;
704: hr_utility.set_location(l_proc, 20);
705: --
706: end if;
707: hr_utility.set_location(l_proc, 30);
708: --
703: l_asg_future_changes_warning := TRUE;
704: hr_utility.set_location(l_proc, 20);
705: --
706: end if;
707: hr_utility.set_location(l_proc, 30);
708: --
709: -- Lock the appropriate child rows for this assignment.
710: --
711: open csr_lock_csa; -- Locking ladder processing order 970
709: -- Lock the appropriate child rows for this assignment.
710: --
711: open csr_lock_csa; -- Locking ladder processing order 970
712: close csr_lock_csa;
713: hr_utility.set_location(l_proc, 40);
714: --
715: open csr_lock_alu; -- Locking ladder processing order 1110
716: close csr_lock_alu;
717: hr_utility.set_location(l_proc, 50);
713: hr_utility.set_location(l_proc, 40);
714: --
715: open csr_lock_alu; -- Locking ladder processing order 1110
716: close csr_lock_alu;
717: hr_utility.set_location(l_proc, 50);
718: --
719: open csr_lock_asa; -- Locking ladder processing order 1190
720: close csr_lock_asa;
721: hr_utility.set_location(l_proc,55);
717: hr_utility.set_location(l_proc, 50);
718: --
719: open csr_lock_asa; -- Locking ladder processing order 1190
720: close csr_lock_asa;
721: hr_utility.set_location(l_proc,55);
722: --
723: -- fix for bug 5841180 starts here
724: /* open csr_lock_ele; -- Locking ladder processing order 1440
725: close csr_lock_ele;
722: --
723: -- fix for bug 5841180 starts here
724: /* open csr_lock_ele; -- Locking ladder processing order 1440
725: close csr_lock_ele;
726: hr_utility.set_location(l_proc, 60);
727: --
728: open csr_lock_eev; -- Locking ladder processing order 1450
729: close csr_lock_eev;
730: hr_utility.set_location(l_proc, 70);*/
726: hr_utility.set_location(l_proc, 60);
727: --
728: open csr_lock_eev; -- Locking ladder processing order 1450
729: close csr_lock_eev;
730: hr_utility.set_location(l_proc, 70);*/
731:
732: open csr_lock_ele(p_final_process_date); -- Locking ladder processing order 1440
733: close csr_lock_ele;
734: hr_utility.set_location(l_proc, 60);
730: hr_utility.set_location(l_proc, 70);*/
731:
732: open csr_lock_ele(p_final_process_date); -- Locking ladder processing order 1440
733: close csr_lock_ele;
734: hr_utility.set_location(l_proc, 60);
735: --
736: open csr_lock_eev(p_final_process_date); -- Locking ladder processing order 1450
737: close csr_lock_eev;
738: hr_utility.set_location(l_proc, 70);
734: hr_utility.set_location(l_proc, 60);
735: --
736: open csr_lock_eev(p_final_process_date); -- Locking ladder processing order 1450
737: close csr_lock_eev;
738: hr_utility.set_location(l_proc, 70);
739:
740: -- fix for bug 5841180 ends here
741: --
742:
741: --
742:
743: open csr_lock_spp; -- Locking ladder processing order 1470
744: close csr_lock_spp;
745: hr_utility.set_location(l_proc, 80);
746: --
747: open csr_lock_ppm; -- Locking ladder processing order 1490
748: close csr_lock_ppm;
749: hr_utility.set_location(l_proc, 90);
745: hr_utility.set_location(l_proc, 80);
746: --
747: open csr_lock_ppm; -- Locking ladder processing order 1490
748: close csr_lock_ppm;
749: hr_utility.set_location(l_proc, 90);
750: --
751: --
752: open csr_lock_abv; -- Locking ladder processing order 1550
753: close csr_lock_abv;
750: --
751: --
752: open csr_lock_abv; -- Locking ladder processing order 1550
753: close csr_lock_abv;
754: hr_utility.set_location(l_proc, 115);
755:
756: --
757: open csr_lock_sas; -- Locking ladder processing order 1590
758: close csr_lock_sas;
755:
756: --
757: open csr_lock_sas; -- Locking ladder processing order 1590
758: close csr_lock_sas;
759: hr_utility.set_location(l_proc, 120);
760: --
761: open csr_lock_pyp; -- Locking ladder processing order 1630
762: close csr_lock_pyp;
763: --
760: --
761: open csr_lock_pyp; -- Locking ladder processing order 1630
762: close csr_lock_pyp;
763: --
764: hr_utility.set_location(l_proc, 130);
765: --
766: open csr_lock_asg_rates;
767: close csr_lock_asg_rates;
768: --
765: --
766: open csr_lock_asg_rates;
767: close csr_lock_asg_rates;
768: --
769: hr_utility.set_location(l_proc,135);
770: --
771: -- For the following tables, date effectively delete any rows which exist as
772: -- of the final process date, and ZAP any rows which start after the final
773: -- process date:
782: set sas.end_date = p_final_process_date
783: where sas.assignment_id = p_assignment_id
784: and sas.end_date IS NULL;
785: --
786: hr_utility.set_location(l_proc, 140);
787: --
788: delete per_secondary_ass_statuses sas
789: where sas.assignment_id = p_assignment_id
790: and sas.start_date > p_final_process_date;
788: delete per_secondary_ass_statuses sas
789: where sas.assignment_id = p_assignment_id
790: and sas.start_date > p_final_process_date;
791: --
792: hr_utility.set_location(l_proc, 150);
793: --
794: hr_utility.set_location(l_proc, 170);
795: --
796: update pay_cost_allocations_f pca
790: and sas.start_date > p_final_process_date;
791: --
792: hr_utility.set_location(l_proc, 150);
793: --
794: hr_utility.set_location(l_proc, 170);
795: --
796: update pay_cost_allocations_f pca
797: set pca.effective_end_date = p_final_process_date
798: where pca.assignment_id = p_assignment_id
798: where pca.assignment_id = p_assignment_id
799: and p_final_process_date between pca.effective_start_date
800: and pca.effective_end_date;
801: --
802: hr_utility.set_location(l_proc, 180);
803: --
804: delete pay_cost_allocations_f pca
805: where pca.assignment_id = p_assignment_id
806: and pca.effective_start_date > p_final_process_date;
804: delete pay_cost_allocations_f pca
805: where pca.assignment_id = p_assignment_id
806: and pca.effective_start_date > p_final_process_date;
807: --
808: hr_utility.set_location(l_proc, 190);
809: --
810: update per_spinal_point_placements_f spp
811: set spp.effective_end_date = p_final_process_date
812: where spp.assignment_id = p_assignment_id
812: where spp.assignment_id = p_assignment_id
813: and p_final_process_date between spp.effective_start_date
814: and spp.effective_end_date;
815: --
816: hr_utility.set_location(l_proc, 200);
817: --
818: delete per_spinal_point_placements_f spp
819: where spp.assignment_id = p_assignment_id
820: and spp.effective_start_date > p_final_process_date;
818: delete per_spinal_point_placements_f spp
819: where spp.assignment_id = p_assignment_id
820: and spp.effective_start_date > p_final_process_date;
821: --
822: hr_utility.set_location(l_proc, 210);
823:
824: --
825: -- SASmith date track of abv. 16-APR-1998
826:
829: where abv.assignment_id = p_assignment_id
830: and p_final_process_date between abv.effective_start_date
831: and abv.effective_end_date;
832: --
833: hr_utility.set_location(l_proc, 212);
834: --
835: delete per_assignment_budget_values_f abv
836: where abv.assignment_id = p_assignment_id
837: and abv.effective_start_date > p_final_process_date;
835: delete per_assignment_budget_values_f abv
836: where abv.assignment_id = p_assignment_id
837: and abv.effective_start_date > p_final_process_date;
838: --
839: hr_utility.set_location(l_proc, 214);
840: --
841: update pay_grade_rules_f pgr
842: set pgr.effective_end_date = p_final_process_date
843: where pgr.grade_or_spinal_point_id = p_assignment_id
844: and pgr.rate_type = 'A'
845: and p_final_process_date between pgr.effective_start_date
846: and pgr.effective_end_date;
847: --
848: hr_utility.set_location(l_proc,220);
849: --
850: delete pay_grade_rules_f pgr
851: where grade_or_spinal_point_Id = p_assignment_id
852: and pgr.rate_type = 'A'
851: where grade_or_spinal_point_Id = p_assignment_id
852: and pgr.rate_type = 'A'
853: and pgr.effective_start_Date > p_final_process_date;
854: --
855: hr_utility.set_location(l_proc,225);
856: --
857: -- Process any element entries and assignment_link_usages for this
858: -- assignment.
859: -- N.B. The procedure hrempter.terminate_entries_and_alus was procduced for
868: --
869: if csr_get_busgrp_legislation%NOTFOUND
870: then
871: --
872: hr_utility.set_location(l_proc, 230);
873: --
874: close csr_get_busgrp_legislation;
875: --
876: -- This should never happen!
874: close csr_get_busgrp_legislation;
875: --
876: -- This should never happen!
877: --
878: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
879: hr_utility.set_message_token('PROCEDURE', l_proc);
880: hr_utility.set_message_token('STEP','20');
881: hr_utility.raise_error;
882: end if;
875: --
876: -- This should never happen!
877: --
878: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
879: hr_utility.set_message_token('PROCEDURE', l_proc);
880: hr_utility.set_message_token('STEP','20');
881: hr_utility.raise_error;
882: end if;
883: --
876: -- This should never happen!
877: --
878: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
879: hr_utility.set_message_token('PROCEDURE', l_proc);
880: hr_utility.set_message_token('STEP','20');
881: hr_utility.raise_error;
882: end if;
883: --
884: close csr_get_busgrp_legislation;
877: --
878: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
879: hr_utility.set_message_token('PROCEDURE', l_proc);
880: hr_utility.set_message_token('STEP','20');
881: hr_utility.raise_error;
882: end if;
883: --
884: close csr_get_busgrp_legislation;
885: --
882: end if;
883: --
884: close csr_get_busgrp_legislation;
885: --
886: hr_utility.set_location(l_proc, 240);
887: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
888: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
889: 'DD-MON-yyyy'),99);
890: --
883: --
884: close csr_get_busgrp_legislation;
885: --
886: hr_utility.set_location(l_proc, 240);
887: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
888: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
889: 'DD-MON-yyyy'),99);
890: --
891: -- VT 10/07/96 bug #306710 added parameter in a call list
884: close csr_get_busgrp_legislation;
885: --
886: hr_utility.set_location(l_proc, 240);
887: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
888: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
889: 'DD-MON-yyyy'),99);
890: --
891: -- VT 10/07/96 bug #306710 added parameter in a call list
892: hrempter.terminate_entries_and_alus
898: ,p_entries_changed_warning => l_entries_changed_warning
899: );
900: --
901: --
902: hr_utility.set_location(l_proc, 250);
903: --
904:
905: -- Call the row handler to date effectively delete the rows
906: --
915: ,p_datetrack_mode => 'DELETE');
916:
917: end loop;
918: --
919: hr_utility.set_location(l_proc, 255);
920:
921: -- Call the row handler to zap rows
922:
923: for rec in csr_zap_ppm loop
933: end loop;
934:
935: -- Date effectively delete the assignment.
936: --
937: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
938: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
939: 'DD-MON-yyyy'),99);
940: per_asg_del.del
941: (p_assignment_id => p_assignment_id
934:
935: -- Date effectively delete the assignment.
936: --
937: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
938: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
939: 'DD-MON-yyyy'),99);
940: per_asg_del.del
941: (p_assignment_id => p_assignment_id
942: ,p_effective_start_date => l_effective_start_date
950: ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
951: );
952: --
953: --
954: hr_utility.set_location(l_proc, 260);
955: --
956: -- Set all output arguments
957: --
958: p_asg_future_changes_warning := l_asg_future_changes_warning;
961: p_entries_changed_warning := l_entries_changed_warning;
962: p_object_version_number := l_object_version_number;
963: p_org_now_no_manager_warning := l_org_now_no_manager_warning;
964: --
965: hr_utility.set_location(' Leaving:'||l_proc, 400);
966: end final_process_cwk_asg;
967: --
968: -- 115.66 (START)
969: --
1168: l_object_version_number1 per_assignments_f.object_version_number%TYPE;*/
1169: --end of bug 5026287
1170:
1171: begin
1172: hr_utility.set_location('Entering:'|| l_proc, 1);
1173: --
1174: -- Process Logic
1175: --
1176: -- Determine the datetrack mode to use for the assignment table handler
1184: , l_effective_end_date1;
1185: if csr_asg_values%notfound then
1186: close csr_asg_values;
1187: -- This should never happen.
1188: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1189: hr_utility.set_message_token('PROCEDURE', l_proc);
1190: hr_utility.set_message_token('STEP','270');
1191: hr_utility.raise_error;
1192: end if;
1185: if csr_asg_values%notfound then
1186: close csr_asg_values;
1187: -- This should never happen.
1188: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1189: hr_utility.set_message_token('PROCEDURE', l_proc);
1190: hr_utility.set_message_token('STEP','270');
1191: hr_utility.raise_error;
1192: end if;
1193: close csr_asg_values;*/
1186: close csr_asg_values;
1187: -- This should never happen.
1188: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1189: hr_utility.set_message_token('PROCEDURE', l_proc);
1190: hr_utility.set_message_token('STEP','270');
1191: hr_utility.raise_error;
1192: end if;
1193: close csr_asg_values;*/
1194: /* commented the changes made by bug 5026287*/
1187: -- This should never happen.
1188: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1189: hr_utility.set_message_token('PROCEDURE', l_proc);
1190: hr_utility.set_message_token('STEP','270');
1191: hr_utility.raise_error;
1192: end if;
1193: close csr_asg_values;*/
1194: /* commented the changes made by bug 5026287*/
1195: /*if p_actual_termination_date = l_effective_start_date1 then
1223: );
1224: --end of bug 5026287
1225:
1226:
1227: hr_utility.set_location(l_proc, 10);
1228: --
1229: if l_update_change_insert then
1230: --
1231: -- This is the case where there is a future dated assignment and
1236: l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
1237: l_asg_future_changes_warning := TRUE;
1238: l_object_version_number := p_object_version_number;
1239: l_future_records_flag := TRUE;
1240: hr_utility.set_location(l_proc, 20);
1241: elsif l_update then
1242: --
1243: l_datetrack_mode := 'UPDATE';
1244: l_object_version_number := p_object_version_number;
1241: elsif l_update then
1242: --
1243: l_datetrack_mode := 'UPDATE';
1244: l_object_version_number := p_object_version_number;
1245: hr_utility.set_location(l_proc, 30);
1246: --
1247: elsif l_correction then
1248: --
1249: hr_utility.set_location(l_proc, 40);
1245: hr_utility.set_location(l_proc, 30);
1246: --
1247: elsif l_correction then
1248: --
1249: hr_utility.set_location(l_proc, 40);
1250: --
1251: -- We have the OVN for the assignment record which starts on ATD+1
1252: -- so lock this one for termination.
1253: --
1264: ,p_object_version_number => l_object_version_number
1265: ,p_validation_start_date => l_cor_validation_start_date
1266: ,p_validation_end_date => l_cor_validation_end_date
1267: );
1268: hr_utility.set_location(l_proc, 50);
1269: --
1270: -- Find out if there changes after, the day
1271: -- after the actual termination date
1272: --
1273: l_max_asg_end_date := get_max_asg_fut_change_end_dt
1274: (p_assignment_id => p_assignment_id
1275: ,p_effective_date => p_actual_termination_date + 1
1276: );
1277: hr_utility.set_location(l_proc, 70);
1278: --
1279: if l_max_asg_end_date is not null then
1280: --
1281: l_future_records_flag := TRUE;
1278: --
1279: if l_max_asg_end_date is not null then
1280: --
1281: l_future_records_flag := TRUE;
1282: hr_utility.set_location(l_proc, 80);
1283: end if;
1284: --
1285: hr_utility.set_location(l_proc, 110);
1286: --
1281: l_future_records_flag := TRUE;
1282: hr_utility.set_location(l_proc, 80);
1283: end if;
1284: --
1285: hr_utility.set_location(l_proc, 110);
1286: --
1287: l_datetrack_mode := 'CORRECTION';
1288: l_asg_future_changes_warning := TRUE;
1289: -- For correction the object_version_number has already been derived.
1286: --
1287: l_datetrack_mode := 'CORRECTION';
1288: l_asg_future_changes_warning := TRUE;
1289: -- For correction the object_version_number has already been derived.
1290: hr_utility.set_location(l_proc, 120);
1291: else
1292: --
1293: -- No other datetrack modes are valid, and so should not occur.
1294: --
1291: else
1292: --
1293: -- No other datetrack modes are valid, and so should not occur.
1294: --
1295: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1296: hr_utility.set_message_token('PROCEDURE', l_proc);
1297: hr_utility.set_message_token('STEP','130');
1298: hr_utility.raise_error;
1299: end if;
1292: --
1293: -- No other datetrack modes are valid, and so should not occur.
1294: --
1295: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1296: hr_utility.set_message_token('PROCEDURE', l_proc);
1297: hr_utility.set_message_token('STEP','130');
1298: hr_utility.raise_error;
1299: end if;
1300: hr_utility.set_location(l_proc, 140);
1293: -- No other datetrack modes are valid, and so should not occur.
1294: --
1295: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1296: hr_utility.set_message_token('PROCEDURE', l_proc);
1297: hr_utility.set_message_token('STEP','130');
1298: hr_utility.raise_error;
1299: end if;
1300: hr_utility.set_location(l_proc, 140);
1301: --
1294: --
1295: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1296: hr_utility.set_message_token('PROCEDURE', l_proc);
1297: hr_utility.set_message_token('STEP','130');
1298: hr_utility.raise_error;
1299: end if;
1300: hr_utility.set_location(l_proc, 140);
1301: --
1302: -- Update employee assignment.
1296: hr_utility.set_message_token('PROCEDURE', l_proc);
1297: hr_utility.set_message_token('STEP','130');
1298: hr_utility.raise_error;
1299: end if;
1300: hr_utility.set_location(l_proc, 140);
1301: --
1302: -- Update employee assignment.
1303: --
1304: per_asg_upd.upd
1319: ,p_datetrack_mode => l_datetrack_mode
1320: ,p_validate => FALSE
1321: ,p_hourly_salaried_warning => l_hourly_salaried_warning
1322: );
1323: hr_utility.set_location(l_proc, 150);
1324: --
1325: -- If there are future dated records then we need to process these and set the
1326: -- right assignment status.
1327: --
1357: -- Lock the appropriate child rows for this assignment.
1358: --
1359: open csr_lock_alu; -- Locking ladder processing order 1110
1360: close csr_lock_alu;
1361: hr_utility.set_location(l_proc, 160);
1362: --
1363: open csr_lock_asa; -- Locking ladder processing order 1190
1364: close csr_lock_asa;
1365: hr_utility.set_location(l_proc,170);
1361: hr_utility.set_location(l_proc, 160);
1362: --
1363: open csr_lock_asa; -- Locking ladder processing order 1190
1364: close csr_lock_asa;
1365: hr_utility.set_location(l_proc,170);
1366: --
1367:
1368: -- Fix for bug 5841180 starts here
1369:
1368: -- Fix for bug 5841180 starts here
1369:
1370: /* open csr_lock_ele; -- Locking ladder processing order 1440
1371: close csr_lock_ele;
1372: hr_utility.set_location(l_proc, 180);
1373: --
1374: open csr_lock_eev; -- Locking ladder processing order 1450
1375: close csr_lock_eev;
1376: hr_utility.set_location(l_proc, 190); */
1372: hr_utility.set_location(l_proc, 180);
1373: --
1374: open csr_lock_eev; -- Locking ladder processing order 1450
1375: close csr_lock_eev;
1376: hr_utility.set_location(l_proc, 190); */
1377:
1378: open csr_lock_ele(p_actual_termination_date); -- Locking ladder processing order 1440
1379: close csr_lock_ele;
1380: hr_utility.set_location(l_proc, 180);
1376: hr_utility.set_location(l_proc, 190); */
1377:
1378: open csr_lock_ele(p_actual_termination_date); -- Locking ladder processing order 1440
1379: close csr_lock_ele;
1380: hr_utility.set_location(l_proc, 180);
1381: --
1382: open csr_lock_eev(p_actual_termination_date); -- Locking ladder processing order 1450
1383: close csr_lock_eev;
1384: hr_utility.set_location(l_proc, 190);
1380: hr_utility.set_location(l_proc, 180);
1381: --
1382: open csr_lock_eev(p_actual_termination_date); -- Locking ladder processing order 1450
1383: close csr_lock_eev;
1384: hr_utility.set_location(l_proc, 190);
1385:
1386: -- Fix for bug 5841180 ends here
1387:
1388: open csr_lock_pyp; -- Locking ladder processing order 1630
1386: -- Fix for bug 5841180 ends here
1387:
1388: open csr_lock_pyp; -- Locking ladder processing order 1630
1389: close csr_lock_pyp;
1390: hr_utility.set_location(l_proc, 200);
1391: --
1392: -- Process any element entries and assignment_link_usages for this
1393: -- assignment.
1394: -- N.B. The procedure hrempter.terminate_entries_and_alus was procduced for
1399: fetch csr_get_legislation_code into l_legislation_code;
1400: if csr_get_legislation_code%NOTFOUND then
1401: close csr_get_legislation_code;
1402: -- This should never happen
1403: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1404: hr_utility.set_message_token('PROCEDURE', l_proc);
1405: hr_utility.set_message_token('STEP','210');
1406: hr_utility.raise_error;
1407: end if;
1400: if csr_get_legislation_code%NOTFOUND then
1401: close csr_get_legislation_code;
1402: -- This should never happen
1403: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1404: hr_utility.set_message_token('PROCEDURE', l_proc);
1405: hr_utility.set_message_token('STEP','210');
1406: hr_utility.raise_error;
1407: end if;
1408: close csr_get_legislation_code;
1401: close csr_get_legislation_code;
1402: -- This should never happen
1403: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1404: hr_utility.set_message_token('PROCEDURE', l_proc);
1405: hr_utility.set_message_token('STEP','210');
1406: hr_utility.raise_error;
1407: end if;
1408: close csr_get_legislation_code;
1409: hr_utility.set_location(l_proc, 220);
1402: -- This should never happen
1403: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1404: hr_utility.set_message_token('PROCEDURE', l_proc);
1405: hr_utility.set_message_token('STEP','210');
1406: hr_utility.raise_error;
1407: end if;
1408: close csr_get_legislation_code;
1409: hr_utility.set_location(l_proc, 220);
1410: --
1405: hr_utility.set_message_token('STEP','210');
1406: hr_utility.raise_error;
1407: end if;
1408: close csr_get_legislation_code;
1409: hr_utility.set_location(l_proc, 220);
1410: --
1411: -- VT 10/07/96 bug #306710 added parameter in a call list
1412: hrempter.terminate_entries_and_alus
1413: (p_assignment_id => p_assignment_id
1431: --
1432: -- 115.66 (END)
1433: --
1434: --
1435: hr_utility.set_location(l_proc, 230);
1436: --
1437: -- Delete any pay proposals for this assignment that occur after the
1438: -- actual termination date.
1439: --
1441: --
1442: -- Start of fix 3202260
1443: for rec_pay_prop in csr_pay_proposal loop
1444: --
1445: hr_utility.set_location(l_proc, 231);
1446: --
1447: for rec_prop_comp in csr_proposal_comp(rec_pay_prop.pay_proposal_id) loop
1448: -- Calling the per_pay_proposal_components row handler to delete the
1449: -- proposal components
1447: for rec_prop_comp in csr_proposal_comp(rec_pay_prop.pay_proposal_id) loop
1448: -- Calling the per_pay_proposal_components row handler to delete the
1449: -- proposal components
1450: --
1451: hr_utility.set_location(l_proc, 232);
1452: --
1453: per_ppc_del.del(p_component_id => rec_prop_comp.component_id,
1454: p_object_version_number => rec_prop_comp.object_version_number,
1455: p_validation_strength => 'WEAK',
1454: p_object_version_number => rec_prop_comp.object_version_number,
1455: p_validation_strength => 'WEAK',
1456: p_validate => true);
1457: --
1458: hr_utility.set_location(l_proc, 233);
1459: --
1460: end loop;
1461: /* --
1462: -- Now deleting the salary proposal
1460: end loop;
1461: /* --
1462: -- Now deleting the salary proposal
1463: --
1464: hr_utility.set_location(l_proc, 234);
1465: --
1466: delete
1467: from per_pay_proposals pyp
1468: where pyp.pay_proposal_id = rec_pay_prop.pay_proposal_id;
1484: );
1485: l_pay_proposal_warning := TRUE;
1486: --End of fix for bug 4689950
1487: --
1488: hr_utility.set_location(l_proc, 235);
1489: --
1490: end loop;
1491: -- End of fix 3202260
1492: --
1489: --
1490: end loop;
1491: -- End of fix 3202260
1492: --
1493: hr_utility.set_location(l_proc, 240);
1494: --
1495: if l_datetrack_mode = 'CORRECTION' then
1496: --
1497: -- Leave p_object_version_number set to its existing value, as it will
1500: -- which were returned when the assignment row was locked.
1501: --
1502: p_effective_start_date := l_cor_validation_start_date;
1503: p_effective_end_date := l_cor_validation_end_date;
1504: hr_utility.set_location(l_proc, 250);
1505: else
1506: hr_utility.set_location(l_proc, 260);
1507: --
1508: -- When a different DateTrack mode is used, need to select the current
1502: p_effective_start_date := l_cor_validation_start_date;
1503: p_effective_end_date := l_cor_validation_end_date;
1504: hr_utility.set_location(l_proc, 250);
1505: else
1506: hr_utility.set_location(l_proc, 260);
1507: --
1508: -- When a different DateTrack mode is used, need to select the current
1509: -- object version number and effective dates. This is because the row
1510: -- as of the actual termination date will have been modified.
1515: , p_effective_end_date;
1516: if csr_asg_values%notfound then
1517: close csr_asg_values;
1518: -- This should never happen.
1519: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1520: hr_utility.set_message_token('PROCEDURE', l_proc);
1521: hr_utility.set_message_token('STEP','270');
1522: hr_utility.raise_error;
1523: end if;
1516: if csr_asg_values%notfound then
1517: close csr_asg_values;
1518: -- This should never happen.
1519: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1520: hr_utility.set_message_token('PROCEDURE', l_proc);
1521: hr_utility.set_message_token('STEP','270');
1522: hr_utility.raise_error;
1523: end if;
1524: close csr_asg_values;
1517: close csr_asg_values;
1518: -- This should never happen.
1519: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1520: hr_utility.set_message_token('PROCEDURE', l_proc);
1521: hr_utility.set_message_token('STEP','270');
1522: hr_utility.raise_error;
1523: end if;
1524: close csr_asg_values;
1525: hr_utility.set_location(l_proc, 280);
1518: -- This should never happen.
1519: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1520: hr_utility.set_message_token('PROCEDURE', l_proc);
1521: hr_utility.set_message_token('STEP','270');
1522: hr_utility.raise_error;
1523: end if;
1524: close csr_asg_values;
1525: hr_utility.set_location(l_proc, 280);
1526: end if;
1521: hr_utility.set_message_token('STEP','270');
1522: hr_utility.raise_error;
1523: end if;
1524: close csr_asg_values;
1525: hr_utility.set_location(l_proc, 280);
1526: end if;
1527: --
1528: -- Set other output arguments
1529: --
1530: p_asg_future_changes_warning := l_asg_future_changes_warning;
1531: p_entries_changed_warning := l_entries_changed_warning;
1532: p_pay_proposal_warning := l_pay_proposal_warning;
1533: --
1534: hr_utility.set_location(' Leaving:'||l_proc, 300);
1535: end actual_term_emp_asg_sup;
1536: --
1537: -- ----------------------------------------------------------------------------
1538: -- |---------------------------< delete_first_spp >---------------------------|
1589: loop
1590:
1591: l_datetrack_mode := 'DELETE_NEXT_CHANGE';
1592:
1593: hr_utility.set_location('l_effective_end_date :'||l_effective_end_date,25);
1594:
1595: if l_effective_end_date = p_validation_end_date then
1596: exit;
1597: end if;
1668: from per_business_groups bus
1669: where bus.business_group_id = p_business_group_id;
1670: --
1671: begin
1672: hr_utility.set_location('Entering:'|| l_proc, 1);
1673: --
1674: l_assignment_number := null;
1675: --
1676: -- Process Logic
1692: , l_legislation_code;
1693: --
1694: if csr_get_default_details%NOTFOUND then
1695: --
1696: hr_utility.set_location(l_proc, 10);
1697: --
1698: close csr_get_default_details;
1699: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1700: hr_utility.raise_error;
1695: --
1696: hr_utility.set_location(l_proc, 10);
1697: --
1698: close csr_get_default_details;
1699: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1700: hr_utility.raise_error;
1701: end if;
1702: close csr_get_default_details;
1703: --
1696: hr_utility.set_location(l_proc, 10);
1697: --
1698: close csr_get_default_details;
1699: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1700: hr_utility.raise_error;
1701: end if;
1702: close csr_get_default_details;
1703: --
1704: hr_utility.set_location(l_proc, 20);
1700: hr_utility.raise_error;
1701: end if;
1702: close csr_get_default_details;
1703: --
1704: hr_utility.set_location(l_proc, 20);
1705: --
1706: -- Create employee assignment.
1707: --
1708: hr_assignment_internal.create_emp_asg
1729: ,p_other_manager_warning => l_other_manager_warning
1730: ,p_validate_df_flex => false
1731: );
1732: --
1733: hr_utility.set_location(l_proc, 30);
1734: --
1735: -- Set remaining output arguments
1736: --
1737: p_assignment_number := l_assignment_number;
1735: -- Set remaining output arguments
1736: --
1737: p_assignment_number := l_assignment_number;
1738: --
1739: hr_utility.set_location(' Leaving:'||l_proc, 100);
1740: end create_default_emp_asg;
1741:
1742: --
1743: -- ----------------------------------------------------------------------------
1853: l_object_version_number per_assignments_f.object_version_number%TYPE;
1854:
1855: --
1856: begin
1857: hr_utility.set_location('Entering:'|| l_proc, 1);
1858: --
1859: l_assignment_status_type_id := p_assignment_status_type_id;
1860:
1861: hr_assignment_internal.create_emp_asg
1960: p_effective_start_date := l_effective_start_date;
1961: p_effective_end_date := l_effective_end_date;
1962: p_object_version_number := l_object_version_number ;
1963: --
1964: hr_utility.set_location(' Leaving:'||l_proc, 100);
1965: end create_emp_asg;
1966:
1967: --
1968: -- ----------------------------------------------------------------------------
2078: l_labour_union_member_flag per_assignments_f.labour_union_member_flag%TYPE;
2079: l_work_at_home per_all_assignments_f.work_at_home%TYPE; --FIX FOR BUG 9162187
2080: --
2081: begin
2082: hr_utility.set_location('Entering:'|| l_proc, 1);
2083: --
2084: l_assignment_status_type_id := p_assignment_status_type_id;
2085: --
2086: -- fix for bug 4550165 starts here.
2108: ,p_legislation_code => p_legislation_code
2109: ,p_expected_system_status => 'ACTIVE_ASSIGN'
2110: );
2111: --
2112: hr_utility.set_location(l_proc, 10);
2113: --
2114: -- Insert per_assignments_f row.
2115: --
2116: per_asg_ins.ins
2210: ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
2211: ,p_supervisor_assignment_id => p_supervisor_assignment_id
2212: );
2213: --
2214: hr_utility.set_location(l_proc, 20);
2215: --
2216: -- Create standard element entries for this assignment.
2217: --
2218: hrentmnt.maintain_entries_asg
2229: ,p_validation_end_date => l_effective_end_date
2230: ,p_entries_changed => l_entries_changed
2231: );
2232: --
2233: hr_utility.set_location(l_proc, 30);
2234: --
2235: -- Create budget values for this assignment.
2236: -- 16-APR-1998 Change to include effective dates. SASmith
2237: --
2255: p_assignment_id := l_assignment_id;
2256: p_effective_start_date := l_effective_start_date;
2257: p_effective_end_date := l_effective_end_date;
2258: --
2259: hr_utility.set_location(' Leaving:'||l_proc, 100);
2260: end create_emp_asg;
2261:
2262: --
2263: -- ----------------------------------------------------------------------------
2419: and p_final_process_date between effective_start_date
2420: and effective_end_date;
2421:
2422: begin
2423: hr_utility.set_location('Entering:'|| l_proc, 1);
2424: --
2425: l_object_version_number := p_object_version_number;
2426: --
2427: -- Validation in addition to Table Handlers
2445: (p_assignment_id => p_assignment_id
2446: ,p_effective_date => p_final_process_date
2447: );
2448: End if;
2449: hr_utility.set_location(l_proc, 10);
2450: --
2451: if l_max_asg_end_date is not null then
2452: --
2453: l_asg_future_changes_warning := TRUE;
2450: --
2451: if l_max_asg_end_date is not null then
2452: --
2453: l_asg_future_changes_warning := TRUE;
2454: hr_utility.set_location(l_proc, 20);
2455: --
2456: end if;
2457: hr_utility.set_location(l_proc, 30);
2458: --
2453: l_asg_future_changes_warning := TRUE;
2454: hr_utility.set_location(l_proc, 20);
2455: --
2456: end if;
2457: hr_utility.set_location(l_proc, 30);
2458: --
2459: -- Lock the appropriate child rows for this assignment.
2460: --
2461: open csr_lock_csa; -- Locking ladder processing order 970
2459: -- Lock the appropriate child rows for this assignment.
2460: --
2461: open csr_lock_csa; -- Locking ladder processing order 970
2462: close csr_lock_csa;
2463: hr_utility.set_location(l_proc, 40);
2464: --
2465: open csr_lock_alu; -- Locking ladder processing order 1110
2466: close csr_lock_alu;
2467: hr_utility.set_location(l_proc, 50);
2463: hr_utility.set_location(l_proc, 40);
2464: --
2465: open csr_lock_alu; -- Locking ladder processing order 1110
2466: close csr_lock_alu;
2467: hr_utility.set_location(l_proc, 50);
2468: --
2469: open csr_lock_asa; -- Locking ladder processing order 1190
2470: close csr_lock_asa;
2471: hr_utility.set_location(l_proc,55);
2467: hr_utility.set_location(l_proc, 50);
2468: --
2469: open csr_lock_asa; -- Locking ladder processing order 1190
2470: close csr_lock_asa;
2471: hr_utility.set_location(l_proc,55);
2472: --
2473: -- Fix for bug 5841180 starts here
2474: /* open csr_lock_ele; -- Locking ladder processing order 1440
2475: close csr_lock_ele;
2472: --
2473: -- Fix for bug 5841180 starts here
2474: /* open csr_lock_ele; -- Locking ladder processing order 1440
2475: close csr_lock_ele;
2476: hr_utility.set_location(l_proc, 60);
2477: --
2478: open csr_lock_eev; -- Locking ladder processing order 1450
2479: close csr_lock_eev;
2480: hr_utility.set_location(l_proc, 70); */
2476: hr_utility.set_location(l_proc, 60);
2477: --
2478: open csr_lock_eev; -- Locking ladder processing order 1450
2479: close csr_lock_eev;
2480: hr_utility.set_location(l_proc, 70); */
2481:
2482: open csr_lock_ele(p_final_process_date); -- Locking ladder processing order 1440
2483: close csr_lock_ele;
2484: hr_utility.set_location(l_proc, 60);
2480: hr_utility.set_location(l_proc, 70); */
2481:
2482: open csr_lock_ele(p_final_process_date); -- Locking ladder processing order 1440
2483: close csr_lock_ele;
2484: hr_utility.set_location(l_proc, 60);
2485: --
2486: open csr_lock_eev(p_final_process_date); -- Locking ladder processing order 1450
2487: close csr_lock_eev;
2488: hr_utility.set_location(l_proc, 70);
2484: hr_utility.set_location(l_proc, 60);
2485: --
2486: open csr_lock_eev(p_final_process_date); -- Locking ladder processing order 1450
2487: close csr_lock_eev;
2488: hr_utility.set_location(l_proc, 70);
2489:
2490: -- Fix for bug 5841180 ends here
2491: --
2492: open csr_lock_spp; -- Locking ladder processing order 1470
2490: -- Fix for bug 5841180 ends here
2491: --
2492: open csr_lock_spp; -- Locking ladder processing order 1470
2493: close csr_lock_spp;
2494: hr_utility.set_location(l_proc, 80);
2495: --
2496: open csr_lock_ppm; -- Locking ladder processing order 1490
2497: close csr_lock_ppm;
2498: hr_utility.set_location(l_proc, 90);
2494: hr_utility.set_location(l_proc, 80);
2495: --
2496: open csr_lock_ppm; -- Locking ladder processing order 1490
2497: close csr_lock_ppm;
2498: hr_utility.set_location(l_proc, 90);
2499: --
2500: open csr_lock_abv; -- Locking ladder processing order 1550
2501: close csr_lock_abv;
2502: hr_utility.set_location(l_proc, 115);
2498: hr_utility.set_location(l_proc, 90);
2499: --
2500: open csr_lock_abv; -- Locking ladder processing order 1550
2501: close csr_lock_abv;
2502: hr_utility.set_location(l_proc, 115);
2503: --
2504: open csr_lock_sas; -- Locking ladder processing order 1590
2505: close csr_lock_sas;
2506: hr_utility.set_location(l_proc, 120);
2502: hr_utility.set_location(l_proc, 115);
2503: --
2504: open csr_lock_sas; -- Locking ladder processing order 1590
2505: close csr_lock_sas;
2506: hr_utility.set_location(l_proc, 120);
2507: --
2508: open csr_lock_pyp; -- Locking ladder processing order 1630
2509: close csr_lock_pyp;
2510: hr_utility.set_location(l_proc, 130);
2506: hr_utility.set_location(l_proc, 120);
2507: --
2508: open csr_lock_pyp; -- Locking ladder processing order 1630
2509: close csr_lock_pyp;
2510: hr_utility.set_location(l_proc, 130);
2511:
2512: --
2513: -- For the following tables, date effectively delete any rows which exist as
2514: -- of the final process date, and ZAP any rows which start after the final
2524: set sas.end_date = p_final_process_date
2525: where sas.assignment_id = p_assignment_id
2526: and sas.end_date IS NULL;
2527: --
2528: hr_utility.set_location(l_proc, 140);
2529: --
2530: delete per_secondary_ass_statuses sas
2531: where sas.assignment_id = p_assignment_id
2532: and sas.start_date > p_final_process_date;
2530: delete per_secondary_ass_statuses sas
2531: where sas.assignment_id = p_assignment_id
2532: and sas.start_date > p_final_process_date;
2533: --
2534: hr_utility.set_location(l_proc, 150);
2535: --
2536: hr_utility.set_location(l_proc, 170);
2537: --
2538: update pay_cost_allocations_f pca
2532: and sas.start_date > p_final_process_date;
2533: --
2534: hr_utility.set_location(l_proc, 150);
2535: --
2536: hr_utility.set_location(l_proc, 170);
2537: --
2538: update pay_cost_allocations_f pca
2539: set pca.effective_end_date = p_final_process_date
2540: where pca.assignment_id = p_assignment_id
2540: where pca.assignment_id = p_assignment_id
2541: and p_final_process_date between pca.effective_start_date
2542: and pca.effective_end_date;
2543: --
2544: hr_utility.set_location(l_proc, 180);
2545: --
2546: delete pay_cost_allocations_f pca
2547: where pca.assignment_id = p_assignment_id
2548: and pca.effective_start_date > p_final_process_date;
2546: delete pay_cost_allocations_f pca
2547: where pca.assignment_id = p_assignment_id
2548: and pca.effective_start_date > p_final_process_date;
2549: --
2550: hr_utility.set_location(l_proc, 190);
2551: --
2552: update per_spinal_point_placements_f spp
2553: set spp.effective_end_date = p_final_process_date
2554: where spp.assignment_id = p_assignment_id
2554: where spp.assignment_id = p_assignment_id
2555: and p_final_process_date between spp.effective_start_date
2556: and spp.effective_end_date;
2557: --
2558: hr_utility.set_location(l_proc, 200);
2559: --
2560: delete per_spinal_point_placements_f spp
2561: where spp.assignment_id = p_assignment_id
2562: and spp.effective_start_date > p_final_process_date;
2560: delete per_spinal_point_placements_f spp
2561: where spp.assignment_id = p_assignment_id
2562: and spp.effective_start_date > p_final_process_date;
2563: --
2564: hr_utility.set_location(l_proc, 210);
2565:
2566: --
2567: -- SASmith date track of abv. 16-APR-1998
2568:
2571: where abv.assignment_id = p_assignment_id
2572: and p_final_process_date between abv.effective_start_date
2573: and abv.effective_end_date;
2574: --
2575: hr_utility.set_location(l_proc, 212);
2576: --
2577: delete per_assignment_budget_values_f abv
2578: where abv.assignment_id = p_assignment_id
2579: and abv.effective_start_date > p_final_process_date;
2577: delete per_assignment_budget_values_f abv
2578: where abv.assignment_id = p_assignment_id
2579: and abv.effective_start_date > p_final_process_date;
2580: --
2581: hr_utility.set_location(l_proc, 214);
2582:
2583:
2584: --
2585: -- Process any element entries and assignment_link_usages for this
2596: --
2597: if csr_get_busgrp_legislation%NOTFOUND
2598: then
2599: --
2600: hr_utility.set_location(l_proc, 230);
2601: --
2602: close csr_get_busgrp_legislation;
2603: --
2604: -- This should never happen!
2602: close csr_get_busgrp_legislation;
2603: --
2604: -- This should never happen!
2605: --
2606: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2607: hr_utility.set_message_token('PROCEDURE', l_proc);
2608: hr_utility.set_message_token('STEP','20');
2609: hr_utility.raise_error;
2610: end if;
2603: --
2604: -- This should never happen!
2605: --
2606: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2607: hr_utility.set_message_token('PROCEDURE', l_proc);
2608: hr_utility.set_message_token('STEP','20');
2609: hr_utility.raise_error;
2610: end if;
2611: --
2604: -- This should never happen!
2605: --
2606: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2607: hr_utility.set_message_token('PROCEDURE', l_proc);
2608: hr_utility.set_message_token('STEP','20');
2609: hr_utility.raise_error;
2610: end if;
2611: --
2612: close csr_get_busgrp_legislation;
2605: --
2606: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2607: hr_utility.set_message_token('PROCEDURE', l_proc);
2608: hr_utility.set_message_token('STEP','20');
2609: hr_utility.raise_error;
2610: end if;
2611: --
2612: close csr_get_busgrp_legislation;
2613: --
2610: end if;
2611: --
2612: close csr_get_busgrp_legislation;
2613: --
2614: hr_utility.set_location(l_proc, 240);
2615: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2616: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2617: 'DD-MON-yyyy'),99);
2618: --
2611: --
2612: close csr_get_busgrp_legislation;
2613: --
2614: hr_utility.set_location(l_proc, 240);
2615: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2616: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2617: 'DD-MON-yyyy'),99);
2618: --
2619: -- VT 10/07/96 bug #306710 added parameter in a call list
2612: close csr_get_busgrp_legislation;
2613: --
2614: hr_utility.set_location(l_proc, 240);
2615: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2616: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2617: 'DD-MON-yyyy'),99);
2618: --
2619: -- VT 10/07/96 bug #306710 added parameter in a call list
2620: hrempter.terminate_entries_and_alus
2633: --
2634: );
2635: --
2636: --
2637: hr_utility.set_location(l_proc, 250);
2638: --
2639:
2640: -- Call the row handler to date effectively delete the rows
2641: --
2650: ,p_datetrack_mode => 'DELETE');
2651:
2652: end loop;
2653: --
2654: hr_utility.set_location(l_proc, 255);
2655:
2656: -- Call the row handler to zap rows
2657:
2658: for rec in csr_zap_ppm loop
2668: end loop;
2669:
2670: -- Date effectively delete the assignment.
2671: --
2672: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2673: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2674: 'DD-MON-yyyy'),99);
2675: --
2676: per_asg_del.del
2669:
2670: -- Date effectively delete the assignment.
2671: --
2672: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2673: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2674: 'DD-MON-yyyy'),99);
2675: --
2676: per_asg_del.del
2677: (p_assignment_id => p_assignment_id
2686: ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2687: );
2688: --
2689: --
2690: hr_utility.set_location(l_proc, 260);
2691: --
2692: -- Set all output arguments
2693: --
2694: p_asg_future_changes_warning := l_asg_future_changes_warning;
2697: p_entries_changed_warning := l_entries_changed_warning;
2698: p_object_version_number := l_object_version_number;
2699: p_org_now_no_manager_warning := l_org_now_no_manager_warning;
2700: --
2701: hr_utility.set_location(' Leaving:'||l_proc, 999);
2702: --
2703: end final_process_emp_asg_sup;
2704: --
2705: --
2726: ORDER BY placement_id;
2727: --
2728: BEGIN
2729: --
2730: hr_utility.set_location('Entering : '||l_proc,10);
2731: --
2732: l_previous_id := -1;
2733: --
2734: FOR c_spp_record IN csr_spp_records LOOP
2732: l_previous_id := -1;
2733: --
2734: FOR c_spp_record IN csr_spp_records LOOP
2735: --
2736: hr_utility.set_location(l_proc||'/'||c_spp_record.placement_id,20);
2737: hr_utility.set_location(l_proc||'/'||c_spp_record.object_version_number,21);
2738: hr_utility.set_location(l_proc||'/'||c_spp_record.effective_start_date,22);
2739: --
2740: IF l_previous_id <> c_spp_record.placement_id THEN
2733: --
2734: FOR c_spp_record IN csr_spp_records LOOP
2735: --
2736: hr_utility.set_location(l_proc||'/'||c_spp_record.placement_id,20);
2737: hr_utility.set_location(l_proc||'/'||c_spp_record.object_version_number,21);
2738: hr_utility.set_location(l_proc||'/'||c_spp_record.effective_start_date,22);
2739: --
2740: IF l_previous_id <> c_spp_record.placement_id THEN
2741: --
2734: FOR c_spp_record IN csr_spp_records LOOP
2735: --
2736: hr_utility.set_location(l_proc||'/'||c_spp_record.placement_id,20);
2737: hr_utility.set_location(l_proc||'/'||c_spp_record.object_version_number,21);
2738: hr_utility.set_location(l_proc||'/'||c_spp_record.effective_start_date,22);
2739: --
2740: IF l_previous_id <> c_spp_record.placement_id THEN
2741: --
2742: hr_utility.set_location(l_proc,30);
2738: hr_utility.set_location(l_proc||'/'||c_spp_record.effective_start_date,22);
2739: --
2740: IF l_previous_id <> c_spp_record.placement_id THEN
2741: --
2742: hr_utility.set_location(l_proc,30);
2743: --
2744: hr_sp_placement_api.delete_spp
2745: (p_effective_date => c_spp_record.effective_start_date
2746: ,p_datetrack_mode => hr_api.g_zap
2754: END IF;
2755: --
2756: END LOOP;
2757: --
2758: hr_utility.set_location('Leaving : '||l_proc,999);
2759: --
2760: END spp_zap;
2761: --
2762: -- ----------------------------------------------------------------------------
2805: ORDER BY effective_start_date DESC;
2806: --
2807: BEGIN
2808: --
2809: hr_utility.set_location('Entering : '||l_proc,10);
2810: --
2811: l_datetrack_mode := p_datetrack_mode;
2812: l_object_version_number := p_object_version_number;
2813: --
2817: FETCH csr_future_records INTO l_dummy_id;
2818: --
2819: IF csr_future_records%FOUND THEN
2820: --
2821: hr_utility.set_location(l_proc,20);
2822: --
2823: -- Loop through all SPP records that are linked to the
2824: -- assignment between the assignment record start and end
2825: -- date and perform a DELETE_NEXT_CHANGE on these records.
2848: -- maintain_spp_asg procedure.
2849: --
2850: FOR c1_rec IN csr_update_change_insert_rows LOOP
2851: --
2852: hr_utility.set_location(l_proc||'/'||c1_rec.object_version_number,30);
2853: hr_utility.set_location(l_proc||'/'||c1_rec.effective_start_date,31);
2854: --
2855: l_object_version_number := c1_rec.object_version_number;
2856: --
2849: --
2850: FOR c1_rec IN csr_update_change_insert_rows LOOP
2851: --
2852: hr_utility.set_location(l_proc||'/'||c1_rec.object_version_number,30);
2853: hr_utility.set_location(l_proc||'/'||c1_rec.effective_start_date,31);
2854: --
2855: l_object_version_number := c1_rec.object_version_number;
2856: --
2857: hr_sp_placement_api.delete_spp
2869: -- the date track mode to Correction.
2870: --
2871: IF p_spp_eff_start_date = p_validation_start_date THEN
2872: --
2873: hr_utility.set_location(l_proc,40);
2874: --
2875: l_datetrack_mode := 'CORRECTION';
2876: --
2877: ELSE
2875: l_datetrack_mode := 'CORRECTION';
2876: --
2877: ELSE
2878: --
2879: hr_utility.set_location(l_proc,50);
2880: --
2881: l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
2882: --
2883: END IF;
2886: -- set the datetrack mode
2887: --
2888: ELSE
2889: --
2890: hr_utility.set_location(l_proc,60);
2891: --
2892: -- Check if the step placement record starts on the same
2893: -- day as the updated assignment record. If it does then change
2894: -- the date track mode to Correction.
2894: -- the date track mode to Correction.
2895: --
2896: IF p_spp_eff_start_date = p_validation_start_date THEN
2897: --
2898: hr_utility.set_location(l_proc,70);
2899: --
2900: l_datetrack_mode := 'CORRECTION';
2901: --
2902: ELSE
2900: l_datetrack_mode := 'CORRECTION';
2901: --
2902: ELSE
2903: --
2904: hr_utility.set_location(l_proc,80);
2905: --
2906: l_datetrack_mode := 'UPDATE';
2907: --
2908: END IF;
2915: --
2916: p_object_version_number := l_object_version_number;
2917: p_datetrack_mode := l_datetrack_mode;
2918: --
2919: hr_utility.set_location('Leaving : '||l_proc,999);
2920: --
2921: END spp_update_change_insert;
2922: --
2923: -- ----------------------------------------------------------------------------
2943: --
2944: begin
2945: --
2946: if g_debug then
2947: hr_utility.set_location('Entering : '||l_proc, 10);
2948: end if;
2949: -- If there are future SPP records that have
2950: -- a different placement id then raise an error
2951: open csr_invalid_placement_id;
2953: if csr_invalid_placement_id%found then
2954: --
2955: close csr_invalid_placement_id;
2956: --
2957: hr_utility.set_message(800, 'HR_289827_SPP_FUTURE_SPP_REC');
2958: hr_utility.raise_error;
2959: --
2960: else
2961: --
2954: --
2955: close csr_invalid_placement_id;
2956: --
2957: hr_utility.set_message(800, 'HR_289827_SPP_FUTURE_SPP_REC');
2958: hr_utility.raise_error;
2959: --
2960: else
2961: --
2962: close csr_invalid_placement_id;
2963: --
2964: end if;
2965: --
2966: if g_debug then
2967: hr_utility.set_location('Leaving : '||l_proc, 99);
2968: end if;
2969: --
2970: end;
2971: --
3044: --
3045: begin
3046: --
3047: if g_debug then
3048: hr_utility.set_location('Entering : '||l_proc, 10);
3049: end if;
3050: -- All Non valid future SPP records needs to be deleted(if any)
3051: for csr_asg_rec in csr_asg_details loop
3052: -- If Grade Id is already set to Null then we should delete the corresponding
3053: -- Grade Step records.
3054: if csr_asg_rec.grade_id is null then
3055: --
3056: if g_debug then
3057: hr_utility.set_location(l_proc, 20);
3058: end if;
3059: -- Needs to be deleted all non valid SPP records
3060: for asg_spp_rec in csr_asg_spp(csr_asg_rec.effective_start_date,
3061: csr_asg_rec.effective_end_date) loop
3065: and spp.effective_start_date = asg_spp_rec.effective_start_date
3066: and spp.effective_end_date = asg_spp_rec.effective_end_date;
3067: --
3068: if g_debug then
3069: hr_utility.set_location(l_proc, 30);
3070: end if;
3071: -- Setting the warning parameter
3072: p_del_end_future_spp := true;
3073: --
3078: for rec_spp_placement in csr_spp_placement(csr_asg_rec.effective_start_date,
3079: csr_asg_rec.effective_end_date) loop
3080: -- Placement is with an invalid Grade, whcih needs to be deleted
3081: if g_debug then
3082: hr_utility.set_location(l_proc||' Placement Grade Id '||rec_spp_placement.grade_id , 40);
3083: hr_utility.set_location(l_proc||' Asg Grade Id '||csr_asg_rec.grade_id , 50);
3084: end if;
3085: if rec_spp_placement.grade_id <> csr_asg_rec.grade_id then
3086: --
3079: csr_asg_rec.effective_end_date) loop
3080: -- Placement is with an invalid Grade, whcih needs to be deleted
3081: if g_debug then
3082: hr_utility.set_location(l_proc||' Placement Grade Id '||rec_spp_placement.grade_id , 40);
3083: hr_utility.set_location(l_proc||' Asg Grade Id '||csr_asg_rec.grade_id , 50);
3084: end if;
3085: if rec_spp_placement.grade_id <> csr_asg_rec.grade_id then
3086: --
3087: delete from per_spinal_point_placements_f spp
3089: and spp.effective_start_date = rec_spp_placement.effective_start_date
3090: and spp.effective_end_date = rec_spp_placement.effective_end_date;
3091: --
3092: if g_debug then
3093: hr_utility.set_location(l_proc, 60);
3094: end if;
3095: -- Setting the warning parameter
3096: p_del_end_future_spp := true;
3097: --
3116: nvl(csr_asg_rec.grade_id, hr_api.g_number) then
3117: --
3118: close csr_asg_spp_error;
3119: --
3120: hr_utility.set_message(800, 'HR_289771_SPP_MIN_START_DATE');
3121: hr_utility.raise_error;
3122: --
3123: else
3124: --
3117: --
3118: close csr_asg_spp_error;
3119: --
3120: hr_utility.set_message(800, 'HR_289771_SPP_MIN_START_DATE');
3121: hr_utility.raise_error;
3122: --
3123: else
3124: --
3125: close csr_asg_spp_error;
3138: where spp.placement_id = spp_rec.placement_id
3139: and spp.effective_start_date = spp_rec.effective_start_date
3140: and spp.effective_end_date = spp_rec.effective_end_date;
3141: if g_debug then
3142: hr_utility.set_location(l_proc, 70);
3143: end if;
3144: -- Setting the warning parameter
3145: p_del_end_future_spp := true;
3146: --
3147: end loop;
3148: --
3149: end if;
3150: if g_debug then
3151: hr_utility.set_location('Leaving : '||l_proc, 99);
3152: end if;
3153: --
3154: end cleanup_spp;
3155: --
3222: --
3223: begin
3224: --
3225: if g_debug then
3226: hr_utility.set_location('Entering : '||l_proc, 10);
3227: end if;
3228: -- Needs to perform the delete next change operation only, if there is
3229: -- Grade change after the assignment level DNC. (Not null to a new not null)
3230: open csr_spp_grade;
3234: if p_grade_id <> l_grade_id then
3235: -- If there are future date SPP records that have
3236: -- a different placement id then raise an error
3237: if g_debug then
3238: hr_utility.set_location(l_proc, 20);
3239: end if;
3240: --
3241: -- At validation end date check for continues placement record.
3242: -- If found, just break it.
3240: --
3241: -- At validation end date check for continues placement record.
3242: -- If found, just break it.
3243: --
3244: hr_utility.set_location(l_proc, 30);
3245: open csr_ved_continues_spp;
3246: fetch csr_ved_continues_spp into
3247: l_placement_id,l_effective_start_date
3248: ,l_effective_end_date,l_object_version_number;
3263: --
3264: -- continues placement record found on validation end date.
3265: -- Therefore simply break it on validation end date.
3266: --
3267: hr_utility.set_location('ved spp found', 40);
3268: hr_utility.set_location('dt mode :'||l_datetrack_mode, 40);
3269: hr_sp_placement_api.update_spp(
3270: p_effective_date => p_validation_end_date+1
3271: ,p_datetrack_mode => l_datetrack_mode
3264: -- continues placement record found on validation end date.
3265: -- Therefore simply break it on validation end date.
3266: --
3267: hr_utility.set_location('ved spp found', 40);
3268: hr_utility.set_location('dt mode :'||l_datetrack_mode, 40);
3269: hr_sp_placement_api.update_spp(
3270: p_effective_date => p_validation_end_date+1
3271: ,p_datetrack_mode => l_datetrack_mode
3272: ,p_placement_id => l_placement_id
3283: --
3284: for csr_rec in csr_update_change_rows loop
3285: --
3286: if g_debug then
3287: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 30);
3288: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 40);
3289: hr_utility.set_location(l_proc||'/'||csr_rec.effective_end_date, 50);
3290: hr_utility.set_location(l_proc||'/'||csr_rec.placement_id, 60);
3291: end if;
3284: for csr_rec in csr_update_change_rows loop
3285: --
3286: if g_debug then
3287: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 30);
3288: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 40);
3289: hr_utility.set_location(l_proc||'/'||csr_rec.effective_end_date, 50);
3290: hr_utility.set_location(l_proc||'/'||csr_rec.placement_id, 60);
3291: end if;
3292: --
3285: --
3286: if g_debug then
3287: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 30);
3288: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 40);
3289: hr_utility.set_location(l_proc||'/'||csr_rec.effective_end_date, 50);
3290: hr_utility.set_location(l_proc||'/'||csr_rec.placement_id, 60);
3291: end if;
3292: --
3293: if p_validation_end_date > csr_rec.effective_end_date and
3286: if g_debug then
3287: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 30);
3288: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 40);
3289: hr_utility.set_location(l_proc||'/'||csr_rec.effective_end_date, 50);
3290: hr_utility.set_location(l_proc||'/'||csr_rec.placement_id, 60);
3291: end if;
3292: --
3293: if p_validation_end_date > csr_rec.effective_end_date and
3294: csr_rec.effective_end_date <> hr_api.g_eot then
3304: --
3305: l_del_end_future_spp := true;
3306: --
3307: if g_debug then
3308: hr_utility.set_location(l_proc, 70);
3309: end if;
3310: --
3311: end if;
3312: --
3321: -- Setting the out parameter
3322: p_del_end_future_spp := l_del_end_future_spp;
3323: --
3324: if g_debug then
3325: hr_utility.set_location('Leaving : '||l_proc, 99);
3326: end if;
3327: --
3328: end delete_next_change_spp;
3329: --
3363: --
3364: begin
3365: --
3366: if g_debug then
3367: hr_utility.set_location('Entering : '||l_proc, 10);
3368: end if;
3369: -- Check for the uniqueness of Step placement id
3370: chk_valid_placement_id(p_assignment_id => p_assignment_id
3371: ,p_placement_id => p_placement_id
3370: chk_valid_placement_id(p_assignment_id => p_assignment_id
3371: ,p_placement_id => p_placement_id
3372: ,p_validation_start_date => p_validation_start_date);
3373: if g_debug then
3374: hr_utility.set_location(l_proc, 20);
3375: end if;
3376: -- Check that there has been a grade step created for this assignment
3377: open csr_spp_details;
3378: fetch csr_spp_details into l_placement_id
3398: --
3399: l_del_end_future_spp := true;
3400: --
3401: if g_debug then
3402: hr_utility.set_location(l_proc, 30);
3403: end if;
3404: --
3405: end if;
3406: --
3406: --
3407: close csr_spp_details;
3408: --
3409: if g_debug then
3410: hr_utility.set_location(l_proc, 40);
3411: end if;
3412: -- Cleanup of all invalid steps(if any)
3413: cleanup_spp(p_assignment_id => p_assignment_id
3414: ,p_datetrack_mode => p_datetrack_mode
3416: ,p_del_end_future_spp => l_del_end_future_spp);
3417: -- Setting the out parameter
3418: p_del_end_future_spp := l_del_end_future_spp;
3419: if g_debug then
3420: hr_utility.set_location('Leaving : '||l_proc, 99);
3421: end if;
3422: --
3423: end future_change_spp;
3424: --
3499: --
3500: begin
3501: --
3502: if g_debug then
3503: hr_utility.set_location('Entering : '||l_proc, 10);
3504: end if;
3505: -- Assignment record is having some SPP records for this DT period
3506: if p_current_spp_exist then
3507: -- If the assignemnt record is having a Grade and that Grade has a Pay Scale
3527: -- User is swapping the Grade with a New Grade
3528: if p_grade_id <> l_grade_id then
3529: --
3530: if g_debug then
3531: hr_utility.set_location(l_proc, 20);
3532: end if;
3533: --
3534: open csr_future_records;
3535: fetch csr_future_records into l_dummy_id;
3537: -- As future records are existing, we can perform an UPDATE_OVERRIDE
3538: l_datetrack_mode := p_datetrack_mode;
3539: --
3540: if g_debug then
3541: hr_utility.set_location(l_proc||' /l_datetrack_mode '||l_datetrack_mode, 30);
3542: hr_utility.set_location(l_proc, 40);
3543: end if;
3544: --
3545: else
3538: l_datetrack_mode := p_datetrack_mode;
3539: --
3540: if g_debug then
3541: hr_utility.set_location(l_proc||' /l_datetrack_mode '||l_datetrack_mode, 30);
3542: hr_utility.set_location(l_proc, 40);
3543: end if;
3544: --
3545: else
3546: -- As there is no future records are existing.
3548: -- with the new Grade
3549: -- Performing a DT UPDATE
3550: l_datetrack_mode := hr_api.g_update;
3551: if g_debug then
3552: hr_utility.set_location(l_proc||' /l_datetrack_mode '||l_datetrack_mode, 50);
3553: hr_utility.set_location(l_proc, 60);
3554: end if;
3555: --
3556: end if;
3549: -- Performing a DT UPDATE
3550: l_datetrack_mode := hr_api.g_update;
3551: if g_debug then
3552: hr_utility.set_location(l_proc||' /l_datetrack_mode '||l_datetrack_mode, 50);
3553: hr_utility.set_location(l_proc, 60);
3554: end if;
3555: --
3556: end if;
3557: --
3571: -- Setting the warning parameter
3572: l_del_end_future_spp := true;
3573: --
3574: if g_debug then
3575: hr_utility.set_location(l_proc, 70);
3576: end if;
3577: -- No change in Grade
3578: else
3579: -- Needs to check if any future records are existing for the SPP records
3584: fetch csr_future_records into l_dummy_id;
3585: if csr_future_records%found then
3586: -- Since future records are existing then perfom an UPDATE_OVERRIDE
3587: if g_debug then
3588: hr_utility.set_location(l_proc||' /p_datetrack_mode '||p_datetrack_mode, 80);
3589: end if;
3590: -- As future records existing for the current SPP. And if the SPP
3591: -- effective start date and validation start date are same, then we
3592: -- cannot perform a DT UPDATE_OVERRIDE.
3594: --
3595: for csr_rec in csr_update_change_rows loop
3596: --
3597: if g_debug then
3598: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 90);
3599: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 100);
3600: end if;
3601: l_object_version_number := csr_rec.object_version_number;
3602: hr_sp_placement_api.delete_spp(
3595: for csr_rec in csr_update_change_rows loop
3596: --
3597: if g_debug then
3598: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 90);
3599: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 100);
3600: end if;
3601: l_object_version_number := csr_rec.object_version_number;
3602: hr_sp_placement_api.delete_spp(
3603: p_effective_date => csr_rec.effective_start_date
3609: -- Setting the warning parameter
3610: l_del_end_future_spp := true;
3611: --
3612: if g_debug then
3613: hr_utility.set_location(l_proc, 110);
3614: end if;
3615: --
3616: end loop;
3617: --
3631: -- Setting the warning parameter
3632: l_del_end_future_spp := true;
3633: --
3634: if g_debug then
3635: hr_utility.set_location(l_proc, 120);
3636: end if;
3637: -- We need to do extra process if the DT Mode is UPDATE_OVERRIDE
3638: -- Update SPP API will end date the current record with
3639: -- p_validation_start_date - 1 and insert a new record. Then the same
3650: ,p_object_version_number => l_object_version_number
3651: ,p_effective_start_date => l_effective_start_date
3652: ,p_effective_end_date => l_effective_end_date);
3653: if g_debug then
3654: hr_utility.set_location(l_proc, 130);
3655: end if;
3656: --
3657: end if;
3658: --
3676: fetch csr_past_spp_records into l_previous_ovn;
3677: if csr_past_spp_records%found then
3678: --
3679: if g_debug then
3680: hr_utility.set_location(l_proc, 140);
3681: hr_utility.set_location(l_proc||' ovn ='||l_previous_ovn, 150);
3682: end if;
3683: hr_sp_placement_api.delete_spp(
3684: p_effective_date => p_validation_start_date - 1
3677: if csr_past_spp_records%found then
3678: --
3679: if g_debug then
3680: hr_utility.set_location(l_proc, 140);
3681: hr_utility.set_location(l_proc||' ovn ='||l_previous_ovn, 150);
3682: end if;
3683: hr_sp_placement_api.delete_spp(
3684: p_effective_date => p_validation_start_date - 1
3685: ,p_datetrack_mode => hr_api.g_delete
3689: ,p_effective_end_date => l_effective_end_date);
3690: -- Setting the warning parameter
3691: l_del_end_future_spp := true;
3692: if g_debug then
3693: hr_utility.set_location(l_proc, 160);
3694: end if;
3695: --
3696: end if;
3697: --
3708: ,p_del_end_future_spp => l_del_end_future_spp);
3709: -- Setting the out parameter
3710: p_del_end_future_spp := l_del_end_future_spp;
3711: if g_debug then
3712: hr_utility.set_location('Leaving : '||l_proc, 999);
3713: end if;
3714: --
3715: end update_override_spp;
3716: --
3753: --
3754: begin
3755: --
3756: if g_debug then
3757: hr_utility.set_location('Entering : '||l_proc, 10);
3758: end if;
3759: -- Assignment record is having some SPP records for this DT period
3760: if p_current_spp_exist then
3761: -- Check for the uniqueness of Step placement id
3762: chk_valid_placement_id(p_assignment_id => p_assignment_id
3763: ,p_placement_id => p_placement_id
3764: ,p_validation_start_date => p_validation_start_date);
3765: if g_debug then
3766: hr_utility.set_location(l_proc, 20);
3767: end if;
3768: -- Check for future records SPP records if any for the same placement id
3769: open csr_spp_future_records;
3770: fetch csr_spp_future_records into l_dummy_id;
3769: open csr_spp_future_records;
3770: fetch csr_spp_future_records into l_dummy_id;
3771: if csr_spp_future_records%found then
3772: if g_debug then
3773: hr_utility.set_location(l_proc, 30);
3774: end if;
3775: -- If the future SPP changes are existing after the validation_start_date
3776: -- then we need to delete all future changes
3777: hr_sp_placement_api.delete_spp(
3784: -- Setting the warning parameter
3785: l_del_end_future_spp := true;
3786: --
3787: if g_debug then
3788: hr_utility.set_location(l_proc, 40);
3789: end if;
3790: --
3791: end if;
3792: --
3796: fetch csr_past_spp_records into l_object_version_number;
3797: if csr_past_spp_records%found then
3798: --
3799: if g_debug then
3800: hr_utility.set_location(l_proc, 50);
3801: hr_utility.set_location(l_proc||' ovn ='||l_object_version_number, 60);
3802: end if;
3803: --
3804: hr_sp_placement_api.delete_spp(
3797: if csr_past_spp_records%found then
3798: --
3799: if g_debug then
3800: hr_utility.set_location(l_proc, 50);
3801: hr_utility.set_location(l_proc||' ovn ='||l_object_version_number, 60);
3802: end if;
3803: --
3804: hr_sp_placement_api.delete_spp(
3805: p_effective_date => p_validation_start_date - 1
3811: -- Setting the warning parameter
3812: l_del_end_future_spp := true;
3813: --
3814: if g_debug then
3815: hr_utility.set_location(l_proc, 70);
3816: end if;
3817: --
3818: end if;
3819: --
3826: -- Setting the out parameter
3827: p_del_end_future_spp := l_del_end_future_spp;
3828: --
3829: if g_debug then
3830: hr_utility.set_location('Leaving : '||l_proc, 99);
3831: end if;
3832: --
3833: end close_spp_records;
3834: --
3910: --
3911: begin
3912: --
3913: if g_debug then
3914: hr_utility.set_location('Entering : '||l_proc, 10);
3915: end if;
3916: --
3917: l_min_step_id := p_min_step_id;
3918: --
3937:
3938: -- continues placement record found on validation end date.
3939: -- Therefore simply break it on validation end date.
3940: --
3941: hr_utility.set_location('ved spp found', 20);
3942: hr_utility.set_location('dt mode :'||l_datetrack_mode, 20);
3943: --
3944: l_del_end_future_spp := TRUE;
3945: --
3938: -- continues placement record found on validation end date.
3939: -- Therefore simply break it on validation end date.
3940: --
3941: hr_utility.set_location('ved spp found', 20);
3942: hr_utility.set_location('dt mode :'||l_datetrack_mode, 20);
3943: --
3944: l_del_end_future_spp := TRUE;
3945: --
3946: hr_sp_placement_api.update_spp(
3975: --
3976: -- continues placement record found on validation start date.
3977: -- Therefore simply break it on validation start date.
3978: --
3979: hr_utility.set_location('vsd spp found', 20);
3980: hr_utility.set_location('dt mode :'||l_datetrack_mode, 20);
3981: --
3982: l_del_end_future_spp := TRUE;
3983: --
3976: -- continues placement record found on validation start date.
3977: -- Therefore simply break it on validation start date.
3978: --
3979: hr_utility.set_location('vsd spp found', 20);
3980: hr_utility.set_location('dt mode :'||l_datetrack_mode, 20);
3981: --
3982: l_del_end_future_spp := TRUE;
3983: --
3984: hr_sp_placement_api.update_spp(
3998: chk_valid_placement_id(p_assignment_id => p_assignment_id
3999: ,p_placement_id => p_placement_id
4000: ,p_validation_start_date => p_validation_start_date);
4001: --
4002: hr_utility.set_location(l_proc,30);
4003: -- Now starting from the first spp record in the validation period, perform
4004: -- DELETE-NEXT-CHANGE to make all the spp records in the validation
4005: -- period as a single record.
4006: --
4005: -- period as a single record.
4006: --
4007: -- Get the first record details in the validation period.
4008: --
4009: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,399);
4010: --
4011: --fix for bug 5067855 starts here.
4012:
4013: open csr_next_spp(p_validation_start_date);
4015: LOOP
4016: fetch csr_next_spp into l_placement_id, l_effective_start_date,
4017: l_effective_end_date, l_object_version_number;
4018:
4019: hr_utility.set_location(l_proc,40);
4020: hr_utility.set_location('l_object_version_number'||l_object_version_number,199);
4021: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,199);
4022: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,199);
4023:
4016: fetch csr_next_spp into l_placement_id, l_effective_start_date,
4017: l_effective_end_date, l_object_version_number;
4018:
4019: hr_utility.set_location(l_proc,40);
4020: hr_utility.set_location('l_object_version_number'||l_object_version_number,199);
4021: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,199);
4022: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,199);
4023:
4024: if (l_effective_end_date >= p_validation_end_date or csr_next_spp%notfound ) then
4017: l_effective_end_date, l_object_version_number;
4018:
4019: hr_utility.set_location(l_proc,40);
4020: hr_utility.set_location('l_object_version_number'||l_object_version_number,199);
4021: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,199);
4022: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,199);
4023:
4024: if (l_effective_end_date >= p_validation_end_date or csr_next_spp%notfound ) then
4025: --
4018:
4019: hr_utility.set_location(l_proc,40);
4020: hr_utility.set_location('l_object_version_number'||l_object_version_number,199);
4021: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,199);
4022: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,199);
4023:
4024: if (l_effective_end_date >= p_validation_end_date or csr_next_spp%notfound ) then
4025: --
4026: hr_utility.set_location(l_proc,50);
4022: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,199);
4023:
4024: if (l_effective_end_date >= p_validation_end_date or csr_next_spp%notfound ) then
4025: --
4026: hr_utility.set_location(l_proc,50);
4027: close csr_next_spp;
4028: exit;
4029: --
4030: end if;
4033: --
4034: -- For further safety, perform the DNC only when the spp eed is
4035: -- less than the validation end date.
4036: --
4037: hr_utility.set_location(l_proc,60);
4038: if l_effective_end_date < p_validation_end_date then
4039: --
4040: -- ADD check to see if the SPP is not end dated in
4041: -- between the validation dates. DNC which will open up the end dated SPP.
4039: --
4040: -- ADD check to see if the SPP is not end dated in
4041: -- between the validation dates. DNC which will open up the end dated SPP.
4042: --
4043: hr_utility.set_location('performing delete_spp',70);
4044: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,399);
4045: --
4046: l_effective_date := l_effective_start_date;
4047: hr_sp_placement_api.delete_spp(
4040: -- ADD check to see if the SPP is not end dated in
4041: -- between the validation dates. DNC which will open up the end dated SPP.
4042: --
4043: hr_utility.set_location('performing delete_spp',70);
4044: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,399);
4045: --
4046: l_effective_date := l_effective_start_date;
4047: hr_sp_placement_api.delete_spp(
4048: p_effective_date => l_effective_date
4053: ,p_effective_end_date => l_effective_end_date);
4054: --
4055: l_del_end_future_spp := TRUE;
4056: --
4057: hr_utility.set_location('l_object_version_number'||l_object_version_number,299);
4058: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,299);
4059: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,299);
4060: --
4061: end if;
4054: --
4055: l_del_end_future_spp := TRUE;
4056: --
4057: hr_utility.set_location('l_object_version_number'||l_object_version_number,299);
4058: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,299);
4059: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,299);
4060: --
4061: end if;
4062: --
4055: l_del_end_future_spp := TRUE;
4056: --
4057: hr_utility.set_location('l_object_version_number'||l_object_version_number,299);
4058: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,299);
4059: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,299);
4060: --
4061: end if;
4062: --
4063: hr_utility.set_location(l_proc,90);
4059: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,299);
4060: --
4061: end if;
4062: --
4063: hr_utility.set_location(l_proc,90);
4064: END LOOP;
4065: --
4066: -- Now, update the spp in the validation period in CORRECTION mode.
4067: --
4080: end if;
4081: p_del_end_future_spp := l_del_end_future_spp;
4082: --
4083: if g_debug then
4084: hr_utility.set_location('Leaving : '||l_proc, 99);
4085: end if;
4086: --
4087: end correction_spp;
4088: --
4138: ORDER BY spp.effective_end_date desc;
4139: --
4140: BEGIN
4141: --
4142: hr_utility.set_location('Entering : '||l_proc, 10);
4143: --
4144: IF p_datetrack_mode = hr_api.g_correction THEN
4145: --
4146: hr_utility.set_location(l_proc, 20);
4142: hr_utility.set_location('Entering : '||l_proc, 10);
4143: --
4144: IF p_datetrack_mode = hr_api.g_correction THEN
4145: --
4146: hr_utility.set_location(l_proc, 20);
4147: --
4148: -- Check for previous SPP records
4149: --
4150: OPEN csr_past_spp_records;
4154: -- then ZAP the SPP record.
4155: --
4156: IF csr_past_spp_records%NOTFOUND THEN
4157: --
4158: hr_utility.set_location(l_proc, 30);
4159: --
4160: l_object_version_number := p_object_version_number;
4161: --
4162: hr_sp_placement_api.delete_spp
4171: -- then perform a DT Delete.
4172: --
4173: ELSE
4174: --
4175: hr_utility.set_location(l_proc, 40);
4176: --
4177: l_object_version_number := p_object_version_number;
4178: --
4179: OPEN csr_previous_spp_record;
4179: OPEN csr_previous_spp_record;
4180: FETCH csr_previous_spp_record INTO l_previous_ovn,
4181: l_previous_end_date;
4182: --
4183: hr_utility.set_location(l_proc||l_previous_ovn||'/'||l_previous_end_date, 50);
4184: --
4185: hr_sp_placement_api.delete_spp
4186: (p_effective_date => l_previous_end_date
4187: ,p_datetrack_mode => hr_api.g_delete
4195: -- If datetrack mode is not CORRECTION then
4196: --
4197: ELSE
4198: --
4199: hr_utility.set_location(l_proc, 50);
4200: --
4201: l_object_version_number := p_object_version_number;
4202: --
4203: hr_sp_placement_api.delete_spp
4209: ,p_effective_end_date => l_effective_end_date);
4210: --
4211: END IF;
4212: --
4213: hr_utility.set_location(l_proc, 60);
4214: --
4215: l_previous_id := -1;
4216: --
4217: -- Delete all future SPP records that have
4218: -- a different placement_id do the SPP record delete above
4219: --
4220: FOR c_future_spp IN csr_future_spp_records LOOP
4221: --
4222: hr_utility.set_location(l_proc||'/ pl_id = '||c_future_spp.placement_id, 70);
4223: hr_utility.set_location(l_proc||'/ ovn = '||c_future_spp.object_version_number, 71);
4224: --
4225: -- If the record retrieved has a different placement id
4226: -- then perform a ZAP on this record. If the ID is the same
4219: --
4220: FOR c_future_spp IN csr_future_spp_records LOOP
4221: --
4222: hr_utility.set_location(l_proc||'/ pl_id = '||c_future_spp.placement_id, 70);
4223: hr_utility.set_location(l_proc||'/ ovn = '||c_future_spp.object_version_number, 71);
4224: --
4225: -- If the record retrieved has a different placement id
4226: -- then perform a ZAP on this record. If the ID is the same
4227: -- as the previous id then do nothing as this record has already
4228: -- been deleted.
4229: --
4230: IF l_previous_id <> c_future_spp.placement_id THEN
4231: --
4232: hr_utility.set_location(l_proc, 80);
4233: --
4234: l_previous_id := c_future_spp.placement_id;
4235: l_object_version_number := c_future_spp.object_version_number;
4236: --
4245: END IF;
4246: --
4247: END LOOP;
4248: --
4249: hr_utility.set_location('Leaving : '||l_proc, 999);
4250: --
4251: END delete_future_spp_records;
4252: --
4253: -- ----------------------------------------------------------------------------
4346: --
4347: begin
4348: --
4349: if g_debug then
4350: hr_utility.set_location('Entering:'|| l_proc, 10);
4351: end if;
4352: -- Validation in addition to Table Handlers
4353: -- Check that all mandatory arguments are not null.
4354: hr_api.mandatory_arg_error
4371: p_argument => 'validation_end_date',
4372: p_argument_value => p_validation_end_date);
4373: -- Process Logic
4374: if g_debug then
4375: hr_utility.set_location(l_proc||' p_assignment_id = '||p_assignment_id, 15);
4376: hr_utility.set_location(l_proc||' p_datetrack_mode = '||p_datetrack_mode, 16);
4377: hr_utility.set_location(l_proc||' p_val_st_date = '||p_validation_start_date, 17);
4378: hr_utility.set_location(l_proc||' p_val_end_date = '||p_validation_end_date, 18);
4379: end if;
4372: p_argument_value => p_validation_end_date);
4373: -- Process Logic
4374: if g_debug then
4375: hr_utility.set_location(l_proc||' p_assignment_id = '||p_assignment_id, 15);
4376: hr_utility.set_location(l_proc||' p_datetrack_mode = '||p_datetrack_mode, 16);
4377: hr_utility.set_location(l_proc||' p_val_st_date = '||p_validation_start_date, 17);
4378: hr_utility.set_location(l_proc||' p_val_end_date = '||p_validation_end_date, 18);
4379: end if;
4380: -- Setting this variable as TRUE, because when we call SPP rhi, the validation proc
4373: -- Process Logic
4374: if g_debug then
4375: hr_utility.set_location(l_proc||' p_assignment_id = '||p_assignment_id, 15);
4376: hr_utility.set_location(l_proc||' p_datetrack_mode = '||p_datetrack_mode, 16);
4377: hr_utility.set_location(l_proc||' p_val_st_date = '||p_validation_start_date, 17);
4378: hr_utility.set_location(l_proc||' p_val_end_date = '||p_validation_end_date, 18);
4379: end if;
4380: -- Setting this variable as TRUE, because when we call SPP rhi, the validation proc
4381: -- per_spp_bus.chk_future_asg_changes should not be executed
4374: if g_debug then
4375: hr_utility.set_location(l_proc||' p_assignment_id = '||p_assignment_id, 15);
4376: hr_utility.set_location(l_proc||' p_datetrack_mode = '||p_datetrack_mode, 16);
4377: hr_utility.set_location(l_proc||' p_val_st_date = '||p_validation_start_date, 17);
4378: hr_utility.set_location(l_proc||' p_val_end_date = '||p_validation_end_date, 18);
4379: end if;
4380: -- Setting this variable as TRUE, because when we call SPP rhi, the validation proc
4381: -- per_spp_bus.chk_future_asg_changes should not be executed
4382: g_called_from_spp_asg := true;
4389: --
4390: close csr_lock_spp_rows;
4391: --
4392: if g_debug then
4393: hr_utility.set_location(l_proc, 20);
4394: end if;
4395: -- DT mode ZAP needs to processed seperately. Date effective SPP records
4396: -- check may not be valid for all the case.
4397: -- If the datetrack mode is ZAP,(This DT mode is allowed only for
4399: -- to this assignment from the database.
4400: if p_datetrack_mode = hr_api.g_zap then
4401: --
4402: if g_debug then
4403: hr_utility.set_location(l_proc, 30);
4404: hr_utility.set_location(l_proc||' DT Mode = '||p_datetrack_mode, 31);
4405: end if;
4406: --
4407: spp_zap (p_assignment_id => p_assignment_id);
4400: if p_datetrack_mode = hr_api.g_zap then
4401: --
4402: if g_debug then
4403: hr_utility.set_location(l_proc, 30);
4404: hr_utility.set_location(l_proc||' DT Mode = '||p_datetrack_mode, 31);
4405: end if;
4406: --
4407: spp_zap (p_assignment_id => p_assignment_id);
4408: -- All other DT mode, Date effective check that there has been a grade step
4417: --
4418: l_current_spp_exist := true;
4419: --
4420: if g_debug then
4421: hr_utility.set_location(l_proc||' Current SPP record exist', 32);
4422: end if;
4423: -- If there is NO current SPP records exists, then we need to
4424: -- check for future SPP records
4425: else
4430: --
4431: l_future_spp_exist := true;
4432: --
4433: if g_debug then
4434: hr_utility.set_location(l_proc||' Future SPP record exist', 33);
4435: end if;
4436: --
4437: end if;
4438: --
4455: ,p_validation_start_date => p_validation_start_date
4456: ,p_validation_end_date => p_validation_end_date
4457: ,p_del_end_future_spp => l_del_end_future_spp);
4458: if g_debug then
4459: hr_utility.set_location(l_proc, 60);
4460: end if;
4461: --
4462: elsif p_datetrack_mode = hr_api.g_future_change then
4463: --
4467: ,p_datetrack_mode => p_datetrack_mode
4468: ,p_validation_start_date => p_validation_start_date
4469: ,p_del_end_future_spp => l_del_end_future_spp);
4470: if g_debug then
4471: hr_utility.set_location(l_proc, 80);
4472: end if;
4473: -- Needs to perform DT modes like CORRECTION, UPDATE, UPDATE_CHANGE_INSERT,
4474: -- UPDATE_OVERRIDE, DELETE only if there is an SPP record exist for current DT
4475: else
4482: if csr_grade_pay_scale_defined%found then
4483: --
4484: l_pay_scale_defined := true;
4485: if g_debug then
4486: hr_utility.set_location(l_proc, 100);
4487: end if;
4488: open csr_new_grade_scale;
4489: fetch csr_new_grade_scale into l_min_step_id;
4490: -- If no steps exists on the effective date then raise an error
4491: if csr_new_grade_scale%notfound then
4492: --
4493: close csr_new_grade_scale;
4494: --
4495: hr_utility.set_message(800, 'HR_289829_NO_SPP_REC_FOR_EDATE');
4496: hr_utility.raise_error;
4497: --
4498: end if;
4499: --
4492: --
4493: close csr_new_grade_scale;
4494: --
4495: hr_utility.set_message(800, 'HR_289829_NO_SPP_REC_FOR_EDATE');
4496: hr_utility.raise_error;
4497: --
4498: end if;
4499: --
4500: close csr_new_grade_scale;
4499: --
4500: close csr_new_grade_scale;
4501: --
4502: if g_debug then
4503: hr_utility.set_location(l_proc||'/l_min_step_id = '||l_min_step_id, 250);
4504: end if;
4505: --
4506: /* else
4507: --
4504: end if;
4505: --
4506: /* else
4507: --
4508: hr_utility.set_message(800, 'HR_289829_NO_SPP_REC_FOR_EDATE');
4509: hr_utility.raise_error; */ /* commented for bug 6346478*/
4510:
4511: --
4512: end if;
4505: --
4506: /* else
4507: --
4508: hr_utility.set_message(800, 'HR_289829_NO_SPP_REC_FOR_EDATE');
4509: hr_utility.raise_error; */ /* commented for bug 6346478*/
4510:
4511: --
4512: end if;
4513: --
4548: Else
4549: --
4550: -- Inform user there are steps that they will have to delete before correcting.
4551: --
4552: hr_utility.set_message(800, 'HR_50426_REM_STEP_BEF_REM_GRD');
4553: hr_utility.raise_error;
4554: End if;
4555: --
4556: elsif p_datetrack_mode = hr_api.g_update then
4549: --
4550: -- Inform user there are steps that they will have to delete before correcting.
4551: --
4552: hr_utility.set_message(800, 'HR_50426_REM_STEP_BEF_REM_GRD');
4553: hr_utility.raise_error;
4554: End if;
4555: --
4556: elsif p_datetrack_mode = hr_api.g_update then
4557: -- Check for future records SPP records if any for the same
4578: -- Setting the warning parameter
4579: l_del_end_future_spp := true;
4580: --
4581: if g_debug then
4582: hr_utility.set_location(l_proc, 275);
4583: end if;
4584: l_datetrack_mode := hr_api.g_correction;
4585: --
4586: else
4595: -- date track mode to CORRECTION
4596: if l_spp_eff_start_date = p_validation_start_date then
4597: --
4598: if g_debug then
4599: hr_utility.set_location(l_proc, 300);
4600: end if;
4601: l_datetrack_mode := hr_api.g_correction;
4602: --
4603: else
4602: --
4603: else
4604: --
4605: if g_debug then
4606: hr_utility.set_location(l_proc, 310);
4607: end if;
4608: l_datetrack_mode := hr_api.g_update;
4609: --
4610: end if;
4613: --
4614: close csr_spp_future_records;
4615: --
4616: if g_debug then
4617: hr_utility.set_location(l_proc||' DT Mode = '||l_datetrack_mode, 320);
4618: end if;
4619: --
4620: elsif p_datetrack_mode = hr_api.g_delete then
4621: --
4631: open csr_min_spp_date;
4632: fetch csr_min_spp_date into l_min_spp_date;
4633: if l_min_spp_date > p_validation_start_date then
4634: --
4635: hr_utility.set_message(800, 'HR_289771_SPP_MIN_START_DATE');
4636: hr_utility.raise_error;
4637: --
4638: end if;
4639: --
4632: fetch csr_min_spp_date into l_min_spp_date;
4633: if l_min_spp_date > p_validation_start_date then
4634: --
4635: hr_utility.set_message(800, 'HR_289771_SPP_MIN_START_DATE');
4636: hr_utility.raise_error;
4637: --
4638: end if;
4639: --
4640: close csr_min_spp_date;
4691: -- The value should be FALSE, when maintain_app_asg is called through SPP rhi's.
4692: g_called_from_spp_asg := false;
4693: --
4694: if g_debug then
4695: hr_utility.set_location(' Leaving:'||l_proc, 999);
4696: end if;
4697: --
4698: exception
4699: --
4761: and asg.effective_end_date
4762: and bus.business_group_id+0 = asg.business_group_id;
4763: --
4764: begin
4765: hr_utility.set_location('Entering:'|| l_proc, 1);
4766: --
4767: l_assignment_status_type_id := p_assignment_status_type_id;
4768: l_object_version_number := p_object_version_number;
4769: --
4772: if p_expected_system_status <> 'ACTIVE_CWK'
4773: and p_expected_system_status <> 'SUSP_CWK_ASG'
4774: then
4775: --
4776: hr_utility.set_location(l_proc, 10);
4777: --
4778: hr_utility.set_message(800, 'HR_289693_ASG_INV_EXP_STATUS');
4779: hr_utility.raise_error;
4780: end if;
4774: then
4775: --
4776: hr_utility.set_location(l_proc, 10);
4777: --
4778: hr_utility.set_message(800, 'HR_289693_ASG_INV_EXP_STATUS');
4779: hr_utility.raise_error;
4780: end if;
4781: --
4782: hr_utility.set_location(l_proc, 20);
4775: --
4776: hr_utility.set_location(l_proc, 10);
4777: --
4778: hr_utility.set_message(800, 'HR_289693_ASG_INV_EXP_STATUS');
4779: hr_utility.raise_error;
4780: end if;
4781: --
4782: hr_utility.set_location(l_proc, 20);
4783: --
4778: hr_utility.set_message(800, 'HR_289693_ASG_INV_EXP_STATUS');
4779: hr_utility.raise_error;
4780: end if;
4781: --
4782: hr_utility.set_location(l_proc, 20);
4783: --
4784: -- Get assignment details.
4785: --
4786: hr_api.mandatory_arg_error
4792: (p_api_name => l_proc,
4793: p_argument => 'effective_date',
4794: p_argument_value => p_effective_date);
4795: --
4796: hr_utility.set_location(l_proc, 30);
4797: --
4798: open csr_get_asg_dets;
4799: fetch csr_get_asg_dets
4800: into l_assignment_type
4802: , l_legislation_code;
4803: --
4804: if csr_get_asg_dets%NOTFOUND then
4805: --
4806: hr_utility.set_location(l_proc, 40);
4807: --
4808: close csr_get_asg_dets;
4809: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4810: hr_utility.raise_error;
4805: --
4806: hr_utility.set_location(l_proc, 40);
4807: --
4808: close csr_get_asg_dets;
4809: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4810: hr_utility.raise_error;
4811: end if;
4812: --
4813: close csr_get_asg_dets;
4806: hr_utility.set_location(l_proc, 40);
4807: --
4808: close csr_get_asg_dets;
4809: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4810: hr_utility.raise_error;
4811: end if;
4812: --
4813: close csr_get_asg_dets;
4814: --
4811: end if;
4812: --
4813: close csr_get_asg_dets;
4814: --
4815: hr_utility.set_location(l_proc, 50);
4816: --
4817: if l_assignment_type <> 'C' then
4818: --
4819: -- Assignment is not an employee assignment.
4817: if l_assignment_type <> 'C' then
4818: --
4819: -- Assignment is not an employee assignment.
4820: --
4821: hr_utility.set_location(l_proc, 60);
4822: --
4823: hr_utility.set_message(800, 'HR_289616_ASG_NOT_CWK');
4824: hr_utility.raise_error;
4825: end if;
4819: -- Assignment is not an employee assignment.
4820: --
4821: hr_utility.set_location(l_proc, 60);
4822: --
4823: hr_utility.set_message(800, 'HR_289616_ASG_NOT_CWK');
4824: hr_utility.raise_error;
4825: end if;
4826: --
4827: hr_utility.set_location(l_proc, 70);
4820: --
4821: hr_utility.set_location(l_proc, 60);
4822: --
4823: hr_utility.set_message(800, 'HR_289616_ASG_NOT_CWK');
4824: hr_utility.raise_error;
4825: end if;
4826: --
4827: hr_utility.set_location(l_proc, 70);
4828: --
4823: hr_utility.set_message(800, 'HR_289616_ASG_NOT_CWK');
4824: hr_utility.raise_error;
4825: end if;
4826: --
4827: hr_utility.set_location(l_proc, 70);
4828: --
4829: -- Process Logic
4830: --
4831: -- If p_assignment_status_type_id is g_number then derive it's default value,
4837: ,p_legislation_code => l_legislation_code
4838: ,p_expected_system_status => p_expected_system_status
4839: );
4840: --
4841: hr_utility.set_location(l_proc, 80);
4842: --
4843: -- Update employee assignment.
4844: --
4845: per_asg_upd.upd
4862: ,p_validate => FALSE
4863: ,p_hourly_salaried_warning => l_hourly_salaried_warning
4864: );
4865: --
4866: hr_utility.set_location(l_proc, 90);
4867: --
4868: -- Set all output arguments
4869: --
4870: p_effective_start_date := l_effective_start_date;
4870: p_effective_start_date := l_effective_start_date;
4871: p_effective_end_date := l_effective_end_date;
4872: p_object_version_number := l_object_version_number;
4873: --
4874: hr_utility.set_location(' Leaving:'||l_proc, 200);
4875: end update_status_type_cwk_asg;
4876: --
4877: -- ----------------------------------------------------------------------------
4878: -- |----------------------< update_status_type_emp_asg >----------------------|
4926: and asg.effective_end_date
4927: and bus.business_group_id+0 = asg.business_group_id;
4928: --
4929: begin
4930: hr_utility.set_location('Entering:'|| l_proc, 1);
4931: --
4932: l_assignment_status_type_id := p_assignment_status_type_id;
4933: l_object_version_number := p_object_version_number;
4934: --
4937: if p_expected_system_status <> 'ACTIVE_ASSIGN'
4938: and p_expected_system_status <> 'SUSP_ASSIGN'
4939: then
4940: --
4941: hr_utility.set_location(l_proc, 10);
4942: --
4943: hr_utility.set_message(801, 'HR_7947_ASG_INV_EXP_STATUS');
4944: hr_utility.raise_error;
4945: end if;
4939: then
4940: --
4941: hr_utility.set_location(l_proc, 10);
4942: --
4943: hr_utility.set_message(801, 'HR_7947_ASG_INV_EXP_STATUS');
4944: hr_utility.raise_error;
4945: end if;
4946: --
4947: hr_utility.set_location(l_proc, 20);
4940: --
4941: hr_utility.set_location(l_proc, 10);
4942: --
4943: hr_utility.set_message(801, 'HR_7947_ASG_INV_EXP_STATUS');
4944: hr_utility.raise_error;
4945: end if;
4946: --
4947: hr_utility.set_location(l_proc, 20);
4948: --
4943: hr_utility.set_message(801, 'HR_7947_ASG_INV_EXP_STATUS');
4944: hr_utility.raise_error;
4945: end if;
4946: --
4947: hr_utility.set_location(l_proc, 20);
4948: --
4949: -- Get assignment details.
4950: --
4951: hr_api.mandatory_arg_error
4957: (p_api_name => l_proc,
4958: p_argument => 'effective_date',
4959: p_argument_value => p_effective_date);
4960: --
4961: hr_utility.set_location(l_proc, 30);
4962: --
4963: open csr_get_asg_dets;
4964: fetch csr_get_asg_dets
4965: into l_assignment_type
4967: , l_legislation_code;
4968: --
4969: if csr_get_asg_dets%NOTFOUND then
4970: --
4971: hr_utility.set_location(l_proc, 40);
4972: --
4973: close csr_get_asg_dets;
4974: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4975: hr_utility.raise_error;
4970: --
4971: hr_utility.set_location(l_proc, 40);
4972: --
4973: close csr_get_asg_dets;
4974: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4975: hr_utility.raise_error;
4976: end if;
4977: --
4978: close csr_get_asg_dets;
4971: hr_utility.set_location(l_proc, 40);
4972: --
4973: close csr_get_asg_dets;
4974: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4975: hr_utility.raise_error;
4976: end if;
4977: --
4978: close csr_get_asg_dets;
4979: --
4976: end if;
4977: --
4978: close csr_get_asg_dets;
4979: --
4980: hr_utility.set_location(l_proc, 50);
4981: --
4982: if l_assignment_type <> 'E' then
4983: --
4984: -- Assignment is not an employee assignment.
4982: if l_assignment_type <> 'E' then
4983: --
4984: -- Assignment is not an employee assignment.
4985: --
4986: hr_utility.set_location(l_proc, 60);
4987: --
4988: hr_utility.set_message(801, 'HR_7948_ASG_ASG_NOT_EMP');
4989: hr_utility.raise_error;
4990: end if;
4984: -- Assignment is not an employee assignment.
4985: --
4986: hr_utility.set_location(l_proc, 60);
4987: --
4988: hr_utility.set_message(801, 'HR_7948_ASG_ASG_NOT_EMP');
4989: hr_utility.raise_error;
4990: end if;
4991: --
4992: hr_utility.set_location(l_proc, 70);
4985: --
4986: hr_utility.set_location(l_proc, 60);
4987: --
4988: hr_utility.set_message(801, 'HR_7948_ASG_ASG_NOT_EMP');
4989: hr_utility.raise_error;
4990: end if;
4991: --
4992: hr_utility.set_location(l_proc, 70);
4993: --
4988: hr_utility.set_message(801, 'HR_7948_ASG_ASG_NOT_EMP');
4989: hr_utility.raise_error;
4990: end if;
4991: --
4992: hr_utility.set_location(l_proc, 70);
4993: --
4994: -- Process Logic
4995: --
4996: -- If p_assignment_status_type_id is g_number then derive it's default value,
5002: ,p_legislation_code => l_legislation_code
5003: ,p_expected_system_status => p_expected_system_status
5004: );
5005: --
5006: hr_utility.set_location(l_proc, 80);
5007: --
5008: -- Update employee assignment.
5009: --
5010: per_asg_upd.upd
5027: ,p_validate => FALSE
5028: ,p_hourly_salaried_warning => l_hourly_salaried_warning
5029: );
5030: --
5031: hr_utility.set_location(l_proc, 90);
5032: --
5033: -- Set all output arguments
5034: --
5035: p_effective_start_date := l_effective_start_date;
5035: p_effective_start_date := l_effective_start_date;
5036: p_effective_end_date := l_effective_end_date;
5037: p_object_version_number := l_object_version_number;
5038: --
5039: hr_utility.set_location(' Leaving:'||l_proc, 200);
5040: end update_status_type_emp_asg;
5041: --
5042: -- ----------------------------------------------------------------------------
5043: -- |----------------------------< create_apl_asg >----------------------------|
5161: l_labour_union_member_flag per_assignments_f.labour_union_member_flag%TYPE;
5162: l_work_at_home per_all_assignments_f.work_at_home%TYPE; --FIX FOR BUG 9246917
5163: begin
5164: --
5165: hr_utility.set_location(l_proc, 10);
5166: -- fix for bug 4550165 starts here.
5167: if p_legislation_code = 'DE' then
5168: l_labour_union_member_flag := null;
5169: else
5196: else
5197: l_assignment_status_type_id := p_assignment_status_type_id;
5198: end if;
5199: --
5200: hr_utility.set_location(l_proc, 20);
5201: --
5202: l_assignment_number := null;
5203: l_work_at_home := nvl(p_work_at_home,'N'); --FIX FOR BUG 9246917
5204: --
5318: , p_assignment_status_id => l_assignment_status_id
5319: , p_object_version_number => l_asg_status_ovn);
5320:
5321:
5322: hr_utility.set_location(l_proc, 30);
5323: --
5324: -- Create assignment budget values.
5325: --Change 16-APR-1998 Include effective dates. SASmith
5326: --
5332: ,p_effective_start_date => l_effective_start_date
5333: ,p_effective_end_date => l_effective_end_date
5334: );
5335: --
5336: hr_utility.set_location(l_proc, 40);
5337: --
5338: -- Check if a letter request is necessary for the assignment.
5339: --
5340: per_applicant_pkg.check_for_letter_requests
5347: ,p_validation_start_date => l_effective_start_date
5348: ,p_vacancy_id => p_vacancy_id
5349: );
5350: --
5351: hr_utility.set_location(l_proc, 50);
5352: --
5353: -- Set OUT parameters
5354: --
5355: p_assignment_id := l_assignment_id;
5358: p_effective_end_date := l_effective_end_date;
5359: p_assignment_sequence := l_assignment_sequence;
5360: p_comment_id := l_comment_id;
5361: --
5362: hr_utility.set_location(' Leaving:'||l_proc, 200);
5363: --
5364: end create_apl_asg;
5365: --
5366: -- ----------------------------------------------------------------------------
5404: where bus.business_group_id = p_business_group_id;
5405: --
5406: begin
5407: --
5408: hr_utility.set_location('Entering:'|| l_proc, 1);
5409: --
5410: l_assignment_number := null;
5411: --
5412: -- Process Logic
5428: , l_legislation_code;
5429: --
5430: if csr_get_default_details%NOTFOUND then
5431: --
5432: hr_utility.set_location(l_proc, 10);
5433: --
5434: close csr_get_default_details;
5435: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5436: hr_utility.raise_error;
5431: --
5432: hr_utility.set_location(l_proc, 10);
5433: --
5434: close csr_get_default_details;
5435: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5436: hr_utility.raise_error;
5437: --
5438: end if;
5439: --
5432: hr_utility.set_location(l_proc, 10);
5433: --
5434: close csr_get_default_details;
5435: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5436: hr_utility.raise_error;
5437: --
5438: end if;
5439: --
5440: close csr_get_default_details;
5438: end if;
5439: --
5440: close csr_get_default_details;
5441: --
5442: hr_utility.set_location(l_proc, 20);
5443: --
5444: -- Create the contingent worker assignment.
5445: --
5446: hr_assignment_internal.create_cwk_asg
5467: ,p_other_manager_warning => l_other_manager_warning
5468: ,p_validate_df_flex => false
5469: );
5470: --
5471: hr_utility.set_location(l_proc, 30);
5472: --
5473: -- Set remaining output arguments
5474: --
5475: p_assignment_number := l_assignment_number;
5473: -- Set remaining output arguments
5474: --
5475: p_assignment_number := l_assignment_number;
5476: --
5477: hr_utility.set_location(' Leaving:'||l_proc, 999);
5478: --
5479: end create_default_cwk_asg;
5480: --
5481: -- ----------------------------------------------------------------------------
5576: l_hourly_salaried_warning boolean;
5577: l_proc varchar2(72) := g_package||'create_cwk_asg';
5578: --
5579: begin
5580: hr_utility.set_location('Entering:'|| l_proc, 1);
5581: --
5582: l_assignment_status_type_id := p_assignment_status_type_id;
5583: --
5584: -- Validation in addition to Table Handlers
5596: ,p_legislation_code => p_legislation_code
5597: ,p_expected_system_status => 'ACTIVE_CWK'
5598: );
5599: --
5600: hr_utility.set_location(l_proc, 10);
5601: --
5602: -- Insert per_assignments_f row.
5603: --
5604: per_asg_ins.ins
5686: ,p_hourly_salaried_warning => l_hourly_salaried_warning
5687: ,p_supervisor_assignment_id => p_supervisor_assignment_id
5688: );
5689: --
5690: hr_utility.set_location(l_proc, 30);
5691: --
5692: -- Set all output arguments
5693: --
5694: p_assignment_id := l_assignment_id;
5694: p_assignment_id := l_assignment_id;
5695: p_effective_start_date := l_effective_start_date;
5696: p_effective_end_date := l_effective_end_date;
5697: --
5698: hr_utility.set_location(' Leaving:'||l_proc, 100);
5699: end create_cwk_asg;
5700: --
5701: -- ----------------------------------------------------------------------------
5702: -- |------------------------< create_default_apl_asg >------------------------|
5756: and nvl(date_to, hr_api.g_eot);
5757: -- Bug 4325900 Ends
5758: --
5759: begin
5760: hr_utility.set_location('Entering:'|| l_proc, 10);
5761: --
5762: -- Process Logic
5763: --
5764: -- Get default details.
5774: if csr_get_default_details%NOTFOUND then
5775: --
5776: close csr_get_default_details;
5777: --
5778: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5779: hr_utility.raise_error;
5780: end if;
5781: close csr_get_default_details;
5782: --
5775: --
5776: close csr_get_default_details;
5777: --
5778: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5779: hr_utility.raise_error;
5780: end if;
5781: close csr_get_default_details;
5782: --
5783: hr_utility.set_location(l_proc, 20);
5779: hr_utility.raise_error;
5780: end if;
5781: close csr_get_default_details;
5782: --
5783: hr_utility.set_location(l_proc, 20);
5784: -- Bug 4325900 Starts
5785: -- Desc: Check to see if the vacancy is null or not. If vacancy
5786: -- is not null then get the default location of the vacancy
5787: -- to pass the the assignment rowhandler.
5834: ,p_grade_id => l_vac_grade_id
5835: ,p_people_group_id => l_vac_pgp_id
5836: );
5837: --
5838: hr_utility.set_location(' Leaving:'||l_proc, 40);
5839: end create_default_apl_asg;
5840: --
5841: -- ----------------------------------------------------------------------------
5842: -- |----------------------< update_status_type_apl_asg >----------------------|
5938: -- call to ethnicity
5939:
5940:
5941: begin
5942: hr_utility.set_location('Entering:'|| l_proc, 1);
5943: --
5944: -- Validation in addition to Table Handlers
5945: --
5946: if p_expected_system_status <> 'ACTIVE_APL'
5949: and p_expected_system_status <> 'INTERVIEW1'
5950: and p_expected_system_status <> 'INTERVIEW2'
5951: then
5952: --
5953: hr_utility.set_message(801, 'HR_51232_ASG_INV_AASG_AST');
5954: hr_utility.raise_error;
5955: end if;
5956: --
5957: hr_utility.set_location(l_proc, 10);
5950: and p_expected_system_status <> 'INTERVIEW2'
5951: then
5952: --
5953: hr_utility.set_message(801, 'HR_51232_ASG_INV_AASG_AST');
5954: hr_utility.raise_error;
5955: end if;
5956: --
5957: hr_utility.set_location(l_proc, 10);
5958: --
5953: hr_utility.set_message(801, 'HR_51232_ASG_INV_AASG_AST');
5954: hr_utility.raise_error;
5955: end if;
5956: --
5957: hr_utility.set_location(l_proc, 10);
5958: --
5959:
5960: -- call to ethnicity
5961:
5962: open c_person_enthn;
5963: fetch c_person_enthn into l_eth_person_id,l_bgid,l_per_info1;
5964: close c_person_enthn;
5965:
5966: hr_utility.set_location('l_bgid call enthn pkg :'||l_bgid,262);
5967:
5968: hr_utility.set_location('l_perinfo1 enthn pkg :'||l_per_info1,262);
5969:
5970: hr_utility.set_location('p_expected_system_status :'||p_expected_system_status,262);
5964: close c_person_enthn;
5965:
5966: hr_utility.set_location('l_bgid call enthn pkg :'||l_bgid,262);
5967:
5968: hr_utility.set_location('l_perinfo1 enthn pkg :'||l_per_info1,262);
5969:
5970: hr_utility.set_location('p_expected_system_status :'||p_expected_system_status,262);
5971: hr_utility.set_location('hr_api.return_legislation_code (l_bgid):'||hr_api.return_legislation_code (l_bgid),262);
5972:
5966: hr_utility.set_location('l_bgid call enthn pkg :'||l_bgid,262);
5967:
5968: hr_utility.set_location('l_perinfo1 enthn pkg :'||l_per_info1,262);
5969:
5970: hr_utility.set_location('p_expected_system_status :'||p_expected_system_status,262);
5971: hr_utility.set_location('hr_api.return_legislation_code (l_bgid):'||hr_api.return_legislation_code (l_bgid),262);
5972:
5973: open csr_asg_status_type;
5974: fetch csr_asg_status_type into l_asg_status;
5967:
5968: hr_utility.set_location('l_perinfo1 enthn pkg :'||l_per_info1,262);
5969:
5970: hr_utility.set_location('p_expected_system_status :'||p_expected_system_status,262);
5971: hr_utility.set_location('hr_api.return_legislation_code (l_bgid):'||hr_api.return_legislation_code (l_bgid),262);
5972:
5973: open csr_asg_status_type;
5974: fetch csr_asg_status_type into l_asg_status;
5975: close csr_asg_status_type;
5973: open csr_asg_status_type;
5974: fetch csr_asg_status_type into l_asg_status;
5975: close csr_asg_status_type;
5976:
5977: hr_utility.set_location(' l_asg_status :'|| l_asg_status,262);
5978: hr_utility.set_location(' p_assignment_status_type_id :'|| p_assignment_status_type_id,262);
5979:
5980: if hr_api.return_legislation_code (l_bgid) ='US' and l_per_info1 is not null
5981: and l_asg_status = 'ACCEPTED' then
5974: fetch csr_asg_status_type into l_asg_status;
5975: close csr_asg_status_type;
5976:
5977: hr_utility.set_location(' l_asg_status :'|| l_asg_status,262);
5978: hr_utility.set_location(' p_assignment_status_type_id :'|| p_assignment_status_type_id,262);
5979:
5980: if hr_api.return_legislation_code (l_bgid) ='US' and l_per_info1 is not null
5981: and l_asg_status = 'ACCEPTED' then
5982:
5979:
5980: if hr_api.return_legislation_code (l_bgid) ='US' and l_per_info1 is not null
5981: and l_asg_status = 'ACCEPTED' then
5982:
5983: hr_utility.set_location('now check if an emp enthn pkg :'||l_per_info1,262);
5984: open csr_is_emp;
5985: fetch csr_is_emp into l_yes_an_emp;
5986: close csr_is_emp;
5987:
5986: close csr_is_emp;
5987:
5988: if nvl(l_yes_an_emp,-1) <> 1 then
5989:
5990: hr_utility.set_location('Yes not an EMP : enthn pkg :'||l_per_info1,262);
5991: hr_utility.set_location('calling enthn pkg :',262);
5992:
5993: per_us_eth_orig_mig_pkg.irc_upgrade_ethnic_origin(l_eth_person_id,'Y');
5994:
5987:
5988: if nvl(l_yes_an_emp,-1) <> 1 then
5989:
5990: hr_utility.set_location('Yes not an EMP : enthn pkg :'||l_per_info1,262);
5991: hr_utility.set_location('calling enthn pkg :',262);
5992:
5993: per_us_eth_orig_mig_pkg.irc_upgrade_ethnic_origin(l_eth_person_id,'Y');
5994:
5995: end if;
5993: per_us_eth_orig_mig_pkg.irc_upgrade_ethnic_origin(l_eth_person_id,'Y');
5994:
5995: end if;
5996:
5997: hr_utility.set_location('AFTER calling enthn pkg :',262);
5998: end if;
5999:
6000: -- call to ethnicity 12858051
6001:
6010: --
6011: if csr_get_asg_dets%NOTFOUND then
6012: --
6013: close csr_get_asg_dets;
6014: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
6015: hr_utility.raise_error;
6016: end if;
6017: --
6018: close csr_get_asg_dets;
6011: if csr_get_asg_dets%NOTFOUND then
6012: --
6013: close csr_get_asg_dets;
6014: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
6015: hr_utility.raise_error;
6016: end if;
6017: --
6018: close csr_get_asg_dets;
6019: --
6016: end if;
6017: --
6018: close csr_get_asg_dets;
6019: --
6020: hr_utility.set_location(l_proc, 20);
6021: --
6022: if l_assignment_type <> 'A' then
6023: --
6024: -- Assignment is not an applicant assignment.
6022: if l_assignment_type <> 'A' then
6023: --
6024: -- Assignment is not an applicant assignment.
6025: --
6026: hr_utility.set_message(801, 'HR_51036_ASG_ASG_NOT_APL');
6027: hr_utility.raise_error;
6028: end if;
6029: --
6030: hr_utility.set_location(l_proc, 30);
6023: --
6024: -- Assignment is not an applicant assignment.
6025: --
6026: hr_utility.set_message(801, 'HR_51036_ASG_ASG_NOT_APL');
6027: hr_utility.raise_error;
6028: end if;
6029: --
6030: hr_utility.set_location(l_proc, 30);
6031: --
6026: hr_utility.set_message(801, 'HR_51036_ASG_ASG_NOT_APL');
6027: hr_utility.raise_error;
6028: end if;
6029: --
6030: hr_utility.set_location(l_proc, 30);
6031: --
6032: -- Process Logic
6033: --
6034: -- If p_assignment_status_type_id is hr_api.g_number then derive it's default
6067: ,p_validate => FALSE
6068: ,p_hourly_salaried_warning => l_hourly_salaried_warning
6069: );
6070: --
6071: hr_utility.set_location(l_proc, 45);
6072: --
6073: IRC_ASG_STATUS_API.dt_update_irc_asg_status
6074: (p_assignment_id => p_assignment_id
6075: , p_datetrack_mode => 'INSERT'
6079: , p_assignment_status_id => l_assignment_status_id
6080: , p_object_version_number => l_asg_status_ovn);
6081:
6082: --
6083: hr_utility.set_location(l_proc, 50);
6084: --
6085: -- Remove out-of-date letter request lines
6086: --
6087: per_app_asg_pkg.cleanup_letters
6105: ,p_validation_start_date => l_validation_start_date
6106: ,p_vacancy_id => l_vacancy_id
6107: );
6108: --
6109: hr_utility.set_location(l_proc, 60);
6110: --
6111: -- Set out arguments
6112: --
6113: p_effective_start_date := l_effective_start_date;
6113: p_effective_start_date := l_effective_start_date;
6114: p_effective_end_date := l_effective_end_date;
6115: p_object_version_number := l_object_version_number;
6116: --
6117: hr_utility.set_location(' Leaving:'||l_proc, 70);
6118: end update_status_type_apl_asg;
6119: --surendra
6120: --
6121: -- ----------------------------------------------------------------------------
6146: --
6147: begin
6148: --
6149: if g_debug then
6150: hr_utility.set_location('Entering:'|| l_proc, 10);
6151: end if;
6152: --
6153: /* if per_asg_shd.g_old_rec.assignment_type = 'O' then
6154: if g_debug then
6151: end if;
6152: --
6153: /* if per_asg_shd.g_old_rec.assignment_type = 'O' then
6154: if g_debug then
6155: hr_utility.set_location('Assignment of type O is found', 20);
6156: end if;
6157: --
6158: fnd_message.set_name('PER', 'ERROR_TO_BE_REPLACED_BY_IRC_1');
6159: fnd_message.raise_error;
6166: --
6167: if (temp = 'Y') then
6168: --
6169: if g_debug then
6170: hr_utility.set_location('IRC Offers available for this assignment', 30);
6171: end if;
6172: --
6173: fnd_message.set_name('PER', 'ERROR_TO_BE_REPLACED_BY_IRC_2');
6174: fnd_message.raise_error;
6185: END LOOP;
6186: close irc_asgt_statuses;
6187: --
6188: if g_debug then
6189: hr_utility.set_location('Leaving:'|| l_proc, 40);
6190: end if;
6191: --
6192: end;
6193: --
6211: l_proc varchar2(72) := g_package||'ben_delete_assgt_checks';
6212: begin
6213: --
6214: if g_debug then
6215: hr_utility.set_location('Entering:'|| l_proc, 10);
6216: end if;
6217: --
6218: if (p_datetrack_mode = 'ZAP') then
6219: open ben_le_checks;
6222: --
6223: if (temp = 'Y') then
6224: --
6225: if g_debug then
6226: hr_utility.set_location('BEN Life Events available for this assignment', 20);
6227: end if;
6228: p_life_events_exists := true;
6229: --
6230: end if;
6230: end if;
6231: --
6232: end if;
6233: if g_debug then
6234: hr_utility.set_location('Leaving:'|| l_proc, 30);
6235: end if;
6236: --
6237: end;
6238: --
6338: --
6339: begin
6340: --
6341: if g_debug then
6342: hr_utility.set_location('Entering:'|| l_proc, 10);
6343: end if;
6344: --
6345: open csr_ass_sys_type;
6346: fetch csr_ass_sys_type into l_sys_status_type;
6364: fetch csr_get_legislation_code into l_legislation_code;
6365: if csr_get_legislation_code%NOTFOUND then
6366: close csr_get_legislation_code;
6367: -- This should never happen
6368: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
6369: hr_utility.set_message_token('PROCEDURE', l_proc);
6370: hr_utility.set_message_token('STEP','210');
6371: hr_utility.raise_error;
6372: end if;
6365: if csr_get_legislation_code%NOTFOUND then
6366: close csr_get_legislation_code;
6367: -- This should never happen
6368: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
6369: hr_utility.set_message_token('PROCEDURE', l_proc);
6370: hr_utility.set_message_token('STEP','210');
6371: hr_utility.raise_error;
6372: end if;
6373: close csr_get_legislation_code;
6366: close csr_get_legislation_code;
6367: -- This should never happen
6368: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
6369: hr_utility.set_message_token('PROCEDURE', l_proc);
6370: hr_utility.set_message_token('STEP','210');
6371: hr_utility.raise_error;
6372: end if;
6373: close csr_get_legislation_code;
6374: --end changes for bug 6917728
6367: -- This should never happen
6368: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
6369: hr_utility.set_message_token('PROCEDURE', l_proc);
6370: hr_utility.set_message_token('STEP','210');
6371: hr_utility.raise_error;
6372: end if;
6373: close csr_get_legislation_code;
6374: --end changes for bug 6917728
6375: --
6389: --
6390: if per_asg_shd.g_old_rec.assignment_type = 'A' then
6391: ---
6392: if g_debug then
6393: hr_utility.set_location('Applicant type Assignment', 20);
6394: end if;
6395: --
6396: per_app_asg_pkg.pre_delete_validation (
6397: p_business_group_id => per_asg_shd.g_old_rec.business_group_id,
6406: ---
6407: elsif ((per_asg_shd.g_old_rec.assignment_type = 'E') OR (per_asg_shd.g_old_rec.assignment_type = 'C')) then
6408: ---
6409: if g_debug then
6410: hr_utility.set_location('EMP/CWK type Assignment', 30);
6411: end if;
6412: --
6413: l_re_entry_point := 999;
6414: l_returned_warning := null;
6425: -- l_prim_date_from := p_prim_date_from;
6426: --
6427: while l_re_entry_point <> 0 loop
6428: if g_debug then
6429: hr_utility.set_location('l_re_entry_point :'||l_re_entry_point, 40);
6430: end if;
6431: --
6432: per_assignments_f2_pkg.pre_delete(
6433: p_datetrack_mode, -- p_del_mode,
6502: -- to delete the record. and he will get an warning This is done as an
6503: -- impact of date tracking of W4 screen
6504: --
6505: if g_debug then
6506: hr_utility.set_location('Before check_payroll_run checks', 50);
6507: end if;
6508:
6509: --if condition added for bug 6917728
6510: -- extra check in if condition added for bug 8353075
6508:
6509: --if condition added for bug 6917728
6510: -- extra check in if condition added for bug 8353075
6511: IF l_legislation_code = 'US'
6512: and not (hr_utility.chk_product_install ( 'GHR','US') ) then
6513: l_warning_text := pay_us_emp_dt_tax_val.check_payroll_run(
6514: p_rec.assignment_id, -- p_ass_id ,
6515: l_loc_code, -- p_loc_code,
6516: per_asg_shd.g_old_rec.location_id, -- p_loc_id ,
6530: --
6531: -- l_new_end_date := p_new_end_date;
6532: --
6533: if g_debug then
6534: hr_utility.set_location('Before key_delrec ', 60);
6535: end if;
6536: --
6537: per_assignments_f2_pkg.key_delrec(
6538: p_datetrack_mode, -- l_del_mode,
6562: l_rec2.assignment_id := p_rec.assignment_id; -- p_ass_id;
6563: l_rec2.position_id := per_asg_shd.g_old_rec.position_id; -- name_in('ASSGT.POSITION_ID');
6564: --
6565: if g_debug then
6566: hr_utility.set_location('Before per_pqh_shr.per_asg_bus call ', 70);
6567: end if;
6568: per_pqh_shr.per_asg_bus(
6569: p_event => 'DELETE_VALIDATE'
6570: ,p_rec => l_rec2
6581: --
6582: end if; -- End of EMP/CWK type Assignment validation checks.
6583: --
6584: if g_debug then
6585: hr_utility.set_location('Leaving :'||l_proc, 80);
6586: end if;
6587: --
6588: end; -- End of pre-delete checks
6589: --
6638: --
6639: begin
6640: --
6641: if g_debug then
6642: hr_utility.set_location('Entering :'||l_proc, 10);
6643: end if;
6644: --
6645: open csr_ass_sys_type;
6646: fetch csr_ass_sys_type into l_sys_status_type;
6651: --
6652: if per_asg_shd.g_old_rec.assignment_type = 'A' then
6653: ---
6654: if g_debug then
6655: hr_utility.set_location('Applicant type Assignment', 20);
6656: end if;
6657: --
6658: if ( p_datetrack_mode in ('FUTURE_CHANGE','DELETE_NEXT_CHANGE' ) ) then
6659: if ( p_appl_asg_new_end_date is null ) then
6681: -- set_end_date ( p_new_end_date , p_assignment_id ) ; -- copied this logic from per_app_asg_pkg
6682: -- Sets an end date on rows which are deleted with delete mode FUTURE_CHANGES or NEXT_CHANGE
6683: --
6684: if g_debug then
6685: hr_utility.set_location('Before updating assignment end date', 30);
6686: end if;
6687: --
6688: update per_assignments_f a
6689: set a.effective_end_date = p_appl_asg_new_end_date
6705: ---
6706: elsif ((per_asg_shd.g_old_rec.assignment_type = 'E') OR (per_asg_shd.g_old_rec.assignment_type = 'C')) then
6707: ---
6708: if g_debug then
6709: hr_utility.set_location('EMP/CWK type assignment checks ', 40);
6710: end if;
6711: --
6712: per_assignments_f1_pkg.post_delete(
6713: p_rec.assignment_id, -- p_ass_id,
6750: --
6751: end if; -- End of EMP/CWK type Assignment validation checks.
6752: ---
6753: if g_debug then
6754: hr_utility.set_location('Leaving :'||l_proc, 50);
6755: end if;
6756: --
6757: end;
6758: --3
6852:
6853: BEGIN
6854:
6855: IF g_debug is null THEN
6856: g_debug := hr_utility.debug_enabled;
6857: END IF;
6858:
6859: hr_utility.trace('Entering '||l_proc);
6860: hr_utility.trace('p_assignment_id :'||p_assignment_id);
6855: IF g_debug is null THEN
6856: g_debug := hr_utility.debug_enabled;
6857: END IF;
6858:
6859: hr_utility.trace('Entering '||l_proc);
6860: hr_utility.trace('p_assignment_id :'||p_assignment_id);
6861: hr_utility.trace('p_effective_start_date :'||p_effective_start_date);
6862:
6863: BEGIN
6856: g_debug := hr_utility.debug_enabled;
6857: END IF;
6858:
6859: hr_utility.trace('Entering '||l_proc);
6860: hr_utility.trace('p_assignment_id :'||p_assignment_id);
6861: hr_utility.trace('p_effective_start_date :'||p_effective_start_date);
6862:
6863: BEGIN
6864:
6857: END IF;
6858:
6859: hr_utility.trace('Entering '||l_proc);
6860: hr_utility.trace('p_assignment_id :'||p_assignment_id);
6861: hr_utility.trace('p_effective_start_date :'||p_effective_start_date);
6862:
6863: BEGIN
6864:
6865: l_assignment_id := p_assignment_id;
6871: open get_ass_typ_bus_grp_id(p_assignment_id => l_assignment_id, p_effective_date => l_effective_start_date);
6872: fetch get_ass_typ_bus_grp_id into l_assignment_type, l_business_group_id;
6873: close get_ass_typ_bus_grp_id;
6874:
6875: hr_utility.trace('l_business_group_id :'||l_business_group_id);
6876: hr_utility.trace('l_assignment_type :'||l_assignment_type);
6877: hr_utility.trace('l_legislation_code :'||l_legislation_code);
6878:
6879:
6872: fetch get_ass_typ_bus_grp_id into l_assignment_type, l_business_group_id;
6873: close get_ass_typ_bus_grp_id;
6874:
6875: hr_utility.trace('l_business_group_id :'||l_business_group_id);
6876: hr_utility.trace('l_assignment_type :'||l_assignment_type);
6877: hr_utility.trace('l_legislation_code :'||l_legislation_code);
6878:
6879:
6880: IF (l_assignment_id IS NOT NULL AND l_assignment_type = 'E')
6873: close get_ass_typ_bus_grp_id;
6874:
6875: hr_utility.trace('l_business_group_id :'||l_business_group_id);
6876: hr_utility.trace('l_assignment_type :'||l_assignment_type);
6877: hr_utility.trace('l_legislation_code :'||l_legislation_code);
6878:
6879:
6880: IF (l_assignment_id IS NOT NULL AND l_assignment_type = 'E')
6881: THEN
6884: -- This code will attempt to update the FTE value for
6885: -- an assignment. It is within its own block because we don't
6886: -- want to rollback the update to the assignment in the event that
6887: -- the FTE processing fails for some reason.
6888: hr_utility.trace('Inside '||l_proc||l_assignment_id||' on '||
6889: fnd_date.date_to_canonical(l_effective_start_date)
6890: );
6891: FOR this_abvm IN csr_abvm_uoms_to_process
6892: (p_business_group_id => l_business_group_id
6894: )
6895: LOOP
6896:
6897: IF g_debug THEN
6898: hr_utility.trace('auto_calc_fte this_abvm.configuration_value_id:'||this_abvm.configuration_value_id);
6899: hr_utility.trace('auto_calc_fte this_abvm.configuration_name:'||this_abvm.configuration_name);
6900: hr_utility.trace('auto_calc_fte this_abvm.business_group_id:'||this_abvm.business_group_id);
6901: hr_utility.trace('auto_calc_fte this_abvm.legislation_code:'||this_abvm.legislation_code);
6902: hr_utility.trace('auto_calc_fte this_abvm.uom:'||this_abvm.uom);
6895: LOOP
6896:
6897: IF g_debug THEN
6898: hr_utility.trace('auto_calc_fte this_abvm.configuration_value_id:'||this_abvm.configuration_value_id);
6899: hr_utility.trace('auto_calc_fte this_abvm.configuration_name:'||this_abvm.configuration_name);
6900: hr_utility.trace('auto_calc_fte this_abvm.business_group_id:'||this_abvm.business_group_id);
6901: hr_utility.trace('auto_calc_fte this_abvm.legislation_code:'||this_abvm.legislation_code);
6902: hr_utility.trace('auto_calc_fte this_abvm.uom:'||this_abvm.uom);
6903: hr_utility.trace('auto_calc_fte this_abvm.is_enabled:'||this_abvm.is_enabled);
6896:
6897: IF g_debug THEN
6898: hr_utility.trace('auto_calc_fte this_abvm.configuration_value_id:'||this_abvm.configuration_value_id);
6899: hr_utility.trace('auto_calc_fte this_abvm.configuration_name:'||this_abvm.configuration_name);
6900: hr_utility.trace('auto_calc_fte this_abvm.business_group_id:'||this_abvm.business_group_id);
6901: hr_utility.trace('auto_calc_fte this_abvm.legislation_code:'||this_abvm.legislation_code);
6902: hr_utility.trace('auto_calc_fte this_abvm.uom:'||this_abvm.uom);
6903: hr_utility.trace('auto_calc_fte this_abvm.is_enabled:'||this_abvm.is_enabled);
6904: hr_utility.trace('auto_calc_fte this_abvm.defn_config_value_id:'||this_abvm.defn_config_value_id);
6897: IF g_debug THEN
6898: hr_utility.trace('auto_calc_fte this_abvm.configuration_value_id:'||this_abvm.configuration_value_id);
6899: hr_utility.trace('auto_calc_fte this_abvm.configuration_name:'||this_abvm.configuration_name);
6900: hr_utility.trace('auto_calc_fte this_abvm.business_group_id:'||this_abvm.business_group_id);
6901: hr_utility.trace('auto_calc_fte this_abvm.legislation_code:'||this_abvm.legislation_code);
6902: hr_utility.trace('auto_calc_fte this_abvm.uom:'||this_abvm.uom);
6903: hr_utility.trace('auto_calc_fte this_abvm.is_enabled:'||this_abvm.is_enabled);
6904: hr_utility.trace('auto_calc_fte this_abvm.defn_config_value_id:'||this_abvm.defn_config_value_id);
6905: hr_utility.trace('auto_calc_fte this_abvm.defn_config_name:'||this_abvm.defn_config_name);
6898: hr_utility.trace('auto_calc_fte this_abvm.configuration_value_id:'||this_abvm.configuration_value_id);
6899: hr_utility.trace('auto_calc_fte this_abvm.configuration_name:'||this_abvm.configuration_name);
6900: hr_utility.trace('auto_calc_fte this_abvm.business_group_id:'||this_abvm.business_group_id);
6901: hr_utility.trace('auto_calc_fte this_abvm.legislation_code:'||this_abvm.legislation_code);
6902: hr_utility.trace('auto_calc_fte this_abvm.uom:'||this_abvm.uom);
6903: hr_utility.trace('auto_calc_fte this_abvm.is_enabled:'||this_abvm.is_enabled);
6904: hr_utility.trace('auto_calc_fte this_abvm.defn_config_value_id:'||this_abvm.defn_config_value_id);
6905: hr_utility.trace('auto_calc_fte this_abvm.defn_config_name:'||this_abvm.defn_config_name);
6906: hr_utility.trace('auto_calc_fte this_abvm.defn_bg_id:'||this_abvm.defn_bg_id);
6899: hr_utility.trace('auto_calc_fte this_abvm.configuration_name:'||this_abvm.configuration_name);
6900: hr_utility.trace('auto_calc_fte this_abvm.business_group_id:'||this_abvm.business_group_id);
6901: hr_utility.trace('auto_calc_fte this_abvm.legislation_code:'||this_abvm.legislation_code);
6902: hr_utility.trace('auto_calc_fte this_abvm.uom:'||this_abvm.uom);
6903: hr_utility.trace('auto_calc_fte this_abvm.is_enabled:'||this_abvm.is_enabled);
6904: hr_utility.trace('auto_calc_fte this_abvm.defn_config_value_id:'||this_abvm.defn_config_value_id);
6905: hr_utility.trace('auto_calc_fte this_abvm.defn_config_name:'||this_abvm.defn_config_name);
6906: hr_utility.trace('auto_calc_fte this_abvm.defn_bg_id:'||this_abvm.defn_bg_id);
6907: hr_utility.trace('auto_calc_fte this_abvm.defn_leg_code:'||this_abvm.defn_leg_code);
6900: hr_utility.trace('auto_calc_fte this_abvm.business_group_id:'||this_abvm.business_group_id);
6901: hr_utility.trace('auto_calc_fte this_abvm.legislation_code:'||this_abvm.legislation_code);
6902: hr_utility.trace('auto_calc_fte this_abvm.uom:'||this_abvm.uom);
6903: hr_utility.trace('auto_calc_fte this_abvm.is_enabled:'||this_abvm.is_enabled);
6904: hr_utility.trace('auto_calc_fte this_abvm.defn_config_value_id:'||this_abvm.defn_config_value_id);
6905: hr_utility.trace('auto_calc_fte this_abvm.defn_config_name:'||this_abvm.defn_config_name);
6906: hr_utility.trace('auto_calc_fte this_abvm.defn_bg_id:'||this_abvm.defn_bg_id);
6907: hr_utility.trace('auto_calc_fte this_abvm.defn_leg_code:'||this_abvm.defn_leg_code);
6908: hr_utility.trace('auto_calc_fte this_abvm.defn_custom_function:'||this_abvm.defn_custom_function);
6901: hr_utility.trace('auto_calc_fte this_abvm.legislation_code:'||this_abvm.legislation_code);
6902: hr_utility.trace('auto_calc_fte this_abvm.uom:'||this_abvm.uom);
6903: hr_utility.trace('auto_calc_fte this_abvm.is_enabled:'||this_abvm.is_enabled);
6904: hr_utility.trace('auto_calc_fte this_abvm.defn_config_value_id:'||this_abvm.defn_config_value_id);
6905: hr_utility.trace('auto_calc_fte this_abvm.defn_config_name:'||this_abvm.defn_config_name);
6906: hr_utility.trace('auto_calc_fte this_abvm.defn_bg_id:'||this_abvm.defn_bg_id);
6907: hr_utility.trace('auto_calc_fte this_abvm.defn_leg_code:'||this_abvm.defn_leg_code);
6908: hr_utility.trace('auto_calc_fte this_abvm.defn_custom_function:'||this_abvm.defn_custom_function);
6909: END IF;
6902: hr_utility.trace('auto_calc_fte this_abvm.uom:'||this_abvm.uom);
6903: hr_utility.trace('auto_calc_fte this_abvm.is_enabled:'||this_abvm.is_enabled);
6904: hr_utility.trace('auto_calc_fte this_abvm.defn_config_value_id:'||this_abvm.defn_config_value_id);
6905: hr_utility.trace('auto_calc_fte this_abvm.defn_config_name:'||this_abvm.defn_config_name);
6906: hr_utility.trace('auto_calc_fte this_abvm.defn_bg_id:'||this_abvm.defn_bg_id);
6907: hr_utility.trace('auto_calc_fte this_abvm.defn_leg_code:'||this_abvm.defn_leg_code);
6908: hr_utility.trace('auto_calc_fte this_abvm.defn_custom_function:'||this_abvm.defn_custom_function);
6909: END IF;
6910:
6903: hr_utility.trace('auto_calc_fte this_abvm.is_enabled:'||this_abvm.is_enabled);
6904: hr_utility.trace('auto_calc_fte this_abvm.defn_config_value_id:'||this_abvm.defn_config_value_id);
6905: hr_utility.trace('auto_calc_fte this_abvm.defn_config_name:'||this_abvm.defn_config_name);
6906: hr_utility.trace('auto_calc_fte this_abvm.defn_bg_id:'||this_abvm.defn_bg_id);
6907: hr_utility.trace('auto_calc_fte this_abvm.defn_leg_code:'||this_abvm.defn_leg_code);
6908: hr_utility.trace('auto_calc_fte this_abvm.defn_custom_function:'||this_abvm.defn_custom_function);
6909: END IF;
6910:
6911:
6904: hr_utility.trace('auto_calc_fte this_abvm.defn_config_value_id:'||this_abvm.defn_config_value_id);
6905: hr_utility.trace('auto_calc_fte this_abvm.defn_config_name:'||this_abvm.defn_config_name);
6906: hr_utility.trace('auto_calc_fte this_abvm.defn_bg_id:'||this_abvm.defn_bg_id);
6907: hr_utility.trace('auto_calc_fte this_abvm.defn_leg_code:'||this_abvm.defn_leg_code);
6908: hr_utility.trace('auto_calc_fte this_abvm.defn_custom_function:'||this_abvm.defn_custom_function);
6909: END IF;
6910:
6911:
6912: l_abv_uom := this_abvm.uom;
6910:
6911:
6912: l_abv_uom := this_abvm.uom;
6913:
6914: hr_utility.trace('this_abvm.uom is :'||this_abvm.uom);
6915: hr_utility.trace('this_abvm.defn_custom_function is :'||this_abvm.defn_custom_function);
6916:
6917: IF ( this_abvm.defn_custom_function is null or
6918: this_abvm.defn_custom_function <> 'pqp_budget_maintenance.get_FTE_event_dates' )
6911:
6912: l_abv_uom := this_abvm.uom;
6913:
6914: hr_utility.trace('this_abvm.uom is :'||this_abvm.uom);
6915: hr_utility.trace('this_abvm.defn_custom_function is :'||this_abvm.defn_custom_function);
6916:
6917: IF ( this_abvm.defn_custom_function is null or
6918: this_abvm.defn_custom_function <> 'pqp_budget_maintenance.get_FTE_event_dates' )
6919: THEN
6928: END IF;
6929: END LOOP; -- FOR this_abvm IN csr_abvm_uoms_to_process
6930:
6931: IF g_debug THEN
6932: hr_utility.trace('Leaving '||l_proc||' for '|| l_assignment_id||' on '||
6933: fnd_date.date_to_canonical(l_effective_start_date));
6934: END IF;
6935: END IF; -- IF (l_assignment_id IS NOT NULL AND l_assignment_type = 'E')
6936: END;
6936: END;
6937:
6938: EXCEPTION
6939: WHEN OTHERS THEN
6940: hr_utility.trace('Error in '||l_proc||' for '||l_assignment_id||' on '||
6941: fnd_date.date_to_canonical(l_effective_start_date));
6942: l_error_message := fnd_message.get;
6943: l_error_message := NVL(RTRIM(LTRIM(l_error_message)),'SQLERRM:'||SQLERRM);
6944: IF g_debug THEN
6941: fnd_date.date_to_canonical(l_effective_start_date));
6942: l_error_message := fnd_message.get;
6943: l_error_message := NVL(RTRIM(LTRIM(l_error_message)),'SQLERRM:'||SQLERRM);
6944: IF g_debug THEN
6945: hr_utility.trace(l_proc||' : Error msg '||l_error_message);
6946: END IF;
6947: hr_utility.set_message(8303,'PQP_230514_FTE_FAILURE');
6948: fnd_message.set_token('ABVUOM',l_abv_uom);
6949: fnd_message.set_token('ERRORMSG',l_error_message);
6943: l_error_message := NVL(RTRIM(LTRIM(l_error_message)),'SQLERRM:'||SQLERRM);
6944: IF g_debug THEN
6945: hr_utility.trace(l_proc||' : Error msg '||l_error_message);
6946: END IF;
6947: hr_utility.set_message(8303,'PQP_230514_FTE_FAILURE');
6948: fnd_message.set_token('ABVUOM',l_abv_uom);
6949: fnd_message.set_token('ERRORMSG',l_error_message);
6950: hr_utility.set_warning;
6951: end auto_calc_fte;
6946: END IF;
6947: hr_utility.set_message(8303,'PQP_230514_FTE_FAILURE');
6948: fnd_message.set_token('ABVUOM',l_abv_uom);
6949: fnd_message.set_token('ERRORMSG',l_error_message);
6950: hr_utility.set_warning;
6951: end auto_calc_fte;
6952:
6953: end hr_assignment_internal;