Product: | PAY - Payroll |
---|---|
Description: | Australian localisation view. |
Implementation/DBA Data: | APPS.PAY_AU_ASG_PAYMENT_RUNS_V |
SELECT /*+ ORDERED */ DISTINCT PAC.ROWID ROW_ID
, PAC.ASSIGNMENT_ID
, PAC.ASSIGNMENT_ACTION_ID
, PPE.PERSON_ID
, PPE.FULL_NAME
, PPE.ORDER_NAME
, PPE.TITLE
, SUBSTR(PPE.FIRST_NAME
, 1
, 1)|| ' ' ||SUBSTR(PPE.MIDDLE_NAMES
, 1
, 1) INITIALS
, PPE.LAST_NAME
, PAA.ASSIGNMENT_NUMBER
, POSTL.NAME POSITION_TITLE
, JOBTL.NAME JOB
, PAY_AU_SOE_PKG.GET_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
, HOI.ORG_INFORMATION3 REGISTERED_EMPLOYER
, RPPA.PAYROLL_ID PAYROLL_ID
, PAC.PAYROLL_ACTION_ID
, PTP.TIME_PERIOD_ID TIME_PERIOD_ID
, PTP.PERIOD_NUM PERIOD_NUMBER
, PTP.START_DATE PERIOD_START_DATE
, PTP.END_DATE PERIOD_END_DATE
, NVL(PTP.DEFAULT_DD_DATE
, PPA.EFFECTIVE_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
, HOI.ORG_INFORMATION12 ABN
, 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
, HOU1.NAME
, GDT.NAME GRADE
FROM PAY_PAYROLL_ACTIONS RPPA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS RPAC
, PAY_ASSIGNMENT_ACTIONS PAC
, PER_ASSIGNMENTS_F PAA
, HR_LOCATIONS HLO
, PER_PEOPLE_F PPE
, HR_SOFT_CODING_KEYFLEX HSC
, HR_ALL_POSITIONS_F_TL POSTL
, PAY_PEOPLE_GROUPS PPG
, PAY_ACTION_INTERLOCKS PAI
, PER_JOBS_TL JOBTL
, PER_GRADES_TL GDT
, HR_ORGANIZATION_INFORMATION HOI
, HR_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_UNITS HOU1
, PER_TIME_PERIODS PTP
WHERE PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('U'
, 'P')
AND RPPA.ACTION_TYPE IN ('Q'
, 'R'
, 'B'
, 'I'
, 'V')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ID = RPPA.PAYROLL_ID
AND PAA.ORGANIZATION_ID = HOU1.ORGANIZATION_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 PAA.POSITION_ID = POSTL.POSITION_ID(+)
AND POSTL.LANGUAGE (+) = USERENV('LANG')
AND PAA.JOB_ID = JOBTL.JOB_ID(+)
AND JOBTL.LANGUAGE(+) = USERENV('LANG')
AND PAA.GRADE_ID = GDT.GRADE_ID (+)
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND PPE.PERSON_ID = PAA.PERSON_ID
AND PAA.ASSIGNMENT_ID = PAC.ASSIGNMENT_ID
AND HLO.LOCATION_ID(+) = PAA.LOCATION_ID
AND RPPA.PAYROLL_ID = PTP.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 HOI.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'AU_LEGAL_EMPLOYER'
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)