View: PAY_SG_ASG_PAYMENT_RUNS_V

Product:	PAY - Payroll
Description:	Singapore localisation view.
Implementation/DBA Data:	APPS.PAY_SG_ASG_PAYMENT_RUNS_V

View Text

SELECT /*+ INDEX(PER_ASSIGNMENTS_F_PK) */ DISTINCT PAC.ROWID ROW_ID
, PAC.ASSIGNMENT_ID
, PAC.ASSIGNMENT_ACTION_ID
, PPE.PERSON_ID
, PPE.FULL_NAME
, PPE.TITLE
, SUBSTR(PPE.FIRST_NAME
, 1
, 1)|| ' ' ||SUBSTR(PPE.MIDDLE_NAMES
, 1
, 1) INITIALS
, PPE.LAST_NAME
, PAA.ASSIGNMENT_NUMBER
, PPO.NAME POSITION_TITLE
, PAY_SG_SOE.CURRENT_SALARY (PAA.PAY_BASIS_ID
, PAA.ASSIGNMENT_ID
, RPPA.DATE_EARNED) SALARY
, HLO.LOCATION_ID
, HLO.LOCATION_CODE LOCATION_CODE
, PAA.INTERNAL_ADDRESS_LINE
, PPE.EXPENSE_CHECK_SEND_TO_ADDRESS
, HOU.BUSINESS_GROUP_ID
, HOU.NAME REGISTERED_EMPLOYER
, RPPA.PAYROLL_ID PAYROLL_ID
, PAC.PAYROLL_ACTION_ID
, RPPA.TIME_PERIOD_ID TIME_PERIOD_ID
, PTP.PERIOD_NUM PERIOD_NUMBER
, PTP.START_DATE PERIOD_START_DATE
, PTP.END_DATE PERIOD_END_DATE
, NVL(RPPA.PAY_ADVICE_DATE
, PTP.PAY_ADVICE_DATE) PAY_ADVICE_DATE
, RPPA.DATE_EARNED DATE_EARNED
, RPAC.ASSIGNMENT_ACTION_ID RUN_ASSIGNMENT_ACTION_ID
, RPAC.PAYROLL_ACTION_ID RUN_PAYROLL_ACTION_ID
, 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 HR_LOCATIONS HLO
, PER_POSITIONS PPO
, PER_ASSIGNMENTS_F PAA
, PER_PEOPLE_F PPE
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS RPAC
, PAY_PAYROLL_ACTIONS RPPA
, PAY_ACTION_INTERLOCKS PAI
, PER_TIME_PERIODS PTP
, PAY_PEOPLE_GROUPS PPG
, HR_SOFT_CODING_KEYFLEX HSC
, HR_ORGANIZATION_UNITS HOU
WHERE PPA.PAYROLL_ACTION_ID = PAC.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 PAC.ASSIGNMENT_ID = RPAC.ASSIGNMENT_ID
AND PAC.ACTION_STATUS = 'C'
AND PPG.PEOPLE_GROUP_ID(+) = PAA.PEOPLE_GROUP_ID
AND PPO.POSITION_ID(+) = PAA.POSITION_ID
AND PPE.PERSON_ID = PAA.PERSON_ID
AND PAA.ASSIGNMENT_ID = PAC.ASSIGNMENT_ID
AND PAA.ASSIGNMENT_ID = RPAC.ASSIGNMENT_ID
AND HLO.LOCATION_ID(+) = PAA.LOCATION_ID
AND PTP.PAYROLL_ID=RPPA.PAYROLL_ID
AND RPPA.DATE_EARNED BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND RPPA.DATE_EARNED BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND RPPA.DATE_EARNED BETWEEN PPE.EFFECTIVE_START_DATE
AND PPE.EFFECTIVE_END_DATE
AND RPPA.PAYROLL_ACTION_ID = RPAC.PAYROLL_ACTION_ID
AND PAI.LOCKING_ACTION_ID = PAC.ASSIGNMENT_ACTION_ID
AND PAI.LOCKED_ACTION_ID = RPAC.ASSIGNMENT_ACTION_ID
AND HSC.SOFT_CODING_KEYFLEX_ID = PAA.SOFT_CODING_KEYFLEX_ID
AND HSC.SEGMENT1 = TO_CHAR(HOU.ORGANIZATION_ID)
AND PAI.ROWID = (SELECT SUBSTR(MAX(LPAD(PAA_LOCKED.ACTION_SEQUENCE
, 15
, 0) ||PAI_LOCKED.ROWID)
, -18) LATEST_ACT
FROM PAY_ASSIGNMENT_ACTIONS PAA_LOCKED
, PAY_ACTION_INTERLOCKS PAI_LOCKED
WHERE PAI_LOCKED.LOCKING_ACTION_ID = PAC.ASSIGNMENT_ACTION_ID
AND PAI_LOCKED.LOCKED_ACTION_ID = PAA_LOCKED.ASSIGNMENT_ACTION_ID)

Columns