[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 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 = 'WAZO'
AND pab.abs_information_category = 'NL_WAZO'
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 = 'WAZO'
AND pab.abs_information_category = 'NL_WAZO'
AND (
hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
,paa.organization_id) IS NOT NULL
OR per_nl_wazo_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 = 'WAZO'
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_information1
AND pab.abs_information_category = 'NL_WAZO'
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 = 'WAZO'
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_information1
AND pab.abs_information_category = 'NL_WAZO'
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 = 'WAZO'
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_information1
AND pab.abs_information_category = 'NL_WAZO'
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_wazo_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_WAZO_ABS_INFO'
AND pai.action_information4 = 'WAZO'
AND pai.action_information29 = 'NL_WAZO'
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_WAZO_ABS_INFO'
AND pai.action_information4 = 'WAZO'
AND pai.action_information29 = 'NL_WAZO'
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_WAZO_ABS_INFO'
AND pai.action_information4 = 'WAZO'
AND pai.action_information29 = 'NL_WAZO'
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_wazo_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
,pab.date_start start_date
,pab.abs_information1 ass_id
,pab.abs_information2 sick_ref
,decode (pab.abs_information3,'Y','1','N','2') code_pymt
,pab.abs_information4 reason
,decode (pab.abs_information5,'Y','1','N','2') spl_sit
,to_char (fnd_date.canonical_to_date(pab.abs_information6),'YYYYMMDD') est_dob
,to_char (pab.date_start,'YYYYMMDD') wazo_st_dt
FROM per_absence_attendance_types paat
,per_absence_attendances pab
,pay_assignment_actions paa
,per_all_assignments_f pas
WHERE paat.absence_category = 'WAZO'
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_information1 = pas.assignment_id
AND pab.person_id = pas.person_id
AND pab.abs_information_category = 'NL_WAZO';
SELECT 'Y'
FROM pay_action_information pai
WHERE pai.action_context_type = 'AAP'
AND pai.action_information_category = 'NL_WAZO_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_WAZO_ABS_INFO'
AND pai.action_information29 = 'NL_WAZO'
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 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 decode (hsck.segment4, 'Y', '1', 'N', '2') wage_tax_discount
,hsck.segment11 wage_tax_table
,pas.assignment_sequence asg_seq
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 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
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 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';
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_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 sick_ref
,pai.action_information7 code_pymt
,pai.action_information8 wazo_reason
,pai.action_information9 wazo_st_dt
,pai.action_information10 contact_name
,pai.action_information11 est_name
,pai.action_information12 gender
,pai.action_information13 contact_ph_no
,pai.action_information14 employer_acc_no
,pai.action_information17 employer_iban
,pai.action_information18 spl_sit
,pai.action_information19 est_dob
FROM pay_action_information pai
WHERE pai.action_context_id = c_assignment_action_id
AND pai.action_information_category = 'NL_WAZO_ABS_INFO'
AND action_context_type = 'AAP';
SELECT action_information2 test_msg
,action_information3 tax_no_sender
,action_information4 role_sender
,action_information5 contact_per_or_dept
,action_information6 ph_no_contact
,action_information7 risk_group
,action_information8 sector
,action_information9 tax_reg_num
-- ,action_information10 employer_acc_no
,action_information11 sofi_number
,action_information12 date_of_birth
,action_information13 gender
,action_information14 init
,action_information15 prefix
,action_information16 last_name
,action_information17 emp_acc_no
,action_information18 emp_bic
,action_information19 emp_iban
,action_information20 wage_tax_discount
,action_information21 asg_seq
,action_information22 wage_tax_table
,action_information23 end_date
,action_information24 emp_num
,action_information25 est_name
,action_information26 gender_contact
,action_information27 ass_start_date
,action_information28 ass_end_date
FROM pay_action_information pai
WHERE pai.action_context_id = c_assignment_action_id
AND pai.action_information_category = 'NL_WAZO_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_WAZO_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_WAZO_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;