[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.assignment_status_type_id
FROM per_assignments_f a
WHERE a.person_id = p_person_id
AND A.business_group_id + 0 = p_business_group_id
AND A.APPLICATION_ID = p_application_id
AND p_date_end between A.EFFECTIVE_START_DATE
and A.EFFECTIVE_END_DATE; --
PROCEDURE term_update_ass_bud_val(p_application_id NUMBER
,p_person_id NUMBER
,p_business_group_id NUMBER
,p_date_end DATE
,p_last_updated_by NUMBER
,p_last_update_login NUMBER) IS
--
p_del_flag VARCHAR2(1) := 'N';
select abv1.*
from PER_ALL_ASSIGNMENTS_F paa,
per_assignment_budget_values_f abv1
where paa.APPLICATION_ID = p_application_id
and paa.PERSON_ID = p_person_id
and paa.business_group_id = p_business_group_id
and paa.ASSIGNMENT_TYPE = 'A'
and paa.assignment_id = abv1.assignment_id
and p_date_end
between abv1.effective_start_date
and abv1.effective_end_date;
hr_utility.set_location('PER_APPLICATIONS_PKG.term_update_ass_bud_val',5);
select 'Y'
into p_del_flag
from sys.dual
where exists (
Select null
from PER_ALL_ASSIGNMENTS_F paa, per_assignment_budget_values_f abv
where paa.APPLICATION_ID = p_application_id
and paa.PERSON_ID = p_person_id
and paa.business_group_id + 0 = p_business_group_id
and paa.ASSIGNMENT_TYPE = 'A'
and paa.assignment_id = abv.assignment_id
and abv.effective_start_date > p_date_end);
hr_utility.set_location('PER_APPLICATIONS_PKG.term_update_ass_bud_val',10);
delete from per_assignment_budget_values_f abv
where exists (
Select null
from PER_ALL_ASSIGNMENTS_F paa, per_assignment_budget_values_f abv1
where paa.APPLICATION_ID = p_application_id
and paa.PERSON_ID = p_person_id
and paa.business_group_id + 0 = p_business_group_id
and paa.ASSIGNMENT_TYPE = 'A'
and paa.assignment_id = abv1.assignment_id
and abv1.assignment_id = abv.assignment_id
and abv1.effective_start_date > p_date_end
and abv1.effective_start_date = abv.effective_start_date);
hr_utility.set_location('PER_APPLICATIONS_PKG.term_update_ass_bud_val',15);
select 'Y'
into p_del_flag
from sys.dual
where exists (
Select null
from PER_ALL_ASSIGNMENTS_F paa, per_assignment_budget_values_f abv
where paa.APPLICATION_ID = p_application_id
and paa.PERSON_ID = p_person_id
and paa.business_group_id + 0 = p_business_group_id
and paa.ASSIGNMENT_TYPE = 'A'
and paa.assignment_id = abv.assignment_id
and p_date_end between abv.effective_start_date and abv.effective_end_date);
hr_utility.set_location('PER_APPLICATIONS_PKG.term_update_ass_bud_val',20);
update per_assignment_budget_values_f abv
set abv.effective_end_date = p_date_end,
abv.last_updated_by = P_LAST_UPDATED_BY,
abv.last_update_login = P_LAST_UPDATE_LOGIN,
abv.last_update_date = sysdate
where abv.assignment_budget_value_id=l_c1.assignment_budget_value_id
and abv.effective_start_date = l_c1.effective_start_date
and abv.effective_end_date = l_c1.effective_end_date;
END term_update_ass_bud_val;
PROCEDURE cancel_update_ass_bud_val(p_application_id NUMBER
,p_person_id NUMBER
,p_business_group_id NUMBER
,p_date_end DATE
,p_end_of_time DATE
,p_last_updated_by NUMBER
,p_last_update_login NUMBER) IS
--
p_del_flag VARCHAR2(1) := 'N';
select abv1.*
from PER_ALL_ASSIGNMENTS_F paa,
per_assignment_budget_values_f abv1
where paa.APPLICATION_ID = p_application_id
and paa.PERSON_ID = p_person_id
and paa.business_group_id = p_business_group_id
and paa.ASSIGNMENT_TYPE = 'A'
and abv1.assignment_id = paa.assignment_id
and abv1.effective_end_date = p_date_end;
hr_utility.set_location('PER_APPLICATIONS_PKG.cancel_update_ass_bud_val',5);
select 'Y'
into p_del_flag
from sys.dual
where exists (
Select null
from PER_ALL_ASSIGNMENTS_F paa
where paa.APPLICATION_ID = p_application_id
and paa.PERSON_ID = p_person_id
and paa.business_group_id + 0 = p_business_group_id
and paa.ASSIGNMENT_TYPE = 'A'
and exists (Select abv.assignment_id
from per_assignment_budget_values_f abv
where abv.assignment_id = paa.assignment_id
and abv.effective_end_date = p_date_end));
hr_utility.set_location('PER_APPLICATIONS_PKG.cancel_update_ass_bud_val',10);
update per_assignment_budget_values_f abv
set abv.effective_end_date = p_end_of_time,
abv.last_updated_by = P_LAST_UPDATED_BY,
abv.last_update_login = P_LAST_UPDATE_LOGIN,
abv.last_update_date = sysdate
where abv.assignment_budget_value_id = l_c1.assignment_budget_value_id
and abv.effective_start_date = l_c1.effective_start_date
and abv.effective_end_date = l_c1.effective_end_date;
END cancel_update_ass_bud_val;
SELECT 1
FROM PER_LETTER_GEN_STATUSES PLG
WHERE PLG.business_group_id + 0 = p_business_group_id
AND PLG.ASSIGNMENT_STATUS_TYPE_ID = P_dummy_asg_stat_id
AND PLG.ENABLED_FLAG = 'Y';
SELECT distinct(1)
FROM per_letter_request_lines l
WHERE L.person_id = p_person_id
AND l.business_group_id + 0 = p_business_group_id
AND l.assignment_status_type_id = P_dummy_asg_stat_id
AND EXISTS
(SELECT NULL
FROM per_assignments_f A
WHERE a.business_group_id + 0 = p_business_group_id
AND a.person_id = p_person_id
AND a.assignment_status_type_id = P_dummy_asg_stat_id
AND a.assignment_type = 'A'
AND a.application_id = p_application_id
AND a.assignment_id = l.assignment_id);
SELECT 1
FROM per_letter_requests r
WHERE NOT EXISTS
(SELECT NULL
FROM per_letter_request_lines L
WHERE r.letter_request_id = l.letter_request_id);
DELETE FROM PER_LETTER_REQUEST_LINES l
WHERE l.person_id = p_person_id
AND l.assignment_status_type_id = P_dummy_asg_stat_id
AND l.business_group_id + 0 = p_business_group_id;
DELETE FROM per_letter_requests R
WHERE r.business_group_id = p_business_group_id
AND r.request_status = 'PENDING'
AND r.auto_or_manual = 'AUTO'
AND NOT EXISTS
(SELECT null
FROM Per_letter_request_lines l
WHERE l.letter_request_id = r.letter_request_id
AND l.business_group_id + 0 = p_business_group_id);
PROCEDURE insert_letter_term(P_business_group_id NUMBER,
p_application_id NUMBER,
p_person_id NUMBER,
p_session_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_assignment_status_type_id NUMBER ) IS
CURSOR ck_gen_stats IS
SELECT 1
FROM per_letter_gen_statuses s
WHERE S.business_group_id + 0 = P_business_group_id
AND s.assignment_status_type_id = p_assignment_status_type_id
AND s.enabled_flag = 'Y';
SELECT R.LETTER_REQUEST_ID,
r.letter_type_id
FROM PER_LETTER_REQUESTS R,
PER_LETTER_GEN_STATUSES s
WHERE r.business_group_id + 0 = p_business_group_id
AND s.business_group_id + 0 = p_business_group_id
AND r.LETTER_TYPE_ID = S.LETTER_TYPE_ID
AND s.ASSIGNMENT_STATUS_TYPE_ID = p_assignment_status_type_id
AND s.ENABLED_FLAG = 'Y'
AND R.REQUEST_STATUS = 'PENDING'
AND R.AUTO_OR_MANUAL = 'AUTO';
SELECT 1
FROM per_letter_gen_statuses s
WHERE S.business_group_id + 0 = P_business_group_id
AND s.assignment_status_type_id = p_assignment_status_type_id
AND s.enabled_flag = 'Y'
AND NOT EXISTS
(SELECT NULL
FROM per_letter_requests r
WHERE r.letter_type_id = S.letter_type_id
AND R.business_group_id + 0 = P_business_group_id
AND r.request_status = 'PENDING'
AND r.auto_or_manual = 'AUTO');
SELECT distinct(s.letter_type_id)
FROM per_letter_gen_statuses s
WHERE s.business_group_id + 0 = p_business_group_id
AND s.assignment_status_type_id = p_assignment_status_type_id
AND s.enabled_flag = 'Y'
AND s.letter_type_id NOT IN (SELECT distinct(r.letter_type_id)
FROM per_letter_requests r
WHERE r.business_group_id + 0
= p_business_group_id
and r.REQUEST_STATUS
= 'PENDING'
and r.AUTO_OR_MANUAL
= 'AUTO');
SELECT ASSIGNMENT_ID
FROM PER_ASSIGNMENTS_f
WHERE business_group_id + 0 = p_business_group_id
AND PERSON_ID = p_person_id
AND APPLICATION_ID = p_application_id
AND ASSIGNMENT_TYPE = 'A'
and effective_start_date <= p_session_date
and effective_end_date > p_session_date;
SELECT 1
FROM PER_LETTER_REQUESTS PLR,
PER_LETTER_GEN_STATUSES PLGS
WHERE PLGS.business_group_id + 0 = p_business_group_id
AND PLR.business_group_id +0 = p_business_group_id
AND PLGS.assignment_status_type_id = p_assignment_status_type_id
AND PLR.letter_type_id = PLGS.letter_type_id
AND PLR.auto_or_manual = 'MANUAL';
INSERT INTO PER_LETTER_REQUEST_LINES
(
LETTER_REQUEST_LINE_ID
, BUSINESS_GROUP_ID
, LETTER_REQUEST_ID
, PERSON_ID
, ASSIGNMENT_ID
, ASSIGNMENT_STATUS_TYPE_ID
, DATE_FROM
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE)
select
PER_LETTER_REQUEST_LINES_S.nextval
, p_business_group_id
, v_letter_request_id
, p_person_id
, a.ASSIGNMENT_ID
, p_assignment_status_type_id
, p_session_date
, trunc(SYSDATE)
, p_last_updated_by
, p_last_update_login
, p_last_updated_by
, trunc(SYSDATE)
FROM PER_LETTER_REQUESTS r
, PER_LETTER_GEN_STATUSES s
, PER_ASSIGNMENTS a
WHERE R.LETTER_TYPE_ID = S.LETTER_TYPE_ID
AND R.LETTER_TYPE_ID = v_letter_type
AND R.letter_request_id = v_letter_request_id -- Added for bug3680947.
AND R.REQUEST_STATUS = 'PENDING'
AND S.ASSIGNMENT_STATUS_TYPE_ID = p_assignment_status_type_id
AND S.business_group_id + 0 = R.business_group_id + 0
AND S.BUSINESS_GROUP_ID + 0 = p_business_group_id
AND s.ENABLED_FLAG = 'Y'
AND a.BUSINESS_GROUP_ID + 0 = p_business_group_id
AND a.PERSON_ID = p_person_id
AND a.APPLICATION_ID = p_application_id
and not exists
(select null
from PER_LETTER_REQUEST_LINES l
where l.PERSON_ID = p_person_id
AND A.PERSON_ID = p_person_id
and l.ASSIGNMENT_ID = a.ASSIGNMENT_ID
and l.ASSIGNMENT_STATUS_TYPE_ID =
p_assignment_status_type_id
and l.LETTER_REQUEST_ID = v_letter_request_id
and l.business_group_id + 0 = p_business_group_id
and l.business_group_id + 0 = A.business_group_id + 0
and l.business_group_id + 0 = p_business_group_id);
insert into PER_LETTER_REQUESTS(
LETTER_REQUEST_ID
, BUSINESS_GROUP_ID
, LETTER_TYPE_ID
, DATE_FROM
, REQUEST_STATUS
, AUTO_OR_MANUAL
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE)
select PER_LETTER_REQUESTS_S.nextval
, P_Business_group_id
, v_letter_type
, P_session_date
, 'PENDING'
, 'AUTO'
, trunc(SYSDATE)
, p_last_updated_by
, p_last_update_login
, p_last_updated_by
, trunc(SYSDATE)
from sys.dual;
INSERT INTO PER_LETTER_REQUEST_LINES
(
LETTER_REQUEST_LINE_ID
, BUSINESS_GROUP_ID
, LETTER_REQUEST_ID
, PERSON_ID
, ASSIGNMENT_ID
, ASSIGNMENT_STATUS_TYPE_ID
, DATE_FROM
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE)
select
PER_LETTER_REQUEST_LINES_S.nextval
, P_Business_group_id
, r.LETTER_REQUEST_ID
, P_person_id
, v_assignment_id
, p_assignment_status_type_id
, p_session_date
, trunc(SYSDATE)
, p_last_updated_by
, p_last_update_login
, p_last_updated_by
, trunc(SYSDATE)
FROM PER_LETTER_REQUESTS R
, PER_LETTER_GEN_STATUSES s
WHERE R.LETTER_TYPE_ID = S.LETTER_TYPE_ID
AND p_assignment_status_type_id = S.ASSIGNMENT_STATUS_TYPE_ID
AND S.business_group_id + 0 = R.business_group_id + 0
AND S.BUSINESS_GROUP_ID + 0 = P_Business_group_id
AND R.REQUEST_STATUS = 'PENDING'
AND R.AUTO_OR_MANUAL = 'AUTO'
AND r.DATE_FROM = p_session_date
AND s.ENABLED_FLAG = 'Y'
AND NOT EXISTS
(SELECT NULL
FROM per_letter_request_lines L
WHERE L.person_id = P_person_id
AND L.assignment_id = v_assignment_id
AND L.assignment_status_type_id =
p_assignment_status_type_id
AND L.letter_request_id = r.letter_request_id
AND L.business_group_id + 0 = r.business_group_id + 0
AND L.business_group_id + 0 = P_Business_group_id);
END insert_letter_term;
SELECT 1
FROM per_letter_gen_statuses s
, per_assignment_status_types a
WHERE s.business_group_id + 0 = p_business_group_id
AND a.assignment_status_type_id = s.assignment_status_type_id
AND a.per_system_status = 'TERM_APL'
AND s.enabled_flag = 'Y';
SELECT distinct(1)
FROM per_letter_request_lines L
, per_assignments_f a
WHERE l.person_id = P_person_id
AND l.business_group_id + 0 = p_business_group_id
AND l.assignment_id = a.assignment_id
AND a.person_id = P_person_id
AND a.business_group_id + 0 = p_business_group_id
AND a.assignment_type = 'A'
AND a.application_id = p_application_id;
SELECT r.letter_request_id
FROM PER_LETTER_REQUESTS R,
PER_LETTER_GEN_STATUSES s,
PER_ASSIGNMENT_STATUS_TYPES T
WHERE r.business_group_id + 0 = p_business_group_id
AND s.business_group_id + 0 = p_business_group_id
AND r.LETTER_TYPE_ID = S.LETTER_TYPE_ID
AND s.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
AND T.PER_SYSTEM_STATUS = 'TERM_APL'
AND s.ENABLED_FLAG = 'Y'
AND R.REQUEST_STATUS = 'PENDING'
AND r.auto_or_manual = 'AUTO';
SELECT R.LETTER_REQUEST_ID
FROM PER_LETTER_REQUESTS R,
PER_LETTER_GEN_STATUSES s,
PER_ASSIGNMENT_STATUS_TYPES T
WHERE r.business_group_id + 0 = p_business_group_id
AND s.business_group_id + 0 = p_business_group_id
AND r.LETTER_TYPE_ID = S.LETTER_TYPE_ID
AND s.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
AND T.PER_SYSTEM_STATUS = 'TERM_APL'
and s.ENABLED_FLAG = 'Y'
and R.REQUEST_STATUS = 'PENDING'
and R.AUTO_OR_MANUAL = 'AUTO'
and not exists
(select null
from PER_LETTER_REQUEST_LINES l
where l.LETTER_REQUEST_ID = R.LETTER_REQUEST_ID
and l.business_group_id + 0 = r.business_group_id + 0
and l.business_group_id + 0 = p_business_group_id);
DELETE FROM per_letter_request_lines lrL
WHERE lrl.business_group_id + 0 = p_business_group_id
AND lrl.letter_request_id =
csr_let_req_id_REC.letter_request_id
AND lrl.person_id = P_person_id
AND lrl.person_id = P_person_id
AND EXISTS
(SELECT NULL
FROM per_assignments_f a
WHERE a.assignment_id = lrl.assignment_id
AND a.person_id = P_person_id
AND a.application_id = p_application_id
AND a.business_group_id + 0 = P_business_group_id);
DELETE FROM per_letter_requests R
WHERE r.letter_request_id =
csr_odd_reqs_REC.letter_request_id
AND r.business_group_id + 0 = p_business_group_id;
SELECT 1
FROM per_all_people_f pap
WHERE pap.person_id = p_person_id
AND pap.applicant_number IS NOT NULL
and EXISTS(SELECT 1 -- If hired app table has appl with end date and SUCCESSFUL_FLAG = 'Y'
from per_applications app
where app.person_id = p_person_id
AND app.business_group_id + 0 = p_business_group_id
and app.DATE_END = p_date_end
and nvl(app.SUCCESSFUL_FLAG,'N') = 'Y'
)
AND pap.effective_start_date = p_date_end + 1 -- If hired pap table has emp record with date_end+1
AND EXISTS
(SELECT 1
FROM per_person_types PP
WHERE pp.person_type_id = pap.person_type_id
AND PP.business_group_id + 0 = p_business_group_id
AND pp.active_flag ='Y'
AND pp.system_person_type IN ('EMP'));
PROCEDURE cancel_update_assigns(p_person_id NUMBER,
p_business_group_id NUMBER,
P_date_end DATE,
P_application_id NUMBER,
p_legislation_code VARCHAR2,
P_end_of_time DATE,
P_last_updated_by NUMBER,
p_last_update_login NUMBER) IS
CURSOR c_chk_prv_status IS
select 1
from per_assignment_status_types t
, per_assignments_f asg
where asg.person_id = p_person_id
and nvl(t.business_group_id,
p_business_group_id) = p_business_group_id
and t.PER_SYSTEM_STATUS = 'TERM_APL'
and asg.effective_start_date <= P_date_end
and asg.effective_end_date >= P_date_end
and asg.business_group_id + 0 = p_business_group_id
and asg.application_id = P_application_id
and t.assignment_status_type_id = asg.assignment_status_type_id;
SELECT a.assignment_status_type_id
FROM per_assignment_status_types a
, per_ass_status_type_amends b
WHERE a.per_system_status = 'ACTIVE_APL'
AND b.assignment_status_type_id(+) = a.assignment_status_type_id
AND b.business_group_id(+) + 0 = p_business_group_id
AND nvl(a.business_group_id, p_business_group_id) =
p_business_group_id
AND nvl(a.legislation_codE,
p_legislation_code) = p_legislation_code
AND NVL(B.ACTIVE_FLAG,A.ACTIVE_FLAG) = 'Y'
and nvl(B.DEFAULT_FLAG, A.DEFAULT_FLAG) = 'Y';
UPDATE PER_ALL_ASSIGNMENTS_F A
SET A.LAST_UPDATE_DATE = trunc(sysdate)
, A.LAST_UPDATED_BY = P_last_updated_by
, A.LAST_UPDATE_LOGIN = p_last_update_login
, A.EFFECTIVE_END_DATE = P_end_of_time
, A.ASSIGNMENT_STATUS_TYPE_ID = v_act_ass_stat_id
WHERE A.APPLICATION_ID = P_application_id
AND A.PERSON_ID = p_person_id
AND A.business_group_id + 0 = p_business_group_id
AND A.ASSIGNMENT_TYPE = 'A'
AND A.EFFECTIVE_END_DATE = P_date_end;
cancel_update_ass_bud_val(p_application_id
,p_person_id
,p_business_group_id
,p_date_end
,p_end_of_time
,p_last_updated_by
,p_last_update_login);
UPDATE PER_ALL_ASSIGNMENTS_F A
SET A.LAST_UPDATE_DATE = trunc(sysdate)
, A.LAST_UPDATED_BY = P_last_updated_by
, A.LAST_UPDATE_LOGIN = p_last_update_login
, A.EFFECTIVE_END_DATE = P_end_of_time
WHERE A.APPLICATION_ID = P_application_id
AND A.PERSON_ID = p_person_id
AND A.business_group_id + 0 = p_business_group_id
AND A.ASSIGNMENT_TYPE = 'A'
AND A.EFFECTIVE_END_DATE = P_date_end;
cancel_update_ass_bud_val(p_application_id
,p_person_id
,p_business_group_id
,p_date_end
,p_end_of_time
,p_last_updated_by
,p_last_update_login);
END cancel_update_assigns;
PROCEDURE term_update_assignments(p_person_id NUMBER,
p_business_group_id NUMBER,
P_date_end DATE,
P_application_id NUMBER,
p_last_updated_by NUMBER,
p_last_update_login NUMBER) IS
-- CURSOR c_chk_assigns IS
-- SELECT 1
-- FROM per_all_assignments_f a
-- WHERE a.application_id = P_application_id
-- AND a.person_id = p_person_id
-- AND a.business_group_id + 0 = p_business_group_id
-- AND a.assignment_type = 'A'
-- AND a.effective_start_date > P_date_end;
select assignment_id, object_version_number, effective_start_date
from per_all_assignments_f a
WHERE a.application_id = P_application_id
AND a.person_id = p_person_id
AND a.business_group_id = p_business_group_id
AND a.assignment_type = 'A'
AND a.effective_start_date > P_date_end
AND not exists
(select 'Y'
from per_all_assignments_f paf2
where paf2.assignment_id = a.assignment_id
and paf2.effective_start_date < a.EFFECTIVE_START_DATE);
DELETE per_all_assignments_f a
WHERE a.application_id = P_application_id
AND a.person_id = p_person_id
AND a.business_group_id = p_business_group_id
AND a.assignment_type = 'A'
AND a.effective_start_date > P_date_end;
UPDATE per_all_assignments_f paa
SET paa.last_update_date = trunc(sysdate),
paa.last_updated_by = p_last_updated_by,
paa.last_update_login = p_last_update_login,
paa.EFFECTIVE_END_DATE = P_date_end
where paa.APPLICATION_ID = P_application_id
and paa.PERSON_ID = p_person_id
and paa.business_group_id + 0 = p_business_group_id
and paa.ASSIGNMENT_TYPE = 'A'
and paa.EFFECTIVE_END_DATE =
(select max(pa2.EFFECTIVE_END_DATE)
from PER_ALL_ASSIGNMENTS_F pa2
where pa2.PERSON_ID = p_person_id
and pa2.assignment_id = paa.assignment_id -- 3957964 >>
and pa2.effective_end_date > p_date_end -- <<
and pa2.APPLICATION_ID = P_application_id);
term_update_ass_bud_val(p_application_id
,p_person_id
,p_business_group_id
,p_date_end
,p_last_updated_by
,p_last_update_login);
END term_update_assignments;
SELECT 1
FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID = P_PERSON_ID
AND PAPF.EFFECTIVE_START_DATE > P_DATE_END + 1 ;
SELECT 1
FROM per_all_people_f papf
WHERE papf.person_id = p_person_id
AND papf.effective_start_date > P_date_end
AND papf.business_group_id + 0 = p_business_group_id;
SELECT 1
FROM PER_ALL_ASSIGNMENTS_F PAAF
WHERE PAAF.PERSON_ID = p_person_id
AND PAAF.business_group_id + 0 = p_business_group_id
AND PAAF.EFFECTIVE_START_DATE > P_date_end;
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_person_id NUMBER) IS
CURSOR c_sec_stat_cancel IS
select sa.assignment_id
from per_secondary_ass_statuses sa
where sa.business_group_id + 0 = p_business_group_id
and sa.end_date = p_end_date
and exists
( SELECT s.assignment_id
FROM PER_SECONDARY_ASS_STATUSES s
where s.business_group_id + 0 = p_business_group_id
and s.end_date = p_end_date
and sa.assignment_id = s.assignment_id
and exists
(select null
from per_assignments_f paf
where paf.person_id = p_person_id
and paf.application_id = p_application_id
and paf.assignment_type = 'A'
and paf.effective_end_date = p_end_date
and paf.assignment_id = s.assignment_id));
UPDATE per_secondary_ass_statuses s
SET s.END_DATE = NULL
, s.LAST_UPDATE_DATE = trunc(SYSDATE)
, s.LAST_UPDATED_BY = p_last_updated_by
, s.LAST_UPDATE_LOGIN = p_last_update_login
WHERE s.assignment_id = v_assignment_id
AND s.business_group_id + 0 = p_business_group_id
AND s.END_DATE = p_end_date;
,p_last_updated_by number
,p_last_update_login number
,p_person_id number) is
-- WWbug 633263
-- Modified cursor for performance improvements by removing the full table
-- scan on per_secondary_ass_statuses.
-- This was achieved by removing the sub-query
cursor chk_sec_stat is
select 1
from per_secondary_ass_statuses s
,per_assignments_f a
where s.business_group_id + 0 = p_business_group_id
and s.start_date is not null
and a.business_group_id + 0 = p_business_group_id
and a.person_id = p_person_id
and s.assignment_id = a.assignment_id
and a.application_id = p_application_id
and a.assignment_type = 'A'
and p_end_date
between a.effective_start_date
and a.effective_end_date;
select sa.assignment_id
from per_secondary_ass_statuses sa
,per_assignments_f paf
where sa.business_group_id + 0 = p_business_group_id
and sa.start_date <= p_end_date
and (sa.end_date is null
or sa.end_date > p_end_date)
and sa.assignment_id = paf.assignment_id
and paf.person_id = p_person_id
and paf.application_id = p_application_id
and paf.assignment_type = 'A'
and p_end_date
between paf.effective_start_date
and paf.effective_end_date;
delete from per_secondary_ass_statuses s
where s.business_group_id + 0 = p_business_group_id
and trunc(s.start_date) > p_end_date
and s.assignment_id in
(select a.assignment_id
from per_assignments_f a
where a.business_group_id + 0 = p_business_group_id
and a.person_id = p_person_id
and a.application_id = p_application_id
and a.assignment_type = 'A'
and p_end_date
between a.effective_start_date
and a.effective_end_date);
update per_secondary_ass_statuses s
set s.end_date = p_end_date
, s.last_update_date = trunc(sysdate)
, s.last_updated_by = p_last_updated_by
, s.last_update_login = p_last_update_login
where s.assignment_id = csr_rec.assignment_id
and s.business_group_id + 0 = p_business_group_id
and s.start_date <= p_end_date
and (s.end_date is null
or s.end_date > p_end_date);
SELECT E.EVENT_ID
FROM PER_EVENTS E
, PER_ASSIGNMENTS_F A
WHERE A.PERSON_ID = P_person_id
AND E.business_group_id + 0 = p_business_group_id
AND A.business_group_id + 0 = p_business_group_id
AND A.APPLICATION_ID = P_application_id
AND E.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND E.DATE_START >= P_date_end
AND E.EVENT_OR_INTERVIEW = 'I';
SELECT distinct(1)
FROM PER_BOOKINGS B
, PER_EVENTS E
, PER_ASSIGNMENTS_F A
WHERE A.PERSON_ID = P_person_id
AND A.APPLICATION_ID = P_application_id
AND B.EVENT_ID = E.EVENT_ID
AND E.DATE_START >= P_date_end
AND E.EVENT_OR_INTERVIEW = 'I'
AND E.ASSIGNMENT_ID = A.ASSIGNMENT_ID;
select B.PERSON_ID,B.BOOKING_ID
from PER_BOOKINGS B
, PER_EVENTS E
, PER_ASSIGNMENTS A
where B.business_group_id + 0 = p_business_group_id
and E.business_group_id + 0 = p_business_group_id
and A.business_group_id + 0 = p_business_group_id
and A.PERSON_ID = p_person_id
and A.APPLICATION_ID = p_application_id
and B.EVENT_ID = E.EVENT_ID
and E.DATE_START >= P_date_end
and E.EVENT_OR_INTERVIEW = 'I'
and E.ASSIGNMENT_ID = A.ASSIGNMENT_ID;
DELETE FROM per_bookings bk
WHERE bk.business_group_id + 0 = p_business_group_id
AND bk.booking_id = c_viewers_rec.BOOKING_ID
AND bk.person_id = c_viewers_rec.PERSON_ID;
DELETE FROM per_events ev
WHERE ev.event_id = chk_events_rec.event_id
AND ev.business_group_id + 0 = p_business_group_id;
DELETE FROM per_events ev
WHERE ev.event_id = chk_events_rec.event_id
AND ev.business_group_id + 0 = p_business_group_id;
P_last_updated_by NUMBER,
P_last_update_login NUMBER,
P_end_of_time DATE) IS
--
BEGIN
DELETE FROM per_all_people_f papf
WHERE papf.person_id = P_person_id
AND papf.business_group_id + 0 = P_Business_group_id
AND papf.effective_start_date = P_date_end + 1;
UPDATE per_all_people_f papf
SET papf.effective_end_date = P_end_of_time
, papf.last_updated_by = P_last_updated_by
, papf.last_update_date = trunc(sysdate)
, papf.last_update_login = P_last_update_login
WHERE papf.person_id = P_person_id
AND papf.BUSINESS_GROUP_ID + 0 = P_Business_group_id
AND papf.effective_end_date = P_date_end;
SELECT 1
FROM PER_APPLICATIONS PA
WHERE PA.business_group_id + 0 = P_Business_group_id
AND PA.PERSON_ID = P_person_id
AND PA.APPLICATION_ID = P_application_id
AND PA.DATE_END IS NOT NULL
AND PA.DATE_END = P_date_end;
P_last_updated_by NUMBER,
P_last_update_login NUMBER) IS
--
BEGIN
UPDATE per_all_people_f papf
set PAPF.effective_end_date = P_date_end
, PAPF.last_updated_by = P_last_updated_by
, PAPF.last_update_date = trunc(sysdate)
, PAPF.last_update_login = P_last_update_login
where PAPF.person_id = P_person_id
and P_date_end BETWEEN
PAPF.effective_start_date AND PAPF.effective_end_date
and PAPF.business_group_id + 0 = P_Business_group_id;
INSERT INTO per_all_people_f
(PERSON_ID ,EFFECTIVE_START_DATE ,EFFECTIVE_END_DATE
,BUSINESS_GROUP_ID ,PERSON_TYPE_ID ,LAST_NAME
,START_DATE ,APPLICANT_NUMBER
,COMMENT_ID
,CURRENT_APPLICANT_FLAG
,CURRENT_EMP_OR_APL_FLAG
,CURRENT_EMPLOYEE_FLAG
,CURRENT_NPW_FLAG
,DATE_EMPLOYEE_DATA_VERIFIED
,DATE_OF_BIRTH ,EMAIL_ADDRESS
,EMPLOYEE_NUMBER ,EXPENSE_CHECK_SEND_TO_ADDRESS
,FIRST_NAME ,FULL_NAME
,KNOWN_AS ,MARITAL_STATUS ,MIDDLE_NAMES
,NATIONALITY ,NATIONAL_IDENTIFIER ,PREVIOUS_LAST_NAME
,REGISTERED_DISABLED_FLAG ,SEX ,TITLE
,VENDOR_ID ,WORK_TELEPHONE ,REQUEST_ID
,PROGRAM_APPLICATION_ID ,PROGRAM_ID
,PROGRAM_UPDATE_DATE ,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 , LAST_UPDATE_DATE ,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN ,CREATED_BY ,CREATION_DATE
,PER_INFORMATION_CATEGORY
,PER_INFORMATION1
,PER_INFORMATION2
,PER_INFORMATION3
,PER_INFORMATION4
,PER_INFORMATION5
,PER_INFORMATION6
,PER_INFORMATION7
,PER_INFORMATION8
,PER_INFORMATION9
,PER_INFORMATION10
,PER_INFORMATION11
,PER_INFORMATION12
,PER_INFORMATION13
,PER_INFORMATION14
,PER_INFORMATION15
,PER_INFORMATION16
,PER_INFORMATION17
,PER_INFORMATION18
,PER_INFORMATION19
,PER_INFORMATION20
,PER_INFORMATION21
,PER_INFORMATION22
,PER_INFORMATION23
,PER_INFORMATION24
,PER_INFORMATION25
,PER_INFORMATION26
,PER_INFORMATION27
,PER_INFORMATION28
,PER_INFORMATION29
,PER_INFORMATION30
,BACKGROUND_CHECK_STATUS
,BACKGROUND_DATE_CHECK
,BLOOD_TYPE
,CORRESPONDENCE_LANGUAGE
,FAST_PATH_EMPLOYEE
,FTE_CAPACITY
,HOLD_APPLICANT_DATE_UNTIL
,HONORS
,INTERNAL_LOCATION
,LAST_MEDICAL_TEST_BY
,LAST_MEDICAL_TEST_DATE
,MAILSTOP
,OFFICE_NUMBER
,ON_MILITARY_SERVICE
,ORDER_NAME
,PRE_NAME_ADJUNCT
,PROJECTED_START_DATE
,REHIRE_AUTHORIZOR
,REHIRE_REASON
,REHIRE_RECOMMENDATION
,RESUME_EXISTS
,RESUME_LAST_UPDATED
,SECOND_PASSPORT_EXISTS
,STUDENT_STATUS
,SUFFIX
,WORK_SCHEDULE
,town_of_birth
,region_of_birth
,country_of_birth
,global_person_id
,party_id
,original_date_of_hire
--Bug2974671 starts here.
,BENEFIT_GROUP_ID
,COORD_BEN_MED_PLN_NO
,COORD_BEN_NO_CVG_FLAG
,DPDNT_ADOPTION_DATE
,DPDNT_VLNTRY_SVCE_FLAG
,USES_TOBACCO_FLAG
-- Bug2974671 ends here.
,NPW_NUMBER -- Added for Fix for #3184546
)
select PAPF.PERSON_ID
,PAPF.EFFECTIVE_END_DATE+1
,P_end_of_time
,PAPF.BUSINESS_GROUP_ID ,PPT.PERSON_TYPE_ID
,PAPF.LAST_NAME ,PAPF.START_DATE
,PAPF.APPLICANT_NUMBER ,PAPF.COMMENT_ID
,null
,PAPF.CURRENT_EMPLOYEE_FLAG
,PAPF.CURRENT_EMPLOYEE_FLAG
,PAPF.CURRENT_NPW_FLAG
,PAPF.DATE_EMPLOYEE_DATA_VERIFIED
,PAPF.DATE_OF_BIRTH
,PAPF.EMAIL_ADDRESS
,PAPF.EMPLOYEE_NUMBER
,PAPF.EXPENSE_CHECK_SEND_TO_ADDRESS
,PAPF.FIRST_NAME ,PAPF.FULL_NAME
,PAPF.KNOWN_AS ,PAPF.MARITAL_STATUS
,PAPF.MIDDLE_NAMES ,PAPF.NATIONALITY
,PAPF.NATIONAL_IDENTIFIER
,PAPF.PREVIOUS_LAST_NAME
,PAPF.REGISTERED_DISABLED_FLAG
,PAPF.SEX ,PAPF.TITLE ,PAPF.VENDOR_ID
,PAPF.WORK_TELEPHONE ,PAPF.REQUEST_ID
,PAPF.PROGRAM_APPLICATION_ID
,PAPF.PROGRAM_ID
,PAPF.PROGRAM_UPDATE_DATE
,PAPF.ATTRIBUTE_CATEGORY
,PAPF.ATTRIBUTE1 ,PAPF.ATTRIBUTE2
,PAPF.ATTRIBUTE3 ,PAPF.ATTRIBUTE4
,PAPF.ATTRIBUTE5 ,PAPF.ATTRIBUTE6
,PAPF.ATTRIBUTE7 ,PAPF.ATTRIBUTE8
,PAPF.ATTRIBUTE9 ,PAPF.ATTRIBUTE10
,PAPF.ATTRIBUTE11 ,PAPF.ATTRIBUTE12
,PAPF.ATTRIBUTE13 ,PAPF.ATTRIBUTE14
,PAPF.ATTRIBUTE15 ,PAPF.ATTRIBUTE16
,PAPF.ATTRIBUTE17 ,PAPF.ATTRIBUTE18
,PAPF.ATTRIBUTE19 ,PAPF.ATTRIBUTE20
,PAPF.ATTRIBUTE21 ,PAPF.ATTRIBUTE22
,PAPF.ATTRIBUTE23 ,PAPF.ATTRIBUTE24
,PAPF.ATTRIBUTE25 ,PAPF.ATTRIBUTE26
,PAPF.ATTRIBUTE27 ,PAPF.ATTRIBUTE28
,PAPF.ATTRIBUTE29 ,PAPF.ATTRIBUTE30
,PAPF.LAST_UPDATE_DATE ,PAPF.LAST_UPDATED_BY
,PAPF.LAST_UPDATE_LOGIN ,PAPF.CREATED_BY
,PAPF.CREATION_DATE
,PAPF.PER_INFORMATION_CATEGORY
,PAPF.PER_INFORMATION1
,PAPF.PER_INFORMATION2
,PAPF.PER_INFORMATION3
,PAPF.PER_INFORMATION4
,PAPF.PER_INFORMATION5
,PAPF.PER_INFORMATION6
,PAPF.PER_INFORMATION7
,PAPF.PER_INFORMATION8
,PAPF.PER_INFORMATION9
,PAPF.PER_INFORMATION10
,PAPF.PER_INFORMATION11
,PAPF.PER_INFORMATION12
,PAPF.PER_INFORMATION13
,PAPF.PER_INFORMATION14
,PAPF.PER_INFORMATION15
,PAPF.PER_INFORMATION16
,PAPF.PER_INFORMATION17
,PAPF.PER_INFORMATION18
,PAPF.PER_INFORMATION19
,PAPF.PER_INFORMATION20
,PAPF.PER_INFORMATION21
,PAPF.PER_INFORMATION22
,PAPF.PER_INFORMATION23
,PAPF.PER_INFORMATION24
,PAPF.PER_INFORMATION25
,PAPF.PER_INFORMATION26
,PAPF.PER_INFORMATION27
,PAPF.PER_INFORMATION28
,PAPF.PER_INFORMATION29
,PAPF.PER_INFORMATION30
,PAPF.BACKGROUND_CHECK_STATUS
,PAPF.BACKGROUND_DATE_CHECK
,PAPF.BLOOD_TYPE
,PAPF.CORRESPONDENCE_LANGUAGE
,PAPF.FAST_PATH_EMPLOYEE
,PAPF.FTE_CAPACITY
,PAPF.HOLD_APPLICANT_DATE_UNTIL
,PAPF.HONORS
,PAPF.INTERNAL_LOCATION
,PAPF.LAST_MEDICAL_TEST_BY
,PAPF.LAST_MEDICAL_TEST_DATE
,PAPF.MAILSTOP
,PAPF.OFFICE_NUMBER
,PAPF.ON_MILITARY_SERVICE
,PAPF.ORDER_NAME
,PAPF.PRE_NAME_ADJUNCT
,PAPF.PROJECTED_START_DATE
,PAPF.REHIRE_AUTHORIZOR
,PAPF.REHIRE_REASON
,PAPF.REHIRE_RECOMMENDATION
,PAPF.RESUME_EXISTS
,PAPF.RESUME_LAST_UPDATED
,PAPF.SECOND_PASSPORT_EXISTS
,PAPF.STUDENT_STATUS
,PAPF.SUFFIX
,PAPF.WORK_SCHEDULE
,PAPF.town_of_birth
,PAPF.region_of_birth
,PAPF.country_of_birth
,PAPF.global_person_id
,PAPF.party_id
,PAPF.original_date_of_hire
-- Bug2974671 starts here.
,PAPF.BENEFIT_GROUP_ID
,PAPF.COORD_BEN_MED_PLN_NO
,PAPF.COORD_BEN_NO_CVG_FLAG
,PAPF.DPDNT_ADOPTION_DATE
,PAPF.DPDNT_VLNTRY_SVCE_FLAG
,PAPF.USES_TOBACCO_FLAG
--Bug2974671 ends here.
,PAPF.NPW_NUMBER -- Added for Fix for #3184546
FROM per_all_people_f PAPF,
PER_PERSON_TYPES PPT,
per_person_types PPT2
WHERE PAPF.person_id = P_person_id
AND PAPF.effective_end_date = P_date_end
AND PPT.business_group_id = P_business_group_id
and PAPF.business_group_id + 0 = P_Business_group_id
AND PPT.default_flag = 'Y'
AND PPT2.person_type_id = PAPF.person_type_id
AND PPT.system_person_type =
decode(PPT2.system_person_type,'APL', 'EX_APL'
,'APL_EX_APL', 'EX_APL'
,'EMP_APL', 'EMP'
,'EX_EMP', 'EX_APL'
,'EX_EMP_APL', 'EX_EMP' -- Added for fix of #3311891
,'EX_APL');
PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
p_Application_Id IN OUT NOCOPY NUMBER,
p_Business_Group_Id NUMBER,
p_Person_Id NUMBER,
p_Date_Received DATE,
p_Comments VARCHAR2,
p_Current_Employer VARCHAR2,
p_Date_End DATE,
p_Projected_Hire_Date DATE,
p_Successful_Flag VARCHAR2,
p_Termination_Reason VARCHAR2,
p_Appl_Attribute_Category VARCHAR2,
p_Appl_Attribute1 VARCHAR2,
p_Appl_Attribute2 VARCHAR2,
p_Appl_Attribute3 VARCHAR2,
p_Appl_Attribute4 VARCHAR2,
p_Appl_Attribute5 VARCHAR2,
p_Appl_Attribute6 VARCHAR2,
p_Appl_Attribute7 VARCHAR2,
p_Appl_Attribute8 VARCHAR2,
p_Appl_Attribute9 VARCHAR2,
p_Appl_Attribute10 VARCHAR2,
p_Appl_Attribute11 VARCHAR2,
p_Appl_Attribute12 VARCHAR2,
p_Appl_Attribute13 VARCHAR2,
p_Appl_Attribute14 VARCHAR2,
p_Appl_Attribute15 VARCHAR2,
p_Appl_Attribute16 VARCHAR2,
p_Appl_Attribute17 VARCHAR2,
p_Appl_Attribute18 VARCHAR2,
p_Appl_Attribute19 VARCHAR2,
p_Appl_Attribute20 VARCHAR2,
p_Last_Update_Date DATE,
p_Last_Updated_By NUMBER,
p_Last_Update_Login NUMBER,
p_Created_By NUMBER,
p_Creation_Date DATE
) IS
CURSOR C IS SELECT rowid FROM PER_APPLICATIONS
WHERE application_id = p_Application_Id;
CURSOR C2 IS SELECT per_applications_s.nextval FROM sys.dual;
INSERT INTO PER_APPLICATIONS(
application_id,
business_group_id,
person_id,
date_received,
comments,
current_employer,
date_end,
projected_hire_date,
successful_flag,
termination_reason,
appl_attribute_category,
appl_attribute1,
appl_attribute2,
appl_attribute3,
appl_attribute4,
appl_attribute5,
appl_attribute6,
appl_attribute7,
appl_attribute8,
appl_attribute9,
appl_attribute10,
appl_attribute11,
appl_attribute12,
appl_attribute13,
appl_attribute14,
appl_attribute15,
appl_attribute16,
appl_attribute17,
appl_attribute18,
appl_attribute19,
appl_attribute20,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date
) VALUES (
p_Application_Id,
p_Business_Group_Id,
p_Person_Id,
p_Date_Received,
p_Comments,
p_Current_Employer,
p_Date_End,
p_Projected_Hire_Date,
p_Successful_Flag,
p_Termination_Reason,
p_Appl_Attribute_Category,
p_Appl_Attribute1,
p_Appl_Attribute2,
p_Appl_Attribute3,
p_Appl_Attribute4,
p_Appl_Attribute5,
p_Appl_Attribute6,
p_Appl_Attribute7,
p_Appl_Attribute8,
p_Appl_Attribute9,
p_Appl_Attribute10,
p_Appl_Attribute11,
p_Appl_Attribute12,
p_Appl_Attribute13,
p_Appl_Attribute14,
p_Appl_Attribute15,
p_Appl_Attribute16,
p_Appl_Attribute17,
p_Appl_Attribute18,
p_Appl_Attribute19,
p_Appl_Attribute20,
p_Last_Update_Date,
p_Last_Updated_By,
p_Last_Update_Login,
p_Created_By,
p_Creation_Date
);
END Insert_Row;
SELECT *
FROM PER_APPLICATIONS
WHERE rowid = p_Rowid
FOR UPDATE of Application_Id NOWAIT;
PROCEDURE Update_Row(p_Rowid VARCHAR2,
p_Application_Id NUMBER,
p_Business_Group_Id NUMBER,
p_Person_Id NUMBER,
p_Person_Type_Id NUMBER,
p_Date_Received DATE,
p_Comments VARCHAR2,
p_Current_Employer VARCHAR2,
p_Date_End DATE,
p_Projected_Hire_Date DATE,
p_Successful_Flag VARCHAR2,
p_Termination_Reason VARCHAR2,
p_Cancellation_Flag VARCHAR2, -- parameter added for Bug 3053711
p_Appl_Attribute_Category VARCHAR2,
p_Appl_Attribute1 VARCHAR2,
p_Appl_Attribute2 VARCHAR2,
p_Appl_Attribute3 VARCHAR2,
p_Appl_Attribute4 VARCHAR2,
p_Appl_Attribute5 VARCHAR2,
p_Appl_Attribute6 VARCHAR2,
p_Appl_Attribute7 VARCHAR2,
p_Appl_Attribute8 VARCHAR2,
p_Appl_Attribute9 VARCHAR2,
p_Appl_Attribute10 VARCHAR2,
p_Appl_Attribute11 VARCHAR2,
p_Appl_Attribute12 VARCHAR2,
p_Appl_Attribute13 VARCHAR2,
p_Appl_Attribute14 VARCHAR2,
p_Appl_Attribute15 VARCHAR2,
p_Appl_Attribute16 VARCHAR2,
p_Appl_Attribute17 VARCHAR2,
p_Appl_Attribute18 VARCHAR2,
p_Appl_Attribute19 VARCHAR2,
p_Appl_Attribute20 VARCHAR2
) IS
--changed for 2506446 from the old select
cursor csr_ptu_row is
select ptu.effective_start_date
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = p_person_id
and ptu.effective_start_date > p_date_received
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'EX_APL'
order by ptu.effective_start_date;
select ptu.person_type_id,ptu.effective_start_date
from per_person_type_usages_f ptu
where ptu.person_id = p_person_id
and p_date_end+1 between ptu.effective_start_date and
ptu.effective_start_date;
l_update_mode varchar2(30);
hr_utility.set_location('per_applications_pkg.update_row',10);
UPDATE PER_APPLICATIONS
SET
application_id = p_Application_Id,
business_group_id = p_Business_Group_Id,
person_id = p_Person_Id,
date_received = p_Date_Received,
comments = p_Comments,
current_employer = p_Current_Employer,
date_end = p_Date_End,
projected_hire_date = p_Projected_Hire_Date,
successful_flag = p_Successful_Flag,
termination_reason = p_Termination_Reason,
appl_attribute_category = p_Appl_Attribute_Category,
appl_attribute1 = p_Appl_Attribute1,
appl_attribute2 = p_Appl_Attribute2,
appl_attribute3 = p_Appl_Attribute3,
appl_attribute4 = p_Appl_Attribute4,
appl_attribute5 = p_Appl_Attribute5,
appl_attribute6 = p_Appl_Attribute6,
appl_attribute7 = p_Appl_Attribute7,
appl_attribute8 = p_Appl_Attribute8,
appl_attribute9 = p_Appl_Attribute9,
appl_attribute10 = p_Appl_Attribute10,
appl_attribute11 = p_Appl_Attribute11,
appl_attribute12 = p_Appl_Attribute12,
appl_attribute13 = p_Appl_Attribute13,
appl_attribute14 = p_Appl_Attribute14,
appl_attribute15 = p_Appl_Attribute15,
appl_attribute16 = p_Appl_Attribute16,
appl_attribute17 = p_Appl_Attribute17,
appl_attribute18 = p_Appl_Attribute18,
appl_attribute19 = p_Appl_Attribute19,
appl_attribute20 = p_Appl_Attribute20
WHERE rowid = p_rowid;
UPDATE PER_APPLICATIONS
SET
application_id = p_Application_Id,
business_group_id = p_Business_Group_Id,
person_id = p_Person_Id,
date_received = p_Date_Received,
comments = p_Comments,
current_employer = p_Current_Employer,
date_end = p_Date_End,
projected_hire_date = p_Projected_Hire_Date,
successful_flag = p_Successful_Flag,
termination_reason = p_Termination_Reason,
appl_attribute_category = p_Appl_Attribute_Category,
appl_attribute1 = p_Appl_Attribute1,
appl_attribute2 = p_Appl_Attribute2,
appl_attribute3 = p_Appl_Attribute3,
appl_attribute4 = p_Appl_Attribute4,
appl_attribute5 = p_Appl_Attribute5,
appl_attribute6 = p_Appl_Attribute6,
appl_attribute7 = p_Appl_Attribute7,
appl_attribute8 = p_Appl_Attribute8,
appl_attribute9 = p_Appl_Attribute9,
appl_attribute10 = p_Appl_Attribute10,
appl_attribute11 = p_Appl_Attribute11,
appl_attribute12 = p_Appl_Attribute12,
appl_attribute13 = p_Appl_Attribute13,
appl_attribute14 = p_Appl_Attribute14,
appl_attribute15 = p_Appl_Attribute15,
appl_attribute16 = p_Appl_Attribute16,
appl_attribute17 = p_Appl_Attribute17,
appl_attribute18 = p_Appl_Attribute18,
appl_attribute19 = p_Appl_Attribute19,
appl_attribute20 = p_Appl_Attribute20
WHERE rowid = p_rowid;
hr_utility.set_location('per_applications_pkg.update_row',20);
hr_utility.set_location('per_applications_pkg.update_row',30);
END Update_Row;
PROCEDURE Delete_Row(p_Rowid VARCHAR2) IS
BEGIN
DELETE FROM PER_APPLICATIONS
WHERE rowid = p_Rowid;
END Delete_Row;
select a.assignment_status_type_id
from per_assignment_status_types a,
per_ass_status_type_amends b
where a.per_system_status = l_ass_status
and b.assignment_status_type_id(+) = a.assignment_status_type_id
and b.business_group_id(+) + 0 = p_business_group_id
and nvl(a.business_group_id, p_business_group_id) =
p_business_group_id
and nvl(a.legislation_codE, p_legislation_code) =
p_legislation_code
and nvl(b.active_flag, a.active_flag) = 'Y'
and nvl(b.default_flag, a.default_flag) = 'Y';
select paa.assignment_id
from per_all_assignments_f paa
where paa.application_id = p_application_id
and paa.person_id = p_person_id
and paa.business_group_id + 0 = p_business_group_id
and paa.assignment_type = 'A'
and paa.effective_end_date =
(select max(pa2.effective_end_date)
from per_all_assignments_f pa2
where pa2.person_id = p_person_id
and pa2.application_id = p_application_id);
select paa.assignment_id
from per_all_assignments_f paa
where paa.application_id = p_application_id
and paa.person_id = p_person_id
and paa.business_group_id + 0 = p_business_group_id
and paa.assignment_type = 'A'
and paa.effective_end_date = p_date_end;