VIEW: APPS.HR_FR_ESTABLISHMENTS_V

Source

View Text - Preformatted

SELECT OU1.ORGANIZATION_ID ,OU1.BUSINESS_GROUP_ID ,OU1.NAME ,OU1.DATE_FROM ,OU1.DATE_TO ,OU1.INTERNAL_ADDRESS_LINE ,OU1.LOCATION_ID ,OL.LOCATION_CODE ,OU1.COMMENTS ,OI1.ORG_INFORMATION1 ,OU2.NAME ,OI1.ORG_INFORMATION2 ,OI1.ORG_INFORMATION3 ,OI1.ORG_INFORMATION4 ,OI1.ORG_INFORMATION5 ,OI1.ORG_INFORMATION6 ,OI1.ORG_INFORMATION7 ,OI1.ORG_INFORMATION8 ,OI2.ORG_INFORMATION1 ,OU3.NAME ,OI2.ORG_INFORMATION2 ,OI2.ORG_INFORMATION3 ,OI2.ORG_INFORMATION4 ,OU4.NAME ,OI3.ORG_INFORMATION1 ,OU5.NAME ,OI3.ORG_INFORMATION2 ,OI4.ORG_INFORMATION1 ,OI4.ORG_INFORMATION2 ,OI4.ORG_INFORMATION3 ,OI4.ORG_INFORMATION4 ,OI5.ORG_INFORMATION5 ,OI5.ORG_INFORMATION2 FROM HR_LOCATIONS OL ,HR_ALL_ORGANIZATION_UNITS OU5 ,HR_ALL_ORGANIZATION_UNITS OU4 ,HR_ALL_ORGANIZATION_UNITS OU3 ,HR_ALL_ORGANIZATION_UNITS OU2 ,HR_ORGANIZATION_INFORMATION OI5 ,HR_ORGANIZATION_INFORMATION OI4 ,HR_ORGANIZATION_INFORMATION OI3 ,HR_ORGANIZATION_INFORMATION OI2 ,HR_ORGANIZATION_INFORMATION OI1 ,HR_ALL_ORGANIZATION_UNITS OU1 WHERE OU1.ORGANIZATION_ID = OI1.ORGANIZATION_ID (+) AND OU1.ORGANIZATION_ID = OI2.ORGANIZATION_ID (+) AND OU1.ORGANIZATION_ID = OI3.ORGANIZATION_ID (+) AND OU1.ORGANIZATION_ID = OI4.ORGANIZATION_ID (+) AND OU1.ORGANIZATION_ID = OI5.ORGANIZATION_ID AND OI1.ORG_INFORMATION_CONTEXT (+) || '' = 'FR_ESTAB_INFO' AND OI2.ORG_INFORMATION_CONTEXT (+) || '' = 'FR_ESTAB_URSSAF' AND OI3.ORG_INFORMATION_CONTEXT (+) || '' = 'FR_ESTAB_ASSEDIC' AND OI4.ORG_INFORMATION_CONTEXT (+) || '' = 'FR_ESTAB_PREV_INFO' AND OI5.ORG_INFORMATION_CONTEXT || '' = 'CLASS' AND OI5.ORG_INFORMATION1 = 'FR_ETABLISSEMENT' AND OI5.ORG_INFORMATION2 = 'Y' AND OI1.ORG_INFORMATION1 = OU2.ORGANIZATION_ID (+) AND OI2.ORG_INFORMATION1 = OU3.ORGANIZATION_ID (+) AND OI2.ORG_INFORMATION4 = OU4.ORGANIZATION_ID (+) AND OI3.ORG_INFORMATION1 = OU5.ORGANIZATION_ID (+) AND OL.LOCATION_ID (+) = OU1.LOCATION_ID

View Text - HTML Formatted

SELECT OU1.ORGANIZATION_ID
, OU1.BUSINESS_GROUP_ID
, OU1.NAME
, OU1.DATE_FROM
, OU1.DATE_TO
, OU1.INTERNAL_ADDRESS_LINE
, OU1.LOCATION_ID
, OL.LOCATION_CODE
, OU1.COMMENTS
, OI1.ORG_INFORMATION1
, OU2.NAME
, OI1.ORG_INFORMATION2
, OI1.ORG_INFORMATION3
, OI1.ORG_INFORMATION4
, OI1.ORG_INFORMATION5
, OI1.ORG_INFORMATION6
, OI1.ORG_INFORMATION7
, OI1.ORG_INFORMATION8
, OI2.ORG_INFORMATION1
, OU3.NAME
, OI2.ORG_INFORMATION2
, OI2.ORG_INFORMATION3
, OI2.ORG_INFORMATION4
, OU4.NAME
, OI3.ORG_INFORMATION1
, OU5.NAME
, OI3.ORG_INFORMATION2
, OI4.ORG_INFORMATION1
, OI4.ORG_INFORMATION2
, OI4.ORG_INFORMATION3
, OI4.ORG_INFORMATION4
, OI5.ORG_INFORMATION5
, OI5.ORG_INFORMATION2
FROM HR_LOCATIONS OL
, HR_ALL_ORGANIZATION_UNITS OU5
, HR_ALL_ORGANIZATION_UNITS OU4
, HR_ALL_ORGANIZATION_UNITS OU3
, HR_ALL_ORGANIZATION_UNITS OU2
, HR_ORGANIZATION_INFORMATION OI5
, HR_ORGANIZATION_INFORMATION OI4
, HR_ORGANIZATION_INFORMATION OI3
, HR_ORGANIZATION_INFORMATION OI2
, HR_ORGANIZATION_INFORMATION OI1
, HR_ALL_ORGANIZATION_UNITS OU1
WHERE OU1.ORGANIZATION_ID = OI1.ORGANIZATION_ID (+)
AND OU1.ORGANIZATION_ID = OI2.ORGANIZATION_ID (+)
AND OU1.ORGANIZATION_ID = OI3.ORGANIZATION_ID (+)
AND OU1.ORGANIZATION_ID = OI4.ORGANIZATION_ID (+)
AND OU1.ORGANIZATION_ID = OI5.ORGANIZATION_ID
AND OI1.ORG_INFORMATION_CONTEXT (+) || '' = 'FR_ESTAB_INFO'
AND OI2.ORG_INFORMATION_CONTEXT (+) || '' = 'FR_ESTAB_URSSAF'
AND OI3.ORG_INFORMATION_CONTEXT (+) || '' = 'FR_ESTAB_ASSEDIC'
AND OI4.ORG_INFORMATION_CONTEXT (+) || '' = 'FR_ESTAB_PREV_INFO'
AND OI5.ORG_INFORMATION_CONTEXT || '' = 'CLASS'
AND OI5.ORG_INFORMATION1 = 'FR_ETABLISSEMENT'
AND OI5.ORG_INFORMATION2 = 'Y'
AND OI1.ORG_INFORMATION1 = OU2.ORGANIZATION_ID (+)
AND OI2.ORG_INFORMATION1 = OU3.ORGANIZATION_ID (+)
AND OI2.ORG_INFORMATION4 = OU4.ORGANIZATION_ID (+)
AND OI3.ORG_INFORMATION1 = OU5.ORGANIZATION_ID (+)
AND OL.LOCATION_ID (+) = OU1.LOCATION_ID