[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.
04-Nov-08 sidsaxen 115.17 Added procedure end_date_irec_RA, end_date_PER_RA
and stubbed procedure D_from_updt_rec_act_chk
for bug 6497289
24-Feb-09 lbodired 115.19 Modified the procedure UPDATE_ROW
for the bug 7592739
01-Jun-09 sidsaxen 115.23 bug 8518955, handled NULL while updating
per_all_assignments_f in per_vacancies_pkg.update_row
08-APR-10 karthmoh 120.3.12010000.9 Modified/Added Procedures for ER#8530112
============================================================================*/
----------------------------------------------------------------------------
--
-- 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 NVL(P_vac_date_to,to_date('31-12-4712','DD-MM-YYYY')) > 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
l_end_of_time date := hr_api.g_eot;
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 = x_job_id
,asg.grade_id = x_grade_id
,asg.people_group_id = x_people_group_id
,asg.location_id = x_location_id
,asg.recruiter_id = x_recruiter_id
,asg.position_id = x_position_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 exists ( select 1
from per_all_assignments_f f2
where asg.assignment_id = f2.assignment_id
and f2.effective_end_date = l_end_of_time )
and not exists ( select 1
from per_all_assignments_f f2
where asg.assignment_id = f2.assignment_id
and f2.assignment_status_type_id in ( select assignment_status_type_id
from per_assignment_status_types
where per_system_status in ('ACCEPTED')))
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, -1)
or nvl(asg.grade_id, -1) <> nvl(x_grade_id,-1)
or nvl(asg.people_group_id, -1) <> nvl(x_people_group_id,-1)
or nvl(asg.position_id, -1) <> nvl(x_position_id,-1)
or nvl(asg.location_id, -1) <> nvl(x_location_id, -1)
or nvl(asg.recruiter_id, -1) <> nvl(x_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;
select pra.recruitment_activity_id, pra.object_version_number
from per_recruitment_activities pra, per_recruitment_activity_for praf
where pra.recruitment_activity_id = praf.recruitment_activity_id
and praf.vacancy_id = P_vacancy_id
and pra.posting_content_id is not NULL
and pra.recruiting_site_id is not NULL
AND nvl(pra.date_end,to_date('31/12/4712','dd/mm/yyyy')) > p_vac_date_to;
per_recruitment_activity_swi.update_recruitment_activity
(p_recruitment_activity_id => Irec_site_RA.recruitment_activity_id
,p_date_end => P_vac_date_to
,p_object_version_number => l_object_version_number
,p_return_status => l_return_status
);
SELECT pra.recruitment_activity_id,pra.object_version_number
FROM per_recruitment_activities pra, per_recruitment_activity_for praf
WHERE pra.recruitment_activity_id = praf.recruitment_activity_id
AND praf.vacancy_id = P_vacancy_id
AND pra.recruitment_activity_id = P_recruitment_activity_id
AND pra.posting_content_id is null
AND pra.recruiting_site_id is null;
per_recruitment_activity_swi.update_recruitment_activity
(p_recruitment_activity_id => l_recruitment_activity_id
,p_date_end => P_vac_date_to
,p_object_version_number => l_object_version_number
,p_return_status => l_return_status
);
select business_group_id
from hr_all_positions_f
where position_id = p_position_id;
select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
from pqh_budgets
where position_control_flag = 'Y'
and budgeted_entity_cd = 'POSITION'
and business_group_id = l_business_group_id
and p_effective_date between budget_start_date and budget_end_date
and (
hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'HEAD'
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'HEAD'
or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'HEAD'
);
select system_type_cd from
per_shared_types where shared_type_id = p_unit_id;
select bdt.budget_detail_id
from pqh_budget_details bdt,pqh_budget_versions bvr
where bvr.budget_id = p_budget_id
and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date)
and bdt.budget_version_id = bvr.budget_version_id
and bdt.position_id = p_position_id;
select bpr.budget_unit1_value, bpr.budget_unit2_value, bpr.budget_unit3_value
from pqh_budget_periods bpr, per_time_periods tp_s,
per_time_periods tp_e
where bpr.budget_detail_id = p_budget_detail_id
and tp_s.time_period_id = bpr.start_time_period_id
and tp_e.time_period_id = bpr.end_time_period_id
and tp_s.period_set_name = l_calendar
and tp_e.period_set_name = l_calendar
and p_effective_date between tp_s.start_date and tp_e.end_date;
select sum(nvl(value,1))
from per_assignment_budget_values_f abv, per_all_assignments_f asn,
per_assignment_status_types ast
where abv.assignment_id(+) = asn.assignment_id
and p_effective_date between asn.effective_start_date and asn.effective_end_date
and p_effective_date between abv.effective_start_date and abv.effective_end_date
and asn.position_id = p_position_id
and asn.assignment_type in ('E', 'C')
and abv.unit(+) = 'HEAD'
and asn.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN';
Select sum(number_of_openings) from
Per_vacancies
WHERE position_id = p_position_id
and vacancy_id <> p_vacancy_id
AND p_effective_date BETWEEN date_from AND
nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'))
AND status in ('FILL','APPROVED','CLOSED')
GROUP BY position_id;