VIEW: APPS.PAY_PAYE_ELEMENT_ENTRIES_V

Source

View Text - Preformatted

SELECT ele.rowid ROW_ID, ele.element_entry_id, min(decode(inv.name, 'Tax Code', eev.input_value_id, null)) INPUT_VALUE_ID1, min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) Tax_Code, min(decode(inv.name, 'Tax Basis', eev.input_value_id, null)) INPUT_VALUE_ID2, min(decode(inv.name, 'Tax Basis', substr(HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',eev.screen_entry_value),1,80),null)) D_Tax_Basis, min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) Tax_Basis, min(decode(inv.name, 'Refundable', eev.input_value_id, null)) INPUT_VALUE_ID3, min(decode(inv.name, 'Refundable', substr(HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',eev.screen_entry_value),1,80),null)) D_Refundable, min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) Refundable, min(decode(inv.name, 'Pay Previous', eev.input_value_id, null)) INPUT_VALUE_ID4, hr_chkfmt.changeformat(nvl(min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)), 0), 'M', 'GBP') D_Pay_Previous, min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)) Pay_Previous, min(decode(inv.name, 'Tax Previous', eev.input_value_id, null)) INPUT_VALUE_ID5, hr_chkfmt.changeformat(nvl(min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)), 0), 'M', 'GBP') D_Tax_Previous, min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)) Tax_Previous, min(decode(inv.name, 'Authority', eev.input_value_id, null)) INPUT_VALUE_ID6, min(decode(inv.name, 'Authority', substr(HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY',eev.screen_entry_value),1,80),null)) D_AUTHORITY, min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) Authority, ele.assignment_id, ele.effective_start_date, ele.effective_end_date, ele.entry_information_category, ele.entry_information1, ele.entry_information2 from pay_element_entries_f ele, pay_element_entry_values_f eev, pay_input_values_f inv, pay_element_links_f lnk, pay_element_types_f elt, fnd_sessions ses where ses.session_id = userenv('SESSIONID') and ele.element_entry_id = eev.element_entry_id and ses.effective_date between ele.effective_start_date and ele.effective_end_date and eev.input_value_id + 0 = inv.input_value_id and ses.effective_date between eev.effective_start_date and eev.effective_end_date and inv.element_type_id = elt.element_type_id and ses.effective_date between inv.effective_start_date and inv.effective_end_date and ele.element_link_id = lnk.element_link_id and elt.element_type_id = lnk.element_type_id and ses.effective_date between lnk.effective_start_date and lnk.effective_end_date and elt.element_name = 'PAYE Details' and ses.effective_date between elt.effective_start_date and elt.effective_end_date group by ele.rowid, ele.element_entry_id, ele.assignment_id, ele.entry_information_category, ele.entry_information1, ele.entry_information2, ele.effective_start_date, ele.effective_end_date

View Text - HTML Formatted

SELECT ELE.ROWID ROW_ID
, ELE.ELEMENT_ENTRY_ID
, MIN(DECODE(INV.NAME
, 'TAX CODE'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID1
, MIN(DECODE(INV.NAME
, 'TAX CODE'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) TAX_CODE
, MIN(DECODE(INV.NAME
, 'TAX BASIS'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID2
, MIN(DECODE(INV.NAME
, 'TAX BASIS'
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS'
, EEV.SCREEN_ENTRY_VALUE)
, 1
, 80)
, NULL)) D_TAX_BASIS
, MIN(DECODE(INV.NAME
, 'TAX BASIS'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) TAX_BASIS
, MIN(DECODE(INV.NAME
, 'REFUNDABLE'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID3
, MIN(DECODE(INV.NAME
, 'REFUNDABLE'
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE'
, EEV.SCREEN_ENTRY_VALUE)
, 1
, 80)
, NULL)) D_REFUNDABLE
, MIN(DECODE(INV.NAME
, 'REFUNDABLE'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) REFUNDABLE
, MIN(DECODE(INV.NAME
, 'PAY PREVIOUS'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID4
, HR_CHKFMT.CHANGEFORMAT(NVL(MIN(DECODE(INV.NAME
, 'PAY PREVIOUS'
, EEV.SCREEN_ENTRY_VALUE
, NULL))
, 0)
, 'M'
, 'GBP') D_PAY_PREVIOUS
, MIN(DECODE(INV.NAME
, 'PAY PREVIOUS'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) PAY_PREVIOUS
, MIN(DECODE(INV.NAME
, 'TAX PREVIOUS'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID5
, HR_CHKFMT.CHANGEFORMAT(NVL(MIN(DECODE(INV.NAME
, 'TAX PREVIOUS'
, EEV.SCREEN_ENTRY_VALUE
, NULL))
, 0)
, 'M'
, 'GBP') D_TAX_PREVIOUS
, MIN(DECODE(INV.NAME
, 'TAX PREVIOUS'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) TAX_PREVIOUS
, MIN(DECODE(INV.NAME
, 'AUTHORITY'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID6
, MIN(DECODE(INV.NAME
, 'AUTHORITY'
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY'
, EEV.SCREEN_ENTRY_VALUE)
, 1
, 80)
, NULL)) D_AUTHORITY
, MIN(DECODE(INV.NAME
, 'AUTHORITY'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) AUTHORITY
, ELE.ASSIGNMENT_ID
, ELE.EFFECTIVE_START_DATE
, ELE.EFFECTIVE_END_DATE
, ELE.ENTRY_INFORMATION_CATEGORY
, ELE.ENTRY_INFORMATION1
, ELE.ENTRY_INFORMATION2
FROM PAY_ELEMENT_ENTRIES_F ELE
, PAY_ELEMENT_ENTRY_VALUES_F EEV
, PAY_INPUT_VALUES_F INV
, PAY_ELEMENT_LINKS_F LNK
, PAY_ELEMENT_TYPES_F ELT
, FND_SESSIONS SES
WHERE SES.SESSION_ID = USERENV('SESSIONID')
AND ELE.ELEMENT_ENTRY_ID = EEV.ELEMENT_ENTRY_ID
AND SES.EFFECTIVE_DATE BETWEEN ELE.EFFECTIVE_START_DATE
AND ELE.EFFECTIVE_END_DATE
AND EEV.INPUT_VALUE_ID + 0 = INV.INPUT_VALUE_ID
AND SES.EFFECTIVE_DATE BETWEEN EEV.EFFECTIVE_START_DATE
AND EEV.EFFECTIVE_END_DATE
AND INV.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID
AND SES.EFFECTIVE_DATE BETWEEN INV.EFFECTIVE_START_DATE
AND INV.EFFECTIVE_END_DATE
AND ELE.ELEMENT_LINK_ID = LNK.ELEMENT_LINK_ID
AND ELT.ELEMENT_TYPE_ID = LNK.ELEMENT_TYPE_ID
AND SES.EFFECTIVE_DATE BETWEEN LNK.EFFECTIVE_START_DATE
AND LNK.EFFECTIVE_END_DATE
AND ELT.ELEMENT_NAME = 'PAYE DETAILS'
AND SES.EFFECTIVE_DATE BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE GROUP BY ELE.ROWID
, ELE.ELEMENT_ENTRY_ID
, ELE.ASSIGNMENT_ID
, ELE.ENTRY_INFORMATION_CATEGORY
, ELE.ENTRY_INFORMATION1
, ELE.ENTRY_INFORMATION2
, ELE.EFFECTIVE_START_DATE
, ELE.EFFECTIVE_END_DATE