[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT value
FROM v$parameter
WHERE lower (name) = 'utl_file_dir';
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = c_lookup_type
AND language = c_language
AND lookup_code = c_lookup_code;
SELECT to_number(get_parameter(legislative_parameters,'ORG_STRUCT_ID')) org_struct_id
,to_number(get_parameter(legislative_parameters,'PERSON_ID')) person_id
,to_number(get_parameter(legislative_parameters,'ORG_ID')) org_id
,business_group_id bg_id
,start_date start_date
,effective_date end_date
,get_parameter(legislative_parameters,'TYPE') report_type
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT pei_information3 standby_emp
,pei_information4 art_4_5
,pei_information5 art_29
,pei_information6 sickness_reference
,pei_information7 payment_to_employee
,pei_information8 workday_saturday
,pei_information9 workday_sunday
,pei_information10 first_day_si
,pei_information11 daily_si
,pei_information12 accident
,pei_information13 work_hours
,pei_information14 partial_recovery_date
,pei_information15 termination_notice_date
,pei_information16 immediate_termination
,pei_information17 period_lt_3_months
,pei_information18 first_call
,pei_information19 exceeds_allowed_period
,pei_information20 temporary_contracts
FROM per_people_extra_info
WHERE information_type = 'NL_DS_SICK_INFO'
AND person_id = c_person_id
AND c_effective_date BETWEEN fnd_date.canonical_to_date (pei_information1)
AND nvl (fnd_date.canonical_to_date (pei_information2),hr_general.end_of_time);
SELECT aei.aei_information3 temp_worker
FROM per_assignment_extra_info aei
WHERE aei.assignment_id = c_assignment_id
AND aei_information_category = 'NL_TML'
AND c_effective_date BETWEEN fnd_date.canonical_to_date (aei_information1)
AND nvl (fnd_date.canonical_to_date (aei_information2)
,hr_general.end_of_time);
SELECT pas.person_id
FROM per_all_assignments_f pas
WHERE pas.assignment_id = c_assignment_id;
SELECT 1
FROM per_all_assignments_f paa
,per_assignment_status_types past
,per_all_people_f pap
,per_org_structure_versions posv
,per_absence_attendances pab
,per_absence_attendance_types paat
WHERE posv.organization_structure_id = c_org_struct_id
AND posv.date_from <= c_start_date
AND nvl (posv.date_to
,hr_general.end_of_time) >= c_end_date
AND (
paa.organization_id IN
(
(
SELECT pose.organization_id_child
FROM per_org_structure_elements pose
WHERE pose.org_structure_version_id = posv.org_structure_version_id
START WITH pose.organization_id_parent = c_org_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
)
UNION
(
SELECT c_org_id
FROM dual
)
)
OR nvl (paa.establishment_id,- 1) = c_org_id
)
AND paa.person_id = c_person_id
AND paat.absence_category = 'S'
AND pab.abs_information_category = 'NL_S'
AND pab.abs_information4 = 'S'
AND pab.business_group_id = pap.business_group_id
AND pab.date_start BETWEEN c_start_date
AND c_end_date
AND pab.absence_attendance_type_id = paat.absence_attendance_type_id
AND pab.business_group_id = paat.business_group_id
AND paa.business_group_id = c_bg_id
AND pap.person_id = c_person_id
AND pap.business_group_id = paa.business_group_id;
SELECT 1
FROM per_all_assignments_f paa
,per_all_people_f pap
,per_org_structure_versions posv
,per_absence_attendances pab
,per_absence_attendance_types paat
WHERE posv.organization_structure_id = c_org_struct_id
AND posv.date_from <= c_start_date
AND nvl (posv.date_to
,hr_general.end_of_time) >= c_end_date
AND paa.person_id = c_person_id
AND paa.business_group_id = c_bg_id
AND pap.person_id = paa.person_id
AND pap.business_group_id = paa.business_group_id
AND pab.business_group_id = pap.business_group_id
AND pab.person_id = pap.person_id
AND pab.date_start BETWEEN c_start_date
AND c_end_date
AND pab.absence_attendance_type_id = paat.absence_attendance_type_id
AND pab.business_group_id = paat.business_group_id
AND paat.absence_category = 'S'
AND pab.abs_information_category = 'NL_S'
AND pab.abs_information4 = 'S'
AND (
hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
,paa.organization_id) IS NOT NULL
OR per_nl_dsr_archive.org_check (pap.business_group_id
,NULL
,nvl (paa.establishment_id,- 1)
,c_start_date
,c_end_date) = 1
)
AND paa.organization_id IN
(
SELECT pose.organization_id_parent
FROM per_org_structure_elements pose
WHERE posv.org_structure_version_id = pose.org_structure_version_id
UNION
SELECT pose.organization_id_child
FROM per_org_structure_elements pose
WHERE posv.org_structure_version_id = pose.org_structure_version_id
);
SELECT 1
FROM hr_organization_units hou
WHERE hou.business_group_id = c_bg_id
AND hou.organization_id = c_org_id
AND EXISTS
(
SELECT 1
FROM hr_all_organization_units hou1
,hr_organization_information hoi1
WHERE hou1.business_group_id = c_bg_id
AND hoi1.org_information_context = 'NL_LE_TAX_DETAILS'
AND hoi1.org_information1 IS NOT NULL
AND hoi1.org_information2 IS NOT NULL
AND hou1.organization_id = hoi1.organization_id
AND hou1.organization_id = hou.organization_id
UNION
SELECT 1
FROM hr_all_organization_units hou2
,hr_organization_information hoi2
WHERE hou2.business_group_id = c_bg_id
AND hoi2.org_information_context = 'NL_ORG_INFORMATION'
AND hoi2.org_information4 IS NOT NULL
AND hoi2.org_information3 IS NOT NULL
AND hou2.organization_id = hoi2.organization_id
AND hou2.organization_id = hou.organization_id
);
SELECT 1
FROM hr_organization_units hou
WHERE hou.organization_id = c_org_id
AND hou.business_group_id = c_bg_id
AND EXISTS
(
SELECT 1
FROM hr_organization_units hou1
,hr_organization_information hoi1
WHERE hoi1.org_information_context = 'NL_ORG_INFORMATION'
AND hou1.business_group_id = c_bg_id
AND hou1.organization_id = hou.organization_id
AND hou1.organization_id = hoi1.organization_id
AND hoi1.org_information4 IS NOT NULL
AND hoi1.org_information3 IS NOT NULL
AND hou1.organization_id IN
(
SELECT pose.organization_id_parent
FROM per_org_structure_elements pose
,per_org_structure_versions posv
WHERE posv.org_structure_version_id = pose.org_structure_version_id
AND posv.organization_structure_id = c_org_struct_id
AND posv.date_from <= c_start_date
AND nvl (posv.date_to
,hr_general.end_of_time) >= c_end_date
UNION
SELECT pose.organization_id_child
FROM per_org_structure_elements pose
,per_org_structure_versions posv
WHERE posv.org_structure_version_id = pose.org_structure_version_id
AND posv.organization_structure_id = c_org_struct_id
AND posv.date_from <= c_start_date
AND nvl (posv.date_to
,hr_general.end_of_time) >= c_end_date
)
UNION
SELECT 1
FROM hr_organization_units hou2
,hr_organization_information hoi2
WHERE hoi2.org_information_context = 'NL_LE_TAX_DETAILS'
AND hou2.business_group_id = c_bg_id
AND hou2.organization_id = hou.organization_id
AND hou2.organization_id = hoi2.organization_id
AND hoi2.org_information1 IS NOT NULL
AND hoi2.org_information2 IS NOT NULL
AND hou2.organization_id IN
(
SELECT pose.organization_id_parent
FROM per_org_structure_elements pose
,per_org_structure_versions posv
WHERE posv.org_structure_version_id = pose.org_structure_version_id
AND posv.organization_structure_id = c_org_struct_id
AND posv.date_from <= c_start_date
AND nvl (posv.date_to
,hr_general.end_of_time) >= c_end_date
UNION
SELECT pose.organization_id_child
FROM per_org_structure_elements pose
,per_org_structure_versions posv
WHERE posv.org_structure_version_id = pose.org_structure_version_id
AND posv.organization_structure_id = c_org_struct_id
AND posv.date_from <= c_start_date
AND nvl (posv.date_to
,hr_general.end_of_time) >= c_end_date
)
);
|Description: This procedure returns an sql string to select a range |
| of assignments eligible for reporting |
----------------------------------------------------------------------*/
PROCEDURE range_code (pactid IN NUMBER
,sqlstr OUT NOCOPY VARCHAR2) is
BEGIN
hr_utility.trace('+====range_code============================================+');
sqlstr := 'SELECT DISTINCT person_id
FROM per_all_people_f pap
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = pap.business_group_id
ORDER BY pap.person_id';
sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT put.user_table_id
,puc.user_column_id
FROM pay_user_tables put
,pay_user_columns puc
WHERE put.user_table_id = puc.user_table_id
AND put.legislation_code = puc.legislation_code
AND put.user_table_name = 'NL_DIACRITICAL_MARKS'
AND put.legislation_code = 'NL';
SELECT DISTINCT
UPPER (purf.row_low_range_or_name) Source
,UPPER (pucif.value) Target
FROM pay_user_column_instances_f pucif
,pay_user_rows_f purf
WHERE pucif.user_column_id = p_user_column_id
AND purf.user_table_id = p_user_table_id
AND pucif.user_row_id = purf.user_row_id
AND pucif.business_group_id = purf.business_group_id
AND pucif.business_group_id = p_business_group_id
AND p_start_date BETWEEN pucif.EFFECTIVE_START_DATE AND pucif.EFFECTIVE_END_DATE
AND p_start_date BETWEEN purf.EFFECTIVE_START_DATE AND purf.EFFECTIVE_END_DATE;
SELECT business_group_id bg_id
,start_date start_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT DISTINCT
paa.assignment_id assignment_id
FROM per_absence_attendance_types paat
,per_absence_attendances pab
,pay_payroll_actions ppa
,per_all_assignments_f paa
,per_all_people_f pap
WHERE paat.absence_category = 'S'
AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
AND paat.business_group_id = pab.business_group_id
AND pab.date_start BETWEEN p_start_date
AND p_end_date
AND pab.business_group_id = pap.business_group_id
AND pap.person_id = p_person_id
AND p_person_id BETWEEN p_start_person_id
AND p_end_person_id
AND pap.person_id = paa.person_id
AND pab.person_id = pap.person_id
AND paa.business_group_id = pap.business_group_id
AND paa.assignment_id = pab.abs_information5
AND pab.abs_information_category = 'NL_S'
AND pab.abs_information4 = 'S'
AND pap.business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = p_payroll_action_id;
SELECT DISTINCT
paa.assignment_id assignment_id
FROM per_absence_attendance_types paat
,per_absence_attendances pab
,pay_payroll_actions ppa
,per_org_structure_versions posv
,per_all_assignments_f paa
,per_all_people_f pap
WHERE posv.organization_structure_id = p_org_struct_id
AND posv.date_from <= p_end_date
AND nvl (posv.date_to,hr_general.end_of_time) >= p_start_date
AND (
paa.organization_id IN
(
(
SELECT pose.organization_id_child
FROM per_org_structure_elements pose
WHERE pose.org_structure_version_id = posv.org_structure_version_id
START WITH pose.organization_id_parent = p_org_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
)
UNION
(
SELECT p_org_id
FROM dual
)
)
OR nvl (paa.establishment_id,- 1) = p_org_id
)
AND pap.person_id = paa.person_id
AND paa.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND paa.business_group_id = pap.business_group_id
AND pab.person_id = pap.person_id
AND pab.date_start BETWEEN p_start_date
AND p_end_date
AND paat.absence_category = 'S'
AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
AND paat.business_group_id = pab.business_group_id
AND pab.business_group_id = pap.business_group_id
AND paa.assignment_id = pab.abs_information5
AND pab.abs_information_category = 'NL_S'
AND pab.abs_information4 = 'S'
AND pap.business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = p_payroll_action_id;
SELECT DISTINCT
paa.assignment_id assignment_id
FROM per_absence_attendance_types paat
,per_absence_attendances pab
,per_all_assignments_f paa
,per_all_people_f pap
,pay_payroll_actions ppa
,per_org_structure_versions posv
WHERE posv.organization_structure_id = p_org_struct_id
AND pap.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND pap.business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = p_payroll_action_id
AND paat.absence_category = 'S'
AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
AND paat.business_group_id = pab.business_group_id
AND pab.date_start BETWEEN p_start_date
AND p_end_date
AND paa.assignment_id = pab.abs_information5
AND pab.abs_information_category = 'NL_S'
AND pab.abs_information4 = 'S'
AND pab.business_group_id = pap.business_group_id
AND pab.person_id = pap.person_id
AND pap.person_id = paa.person_id
AND (
hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
,paa.organization_id) IS NOT NULL
OR per_nl_dsr_archive.org_check (pap.business_group_id
,NULL
,nvl (paa.establishment_id,- 1)
,p_start_date
,p_end_date) = 1
)
AND paa.organization_id IN
(
SELECT pose.organization_id_parent
FROM per_org_structure_elements pose
WHERE posv.org_structure_version_id = pose.org_structure_version_id
UNION
SELECT pose.organization_id_child
FROM per_org_structure_elements pose
WHERE posv.org_structure_version_id = pose.org_structure_version_id
);
SELECT DISTINCT
paa.assignment_id assignment_id
FROM pay_action_information pai
,pay_payroll_actions ppa
,per_all_assignments_f paa
,per_all_people_f pap
WHERE pai.action_context_type = 'AAP'
AND pai.action_information_category = 'NL_DSR_ABS_INFO'
AND pai.action_information4 = 'SR'
AND pai.action_information29 = 'NL_SR'
AND (
(
pai.action_information30 = 'N'
AND p_type = 'W'
)
OR (
p_type = 'R'
)
)
AND (
fnd_date.canonical_to_date(pai.action_information2) BETWEEN p_start_date
AND p_end_date
)
AND paa.assignment_id = pai.assignment_id
AND pap.person_id = p_person_id
AND p_person_id BETWEEN p_start_person_id
AND p_end_person_id
AND pap.person_id = paa.person_id
AND paa.business_group_id = pap.business_group_id
AND pap.business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = p_payroll_action_id;
SELECT DISTINCT
paa.assignment_id assignment_id
FROM pay_action_information pai
,pay_payroll_actions ppa
,per_org_structure_versions posv
,per_all_assignments_f paa
,per_all_people_f pap
WHERE posv.organization_structure_id = p_org_struct_id
AND posv.date_from <= p_end_date
AND nvl (posv.date_to
,hr_general.end_of_time) >= p_start_date
AND (
paa.organization_id IN
(
(
SELECT pose.organization_id_child
FROM per_org_structure_elements pose
WHERE pose.org_structure_version_id = posv.org_structure_version_id
START WITH pose.organization_id_parent = p_org_id
CONNECT BY PRIOR organization_id_child = organization_id_parent
)
UNION
(
SELECT p_org_id
FROM dual
)
)
OR nvl (paa.establishment_id,- 1) = p_org_id
)
AND pap.person_id = paa.person_id
AND paa.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND paa.business_group_id = pap.business_group_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'NL_DSR_ABS_INFO'
AND pai.action_information4 = 'SR'
AND pai.action_information29 = 'NL_SR'
AND (
(
pai.action_information30 = 'N'
AND p_type = 'W'
)
OR (
p_type = 'R'
)
)
AND (
fnd_date.canonical_to_date(pai.action_information2) BETWEEN p_start_date
AND p_end_date
)
AND paa.assignment_id = pai.assignment_id
AND pap.business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = p_payroll_action_id;
SELECT DISTINCT
paa.assignment_id assignment_id
FROM pay_action_information pai
,per_all_assignments_f paa
,per_all_people_f pap
,pay_payroll_actions ppa
,per_org_structure_versions posv
WHERE posv.organization_structure_id = p_org_struct_id
AND pap.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND pap.business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = p_payroll_action_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'NL_DSR_ABS_INFO'
AND pai.action_information4 = 'SR'
AND pai.action_information29 = 'NL_SR'
AND (
(
pai.action_information30 = 'N'
AND p_type = 'W'
)
OR (
p_type = 'R'
)
)
AND (
fnd_date.canonical_to_date(pai.action_information2) BETWEEN p_start_date
AND p_end_date
)
AND paa.assignment_id = pai.assignment_id
AND ppa.payroll_action_id = p_payroll_action_id
AND pap.business_group_id = ppa.business_group_id
AND pap.person_id = paa.person_id
AND (
hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
,paa.organization_id) IS NOT NULL
OR per_nl_dsr_archive.org_check (pap.business_group_id
,NULL
,nvl (paa.establishment_id,- 1)
,p_start_date
,p_end_date) = 1
)
AND paa.organization_id IN
(
SELECT pose.organization_id_parent
FROM per_org_structure_elements pose
WHERE posv.org_structure_version_id = pose.org_structure_version_id
UNION
SELECT pose.organization_id_child
FROM per_org_structure_elements pose
WHERE posv.org_structure_version_id = pose.org_structure_version_id
);
SELECT pay_assignment_actions_s.NEXTVAL INTO l_asg_act_id FROM dual;
hr_utility.trace(l_type||' Person selected ' ||l_person_id||' '||p_start_person_id||' '||p_end_person_id);
hr_utility.trace(l_type||' Org selected ' ||l_org_id);
hr_utility.trace(l_type||' Hierarchy selected ' ||l_org_struct_id);
SELECT hou.organization_id org_id
,hou.name org_name
,hoi.org_information4 tax_reg
FROM per_all_assignments_f pas
,hr_organization_units hou
,hr_organization_information hoi
,per_org_structure_versions posv
WHERE posv.organization_structure_id = p_org_struct_id
AND p_abs_start_date BETWEEN posv.date_from
AND nvl (posv.date_to
,hr_general.end_of_time)
AND pas.assignment_id = p_assignment_id
AND hou.organization_id = hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
,pas.organization_id)
AND hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'NL_ORG_INFORMATION';
SELECT hou.organization_id org_id
,hou.name org_name
,hoi.org_information1 tax_reg
FROM per_all_assignments_f pas
,hr_organization_units hou
,hr_organization_information hoi
WHERE pas.assignment_id = p_assignment_id
AND hou.organization_id = pas.establishment_id
AND hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'NL_LE_TAX_DETAILS';
SELECT paa.assignment_id assignment_id
,pas.person_id person_id
,pab.absence_attendance_type_id abs_att_type_id
,pab.business_group_id busi_group_id
,pab.absence_attendance_id abs_att_id
,pab.date_end end_date
,nvl(fnd_date.canonical_to_date(pab.abs_information9),pab.date_start) start_date
,pab.abs_information4 report_type
,pab.abs_information5 ass_id
,pab.abs_information6 sick_ref
,to_char (pab.date_notification,'YYYYMMDD') noti_date
,to_char (nvl(fnd_date.canonical_to_date(pab.abs_information9),pab.date_start),'YYYYMMDD') dsr_st_dt
,decode (pab.abs_information7,'Y','1','N','2') code_pymt
,pab.abs_information8 reason
,to_char (pab.abs_information9,'YYYYMMDD') overall_start_date
,decode (pab.abs_information10,'Y','1','N','2') work_sat
,decode (pab.abs_information11,'Y','1','N','2') work_sun
,pab.abs_information12 si_first
,pab.abs_information13 daily_si
,pab.abs_information14 amt_wsw
,pab.abs_information15 acc_reason
,pab.abs_information16 reason_late
,pab.abs_information17 avg_hr
,pab.abs_information18 partial_rec
,to_char (fnd_date.canonical_to_date (pab.abs_information19)
,'YYYYMMDD') dt_ter_notice
,pab.abs_information20 term_init
,decode (pab.abs_information21,'Y','1','N','2') imme_term
,pab.abs_information22 no_of_calls
,decode (pab.abs_information23,'Y','1','N','2') period_less_three
,pab.abs_information24 no_of_first_call
,decode (pab.abs_information25,'Y','1','N','2') work_long_all
,pab.abs_information26 temp_cont
FROM per_absence_attendance_types paat
,per_absence_attendances pab
,pay_assignment_actions paa
,per_all_assignments_f pas
WHERE paat.absence_category = 'S'
AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
AND paat.business_group_id = pab.business_group_id
AND pab.date_start BETWEEN p_start_date
AND p_end_date
AND pab.date_start BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND pab.business_group_id = pas.business_group_id
AND paa.assignment_action_id = p_assignment_action_id
AND pas.assignment_id = paa.assignment_id
AND pab.abs_information5 = pas.assignment_id
AND pab.person_id = pas.person_id
AND pab.abs_information_category = 'NL_S'
AND pab.abs_information4 = 'S';
SELECT 'Y'
FROM pay_action_information pai
WHERE pai.action_context_type = 'AAP'
AND pai.action_information_category = 'NL_DSR_ABS_INFO'
AND pai.ASSIGNMENT_ID = p_assignment_id
AND fnd_date.canonical_to_date(pai.action_information2) = p_abs_start_date
AND pai.action_information30 = 'N';
SELECT pai.action_information1 abs_att_id
,pai.action_information2 st_dt
,pai.action_information3 end_dt
,pai.action_context_id org_asg_act_id
,pai.action_information_id act_info_id
FROM pay_action_information pai
WHERE pai.assignment_id = p_assignment_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'NL_DSR_ABS_INFO'
AND pai.action_information29 = 'NL_SR'
AND fnd_date.canonical_to_date (pai.action_information2) BETWEEN p_start_date
AND p_end_date
AND (
(
pai.action_information30 = 'N'
AND p_type = 'W'
)
OR (
p_type = 'R'
)
);
SELECT decode (hoi.org_information1,'Y','1','N','2') test_msg
,hoi.org_information2 sector
,lpad(hoi.org_information3, 2, 0) risk_group
,hoi.org_information4 role_sender
,hoi.org_information5 tax_no_sender
,hoi.org_information6 contact_per_or_dept
,hoi.org_information7 est_name
,hoi.org_information8 gender_contact
,hoi.org_information9 ph_no_contact
FROM per_all_assignments_f pas
,hr_organization_units hou
,hr_organization_information hoi
WHERE pas.assignment_id = p_assignment_id
AND p_start_date BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND hou.organization_id = nvl (pas.establishment_id
,pas.organization_id)
AND hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'NL_ORG_DSR_INFO';
SELECT upper(pea.segment2) acc_no
,pea.segment10 iban
FROM per_all_assignments_f pas
,pay_payrolls_f pp
,pay_org_payment_methods_f popm
,pay_external_accounts pea
WHERE pas.assignment_id = p_assignment_id
AND pas.payroll_id = pp.payroll_id
AND p_start_date BETWEEN pp.effective_start_date
AND pp.effective_end_date
AND pp.default_payment_method_id = popm.org_payment_method_id
AND p_start_date BETWEEN popm.effective_start_date
AND popm.effective_end_date
AND popm.external_account_id = pea.external_account_id
AND popm.business_group_id = pas.business_group_id;
SELECT pap.national_identifier sofi_number
,to_char (pap.date_of_birth,'YYYYMMDD') date_of_birth
,decode (pap.sex,'M','1','F','2',NULL,'9') gender
,REPLACE(REPLACE(pap.per_information1, '.', ''), ' ', '') init
,pap.pre_name_adjunct prefix
,pap.last_name last_name
,pap.employee_number employee_number
,pap.person_id person_id
,pap.business_group_id bg_id
,pas.establishment_id establishment_id
FROM per_all_people_f pap
,per_all_assignments_f pas
WHERE pas.assignment_id = p_assignment_id
AND p_start_date BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND pap.person_id = pas.person_id
AND p_start_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT pj.name
FROM per_all_assignments_f pas
,per_jobs pj
WHERE pas.assignment_id = p_assignment_id
AND p_start_date BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND pas.job_id = pj.job_id
AND pas.business_group_id = pj.business_group_id;
SELECT upper(pea.segment2) acc_no
,pea.segment9 bic
,pea.segment10 iban
FROM pay_personal_payment_methods_f pppm
,pay_external_accounts pea
WHERE pppm.assignment_id = p_assignment_id
AND p_start_date BETWEEN pppm.effective_start_date
AND pppm.effective_end_date
AND pppm.external_account_id = pea.external_account_id
ORDER BY pppm.priority;
SELECT pca.cag_information1 cao_code
,pca.cag_information2 no_of_waiting
,pca.cag_information3 percent_pay
,pca.cag_information4 contract_type
,decode(pca.cag_information5, 'Y', '1', 'N', '2') temp_worker
,decode(pca.cag_information6, 'Y', '1', 'N', '2') temp_to_contr
,decode(pca.cag_information7, 'Y', '1', 'N', '2') termination
,decode(pca.cag_information8, 'Y', '1', 'N', '2') appr_temp_worker
FROM per_all_assignments_f pas
,per_collective_agreements pca
WHERE pas.assignment_id = p_assignment_id
AND p_start_date BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND pca.collective_agreement_id = pas.collective_agreement_id
AND pca.cag_information_category = 'NL';
SELECT decode (hsck.segment4, 'Y', '1', 'N', '2') wage_tax_discount
,hsck.segment11 wage_tax_table
,pas.assignment_sequence asg_seq
,pas.employment_category employment_cat
,pas.employee_category employee_cat
FROM per_all_assignments_f pas
,hr_soft_coding_keyflex hsck
WHERE pas.assignment_id = p_assignment_id
AND p_start_date BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND hsck.soft_coding_keyflex_id(+) = pas.soft_coding_keyflex_id;
SELECT paei.aei_information3 fzcode
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND paei.aei_information_category = 'NL_TML'
AND p_start_date BETWEEN fnd_date.canonical_to_date (paei.aei_information1)
AND nvl (fnd_date.canonical_to_date (paei.aei_information2)
,hr_general.end_of_time);
SELECT to_char(fnd_date.canonical_to_date (paei.aei_information1),'YYYYMMDD') start_date
,to_char(fnd_date.canonical_to_date (paei.aei_information2),'YYYYMMDD') end_date
,paei.aei_information3 code_kind
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND paei.aei_information_category = 'NL_LBR'
AND p_start_date BETWEEN fnd_date.canonical_to_date (paei.aei_information1)
AND nvl (fnd_date.canonical_to_date (paei.aei_information2)
,hr_general.end_of_time);
SELECT pp.phone_number phone_no
FROM per_all_assignments_f pas
,per_phones pp
WHERE pas.assignment_id = p_assignment_id
AND p_start_date BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND pas.person_id = pp.parent_id
AND pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = p_type
AND p_start_date BETWEEN pp.date_from
AND nvl (pp.date_to
,hr_general.end_of_time);
SELECT to_char (pad.date_from,'YYYYMMDD') start_date
,to_char (pad.date_to,'YYYYMMDD') end_date
,pad.style style
,pad.postal_code postal
,pad.town_or_city town
,pad.country country
,pad.address_line1 address1
,pad.address_line2 address2
,pad.telephone_number_1 tel1
,pad.telephone_number_2 tel2
,pad.region_1 reg1
,pad.region_2 reg2
,pad.add_information13 info13
,pad.add_information14 info14
,pad.add_information15 info15
,pad.add_information16 info16
FROM per_all_assignments_f pas
,per_addresses pad
WHERE pas.assignment_id = p_assignment_id
AND p_start_date BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND pas.person_id = pad.person_id
AND pas.business_group_id = pad.business_group_id
AND pad.address_type = 'NL_SICK'
AND pad.date_from =
(
SELECT max (pad1.date_from)
FROM per_addresses pad1
WHERE pad1.person_id = pad.person_id
AND (
p_start_date BETWEEN pad1.date_from
AND nvl (pad1.date_to
,hr_general.end_of_time)
OR pad1.date_from > p_start_date
)
);
SELECT to_char (pad.date_from,'YYYYMMDD') start_date
,to_char (pad.date_to,'YYYYMMDD') end_date
,pad.style style
,pad.postal_code postal
,pad.town_or_city town
,pad.country country
,pad.address_line1 address1
,pad.address_line2 address2
,pad.telephone_number_1 tel1
,pad.telephone_number_2 tel2
,pad.region_1 reg1
,pad.region_2 reg2
,pad.add_information13 info13
,pad.add_information14 info14
,pad.add_information15 info15
,pad.add_information16 info16
FROM per_all_assignments_f pas
,per_addresses pad
WHERE pas.assignment_id = p_assignment_id
AND p_start_date BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND pad.person_id = pas.person_id
AND p_start_date BETWEEN pad.date_from
AND nvl (pad.date_to
,hr_general.end_of_time);
SELECT pei_information21 contact_name
,pei_information22 est_name
,pei_information23 gender
,pei_information24 contact_ph_no
,pei_information3 l29bzw
FROM per_all_assignments_f pas
,per_people_extra_info pei
WHERE pas.assignment_id = p_assignment_id
AND p_start_date BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND pei.person_id = pas.person_id
AND pei.information_type = 'NL_DS_SICK_INFO';
SELECT system_type_cd
FROM per_shared_types
WHERE lookup_type = c_lookup
AND information1 = c_code
AND (
business_group_id = c_business_gr_id
OR business_group_id IS NULL
)
ORDER BY 1;
SELECT absence_attendance_id
,date_start
,date_end
FROM (
SELECT absence_attendance_id
,nvl(fnd_date.canonical_to_date(abs_information9),date_start) date_start
,date_end
FROM per_absence_attendances
START WITH absence_attendance_id = c_current_absence
CONNECT BY PRIOR abs_information27 = absence_attendance_id
)
WHERE absence_attendance_id <> c_current_absence
AND date_start IS NOT NULL
AND date_end IS NOT NULL
AND date_end <
(
SELECT nvl(fnd_date.canonical_to_date(abs_information9),date_start) date_start
FROM per_absence_attendances
WHERE absence_attendance_id = c_current_absence
);
SELECT ppa.payroll_action_id
,paa.chunk_number
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = c_assignment_action_id;
SELECT paa.assignment_id
,paa.chunk_number
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = c_assignment_action_id;
SELECT to_char(min (pas.effective_start_date),'YYYYMMDD') ass_start_date
,to_char(decode (max (pas.effective_end_date)
,hr_general.end_of_time
,to_date(NULL),max (pas.effective_end_date)),'YYYYMMDD') ass_end_date
FROM per_all_assignments_f pas
WHERE pas.assignment_id = p_assignment_id;
SELECT paei.aei_information1 numiv_override
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND paei.aei_information_category = 'NL_NUMIV_OVERRIDE';
connected_absences.DELETE;
SELECT pay_assignment_actions_s.NEXTVAL INTO l_child_aa_id FROM dual;
pay_action_information_api.update_action_information
(p_action_information_id => r_get_archived_absence.act_info_id
, p_object_version_number => l_ovn
, p_action_information30 => 'Y'
);
SELECT name
FROM per_organization_structures
WHERE organization_structure_id = p_org_struct_id;
SELECT name
FROM hr_organization_units
WHERE organization_id = p_org_id;
SELECT full_name
FROM per_all_people_f
WHERE person_id = p_person_id;
SELECT pai.action_information2 conn_count
,pai.action_information3 start1
,pai.action_information4 end1
,pai.action_information5 start2
,pai.action_information6 end2
,pai.action_information7 start3
,pai.action_information8 end3
,pai.action_information9 start4
,pai.action_information10 end4
,pai.action_information11 start5
,pai.action_information12 end5
,pai.action_information13 start6
,pai.action_information14 end6
,pai.action_information15 start7
,pai.action_information16 end7
,pai.action_information17 start8
,pai.action_information18 end8
,pai.action_information19 start9
,pai.action_information20 end9
,pai.action_information21 start10
,pai.action_information22 end10
,pai.action_information23 start11
,pai.action_information24 end11
,pai.action_information25 start12
,pai.action_information26 end12
,pai.action_information27 start13
,pai.action_information28 end13
,pai.action_information29 start14
,pai.action_information30 end14
FROM pay_action_information pai
WHERE pai.action_context_id = c_assignment_action_id
AND pai.action_information_category = 'NL_DSR_CONN_ABS_INFO'
AND action_context_type = 'AAP';
SELECT pai.action_information1 abs_att_id
,pai.action_information2 start_date
,pai.action_information3 end_date
,pai.action_information4 abs_cat
,pai.action_information5 person_id
,pai.action_information6 test_msg
,pai.action_information7 tax_no_sender
,pai.action_information8 role_sender
,pai.action_information9 contact_per_or_dept
,pai.action_information10 ph_no_contact
,pai.action_information11 risk_group
,pai.action_information12 sector
,pai.action_information13 tax_reg_num
,pai.action_information14 employer_acc_no
,pai.action_information17 est_name
,pai.action_information18 gender
,pai.action_information19 employer_iban
FROM pay_action_information pai
WHERE pai.action_context_id=c_assignment_action_id
AND pai.ACTION_INFORMATION_CATEGORY = 'NL_DSR_ABS_INFO'
AND action_context_type = 'AAP';
SELECT pai.action_information2 sick_ref
,pai.action_information3 noti_date
,pai.action_information4 dsr_st_dt
,pai.action_information5 code_pymt
,pai.action_information6 reason
,pai.action_information7 work_sat
,pai.action_information8 work_sun
,pai.action_information9 si_first
,pai.action_information10 daily_si
,pai.action_information11 amt_wsw
,pai.action_information12 acc_reason
,pai.action_information13 reason_late
,pai.action_information14 avg_hr
,pai.action_information15 partial_rec
,pai.action_information16 dt_ter_notice
,pai.action_information17 term_init
,pai.action_information18 imme_term
,pai.action_information19 no_of_calls
,pai.action_information20 period_less_three
,pai.action_information21 no_of_first_call
,pai.action_information22 work_long_all
,pai.action_information23 temp_cont
,pai.action_information24 contact_name
,pai.action_information25 est_name
,pai.action_information26 gender
,pai.action_information27 contact_ph_no
,pai.action_information28 conn_absence_count
,pai.action_information30 l_29bzw
FROM pay_action_information pai
WHERE pai.action_context_id = c_assignment_action_id
AND pai.action_information_category = 'NL_DSR_SICK_INFO'
AND action_context_type = 'AAP';
SELECT action_information2 sofi_number
,action_information3 date_of_birth
,action_information4 gender
,action_information5 init
,action_information6 prefix
,action_information7 last_name
,action_information8 acc_no
,action_information9 bic
,action_information10 iban
,action_information11 wage_tax_discount
,action_information12 asg_seq
,action_information13 job_name
,action_information14 fz_code
,action_information15 wage_tax_table
,action_information16 no_of_waiting
,action_information17 percent_pay
,action_information18 code_kind
,action_information19 start_date
,action_information20 end_date
,action_information21 contract
,action_information22 cao_code
,action_information23 temp_worker
,action_information24 temp_to_contr
,action_information25 termination
,action_information26 appr_temp_worker
,action_information27 emp_num
,action_information28 ass_start_date
,action_information29 ass_end_date
FROM pay_action_information pai
WHERE pai.action_context_id = c_assignment_action_id
AND pai.action_information_category = 'NL_DSR_EMP_INFO'
AND action_context_type = 'AAP';
SELECT action_information2 style
,action_information3 start_date
,action_information4 end_date
,action_information5 postal
,action_information6 town
,action_information7 country
,action_information8 tel1
,action_information9 tel2
,action_information10 reg1
,action_information11 reg2
,action_information12 info13
,action_information13 info14
,action_information14 info15
,action_information15 info16
,action_information16 sick_ph_no
,action_information17 for_sick_ph_no
,action_information18 sick_mobile_no
,action_information19 address1
,action_information20 address2
FROM pay_action_information pai
WHERE pai.action_context_id = c_assignment_action_id
AND pai.action_information_category = 'NL_DSR_ADDRESS'
AND action_context_type = 'AAP';
SELECT fdst.short_text text
FROM fnd_attached_documents fad
,fnd_documents fd
,fnd_document_categories fdc
,fnd_document_datatypes fdd
,fnd_documents_short_text fdst
WHERE fad.entity_name = 'PER_ABSENCE_ATTENDANCES'
AND fad.pk1_value = to_char(p_abs_attendance_id)
AND fd.document_id = fad.document_id
AND fdd.datatype_id = fd.datatype_id
AND fdc.category_id = fd.category_id
AND fdc.name = 'HR_COMMENT'
AND fdd.name = 'SHORT_TEXT'
AND fdd.language = 'US'
AND fdst.media_id = fd.media_id;
SELECT pai.action_information30 flag
FROM pay_action_information pai
WHERE pai.action_context_id = c_assignment_action_id
AND pai.action_information_category = 'NL_DSR_ABS_INFO'
AND action_context_type = 'AAP';
SELECT release_name
FROM fnd_product_groups;
SELECT assignment_action_id ass_act_id
FROM pay_assignment_actions
WHERE source_action_id = c_assignment_action_id;
SELECT locked_action_id parent_id
FROM pay_action_interlocks
WHERE locking_action_id = c_assignment_action_id;
SELECT ppa.payroll_action_id
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = c_assignment_action_id;