[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_program_update_date per_all_assignments_f.program_update_date%TYPE;
select
business_group_id
,recruiter_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,supervisor_id
,special_ceiling_step_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,pay_basis_id
,assignment_sequence
,assignment_type
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,employment_category
,frequency
,internal_address_line
,manager_flag
,normal_hours
,perf_review_period
,perf_review_period_frequency
,period_of_service_id
,probation_period
,probation_unit
,sal_review_period
,sal_review_period_frequency
,set_of_books_id
,source_type
,time_normal_finish
,time_normal_start
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,request_id
,program_application_id
,program_id
,program_update_date
,ass_attribute_category
,ass_attribute1
,ass_attribute2
,ass_attribute3
,ass_attribute4
,ass_attribute5
,ass_attribute6
,ass_attribute7
,ass_attribute8
,ass_attribute9
,ass_attribute10
,ass_attribute11
,ass_attribute12
,ass_attribute13
,ass_attribute14
,ass_attribute15
,ass_attribute16
,ass_attribute17
,ass_attribute18
,ass_attribute19
,ass_attribute20
,ass_attribute21
,ass_attribute22
,ass_attribute23
,ass_attribute24
,ass_attribute25
,ass_attribute26
,ass_attribute27
,ass_attribute28
,ass_attribute29
,ass_attribute30
,title
,contract_id
,establishment_id
,collective_agreement_id
,cagr_grade_def_id
,cagr_id_flex_num
,notice_period
,notice_period_uom
,employee_category
,work_at_home
,job_post_source_name
,posting_content_id
,vendor_id
,vendor_employee_number
,vendor_assignment_number
,assignment_category
,project_title
,applicant_rank
,grade_ladder_pgm_id
,supervisor_assignment_id
,object_version_number
from per_all_assignments_f
where assignment_id = p_source_assignment_id
and p_effective_date
between effective_start_date
and effective_end_date;
l_program_update_date := trunc(l_offer_assignment.program_update_date);
,p_program_update_date => l_program_update_date
,p_ass_attribute_category => l_offer_assignment.ass_attribute_category
,p_ass_attribute1 => l_offer_assignment.ass_attribute1
,p_ass_attribute2 => l_offer_assignment.ass_attribute2
,p_ass_attribute3 => l_offer_assignment.ass_attribute3
,p_ass_attribute4 => l_offer_assignment.ass_attribute4
,p_ass_attribute5 => l_offer_assignment.ass_attribute5
,p_ass_attribute6 => l_offer_assignment.ass_attribute6
,p_ass_attribute7 => l_offer_assignment.ass_attribute7
,p_ass_attribute8 => l_offer_assignment.ass_attribute8
,p_ass_attribute9 => l_offer_assignment.ass_attribute9
,p_ass_attribute10 => l_offer_assignment.ass_attribute10
,p_ass_attribute11 => l_offer_assignment.ass_attribute11
,p_ass_attribute12 => l_offer_assignment.ass_attribute12
,p_ass_attribute13 => l_offer_assignment.ass_attribute13
,p_ass_attribute14 => l_offer_assignment.ass_attribute14
,p_ass_attribute15 => l_offer_assignment.ass_attribute15
,p_ass_attribute16 => l_offer_assignment.ass_attribute16
,p_ass_attribute17 => l_offer_assignment.ass_attribute17
,p_ass_attribute18 => l_offer_assignment.ass_attribute18
,p_ass_attribute19 => l_offer_assignment.ass_attribute19
,p_ass_attribute20 => l_offer_assignment.ass_attribute20
,p_ass_attribute21 => l_offer_assignment.ass_attribute21
,p_ass_attribute22 => l_offer_assignment.ass_attribute22
,p_ass_attribute23 => l_offer_assignment.ass_attribute23
,p_ass_attribute24 => l_offer_assignment.ass_attribute24
,p_ass_attribute25 => l_offer_assignment.ass_attribute25
,p_ass_attribute26 => l_offer_assignment.ass_attribute26
,p_ass_attribute27 => l_offer_assignment.ass_attribute27
,p_ass_attribute28 => l_offer_assignment.ass_attribute28
,p_ass_attribute29 => l_offer_assignment.ass_attribute29
,p_ass_attribute30 => l_offer_assignment.ass_attribute30
,p_title => l_offer_assignment.title
,p_object_version_number => l_object_version_number
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_effective_date => l_effective_date
,p_validate => p_validate
,p_contract_id => l_offer_assignment.contract_id
,p_establishment_id => l_offer_assignment.establishment_id
,p_collective_agreement_id => l_offer_assignment.collective_agreement_id
,p_cagr_grade_def_id => l_offer_assignment.cagr_grade_def_id
,p_cagr_id_flex_num => l_offer_assignment.cagr_id_flex_num
,p_notice_period => l_offer_assignment.notice_period
,p_notice_period_uom => l_offer_assignment.notice_period_uom
,p_employee_category => l_offer_assignment.employee_category
,p_work_at_home => l_offer_assignment.work_at_home
,p_job_post_source_name => l_offer_assignment.job_post_source_name
,p_posting_content_id => l_offer_assignment.posting_content_id
,p_vendor_id => l_offer_assignment.vendor_id
,p_vendor_employee_number => l_offer_assignment.vendor_employee_number
,p_vendor_assignment_number => l_offer_assignment.vendor_assignment_number
,p_assignment_category => l_offer_assignment.assignment_category
,p_project_title => l_offer_assignment.project_title
,p_applicant_rank => l_offer_assignment.applicant_rank
,p_grade_ladder_pgm_id => l_offer_assignment.grade_ladder_pgm_id
,p_supervisor_assignment_id => l_offer_assignment.supervisor_assignment_id
);
procedure update_appl_assg_status
( P_VALIDATE IN boolean default false
,P_EFFECTIVE_DATE IN date default null
,P_APPLICANT_ASSIGNMENT_ID IN NUMBER
,P_OFFER_STATUS IN VARCHAR2
,P_CHANGE_REASON IN VARCHAR2 default null
,P_CALL_FOR_BASE_ASG IN boolean default false
) is
--
l_proc varchar2(72) := g_package||'update_appl_assg_status';
select object_version_number
,effective_start_date
,assignment_status_type_id
,business_group_id
from per_all_assignments_f
where assignment_id = p_applicant_assignment_id
and p_effective_date
between effective_start_date
and effective_end_date;
select ASSIGNMENT_STATUS_TYPE_ID
from PER_ASSIGNMENT_STATUS_TYPES_V
where PER_SYSTEM_STATUS='OFFER'
and DEFAULT_FLAG = 'Y'
and BUSINESS_GROUP_ID = p_business_group_id;
select ASSIGNMENT_STATUS_TYPE_ID
from PER_ASSIGNMENT_STATUS_TYPES_V
where PER_SYSTEM_STATUS='ACCEPTED'
and DEFAULT_FLAG = 'Y'
and BUSINESS_GROUP_ID = p_business_group_id;
select ias1.assignment_status_type_id
,past.per_system_status
from irc_assignment_statuses ias1
,per_assignment_status_types past
where past.assignment_status_type_id = ias1.assignment_status_type_id
and ias1.assignment_id = p_applicant_assignment_id
and ias1.creation_date = (select max(ias2.creation_date)
from irc_assignment_statuses ias2
where ias2.assignment_id = p_applicant_assignment_id
and ias2.assignment_status_type_id not in (5,6)
and ias2.assignment_status_type_id not in (select ASSIGNMENT_STATUS_TYPE_ID
from PER_ASSIGNMENT_STATUS_TYPES_V
where PER_SYSTEM_STATUS in ('OFFER','ACCEPTED')
and BUSINESS_GROUP_ID = p_business_group_id));
select src_apl_asg_id
from per_vac_linked_assignments
where tgt_apl_asg_id=p_applicant_assignment_id;
if p_change_reason = 'UPDATED' or p_change_reason = 'APL_DECLINED_ACCEPTANCE' or p_change_reason = 'MGR_WITHDRAW'
then
--
-- This is the case where the offer is updated when in EXTENDED or CLOSED status.
-- 1) The EXTENDED offer is closed and a new offer version is
-- created. We hence need to set back the Applicant's assignment status to
-- the status which existed before the most recent 'OFFER' Applicant assignment status.
-- 2) The offer was previously in CLOSED status before the Applicant
-- WITHDREW his application it could have been CLOSED previously due to 2
-- reasons:
-- 1. Applicant Accepted the offer - We need to rollback 2 steps here.
-- One, ACCEPTED assignment status (6)
-- Two, OFFER assignment status (5)
-- 2. Applicant Declined the offer - Do nothing in this case
-- or, Offer Duration Expired.
--
open csr_prev_assg_status_type(l_business_group_id);
l_datetrack_mode := hr_api.g_update;
hr_assignment_internal.update_status_type_apl_asg
( p_effective_date => p_effective_date
,p_datetrack_update_mode => l_datetrack_mode
,p_assignment_id => p_applicant_assignment_id
,p_object_version_number => l_asg_object_version_number
,p_expected_system_status => l_expected_system_status
,p_assignment_status_type_id => l_assignment_status_type_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
update_appl_assg_status
( P_VALIDATE =>P_VALIDATE
,P_EFFECTIVE_DATE =>P_EFFECTIVE_DATE
,P_APPLICANT_ASSIGNMENT_ID =>L_BASE_ASG_ID
,P_OFFER_STATUS =>P_OFFER_STATUS
,P_CHANGE_REASON =>P_CHANGE_REASON
,P_CALL_FOR_BASE_ASG =>true
);
end update_appl_assg_status;
procedure update_latest_offer_flag
( P_VALIDATE IN boolean default false
,P_EFFECTIVE_DATE IN date default null
,P_OFFER_ID IN NUMBER
,P_OFFER_STATUS IN VARCHAR2 default hr_api.g_varchar2
,P_LATEST_OFFER IN VARCHAR2
,P_CHANGE_REASON IN VARCHAR2 default null
,P_STATUS_CHANGE_DATE IN date default null
) is
--
l_proc varchar2(72) := g_package||'update_latest_offer_flag';
l_updated_offer_status irc_offers.offer_status%TYPE;
select offer_assignment_id
,object_version_number
,offer_status
from irc_offers
where offer_id = p_offer_id;
select object_version_number
from per_all_assignments_f
where assignment_id = l_offer_assignment_id;
,l_updated_offer_status;
and p_offer_status <> l_updated_offer_status
then
--
hr_utility.set_location(l_proc, 50);
and p_offer_status <> l_updated_offer_status
then
--
hr_utility.set_location(l_proc, 60);
,p_datetrack_mode => hr_api.g_delete
,p_validate => p_validate
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
end update_latest_offer_flag;
l_payroll_id_updated boolean;
l_datetrack_mode varchar2(30) := hr_api.g_update;
select applicant_assignment_id
,offer_assignment_id
from irc_offers
where offer_id = p_offer_id;
select effective_start_date
,object_version_number
from per_all_assignments_f
where assignment_id = l_applicant_assignment_id
and p_effective_date
between effective_start_date
and effective_end_date;
select
business_group_id
,recruiter_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,supervisor_id
,special_ceiling_step_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,pay_basis_id
,assignment_sequence
,assignment_type
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,employment_category
,frequency
,internal_address_line
,manager_flag
,normal_hours
,perf_review_period
,perf_review_period_frequency
,period_of_service_id
,probation_period
,probation_unit
,sal_review_period
,sal_review_period_frequency
,set_of_books_id
,source_type
,time_normal_finish
,time_normal_start
,request_id
,program_application_id
,program_id
,program_update_date
,ass_attribute_category
,ass_attribute1
,ass_attribute2
,ass_attribute3
,ass_attribute4
,ass_attribute5
,ass_attribute6
,ass_attribute7
,ass_attribute8
,ass_attribute9
,ass_attribute10
,ass_attribute11
,ass_attribute12
,ass_attribute13
,ass_attribute14
,ass_attribute15
,ass_attribute16
,ass_attribute17
,ass_attribute18
,ass_attribute19
,ass_attribute20
,ass_attribute21
,ass_attribute22
,ass_attribute23
,ass_attribute24
,ass_attribute25
,ass_attribute26
,ass_attribute27
,ass_attribute28
,ass_attribute29
,ass_attribute30
,title
,object_version_number
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,contract_id
,collective_agreement_id
,cagr_id_flex_num
,cagr_grade_def_id
,establishment_id
,notice_period
,notice_period_uom
,employee_category
,work_at_home
,job_post_source_name
,posting_content_id
,applicant_rank
,period_of_placement_date_start
,vendor_id
,vendor_employee_number
,vendor_assignment_number
,assignment_category
,project_title
,grade_ladder_pgm_id
,supervisor_assignment_id
,vendor_site_id
,po_header_id
,po_line_id
,projected_assignment_end
from per_all_assignments_f
where assignment_id = l_offer_assignment_id
and p_effective_date
between effective_start_date
and effective_end_date;
,p_program_update_date => l_offer_assignment.program_update_date
,p_ass_attribute_category => l_offer_assignment.ass_attribute_category
,p_ass_attribute1 => l_offer_assignment.ass_attribute1
,p_ass_attribute2 => l_offer_assignment.ass_attribute2
,p_ass_attribute3 => l_offer_assignment.ass_attribute3
,p_ass_attribute4 => l_offer_assignment.ass_attribute4
,p_ass_attribute5 => l_offer_assignment.ass_attribute5
,p_ass_attribute6 => l_offer_assignment.ass_attribute6
,p_ass_attribute7 => l_offer_assignment.ass_attribute7
,p_ass_attribute8 => l_offer_assignment.ass_attribute8
,p_ass_attribute9 => l_offer_assignment.ass_attribute9
,p_ass_attribute10 => l_offer_assignment.ass_attribute10
,p_ass_attribute11 => l_offer_assignment.ass_attribute11
,p_ass_attribute12 => l_offer_assignment.ass_attribute12
,p_ass_attribute13 => l_offer_assignment.ass_attribute13
,p_ass_attribute14 => l_offer_assignment.ass_attribute14
,p_ass_attribute15 => l_offer_assignment.ass_attribute15
,p_ass_attribute16 => l_offer_assignment.ass_attribute16
,p_ass_attribute17 => l_offer_assignment.ass_attribute17
,p_ass_attribute18 => l_offer_assignment.ass_attribute18
,p_ass_attribute19 => l_offer_assignment.ass_attribute19
,p_ass_attribute20 => l_offer_assignment.ass_attribute20
,p_ass_attribute21 => l_offer_assignment.ass_attribute21
,p_ass_attribute22 => l_offer_assignment.ass_attribute22
,p_ass_attribute23 => l_offer_assignment.ass_attribute23
,p_ass_attribute24 => l_offer_assignment.ass_attribute24
,p_ass_attribute25 => l_offer_assignment.ass_attribute25
,p_ass_attribute26 => l_offer_assignment.ass_attribute26
,p_ass_attribute27 => l_offer_assignment.ass_attribute27
,p_ass_attribute28 => l_offer_assignment.ass_attribute28
,p_ass_attribute29 => l_offer_assignment.ass_attribute29
,p_ass_attribute30 => l_offer_assignment.ass_attribute30
,p_title => l_offer_assignment.title
,p_object_version_number => l_object_version_number
,p_bargaining_unit_code => l_offer_assignment.bargaining_unit_code
,p_labour_union_member_flag => l_offer_assignment.labour_union_member_flag
,p_hourly_salaried_code => l_offer_assignment.hourly_salaried_code
,p_contract_id => l_offer_assignment.contract_id
,p_collective_agreement_id => l_offer_assignment.collective_agreement_id
,p_cagr_id_flex_num => l_offer_assignment.cagr_id_flex_num
,p_cagr_grade_def_id => l_offer_assignment.cagr_grade_def_id
,p_establishment_id => l_offer_assignment.establishment_id
,p_notice_period => l_offer_assignment.notice_period
,p_notice_period_uom => l_offer_assignment.notice_period_uom
,p_employee_category => l_offer_assignment.employee_category
,p_work_at_home => l_offer_assignment.work_at_home
,p_job_post_source_name => l_offer_assignment.job_post_source_name
,p_posting_content_id => l_offer_assignment.posting_content_id
,p_applicant_rank => l_offer_assignment.applicant_rank
,p_placement_date_start => l_offer_assignment.period_of_placement_date_start
,p_vendor_id => l_offer_assignment.vendor_id
,p_vendor_employee_number => l_offer_assignment.vendor_employee_number
,p_vendor_assignment_number => l_offer_assignment.vendor_assignment_number
,p_assignment_category => l_offer_assignment.assignment_category
,p_project_title => l_offer_assignment.project_title
,p_grade_ladder_pgm_id => l_offer_assignment.grade_ladder_pgm_id
,p_supervisor_assignment_id => l_offer_assignment.supervisor_assignment_id
,p_vendor_site_id => l_offer_assignment.vendor_site_id
,p_po_header_id => l_offer_assignment.po_header_id
,p_po_line_id => l_offer_assignment.po_line_id
,p_projected_assignment_end => l_offer_assignment.projected_assignment_end
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_effective_date => l_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_validate => p_validate
);
select applicant_assignment_id
,offer_assignment_id
from irc_offers
where offer_id = p_offer_id;
select pay_proposal_id
,object_version_number
from per_pay_proposals
where assignment_id = l_applicant_assignment_id
and approved = 'N';
select
pay_proposal_id
,assignment_id
,event_id
,business_group_id
,change_date
,comments
,last_change_date
,next_perf_review_date
,next_sal_review_date
,performance_rating
,proposal_reason
,proposed_salary
,review_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,object_version_number
,approved
,multiple_components
,forced_ranking
,performance_review_id
,proposed_salary_n
from per_pay_proposals
where assignment_id = p_assignment_id
and approved = 'N';
hr_maintain_proposal_api.delete_salary_proposal
(p_validate => p_validate
,p_pay_proposal_id => l_pay_proposal_record.pay_proposal_id
,p_business_group_id => l_pay_proposal_record.business_group_id
,p_object_version_number => l_pay_proposal_record.object_version_number
,p_salary_warning => l_salary_warning
);
SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = p_applicant_assignment_id
AND p_effective_date
BETWEEN effective_start_date
AND effective_end_date;
SELECT count(otheroffer.offer_id)
FROM IRC_OFFERS otheroffer
,PER_ALL_ASSIGNMENTS_F otherasg
,PER_ALL_PEOPLE_F per
,PER_ALL_PEOPLE_F linkper
,IRC_OFFER_STATUS_HISTORY otherhistory
WHERE (
-- Other Extended Offers
otheroffer.offer_status = 'EXTENDED'
OR
( -- Other Closed and Accepted Offers
otheroffer.offer_status = 'CLOSED'
AND otherhistory.change_reason = 'APL_ACCEPTED'
)
)
AND otherasg.effective_start_date = (select max(effective_start_date)
from per_assignments_f asg2
where otherasg.assignment_id=asg2.assignment_id
and asg2.effective_start_date <= trunc(sysdate)
)
AND p_effective_date BETWEEN trunc(per.effective_start_date) and trunc(nvl(per.effective_end_date,p_effective_date))
AND p_effective_date BETWEEN trunc(linkper.effective_start_date) and trunc(nvl(linkper.effective_end_date,p_effective_date))
AND otheroffer.offer_assignment_id = otherasg.assignment_id
AND otherasg.person_id = linkper.person_id
AND per.person_id = l_person_id
AND linkper.party_id = per.party_id
AND otheroffer.latest_offer = 'Y'
AND otheroffer.applicant_assignment_id <> nvl(p_applicant_assignment_id, -1)
AND otheroffer.applicant_assignment_id <> nvl((select tgt_apl_asg_id from per_vac_linked_assignments where src_apl_asg_id=p_applicant_assignment_id and rownum<2), -1)
AND decode(hr_general.get_xbg_profile,'Y', otherasg.business_group_id , hr_general.get_business_group_id) = otherasg.business_group_id
AND otheroffer.offer_id = otherhistory.offer_id
AND NOT EXISTS
(SELECT 1
FROM irc_offer_status_history iosh1
WHERE iosh1.offer_id = otherhistory.offer_id
AND iosh1.status_change_date > otherhistory.status_change_date
)
AND otherhistory.offer_status_history_id =
(SELECT MAX(iosh2.offer_status_history_id)
FROM irc_offer_status_history iosh2
WHERE iosh2.offer_id = otherhistory.offer_id
AND iosh2.status_change_date = otherhistory.status_change_date
);
select transaction_id
,item_type
,item_key
from hr_api_transactions
where transaction_ref_table='IRC_OFFERS'
and transaction_ref_id = p_offer_id;
select hat.transaction_id
,wn.MESSAGE_TYPE
,wn.item_key
from hr_api_transactions hat,
wf_notifications wn
where hat.transaction_ref_table='IRC_OFFERS'
and hat.STATUS='S'
and wn.MESSAGE_TYPE='HRSFL'
and to_char(hat.transaction_id)=wn.user_key
and hat.ASSIGNMENT_ID = p_applicant_assignment_id;
l_updated_offer_id irc_offers.offer_id%TYPE;
l_updated_offer_status irc_offers.offer_status%TYPE;
l_updated_appl_assignment_id irc_offers.applicant_assignment_id%TYPE;
select offer_id
,offer_status
,applicant_assignment_id
from irc_offers
where latest_offer = 'Y'
and ( applicant_assignment_id = nvl(p_applicant_assignment_id,-1)
OR
applicant_assignment_id = nvl(p_src_apl_asg_id,-1)
OR
applicant_assignment_id =
(
select tgt_apl_asg_id from per_vac_linked_assignments
where src_apl_asg_id = p_src_apl_asg_id
and sysdate between nvl(start_date,sysdate)
and nvl(end_date,sysdate)
)
);
fetch csr_latest_offer into l_updated_offer_id
,l_updated_offer_status
,l_updated_appl_assignment_id;
update_latest_offer_flag
( p_offer_id => l_updated_offer_id
,p_effective_date => l_effective_date
,p_validate => p_validate
,p_offer_status => 'CLOSED'
,p_latest_offer => 'N'
,p_change_reason => 'UPDATED'
,p_status_change_date => l_status_change_date
);
if l_updated_offer_status in ('EXTENDED', 'CLOSED')
then
--
update_appl_assg_status
( p_validate => p_validate
,p_effective_date => l_effective_date
,p_applicant_assignment_id => l_updated_appl_assignment_id
,p_offer_status => l_updated_offer_status
,p_change_reason => 'UPDATED'
);
procedure update_offer
( P_VALIDATE IN boolean default false
,P_EFFECTIVE_DATE IN date default null
,P_OFFER_STATUS IN VARCHAR2 default hr_api.g_varchar2
,P_DISCRETIONARY_JOB_TITLE IN VARCHAR2 default hr_api.g_varchar2
,P_OFFER_EXTENDED_METHOD IN VARCHAR2 default hr_api.g_varchar2
,P_RESPONDENT_ID IN NUMBER default hr_api.g_number
,P_EXPIRY_DATE IN DATE default hr_api.g_date
,P_PROPOSED_START_DATE IN DATE default hr_api.g_date
,P_OFFER_LETTER_TRACKING_CODE IN VARCHAR2 default hr_api.g_varchar2
,P_OFFER_POSTAL_SERVICE IN VARCHAR2 default hr_api.g_varchar2
,P_OFFER_SHIPPING_DATE IN DATE default hr_api.g_date
,P_APPLICANT_ASSIGNMENT_ID IN NUMBER default hr_api.g_number
,P_OFFER_ASSIGNMENT_ID IN NUMBER default hr_api.g_number
,P_ADDRESS_ID IN NUMBER default hr_api.g_number
,P_TEMPLATE_ID IN NUMBER default hr_api.g_number
,P_OFFER_LETTER_FILE_TYPE IN VARCHAR2 default hr_api.g_varchar2
,P_OFFER_LETTER_FILE_NAME IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE_CATEGORY IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE1 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE2 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE3 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE4 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE5 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE6 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE7 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE8 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE9 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE10 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE11 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE12 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE13 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE14 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE15 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE16 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE17 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE18 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE19 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE20 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE21 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE22 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE23 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE24 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE25 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE26 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE27 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE28 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE29 IN VARCHAR2 default hr_api.g_varchar2
,P_ATTRIBUTE30 IN VARCHAR2 default hr_api.g_varchar2
,P_CHANGE_REASON IN VARCHAR2 default null
,P_DECLINE_REASON IN VARCHAR2 default null
,P_NOTE_TEXT IN VARCHAR2 default null
,P_STATUS_CHANGE_DATE IN DATE default null
,P_OFFER_ID IN OUT nocopy NUMBER
,P_OBJECT_VERSION_NUMBER IN OUT nocopy NUMBER
,P_OFFER_VERSION OUT nocopy NUMBER
) is
--
-- Declare local variables
--
l_proc varchar2(72) := g_package||'update_offer';
l_mutiple_fields_updated boolean;
select offer_status
,expiry_date
,applicant_assignment_id
,offer_assignment_id
,offer_letter_tracking_code
,offer_postal_service
,offer_shipping_date
,object_version_number
from irc_offers
where offer_id = p_offer_id;
select object_version_number
,effective_end_date
from per_all_assignments_f
where assignment_id = l_prev_offer_assignment_id;
select HISTORY.offer_status_history_id
from irc_offer_status_history HISTORY
where HISTORY.offer_id = p_offer_id
and NOT EXISTS
(SELECT 1
FROM irc_offer_status_history iosh1
WHERE iosh1.offer_id = HISTORY.offer_id
AND iosh1.status_change_date > HISTORY.status_change_date
)
AND HISTORY.offer_status_history_id =
(SELECT MAX(iosh2.offer_status_history_id)
FROM irc_offer_status_history iosh2
WHERE iosh2.offer_id = HISTORY.offer_id
AND iosh2.status_change_date = HISTORY.status_change_date
);
select change_reason
from irc_offer_status_history
where offer_status_history_id = p_status_history_id;
select
offer_version
,offer_status
,discretionary_job_title
,offer_extended_method
,respondent_id
,expiry_date
,proposed_start_date
,offer_letter_tracking_code
,offer_postal_service
,offer_shipping_date
,vacancy_id
,applicant_assignment_id
,offer_assignment_id
,address_id
,template_id
,offer_letter_file_type
,offer_letter_file_name
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
from irc_offers
where offer_id = p_offer_id;
savepoint UPDATE_OFFER;
irc_iof_bus.chk_multiple_fields_updated
( p_mutiple_fields_updated => l_mutiple_fields_updated
,p_offer_id => p_offer_id
,p_offer_status => p_offer_status
,p_discretionary_job_title => p_discretionary_job_title
,p_offer_extended_method => p_offer_extended_method
,p_expiry_date => l_expiry_date
,p_proposed_start_date => p_proposed_start_date
,p_offer_letter_tracking_code => p_offer_letter_tracking_code
,p_offer_postal_service => p_offer_postal_service
,p_offer_shipping_date => p_offer_shipping_date
,p_applicant_assignment_id => p_applicant_assignment_id
,p_offer_assignment_id => p_offer_assignment_id
,p_address_id => p_address_id
,p_template_id => p_template_id
,p_offer_letter_file_type => p_offer_letter_file_type
,p_offer_letter_file_name => p_offer_letter_file_name
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
);
if ( l_mutiple_fields_updated = false
AND p_offer_status <> 'HOLD' -- Work Around! if ofeer is set to HOLD and the expiry date
-- is not passed in, the flow can come this far.
-- Atleast stop it here.
)
then
l_create_new_version := false;
IRC_OFFERS_BK2.update_offer_b
( P_EFFECTIVE_DATE => l_effective_date
, P_OFFER_ID => P_OFFER_ID
, P_LATEST_OFFER => l_latest_offer
, P_OFFER_STATUS => l_offer_status
, P_DISCRETIONARY_JOB_TITLE => P_DISCRETIONARY_JOB_TITLE
, P_OFFER_EXTENDED_METHOD => P_OFFER_EXTENDED_METHOD
, P_RESPONDENT_ID => P_RESPONDENT_ID
, P_EXPIRY_DATE => l_expiry_date
, P_PROPOSED_START_DATE => l_proposed_start_date
, P_OFFER_LETTER_TRACKING_CODE => P_OFFER_LETTER_TRACKING_CODE
, P_OFFER_POSTAL_SERVICE => P_OFFER_POSTAL_SERVICE
, P_OFFER_SHIPPING_DATE => l_offer_shipping_date
, P_APPLICANT_ASSIGNMENT_ID => P_APPLICANT_ASSIGNMENT_ID
, P_OFFER_ASSIGNMENT_ID => P_OFFER_ASSIGNMENT_ID
, P_ADDRESS_ID => P_ADDRESS_ID
, P_TEMPLATE_ID => P_TEMPLATE_ID
, P_OFFER_LETTER_FILE_TYPE => P_OFFER_LETTER_FILE_TYPE
, P_OFFER_LETTER_FILE_NAME => P_OFFER_LETTER_FILE_NAME
, P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
, P_ATTRIBUTE1 => P_ATTRIBUTE1
, P_ATTRIBUTE2 => P_ATTRIBUTE2
, P_ATTRIBUTE3 => P_ATTRIBUTE3
, P_ATTRIBUTE4 => P_ATTRIBUTE4
, P_ATTRIBUTE5 => P_ATTRIBUTE5
, P_ATTRIBUTE6 => P_ATTRIBUTE6
, P_ATTRIBUTE7 => P_ATTRIBUTE7
, P_ATTRIBUTE8 => P_ATTRIBUTE8
, P_ATTRIBUTE9 => P_ATTRIBUTE9
, P_ATTRIBUTE10 => P_ATTRIBUTE10
, P_ATTRIBUTE11 => P_ATTRIBUTE11
, P_ATTRIBUTE12 => P_ATTRIBUTE12
, P_ATTRIBUTE13 => P_ATTRIBUTE13
, P_ATTRIBUTE14 => P_ATTRIBUTE14
, P_ATTRIBUTE15 => P_ATTRIBUTE15
, P_ATTRIBUTE16 => P_ATTRIBUTE16
, P_ATTRIBUTE17 => P_ATTRIBUTE17
, P_ATTRIBUTE18 => P_ATTRIBUTE18
, P_ATTRIBUTE19 => P_ATTRIBUTE19
, P_ATTRIBUTE20 => P_ATTRIBUTE20
, P_ATTRIBUTE21 => P_ATTRIBUTE21
, P_ATTRIBUTE22 => P_ATTRIBUTE22
, P_ATTRIBUTE23 => P_ATTRIBUTE23
, P_ATTRIBUTE24 => P_ATTRIBUTE24
, P_ATTRIBUTE25 => P_ATTRIBUTE25
, P_ATTRIBUTE26 => P_ATTRIBUTE26
, P_ATTRIBUTE27 => P_ATTRIBUTE27
, P_ATTRIBUTE28 => P_ATTRIBUTE28
, P_ATTRIBUTE29 => P_ATTRIBUTE29
, P_ATTRIBUTE30 => P_ATTRIBUTE30
, P_OBJECT_VERSION_NUMBER => p_object_version_number
);
(p_module_name => 'update_offer'
,p_hook_type => 'BP'
);
irc_iof_bus.chk_multiple_fields_updated
( p_mutiple_fields_updated => l_mutiple_fields_updated
,p_offer_id => p_offer_id
,p_offer_status => l_prev_offer_status
,p_discretionary_job_title => p_discretionary_job_title
,p_offer_extended_method => p_offer_extended_method
,p_expiry_date => l_expiry_date
,p_proposed_start_date => p_proposed_start_date
,p_offer_letter_tracking_code => p_offer_letter_tracking_code
,p_offer_postal_service => p_offer_postal_service
,p_offer_shipping_date => p_offer_shipping_date
,p_applicant_assignment_id => p_applicant_assignment_id
,p_offer_assignment_id => p_offer_assignment_id
,p_address_id => p_address_id
,p_template_id => p_template_id
,p_offer_letter_file_type => p_offer_letter_file_type
,p_offer_letter_file_name => p_offer_letter_file_name
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
);
if ( l_mutiple_fields_updated = false )
then
--
hr_utility.set_location(l_proc,125);
irc_offers_api.update_appl_assg_status
( p_validate => p_validate
, p_effective_date => l_effective_date
, p_applicant_assignment_id => l_prev_applicant_assignment_id
, p_offer_status => l_offer_status
, p_change_reason => p_change_reason
);
hr_utility.set_location('Delete the offer assignment ',143);
,p_datetrack_mode => hr_api.g_delete
,p_validate => p_validate
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
irc_offer_status_history_api.update_offer_status_history
( p_validate => p_validate
, p_effective_date => l_effective_date
, p_offer_status_history_id => l_offer_status_history_id
, p_offer_id => p_offer_id
, p_offer_status => l_offer_status
, p_change_reason => p_change_reason
, p_decline_reason => p_decline_reason
, p_note_text => p_note_text
, p_object_version_number => l_osh_object_version_number
, p_status_change_date => l_status_change_date
);
IRC_OFFERS_BK2.update_offer_a
( P_EFFECTIVE_DATE => l_effective_date
, P_OFFER_ID => P_OFFER_ID
, P_OFFER_VERSION => l_offer_version
, P_LATEST_OFFER => l_latest_offer
, P_OFFER_STATUS => l_offer_status
, P_DISCRETIONARY_JOB_TITLE => P_DISCRETIONARY_JOB_TITLE
, P_OFFER_EXTENDED_METHOD => P_OFFER_EXTENDED_METHOD
, P_RESPONDENT_ID => P_RESPONDENT_ID
, P_EXPIRY_DATE => l_expiry_date
, P_PROPOSED_START_DATE => l_proposed_start_date
, P_OFFER_LETTER_TRACKING_CODE => P_OFFER_LETTER_TRACKING_CODE
, P_OFFER_POSTAL_SERVICE => P_OFFER_POSTAL_SERVICE
, P_OFFER_SHIPPING_DATE => l_offer_shipping_date
, P_APPLICANT_ASSIGNMENT_ID => P_APPLICANT_ASSIGNMENT_ID
, P_OFFER_ASSIGNMENT_ID => P_OFFER_ASSIGNMENT_ID
, P_ADDRESS_ID => P_ADDRESS_ID
, P_TEMPLATE_ID => P_TEMPLATE_ID
, P_OFFER_LETTER_FILE_TYPE => P_OFFER_LETTER_FILE_TYPE
, P_OFFER_LETTER_FILE_NAME => P_OFFER_LETTER_FILE_NAME
, P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
, P_ATTRIBUTE1 => P_ATTRIBUTE1
, P_ATTRIBUTE2 => P_ATTRIBUTE2
, P_ATTRIBUTE3 => P_ATTRIBUTE3
, P_ATTRIBUTE4 => P_ATTRIBUTE4
, P_ATTRIBUTE5 => P_ATTRIBUTE5
, P_ATTRIBUTE6 => P_ATTRIBUTE6
, P_ATTRIBUTE7 => P_ATTRIBUTE7
, P_ATTRIBUTE8 => P_ATTRIBUTE8
, P_ATTRIBUTE9 => P_ATTRIBUTE9
, P_ATTRIBUTE10 => P_ATTRIBUTE10
, P_ATTRIBUTE11 => P_ATTRIBUTE11
, P_ATTRIBUTE12 => P_ATTRIBUTE12
, P_ATTRIBUTE13 => P_ATTRIBUTE13
, P_ATTRIBUTE14 => P_ATTRIBUTE14
, P_ATTRIBUTE15 => P_ATTRIBUTE15
, P_ATTRIBUTE16 => P_ATTRIBUTE16
, P_ATTRIBUTE17 => P_ATTRIBUTE17
, P_ATTRIBUTE18 => P_ATTRIBUTE18
, P_ATTRIBUTE19 => P_ATTRIBUTE19
, P_ATTRIBUTE20 => P_ATTRIBUTE20
, P_ATTRIBUTE21 => P_ATTRIBUTE21
, P_ATTRIBUTE22 => P_ATTRIBUTE22
, P_ATTRIBUTE23 => P_ATTRIBUTE23
, P_ATTRIBUTE24 => P_ATTRIBUTE24
, P_ATTRIBUTE25 => P_ATTRIBUTE25
, P_ATTRIBUTE26 => P_ATTRIBUTE26
, P_ATTRIBUTE27 => P_ATTRIBUTE27
, P_ATTRIBUTE28 => P_ATTRIBUTE28
, P_ATTRIBUTE29 => P_ATTRIBUTE29
, P_ATTRIBUTE30 => P_ATTRIBUTE30
, P_OBJECT_VERSION_NUMBER => p_object_version_number
);
(p_module_name => 'update_offer'
,p_hook_type => 'AP'
);
rollback to UPDATE_OFFER;
rollback to UPDATE_OFFER;
end update_offer;
procedure delete_offer
(
P_VALIDATE in boolean default false
, P_OBJECT_VERSION_NUMBER in number
, P_OFFER_ID in number
, P_EFFECTIVE_DATE in date default null
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_offer';
select offer_status
,applicant_assignment_id
,offer_assignment_id
from irc_offers
where offer_id = p_offer_id;
select offer_id
,offer_status
from irc_offers
where applicant_assignment_id = l_applicant_assignment_id
and offer_version = ( select max(offer_version) from irc_offers
where applicant_assignment_id = l_applicant_assignment_id );
select object_version_number
from per_all_assignments_f
where assignment_id = l_offer_assignment_id;
savepoint DELETE_OFFER;
IRC_OFFERS_BK3.delete_offer_b
(
P_OBJECT_VERSION_NUMBER
,P_OFFER_ID
);
(p_module_name => 'delete_offer'
,p_hook_type => 'BP'
);
irc_offer_status_history_api.delete_offer_status_history
(
p_validate => p_validate
,p_offer_id => p_offer_id
,p_effective_date => l_effective_date
);
update_latest_offer_flag
( p_offer_id => l_offer_id
,p_effective_date => l_effective_date
,p_validate => p_validate
,p_latest_offer => 'Y'
);
IRC_OFFERS_BK3.delete_offer_a
(
P_OBJECT_VERSION_NUMBER
,P_OFFER_ID
);
(p_module_name => 'delete_offer'
,p_hook_type => 'AP'
);
rollback to DELETE_OFFER;
rollback to DELETE_OFFER;
end delete_offer;
select user_id
from per_all_assignments_f paf, fnd_user usr, per_all_people_f ppf,
per_all_people_f linkppf
where p_effective_date between paf.effective_start_date and
paf.effective_end_date
and p_effective_date between usr.start_date and
nvl(usr.end_date,p_effective_date)
and p_effective_date between ppf.effective_start_date and
ppf.effective_end_date
and p_effective_date between linkppf.effective_start_date and
linkppf.effective_end_date
and usr.employee_id=linkppf.person_id
and ppf.party_id = linkppf.party_id
and ppf.person_id = paf.person_id
and paf.assignment_id= p_assignment_id
and usr.user_id = fnd_global.user_id;
select offer_assignment_id
,offer_id
,offer_status
from irc_offers
where applicant_assignment_id = p_applicant_assignment_id
and latest_offer = 'Y';
select offer_id
,object_version_number
from irc_offers
where applicant_assignment_id = p_applicant_assignment_id
and offer_status = 'SAVED';
select offer_assignment_id
,offer_status
from irc_offers
where offer_id = p_offer_id;
select object_version_number
from irc_offers
where offer_id = l_offer_id;
update_offer
(p_validate => p_validate
,p_effective_date => l_effective_date
,p_offer_id => l_offer_id
,p_offer_version => l_offer_version
,p_offer_status => 'CLOSED'
,p_respondent_id => p_respondent_id
,p_change_reason => l_change_reason
,p_decline_reason => p_decline_reason
,p_note_text => p_note_text
,p_status_change_date => p_status_change_date
,p_object_version_number => l_iof_object_version_number
);
update_offer
(p_validate => p_validate
,p_effective_date => l_effective_date
,p_offer_id => c_rec.offer_id
,p_offer_version => l_offer_version
,p_offer_status => 'CLOSED'
,p_respondent_id => p_respondent_id
,p_change_reason => l_change_reason
,p_decline_reason => p_decline_reason
,p_note_text => p_note_text
,p_status_change_date => p_status_change_date
,p_object_version_number => c_rec.object_version_number
);
update_offer
( p_validate => p_validate
,p_effective_date => p_effective_date
,p_offer_id => l_offer_id
,p_offer_version => l_offer_version
,p_object_version_number => p_object_version_number
,p_offer_status => 'HOLD'
,p_respondent_id => p_respondent_id
,p_change_reason => p_change_reason
,p_note_text => p_note_text
,p_status_change_date => p_status_change_date
);
SELECT ios1.offer_status,
ios1.change_reason,
ios1.decline_reason
FROM irc_offer_status_history ios1
WHERE EXISTS ( SELECT 1
FROM irc_offer_status_history iosh1
WHERE iosh1.offer_id = p_offer_id
AND iosh1.status_change_date > ios1.status_change_date
)
AND ios1.offer_status_history_id = (SELECT MAX(iosh2.offer_status_history_id)
FROM irc_offer_status_history iosh2
WHERE iosh2.offer_id = p_offer_id
AND iosh2.status_change_date = ios1.status_change_date
)
AND 1 =
(SELECT COUNT(*)
FROM irc_offer_status_history ios3
WHERE ios3.offer_id = p_offer_id
AND ios3.status_change_date > ios1.status_change_date
);
select offer_status
,applicant_assignment_id
,expiry_date
from irc_offers
where offer_id = p_offer_id;
,p_program_update_date IN DATE default null
,p_ass_attribute_category IN VARCHAR2 default null
,p_ass_attribute1 IN VARCHAR2 default null
,p_ass_attribute2 IN VARCHAR2 default null
,p_ass_attribute3 IN VARCHAR2 default null
,p_ass_attribute4 IN VARCHAR2 default null
,p_ass_attribute5 IN VARCHAR2 default null
,p_ass_attribute6 IN VARCHAR2 default null
,p_ass_attribute7 IN VARCHAR2 default null
,p_ass_attribute8 IN VARCHAR2 default null
,p_ass_attribute9 IN VARCHAR2 default null
,p_ass_attribute10 IN VARCHAR2 default null
,p_ass_attribute11 IN VARCHAR2 default null
,p_ass_attribute12 IN VARCHAR2 default null
,p_ass_attribute13 IN VARCHAR2 default null
,p_ass_attribute14 IN VARCHAR2 default null
,p_ass_attribute15 IN VARCHAR2 default null
,p_ass_attribute16 IN VARCHAR2 default null
,p_ass_attribute17 IN VARCHAR2 default null
,p_ass_attribute18 IN VARCHAR2 default null
,p_ass_attribute19 IN VARCHAR2 default null
,p_ass_attribute20 IN VARCHAR2 default null
,p_ass_attribute21 IN VARCHAR2 default null
,p_ass_attribute22 IN VARCHAR2 default null
,p_ass_attribute23 IN VARCHAR2 default null
,p_ass_attribute24 IN VARCHAR2 default null
,p_ass_attribute25 IN VARCHAR2 default null
,p_ass_attribute26 IN VARCHAR2 default null
,p_ass_attribute27 IN VARCHAR2 default null
,p_ass_attribute28 IN VARCHAR2 default null
,p_ass_attribute29 IN VARCHAR2 default null
,p_ass_attribute30 IN VARCHAR2 default null
,p_title IN VARCHAR2 default null
,p_validate_df_flex IN BOOLEAN default true
,p_object_version_number OUT NOCOPY NUMBER
,p_other_manager_warning OUT NOCOPY BOOLEAN
,p_hourly_salaried_warning OUT NOCOPY BOOLEAN
,p_effective_date IN DATE
,p_validate IN BOOLEAN default false
,p_contract_id IN NUMBER default null
,p_establishment_id IN NUMBER default null
,p_collective_agreement_id IN NUMBER default null
,p_cagr_grade_def_id IN NUMBER default null
,p_cagr_id_flex_num IN NUMBER default null
,p_notice_period IN NUMBER default null
,p_notice_period_uom IN VARCHAR2 default null
,p_employee_category IN VARCHAR2 default null
,p_work_at_home IN VARCHAR2 default null
,p_job_post_source_name IN VARCHAR2 default null
,p_posting_content_id IN NUMBER default null
,p_placement_date_start IN DATE default null
,p_vendor_id IN NUMBER default null
,p_vendor_employee_number IN VARCHAR2 default null
,p_vendor_assignment_number IN VARCHAR2 default null
,p_assignment_category IN VARCHAR2 default null
,p_project_title IN VARCHAR2 default null
,p_applicant_rank IN NUMBER default null
,p_grade_ladder_pgm_id IN NUMBER default null
,p_supervisor_assignment_id IN NUMBER default null
,p_vendor_site_id IN NUMBER default null
,p_po_header_id IN NUMBER default null
,p_po_line_id IN NUMBER default null
,p_projected_assignment_end IN DATE default null
)Is
--
l_proc varchar2(72) := g_package||'create_offer_assignment';
l_program_update_date per_all_assignments_f.program_update_date%TYPE;
l_program_update_date := trunc(p_program_update_date);
,p_program_update_date => l_program_update_date
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_validate_df_flex => p_validate_df_flex
,p_object_version_number => l_object_version_number
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_effective_date => l_effective_date
,p_validate => p_validate
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_grade_def_id => p_cagr_grade_def_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_posting_content_id => p_posting_content_id
,p_placement_date_start => l_placement_date_start
,p_vendor_id => p_vendor_id
,p_vendor_employee_number => p_vendor_employee_number
,p_vendor_assignment_number => p_vendor_assignment_number
,p_assignment_category => p_assignment_category
,p_project_title => p_project_title
,p_applicant_rank => p_applicant_rank
,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
,p_supervisor_assignment_id => p_supervisor_assignment_id
,p_vendor_site_id => p_vendor_site_id
,p_po_header_id => p_po_header_id
,p_po_line_id => p_po_line_id
,p_projected_assignment_end => p_projected_assignment_end
);
procedure update_offer_assignment
( P_ASSIGNMENT_ID IN OUT NOCOPY NUMBER
,P_EFFECTIVE_START_DATE OUT NOCOPY DATE
,P_EFFECTIVE_END_DATE OUT NOCOPY DATE
,P_BUSINESS_GROUP_ID OUT NOCOPY NUMBER
,P_RECRUITER_ID IN NUMBER default hr_api.g_number
,P_GRADE_ID IN NUMBER default hr_api.g_number
,P_POSITION_ID IN NUMBER default hr_api.g_number
,P_JOB_ID IN NUMBER default hr_api.g_number
,P_ASSIGNMENT_STATUS_TYPE_ID IN NUMBER default hr_api.g_number
,P_PAYROLL_ID IN NUMBER default hr_api.g_number
,P_LOCATION_ID IN NUMBER default hr_api.g_number
,P_PERSON_REFERRED_BY_ID IN NUMBER default hr_api.g_number
,P_SUPERVISOR_ID IN NUMBER default hr_api.g_number
,P_SPECIAL_CEILING_STEP_ID IN NUMBER default hr_api.g_number
,P_RECRUITMENT_ACTIVITY_ID IN NUMBER default hr_api.g_number
,P_SOURCE_ORGANIZATION_ID IN NUMBER default hr_api.g_number
,P_ORGANIZATION_ID IN NUMBER default hr_api.g_number
,P_PEOPLE_GROUP_ID IN NUMBER default hr_api.g_number
,P_SOFT_CODING_KEYFLEX_ID IN NUMBER default hr_api.g_number
,P_VACANCY_ID IN NUMBER default hr_api.g_number
,P_PAY_BASIS_ID IN NUMBER default hr_api.g_number
,P_ASSIGNMENT_TYPE IN VARCHAR2 default hr_api.g_varchar2
,P_PRIMARY_FLAG IN VARCHAR2 default hr_api.g_varchar2
,P_APPLICATION_ID IN NUMBER default hr_api.g_number
,P_ASSIGNMENT_NUMBER IN VARCHAR2 default hr_api.g_varchar2
,P_CHANGE_REASON IN VARCHAR2 default hr_api.g_varchar2
,P_COMMENT_ID OUT NOCOPY NUMBER
,P_COMMENTS IN VARCHAR2 default hr_api.g_varchar2
,P_DATE_PROBATION_END IN DATE default hr_api.g_date
,P_DEFAULT_CODE_COMB_ID IN NUMBER default hr_api.g_number
,P_EMPLOYMENT_CATEGORY IN VARCHAR2 default hr_api.g_varchar2
,P_FREQUENCY IN VARCHAR2 default hr_api.g_varchar2
,P_INTERNAL_ADDRESS_LINE IN VARCHAR2 default hr_api.g_varchar2
,P_MANAGER_FLAG IN VARCHAR2 default hr_api.g_varchar2
,P_NORMAL_HOURS IN NUMBER default hr_api.g_number
,P_PERF_REVIEW_PERIOD IN NUMBER default hr_api.g_number
,P_PERF_REVIEW_PERIOD_FREQUENCY IN VARCHAR2 default hr_api.g_varchar2
,P_PERIOD_OF_SERVICE_ID IN NUMBER default hr_api.g_number
,P_PROBATION_PERIOD IN NUMBER default hr_api.g_number
,P_PROBATION_UNIT IN VARCHAR2 default hr_api.g_varchar2
,P_SAL_REVIEW_PERIOD IN NUMBER default hr_api.g_number
,P_SAL_REVIEW_PERIOD_FREQUENCY IN VARCHAR2 default hr_api.g_varchar2
,P_SET_OF_BOOKS_ID IN NUMBER default hr_api.g_number
,P_SOURCE_TYPE IN VARCHAR2 default hr_api.g_varchar2
,P_TIME_NORMAL_FINISH IN VARCHAR2 default hr_api.g_varchar2
,P_TIME_NORMAL_START IN VARCHAR2 default hr_api.g_varchar2
,P_BARGAINING_UNIT_CODE IN VARCHAR2 default hr_api.g_varchar2
,P_LABOUR_UNION_MEMBER_FLAG IN VARCHAR2 default hr_api.g_varchar2
,P_HOURLY_SALARIED_CODE IN VARCHAR2 default hr_api.g_varchar2
,P_REQUEST_ID IN NUMBER default hr_api.g_number
,P_PROGRAM_APPLICATION_ID IN NUMBER default hr_api.g_number
,P_PROGRAM_ID IN NUMBER default hr_api.g_number
,P_PROGRAM_UPDATE_DATE IN DATE default hr_api.g_date
,P_ASS_ATTRIBUTE_CATEGORY IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE1 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE2 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE3 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE4 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE5 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE6 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE7 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE8 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE9 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE10 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE11 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE12 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE13 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE14 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE15 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE16 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE17 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE18 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE19 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE20 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE21 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE22 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE23 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE24 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE25 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE26 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE27 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE28 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE29 IN VARCHAR2 default hr_api.g_varchar2
,P_ASS_ATTRIBUTE30 IN VARCHAR2 default hr_api.g_varchar2
,P_TITLE IN VARCHAR2 default hr_api.g_varchar2
,P_CONTRACT_ID IN NUMBER default hr_api.g_number
,P_ESTABLISHMENT_ID IN NUMBER default hr_api.g_number
,P_COLLECTIVE_AGREEMENT_ID IN NUMBER default hr_api.g_number
,P_CAGR_GRADE_DEF_ID IN NUMBER default hr_api.g_number
,P_CAGR_ID_FLEX_NUM IN NUMBER default hr_api.g_number
,P_ASG_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER
,P_NOTICE_PERIOD IN NUMBER default hr_api.g_number
,P_NOTICE_PERIOD_UOM IN VARCHAR2 default hr_api.g_varchar2
,P_EMPLOYEE_CATEGORY IN VARCHAR2 default hr_api.g_varchar2
,P_WORK_AT_HOME IN VARCHAR2 default hr_api.g_varchar2
,P_JOB_POST_SOURCE_NAME IN VARCHAR2 default hr_api.g_varchar2
,P_POSTING_CONTENT_ID IN NUMBER default hr_api.g_number
,P_PLACEMENT_DATE_START IN DATE default hr_api.g_date
,P_VENDOR_ID IN NUMBER default hr_api.g_number
,P_VENDOR_EMPLOYEE_NUMBER IN VARCHAR2 default hr_api.g_varchar2
,P_VENDOR_ASSIGNMENT_NUMBER IN VARCHAR2 default hr_api.g_varchar2
,P_ASSIGNMENT_CATEGORY IN VARCHAR2 default hr_api.g_varchar2
,P_PROJECT_TITLE IN VARCHAR2 default hr_api.g_varchar2
,P_APPLICANT_RANK IN NUMBER default hr_api.g_number
,P_GRADE_LADDER_PGM_ID IN NUMBER default hr_api.g_number
,P_SUPERVISOR_ASSIGNMENT_ID IN NUMBER default hr_api.g_number
,P_VENDOR_SITE_ID IN NUMBER default hr_api.g_number
,P_PO_HEADER_ID IN NUMBER default hr_api.g_number
,P_PO_LINE_ID IN NUMBER default hr_api.g_number
,P_PROJECTED_ASSIGNMENT_END IN DATE default hr_api.g_date
,P_PAYROLL_ID_UPDATED OUT NOCOPY BOOLEAN
,P_OTHER_MANAGER_WARNING OUT NOCOPY BOOLEAN
,P_HOURLY_SALARIED_WARNING OUT NOCOPY BOOLEAN
,P_NO_MANAGERS_WARNING OUT NOCOPY BOOLEAN
,P_ORG_NOW_NO_MANAGER_WARNING OUT NOCOPY BOOLEAN
,P_VALIDATION_START_DATE OUT NOCOPY DATE
,P_VALIDATION_END_DATE OUT NOCOPY DATE
,P_EFFECTIVE_DATE IN DATE default null
,P_DATETRACK_MODE IN VARCHAR2 default hr_api.g_update
,P_VALIDATE IN BOOLEAN default false
,P_OFFER_ID IN OUT NOCOPY NUMBER
,P_OFFER_STATUS IN VARCHAR2 default null
) Is
--
l_proc varchar2(72) := g_package||'update_offer_assignment';
l_payroll_id_updated boolean;
l_datetrack_mode varchar2(30) := hr_api.g_update;
select offer_status
,offer_assignment_id
from irc_offers
where offer_id = p_offer_id;
select effective_start_date
from per_all_assignments_f
where assignment_id = P_ASSIGNMENT_ID
and p_effective_date
between effective_start_date
and effective_end_date;
select
offer_version
,offer_status
,discretionary_job_title
,offer_extended_method
,respondent_id
,expiry_date
,proposed_start_date
,offer_letter_tracking_code
,offer_postal_service
,offer_shipping_date
,vacancy_id
,applicant_assignment_id
,offer_assignment_id
,address_id
,template_id
,offer_letter_file_type
,offer_letter_file_name
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
from irc_offers
where offer_id = p_offer_id;
savepoint UPDATE_OFFER_ASSIGNMENT;
l_datetrack_mode := hr_api.g_update;
,p_change_reason => 'UPDATED'
,p_status_change_date => l_effective_date
);
,p_program_update_date => p_program_update_date
,p_ass_attribute_category => p_ass_attribute_category
,p_ass_attribute1 => p_ass_attribute1
,p_ass_attribute2 => p_ass_attribute2
,p_ass_attribute3 => p_ass_attribute3
,p_ass_attribute4 => p_ass_attribute4
,p_ass_attribute5 => p_ass_attribute5
,p_ass_attribute6 => p_ass_attribute6
,p_ass_attribute7 => p_ass_attribute7
,p_ass_attribute8 => p_ass_attribute8
,p_ass_attribute9 => p_ass_attribute9
,p_ass_attribute10 => p_ass_attribute10
,p_ass_attribute11 => p_ass_attribute11
,p_ass_attribute12 => p_ass_attribute12
,p_ass_attribute13 => p_ass_attribute13
,p_ass_attribute14 => p_ass_attribute14
,p_ass_attribute15 => p_ass_attribute15
,p_ass_attribute16 => p_ass_attribute16
,p_ass_attribute17 => p_ass_attribute17
,p_ass_attribute18 => p_ass_attribute18
,p_ass_attribute19 => p_ass_attribute19
,p_ass_attribute20 => p_ass_attribute20
,p_ass_attribute21 => p_ass_attribute21
,p_ass_attribute22 => p_ass_attribute22
,p_ass_attribute23 => p_ass_attribute23
,p_ass_attribute24 => p_ass_attribute24
,p_ass_attribute25 => p_ass_attribute25
,p_ass_attribute26 => p_ass_attribute26
,p_ass_attribute27 => p_ass_attribute27
,p_ass_attribute28 => p_ass_attribute28
,p_ass_attribute29 => p_ass_attribute29
,p_ass_attribute30 => p_ass_attribute30
,p_title => p_title
,p_contract_id => p_contract_id
,p_establishment_id => p_establishment_id
,p_collective_agreement_id => p_collective_agreement_id
,p_cagr_grade_def_id => p_cagr_grade_def_id
,p_cagr_id_flex_num => p_cagr_id_flex_num
,p_object_version_number => l_asg_object_version_number
,p_notice_period => p_notice_period
,p_notice_period_uom => p_notice_period_uom
,p_employee_category => p_employee_category
,p_work_at_home => p_work_at_home
,p_job_post_source_name => p_job_post_source_name
,p_posting_content_id => p_posting_content_id
,p_placement_date_start => p_placement_date_start
,p_vendor_id => p_vendor_id
,p_vendor_employee_number => p_vendor_employee_number
,p_vendor_assignment_number => p_vendor_assignment_number
,p_assignment_category => p_assignment_category
,p_project_title => p_project_title
,p_applicant_rank => p_applicant_rank
,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
,p_supervisor_assignment_id => p_supervisor_assignment_id
,p_vendor_site_id => p_vendor_site_id
,p_po_header_id => p_po_header_id
,p_po_line_id => p_po_line_id
,p_projected_assignment_end => p_projected_assignment_end
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_hourly_salaried_warning => l_hourly_salaried_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_effective_date => l_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_validate => p_validate
);
p_payroll_id_updated := l_payroll_id_updated;
rollback to UPDATE_OFFER_ASSIGNMENT;
p_payroll_id_updated := l_payroll_id_updated;
p_payroll_id_updated := l_payroll_id_updated;
rollback to UPDATE_OFFER_ASSIGNMENT;
end update_offer_assignment;
procedure delete_offer_assignment
( P_VALIDATE IN boolean default false
,P_EFFECTIVE_DATE IN date default null
,P_OFFER_ASSIGNMENT_ID IN number
) Is
--
l_proc varchar2(72) := g_package||'delete_offer_assignment';
select offer_id
,object_version_number
from irc_offers
where offer_assignment_id = p_offer_assignment_id;
savepoint DELETE_OFFER_ASSIGNMENT;
delete_offer
( p_validate => p_validate
,p_object_version_number => l_offer_object_version_number
,p_offer_id => l_offer_id
,p_effective_date => p_effective_date
);
rollback to DELETE_OFFER_ASSIGNMENT;
rollback to DELETE_OFFER_ASSIGNMENT;
end delete_offer_assignment;