View: PER_ES_TAX_DETAILS_V

Product:	PER - Human Resources
Description:	Used in the Social Security and Tax Forms
Implementation/DBA Data:	APPS.PER_ES_TAX_DETAILS_V

View Text

SELECT E.ROWID AS ROW_ID
, E.ASSIGNMENT_ID AS ASSIGNMENT_ID
, E.ELEMENT_ENTRY_ID AS ELEMENT_ENTRY_ID
, L.ELEMENT_LINK_ID AS ELEMENT_LINK_ID
, T.ELEMENT_TYPE_ID
, MAX(E.OBJECT_VERSION_NUMBER) AS OBJECT_VERSION_NUMBER
, MIN(DECODE(I.NAME
, 'WORKER STATUS'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SCREEN_ENTRY_VALUE0
, HR_GENERAL.DECODE_FND_COMM_LOOKUP('ES_WORKER_STATUS'
, MIN(DECODE(I.NAME
, 'WORKER STATUS'
, V.SCREEN_ENTRY_VALUE
, NULL))) AS WORKER_STATUS
, MIN(DECODE(I.NAME
, 'PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SCREEN_ENTRY_VALUE1
, HR_GENERAL.DECODE_FND_COMM_LOOKUP('ES_PAYMENT_KEY'
, MIN(DECODE(I.NAME
, 'PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL))) AS PAYMENT_KEY
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SCREEN_ENTRY_VALUE2
, DECODE(MIN(DECODE(I.NAME
, 'PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL))
, 'B'
, HR_GENERAL.DECODE_LOOKUP('ES_SUB_PAYMENT_KEY_B'
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)))
, 'F'
, HR_GENERAL.DECODE_LOOKUP('ES_SUB_PAYMENT_KEY_F'
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)))
, 'G'
, HR_GENERAL.DECODE_LOOKUP('ES_SUB_PAYMENT_KEY_G'
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)))
, 'H'
, HR_GENERAL.DECODE_LOOKUP('ES_SUB_PAYMENT_KEY_H'
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)))
, 'L'
, HR_GENERAL.DECODE_LOOKUP('ES_SUB_PAYMENT_KEY_L'
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)))
, NULL) SUB_PAYMENT_KEY
, MIN(DECODE(I.NAME
, 'CHANGE OF RESIDENCY DATE'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS CHANGE_OF_RESIDENCY_DATE
, MIN(DECODE(I.NAME
, 'ANY OTHER SITUATIONS'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS OTHER_SITUATION
, MIN(DECODE(I.NAME
, 'TAX WITHHOLDING RATE'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS TAX_WITHHOLDING_RATE
, E.EFFECTIVE_START_DATE
, E.EFFECTIVE_END_DATE
FROM PAY_ELEMENT_ENTRIES_F E
, PAY_INPUT_VALUES_F I
, PAY_ELEMENT_ENTRY_VALUES_F V
, PAY_ELEMENT_TYPES_F T
, PAY_ELEMENT_LINKS_F L
, FND_SESSIONS F
WHERE E.ELEMENT_ENTRY_ID = V.ELEMENT_ENTRY_ID
AND V.INPUT_VALUE_ID = I.INPUT_VALUE_ID
AND I.ELEMENT_TYPE_ID = T.ELEMENT_TYPE_ID
AND T.ELEMENT_TYPE_ID = L.ELEMENT_TYPE_ID
AND L.ELEMENT_LINK_ID = E.ELEMENT_LINK_ID
AND T.ELEMENT_NAME = 'TAX DETAILS'
AND T.LEGISLATION_CODE = 'ES'
AND F.SESSION_ID = USERENV('SESSIONID')
AND F.EFFECTIVE_DATE BETWEEN E.EFFECTIVE_START_DATE
AND E.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN V.EFFECTIVE_START_DATE
AND V.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN I.EFFECTIVE_START_DATE
AND I.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN T.EFFECTIVE_START_DATE
AND T.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN L.EFFECTIVE_START_DATE
AND L.EFFECTIVE_END_DATE GROUP BY E.ROWID
, E.ELEMENT_ENTRY_ID
, L.ELEMENT_LINK_ID
, T.ELEMENT_TYPE_ID
, E.ASSIGNMENT_ID
, E.EFFECTIVE_START_DATE
, E.EFFECTIVE_END_DATE

Columns