[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pbg.security_group_id
from per_business_groups pbg
, pqp_assignment_attributes_f aat
where aat.assignment_attribute_id = p_assignment_attribute_id
and pbg.business_group_id = aat.business_group_id;
select pbg.legislation_code
from per_business_groups pbg
, pqp_assignment_attributes_f aat
where aat.assignment_attribute_id = p_assignment_attribute_id
and pbg.business_group_id = aat.business_group_id;
Procedure chk_non_updateable_args
(p_effective_date in date
,p_rec in pqp_aat_shd.g_rec_type
) IS
--
l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
End chk_non_updateable_args;
select 1
from pqp_assignment_attributes_f
where (assignment_id > p_assignment_id OR assignment_id < p_assignment_id) -- for bug 6871534
and (primary_company_car in (p_primary_company_car, p_secondary_company_car) or
secondary_company_car in (p_primary_company_car, p_secondary_company_car))
and p_validation_start_date <= effective_end_date
and p_validation_end_date >= effective_start_date;
select 1
from pqp_assignment_attributes_f
where assignment_id = p_assignment_id
and assignment_attribute_id <> nvl(p_assignment_attribute_id, -1)
and p_validation_start_date <= effective_end_date
and p_validation_end_date >= effective_start_date;
SELECT 'x'
FROM pay_user_tables
WHERE user_table_id = p_rates_table_id
AND business_group_id = p_business_group_id;
Select 'Y'
From dual
Where length(nvl(p_tp_safeguarded_grade,'x')) <= 3
and ascii( substr(p_tp_safeguarded_grade,1,1)) between 65 and 90
and (-- Either both 2nd and 3rd chars are alpha
(ascii( substr(p_tp_safeguarded_grade,2,1)) between 65 and 90
AND
ascii( substr(p_tp_safeguarded_grade,3,1)) between 65 and 90
)
OR -- 2nd and 3rd chars r both numbers
(ascii( substr(p_tp_safeguarded_grade,2,1)) between 48 and 57
AND
ascii( substr(p_tp_safeguarded_grade,3,1)) between 48 and 57
)
);
Select 'Y'
From per_grades pg
Where pg.grade_id = p_tp_safeguarded_grade_id;
Select '1'
From pqp_assignment_attributes_f
Where assignment_id = p_assignment_id
and effective_end_date < p_effective_date
and ( tp_is_teacher = 'TCHR'
or tp_is_teacher = 'TTR6');
Select '1'
From pqp_assignment_attributes_f
Where assignment_id = p_assignment_id
and effective_start_date > p_effective_date
and tp_is_teacher = 'TTR6';
re-used in UPDATE mode checks.
*/
--
-- Check 'TCHR' and 'TTR6' history
--
open C_TCHR_TTR6_History;
if (p_datetrack_mode = hr_api.g_update or
p_datetrack_mode = hr_api.g_update_override or
p_datetrack_mode = hr_api.g_update_change_insert) then
-- 7
if p_old_job_status = 'TCHR' and p_new_job_status = 'TTR6' then
-- 8
pqp_aat_shd.constraint_error
(p_constraint_name => 'PQP_INVALID_JOB_STATUS');
if (p_datetrack_mode = hr_api.g_update or
p_datetrack_mode = hr_api.g_update_change_insert) then
-- 11
/* For old = 'NONT' and (new = 'TTR6' or 'TCHR')
Rules Handled : 3 and 4(partly) Refer design doc for rules table */
if p_old_job_status = 'NONT' and
(p_new_job_status = 'TCHR' or p_new_job_status = 'TTR6') then
-- 12
if l_TTR6_Future then
-- 13
pqp_aat_shd.constraint_error
(p_constraint_name => 'PQP_INVALID_JOB_STATUS');
Select 'Y'
From pay_rates pr
Where pr.rate_type = p_tp_safeguarded_rate_type
and pr.rate_id = p_tp_safeguarded_rate_id;
Select 'Y'
From per_spinal_points psp
Where psp.spinal_point_id = p_tp_spinal_point_id;
Select 'Y'
From pay_rates pr
,per_grade_spines_f pgs
,per_spinal_point_steps_f psps
Where pr.parent_spine_id = pgs.parent_spine_id
and psps.grade_spine_id = pgs.grade_spine_id
and pgs.grade_id = p_tp_safeguarded_grade_id
and pr.rate_id = p_tp_safeguarded_rate_id
and psps.spinal_point_id = p_tp_spinal_point_id
and p_validation_start_date <= psps.effective_end_date
and p_validation_end_date >= psps.effective_start_date;
Procedure dt_update_validate
(p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
) Is
--
l_proc varchar2(72) := g_package||'dt_update_validate';
End dt_update_validate;
Procedure dt_delete_validate
(p_assignment_attribute_id in number
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
) Is
--
l_proc varchar2(72) := g_package||'dt_delete_validate';
If (p_datetrack_mode = hr_api.g_delete or
p_datetrack_mode = hr_api.g_zap) then
--
--
-- Ensure the arguments are not null
--
hr_api.mandatory_arg_error
(p_api_name => l_proc
,p_argument => 'validation_start_date'
,p_argument_value => p_validation_start_date
);
End dt_delete_validate;
Procedure insert_validate
(p_rec in pqp_aat_shd.g_rec_type
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
) is
--
l_proc varchar2(72) := g_package||'insert_validate';
End insert_validate;
Procedure update_validate
(p_rec in pqp_aat_shd.g_rec_type
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
) is
--
l_proc varchar2(72) := g_package||'update_validate';
dt_update_validate
(p_datetrack_mode => p_datetrack_mode
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
);
chk_non_updateable_args
(p_effective_date => p_effective_date
,p_rec => p_rec
);
End update_validate;
Procedure delete_validate
(p_rec in pqp_aat_shd.g_rec_type
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
) is
--
l_proc varchar2(72) := g_package||'delete_validate';
select assignment_id
from pqp_assignment_attributes_f
where assignment_attribute_id = p_rec.assignment_attribute_id
and p_effective_date between effective_start_date
and effective_end_date;
dt_delete_validate
(p_datetrack_mode => p_datetrack_mode
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
,p_assignment_attribute_id => p_rec.assignment_attribute_id
);
if p_datetrack_mode in ('DELETE_NEXT_CHANGE', 'FUTURE_CHANGE') then
--
open c_asg;
End delete_validate;