VIEW: APPS.PAY_SG_ASG_ELEMENTS_V

Source

View Text - Preformatted

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 prr.status in ('P','PA') /* Bug 13362759 */ 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 prr.status in ('P','PA') /* Bug 13362759 */ 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)

View Text - HTML Formatted

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 PRR.STATUS IN ('P'
, 'PA') /* BUG 13362759 */
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 PRR.STATUS IN ('P'
, 'PA') /* BUG 13362759 */
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)