VIEW: APPS.PAY_CN_ASG_ELEMENTS_V

Source

View Text - Preformatted

SELECT pet.rowid ROW_ID ,paa.assignment_action_id ASSIGNMENT_ACTION_ID ,decode(pec.classification_name ,'Special Payments','Taxable Earnings' ,'Annual Bonus','Taxable Earnings' ,'Retro Taxable Earnings','Taxable Earnings' ,'Retro Special Payments','Taxable Earnings' ,'Retro Annual Bonus','Taxable Earnings' ,'Voluntary Deductions','Voluntary Dedn' ,'Severance Payments','Taxable Earnings' ,'Direct Payments','Non Taxable Earnings' ,'Retro Statutory Deductions','Statutory Deductions' ,'Retro Variable Yearly Earnings','Taxable Earnings' ,'Variable Yearly Earnings','Taxable Earnings' ,'Retro Pre Tax Non Statutory Deductions' , 'Pre Tax Non Statutory Deductions' ,pec.classification_name) CLASSIFICATION_NAME ,nvl(petl.reporting_name,petl.element_name) ELEMENT_REPORTING_NAME ,pet.processing_priority PROCESSING_PRIORITY ,sum(decode(substr(piv.uom,1,1), 'M', prrv.result_value, null)) AMOUNT ,decode(pet.input_currency_code, 'CNY',null, pet.input_currency_code) FOREIGN_CURRENCY_CODE ,pay_cn_payslip.get_exchange_rate(pet.input_currency_code ,pet.output_currency_code ,ppa.effective_date ,ppa.business_group_id) EXCHANGE_RATE FROM pay_payroll_actions ppa ,pay_assignment_actions paa ,pay_run_results prr ,pay_run_result_values prrv ,pay_input_values_f piv ,pay_element_types_f pet ,pay_element_types_f_tl petl ,pay_element_classifications pec WHERE ppa.action_type in ('R','Q') AND ppa.action_status = 'C' AND ppa.payroll_action_id = paa.payroll_action_id AND paa.assignment_action_id = prr.assignment_action_id AND pec.classification_name IN ('Taxable Earnings' ,'Voluntary Deductions' ,'Non Taxable Earnings' ,'Statutory Deductions' ,'Special Payments' ,'Annual Bonus' ,'Severance Payments' ,'Direct Payments' ,'Retro Taxable Earnings' ,'Retro Statutory Deductions' ,'Retro Special Payments' ,'Retro Annual Bonus' ,'Variable Yearly Earnings' ,'Retro Variable Yearly Earnings' ,'Pre Tax Non Statutory Deductions' ,'Retro Pre Tax Non Statutory Deductions' ) AND pec.legislation_code = 'CN' AND pec.classification_id = pet.classification_id AND pet.element_name <> 'Special Payments Normal' AND pet.element_type_id = petl.element_type_id AND petl.language = USERENV('LANG') AND pet.element_type_id = piv.element_type_id AND piv.name = decode(pec.classification_name,'Special Payments','Payment Amount','Pay Value') AND pet.element_type_id = prr.element_type_id AND prr.run_result_id = prrv.run_result_id AND prr.status in ('P','PA') AND piv.input_value_id = prrv.input_value_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 NOT EXISTS (SELECT null FROM pay_action_interlocks pai WHERE pai.locked_action_id = paa.assignment_action_id) GROUP BY pet.rowid ,paa.assignment_action_id ,decode(pec.classification_name ,'Special Payments','Taxable Earnings' ,'Annual Bonus','Taxable Earnings' ,'Retro Taxable Earnings','Taxable Earnings' ,'Retro Special Payments','Taxable Earnings' ,'Retro Annual Bonus','Taxable Earnings' ,'Voluntary Deductions','Voluntary Dedn' ,'Severance Payments','Taxable Earnings' ,'Direct Payments','Non Taxable Earnings' ,'Retro Statutory Deductions','Statutory Deductions' ,'Retro Variable Yearly Earnings','Taxable Earnings' ,'Variable Yearly Earnings','Taxable Earnings' ,'Retro Pre Tax Non Statutory Deductions' , 'Pre Tax Non Statutory Deductions' ,pec.classification_name) ,nvl(petl.reporting_name,petl.element_name) ,pet.processing_priority ,pet.input_currency_code ,pay_cn_payslip.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 ASSIGNMENT_ACTION_ID ,decode(pec.classification_name ,'Special Payments','Taxable Earnings' ,'Annual Bonus','Taxable Earnings' ,'Retro Taxable Earnings','Taxable Earnings' ,'Retro Special Payments','Taxable Earnings' ,'Retro Annual Bonus','Taxable Earnings' ,'Voluntary Deductions','Voluntary Dedn' ,'Severance Payments','Taxable Earnings' ,'Direct Payments','Non Taxable Earnings' ,'Retro Statutory Deductions','Statutory Deductions' ,'Retro Variable Yearly Earnings','Taxable Earnings' ,'Variable Yearly Earnings','Taxable Earnings' ,'Retro Pre Tax Non Statutory Deductions' , 'Pre Tax Non Statutory Deductions' ,pec.classification_name) CLASSIFICATION_NAME ,nvl(petl.reporting_name,petl.element_name) ELEMENT_REPORTING_NAME ,pet.processing_priority PROCESSING_PRIORITY ,sum(decode(substr(piv.uom,1,1), 'M', prrv.result_value, null)) AMOUNT ,decode(pet.input_currency_code, 'CNY',null, pet.input_currency_code) FOREIGN_CURRENCY_CODE ,pay_cn_payslip.get_exchange_rate(pet.input_currency_code ,pet.output_currency_code ,ppa.effective_date ,ppa.business_group_id) EXCHANGE_RATE FROM pay_payroll_actions ppa ,pay_assignment_actions paa ,pay_run_results prr ,pay_run_result_values prrv ,pay_input_values_f piv ,pay_element_types_f pet ,pay_element_types_f_tl petl ,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 = paa.payroll_action_id AND paa.assignment_action_id = prr.assignment_action_id AND pec.classification_name IN ('Taxable Earnings' ,'Voluntary Deductions' ,'Non Taxable Earnings' ,'Statutory Deductions' ,'Special Payments' ,'Annual Bonus' ,'Severance Payments' ,'Direct Payments' ,'Retro Taxable Earnings' ,'Retro Statutory Deductions' ,'Retro Special Payments' ,'Retro Annual Bonus' ,'Variable Yearly Earnings' ,'Retro Variable Yearly Earnings' ,'Pre Tax Non Statutory Deductions' ,'Retro Pre Tax Non Statutory Deductions' ) AND pec.legislation_code = 'CN' AND pec.classification_id = pet.classification_id AND pet.element_name <> 'Special Payments Normal' AND pet.element_type_id = petl.element_type_id AND petl.language = USERENV('LANG') AND pet.element_type_id = piv.element_type_id AND piv.name = decode(pec.classification_name,'Special Payments','Payment Amount','Pay Value') AND pet.element_type_id = prr.element_type_id AND prr.run_result_id = prrv.run_result_id AND prr.status in ('P','PA') AND piv.input_value_id = prrv.input_value_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 pai.locked_action_id = paa.assignment_action_id GROUP BY pet.rowid ,pai.locking_action_id ,decode(pec.classification_name ,'Special Payments','Taxable Earnings' ,'Annual Bonus','Taxable Earnings' ,'Retro Taxable Earnings','Taxable Earnings' ,'Retro Special Payments','Taxable Earnings' ,'Retro Annual Bonus','Taxable Earnings' ,'Voluntary Deductions','Voluntary Dedn' ,'Severance Payments','Taxable Earnings' ,'Direct Payments','Non Taxable Earnings' ,'Retro Statutory Deductions','Statutory Deductions' ,'Retro Variable Yearly Earnings','Taxable Earnings' ,'Variable Yearly Earnings','Taxable Earnings' ,'Retro Pre Tax Non Statutory Deductions' , 'Pre Tax Non Statutory Deductions' ,pec.classification_name) ,nvl(petl.reporting_name,petl.element_name) ,pet.processing_priority ,pet.input_currency_code ,pay_cn_payslip.get_exchange_rate(pet.input_currency_code,pet.output_currency_code,ppa.effective_date, ppa.business_group_id)

View Text - HTML Formatted

SELECT PET.ROWID ROW_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'SPECIAL PAYMENTS'
, 'TAXABLE EARNINGS'
, 'ANNUAL BONUS'
, 'TAXABLE EARNINGS'
, 'RETRO TAXABLE EARNINGS'
, 'TAXABLE EARNINGS'
, 'RETRO SPECIAL PAYMENTS'
, 'TAXABLE EARNINGS'
, 'RETRO ANNUAL BONUS'
, 'TAXABLE EARNINGS'
, 'VOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDN'
, 'SEVERANCE PAYMENTS'
, 'TAXABLE EARNINGS'
, 'DIRECT PAYMENTS'
, 'NON TAXABLE EARNINGS'
, 'RETRO STATUTORY DEDUCTIONS'
, 'STATUTORY DEDUCTIONS'
, 'RETRO VARIABLE YEARLY EARNINGS'
, 'TAXABLE EARNINGS'
, 'VARIABLE YEARLY EARNINGS'
, 'TAXABLE EARNINGS'
, 'RETRO PRE TAX NON STATUTORY DEDUCTIONS'
, 'PRE TAX NON STATUTORY DEDUCTIONS'
, PEC.CLASSIFICATION_NAME) CLASSIFICATION_NAME
, NVL(PETL.REPORTING_NAME
, PETL.ELEMENT_NAME) ELEMENT_REPORTING_NAME
, PET.PROCESSING_PRIORITY PROCESSING_PRIORITY
, SUM(DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'M'
, PRRV.RESULT_VALUE
, NULL)) AMOUNT
, DECODE(PET.INPUT_CURRENCY_CODE
, 'CNY'
, NULL
, PET.INPUT_CURRENCY_CODE) FOREIGN_CURRENCY_CODE
, PAY_CN_PAYSLIP.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID) EXCHANGE_RATE
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETL
, PAY_ELEMENT_CLASSIFICATIONS PEC
WHERE PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PEC.CLASSIFICATION_NAME IN ('TAXABLE EARNINGS'
, 'VOLUNTARY DEDUCTIONS'
, 'NON TAXABLE EARNINGS'
, 'STATUTORY DEDUCTIONS'
, 'SPECIAL PAYMENTS'
, 'ANNUAL BONUS'
, 'SEVERANCE PAYMENTS'
, 'DIRECT PAYMENTS'
, 'RETRO TAXABLE EARNINGS'
, 'RETRO STATUTORY DEDUCTIONS'
, 'RETRO SPECIAL PAYMENTS'
, 'RETRO ANNUAL BONUS'
, 'VARIABLE YEARLY EARNINGS'
, 'RETRO VARIABLE YEARLY EARNINGS'
, 'PRE TAX NON STATUTORY DEDUCTIONS'
, 'RETRO PRE TAX NON STATUTORY DEDUCTIONS' )
AND PEC.LEGISLATION_CODE = 'CN'
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PET.ELEMENT_NAME <> 'SPECIAL PAYMENTS NORMAL'
AND PET.ELEMENT_TYPE_ID = PETL.ELEMENT_TYPE_ID
AND PETL.LANGUAGE = USERENV('LANG')
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PIV.NAME = DECODE(PEC.CLASSIFICATION_NAME
, 'SPECIAL PAYMENTS'
, 'PAYMENT AMOUNT'
, 'PAY VALUE')
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_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 NOT EXISTS (SELECT NULL
FROM PAY_ACTION_INTERLOCKS PAI
WHERE PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID) GROUP BY PET.ROWID
, PAA.ASSIGNMENT_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'SPECIAL PAYMENTS'
, 'TAXABLE EARNINGS'
, 'ANNUAL BONUS'
, 'TAXABLE EARNINGS'
, 'RETRO TAXABLE EARNINGS'
, 'TAXABLE EARNINGS'
, 'RETRO SPECIAL PAYMENTS'
, 'TAXABLE EARNINGS'
, 'RETRO ANNUAL BONUS'
, 'TAXABLE EARNINGS'
, 'VOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDN'
, 'SEVERANCE PAYMENTS'
, 'TAXABLE EARNINGS'
, 'DIRECT PAYMENTS'
, 'NON TAXABLE EARNINGS'
, 'RETRO STATUTORY DEDUCTIONS'
, 'STATUTORY DEDUCTIONS'
, 'RETRO VARIABLE YEARLY EARNINGS'
, 'TAXABLE EARNINGS'
, 'VARIABLE YEARLY EARNINGS'
, 'TAXABLE EARNINGS'
, 'RETRO PRE TAX NON STATUTORY DEDUCTIONS'
, 'PRE TAX NON STATUTORY DEDUCTIONS'
, PEC.CLASSIFICATION_NAME)
, NVL(PETL.REPORTING_NAME
, PETL.ELEMENT_NAME)
, PET.PROCESSING_PRIORITY
, PET.INPUT_CURRENCY_CODE
, PAY_CN_PAYSLIP.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 ASSIGNMENT_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'SPECIAL PAYMENTS'
, 'TAXABLE EARNINGS'
, 'ANNUAL BONUS'
, 'TAXABLE EARNINGS'
, 'RETRO TAXABLE EARNINGS'
, 'TAXABLE EARNINGS'
, 'RETRO SPECIAL PAYMENTS'
, 'TAXABLE EARNINGS'
, 'RETRO ANNUAL BONUS'
, 'TAXABLE EARNINGS'
, 'VOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDN'
, 'SEVERANCE PAYMENTS'
, 'TAXABLE EARNINGS'
, 'DIRECT PAYMENTS'
, 'NON TAXABLE EARNINGS'
, 'RETRO STATUTORY DEDUCTIONS'
, 'STATUTORY DEDUCTIONS'
, 'RETRO VARIABLE YEARLY EARNINGS'
, 'TAXABLE EARNINGS'
, 'VARIABLE YEARLY EARNINGS'
, 'TAXABLE EARNINGS'
, 'RETRO PRE TAX NON STATUTORY DEDUCTIONS'
, 'PRE TAX NON STATUTORY DEDUCTIONS'
, PEC.CLASSIFICATION_NAME) CLASSIFICATION_NAME
, NVL(PETL.REPORTING_NAME
, PETL.ELEMENT_NAME) ELEMENT_REPORTING_NAME
, PET.PROCESSING_PRIORITY PROCESSING_PRIORITY
, SUM(DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'M'
, PRRV.RESULT_VALUE
, NULL)) AMOUNT
, DECODE(PET.INPUT_CURRENCY_CODE
, 'CNY'
, NULL
, PET.INPUT_CURRENCY_CODE) FOREIGN_CURRENCY_CODE
, PAY_CN_PAYSLIP.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID) EXCHANGE_RATE
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETL
, 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 = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PEC.CLASSIFICATION_NAME IN ('TAXABLE EARNINGS'
, 'VOLUNTARY DEDUCTIONS'
, 'NON TAXABLE EARNINGS'
, 'STATUTORY DEDUCTIONS'
, 'SPECIAL PAYMENTS'
, 'ANNUAL BONUS'
, 'SEVERANCE PAYMENTS'
, 'DIRECT PAYMENTS'
, 'RETRO TAXABLE EARNINGS'
, 'RETRO STATUTORY DEDUCTIONS'
, 'RETRO SPECIAL PAYMENTS'
, 'RETRO ANNUAL BONUS'
, 'VARIABLE YEARLY EARNINGS'
, 'RETRO VARIABLE YEARLY EARNINGS'
, 'PRE TAX NON STATUTORY DEDUCTIONS'
, 'RETRO PRE TAX NON STATUTORY DEDUCTIONS' )
AND PEC.LEGISLATION_CODE = 'CN'
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PET.ELEMENT_NAME <> 'SPECIAL PAYMENTS NORMAL'
AND PET.ELEMENT_TYPE_ID = PETL.ELEMENT_TYPE_ID
AND PETL.LANGUAGE = USERENV('LANG')
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PIV.NAME = DECODE(PEC.CLASSIFICATION_NAME
, 'SPECIAL PAYMENTS'
, 'PAYMENT AMOUNT'
, 'PAY VALUE')
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_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 PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID GROUP BY PET.ROWID
, PAI.LOCKING_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'SPECIAL PAYMENTS'
, 'TAXABLE EARNINGS'
, 'ANNUAL BONUS'
, 'TAXABLE EARNINGS'
, 'RETRO TAXABLE EARNINGS'
, 'TAXABLE EARNINGS'
, 'RETRO SPECIAL PAYMENTS'
, 'TAXABLE EARNINGS'
, 'RETRO ANNUAL BONUS'
, 'TAXABLE EARNINGS'
, 'VOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDN'
, 'SEVERANCE PAYMENTS'
, 'TAXABLE EARNINGS'
, 'DIRECT PAYMENTS'
, 'NON TAXABLE EARNINGS'
, 'RETRO STATUTORY DEDUCTIONS'
, 'STATUTORY DEDUCTIONS'
, 'RETRO VARIABLE YEARLY EARNINGS'
, 'TAXABLE EARNINGS'
, 'VARIABLE YEARLY EARNINGS'
, 'TAXABLE EARNINGS'
, 'RETRO PRE TAX NON STATUTORY DEDUCTIONS'
, 'PRE TAX NON STATUTORY DEDUCTIONS'
, PEC.CLASSIFICATION_NAME)
, NVL(PETL.REPORTING_NAME
, PETL.ELEMENT_NAME)
, PET.PROCESSING_PRIORITY
, PET.INPUT_CURRENCY_CODE
, PAY_CN_PAYSLIP.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID)