[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'compatible'
FROM hxt_rotation_plans rot
WHERE p_effective_start_date between rot.date_from
and nvl(rot.date_to, p_effective_start_date)
AND NOT EXISTS (SELECT '1'
FROM hxt_rotation_schedules hrs
,hxt_weekly_work_schedules wws
WHERE hrs.rtp_id = rot.id
AND wws.id = hrs.tws_id
AND wws.business_group_id <> nvl(asg_bus_grp_id, -99))
AND rot.id = p_rotation_plan;
SELECT 'compatible'
FROM hxt_earning_policies erp
WHERE p_effective_start_date between erp.effective_start_date
and erp.effective_end_date
AND erp.business_group_id = nvl(asg_bus_grp_id, -99)
AND EXISTS
(select 1
from hxt_pay_element_types_f_ddf_v v
, pay_element_types_f t
, hxt_earning_rules r
where r.egp_id = erp.id
and p_effective_start_date between r.effective_start_date
and r.effective_end_date
and t.element_type_id = r.element_type_id
and p_effective_start_date between t.effective_start_date
and t.effective_end_date
and asg_bus_grp_id
= nvl(t.business_group_id,asg_bus_grp_id)
and v.element_type_id = t.element_type_id
and p_effective_start_date between v.effective_start_date
and v.effective_end_date
and v.hxt_earning_category in ('ABS', 'OVT', 'REG'))
AND NOT EXISTS
(select 1
from hxt_earning_rules er
where er.egp_id = erp.id
and p_effective_start_date between er.effective_start_date
and er.effective_end_date
and er.element_type_id NOT IN
(select ern.element_type_id
from pay_element_links_f ell
, per_assignments_f asm
, hxt_earning_rules ern
where ern.egp_id = erp.id
and p_effective_start_date between ern.effective_start_date
and ern.effective_end_date
and asm.assignment_id = p_assignment_id
and p_effective_start_date between asm.effective_start_date
and asm.effective_end_date
and ell.element_type_id = ern.element_type_id
and p_effective_start_date between ell.effective_start_date
and ell.effective_end_date
and nvl(ell.organization_id, nvl(asm.organization_id,-1))
= nvl(asm.organization_id,-1)
and (ell.people_group_id is null
or exists
(select 1
from pay_assignment_link_usages_f usage
where usage.assignment_id = asm.assignment_id
and usage.element_link_id = ell.element_link_id
and p_effective_start_date
between usage.effective_start_date
and usage.effective_end_date))
and nvl(ell.job_id,nvl(asm.job_id,-1))
= nvl(asm.job_id,-1)
and nvl(ell.position_id,nvl(asm.position_id,-1))
= nvl(asm.position_id,-1)
and nvl(ell.grade_id,nvl(asm.grade_id,-1))
= nvl(asm.grade_id,-1)
and nvl(ell.location_id,nvl(asm.location_id,-1))
= nvl(asm.location_id,-1)
and nvl(ell.payroll_id,nvl(asm.payroll_id,-1))
= nvl(asm.payroll_id,-1)
and nvl(ell.employment_category,nvl(asm.employment_category,-1)) = nvl(asm.employment_category,-1)
and nvl(ell.pay_basis_id,nvl(asm.pay_basis_id,-1)) = nvl(asm.pay_basis_id,-1)
and nvl(ell.business_group_id,nvl(asm.business_group_id,-1)) = nvl(asm.business_group_id,-1)))
AND erp.id = p_earning_policy;
SELECT 'compatible'
FROM hxt_shift_diff_policies sdp
WHERE p_effective_start_date between sdp.date_from
and nvl(sdp.date_to
,p_effective_start_date)
AND EXISTS
(select 1
from hxt_pay_element_types_f_ddf_v v
,pay_element_types_f t
,hxt_shift_diff_rules r
where r.sdp_id = sdp.id
and p_effective_start_date between r.effective_start_date
and r.effective_end_date
and t.element_type_id = r.element_type_id
and p_effective_start_date between t.effective_start_date
and t.effective_end_date
and asg_bus_grp_id = t.business_group_id
and v.element_type_id = t.element_type_id
and p_effective_start_date between v.effective_start_date
and v.effective_end_date
and v.hxt_earning_category = 'SDF')
AND NOT EXISTS
(select 1
from pay_element_types_f pet
, hxt_shift_diff_rules hsdr
where hsdr.sdp_id = sdp.id
and p_effective_start_date between hsdr.effective_start_date
and hsdr.effective_end_date
and pet.element_type_id = hsdr.element_type_id
and p_effective_start_date between pet.effective_start_date
and pet.effective_end_date
and pet.business_group_id <> nvl(asg_bus_grp_id, -99))
AND NOT EXISTS
(select 1
from hxt_shift_diff_rules dr
where dr.sdp_id = sdp.id
and p_effective_start_date between dr.effective_start_date
and dr.effective_end_date
and dr.element_type_id not in
(select sdr.element_type_id
from pay_element_links_f ell
,per_assignments_f asm
,hxt_shift_diff_rules sdr
where sdr.sdp_id = sdp.id
and p_effective_start_date between sdr.effective_start_date
and sdr.effective_end_date
and asm.assignment_id = p_assignment_id
and p_effective_start_date between asm.effective_start_date
and asm.effective_end_date
and ell.element_type_id = sdr.element_type_id
and p_effective_start_date between ell.effective_start_date
and ell.effective_end_date
and nvl(ell.organization_id, nvl(asm.organization_id,-1))
= nvl(asm.organization_id,-1)
and (ell.people_group_id is null
or exists
(select 1
from pay_assignment_link_usages_f usage
where usage.assignment_id = asm.assignment_id
and usage.element_link_id = ell.element_link_id
and p_effective_start_date
between usage.effective_start_date
and usage.effective_end_date))
and nvl(ell.job_id,nvl(asm.job_id,-1))
= nvl(asm.job_id,-1)
and nvl(ell.position_id,nvl(asm.position_id,-1))
= nvl(asm.position_id,-1)
and nvl(ell.grade_id,nvl(asm.grade_id,-1))
= nvl(asm.grade_id,-1)
and nvl(ell.location_id,nvl(asm.location_id,-1))
= nvl(asm.location_id,-1)
and nvl(ell.payroll_id,nvl(asm.payroll_id,-1))
= nvl(asm.payroll_id,-1)
and nvl(ell.employment_category,nvl(asm.employment_category,-1))
= nvl(asm.employment_category,-1)
and nvl(ell.pay_basis_id,nvl(asm.pay_basis_id,-1))
= nvl(asm.pay_basis_id,-1)
and nvl(ell.business_group_id,nvl(asm.business_group_id,-1))
= nvl(asm.business_group_id,-1)))
and sdp.id = p_shift_differential_policy;
SELECT 'compatible'
FROM hxt_hour_deduct_policies hdp
WHERE p_effective_start_date between hdp.date_from
and nvl(hdp.date_to,p_effective_start_date)
AND nvl(hdp.business_group_id, nvl(asg_bus_grp_id, -99))
= nvl(asg_bus_grp_id, -98)
AND hdp.id = p_hour_deduction_policy;
SELECT business_group_id
FROM per_assignments_f
WHERE p_effective_start_date between effective_start_date
and effective_end_date
And assignment_type = 'E'
And assignment_id = p_assignment_id;
,p_last_updated_by NUMBER
,p_last_update_date DATE
,p_last_update_login NUMBER
,p_attribute_category VARCHAR2 DEFAULT NULL
,p_attribute1 VARCHAR2 DEFAULT NULL
,p_attribute2 VARCHAR2 DEFAULT NULL
,p_attribute3 VARCHAR2 DEFAULT NULL
,p_attribute4 VARCHAR2 DEFAULT NULL
,p_attribute5 VARCHAR2 DEFAULT NULL
,p_attribute6 VARCHAR2 DEFAULT NULL
,p_attribute7 VARCHAR2 DEFAULT NULL
,p_attribute8 VARCHAR2 DEFAULT NULL
,p_attribute9 VARCHAR2 DEFAULT NULL
,p_attribute10 VARCHAR2 DEFAULT NULL
,p_attribute11 VARCHAR2 DEFAULT NULL
,p_attribute12 VARCHAR2 DEFAULT NULL
,p_attribute13 VARCHAR2 DEFAULT NULL
,p_attribute14 VARCHAR2 DEFAULT NULL
,p_attribute15 VARCHAR2 DEFAULT NULL
,p_attribute16 VARCHAR2 DEFAULT NULL
,p_attribute17 VARCHAR2 DEFAULT NULL
,p_attribute18 VARCHAR2 DEFAULT NULL
,p_attribute19 VARCHAR2 DEFAULT NULL
,p_attribute20 VARCHAR2 DEFAULT NULL
,p_attribute21 VARCHAR2 DEFAULT NULL
,p_attribute22 VARCHAR2 DEFAULT NULL
,p_attribute23 VARCHAR2 DEFAULT NULL
,p_attribute24 VARCHAR2 DEFAULT NULL
,p_attribute25 VARCHAR2 DEFAULT NULL
,p_attribute26 VARCHAR2 DEFAULT NULL
,p_attribute27 VARCHAR2 DEFAULT NULL
,p_attribute28 VARCHAR2 DEFAULT NULL
,p_attribute29 VARCHAR2 DEFAULT NULL
,p_attribute30 VARCHAR2 DEFAULT NULL
) IS
--The purpose of this procedure is to INSERT a row for the additional
--assignment information
--Arguments
-- Arguments
-- p_id -ID of the record.
-- p_effective_start_date -effective_start_date for the record to be
-- -inserted.
-- p_assignment_id -id of the assignment for which the information
-- -has to be added.
-- p_autogen_hours_yn -The value for this argument can be either
-- -'Y' or 'N',to specify whether to autogen
-- -the hours for this assignment
--p_rotation_plan -rotation_plan_id for this assignment
--p_earning_policy -earning_policy_id for this assignment
--p_shift_differential_policy -shift_differential_policy_id for this
-- -assignment
--p_hour_deduction_policy -hour_deduction_policy_id for this assignment
--p_attribute1 .. p_attribute30 -attribute values
asg_bus_grp_id NUMBER(15);
SELECT '1'
FROM hxt_add_assign_info_f aai
WHERE aai.assignment_id = p_assignment_id;
SELECT hxt_seqno.nextval
FROM sys.dual;
INSERT into HXT_ADD_ASSIGN_INFO_F
(id
,effective_start_date
,effective_end_date
,assignment_id
,autogen_hours_yn
,rotation_plan
,earning_policy
,shift_differential_policy
,hour_deduction_policy
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,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)
VALUES( p_id
,p_effective_start_date
,hr_general.end_of_time --p_effective_end_date
,p_assignment_id
,p_autogen_hours_yn
,p_rotation_plan
,p_earning_policy
,p_shift_differential_policy
,p_hour_deduction_policy
,p_created_by
,p_creation_date
,p_last_updated_by
,p_last_update_date
,p_last_update_login
,p_attribute_category
,p_attribute1
,p_attribute2
,p_attribute3
,p_attribute4
,p_attribute5
,p_attribute6
,p_attribute7
,p_attribute8
,p_attribute9
,p_attribute10
,p_attribute11
,p_attribute12
,p_attribute13
,p_attribute14
,p_attribute15
,p_attribute16
,p_attribute17
,p_attribute18
,p_attribute19
,p_attribute20
,p_attribute21
,p_attribute22
,p_attribute23
,p_attribute24
,p_attribute25
,p_attribute26
,p_attribute27
,p_attribute28
,p_attribute29
,p_attribute30);
PROCEDURE Update_Otlr_Add_Assign_Info (
p_id NUMBER
,p_datetrack_mode VARCHAR2
,p_effective_date DATE
,p_effective_start_date DATE
-- ,p_effective_end_date DATE
,p_assignment_id NUMBER
,p_autogen_hours_yn VARCHAR2
,p_rotation_plan NUMBER DEFAULT NULL
,p_earning_policy NUMBER
,p_shift_differential_policy NUMBER DEFAULT NULL
,p_hour_deduction_policy NUMBER DEFAULT NULL
,p_created_by NUMBER
,p_creation_date DATE
,p_last_updated_by NUMBER
,p_last_update_date DATE
,p_last_update_login NUMBER
,p_attribute_category VARCHAR2 DEFAULT NULL
,p_attribute1 VARCHAR2 DEFAULT NULL
,p_attribute2 VARCHAR2 DEFAULT NULL
,p_attribute3 VARCHAR2 DEFAULT NULL
,p_attribute4 VARCHAR2 DEFAULT NULL
,p_attribute5 VARCHAR2 DEFAULT NULL
,p_attribute6 VARCHAR2 DEFAULT NULL
,p_attribute7 VARCHAR2 DEFAULT NULL
,p_attribute8 VARCHAR2 DEFAULT NULL
,p_attribute9 VARCHAR2 DEFAULT NULL
,p_attribute10 VARCHAR2 DEFAULT NULL
,p_attribute11 VARCHAR2 DEFAULT NULL
,p_attribute12 VARCHAR2 DEFAULT NULL
,p_attribute13 VARCHAR2 DEFAULT NULL
,p_attribute14 VARCHAR2 DEFAULT NULL
,p_attribute15 VARCHAR2 DEFAULT NULL
,p_attribute16 VARCHAR2 DEFAULT NULL
,p_attribute17 VARCHAR2 DEFAULT NULL
,p_attribute18 VARCHAR2 DEFAULT NULL
,p_attribute19 VARCHAR2 DEFAULT NULL
,p_attribute20 VARCHAR2 DEFAULT NULL
,p_attribute21 VARCHAR2 DEFAULT NULL
,p_attribute22 VARCHAR2 DEFAULT NULL
,p_attribute23 VARCHAR2 DEFAULT NULL
,p_attribute24 VARCHAR2 DEFAULT NULL
,p_attribute25 VARCHAR2 DEFAULT NULL
,p_attribute26 VARCHAR2 DEFAULT NULL
,p_attribute27 VARCHAR2 DEFAULT NULL
,p_attribute28 VARCHAR2 DEFAULT NULL
,p_attribute29 VARCHAR2 DEFAULT NULL
,p_attribute30 VARCHAR2 DEFAULT NULL
) IS
-- The purpose of this procedure is to perform 'CORRECTION' and
-- 'UPDATE_OVERRIDE' for the additional assignment information.This procedure
-- when run in an 'UPDATE_OVERRIDE' mode would override the future changes
-- Arguments
-- p_id - ID of the record.
-- p_datetrack_mode -The mode in which the api has to be run.
-- -It can be run in 'CORRECTION' or
-- -'UPDATE_OVERRIDE' mode.
-- p_effective_date -The record will be update das of this date.
-- p_effective_start_date -effective_start_date of the record for this
-- -assignment that has effective_end_date as
-- -end_of_time.
-- p_assignment_id -assignment_id for which the update has to be
-- -done.
-- p_autogen_hours_yn -The value for this argument can be either
-- -'Y' or 'N',to specify whether to autogen
-- -the hours for this assignment.
--p_rotation_plan -rotation_plan_id for this assignment.
--p_earning_policy -earning_policy_id for this assignment.
--p_shift_differential_policy -shift_differential_policy_id for this
-- -assignment.
--p_hour_deduction_policy -hour_deduction_policy_id for this assignment.
--p_attribute1 .. p_attribute30 -attribute values.
CURSOR c2 is
SELECT rowid,effective_start_date,effective_end_date
FROM hxt_add_assign_info_f
WHERE id = p_id
AND effective_start_date = p_effective_start_date;
CURSOR c_delete_rec_eot is
SELECT rowid
FROM hxt_add_assign_info_f
WHERE id = p_id
AND effective_end_date = hr_general.end_of_time;
IS SELECT ROWIDTOCHAR(rowid),
id
FROM hxt_add_assign_info_f
WHERE id = p_id
AND effective_start_date > p_effective_date;
SELECT hxt_seqno.nextval
FROM sys.dual;
hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',10);
hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',20);
hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',30);
||' for the given record. Updates would be '
||' done based on p_effective_date ');
l_datetrack_mode := 'UPDATE_OVERRIDE';
OPEN c_delete_rec_eot;
FETCH c_delete_rec_eot into p_rowid_eot;
CLOSE c_delete_rec_eot;
hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',40);
UPDATE HXT_ADD_ASSIGN_INFO_F
SET--effective_start_date = p_effective_start_date
--,effective_end_date = p_effective_end_date
--,assignment_id = p_assignment_id
autogen_hours_yn = p_autogen_hours_yn
,rotation_plan = p_rotation_plan
,earning_policy = p_earning_policy
,shift_differential_policy = p_shift_differential_policy
,hour_deduction_policy = p_hour_deduction_policy
,created_by = p_created_by
,creation_date = p_creation_date
,last_updated_by = p_last_updated_by
,last_update_date = p_last_update_date
,last_update_login = p_last_update_login
,attribute_category = p_attribute_category
,attribute1 = p_attribute1
,attribute2 = p_attribute2
,attribute3 = p_attribute3
,attribute4 = p_attribute4
,attribute5 = p_attribute5
,attribute6 = p_attribute6
,attribute7 = p_attribute7
,attribute8 = p_attribute8
,attribute9 = p_attribute9
,attribute10 = p_attribute10
,attribute11 = p_attribute11
,attribute12 = p_attribute12
,attribute13 = p_attribute13
,attribute14 = p_attribute14
,attribute15 = p_attribute15
,attribute16 = p_attribute16
,attribute17 = p_attribute17
,attribute18 = p_attribute18
,attribute19 = p_attribute19
,attribute20 = p_attribute20
,attribute21 = p_attribute21
,attribute22 = p_attribute22
,attribute23 = p_attribute23
,attribute24 = p_attribute24
,attribute25 = p_attribute25
,attribute26 = p_attribute26
,attribute27 = p_attribute27
,attribute28 = p_attribute28
,attribute29 = p_attribute29
,attribute30 = p_attribute30
WHERE rowid = p_rowid;
ELSIF l_datetrack_mode = 'UPDATE_OVERRIDE' THEN
/* end date the existing record and then create a new record if an
existing record found */
if g_debug then
hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',50);
/*UPDATE hxt_add_assign_info_f
SET effective_end_date = p_effective_date - 1
WHERE rowid = p_rowid;
SELECT count(*) into l_count
FROM hxt_add_assign_info_f
WHERE assignment_id = p_assignment_id;
hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',60);
DELETE from hxt_add_assign_info_f
WHERE rowid = p_rowid_eot;
DELETE FROM hxt_add_assign_info_f
WHERE rowid = chartorowid(rowidtab(i));
UPDATE hxt_add_assign_info_f aai
SET aai.effective_end_date = p_effective_date - 1
WHERE aai.assignment_id = p_assignment_id
AND p_effective_date between aai.effective_start_date
and aai.effective_end_date;
UPDATE hxt_add_assign_info_f
SET effective_end_date = p_effective_date - 1
WHERE rowid = p_rowid;
hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',70);
INSERT into HXT_ADD_ASSIGN_INFO_F
(id
,effective_start_date
,effective_end_date
,assignment_id
,autogen_hours_yn
,rotation_plan
,earning_policy
,shift_differential_policy
,hour_deduction_policy
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,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)
VALUES
(p_id
,p_effective_date
,hr_general.end_of_time
,p_assignment_id
,p_autogen_hours_yn
,p_rotation_plan
,p_earning_policy
,p_shift_differential_policy
,p_hour_deduction_policy
,p_created_by
,p_creation_date
,p_last_updated_by
,p_last_update_date
,p_last_update_login
,p_attribute_category
,p_attribute1
,p_attribute2
,p_attribute3
,p_attribute4
,p_attribute5
,p_attribute6
,p_attribute7
,p_attribute8
,p_attribute9
,p_attribute10
,p_attribute11
,p_attribute12
,p_attribute13
,p_attribute14
,p_attribute15
,p_attribute16
,p_attribute17
,p_attribute18
,p_attribute19
,p_attribute20
,p_attribute21
,p_attribute22
,p_attribute23
,p_attribute24
,p_attribute25
,p_attribute26
,p_attribute27
,p_attribute28
,p_attribute29
,p_attribute30);
hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',80);
hr_utility.set_location('HXT_GEN_AAI.Update_Otlr_Add_Assign_Info ',90);
END Update_Otlr_Add_Assign_Info ;