[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hou.business_group_id
from hr_organization_units hou
where hou.organization_id = p_business_place ;
select
count(*),
sum(nvl(to_number(aei_information9),0)),
sum(nvl(to_number(aei_information10),0))
from per_people_f pp,
per_assignments_f pa,
pay_assignment_actions paa,
pay_payroll_actions ppa,
hr_organization_units bp,
hr_organization_information hoi1,
hr_organization_information hoi2,
per_assignment_extra_info pae
where hoi1.org_information2 = p_bp_number
and hoi2.org_information9 = p_tax_office_code
and hoi1.organization_id = bp.organization_id
and hoi1.organization_id = hoi2.organization_id
and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
and ppa.business_group_id = bp.business_group_id
and ppa.report_type = 'YEA'
and ppa.report_qualifier = 'KR'
and ( (ppa.report_category in (pay_kr_yea_don_efile_pkg.g_normal_yea, pay_kr_yea_don_efile_pkg.g_interim_yea, pay_kr_yea_don_efile_pkg.g_re_yea))
or (ppa.payroll_action_id = pay_kr_yea_don_efile_pkg.g_payroll_action_id) )
and to_number(to_char(ppa.effective_date, 'YYYY')) = pay_kr_yea_don_efile_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
--
and (
(pay_kr_yea_don_efile_pkg.g_assignment_set_id is null) or
(hr_assignment_set.assignment_in_set(pay_kr_yea_don_efile_pkg.g_assignment_set_id, paa.assignment_id) = 'Y')
)
and (
(pay_kr_yea_don_efile_pkg.g_re_yea <> 'R')
or
(pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, pay_kr_yea_don_efile_pkg.g_payroll_action_id, pay_kr_yea_don_efile_pkg.g_target_year) = 'Y')
)
--
and paa.tax_unit_id = bp.organization_id
and paa.action_status = 'C'
and pa.assignment_id = paa.assignment_id
and pp.person_id = pa.person_id
and ppa.effective_date
between pa.effective_start_date and pa.effective_end_date
and ppa.effective_date
between pp.effective_start_date and pp.effective_end_date
and pae.assignment_id = pa.assignment_id
and pae.information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and pae.aei_information2 <= pay_kr_yea_don_efile_pkg.g_target_year
and to_char(fnd_date.canonical_to_date(pae.aei_information1),'yyyy') = pay_kr_yea_don_efile_pkg.g_target_year;
select
count(*)
from
(select aei.aei_information5,
aei.aei_information7,
aei.aei_information8,
aei.aei_information12,
pp.last_name || pp.first_name,
aei.aei_information13,
sum(nvl(aei.aei_information4, 1)),
sum(aei.aei_information3)
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,
per_people_f pp,
per_assignments_f pa,
per_kr_resident_reg_number_v resreg
where hoi1.org_information2 = p_bp_number
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'
and ( (ppa.report_category in (pay_kr_yea_don_efile_pkg.g_normal_yea, pay_kr_yea_don_efile_pkg.g_interim_yea, pay_kr_yea_don_efile_pkg.g_re_yea)) or (ppa.payroll_action_id = pay_kr_yea_don_efile_pkg.g_payroll_action_id) )
and to_number(to_char(ppa.effective_date, 'YYYY')) = pay_kr_yea_don_efile_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
and ((pay_kr_yea_don_efile_pkg.g_assignment_set_id is null) or (hr_assignment_set.assignment_in_set(pay_kr_yea_don_efile_pkg.g_assignment_set_id, paa.assignment_id) = 'Y'))
and ((pay_kr_yea_don_efile_pkg.g_re_yea <> 'R') or (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, pay_kr_yea_don_efile_pkg.g_payroll_action_id, pay_kr_yea_don_efile_pkg.g_target_year) = 'Y'))
--
and paa.tax_unit_id = bp.organization_id
and paa.action_status = 'C'
and pa.assignment_id = paa.assignment_id
and fnd_date.canonical_to_date(aei.aei_information1)
between pa.effective_start_date and pa.effective_end_date
and fnd_date.canonical_to_date(aei.aei_information1)
between pp.effective_start_date and pp.effective_end_date
and pa.person_id = resreg.person_id
and fnd_date.canonical_to_date(aei.aei_information1) BETWEEN resreg.cont_effective_start_date
AND resreg.cont_effective_end_date
and fnd_date.canonical_to_date(aei.aei_information1) BETWEEN resreg.person_effective_start_date
AND resreg.person_effective_end_date
and fnd_date.canonical_to_date(aei.aei_information1) BETWEEN nvl(resreg.relationship_start_date, fnd_date.canonical_to_date(aei.aei_information1))
AND decode(resreg.relationship_end_date,NULL,fnd_date.canonical_to_date(aei.aei_information1),
decode(resreg.relationship_end_reason,'D',
TRUNC(add_months(resreg.relationship_end_date,12),'YYYY') -1,resreg.relationship_end_date))
and resreg.cont_person_id = pp.person_id
and pp.national_identifier = aei.aei_information13
and aei.assignment_id = pa.assignment_id
and aei.information_type = 'KR_YEA_DETAIL_DONATION_INFO'
and to_char(fnd_date.canonical_to_date(aei.aei_information1),'yyyy') = pay_kr_yea_don_efile_pkg.g_target_year
group by aei.assignment_id,
aei.aei_information13,
pp.last_name || pp.first_name,
aei.aei_information12,
aei.aei_information5,
aei.aei_information7,
aei.aei_information8);