[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
this procedure from delete_row.
05-MAR-97 J.Alloun 70.10 Changed all occurances of system.dual
to sys.dual for next release requirements.
26-JAN-98 I.Harding 110.2 Added vacancy_category parameter to
insert, update and lock procs.
22-APR-98 D.Kerr 110.3 658840: removed date conversions in
csr_current in folder_current.
25-FEB-98 B.Goodsell 115.2 Added Budget Measurement columns to
Table Handler procedures
21-MAY-99 C.Carter 115.3 Removed set_token call after error
message 6125.
05-Oct-99 SCNair 115.4 Date Track position related changes
12-Jun-00 hsajja 115.5 Changed HR_POSITIONS to HR_POSITIONS_F
and corresponding effective_date changes
26-Jun-00 C.Carter 115.6 Changed PER_JOBS to PER_JOBS_V.
07-SEP-01 A.Cowan 115.7-10 Cascade vacancy enhancement
bug # 1923803
26-Jun-03 vanantha 115.12 Added a procedure D_to_updt_org_chk
to validate end date for Vacancy
with respect to Org end date.
06-Jul-05 ghshanka 115.14 modified the cursor def in the procedure
D_from_updt_person so that it can
validate the cross business group
profile option also.
03-JUL-06 avarri 115.15 Modiifed the procedure Check_Unique_Name
for 4262036.
============================================================================*/
----------------------------------------------------------------------------
--
-- Name --
-- Check_References --
-- Purpose --
-- To ensure the referential integrity when a vacancy is deleted from the--
-- Define Requisition and Vacancy form. --
-- Checks that the vacancy is not used in a recruitment activity
-- or by an assignment.
-----------------------------------------------------------------------------
--
PROCEDURE Check_References(P_vacancy_id NUMBER ) is
CURSOR c_check_references1 IS
SELECT distinct(PV.NAME)
FROM PER_VACANCIES PV,
PER_ALL_ASSIGNMENTS_F PAF
WHERE PAF.VACANCY_ID = P_vacancy_id
AND PV.VACANCY_ID = P_vacancy_id
AND PAF.VACANCY_ID = PV.VACANCY_ID;
SELECT PV.NAME
FROM PER_VACANCIES PV
, PER_RECRUITMENT_ACTIVITY_FOR PRAF
WHERE PRAF.VACANCY_ID = P_vacancy_id
AND PV.VACANCY_ID = P_vacancy_id ;
SELECT COUNT(distinct ass.assignment_id)
FROM PER_ALL_ASSIGNMENTS ASS,
PER_ASSIGNMENT_STATUS_TYPES a
where nvl(A.BUSINESS_GROUP_ID,P_Business_group_id) =
P_Business_group_id
and ass.business_group_id + 0 = P_Business_group_id
and ass.ASSIGNMENT_TYPE = 'A'
and ass.ASSIGNMENT_STATUS_TYPE_ID = A.ASSIGNMENT_STATUS_TYPE_ID
and nvl(a.LEGISLATION_CODE,P_legislation_code) =
P_legislation_code
and A.PER_SYSTEM_STATUS = P_vac_type
and ass.vacancy_id = P_vacancy_id;
SELECT COUNT(*)
FROM PER_ALL_ASSIGNMENTS A
WHERE A.business_group_id + 0 = P_Business_group_id
AND A.VACANCY_ID = P_vacancy_id
AND A.ASSIGNMENT_TYPE = 'E';
SELECT COUNT(DISTINCT A.ASSIGNMENT_ID)
FROM PER_ALL_ASSIGNMENTS A
WHERE A.VACANCY_ID = P_vacancy_id
AND A.business_group_id + 0 = P_Business_group_id
AND A.ASSIGNMENT_TYPE = 'E'
AND A.EFFECTIVE_START_DATE <= P_session_date ;
select '1'
from per_all_assignments_f
where vacancy_id =P_vacancy_id
and effective_start_date < P_vac_date_from
and assignment_type = 'A';
SELECT v.name
FROM PER_ALL_VACANCIES v
WHERE v.NAME = P_Name
AND v.business_group_id = P_business_group_id
AND (P_rowid <> v.rowid
or P_rowid is NULL);
SELECT 1
FROM PER_REQUISITIONS PR
WHERE PR.REQUISITION_ID = P_requisition_id
AND PR.business_group_id + 0 = P_Business_group_id
AND P_vac_date_from < PR.DATE_FROM;
SELECT 1
FROM PER_REQUISITIONS PR
WHERE PR.REQUISITION_ID = P_requisition_id
AND PR.business_group_id + 0 = P_Business_group_id
AND P_vac_date_to > PR.DATE_TO;
SELECT 1
FROM PER_RECRUITMENT_ACTIVITY_FOR F,
PER_RECRUITMENT_ACTIVITIES ACTS
WHERE F.VACANCY_ID = P_vacancy_id
AND F.RECRUITMENT_ACTIVITY_ID = ACTS.RECRUITMENT_ACTIVITY_ID
AND ( ACTS.DATE_START < P_vac_date_from
OR nvl(ACTS.DATE_END,p_end_of_time) > nvl(P_vac_date_to, P_end_of_time) ) ;
SELECT 1
FROM HR_ORGANIZATION_UNITS HOU
WHERE HOU.ORGANIZATION_ID = P_organization_id
AND HOU.business_group_id + 0 = P_Business_group_id
AND P_vac_date_from < HOU.DATE_FROM;
SELECT date_to
FROM HR_ORGANIZATION_UNITS HOU
WHERE HOU.ORGANIZATION_ID = P_organization_id
AND HOU.business_group_id + 0 = P_Business_group_id
AND P_vac_date_to > nvl(HOU.date_to, hr_api.g_eot);
SELECT 1
FROM HR_POSITIONS_F POS
WHERE POS.POSITION_ID = P_position_id
AND POS.business_group_id + 0 = P_Business_group_id
AND P_vac_date_from < POS.DATE_EFFECTIVE;
SELECT 1
FROM PAY_PEOPLE_GROUPS PPG
WHERE PPG.PEOPLE_GROUP_ID = P_people_group_id
AND P_vac_date_from < nvl(PPG.START_DATE_ACTIVE,
P_start_of_time);
SELECT 1
FROM PER_JOBS_V PJ
WHERE PJ.JOB_ID = P_job_id
AND PJ.business_group_id + 0 = P_business_group_id
AND P_vac_date_from < PJ.DATE_FROM;
SELECT 1
FROM PER_GRADES PG
WHERE PG.GRADE_ID = P_grade_id
AND PG.business_group_id + 0 = P_business_group_id
AND P_vac_date_from < PG.DATE_FROM;
SELECT 1
FROM HR_LOCATIONS HL
WHERE HL.LOCATION_ID = P_location_id
AND P_vac_date_from > nvl(HL.INACTIVE_DATE,P_end_of_time);
SELECT 1
FROM PER_ALL_PEOPLE_F P
WHERE P.PERSON_ID = P_recruiter_id
-- AND P.business_group_id + 0 = P_business_group_id
AND ( P.business_group_id = P_business_group_id or
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
AND P_vac_date_from BETWEEN p.effective_start_date
AND p.effective_end_date;
Select people_group_structure
From per_business_groups
Where business_group_id + 0 = P_Business_Group_id;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Vacancy_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Position_Id NUMBER,
X_Job_Id NUMBER,
X_Grade_Id NUMBER,
X_Organization_Id NUMBER,
X_Requisition_Id NUMBER,
X_People_Group_Id NUMBER,
X_People_Group_Name VARCHAR2,
X_Location_Id NUMBER,
X_Recruiter_Id NUMBER,
X_Date_From DATE,
X_Name VARCHAR2,
X_Comments VARCHAR2,
X_Date_To DATE,
X_Description VARCHAR2,
X_Vacancy_category varchar2,
X_Number_Of_Openings NUMBER,
X_Status VARCHAR2,
X_Budget_Measurement_Type VARCHAR2,
X_Budget_Measurement_Value NUMBER,
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 )
IS
CURSOR C IS SELECT rowid
FROM PER_VACANCIES
WHERE vacancy_id = X_Vacancy_Id;
CURSOR C2 IS SELECT per_vacancies_s.nextval
FROM sys.dual;
INSERT INTO PER_VACANCIES(
vacancy_id,
business_group_id,
position_id,
job_id,
grade_id,
organization_id,
requisition_id,
people_group_id,
location_id,
recruiter_id,
date_from,
name,
comments,
date_to,
description,
vacancy_category,
number_of_openings,
status,
budget_measurement_type,
budget_measurement_value,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20
) VALUES (
X_Vacancy_Id,
X_Business_Group_Id,
X_Position_Id,
X_Job_Id,
X_Grade_Id,
X_Organization_Id,
X_Requisition_Id,
X_People_Group_Id,
X_Location_Id,
X_Recruiter_Id,
X_Date_From,
X_Name,
X_Comments,
X_Date_To,
X_Description,
X_vacancy_category,
X_Number_Of_Openings,
X_Status,
X_Budget_Measurement_Type,
X_Budget_Measurement_Value,
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 );
'per_vacancies_pkg.insert_row');
per_applicant_pkg.update_group ( x_people_group_id,
x_people_group_name ) ;
END Insert_Row;
SELECT *
FROM PER_VACANCIES
WHERE rowid = X_Rowid
FOR UPDATE of Vacancy_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Vacancy_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Position_Id NUMBER,
X_Job_Id NUMBER,
X_Grade_Id NUMBER,
X_Organization_Id NUMBER,
X_Requisition_Id NUMBER,
X_People_Group_Id NUMBER,
X_People_Group_Name VARCHAR2,
X_Location_Id NUMBER,
X_Recruiter_Id NUMBER,
X_Date_From DATE,
X_Name VARCHAR2,
X_Comments VARCHAR2,
X_Date_To DATE,
X_Description VARCHAR2,
X_Vacancy_category varchar2,
X_Number_Of_Openings NUMBER,
X_Status VARCHAR2,
X_Budget_Measurement_Type VARCHAR2,
X_Budget_Measurement_Value NUMBER,
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
) IS
BEGIN
--
IF X_Organization_Id is not null then
PER_VACANCIES_PKG.D_to_updt_org_chk(P_Business_group_id => X_Business_group_id
,P_vac_date_to => X_Date_To
,P_organization_id => X_Organization_Id);
UPDATE PER_VACANCIES
SET
vacancy_id = X_Vacancy_Id,
business_group_id = X_Business_Group_Id,
position_id = X_Position_Id,
job_id = X_Job_Id,
grade_id = X_Grade_Id,
organization_id = X_Organization_Id,
requisition_id = X_Requisition_Id,
people_group_id = X_People_Group_Id,
location_id = X_Location_Id,
recruiter_id = X_Recruiter_Id,
date_from = X_Date_From,
name = X_Name,
comments = X_Comments,
date_to = X_Date_To,
description = X_Description,
vacancy_category = X_Vacancy_category,
number_of_openings = X_Number_Of_Openings,
status = X_Status,
budget_measurement_type = X_Budget_Measurement_Type,
budget_measurement_value = X_Budget_Measurement_Value,
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;
'per_vacancies_pkb.update_row');
update per_all_assignments_f asg
set asg.organization_id = nvl(x_organization_id, asg.organization_id)
,asg.job_id = nvl(x_job_id, asg.job_id)
,asg.grade_id = nvl(x_grade_id, asg.grade_id)
,asg.people_group_id = nvl(x_people_group_id, asg.people_group_id)
,asg.location_id = nvl(x_location_id, asg.location_id)
,asg.recruiter_id = nvl(x_recruiter_id, asg.recruiter_id)
,asg.position_id = decode
(x_organization_id||'.'||x_job_id,
g_recinfo.organization_id||'.'||g_recinfo.job_id,
nvl(x_position_id, asg.position_id),
x_position_id)
where asg.assignment_type = 'A'
and asg.vacancy_id = x_vacancy_id
and ( asg.organization_id <> nvl(x_organization_id,
asg.organization_id)
or nvl(asg.job_id, -1) <> nvl(x_job_id,
nvl(asg.job_id, -1))
or nvl(asg.grade_id, -1) <> nvl(x_grade_id,
nvl(asg.grade_id, -1))
or nvl(asg.people_group_id, -1) <> nvl(x_people_group_id,
nvl(asg.people_group_id, -1))
or nvl(asg.position_id, -1) <> nvl(x_position_id,
nvl(asg.position_id, -1))
or nvl(asg.location_id, -1) <> nvl(x_location_id,
nvl(asg.location_id, -1))
or nvl(asg.recruiter_id, -1) <> nvl(x_recruiter_id,
nvl(asg.recruiter_id, -1))
);
per_applicant_pkg.update_group ( x_people_group_id,
x_people_group_name ) ;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2 , x_vacancy_id in number ) IS
BEGIN
check_references( x_vacancy_id ) ;
DELETE FROM PER_VACANCIES
WHERE rowid = X_Rowid;
END Delete_Row;