[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT, UPDATE and DELETE Assignment Statuses for
Applicant Interviews called from PERREAB.
Change List
-----------
Version Date Author ER/CR No. Description of Change
-------+---------+----------+---------+-----------------------
70.0 09-FEB-93 PShergill Date Created
70.1 11-MAR-93 Nkhan Added 'exit' to the end
70.2 17-AUG-93 Nkhan Added SOURCE_TYPE field
references to per_assignments_f
70.3 16-JUN-94 PShergill Fix 220466 added ATTRIBUTE21..30
70.11 23-NOV-94 RFine Suppressed index on business_group_id
115.3 23-DEC-03 bsubrama Bug 3333891 - Changed the NVL values
for salary rewiew period, performance
review period and pay basis to -99999
rather than ' '. Also made GSCC
compliant.
115.4 13-Jan-06 irgonzal Pef bug 4894555. Added function
chk_duplicate.
================================================================= */
--
--
------------------- insert_interview -----------------------------
/*
NAME
insert_interview
DESCRIPTION
Inserts an assignment of type specified in the paramenter list
starting from applicant interview start date
PARAMETERS
p_assignment_id - assignment_id of applicant
p_idate - New Interview Date
p_assignment_status_type_id - Assignment Status Type Id of Interview
p_last_updated_by - Required for Auditing
p_last_update_login - Required for Auditing
*/
PROCEDURE insert_interview
(
p_assignment_id IN INTEGER,
p_idate IN DATE,
p_assignment_status_type_id IN INTEGER,
p_last_updated_by IN INTEGER,
p_last_update_login IN INTEGER
) IS
-----------------------------------------------------------
-- DECLARE THE LOCAL VARIABLES
-----------------------------------------------------------
p_int_date DATE;
CURSOR select_ass_for_insert IS
SELECT *
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
FOR UPDATE;
hr_utility.set_location('hr_interview.insert_interview',1);
FOR ass_rec_ins IN select_ass_for_insert LOOP
NULL;
hr_utility.set_location('hr_interview.insert_interview',2);
SELECT 'Y'
INTO p_dummy
FROM sys.dual
WHERE EXISTS
( SELECT '1'
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
AND ((effective_end_date <> to_date('31/12/4712','DD/MM/YYYY')
AND effective_end_date >= p_int_date)
OR (effective_start_date = p_int_date)));
hr_utility.set_location('hr_interview.insert_interview',3);
INSERT INTO per_assignments_f
(
assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,assignment_sequence
,assignment_type
,manager_flag
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,frequency
,internal_address_line
,normal_hours
,period_of_service_id
,probation_period
,probation_unit
,recruiter_id
,set_of_books_id
,special_ceiling_step_id
,supervisor_id
,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
,sal_review_period
,sal_review_period_frequency
,perf_review_period
,perf_review_period_frequency
,pay_basis_id
,employment_category
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,source_type
)
SELECT
assignment_id
,effective_start_date
,p_int_date - 1
,business_group_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,assignment_sequence
,assignment_type
,manager_flag
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,frequency
,internal_address_line
,normal_hours
,period_of_service_id
,probation_period
,probation_unit
,recruiter_id
,set_of_books_id
,special_ceiling_step_id
,supervisor_id
,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
,sal_review_period
,sal_review_period_frequency
,perf_review_period
,perf_review_period_frequency
,pay_basis_id
,employment_category
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,source_type
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
AND p_int_date
BETWEEN effective_start_date and effective_end_date;
hr_utility.set_message_token('PROCEDURE','INSERT_INTERVIEW');
hr_utility.set_location('hr_interview.insert_interview',4);
UPDATE per_assignments_f
SET effective_start_date = p_int_date
, assignment_status_type_id = p_assignment_status_type_id
, last_updated_by = p_last_updated_by
, last_update_login = p_last_update_login
, last_update_date = sysdate
WHERE assignment_id = p_assignment_id
AND p_int_date
BETWEEN effective_start_date and effective_end_date;
hr_utility.set_message_token('PROCEDURE','INSERT_INTERVIEW');
END insert_interview;
SELECT *
FROM per_assignments_f c
WHERE c.assignment_id = p_assignment_id
AND p_effective_date BETWEEN
c.effective_start_date AND c.effective_end_date;
SELECT *
FROM per_assignments_f o
WHERE o.assignment_id = cp_asg_id
AND o.effective_end_date = cp_effective_date
AND o.business_group_id + 0 = cp_bg_id + 0;
delete_interview
DESCRIPTION
Deletes assignment for associated applicant interview
PARAMETERS
p_assignment_id - assignment_id of applicant
p_idate - New Interview Date
p_last_updated_by - Required for Auditing
p_last_update_login - Required for Auditing
*/
PROCEDURE delete_interview
(p_assignment_id IN INTEGER,
p_idate IN DATE,
p_last_updated_by IN INTEGER,
p_last_update_login IN INTEGER
) IS
-----------------------------------------------------------
-- DECLARE THE LOCAL VARIABLES
-----------------------------------------------------------
p_int_date DATE;
CURSOR select_ass_for_delete IS
SELECT *
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
FOR UPDATE;
hr_utility.set_location('hr_interview.delete_interview',1);
FOR ass_rec_del IN select_ass_for_delete LOOP
NULL;
hr_utility.set_location('hr_interview.delete_interview',2);
hr_utility.set_location('hr_interview.delete_interview',25);
hr_utility.set_location('hr_interview.delete_interview',3);
SELECT a.assignment_status_type_id
INTO p_old_interview_status
FROM per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND a.effective_start_date = p_int_date;
hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
hr_utility.set_location('hr_interview.delete_interview',4);
UPDATE per_assignments_f a
SET effective_end_date = (SELECT effective_end_date
FROM per_assignments_f b
WHERE b.assignment_id = p_assignment_id
AND b.effective_start_date =
p_int_date)
, last_updated_by = p_last_updated_by
, last_update_login = p_last_update_login
, last_update_date = sysdate
WHERE a.assignment_id = p_assignment_id
AND a.effective_end_date = p_int_date -1;
hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
hr_utility.set_location('hr_interview.delete_interview',5);
DELETE per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND a.effective_start_date = p_int_date;
hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
hr_utility.set_location('hr_interview.delete_interview',6);
SELECT a.effective_end_date
INTO p_dummy_date
FROM per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND p_int_date BETWEEN a.effective_start_date AND a.effective_end_date;
hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
hr_utility.set_location('hr_interview.delete_interview',7);
SELECT d.assignment_status_type_id
INTO p_previous_status
FROM per_assignments_f d
WHERE d.assignment_id = p_assignment_id
AND d.effective_end_date = p_int_date -1;
hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
UPDATE per_assignments_f a
SET assignment_status_type_id = p_previous_status
, last_updated_by = p_last_updated_by
, last_update_login = p_last_update_login
, last_update_date = sysdate
WHERE a.assignment_id = p_assignment_id
AND a.effective_start_date = p_int_date;
hr_utility.set_location('hr_interview.delete_interview',8);
SELECT MIN(e.date_start)
INTO p_nxt_interview_date
FROM per_events e
WHERE e.assignment_id = p_assignment_id
AND e.date_start > p_int_date;
hr_utility.set_location('hr_interview.delete_interview',9);
SELECT d.assignment_status_type_id
INTO p_previous_status
FROM per_assignments_f d
WHERE d.assignment_id = p_assignment_id
AND p_int_date
BETWEEN d.effective_start_date AND d.effective_end_date;
hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
hr_utility.set_location('hr_interview.delete_interview',10);
SELECT d.assignment_status_type_id
INTO p_previous_status
FROM per_assignments_f d
WHERE d.assignment_id = p_assignment_id
AND d.effective_end_date = p_int_date -1;
hr_utility.set_message_token('PROCEDURE','DELETE_INTERVIEW');
hr_utility.set_location('hr_interview.delete_interview',11);
SELECT MIN(c.effective_start_date)
INTO p_new_status_type_date
FROM per_assignments_f c
WHERE c.assignment_id = p_assignment_id
AND c.effective_start_date > p_int_date
AND c.assignment_status_type_id <> p_old_interview_status;
hr_utility.set_location('hr_interview.delete_interview',12);
UPDATE per_assignments_f a
SET assignment_status_type_id = p_previous_status
, last_updated_by = p_last_updated_by
, last_update_login = p_last_update_login
, last_update_date = sysdate
WHERE a.assignment_id = p_assignment_id
AND a.effective_start_date >= p_int_date
AND ((a.effective_end_date < least(nvl(p_nxt_interview_date,
to_date('31/12/4712','DD/MM/YYYY')),
nvl(p_new_status_type_date,
to_date('31/12/4712','DD/MM/YYYY')))));
hr_utility.set_location('hr_interview.delete_interview',13);
hr_utility.set_location('hr_interview.delete_interview',14);
UPDATE per_assignments_f a
SET assignment_status_type_id = p_previous_status
, last_updated_by = p_last_updated_by
, last_update_login = p_last_update_login
, last_update_date = sysdate
WHERE a.assignment_id = p_assignment_id
AND a.effective_start_date >= p_int_date
AND a.effective_end_date = to_date('31/12/4712','DD/MM/YYYY');
END delete_interview;
update_interview
DESCRIPTION
Update assignment for associated applicant interview
PARAMETERS
p_assignment_id - assignment_id of applicant
p_idate - New Interview Date
p_odate - Old Interview Date
p_last_updated_by - Required for Auditing
p_last_update_login - Required for Auditing
*/
PROCEDURE update_interview
(p_assignment_id IN INTEGER,
p_idate IN DATE,
p_odate IN DATE,
p_last_updated_by IN INTEGER,
p_last_update_login IN INTEGER
) IS
-----------------------------------------------------------
-- DECLARE THE LOCAL VARIABLES
-----------------------------------------------------------
p_int_date DATE;
p_no_update VARCHAR2(1);
CURSOR select_ass_for_update IS
SELECT *
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
FOR UPDATE;
p_no_update := 'N';
hr_utility.set_location('hr_interview.update_interview',1);
FOR ass_rec_upd IN select_ass_for_update LOOP
NULL;
hr_utility.set_location('hr_interview.update_interview',2);
SELECT 'Y'
INTO p_no_update
FROM sys.dual
WHERE EXISTS
(SELECT '1'
FROM per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND (((a.effective_start_date
BETWEEN p_old_int_date + 1 AND p_int_date -1)
AND p_old_int_date < p_int_date)
OR
((a.effective_start_date
BETWEEN p_int_date AND p_old_int_date -1)
AND p_old_int_date > p_int_date)));
IF p_no_update = 'Y' THEN /* CASE D or E */
hr_utility.set_message(801,'HR_6629_APPL_STATUS_MOVE');
hr_utility.set_location('hr_interview.update_interview',3);
hr_utility.set_location('hr_interview.update_interview',4);
SELECT 'Y'
INTO p_matches
FROM per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND a.effective_start_date = p_int_date;
hr_utility.set_location('hr_interview.update_interview',5);
hr_utility.set_location('hr_interview.update_interview',6);
UPDATE per_assignments_f a
SET a.effective_start_date = p_int_date
, a.last_updated_by = p_last_updated_by
, a.last_update_login = p_last_update_login
, a.last_update_date = sysdate
WHERE a.assignment_id = p_assignment_id
AND a.effective_start_date = p_old_int_date;
hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
hr_utility.set_location('hr_interview.update_interview',7);
UPDATE per_assignments_f a
SET a.effective_end_date = p_int_date -1
, a.last_updated_by = p_last_updated_by
, a.last_update_login = p_last_update_login
, a.last_update_date = sysdate
WHERE a.assignment_id = p_assignment_id
AND a.effective_end_date = p_old_int_date -1;
hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
hr_utility.set_location('hr_interview.update_interview',8);
DELETE per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND a.effective_start_date = p_old_int_date;
hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
hr_utility.set_location('hr_interview.update_interview',9);
UPDATE per_assignments_f a
SET a.effective_end_date = p_int_date -1
, a.last_updated_by = p_last_updated_by
, a.last_update_login = p_last_update_login
, a.last_update_date = sysdate
WHERE a.assignment_id = p_assignment_id
AND a.effective_end_date = p_old_int_date -1;
hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
p_no_update := 'N';
hr_utility.set_location('hr_interview.update_interview',10);
SELECT 'Y'
INTO p_no_update
FROM sys.dual
WHERE EXISTS
(SELECT '1'
FROM per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND a.effective_start_date >= p_int_date);
IF p_no_update = 'Y' THEN
hr_utility.set_message(801,'HR_6629_APPL_STATUS_MOVE');
hr_utility.set_location('hr_interview.update_interview',11);
SELECT a.assignment_status_type_id
INTO p_ass_status_type_id
FROM per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND a.effective_end_date = p_old_int_date -1;
hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
hr_utility.set_location('hr_interview.update_interview',12);
INSERT INTO per_assignments_f
(
assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,assignment_sequence
,assignment_type
,manager_flag
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,frequency
,internal_address_line
,normal_hours
,period_of_service_id
,probation_period
,probation_unit
,recruiter_id
,set_of_books_id
,special_ceiling_step_id
,supervisor_id
,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
,sal_review_period
,sal_review_period_frequency
,perf_review_period
,perf_review_period_frequency
,pay_basis_id
,employment_category
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,source_type
)
SELECT
assignment_id
,p_old_int_date
,p_int_date - 1
,business_group_id
,grade_id
,position_id
,job_id
,p_ass_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,assignment_sequence
,assignment_type
,manager_flag
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,frequency
,internal_address_line
,normal_hours
,period_of_service_id
,probation_period
,probation_unit
,recruiter_id
,set_of_books_id
,special_ceiling_step_id
,supervisor_id
,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
,sal_review_period
,sal_review_period_frequency
,perf_review_period
,perf_review_period_frequency
,pay_basis_id
,employment_category
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,source_type
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
AND effective_start_date = p_old_int_date;
hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
hr_utility.set_location('hr_interview.update_interview',13);
UPDATE per_assignments_f a
SET a.effective_start_date = p_int_date
, a.last_updated_by = p_last_updated_by
, a.last_update_login = p_last_update_login
, a.last_update_date = sysdate
WHERE assignment_id = p_assignment_id
AND effective_end_date = TO_DATE('31/12/4712','DD/MM/YYYY');
hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
hr_utility.set_location('hr_interview.update_interview',14);
SELECT a.assignment_status_type_id
INTO p_ass_status_type_id
FROM per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND a.effective_start_date = p_old_int_date;
hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
hr_utility.set_location('hr_interview.update_interview',15);
SELECT a.effective_start_date
INTO p_old_prev_start_date
FROM per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND a.effective_end_date = p_old_int_date -1;
hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
hr_utility.set_location('hr_interview.update_interview',16);
INSERT INTO per_assignments_f
(
assignment_id
,effective_start_date
,effective_end_date
,business_group_id
,grade_id
,position_id
,job_id
,assignment_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,assignment_sequence
,assignment_type
,manager_flag
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,frequency
,internal_address_line
,normal_hours
,period_of_service_id
,probation_period
,probation_unit
,recruiter_id
,set_of_books_id
,special_ceiling_step_id
,supervisor_id
,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
,sal_review_period
,sal_review_period_frequency
,perf_review_period
,perf_review_period_frequency
,pay_basis_id
,employment_category
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,source_type
)
SELECT
assignment_id
,p_int_date
,p_old_int_date - 1
,business_group_id
,grade_id
,position_id
,job_id
,p_ass_status_type_id
,payroll_id
,location_id
,person_referred_by_id
,person_id
,recruitment_activity_id
,source_organization_id
,organization_id
,people_group_id
,soft_coding_keyflex_id
,vacancy_id
,assignment_sequence
,assignment_type
,manager_flag
,primary_flag
,application_id
,assignment_number
,change_reason
,comment_id
,date_probation_end
,default_code_comb_id
,frequency
,internal_address_line
,normal_hours
,period_of_service_id
,probation_period
,probation_unit
,recruiter_id
,set_of_books_id
,special_ceiling_step_id
,supervisor_id
,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
,sal_review_period
,sal_review_period_frequency
,perf_review_period
,perf_review_period_frequency
,pay_basis_id
,employment_category
,bargaining_unit_code
,labour_union_member_flag
,hourly_salaried_code
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,source_type
FROM per_assignments_f
WHERE assignment_id = p_assignment_id
AND effective_end_date = p_old_int_date -1;
hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
hr_utility.set_location('hr_interview.update_interview',17);
UPDATE per_assignments_f a
SET a.effective_end_date = p_int_date -1
, a.last_updated_by = p_last_updated_by
, a.last_update_login = p_last_update_login
, a.last_update_date = sysdate
WHERE assignment_id = p_assignment_id
AND effective_start_date = p_old_prev_start_date
AND effective_end_date = p_old_int_date -1;
hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
END update_interview;