[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(distinct hoi.org_information2||ihoi.org_information9) bp_count
from hr_organization_information hoi
,hr_organization_units hou
,hr_organization_units phou
,hr_organization_information phoi
,hr_organization_information ihoi
where hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
and hoi.org_information10 = phoi.org_information10
and phou.organization_id = primary_business_place_id
and phou.organization_id = phoi.organization_id
--Bug 5069923
and ( (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
or ( (hoi.organization_id in (select posev.ORGANIZATION_ID_child
from PER_ORG_STRUCTURE_ELEMENTS posev
where posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
and exists ( select null
from hr_organization_information
where organization_id = posev.ORGANIZATION_ID_child
and org_information_context = 'CLASS'
and org_information1 = 'KR_BUSINESS_PLACE'
)
start with ORGANIZATION_ID_PARENT = (decode(pay_magtape_generic.get_parameter_value('REPORT_FOR'),'S',null,'SUB',primary_business_place_id))
connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
)
)
or (hoi.organization_id = primary_business_place_id
)
)
)
and phoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
and ihoi.org_information_context = 'KR_INCOME_TAX_OFFICE'
and ihoi.organization_id = hou.organization_id
and exists( select 'x'
from ff_user_entities fue
,ff_archive_items fai
,pay_assignment_actions xpaa
,pay_payroll_actions xppa
where xppa.action_type = 'X'
and xppa.action_status = 'C'
--Bug 5069923
and xppa.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'),xpaa.payroll_action_id)
and xppa.effective_date between
to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'0101','YYYYMMDD')
and to_date(pay_magtape_generic.get_parameter_value('TARGET_YEAR')||'1231','YYYYMMDD')
and xppa.payroll_action_id = xpaa.payroll_action_id
and xpaa.action_status = 'C'
--Bug 5069923
and ( (pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID') is null)
or (hr_assignment_set.assignment_in_set(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'), xpaa.assignment_id) = 'Y')
)
and xppa.report_type = 'KR_SEP'
--Bug 5069923
and xppa.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
and xpaa.tax_unit_id = hou.organization_id
and fue.user_entity_name in ('A_NON_STAT_SEP_PAY_TAXABLE_EARNINGS_ASG_RUN','A_RECEIVABLE_NON_STAT_SEP_PAY_ASG_RUN','A_RECEIVABLE_SEPARATION_PAY_ASG_RUN',
'A_TAXABLE_EARNINGS_WI_PREV_ASG_RUN' ) --Bug 9409509
and fue.legislation_code = 'KR'
and fai.user_entity_id = fue.user_entity_id
and fai.context1 = xpaa.assignment_action_id
and fai.value > '0'
)
group by hoi.org_information10;
select
fac.context into l_element_entry_id
from
ff_contexts fc,
ff_user_entities fue,
ff_route_context_usages frc,
ff_archive_item_contexts fac,
ff_archive_items fai
where
fue.user_entity_name = 'X_KR_PREV_BP_NUMBER'
and fue.legislation_code = 'KR'
and fue.route_id = frc.route_id
and frc.context_id = fc.context_id
and fc.context_name = 'ELEMENT_ENTRY_ID'
and fai.context1 = p_assignment_action_id
and fai.user_entity_id = fue.user_entity_id
and fac.archive_item_id = fai.archive_item_id
and fac.context_id = fc.context_id
order by
fai.value, fac.context ;