VIEW: APPS.PAY_IN_ASG_ELEMENTS_V

Source

View Text - Preformatted

SELECT pet.rowid ROW_ID ,paa.assignment_action_id ASSIGNMENT_ACTION_ID ,decode(pec.classification_name ,'Pre Tax Deductions','Deductions' ,'Tax Deductions','Deductions' ,'Involuntary Deductions','Deductions' ,'Voluntary Deductions','Deductions' ,'Allowances','Earnings' ,'Advances','Advances' ,'Fringe Benefits','Fringe Benefits' ,'Paid Monetary Perquisite', 'Paid Monetary Perquisite' ,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, 'INR',null, pet.input_currency_code) FOREIGN_CURRENCY_CODE ,pay_in_payslip_utils.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 ('Earnings' ,'Allowances' ,'Pre Tax Deductions' ,'Tax Deductions' ,'Involuntary Deductions' ,'Voluntary Deductions' ,'Perquisites' ,'Employer Charges' ,'Advances' ,'Fringe Benefits' ,'Termination Payments' ,'Paid Monetary Perquisite' ) AND pec.legislation_code = 'IN' AND pec.classification_id = pet.classification_id 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 = 'Pay Value' AND pet.element_type_id = prr.element_type_id AND prr.run_result_id = prrv.run_result_id 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 paa.source_action_id IS NOT NULL GROUP BY pet.rowid ,paa.assignment_action_id ,decode(pec.classification_name ,'Pre Tax Deductions','Deductions' ,'Tax Deductions','Deductions' ,'Involuntary Deductions','Deductions' ,'Voluntary Deductions','Deductions' ,'Allowances','Earnings' ,'Advances','Advances' ,'Fringe Benefits','Fringe Benefits' ,'Paid Monetary Perquisite', 'Paid Monetary Perquisite' ,pec.classification_name) ,nvl(petl.reporting_name,petl.element_name) ,pet.processing_priority ,pet.input_currency_code ,pay_in_payslip_utils.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 ,'Pre Tax Deductions','Deductions' ,'Tax Deductions','Deductions' ,'Involuntary Deductions','Deductions' ,'Voluntary Deductions','Deductions' ,'Allowances','Earnings' ,'Advances','Advances' ,'Fringe Benefits','Fringe Benefits' ,'Paid Monetary Perquisite', 'Paid Monetary Perquisite' ,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, 'INR',null, pet.input_currency_code) FOREIGN_CURRENCY_CODE ,pay_in_payslip_utils.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 pai.locked_action_id = paa.assignment_action_id AND pec.classification_name IN ('Earnings' ,'Allowances' ,'Pre Tax Deductions' ,'Tax Deductions' ,'Involuntary Deductions' ,'Voluntary Deductions' ,'Perquisites' ,'Employer Charges' ,'Advances' ,'Fringe Benefits' ,'Termination Payments' ,'Paid Monetary Perquisite' ) AND pec.legislation_code = 'IN' AND pec.classification_id = pet.classification_id 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 = 'Pay Value' AND pet.element_type_id = prr.element_type_id AND prr.run_result_id = prrv.run_result_id 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 paa.source_action_id IS NOT NULL GROUP BY pet.rowid ,pai.locking_action_id ,decode(pec.classification_name ,'Pre Tax Deductions','Deductions' ,'Tax Deductions','Deductions' ,'Involuntary Deductions','Deductions' ,'Voluntary Deductions','Deductions' ,'Allowances','Earnings' ,'Advances','Advances' ,'Fringe Benefits','Fringe Benefits' ,'Paid Monetary Perquisite', 'Paid Monetary Perquisite' ,pec.classification_name) ,nvl(petl.reporting_name,petl.element_name) ,pet.processing_priority ,pet.input_currency_code ,pay_in_payslip_utils.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
, 'PRE TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'ALLOWANCES'
, 'EARNINGS'
, 'ADVANCES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'FRINGE BENEFITS'
, 'PAID MONETARY PERQUISITE'
, 'PAID MONETARY PERQUISITE'
, 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
, 'INR'
, NULL
, PET.INPUT_CURRENCY_CODE) FOREIGN_CURRENCY_CODE
, PAY_IN_PAYSLIP_UTILS.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 ('EARNINGS'
, 'ALLOWANCES'
, 'PRE TAX DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'PERQUISITES'
, 'EMPLOYER CHARGES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'TERMINATION PAYMENTS'
, 'PAID MONETARY PERQUISITE' )
AND PEC.LEGISLATION_CODE = 'IN'
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
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 = 'PAY VALUE'
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
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 PAA.SOURCE_ACTION_ID IS NOT NULL GROUP BY PET.ROWID
, PAA.ASSIGNMENT_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'PRE TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'ALLOWANCES'
, 'EARNINGS'
, 'ADVANCES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'FRINGE BENEFITS'
, 'PAID MONETARY PERQUISITE'
, 'PAID MONETARY PERQUISITE'
, PEC.CLASSIFICATION_NAME)
, NVL(PETL.REPORTING_NAME
, PETL.ELEMENT_NAME)
, PET.PROCESSING_PRIORITY
, PET.INPUT_CURRENCY_CODE
, PAY_IN_PAYSLIP_UTILS.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
, 'PRE TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'ALLOWANCES'
, 'EARNINGS'
, 'ADVANCES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'FRINGE BENEFITS'
, 'PAID MONETARY PERQUISITE'
, 'PAID MONETARY PERQUISITE'
, 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
, 'INR'
, NULL
, PET.INPUT_CURRENCY_CODE) FOREIGN_CURRENCY_CODE
, PAY_IN_PAYSLIP_UTILS.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 PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PEC.CLASSIFICATION_NAME IN ('EARNINGS'
, 'ALLOWANCES'
, 'PRE TAX DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'PERQUISITES'
, 'EMPLOYER CHARGES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'TERMINATION PAYMENTS'
, 'PAID MONETARY PERQUISITE' )
AND PEC.LEGISLATION_CODE = 'IN'
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
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 = 'PAY VALUE'
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
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 PAA.SOURCE_ACTION_ID IS NOT NULL GROUP BY PET.ROWID
, PAI.LOCKING_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'PRE TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'ALLOWANCES'
, 'EARNINGS'
, 'ADVANCES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'FRINGE BENEFITS'
, 'PAID MONETARY PERQUISITE'
, 'PAID MONETARY PERQUISITE'
, PEC.CLASSIFICATION_NAME)
, NVL(PETL.REPORTING_NAME
, PETL.ELEMENT_NAME)
, PET.PROCESSING_PRIORITY
, PET.INPUT_CURRENCY_CODE
, PAY_IN_PAYSLIP_UTILS.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID)