VIEW: APPS.PAY_IE_SOE_PAYE_DETAILS_V

Source

View Text - Preformatted

SELECT /*+ ORDERED INDEX(SES FND_SESSIONS_U1) INDEX(PET PAY_ELEMENT_TYPES_F_UK2) INDEX(PRR PAY_RUN_RESULTS_N50) INDEX(PIV PAY_INPUT_VALUES_F_N50) INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) USE_NL(SES, PRR, PET) */ prr.assignment_action_id, MAX (DECODE (piv.NAME, 'Weekly Tax Credit', prrv.result_value, NULL ) ) weekly_tax_credit, MAX (DECODE (piv.NAME, 'Weekly Standard Rate Cutoff', prrv.result_value, NULL ) ) weekly_std_rate_cut_off, MAX (DECODE (piv.NAME, 'Monthly Tax Credit', prrv.result_value, NULL ) ) monthly_tax_credit, MAX (DECODE (piv.NAME, 'Monthly Standard Rate Cutoff', prrv.result_value, NULL ) ) monthly_std_rate_cut_off, SUBSTR (hr_general.decode_lookup ('IE_PAYE_TAX_BASIS', MAX (DECODE (piv.NAME, 'Tax Basis', prrv.result_value, NULL ) ) ), 1, 80 ) d_tax_basis, SUBSTR (hr_general.decode_lookup ('IE_PAYE_INFO_SOURCE', MAX (DECODE (piv.NAME, 'Info source', prrv.result_value, NULL ) ) ), 1, 80 ) d_info_source, SUBSTR (hr_general.decode_lookup ('IE_PAYE_ASSESS_BASIS', MAX (DECODE (piv.NAME, 'Tax assess basis', prrv.result_value, NULL ) ) ), 1, 80 ) d_tax_assess_basis, MAX (DECODE (piv.NAME, 'Certificate Issue date', prrv.result_value, NULL ) ) certificate_issue_date FROM fnd_sessions ses, pay_element_types_f pet, pay_run_results prr, pay_input_values_f piv, pay_run_result_values prrv WHERE pet.element_type_id = prr.element_type_id AND pet.element_name = 'IE PAYE details' AND pet.legislation_code = 'IE' AND prr.run_result_id = prrv.run_result_id AND piv.element_type_id = pet.element_type_id AND piv.input_value_id = prrv.input_value_id AND piv.NAME IN ('Weekly Tax Credit', 'Weekly Standard Rate Cutoff', 'Monthly Tax Credit', 'Tax Basis', 'Info source', 'Tax assess basis', 'Certificate Issue date', 'Monthly Standard Rate Cutoff' ) AND ses.session_id = USERENV ('sessionid') AND ses.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date AND ses.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date GROUP BY prr.assignment_action_id

View Text - HTML Formatted

SELECT /*+ ORDERED INDEX(SES FND_SESSIONS_U1) INDEX(PET PAY_ELEMENT_TYPES_F_UK2) INDEX(PRR PAY_RUN_RESULTS_N50) INDEX(PIV PAY_INPUT_VALUES_F_N50) INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) USE_NL(SES
, PRR
, PET) */ PRR.ASSIGNMENT_ACTION_ID
, MAX (DECODE (PIV.NAME
, 'WEEKLY TAX CREDIT'
, PRRV.RESULT_VALUE
, NULL ) ) WEEKLY_TAX_CREDIT
, MAX (DECODE (PIV.NAME
, 'WEEKLY STANDARD RATE CUTOFF'
, PRRV.RESULT_VALUE
, NULL ) ) WEEKLY_STD_RATE_CUT_OFF
, MAX (DECODE (PIV.NAME
, 'MONTHLY TAX CREDIT'
, PRRV.RESULT_VALUE
, NULL ) ) MONTHLY_TAX_CREDIT
, MAX (DECODE (PIV.NAME
, 'MONTHLY STANDARD RATE CUTOFF'
, PRRV.RESULT_VALUE
, NULL ) ) MONTHLY_STD_RATE_CUT_OFF
, SUBSTR (HR_GENERAL.DECODE_LOOKUP ('IE_PAYE_TAX_BASIS'
, MAX (DECODE (PIV.NAME
, 'TAX BASIS'
, PRRV.RESULT_VALUE
, NULL ) ) )
, 1
, 80 ) D_TAX_BASIS
, SUBSTR (HR_GENERAL.DECODE_LOOKUP ('IE_PAYE_INFO_SOURCE'
, MAX (DECODE (PIV.NAME
, 'INFO SOURCE'
, PRRV.RESULT_VALUE
, NULL ) ) )
, 1
, 80 ) D_INFO_SOURCE
, SUBSTR (HR_GENERAL.DECODE_LOOKUP ('IE_PAYE_ASSESS_BASIS'
, MAX (DECODE (PIV.NAME
, 'TAX ASSESS BASIS'
, PRRV.RESULT_VALUE
, NULL ) ) )
, 1
, 80 ) D_TAX_ASSESS_BASIS
, MAX (DECODE (PIV.NAME
, 'CERTIFICATE ISSUE DATE'
, PRRV.RESULT_VALUE
, NULL ) ) CERTIFICATE_ISSUE_DATE
FROM FND_SESSIONS SES
, PAY_ELEMENT_TYPES_F PET
, PAY_RUN_RESULTS PRR
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRRV
WHERE PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PET.ELEMENT_NAME = 'IE PAYE DETAILS'
AND PET.LEGISLATION_CODE = 'IE'
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PIV.NAME IN ('WEEKLY TAX CREDIT'
, 'WEEKLY STANDARD RATE CUTOFF'
, 'MONTHLY TAX CREDIT'
, 'TAX BASIS'
, 'INFO SOURCE'
, 'TAX ASSESS BASIS'
, 'CERTIFICATE ISSUE DATE'
, 'MONTHLY STANDARD RATE CUTOFF' )
AND SES.SESSION_ID = USERENV ('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE GROUP BY PRR.ASSIGNMENT_ACTION_ID