View: PAY_SG_ASG_ELEMENTS_V

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

View Text

SELECT PET.ROWID ROW_ID
, PAC.ASSIGNMENT_ACTION_ID RUN_ASSIGNMENT_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'ADDITIONAL EARNINGS'
, 'EARNINGS'
, 'NON-PAYROLL PAYMENTS'
, 'EARNINGS'
, 'ORDINARY EARNINGS'
, 'EARNINGS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'STATUTORY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, PEC.CLASSIFICATION_NAME) CLASSIFICATION_GROUP
, DECODE(PEC.CLASSIFICATION_NAME
, 'ORDINARY EARNINGS'
, 1
, 'ADDITIONAL EARNINGS'
, 2
, 'NON-PAYROLL PAYMENTS'
, 3
, 'STATUTORY DEDUCTIONS'
, 4
, 'INVOLUNTARY DEDUCTIONS'
, 5
, 'VOLUNTARY DEDUCTIONS'
, 6
, 7) CLASSIFICATION_ORDER
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME) ELEMENT_REPORTING_NAME
, PET.PROCESSING_PRIORITY
, SUM(DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'M'
, PRV.RESULT_VALUE
, NULL)) AMOUNT
, DECODE(PET.INPUT_CURRENCY_CODE
, 'SGD'
, NULL
, PET.INPUT_CURRENCY_CODE) FOREIGN_CURRENCY_CODE
, PAY_SG_SOE.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID)
, SUM(DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'H'
, PRV.RESULT_VALUE
, NULL)) HOURS
FROM PAY_PAYROLL_ACTIONS PPA
, 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
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 PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND NOT EXISTS (SELECT NULL
FROM PAY_ACTION_INTERLOCKS PAI
, PAY_PAYROLL_ACTIONS PPA1
, PAY_ASSIGNMENT_ACTIONS PAA1
WHERE PAI.LOCKED_ACTION_ID = PAC.ASSIGNMENT_ACTION_ID
AND PPA1.PAYROLL_ACTION_ID = PAA1.PAYROLL_ACTION_ID
AND PAI.LOCKING_ACTION_ID = PAA1.ASSIGNMENT_ACTION_ID
AND PPA1.ACTION_TYPE IN ('P'
, 'U'
, 'C')
AND PPA1.ACTION_STATUS = 'C')
AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
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 IS NULL OR PEC.LEGISLATION_CODE = 'SG')
AND PEC.CLASSIFICATION_NAME IN ('ADDITIONAL EARNINGS'
, 'NON-PAYROLL PAYMENTS'
, 'ORDINARY EARNINGS'
, 'INVOLUNTARY DEDUCTIONS'
, 'STATUTORY DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS')
AND PET.ELEMENT_NAME NOT IN ('STATUTORY DEDUCTIONS'
, 'COMMUNITY FUND') GROUP BY PET.ROWID
, PAC.ASSIGNMENT_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'ADDITIONAL EARNINGS'
, 'EARNINGS'
, 'NON-PAYROLL PAYMENTS'
, 'EARNINGS'
, 'ORDINARY EARNINGS'
, 'EARNINGS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'STATUTORY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, PEC.CLASSIFICATION_NAME)
, DECODE(PEC.CLASSIFICATION_NAME
, 'ORDINARY EARNINGS'
, 1
, 'ADDITIONAL EARNINGS'
, 2
, 'NON-PAYROLL PAYMENTS'
, 3
, 'STATUTORY DEDUCTIONS'
, 4
, 'INVOLUNTARY DEDUCTIONS'
, 5
, 'VOLUNTARY DEDUCTIONS'
, 6
, 7)
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, PET.PROCESSING_PRIORITY
, PET.INPUT_CURRENCY_CODE
, PAY_SG_SOE.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID) UNION ALL SELECT PET.ROWID ROW_ID
, PAI.LOCKING_ACTION_ID RUN_ASSIGNMENT_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'ADDITIONAL EARNINGS'
, 'EARNINGS'
, 'NON-PAYROLL PAYMENTS'
, 'EARNINGS'
, 'ORDINARY EARNINGS'
, 'EARNINGS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'STATUTORY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, PEC.CLASSIFICATION_NAME) CLASSIFICATION_GROUP
, DECODE(PEC.CLASSIFICATION_NAME
, 'ORDINARY EARNINGS'
, 1
, 'ADDITIONAL EARNINGS'
, 2
, 'NON-PAYROLL PAYMENTS'
, 3
, 'STATUTORY DEDUCTIONS'
, 4
, 'INVOLUNTARY DEDUCTIONS'
, 5
, 'VOLUNTARY DEDUCTIONS'
, 6
, 7) CLASSIFICATION_ORDER
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME) ELEMENT_REPORTING_NAME
, PET.PROCESSING_PRIORITY
, SUM(DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'M'
, PRV.RESULT_VALUE
, NULL)) AMOUNT
, DECODE(PET.INPUT_CURRENCY_CODE
, 'SGD'
, NULL
, PET.INPUT_CURRENCY_CODE) FOREIGN_CURRENCY_CODE
, PAY_SG_SOE.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID)
, SUM(DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'H'
, PRV.RESULT_VALUE
, NULL)) HOURS
FROM PAY_PAYROLL_ACTIONS PPA
, 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 PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
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 IS NULL OR PEC.LEGISLATION_CODE = 'SG')
AND PEC.CLASSIFICATION_NAME IN ('ADDITIONAL EARNINGS'
, 'NON-PAYROLL PAYMENTS'
, 'ORDINARY EARNINGS'
, 'INVOLUNTARY DEDUCTIONS'
, 'STATUTORY DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS')
AND PET.ELEMENT_NAME NOT IN ('STATUTORY DEDUCTIONS'
, 'COMMUNITY FUND') GROUP BY PET.ROWID
, PAI.LOCKING_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'ADDITIONAL EARNINGS'
, 'EARNINGS'
, 'NON-PAYROLL PAYMENTS'
, 'EARNINGS'
, 'ORDINARY EARNINGS'
, 'EARNINGS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'STATUTORY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, PEC.CLASSIFICATION_NAME)
, DECODE(PEC.CLASSIFICATION_NAME
, 'ORDINARY EARNINGS'
, 1
, 'ADDITIONAL EARNINGS'
, 2
, 'NON-PAYROLL PAYMENTS'
, 3
, 'STATUTORY DEDUCTIONS'
, 4
, 'INVOLUNTARY DEDUCTIONS'
, 5
, 'VOLUNTARY DEDUCTIONS'
, 6
, 7)
, NVL(PET.REPORTING_NAME
, PET.ELEMENT_NAME)
, PET.PROCESSING_PRIORITY
, PET.INPUT_CURRENCY_CODE
, PAY_SG_SOE.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID)

Columns