VIEW: APPS.PAY_KR_SEP_TAX_WO_ADDR_V

Source

View Text - Preformatted

SELECT sep2.business_group_id business_group_id, sep2.payroll_id payroll_id, sep2.assignment_id assignment_id, sep2.assignment_action_id assignment_action_id, sep2.payroll_action_id payroll_action_id, sep2.effective_date effective_date, sep2.date_earned date_earned, sep2.xeffective_date xeffective_date, sep2.run_type_id run_type_id, sep2.run_type_name run_type_name, sep2.tax_unit_id tax_unit_id, sep2.xassignment_action_id xassignment_action_id, sep2.xpayroll_action_id xpayroll_action_id, sep2.element_entry_id prev_element_entry_id, peev1.screen_entry_value prev_bp_number, peev2.screen_entry_value prev_bp_name, peev3.screen_entry_value prev_sep_pay, peev4.screen_entry_value prev_sep_ins, peev5.screen_entry_value prev_sp_sep_alw, pay_kr_report_pkg.get_result_value_char(sep2.assignment_action_id, sep2.business_group_id, 'PREV_ER_INFO', 'ELIGIBLE_POST_TAX_DEDUC_FLAG') prev_eligible_for_ptax, pay_kr_report_pkg.get_result_value_number(sep2.assignment_action_id, sep2.business_group_id, 'PREV_ER_INFO', 'Non Taxable Earning') prev_sp_non_tax_amt, bhoi.org_information2 bp_number, choi.org_information1 corp_name, choi.org_information6 corp_representative_name, choi.org_information2 corp_number, pay_kr_sep_form_pkg.get_kr_d_address_line1(bhl.loc_information14) || ' ' || bhl.address_line_2 bp_address, thoi.org_information1 tax_office_name, pp.last_name last_name, pp.first_name first_name, pp.national_identifier national_identifier, pp.nationality nationality, pay_kr_report_pkg.get_result_value_date(sep2.assignment_action_id, sep2.business_group_id, 'WKPD', 'H_DATE') hiring_date, pay_kr_report_pkg.get_result_value_date(sep2.assignment_action_id, sep2.business_group_id, 'WKPD', 'L_DATE') leaving_date, pay_kr_report_pkg.get_result_value_date(sep2.assignment_action_id, sep2.business_group_id, 'WKPD', 'PREV_FH_DATE') prev_first_hiring_date, pay_kr_report_pkg.get_result_value_date(sep2.assignment_action_id, sep2.business_group_id, 'WKPD', 'PREV_LL_DATE') prev_last_leaving_date, pay_kr_report_pkg.get_result_value_number(sep2.assignment_action_id, sep2.business_group_id, 'SEP_PAY_OVERSEAS_TAX_BREAK', 'OVERSEAS_TAX_BREAK') sep_pay_overseas_tax_break, pay_kr_report_pkg.get_result_value_number(sep2.assignment_action_id, sep2.business_group_id, 'NON_STAT_SEP_OVERSEAS_TAX_BRK', 'OVERSEAS_TAX_BREAK') non_stat_sep_overseas_tax_brk, pay_kr_report_pkg.get_result_value_number(sep2.assignment_action_id, sep2.business_group_id, 'STAT_SEP_PAY_POST_TAX_DEDUC', 'POST_TAX_DEDUCTION') stat_sep_pay_post_tax_deduc, pay_kr_report_pkg.get_result_value_number(sep2.assignment_action_id, sep2.business_group_id, 'NON_STAT_SEP_PAY_POST_TAX_DEDUC', 'POST_TAX_DEDUCTION') non_stat_sep_post_tax_deduc, ft.territory_code country_code, ft.territory_short_name country, ppos.actual_termination_date actual_termination_date, pp.person_id person_id FROM hr_organization_information thoi, hr_organization_information choi, hr_locations bhl, hr_organization_units bhou, hr_organization_information bhoi, per_people_f pp, per_assignments_f pa, per_periods_of_service ppos, fnd_territories_vl ft, pay_element_entry_values_f peev5, pay_element_entry_values_f peev4, pay_element_entry_values_f peev3, pay_element_entry_values_f peev2, pay_element_entry_values_f peev1, (SELECT sep.business_group_id business_group_id, sep.payroll_id payroll_id, sep.assignment_id assignment_id, sep.assignment_action_id assignment_action_id, sep.payroll_action_id payroll_action_id, sep.effective_date effective_date, sep.date_earned date_earned, sep.xeffective_date xeffective_date, sep.run_type_id run_type_id, sep.run_type_name run_type_name, sep.tax_unit_id tax_unit_id, sep.xpayroll_action_id xpayroll_action_id, sep.xassignment_action_id xassignment_action_id, pee.element_entry_id element_entry_id, sep.einput_value_id1 einput_value_id1, sep.einput_value_id2 einput_value_id2, sep.einput_value_id3 einput_value_id3, sep.einput_value_id4 einput_value_id4, sep.einput_value_id5 einput_value_id5 FROM pay_element_entries_f pee, (SELECT ppa.business_group_id business_group_id, ppa.payroll_id payroll_id, paa.assignment_id assignment_id, paa.assignment_action_id assignment_action_id, ppa.payroll_action_id payroll_action_id, ppa.effective_date effective_date, ppa.date_earned date_earned, xppa.effective_date xeffective_date, prt.run_type_id run_type_id, prt.run_type_name run_type_name, paa.tax_unit_id tax_unit_id, xpaa.assignment_action_id xassignment_action_id, xppa.payroll_action_id xpayroll_action_id, pliv1.element_link_id element_link_id, epiv1.input_value_id einput_value_id1, epiv2.input_value_id einput_value_id2, epiv3.input_value_id einput_value_id3, epiv4.input_value_id einput_value_id4, epiv5.input_value_id einput_value_id5 FROM pay_link_input_values_f pliv5, pay_link_input_values_f pliv4, pay_link_input_values_f pliv3, pay_link_input_values_f pliv2, pay_link_input_values_f pliv1, pay_input_values_f epiv5, pay_input_values_f epiv4, pay_input_values_f epiv3, pay_input_values_f epiv2, pay_input_values_f epiv1, pay_element_types_f epet, pay_payroll_actions xppa, pay_assignment_actions xpaa, pay_action_interlocks pai, pay_run_types_f prt, pay_payroll_actions ppa, pay_assignment_actions paa, pay_element_links_f pel WHERE 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 pai.locked_action_id = paa.assignment_action_id AND xpaa.assignment_action_id = pai.locking_action_id AND xpaa.action_status = 'C' AND xppa.payroll_action_id = xpaa.payroll_action_id AND xppa.action_type = 'X' AND pel.business_group_id = ppa.business_group_id AND(ppa.payroll_id = pel.payroll_id OR pel.payroll_id IS NULL) AND epet.element_name = 'PREV_ER_INFO' AND epet.legislation_code = 'KR' AND epiv1.element_type_id = epet.element_type_id AND epiv1.name = 'BP_NUMBER' AND ppa.effective_date BETWEEN epiv1.effective_start_date AND epiv1.effective_end_date AND epiv2.element_type_id = epiv1.element_type_id AND epiv2.name = 'BP_NAME' AND ppa.effective_date BETWEEN epiv2.effective_start_date AND epiv2.effective_end_date AND epiv3.element_type_id = epiv1.element_type_id AND epiv3.name = 'SEP_PAY' AND ppa.effective_date BETWEEN epiv3.effective_start_date AND epiv3.effective_end_date AND epiv4.element_type_id = epiv1.element_type_id AND epiv4.name = 'SEP_INS' AND ppa.effective_date BETWEEN epiv4.effective_start_date AND epiv4.effective_end_date AND epiv5.element_type_id = epiv1.element_type_id AND epiv5.name = 'SP_SEP_ALW' AND ppa.effective_date BETWEEN epiv5.effective_start_date AND epiv5.effective_end_date AND pliv1.input_value_id(+) = epiv1.input_value_id AND pliv1.element_link_id = pel.element_link_id AND ppa.date_earned BETWEEN nvl(pliv1.effective_start_date, ppa.date_earned) AND nvl(pliv1.effective_end_date, ppa.date_earned) AND pliv2.input_value_id(+) = epiv2.input_value_id AND pliv2.element_link_id = pel.element_link_id AND ppa.date_earned BETWEEN nvl(pliv2.effective_start_date, ppa.date_earned) AND nvl(pliv2.effective_end_date, ppa.date_earned) AND pliv3.input_value_id(+) = epiv3.input_value_id AND pliv3.element_link_id = pel.element_link_id AND ppa.date_earned BETWEEN nvl(pliv3.effective_start_date, ppa.date_earned) AND nvl(pliv3.effective_end_date, ppa.date_earned) AND pliv4.input_value_id(+) = epiv4.input_value_id AND pliv4.element_link_id = pel.element_link_id AND ppa.date_earned BETWEEN nvl(pliv4.effective_start_date, ppa.date_earned) AND nvl(pliv4.effective_end_date, ppa.date_earned) AND pliv5.input_value_id(+) = epiv5.input_value_id AND pliv5.element_link_id = pel.element_link_id AND ppa.date_earned BETWEEN nvl(pliv5.effective_start_date, ppa.date_earned) AND nvl(pliv5.effective_end_date, ppa.date_earned)) sep WHERE pee.assignment_id(+) = sep.assignment_id AND sep.date_earned BETWEEN pee.effective_start_date(+) AND pee.effective_end_date(+) AND pee.entry_type(+) = 'E' AND pee.element_link_id(+) = sep.element_link_id) sep2 WHERE peev1.element_entry_id(+) = sep2.element_entry_id AND peev1.input_value_id(+) = sep2.einput_value_id1 AND peev2.element_entry_id(+) = sep2.element_entry_id AND peev2.input_value_id(+) = sep2.einput_value_id2 AND peev3.element_entry_id(+) = sep2.element_entry_id AND peev3.input_value_id(+) = sep2.einput_value_id3 AND peev4.element_entry_id(+) = sep2.element_entry_id AND peev4.input_value_id(+) = sep2.einput_value_id4 AND peev5.element_entry_id(+) = sep2.element_entry_id AND peev5.input_value_id(+) = sep2.einput_value_id5 AND pa.assignment_id = sep2.assignment_id AND sep2.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date AND pp.person_id = pa.person_id AND sep2.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date AND bhoi.organization_id = sep2.tax_unit_id AND bhoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION' AND bhou.organization_id = bhoi.organization_id AND bhl.location_id(+) = bhou.location_id AND bhl.style(+) = 'KR' AND choi.organization_id = to_number(bhoi.org_information10) AND choi.org_information_context = 'KR_CORPORATE_INFORMATION' AND thoi.organization_id = bhoi.organization_id AND thoi.org_information_context = 'KR_INCOME_TAX_OFFICE' AND pp.country_of_birth = ft.territory_code(+) AND ppos.period_of_service_id = pa.period_of_service_id

View Text - HTML Formatted

SELECT SEP2.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, SEP2.PAYROLL_ID PAYROLL_ID
, SEP2.ASSIGNMENT_ID ASSIGNMENT_ID
, SEP2.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, SEP2.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, SEP2.EFFECTIVE_DATE EFFECTIVE_DATE
, SEP2.DATE_EARNED DATE_EARNED
, SEP2.XEFFECTIVE_DATE XEFFECTIVE_DATE
, SEP2.RUN_TYPE_ID RUN_TYPE_ID
, SEP2.RUN_TYPE_NAME RUN_TYPE_NAME
, SEP2.TAX_UNIT_ID TAX_UNIT_ID
, SEP2.XASSIGNMENT_ACTION_ID XASSIGNMENT_ACTION_ID
, SEP2.XPAYROLL_ACTION_ID XPAYROLL_ACTION_ID
, SEP2.ELEMENT_ENTRY_ID PREV_ELEMENT_ENTRY_ID
, PEEV1.SCREEN_ENTRY_VALUE PREV_BP_NUMBER
, PEEV2.SCREEN_ENTRY_VALUE PREV_BP_NAME
, PEEV3.SCREEN_ENTRY_VALUE PREV_SEP_PAY
, PEEV4.SCREEN_ENTRY_VALUE PREV_SEP_INS
, PEEV5.SCREEN_ENTRY_VALUE PREV_SP_SEP_ALW
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR(SEP2.ASSIGNMENT_ACTION_ID
, SEP2.BUSINESS_GROUP_ID
, 'PREV_ER_INFO'
, 'ELIGIBLE_POST_TAX_DEDUC_FLAG') PREV_ELIGIBLE_FOR_PTAX
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_NUMBER(SEP2.ASSIGNMENT_ACTION_ID
, SEP2.BUSINESS_GROUP_ID
, 'PREV_ER_INFO'
, 'NON TAXABLE EARNING') PREV_SP_NON_TAX_AMT
, BHOI.ORG_INFORMATION2 BP_NUMBER
, CHOI.ORG_INFORMATION1 CORP_NAME
, CHOI.ORG_INFORMATION6 CORP_REPRESENTATIVE_NAME
, CHOI.ORG_INFORMATION2 CORP_NUMBER
, PAY_KR_SEP_FORM_PKG.GET_KR_D_ADDRESS_LINE1(BHL.LOC_INFORMATION14) || ' ' || BHL.ADDRESS_LINE_2 BP_ADDRESS
, THOI.ORG_INFORMATION1 TAX_OFFICE_NAME
, PP.LAST_NAME LAST_NAME
, PP.FIRST_NAME FIRST_NAME
, PP.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PP.NATIONALITY NATIONALITY
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(SEP2.ASSIGNMENT_ACTION_ID
, SEP2.BUSINESS_GROUP_ID
, 'WKPD'
, 'H_DATE') HIRING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(SEP2.ASSIGNMENT_ACTION_ID
, SEP2.BUSINESS_GROUP_ID
, 'WKPD'
, 'L_DATE') LEAVING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(SEP2.ASSIGNMENT_ACTION_ID
, SEP2.BUSINESS_GROUP_ID
, 'WKPD'
, 'PREV_FH_DATE') PREV_FIRST_HIRING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(SEP2.ASSIGNMENT_ACTION_ID
, SEP2.BUSINESS_GROUP_ID
, 'WKPD'
, 'PREV_LL_DATE') PREV_LAST_LEAVING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_NUMBER(SEP2.ASSIGNMENT_ACTION_ID
, SEP2.BUSINESS_GROUP_ID
, 'SEP_PAY_OVERSEAS_TAX_BREAK'
, 'OVERSEAS_TAX_BREAK') SEP_PAY_OVERSEAS_TAX_BREAK
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_NUMBER(SEP2.ASSIGNMENT_ACTION_ID
, SEP2.BUSINESS_GROUP_ID
, 'NON_STAT_SEP_OVERSEAS_TAX_BRK'
, 'OVERSEAS_TAX_BREAK') NON_STAT_SEP_OVERSEAS_TAX_BRK
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_NUMBER(SEP2.ASSIGNMENT_ACTION_ID
, SEP2.BUSINESS_GROUP_ID
, 'STAT_SEP_PAY_POST_TAX_DEDUC'
, 'POST_TAX_DEDUCTION') STAT_SEP_PAY_POST_TAX_DEDUC
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_NUMBER(SEP2.ASSIGNMENT_ACTION_ID
, SEP2.BUSINESS_GROUP_ID
, 'NON_STAT_SEP_PAY_POST_TAX_DEDUC'
, 'POST_TAX_DEDUCTION') NON_STAT_SEP_POST_TAX_DEDUC
, FT.TERRITORY_CODE COUNTRY_CODE
, FT.TERRITORY_SHORT_NAME COUNTRY
, PPOS.ACTUAL_TERMINATION_DATE ACTUAL_TERMINATION_DATE
, PP.PERSON_ID PERSON_ID
FROM HR_ORGANIZATION_INFORMATION THOI
, HR_ORGANIZATION_INFORMATION CHOI
, HR_LOCATIONS BHL
, HR_ORGANIZATION_UNITS BHOU
, HR_ORGANIZATION_INFORMATION BHOI
, PER_PEOPLE_F PP
, PER_ASSIGNMENTS_F PA
, PER_PERIODS_OF_SERVICE PPOS
, FND_TERRITORIES_VL FT
, PAY_ELEMENT_ENTRY_VALUES_F PEEV5
, PAY_ELEMENT_ENTRY_VALUES_F PEEV4
, PAY_ELEMENT_ENTRY_VALUES_F PEEV3
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
, PAY_ELEMENT_ENTRY_VALUES_F PEEV1
, (SELECT SEP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, SEP.PAYROLL_ID PAYROLL_ID
, SEP.ASSIGNMENT_ID ASSIGNMENT_ID
, SEP.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, SEP.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, SEP.EFFECTIVE_DATE EFFECTIVE_DATE
, SEP.DATE_EARNED DATE_EARNED
, SEP.XEFFECTIVE_DATE XEFFECTIVE_DATE
, SEP.RUN_TYPE_ID RUN_TYPE_ID
, SEP.RUN_TYPE_NAME RUN_TYPE_NAME
, SEP.TAX_UNIT_ID TAX_UNIT_ID
, SEP.XPAYROLL_ACTION_ID XPAYROLL_ACTION_ID
, SEP.XASSIGNMENT_ACTION_ID XASSIGNMENT_ACTION_ID
, PEE.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID
, SEP.EINPUT_VALUE_ID1 EINPUT_VALUE_ID1
, SEP.EINPUT_VALUE_ID2 EINPUT_VALUE_ID2
, SEP.EINPUT_VALUE_ID3 EINPUT_VALUE_ID3
, SEP.EINPUT_VALUE_ID4 EINPUT_VALUE_ID4
, SEP.EINPUT_VALUE_ID5 EINPUT_VALUE_ID5
FROM PAY_ELEMENT_ENTRIES_F PEE
, (SELECT PPA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PPA.PAYROLL_ID PAYROLL_ID
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PPA.DATE_EARNED DATE_EARNED
, XPPA.EFFECTIVE_DATE XEFFECTIVE_DATE
, PRT.RUN_TYPE_ID RUN_TYPE_ID
, PRT.RUN_TYPE_NAME RUN_TYPE_NAME
, PAA.TAX_UNIT_ID TAX_UNIT_ID
, XPAA.ASSIGNMENT_ACTION_ID XASSIGNMENT_ACTION_ID
, XPPA.PAYROLL_ACTION_ID XPAYROLL_ACTION_ID
, PLIV1.ELEMENT_LINK_ID ELEMENT_LINK_ID
, EPIV1.INPUT_VALUE_ID EINPUT_VALUE_ID1
, EPIV2.INPUT_VALUE_ID EINPUT_VALUE_ID2
, EPIV3.INPUT_VALUE_ID EINPUT_VALUE_ID3
, EPIV4.INPUT_VALUE_ID EINPUT_VALUE_ID4
, EPIV5.INPUT_VALUE_ID EINPUT_VALUE_ID5
FROM PAY_LINK_INPUT_VALUES_F PLIV5
, PAY_LINK_INPUT_VALUES_F PLIV4
, PAY_LINK_INPUT_VALUES_F PLIV3
, PAY_LINK_INPUT_VALUES_F PLIV2
, PAY_LINK_INPUT_VALUES_F PLIV1
, PAY_INPUT_VALUES_F EPIV5
, PAY_INPUT_VALUES_F EPIV4
, PAY_INPUT_VALUES_F EPIV3
, PAY_INPUT_VALUES_F EPIV2
, PAY_INPUT_VALUES_F EPIV1
, PAY_ELEMENT_TYPES_F EPET
, PAY_PAYROLL_ACTIONS XPPA
, PAY_ASSIGNMENT_ACTIONS XPAA
, PAY_ACTION_INTERLOCKS PAI
, PAY_RUN_TYPES_F PRT
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ELEMENT_LINKS_F PEL
WHERE 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 PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND XPAA.ASSIGNMENT_ACTION_ID = PAI.LOCKING_ACTION_ID
AND XPAA.ACTION_STATUS = 'C'
AND XPPA.PAYROLL_ACTION_ID = XPAA.PAYROLL_ACTION_ID
AND XPPA.ACTION_TYPE = 'X'
AND PEL.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID AND(PPA.PAYROLL_ID = PEL.PAYROLL_ID OR PEL.PAYROLL_ID IS NULL)
AND EPET.ELEMENT_NAME = 'PREV_ER_INFO'
AND EPET.LEGISLATION_CODE = 'KR'
AND EPIV1.ELEMENT_TYPE_ID = EPET.ELEMENT_TYPE_ID
AND EPIV1.NAME = 'BP_NUMBER'
AND PPA.EFFECTIVE_DATE BETWEEN EPIV1.EFFECTIVE_START_DATE
AND EPIV1.EFFECTIVE_END_DATE
AND EPIV2.ELEMENT_TYPE_ID = EPIV1.ELEMENT_TYPE_ID
AND EPIV2.NAME = 'BP_NAME'
AND PPA.EFFECTIVE_DATE BETWEEN EPIV2.EFFECTIVE_START_DATE
AND EPIV2.EFFECTIVE_END_DATE
AND EPIV3.ELEMENT_TYPE_ID = EPIV1.ELEMENT_TYPE_ID
AND EPIV3.NAME = 'SEP_PAY'
AND PPA.EFFECTIVE_DATE BETWEEN EPIV3.EFFECTIVE_START_DATE
AND EPIV3.EFFECTIVE_END_DATE
AND EPIV4.ELEMENT_TYPE_ID = EPIV1.ELEMENT_TYPE_ID
AND EPIV4.NAME = 'SEP_INS'
AND PPA.EFFECTIVE_DATE BETWEEN EPIV4.EFFECTIVE_START_DATE
AND EPIV4.EFFECTIVE_END_DATE
AND EPIV5.ELEMENT_TYPE_ID = EPIV1.ELEMENT_TYPE_ID
AND EPIV5.NAME = 'SP_SEP_ALW'
AND PPA.EFFECTIVE_DATE BETWEEN EPIV5.EFFECTIVE_START_DATE
AND EPIV5.EFFECTIVE_END_DATE
AND PLIV1.INPUT_VALUE_ID(+) = EPIV1.INPUT_VALUE_ID
AND PLIV1.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID
AND PPA.DATE_EARNED BETWEEN NVL(PLIV1.EFFECTIVE_START_DATE
, PPA.DATE_EARNED)
AND NVL(PLIV1.EFFECTIVE_END_DATE
, PPA.DATE_EARNED)
AND PLIV2.INPUT_VALUE_ID(+) = EPIV2.INPUT_VALUE_ID
AND PLIV2.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID
AND PPA.DATE_EARNED BETWEEN NVL(PLIV2.EFFECTIVE_START_DATE
, PPA.DATE_EARNED)
AND NVL(PLIV2.EFFECTIVE_END_DATE
, PPA.DATE_EARNED)
AND PLIV3.INPUT_VALUE_ID(+) = EPIV3.INPUT_VALUE_ID
AND PLIV3.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID
AND PPA.DATE_EARNED BETWEEN NVL(PLIV3.EFFECTIVE_START_DATE
, PPA.DATE_EARNED)
AND NVL(PLIV3.EFFECTIVE_END_DATE
, PPA.DATE_EARNED)
AND PLIV4.INPUT_VALUE_ID(+) = EPIV4.INPUT_VALUE_ID
AND PLIV4.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID
AND PPA.DATE_EARNED BETWEEN NVL(PLIV4.EFFECTIVE_START_DATE
, PPA.DATE_EARNED)
AND NVL(PLIV4.EFFECTIVE_END_DATE
, PPA.DATE_EARNED)
AND PLIV5.INPUT_VALUE_ID(+) = EPIV5.INPUT_VALUE_ID
AND PLIV5.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID
AND PPA.DATE_EARNED BETWEEN NVL(PLIV5.EFFECTIVE_START_DATE
, PPA.DATE_EARNED)
AND NVL(PLIV5.EFFECTIVE_END_DATE
, PPA.DATE_EARNED)) SEP
WHERE PEE.ASSIGNMENT_ID(+) = SEP.ASSIGNMENT_ID
AND SEP.DATE_EARNED BETWEEN PEE.EFFECTIVE_START_DATE(+)
AND PEE.EFFECTIVE_END_DATE(+)
AND PEE.ENTRY_TYPE(+) = 'E'
AND PEE.ELEMENT_LINK_ID(+) = SEP.ELEMENT_LINK_ID) SEP2
WHERE PEEV1.ELEMENT_ENTRY_ID(+) = SEP2.ELEMENT_ENTRY_ID
AND PEEV1.INPUT_VALUE_ID(+) = SEP2.EINPUT_VALUE_ID1
AND PEEV2.ELEMENT_ENTRY_ID(+) = SEP2.ELEMENT_ENTRY_ID
AND PEEV2.INPUT_VALUE_ID(+) = SEP2.EINPUT_VALUE_ID2
AND PEEV3.ELEMENT_ENTRY_ID(+) = SEP2.ELEMENT_ENTRY_ID
AND PEEV3.INPUT_VALUE_ID(+) = SEP2.EINPUT_VALUE_ID3
AND PEEV4.ELEMENT_ENTRY_ID(+) = SEP2.ELEMENT_ENTRY_ID
AND PEEV4.INPUT_VALUE_ID(+) = SEP2.EINPUT_VALUE_ID4
AND PEEV5.ELEMENT_ENTRY_ID(+) = SEP2.ELEMENT_ENTRY_ID
AND PEEV5.INPUT_VALUE_ID(+) = SEP2.EINPUT_VALUE_ID5
AND PA.ASSIGNMENT_ID = SEP2.ASSIGNMENT_ID
AND SEP2.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PP.PERSON_ID = PA.PERSON_ID
AND SEP2.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND BHOI.ORGANIZATION_ID = SEP2.TAX_UNIT_ID
AND BHOI.ORG_INFORMATION_CONTEXT = 'KR_BUSINESS_PLACE_REGISTRATION'
AND BHOU.ORGANIZATION_ID = BHOI.ORGANIZATION_ID
AND BHL.LOCATION_ID(+) = BHOU.LOCATION_ID
AND BHL.STYLE(+) = 'KR'
AND CHOI.ORGANIZATION_ID = TO_NUMBER(BHOI.ORG_INFORMATION10)
AND CHOI.ORG_INFORMATION_CONTEXT = 'KR_CORPORATE_INFORMATION'
AND THOI.ORGANIZATION_ID = BHOI.ORGANIZATION_ID
AND THOI.ORG_INFORMATION_CONTEXT = 'KR_INCOME_TAX_OFFICE'
AND PP.COUNTRY_OF_BIRTH = FT.TERRITORY_CODE(+)
AND PPOS.PERIOD_OF_SERVICE_ID = PA.PERIOD_OF_SERVICE_ID