[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 >---------------------------|
194: from per_pay_proposal_components ppc
195: where ppc.pay_proposal_id = l_proposal_id;
196: -- End of 3202260
197: begin
198: hr_utility.set_location('Entering:'|| l_proc, 1);
199: --
200: -- Process Logic
201: --
202: -- Determine the datetrack mode to use for the assignment table handler
209: ,p_update => l_update
210: ,p_update_override => l_update_override
211: ,p_update_change_insert => l_update_change_insert
212: );
213: hr_utility.set_location(l_proc, 10);
214: --
215: if l_update_change_insert then
216: --
217: -- This is the case where there is a future dated assignment and
222: l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
223: l_asg_future_changes_warning := TRUE;
224: l_object_version_number := p_object_version_number;
225: l_future_records_flag := TRUE;
226: hr_utility.set_location(l_proc, 20);
227: elsif l_update then
228: --
229: l_datetrack_mode := 'UPDATE';
230: l_object_version_number := p_object_version_number;
227: elsif l_update then
228: --
229: l_datetrack_mode := 'UPDATE';
230: l_object_version_number := p_object_version_number;
231: hr_utility.set_location(l_proc, 30);
232: --
233: elsif l_correction then
234: --
235: hr_utility.set_location(l_proc, 40);
231: hr_utility.set_location(l_proc, 30);
232: --
233: elsif l_correction then
234: --
235: hr_utility.set_location(l_proc, 40);
236: --
237: -- We have the OVN for the assignment record which starts on ATD+1
238: -- so lock this one for termination.
239: --
250: ,p_object_version_number => l_object_version_number
251: ,p_validation_start_date => l_cor_validation_start_date
252: ,p_validation_end_date => l_cor_validation_end_date
253: );
254: hr_utility.set_location(l_proc, 50);
255: --
256: -- Find out if there changes after, the day
257: -- after the actual termination date
258: --
259: l_max_asg_end_date := get_max_asg_fut_change_end_dt
260: (p_assignment_id => p_assignment_id
261: ,p_effective_date => p_actual_termination_date + 1
262: );
263: hr_utility.set_location(l_proc, 70);
264: --
265: if l_max_asg_end_date is not null then
266: --
267: l_future_records_flag := TRUE;
264: --
265: if l_max_asg_end_date is not null then
266: --
267: l_future_records_flag := TRUE;
268: hr_utility.set_location(l_proc, 80);
269: end if;
270: --
271: hr_utility.set_location(l_proc, 110);
272: --
267: l_future_records_flag := TRUE;
268: hr_utility.set_location(l_proc, 80);
269: end if;
270: --
271: hr_utility.set_location(l_proc, 110);
272: --
273: l_datetrack_mode := 'CORRECTION';
274: l_asg_future_changes_warning := TRUE;
275: -- For correction the object_version_number has already been derived.
272: --
273: l_datetrack_mode := 'CORRECTION';
274: l_asg_future_changes_warning := TRUE;
275: -- For correction the object_version_number has already been derived.
276: hr_utility.set_location(l_proc, 120);
277: else
278: --
279: -- No other datetrack modes are valid, and so should not occur.
280: --
277: else
278: --
279: -- No other datetrack modes are valid, and so should not occur.
280: --
281: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
282: hr_utility.set_message_token('PROCEDURE', l_proc);
283: hr_utility.set_message_token('STEP','130');
284: hr_utility.raise_error;
285: end if;
278: --
279: -- No other datetrack modes are valid, and so should not occur.
280: --
281: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
282: hr_utility.set_message_token('PROCEDURE', l_proc);
283: hr_utility.set_message_token('STEP','130');
284: hr_utility.raise_error;
285: end if;
286: hr_utility.set_location(l_proc, 140);
279: -- No other datetrack modes are valid, and so should not occur.
280: --
281: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
282: hr_utility.set_message_token('PROCEDURE', l_proc);
283: hr_utility.set_message_token('STEP','130');
284: hr_utility.raise_error;
285: end if;
286: hr_utility.set_location(l_proc, 140);
287: --
280: --
281: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
282: hr_utility.set_message_token('PROCEDURE', l_proc);
283: hr_utility.set_message_token('STEP','130');
284: hr_utility.raise_error;
285: end if;
286: hr_utility.set_location(l_proc, 140);
287: --
288: -- Update employee assignment.
282: hr_utility.set_message_token('PROCEDURE', l_proc);
283: hr_utility.set_message_token('STEP','130');
284: hr_utility.raise_error;
285: end if;
286: hr_utility.set_location(l_proc, 140);
287: --
288: -- Update employee assignment.
289: --
290: per_asg_upd.upd
305: ,p_datetrack_mode => l_datetrack_mode
306: ,p_validate => FALSE
307: ,p_hourly_salaried_warning => l_hourly_salaried_warning
308: );
309: hr_utility.set_location(l_proc, 150);
310: --
311: -- If there are future dated records then we need to process these and set the
312: -- right assignment status.
313: --
343: -- Lock the appropriate child rows for this assignment.
344: --
345: open csr_lock_alu; -- Locking ladder processing order 1110
346: close csr_lock_alu;
347: hr_utility.set_location(l_proc, 160);
348: --
349: open csr_lock_asa; -- Locking ladder processing order 1190
350: close csr_lock_asa;
351: hr_utility.set_location(l_proc,170);
347: hr_utility.set_location(l_proc, 160);
348: --
349: open csr_lock_asa; -- Locking ladder processing order 1190
350: close csr_lock_asa;
351: hr_utility.set_location(l_proc,170);
352: --
353: open csr_lock_ele; -- Locking ladder processing order 1440
354: close csr_lock_ele;
355: hr_utility.set_location(l_proc, 180);
351: hr_utility.set_location(l_proc,170);
352: --
353: open csr_lock_ele; -- Locking ladder processing order 1440
354: close csr_lock_ele;
355: hr_utility.set_location(l_proc, 180);
356: --
357: open csr_lock_eev; -- Locking ladder processing order 1450
358: close csr_lock_eev;
359: hr_utility.set_location(l_proc, 190);
355: hr_utility.set_location(l_proc, 180);
356: --
357: open csr_lock_eev; -- Locking ladder processing order 1450
358: close csr_lock_eev;
359: hr_utility.set_location(l_proc, 190);
360: --
361: open csr_lock_pyp; -- Locking ladder processing order 1630
362: close csr_lock_pyp;
363: hr_utility.set_location(l_proc, 200);
359: hr_utility.set_location(l_proc, 190);
360: --
361: open csr_lock_pyp; -- Locking ladder processing order 1630
362: close csr_lock_pyp;
363: hr_utility.set_location(l_proc, 200);
364: --
365: -- Process any element entries and assignment_link_usages for this
366: -- assignment.
367: -- N.B. The procedure hrempter.terminate_entries_and_alus was procduced for
372: fetch csr_get_legislation_code into l_legislation_code;
373: if csr_get_legislation_code%NOTFOUND then
374: close csr_get_legislation_code;
375: -- This should never happen
376: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
377: hr_utility.set_message_token('PROCEDURE', l_proc);
378: hr_utility.set_message_token('STEP','210');
379: hr_utility.raise_error;
380: end if;
373: if csr_get_legislation_code%NOTFOUND then
374: close csr_get_legislation_code;
375: -- This should never happen
376: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
377: hr_utility.set_message_token('PROCEDURE', l_proc);
378: hr_utility.set_message_token('STEP','210');
379: hr_utility.raise_error;
380: end if;
381: close csr_get_legislation_code;
374: close csr_get_legislation_code;
375: -- This should never happen
376: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
377: hr_utility.set_message_token('PROCEDURE', l_proc);
378: hr_utility.set_message_token('STEP','210');
379: hr_utility.raise_error;
380: end if;
381: close csr_get_legislation_code;
382: hr_utility.set_location(l_proc, 220);
375: -- This should never happen
376: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
377: hr_utility.set_message_token('PROCEDURE', l_proc);
378: hr_utility.set_message_token('STEP','210');
379: hr_utility.raise_error;
380: end if;
381: close csr_get_legislation_code;
382: hr_utility.set_location(l_proc, 220);
383: --
378: hr_utility.set_message_token('STEP','210');
379: hr_utility.raise_error;
380: end if;
381: close csr_get_legislation_code;
382: hr_utility.set_location(l_proc, 220);
383: --
384: -- VT 10/07/96 bug #306710 added parameter in a call list
385: hrempter.terminate_entries_and_alus
386: (p_assignment_id => p_assignment_id
390: ,p_legislation_code => l_legislation_code
391: ,p_entries_changed_warning => l_entries_changed_warning
392: );
393: --
394: hr_utility.set_location(l_proc, 230);
395: --
396: -- Delete any pay proposals for this assignment that occur after the
397: -- actual termination date.
398: --
400: --
401: -- Start of fix 3202260
402: for rec_pay_prop in csr_pay_proposal loop
403: --
404: hr_utility.set_location(l_proc, 231);
405: --
406: for rec_prop_comp in csr_proposal_comp(rec_pay_prop.pay_proposal_id) loop
407: -- Calling the per_pay_proposal_components row handler to delete the
408: -- proposal components
406: for rec_prop_comp in csr_proposal_comp(rec_pay_prop.pay_proposal_id) loop
407: -- Calling the per_pay_proposal_components row handler to delete the
408: -- proposal components
409: --
410: hr_utility.set_location(l_proc, 232);
411: --
412: per_ppc_del.del(p_component_id => rec_prop_comp.component_id,
413: p_object_version_number => rec_prop_comp.object_version_number,
414: p_validation_strength => 'WEAK');
412: per_ppc_del.del(p_component_id => rec_prop_comp.component_id,
413: p_object_version_number => rec_prop_comp.object_version_number,
414: p_validation_strength => 'WEAK');
415: --
416: hr_utility.set_location(l_proc, 233);
417: --
418: end loop;
419: --
420: -- Now deleting the salary proposal
418: end loop;
419: --
420: -- Now deleting the salary proposal
421: --
422: hr_utility.set_location(l_proc, 234);
423: --
424: delete
425: from per_pay_proposals pyp
426: where pyp.pay_proposal_id = rec_pay_prop.pay_proposal_id;
432: l_pay_proposal_warning := TRUE;
433: end if;
434: --
435: --
436: hr_utility.set_location(l_proc, 235);
437: --
438: end loop;
439: -- End of fix 3202260
440: --
437: --
438: end loop;
439: -- End of fix 3202260
440: --
441: hr_utility.set_location(l_proc, 240);
442: --
443: if l_datetrack_mode = 'CORRECTION' then
444: --
445: -- Leave p_object_version_number set to its existing value, as it will
448: -- which were returned when the assignment row was locked.
449: --
450: p_effective_start_date := l_cor_validation_start_date;
451: p_effective_end_date := l_cor_validation_end_date;
452: hr_utility.set_location(l_proc, 250);
453: else
454: hr_utility.set_location(l_proc, 260);
455: --
456: -- When a different DateTrack mode is used, need to select the current
450: p_effective_start_date := l_cor_validation_start_date;
451: p_effective_end_date := l_cor_validation_end_date;
452: hr_utility.set_location(l_proc, 250);
453: else
454: hr_utility.set_location(l_proc, 260);
455: --
456: -- When a different DateTrack mode is used, need to select the current
457: -- object version number and effective dates. This is because the row
458: -- as of the actual termination date will have been modified.
463: , p_effective_end_date;
464: if csr_asg_values%notfound then
465: close csr_asg_values;
466: -- This should never happen.
467: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
468: hr_utility.set_message_token('PROCEDURE', l_proc);
469: hr_utility.set_message_token('STEP','270');
470: hr_utility.raise_error;
471: end if;
464: if csr_asg_values%notfound then
465: close csr_asg_values;
466: -- This should never happen.
467: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
468: hr_utility.set_message_token('PROCEDURE', l_proc);
469: hr_utility.set_message_token('STEP','270');
470: hr_utility.raise_error;
471: end if;
472: close csr_asg_values;
465: close csr_asg_values;
466: -- This should never happen.
467: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
468: hr_utility.set_message_token('PROCEDURE', l_proc);
469: hr_utility.set_message_token('STEP','270');
470: hr_utility.raise_error;
471: end if;
472: close csr_asg_values;
473: hr_utility.set_location(l_proc, 280);
466: -- This should never happen.
467: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
468: hr_utility.set_message_token('PROCEDURE', l_proc);
469: hr_utility.set_message_token('STEP','270');
470: hr_utility.raise_error;
471: end if;
472: close csr_asg_values;
473: hr_utility.set_location(l_proc, 280);
474: end if;
469: hr_utility.set_message_token('STEP','270');
470: hr_utility.raise_error;
471: end if;
472: close csr_asg_values;
473: hr_utility.set_location(l_proc, 280);
474: end if;
475: --
476: -- Set other output arguments
477: --
478: p_asg_future_changes_warning := l_asg_future_changes_warning;
479: p_entries_changed_warning := l_entries_changed_warning;
480: p_pay_proposal_warning := l_pay_proposal_warning;
481: --
482: hr_utility.set_location(' Leaving:'||l_proc, 300);
483: end actual_term_cwk_asg;
484: --
485: -- ----------------------------------------------------------------------------
486: -- |-----------------------< final_process_cwk_asg >--------------------------|
615: and p_final_process_Date between pgr.effective_start_date
616: and pgr.effective_end_date;
617: --
618: begin
619: hr_utility.set_location('Entering:'|| l_proc, 1);
620: --
621: l_object_version_number := p_object_version_number;
622: --
623: -- Validation in addition to Table Handlers
641: (p_assignment_id => p_assignment_id
642: ,p_effective_date => p_final_process_date
643: );
644: End if;
645: hr_utility.set_location(l_proc, 10);
646: --
647: if l_max_asg_end_date is not null then
648: --
649: l_asg_future_changes_warning := TRUE;
646: --
647: if l_max_asg_end_date is not null then
648: --
649: l_asg_future_changes_warning := TRUE;
650: hr_utility.set_location(l_proc, 20);
651: --
652: end if;
653: hr_utility.set_location(l_proc, 30);
654: --
649: l_asg_future_changes_warning := TRUE;
650: hr_utility.set_location(l_proc, 20);
651: --
652: end if;
653: hr_utility.set_location(l_proc, 30);
654: --
655: -- Lock the appropriate child rows for this assignment.
656: --
657: open csr_lock_csa; -- Locking ladder processing order 970
655: -- Lock the appropriate child rows for this assignment.
656: --
657: open csr_lock_csa; -- Locking ladder processing order 970
658: close csr_lock_csa;
659: hr_utility.set_location(l_proc, 40);
660: --
661: open csr_lock_alu; -- Locking ladder processing order 1110
662: close csr_lock_alu;
663: hr_utility.set_location(l_proc, 50);
659: hr_utility.set_location(l_proc, 40);
660: --
661: open csr_lock_alu; -- Locking ladder processing order 1110
662: close csr_lock_alu;
663: hr_utility.set_location(l_proc, 50);
664: --
665: open csr_lock_asa; -- Locking ladder processing order 1190
666: close csr_lock_asa;
667: hr_utility.set_location(l_proc,55);
663: hr_utility.set_location(l_proc, 50);
664: --
665: open csr_lock_asa; -- Locking ladder processing order 1190
666: close csr_lock_asa;
667: hr_utility.set_location(l_proc,55);
668: --
669: open csr_lock_ele; -- Locking ladder processing order 1440
670: close csr_lock_ele;
671: hr_utility.set_location(l_proc, 60);
667: hr_utility.set_location(l_proc,55);
668: --
669: open csr_lock_ele; -- Locking ladder processing order 1440
670: close csr_lock_ele;
671: hr_utility.set_location(l_proc, 60);
672: --
673: open csr_lock_eev; -- Locking ladder processing order 1450
674: close csr_lock_eev;
675: hr_utility.set_location(l_proc, 70);
671: hr_utility.set_location(l_proc, 60);
672: --
673: open csr_lock_eev; -- Locking ladder processing order 1450
674: close csr_lock_eev;
675: hr_utility.set_location(l_proc, 70);
676: --
677: open csr_lock_spp; -- Locking ladder processing order 1470
678: close csr_lock_spp;
679: hr_utility.set_location(l_proc, 80);
675: hr_utility.set_location(l_proc, 70);
676: --
677: open csr_lock_spp; -- Locking ladder processing order 1470
678: close csr_lock_spp;
679: hr_utility.set_location(l_proc, 80);
680: --
681: open csr_lock_ppm; -- Locking ladder processing order 1490
682: close csr_lock_ppm;
683: hr_utility.set_location(l_proc, 90);
679: hr_utility.set_location(l_proc, 80);
680: --
681: open csr_lock_ppm; -- Locking ladder processing order 1490
682: close csr_lock_ppm;
683: hr_utility.set_location(l_proc, 90);
684: --
685: --
686: open csr_lock_abv; -- Locking ladder processing order 1550
687: close csr_lock_abv;
684: --
685: --
686: open csr_lock_abv; -- Locking ladder processing order 1550
687: close csr_lock_abv;
688: hr_utility.set_location(l_proc, 115);
689:
690: --
691: open csr_lock_sas; -- Locking ladder processing order 1590
692: close csr_lock_sas;
689:
690: --
691: open csr_lock_sas; -- Locking ladder processing order 1590
692: close csr_lock_sas;
693: hr_utility.set_location(l_proc, 120);
694: --
695: open csr_lock_pyp; -- Locking ladder processing order 1630
696: close csr_lock_pyp;
697: --
694: --
695: open csr_lock_pyp; -- Locking ladder processing order 1630
696: close csr_lock_pyp;
697: --
698: hr_utility.set_location(l_proc, 130);
699: --
700: open csr_lock_asg_rates;
701: close csr_lock_asg_rates;
702: --
699: --
700: open csr_lock_asg_rates;
701: close csr_lock_asg_rates;
702: --
703: hr_utility.set_location(l_proc,135);
704: --
705: -- For the following tables, date effectively delete any rows which exist as
706: -- of the final process date, and ZAP any rows which start after the final
707: -- process date:
716: set sas.end_date = p_final_process_date
717: where sas.assignment_id = p_assignment_id
718: and sas.end_date IS NULL;
719: --
720: hr_utility.set_location(l_proc, 140);
721: --
722: delete per_secondary_ass_statuses sas
723: where sas.assignment_id = p_assignment_id
724: and sas.start_date > p_final_process_date;
722: delete per_secondary_ass_statuses sas
723: where sas.assignment_id = p_assignment_id
724: and sas.start_date > p_final_process_date;
725: --
726: hr_utility.set_location(l_proc, 150);
727: --
728: hr_utility.set_location(l_proc, 170);
729: --
730: update pay_cost_allocations_f pca
724: and sas.start_date > p_final_process_date;
725: --
726: hr_utility.set_location(l_proc, 150);
727: --
728: hr_utility.set_location(l_proc, 170);
729: --
730: update pay_cost_allocations_f pca
731: set pca.effective_end_date = p_final_process_date
732: where pca.assignment_id = p_assignment_id
732: where pca.assignment_id = p_assignment_id
733: and p_final_process_date between pca.effective_start_date
734: and pca.effective_end_date;
735: --
736: hr_utility.set_location(l_proc, 180);
737: --
738: delete pay_cost_allocations_f pca
739: where pca.assignment_id = p_assignment_id
740: and pca.effective_start_date > p_final_process_date;
738: delete pay_cost_allocations_f pca
739: where pca.assignment_id = p_assignment_id
740: and pca.effective_start_date > p_final_process_date;
741: --
742: hr_utility.set_location(l_proc, 190);
743: --
744: update per_spinal_point_placements_f spp
745: set spp.effective_end_date = p_final_process_date
746: where spp.assignment_id = p_assignment_id
746: where spp.assignment_id = p_assignment_id
747: and p_final_process_date between spp.effective_start_date
748: and spp.effective_end_date;
749: --
750: hr_utility.set_location(l_proc, 200);
751: --
752: delete per_spinal_point_placements_f spp
753: where spp.assignment_id = p_assignment_id
754: and spp.effective_start_date > p_final_process_date;
752: delete per_spinal_point_placements_f spp
753: where spp.assignment_id = p_assignment_id
754: and spp.effective_start_date > p_final_process_date;
755: --
756: hr_utility.set_location(l_proc, 210);
757:
758: --
759: -- SASmith date track of abv. 16-APR-1998
760:
763: where abv.assignment_id = p_assignment_id
764: and p_final_process_date between abv.effective_start_date
765: and abv.effective_end_date;
766: --
767: hr_utility.set_location(l_proc, 212);
768: --
769: delete per_assignment_budget_values_f abv
770: where abv.assignment_id = p_assignment_id
771: and abv.effective_start_date > p_final_process_date;
769: delete per_assignment_budget_values_f abv
770: where abv.assignment_id = p_assignment_id
771: and abv.effective_start_date > p_final_process_date;
772: --
773: hr_utility.set_location(l_proc, 214);
774: --
775: update pay_grade_rules_f pgr
776: set pgr.effective_end_date = p_final_process_date
777: where pgr.grade_or_spinal_point_id = p_assignment_id
778: and pgr.rate_type = 'A'
779: and p_final_process_date between pgr.effective_start_date
780: and pgr.effective_end_date;
781: --
782: hr_utility.set_location(l_proc,220);
783: --
784: delete pay_grade_rules_f pgr
785: where grade_or_spinal_point_Id = p_assignment_id
786: and pgr.rate_type = 'A'
785: where grade_or_spinal_point_Id = p_assignment_id
786: and pgr.rate_type = 'A'
787: and pgr.effective_start_Date > p_final_process_date;
788: --
789: hr_utility.set_location(l_proc,225);
790: --
791: -- Process any element entries and assignment_link_usages for this
792: -- assignment.
793: -- N.B. The procedure hrempter.terminate_entries_and_alus was procduced for
802: --
803: if csr_get_busgrp_legislation%NOTFOUND
804: then
805: --
806: hr_utility.set_location(l_proc, 230);
807: --
808: close csr_get_busgrp_legislation;
809: --
810: -- This should never happen!
808: close csr_get_busgrp_legislation;
809: --
810: -- This should never happen!
811: --
812: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
813: hr_utility.set_message_token('PROCEDURE', l_proc);
814: hr_utility.set_message_token('STEP','20');
815: hr_utility.raise_error;
816: end if;
809: --
810: -- This should never happen!
811: --
812: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
813: hr_utility.set_message_token('PROCEDURE', l_proc);
814: hr_utility.set_message_token('STEP','20');
815: hr_utility.raise_error;
816: end if;
817: --
810: -- This should never happen!
811: --
812: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
813: hr_utility.set_message_token('PROCEDURE', l_proc);
814: hr_utility.set_message_token('STEP','20');
815: hr_utility.raise_error;
816: end if;
817: --
818: close csr_get_busgrp_legislation;
811: --
812: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
813: hr_utility.set_message_token('PROCEDURE', l_proc);
814: hr_utility.set_message_token('STEP','20');
815: hr_utility.raise_error;
816: end if;
817: --
818: close csr_get_busgrp_legislation;
819: --
816: end if;
817: --
818: close csr_get_busgrp_legislation;
819: --
820: hr_utility.set_location(l_proc, 240);
821: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
822: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
823: 'DD-MON-yyyy'),99);
824: --
817: --
818: close csr_get_busgrp_legislation;
819: --
820: hr_utility.set_location(l_proc, 240);
821: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
822: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
823: 'DD-MON-yyyy'),99);
824: --
825: -- VT 10/07/96 bug #306710 added parameter in a call list
818: close csr_get_busgrp_legislation;
819: --
820: hr_utility.set_location(l_proc, 240);
821: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
822: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
823: 'DD-MON-yyyy'),99);
824: --
825: -- VT 10/07/96 bug #306710 added parameter in a call list
826: hrempter.terminate_entries_and_alus
832: ,p_entries_changed_warning => l_entries_changed_warning
833: );
834: --
835: --
836: hr_utility.set_location(l_proc, 250);
837: --
838:
839: -- Call the row handler to date effectively delete the rows
840: --
849: ,p_datetrack_mode => 'DELETE');
850:
851: end loop;
852: --
853: hr_utility.set_location(l_proc, 255);
854:
855: -- Call the row handler to zap rows
856:
857: for rec in csr_zap_ppm loop
867: end loop;
868:
869: -- Date effectively delete the assignment.
870: --
871: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
872: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
873: 'DD-MON-yyyy'),99);
874: per_asg_del.del
875: (p_assignment_id => p_assignment_id
868:
869: -- Date effectively delete the assignment.
870: --
871: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
872: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
873: 'DD-MON-yyyy'),99);
874: per_asg_del.del
875: (p_assignment_id => p_assignment_id
876: ,p_effective_start_date => l_effective_start_date
884: ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
885: );
886: --
887: --
888: hr_utility.set_location(l_proc, 260);
889: --
890: -- Set all output arguments
891: --
892: p_asg_future_changes_warning := l_asg_future_changes_warning;
895: p_entries_changed_warning := l_entries_changed_warning;
896: p_object_version_number := l_object_version_number;
897: p_org_now_no_manager_warning := l_org_now_no_manager_warning;
898: --
899: hr_utility.set_location(' Leaving:'||l_proc, 400);
900: end final_process_cwk_asg;
901: --
902: -- 115.66 (START)
903: --
1082: l_object_version_number1 per_assignments_f.object_version_number%TYPE;*/
1083: --end of bug 5026287
1084:
1085: begin
1086: hr_utility.set_location('Entering:'|| l_proc, 1);
1087: --
1088: -- Process Logic
1089: --
1090: -- Determine the datetrack mode to use for the assignment table handler
1098: , l_effective_end_date1;
1099: if csr_asg_values%notfound then
1100: close csr_asg_values;
1101: -- This should never happen.
1102: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1103: hr_utility.set_message_token('PROCEDURE', l_proc);
1104: hr_utility.set_message_token('STEP','270');
1105: hr_utility.raise_error;
1106: end if;
1099: if csr_asg_values%notfound then
1100: close csr_asg_values;
1101: -- This should never happen.
1102: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1103: hr_utility.set_message_token('PROCEDURE', l_proc);
1104: hr_utility.set_message_token('STEP','270');
1105: hr_utility.raise_error;
1106: end if;
1107: close csr_asg_values;*/
1100: close csr_asg_values;
1101: -- This should never happen.
1102: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1103: hr_utility.set_message_token('PROCEDURE', l_proc);
1104: hr_utility.set_message_token('STEP','270');
1105: hr_utility.raise_error;
1106: end if;
1107: close csr_asg_values;*/
1108: /* commented the changes made by bug 5026287*/
1101: -- This should never happen.
1102: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1103: hr_utility.set_message_token('PROCEDURE', l_proc);
1104: hr_utility.set_message_token('STEP','270');
1105: hr_utility.raise_error;
1106: end if;
1107: close csr_asg_values;*/
1108: /* commented the changes made by bug 5026287*/
1109: /*if p_actual_termination_date = l_effective_start_date1 then
1137: );
1138: --end of bug 5026287
1139:
1140:
1141: hr_utility.set_location(l_proc, 10);
1142: --
1143: if l_update_change_insert then
1144: --
1145: -- This is the case where there is a future dated assignment and
1150: l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
1151: l_asg_future_changes_warning := TRUE;
1152: l_object_version_number := p_object_version_number;
1153: l_future_records_flag := TRUE;
1154: hr_utility.set_location(l_proc, 20);
1155: elsif l_update then
1156: --
1157: l_datetrack_mode := 'UPDATE';
1158: l_object_version_number := p_object_version_number;
1155: elsif l_update then
1156: --
1157: l_datetrack_mode := 'UPDATE';
1158: l_object_version_number := p_object_version_number;
1159: hr_utility.set_location(l_proc, 30);
1160: --
1161: elsif l_correction then
1162: --
1163: hr_utility.set_location(l_proc, 40);
1159: hr_utility.set_location(l_proc, 30);
1160: --
1161: elsif l_correction then
1162: --
1163: hr_utility.set_location(l_proc, 40);
1164: --
1165: -- We have the OVN for the assignment record which starts on ATD+1
1166: -- so lock this one for termination.
1167: --
1178: ,p_object_version_number => l_object_version_number
1179: ,p_validation_start_date => l_cor_validation_start_date
1180: ,p_validation_end_date => l_cor_validation_end_date
1181: );
1182: hr_utility.set_location(l_proc, 50);
1183: --
1184: -- Find out if there changes after, the day
1185: -- after the actual termination date
1186: --
1187: l_max_asg_end_date := get_max_asg_fut_change_end_dt
1188: (p_assignment_id => p_assignment_id
1189: ,p_effective_date => p_actual_termination_date + 1
1190: );
1191: hr_utility.set_location(l_proc, 70);
1192: --
1193: if l_max_asg_end_date is not null then
1194: --
1195: l_future_records_flag := TRUE;
1192: --
1193: if l_max_asg_end_date is not null then
1194: --
1195: l_future_records_flag := TRUE;
1196: hr_utility.set_location(l_proc, 80);
1197: end if;
1198: --
1199: hr_utility.set_location(l_proc, 110);
1200: --
1195: l_future_records_flag := TRUE;
1196: hr_utility.set_location(l_proc, 80);
1197: end if;
1198: --
1199: hr_utility.set_location(l_proc, 110);
1200: --
1201: l_datetrack_mode := 'CORRECTION';
1202: l_asg_future_changes_warning := TRUE;
1203: -- For correction the object_version_number has already been derived.
1200: --
1201: l_datetrack_mode := 'CORRECTION';
1202: l_asg_future_changes_warning := TRUE;
1203: -- For correction the object_version_number has already been derived.
1204: hr_utility.set_location(l_proc, 120);
1205: else
1206: --
1207: -- No other datetrack modes are valid, and so should not occur.
1208: --
1205: else
1206: --
1207: -- No other datetrack modes are valid, and so should not occur.
1208: --
1209: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1210: hr_utility.set_message_token('PROCEDURE', l_proc);
1211: hr_utility.set_message_token('STEP','130');
1212: hr_utility.raise_error;
1213: end if;
1206: --
1207: -- No other datetrack modes are valid, and so should not occur.
1208: --
1209: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1210: hr_utility.set_message_token('PROCEDURE', l_proc);
1211: hr_utility.set_message_token('STEP','130');
1212: hr_utility.raise_error;
1213: end if;
1214: hr_utility.set_location(l_proc, 140);
1207: -- No other datetrack modes are valid, and so should not occur.
1208: --
1209: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1210: hr_utility.set_message_token('PROCEDURE', l_proc);
1211: hr_utility.set_message_token('STEP','130');
1212: hr_utility.raise_error;
1213: end if;
1214: hr_utility.set_location(l_proc, 140);
1215: --
1208: --
1209: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1210: hr_utility.set_message_token('PROCEDURE', l_proc);
1211: hr_utility.set_message_token('STEP','130');
1212: hr_utility.raise_error;
1213: end if;
1214: hr_utility.set_location(l_proc, 140);
1215: --
1216: -- Update employee assignment.
1210: hr_utility.set_message_token('PROCEDURE', l_proc);
1211: hr_utility.set_message_token('STEP','130');
1212: hr_utility.raise_error;
1213: end if;
1214: hr_utility.set_location(l_proc, 140);
1215: --
1216: -- Update employee assignment.
1217: --
1218: per_asg_upd.upd
1233: ,p_datetrack_mode => l_datetrack_mode
1234: ,p_validate => FALSE
1235: ,p_hourly_salaried_warning => l_hourly_salaried_warning
1236: );
1237: hr_utility.set_location(l_proc, 150);
1238: --
1239: -- If there are future dated records then we need to process these and set the
1240: -- right assignment status.
1241: --
1271: -- Lock the appropriate child rows for this assignment.
1272: --
1273: open csr_lock_alu; -- Locking ladder processing order 1110
1274: close csr_lock_alu;
1275: hr_utility.set_location(l_proc, 160);
1276: --
1277: open csr_lock_asa; -- Locking ladder processing order 1190
1278: close csr_lock_asa;
1279: hr_utility.set_location(l_proc,170);
1275: hr_utility.set_location(l_proc, 160);
1276: --
1277: open csr_lock_asa; -- Locking ladder processing order 1190
1278: close csr_lock_asa;
1279: hr_utility.set_location(l_proc,170);
1280: --
1281: open csr_lock_ele; -- Locking ladder processing order 1440
1282: close csr_lock_ele;
1283: hr_utility.set_location(l_proc, 180);
1279: hr_utility.set_location(l_proc,170);
1280: --
1281: open csr_lock_ele; -- Locking ladder processing order 1440
1282: close csr_lock_ele;
1283: hr_utility.set_location(l_proc, 180);
1284: --
1285: open csr_lock_eev; -- Locking ladder processing order 1450
1286: close csr_lock_eev;
1287: hr_utility.set_location(l_proc, 190);
1283: hr_utility.set_location(l_proc, 180);
1284: --
1285: open csr_lock_eev; -- Locking ladder processing order 1450
1286: close csr_lock_eev;
1287: hr_utility.set_location(l_proc, 190);
1288: --
1289: open csr_lock_pyp; -- Locking ladder processing order 1630
1290: close csr_lock_pyp;
1291: hr_utility.set_location(l_proc, 200);
1287: hr_utility.set_location(l_proc, 190);
1288: --
1289: open csr_lock_pyp; -- Locking ladder processing order 1630
1290: close csr_lock_pyp;
1291: hr_utility.set_location(l_proc, 200);
1292: --
1293: -- Process any element entries and assignment_link_usages for this
1294: -- assignment.
1295: -- N.B. The procedure hrempter.terminate_entries_and_alus was procduced for
1300: fetch csr_get_legislation_code into l_legislation_code;
1301: if csr_get_legislation_code%NOTFOUND then
1302: close csr_get_legislation_code;
1303: -- This should never happen
1304: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1305: hr_utility.set_message_token('PROCEDURE', l_proc);
1306: hr_utility.set_message_token('STEP','210');
1307: hr_utility.raise_error;
1308: end if;
1301: if csr_get_legislation_code%NOTFOUND then
1302: close csr_get_legislation_code;
1303: -- This should never happen
1304: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1305: hr_utility.set_message_token('PROCEDURE', l_proc);
1306: hr_utility.set_message_token('STEP','210');
1307: hr_utility.raise_error;
1308: end if;
1309: close csr_get_legislation_code;
1302: close csr_get_legislation_code;
1303: -- This should never happen
1304: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1305: hr_utility.set_message_token('PROCEDURE', l_proc);
1306: hr_utility.set_message_token('STEP','210');
1307: hr_utility.raise_error;
1308: end if;
1309: close csr_get_legislation_code;
1310: hr_utility.set_location(l_proc, 220);
1303: -- This should never happen
1304: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1305: hr_utility.set_message_token('PROCEDURE', l_proc);
1306: hr_utility.set_message_token('STEP','210');
1307: hr_utility.raise_error;
1308: end if;
1309: close csr_get_legislation_code;
1310: hr_utility.set_location(l_proc, 220);
1311: --
1306: hr_utility.set_message_token('STEP','210');
1307: hr_utility.raise_error;
1308: end if;
1309: close csr_get_legislation_code;
1310: hr_utility.set_location(l_proc, 220);
1311: --
1312: -- VT 10/07/96 bug #306710 added parameter in a call list
1313: hrempter.terminate_entries_and_alus
1314: (p_assignment_id => p_assignment_id
1332: --
1333: -- 115.66 (END)
1334: --
1335: --
1336: hr_utility.set_location(l_proc, 230);
1337: --
1338: -- Delete any pay proposals for this assignment that occur after the
1339: -- actual termination date.
1340: --
1342: --
1343: -- Start of fix 3202260
1344: for rec_pay_prop in csr_pay_proposal loop
1345: --
1346: hr_utility.set_location(l_proc, 231);
1347: --
1348: for rec_prop_comp in csr_proposal_comp(rec_pay_prop.pay_proposal_id) loop
1349: -- Calling the per_pay_proposal_components row handler to delete the
1350: -- proposal components
1348: for rec_prop_comp in csr_proposal_comp(rec_pay_prop.pay_proposal_id) loop
1349: -- Calling the per_pay_proposal_components row handler to delete the
1350: -- proposal components
1351: --
1352: hr_utility.set_location(l_proc, 232);
1353: --
1354: per_ppc_del.del(p_component_id => rec_prop_comp.component_id,
1355: p_object_version_number => rec_prop_comp.object_version_number,
1356: p_validation_strength => 'WEAK',
1355: p_object_version_number => rec_prop_comp.object_version_number,
1356: p_validation_strength => 'WEAK',
1357: p_validate => true);
1358: --
1359: hr_utility.set_location(l_proc, 233);
1360: --
1361: end loop;
1362: /* --
1363: -- Now deleting the salary proposal
1361: end loop;
1362: /* --
1363: -- Now deleting the salary proposal
1364: --
1365: hr_utility.set_location(l_proc, 234);
1366: --
1367: delete
1368: from per_pay_proposals pyp
1369: where pyp.pay_proposal_id = rec_pay_prop.pay_proposal_id;
1385: );
1386: l_pay_proposal_warning := TRUE;
1387: --End of fix for bug 4689950
1388: --
1389: hr_utility.set_location(l_proc, 235);
1390: --
1391: end loop;
1392: -- End of fix 3202260
1393: --
1390: --
1391: end loop;
1392: -- End of fix 3202260
1393: --
1394: hr_utility.set_location(l_proc, 240);
1395: --
1396: if l_datetrack_mode = 'CORRECTION' then
1397: --
1398: -- Leave p_object_version_number set to its existing value, as it will
1401: -- which were returned when the assignment row was locked.
1402: --
1403: p_effective_start_date := l_cor_validation_start_date;
1404: p_effective_end_date := l_cor_validation_end_date;
1405: hr_utility.set_location(l_proc, 250);
1406: else
1407: hr_utility.set_location(l_proc, 260);
1408: --
1409: -- When a different DateTrack mode is used, need to select the current
1403: p_effective_start_date := l_cor_validation_start_date;
1404: p_effective_end_date := l_cor_validation_end_date;
1405: hr_utility.set_location(l_proc, 250);
1406: else
1407: hr_utility.set_location(l_proc, 260);
1408: --
1409: -- When a different DateTrack mode is used, need to select the current
1410: -- object version number and effective dates. This is because the row
1411: -- as of the actual termination date will have been modified.
1416: , p_effective_end_date;
1417: if csr_asg_values%notfound then
1418: close csr_asg_values;
1419: -- This should never happen.
1420: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1421: hr_utility.set_message_token('PROCEDURE', l_proc);
1422: hr_utility.set_message_token('STEP','270');
1423: hr_utility.raise_error;
1424: end if;
1417: if csr_asg_values%notfound then
1418: close csr_asg_values;
1419: -- This should never happen.
1420: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1421: hr_utility.set_message_token('PROCEDURE', l_proc);
1422: hr_utility.set_message_token('STEP','270');
1423: hr_utility.raise_error;
1424: end if;
1425: close csr_asg_values;
1418: close csr_asg_values;
1419: -- This should never happen.
1420: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1421: hr_utility.set_message_token('PROCEDURE', l_proc);
1422: hr_utility.set_message_token('STEP','270');
1423: hr_utility.raise_error;
1424: end if;
1425: close csr_asg_values;
1426: hr_utility.set_location(l_proc, 280);
1419: -- This should never happen.
1420: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1421: hr_utility.set_message_token('PROCEDURE', l_proc);
1422: hr_utility.set_message_token('STEP','270');
1423: hr_utility.raise_error;
1424: end if;
1425: close csr_asg_values;
1426: hr_utility.set_location(l_proc, 280);
1427: end if;
1422: hr_utility.set_message_token('STEP','270');
1423: hr_utility.raise_error;
1424: end if;
1425: close csr_asg_values;
1426: hr_utility.set_location(l_proc, 280);
1427: end if;
1428: --
1429: -- Set other output arguments
1430: --
1431: p_asg_future_changes_warning := l_asg_future_changes_warning;
1432: p_entries_changed_warning := l_entries_changed_warning;
1433: p_pay_proposal_warning := l_pay_proposal_warning;
1434: --
1435: hr_utility.set_location(' Leaving:'||l_proc, 300);
1436: end actual_term_emp_asg_sup;
1437: --
1438: -- ----------------------------------------------------------------------------
1439: -- |---------------------------< delete_first_spp >---------------------------|
1490: loop
1491:
1492: l_datetrack_mode := 'DELETE_NEXT_CHANGE';
1493:
1494: hr_utility.set_location('l_effective_end_date :'||l_effective_end_date,25);
1495:
1496: if l_effective_end_date = p_validation_end_date then
1497: exit;
1498: end if;
1569: from per_business_groups bus
1570: where bus.business_group_id = p_business_group_id;
1571: --
1572: begin
1573: hr_utility.set_location('Entering:'|| l_proc, 1);
1574: --
1575: l_assignment_number := null;
1576: --
1577: -- Process Logic
1593: , l_legislation_code;
1594: --
1595: if csr_get_default_details%NOTFOUND then
1596: --
1597: hr_utility.set_location(l_proc, 10);
1598: --
1599: close csr_get_default_details;
1600: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1601: hr_utility.raise_error;
1596: --
1597: hr_utility.set_location(l_proc, 10);
1598: --
1599: close csr_get_default_details;
1600: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1601: hr_utility.raise_error;
1602: end if;
1603: close csr_get_default_details;
1604: --
1597: hr_utility.set_location(l_proc, 10);
1598: --
1599: close csr_get_default_details;
1600: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1601: hr_utility.raise_error;
1602: end if;
1603: close csr_get_default_details;
1604: --
1605: hr_utility.set_location(l_proc, 20);
1601: hr_utility.raise_error;
1602: end if;
1603: close csr_get_default_details;
1604: --
1605: hr_utility.set_location(l_proc, 20);
1606: --
1607: -- Create employee assignment.
1608: --
1609: hr_assignment_internal.create_emp_asg
1630: ,p_other_manager_warning => l_other_manager_warning
1631: ,p_validate_df_flex => false
1632: );
1633: --
1634: hr_utility.set_location(l_proc, 30);
1635: --
1636: -- Set remaining output arguments
1637: --
1638: p_assignment_number := l_assignment_number;
1636: -- Set remaining output arguments
1637: --
1638: p_assignment_number := l_assignment_number;
1639: --
1640: hr_utility.set_location(' Leaving:'||l_proc, 100);
1641: end create_default_emp_asg;
1642:
1643: --
1644: -- ----------------------------------------------------------------------------
1754: l_object_version_number per_assignments_f.object_version_number%TYPE;
1755:
1756: --
1757: begin
1758: hr_utility.set_location('Entering:'|| l_proc, 1);
1759: --
1760: l_assignment_status_type_id := p_assignment_status_type_id;
1761:
1762: hr_assignment_internal.create_emp_asg
1861: p_effective_start_date := l_effective_start_date;
1862: p_effective_end_date := l_effective_end_date;
1863: p_object_version_number := l_object_version_number ;
1864: --
1865: hr_utility.set_location(' Leaving:'||l_proc, 100);
1866: end create_emp_asg;
1867:
1868: --
1869: -- ----------------------------------------------------------------------------
1978: l_proc varchar2(72) := g_package||'create_emp_asg';
1979: l_labour_union_member_flag per_assignments_f.labour_union_member_flag%TYPE;
1980: --
1981: begin
1982: hr_utility.set_location('Entering:'|| l_proc, 1);
1983: --
1984: l_assignment_status_type_id := p_assignment_status_type_id;
1985: --
1986: -- fix for bug 4550165 starts here.
2006: ,p_legislation_code => p_legislation_code
2007: ,p_expected_system_status => 'ACTIVE_ASSIGN'
2008: );
2009: --
2010: hr_utility.set_location(l_proc, 10);
2011: --
2012: -- Insert per_assignments_f row.
2013: --
2014: per_asg_ins.ins
2108: ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
2109: ,p_supervisor_assignment_id => p_supervisor_assignment_id
2110: );
2111: --
2112: hr_utility.set_location(l_proc, 20);
2113: --
2114: -- Create standard element entries for this assignment.
2115: --
2116: hrentmnt.maintain_entries_asg
2127: ,p_validation_end_date => l_effective_end_date
2128: ,p_entries_changed => l_entries_changed
2129: );
2130: --
2131: hr_utility.set_location(l_proc, 30);
2132: --
2133: -- Create budget values for this assignment.
2134: -- 16-APR-1998 Change to include effective dates. SASmith
2135: --
2153: p_assignment_id := l_assignment_id;
2154: p_effective_start_date := l_effective_start_date;
2155: p_effective_end_date := l_effective_end_date;
2156: --
2157: hr_utility.set_location(' Leaving:'||l_proc, 100);
2158: end create_emp_asg;
2159:
2160: --
2161: -- ----------------------------------------------------------------------------
2297: and p_final_process_date between effective_start_date
2298: and effective_end_date;
2299:
2300: begin
2301: hr_utility.set_location('Entering:'|| l_proc, 1);
2302: --
2303: l_object_version_number := p_object_version_number;
2304: --
2305: -- Validation in addition to Table Handlers
2323: (p_assignment_id => p_assignment_id
2324: ,p_effective_date => p_final_process_date
2325: );
2326: End if;
2327: hr_utility.set_location(l_proc, 10);
2328: --
2329: if l_max_asg_end_date is not null then
2330: --
2331: l_asg_future_changes_warning := TRUE;
2328: --
2329: if l_max_asg_end_date is not null then
2330: --
2331: l_asg_future_changes_warning := TRUE;
2332: hr_utility.set_location(l_proc, 20);
2333: --
2334: end if;
2335: hr_utility.set_location(l_proc, 30);
2336: --
2331: l_asg_future_changes_warning := TRUE;
2332: hr_utility.set_location(l_proc, 20);
2333: --
2334: end if;
2335: hr_utility.set_location(l_proc, 30);
2336: --
2337: -- Lock the appropriate child rows for this assignment.
2338: --
2339: open csr_lock_csa; -- Locking ladder processing order 970
2337: -- Lock the appropriate child rows for this assignment.
2338: --
2339: open csr_lock_csa; -- Locking ladder processing order 970
2340: close csr_lock_csa;
2341: hr_utility.set_location(l_proc, 40);
2342: --
2343: open csr_lock_alu; -- Locking ladder processing order 1110
2344: close csr_lock_alu;
2345: hr_utility.set_location(l_proc, 50);
2341: hr_utility.set_location(l_proc, 40);
2342: --
2343: open csr_lock_alu; -- Locking ladder processing order 1110
2344: close csr_lock_alu;
2345: hr_utility.set_location(l_proc, 50);
2346: --
2347: open csr_lock_asa; -- Locking ladder processing order 1190
2348: close csr_lock_asa;
2349: hr_utility.set_location(l_proc,55);
2345: hr_utility.set_location(l_proc, 50);
2346: --
2347: open csr_lock_asa; -- Locking ladder processing order 1190
2348: close csr_lock_asa;
2349: hr_utility.set_location(l_proc,55);
2350: --
2351: open csr_lock_ele; -- Locking ladder processing order 1440
2352: close csr_lock_ele;
2353: hr_utility.set_location(l_proc, 60);
2349: hr_utility.set_location(l_proc,55);
2350: --
2351: open csr_lock_ele; -- Locking ladder processing order 1440
2352: close csr_lock_ele;
2353: hr_utility.set_location(l_proc, 60);
2354: --
2355: open csr_lock_eev; -- Locking ladder processing order 1450
2356: close csr_lock_eev;
2357: hr_utility.set_location(l_proc, 70);
2353: hr_utility.set_location(l_proc, 60);
2354: --
2355: open csr_lock_eev; -- Locking ladder processing order 1450
2356: close csr_lock_eev;
2357: hr_utility.set_location(l_proc, 70);
2358: --
2359: open csr_lock_spp; -- Locking ladder processing order 1470
2360: close csr_lock_spp;
2361: hr_utility.set_location(l_proc, 80);
2357: hr_utility.set_location(l_proc, 70);
2358: --
2359: open csr_lock_spp; -- Locking ladder processing order 1470
2360: close csr_lock_spp;
2361: hr_utility.set_location(l_proc, 80);
2362: --
2363: open csr_lock_ppm; -- Locking ladder processing order 1490
2364: close csr_lock_ppm;
2365: hr_utility.set_location(l_proc, 90);
2361: hr_utility.set_location(l_proc, 80);
2362: --
2363: open csr_lock_ppm; -- Locking ladder processing order 1490
2364: close csr_lock_ppm;
2365: hr_utility.set_location(l_proc, 90);
2366: --
2367: open csr_lock_abv; -- Locking ladder processing order 1550
2368: close csr_lock_abv;
2369: hr_utility.set_location(l_proc, 115);
2365: hr_utility.set_location(l_proc, 90);
2366: --
2367: open csr_lock_abv; -- Locking ladder processing order 1550
2368: close csr_lock_abv;
2369: hr_utility.set_location(l_proc, 115);
2370: --
2371: open csr_lock_sas; -- Locking ladder processing order 1590
2372: close csr_lock_sas;
2373: hr_utility.set_location(l_proc, 120);
2369: hr_utility.set_location(l_proc, 115);
2370: --
2371: open csr_lock_sas; -- Locking ladder processing order 1590
2372: close csr_lock_sas;
2373: hr_utility.set_location(l_proc, 120);
2374: --
2375: open csr_lock_pyp; -- Locking ladder processing order 1630
2376: close csr_lock_pyp;
2377: hr_utility.set_location(l_proc, 130);
2373: hr_utility.set_location(l_proc, 120);
2374: --
2375: open csr_lock_pyp; -- Locking ladder processing order 1630
2376: close csr_lock_pyp;
2377: hr_utility.set_location(l_proc, 130);
2378:
2379: --
2380: -- For the following tables, date effectively delete any rows which exist as
2381: -- of the final process date, and ZAP any rows which start after the final
2391: set sas.end_date = p_final_process_date
2392: where sas.assignment_id = p_assignment_id
2393: and sas.end_date IS NULL;
2394: --
2395: hr_utility.set_location(l_proc, 140);
2396: --
2397: delete per_secondary_ass_statuses sas
2398: where sas.assignment_id = p_assignment_id
2399: and sas.start_date > p_final_process_date;
2397: delete per_secondary_ass_statuses sas
2398: where sas.assignment_id = p_assignment_id
2399: and sas.start_date > p_final_process_date;
2400: --
2401: hr_utility.set_location(l_proc, 150);
2402: --
2403: hr_utility.set_location(l_proc, 170);
2404: --
2405: update pay_cost_allocations_f pca
2399: and sas.start_date > p_final_process_date;
2400: --
2401: hr_utility.set_location(l_proc, 150);
2402: --
2403: hr_utility.set_location(l_proc, 170);
2404: --
2405: update pay_cost_allocations_f pca
2406: set pca.effective_end_date = p_final_process_date
2407: where pca.assignment_id = p_assignment_id
2407: where pca.assignment_id = p_assignment_id
2408: and p_final_process_date between pca.effective_start_date
2409: and pca.effective_end_date;
2410: --
2411: hr_utility.set_location(l_proc, 180);
2412: --
2413: delete pay_cost_allocations_f pca
2414: where pca.assignment_id = p_assignment_id
2415: and pca.effective_start_date > p_final_process_date;
2413: delete pay_cost_allocations_f pca
2414: where pca.assignment_id = p_assignment_id
2415: and pca.effective_start_date > p_final_process_date;
2416: --
2417: hr_utility.set_location(l_proc, 190);
2418: --
2419: update per_spinal_point_placements_f spp
2420: set spp.effective_end_date = p_final_process_date
2421: where spp.assignment_id = p_assignment_id
2421: where spp.assignment_id = p_assignment_id
2422: and p_final_process_date between spp.effective_start_date
2423: and spp.effective_end_date;
2424: --
2425: hr_utility.set_location(l_proc, 200);
2426: --
2427: delete per_spinal_point_placements_f spp
2428: where spp.assignment_id = p_assignment_id
2429: and spp.effective_start_date > p_final_process_date;
2427: delete per_spinal_point_placements_f spp
2428: where spp.assignment_id = p_assignment_id
2429: and spp.effective_start_date > p_final_process_date;
2430: --
2431: hr_utility.set_location(l_proc, 210);
2432:
2433: --
2434: -- SASmith date track of abv. 16-APR-1998
2435:
2438: where abv.assignment_id = p_assignment_id
2439: and p_final_process_date between abv.effective_start_date
2440: and abv.effective_end_date;
2441: --
2442: hr_utility.set_location(l_proc, 212);
2443: --
2444: delete per_assignment_budget_values_f abv
2445: where abv.assignment_id = p_assignment_id
2446: and abv.effective_start_date > p_final_process_date;
2444: delete per_assignment_budget_values_f abv
2445: where abv.assignment_id = p_assignment_id
2446: and abv.effective_start_date > p_final_process_date;
2447: --
2448: hr_utility.set_location(l_proc, 214);
2449:
2450:
2451: --
2452: -- Process any element entries and assignment_link_usages for this
2463: --
2464: if csr_get_busgrp_legislation%NOTFOUND
2465: then
2466: --
2467: hr_utility.set_location(l_proc, 230);
2468: --
2469: close csr_get_busgrp_legislation;
2470: --
2471: -- This should never happen!
2469: close csr_get_busgrp_legislation;
2470: --
2471: -- This should never happen!
2472: --
2473: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2474: hr_utility.set_message_token('PROCEDURE', l_proc);
2475: hr_utility.set_message_token('STEP','20');
2476: hr_utility.raise_error;
2477: end if;
2470: --
2471: -- This should never happen!
2472: --
2473: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2474: hr_utility.set_message_token('PROCEDURE', l_proc);
2475: hr_utility.set_message_token('STEP','20');
2476: hr_utility.raise_error;
2477: end if;
2478: --
2471: -- This should never happen!
2472: --
2473: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2474: hr_utility.set_message_token('PROCEDURE', l_proc);
2475: hr_utility.set_message_token('STEP','20');
2476: hr_utility.raise_error;
2477: end if;
2478: --
2479: close csr_get_busgrp_legislation;
2472: --
2473: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2474: hr_utility.set_message_token('PROCEDURE', l_proc);
2475: hr_utility.set_message_token('STEP','20');
2476: hr_utility.raise_error;
2477: end if;
2478: --
2479: close csr_get_busgrp_legislation;
2480: --
2477: end if;
2478: --
2479: close csr_get_busgrp_legislation;
2480: --
2481: hr_utility.set_location(l_proc, 240);
2482: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2483: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2484: 'DD-MON-yyyy'),99);
2485: --
2478: --
2479: close csr_get_busgrp_legislation;
2480: --
2481: hr_utility.set_location(l_proc, 240);
2482: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2483: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2484: 'DD-MON-yyyy'),99);
2485: --
2486: -- VT 10/07/96 bug #306710 added parameter in a call list
2479: close csr_get_busgrp_legislation;
2480: --
2481: hr_utility.set_location(l_proc, 240);
2482: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2483: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2484: 'DD-MON-yyyy'),99);
2485: --
2486: -- VT 10/07/96 bug #306710 added parameter in a call list
2487: hrempter.terminate_entries_and_alus
2500: --
2501: );
2502: --
2503: --
2504: hr_utility.set_location(l_proc, 250);
2505: --
2506:
2507: -- Call the row handler to date effectively delete the rows
2508: --
2517: ,p_datetrack_mode => 'DELETE');
2518:
2519: end loop;
2520: --
2521: hr_utility.set_location(l_proc, 255);
2522:
2523: -- Call the row handler to zap rows
2524:
2525: for rec in csr_zap_ppm loop
2535: end loop;
2536:
2537: -- Date effectively delete the assignment.
2538: --
2539: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2540: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2541: 'DD-MON-yyyy'),99);
2542: --
2543: per_asg_del.del
2536:
2537: -- Date effectively delete the assignment.
2538: --
2539: hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2540: hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2541: 'DD-MON-yyyy'),99);
2542: --
2543: per_asg_del.del
2544: (p_assignment_id => p_assignment_id
2553: ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2554: );
2555: --
2556: --
2557: hr_utility.set_location(l_proc, 260);
2558: --
2559: -- Set all output arguments
2560: --
2561: p_asg_future_changes_warning := l_asg_future_changes_warning;
2564: p_entries_changed_warning := l_entries_changed_warning;
2565: p_object_version_number := l_object_version_number;
2566: p_org_now_no_manager_warning := l_org_now_no_manager_warning;
2567: --
2568: hr_utility.set_location(' Leaving:'||l_proc, 999);
2569: --
2570: end final_process_emp_asg_sup;
2571: --
2572: --
2593: ORDER BY placement_id;
2594: --
2595: BEGIN
2596: --
2597: hr_utility.set_location('Entering : '||l_proc,10);
2598: --
2599: l_previous_id := -1;
2600: --
2601: FOR c_spp_record IN csr_spp_records LOOP
2599: l_previous_id := -1;
2600: --
2601: FOR c_spp_record IN csr_spp_records LOOP
2602: --
2603: hr_utility.set_location(l_proc||'/'||c_spp_record.placement_id,20);
2604: hr_utility.set_location(l_proc||'/'||c_spp_record.object_version_number,21);
2605: hr_utility.set_location(l_proc||'/'||c_spp_record.effective_start_date,22);
2606: --
2607: IF l_previous_id <> c_spp_record.placement_id THEN
2600: --
2601: FOR c_spp_record IN csr_spp_records LOOP
2602: --
2603: hr_utility.set_location(l_proc||'/'||c_spp_record.placement_id,20);
2604: hr_utility.set_location(l_proc||'/'||c_spp_record.object_version_number,21);
2605: hr_utility.set_location(l_proc||'/'||c_spp_record.effective_start_date,22);
2606: --
2607: IF l_previous_id <> c_spp_record.placement_id THEN
2608: --
2601: FOR c_spp_record IN csr_spp_records LOOP
2602: --
2603: hr_utility.set_location(l_proc||'/'||c_spp_record.placement_id,20);
2604: hr_utility.set_location(l_proc||'/'||c_spp_record.object_version_number,21);
2605: hr_utility.set_location(l_proc||'/'||c_spp_record.effective_start_date,22);
2606: --
2607: IF l_previous_id <> c_spp_record.placement_id THEN
2608: --
2609: hr_utility.set_location(l_proc,30);
2605: hr_utility.set_location(l_proc||'/'||c_spp_record.effective_start_date,22);
2606: --
2607: IF l_previous_id <> c_spp_record.placement_id THEN
2608: --
2609: hr_utility.set_location(l_proc,30);
2610: --
2611: hr_sp_placement_api.delete_spp
2612: (p_effective_date => c_spp_record.effective_start_date
2613: ,p_datetrack_mode => hr_api.g_zap
2621: END IF;
2622: --
2623: END LOOP;
2624: --
2625: hr_utility.set_location('Leaving : '||l_proc,999);
2626: --
2627: END spp_zap;
2628: --
2629: -- ----------------------------------------------------------------------------
2672: ORDER BY effective_start_date DESC;
2673: --
2674: BEGIN
2675: --
2676: hr_utility.set_location('Entering : '||l_proc,10);
2677: --
2678: l_datetrack_mode := p_datetrack_mode;
2679: l_object_version_number := p_object_version_number;
2680: --
2684: FETCH csr_future_records INTO l_dummy_id;
2685: --
2686: IF csr_future_records%FOUND THEN
2687: --
2688: hr_utility.set_location(l_proc,20);
2689: --
2690: -- Loop through all SPP records that are linked to the
2691: -- assignment between the assignment record start and end
2692: -- date and perform a DELETE_NEXT_CHANGE on these records.
2715: -- maintain_spp_asg procedure.
2716: --
2717: FOR c1_rec IN csr_update_change_insert_rows LOOP
2718: --
2719: hr_utility.set_location(l_proc||'/'||c1_rec.object_version_number,30);
2720: hr_utility.set_location(l_proc||'/'||c1_rec.effective_start_date,31);
2721: --
2722: l_object_version_number := c1_rec.object_version_number;
2723: --
2716: --
2717: FOR c1_rec IN csr_update_change_insert_rows LOOP
2718: --
2719: hr_utility.set_location(l_proc||'/'||c1_rec.object_version_number,30);
2720: hr_utility.set_location(l_proc||'/'||c1_rec.effective_start_date,31);
2721: --
2722: l_object_version_number := c1_rec.object_version_number;
2723: --
2724: hr_sp_placement_api.delete_spp
2736: -- the date track mode to Correction.
2737: --
2738: IF p_spp_eff_start_date = p_validation_start_date THEN
2739: --
2740: hr_utility.set_location(l_proc,40);
2741: --
2742: l_datetrack_mode := 'CORRECTION';
2743: --
2744: ELSE
2742: l_datetrack_mode := 'CORRECTION';
2743: --
2744: ELSE
2745: --
2746: hr_utility.set_location(l_proc,50);
2747: --
2748: l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
2749: --
2750: END IF;
2753: -- set the datetrack mode
2754: --
2755: ELSE
2756: --
2757: hr_utility.set_location(l_proc,60);
2758: --
2759: -- Check if the step placement record starts on the same
2760: -- day as the updated assignment record. If it does then change
2761: -- the date track mode to Correction.
2761: -- the date track mode to Correction.
2762: --
2763: IF p_spp_eff_start_date = p_validation_start_date THEN
2764: --
2765: hr_utility.set_location(l_proc,70);
2766: --
2767: l_datetrack_mode := 'CORRECTION';
2768: --
2769: ELSE
2767: l_datetrack_mode := 'CORRECTION';
2768: --
2769: ELSE
2770: --
2771: hr_utility.set_location(l_proc,80);
2772: --
2773: l_datetrack_mode := 'UPDATE';
2774: --
2775: END IF;
2782: --
2783: p_object_version_number := l_object_version_number;
2784: p_datetrack_mode := l_datetrack_mode;
2785: --
2786: hr_utility.set_location('Leaving : '||l_proc,999);
2787: --
2788: END spp_update_change_insert;
2789: --
2790: -- ----------------------------------------------------------------------------
2810: --
2811: begin
2812: --
2813: if g_debug then
2814: hr_utility.set_location('Entering : '||l_proc, 10);
2815: end if;
2816: -- If there are future SPP records that have
2817: -- a different placement id then raise an error
2818: open csr_invalid_placement_id;
2820: if csr_invalid_placement_id%found then
2821: --
2822: close csr_invalid_placement_id;
2823: --
2824: hr_utility.set_message(800, 'HR_289827_SPP_FUTURE_SPP_REC');
2825: hr_utility.raise_error;
2826: --
2827: else
2828: --
2821: --
2822: close csr_invalid_placement_id;
2823: --
2824: hr_utility.set_message(800, 'HR_289827_SPP_FUTURE_SPP_REC');
2825: hr_utility.raise_error;
2826: --
2827: else
2828: --
2829: close csr_invalid_placement_id;
2830: --
2831: end if;
2832: --
2833: if g_debug then
2834: hr_utility.set_location('Leaving : '||l_proc, 99);
2835: end if;
2836: --
2837: end;
2838: --
2911: --
2912: begin
2913: --
2914: if g_debug then
2915: hr_utility.set_location('Entering : '||l_proc, 10);
2916: end if;
2917: -- All Non valid future SPP records needs to be deleted(if any)
2918: for csr_asg_rec in csr_asg_details loop
2919: -- If Grade Id is already set to Null then we should delete the corresponding
2920: -- Grade Step records.
2921: if csr_asg_rec.grade_id is null then
2922: --
2923: if g_debug then
2924: hr_utility.set_location(l_proc, 20);
2925: end if;
2926: -- Needs to be deleted all non valid SPP records
2927: for asg_spp_rec in csr_asg_spp(csr_asg_rec.effective_start_date,
2928: csr_asg_rec.effective_end_date) loop
2932: and spp.effective_start_date = asg_spp_rec.effective_start_date
2933: and spp.effective_end_date = asg_spp_rec.effective_end_date;
2934: --
2935: if g_debug then
2936: hr_utility.set_location(l_proc, 30);
2937: end if;
2938: -- Setting the warning parameter
2939: p_del_end_future_spp := true;
2940: --
2945: for rec_spp_placement in csr_spp_placement(csr_asg_rec.effective_start_date,
2946: csr_asg_rec.effective_end_date) loop
2947: -- Placement is with an invalid Grade, whcih needs to be deleted
2948: if g_debug then
2949: hr_utility.set_location(l_proc||' Placement Grade Id '||rec_spp_placement.grade_id , 40);
2950: hr_utility.set_location(l_proc||' Asg Grade Id '||csr_asg_rec.grade_id , 50);
2951: end if;
2952: if rec_spp_placement.grade_id <> csr_asg_rec.grade_id then
2953: --
2946: csr_asg_rec.effective_end_date) loop
2947: -- Placement is with an invalid Grade, whcih needs to be deleted
2948: if g_debug then
2949: hr_utility.set_location(l_proc||' Placement Grade Id '||rec_spp_placement.grade_id , 40);
2950: hr_utility.set_location(l_proc||' Asg Grade Id '||csr_asg_rec.grade_id , 50);
2951: end if;
2952: if rec_spp_placement.grade_id <> csr_asg_rec.grade_id then
2953: --
2954: delete from per_spinal_point_placements_f spp
2956: and spp.effective_start_date = rec_spp_placement.effective_start_date
2957: and spp.effective_end_date = rec_spp_placement.effective_end_date;
2958: --
2959: if g_debug then
2960: hr_utility.set_location(l_proc, 60);
2961: end if;
2962: -- Setting the warning parameter
2963: p_del_end_future_spp := true;
2964: --
2983: nvl(csr_asg_rec.grade_id, hr_api.g_number) then
2984: --
2985: close csr_asg_spp_error;
2986: --
2987: hr_utility.set_message(800, 'HR_289771_SPP_MIN_START_DATE');
2988: hr_utility.raise_error;
2989: --
2990: else
2991: --
2984: --
2985: close csr_asg_spp_error;
2986: --
2987: hr_utility.set_message(800, 'HR_289771_SPP_MIN_START_DATE');
2988: hr_utility.raise_error;
2989: --
2990: else
2991: --
2992: close csr_asg_spp_error;
3005: where spp.placement_id = spp_rec.placement_id
3006: and spp.effective_start_date = spp_rec.effective_start_date
3007: and spp.effective_end_date = spp_rec.effective_end_date;
3008: if g_debug then
3009: hr_utility.set_location(l_proc, 70);
3010: end if;
3011: -- Setting the warning parameter
3012: p_del_end_future_spp := true;
3013: --
3014: end loop;
3015: --
3016: end if;
3017: if g_debug then
3018: hr_utility.set_location('Leaving : '||l_proc, 99);
3019: end if;
3020: --
3021: end cleanup_spp;
3022: --
3089: --
3090: begin
3091: --
3092: if g_debug then
3093: hr_utility.set_location('Entering : '||l_proc, 10);
3094: end if;
3095: -- Needs to perform the delete next change operation only, if there is
3096: -- Grade change after the assignment level DNC. (Not null to a new not null)
3097: open csr_spp_grade;
3101: if p_grade_id <> l_grade_id then
3102: -- If there are future date SPP records that have
3103: -- a different placement id then raise an error
3104: if g_debug then
3105: hr_utility.set_location(l_proc, 20);
3106: end if;
3107: --
3108: -- At validation end date check for continues placement record.
3109: -- If found, just break it.
3107: --
3108: -- At validation end date check for continues placement record.
3109: -- If found, just break it.
3110: --
3111: hr_utility.set_location(l_proc, 30);
3112: open csr_ved_continues_spp;
3113: fetch csr_ved_continues_spp into
3114: l_placement_id,l_effective_start_date
3115: ,l_effective_end_date,l_object_version_number;
3130: --
3131: -- continues placement record found on validation end date.
3132: -- Therefore simply break it on validation end date.
3133: --
3134: hr_utility.set_location('ved spp found', 40);
3135: hr_utility.set_location('dt mode :'||l_datetrack_mode, 40);
3136: hr_sp_placement_api.update_spp(
3137: p_effective_date => p_validation_end_date+1
3138: ,p_datetrack_mode => l_datetrack_mode
3131: -- continues placement record found on validation end date.
3132: -- Therefore simply break it on validation end date.
3133: --
3134: hr_utility.set_location('ved spp found', 40);
3135: hr_utility.set_location('dt mode :'||l_datetrack_mode, 40);
3136: hr_sp_placement_api.update_spp(
3137: p_effective_date => p_validation_end_date+1
3138: ,p_datetrack_mode => l_datetrack_mode
3139: ,p_placement_id => l_placement_id
3150: --
3151: for csr_rec in csr_update_change_rows loop
3152: --
3153: if g_debug then
3154: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 30);
3155: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 40);
3156: hr_utility.set_location(l_proc||'/'||csr_rec.effective_end_date, 50);
3157: hr_utility.set_location(l_proc||'/'||csr_rec.placement_id, 60);
3158: end if;
3151: for csr_rec in csr_update_change_rows loop
3152: --
3153: if g_debug then
3154: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 30);
3155: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 40);
3156: hr_utility.set_location(l_proc||'/'||csr_rec.effective_end_date, 50);
3157: hr_utility.set_location(l_proc||'/'||csr_rec.placement_id, 60);
3158: end if;
3159: --
3152: --
3153: if g_debug then
3154: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 30);
3155: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 40);
3156: hr_utility.set_location(l_proc||'/'||csr_rec.effective_end_date, 50);
3157: hr_utility.set_location(l_proc||'/'||csr_rec.placement_id, 60);
3158: end if;
3159: --
3160: if p_validation_end_date > csr_rec.effective_end_date and
3153: if g_debug then
3154: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 30);
3155: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 40);
3156: hr_utility.set_location(l_proc||'/'||csr_rec.effective_end_date, 50);
3157: hr_utility.set_location(l_proc||'/'||csr_rec.placement_id, 60);
3158: end if;
3159: --
3160: if p_validation_end_date > csr_rec.effective_end_date and
3161: csr_rec.effective_end_date <> hr_api.g_eot then
3171: --
3172: l_del_end_future_spp := true;
3173: --
3174: if g_debug then
3175: hr_utility.set_location(l_proc, 70);
3176: end if;
3177: --
3178: end if;
3179: --
3188: -- Setting the out parameter
3189: p_del_end_future_spp := l_del_end_future_spp;
3190: --
3191: if g_debug then
3192: hr_utility.set_location('Leaving : '||l_proc, 99);
3193: end if;
3194: --
3195: end delete_next_change_spp;
3196: --
3230: --
3231: begin
3232: --
3233: if g_debug then
3234: hr_utility.set_location('Entering : '||l_proc, 10);
3235: end if;
3236: -- Check for the uniqueness of Step placement id
3237: chk_valid_placement_id(p_assignment_id => p_assignment_id
3238: ,p_placement_id => p_placement_id
3237: chk_valid_placement_id(p_assignment_id => p_assignment_id
3238: ,p_placement_id => p_placement_id
3239: ,p_validation_start_date => p_validation_start_date);
3240: if g_debug then
3241: hr_utility.set_location(l_proc, 20);
3242: end if;
3243: -- Check that there has been a grade step created for this assignment
3244: open csr_spp_details;
3245: fetch csr_spp_details into l_placement_id
3265: --
3266: l_del_end_future_spp := true;
3267: --
3268: if g_debug then
3269: hr_utility.set_location(l_proc, 30);
3270: end if;
3271: --
3272: end if;
3273: --
3273: --
3274: close csr_spp_details;
3275: --
3276: if g_debug then
3277: hr_utility.set_location(l_proc, 40);
3278: end if;
3279: -- Cleanup of all invalid steps(if any)
3280: cleanup_spp(p_assignment_id => p_assignment_id
3281: ,p_datetrack_mode => p_datetrack_mode
3283: ,p_del_end_future_spp => l_del_end_future_spp);
3284: -- Setting the out parameter
3285: p_del_end_future_spp := l_del_end_future_spp;
3286: if g_debug then
3287: hr_utility.set_location('Leaving : '||l_proc, 99);
3288: end if;
3289: --
3290: end future_change_spp;
3291: --
3366: --
3367: begin
3368: --
3369: if g_debug then
3370: hr_utility.set_location('Entering : '||l_proc, 10);
3371: end if;
3372: -- Assignment record is having some SPP records for this DT period
3373: if p_current_spp_exist then
3374: -- If the assignemnt record is having a Grade and that Grade has a Pay Scale
3394: -- User is swapping the Grade with a New Grade
3395: if p_grade_id <> l_grade_id then
3396: --
3397: if g_debug then
3398: hr_utility.set_location(l_proc, 20);
3399: end if;
3400: --
3401: open csr_future_records;
3402: fetch csr_future_records into l_dummy_id;
3404: -- As future records are existing, we can perform an UPDATE_OVERRIDE
3405: l_datetrack_mode := p_datetrack_mode;
3406: --
3407: if g_debug then
3408: hr_utility.set_location(l_proc||' /l_datetrack_mode '||l_datetrack_mode, 30);
3409: hr_utility.set_location(l_proc, 40);
3410: end if;
3411: --
3412: else
3405: l_datetrack_mode := p_datetrack_mode;
3406: --
3407: if g_debug then
3408: hr_utility.set_location(l_proc||' /l_datetrack_mode '||l_datetrack_mode, 30);
3409: hr_utility.set_location(l_proc, 40);
3410: end if;
3411: --
3412: else
3413: -- As there is no future records are existing.
3415: -- with the new Grade
3416: -- Performing a DT UPDATE
3417: l_datetrack_mode := hr_api.g_update;
3418: if g_debug then
3419: hr_utility.set_location(l_proc||' /l_datetrack_mode '||l_datetrack_mode, 50);
3420: hr_utility.set_location(l_proc, 60);
3421: end if;
3422: --
3423: end if;
3416: -- Performing a DT UPDATE
3417: l_datetrack_mode := hr_api.g_update;
3418: if g_debug then
3419: hr_utility.set_location(l_proc||' /l_datetrack_mode '||l_datetrack_mode, 50);
3420: hr_utility.set_location(l_proc, 60);
3421: end if;
3422: --
3423: end if;
3424: --
3438: -- Setting the warning parameter
3439: l_del_end_future_spp := true;
3440: --
3441: if g_debug then
3442: hr_utility.set_location(l_proc, 70);
3443: end if;
3444: -- No change in Grade
3445: else
3446: -- Needs to check if any future records are existing for the SPP records
3451: fetch csr_future_records into l_dummy_id;
3452: if csr_future_records%found then
3453: -- Since future records are existing then perfom an UPDATE_OVERRIDE
3454: if g_debug then
3455: hr_utility.set_location(l_proc||' /p_datetrack_mode '||p_datetrack_mode, 80);
3456: end if;
3457: -- As future records existing for the current SPP. And if the SPP
3458: -- effective start date and validation start date are same, then we
3459: -- cannot perform a DT UPDATE_OVERRIDE.
3461: --
3462: for csr_rec in csr_update_change_rows loop
3463: --
3464: if g_debug then
3465: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 90);
3466: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 100);
3467: end if;
3468: l_object_version_number := csr_rec.object_version_number;
3469: hr_sp_placement_api.delete_spp(
3462: for csr_rec in csr_update_change_rows loop
3463: --
3464: if g_debug then
3465: hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 90);
3466: hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 100);
3467: end if;
3468: l_object_version_number := csr_rec.object_version_number;
3469: hr_sp_placement_api.delete_spp(
3470: p_effective_date => csr_rec.effective_start_date
3476: -- Setting the warning parameter
3477: l_del_end_future_spp := true;
3478: --
3479: if g_debug then
3480: hr_utility.set_location(l_proc, 110);
3481: end if;
3482: --
3483: end loop;
3484: --
3498: -- Setting the warning parameter
3499: l_del_end_future_spp := true;
3500: --
3501: if g_debug then
3502: hr_utility.set_location(l_proc, 120);
3503: end if;
3504: -- We need to do extra process if the DT Mode is UPDATE_OVERRIDE
3505: -- Update SPP API will end date the current record with
3506: -- p_validation_start_date - 1 and insert a new record. Then the same
3517: ,p_object_version_number => l_object_version_number
3518: ,p_effective_start_date => l_effective_start_date
3519: ,p_effective_end_date => l_effective_end_date);
3520: if g_debug then
3521: hr_utility.set_location(l_proc, 130);
3522: end if;
3523: --
3524: end if;
3525: --
3543: fetch csr_past_spp_records into l_previous_ovn;
3544: if csr_past_spp_records%found then
3545: --
3546: if g_debug then
3547: hr_utility.set_location(l_proc, 140);
3548: hr_utility.set_location(l_proc||' ovn ='||l_previous_ovn, 150);
3549: end if;
3550: hr_sp_placement_api.delete_spp(
3551: p_effective_date => p_validation_start_date - 1
3544: if csr_past_spp_records%found then
3545: --
3546: if g_debug then
3547: hr_utility.set_location(l_proc, 140);
3548: hr_utility.set_location(l_proc||' ovn ='||l_previous_ovn, 150);
3549: end if;
3550: hr_sp_placement_api.delete_spp(
3551: p_effective_date => p_validation_start_date - 1
3552: ,p_datetrack_mode => hr_api.g_delete
3556: ,p_effective_end_date => l_effective_end_date);
3557: -- Setting the warning parameter
3558: l_del_end_future_spp := true;
3559: if g_debug then
3560: hr_utility.set_location(l_proc, 160);
3561: end if;
3562: --
3563: end if;
3564: --
3575: ,p_del_end_future_spp => l_del_end_future_spp);
3576: -- Setting the out parameter
3577: p_del_end_future_spp := l_del_end_future_spp;
3578: if g_debug then
3579: hr_utility.set_location('Leaving : '||l_proc, 999);
3580: end if;
3581: --
3582: end update_override_spp;
3583: --
3620: --
3621: begin
3622: --
3623: if g_debug then
3624: hr_utility.set_location('Entering : '||l_proc, 10);
3625: end if;
3626: -- Assignment record is having some SPP records for this DT period
3627: if p_current_spp_exist then
3628: -- Check for the uniqueness of Step placement id
3629: chk_valid_placement_id(p_assignment_id => p_assignment_id
3630: ,p_placement_id => p_placement_id
3631: ,p_validation_start_date => p_validation_start_date);
3632: if g_debug then
3633: hr_utility.set_location(l_proc, 20);
3634: end if;
3635: -- Check for future records SPP records if any for the same placement id
3636: open csr_spp_future_records;
3637: fetch csr_spp_future_records into l_dummy_id;
3636: open csr_spp_future_records;
3637: fetch csr_spp_future_records into l_dummy_id;
3638: if csr_spp_future_records%found then
3639: if g_debug then
3640: hr_utility.set_location(l_proc, 30);
3641: end if;
3642: -- If the future SPP changes are existing after the validation_start_date
3643: -- then we need to delete all future changes
3644: hr_sp_placement_api.delete_spp(
3651: -- Setting the warning parameter
3652: l_del_end_future_spp := true;
3653: --
3654: if g_debug then
3655: hr_utility.set_location(l_proc, 40);
3656: end if;
3657: --
3658: end if;
3659: --
3663: fetch csr_past_spp_records into l_object_version_number;
3664: if csr_past_spp_records%found then
3665: --
3666: if g_debug then
3667: hr_utility.set_location(l_proc, 50);
3668: hr_utility.set_location(l_proc||' ovn ='||l_object_version_number, 60);
3669: end if;
3670: --
3671: hr_sp_placement_api.delete_spp(
3664: if csr_past_spp_records%found then
3665: --
3666: if g_debug then
3667: hr_utility.set_location(l_proc, 50);
3668: hr_utility.set_location(l_proc||' ovn ='||l_object_version_number, 60);
3669: end if;
3670: --
3671: hr_sp_placement_api.delete_spp(
3672: p_effective_date => p_validation_start_date - 1
3678: -- Setting the warning parameter
3679: l_del_end_future_spp := true;
3680: --
3681: if g_debug then
3682: hr_utility.set_location(l_proc, 70);
3683: end if;
3684: --
3685: end if;
3686: --
3693: -- Setting the out parameter
3694: p_del_end_future_spp := l_del_end_future_spp;
3695: --
3696: if g_debug then
3697: hr_utility.set_location('Leaving : '||l_proc, 99);
3698: end if;
3699: --
3700: end close_spp_records;
3701: --
3777: --
3778: begin
3779: --
3780: if g_debug then
3781: hr_utility.set_location('Entering : '||l_proc, 10);
3782: end if;
3783: --
3784: l_min_step_id := p_min_step_id;
3785: --
3804:
3805: -- continues placement record found on validation end date.
3806: -- Therefore simply break it on validation end date.
3807: --
3808: hr_utility.set_location('ved spp found', 20);
3809: hr_utility.set_location('dt mode :'||l_datetrack_mode, 20);
3810: --
3811: l_del_end_future_spp := TRUE;
3812: --
3805: -- continues placement record found on validation end date.
3806: -- Therefore simply break it on validation end date.
3807: --
3808: hr_utility.set_location('ved spp found', 20);
3809: hr_utility.set_location('dt mode :'||l_datetrack_mode, 20);
3810: --
3811: l_del_end_future_spp := TRUE;
3812: --
3813: hr_sp_placement_api.update_spp(
3842: --
3843: -- continues placement record found on validation start date.
3844: -- Therefore simply break it on validation start date.
3845: --
3846: hr_utility.set_location('vsd spp found', 20);
3847: hr_utility.set_location('dt mode :'||l_datetrack_mode, 20);
3848: --
3849: l_del_end_future_spp := TRUE;
3850: --
3843: -- continues placement record found on validation start date.
3844: -- Therefore simply break it on validation start date.
3845: --
3846: hr_utility.set_location('vsd spp found', 20);
3847: hr_utility.set_location('dt mode :'||l_datetrack_mode, 20);
3848: --
3849: l_del_end_future_spp := TRUE;
3850: --
3851: hr_sp_placement_api.update_spp(
3865: chk_valid_placement_id(p_assignment_id => p_assignment_id
3866: ,p_placement_id => p_placement_id
3867: ,p_validation_start_date => p_validation_start_date);
3868: --
3869: hr_utility.set_location(l_proc,30);
3870: -- Now starting from the first spp record in the validation period, perform
3871: -- DELETE-NEXT-CHANGE to make all the spp records in the validation
3872: -- period as a single record.
3873: --
3872: -- period as a single record.
3873: --
3874: -- Get the first record details in the validation period.
3875: --
3876: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,399);
3877: --
3878: --fix for bug 5067855 starts here.
3879:
3880: open csr_next_spp(p_validation_start_date);
3882: LOOP
3883: fetch csr_next_spp into l_placement_id, l_effective_start_date,
3884: l_effective_end_date, l_object_version_number;
3885:
3886: hr_utility.set_location(l_proc,40);
3887: hr_utility.set_location('l_object_version_number'||l_object_version_number,199);
3888: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,199);
3889: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,199);
3890:
3883: fetch csr_next_spp into l_placement_id, l_effective_start_date,
3884: l_effective_end_date, l_object_version_number;
3885:
3886: hr_utility.set_location(l_proc,40);
3887: hr_utility.set_location('l_object_version_number'||l_object_version_number,199);
3888: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,199);
3889: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,199);
3890:
3891: if (l_effective_end_date >= p_validation_end_date or csr_next_spp%notfound ) then
3884: l_effective_end_date, l_object_version_number;
3885:
3886: hr_utility.set_location(l_proc,40);
3887: hr_utility.set_location('l_object_version_number'||l_object_version_number,199);
3888: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,199);
3889: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,199);
3890:
3891: if (l_effective_end_date >= p_validation_end_date or csr_next_spp%notfound ) then
3892: --
3885:
3886: hr_utility.set_location(l_proc,40);
3887: hr_utility.set_location('l_object_version_number'||l_object_version_number,199);
3888: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,199);
3889: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,199);
3890:
3891: if (l_effective_end_date >= p_validation_end_date or csr_next_spp%notfound ) then
3892: --
3893: hr_utility.set_location(l_proc,50);
3889: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,199);
3890:
3891: if (l_effective_end_date >= p_validation_end_date or csr_next_spp%notfound ) then
3892: --
3893: hr_utility.set_location(l_proc,50);
3894: close csr_next_spp;
3895: exit;
3896: --
3897: end if;
3900: --
3901: -- For further safety, perform the DNC only when the spp eed is
3902: -- less than the validation end date.
3903: --
3904: hr_utility.set_location(l_proc,60);
3905: if l_effective_end_date < p_validation_end_date then
3906: --
3907: -- ADD check to see if the SPP is not end dated in
3908: -- between the validation dates. DNC which will open up the end dated SPP.
3906: --
3907: -- ADD check to see if the SPP is not end dated in
3908: -- between the validation dates. DNC which will open up the end dated SPP.
3909: --
3910: hr_utility.set_location('performing delete_spp',70);
3911: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,399);
3912: --
3913: l_effective_date := l_effective_start_date;
3914: hr_sp_placement_api.delete_spp(
3907: -- ADD check to see if the SPP is not end dated in
3908: -- between the validation dates. DNC which will open up the end dated SPP.
3909: --
3910: hr_utility.set_location('performing delete_spp',70);
3911: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,399);
3912: --
3913: l_effective_date := l_effective_start_date;
3914: hr_sp_placement_api.delete_spp(
3915: p_effective_date => l_effective_date
3920: ,p_effective_end_date => l_effective_end_date);
3921: --
3922: l_del_end_future_spp := TRUE;
3923: --
3924: hr_utility.set_location('l_object_version_number'||l_object_version_number,299);
3925: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,299);
3926: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,299);
3927: --
3928: end if;
3921: --
3922: l_del_end_future_spp := TRUE;
3923: --
3924: hr_utility.set_location('l_object_version_number'||l_object_version_number,299);
3925: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,299);
3926: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,299);
3927: --
3928: end if;
3929: --
3922: l_del_end_future_spp := TRUE;
3923: --
3924: hr_utility.set_location('l_object_version_number'||l_object_version_number,299);
3925: hr_utility.set_location('l_effective_start_date'||l_effective_start_date,299);
3926: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,299);
3927: --
3928: end if;
3929: --
3930: hr_utility.set_location(l_proc,90);
3926: hr_utility.set_location('l_effective_end_date'||l_effective_end_date,299);
3927: --
3928: end if;
3929: --
3930: hr_utility.set_location(l_proc,90);
3931: END LOOP;
3932: --
3933: -- Now, update the spp in the validation period in CORRECTION mode.
3934: --
3947: end if;
3948: p_del_end_future_spp := l_del_end_future_spp;
3949: --
3950: if g_debug then
3951: hr_utility.set_location('Leaving : '||l_proc, 99);
3952: end if;
3953: --
3954: end correction_spp;
3955: --
4005: ORDER BY spp.effective_end_date desc;
4006: --
4007: BEGIN
4008: --
4009: hr_utility.set_location('Entering : '||l_proc, 10);
4010: --
4011: IF p_datetrack_mode = hr_api.g_correction THEN
4012: --
4013: hr_utility.set_location(l_proc, 20);
4009: hr_utility.set_location('Entering : '||l_proc, 10);
4010: --
4011: IF p_datetrack_mode = hr_api.g_correction THEN
4012: --
4013: hr_utility.set_location(l_proc, 20);
4014: --
4015: -- Check for previous SPP records
4016: --
4017: OPEN csr_past_spp_records;
4021: -- then ZAP the SPP record.
4022: --
4023: IF csr_past_spp_records%NOTFOUND THEN
4024: --
4025: hr_utility.set_location(l_proc, 30);
4026: --
4027: l_object_version_number := p_object_version_number;
4028: --
4029: hr_sp_placement_api.delete_spp
4038: -- then perform a DT Delete.
4039: --
4040: ELSE
4041: --
4042: hr_utility.set_location(l_proc, 40);
4043: --
4044: l_object_version_number := p_object_version_number;
4045: --
4046: OPEN csr_previous_spp_record;
4046: OPEN csr_previous_spp_record;
4047: FETCH csr_previous_spp_record INTO l_previous_ovn,
4048: l_previous_end_date;
4049: --
4050: hr_utility.set_location(l_proc||l_previous_ovn||'/'||l_previous_end_date, 50);
4051: --
4052: hr_sp_placement_api.delete_spp
4053: (p_effective_date => l_previous_end_date
4054: ,p_datetrack_mode => hr_api.g_delete
4062: -- If datetrack mode is not CORRECTION then
4063: --
4064: ELSE
4065: --
4066: hr_utility.set_location(l_proc, 50);
4067: --
4068: l_object_version_number := p_object_version_number;
4069: --
4070: hr_sp_placement_api.delete_spp
4076: ,p_effective_end_date => l_effective_end_date);
4077: --
4078: END IF;
4079: --
4080: hr_utility.set_location(l_proc, 60);
4081: --
4082: l_previous_id := -1;
4083: --
4084: -- Delete all future SPP records that have
4085: -- a different placement_id do the SPP record delete above
4086: --
4087: FOR c_future_spp IN csr_future_spp_records LOOP
4088: --
4089: hr_utility.set_location(l_proc||'/ pl_id = '||c_future_spp.placement_id, 70);
4090: hr_utility.set_location(l_proc||'/ ovn = '||c_future_spp.object_version_number, 71);
4091: --
4092: -- If the record retrieved has a different placement id
4093: -- then perform a ZAP on this record. If the ID is the same
4086: --
4087: FOR c_future_spp IN csr_future_spp_records LOOP
4088: --
4089: hr_utility.set_location(l_proc||'/ pl_id = '||c_future_spp.placement_id, 70);
4090: hr_utility.set_location(l_proc||'/ ovn = '||c_future_spp.object_version_number, 71);
4091: --
4092: -- If the record retrieved has a different placement id
4093: -- then perform a ZAP on this record. If the ID is the same
4094: -- as the previous id then do nothing as this record has already
4095: -- been deleted.
4096: --
4097: IF l_previous_id <> c_future_spp.placement_id THEN
4098: --
4099: hr_utility.set_location(l_proc, 80);
4100: --
4101: l_previous_id := c_future_spp.placement_id;
4102: l_object_version_number := c_future_spp.object_version_number;
4103: --
4112: END IF;
4113: --
4114: END LOOP;
4115: --
4116: hr_utility.set_location('Leaving : '||l_proc, 999);
4117: --
4118: END delete_future_spp_records;
4119: --
4120: -- ----------------------------------------------------------------------------
4213: --
4214: begin
4215: --
4216: if g_debug then
4217: hr_utility.set_location('Entering:'|| l_proc, 10);
4218: end if;
4219: -- Validation in addition to Table Handlers
4220: -- Check that all mandatory arguments are not null.
4221: hr_api.mandatory_arg_error
4238: p_argument => 'validation_end_date',
4239: p_argument_value => p_validation_end_date);
4240: -- Process Logic
4241: if g_debug then
4242: hr_utility.set_location(l_proc||' p_assignment_id = '||p_assignment_id, 15);
4243: hr_utility.set_location(l_proc||' p_datetrack_mode = '||p_datetrack_mode, 16);
4244: hr_utility.set_location(l_proc||' p_val_st_date = '||p_validation_start_date, 17);
4245: hr_utility.set_location(l_proc||' p_val_end_date = '||p_validation_end_date, 18);
4246: end if;
4239: p_argument_value => p_validation_end_date);
4240: -- Process Logic
4241: if g_debug then
4242: hr_utility.set_location(l_proc||' p_assignment_id = '||p_assignment_id, 15);
4243: hr_utility.set_location(l_proc||' p_datetrack_mode = '||p_datetrack_mode, 16);
4244: hr_utility.set_location(l_proc||' p_val_st_date = '||p_validation_start_date, 17);
4245: hr_utility.set_location(l_proc||' p_val_end_date = '||p_validation_end_date, 18);
4246: end if;
4247: -- Setting this variable as TRUE, because when we call SPP rhi, the validation proc
4240: -- Process Logic
4241: if g_debug then
4242: hr_utility.set_location(l_proc||' p_assignment_id = '||p_assignment_id, 15);
4243: hr_utility.set_location(l_proc||' p_datetrack_mode = '||p_datetrack_mode, 16);
4244: hr_utility.set_location(l_proc||' p_val_st_date = '||p_validation_start_date, 17);
4245: hr_utility.set_location(l_proc||' p_val_end_date = '||p_validation_end_date, 18);
4246: end if;
4247: -- Setting this variable as TRUE, because when we call SPP rhi, the validation proc
4248: -- per_spp_bus.chk_future_asg_changes should not be executed
4241: if g_debug then
4242: hr_utility.set_location(l_proc||' p_assignment_id = '||p_assignment_id, 15);
4243: hr_utility.set_location(l_proc||' p_datetrack_mode = '||p_datetrack_mode, 16);
4244: hr_utility.set_location(l_proc||' p_val_st_date = '||p_validation_start_date, 17);
4245: hr_utility.set_location(l_proc||' p_val_end_date = '||p_validation_end_date, 18);
4246: end if;
4247: -- Setting this variable as TRUE, because when we call SPP rhi, the validation proc
4248: -- per_spp_bus.chk_future_asg_changes should not be executed
4249: g_called_from_spp_asg := true;
4256: --
4257: close csr_lock_spp_rows;
4258: --
4259: if g_debug then
4260: hr_utility.set_location(l_proc, 20);
4261: end if;
4262: -- DT mode ZAP needs to processed seperately. Date effective SPP records
4263: -- check may not be valid for all the case.
4264: -- If the datetrack mode is ZAP,(This DT mode is allowed only for
4266: -- to this assignment from the database.
4267: if p_datetrack_mode = hr_api.g_zap then
4268: --
4269: if g_debug then
4270: hr_utility.set_location(l_proc, 30);
4271: hr_utility.set_location(l_proc||' DT Mode = '||p_datetrack_mode, 31);
4272: end if;
4273: --
4274: spp_zap (p_assignment_id => p_assignment_id);
4267: if p_datetrack_mode = hr_api.g_zap then
4268: --
4269: if g_debug then
4270: hr_utility.set_location(l_proc, 30);
4271: hr_utility.set_location(l_proc||' DT Mode = '||p_datetrack_mode, 31);
4272: end if;
4273: --
4274: spp_zap (p_assignment_id => p_assignment_id);
4275: -- All other DT mode, Date effective check that there has been a grade step
4284: --
4285: l_current_spp_exist := true;
4286: --
4287: if g_debug then
4288: hr_utility.set_location(l_proc||' Current SPP record exist', 32);
4289: end if;
4290: -- If there is NO current SPP records exists, then we need to
4291: -- check for future SPP records
4292: else
4297: --
4298: l_future_spp_exist := true;
4299: --
4300: if g_debug then
4301: hr_utility.set_location(l_proc||' Future SPP record exist', 33);
4302: end if;
4303: --
4304: end if;
4305: --
4322: ,p_validation_start_date => p_validation_start_date
4323: ,p_validation_end_date => p_validation_end_date
4324: ,p_del_end_future_spp => l_del_end_future_spp);
4325: if g_debug then
4326: hr_utility.set_location(l_proc, 60);
4327: end if;
4328: --
4329: elsif p_datetrack_mode = hr_api.g_future_change then
4330: --
4334: ,p_datetrack_mode => p_datetrack_mode
4335: ,p_validation_start_date => p_validation_start_date
4336: ,p_del_end_future_spp => l_del_end_future_spp);
4337: if g_debug then
4338: hr_utility.set_location(l_proc, 80);
4339: end if;
4340: -- Needs to perform DT modes like CORRECTION, UPDATE, UPDATE_CHANGE_INSERT,
4341: -- UPDATE_OVERRIDE, DELETE only if there is an SPP record exist for current DT
4342: else
4349: if csr_grade_pay_scale_defined%found then
4350: --
4351: l_pay_scale_defined := true;
4352: if g_debug then
4353: hr_utility.set_location(l_proc, 100);
4354: end if;
4355: open csr_new_grade_scale;
4356: fetch csr_new_grade_scale into l_min_step_id;
4357: -- If no steps exists on the effective date then raise an error
4358: if csr_new_grade_scale%notfound then
4359: --
4360: close csr_new_grade_scale;
4361: --
4362: hr_utility.set_message(800, 'HR_289829_NO_SPP_REC_FOR_EDATE');
4363: hr_utility.raise_error;
4364: --
4365: end if;
4366: --
4359: --
4360: close csr_new_grade_scale;
4361: --
4362: hr_utility.set_message(800, 'HR_289829_NO_SPP_REC_FOR_EDATE');
4363: hr_utility.raise_error;
4364: --
4365: end if;
4366: --
4367: close csr_new_grade_scale;
4366: --
4367: close csr_new_grade_scale;
4368: --
4369: if g_debug then
4370: hr_utility.set_location(l_proc||'/l_min_step_id = '||l_min_step_id, 250);
4371: end if;
4372: --
4373: /* else
4374: --
4371: end if;
4372: --
4373: /* else
4374: --
4375: hr_utility.set_message(800, 'HR_289829_NO_SPP_REC_FOR_EDATE');
4376: hr_utility.raise_error; */ /* commented for bug 6346478*/
4377:
4378: --
4379: end if;
4372: --
4373: /* else
4374: --
4375: hr_utility.set_message(800, 'HR_289829_NO_SPP_REC_FOR_EDATE');
4376: hr_utility.raise_error; */ /* commented for bug 6346478*/
4377:
4378: --
4379: end if;
4380: --
4415: Else
4416: --
4417: -- Inform user there are steps that they will have to delete before correcting.
4418: --
4419: hr_utility.set_message(800, 'HR_50426_REM_STEP_BEF_REM_GRD');
4420: hr_utility.raise_error;
4421: End if;
4422: --
4423: elsif p_datetrack_mode = hr_api.g_update then
4416: --
4417: -- Inform user there are steps that they will have to delete before correcting.
4418: --
4419: hr_utility.set_message(800, 'HR_50426_REM_STEP_BEF_REM_GRD');
4420: hr_utility.raise_error;
4421: End if;
4422: --
4423: elsif p_datetrack_mode = hr_api.g_update then
4424: -- Check for future records SPP records if any for the same
4445: -- Setting the warning parameter
4446: l_del_end_future_spp := true;
4447: --
4448: if g_debug then
4449: hr_utility.set_location(l_proc, 275);
4450: end if;
4451: l_datetrack_mode := hr_api.g_correction;
4452: --
4453: else
4462: -- date track mode to CORRECTION
4463: if l_spp_eff_start_date = p_validation_start_date then
4464: --
4465: if g_debug then
4466: hr_utility.set_location(l_proc, 300);
4467: end if;
4468: l_datetrack_mode := hr_api.g_correction;
4469: --
4470: else
4469: --
4470: else
4471: --
4472: if g_debug then
4473: hr_utility.set_location(l_proc, 310);
4474: end if;
4475: l_datetrack_mode := hr_api.g_update;
4476: --
4477: end if;
4480: --
4481: close csr_spp_future_records;
4482: --
4483: if g_debug then
4484: hr_utility.set_location(l_proc||' DT Mode = '||l_datetrack_mode, 320);
4485: end if;
4486: --
4487: elsif p_datetrack_mode = hr_api.g_delete then
4488: --
4498: open csr_min_spp_date;
4499: fetch csr_min_spp_date into l_min_spp_date;
4500: if l_min_spp_date > p_validation_start_date then
4501: --
4502: hr_utility.set_message(800, 'HR_289771_SPP_MIN_START_DATE');
4503: hr_utility.raise_error;
4504: --
4505: end if;
4506: --
4499: fetch csr_min_spp_date into l_min_spp_date;
4500: if l_min_spp_date > p_validation_start_date then
4501: --
4502: hr_utility.set_message(800, 'HR_289771_SPP_MIN_START_DATE');
4503: hr_utility.raise_error;
4504: --
4505: end if;
4506: --
4507: close csr_min_spp_date;
4558: -- The value should be FALSE, when maintain_app_asg is called through SPP rhi's.
4559: g_called_from_spp_asg := false;
4560: --
4561: if g_debug then
4562: hr_utility.set_location(' Leaving:'||l_proc, 999);
4563: end if;
4564: --
4565: exception
4566: --
4628: and asg.effective_end_date
4629: and bus.business_group_id+0 = asg.business_group_id;
4630: --
4631: begin
4632: hr_utility.set_location('Entering:'|| l_proc, 1);
4633: --
4634: l_assignment_status_type_id := p_assignment_status_type_id;
4635: l_object_version_number := p_object_version_number;
4636: --
4639: if p_expected_system_status <> 'ACTIVE_CWK'
4640: and p_expected_system_status <> 'SUSP_CWK_ASG'
4641: then
4642: --
4643: hr_utility.set_location(l_proc, 10);
4644: --
4645: hr_utility.set_message(800, 'HR_289693_ASG_INV_EXP_STATUS');
4646: hr_utility.raise_error;
4647: end if;
4641: then
4642: --
4643: hr_utility.set_location(l_proc, 10);
4644: --
4645: hr_utility.set_message(800, 'HR_289693_ASG_INV_EXP_STATUS');
4646: hr_utility.raise_error;
4647: end if;
4648: --
4649: hr_utility.set_location(l_proc, 20);
4642: --
4643: hr_utility.set_location(l_proc, 10);
4644: --
4645: hr_utility.set_message(800, 'HR_289693_ASG_INV_EXP_STATUS');
4646: hr_utility.raise_error;
4647: end if;
4648: --
4649: hr_utility.set_location(l_proc, 20);
4650: --
4645: hr_utility.set_message(800, 'HR_289693_ASG_INV_EXP_STATUS');
4646: hr_utility.raise_error;
4647: end if;
4648: --
4649: hr_utility.set_location(l_proc, 20);
4650: --
4651: -- Get assignment details.
4652: --
4653: hr_api.mandatory_arg_error
4659: (p_api_name => l_proc,
4660: p_argument => 'effective_date',
4661: p_argument_value => p_effective_date);
4662: --
4663: hr_utility.set_location(l_proc, 30);
4664: --
4665: open csr_get_asg_dets;
4666: fetch csr_get_asg_dets
4667: into l_assignment_type
4669: , l_legislation_code;
4670: --
4671: if csr_get_asg_dets%NOTFOUND then
4672: --
4673: hr_utility.set_location(l_proc, 40);
4674: --
4675: close csr_get_asg_dets;
4676: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4677: hr_utility.raise_error;
4672: --
4673: hr_utility.set_location(l_proc, 40);
4674: --
4675: close csr_get_asg_dets;
4676: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4677: hr_utility.raise_error;
4678: end if;
4679: --
4680: close csr_get_asg_dets;
4673: hr_utility.set_location(l_proc, 40);
4674: --
4675: close csr_get_asg_dets;
4676: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4677: hr_utility.raise_error;
4678: end if;
4679: --
4680: close csr_get_asg_dets;
4681: --
4678: end if;
4679: --
4680: close csr_get_asg_dets;
4681: --
4682: hr_utility.set_location(l_proc, 50);
4683: --
4684: if l_assignment_type <> 'C' then
4685: --
4686: -- Assignment is not an employee assignment.
4684: if l_assignment_type <> 'C' then
4685: --
4686: -- Assignment is not an employee assignment.
4687: --
4688: hr_utility.set_location(l_proc, 60);
4689: --
4690: hr_utility.set_message(800, 'HR_289616_ASG_NOT_CWK');
4691: hr_utility.raise_error;
4692: end if;
4686: -- Assignment is not an employee assignment.
4687: --
4688: hr_utility.set_location(l_proc, 60);
4689: --
4690: hr_utility.set_message(800, 'HR_289616_ASG_NOT_CWK');
4691: hr_utility.raise_error;
4692: end if;
4693: --
4694: hr_utility.set_location(l_proc, 70);
4687: --
4688: hr_utility.set_location(l_proc, 60);
4689: --
4690: hr_utility.set_message(800, 'HR_289616_ASG_NOT_CWK');
4691: hr_utility.raise_error;
4692: end if;
4693: --
4694: hr_utility.set_location(l_proc, 70);
4695: --
4690: hr_utility.set_message(800, 'HR_289616_ASG_NOT_CWK');
4691: hr_utility.raise_error;
4692: end if;
4693: --
4694: hr_utility.set_location(l_proc, 70);
4695: --
4696: -- Process Logic
4697: --
4698: -- If p_assignment_status_type_id is g_number then derive it's default value,
4704: ,p_legislation_code => l_legislation_code
4705: ,p_expected_system_status => p_expected_system_status
4706: );
4707: --
4708: hr_utility.set_location(l_proc, 80);
4709: --
4710: -- Update employee assignment.
4711: --
4712: per_asg_upd.upd
4729: ,p_validate => FALSE
4730: ,p_hourly_salaried_warning => l_hourly_salaried_warning
4731: );
4732: --
4733: hr_utility.set_location(l_proc, 90);
4734: --
4735: -- Set all output arguments
4736: --
4737: p_effective_start_date := l_effective_start_date;
4737: p_effective_start_date := l_effective_start_date;
4738: p_effective_end_date := l_effective_end_date;
4739: p_object_version_number := l_object_version_number;
4740: --
4741: hr_utility.set_location(' Leaving:'||l_proc, 200);
4742: end update_status_type_cwk_asg;
4743: --
4744: -- ----------------------------------------------------------------------------
4745: -- |----------------------< update_status_type_emp_asg >----------------------|
4793: and asg.effective_end_date
4794: and bus.business_group_id+0 = asg.business_group_id;
4795: --
4796: begin
4797: hr_utility.set_location('Entering:'|| l_proc, 1);
4798: --
4799: l_assignment_status_type_id := p_assignment_status_type_id;
4800: l_object_version_number := p_object_version_number;
4801: --
4804: if p_expected_system_status <> 'ACTIVE_ASSIGN'
4805: and p_expected_system_status <> 'SUSP_ASSIGN'
4806: then
4807: --
4808: hr_utility.set_location(l_proc, 10);
4809: --
4810: hr_utility.set_message(801, 'HR_7947_ASG_INV_EXP_STATUS');
4811: hr_utility.raise_error;
4812: end if;
4806: then
4807: --
4808: hr_utility.set_location(l_proc, 10);
4809: --
4810: hr_utility.set_message(801, 'HR_7947_ASG_INV_EXP_STATUS');
4811: hr_utility.raise_error;
4812: end if;
4813: --
4814: hr_utility.set_location(l_proc, 20);
4807: --
4808: hr_utility.set_location(l_proc, 10);
4809: --
4810: hr_utility.set_message(801, 'HR_7947_ASG_INV_EXP_STATUS');
4811: hr_utility.raise_error;
4812: end if;
4813: --
4814: hr_utility.set_location(l_proc, 20);
4815: --
4810: hr_utility.set_message(801, 'HR_7947_ASG_INV_EXP_STATUS');
4811: hr_utility.raise_error;
4812: end if;
4813: --
4814: hr_utility.set_location(l_proc, 20);
4815: --
4816: -- Get assignment details.
4817: --
4818: hr_api.mandatory_arg_error
4824: (p_api_name => l_proc,
4825: p_argument => 'effective_date',
4826: p_argument_value => p_effective_date);
4827: --
4828: hr_utility.set_location(l_proc, 30);
4829: --
4830: open csr_get_asg_dets;
4831: fetch csr_get_asg_dets
4832: into l_assignment_type
4834: , l_legislation_code;
4835: --
4836: if csr_get_asg_dets%NOTFOUND then
4837: --
4838: hr_utility.set_location(l_proc, 40);
4839: --
4840: close csr_get_asg_dets;
4841: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4842: hr_utility.raise_error;
4837: --
4838: hr_utility.set_location(l_proc, 40);
4839: --
4840: close csr_get_asg_dets;
4841: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4842: hr_utility.raise_error;
4843: end if;
4844: --
4845: close csr_get_asg_dets;
4838: hr_utility.set_location(l_proc, 40);
4839: --
4840: close csr_get_asg_dets;
4841: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4842: hr_utility.raise_error;
4843: end if;
4844: --
4845: close csr_get_asg_dets;
4846: --
4843: end if;
4844: --
4845: close csr_get_asg_dets;
4846: --
4847: hr_utility.set_location(l_proc, 50);
4848: --
4849: if l_assignment_type <> 'E' then
4850: --
4851: -- Assignment is not an employee assignment.
4849: if l_assignment_type <> 'E' then
4850: --
4851: -- Assignment is not an employee assignment.
4852: --
4853: hr_utility.set_location(l_proc, 60);
4854: --
4855: hr_utility.set_message(801, 'HR_7948_ASG_ASG_NOT_EMP');
4856: hr_utility.raise_error;
4857: end if;
4851: -- Assignment is not an employee assignment.
4852: --
4853: hr_utility.set_location(l_proc, 60);
4854: --
4855: hr_utility.set_message(801, 'HR_7948_ASG_ASG_NOT_EMP');
4856: hr_utility.raise_error;
4857: end if;
4858: --
4859: hr_utility.set_location(l_proc, 70);
4852: --
4853: hr_utility.set_location(l_proc, 60);
4854: --
4855: hr_utility.set_message(801, 'HR_7948_ASG_ASG_NOT_EMP');
4856: hr_utility.raise_error;
4857: end if;
4858: --
4859: hr_utility.set_location(l_proc, 70);
4860: --
4855: hr_utility.set_message(801, 'HR_7948_ASG_ASG_NOT_EMP');
4856: hr_utility.raise_error;
4857: end if;
4858: --
4859: hr_utility.set_location(l_proc, 70);
4860: --
4861: -- Process Logic
4862: --
4863: -- If p_assignment_status_type_id is g_number then derive it's default value,
4869: ,p_legislation_code => l_legislation_code
4870: ,p_expected_system_status => p_expected_system_status
4871: );
4872: --
4873: hr_utility.set_location(l_proc, 80);
4874: --
4875: -- Update employee assignment.
4876: --
4877: per_asg_upd.upd
4894: ,p_validate => FALSE
4895: ,p_hourly_salaried_warning => l_hourly_salaried_warning
4896: );
4897: --
4898: hr_utility.set_location(l_proc, 90);
4899: --
4900: -- Set all output arguments
4901: --
4902: p_effective_start_date := l_effective_start_date;
4902: p_effective_start_date := l_effective_start_date;
4903: p_effective_end_date := l_effective_end_date;
4904: p_object_version_number := l_object_version_number;
4905: --
4906: hr_utility.set_location(' Leaving:'||l_proc, 200);
4907: end update_status_type_emp_asg;
4908: --
4909: -- ----------------------------------------------------------------------------
4910: -- |----------------------------< create_apl_asg >----------------------------|
5027: l_proc varchar2(72) := g_package||'create_apl_asg';
5028: l_labour_union_member_flag per_assignments_f.labour_union_member_flag%TYPE;
5029: begin
5030: --
5031: hr_utility.set_location(l_proc, 10);
5032: -- fix for bug 4550165 starts here.
5033: if p_legislation_code = 'DE' then
5034: l_labour_union_member_flag := null;
5035: else
5062: else
5063: l_assignment_status_type_id := p_assignment_status_type_id;
5064: end if;
5065: --
5066: hr_utility.set_location(l_proc, 20);
5067: --
5068: l_assignment_number := null;
5069: --
5070: -- Insert per_assignments_f row.
5183: , p_assignment_status_id => l_assignment_status_id
5184: , p_object_version_number => l_asg_status_ovn);
5185:
5186:
5187: hr_utility.set_location(l_proc, 30);
5188: --
5189: -- Create assignment budget values.
5190: --Change 16-APR-1998 Include effective dates. SASmith
5191: --
5197: ,p_effective_start_date => l_effective_start_date
5198: ,p_effective_end_date => l_effective_end_date
5199: );
5200: --
5201: hr_utility.set_location(l_proc, 40);
5202: --
5203: -- Check if a letter request is necessary for the assignment.
5204: --
5205: per_applicant_pkg.check_for_letter_requests
5212: ,p_validation_start_date => l_effective_start_date
5213: ,p_vacancy_id => p_vacancy_id
5214: );
5215: --
5216: hr_utility.set_location(l_proc, 50);
5217: --
5218: -- Set OUT parameters
5219: --
5220: p_assignment_id := l_assignment_id;
5223: p_effective_end_date := l_effective_end_date;
5224: p_assignment_sequence := l_assignment_sequence;
5225: p_comment_id := l_comment_id;
5226: --
5227: hr_utility.set_location(' Leaving:'||l_proc, 200);
5228: --
5229: end create_apl_asg;
5230: --
5231: -- ----------------------------------------------------------------------------
5269: where bus.business_group_id = p_business_group_id;
5270: --
5271: begin
5272: --
5273: hr_utility.set_location('Entering:'|| l_proc, 1);
5274: --
5275: l_assignment_number := null;
5276: --
5277: -- Process Logic
5293: , l_legislation_code;
5294: --
5295: if csr_get_default_details%NOTFOUND then
5296: --
5297: hr_utility.set_location(l_proc, 10);
5298: --
5299: close csr_get_default_details;
5300: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5301: hr_utility.raise_error;
5296: --
5297: hr_utility.set_location(l_proc, 10);
5298: --
5299: close csr_get_default_details;
5300: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5301: hr_utility.raise_error;
5302: --
5303: end if;
5304: --
5297: hr_utility.set_location(l_proc, 10);
5298: --
5299: close csr_get_default_details;
5300: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5301: hr_utility.raise_error;
5302: --
5303: end if;
5304: --
5305: close csr_get_default_details;
5303: end if;
5304: --
5305: close csr_get_default_details;
5306: --
5307: hr_utility.set_location(l_proc, 20);
5308: --
5309: -- Create the contingent worker assignment.
5310: --
5311: hr_assignment_internal.create_cwk_asg
5332: ,p_other_manager_warning => l_other_manager_warning
5333: ,p_validate_df_flex => false
5334: );
5335: --
5336: hr_utility.set_location(l_proc, 30);
5337: --
5338: -- Set remaining output arguments
5339: --
5340: p_assignment_number := l_assignment_number;
5338: -- Set remaining output arguments
5339: --
5340: p_assignment_number := l_assignment_number;
5341: --
5342: hr_utility.set_location(' Leaving:'||l_proc, 999);
5343: --
5344: end create_default_cwk_asg;
5345: --
5346: -- ----------------------------------------------------------------------------
5441: l_hourly_salaried_warning boolean;
5442: l_proc varchar2(72) := g_package||'create_cwk_asg';
5443: --
5444: begin
5445: hr_utility.set_location('Entering:'|| l_proc, 1);
5446: --
5447: l_assignment_status_type_id := p_assignment_status_type_id;
5448: --
5449: -- Validation in addition to Table Handlers
5461: ,p_legislation_code => p_legislation_code
5462: ,p_expected_system_status => 'ACTIVE_CWK'
5463: );
5464: --
5465: hr_utility.set_location(l_proc, 10);
5466: --
5467: -- Insert per_assignments_f row.
5468: --
5469: per_asg_ins.ins
5551: ,p_hourly_salaried_warning => l_hourly_salaried_warning
5552: ,p_supervisor_assignment_id => p_supervisor_assignment_id
5553: );
5554: --
5555: hr_utility.set_location(l_proc, 30);
5556: --
5557: -- Set all output arguments
5558: --
5559: p_assignment_id := l_assignment_id;
5559: p_assignment_id := l_assignment_id;
5560: p_effective_start_date := l_effective_start_date;
5561: p_effective_end_date := l_effective_end_date;
5562: --
5563: hr_utility.set_location(' Leaving:'||l_proc, 100);
5564: end create_cwk_asg;
5565: --
5566: -- ----------------------------------------------------------------------------
5567: -- |------------------------< create_default_apl_asg >------------------------|
5621: and nvl(date_to, hr_api.g_eot);
5622: -- Bug 4325900 Ends
5623: --
5624: begin
5625: hr_utility.set_location('Entering:'|| l_proc, 10);
5626: --
5627: -- Process Logic
5628: --
5629: -- Get default details.
5639: if csr_get_default_details%NOTFOUND then
5640: --
5641: close csr_get_default_details;
5642: --
5643: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5644: hr_utility.raise_error;
5645: end if;
5646: close csr_get_default_details;
5647: --
5640: --
5641: close csr_get_default_details;
5642: --
5643: hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5644: hr_utility.raise_error;
5645: end if;
5646: close csr_get_default_details;
5647: --
5648: hr_utility.set_location(l_proc, 20);
5644: hr_utility.raise_error;
5645: end if;
5646: close csr_get_default_details;
5647: --
5648: hr_utility.set_location(l_proc, 20);
5649: -- Bug 4325900 Starts
5650: -- Desc: Check to see if the vacancy is null or not. If vacancy
5651: -- is not null then get the default location of the vacancy
5652: -- to pass the the assignment rowhandler.
5699: ,p_grade_id => l_vac_grade_id
5700: ,p_people_group_id => l_vac_pgp_id
5701: );
5702: --
5703: hr_utility.set_location(' Leaving:'||l_proc, 40);
5704: end create_default_apl_asg;
5705: --
5706: -- ----------------------------------------------------------------------------
5707: -- |----------------------< update_status_type_apl_asg >----------------------|
5770: from per_assignment_status_types ast
5771: where ast.assignment_status_type_id = p_assignment_status_type_id;
5772: --
5773: begin
5774: hr_utility.set_location('Entering:'|| l_proc, 1);
5775: --
5776: -- Validation in addition to Table Handlers
5777: --
5778: if p_expected_system_status <> 'ACTIVE_APL'
5781: and p_expected_system_status <> 'INTERVIEW1'
5782: and p_expected_system_status <> 'INTERVIEW2'
5783: then
5784: --
5785: hr_utility.set_message(801, 'HR_51232_ASG_INV_AASG_AST');
5786: hr_utility.raise_error;
5787: end if;
5788: --
5789: hr_utility.set_location(l_proc, 10);
5782: and p_expected_system_status <> 'INTERVIEW2'
5783: then
5784: --
5785: hr_utility.set_message(801, 'HR_51232_ASG_INV_AASG_AST');
5786: hr_utility.raise_error;
5787: end if;
5788: --
5789: hr_utility.set_location(l_proc, 10);
5790: --
5785: hr_utility.set_message(801, 'HR_51232_ASG_INV_AASG_AST');
5786: hr_utility.raise_error;
5787: end if;
5788: --
5789: hr_utility.set_location(l_proc, 10);
5790: --
5791: -- Get assignment details.
5792: --
5793: open csr_get_asg_dets;
5799: --
5800: if csr_get_asg_dets%NOTFOUND then
5801: --
5802: close csr_get_asg_dets;
5803: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
5804: hr_utility.raise_error;
5805: end if;
5806: --
5807: close csr_get_asg_dets;
5800: if csr_get_asg_dets%NOTFOUND then
5801: --
5802: close csr_get_asg_dets;
5803: hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
5804: hr_utility.raise_error;
5805: end if;
5806: --
5807: close csr_get_asg_dets;
5808: --
5805: end if;
5806: --
5807: close csr_get_asg_dets;
5808: --
5809: hr_utility.set_location(l_proc, 20);
5810: --
5811: if l_assignment_type <> 'A' then
5812: --
5813: -- Assignment is not an applicant assignment.
5811: if l_assignment_type <> 'A' then
5812: --
5813: -- Assignment is not an applicant assignment.
5814: --
5815: hr_utility.set_message(801, 'HR_51036_ASG_ASG_NOT_APL');
5816: hr_utility.raise_error;
5817: end if;
5818: --
5819: hr_utility.set_location(l_proc, 30);
5812: --
5813: -- Assignment is not an applicant assignment.
5814: --
5815: hr_utility.set_message(801, 'HR_51036_ASG_ASG_NOT_APL');
5816: hr_utility.raise_error;
5817: end if;
5818: --
5819: hr_utility.set_location(l_proc, 30);
5820: --
5815: hr_utility.set_message(801, 'HR_51036_ASG_ASG_NOT_APL');
5816: hr_utility.raise_error;
5817: end if;
5818: --
5819: hr_utility.set_location(l_proc, 30);
5820: --
5821: -- Process Logic
5822: --
5823: -- If p_assignment_status_type_id is hr_api.g_number then derive it's default
5856: ,p_validate => FALSE
5857: ,p_hourly_salaried_warning => l_hourly_salaried_warning
5858: );
5859: --
5860: hr_utility.set_location(l_proc, 45);
5861: --
5862: IRC_ASG_STATUS_API.dt_update_irc_asg_status
5863: (p_assignment_id => p_assignment_id
5864: , p_datetrack_mode => 'INSERT'
5868: , p_assignment_status_id => l_assignment_status_id
5869: , p_object_version_number => l_asg_status_ovn);
5870:
5871: --
5872: hr_utility.set_location(l_proc, 50);
5873: --
5874: -- Remove out-of-date letter request lines
5875: --
5876: per_app_asg_pkg.cleanup_letters
5894: ,p_validation_start_date => l_validation_start_date
5895: ,p_vacancy_id => l_vacancy_id
5896: );
5897: --
5898: hr_utility.set_location(l_proc, 60);
5899: --
5900: -- Set out arguments
5901: --
5902: p_effective_start_date := l_effective_start_date;
5902: p_effective_start_date := l_effective_start_date;
5903: p_effective_end_date := l_effective_end_date;
5904: p_object_version_number := l_object_version_number;
5905: --
5906: hr_utility.set_location(' Leaving:'||l_proc, 70);
5907: end update_status_type_apl_asg;
5908: --surendra
5909: --
5910: -- ----------------------------------------------------------------------------
5935: --
5936: begin
5937: --
5938: if g_debug then
5939: hr_utility.set_location('Entering:'|| l_proc, 10);
5940: end if;
5941: --
5942: /* if per_asg_shd.g_old_rec.assignment_type = 'O' then
5943: if g_debug then
5940: end if;
5941: --
5942: /* if per_asg_shd.g_old_rec.assignment_type = 'O' then
5943: if g_debug then
5944: hr_utility.set_location('Assignment of type O is found', 20);
5945: end if;
5946: --
5947: fnd_message.set_name('PER', 'ERROR_TO_BE_REPLACED_BY_IRC_1');
5948: fnd_message.raise_error;
5955: --
5956: if (temp = 'Y') then
5957: --
5958: if g_debug then
5959: hr_utility.set_location('IRC Offers available for this assignment', 30);
5960: end if;
5961: --
5962: fnd_message.set_name('PER', 'ERROR_TO_BE_REPLACED_BY_IRC_2');
5963: fnd_message.raise_error;
5974: END LOOP;
5975: close irc_asgt_statuses;
5976: --
5977: if g_debug then
5978: hr_utility.set_location('Leaving:'|| l_proc, 40);
5979: end if;
5980: --
5981: end;
5982: --
6000: l_proc varchar2(72) := g_package||'ben_delete_assgt_checks';
6001: begin
6002: --
6003: if g_debug then
6004: hr_utility.set_location('Entering:'|| l_proc, 10);
6005: end if;
6006: --
6007: if (p_datetrack_mode = 'ZAP') then
6008: open ben_le_checks;
6011: --
6012: if (temp = 'Y') then
6013: --
6014: if g_debug then
6015: hr_utility.set_location('BEN Life Events available for this assignment', 20);
6016: end if;
6017: p_life_events_exists := true;
6018: --
6019: end if;
6019: end if;
6020: --
6021: end if;
6022: if g_debug then
6023: hr_utility.set_location('Leaving:'|| l_proc, 30);
6024: end if;
6025: --
6026: end;
6027: --
6124: --
6125: begin
6126: --
6127: if g_debug then
6128: hr_utility.set_location('Entering:'|| l_proc, 10);
6129: end if;
6130: --
6131: open csr_ass_sys_type;
6132: fetch csr_ass_sys_type into l_sys_status_type;
6150: fetch csr_get_legislation_code into l_legislation_code;
6151: if csr_get_legislation_code%NOTFOUND then
6152: close csr_get_legislation_code;
6153: -- This should never happen
6154: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
6155: hr_utility.set_message_token('PROCEDURE', l_proc);
6156: hr_utility.set_message_token('STEP','210');
6157: hr_utility.raise_error;
6158: end if;
6151: if csr_get_legislation_code%NOTFOUND then
6152: close csr_get_legislation_code;
6153: -- This should never happen
6154: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
6155: hr_utility.set_message_token('PROCEDURE', l_proc);
6156: hr_utility.set_message_token('STEP','210');
6157: hr_utility.raise_error;
6158: end if;
6159: close csr_get_legislation_code;
6152: close csr_get_legislation_code;
6153: -- This should never happen
6154: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
6155: hr_utility.set_message_token('PROCEDURE', l_proc);
6156: hr_utility.set_message_token('STEP','210');
6157: hr_utility.raise_error;
6158: end if;
6159: close csr_get_legislation_code;
6160: --end changes for bug 6917728
6153: -- This should never happen
6154: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
6155: hr_utility.set_message_token('PROCEDURE', l_proc);
6156: hr_utility.set_message_token('STEP','210');
6157: hr_utility.raise_error;
6158: end if;
6159: close csr_get_legislation_code;
6160: --end changes for bug 6917728
6161: --
6175: --
6176: if per_asg_shd.g_old_rec.assignment_type = 'A' then
6177: ---
6178: if g_debug then
6179: hr_utility.set_location('Applicant type Assignment', 20);
6180: end if;
6181: --
6182: per_app_asg_pkg.pre_delete_validation (
6183: p_business_group_id => per_asg_shd.g_old_rec.business_group_id,
6192: ---
6193: elsif ((per_asg_shd.g_old_rec.assignment_type = 'E') OR (per_asg_shd.g_old_rec.assignment_type = 'C')) then
6194: ---
6195: if g_debug then
6196: hr_utility.set_location('EMP/CWK type Assignment', 30);
6197: end if;
6198: --
6199: l_re_entry_point := 999;
6200: l_returned_warning := null;
6211: -- l_prim_date_from := p_prim_date_from;
6212: --
6213: while l_re_entry_point <> 0 loop
6214: if g_debug then
6215: hr_utility.set_location('l_re_entry_point :'||l_re_entry_point, 40);
6216: end if;
6217: --
6218: per_assignments_f2_pkg.pre_delete(
6219: p_datetrack_mode, -- p_del_mode,
6286: -- to delete the record. and he will get an warning This is done as an
6287: -- impact of date tracking of W4 screen
6288: --
6289: if g_debug then
6290: hr_utility.set_location('Before check_payroll_run checks', 50);
6291: end if;
6292:
6293: --if condition added for bug 6917728
6294: IF l_legislation_code = 'US' then
6312: --
6313: -- l_new_end_date := p_new_end_date;
6314: --
6315: if g_debug then
6316: hr_utility.set_location('Before key_delrec ', 60);
6317: end if;
6318: --
6319: per_assignments_f2_pkg.key_delrec(
6320: p_datetrack_mode, -- l_del_mode,
6344: l_rec2.assignment_id := p_rec.assignment_id; -- p_ass_id;
6345: l_rec2.position_id := per_asg_shd.g_old_rec.position_id; -- name_in('ASSGT.POSITION_ID');
6346: --
6347: if g_debug then
6348: hr_utility.set_location('Before per_pqh_shr.per_asg_bus call ', 70);
6349: end if;
6350: per_pqh_shr.per_asg_bus(
6351: p_event => 'DELETE_VALIDATE'
6352: ,p_rec => l_rec2
6363: --
6364: end if; -- End of EMP/CWK type Assignment validation checks.
6365: --
6366: if g_debug then
6367: hr_utility.set_location('Leaving :'||l_proc, 80);
6368: end if;
6369: --
6370: end; -- End of pre-delete checks
6371: --
6420: --
6421: begin
6422: --
6423: if g_debug then
6424: hr_utility.set_location('Entering :'||l_proc, 10);
6425: end if;
6426: --
6427: open csr_ass_sys_type;
6428: fetch csr_ass_sys_type into l_sys_status_type;
6433: --
6434: if per_asg_shd.g_old_rec.assignment_type = 'A' then
6435: ---
6436: if g_debug then
6437: hr_utility.set_location('Applicant type Assignment', 20);
6438: end if;
6439: --
6440: if ( p_datetrack_mode in ('FUTURE_CHANGE','DELETE_NEXT_CHANGE' ) ) then
6441: if ( p_appl_asg_new_end_date is null ) then
6463: -- set_end_date ( p_new_end_date , p_assignment_id ) ; -- copied this logic from per_app_asg_pkg
6464: -- Sets an end date on rows which are deleted with delete mode FUTURE_CHANGES or NEXT_CHANGE
6465: --
6466: if g_debug then
6467: hr_utility.set_location('Before updating assignment end date', 30);
6468: end if;
6469: --
6470: update per_assignments_f a
6471: set a.effective_end_date = p_appl_asg_new_end_date
6487: ---
6488: elsif ((per_asg_shd.g_old_rec.assignment_type = 'E') OR (per_asg_shd.g_old_rec.assignment_type = 'C')) then
6489: ---
6490: if g_debug then
6491: hr_utility.set_location('EMP/CWK type assignment checks ', 40);
6492: end if;
6493: --
6494: per_assignments_f1_pkg.post_delete(
6495: p_rec.assignment_id, -- p_ass_id,
6532: --
6533: end if; -- End of EMP/CWK type Assignment validation checks.
6534: ---
6535: if g_debug then
6536: hr_utility.set_location('Leaving :'||l_proc, 50);
6537: end if;
6538: --
6539: end;
6540: --3