[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT substr (LTRIM (RTRIM ( decode (p_last_name, NULL, '', ' ' || p_last_name)
|| decode (p_first_name, NULL, '', ' ' || p_first_name)
|| decode (p_middle_names, NULL, '', ' ' || p_middle_names)
)
)
,1
,240
)
INTO l_full_name
FROM dual;
SELECT SUBSTR
(LTRIM (RTRIM ( DECODE (l_last_name,
NULL, '',
' ' || l_last_name
)
|| DECODE
(p_first_name,
NULL, '',
' '
|| SUBSTR
(RTRIM (LTRIM (p_first_name)),
1,
1
)
|| '.'
)
|| DECODE
(p_middle_names,
NULL, '',
' '
|| SUBSTR
(RTRIM (LTRIM (p_middle_names)),
1,
1
)
|| '.'
)
)
),
1,
240
)
INTO l_full_name
FROM DUAL;
SELECT 'Y'
INTO l_status
FROM SYS.dual
WHERE EXISTS (
SELECT '1'
FROM per_all_people_f pp
WHERE (p_person_id IS NULL OR p_person_id <> pp.person_id)
AND p_spifn = pp.per_information13
AND pp.business_group_id IN (
--Select all business groups having leg. code RU
SELECT o1.organization_id
FROM hr_organization_information o1, hr_organization_information o2
WHERE o1.org_information9 = 'RU'
AND o1.organization_id = o2.organization_id
AND o1.org_information_context = 'Business Group Information'
AND o2.org_information_context = 'CLASS'
AND o2.org_information1 = 'HR_BG'
AND o2.org_information2 = 'Y'));
SELECT formula_id
FROM ff_compiled_info_f
WHERE formula_id = l_formula_id
AND p_session_date BETWEEN effective_start_date AND effective_end_date;
SELECT formula_id, effective_start_date
INTO l_formula_id, l_effective_date
FROM ff_formulas_f fo, ff_formula_types ft
WHERE ft.formula_type_name = 'Oracle Payroll'
AND fo.formula_type_id = ft.formula_type_id
AND fo.formula_name = 'RU_SPIFN_VALIDATION'
AND p_session_date BETWEEN fo.effective_start_date AND fo.effective_end_date;
SELECT translate (p_military_reg_board_code, '0123456789', ' ')
INTO l_tmp
FROM dual;
SELECT 'Y'
FROM SYS.dual
WHERE EXISTS (
SELECT '1'
FROM hr_organization_units hou, hr_organization_information hoi
WHERE (p_org_id <> hoi.organization_id)
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id IN (
SELECT o3.organization_id
FROM hr_organization_information o3, hr_organization_information o4
WHERE o3.org_information9 = 'RU'
AND o3.organization_id = o4.organization_id
AND o3.org_information_context = 'Business Group Information'
AND o4.org_information_context = 'CLASS'
AND o4.org_information1 = 'HR_BG'
AND o4.org_information2 = 'Y')
AND p_tax_no = hoi.org_information2
AND p_org_info_code = hoi.org_information_context);
SELECT 'Y'
INTO l_status
FROM SYS.dual
WHERE EXISTS (
SELECT '1'
FROM per_assignments_f paaf,
hr_soft_coding_keyflex scl
WHERE (paaf.assignment_id <> p_assignment_id
or
p_assignment_id is null)
and p_contract_number = scl.segment3
AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND paaf.business_group_id IN (
--Select all business groups having leg. code RU
SELECT o1.organization_id
FROM hr_organization_information o1, hr_organization_information o2
WHERE o1.org_information9 = 'RU'
AND o1.organization_id = o2.organization_id
AND o1.org_information_context = 'Business Group Information'
AND o2.org_information_context = 'CLASS'
AND o2.org_information1 = 'HR_BG'
AND o2.org_information2 = 'Y'
)
);
SELECT 'Y'
INTO l_status
FROM SYS.dual
WHERE EXISTS (
SELECT '1'
FROM per_all_assignments_f paaf,
hr_soft_coding_keyflex scl
WHERE (p_assignment_id IS NULL OR p_assignment_id <> paaf.assignment_id)
AND (nvl(scl.segment2,'N') = 'N' OR
paaf.soft_coding_keyflex_id IS NULL)
and paaf.person_id = p_person_id
and paaf.assignment_status_type_id = 1
and paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
and paaf.business_group_id = p_business_group_id
and (
(paaf.effective_start_date
between p_eff_start_date and p_eff_end_date)
or
(paaf.effective_end_date
between p_eff_start_date and p_eff_end_date)
or
(p_eff_start_date
between paaf.effective_start_date and paaf.effective_end_date)
or
(p_eff_end_date
between paaf.effective_start_date and paaf.effective_end_date)
)
);