VIEW: APPS.PAY_NI_ELEMENT_ENTRIES_V

Source

View Text - Preformatted

SELECT pee.rowid ROW_ID , pee.element_entry_id , min(decode(piv.name, 'Category', eev.input_value_id, null)) INPUT_VALUE_ID1 , min(decode(piv.name, 'Category', eev.screen_entry_value, null)) Category , min(decode(piv.name, 'Category', substr(HR_GENERAL.DECODE_LOOKUP ('GB_CATEGORY' ,eev.screen_entry_value),1,80), null)) Category_Meaning , min(decode(piv.name, 'Certificate', eev.input_value_id, null)) INPUT_VALUE_ID2 , min(decode(piv.name, 'Certificate', eev.screen_entry_value, null)) Certificate , min(decode(piv.name, 'Certificate', substr(HR_GENERAL.DECODE_LOOKUP('GB_CERTIFICATE' ,eev.screen_entry_value),1,80), null)) Certificate_Meaning , min(decode(piv.name, 'Date of Renewal', eev.input_value_id, null)) INPUT_VALUE_ID3 , min(decode(piv.name, 'Date of Renewal', eev.screen_entry_value, null)) Date_of_Renewal , min(decode(piv.name, 'Pension', eev.input_value_id, null)) INPUT_VALUE_ID4 , min(decode(piv.name, 'Pension', eev.screen_entry_value, null)) Pension , min(decode(piv.name, 'Pension', substr(HR_GENERAL.DECODE_LOOKUP('GB_PENSION', eev.screen_entry_value),1,80), null)) Pension_Meaning , min(decode(piv.name, 'Process Type', eev.input_value_id, null)) INPUT_VALUE_ID5 , min(decode(piv.name, 'Process Type', eev.screen_entry_value, null)) Process_Type , min(decode(piv.name, 'Process Type', substr(HR_GENERAL.DECODE_LOOKUP('GB_PROCESS' ,eev.screen_entry_value),1,80), null)) Process_Type_Meaning , min(decode(piv.name, 'Periods', eev.input_value_id, null)) INPUT_VALUE_ID6 , min(decode(piv.name, 'Periods', eev.screen_entry_value, null)) Periods , min(decode(piv.name, 'Priority Period Type', eev.input_value_id, null)) INPUT_VALUE_ID7 , min(decode(piv.name, 'Priority Period Type', eev.screen_entry_value, null)) period_type , min(decode(piv.name, 'SCON', eev.input_value_id, null)) INPUT_VALUE_ID8 , min(decode(piv.name, 'SCON', substr(HR_GENERAL.DECODE_LOOKUP('GB_SCON' ,eev.screen_entry_value),1,80), null)) SCON , pee.effective_start_date , pee.effective_end_date , pee.assignment_id , min(decode(piv.name, 'Priority Period Type', eev.screen_entry_value, null)) Display_period_type from pay_element_entries_f pee , pay_element_entry_values_f eev , pay_input_values_f piv , pay_element_links_f pel , pay_element_types_f pet , fnd_sessions ses where ses.session_id = userenv('SESSIONID') and pee.element_entry_id = eev.element_entry_id and ses.effective_date between pee.effective_start_date and pee.effective_end_date and eev.input_value_id + 0 = piv.input_value_id and ses.effective_date between eev.effective_start_date and eev.effective_end_date and piv.element_type_id = pet.element_type_id and ses.effective_date between piv.effective_start_date and piv.effective_end_date and pee.element_link_id = pel.element_link_id and pet.element_type_id = pel.element_type_id and ses.effective_date between pel.effective_start_date and pel.effective_end_date and pet.element_name = 'NI' and ses.effective_date between pet.effective_start_date and pet.effective_end_date group by pee.rowid, pee.element_entry_id, pee.assignment_id, pee.effective_start_date, pee.effective_end_date

View Text - HTML Formatted

SELECT PEE.ROWID ROW_ID
, PEE.ELEMENT_ENTRY_ID
, MIN(DECODE(PIV.NAME
, 'CATEGORY'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID1
, MIN(DECODE(PIV.NAME
, 'CATEGORY'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) CATEGORY
, MIN(DECODE(PIV.NAME
, 'CATEGORY'
, SUBSTR(HR_GENERAL.DECODE_LOOKUP ('GB_CATEGORY'
, EEV.SCREEN_ENTRY_VALUE)
, 1
, 80)
, NULL)) CATEGORY_MEANING
, MIN(DECODE(PIV.NAME
, 'CERTIFICATE'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID2
, MIN(DECODE(PIV.NAME
, 'CERTIFICATE'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) CERTIFICATE
, MIN(DECODE(PIV.NAME
, 'CERTIFICATE'
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('GB_CERTIFICATE'
, EEV.SCREEN_ENTRY_VALUE)
, 1
, 80)
, NULL)) CERTIFICATE_MEANING
, MIN(DECODE(PIV.NAME
, 'DATE OF RENEWAL'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID3
, MIN(DECODE(PIV.NAME
, 'DATE OF RENEWAL'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) DATE_OF_RENEWAL
, MIN(DECODE(PIV.NAME
, 'PENSION'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID4
, MIN(DECODE(PIV.NAME
, 'PENSION'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) PENSION
, MIN(DECODE(PIV.NAME
, 'PENSION'
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('GB_PENSION'
, EEV.SCREEN_ENTRY_VALUE)
, 1
, 80)
, NULL)) PENSION_MEANING
, MIN(DECODE(PIV.NAME
, 'PROCESS TYPE'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID5
, MIN(DECODE(PIV.NAME
, 'PROCESS TYPE'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) PROCESS_TYPE
, MIN(DECODE(PIV.NAME
, 'PROCESS TYPE'
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('GB_PROCESS'
, EEV.SCREEN_ENTRY_VALUE)
, 1
, 80)
, NULL)) PROCESS_TYPE_MEANING
, MIN(DECODE(PIV.NAME
, 'PERIODS'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID6
, MIN(DECODE(PIV.NAME
, 'PERIODS'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) PERIODS
, MIN(DECODE(PIV.NAME
, 'PRIORITY PERIOD TYPE'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID7
, MIN(DECODE(PIV.NAME
, 'PRIORITY PERIOD TYPE'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) PERIOD_TYPE
, MIN(DECODE(PIV.NAME
, 'SCON'
, EEV.INPUT_VALUE_ID
, NULL)) INPUT_VALUE_ID8
, MIN(DECODE(PIV.NAME
, 'SCON'
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('GB_SCON'
, EEV.SCREEN_ENTRY_VALUE)
, 1
, 80)
, NULL)) SCON
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE
, PEE.ASSIGNMENT_ID
, MIN(DECODE(PIV.NAME
, 'PRIORITY PERIOD TYPE'
, EEV.SCREEN_ENTRY_VALUE
, NULL)) DISPLAY_PERIOD_TYPE
FROM PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_ENTRY_VALUES_F EEV
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_TYPES_F PET
, FND_SESSIONS SES
WHERE SES.SESSION_ID = USERENV('SESSIONID')
AND PEE.ELEMENT_ENTRY_ID = EEV.ELEMENT_ENTRY_ID
AND SES.EFFECTIVE_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND EEV.INPUT_VALUE_ID + 0 = PIV.INPUT_VALUE_ID
AND SES.EFFECTIVE_DATE BETWEEN EEV.EFFECTIVE_START_DATE
AND EEV.EFFECTIVE_END_DATE
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND SES.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PEE.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID
AND PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND SES.EFFECTIVE_DATE BETWEEN PEL.EFFECTIVE_START_DATE
AND PEL.EFFECTIVE_END_DATE
AND PET.ELEMENT_NAME = 'NI'
AND SES.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE GROUP BY PEE.ROWID
, PEE.ELEMENT_ENTRY_ID
, PEE.ASSIGNMENT_ID
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE