[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 = 'S'
AND pab.abs_information_category = 'NL_S'
AND pab.abs_information4 = 'L'
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 = 'L'
AND (
hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
,paa.organization_id) IS NOT NULL
OR per_nl_ltsr_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 = 'L'
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 = 'L'
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 = 'L'
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_ltsr_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_LTSR_ABS_INFO'
AND pai.action_information4 = 'LTSR'
AND pai.action_information29 = 'NL_LTSR'
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_LTSR_ABS_INFO'
AND pai.action_information4 = 'LTSR'
AND pai.action_information29 = 'NL_LTSR'
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_LTSR_ABS_INFO'
AND pai.action_information4 = 'LTSR'
AND pai.action_information29 = 'NL_LTSR'
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_ltsr_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_id abs_att_id
, pab.date_start start_date
, pab.date_end end_date
, paat.absence_category abs_cat
, pab.abs_information1 ass_id
, pab.abs_information3 sick_ref
, to_char (pab.date_start, 'YYYYMMDD') lts_st_dt
, to_char (pab.date_end, 'YYYYMMDD') lts_end_dt
, pab.abs_information4 name_contact
, pab.abs_information5 est_name
, pab.abs_information6 gender
, pab.abs_information7 tel_no
, pab.abs_information_category info_cat
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 = 'L';
SELECT 'Y'
FROM pay_action_information pai
WHERE pai.action_context_type = 'AAP'
AND pai.action_information_category = 'NL_LTSR_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 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 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
,pas.assignment_sequence asg_seq
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 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_LTSR_ABS_INFO'
AND pai.action_information29 = 'NL_LTSR'
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 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 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 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 ass_id
,pai.action_information7 sick_ref
,pai.action_information8 lts_st_dt
,pai.action_information9 lts_end_dt
,pai.action_information10 contact_name
,pai.action_information11 est_name
,pai.action_information12 contact_gender
,pai.action_information13 contact_ph
FROM pay_action_information pai
WHERE pai.action_context_id = c_assignment_action_id
AND pai.action_information_category = 'NL_LTSR_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_information11 sofi_number
,action_information12 date_of_birth
,action_information13 gender
,action_information14 init
,action_information15 prefix
,action_information16 last_name
,action_information21 asg_seq
,action_information22 emp_num
,action_information23 est_name
,action_information24 gender_contact
FROM pay_action_information pai
WHERE pai.action_context_id = c_assignment_action_id
AND pai.action_information_category = 'NL_LTSR_EMP_INFO'
AND action_context_type = 'AAP';
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_LTSR_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;