VIEW: APPS.PAY_IE_SOE_PAYE_DETAILS_V

Source

View Text - Preformatted

SELECT assignment_action_id, nvl(period_weekly_tax_credit,weekly_tax_credit) weekly_tax_credit, nvl(period_weekly_std_rate_cut_off,weekly_std_rate_cut_off ) weekly_std_rate_cut_off, monthly_tax_credit,monthly_std_rate_cut_off,d_tax_basis,d_info_source,d_tax_assess_basis,certificate_issue_date FROM ( 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 assignment_action_id, MAX (DECODE (piv.NAME, 'Period Weekly Tax Credit', prrv.result_value , null)) period_weekly_tax_credit, MAX (DECODE (piv.NAME, 'Weekly Tax Credit', prrv.result_value, NULL )) weekly_tax_credit, MAX (DECODE (piv.NAME, 'Period Weekly Standard Rate Cutoff',prrv.result_value,null)) period_weekly_std_rate_cut_off, 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', 'Period Weekly Tax Credit', 'Period Weekly Standard Rate Cutoff', '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 ASSIGNMENT_ACTION_ID
, NVL(PERIOD_WEEKLY_TAX_CREDIT
, WEEKLY_TAX_CREDIT) WEEKLY_TAX_CREDIT
, NVL(PERIOD_WEEKLY_STD_RATE_CUT_OFF
, WEEKLY_STD_RATE_CUT_OFF ) WEEKLY_STD_RATE_CUT_OFF
, MONTHLY_TAX_CREDIT
, MONTHLY_STD_RATE_CUT_OFF
, D_TAX_BASIS
, D_INFO_SOURCE
, D_TAX_ASSESS_BASIS
, CERTIFICATE_ISSUE_DATE
FROM ( 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 ASSIGNMENT_ACTION_ID
, MAX (DECODE (PIV.NAME
, 'PERIOD WEEKLY TAX CREDIT'
, PRRV.RESULT_VALUE
, NULL)) PERIOD_WEEKLY_TAX_CREDIT
, MAX (DECODE (PIV.NAME
, 'WEEKLY TAX CREDIT'
, PRRV.RESULT_VALUE
, NULL )) WEEKLY_TAX_CREDIT
, MAX (DECODE (PIV.NAME
, 'PERIOD WEEKLY STANDARD RATE CUTOFF'
, PRRV.RESULT_VALUE
, NULL)) PERIOD_WEEKLY_STD_RATE_CUT_OFF
, 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'
, 'PERIOD WEEKLY TAX CREDIT'
, 'PERIOD WEEKLY STANDARD RATE CUTOFF'
, '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 )