The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
from pay_assignment_actions paa,
pay_payroll_actions ppa,
hr_organization_units bp,
hr_organization_information hoi1,
hr_organization_information hoi2
where hoi1.org_information2 = p_bp_number --Bug# 2822459
and hoi2.org_information9 = p_tax_office_code
and hoi1.organization_id = hoi2.organization_id
and hoi1.organization_id = bp.organization_id
and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
and ppa.report_type = 'YEA'
and ppa.report_qualifier = 'KR'
-- Bug 3248513
and ( (ppa.report_category in (pay_kr_yea_magtape_pkg.g_normal_yea, pay_kr_yea_magtape_pkg.g_interim_yea, pay_kr_yea_magtape_pkg.g_re_yea)) or (ppa.payroll_action_id = pay_kr_yea_magtape_pkg.g_payroll_action_id) )
and to_number(to_char(ppa.effective_date, 'YYYY')) = pay_kr_yea_magtape_pkg.g_target_year
--
and ppa.action_type in ('B','X')
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.payroll_action_id = ppa.payroll_action_id
-- Bug 3248513
and ((pay_kr_yea_magtape_pkg.g_assignment_set_id is null) or (hr_assignment_set.assignment_in_set(pay_kr_yea_magtape_pkg.g_assignment_set_id, paa.assignment_id) = 'Y'))
and ((pay_kr_yea_magtape_pkg.g_re_yea <> 'R') or (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, pay_kr_yea_magtape_pkg.g_payroll_action_id, pay_kr_yea_magtape_pkg.g_target_year) = 'Y'))
--
and paa.tax_unit_id = bp.organization_id
and paa.action_status = 'C';
select nvl(sum(greatest(to_number(i1.value), 0)), 0)
from ff_archive_items i1,
pay_assignment_actions paa,
pay_payroll_actions ppa,
hr_organization_units bp,
hr_organization_information hoi1,
hr_organization_information hoi2
where hoi1.org_information2 = p_bp_number --Bug# 2822459
and hoi2.org_information9 = p_tax_office_code
and hoi1.organization_id = hoi2.organization_id
and hoi1.organization_id = bp.organization_id
and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
and ppa.report_type = 'YEA'
and ppa.report_qualifier = 'KR'
-- Bug 3248513
and ( (ppa.report_category in (pay_kr_yea_magtape_pkg.g_normal_yea, pay_kr_yea_magtape_pkg.g_interim_yea, pay_kr_yea_magtape_pkg.g_re_yea)) or (ppa.payroll_action_id = pay_kr_yea_magtape_pkg.g_payroll_action_id) )
and to_number(to_char(ppa.effective_date, 'YYYY')) = pay_kr_yea_magtape_pkg.g_target_year
--
and ppa.action_type in ('B','X')
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.payroll_action_id = ppa.payroll_action_id
-- Bug 3248513
and ((pay_kr_yea_magtape_pkg.g_assignment_set_id is null) or (hr_assignment_set.assignment_in_set(pay_kr_yea_magtape_pkg.g_assignment_set_id, paa.assignment_id) = 'Y'))
and ((pay_kr_yea_magtape_pkg.g_re_yea <> 'R') or (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, pay_kr_yea_magtape_pkg.g_payroll_action_id, pay_kr_yea_magtape_pkg.g_target_year) = 'Y'))
--
and paa.tax_unit_id = bp.organization_id
and paa.action_status = 'C'
and i1.context1(+) = paa.assignment_action_id
and i1.user_entity_id(+) = p_user_entity_id;
select
count(*)
from per_assignment_extra_info aei,
pay_assignment_actions paa,
pay_payroll_actions ppa,
hr_organization_units bp,
hr_organization_information hoi1,
hr_organization_information hoi2
where hoi1.org_information2 = p_bp_number --Bug# 2822459
and hoi2.org_information9 = p_tax_office_code
and hoi1.organization_id = hoi2.organization_id
and hoi1.organization_id = bp.organization_id
and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
and hoi1.org_information_context like 'KR_BUSINESS_PLACE_REGISTRATION'
and ppa.report_type = 'YEA'
and ppa.report_qualifier = 'KR'
-- Bug 3248513
and ( (ppa.report_category in (pay_kr_yea_magtape_pkg.g_normal_yea, pay_kr_yea_magtape_pkg.g_interim_yea, pay_kr_yea_magtape_pkg.g_re_yea)) or (ppa.payroll_action_id = pay_kr_yea_magtape_pkg.g_payroll_action_id) )
and to_number(to_char(ppa.effective_date, 'YYYY')) = pay_kr_yea_magtape_pkg.g_target_year
--
and ppa.action_type in ('B','X')
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.payroll_action_id = ppa.payroll_action_id
-- Bug 3248513
and ((pay_kr_yea_magtape_pkg.g_assignment_set_id is null) or (hr_assignment_set.assignment_in_set(pay_kr_yea_magtape_pkg.g_assignment_set_id, paa.assignment_id) = 'Y'))
and ((pay_kr_yea_magtape_pkg.g_re_yea <> 'R') or (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, pay_kr_yea_magtape_pkg.g_payroll_action_id, pay_kr_yea_magtape_pkg.g_target_year) = 'Y'))
--
and paa.tax_unit_id = bp.organization_id
and paa.action_status = 'C'
and aei.assignment_id = paa.assignment_id
and aei.information_type = 'KR_YEA_PREV_ER_INFO'
and to_number(to_char(fnd_date.canonical_to_date(aei.aei_information1), 'YYYY')) = pay_kr_yea_magtape_pkg.g_target_year;
select
count(*)
from per_assignment_extra_info aei
where aei.assignment_id = p_assignment_id
and aei.information_type = 'KR_YEA_PREV_ER_INFO'
and to_number(to_char(fnd_date.canonical_to_date(aei.aei_information1), 'YYYY')) = pay_kr_yea_magtape_pkg.g_target_year;
Select 'Y'
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_asg_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and not exists
( Select assignment_action_id
from pay_assignment_actions paa1,
pay_payroll_actions ppa1
where paa1.assignment_id = paa.assignment_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type in ('B', 'X')
and paa1.action_status = 'C'
and ppa1.report_type = 'YEA'
and ppa1.report_qualifier = 'KR'
and ppa1.report_category = 'R'
and to_number(to_char(ppa1.effective_date, 'YYYY')) = p_target_year -- Bug 4726974
and paa1.action_sequence > paa.action_sequence);
select count(*)
from pay_kr_cont_details_v pkc,
per_contact_extra_info_f cei -- Bug 5879106
where pkc.assignment_id = p_assignment_id
and p_effective_date between pkc.emp_start_date and pkc.emp_end_date
and pay_kr_ff_functions_pkg.is_exempted_dependent(pkc.contact_type, pkc.cont_information11, pkc.national_identifier, pkc.cont_information2,
pkc.cont_information3,
pkc.cont_information4,
pkc.cont_information7,
pkc.cont_information8,
p_effective_date,
pkc.cont_information10,
pkc.cont_information12,
pkc.cont_information13,
pkc.cont_information14,
cei.contact_extra_info_id) = 'Y'
and to_char(cei.effective_start_date(+), 'yyyy') = to_char(p_effective_date,'yyyy')
and cei.information_type(+) = 'KR_DPNT_EXPENSE_INFO'
and cei.contact_relationship_id(+) = pkc.contact_relationship_id
and p_effective_date between nvl(pkc.date_start, p_effective_date)
and decode(pkc.cont_information9, 'D',trunc(add_months(nvl(pkc.date_end, p_effective_date),12),'YYYY')-1,
nvl(pkc.date_end, p_effective_date) )
and p_effective_date between nvl(ADDRESS_START_DATE, p_effective_date) and nvl(ADDRESS_END_DATE, p_effective_date)
and p_effective_date between pkc.CONT_START_DATE and pkc.CONT_END_DATE;
SELECT nvl(aei_information5, 0) VALUE
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND aei_information4 = p_bp_number
AND aei_information2 = p_code
AND information_type = 'KR_YEA_NON_TAXABLE_EARN_DETAIL'
AND TRUNC(fnd_date.canonical_to_date(aei_information1), 'YYYY') = TRUNC(p_effective_date, 'YYYY');
select count(*)
from per_assignment_extra_info
where
information_type in ('KR_YEA_SEP_PEN_DETAILS','KR_YEA_PEN_SAVING_DETAILS','KR_YEA_HOU_SAVING_DETAILS','KR_YEA_LT_STOCK_SAVING_DETAILS')
and assignment_id = p_ass_id
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
and pay_kr_yea_magtape_fun_pkg.get_sep_pen_eligible(p_assignment_id,p_assignment_action_id,
p_effective_date, information_type,aei_information6) > 0;
select aei_information6
from per_assignment_extra_info
where information_type = p_information_type
and assignment_id = p_assignment_id
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by to_number(aei_information6);
select ffai.value
into value
from ff_user_entities ffue, ff_archive_items ffai
where ffue.user_entity_name = p_user_entity_name
and ffue.legislation_code = 'KR'
and ffue.creator_type = 'X'
and ffai.user_entity_id = ffue.user_entity_id
and ffai.context1 = p_assignment_action_id;