View: PAY_AU_SOE_RUN_ELEMENTS_V

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

View Text

SELECT ROW_ID
, ELEMENT_NAME
, REPORT_NAME
, LABEL
, SUM(AMOUNT) AMOUNT
, PROCESSING_PRIORITY
, RUN_ASSIGNMENT_ACTION_ID
, ASSIGNMENT_ID
, SUM(HOURS) HOURS
, TRUNC(RATE
, 2) RATE /* 5593548 */
FROM ( SELECT DISTINCT *
FROM ( SELECT DECODE(PEC2.CLASSIFICATION_NAME
, 'EMPLOYER SUPERANNUATION CONTRIBUTION'
, '-999'
, PET.ROWID) ROW_ID
, DECODE(PEC2.CLASSIFICATION_NAME
, 'EMPLOYER SUPERANNUATION CONTRIBUTION'
, '-999'
, PRR.ROWID) PRR_ROW_ID
, DECODE(PEC2.CLASSIFICATION_NAME
, 'EMPLOYER SUPERANNUATION CONTRIBUTION'
, PAY_AU_SOE_PKG.SUPER_FUND_NAME(PRR.SOURCE_ID
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, PPA.DATE_EARNED
, PAC.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PRR.ELEMENT_ENTRY_ID
, PAA.BUSINESS_GROUP_ID)
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)) ELEMENT_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)
, PPA.DATE_EARNED
, PAC.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PRR.ELEMENT_ENTRY_ID
, PAA.BUSINESS_GROUP_ID)
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)) REPORT_NAME
, 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')) LABEL
, DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'M'
, PRV.RESULT_VALUE
, NULL) AMOUNT
, DECODE(PEC2.CLASSIFICATION_NAME
, 'EMPLOYER SUPERANNUATION CONTRIBUTION'
, '3250'
, PET.PROCESSING_PRIORITY) PROCESSING_PRIORITY
, PAI.LOCKING_ACTION_ID RUN_ASSIGNMENT_ACTION_ID
, PAC.ASSIGNMENT_ID
, PRR.RUN_RESULT_ID
, 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
, PER_ALL_ASSIGNMENTS_F PAA
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRV
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_CLASSIFICATIONS PEC2
, PAY_SUB_CLASSIFICATION_RULES_F PSCR
, PAY_ACTION_INTERLOCKS PAI
WHERE PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.ACTION_STATUS = 'C'
AND PAC.ACTION_STATUS(+) = 'C'
AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAC.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PAC.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
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 PIV.NAME = 'PAY VALUE' /* BUG 5461557 */
AND PIV.INPUT_VALUE_ID = PRV.INPUT_VALUE_ID
AND PRR.RUN_RESULT_ID = PRV.RUN_RESULT_ID
AND (PEC.LEGISLATION_CODE IS NULL OR PEC.LEGISLATION_CODE = 'AU')
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
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 )
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 PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PRR.STATUS IN ('P'
, 'PA') UNION ALL SELECT DECODE(PEC2.CLASSIFICATION_NAME
, 'EMPLOYER SUPERANNUATION CONTRIBUTION'
, '-999'
, PET.ROWID) ROW_ID
, DECODE(PEC2.CLASSIFICATION_NAME
, 'EMPLOYER SUPERANNUATION CONTRIBUTION'
, '-999'
, PRR.ROWID) PRR_ROW_ID
, DECODE(PEC2.CLASSIFICATION_NAME
, 'EMPLOYER SUPERANNUATION CONTRIBUTION'
, PAY_AU_SOE_PKG.SUPER_FUND_NAME(PRR.SOURCE_ID
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, PPA.DATE_EARNED
, PAC.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PRR.ELEMENT_ENTRY_ID
, PAA.BUSINESS_GROUP_ID)
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)) ELEMENT_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)
, PPA.DATE_EARNED
, PAC.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PRR.ELEMENT_ENTRY_ID
, PAA.BUSINESS_GROUP_ID)
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)) REPORT_NAME
, 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')) LABEL
, DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'M'
, PRV.RESULT_VALUE
, NULL) AMOUNT
, DECODE(PEC2.CLASSIFICATION_NAME
, 'EMPLOYER SUPERANNUATION CONTRIBUTION'
, '3250'
, PET.PROCESSING_PRIORITY) PROCESSING_PRIORITY
, PAC.ASSIGNMENT_ACTION_ID RUN_ASSIGNMENT_ACTION_ID
, PAC.ASSIGNMENT_ID
, PRR.RUN_RESULT_ID
, 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
, PER_ALL_ASSIGNMENTS_F PAA
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRV
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_CLASSIFICATIONS PEC2
, PAY_SUB_CLASSIFICATION_RULES_F PSCR
WHERE PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.ACTION_STATUS = 'C'
AND PAC.ACTION_STATUS(+) = 'C'
AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PAC.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
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 /* BUG 5461557 */
AND PIV.NAME = 'PAY VALUE'
AND PIV.INPUT_VALUE_ID = PRV.INPUT_VALUE_ID
AND PRR.RUN_RESULT_ID = PRV.RUN_RESULT_ID /* BUG 5461557 */
AND (PEC.LEGISLATION_CODE IS NULL OR PEC.LEGISLATION_CODE = 'AU')
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
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 )
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 PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PRR.STATUS IN ('P'
, 'PA') ) ) GROUP BY ROW_ID
, ELEMENT_NAME
, REPORT_NAME
, LABEL
, PROCESSING_PRIORITY
, RUN_ASSIGNMENT_ACTION_ID
, ASSIGNMENT_ID
, TRUNC(RATE
, 2)

Columns