View: PAY_KR_SEP_SLIP_V

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

View Text

SELECT SEP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, SEP.LEGISLATION_CODE LEGISLATION_CODE
, SEP.PAYROLL_ID PAYROLL_ID
, SEP.ASSIGNMENT_ID ASSIGNMENT_ID
, SEP.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, SEP.EFFECTIVE_DATE EFFECTIVE_DATE
, SEP.DATE_EARNED DATE_EARNED
, SEP.RUN_TYPE_ID RUN_TYPE_ID
, SEP.RUN_TYPE_NAME RUN_TYPE_NAME
, SEP.TAX_UNIT_ID TAX_UNIT_ID
, SEP.XASSIGNMENT_ACTION_ID XASSIGNMENT_ACTION_ID
, SEP.LAST_NAME LAST_NAME
, SEP.FIRST_NAME FIRST_NAME
, SEP.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, SEP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, SEP.ORGANIZATION_NAME ORGANIZATION_NAME
, SEP.KR_JOB_TITLE KR_JOB_TITLE
, SEP.GRADE_NAME GRADE_NAME
, SEP.HIRING_DATE HIRING_DATE
, SEP.LEAVING_DATE LEAVING_DATE
, SEP.WORKING_PERIOD WORKING_PERIOD
, SEP.ME_1ST_STD ME_1ST_STD
, SEP.ME_1ST_EDD ME_1ST_EDD
, SEP.ME_2ND_STD ME_2ND_STD
, SEP.ME_2ND_EDD ME_2ND_EDD
, SEP.ME_3RD_STD ME_3RD_STD
, SEP.ME_3RD_EDD ME_3RD_EDD
, SEP.ME_4TH_STD ME_4TH_STD
, SEP.ME_4TH_EDD ME_4TH_EDD
, SEP.BBALANCE_TYPE_ID BBALANCE_TYPE_ID
, SEP.VBALANCE_TYPE_ID VBALANCE_TYPE_ID
, SEP.IVBALANCE_TYPE_ID IVBALANCE_TYPE_ID
, BON.BASSIGNMENT_ACTION_ID BASSIGNMENT_ACTION_ID
, BON.BEFFECTIVE_DATE BEFFECTIVE_DATE
, BON.BRUN_TYPE_ID BRUN_TYPE_ID
, BON.BRUN_TYPE_NAME BRUN_TYPE_NAME
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE_ASG_RUN(BON.BASSIGNMENT_ACTION_ID
, SEP.BBALANCE_TYPE_ID) BON_AMT
, SEP.PERSON_ID
FROM (SELECT BPAA.ASSIGNMENT_ID BASSIGNMENT_ID
, BPPA.EFFECTIVE_DATE BEFFECTIVE_DATE
, BPAA.ASSIGNMENT_ACTION_ID BASSIGNMENT_ACTION_ID
, BPRT.RUN_TYPE_ID BRUN_TYPE_ID
, BPRT.RUN_TYPE_NAME BRUN_TYPE_NAME FROM PAY_RUN_TYPES_F BPRT
, PAY_PAYROLL_ACTIONS BPPA
, PAY_ASSIGNMENT_ACTIONS BPAA
WHERE BPAA.SOURCE_ACTION_ID IS NOT NULL AND BPAA.ACTION_STATUS = 'C' AND BPPA.PAYROLL_ACTION_ID = BPAA.PAYROLL_ACTION_ID AND BPRT.RUN_TYPE_ID = BPAA.RUN_TYPE_ID AND BPPA.EFFECTIVE_DATE BETWEEN BPRT.EFFECTIVE_START_DATE
AND BPRT.EFFECTIVE_END_DATE AND SUBSTR(BPRT.RUN_TYPE_NAME
, 1
, 3) = 'BON' AND BPRT.RUN_TYPE_NAME <> 'BON_ALR') BON
, (SELECT PPA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PPA.PAYROLL_ID PAYROLL_ID
, PAY_KR_REPORT_PKG.LEGISLATION_CODE(PPA.BUSINESS_GROUP_ID) LEGISLATION_CODE
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PPA.DATE_EARNED DATE_EARNED
, 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
, PP.LAST_NAME LAST_NAME
, PP.FIRST_NAME FIRST_NAME
, PP.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, HOU.NAME ORGANIZATION_NAME
, HR_GENERAL.DECODE_LOOKUP('KR_JOB_TITLE'
, HSCK.SEGMENT2) KR_JOB_TITLE
, PGT.NAME GRADE_NAME
, BPBT.BALANCE_TYPE_ID BBALANCE_TYPE_ID
, VPBT.BALANCE_TYPE_ID VBALANCE_TYPE_ID
, IVPBT.BALANCE_TYPE_ID IVBALANCE_TYPE_ID
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'H_DATE') HIRING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'L_DATE') LEAVING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'WKPD') WORKING_PERIOD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_1ST_STD') ME_1ST_STD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_1ST_EDD') ME_1ST_EDD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_2ND_STD') ME_2ND_STD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_2ND_EDD') ME_2ND_EDD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_3RD_STD') ME_3RD_STD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_3RD_EDD') ME_3RD_EDD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG_ADD'
, 'ME_4TH_STD') ME_4TH_STD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG_ADD'
, 'ME_4TH_EDD') ME_4TH_EDD
, PP.PERSON_ID FROM PAY_BALANCE_TYPES IVPBT
, PAY_BALANCE_TYPES VPBT
, PAY_BALANCE_TYPES BPBT
, PER_GRADES_TL PGT
, HR_SOFT_CODING_KEYFLEX HSCK
, HR_ORGANIZATION_UNITS HOU
, PER_PEOPLE_F PP
, PER_ASSIGNMENTS_F PA
, 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 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 PA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PPA.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE AND PP.PERSON_ID = PA.PERSON_ID AND PPA.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE AND HOU.ORGANIZATION_ID = PA.ORGANIZATION_ID AND HSCK.SOFT_CODING_KEYFLEX_ID(+) = PA.SOFT_CODING_KEYFLEX_ID AND PGT.GRADE_ID(+) = PA.GRADE_ID
AND PGT.LANGUAGE (+) = USERENV('LANG') AND BPBT.BALANCE_NAME = 'EARNINGS_SUBJ_AVG' AND BPBT.LEGISLATION_CODE = 'KR' AND VPBT.BALANCE_NAME = 'VOL_DEDUCTIONS' AND VPBT.LEGISLATION_CODE = 'KR' AND IVPBT.BALANCE_NAME = 'INVOL_DEDUCTIONS' AND IVPBT.LEGISLATION_CODE = 'KR'
AND BPBT.LEGISLATION_CODE = VPBT.LEGISLATION_CODE AND VPBT.LEGISLATION_CODE = IVPBT.LEGISLATION_CODE ) SEP
WHERE BON.BASSIGNMENT_ID (+) = SEP.ASSIGNMENT_ID AND BON.BEFFECTIVE_DATE (+) BETWEEN ADD_MONTHS(SEP.EFFECTIVE_DATE -1
, -12)
AND (SEP.EFFECTIVE_DATE -1)

Columns