View: PAY_NZ_SOE_RUN_ELEMENTS_V2

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_NZ_SOE_RUN_ELEMENTS_V2

View Text

SELECT NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME) REPORT_NAME
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, 'TAXABLE EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'REIMBURSEMENTS')
, 0
, NULL
, 'NON TAXABLE ALLOWANCES') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, 'DEDUCTIONS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'INFORMATION')
, 0
, NULL
, DECODE(PET.ELEMENT_NAME
, 'EMPLOYER SUPER CONTRIBUTIONS'
, 'EMPLOYER SUPERANNUATION CONTRIBUTIONS'
, NULL)) || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER CHARGES')
, 0
, NULL
, DECODE(PET.ELEMENT_NAME
, 'SSCWT DEDUCTION'
, 'EMPLOYER SUPERANNUATION CONTRIBUTIONS'
, NULL)) CLASSIFICATION_NAME
, SUM(DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'M'
, PRV.RESULT_VALUE
, NULL)) AMOUNT
, PET.PROCESSING_PRIORITY
, PAI.LOCKING_ACTION_ID RUN_ASSIGNMENT_ACTION_ID
, PAC.ASSIGNMENT_ID
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_ACTION_INTERLOCKS PAI
WHERE PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.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 (PIV.NAME = 'PAY VALUE' OR PIV.NAME = 'HOURS')
AND PIV.INPUT_VALUE_ID = PRV.INPUT_VALUE_ID
AND PRR.RUN_RESULT_ID = PRV.RUN_RESULT_ID
AND PEC.LEGISLATION_CODE = 'NZ'
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
, 'REIMBURSEMENTS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER SPECIFIED SUPERANNUATION CONTRIBUTIONS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER CHARGES') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'INFORMATION') > 0)
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 PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE GROUP BY PAC.ASSIGNMENT_ID
, PAI.LOCKING_ACTION_ID
, PET.PROCESSING_PRIORITY
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, 'TAXABLE EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'REIMBURSEMENTS')
, 0
, NULL
, 'NON TAXABLE ALLOWANCES') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, 'DEDUCTIONS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'INFORMATION')
, 0
, NULL
, DECODE(PET.ELEMENT_NAME
, 'EMPLOYER SUPER CONTRIBUTIONS'
, 'EMPLOYER SUPERANNUATION CONTRIBUTIONS'
, NULL)) || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER CHARGES')
, 0
, NULL
, DECODE(PET.ELEMENT_NAME
, 'SSCWT DEDUCTION'
, 'EMPLOYER SUPERANNUATION CONTRIBUTIONS'
, NULL))
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, PET.ROWID

Columns