[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
into l_exists
from sys.dual
where exists (select null
from per_pos_structure_elements pse
where pse.pos_structure_VERSION_id = X_Pos_Structure_Version_Id
and ((pse.subordinate_position_id = X_Position_Id
or pse.parent_position_id = X_Position_Id)
or X_Position_Id is null)
);
SELECT '1'
FROM PER_ALL_POSITIONS PST
WHERE (PST.ROWID <> p_Rowid
OR p_Rowid IS NULL)
AND PST.POSITION_ID = p_Position_Id;
hr_utility.set_message(801,'HR_6012_ROW_INSERTED');
SELECT '1'
FROM PER_ALL_POSITIONS PST
WHERE (PST.ROWID <> p_Rowid
OR p_Rowid IS NULL)
AND PST.POSITION_DEFINITION_ID = p_Position_Definition_Id
AND PST.business_group_id + 0 = p_Business_Group_Id;
PROCEDURE pre_delete_checks(p_rowid varchar2,
p_position_id number,
p_business_group_id number,
p_hr_ins varchar2,
p_po_ins varchar2,
p_delete_row out nocopy boolean
) is
l_exists varchar2(1);
p_delete_row := false;
select '1'
into l_exists
from sys.dual
where exists (select null
from per_ALL_assignments_f a
where a.position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists (SELECT NULL
FROM PAY_ELEMENT_LINKS_F EL
WHERE EL.POSITION_ID = p_position_id);
hr_utility.set_message_token('TRIGGER','on-delete');
select '1'
into l_exists
from sys.dual
where exists(SELECT NULL
from PER_BUDGET_ELEMENTS BE
where BE.POSITION_ID = p_position_id);
hr_utility.set_message_token('TRIGGER','on-delete');
select '1'
into l_exists
from sys.dual
where exists(SELECT NULL
from PER_VACANCIES VAC
where VAC.POSITION_ID = p_position_id);
select e.pos_structure_element_id
into l_pos_structure_element_id
from per_pos_structure_elements e
where e.parent_position_id = p_position_id
and not exists (
select null
from per_pos_structure_elements e2
where e2.subordinate_position_id = p_position_id)
and 1 = (
select count(e3.pos_structure_element_id)
from per_pos_structure_elements e3
where e3.parent_position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(SELECT NULL
FROM PER_POS_STRUCTURE_ELEMENTS PSE
WHERE PSE.PARENT_POSITION_ID = p_position_id
OR PSE.SUBORDINATE_POSITION_ID = p_position_id) ;
select '1'
into l_exists
from sys.dual
where exists(SELECT NULL
FROM PER_VALID_GRADES VG1
WHERE business_group_id + 0 = p_business_group_id
AND VG1.POSITION_ID = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from per_job_requirements jre1
where jre1.position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from per_job_evaluations jev1
where jev1.position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from per_positions
where successor_position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from per_positions
where relief_position_id = p_position_id);
select '1'
into l_exists
from sys.dual
where exists(select null
from per_mm_positions
where new_position_id = p_position_id);
l_sql_text := 'select null '
||'from sys.dual '
||'where exists( select null '
||' from po_system_parameters '
||' where security_position_structure_id = '
||to_char(p_position_id)
||' ) '
||'or exists( select null '
||' from po_employee_hierarchies '
||' where employee_position_id = '
||to_char(p_position_id)
||'or superior_position_id = '
||to_char(p_position_id)
||' ) ';
p_delete_row := true;
end pre_delete_checks;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Position_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Job_Id NUMBER,
X_Organization_Id NUMBER,
X_Successor_Position_Id NUMBER,
X_Relief_Position_Id NUMBER,
X_Location_Id NUMBER,
X_Position_Definition_Id NUMBER,
X_Date_Effective DATE,
X_Comments VARCHAR2,
X_Date_End DATE,
X_Frequency VARCHAR2,
X_Name VARCHAR2,
X_Probation_Period NUMBER,
X_Probation_Period_Units VARCHAR2,
X_Replacement_Required_Flag VARCHAR2,
X_Time_Normal_Finish VARCHAR2,
X_Time_Normal_Start VARCHAR2,
X_Working_Hours NUMBER,
X_Status 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_View_All_Psts VARCHAR2,
X_Security_Profile_id NUMBER
) IS
CURSOR C IS SELECT rowid FROM PER_ALL_POSITIONS
WHERE position_id = X_Position_Id;
CURSOR C2 IS SELECT per_positions_s.nextval FROM sys.dual;
INSERT INTO PER_POSITIONS(
position_id,
business_group_id,
job_id,
organization_id,
successor_position_id,
relief_position_id,
location_id,
position_definition_id,
date_effective,
comments,
date_end,
frequency,
name,
probation_period,
probation_period_units,
replacement_required_flag,
time_normal_finish,
time_normal_start,
working_hours,
status,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20
) VALUES (
X_Position_Id,
X_Business_Group_Id,
X_Job_Id,
X_Organization_Id,
X_Successor_Position_Id,
X_Relief_Position_Id,
X_Location_Id,
X_Position_Definition_Id,
X_Date_Effective,
X_Comments,
X_Date_End,
X_Frequency,
X_Name,
X_Probation_Period,
X_Probation_Period_Units,
X_Replacement_Required_Flag,
X_Time_Normal_Finish,
X_Time_Normal_Start,
X_Working_Hours,
X_Status,
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
);
hr_utility.set_message_token('PROCEDURE','Insert_row');
END Insert_Row;
SELECT Position_id
,Business_Group_Id
,Job_Id
,Organization_Id
,Successor_Position_Id
,Relief_Position_Id
,Location_Id
,Position_Definition_Id
,Date_Effective
-- ,Comments
,Date_End
,Frequency
,Name
,Probation_Period
,Probation_Period_Units
,Replacement_Required_Flag
,Time_Normal_Finish
,Time_Normal_Start
,Working_Hours
,Attribute_Category
,Attribute1
,Attribute2
,Attribute3
,Attribute4
,Attribute5
,Attribute6
,Attribute7
,Attribute8
,Attribute9
,Attribute10
,Attribute11
,Attribute12
,Attribute13
,Attribute14
,Attribute15
,Attribute16
,Attribute17
,Attribute18
,Attribute19
,Attribute20
,Status
FROM PER_POSITIONS
WHERE rowid = chartorowid(X_Rowid)
FOR UPDATE of Position_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Position_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Job_Id NUMBER,
X_Organization_Id NUMBER,
X_Successor_Position_Id NUMBER,
X_Relief_Position_Id NUMBER,
X_Location_Id NUMBER,
X_Position_Definition_Id NUMBER,
X_Date_Effective DATE,
X_Comments VARCHAR2,
X_Date_End DATE,
X_Frequency VARCHAR2,
X_Name VARCHAR2,
X_Probation_Period NUMBER,
X_Probation_Period_Units VARCHAR2,
X_Replacement_Required_Flag VARCHAR2,
X_Time_Normal_Finish VARCHAR2,
X_Time_Normal_Start VARCHAR2,
X_Working_Hours NUMBER,
X_Status 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
) IS
BEGIN
null;
UPDATE PER_POSITIONS
SET
position_id = X_Position_Id,
business_group_id = X_Business_Group_Id,
job_id = X_Job_Id,
organization_id = X_Organization_Id,
successor_position_id = X_Successor_Position_Id,
relief_position_id = X_Relief_Position_Id,
location_id = X_Location_Id,
position_definition_id = X_Position_Definition_Id,
date_effective = X_Date_Effective,
comments = X_Comments,
date_end = X_Date_End,
frequency = X_Frequency,
name = X_Name,
probation_period = X_Probation_Period,
probation_period_units = X_Probation_Period_Units,
replacement_required_flag = X_Replacement_Required_Flag,
time_normal_finish = X_Time_Normal_Finish,
time_normal_start = X_Time_Normal_Start,
working_hours = X_Working_Hours,
status = X_Status,
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_Position_id number,
X_business_group_id number,
X_Hr_Ins varchar2,
X_Po_Ins varchar2,
X_View_All_Psts varchar2) IS
l_delete_row boolean;
pre_delete_checks(X_Rowid,
X_Position_id,
X_business_group_id,
X_Hr_Ins ,
X_Po_Ins,
l_delete_row );
hr_security.delete_pos_from_list(X_Position_Id);
DELETE FROM PER_POSITIONS
WHERE rowid = X_Rowid;
hr_utility.set_message_token('PROCEDURE','Delete_Row');
END Delete_Row;
SELECT
'Y'
FROM
FND_COMPILED_ID_FLEX_STRUCTS FCF,
FND_ID_FLEX_STRUCTURES FS
WHERE
FCF.ID_FLEX_CODE = p_id_flex_code AND
FCF.ID_FLEX_NUM = p_id_flex_num
AND
FS.ID_FLEX_CODE = FCF.ID_FLEX_CODE AND
FS.ID_FLEX_NUM = FCF.ID_FLEX_NUM AND
FS.DYNAMIC_INSERTS_ALLOWED_FLAG = 'Y';
SELECT 'Y'
FROM per_valid_grades
WHERE position_id = p_position_id
AND date_from < p_date_effective;
cursor csr_date_to is select null
from per_valid_grades
where position_id = p_position_id
and nvl(date_to, p_end_of_time) > p_date_end;
cursor csr_date_from is select null
from per_valid_grades vg
where vg.position_id = p_position_id
and vg.date_from > p_date_end;
cursor csr_end_date_blank is select null
from per_valid_grades vg , per_positions p
where vg.position_id = p_position_id
and p.position_id = p_position_id
and nvl(vg.date_to, p_end_of_time) =
nvl(p.date_end, p_end_of_time)
and p_date_end is null;
cursor csr_after_date_to is select null
from per_valid_grades vg , per_positions p
where vg.position_id = p_position_id
and p.position_id = p_position_id
and nvl(vg.date_to,p_end_of_time) <
nvl(p_date_end,p_end_of_time)
and nvl(vg.date_to,p_end_of_time) =
nvl(p.date_end,p_end_of_time);
update per_valid_grades vg
set vg.date_to =
(select least(nvl(p_date_end, p_end_of_time),
nvl(g.date_to, p_end_of_time))
from per_grades g
where g.grade_id = vg.grade_id)
where
vg.position_id = p_position_id
and nvl(vg.date_to, p_end_of_time) > p_date_end;
delete from per_valid_grades
where position_id = p_position_id
and date_from > p_date_end;
update per_valid_grades vg
set vg.date_to =
(select decode(least(nvl(p_date_end, p_end_of_time),
nvl(g.date_to,p_end_of_time)),
p_date_end,
p_date_end,
g.date_to,
g.date_to,
p_end_of_time,
null,
p_date_end)
from per_grades g
where g.grade_id = vg.grade_id)
where
vg.position_id = p_position_id
and nvl(vg.date_to, p_end_of_time) = (select nvl(p.date_end,p_end_of_time)
from per_positions p
where p.position_id = p_position_id);
select information12
from ben_copy_entity_results
where copy_entity_txn_id = 0
and table_alias = 'NONE'
and INFORMATION11 = 'PERWSDPO'
and information1 = p_user_id;
select information12
from ben_copy_entity_results
where copy_entity_txn_id = 0
and table_alias = 'NONE'
and INFORMATION11 = 'PERWSDPO'
and information1 = p_user_id;
UPDATE ben_copy_entity_results
SET information12 = 'Y',
object_version_number = object_version_number + 1
WHERE copy_entity_txn_id = 0
and table_alias = 'NONE'
and INFORMATION11 = 'PERWSDPO'
and information1 = p_user_id;
INSERT INTO ben_copy_entity_results
(COPY_ENTITY_RESULT_ID, COPY_ENTITY_TXN_ID, RESULT_TYPE_CD,
NUMBER_OF_COPIES, TABLE_ALIAS, STATUS,
DML_OPERATION, DATETRACK_MODE,
INFORMATION1, INFORMATION11, INFORMATION12,
OBJECT_VERSION_NUMBER
)
values
(
ben_copy_entity_results_s.nextval, 0, 'DISPLAY',
0, 'NONE', 'VALID',
'INSERT', 'INSERT',
p_user_id, 'PERWSDPO', p_show_again_flag,
1
);