The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_dummy number(1); -- dummy variable for 'select 1...' statements
SELECT 1
FROM per_people_f
where person_id= X_INTERNAL_CONTACT_PERSON_ID
AND X_DATE_START BETWEEN
effective_Start_date and effective_end_Date;
SELECT 1
FROM hr_organization_units H
WHERE H.business_group_id + 0 = X_BUSINESS_GROUP_ID
AND X_DATE_START BETWEEN H.date_from
and NVL(H.date_to , X_CTL_GLOBALS_END_OF_TIME)
AND H.ORGANIZATION_ID = X_ORGANIZATION_RUN_BY_ID;
SELECT 1
FROM hr_locations l
WHERE X_DATE_START <= nvl(l.inactive_date,X_CTL_GLOBALS_END_OF_TIME)
AND LOCATION_ID = X_LOCATION_ID;
SELECT 1
FROM PER_PEOPLE_F P,
PER_BOOKINGS B
WHERE P.PERSON_ID = B.PERSON_ID
AND B.EVENT_ID = X_EVENT_ID;
SELECT 1
FROM PER_PEOPLE_F P,
PER_BOOKINGS B
WHERE P.PERSON_ID = B.PERSON_ID
AND B.EVENT_ID = X_EVENT_ID
AND X_DATE_START BETWEEN
P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE; */
select 1
from per_assignments_f
where assignment_id = p_assignment_id
and effective_start_date = p_event_date;
select event.time_start, nvl(event.time_end,'24:00')
from per_events event, per_assignments assignment
where (p_rowid is null or p_rowid <> event.rowid)
and assignment.person_id = p_person_id
and event.assignment_id = assignment.assignment_id
and event.event_or_interview = 'I'
and p_interview_start_date between event.date_start
and nvl(event.date_end,
event.date_start);
select 1
from per_bookings
where event_id = p_event_id;
hr_utility.set_message (801,'PER_7517_APP_INT_DELETE');
select interview.person_id
from per_bookings INTERVIEW
where interview.event_id = p_event_id;
select min (effective_start_date)
from per_all_people_f
where person_id = p_person_id;
select max (effective_start_date)
from per_all_people_f
where person_id = p_person_id;
select 'Y'
from per_all_workforce_v
where person_id = p_person_id
and p_new_interview_start_date between effective_start_date
and effective_end_date;
select null
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 vacancy_id
From per_all_assignments_f
Where assignment_id = p_assignment_id
And p_session_date between effective_start_date and effective_end_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_requests
( letter_request_id
, business_group_id
, letter_type_id
, request_status
, auto_or_manual
, date_from
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, vacancy_id)
select per_letter_requests_s.nextval
, p_business_group_id
, s.letter_type_id
, 'PENDING'
, 'AUTO'
, p_session_date
, sysdate
, p_user
, p_login_id
, p_user
, sysdate
, l_vacancy_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 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.business_group_id + 0 = s.business_group_id
and r.request_status = 'PENDING'
and r.auto_or_manual = 'AUTO'
and r.vacancy_id = l_vacancy_id);
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
, p_ASSIGNMENT_ID
, p_ASSIGNMENT_STATUS_TYPE_ID
, p_session_date
, sysdate
, p_user
, p_login_id
, p_user
, sysdate
from per_letter_requests r
where exists
(select null
from per_letter_gen_statuses s
where s.letter_type_id = r.letter_type_id
and s.business_group_id + 0 = p_business_group_id
and s.business_group_id + 0 = r.business_group_id + 0
and s.assignment_status_type_id =
p_ASSIGNMENT_STATUS_TYPE_ID
and s.enabled_flag = 'Y')
and not exists
(select l.assignment_id
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
and l.assignment_id = p_ASSIGNMENT_ID
and l.business_group_id +0 = r.business_group_id + 0)
and r.request_status = 'PENDING'
and r.business_group_id = p_business_group_id -- bug fix 3648618
and r.vacancy_id = l_vacancy_id;
insert into per_letter_requests
( letter_request_id
, business_group_id
, letter_type_id
, request_status
, auto_or_manual
, date_from
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, vacancy_id)
select per_letter_requests_s.nextval
, p_business_group_id
, s.letter_type_id
, 'PENDING'
, 'AUTO'
, p_session_date
, sysdate
, p_user
, p_login_id
, p_user
, sysdate
, l_vacancy_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 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.business_group_id + 0 = s.business_group_id
and r.request_status = 'PENDING'
and r.auto_or_manual = 'AUTO'
);
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
, p_ASSIGNMENT_ID
, p_ASSIGNMENT_STATUS_TYPE_ID
, p_session_date
, sysdate
, p_user
, p_login_id
, p_user
, sysdate
from per_letter_requests r
where exists
(select null
from per_letter_gen_statuses s
where s.letter_type_id = r.letter_type_id
and s.business_group_id + 0 = p_business_group_id
and s.business_group_id + 0 = r.business_group_id + 0
and s.assignment_status_type_id =
p_ASSIGNMENT_STATUS_TYPE_ID
and s.enabled_flag = 'Y')
and not exists
(select l.assignment_id
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
and l.assignment_id = p_ASSIGNMENT_ID
and l.business_group_id +0 = r.business_group_id + 0)
and r.request_status = 'PENDING'
and r.business_group_id = p_business_group_id -- bug fix 3648618
;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Event_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Location_Id NUMBER,
X_Internal_Contact_Person_Id NUMBER,
X_Organization_Run_By_Id NUMBER,
X_Assignment_Id NUMBER,
X_Date_Start DATE,
X_Type VARCHAR2,
X_Comments VARCHAR2,
X_Contact_Telephone_Number VARCHAR2,
X_Date_End DATE,
X_Emp_Or_Apl VARCHAR2,
X_Event_Or_Interview VARCHAR2,
X_External_Contact VARCHAR2,
X_Time_End VARCHAR2,
X_Time_Start VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_ctl_globals_end_of_time DATE
) IS
L_DUMMY NUMBER;
SELECT rowid FROM PER_EVENTS
WHERE event_id = X_Event_Id;
SELECT PER_EVENTS_S.NEXTVAL
FROM SYS.DUAL;
select 1
from hr_locations l
where l.location_id = X_Location_Id
and nvl(l.inactive_date,X_ctl_globals_end_of_time) >= X_date_start;
select max(party_id) from per_all_people_f
where person_id = (select asg.person_id
from per_all_assignments_f asg
where asg.assignment_id = X_Assignment_Id
and X_Date_Start between asg.effective_start_date
and asg.effective_end_date);
INSERT INTO PER_EVENTS(
event_id,
business_group_id,
location_id,
internal_contact_person_id,
organization_run_by_id,
assignment_id,
date_start,
type,
comments,
contact_telephone_number,
date_end,
emp_or_apl,
event_or_interview,
external_contact,
time_end,
time_start,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
party_id
) VALUES (
X_Event_Id,
X_Business_Group_Id,
X_Location_Id,
X_Internal_Contact_Person_Id,
X_Organization_Run_By_Id,
X_Assignment_Id,
X_Date_Start,
X_Type,
X_Comments,
X_Contact_Telephone_Number,
X_Date_End,
X_Emp_Or_Apl,
X_Event_Or_Interview,
X_External_Contact,
X_Time_End,
X_Time_Start,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Attribute16,
X_Attribute17,
X_Attribute18,
X_Attribute19,
X_Attribute20,
l_party_id
);
HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','INSERT_ROW');
END Insert_Row;
SELECT *
FROM PER_EVENTS
WHERE rowid = X_Rowid
FOR UPDATE of Event_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Event_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Location_Id NUMBER,
X_Internal_Contact_Person_Id NUMBER,
X_Organization_Run_By_Id NUMBER,
X_Assignment_Id NUMBER,
X_Date_Start DATE,
X_Type VARCHAR2,
X_Comments VARCHAR2,
X_Contact_Telephone_Number VARCHAR2,
X_Date_End DATE,
X_Emp_Or_Apl VARCHAR2,
X_Event_Or_Interview VARCHAR2,
X_External_Contact VARCHAR2,
X_Time_End VARCHAR2,
X_Time_Start VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_ctl_globals_end_of_time DATE
) IS
L_DUMMY NUMBER;
select 1
from hr_locations l
where l.location_id = X_Location_Id
and nvl(l.inactive_date,X_ctl_globals_end_of_time) >= X_Date_Start;
UPDATE PER_EVENTS
SET
event_id = X_Event_Id,
business_group_id = X_Business_Group_Id,
location_id = X_Location_Id,
internal_contact_person_id = X_Internal_Contact_Person_Id,
organization_run_by_id = X_Organization_Run_By_Id,
assignment_id = X_Assignment_Id,
date_start = X_Date_Start,
type = X_Type,
comments = X_Comments,
contact_telephone_number = X_Contact_Telephone_Number,
date_end = X_Date_End,
emp_or_apl = X_Emp_Or_Apl,
event_or_interview = X_Event_Or_Interview,
external_contact = X_External_Contact,
time_end = X_Time_End,
time_start = X_Time_Start,
attribute_category = X_Attribute_Category,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15,
attribute16 = X_Attribute16,
attribute17 = X_Attribute17,
attribute18 = X_Attribute18,
attribute19 = X_Attribute19,
attribute20 = X_Attribute20
WHERE rowid = X_rowid;
HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','UPDATE_ROW');
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
X_Event_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Message VARCHAR2,
X_Form VARCHAR2
) IS
L_DUMMY NUMBER;
SELECT 1
FROM PER_BOOKINGS
WHERE BUSINESS_GROUP_ID + 0 = X_Business_Group_Id
AND EVENT_ID = X_Event_Id;
SELECT 1
FROM PER_PAY_PROPOSALS PP
WHERE PP.EVENT_ID = X_Event_Id;
DELETE FROM PER_EVENTS
WHERE rowid = X_Rowid;
HR_UTILITY.SET_MESSAGE_TOKEN('PROCEDURE','DELETE_ROW');
END Delete_Row;