View: PAY_GB_NICABLE_V

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

View Text

SELECT MAX(PAPF.FIRST_NAME) FIRST_NAME
, MAX(PAPF.LAST_NAME) LAST_NAME
, PAPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, MAX(DECODE(PIVF1.NAME
, 'BENEFIT DESCRIPTION'
, PRRV1.RESULT_VALUE
, NULL)) BENEFIT_DESCRIPTION
, MAX(DECODE(PIVF1.NAME
, 'AMOUNT'
, PRRV1.RESULT_VALUE
, NULL)) AMOUNT
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.PAYROLL_ID
, MAX(PPA.EFFECTIVE_DATE) EFFECTIVE_DATE
FROM PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV1
, PAY_ELEMENT_TYPES_F PETF
, PAY_INPUT_VALUES_F PIVF1
, PER_ALL_PEOPLE_F PAPF
, PER_ALL_ASSIGNMENTS_F PAAF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ALL_PAYROLLS_F PAPAYF
, FND_SESSIONS FS
WHERE FS.SESSION_ID = USERENV('SESSIONID')
AND PAPAYF.PAYROLL_ID = PPA.PAYROLL_ID
AND FS.EFFECTIVE_DATE BETWEEN PAPAYF.EFFECTIVE_START_DATE
AND PAPAYF.EFFECTIVE_END_DATE
AND PPA.ACTION_TYPE IN ( 'Q'
, 'R'
, 'B'
, 'I')
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND NOT EXISTS( SELECT '1'
FROM PAY_ACTION_INTERLOCKS PAI
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PACT3
WHERE PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAI.LOCKING_ACTION_ID = PAA2.ASSIGNMENT_ACTION_ID
AND PACT3.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
AND PACT3.ACTION_TYPE = 'V'
AND PAA2.ACTION_STATUS = 'C')
AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PAAF.PERSON_ID = PAPF.PERSON_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND PETF.ELEMENT_NAME = 'NIC ABLE BENEFITS'
AND FS.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PIVF1.NAME IN ('AMOUNT'
, 'BENEFIT DESCRIPTION')
AND PIVF1.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND FS.EFFECTIVE_DATE BETWEEN PIVF1.EFFECTIVE_START_DATE
AND PIVF1.EFFECTIVE_END_DATE
AND PRRV1.INPUT_VALUE_ID = PIVF1.INPUT_VALUE_ID
AND PRRV1.RUN_RESULT_ID = PRR.RUN_RESULT_ID GROUP BY PPA.PAYROLL_ID
, EMPLOYEE_NUMBER
, PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID

Columns