View: PAY_KR_SEP_HISTORY_V

Product:	PAY - Payroll
Description:	This view returns Separation Pay Process history based on assignment_id.
Implementation/DBA Data:	APPS.PAY_KR_SEP_HISTORY_V

View Text

SELECT PPA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PAY_KR_REPORT_PKG.LEGISLATION_CODE(PPA.BUSINESS_GROUP_ID) LEGISLATION_CODE
, PP.PERSON_ID PERSON_ID
, PP.FULL_NAME FULL_NAME
, PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PP.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PA.ASSIGNMENT_ID ASSIGNMENT_ID
, PA.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, DECODE(PPA.ACTION_TYPE
, 'X'
, PAA.ASSIGNMENT_ACTION_ID
, NVL(PAA.SOURCE_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID)) SOURCE_ACTION_ID
, NVL(PAA.RUN_TYPE_ID
, PPA.RUN_TYPE_ID) RUN_TYPE_ID
, PAY_KR_SEP_FORM_PKG.GET_RUN_TYPE_NAME(NVL(PAA.RUN_TYPE_ID
, PPA.RUN_TYPE_ID)
, PPA.EFFECTIVE_DATE) RUN_TYPE_NAME
, PAA.TAX_UNIT_ID TAX_UNIT_ID
, PAA.ACTION_STATUS ACTION_STATUS_CODE
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS(PAA.ASSIGNMENT_ACTION_ID
, PPA.ACTION_TYPE
, PAA.ACTION_STATUS) ACTION_STATUS
, PAA.ACTION_SEQUENCE ACTION_SEQUENCE
, PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PPA.DATE_EARNED DATE_EARNED
, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PPA.ACTION_TYPE) ACTION_TYPE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAA.PAYROLL_ACTION_ID) PERIOD_NAME
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'H_DATE') H_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'L_DATE') L_DATE
FROM PER_PEOPLE_F PP
, PER_ASSIGNMENTS_F PA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND ( ( PPA.ACTION_TYPE IN ('R'
, 'Q')
AND EXISTS ( SELECT NULL
FROM PAY_RUN_TYPES_F SPRT
WHERE SPRT.RUN_TYPE_ID = NVL(PAA.RUN_TYPE_ID
, PPA.RUN_TYPE_ID)
AND PPA.EFFECTIVE_DATE BETWEEN SPRT.EFFECTIVE_START_DATE
AND SPRT.EFFECTIVE_END_DATE
AND SPRT.RUN_TYPE_NAME LIKE 'SEP%' )
AND PAA.SOURCE_ACTION_ID IS NOT NULL ) OR ( PPA.ACTION_TYPE = 'X'
AND EXISTS ( SELECT NULL
FROM PAY_RUN_TYPES_F XSPRT
, PAY_ASSIGNMENT_ACTIONS XSPAA
, PAY_ACTION_INTERLOCKS XPAI
WHERE XPAI.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND XSPAA.ASSIGNMENT_ACTION_ID = XPAI.LOCKED_ACTION_ID
AND XSPRT.RUN_TYPE_ID = XSPAA.RUN_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN XSPRT.EFFECTIVE_START_DATE
AND XSPRT.EFFECTIVE_END_DATE
AND XSPRT.RUN_TYPE_NAME LIKE 'SEP%' ) ) )
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

Columns