[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = c_lookup_type
AND language = c_language
AND lookup_code = c_lookup_code;
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = c_bal_dim
AND u.user_entity_id = d.user_entity_id
AND (u.legislation_code = 'NL' )
AND (u.business_group_id IS NULL )
AND u.creator_type = 'B';
SELECT ff.context_id context_id
,pact.context_value context_value
,decode (context_value
,'ZFW'
,0
,'ZW'
,1
,'WEWE'
,2
,'WEWA'
,3
,'WAOD'
,4
,'WAOB'
,5
,6) seq
FROM ff_contexts ff
,pay_action_contexts pact
WHERE ff.context_name = 'SOURCE_TEXT'
AND ff.context_id = pact.context_id
AND pact.assignment_action_id = c_ass_act_id
ORDER BY decode (context_value
,'ZFW'
,0
,'ZW'
,1
,'WEWE'
,2
,'WEWA'
,3
,'WAOD'
,4
,'WAOB'
,5
,6);
SELECT ff.context_id
FROM ff_contexts ff
WHERE ff.context_name = 'SOURCE_TEXT';
SELECT
piv.input_value_id
FROM
pay_element_types_f pet,
pay_input_values_f piv
WHERE pet.element_name = p_element_name
AND pet.element_type_id = piv.element_type_id
AND pet.legislation_code = 'nl'
AND piv.name = 'pay value';
CURSOR cur_get_deleted_zvw_entry(p_input_value IN NUMBER) IS
SELECT
NVL(fnd_number.canonical_to_number(prrv.result_value),0)
FROM
pay_run_results prr,
pay_run_result_values prrv,
pay_element_entries_f pee
WHERE prr.assignment_action_id = assact_id
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = p_input_value
AND prr.element_entry_id = pee.element_entry_id (+)
AND prr.element_entry_id IS NOT NULL
AND pee.element_entry_id IS NULL;
SELECT
ff.context_id
FROM
ff_contexts ff
WHERE ff.context_name = 'SOURCE_TEXT';
SELECT
ptp.end_date,
ptp.period_name
FROM
per_time_periods ptp,
per_all_assignments_f paa
WHERE paa.assignment_id = asg_id
AND ptp.end_date between paa.effective_start_date
AND paa.effective_end_date
AND paa.payroll_id = ptp.payroll_id
AND ptp.start_date = retro_period;
OPEN cur_get_deleted_zvw_entry(l_zvw_input_value);
FETCH cur_get_deleted_zvw_entry
INTO l_std_zvw_correction;
CLOSE cur_get_deleted_zvw_entry;
OPEN cur_get_deleted_zvw_entry(l_zvw_input_value);
FETCH cur_get_deleted_zvw_entry
INTO l_spl_zvw_correction;
CLOSE cur_get_deleted_zvw_entry;
SELECT hrou.name
FROM hr_all_organization_units hrou
WHERE hrou.organization_id = p_business_group_id;
SELECT hlc.loc_information14 house_number
,hlc.loc_information15 house_number_add
,hlc.address_line_1 address_1
,hlc.address_line_2 address_2
,hlc.address_line_3 address_3
,hlc.region_1 street_name
,decode (length (hlc.postal_code)
,6
,concat (substr (hlc.postal_code
,1
,4)
,concat (' '
,substr (hlc.postal_code
,5
,2)))
,hlc.postal_code) postcode
,hr_general.decode_lookup ('HR_NL_CITY'
,hlc.town_or_city) city
,hlc.country country
FROM hr_locations hlc
,hr_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = l_tax_org_id
AND hlc.location_id = hou.location_id;
SELECT pad.add_information13 house_number
,pad.add_information14 house_no_add
,pad.region_1 street_name
,pad.address_line1 address_line1
,pad.address_line2 address_line2
,pad.address_line3 address_line3
,decode (length (pad.postal_code)
,6
,concat (substr (pad.postal_code
,1
,4)
,concat (' '
,substr (pad.postal_code
,5
,2)))
,pad.postal_code) postcode
,hr_general.decode_lookup ('HR_NL_CITY'
,pad.town_or_city) city
,pad.country country
FROM per_addresses pad
WHERE pad.person_id = c_person_id
AND l_reporting_date BETWEEN date_from
AND nvl (date_to
,hr_general.end_of_time)
AND pad.primary_flag = 'Y';
SELECT org_structure_version_id
FROM per_org_structure_versions posv
WHERE organization_structure_id = c_org_struct_id
AND l_reporting_date BETWEEN posv.date_from
AND nvl (posv.date_to
,hr_general.end_of_time);
SELECT DISTINCT
hou1.name employer_name
,hou1.organization_id tax_org_id
FROM hr_all_organization_units hou1
WHERE (
nvl (p_sub_emp
,'Y') = 'N'
AND hou1.organization_id = nvl (p_top_org_id
,p_business_group_id)
)
OR (
nvl (p_sub_emp
,'Y') = 'Y'
AND EXISTS
(
SELECT hou1.organization_id
FROM per_org_structure_elements pose
WHERE (
pose.organization_id_child = hou1.organization_id
OR pose.organization_id_parent = hou1.organization_id
)
AND pose.org_structure_version_id = l_org_struct_version_id
)
AND hou1.organization_id IN
(
(
SELECT nvl (p_top_org_id
,p_business_group_id)
FROM dual
)
UNION
(
SELECT (p_business_group_id)
FROM dual
)
UNION
(
SELECT e.organization_id
FROM hr_organization_information e
WHERE e.organization_id IN
(
SELECT pose.organization_id_child employer
FROM per_org_structure_elements pose
WHERE pose.org_structure_version_id = l_org_struct_version_id
START WITH pose.organization_id_parent = nvl (p_top_org_id
,p_business_group_id)
CONNECT BY NOCYCLE PRIOR pose.organization_id_child = pose.organization_id_parent
)
AND (
(
e.org_information_context = 'NL_ORG_INFORMATION'
AND e.org_information3 IS NOT NULL
AND e.org_information4 IS NOT NULL
)
OR (
e.org_information_context = 'NL_LE_TAX_DETAILS'
AND e.org_information1 IS NOT NULL
AND e.org_information2 IS NOT NULL
)
)
)
)
)
;
SELECT aei_information1 numiv_override
FROM per_assignment_extra_info
WHERE assignment_id = c_asg_id
AND aei_information_category = 'NL_NUMIV_OVERRIDE';
SELECT hoi.org_information1 tax_ref_no
,hoi.org_information2 org_id
,hoi.org_information3 tax_rep_name
FROM hr_organization_information hoi
,hr_organization_information hoi1
WHERE hoi.org_information_context = 'NL_LE_TAX_DETAILS'
AND hoi.organization_id = hoi1.organization_id
AND hoi1.organization_id = l_tax_org_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS';
SELECT hoi.org_information4 tax_reg_number
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hoi.org_information_context = 'NL_ORG_INFORMATION'
AND hou.business_group_id = c_business_group_id
AND hou.organization_id = hoi.organization_id
AND hou.organization_id = c_employer_id;
SELECT DISTINCT
paa.person_id person_id
,paa.assignment_number assignmentno
,paa.assignment_id assignmentid
,papf.last_name
|| ' '
|| papf.pre_name_adjunct
|| ' '
|| papf.per_information1 employee_name
,papf.national_identifier sofi
,papf.date_of_birth dateofbirth
,paa.assignment_sequence assignment_sequence
FROM pay_payroll_actions ppa
,pay_assignment_actions asg_act
,per_assignments_f paa
,pay_all_payrolls_f ppf
,per_periods_of_service pps
,hr_all_organization_units hou1
,per_all_people_f papf
WHERE ppa.business_group_id = p_business_group_id
AND ppa.action_type IN ('R','B','I'
,'Q','V')
AND ppa.action_status = 'C'
AND ppa.payroll_action_id = asg_act.payroll_action_id
AND asg_act.action_status = 'C'
AND ppa.effective_date BETWEEN l_reporting_start_date
AND l_reporting_date
AND ppa.effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND ppa.effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND paa.assignment_id = asg_act.assignment_id
AND pps.person_id = paa.person_id
AND pps.period_of_service_id = paa.period_of_service_id
AND paa.business_group_id = ppa.business_group_id
AND paa.payroll_id = ppf.payroll_id
AND ppf.prl_information_category = 'NL'
AND hou1.organization_id = nvl (paa.establishment_id
,ppf.prl_information1)
AND l_reporting_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND papf.person_id = paa.person_id
AND ( (NVL(paa.establishment_id,ppf.prl_information1) = c_tax_org_id) OR
NVL(paa.establishment_id,ppf.prl_information1) IN
( SELECT DISTINCT
pose1.organization_id_child employer
FROM per_org_structure_elements pose1
WHERE pose1.org_structure_version_id = l_org_struct_version_id
AND pose1.organization_id_parent = c_tax_org_id
AND pose1.organization_id_child NOT IN
(
SELECT DISTINCT e1.organization_id
FROM hr_organization_information e1
WHERE e1.organization_id = pose1.organization_id_child
AND (
(
e1.org_information_context = 'NL_ORG_INFORMATION'
AND e1.org_information3 IS NOT NULL
AND e1.org_information4 IS NOT NULL
)
OR (
e1.org_information_context = 'NL_LE_TAX_DETAILS'
AND e1.org_information1 IS NOT NULL
AND e1.org_information2 IS NOT NULL
)
)
)
)) AND
paa.person_id=nvl(p_person_id,paa.person_id);
SELECT paei.aei_information3 lhd_type
,fnd_date.canonical_to_date (paei.aei_information1) date_from
,fnd_date.canonical_to_date (paei.aei_information2) date_to
FROM per_assignment_extra_info paei
WHERE paei.aei_information_category = 'NL_LHI'
AND fnd_date.canonical_to_date (aei_information1) < l_reporting_date
AND NVL(fnd_date.canonical_to_date (aei_information2),hr_general.end_of_time) > l_reporting_start_date
AND paei.assignment_id = c_assignment_id
AND paei.aei_information3 IN ('1','2','3','4')
ORDER BY fnd_date.canonical_to_date (paei.aei_information1);
SELECT paei.aei_information3 ttd_type
,FND_DATE.CANONICAL_TO_DATE(paei.aei_information1) date_from
,FND_DATE.CANONICAL_TO_DATE(paei.aei_information2) date_to
FROM per_assignment_extra_info paei
WHERE paei.aei_information_category = 'NL_TTD'
AND FND_DATE.CANONICAL_TO_DATE(aei_information1) < l_reporting_date
AND NVL(fnd_date.canonical_to_date (aei_information2),hr_general.end_of_time) > l_reporting_start_date
AND paei.assignment_id = c_assignment_id
AND paei.aei_information3 = '2'
ORDER BY paei.aei_information1;
SELECT DISTINCT
paa2.assignment_id assignmentid
,paa2.effective_start_date effectivefrom
,paa2.effective_end_date effectiveto
,sck2.segment4 wagetaxdiscount
FROM per_all_assignments_f paa1
,per_all_assignments_f paa2
,hr_soft_coding_keyflex sck1
,hr_soft_coding_keyflex sck2
WHERE l_reporting_date > paa2.effective_start_date
AND (
(
paa2.effective_start_date =
(
SELECT min (effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = paa2.assignment_id
)
)
OR (
paa2.effective_start_date - paa1.effective_end_date = 1
AND (
sck2.segment4 <> sck1.segment4
OR sck2.segment9 <> sck1.segment9
)
)
)
AND paa2.effective_end_date > l_reporting_start_date
AND paa2.assignment_id = paa1.assignment_id
AND paa1.soft_coding_keyflex_id = sck1.soft_coding_keyflex_id
AND paa2.soft_coding_keyflex_id = sck2.soft_coding_keyflex_id
AND paa2.assignment_id = c_assignment_id
AND sck2.segment4 IS NOT NULL
ORDER BY effectivefrom;
SELECT ppa.payroll_action_id pact_id
,ppa.effective_date eff_date
,paa.assignment_action_id assact_id
,paa.assignment_id asg_id
,concat(concat(ptp.period_num,' '),TO_CHAR(ptp.START_DATE,'YYYY')) period_name
,ptp.start_date start_date
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,per_time_periods ptp
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status IN ('C','S')
AND ppa.effective_date BETWEEN l_reporting_start_date
AND l_reporting_date
AND (
(
ppa.action_type IN ('R','B','I'
,'Q')
AND paa.source_action_id IS NOT NULL
)
OR ppa.action_type = 'V'
)
AND paa.assignment_id = c_assignment_id
AND ptp.payroll_id = ppa.payroll_id
AND ppa.date_earned between ptp.start_date and ptp.end_date
ORDER BY period_name
,pact_id;
SELECT ptp.start_date retro_period
,ppa.payroll_action_id pact_id
,ppa.effective_date eff_date
,paa.assignment_action_id assact_id
,paa.assignment_id asg_id
,concat(concat('R '||ptp.period_num,' '),TO_CHAR(ptp.START_DATE,'YYYY')) period_name
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,per_time_periods ptp
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.source_action_id IS NOT NULL
AND paa.action_status IN ('C','S')
AND prr.assignment_action_id = paa.assignment_action_id
AND ptp.start_date IS NOT NULL
AND ppa.payroll_action_id = c_pact_id
AND ppa.action_type IN ('R','B','I'
,'Q','V')
AND ppa.effective_date BETWEEN l_reporting_start_date
AND l_reporting_date
AND ppa.payroll_id = ptp.payroll_id
AND prr.start_date BETWEEN ptp.start_date
AND ptp.end_date
AND paa.assignment_action_id IN
(
SELECT paa1.assignment_action_id
FROM pay_assignment_actions paa1
WHERE paa1.payroll_action_id = ppa.payroll_action_id
AND paa1.assignment_id = paa.assignment_id
)
AND paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = c_pact_id
AND paa.assignment_action_id = c_assact_id
GROUP BY ptp.start_date
,ptp.period_num
,ppa.payroll_action_id
,ppa.effective_date
,paa.assignment_action_id
,paa.assignment_id
ORDER BY retro_period;
SELECT DISTINCT
papf.national_identifier sofi_number
FROM per_all_people_f papf
,per_all_assignments_f paa
,per_periods_of_service pps
,pay_payroll_actions ppa
,pay_assignment_actions asg_act
,pay_all_payrolls_f ppf
WHERE ppa.business_group_id = p_business_group_id
AND nvl (paa.establishment_id
,ppf.prl_information1) = p_org_id
AND papf.effective_start_date <= l_reporting_end_date
AND papf.effective_end_date >= l_reporting_start_date
AND papf.person_id = paa.person_id
AND pps.person_id = paa.person_id
AND pps.period_of_service_id = paa.period_of_service_id
AND asg_act.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = asg_act.payroll_action_id
AND ppa.action_type IN ('R','B','I'
,'Q','V')
AND ppa.action_status IN ('C','S')
AND ppa.effective_date BETWEEN l_reporting_start_date
AND l_reporting_end_date
AND ppa.effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND ppa.effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.payroll_id = ppa.payroll_id
AND ppf.prl_information_category = 'NL'
ORDER BY papf.national_identifier;
SELECT employee_number,
employee_name,
sofi_number,
hire_date,
end_date,
assignment_id,
person_id,org_id,
employer_name,
RANK() OVER (PARTITION BY sofi_number ORDER BY person_id) rank
FROM
(
SELECT DISTINCT
papf.employee_number employee_number
,REPLACE(papf.last_name
|| ' '
|| papf.pre_name_adjunct
|| ' '
|| papf.per_information1
,l_seperator
,' ') employee_name
,papf.national_identifier sofi_number
,papf.effective_start_date hire_date
,papf.effective_end_date end_date
,paa.assignment_id assignment_id
,paa.person_id person_id
,NVL (paa.establishment_id
,ppf.prl_information1) org_id
, REPLACE(hou1.name,l_seperator,' ') employer_name
FROM per_all_people_f papf
,per_all_assignments_f paa
,per_periods_of_service pps
,pay_payroll_actions ppa
,pay_assignment_actions asg_act
,pay_all_payrolls_f ppf
,hr_all_organization_units hou1
WHERE ppa.business_group_id = p_business_group_id
AND papf.effective_start_date <= l_reporting_end_date
AND papf.effective_end_date >= l_reporting_start_date
AND papf.current_employee_flag = 'Y'
AND papf.person_id = paa.person_id
AND pps.person_id = paa.person_id
AND pps.period_of_service_id = paa.period_of_service_id
AND asg_act.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = asg_act.payroll_action_id
AND ppa.action_type IN ('R','B','I'
,'Q','V')
AND ppa.action_status IN ('C','S')
AND ppa.effective_date BETWEEN l_reporting_start_date
AND l_reporting_end_date
AND ppa.effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND ppa.effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.payroll_id = ppa.payroll_id
AND ppf.prl_information_category = 'NL'
AND papf.national_identifier = c_sofi_number
AND NVL (paa.establishment_id,ppf.prl_information1)=hou1.organization_id
AND paa.primary_flag = 'Y'
)
ORDER BY RANK DESC,end_date DESC;
t_individual_person_details.DELETE;