SELECT o.business_group_id BUSINESS_GROUP_ID, o.organization_id ORGANIZATION_ID, otl.name NAME, o.date_from DATE_FROM, o.date_to DATE_TO, o3.org_information2 LOB_CODE, (select ffvvl2.description from fnd_flex_values_vl ffvvl2 where ffvvl2.flex_value_set_id = o3.org_information4 and ffvvl2.FLEX_VALUE = o3.org_information2) LOB_DESCRIPTION, o3.org_information1 COMPANY_CODE, (select ffvvl1.description from fnd_flex_values_vl ffvvl1 where ffvvl1.flex_value_set_id = o3.org_information3 and ffvvl1.FLEX_VALUE = o3.org_information1) COMPANY, o3.org_information3 SUBSIDIARY_VALUESET, o3.org_information4 LOB_VALUESET, o.location_id location_id, hrlkp.meaning org_type from hr_all_organization_units o, hr_all_organization_units_tl otl, hr_organization_information o2, hr_organization_information o3, hr_lookups hrlkp WHERE o.organization_id = o2.organization_id and o.organization_id = o3.organization_id (+) and o2.org_information_context = 'CLASS' and o3.org_information_context(+) = 'AMW_AUDIT_UNIT' and o2.org_information1 = 'AMW_AUDIT_UNIT' and o2.org_information2 = 'Y' and o.organization_id = otl.organization_id and otl.language = userenv('LANG') AND hrlkp.LOOKUP_TYPE(+) = 'ORG_TYPE' AND hrlkp.LOOKUP_CODE(+) = o.type
SELECT O.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, O.ORGANIZATION_ID ORGANIZATION_ID
, OTL.NAME NAME
, O.DATE_FROM DATE_FROM
, O.DATE_TO DATE_TO
, O3.ORG_INFORMATION2 LOB_CODE
, (SELECT FFVVL2.DESCRIPTION
FROM FND_FLEX_VALUES_VL FFVVL2
WHERE FFVVL2.FLEX_VALUE_SET_ID = O3.ORG_INFORMATION4
AND FFVVL2.FLEX_VALUE = O3.ORG_INFORMATION2) LOB_DESCRIPTION
, O3.ORG_INFORMATION1 COMPANY_CODE
, (SELECT FFVVL1.DESCRIPTION
FROM FND_FLEX_VALUES_VL FFVVL1
WHERE FFVVL1.FLEX_VALUE_SET_ID = O3.ORG_INFORMATION3
AND FFVVL1.FLEX_VALUE = O3.ORG_INFORMATION1) COMPANY
, O3.ORG_INFORMATION3 SUBSIDIARY_VALUESET
, O3.ORG_INFORMATION4 LOB_VALUESET
, O.LOCATION_ID LOCATION_ID
, HRLKP.MEANING ORG_TYPE
FROM HR_ALL_ORGANIZATION_UNITS O
, HR_ALL_ORGANIZATION_UNITS_TL OTL
, HR_ORGANIZATION_INFORMATION O2
, HR_ORGANIZATION_INFORMATION O3
, HR_LOOKUPS HRLKP
WHERE O.ORGANIZATION_ID = O2.ORGANIZATION_ID
AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID (+)
AND O2.ORG_INFORMATION_CONTEXT = 'CLASS'
AND O3.ORG_INFORMATION_CONTEXT(+) = 'AMW_AUDIT_UNIT'
AND O2.ORG_INFORMATION1 = 'AMW_AUDIT_UNIT'
AND O2.ORG_INFORMATION2 = 'Y'
AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
AND OTL.LANGUAGE = USERENV('LANG')
AND HRLKP.LOOKUP_TYPE(+) = 'ORG_TYPE'
AND HRLKP.LOOKUP_CODE(+) = O.TYPE
|
|
|