View: PAY_GB_ASSIGNMENT_ACTIONS_V

Product:	PAY - Payroll
Description:	View for the UK Pay Advice Report based on the PAY_ASSIGNMENT_ACTIONS table.
Implementation/DBA Data:	APPS.PAY_GB_ASSIGNMENT_ACTIONS_V

View Text

SELECT DISTINCT PAA.ROWID ROW_ID
, PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PER.PERSON_ID
, SUBSTR(PAY_GB_PAYROLL_ACTIONS_PKG.GET_SALARY (ASF.PAY_BASIS_ID
, ASF.ASSIGNMENT_ID
, RPPA.DATE_EARNED)
, 1
, 60) SALARY
, SUBSTR(INITCAP(HR_GENERAL.DECODE_PAY_BASIS (ASF.PAY_BASIS_ID))
, 1
, 30) PAY_BASIS
, JBT.NAME JOB_TITLE
, PER.FULL_NAME
, PER.TITLE
, SUBSTR(PER.FIRST_NAME
, 1
, 1)||' '||SUBSTR(PER.MIDDLE_NAMES
, 1
, 1) INITIALS
, PER.LAST_NAME
, ASF.ASSIGNMENT_NUMBER
, ASF.LOCATION_ID
, ASF.INTERNAL_ADDRESS_LINE
, PER.NATIONAL_IDENTIFIER NI_NUMBER
, PER.EXPENSE_CHECK_SEND_TO_ADDRESS
, RPPA.DATE_EARNED DATE_EARNED
, PAA.PAYROLL_ACTION_ID
, RPAA.ASSIGNMENT_ACTION_ID RUN_ASSIGNMENT_ACTION_ID
, RPPA.EFFECTIVE_DATE RUN_EFFECTIVE_DATE
, RPAA.PAYROLL_ACTION_ID RUN_PAYROLL_ACTION_ID
, RPPA.TIME_PERIOD_ID TIME_PERIOD_ID
, RPPA.PAYROLL_ID PAYROLL_ID
, NVL(RPPA.PAY_ADVICE_DATE
, PTP.PAY_ADVICE_DATE) PAY_ADVICE_DATE
, PPG.SEGMENT1
, PPG.SEGMENT2
, PPG.SEGMENT3
, PPG.SEGMENT4
, PPG.SEGMENT5
, PPG.SEGMENT6
, PPG.SEGMENT7
, PPG.SEGMENT8
, PPG.SEGMENT9
, PPG.SEGMENT10
, PPG.SEGMENT11
, PPG.SEGMENT12
, PPG.SEGMENT13
, PPG.SEGMENT14
, PPG.SEGMENT15
, PPG.SEGMENT16
, PPG.SEGMENT17
, PPG.SEGMENT18
, PPG.SEGMENT19
, PPG.SEGMENT20
, PPG.SEGMENT21
, PPG.SEGMENT22
, PPG.SEGMENT23
, PPG.SEGMENT24
, PPG.SEGMENT25
, PPG.SEGMENT26
, PPG.SEGMENT27
, PPG.SEGMENT28
, PPG.SEGMENT29
, PPG.SEGMENT30
FROM PER_JOBS_TL JBT
, PER_ALL_ASSIGNMENTS_F ASF
, PER_PEOPLE_F PER
, PAY_ASSIGNMENT_ACTIONS PAA
, /* PREPAYMENT ASSIGNMENT ACTION */ PAY_PAYROLL_ACTIONS PPA
, /* PREPAYMENT PAYROLL ACTION */ PAY_ASSIGNMENT_ACTIONS RPAA
, /* RUN ASSIGNMENT ACTION */ PAY_PAYROLL_ACTIONS RPPA
, /* RUN PAYROLL ACTION */ PAY_ACTION_INTERLOCKS IL
, PER_TIME_PERIODS PTP
, PAY_PEOPLE_GROUPS PPG
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('U'
, 'P')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ID = RPPA.PAYROLL_ID
AND PPA.EFFECTIVE_DATE >= RPPA.EFFECTIVE_DATE
AND RPAA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPG.PEOPLE_GROUP_ID(+) = ASF.PEOPLE_GROUP_ID
AND JBT.JOB_ID(+) = ASF.JOB_ID
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND PER.PERSON_ID = ASF.PERSON_ID
AND PAA.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID
AND RPPA.DATE_EARNED BETWEEN ASF.EFFECTIVE_START_DATE
AND ASF.EFFECTIVE_END_DATE
AND PTP.TIME_PERIOD_ID = RPPA.TIME_PERIOD_ID
AND RPPA.DATE_EARNED BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND RPPA.PAYROLL_ACTION_ID = RPAA.PAYROLL_ACTION_ID
AND RPAA.ASSIGNMENT_ACTION_ID = IL.LOCKED_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = IL.LOCKING_ACTION_ID
AND IL.ROWID = (SELECT SUBSTR(MAX(LPAD(AA.ACTION_SEQUENCE
, 15
, 0)||LOC.ROWID)
, -18) /* LENGTH OF ROWID */ LATEST_ACT
FROM PAY_ASSIGNMENT_ACTIONS AA
, PAY_ACTION_INTERLOCKS LOC
WHERE LOC.LOCKED_ACTION_ID = AA.ASSIGNMENT_ACTION_ID
AND LOC.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID)

Columns