View: PAY_HK_ASG_ELEMENTS_V

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

View Text

SELECT /*+ INDEX(PEC PAY_ELEMENT_CLASSIFICATION_PK) INDEX(PRR PAY_RUN_RESULTS_PK) */ PETF.ROWID ROW_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, DECODE(PETF.ELEMENT_NAME
, 'MPF WITHHELD'
, 1
, 'MPF LIABILITY'
, 1
, 'RETRO MPF WITHHELD'
, 2
, 'RETRO MPF LIABILITY'
, 2
, 'VOLUNTARY MPF WITHHELD'
, 3
, 'VOLUNTARY MPF LIABILITY'
, 3
, 'VOLUNTARY MPF LUMP SUMS'
, 3
, 'MPF INFORMATION'
, 3
, 99 ) ELEMENT_NAME_SORT
, DECODE(PETF.ELEMENT_NAME
, 'RETRO MPF WITHHELD'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF WITHHELD'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'RETRO MPF LIABILITY'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF LIABILITY'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'VOLUNTARY MPF LUMP SUMS'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, NVL(PETF.REPORTING_NAME
, PETF.ELEMENT_NAME) ) ELEMENT_REPORTING_NAME
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DIRECT PAYMENT')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, 'DEDUCTIONS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER LIABILITIES')
, 0
, NULL
, 'EMPLOYER LIABILITIES') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'COMPANY QUARTERS')
, 0
, NULL
, 'EARNINGS') CLASSIFICATION_NAME
, PETF.PROCESSING_PRIORITY PROCESSING_PRIORITY
, SUM(NVL(PRRV.RESULT_VALUE
, 0)) AMOUNT
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_RUN_RESULTS PRR
, PAY_ELEMENT_TYPES_F PETF
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIVF
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.ACTION_STATUS = 'C'
AND PAA.ACTION_STATUS = 'C'
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PIVF.EFFECTIVE_START_DATE
AND PIVF.EFFECTIVE_END_DATE
AND PEC.CLASSIFICATION_ID = PETF.CLASSIFICATION_ID
AND (PEC.LEGISLATION_CODE IS NULL OR PEC.LEGISLATION_CODE = 'HK' )
AND (INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'DIRECT PAYMENT') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER LIABILITIES') > 0 OR INSTR(PEC.CLASSIFICATION_NAME
, 'COMPANY QUARTERS') > 0 )
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PIVF.NAME = 'PAY VALUE' GROUP BY PETF.ROWID
, PAA.ASSIGNMENT_ACTION_ID
, DECODE(PETF.ELEMENT_NAME
, 'MPF WITHHELD'
, 1
, 'MPF LIABILITY'
, 1
, 'RETRO MPF WITHHELD'
, 2
, 'RETRO MPF LIABILITY'
, 2
, 'VOLUNTARY MPF WITHHELD'
, 3
, 'VOLUNTARY MPF LIABILITY'
, 3
, 'VOLUNTARY MPF LUMP SUMS'
, 3
, 'MPF INFORMATION'
, 3
, 99 )
, DECODE(PETF.ELEMENT_NAME
, 'RETRO MPF WITHHELD'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF WITHHELD'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'RETRO MPF LIABILITY'
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 'VOLUNTARY MPF LIABILITY'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, 'VOLUNTARY MPF LUMP SUMS'
, 'VC: ' || SUBSTR(PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID)
, 1
, 16)
, NVL(PETF.REPORTING_NAME
, PETF.ELEMENT_NAME) )
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DIRECT PAYMENT')
, 0
, NULL
, 'EARNINGS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, 'DEDUCTIONS') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EMPLOYER LIABILITIES')
, 0
, NULL
, 'EMPLOYER LIABILITIES') || DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'COMPANY QUARTERS')
, 0
, NULL
, 'EARNINGS')
, PAY_HK_PAYSLIP_ARCHIVE.GET_SCHEME_NAME(PRR.RUN_RESULT_ID
, PRR.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID) || DECODE(PETF.ELEMENT_NAME
, 'RETRO MPF WITHHELD'
, 'RETRO'
, 'VOLUNTARY MPF WITHHELD'
, 'VOLUNTARY'
, NULL)
, PETF.PROCESSING_PRIORITY

Columns