[Home] [Help]
15: p_assignment_id number ) is
16: --
17: begin
18: --
19: hr_utility.set_location ( 'PER_APP_ASG_PKG.set_end_date' , 5) ;
20: update per_assignments_f a
21: set a.effective_end_date = p_new_end_date
22: where a.assignment_id = p_assignment_id
23: and a.effective_end_date = (
24: select max(a2.effective_end_date)
25: from per_assignments_f a2
26: where a2.assignment_id = a.assignment_id);
27: --
28: hr_utility.set_location ( 'PER_APP_ASG_PKG.set_end_date' , 10) ;
29: --
30: end set_end_date;
31:
32:
45:
46: --
47: BEGIN
48:
49: hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_child' , 5) ;
50: --
51: BEGIN
52: select 'Y'
53: into p_del_flag
64: --
65: IF p_del_flag = 'Y' and
66: p_delete_mode = 'ZAP' THEN
67: --
68: hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_child' , 10) ;
69:
70: Delete per_assignment_budget_values_f
71: where assignment_id = p_assignment_id;
72:
88: -- As below
89: procedure cleanup_letters ( p_assignment_id in number ) is
90: begin
91: --
92: hr_utility.set_location ( 'PER_APP_ASG_PKG.cleanup_letters' , 5) ;
93: --
94: delete from per_letter_request_lines p
95: where p.assignment_id = p_assignment_id
96: and exists
110: from per_assignments_f a2
111: where a2.assignment_id = p_assignment_id)
112: and a.effective_end_date = p.date_from ))) ;
113: --
114: hr_utility.set_location ( 'PER_APP_ASG_PKG.cleanup_letters' , 10) ;
115: --
116: end cleanup_letters ;
117: -- --
118: procedure insert_row(
217: --
218: begin
219: --
220: -- PRE-INSERT CHECKS
221: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 1 ) ;
222: hr_utility.trace('p_grade_ladder_pgm_id : ' || p_grade_ladder_pgm_id);
223: check_apl_end_date ( p_application_id => p_application_id ) ;
224: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 2 ) ;
225: check_current_applicant ( p_person_id => p_person_id,
218: begin
219: --
220: -- PRE-INSERT CHECKS
221: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 1 ) ;
222: hr_utility.trace('p_grade_ladder_pgm_id : ' || p_grade_ladder_pgm_id);
223: check_apl_end_date ( p_application_id => p_application_id ) ;
224: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 2 ) ;
225: check_current_applicant ( p_person_id => p_person_id,
226: p_session_date => p_session_date ) ;
220: -- PRE-INSERT CHECKS
221: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 1 ) ;
222: hr_utility.trace('p_grade_ladder_pgm_id : ' || p_grade_ladder_pgm_id);
223: check_apl_end_date ( p_application_id => p_application_id ) ;
224: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 2 ) ;
225: check_current_applicant ( p_person_id => p_person_id,
226: p_session_date => p_session_date ) ;
227: --
228: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 3 ) ;
224: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 2 ) ;
225: check_current_applicant ( p_person_id => p_person_id,
226: p_session_date => p_session_date ) ;
227: --
228: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 3 ) ;
229: open c1;
230: fetch c1 into P_ASSIGNMENT_ID;
231: close c1;
232: --
243: );
244: --
245: --
246: begin
247: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 4 ) ;
248: insert into per_assignments_f (
249: assignment_id,
250: effective_start_date,
251: effective_end_date,
415: close c2;
416: --
417: -- Update people group
418: --
419: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 5 ) ;
420: per_applicant_pkg.update_group ( p_people_group_id,
421: p_people_group_name ) ;
422: --
423: -- Create letter request
421: p_people_group_name ) ;
422: --
423: -- Create letter request
424: --
425: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 6 ) ;
426: per_applicant_pkg.check_for_letter_requests (
427: p_business_group_id => p_business_group_id,
428: p_per_system_status => NULL, --***TEMP
429: p_assignment_status_type_id => p_assignment_status_type_id,
433: p_validation_start_date => p_effective_start_date,
434: p_vacancy_id => p_vacancy_id ) ;
435: --
436: -- Create default budget values
437: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 7 ) ;
438: per_applicant_pkg.create_default_budget_values (
439: p_business_group_id,
440: p_assignment_id,
441: p_effective_start_date,
443: --
444: --
445: -- Set the location code if the location id is not null and the code is
446: -- null
447: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 8 ) ;
448: if ( ( p_location_id is not null ) and ( p_location_code is null ) ) then
449: p_location_code := per_applicant_pkg.get_location_code ( p_location_id ) ;
450: end if;
451: --
451: --
452: --
453: -- Set the frequency meaning if the frequency is not null and the meaning is
454: -- null
455: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 9 ) ;
456: if ( ( p_frequency is not null ) and ( p_frequency_meaning is null ) ) then
457: p_frequency_meaning := hr_general.decode_lookup( 'FREQUENCY',
458: p_frequency ) ;
459: end if;
461: --
462: -- Insert record into iRec Asg Statuses. Otherwise the applications applied
463: -- through PUI, will not visible in iRec for applicants created through iRec
464: -- Bug# 2985747
465: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 10 ) ;
466:
467: irc_asg_status_api.create_irc_asg_status
468: ( p_validate => FALSE
469: , p_assignment_id => p_assignment_id
478: -- Whenever Applicant Information is getting changed/inserted via Applicant forms
479: -- we need to trigger OAB Lifeevents. The following ben call will trigger LE
480: -- Bug 3506363
481:
482: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 11 ) ;
483: hr_utility.set_location ( 'Before OAB Call' , 11 ) ;
484: -- Start changes for bug 13059935
485: /*ben_dt_trgr_handle.assignment
486: (p_rowid => p_row_id
479: -- we need to trigger OAB Lifeevents. The following ben call will trigger LE
480: -- Bug 3506363
481:
482: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 11 ) ;
483: hr_utility.set_location ( 'Before OAB Call' , 11 ) ;
484: -- Start changes for bug 13059935
485: /*ben_dt_trgr_handle.assignment
486: (p_rowid => p_row_id
487: ,p_assignment_id => p_assignment_id
595: ,p_ass_attribute30 => null
596: );
597: -- End changes for bug 13059935
598:
599: hr_utility.set_location ( 'After OAB Call' , 11 ) ;
600: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 11 ) ;
601:
602:
603:
596: );
597: -- End changes for bug 13059935
598:
599: hr_utility.set_location ( 'After OAB Call' , 11 ) ;
600: hr_utility.set_location ( 'PER_APP_ASG_PKG.insert_row' , 11 ) ;
601:
602:
603:
604: -- Bug 3506363
637: -- Addition of call to delete_child to ensure child rows are removed when parent is removed on mode of
638: -- 'ZAP'
639: -- SASmith 31-March-1998
640:
641: hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 5 ) ;
642: delete_child ( p_assignment_id
643: ,p_delete_mode);
644:
645: delete from per_assignments_f a
664: --- Fix For Bug # 9183199 ---
665:
666: if ( p_delete_mode = 'ZAP' ) then
667: return ; -- This case is handled by the form at present
668: hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 10 ) ;
669:
670: elsif ( p_delete_mode in ('FUTURE_CHANGE','DELETE_NEXT_CHANGE' ) ) then
671: if ( p_new_end_date is null ) then
672: if ( p_validation_end_date = hr_general.end_of_time ) then
676: p_effective_end_date,
677: null,
678: null ,
679: l_cost_warning) ;
680: hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 15 ) ;
681: end if;
682: else
683: hr_assignment.tidy_up_ref_int ( p_assignment_id,
684: 'FUTURE',
686: p_effective_end_date,
687: null,
688: null,
689: l_cost_warning ) ;
690: hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 20 ) ;
691: end if;
692: --
693: if ( p_new_end_date is not null ) then
694: set_end_date ( p_new_end_date , p_assignment_id ) ;
691: end if;
692: --
693: if ( p_new_end_date is not null ) then
694: set_end_date ( p_new_end_date , p_assignment_id ) ;
695: hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 25 ) ;
696: end if;
697: --
698: hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 27) ;
699: cleanup_letters ( p_assignment_id ) ;
694: set_end_date ( p_new_end_date , p_assignment_id ) ;
695: hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 25 ) ;
696: end if;
697: --
698: hr_utility.set_location ( 'PER_APP_ASG_PKG.delete_row' , 27) ;
699: cleanup_letters ( p_assignment_id ) ;
700: else
701: app_exception.invalid_argument( 'per_app_asg_pkg.delete_row',
702: 'p_delete_mode',
799: --
800: ass_rec ASS_CUR%rowtype;
801: --
802: begin
803: hr_utility.set_location ( 'PER_APP_ASG_PKG.lock_row' , 5) ;
804: --
805: open ASS_CUR;
806: --
807: fetch ASS_CUR into ASS_REC;
1106: and ((ass_rec.ass_attribute30 = p_ass_attribute30)
1107: or (ass_rec.ass_attribute30 is null
1108: and (p_ass_attribute30 is null)))
1109: ) then
1110: hr_utility.set_location ( 'PER_APP_ASG_PKG.lock_row' , 10) ;
1111: return; -- Row successfully locked, no changes
1112: end if;
1113: end if;
1114: hr_utility.set_location ( 'PER_APP_ASG_PKG.lock_row' , 15) ;
1110: hr_utility.set_location ( 'PER_APP_ASG_PKG.lock_row' , 10) ;
1111: return; -- Row successfully locked, no changes
1112: end if;
1113: end if;
1114: hr_utility.set_location ( 'PER_APP_ASG_PKG.lock_row' , 15) ;
1115:
1116: --
1117: fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1118: app_exception.raise_exception;
1246: begin
1247: --
1248: -- PRE-UPDATE-CHECKS
1249: --
1250: hr_utility.set_location('Entering : per_app_asg_pkg.update_row' ,10);
1251: hr_utility.trace('p_grade_ladder_pgm_id : ' || p_grade_ladder_pgm_id);
1252: /* TEMP MOVED TO CLIENT FOR DEVELOPMENT
1253: check_current_applicant ( p_person_id => p_person_id,
1254: p_session_date => p_session_date ) ;
1247: --
1248: -- PRE-UPDATE-CHECKS
1249: --
1250: hr_utility.set_location('Entering : per_app_asg_pkg.update_row' ,10);
1251: hr_utility.trace('p_grade_ladder_pgm_id : ' || p_grade_ladder_pgm_id);
1252: /* TEMP MOVED TO CLIENT FOR DEVELOPMENT
1253: check_current_applicant ( p_person_id => p_person_id,
1254: p_session_date => p_session_date ) ;
1255: */
1269: -- Whenever Applicant Information is getting changed/inserted via Applicant forms
1270: -- we need to trigger OAB Lifeevents. The following ben call will trigger LE
1271: -- Bug 3506363
1272:
1273: hr_utility.set_location ( 'PER_APP_ASG_PKG.update_row' , 11 ) ;
1274: hr_utility.set_location ( 'Before OAB Call' , 11 ) ;
1275: -- Start changes for bug 13059935
1276: /*ben_dt_trgr_handle.assignment
1277: (p_rowid => p_row_id
1270: -- we need to trigger OAB Lifeevents. The following ben call will trigger LE
1271: -- Bug 3506363
1272:
1273: hr_utility.set_location ( 'PER_APP_ASG_PKG.update_row' , 11 ) ;
1274: hr_utility.set_location ( 'Before OAB Call' , 11 ) ;
1275: -- Start changes for bug 13059935
1276: /*ben_dt_trgr_handle.assignment
1277: (p_rowid => p_row_id
1278: ,p_assignment_id => p_assignment_id
1331: );
1332: */
1333: -- End changes for bug 13059935
1334:
1335: hr_utility.set_location ( 'After OAB Call' , 12 ) ;
1336:
1337: -- Bug 3506363
1338: -- end of OAB Code change
1339: update per_assignments_f a
1482: -- End changes for bug 13059935
1483:
1484: -- Start of fix 3634447
1485: -- Get the current organization
1486: hr_utility.set_location ('per_app_asg_pkg.update_row', 13);
1487: open current_org;
1488: fetch current_org into l_old_org_id;
1489: close current_org;
1490: -- Updating security permission when organization is changed
1491: -- from business group to other organization.
1492: if l_old_org_id = p_business_group_id and
1493: p_organization_id <> p_business_group_id then
1494: --
1495: hr_utility.set_location ('per_app_asg_pkg.update_row', 14);
1496: hr_security_internal.clear_from_person_list(
1497: p_person_id => p_person_id);
1498: --
1499: end if;
1496: hr_security_internal.clear_from_person_list(
1497: p_person_id => p_person_id);
1498: --
1499: end if;
1500: hr_utility.set_location ('per_app_asg_pkg.update_row', 15);
1501: hr_security_internal.add_to_person_list(
1502: p_effective_date => p_effective_start_date,
1503: p_assignment_id => p_assignment_id);
1504: hr_utility.set_location ('per_app_asg_pkg.update_row', 16);
1500: hr_utility.set_location ('per_app_asg_pkg.update_row', 15);
1501: hr_security_internal.add_to_person_list(
1502: p_effective_date => p_effective_start_date,
1503: p_assignment_id => p_assignment_id);
1504: hr_utility.set_location ('per_app_asg_pkg.update_row', 16);
1505: -- End of fix 3634447
1506:
1507: -- insert into irc_assignment_statuses if the assignment status has changed
1508: --
1525: end if;
1526: --
1527: -- Fix for bug 3680947 starts here.
1528: --
1529: hr_utility.set_location ('per_app_asg_pkg.update_row', 17);
1530:
1531: IF ( p_status_changed ) or ( p_per_system_status = 'TERM_APL' )
1532: OR nvl(l_previous_vacancy_id,-1) <> nvl(p_vacancy_id,-1)
1533: THEN
1548: --
1549: cleanup_letters( p_assignment_id => p_assignment_id);
1550: --
1551: --Performance Fix done by removing +0
1552: hr_utility.set_location ('per_app_asg_pkg.update_row', 18);
1553: delete from per_letter_requests plr
1554: where plr.business_group_id = p_business_group_id
1555: and plr.request_status = 'PENDING'
1556: and plr.auto_or_manual = 'AUTO'
1559: from per_letter_request_lines plrl
1560: where plrl.letter_request_id = plr.letter_request_id
1561: ) ;
1562: --
1563: hr_utility.set_location ('per_app_asg_pkg.update_row', 19);
1564: per_applicant_pkg.check_for_letter_requests
1565: (p_business_group_id => p_business_group_id
1566: ,p_per_system_status => p_per_system_status
1567: ,p_assignment_status_type_id => p_assignment_status_type_id
1578: --
1579: -- Set the location code if the location id is not null and the code is
1580: -- null
1581: if ( ( p_location_id is not null ) and ( p_location_code is null ) ) then
1582: hr_utility.set_location ('per_app_asg_pkg.update_row', 20);
1583: p_location_code := per_applicant_pkg.get_location_code ( p_location_id ) ;
1584: end if;
1585: --
1586: -- Set the frequency meaning if the frequency is not null and the meaning is
1585: --
1586: -- Set the frequency meaning if the frequency is not null and the meaning is
1587: -- null
1588: if ( ( p_frequency is not null ) and ( p_frequency_meaning is null ) ) then
1589: hr_utility.set_location ('per_app_asg_pkg.update_row', 21);
1590: p_frequency_meaning := hr_general.decode_lookup( 'FREQUENCY',
1591: p_frequency ) ;
1592: end if;
1593: --
1592: end if;
1593: --
1594: --
1595: if ( p_per_system_status = 'TERM_APL' ) then
1596: hr_utility.set_location ('per_app_asg_pkg.update_row', 22);
1597: hr_assignment.tidy_up_ref_int ( p_assignment_id ,
1598: 'END',
1599: p_session_date,
1600: p_session_date,
1604: end if;
1605:
1606: --
1607:
1608: hr_utility.set_location(' Leaving : per_app_asg_pkg.update_row' ,100);
1609: --
1610: end update_row;
1611: --
1612: -------------------------------------------------------------------------
1630:
1631: max_apl_date date;
1632:
1633: begin
1634: hr_utility.set_location ('per_app_asg_pkg.check_apl_update_allowed', 10);
1635:
1636: p_status := 'UNKNOWN';
1637:
1638: open get_max_apl_date;
1638: open get_max_apl_date;
1639: fetch get_max_apl_date into max_apl_date;
1640: if get_max_apl_date%notfound then
1641: close get_max_apl_date;
1642: hr_utility.set_message(800,'HR_52377_NOT_ACTIVE_APPLICANT');
1643: hr_utility.raise_error;
1644: end if;
1645: close get_max_apl_date;
1646:
1639: fetch get_max_apl_date into max_apl_date;
1640: if get_max_apl_date%notfound then
1641: close get_max_apl_date;
1642: hr_utility.set_message(800,'HR_52377_NOT_ACTIVE_APPLICANT');
1643: hr_utility.raise_error;
1644: end if;
1645: close get_max_apl_date;
1646:
1647: if max_apl_date = hr_general.end_of_time
1657: -- Fix for bug 3306906 starts here.
1658: -- Pass p_status as CORR_ONLY for the terminated application.
1659: --
1660: p_status := 'CORR_ONLY';
1661: --hr_utility.set_message(800,'HR_52377_NOT_ACTIVE_APPLICANT');
1662: --hr_utility.raise_error;
1663: --
1664: -- Fix for bug 3306906 ends here.
1665: --
1658: -- Pass p_status as CORR_ONLY for the terminated application.
1659: --
1660: p_status := 'CORR_ONLY';
1661: --hr_utility.set_message(800,'HR_52377_NOT_ACTIVE_APPLICANT');
1662: --hr_utility.raise_error;
1663: --
1664: -- Fix for bug 3306906 ends here.
1665: --
1666: end if;
1663: --
1664: -- Fix for bug 3306906 ends here.
1665: --
1666: end if;
1667: hr_utility.set_location ('per_app_asg_pkg.check_apl_update_allowed', 20);
1668:
1669: end check_apl_update_allowed;
1670: --
1671: procedure check_apl_end_date ( p_application_id in number ) is
1676: where a.application_id = p_application_id
1677: and a.date_end is null ;
1678: begin
1679: --
1680: hr_utility.set_location ('per_app_asg_pkg.check_apl_end_date', 10);
1681: open c1 ;
1682: fetch c1 into l_dummy ;
1683: if c1%notfound then
1684: close c1 ;
1681: open c1 ;
1682: fetch c1 into l_dummy ;
1683: if c1%notfound then
1684: close c1 ;
1685: hr_utility.set_message(800,'HR_52377_NOT_ACTIVE_APPLICANT');
1686: hr_utility.raise_error ;
1687: end if;
1688: close c1 ;
1689: --
1682: fetch c1 into l_dummy ;
1683: if c1%notfound then
1684: close c1 ;
1685: hr_utility.set_message(800,'HR_52377_NOT_ACTIVE_APPLICANT');
1686: hr_utility.raise_error ;
1687: end if;
1688: close c1 ;
1689: --
1690: hr_utility.set_location ('per_app_asg_pkg.check_apl_end_date', 20);
1686: hr_utility.raise_error ;
1687: end if;
1688: close c1 ;
1689: --
1690: hr_utility.set_location ('per_app_asg_pkg.check_apl_end_date', 20);
1691: end check_apl_end_date ;
1692: --
1693: procedure check_current_applicant ( p_person_id in number,
1694: p_session_date in date ) is
1701: and p_session_date
1702: between effective_start_date and effective_end_date ;
1703: begin
1704: --
1705: hr_utility.set_location ('per_app_asg_pkg.check_current_applicant', 10);
1706: open c1 ;
1707: fetch c1 into l_dummy ;
1708: if c1%notfound then
1709: close c1 ;
1706: open c1 ;
1707: fetch c1 into l_dummy ;
1708: if c1%notfound then
1709: close c1 ;
1710: hr_utility.set_message(801,'HR_6067_APP_ASS_APPL_ENDED');
1711: hr_utility.raise_error ;
1712: end if;
1713: close c1 ;
1714: --
1707: fetch c1 into l_dummy ;
1708: if c1%notfound then
1709: close c1 ;
1710: hr_utility.set_message(801,'HR_6067_APP_ASS_APPL_ENDED');
1711: hr_utility.raise_error ;
1712: end if;
1713: close c1 ;
1714: --
1715: hr_utility.set_location ('per_app_asg_pkg.check_current_applicant', 20);
1711: hr_utility.raise_error ;
1712: end if;
1713: close c1 ;
1714: --
1715: hr_utility.set_location ('per_app_asg_pkg.check_current_applicant', 20);
1716: end check_current_applicant ;
1717: --
1718: procedure check_valid_asg_status ( p_business_group_id in number,
1719: p_legislation_code in varchar2,
1732: and nvl(b.per_system_status,a.per_system_status) = p_per_system_status
1733: and a.assignment_status_type_id = p_assignment_status_type_id ;
1734: begin
1735: --
1736: hr_utility.set_location ('per_app_asg_pkg.check_valid_asg_status', 10);
1737: open c1 ;
1738: fetch c1 into l_dummy ;
1739: if c1%notfound then
1740: close c1 ;
1737: open c1 ;
1738: fetch c1 into l_dummy ;
1739: if c1%notfound then
1740: close c1 ;
1741: -- ***TEMP hr_utility.set_message gives up with a value error
1742: -- for this message
1743: fnd_message.set_name('PAY','HR_6073_APP_ASS_INVALID_STATUS' );
1744: app_exception.raise_exception ;
1745: end if;
1744: app_exception.raise_exception ;
1745: end if;
1746: close c1 ;
1747: --
1748: hr_utility.set_location ('per_app_asg_pkg.check_valid_asg_status', 20);
1749: end check_valid_asg_status ;
1750: --
1751: procedure check_future_stat_change ( p_assignment_id in number ) is
1752: l_dummy number ;
1760: where b.per_system_status in ('TERM_APL','ACTIVE_ASSIGN')
1761: and a.assignment_status_type_id = b.assignment_status_type_id) ;
1762: begin
1763: --
1764: hr_utility.set_location ('per_app_asg_pkg.check_future_stat_change', 10);
1765: open c1 ;
1766: fetch c1 into l_dummy ;
1767: if c1%found then
1768: close c1 ;
1770: app_exception.raise_exception ;
1771: end if;
1772: close c1 ;
1773: --
1774: hr_utility.set_location ('per_app_asg_pkg.check_future_stat_change', 20);
1775: end check_future_stat_change ;
1776:
1777: procedure check_end_date ( p_assignment_id in number ,
1778: p_warning_set out nocopy boolean ) is
1782: from per_assignments_f
1783: where assignment_id = p_assignment_id ;
1784: begin
1785: --
1786: hr_utility.set_location ('per_app_asg_pkg.check_end_date', 10);
1787: open c1 ;
1788: fetch c1 into l_dummy_date ;
1789: close c1 ;
1790: if ( l_dummy_date < hr_general.end_of_time ) then
1787: open c1 ;
1788: fetch c1 into l_dummy_date ;
1789: close c1 ;
1790: if ( l_dummy_date < hr_general.end_of_time ) then
1791: hr_utility.set_message ( 801 , 'HR_ASS_FUTURE_END' ) ;
1792: hr_utility.set_warning ;
1793: p_warning_set := true ;
1794: else
1795: p_warning_set := false ;
1788: fetch c1 into l_dummy_date ;
1789: close c1 ;
1790: if ( l_dummy_date < hr_general.end_of_time ) then
1791: hr_utility.set_message ( 801 , 'HR_ASS_FUTURE_END' ) ;
1792: hr_utility.set_warning ;
1793: p_warning_set := true ;
1794: else
1795: p_warning_set := false ;
1796: end if;
1794: else
1795: p_warning_set := false ;
1796: end if;
1797: --
1798: hr_utility.set_location ('per_app_asg_pkg.check_end_date', 20);
1799: end check_end_date ;
1800: --
1801: procedure check_assignment_continuity ( p_business_group_id in number,
1802: p_assignment_id in number,
1834: between a.effective_start_date
1835: and a.effective_end_date ;
1836: begin
1837: --
1838: hr_utility.set_location ('per_app_asg_pkg.check_assignment_continuity', 10);
1839: -- Check that there is at least one other assignment for this person
1840: open c1 ;
1841: fetch c1 into l_dummy ;
1842: if c1%notfound then
1840: open c1 ;
1841: fetch c1 into l_dummy ;
1842: if c1%notfound then
1843: close c1 ;
1844: hr_utility.set_message(801,'HR_7075_APL_ASS_ONLY_ASS' ) ;
1845: hr_utility.raise_error ;
1846: end if;
1847: close c1 ;
1848: --
1841: fetch c1 into l_dummy ;
1842: if c1%notfound then
1843: close c1 ;
1844: hr_utility.set_message(801,'HR_7075_APL_ASS_ONLY_ASS' ) ;
1845: hr_utility.raise_error ;
1846: end if;
1847: close c1 ;
1848: --
1849: -- Get the end date of the given application
1846: end if;
1847: close c1 ;
1848: --
1849: -- Get the end date of the given application
1850: hr_utility.set_location ('per_app_asg_pkg.check_assignment_continuity', 20);
1851: open c2 ;
1852: fetch c2 into l_target_end_date ;
1853: if c2%notfound then
1854: close c2 ;
1851: open c2 ;
1852: fetch c2 into l_target_end_date ;
1853: if c2%notfound then
1854: close c2 ;
1855: hr_utility.set_message(801,'HR_6078_ALL_ZONE_TRIGGER_FAIL');
1856: hr_utility.set_message_token('TRIGGER' , 'check_assignment_continuity' );
1857: hr_utility.raise_error ;
1858: end if;
1859: close c2 ;
1852: fetch c2 into l_target_end_date ;
1853: if c2%notfound then
1854: close c2 ;
1855: hr_utility.set_message(801,'HR_6078_ALL_ZONE_TRIGGER_FAIL');
1856: hr_utility.set_message_token('TRIGGER' , 'check_assignment_continuity' );
1857: hr_utility.raise_error ;
1858: end if;
1859: close c2 ;
1860: --
1853: if c2%notfound then
1854: close c2 ;
1855: hr_utility.set_message(801,'HR_6078_ALL_ZONE_TRIGGER_FAIL');
1856: hr_utility.set_message_token('TRIGGER' , 'check_assignment_continuity' );
1857: hr_utility.raise_error ;
1858: end if;
1859: close c2 ;
1860: --
1861: --
1862: loop
1863: --
1864: exit when l_target_end_date = l_max_end_date ;
1865: --
1866: hr_utility.set_location ('per_app_asg_pkg.check_assignment_continuity', 30);
1867: open c3 ;
1868: fetch c3 into l_max_end_date ;
1869: close c3 ;
1870: --
1868: fetch c3 into l_max_end_date ;
1869: close c3 ;
1870: --
1871: if ( l_max_end_date is null ) then
1872: hr_utility.set_message(801,'HR_6069_APP_ASS_NO_CONTIN');
1873: hr_utility.raise_error ;
1874: end if ;
1875: --
1876: end loop ;
1869: close c3 ;
1870: --
1871: if ( l_max_end_date is null ) then
1872: hr_utility.set_message(801,'HR_6069_APP_ASS_NO_CONTIN');
1873: hr_utility.raise_error ;
1874: end if ;
1875: --
1876: end loop ;
1877: --
1874: end if ;
1875: --
1876: end loop ;
1877: --
1878: hr_utility.set_location ('per_app_asg_pkg.check_assignment_continuity', 40);
1879: end check_assignment_continuity ;
1880: --
1881: --
1882: procedure process_end_status ( p_business_group_id in number,
1917: --
1918: --
1919: -- check whether this assignment has been hired into EMP
1920: --
1921: hr_utility.set_location ('per_app_asg_pkg.process_end_status', 10);
1922:
1923: l_exists := 'N';
1924: open csr_hire_exists(p_assignment_id, p_session_date+1);
1925: fetch csr_hire_exists into l_exists;
1924: open csr_hire_exists(p_assignment_id, p_session_date+1);
1925: fetch csr_hire_exists into l_exists;
1926: if csr_hire_exists%FOUND then
1927: close csr_hire_exists;
1928: hr_utility.set_message(800,'HR_6071_APP_ASS_INVALID_END');
1929: hr_utility.raise_error;
1930: else
1931: close csr_hire_exists;
1932: end if;
1925: fetch csr_hire_exists into l_exists;
1926: if csr_hire_exists%FOUND then
1927: close csr_hire_exists;
1928: hr_utility.set_message(800,'HR_6071_APP_ASS_INVALID_END');
1929: hr_utility.raise_error;
1930: else
1931: close csr_hire_exists;
1932: end if;
1933: --
1932: end if;
1933: --
1934: -- check whether there are other assignments
1935: --
1936: hr_utility.set_location ('per_app_asg_pkg.process_end_status', 20);
1937:
1938: open csr_other_asgs(p_assignment_id, p_application_id);
1939: fetch csr_other_asgs into l_exists;
1940: if csr_other_asgs%NOTFOUND then
1938: open csr_other_asgs(p_assignment_id, p_application_id);
1939: fetch csr_other_asgs into l_exists;
1940: if csr_other_asgs%NOTFOUND then
1941: close csr_other_asgs;
1942: hr_utility.set_message(800,'HR_7075_APL_ASS_ONLY_ASS');
1943: hr_utility.raise_error;
1944: else
1945: close csr_other_asgs;
1946: end if;
1939: fetch csr_other_asgs into l_exists;
1940: if csr_other_asgs%NOTFOUND then
1941: close csr_other_asgs;
1942: hr_utility.set_message(800,'HR_7075_APL_ASS_ONLY_ASS');
1943: hr_utility.raise_error;
1944: else
1945: close csr_other_asgs;
1946: end if;
1947: --
1948: hr_assignment.del_ref_int_check ( p_assignment_id,
1949: 'END',
1950: p_session_date ) ;
1951: --
1952: hr_utility.set_location ('per_app_asg_pkg.process_end_status', 30);
1953: end process_end_status ;
1954: --
1955: --
1956: -- ***temp OBSOLETE ?
1973: from per_assignments_f
1974: where assignment_id = p_assignment_id ;
1975: begin
1976: --
1977: hr_utility.set_location ('per_app_asg_pkg.key_delrec', 10);
1978: if ( p_delete_mode in ( 'FUTURE_CHANGE' , 'DELETE_NEXT_CHANGE' ) ) then
1979: --
1980: check_future_stat_change( p_assignment_id => p_assignment_id ) ;
1981: --
1980: check_future_stat_change( p_assignment_id => p_assignment_id ) ;
1981: --
1982: elsif ( p_delete_mode = 'ZAP' ) then
1983: --
1984: hr_utility.set_location ('per_app_asg_pkg.key_delrec', 20);
1985: check_future_stat_change( p_assignment_id => p_assignment_id ) ;
1986: open c1 ;
1987: fetch c1 into l_max_end_date ;
1988: close c1 ;
1986: open c1 ;
1987: fetch c1 into l_max_end_date ;
1988: close c1 ;
1989: --
1990: hr_utility.set_location ('per_app_asg_pkg.key_delrec', 30);
1991: check_assignment_continuity ( p_business_group_id,
1992: p_assignment_id,
1993: p_person_id,
1994: l_max_end_date,
1993: p_person_id,
1994: l_max_end_date,
1995: p_session_date ) ;
1996: --
1997: hr_utility.set_location ('per_app_asg_pkg.key_delrec', 40);
1998: hr_assignment.del_ref_int_check ( p_assignment_id,
1999: 'ZAP',
2000: p_validation_start_date ) ;
2001: --
2003: 'p_delete_mode',
2004: p_delete_mode ) ;
2005: end if;
2006: --
2007: hr_utility.set_location ('per_app_asg_pkg.key_delrec', 50);
2008: end key_delrec ;
2009: --
2010: procedure pre_delete_validation ( p_business_group_id in number,
2011: p_assignment_id in number,
2039: end check_appl_term ;
2040: --
2041: begin
2042: --
2043: hr_utility.set_location ('per_app_asg_pkg.pre_delete_validation', 10);
2044: if ( p_delete_mode in ( 'FUTURE_CHANGE' , 'DELETE_NEXT_CHANGE' ) ) then
2045: --
2046: check_future_stat_change( p_assignment_id => p_assignment_id ) ;
2047: check_appl_term ( p_application_id => p_application_id,
2049: p_new_end_date => p_new_end_date ) ;
2050: --
2051: elsif ( p_delete_mode = 'ZAP' ) then
2052: --
2053: hr_utility.set_location ('per_app_asg_pkg.pre_delete_validation', 20);
2054: check_future_stat_change( p_assignment_id => p_assignment_id ) ;
2055: open c1 ;
2056: fetch c1 into l_max_end_date ;
2057: close c1 ;
2055: open c1 ;
2056: fetch c1 into l_max_end_date ;
2057: close c1 ;
2058: --
2059: hr_utility.set_location ('per_app_asg_pkg.pre_delete_validation', 25);
2060: check_assignment_continuity ( p_business_group_id,
2061: p_assignment_id,
2062: p_person_id,
2063: l_max_end_date,
2062: p_person_id,
2063: l_max_end_date,
2064: p_session_date ) ;
2065: --
2066: hr_utility.set_location ('per_app_asg_pkg.pre_delete_validation', 27);
2067: hr_assignment.del_ref_int_check ( p_assignment_id,
2068: 'ZAP',
2069: p_validation_start_date ) ;
2070: --
2072: 'p_delete_mode',
2073: p_delete_mode ) ;
2074: end if;
2075: --
2076: hr_utility.set_location ('per_app_asg_pkg.pre_delete_validation', 30);
2077: end pre_delete_validation ;
2078: --
2079: --
2080: procedure post_delete ( p_assignment_id in number,
2084: -- point placements will be lost, as assignment this will
2085: -- not be used only catched locally.
2086: begin
2087: --
2088: hr_utility.set_location ('per_app_asg_pkg.post_delete', 10);
2089: hr_assignment.del_ref_int_delete ( p_assignment_id,
2090: NULL,
2091: 'ZAP',
2092: p_validation_start_date,
2097: null,
2098: null,
2099: l_out_parameter
2100: ) ;
2101: hr_utility.set_location ('per_app_asg_pkg.post_delete', 20);
2102:
2103: cleanup_letters ( p_assignment_id ) ;
2104: --
2105: hr_utility.set_location ('per_app_asg_pkg.post_delete', 30);
2101: hr_utility.set_location ('per_app_asg_pkg.post_delete', 20);
2102:
2103: cleanup_letters ( p_assignment_id ) ;
2104: --
2105: hr_utility.set_location ('per_app_asg_pkg.post_delete', 30);
2106: end post_delete ;
2107: --
2108: --
2109: procedure chk_upd_mode ( p_event varchar2,
2121: where p_assignment_id = a.assignment_id
2122: and (a.effective_start_date < p_effective_start_date);
2123: --
2124: begin
2125: hr_utility.set_location ('per_app_asg_pkg.chk_upd_mode', 5);
2126: if p_object = 'ASSGT' then
2127: hr_utility.set_location ('per_app_asg_pkg.chk_upd_mode', 10);
2128: open csr_chk_mode;
2129: fetch csr_chk_mode into l_dummy;
2123: --
2124: begin
2125: hr_utility.set_location ('per_app_asg_pkg.chk_upd_mode', 5);
2126: if p_object = 'ASSGT' then
2127: hr_utility.set_location ('per_app_asg_pkg.chk_upd_mode', 10);
2128: open csr_chk_mode;
2129: fetch csr_chk_mode into l_dummy;
2130: if p_event = 'WHEN-VALIDATE-ITEM'
2131: and ( p_update_mode = 'UPDATE' or p_update_mode is null OR l_dummy = 'Y' ) then
2145: end if;
2146: close csr_chk_mode;
2147: l_dummy := 'N';
2148: elsif ( p_event = 'WHEN-VALIDATE-ITEM' and p_object = 'STATUS' and p_per_system_status = 'TERM_APL') then
2149: hr_utility.set_location ('per_app_asg_pkg.chk_upd_mode', 20);
2150: open csr_chk_mode;
2151: fetch csr_chk_mode into l_dummy;
2152: if p_update_mode = 'CORRECTION' and l_dummy <> 'Y' then
2153: p_allowed := 'STATUSES_INSERT';
2151: fetch csr_chk_mode into l_dummy;
2152: if p_update_mode = 'CORRECTION' and l_dummy <> 'Y' then
2153: p_allowed := 'STATUSES_INSERT';
2154: -- 3652025: allow termination on the same day
2155: -- hr_utility.set_message(801,'HR_6006_APP_ASS_INVL_FIRST_STA' );
2156: -- hr_utility.raise_error ;
2157: else
2158: p_allowed := 'STATUSES_UPDATE';
2159: end if;
2152: if p_update_mode = 'CORRECTION' and l_dummy <> 'Y' then
2153: p_allowed := 'STATUSES_INSERT';
2154: -- 3652025: allow termination on the same day
2155: -- hr_utility.set_message(801,'HR_6006_APP_ASS_INVL_FIRST_STA' );
2156: -- hr_utility.raise_error ;
2157: else
2158: p_allowed := 'STATUSES_UPDATE';
2159: end if;
2160: close csr_chk_mode;
2160: close csr_chk_mode;
2161: l_dummy := 'N';
2162:
2163: end if;
2164: hr_utility.set_location ('per_app_asg_pkg.chk_upd_mode', 30);
2165: end chk_upd_mode;
2166:
2167:
2168: