VIEW: APPS.PER_ES_SOCIAL_SECURITY_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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