View: PAY_HK_ASG_ELEMENT_PAYMENTS_V

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

View Text

SELECT PAI.ROWID ROW_ID
, PAI.LOCKING_ACTION_ID ASSIGNMENT_ACTION_ID
, PAAF.ASSIGNMENT_ID ASSIGNMENT_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)) PAYMENT_AMOUNT
, NULL PERIOD_START_DATE
, NULL PERIOD_END_DATE
, NULL ASSESSED_RI
FROM PER_ASSIGNMENTS_F PAAF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ACTION_INTERLOCKS PAI
, PAY_RUN_RESULTS PRR
, PAY_ELEMENT_TYPES_F PETF
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIVF
WHERE PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('U'
, 'P')
AND PPA.ACTION_STATUS = 'C'
AND PAA.ACTION_STATUS = 'C'
AND PAI.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PRR.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE 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'
AND NOT EXISTS (SELECT NULL
FROM PAY_HK_ASG_MPF_DATA_V PSD
WHERE PSD.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND ROWNUM < 2) GROUP BY PAI.ROWID
, PAI.LOCKING_ACTION_ID
, PAAF.ASSIGNMENT_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 UNION ALL SELECT PAI.ROWID ROW_ID
, PAI.LOCKING_ACTION_ID ASSIGNMENT_ACTION_ID
, PAAF.ASSIGNMENT_ID ASSIGNMENT_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)) PAYMENT_AMOUNT
, PSD.PERIOD_START_DATE PERIOD_START_DATE
, PSD.PERIOD_END_DATE PERIOD_END_DATE
, PSD.ASSESSED_RI ASSESSED_RI
FROM PER_ASSIGNMENTS_F PAAF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ACTION_INTERLOCKS PAI
, PAY_RUN_RESULTS PRR
, PAY_ELEMENT_TYPES_F PETF
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIVF
, PAY_HK_ASG_MPF_DATA_V PSD
WHERE PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('U'
, 'P')
AND PPA.ACTION_STATUS = 'C'
AND PAA.ACTION_STATUS = 'C'
AND PAI.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PRR.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE 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'
AND PSD.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PSD.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID GROUP BY PAI.ROWID
, PAI.LOCKING_ACTION_ID
, PAAF.ASSIGNMENT_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
, PSD.PERIOD_START_DATE
, PSD.PERIOD_END_DATE
, PSD.ASSESSED_RI

Columns