View: PAY_AU_ASG_ELEMENT_PAYMENTS_V

Product:	PAY - Payroll
Description:	Australian localisation view.
Implementation/DBA Data:	APPS.PAY_AU_ASG_ELEMENT_PAYMENTS_V

View Text

SELECT ROW_ID
, LOCKING_ACTION_ID
, CLASSIFICATION_NAME
, ELEMENT_REPORTING_NAME
, PROCESSING_PRIORITY
, SUM(PAYMENT) PAYMENT
, SUM(HOURS) HOURS
, TRUNC(RATE
, 2) RATE /* 5593548 */
FROM ( SELECT DISTINCT *
FROM ( SELECT PET.ROWID ROW_ID
, PRR.ROWID PRR_ROW_ID
, PAI.LOCKING_ACTION_ID
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, DECODE(PEC2.CLASSIFICATION_NAME
, 'NON TAXABLE'
, 'NON TAXABLE EARNINGS'
, 'TAXABLE EARNINGS')) || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'PAYMENTS')
, 0
, NULL
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DIRECT')
, 0
, 'TAXABLE EARNINGS'
, 'DIRECT PAYMENTS')) || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, DECODE(PEC.CLASSIFICATION_NAME
, 'TERMINATION DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'POST TAX DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'POST TAX DEDUCTIONS'
, PEC.CLASSIFICATION_NAME )) || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER CHARGES')
, 0
, NULL
, DECODE(PEC2.CLASSIFICATION_NAME
, 'EMPLOYER SUPERANNUATION CONTRIBUTION'
, 'EMPLOYER SUPERANNUATION CONTRIBUTIONS')) CLASSIFICATION_NAME
, DECODE(PEC2.CLASSIFICATION_NAME
, 'EMPLOYER SUPERANNUATION CONTRIBUTION'
, PAY_AU_SOE_PKG.SUPER_FUND_NAME(PRR.SOURCE_ID
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, PPA1.DATE_EARNED
, PRR.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PRR.ELEMENT_ENTRY_ID
, PAA.BUSINESS_GROUP_ID)
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)) ELEMENT_REPORTING_NAME
, DECODE(PEC2.CLASSIFICATION_NAME
, 'EMPLOYER SUPERANNUATION CONTRIBUTION'
, '3250'
, PET.PROCESSING_PRIORITY) PROCESSING_PRIORITY
, DECODE(PIV.NAME
, 'PAY VALUE'
, PRV.RESULT_VALUE
, NULL) PAYMENT
, PAY_AU_SOE_PKG.GET_ELEMENT_PAYMENT_HOURS(PRR.ASSIGNMENT_ACTION_ID
, PET.ELEMENT_TYPE_ID
, PAA.PAY_BASIS_ID
, PRR.RUN_RESULT_ID
, PPA.EFFECTIVE_DATE) HOURS /* 5591333 */
, DECODE(PAY_AU_SOE_PKG.GET_ELEMENT_PAYMENT_RATE(PRR.ASSIGNMENT_ACTION_ID
, PET.ELEMENT_TYPE_ID
, PRR.RUN_RESULT_ID
, PPA.EFFECTIVE_DATE)
, NULL
, (PRV.RESULT_VALUE/PAY_AU_SOE_PKG.GET_ELEMENT_PAYMENT_HOURS(PRR.ASSIGNMENT_ACTION_ID
, PET.ELEMENT_TYPE_ID
, PAA.PAY_BASIS_ID
, PRR.RUN_RESULT_ID
, PPA.EFFECTIVE_DATE))
, PAY_AU_SOE_PKG.GET_ELEMENT_PAYMENT_RATE(PRR.ASSIGNMENT_ACTION_ID
, PET.ELEMENT_TYPE_ID
, PRR.RUN_RESULT_ID
, PPA.EFFECTIVE_DATE)) RATE /* 5599310 */
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAC
, PER_ASSIGNMENTS_F PAA
, PAY_ACTION_INTERLOCKS PAI
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRV
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_CLASSIFICATIONS PEC2
, PAY_SUB_CLASSIFICATION_RULES_F PSCR
, PAY_INPUT_VALUES_F PIV
, PAY_PAYROLL_ACTIONS PPA1
, /* BUG 5983711*/ PAY_ASSIGNMENT_ACTIONS PAC1/* BUG 5983711*/
WHERE PPA.ACTION_TYPE IN ('P'
, 'U')
AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAI.LOCKING_ACTION_ID = PAC.ASSIGNMENT_ACTION_ID
AND PPA1.ACTION_TYPE IN ('R'
, 'Q')/* BUG 5983711*/
AND PAC1.ASSIGNMENT_ACTION_ID=PRR.ASSIGNMENT_ACTION_ID
AND PPA1.PAYROLL_ACTION_ID = PAC1.PAYROLL_ACTION_ID/* BUG 5983711*/
AND PAI.LOCKED_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PAA.ASSIGNMENT_ID = PAC.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PET.ELEMENT_TYPE_ID = PSCR.ELEMENT_TYPE_ID (+)
AND PPA.EFFECTIVE_DATE BETWEEN NVL(PSCR.EFFECTIVE_START_DATE
, PPA.EFFECTIVE_DATE)
AND NVL(PSCR.EFFECTIVE_END_DATE
, PPA.EFFECTIVE_DATE)
AND PSCR.CLASSIFICATION_ID = PEC2.CLASSIFICATION_ID(+)
AND PIV.INPUT_VALUE_ID = PRV.INPUT_VALUE_ID
AND PIV.NAME = 'PAY VALUE' /* BUG 5461557 */
AND PRR.RUN_RESULT_ID = PRV.RUN_RESULT_ID
AND (PEC.LEGISLATION_CODE IS NULL OR PEC.LEGISLATION_CODE = 'AU')
AND PRR.STATUS IN ('P'
, 'PA')
AND (INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'PAYMENTS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER CHARGES') > 0) ) ) GROUP BY ROW_ID
, LOCKING_ACTION_ID
, CLASSIFICATION_NAME
, ELEMENT_REPORTING_NAME
, PROCESSING_PRIORITY
, TRUNC(RATE
, 2)

Columns