[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT payroll_id
FROM per_all_assignments_f
WHERE assignment_id = c_assignment_id;
SELECT paa.assignment_action_id
,ppa.effective_date
,ppa.action_type
,ppa.date_earned
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.action_status = 'C'
AND paa.action_status IN ('C','S')
AND ppa.action_type IN ('Q','R')
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_id = nvl (c_payroll_id
,ppa.payroll_id)
AND ppa.consolidation_set_id = nvl (c_con_set_id
,ppa.consolidation_set_id)
AND ppa.effective_date BETWEEN c_start_date
AND c_end_date
AND source_action_id IS NOT NULL
ORDER BY ppa.effective_date;
SELECT max (paa.assignment_action_id)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.action_status = 'C'
AND ppa.action_type IN ('Q','R')
AND paa.action_status IN ('C','S')
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_id = nvl (c_payroll_id
,ppa.payroll_id)
AND ppa.consolidation_set_id = nvl (c_con_set_id
,ppa.consolidation_set_id)
AND source_action_id IS NOT NULL
AND ppa.effective_date BETWEEN c_start_date
AND c_end_date;
SELECT db.defined_balance_id
FROM pay_balance_types pbt
,pay_defined_balances db
,pay_balance_dimensions bd
WHERE pbt.balance_name = c_balance_name
AND pbt.balance_type_id = db.balance_type_id
AND bd.balance_dimension_id = db.balance_dimension_id
AND bd.dimension_name = c_dimension_name
AND (
pbt.business_group_id = c_bg_id
OR pbt.legislation_code = 'NL'
)
AND (
db.business_group_id = pbt.business_group_id
OR db.legislation_code = 'NL'
);
SELECT db.defined_balance_id
FROM pay_defined_balances db
,pay_balance_dimensions bd
WHERE db.balance_type_id = c_balance_type_id
AND bd.balance_dimension_id = db.balance_dimension_id
AND bd.dimension_name = c_dimension_name
AND (
db.business_group_id = c_bg_id
OR db.legislation_code = 'NL'
);
SELECT start_date
,end_date
FROM per_time_periods
WHERE payroll_id = c_payroll_id
AND end_date BETWEEN c_start_date
AND c_end_date;
SELECT start_date
,end_date
FROM per_time_periods
WHERE payroll_id = c_payroll_id
AND c_effective_date BETWEEN start_date
AND end_date;
SELECT old_val1 old_date
,new_val1 new_date
FROM ben_ext_chg_evt_log
WHERE person_id = c_person_id
AND chg_evt_cd = 'COPOS'
AND fnd_date.canonical_to_date (prmtr_09) BETWEEN c_start_date
AND c_end_date
ORDER BY ext_chg_evt_log_id DESC;
SELECT pet.element_type_id
,pei.eei_information9
|| ' Employee Pension Basis' bal_name
,pei.eei_information12 pension_type
,- 1 defined_bal_id
,pei.eei_information18 cy_retro_element_id
,
(
SELECT retro_element_type_id
FROM pay_element_span_usages pesu
,pay_retro_component_usages prcu
WHERE prcu.retro_component_usage_id = pesu.retro_component_usage_id
AND retro_component_id =
(
SELECT retro_component_id
FROM pay_retro_components
WHERE legislation_code = 'NL'
AND short_name = 'Adjustment'
AND component_name = 'Adjustment'
)
AND creator_type = 'ET'
AND creator_id = pet.element_type_id
) py_cy_adj_retro_element_id
FROM pay_element_type_extra_info pei
,pay_element_types_f pet
WHERE pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND c_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.element_type_id = pei.element_type_id
AND pet.business_group_id = c_bg_id
AND pei.eei_information12 IN ('OPNP','AAOP');
SELECT pei.eei_information12 sub_cat
,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id) date_earned
,decode (pei.eei_information12,'FPU_B','VPL','PPP','PPP-I','OPNP-I') pension_type
,decode (pei.eei_information12,'OPNP_65','A','OPNP_W25','B','OPNP_W50','C') pension_type_variant
,sum(fnd_number.canonical_to_number(peev.screen_entry_value)) contr_amt
,pty.ee_contribution_bal_type_id
,pty.er_contribution_bal_type_id
FROM pay_element_type_extra_info pei,
pay_element_types_f pet,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_entry_values_f peev,
pay_input_values_f pivf,
pqp_pension_types_f pty
WHERE pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND c_effective_date BETWEEN to_date(pei.eei_information10,'DD/MM/RRRR') AND
to_date(pei.eei_information11,'DD/MM/RRRR')
AND c_effective_date BETWEEN pet.effective_start_date AND
pet.effective_end_date
AND c_effective_date BETWEEN peef.effective_start_date AND
peef.effective_end_date
AND c_effective_date BETWEEN pelf.effective_start_date AND
pelf.effective_end_date
AND c_effective_date BETWEEN peev.effective_start_date AND
peev.effective_end_date
AND c_effective_date BETWEEN pty.effective_start_date AND
pty.effective_end_date
AND c_effective_date BETWEEN pivf.effective_start_date AND
pivf.effective_end_date
AND (to_number(pei.eei_information18) = pet.element_type_id
OR to_number(pei.eei_information19) = pet.element_type_id
OR to_number(pei.eei_information20) = pet.element_type_id
OR to_number(pei.eei_information21) = pet.element_type_id
OR pet.element_type_id IN (SELECT retro_element_type_id
FROM pay_element_span_usages pesu,
pay_retro_component_usages prcu
WHERE prcu.retro_component_usage_id = pesu.retro_component_usage_id
AND retro_component_id = ( SELECT retro_component_id
FROM pay_retro_components
WHERE legislation_code = 'NL'
AND short_name = 'Adjustment'
AND component_name = 'Adjustment')
AND creator_type = 'ET'
AND (creator_id = pei.element_type_id OR
creator_id IN (SELECT element_type_id
FROM pay_element_types_f pet1
WHERE pet1.element_name = pei.eei_information9 || ' ABP Employer Pension Contribution'
AND pet1.business_group_id = pet.business_group_id))) )
AND pelf.element_type_id = pet.element_type_id
AND pivf.element_type_id = pet.element_type_id
AND pivf.name = 'Pay Value'
AND peef.element_link_id = pelf.element_link_id
AND peev.input_value_id = pivf.input_value_id
AND peev.element_entry_id = peef.element_entry_id
AND pet.business_group_id = c_bg_id
AND peef.assignment_id = c_asg_id
AND pei.eei_information12 IN ('OPNP_65','OPNP_W25','OPNP_W50','PPP','FPU_B')
AND pty.pension_type_id = to_number(pei.eei_information2)
AND pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id) >= g_valid_start_date
GROUP BY pei.eei_information12,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id)
,pty.ee_contribution_bal_type_id,pty.er_contribution_bal_type_id
UNION
--Normal entries
SELECT pension_sub_category sub_cat
,c_effective_date date_earned
,decode (pension_sub_category,'FPU_B','VPL','PPP','PPP-I','OPNP-I') pension_type
,decode (pension_sub_category,'OPNP_65','A','OPNP_W25','B','OPNP_W50','C') pension_type_variant
,-999999 contr_amt
,ee_contribution_bal_type_id
,er_contribution_bal_type_id
FROM pqp_pension_types_f pty
WHERE pension_sub_category IN ('OPNP_65','OPNP_W25','OPNP_W50','PPP','FPU_B')
AND business_group_id = c_bg_id
AND c_effective_date BETWEEN pty.effective_start_date AND
pty.effective_end_date
GROUP BY pension_sub_category,c_effective_date
,ee_contribution_bal_type_id
,er_contribution_bal_type_id;
SELECT fnd_number.canonical_to_number(pai.action_information7) contr_base
FROM pay_action_information pai
WHERE pai.action_information_category = 'NL_ABP_PENSION_INFO'
AND pai.action_context_type = 'AAP'
AND pai.assignment_id = c_assignment_id
AND pai.action_information1 = c_pension_type
AND c_effective_date = fnd_date.canonical_to_date(pai.action_information4)
ORDER BY pai.action_context_id DESC;
SELECT 1
FROM pay_action_information pai
WHERE pai.assignment_id = c_asg_id
AND pai.action_information_category = 'NL_ABP_ASG_INFO'
AND pai.action_context_type = 'AAP'
AND c_effective_date = fnd_date.canonical_to_date(pai.action_information3);
SELECT to_number (get_parameter (legislative_parameters
,'ORG_STRUCT_ID')) org_struct_id
,to_number (get_parameter (legislative_parameters
,'Employer')) org_id
,get_parameter (legislative_parameters
,'Report_Type') report_type
,to_number (get_parameter (legislative_parameters
,'Sequence_Number')) seq_num
,business_group_id bg_id
,start_date
,effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = c_payroll_action_id;
SELECT trunc (date_of_birth)
FROM per_all_people_f per
,per_all_assignments_f paf
WHERE per.person_id = paf.person_id
AND paf.assignment_id = p_assignment_id
AND p_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT 1
FROM pay_run_results
WHERE assignment_action_id = p_ass_act_id
AND element_type_id = p_element_type_id;
SELECT min (effective_start_date) - 1 term_date
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND assignment_status_type_id IN
(
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y'
)
UNION
--
-- Get the dates for any ended assignments. Note that this is for sec
-- assignments only.
--
SELECT max (effective_end_date)
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND asg.primary_flag = 'N'
AND NOT EXISTS
(
SELECT 1
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = p_assignment_id
AND asg1.effective_start_date = asg.effective_end_date + 1
AND asg.assignment_id = asg1.assignment_id
)
AND NOT EXISTS
(
SELECT 1
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = p_assignment_id
AND asg1.effective_start_date > asg.effective_start_date
AND asg.assignment_id = asg1.assignment_id
AND asg1.assignment_status_type_id IN
(
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y'
)
);
SELECT to_number (substr (max (lpad (paa.action_sequence, 15
, '0')
|| lpad (paa.assignment_action_id, 15
, '0')), 16
, 15))
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.action_status = 'C'
AND paa.action_status IN ('C', 'S')
AND paa.payroll_action_id = ppa.payroll_action_id
--AND ppa.effective_date
AND ppa.date_earned --Bug 16301893
BETWEEN c_start_date
AND c_end_date
AND (
ppa.action_type IN ('Q', 'R', 'I')
OR (
ppa.action_type = 'B'
AND EXISTS
(
SELECT 1
FROM pay_run_results prr
, pay_run_result_values prv
, pay_balance_feeds_f pbf
, pay_defined_balances db
WHERE prr.assignment_action_id = paa.assignment_action_id
AND prv.run_result_id = prr.run_result_id
AND pbf.input_value_id = prv.input_value_id
AND prv.result_value IS NOT NULL
AND pbf.balance_type_id = db.balance_type_id
AND db.defined_balance_id = c_balance_id
AND ppa.effective_date
BETWEEN pbf.effective_start_date
AND pbf.effective_end_date
)
)
);
SELECT min (asg.effective_start_date)
FROM per_assignments_f asg
,per_assignment_status_types past
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_start_date <= trunc (c_period_end_date)
AND nvl (asg.effective_end_date
,trunc (c_period_end_date)) >= trunc (c_period_start_date)
AND asg.assignment_id = c_assignment_id
GROUP BY asg.assignment_id;
SELECT asg.effective_start_date start_date
,decode (asg.effective_end_date
,hr_general.end_of_time
,trunc (c_period_end_date)
,asg.effective_end_date) end_date
,fnd_number.canonical_to_number (nvl (target.segment29
,'100')) ptp
FROM per_assignments_f asg
,per_assignment_status_types past
,hr_soft_coding_keyflex target
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_end_date >= c_effective_date
AND asg.assignment_id = c_assignment_id
AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND target.enabled_flag = 'Y';
SELECT nvl (aei_information6
,'0')
FROM per_assignment_extra_info
WHERE assignment_id = c_assignment_id
AND information_type = 'NL_ABP_PAR_INFO'
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND c_effective_date BETWEEN trunc (fnd_date.canonical_to_date (aei_information1))
AND trunc (nvl (fnd_date.canonical_to_date (aei_information2)
,hr_general.end_of_time))
AND aei_information6 IS NOT NULL;
SELECT normal_hours
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = c_assignment_id
AND c_end_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT nvl (sum (round (fnd_number.canonical_to_number (peev.screen_entry_value)
,2))
,0) prior_ptp
FROM pay_element_entries_f peef
,pay_element_links_f pelf
,pay_element_entry_values_f peev
WHERE peef.effective_start_date > c_effective_date
AND c_effective_date BETWEEN pelf.effective_start_date
AND pelf.effective_end_date
AND peev.effective_start_date > c_effective_date
AND peef.element_link_id = pelf.element_link_id
AND peev.element_entry_id = peef.element_entry_id
AND pelf.element_type_id = c_ele_type_id
AND peev.input_value_id = c_input_val_id
AND peef.assignment_id = c_asg_id
AND peev.screen_entry_value IS NOT NULL
AND pay_paywsmee_pkg.get_original_date_earned (peef.element_entry_id) BETWEEN c_orig_st_date
AND c_orig_ed_date;
SELECT min (effective_start_date) hire_date
FROM per_all_assignments_f asg
WHERE assignment_id = c_asg_id
AND assignment_status_type_id IN
(
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'ACTIVE_ASSIGN'
AND active_flag = 'Y'
)
AND assignment_type = 'E';
SELECT min (effective_start_date) - 1 term_date
FROM per_all_assignments_f asg
WHERE assignment_id = c_asg_id
AND assignment_status_type_id IN
(
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y'
)
AND assignment_type = 'E';
SELECT 1
FROM pay_element_entries_f peef
,pay_element_links_f pelf
,pay_element_entry_values_f peev
WHERE c_effective_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
AND c_effective_date BETWEEN pelf.effective_start_date
AND pelf.effective_end_date
AND c_effective_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND peef.element_link_id = pelf.element_link_id
AND peev.element_entry_id = peef.element_entry_id
AND pelf.element_type_id = c_ele_type_id
AND peev.input_value_id = c_input_val_id
AND peef.assignment_id = c_asg_id
AND peev.screen_entry_value IS NOT NULL;
SELECT piv.input_value_id start_dt_id
,piv1.input_value_id end_dt_id
,pet.element_type_id
FROM pay_input_values_f piv
,pay_input_values_f piv1
,pay_element_types_f pet
WHERE piv.name = 'Start Date'
AND piv.element_type_id = pet.element_type_id
AND piv1.name = 'End Date'
AND piv1.element_type_id = pet.element_type_id
AND pet.element_name = 'ABP Record 05 Reporting'
AND pet.legislation_code IS NULL
AND pet.business_group_id = c_bg_id
AND c_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND c_effective_date BETWEEN piv1.effective_start_date
AND piv1.effective_end_date;
SELECT fnd_date.canonical_to_date (peev.screen_entry_value) start_date
,fnd_date.canonical_to_date (peev1.screen_entry_value) end_date
FROM pay_element_entries_f peef
,pay_element_links_f pelf
,pay_element_entry_values_f peev
,pay_element_entry_values_f peev1
WHERE c_effective_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
AND c_effective_date BETWEEN pelf.effective_start_date
AND pelf.effective_end_date
AND c_effective_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND peef.element_link_id = pelf.element_link_id
AND peev.element_entry_id = peef.element_entry_id
AND pelf.element_type_id = c_ele_type_id
AND peev.input_value_id = c_start_dt_id
AND peef.assignment_id = c_asg_id
AND c_effective_date BETWEEN peev1.effective_start_date
AND peev1.effective_end_date
AND peev1.element_entry_id = peef.element_entry_id
AND peev1.input_value_id = c_end_dt_id;
SELECT DISTINCT
asg.effective_start_date start_date
,asg.effective_end_date end_date
,least(fnd_number.canonical_to_number (nvl (target.segment29
,'100')),125) ptp
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE asg.assignment_id = c_asg_id
AND asg.effective_start_date BETWEEN c_start_date
AND nvl (c_end_date
,hr_general.end_of_time)
AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND target.enabled_flag = 'Y'
ORDER BY start_date;
SELECT min (pay_paywsmee_pkg.get_original_date_earned (peef.element_entry_id)) start_date
,max (pay_paywsmee_pkg.get_original_date_earned (peef.element_entry_id)) end_date
FROM pay_element_entries_f peef
,pay_element_links_f pelf
,pay_element_entry_values_f peev
WHERE c_effective_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
AND c_effective_date BETWEEN pelf.effective_start_date
AND pelf.effective_end_date
AND c_effective_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND peef.element_link_id = pelf.element_link_id
AND peev.element_entry_id = peef.element_entry_id
AND pelf.element_type_id = c_ele_type_id
AND peev.input_value_id = c_input_val_id
AND peef.assignment_id = c_asg_id
AND peev.screen_entry_value IS NOT NULL;
SELECT piv.input_value_id
,pet.element_type_id
FROM pay_input_values_f piv
,pay_element_types_f pet
WHERE piv.name = c_input_value_name
AND piv.element_type_id = pet.element_type_id
AND pet.element_name = c_element_name;
SELECT least (fnd_number.canonical_to_number (nvl (target.segment29
,'100'))
,125) ptp
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND asg.assignment_id = c_asg_id
AND target.enabled_flag = 'Y'
AND trunc (c_effective_date) BETWEEN asg.effective_start_date
AND asg.effective_end_date;
SELECT nvl (hourly_salaried_code
,'H') hourly_salaried_code
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND asg.assignment_id = c_asg_id
AND target.enabled_flag = 'Y'
AND trunc (c_effective_date) BETWEEN asg.effective_start_date
AND asg.effective_end_date;
SELECT pay_paywsmee_pkg.get_original_date_earned (peef.element_entry_id) start_date
,pay_paywsmee_pkg.get_original_date_earned (peef.element_entry_id) end_date
,fnd_number.canonical_to_number (peev.screen_entry_value) ptp
FROM pay_element_entries_f peef
,pay_element_links_f pelf
,pay_element_entry_values_f peev
WHERE c_effective_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
AND c_effective_date BETWEEN pelf.effective_start_date
AND pelf.effective_end_date
AND c_effective_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND peef.element_link_id = pelf.element_link_id
AND peev.element_entry_id = peef.element_entry_id
AND pelf.element_type_id = c_ele_type_id
AND peev.input_value_id = c_input_val_id
AND peef.assignment_id = c_asg_id
AND peev.screen_entry_value IS NOT NULL
ORDER BY start_date;
SELECT element_entry_id
FROM pay_run_results prr
WHERE prr.assignment_action_id = c_assignment_action_id
AND prr.element_type_id = c_element_type_id
ORDER BY element_entry_id;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = c_element_type_id
AND name = 'ABP Employee Pension Basis';
SELECT fnd_number.canonical_to_number (nvl (screen_entry_value
,'0')) amt
,pay_paywsmee_pkg.get_original_date_earned(c_element_entry_id) date_earned
FROM pay_element_entry_values_f
WHERE element_entry_id = c_element_entry_id
AND input_value_id = c_input_value_id;
SELECT fnd_number.canonical_to_number(pai.action_information8) contr_amt
FROM pay_action_information pai
WHERE pai.action_information_category = 'NL_ABP_PENSION_INFO'
AND pai.action_context_type = 'AAP'
AND pai.assignment_id = c_assignment_id
AND pai.action_information1 = c_pension_type
AND pai.action_information2 = c_pension_type_variant
AND c_effective_date = fnd_date.canonical_to_date(pai.action_information4)
ORDER BY pai.action_context_id DESC;
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
,paa.person_id
FROM pay_payroll_actions ppa
,per_org_structure_versions posv
,per_all_assignments_f paa
,per_all_people_f pap
WHERE posv.organization_structure_id = c_org_struct_id
AND posv.date_from <= c_end_date
AND nvl (posv.date_to
,hr_general.end_of_time) >= c_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
CONNECT BY NOCYCLE PRIOR pose.organization_id_child = pose.organization_id_parent
START WITH pose.organization_id_parent = c_org_id
)
UNION
(
SELECT c_org_id
FROM dual
)
)
OR nvl (paa.establishment_id
,- 1) = c_org_id
)
AND nvl(pap.current_applicant_flag,'N') <> 'Y'
AND pap.person_id = paa.person_id
AND paa.person_id BETWEEN c_start_person_id
AND c_end_person_id
AND paa.effective_start_date <= c_end_date
AND paa.business_group_id = pap.business_group_id
AND pap.business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = c_payroll_action_id;
SELECT 1
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = c_per_id
AND trunc (ppos1.actual_termination_date) = trunc (ppos1.date_start)
AND trunc (ppos1.date_start) <= c_end_date
AND NOT EXISTS
(
SELECT 1
FROM per_periods_of_service ppos2
WHERE ppos2.person_id = c_per_id
AND ppos1.period_of_service_id <> ppos2.period_of_service_id
AND trunc (ppos2.date_start) > trunc (ppos1.date_start)
AND c_end_date >= trunc (ppos2.date_start)
AND ppos2.actual_termination_date IS NULL
AND ppos2.final_process_date IS NULL
AND ppos2.last_standard_process_date IS NULL
);
SELECT 1
FROM per_periods_of_service pps
,per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
--AND c_effective_date BETWEEN asg.effective_start_date
-- AND asg.effective_end_date
AND asg.period_of_service_id = pps.period_of_service_id
AND pps.actual_termination_date IS NOT NULL
AND pps.final_process_date IS NOT NULL
AND pps.final_process_date < c_start_date
UNION
--secondary asgs
SELECT 1
FROM per_all_assignments_f asg
WHERE assignment_id = c_asg_id
AND business_group_id = c_bg_id
AND effective_start_date < c_start_date
AND asg.primary_flag = 'N'
AND assignment_status_type_id IN
(
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y'
)
GROUP BY period_of_service_id;
SELECT 1
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_asg_id
AND ppa.BUSINESS_GROUP_ID = c_bg_id
AND ppa.action_status = 'C'
AND ppa.action_type IN ('Q','R')
AND paa.action_status = 'C'
AND ppa.payroll_id = Nvl(c_payroll_id,ppa.payroll_id)
AND ppa.consolidation_set_id = Nvl(c_cons_id,ppa.consolidation_set_id)
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.effective_date BETWEEN c_start_date and c_end_date;
SELECT 1
FROM pay_element_entries_f peef
WHERE peef.assignment_id = c_asg_id
AND (
c_start_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
OR c_end_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
)
AND EXISTS
(
SELECT 1
FROM pay_element_types_f petf
WHERE peef.element_type_id = petf.element_type_id
AND upper (element_name) LIKE '%RETRO%ABP%'
AND (
c_start_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
OR c_end_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
)
);
SELECT organization_id_parent
FROM per_org_structure_elements
WHERE organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = c_bg_id;
SELECT 1
FROM hr_organization_information
WHERE org_information_context = 'PQP_ABP_PROVIDER'
AND organization_id = c_org_id;
SELECT ppf.payroll_id
FROM pay_payrolls_f ppf
,hr_organization_information hoi
WHERE hoi.org_information_context = 'PQP_ABP_PROVIDER'
AND hoi.organization_id = c_abp_org_id
AND ppf.prl_information1 = to_char (c_tax_unit_id)
AND ppf.business_group_id = c_bg_id
AND ppf.period_type = decode (hoi.org_information5
,'MONTH'
,'Calendar Month'
,'LMONTH'
,'Lunar Month')
AND c_start_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT pay_assignment_actions_s.NEXTVAL INTO l_asg_act_id FROM dual;
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 to_char(lpad(pas.assignment_sequence,2,0)) asg_seq
,pas.assignment_number
FROM per_all_assignments_f pas
WHERE pas.assignment_id = c_assignment_id
AND c_effective_date BETWEEN pas.effective_start_date
AND pas.effective_end_date;
SELECT min (pas.effective_start_date) asg_start_date
,decode (max (pas.effective_end_date)
,hr_general.end_of_time
,to_date (NULL)
,max (pas.effective_end_date)) asg_end_date
FROM per_all_assignments_f pas
WHERE pas.assignment_id = c_assignment_id;
SELECT DISTINCT
paa.assignment_id
FROM per_all_assignments_f paaf
,pay_assignment_actions paa
WHERE paa.payroll_action_id = c_payroll_action_id
AND paa.assignment_action_id = c_asg_action_id
AND paaf.assignment_id = paa.assignment_id;
SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = c_assignment_id;
SELECT nvl (org_information4
,0) override
,nvl (org_information5
,1) start_pos
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hou.organization_id = c_org_id
AND hou.business_group_id = c_bg_id
AND hoi.organization_id = hou.organization_id
AND org_information_context = 'PQP_NL_ABP_PTP_METHOD';
SELECT aei_information4 kop
,least (fnd_number.canonical_to_number (aei_information5)
,1) * 100 vop
FROM per_assignment_extra_info
WHERE information_type = 'NL_ABP_PAR_INFO'
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND assignment_id = c_assignment_id
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 aei_information4 kop
, least (fnd_number.canonical_to_number (aei_information5), 1) * 100 vop
, fnd_date.canonical_to_date (aei_information1) kop_start_date
, nvl (fnd_date.canonical_to_date (aei_information2), c_eff_end_date) kop_end_date
FROM per_assignment_extra_info
WHERE information_type = 'NL_ABP_PAR_INFO'
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND assignment_id = c_assignment_id
AND fnd_date.canonical_to_date (aei_information1) <= c_eff_end_date
AND nvl (fnd_date.canonical_to_date (aei_information2), hr_general.end_of_time) >= c_eff_start_date
ORDER BY fnd_date.canonical_to_date (kop_start_date);
SELECT *
FROM (
SELECT pai.action_information1 start_date
, least (fnd_number.canonical_to_number (paei.aei_information5), 1) * 100 vop
FROM pay_action_information pai
, per_assignment_extra_info paei
WHERE paei.information_type = 'NL_ABP_PAR_INFO'
AND paei.aei_information_category = 'NL_ABP_PAR_INFO'
AND paei.assignment_id = pai.assignment_id
AND pai.action_context_id = c_ass_act_id
AND pai.action_information_category = 'NL_ABP_PAY_PERIOD_INFO'
AND pai.assignment_id = c_assignment_id
-- AND pai.action_information1 = paei.aei_information1
AND pai.action_information3 <> 'WNE'
UNION
SELECT pai.action_information1 start_date
, NULL vop
FROM pay_action_information pai
WHERE pai.action_context_id = c_ass_act_id
AND pai.action_information_category = 'NL_ABP_PAY_PERIOD_INFO'
AND pai.assignment_id = c_assignment_id
AND action_information3 = 'WNE'
)
ORDER BY fnd_date.canonical_to_date (start_date);
SELECT --lpad(pap.employee_number, 35, 0) employee_number
translate (pap.employee_number
,'x'
|| translate (pap.employee_number
,'x1234567890'
,'x')
,'0'
|| trim (rpad (' '
,nvl (length (translate (pap.employee_number
,'x1234567890'
,'x'))
,0)
,'0'))) employee_number
,pap.national_identifier sofi_number
,substr(pap.last_name,1,200) last_name
,replace(replace(pap.per_information1,'.',''),' ','') init
,substr(pap.pre_name_adjunct, 1, 10) prefix
,pap.date_of_birth date_of_birth
,nationality
,decode (pap.sex
,'M'
,'1'
,'F'
,'2'
,'N'
,'9'
,'0') gender
,pap.date_of_death date_of_death
FROM per_all_people_f pap
WHERE pap.person_id = c_person_id
AND business_group_id = c_bg_id
AND c_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT system_type_cd
FROM per_shared_types
WHERE lookup_type = c_lookup --'NL_NATIONALITY'
AND information1 = c_code
AND (business_group_id = c_bg_id
OR business_group_id is NULL)
ORDER BY 1;
SELECT decode (country
,'NL'
,'Y'
,'N')
FROM per_addresses_v
WHERE person_id = c_person_id
AND c_effective_date BETWEEN date_from
AND nvl (date_to
,hr_api.g_eot)
AND style IN ('NL','NL_GLB')
AND primary_flag = 'Y';
SELECT DECODE(marital_status,'S',1,
'M',2,
'D',3,
'W',4,
'DP',0,
'L',3,
'BE_LIV_TOG',1,
'REG_PART',1,
'BE_WID_PENS',4,
NULL) ms_code
FROM per_all_people_f pap
WHERE pap.person_id = c_person_id
AND business_group_id = c_bg_id
AND c_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT date_start
FROM per_contact_relationships
WHERE person_id = c_person_id
AND business_group_id = c_bg_id
AND contact_type = 'S'
AND c_end_date BETWEEN nvl (date_start
,c_start_date)
AND nvl (date_end
,c_end_date);
SELECT substr(last_name, 1, 200) last_name
,substr(pre_name_adjunct, 1, 10) prefix
,substr(replace(replace(per_information1,'.',''),' ',''), 1, 6) init
FROM per_all_people_f
WHERE person_id IN
(
SELECT contact_person_id
FROM per_contact_relationships
WHERE person_id = c_person_id
AND business_group_id = c_bg_id
AND contact_type IN ('S','D')
AND c_end_date BETWEEN nvl (date_start
,c_start_date)
AND nvl (date_end
,c_end_date)
)
AND business_group_id = c_bg_id
AND c_end_date BETWEEN effective_start_date
AND effective_end_date;
SELECT pad.style style
,pad.region_1 region --NL street, NL_GLB region
,substr(pad.address_line1, 1, 24) glb_street --NL_GLB street
,trim(substr(NVL(pad.add_information13, 0), 1, 5)) nl_hno --NL House Number --15859347
,trim(substr(NVL(pad.address_line2, 0), 1, 9)) glb_hno --NL_GLB House Number --15859347
,substr(pad.add_information14, 1, 4) nl_hnoa --NL House Number Addition
,trim(substr(pad.address_line1||' '||pad.region_3,1,35)) nl_location --NL Location
,substr(pad.address_line3, 1, 35) glb_location --NL_GLB Location
,upper(pad.postal_code) postal --Postal Code
,pad.town_or_city city --City
,pad.add_information16 iso --NL_GLB ISO Country code
FROM per_addresses_v pad
WHERE pad.person_id = c_person_id
AND pad.primary_flag = 'Y'
AND c_effective_date BETWEEN pad.date_from
AND nvl (pad.date_to
,hr_general.end_of_time)
ORDER BY 1 DESC;
SELECT decode (aei_information5
,'Y'
,'G'
,NULL) obj_cd
FROM per_assignment_extra_info
WHERE assignment_id = c_assignment_id
AND information_type = 'NL_USZO_INFO'
AND trunc (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)
AND rownum = 1; -- Added in case of multiple valid rows
SELECT hscf.segment8
FROM hr_soft_coding_keyflex hscf
,per_all_assignments_f paaf
WHERE paaf.assignment_id = c_assignment_id
AND c_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id;
SELECT min (effective_start_date) - 1 term_date
,period_of_service_id
FROM per_all_assignments_f asg
WHERE assignment_id = c_assignment_id
AND business_group_id = c_bg_id
AND effective_start_date <= c_end_date
AND assignment_status_type_id IN
(
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y'
)
GROUP BY period_of_service_id
UNION
-- Get the dates for any ended assignments. Note that this is for sec
-- assignments only.
SELECT max (effective_end_date)
,period_of_service_id
FROM per_all_assignments_f asg
WHERE assignment_id = c_assignment_id
AND business_group_id = c_bg_id
AND asg.primary_flag = 'N'
AND effective_end_date <= c_end_date
AND NOT EXISTS
(
SELECT 1
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = c_assignment_id
AND asg1.effective_start_date = asg.effective_end_date + 1
AND asg.assignment_id = asg1.assignment_id
)
AND NOT EXISTS
(
SELECT 1
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = c_assignment_id
AND asg1.effective_start_date > asg.effective_start_date
AND asg.assignment_id = asg1.assignment_id
AND asg1.assignment_status_type_id IN
(
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y'
)
)
GROUP BY period_of_service_id;
SELECT max(paaf.effective_end_date)
FROM per_all_assignments_f paaf
WHERE
paaf.assignment_id = p_asg_id
AND paaf.person_id = p_person_id
AND paaf.business_group_id = p_bg_id
AND paaf.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
);
SELECT decode(nvl(leaving_reason ,'R')
,'D' ,'O'
,'B' ,'A'
,'RESIGNATION' ,'W'
,'EARLY_RETIREMENT' ,'V'
,'R') term_reas
FROM per_periods_of_service pps
,per_all_assignments_f paaf
WHERE paaf.period_of_service_id = pps.period_of_service_id
AND paaf.assignment_id = c_assignment_id
AND c_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT pei.aei_information24 withdr_flag
FROM per_assignment_extra_info pei
WHERE pei.information_type = 'NL_ABP_PI'
AND pei.aei_information_category = 'NL_ABP_PI'
AND pei.assignment_id = c_asg_id;
SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category = 'NL_ABP_COMP_WITHDRAWAL_INFO'
AND pai.action_context_type = 'AAP'
AND pai.assignment_id = c_asg_id;
SELECT distinct fnd_date.canonical_to_date(pai1.action_information7) start_date
,fnd_date.canonical_to_date(pai1.action_information4) term_date
FROM pay_action_information pai1
WHERE pai1.assignment_id = c_asg_id
AND pai1.action_information_category = 'NL_ABP_ASG_INFO'
AND pai1.action_context_type = 'AAP'
--AND pai1.action_information4 IS NOT NULL
ORDER BY fnd_date.canonical_to_date(pai1.action_information7) DESC;
SELECT distinct fnd_date.canonical_to_date(pai1.action_information7) start_date
,fnd_date.canonical_to_date(pai1.action_information4) term_date
FROM pay_action_information pai1
WHERE pai1.assignment_id = c_asg_id
AND pai1.action_information_category = 'NL_ABP_ASG_INFO'
AND pai1.action_context_type = 'AAP'
AND pai1.action_information4 IS NOT NULL
ORDER BY fnd_date.canonical_to_date(pai1.action_information7) DESC;
SELECT DISTINCT fnd_date.canonical_to_date (pai.action_information7) start_date
, fnd_date.canonical_to_date (pai.action_information4) term_date
FROM pay_action_information pai
WHERE pai.assignment_id = c_asg_id
AND pai.action_information_category = 'NL_ABP_ASG_INFO'
AND pai.action_context_type = 'AAP'
AND pai.action_information4 IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM pay_action_information pai1
WHERE pai.assignment_id = pai1.assignment_id
AND fnd_date.canonical_to_date (pai1.action_information3) = fnd_date.canonical_to_date (pai.action_information3)
AND pai1.action_information_category = 'NL_ABP_ASG_INFO'
AND pai1.action_context_type = 'AAP'
AND pai1.action_information4 IS NULL
)
ORDER BY fnd_date.canonical_to_date (pai.action_information7) DESC;
SELECT DISTINCT
last_day (date_earned) date_earned
FROM (
-- default current period
SELECT c_end_date date_earned
FROM dual
UNION
--to find the late hire scenarios paid in current period
SELECT ppa.date_earned
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.action_status = 'C'
AND paa.action_status IN ('C', 'S')
AND ppa.action_type IN ('Q', 'R')
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_id = nvl (c_payroll_id, ppa.payroll_id)
AND ppa.consolidation_set_id = ppa.consolidation_set_id
AND ppa.effective_date
BETWEEN c_start_date
AND last_day (c_end_date)
AND source_action_id IS NOT NULL
UNION
-- to find Kind of Participation changes for previous periods
SELECT distinct last_day (add_months (start_date, level-1)) date_earned
FROM (
SELECT fnd_date.canonical_to_date (evt.prmtr_01) start_date
, fnd_date.canonical_to_date (NVL(evt.prmtr_02,evt.prmtr_09)) end_date
FROM ben_ext_chg_evt_log evt
WHERE evt.prmtr_10 = to_char (c_assignment_id)
AND evt.person_id = c_person_id
AND evt.chg_evt_cd = 'COAPKOP'
AND fnd_date.canonical_to_date (evt.prmtr_09)
BETWEEN c_start_date
AND last_day (c_end_date)
)
CONNECT BY level <= months_between (trunc (end_date, 'MM'), trunc (start_date, 'MM'))+1
UNION
-- to find change in ABP Annual Pension Salary
SELECT DISTINCT last_day (add_months (start_date, level - 1)) date_earned
FROM (
SELECT fnd_date.canonical_to_date (evt.prmtr_02) start_date
, last_day (c_end_date) end_date
FROM ben_ext_chg_evt_log evt
WHERE evt.ext_chg_evt_log_id =
(
SELECT max (ext_chg_evt_log_id)
FROM ben_ext_chg_evt_log evt
WHERE evt.prmtr_01 = to_char (c_assignment_id)
AND evt.person_id = c_person_id
AND evt.chg_evt_cd = 'COAPS'
AND fnd_date.canonical_to_date (evt.prmtr_02) <= last_day (c_end_date)
AND trunc (evt.chg_actl_dt, 'MM') = trunc (c_end_date,'MM')
)
)
CONNECT BY level <= months_between (trunc (end_date, 'MM'), trunc (start_date, 'MM')) + 1
)
ORDER BY last_day (date_earned);
SELECT DISTINCT
trunc (add_months (start_date, level - 1), 'MM') date_earned
FROM (
SELECT evt.old_val1 start_date
, evt1.new_val1 end_date
FROM ben_ext_chg_evt_log evt
, ben_ext_chg_evt_log evt1
,
(
SELECT min (ext_chg_evt_log_id) min_chg_evt_log_id
, max (ext_chg_evt_log_id) max_chg_evt_log_id
FROM ben_ext_chg_evt_log evt
WHERE evt.person_id = c_person_id
AND evt.chg_evt_cd = 'COPOS'
AND fnd_date.canonical_to_date (evt.prmtr_09)
BETWEEN c_start_date
AND last_day (c_end_date)
) evt2
WHERE evt.ext_chg_evt_log_id = evt2.min_chg_evt_log_id
AND evt1.ext_chg_evt_log_id = evt2.max_chg_evt_log_id
)
WHERE trunc (to_date (end_date), 'MM') <> trunc (to_date (start_date), 'MM')
CONNECT BY level <= months_between (trunc (to_date (end_date), 'MM'), trunc (to_date (start_date), 'MM'));
l_contr_amt_values.delete;
l_contr_amt_values.delete;
l_all_contr_amt_values.delete;
SELECT pay_assignment_actions_s.NEXTVAL INTO l_child_aa_id FROM dual;
l_temp_dates.delete;
l_contr_amt_values.delete;
l_all_contr_amt_values.delete;
SELECT org_information1 reg_num_sender
,decode(org_information2,'Y','T','N','P') prod_test
,substr(org_information3,1,45) name_sender
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hou.organization_id = nvl (c_org_id
,c_bg_id)
AND hou.business_group_id = c_bg_id
AND hoi.organization_id = hou.organization_id
AND org_information_context = 'NL_ABP_SENDER_DETAILS';
SELECT release_name
FROM fnd_product_groups;
SELECT organization_id_parent
FROM per_org_structure_elements
WHERE organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = c_bg_id;
SELECT lpad(org_information2,9,'0') er_num
,decode (org_information5
,'MONTH'
,'MND'
,'LMONTH'
,'VWK') freq
,substr (org_information6
,1
,10) ref_er
FROM hr_organization_information
WHERE org_information_context = 'PQP_ABP_PROVIDER'
AND organization_id = c_org_id;
SELECT name
FROM hr_organization_units
WHERE organization_id = p_org_id;
SELECT pai_p.action_information4 Message
,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt
,pai_p.action_information6 Description
,substr(pai_p.action_information7,1,30) E_Name
,pai_p.action_context_type cxt
FROM pay_action_information pai_p
WHERE pai_p.action_context_id = p_payroll_action_id
AND pai_p.action_information_category = 'NL_ABP_EXCEPTION_REPORT'
AND pai_p.action_context_type = 'PA';
SELECT pai_p.action_information4 Message
,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt
,pai_p.action_information6 Description
,substr(pai_p.action_information7,1,45) E_Name
,substr(pai_p.action_information8,1,30) E_Number
,pai_p.action_context_type cxt
FROM pay_assignment_actions paa
,pay_action_information pai_p
WHERE paa.payroll_action_id = p_payroll_action_id
AND pai_p.action_context_id = paa.assignment_action_id
AND pai_p.action_information_category = 'NL_ABP_EXCEPTION_REPORT'
AND pai_p.action_context_type = 'AAP'
ORDER BY pai_p.action_information8 asc;
SELECT pai_p.action_information4 message
,fnd_date.date_to_displaydate (fnd_date.canonical_to_date (pai_p.action_information5)) dt
,pai_p.action_information6 description
,substr (pai_p.action_information7, 1, 30) e_name
,pai_p.action_context_type cxt
FROM pay_action_information pai_p
WHERE pai_p.action_context_id = p_payroll_action_id
AND pai_p.action_information_category = 'NL_ABP_TECH_EXCEPTION_REPORT';