View: PER_ES_SOCIAL_SECURITY_V

Product:	PER - Human Resources
Description:	Used in the Social Security and Tax Forms
Implementation/DBA Data:	APPS.PER_ES_SOCIAL_SECURITY_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 AS ELEMENT_TYPE_ID
, MAX(E.OBJECT_VERSION_NUMBER) AS OBJECT_VERSION_NUMBER
, MIN(DECODE(I.NAME
, 'SOCIAL SECURITY IDENTIFIER'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SOCIAL_SECURITY_IDENTIFIER
, MIN(DECODE(I.NAME
, 'SS EPIGRAPH CODE'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS EPIGRAPH_CODE_CODE
, MIN(DECODE(I.NAME
, 'SS EPIGRAPH CODE'
, V.SCREEN_ENTRY_VALUE
, NULL)) || ' - ' || HR_GENERAL.DECODE_FND_COMM_LOOKUP('ES_EPIGRAPHS'
, MIN(DECODE(I.NAME
, 'SS EPIGRAPH CODE'
, V.SCREEN_ENTRY_VALUE
, NULL))) AS EPIGRAPH_CODE
, MIN(DECODE(I.NAME
, 'CONTRACT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS CONTRACT_KEY
, MIN(DECODE(I.NAME
, 'REBATE REDUCTION'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS REBATE
, MIN(DECODE(I.NAME
, 'SS EPIGRAPH 114'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SS_EPIGRAPH_114_CODE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, MIN(DECODE(I.NAME
, 'SS EPIGRAPH 114'
, V.SCREEN_ENTRY_VALUE
, NULL))) AS SS_EPIGRAPH_114
, MIN(DECODE(I.NAME
, 'SS EPIGRAPH 126'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SS_EPIGRAPH_126_CODE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, MIN(DECODE(I.NAME
, 'SS EPIGRAPH 126'
, V.SCREEN_ENTRY_VALUE
, NULL))) AS SS_EPIGRAPH_126
, MIN(DECODE(I.NAME
, 'CONTRIBUTION INCREMENT'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS CONTRIBUTION_INCREMENT
, MIN(DECODE(I.NAME
, 'UNEMPLOYMENT STATUS'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS UNEMPLOYMENT_STATUS_CODE
, HR_GENERAL.DECODE_LOOKUP('ES_UNEMPLOYMENT_STATUS'
, MIN(DECODE(I.NAME
, 'UNEMPLOYMENT STATUS'
, V.SCREEN_ENTRY_VALUE
, NULL))) AS UNEMPLOYMENT_STATUS
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 1
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 1
, 1))
, NULL)) AS SUPPORT_PROGRAM_CODE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 1
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 1
, 1))
, NULL))) AS SUPPORT_PROGRAM
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 2
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 2
, 1))
, NULL)) AS REHIRED_DISABLED_CODE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 2
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 2
, 1))
, NULL))) AS REHIRED_DISABLED
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 3
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 3
, 1))
, NULL)) AS FIRST_CONTRACTOR_CODE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 3
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 3
, 1))
, NULL))) AS FIRST_CONTRACTOR
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 4
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 4
, 1))
, NULL)) AS SOCIAL_EXCLUSION_CODE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 4
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 4
, 1))
, NULL))) AS SOCIAL_EXCLUSION
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 5
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 5
, 1))
, NULL)) AS UNDER_REPRESENTED_WOMEN_CODE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 5
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 5
, 1))
, NULL))) AS UNDER_REPRESENTED_WOMEN
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 6
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 6
, 1))
, NULL)) AS AFTER_CHILDBIRTH_CODE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 6
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 6
, 1))
, NULL))) AS AFTER_CHILDBIRTH
, MIN(DECODE(I.NAME
, 'MULTIPLE EMPLOYMENT'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS MULTIPLE_EMPLOYMENT_CODE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, MIN(DECODE(I.NAME
, 'MULTIPLE EMPLOYMENT'
, V.SCREEN_ENTRY_VALUE
, NULL))) AS MULTIPLE_EMPLOYMENT
, MIN(DECODE(I.NAME
, 'SUPPORTING LAW'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SUPPORTING_LAW
, MIN(DECODE(I.NAME
, 'SUPPORTING LAW DATE'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SUPPORTING_LAW_DATE_FULL
, MIN(DECODE(I1.NAME
, 'REBATE REDUCTION DATE'
, V1.SCREEN_ENTRY_VALUE
, NULL)) AS REBATE_REDUCTION_DATE_FULL
, MIN(DECODE(I1.NAME
, 'SPECIAL RELATIONSHIP DATE'
, V1.SCREEN_ENTRY_VALUE
, NULL)) AS SPECIAL_RELATIONSHIP_DATE_FULL
, MIN(DECODE(I1.NAME
, 'WORK CENTER CAC'
, V1.SCREEN_ENTRY_VALUE
, NULL)) AS WORK_CENTER_CAC_CODE
, MIN(DECODE(I1.NAME
, 'WORK CENTER CAC'
, V1.SCREEN_ENTRY_VALUE
, NULL)) || ' - ' || HR_GENERAL.DECODE_LOOKUP('ES_CONTRIBUTION_ACCOUNT_TYPE'
, MIN(DECODE(I1.NAME
, 'WORK CENTER CAC'
, V1.SCREEN_ENTRY_VALUE
, NULL))) AS WORK_CENTER_CAC
, E.EFFECTIVE_START_DATE
, E.EFFECTIVE_END_DATE
, E1.ROWID AS ROW_ID1
, E1.ELEMENT_ENTRY_ID AS ELEMENT_ENTRY_ID1
, L1.ELEMENT_LINK_ID AS ELEMENT_LINK_ID1
, T1.ELEMENT_TYPE_ID AS ELEMENT_TYPE_ID1
, MAX(E1.OBJECT_VERSION_NUMBER) AS OBJECT_VERSION_NUMBER1
, MIN(DECODE(I1.NAME
, 'PERCENTAGE'
, V1.SCREEN_ENTRY_VALUE
, NULL)) AS MULTIPLE_EMPLOYMENT_PERCENT
, MIN(DECODE(I1.NAME
, 'AUTHORIZATION NUMBER'
, V1.SCREEN_ENTRY_VALUE
, NULL)) AS AUTHORIZATION_NUMBER
, MIN(DECODE(I1.NAME
, 'CONTRIBUTION FOR NIR'
, V1.SCREEN_ENTRY_VALUE
, NULL)) AS NON_INDUSTRIAL_RISK
, MIN(DECODE(I1.NAME
, 'CONTRIBUTION FOR WMF'
, V1.SCREEN_ENTRY_VALUE
, NULL)) AS WAGE_MAINTENANCE_FUND
, MIN(DECODE(I1.NAME
, 'MAX CONTRIBUTION BASE'
, V1.SCREEN_ENTRY_VALUE
, NULL)) AS MAXIMUM_CONTRIBUTION
, MIN(DECODE(I1.NAME
, 'MIN CONTRIBUTION BASE'
, V1.SCREEN_ENTRY_VALUE
, NULL)) AS MINIMUM_CONTRIBUTION
, MIN(DECODE(I.NAME
, 'SPECIAL RELATIONSHIP TYPE'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SPECIAL_RELATIONSHIP_TYPE_CODE
, MIN(DECODE(I.NAME
, 'SPECIAL RELATIONSHIP TYPE'
, V.SCREEN_ENTRY_VALUE
, NULL)) || DECODE(MIN(DECODE(I.NAME
, 'SPECIAL RELATIONSHIP TYPE'
, V.SCREEN_ENTRY_VALUE
, NULL))
, NULL
, NULL
, ' - ') || HR_GENERAL.DECODE_FND_COMM_LOOKUP('ES_SPECIAL_RELATIONSHIP_TYPE'
, MIN(DECODE(I.NAME
, 'SPECIAL RELATIONSHIP TYPE'
, V.SCREEN_ENTRY_VALUE
, NULL))) AS SPECIAL_RELATIONSHIP_TYPE
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 7
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 7
, 1))
, NULL)) AS TRANSFERRED_CONTRACT_FLAG_CODE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, MIN(DECODE(I.NAME
, 'CONTRACT INDICATORS'
, DECODE(SUBSTR(V.SCREEN_ENTRY_VALUE
, 7
, 1)
, 'Z'
, NULL
, SUBSTR(V.SCREEN_ENTRY_VALUE
, 7
, 1))
, NULL))) AS TRANSFERRED_CONTRACT_FLAG
, MIN(DECODE(I.NAME
, 'RETIREMENT AGE REDUCTION'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS RETIREMENT_AGE_REDUCTION
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
, PAY_ELEMENT_ENTRIES_F E1
, PAY_INPUT_VALUES_F I1
, PAY_ELEMENT_ENTRY_VALUES_F V1
, PAY_ELEMENT_TYPES_F T1
, PAY_ELEMENT_LINKS_F L1
, 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 = 'SOCIAL SECURITY DETAILS'
AND T.LEGISLATION_CODE = 'ES'
AND E1.ELEMENT_ENTRY_ID = V1.ELEMENT_ENTRY_ID
AND V1.INPUT_VALUE_ID = I1.INPUT_VALUE_ID
AND I1.ELEMENT_TYPE_ID = T1.ELEMENT_TYPE_ID
AND T1.ELEMENT_TYPE_ID = L1.ELEMENT_TYPE_ID
AND L1.ELEMENT_LINK_ID = E1.ELEMENT_LINK_ID
AND T1.ELEMENT_NAME = 'MULTIPLE EMPLOYMENT DETAILS'
AND T1.LEGISLATION_CODE = 'ES'
AND E.ASSIGNMENT_ID = E1.ASSIGNMENT_ID
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
AND F.EFFECTIVE_DATE BETWEEN E1.EFFECTIVE_START_DATE
AND E1.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN V1.EFFECTIVE_START_DATE
AND V1.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN I1.EFFECTIVE_START_DATE
AND I1.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN T1.EFFECTIVE_START_DATE
AND T1.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN L1.EFFECTIVE_START_DATE
AND L1.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
, E1.ROWID
, E1.ELEMENT_ENTRY_ID
, L1.ELEMENT_LINK_ID
, T1.ELEMENT_TYPE_ID

Columns