[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'select distinct paa.person_id
from pay_payroll_actions bppa,
per_assignments_f paa
where bppa.payroll_action_id = :payroll_action_id
and paa.business_group_id = bppa.business_group_id
and paa.payroll_id = to_number(pay_kr_ff_functions_pkg.get_legislative_parameter(bppa.payroll_action_id, ''PAYROLL_ID'', null))
order by 1';
select fue.user_entity_id,
dbi.data_type
from ff_user_entities fue,
ff_database_items dbi
where user_entity_name =c_user_entity_name
and fue.user_entity_id =dbi.user_entity_id;
select ihoi.org_information10 corp_tel_number
,choi.org_information2 corp_number
,choi.org_information1 corp_name
,choi.org_information7 corp_rep_ni
,choi.org_information6 corp_rep_name
,bhoi.org_information1 bp_name
,bhoi.org_information2 bp_number
,bhoi.org_information11 bp_rep_name
,bhoi.org_information12 bp_rep_ni
,ihoi.org_information9 bp_tax_office_code
from hr_organization_information bhoi
,hr_organization_information ihoi
,hr_organization_information choi
where bhoi.organization_id = p_tax_unit_id
and bhoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
and choi.organization_id = to_number(bhoi.org_information10)
and choi.org_information_context = 'KR_CORPORATE_INFORMATION'
and ihoi.organization_id = bhoi.organization_id
and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE' ;
l_arch_tab.delete;
Bug 4442482: Sparse Matrix enhancement - Use function PAY_KR_REPORT_PKG.GET_RESULT_VALUE in SELECT to make
query return row even when any one of these run result values is non-existent (null).
*/
cursor c_cemp_details
is
select
pap.last_name||first_name emp_name
,pap.nationality nationality
,pap.national_identifier ni
,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv1.input_value_id) hire_date
,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv2.input_value_id) leaving_date
,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv3.input_value_id) prev_hire_date
,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv4.input_value_id) prev_leaving_date
,fnd_date.date_to_canonical(ppa.date_earned) date_earned
,decode(substr(pap.national_identifier,8,1),1,NULL,2,NULL,3,NULL,4,NULL,pap.country_of_birth) country_code
from pay_run_results prr1,
pay_run_results prr2,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_types_f pet1,
pay_element_types_f pet2,
pay_input_values_f piv1,
pay_input_values_f piv2,
pay_input_values_f piv3,
pay_input_values_f piv4,
per_people_f pap,
per_assignments_f paaf,
pay_run_types_f prt,
fnd_territories ft
where ppa.payroll_action_id = p_payroll_action_id
and ppa.business_group_id = p_business_group_id
and paa.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and prr1.assignment_action_id = paa.assignment_action_id
and prr1.element_type_id = pet1.element_type_id
and pet1.element_name = 'WKPD'
and pet1.legislation_code = 'KR'
and pet1.element_type_id = piv1.element_type_id
and pet1.element_type_id = piv2.element_type_id
and pet1.element_type_id = piv3.element_type_id
and pet1.element_type_id = piv4.element_type_id
and piv1.name = 'H_DATE'
and piv2.name = 'L_DATE'
and piv3.name = 'PREV_FH_DATE'
and piv4.name = 'PREV_LL_DATE'
and ppa.effective_date between pet1.effective_start_date and pet1.effective_end_date
and ppa.effective_date between piv1.effective_start_date and piv1.effective_end_date
and ppa.effective_date between piv2.effective_start_date and piv2.effective_end_date
and ppa.effective_date between piv3.effective_start_date and piv3.effective_end_date
and ppa.effective_date between piv4.effective_start_date and piv4.effective_end_date
and pet2.element_name = 'TAX'
and pet2.legislation_code = 'KR'
and prr2.element_type_id = pet2.element_type_id
and prr2.source_type = 'E'
and prr2.assignment_action_id = paa.assignment_action_id
and ppa.effective_date between pet2.effective_start_date and pet2.effective_end_date
and prt.run_type_name in ('SEP','SEP_I')
and prt.run_type_id = ppa.run_type_id
and paaf.assignment_id = paa.assignment_id
and pap.person_id = paaf.person_id
and pap.country_of_birth = ft.territory_code (+)
and ppa.effective_date between pap.effective_start_date and pap.effective_end_date
and ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date;
Bug 4442482: Sparse Matrix enhancement - Use function PAY_KR_REPORT_PKG.GET_RESULT_VALUE in SELECT
to make query return row even when any one of these run result values is non-existent
(null).
*/
cursor c_emp_nonstat_details
is
select
pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv1.input_value_id) ns_hire_date
,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv2.input_value_id) ns_leaving_date
,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv3.input_value_id) ns_prev_hire_date
,pay_kr_report_pkg.get_result_value(prr1.run_result_id, piv4.input_value_id) ns_prev_leaving_date
from pay_run_results prr1,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_types_f pet1,
pay_input_values_f piv1,
pay_input_values_f piv2,
pay_input_values_f piv3,
pay_input_values_f piv4,
pay_run_types_f prt
where ppa.payroll_action_id = p_payroll_action_id
and ppa.business_group_id = p_business_group_id
and paa.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and prt.run_type_name in ('SEP','SEP_I')
and prt.run_type_id = ppa.run_type_id
and prr1.assignment_action_id = paa.assignment_action_id
and prr1.element_type_id = pet1.element_type_id
and pet1.element_name = 'WKPD_NON_STAT_SEP_PAY'
and pet1.legislation_code = 'KR'
and pet1.element_type_id = piv1.element_type_id
and pet1.element_type_id = piv2.element_type_id
and pet1.element_type_id = piv3.element_type_id
and pet1.element_type_id = piv4.element_type_id
and piv1.name = 'H_DATE'
and piv2.name = 'L_DATE'
and piv3.name = 'PREV_FH_DATE'
and piv4.name = 'PREV_LL_DATE'
and ppa.effective_date between pet1.effective_start_date and pet1.effective_end_date
and ppa.effective_date between piv1.effective_start_date and piv1.effective_end_date
and ppa.effective_date between piv2.effective_start_date and piv2.effective_end_date
and ppa.effective_date between piv3.effective_start_date and piv3.effective_end_date
and ppa.effective_date between piv4.effective_start_date and piv4.effective_end_date;
l_arch_tab.delete;
l_arch_tab.delete;
select pee.element_entry_id,
peev1.screen_entry_value
from pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_types_f prt,
pay_input_values_f piv1,
pay_element_entry_values_f peev1
where pet.element_name = 'PREV_ER_INFO'
and pel.element_link_id = pee.element_link_id
and pet.element_type_id = pel.element_type_id
and pel.business_group_id = ppa.business_group_id --new
and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and prt.run_type_id = paa.run_type_id
and prt.run_type_name in ('SEP','SEP_I')
and ppa.effective_date between prt.effective_start_date and prt.effective_end_date
and pet.legislation_code = 'KR'
and pee.assignment_id = paa.assignment_id
and ppa.date_earned between nvl(pee.effective_start_date,ppa.date_earned)
and nvl(pee.effective_end_date,ppa.date_earned)
and pee.entry_type = 'E'
and pee.element_link_id = pel.element_link_id
and piv1.name = v_piv_name --- cursor parameter
and piv1.element_type_id = pet.element_type_id
and peev1.element_entry_id = pee.element_entry_id
and peev1.input_value_id = piv1.input_value_id
and ppa.effective_date between piv1.effective_start_date and piv1.effective_end_date
and paa.assignment_id = p_assignment_id
and ppa.payroll_action_id = p_payroll_action_id
and ppa.business_group_id = p_business_group_id
and peev1.screen_entry_value is not null; -- Bug# 2826658 Added not to archive null values
l_ele_ent_id_tab.delete;
l_ele_value_tab.delete;
l_ele_ent_id_tab.delete;
l_ele_value_tab.delete;
select paa.assignment_id,
paa.assignment_action_id,
paa.source_action_id,
paa.tax_unit_id
from pay_run_types_f prt,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f pa,
pay_payroll_actions bppa
where bppa.payroll_action_id = p_payroll_action_id
and pa.business_group_id = bppa.business_group_id
and pa.person_id
between p_start_person_id and p_end_person_id
and bppa.effective_date
between pa.effective_start_date and pa.effective_end_date
and pa.payroll_id = to_number(pay_kr_ff_functions_pkg.get_legislative_parameter(bppa.payroll_action_id, 'PAYROLL_ID', null))
and paa.assignment_id = pa.assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date
/* between trunc(bppa.effective_date,'YYYY') and add_months(trunc(bppa.effective_date,''YYYY''),12) -1 */
between trunc(bppa.effective_date,'YYYY') and bppa.effective_date
and prt.run_type_id = paa.run_type_id
and ppa.effective_date
between prt.effective_start_date and prt.effective_end_date
and prt.run_type_name like 'SEP%'
and prt.run_type_name <> 'SEP_L'
and not exists(
select 'x'
from pay_payroll_actions appa,
pay_assignment_actions apaa,
pay_action_interlocks pai
where pai.locked_action_id = paa.assignment_action_id
and apaa.assignment_action_id = pai.locking_action_id
and appa.payroll_action_id = apaa.payroll_action_id
and appa.action_type = 'X'
and appa.report_type = bppa.report_type
and trunc(appa.effective_date,'YYYY') = trunc(bppa.effective_date,'YYYY')
union all -- 4660184
select 'x'
from pay_payroll_actions ppa2,
pay_run_types_f prt2,
pay_assignment_actions paa2
where paa2.assignment_id = paa.assignment_id
and prt2.run_type_id = paa2.run_type_id
and prt2.run_type_id = ppa2.run_type_id
and prt2.run_type_name like 'SEP%'
and ppa2.payroll_action_id = paa2.payroll_action_id
and ppa2.effective_date
between trunc(bppa.effective_date,'YYYY') and bppa.effective_date
and ppa2.effective_date
between prt2.effective_start_date and prt2.effective_end_date
and paa2.action_sequence > paa.action_sequence
)
order by pa.assignment_id, paa.action_sequence;
select pay_assignment_actions_s.nextval
into l_locking_action_id
from dual;
select ppa.business_group_id,
ppa.payroll_id,
ppa.payroll_action_id,
paa.assignment_id,
paa.assignment_action_id,
ppa.date_earned,
paa.tax_unit_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai,
pay_assignment_actions xpaa
where xpaa.assignment_action_id = p_assignment_action_id
and pai.locking_action_id = xpaa.assignment_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.source_action_id is not null
and ppa.payroll_action_id = paa.payroll_action_id;