View: PAY_NI_ELEMENT_ENTRIES_V

Product:	PAY - Payroll
Description:	View based on PAY_ELEMENT_ENTRY_VALUES_F used by the PAYGBTAX form to display NI details.
Implementation/DBA Data:	APPS.PAY_NI_ELEMENT_ENTRIES_V

View Text

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

Columns