eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IRC_OFFERS_API dependencies on IRC_OFFERS

Line 1: Package Body IRC_OFFERS_API as

1: Package Body IRC_OFFERS_API as
2: /* $Header: iriofapi.pkb 120.24.12010000.3 2008/12/30 16:48:10 vmummidi ship $ */
3: --
4: -- Package Variables
5: --

Line 6: g_package varchar2(33) := 'IRC_OFFERS_API.';

2: /* $Header: iriofapi.pkb 120.24.12010000.3 2008/12/30 16:48:10 vmummidi ship $ */
3: --
4: -- Package Variables
5: --
6: g_package varchar2(33) := 'IRC_OFFERS_API.';
7: --
8: -- ----------------------------------------------------------------------------
9: -- |---------------------< create_offer_assignment_copy >---------------------|
10: -- ----------------------------------------------------------------------------

Line 592: l_object_version_number irc_offers.object_version_number%TYPE;

588: ,P_STATUS_CHANGE_DATE IN date default null
589: ) is
590: --
591: l_proc varchar2(72) := g_package||'update_latest_offer_flag';
592: l_object_version_number irc_offers.object_version_number%TYPE;
593: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
594: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
595: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
596: l_offer_version irc_offers.offer_version%TYPE;

Line 595: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;

591: l_proc varchar2(72) := g_package||'update_latest_offer_flag';
592: l_object_version_number irc_offers.object_version_number%TYPE;
593: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
594: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
595: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
596: l_offer_version irc_offers.offer_version%TYPE;
597: l_updated_offer_status irc_offers.offer_status%TYPE;
598: --
599: l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;

Line 596: l_offer_version irc_offers.offer_version%TYPE;

592: l_object_version_number irc_offers.object_version_number%TYPE;
593: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
594: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
595: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
596: l_offer_version irc_offers.offer_version%TYPE;
597: l_updated_offer_status irc_offers.offer_status%TYPE;
598: --
599: l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
600: l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;

Line 597: l_updated_offer_status irc_offers.offer_status%TYPE;

593: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
594: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
595: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
596: l_offer_version irc_offers.offer_version%TYPE;
597: l_updated_offer_status irc_offers.offer_status%TYPE;
598: --
599: l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
600: l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
601: l_business_group_id per_all_assignments_f.business_group_id%TYPE;

Line 611: from irc_offers

607: cursor csr_offer_details is
608: select offer_assignment_id
609: ,object_version_number
610: ,offer_status
611: from irc_offers
612: where offer_id = p_offer_id;
613: --
614: cursor csr_assignment_ovn is
615: select object_version_number

Line 735: l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;

731: )
732: Is
733: --
734: l_proc varchar2(72) := g_package||'copy_offer_asg_to_appl_asg';
735: l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
736: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
737: --
738: -- Out and In Out variables
739: --

Line 736: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;

732: Is
733: --
734: l_proc varchar2(72) := g_package||'copy_offer_asg_to_appl_asg';
735: l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
736: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
737: --
738: -- Out and In Out variables
739: --
740: l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;

Line 760: from irc_offers

756: --
757: cursor csr_assignment_ids is
758: select applicant_assignment_id
759: ,offer_assignment_id
760: from irc_offers
761: where offer_id = p_offer_id;
762: --
763: cursor csr_appl_asg_effective_date is
764: select effective_start_date

Line 1139: l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;

1135: l_proposed_salary_warning boolean;
1136: l_approved_warning boolean;
1137: l_payroll_warning boolean;
1138:
1139: l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
1140: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
1141: --
1142: -- Define cursors
1143: --

Line 1140: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;

1136: l_approved_warning boolean;
1137: l_payroll_warning boolean;
1138:
1139: l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
1140: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
1141: --
1142: -- Define cursors
1143: --
1144: cursor csr_assignment_ids is

Line 1147: from irc_offers

1143: --
1144: cursor csr_assignment_ids is
1145: select applicant_assignment_id
1146: ,offer_assignment_id
1147: from irc_offers
1148: where offer_id = p_offer_id;
1149: --
1150: cursor csr_appl_pay_proposal is
1151: select pay_proposal_id

Line 1368: FROM IRC_OFFERS otheroffer

1364: AND effective_end_date;
1365: --
1366: CURSOR csr_other_extended_offers IS
1367: SELECT count(otheroffer.offer_id)
1368: FROM IRC_OFFERS otheroffer
1369: ,PER_ALL_ASSIGNMENTS_F otherasg
1370: ,PER_ALL_PEOPLE_F per
1371: ,PER_ALL_PEOPLE_F linkper
1372: ,IRC_OFFER_STATUS_HISTORY otherhistory

Line 1459: where transaction_ref_table='IRC_OFFERS'

1455: select transaction_id
1456: ,item_type
1457: ,item_key
1458: from hr_api_transactions
1459: where transaction_ref_table='IRC_OFFERS'
1460: and transaction_ref_id = p_offer_id;
1461: --
1462: cursor csr_get_sfl_txn_details is
1463: select hat.transaction_id

Line 1468: where hat.transaction_ref_table='IRC_OFFERS'

1464: ,wn.MESSAGE_TYPE
1465: ,wn.item_key
1466: from hr_api_transactions hat,
1467: wf_notifications wn
1468: where hat.transaction_ref_table='IRC_OFFERS'
1469: and hat.STATUS='S'
1470: and wn.MESSAGE_TYPE='HRSFL'
1471: and to_char(hat.transaction_id)=wn.user_key
1472: and hat.ASSIGNMENT_ID = p_applicant_assignment_id;

Line 1598: l_offer_id irc_offers.offer_id%TYPE;

1594: --
1595: -- Declare cursors and local variables
1596: --
1597: l_proc varchar2(72) := g_package||'create_offer';
1598: l_offer_id irc_offers.offer_id%TYPE;
1599: l_object_version_number irc_offers.object_version_number%TYPE := 1;
1600: l_effective_date date;
1601: l_expiry_date irc_offers.expiry_date%TYPE;
1602: l_proposed_start_date irc_offers.proposed_start_date%TYPE;

Line 1599: l_object_version_number irc_offers.object_version_number%TYPE := 1;

1595: -- Declare cursors and local variables
1596: --
1597: l_proc varchar2(72) := g_package||'create_offer';
1598: l_offer_id irc_offers.offer_id%TYPE;
1599: l_object_version_number irc_offers.object_version_number%TYPE := 1;
1600: l_effective_date date;
1601: l_expiry_date irc_offers.expiry_date%TYPE;
1602: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
1603: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;

Line 1601: l_expiry_date irc_offers.expiry_date%TYPE;

1597: l_proc varchar2(72) := g_package||'create_offer';
1598: l_offer_id irc_offers.offer_id%TYPE;
1599: l_object_version_number irc_offers.object_version_number%TYPE := 1;
1600: l_effective_date date;
1601: l_expiry_date irc_offers.expiry_date%TYPE;
1602: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
1603: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
1604: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
1605: l_latest_offer irc_offers.latest_offer%TYPE;

Line 1602: l_proposed_start_date irc_offers.proposed_start_date%TYPE;

1598: l_offer_id irc_offers.offer_id%TYPE;
1599: l_object_version_number irc_offers.object_version_number%TYPE := 1;
1600: l_effective_date date;
1601: l_expiry_date irc_offers.expiry_date%TYPE;
1602: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
1603: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
1604: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
1605: l_latest_offer irc_offers.latest_offer%TYPE;
1606: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;

Line 1603: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;

1599: l_object_version_number irc_offers.object_version_number%TYPE := 1;
1600: l_effective_date date;
1601: l_expiry_date irc_offers.expiry_date%TYPE;
1602: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
1603: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
1604: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
1605: l_latest_offer irc_offers.latest_offer%TYPE;
1606: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
1607: l_status_change_date irc_offer_status_history.status_change_date%TYPE;

Line 1605: l_latest_offer irc_offers.latest_offer%TYPE;

1601: l_expiry_date irc_offers.expiry_date%TYPE;
1602: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
1603: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
1604: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
1605: l_latest_offer irc_offers.latest_offer%TYPE;
1606: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
1607: l_status_change_date irc_offer_status_history.status_change_date%TYPE;
1608: l_offer_version irc_offers.offer_version%TYPE;
1609: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;

Line 1608: l_offer_version irc_offers.offer_version%TYPE;

1604: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
1605: l_latest_offer irc_offers.latest_offer%TYPE;
1606: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
1607: l_status_change_date irc_offer_status_history.status_change_date%TYPE;
1608: l_offer_version irc_offers.offer_version%TYPE;
1609: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
1610: --
1611: l_updated_offer_id irc_offers.offer_id%TYPE;
1612: l_updated_offer_status irc_offers.offer_status%TYPE;

Line 1611: l_updated_offer_id irc_offers.offer_id%TYPE;

1607: l_status_change_date irc_offer_status_history.status_change_date%TYPE;
1608: l_offer_version irc_offers.offer_version%TYPE;
1609: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
1610: --
1611: l_updated_offer_id irc_offers.offer_id%TYPE;
1612: l_updated_offer_status irc_offers.offer_status%TYPE;
1613: l_updated_appl_assignment_id irc_offers.applicant_assignment_id%TYPE;
1614: --
1615: cursor csr_latest_offer is

Line 1612: l_updated_offer_status irc_offers.offer_status%TYPE;

1608: l_offer_version irc_offers.offer_version%TYPE;
1609: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
1610: --
1611: l_updated_offer_id irc_offers.offer_id%TYPE;
1612: l_updated_offer_status irc_offers.offer_status%TYPE;
1613: l_updated_appl_assignment_id irc_offers.applicant_assignment_id%TYPE;
1614: --
1615: cursor csr_latest_offer is
1616: select offer_id

Line 1613: l_updated_appl_assignment_id irc_offers.applicant_assignment_id%TYPE;

1609: l_asg_object_version_number per_all_assignments_f.object_version_number%TYPE;
1610: --
1611: l_updated_offer_id irc_offers.offer_id%TYPE;
1612: l_updated_offer_status irc_offers.offer_status%TYPE;
1613: l_updated_appl_assignment_id irc_offers.applicant_assignment_id%TYPE;
1614: --
1615: cursor csr_latest_offer is
1616: select offer_id
1617: ,offer_status

Line 1619: from irc_offers

1615: cursor csr_latest_offer is
1616: select offer_id
1617: ,offer_status
1618: ,applicant_assignment_id
1619: from irc_offers
1620: where latest_offer = 'Y'
1621: and applicant_assignment_id = p_applicant_assignment_id;
1622: --
1623: begin

Line 1706: IRC_OFFERS_BK1.create_offer_b

1702: --
1703: -- Call Before Process User Hook
1704: --
1705: begin
1706: IRC_OFFERS_BK1.create_offer_b
1707: ( P_EFFECTIVE_DATE => l_effective_date
1708: , P_LATEST_OFFER => l_latest_offer
1709: , P_OFFER_STATUS => P_OFFER_STATUS
1710: , P_DISCRETIONARY_JOB_TITLE => P_DISCRETIONARY_JOB_TITLE

Line 1844: IRC_OFFERS_BK1.create_offer_a

1840: --
1841: -- Call After Process User Hook
1842: --
1843: begin
1844: IRC_OFFERS_BK1.create_offer_a
1845: ( P_EFFECTIVE_DATE => l_effective_date
1846: , P_LATEST_OFFER => l_latest_offer
1847: , P_OFFER_STATUS => P_OFFER_STATUS
1848: , P_DISCRETIONARY_JOB_TITLE => P_DISCRETIONARY_JOB_TITLE

Line 2017: l_object_version_number irc_offers.object_version_number%TYPE;

2013: --
2014: -- Declare local variables
2015: --
2016: l_proc varchar2(72) := g_package||'update_offer';
2017: l_object_version_number irc_offers.object_version_number%TYPE;
2018: l_effective_date date;
2019: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
2020: l_expiry_date irc_offers.expiry_date%TYPE;
2021: l_prev_expiry_date irc_offers.expiry_date%TYPE;

Line 2019: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;

2015: --
2016: l_proc varchar2(72) := g_package||'update_offer';
2017: l_object_version_number irc_offers.object_version_number%TYPE;
2018: l_effective_date date;
2019: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
2020: l_expiry_date irc_offers.expiry_date%TYPE;
2021: l_prev_expiry_date irc_offers.expiry_date%TYPE;
2022: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
2023: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;

Line 2020: l_expiry_date irc_offers.expiry_date%TYPE;

2016: l_proc varchar2(72) := g_package||'update_offer';
2017: l_object_version_number irc_offers.object_version_number%TYPE;
2018: l_effective_date date;
2019: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
2020: l_expiry_date irc_offers.expiry_date%TYPE;
2021: l_prev_expiry_date irc_offers.expiry_date%TYPE;
2022: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
2023: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2024: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;

Line 2021: l_prev_expiry_date irc_offers.expiry_date%TYPE;

2017: l_object_version_number irc_offers.object_version_number%TYPE;
2018: l_effective_date date;
2019: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
2020: l_expiry_date irc_offers.expiry_date%TYPE;
2021: l_prev_expiry_date irc_offers.expiry_date%TYPE;
2022: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
2023: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2024: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
2025: l_prev_offer_status irc_offers.offer_status%TYPE;

Line 2022: l_proposed_start_date irc_offers.proposed_start_date%TYPE;

2018: l_effective_date date;
2019: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
2020: l_expiry_date irc_offers.expiry_date%TYPE;
2021: l_prev_expiry_date irc_offers.expiry_date%TYPE;
2022: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
2023: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2024: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
2025: l_prev_offer_status irc_offers.offer_status%TYPE;
2026: l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;

Line 2023: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;

2019: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
2020: l_expiry_date irc_offers.expiry_date%TYPE;
2021: l_prev_expiry_date irc_offers.expiry_date%TYPE;
2022: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
2023: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2024: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
2025: l_prev_offer_status irc_offers.offer_status%TYPE;
2026: l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
2027: l_latest_offer irc_offers.latest_offer%TYPE := hr_api.g_varchar2;

Line 2025: l_prev_offer_status irc_offers.offer_status%TYPE;

2021: l_prev_expiry_date irc_offers.expiry_date%TYPE;
2022: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
2023: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2024: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
2025: l_prev_offer_status irc_offers.offer_status%TYPE;
2026: l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
2027: l_latest_offer irc_offers.latest_offer%TYPE := hr_api.g_varchar2;
2028: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
2029: l_status_change_date irc_offer_status_history.status_change_date%TYPE;

Line 2026: l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;

2022: l_proposed_start_date irc_offers.proposed_start_date%TYPE;
2023: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2024: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
2025: l_prev_offer_status irc_offers.offer_status%TYPE;
2026: l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
2027: l_latest_offer irc_offers.latest_offer%TYPE := hr_api.g_varchar2;
2028: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
2029: l_status_change_date irc_offer_status_history.status_change_date%TYPE;
2030: l_mutiple_fields_updated boolean;

Line 2027: l_latest_offer irc_offers.latest_offer%TYPE := hr_api.g_varchar2;

2023: l_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2024: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
2025: l_prev_offer_status irc_offers.offer_status%TYPE;
2026: l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
2027: l_latest_offer irc_offers.latest_offer%TYPE := hr_api.g_varchar2;
2028: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
2029: l_status_change_date irc_offer_status_history.status_change_date%TYPE;
2030: l_mutiple_fields_updated boolean;
2031: l_offer_version irc_offers.offer_version%TYPE;

Line 2031: l_offer_version irc_offers.offer_version%TYPE;

2027: l_latest_offer irc_offers.latest_offer%TYPE := hr_api.g_varchar2;
2028: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
2029: l_status_change_date irc_offer_status_history.status_change_date%TYPE;
2030: l_mutiple_fields_updated boolean;
2031: l_offer_version irc_offers.offer_version%TYPE;
2032: l_offer_status irc_offers.offer_status%TYPE := p_offer_status;
2033: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
2034:
2035: l_create_new_version boolean := false;

Line 2032: l_offer_status irc_offers.offer_status%TYPE := p_offer_status;

2028: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
2029: l_status_change_date irc_offer_status_history.status_change_date%TYPE;
2030: l_mutiple_fields_updated boolean;
2031: l_offer_version irc_offers.offer_version%TYPE;
2032: l_offer_status irc_offers.offer_status%TYPE := p_offer_status;
2033: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
2034:
2035: l_create_new_version boolean := false;
2036: l_other_extended_offer_count number := 0;

Line 2033: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;

2029: l_status_change_date irc_offer_status_history.status_change_date%TYPE;
2030: l_mutiple_fields_updated boolean;
2031: l_offer_version irc_offers.offer_version%TYPE;
2032: l_offer_status irc_offers.offer_status%TYPE := p_offer_status;
2033: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
2034:
2035: l_create_new_version boolean := false;
2036: l_other_extended_offer_count number := 0;
2037: --

Line 2046: l_prev_offer_assignment_id irc_offers.offer_assignment_id%TYPE;

2042: l_org_now_no_manager_warning boolean;
2043: l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
2044: l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
2045: --
2046: l_prev_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
2047: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
2048: l_offer_letter_tracking_code irc_offers.offer_letter_tracking_code%TYPE;
2049: l_offer_postal_service irc_offers.offer_postal_service%TYPE;
2050: l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE;

Line 2047: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;

2043: l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
2044: l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
2045: --
2046: l_prev_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
2047: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
2048: l_offer_letter_tracking_code irc_offers.offer_letter_tracking_code%TYPE;
2049: l_offer_postal_service irc_offers.offer_postal_service%TYPE;
2050: l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2051: l_prev_offer_ovn irc_offers.object_version_number%TYPE;

Line 2048: l_offer_letter_tracking_code irc_offers.offer_letter_tracking_code%TYPE;

2044: l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
2045: --
2046: l_prev_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
2047: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
2048: l_offer_letter_tracking_code irc_offers.offer_letter_tracking_code%TYPE;
2049: l_offer_postal_service irc_offers.offer_postal_service%TYPE;
2050: l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2051: l_prev_offer_ovn irc_offers.object_version_number%TYPE;
2052: l_prev_offer_change_reason irc_offer_status_history.change_reason%TYPE;

Line 2049: l_offer_postal_service irc_offers.offer_postal_service%TYPE;

2045: --
2046: l_prev_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
2047: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
2048: l_offer_letter_tracking_code irc_offers.offer_letter_tracking_code%TYPE;
2049: l_offer_postal_service irc_offers.offer_postal_service%TYPE;
2050: l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2051: l_prev_offer_ovn irc_offers.object_version_number%TYPE;
2052: l_prev_offer_change_reason irc_offer_status_history.change_reason%TYPE;
2053: --

Line 2050: l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE;

2046: l_prev_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
2047: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
2048: l_offer_letter_tracking_code irc_offers.offer_letter_tracking_code%TYPE;
2049: l_offer_postal_service irc_offers.offer_postal_service%TYPE;
2050: l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2051: l_prev_offer_ovn irc_offers.object_version_number%TYPE;
2052: l_prev_offer_change_reason irc_offer_status_history.change_reason%TYPE;
2053: --
2054: -- Declare cursors

Line 2051: l_prev_offer_ovn irc_offers.object_version_number%TYPE;

2047: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
2048: l_offer_letter_tracking_code irc_offers.offer_letter_tracking_code%TYPE;
2049: l_offer_postal_service irc_offers.offer_postal_service%TYPE;
2050: l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE;
2051: l_prev_offer_ovn irc_offers.object_version_number%TYPE;
2052: l_prev_offer_change_reason irc_offer_status_history.change_reason%TYPE;
2053: --
2054: -- Declare cursors
2055: --

Line 2065: from irc_offers

2061: ,offer_letter_tracking_code
2062: ,offer_postal_service
2063: ,offer_shipping_date
2064: ,object_version_number
2065: from irc_offers
2066: where offer_id = p_offer_id;
2067: --
2068: cursor csr_assignment_record is
2069: select object_version_number

Line 2134: from irc_offers

2130: ,attribute27
2131: ,attribute28
2132: ,attribute29
2133: ,attribute30
2134: from irc_offers
2135: where offer_id = p_offer_id;
2136:
2137: l_offer_record csr_offer_record%ROWTYPE;
2138: l_temp_offer_record csr_offer_record%ROWTYPE;

Line 2634: IRC_OFFERS_BK2.update_offer_b

2630: --
2631: -- Proceed with normal Update.
2632: --
2633: begin
2634: IRC_OFFERS_BK2.update_offer_b
2635: ( P_EFFECTIVE_DATE => l_effective_date
2636: , P_OFFER_ID => P_OFFER_ID
2637: , P_LATEST_OFFER => l_latest_offer
2638: , P_OFFER_STATUS => l_offer_status

Line 3046: irc_offers_api.update_appl_assg_status

3042: -- to set the appropriate Assignment Status for the application.
3043: --
3044: hr_utility.set_location(l_proc,141);
3045: --
3046: irc_offers_api.update_appl_assg_status
3047: ( p_validate => p_validate
3048: , p_effective_date => l_effective_date
3049: , p_applicant_assignment_id => l_prev_applicant_assignment_id
3050: , p_offer_status => l_offer_status

Line 3138: IRC_OFFERS_BK2.update_offer_a

3134: --
3135: -- Call After Process User Hook
3136: --
3137: begin
3138: IRC_OFFERS_BK2.update_offer_a
3139: ( P_EFFECTIVE_DATE => l_effective_date
3140: , P_OFFER_ID => P_OFFER_ID
3141: , P_OFFER_VERSION => l_offer_version
3142: , P_LATEST_OFFER => l_latest_offer

Line 3267: l_offer_id irc_offers.offer_id%TYPE;

3263: --
3264: -- Declare cursors and local variables
3265: --
3266: l_proc varchar2(72) := g_package||'delete_offer';
3267: l_offer_id irc_offers.offer_id%TYPE;
3268: l_prev_offer_status irc_offers.offer_status%TYPE;
3269: l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
3270: l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
3271: l_effective_date date;

Line 3268: l_prev_offer_status irc_offers.offer_status%TYPE;

3264: -- Declare cursors and local variables
3265: --
3266: l_proc varchar2(72) := g_package||'delete_offer';
3267: l_offer_id irc_offers.offer_id%TYPE;
3268: l_prev_offer_status irc_offers.offer_status%TYPE;
3269: l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
3270: l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
3271: l_effective_date date;
3272: l_validation_start_date date;

Line 3274: l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;

3270: l_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
3271: l_effective_date date;
3272: l_validation_start_date date;
3273: l_validation_end_date date;
3274: l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
3275: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
3276: l_offer_status irc_offers.offer_status%TYPE;
3277: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3278: l_asg_object_version_number number(9);

Line 3275: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;

3271: l_effective_date date;
3272: l_validation_start_date date;
3273: l_validation_end_date date;
3274: l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
3275: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
3276: l_offer_status irc_offers.offer_status%TYPE;
3277: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3278: l_asg_object_version_number number(9);
3279: l_org_now_no_manager_warning boolean;

Line 3276: l_offer_status irc_offers.offer_status%TYPE;

3272: l_validation_start_date date;
3273: l_validation_end_date date;
3274: l_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
3275: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE;
3276: l_offer_status irc_offers.offer_status%TYPE;
3277: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3278: l_asg_object_version_number number(9);
3279: l_org_now_no_manager_warning boolean;
3280: --

Line 3285: from irc_offers

3281: cursor csr_prev_offer_rec is
3282: select offer_status
3283: ,applicant_assignment_id
3284: ,offer_assignment_id
3285: from irc_offers
3286: where offer_id = p_offer_id;
3287: --
3288: l_prev_offer_rec csr_prev_offer_rec%ROWTYPE;
3289: --

Line 3293: from irc_offers

3289: --
3290: cursor csr_latest_offer is
3291: select offer_id
3292: ,offer_status
3293: from irc_offers
3294: where applicant_assignment_id = l_applicant_assignment_id
3295: and offer_version = ( select max(offer_version) from irc_offers
3296: where applicant_assignment_id = l_applicant_assignment_id );
3297: --

Line 3295: and offer_version = ( select max(offer_version) from irc_offers

3291: select offer_id
3292: ,offer_status
3293: from irc_offers
3294: where applicant_assignment_id = l_applicant_assignment_id
3295: and offer_version = ( select max(offer_version) from irc_offers
3296: where applicant_assignment_id = l_applicant_assignment_id );
3297: --
3298: cursor csr_assignment_ovn is
3299: select object_version_number

Line 3343: IRC_OFFERS_BK3.delete_offer_b

3339: --
3340: -- Call Before Process User Hook
3341: --
3342: begin
3343: IRC_OFFERS_BK3.delete_offer_b
3344: (
3345: P_OBJECT_VERSION_NUMBER
3346: ,P_OFFER_ID
3347: );

Line 3454: IRC_OFFERS_BK3.delete_offer_a

3450: --
3451: -- Call After Process User Hook
3452: --
3453: begin
3454: IRC_OFFERS_BK3.delete_offer_a
3455: (
3456: P_OBJECT_VERSION_NUMBER
3457: ,P_OFFER_ID
3458: );

Line 3517: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE := null;

3513: ,P_STATUS_CHANGE_DATE IN date default null
3514: ) Is
3515: --
3516: l_proc varchar2(72) := g_package||'close_offer';
3517: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE := null;
3518: l_offer_id irc_offers.offer_id%TYPE := null;
3519: l_prev_offer_status irc_offers.offer_status%TYPE;
3520: l_offer_version irc_offers.offer_version%TYPE;
3521: l_offer_assignment_exists boolean := true;

Line 3518: l_offer_id irc_offers.offer_id%TYPE := null;

3514: ) Is
3515: --
3516: l_proc varchar2(72) := g_package||'close_offer';
3517: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE := null;
3518: l_offer_id irc_offers.offer_id%TYPE := null;
3519: l_prev_offer_status irc_offers.offer_status%TYPE;
3520: l_offer_version irc_offers.offer_version%TYPE;
3521: l_offer_assignment_exists boolean := true;
3522: l_iof_object_version_number irc_offers.object_version_number%TYPE;

Line 3519: l_prev_offer_status irc_offers.offer_status%TYPE;

3515: --
3516: l_proc varchar2(72) := g_package||'close_offer';
3517: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE := null;
3518: l_offer_id irc_offers.offer_id%TYPE := null;
3519: l_prev_offer_status irc_offers.offer_status%TYPE;
3520: l_offer_version irc_offers.offer_version%TYPE;
3521: l_offer_assignment_exists boolean := true;
3522: l_iof_object_version_number irc_offers.object_version_number%TYPE;
3523: -- Date variables

Line 3520: l_offer_version irc_offers.offer_version%TYPE;

3516: l_proc varchar2(72) := g_package||'close_offer';
3517: l_offer_assignment_id irc_offers.offer_assignment_id%TYPE := null;
3518: l_offer_id irc_offers.offer_id%TYPE := null;
3519: l_prev_offer_status irc_offers.offer_status%TYPE;
3520: l_offer_version irc_offers.offer_version%TYPE;
3521: l_offer_assignment_exists boolean := true;
3522: l_iof_object_version_number irc_offers.object_version_number%TYPE;
3523: -- Date variables
3524: l_effective_date date;

Line 3522: l_iof_object_version_number irc_offers.object_version_number%TYPE;

3518: l_offer_id irc_offers.offer_id%TYPE := null;
3519: l_prev_offer_status irc_offers.offer_status%TYPE;
3520: l_offer_version irc_offers.offer_version%TYPE;
3521: l_offer_assignment_exists boolean := true;
3522: l_iof_object_version_number irc_offers.object_version_number%TYPE;
3523: -- Date variables
3524: l_effective_date date;
3525: l_status_change_date irc_offer_status_history.status_change_date%TYPE;
3526: -- Cursors

Line 3531: from irc_offers

3527: cursor csr_offer_assignment_1 is
3528: select offer_assignment_id
3529: ,offer_id
3530: ,offer_status
3531: from irc_offers
3532: where applicant_assignment_id = p_applicant_assignment_id
3533: and latest_offer = 'Y';
3534: --
3535: cursor csr_saved_offers is

Line 3538: from irc_offers

3534: --
3535: cursor csr_saved_offers is
3536: select offer_id
3537: ,object_version_number
3538: from irc_offers
3539: where applicant_assignment_id = p_applicant_assignment_id
3540: and offer_status = 'SAVED';
3541: --
3542: cursor csr_offer_assignment_2 is

Line 3545: from irc_offers

3541: --
3542: cursor csr_offer_assignment_2 is
3543: select offer_assignment_id
3544: ,offer_status
3545: from irc_offers
3546: where offer_id = p_offer_id;
3547: --
3548: cursor csr_iof_object_version_number is
3549: select object_version_number

Line 3550: from irc_offers

3546: where offer_id = p_offer_id;
3547: --
3548: cursor csr_iof_object_version_number is
3549: select object_version_number
3550: from irc_offers
3551: where offer_id = l_offer_id;
3552: --
3553: Begin
3554: --

Line 3655: -- Update the offer record in IRC_OFFERS and set the offer_status to

3651: end if;
3652: --
3653: close csr_iof_object_version_number;
3654: --
3655: -- Update the offer record in IRC_OFFERS and set the offer_status to
3656: -- Closed and end date the offer assignment.
3657: -- Do this only if the offer is previously not in CLOSED status.
3658: -- Also do this if the change reason is MGR_WITHDRAW, APL_DECLINED_ACCEPTANCE, APL_HIRED
3659: if l_prev_offer_status <> 'CLOSED' or p_change_reason='MGR_WITHDRAW' or p_change_reason='APL_DECLINED_ACCEPTANCE' or p_change_reason='APL_HIRED'

Line 3762: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;

3758: ,P_OBJECT_VERSION_NUMBER IN OUT nocopy NUMBER
3759: ) is
3760: --
3761: l_proc varchar2(72) := g_package||'hold_offer';
3762: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
3763: l_offer_version irc_offers.offer_version%TYPE;
3764: --
3765: Begin
3766: hr_utility.set_location('Entering:'||l_proc,10);

Line 3763: l_offer_version irc_offers.offer_version%TYPE;

3759: ) is
3760: --
3761: l_proc varchar2(72) := g_package||'hold_offer';
3762: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
3763: l_offer_version irc_offers.offer_version%TYPE;
3764: --
3765: Begin
3766: hr_utility.set_location('Entering:'||l_proc,10);
3767: --

Line 3805: l_prev_offer_status irc_offers.offer_status%TYPE;

3801: ,P_OBJECT_VERSION_NUMBER IN OUT nocopy NUMBER
3802: ) is
3803: --
3804: l_proc varchar2(72) := g_package||'release_offer';
3805: l_prev_offer_status irc_offers.offer_status%TYPE;
3806: l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
3807: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
3808: l_prev_to_prev_change_reason irc_offer_status_history.change_reason%TYPE;
3809: l_prev_to_prev_decline_reason irc_offer_status_history.decline_reason%TYPE;

Line 3806: l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;

3802: ) is
3803: --
3804: l_proc varchar2(72) := g_package||'release_offer';
3805: l_prev_offer_status irc_offers.offer_status%TYPE;
3806: l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
3807: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
3808: l_prev_to_prev_change_reason irc_offer_status_history.change_reason%TYPE;
3809: l_prev_to_prev_decline_reason irc_offer_status_history.decline_reason%TYPE;
3810: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;

Line 3807: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;

3803: --
3804: l_proc varchar2(72) := g_package||'release_offer';
3805: l_prev_offer_status irc_offers.offer_status%TYPE;
3806: l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
3807: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
3808: l_prev_to_prev_change_reason irc_offer_status_history.change_reason%TYPE;
3809: l_prev_to_prev_decline_reason irc_offer_status_history.decline_reason%TYPE;
3810: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
3811: l_offer_version irc_offers.offer_version%TYPE;

Line 3810: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;

3806: l_prev_to_prev_offer_status irc_offers.offer_status%TYPE;
3807: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
3808: l_prev_to_prev_change_reason irc_offer_status_history.change_reason%TYPE;
3809: l_prev_to_prev_decline_reason irc_offer_status_history.decline_reason%TYPE;
3810: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
3811: l_offer_version irc_offers.offer_version%TYPE;
3812: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
3813: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
3814: l_other_extended_offer_count number := 0;

Line 3811: l_offer_version irc_offers.offer_version%TYPE;

3807: l_prev_applicant_assignment_id irc_offers.applicant_assignment_id%TYPE;
3808: l_prev_to_prev_change_reason irc_offer_status_history.change_reason%TYPE;
3809: l_prev_to_prev_decline_reason irc_offer_status_history.decline_reason%TYPE;
3810: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
3811: l_offer_version irc_offers.offer_version%TYPE;
3812: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
3813: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
3814: l_other_extended_offer_count number := 0;
3815: l_prev_expiry_date irc_offers.expiry_date%TYPE;

Line 3815: l_prev_expiry_date irc_offers.expiry_date%TYPE;

3811: l_offer_version irc_offers.offer_version%TYPE;
3812: l_offer_status_history_id irc_offer_status_history.offer_status_history_id%TYPE;
3813: l_osh_object_version_number irc_offer_status_history.object_version_number%TYPE;
3814: l_other_extended_offer_count number := 0;
3815: l_prev_expiry_date irc_offers.expiry_date%TYPE;
3816: --
3817: cursor csr_prev_to_prev_offer_status is
3818: SELECT ios1.offer_status,
3819: ios1.change_reason,

Line 3836: from irc_offers

3832: cursor csr_prev_offer_status is
3833: select offer_status
3834: ,applicant_assignment_id
3835: ,expiry_date
3836: from irc_offers
3837: where offer_id = p_offer_id;
3838: --
3839: Begin
3840: --

Line 4479: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;

4475: l_org_now_no_manager_warning boolean;
4476: l_validation_start_date date;
4477: l_validation_end_date date;
4478: l_effective_date date;
4479: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
4480:
4481: l_prev_offer_status irc_offers.offer_status%TYPE;
4482: l_iof_object_version_number irc_offers.object_version_number%TYPE;
4483: l_offer_version irc_offers.offer_version%TYPE;

Line 4481: l_prev_offer_status irc_offers.offer_status%TYPE;

4477: l_validation_end_date date;
4478: l_effective_date date;
4479: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
4480:
4481: l_prev_offer_status irc_offers.offer_status%TYPE;
4482: l_iof_object_version_number irc_offers.object_version_number%TYPE;
4483: l_offer_version irc_offers.offer_version%TYPE;
4484: l_datetrack_mode varchar2(30) := hr_api.g_update;
4485: --

Line 4482: l_iof_object_version_number irc_offers.object_version_number%TYPE;

4478: l_effective_date date;
4479: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
4480:
4481: l_prev_offer_status irc_offers.offer_status%TYPE;
4482: l_iof_object_version_number irc_offers.object_version_number%TYPE;
4483: l_offer_version irc_offers.offer_version%TYPE;
4484: l_datetrack_mode varchar2(30) := hr_api.g_update;
4485: --
4486: cursor csr_prev_offer_details is

Line 4483: l_offer_version irc_offers.offer_version%TYPE;

4479: l_offer_id irc_offers.offer_id%TYPE := p_offer_id;
4480:
4481: l_prev_offer_status irc_offers.offer_status%TYPE;
4482: l_iof_object_version_number irc_offers.object_version_number%TYPE;
4483: l_offer_version irc_offers.offer_version%TYPE;
4484: l_datetrack_mode varchar2(30) := hr_api.g_update;
4485: --
4486: cursor csr_prev_offer_details is
4487: select offer_status

Line 4489: from irc_offers

4485: --
4486: cursor csr_prev_offer_details is
4487: select offer_status
4488: ,offer_assignment_id
4489: from irc_offers
4490: where offer_id = p_offer_id;
4491: --
4492: cursor csr_asg_effective_start_date is
4493: select effective_start_date

Line 4550: from irc_offers

4546: ,attribute27
4547: ,attribute28
4548: ,attribute29
4549: ,attribute30
4550: from irc_offers
4551: where offer_id = p_offer_id;
4552:
4553: l_offer_record csr_offer_record%ROWTYPE;
4554: --

Line 4963: l_offer_id irc_offers.offer_id%TYPE;

4959: ,P_OFFER_ASSIGNMENT_ID IN number
4960: ) Is
4961: --
4962: l_proc varchar2(72) := g_package||'delete_offer_assignment';
4963: l_offer_id irc_offers.offer_id%TYPE;
4964: l_offer_object_version_number irc_offers.object_version_number%TYPE;
4965: --
4966: cursor csr_offer_id is
4967: select offer_id

Line 4964: l_offer_object_version_number irc_offers.object_version_number%TYPE;

4960: ) Is
4961: --
4962: l_proc varchar2(72) := g_package||'delete_offer_assignment';
4963: l_offer_id irc_offers.offer_id%TYPE;
4964: l_offer_object_version_number irc_offers.object_version_number%TYPE;
4965: --
4966: cursor csr_offer_id is
4967: select offer_id
4968: ,object_version_number

Line 4969: from irc_offers

4965: --
4966: cursor csr_offer_id is
4967: select offer_id
4968: ,object_version_number
4969: from irc_offers
4970: where offer_assignment_id = p_offer_assignment_id;
4971: --
4972: Begin
4973: hr_utility.set_location('Entering:'|| l_proc, 10);

Line 5094: end IRC_OFFERS_API;

5090: hr_utility.set_location(' Leaving:'||l_proc, 60);
5091: raise;
5092: end upload_offer_letter;
5093: --
5094: end IRC_OFFERS_API;