[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT dft.person_type_id
FROM per_person_types dft
,per_person_types typ
WHERE dft.active_flag = 'Y'
AND dft.default_flag = 'Y'
AND dft.business_group_id = typ.business_group_id
AND dft.system_person_type = typ.system_person_type
AND typ.person_type_id = p_person_type_id;
SELECT dft.person_type_id
FROM per_person_types dft
WHERE dft.active_flag = 'Y'
AND dft.default_flag = 'Y'
AND dft.business_group_id = p_business_group_id
AND dft.system_person_type = p_system_person_type;
SELECT ttl.user_person_type
FROM per_person_types_tl ttl
,per_person_types typ
,per_person_type_usages_f ptu
WHERE ttl.language = userenv('LANG')
AND ttl.person_type_id = typ.person_type_id
AND typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
AND typ.person_type_id = ptu.person_type_id
AND p_effective_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ptu.person_id = p_person_id
ORDER BY DECODE(typ.system_person_type
,'EMP' ,1
,'CWK' ,2
,'APL' ,3
,'EX_EMP',4
,'EX_CWK',5
,'EX_APL',6
,7
);
SELECT ttl.user_person_type
FROM per_person_types_tl ttl
,per_person_types typ
,per_person_type_usages_f ptu
WHERE ttl.language = userenv('LANG')
AND ttl.person_type_id = typ.person_type_id
AND typ.system_person_type IN ('EMP','CWK')
AND typ.person_type_id = ptu.person_type_id
AND p_effective_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ptu.person_id = p_person_id
ORDER BY DECODE(typ.system_person_type
,'EMP' ,1
,'CWK' ,2
);
SELECT papf.employee_number
,papf.npw_number
,papf.current_employee_flag
,papf.current_npw_flag
FROM per_all_people_f papf
WHERE papf.person_id = p_person_id
AND p_effective_date BETWEEN
papf.effective_start_date AND papf.effective_end_date;
SELECT ttl.user_person_type
FROM per_person_types_tl ttl
,per_person_types typ
,per_person_type_usages_f ptu
WHERE ttl.language = userenv('LANG')
AND ttl.person_type_id = typ.person_type_id
AND typ.system_person_type IN ('APL','EX_APL')
AND typ.person_type_id = ptu.person_type_id
AND p_effective_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ptu.person_id = p_person_id;
SELECT ptu.person_type_id FROM
per_person_type_usages_f ptu, per_person_types ppt WHERE
ptu.person_id = p_person_id and
p_effective_date between ptu.effective_start_date and ptu.effective_end_date
and ptu.person_type_id = ppt.person_type_id and ppt.system_person_type='EMP';
SELECT ttl.user_person_type
FROM per_person_types_tl ttl
,per_person_types typ
,per_person_type_usages_f ptu
WHERE ttl.language = userenv('LANG')
AND ttl.person_type_id = typ.person_type_id
AND typ.system_person_type IN ('EMP','EX_EMP')
AND typ.person_type_id = ptu.person_type_id
AND p_effective_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ptu.person_id = p_person_id;
SELECT ppt.system_person_type
FROM per_person_types ppt
WHERE ppt.person_type_id = p_person_type_id;
SELECT ppt.system_person_type
FROM per_person_types ppt ,
per_person_type_usages_f ptu
WHERE ptu.person_type_usage_id = p_person_type_usage_id
AND p_effective_date between
ptu.effective_start_date and
nvl(ptu.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))
AND ptu.person_type_id = ppt.person_type_id;
SELECT GetSystemPersonType(ptu.person_type_id),
ptu.effective_start_date,
ptu.effective_end_date
FROM per_person_type_usages_f ptu
WHERE ptu.person_type_usage_id = p_person_type_usage_id
AND ptu.effective_start_date > p_effective_date
order by ptu.effective_start_date ;
SELECT pp.period_of_placement_id
FROM per_periods_of_placement pp
WHERE pp.person_id = p_person_id
AND pp.actual_termination_date = p_date_start -1;
SELECT ps.period_of_service_id
FROM per_periods_of_service ps
WHERE ps.person_id = p_person_id
AND ps.actual_termination_date = p_date_start -1;
SELECT GetSystemPersonType(ptu.person_type_id), ptu.person_id
INTO l_current_system_person_type, l_person_id
FROM per_person_type_usages_f ptu
WHERE ptu.person_type_usage_id = p_person_type_usage_id
AND p_effective_date between
ptu.effective_start_date and ptu.effective_end_date;
SELECT GetSystemPersonType(ptu.person_type_id),
ptu.effective_start_date,
ptu.effective_end_date
FROM per_person_type_usages_f ptu
WHERE ptu.person_id = p_person_id
AND ptu.effective_start_date > p_effective_date
order by ptu.effective_start_date ;
SELECT pp.period_of_placement_id
FROM per_periods_of_placement pp
WHERE pp.person_id = p_person_id
AND pp.actual_termination_date = p_date_start -1;
SELECT ps.period_of_service_id
FROM per_periods_of_service ps
WHERE ps.person_id = p_person_id
AND ps.actual_termination_date = p_date_start -1;
SELECT GetSystemPersonType(ptu.person_type_id), ptu.effective_end_date
INTO l_current_system_person_type, l_current_end_date
FROM per_person_type_usages_f ptu
WHERE ptu.person_type_usage_id = p_person_type_usage_id
AND ptu.person_id = p_person_id
AND p_effective_date between
ptu.effective_start_date and ptu.effective_end_date;
SELECT null
FROM per_person_types typ
,per_person_type_usages_f ptu
WHERE typ.system_person_type = p_system_person_type
AND typ.person_type_id = ptu.person_type_id
AND p_effective_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ptu.person_id = p_person_id;
select distinct pff.result action
,hr_general.decode_lookup('HR_PTU_ACTION_TYPES',pff.result) meaning
from per_form_functions pff
where pff.form = 'PERWSEPI'
and pff.function = 'ACTION_RESTRICTIONS'
and ((p_person_id is null and pff.input is null)
or (p_person_id is not null
and exists
(select null
from per_person_types ppt
,per_person_type_usages_f ptu
where ptu.person_type_id = ppt.person_Type_id
and p_effective_date between
ptu.effective_start_date and ptu.effective_end_date
and ptu.person_id = p_person_id
and ppt.system_person_type = pff.input)
and not exists
(select null
from per_person_types ppt2
,per_person_type_usages_f ptu2
where ptu2.person_type_id = ppt2.person_type_id
and p_effective_date between
ptu2.effective_start_date and ptu2.effective_end_date
and ptu2.person_id = p_person_id
and decode(pff.restriction_value, null, 0,
instr(pff.restriction_value,
ppt2.system_person_type)) > 0)))
and ((p_customized_restriction_id is null)
or (p_customized_restriction_id is not null
and (exists
(select null
from pay_restriction_values prv
where prv.customized_restriction_id = p_customized_restriction_id
and prv.restriction_code = 'PERSON_ACTION'
and prv.value = pff.result)
or not exists
(select null
from pay_restriction_values prv2
where prv2.customized_restriction_id = p_customized_restriction_id
and prv2.restriction_code = 'PERSON_ACTION'))))
order by 2;
l_actions.DELETE(j);