View: PAY_KR_SEP_TAX_WO_ADDR_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_KR_SEP_TAX_WO_ADDR_V

View Text

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_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
, 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

Columns