LEGAL ENTITIERS & ORGANIZATIONS

/* LEGAL ENTITIERS & ORGANIZATIONS
Gives an overview of the legal entity and operating unit configuration across multiple OU
This is used when implementing multiple offices to ensure consistent setup across countries and between environments. 
Where clause can be added or commented out to just look at specific countries.if consistent naming conventions have been used. 
( Tested on Vision 11.5.10.2  June 2007 )*/
SELECT HRO.ORGANIZATION_ID
,    HRO.NAME
,    HOI.ORG_INFORMATION_CONTEXT
,    SOB2.NAME "LE SET OF BOOKS"
--,    HOI.ORG_INFORMATION1
,    HRO_LE.NAME "OU LEGAL ENT"
,    HOI.ORG_INFORMATION2 "LE VAT CODES"
--,    HOI.ORG_INFORMATION3
,    SOB.NAME "OU SET OF BOOKS"
FROM HR_ALL_ORGANIZATION_UNITS_TL HRO, HR_ORGANIZATION_INFORMATION_V  HOI, GL_SETS_OF_BOOKS SOB,GL_SETS_OF_BOOKS SOB2, HR_ALL_ORGANIZATION_UNITS_TL HRO_LE
WHERE HOI.ORG_INFORMATION_CONTEXT IN ('Legal Entity Accounting','Operating Unit Information')
AND HRO.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND TO_CHAR(SOB.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION3
AND TO_CHAR(SOB2.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION1
AND TO_CHAR(HRO_LE.ORGANIZATION_ID(+)) = HOI.ORG_INFORMATION2
--AND SUBSTR(HRO.NAME,1,2) IN ('BE','LU','ES')
ORDER BY 2,3