SELECT ORG.ORGANIZATION_ID , ORG.BUSINESS_GROUP_ID , ORG.NAME , ORG.DATE_FROM , ORG.DATE_TO , 'Y' FROM HR_ORGANIZATION_UNITS ORG , PA_IMPLEMENTATIONS_ALL IMP WHERE ORG.BUSINESS_GROUP_ID = IMP.BUSINESS_GROUP_ID AND EXISTS (SELECT 'X' FROM HR_ORGANIZATION_INFORMATION INFO WHERE INFO.ORGANIZATION_ID = ORG.ORGANIZATION_ID AND INFO.ORG_INFORMATION1 = 'PA_EXPENDITURE_ORG' AND INFO.ORG_INFORMATION_CONTEXT||'' = 'CLASS' AND INFO.ORG_INFORMATION2 = 'Y' ) AND ORG.ORGANIZATION_ID IN /*IN EXP ORG HIERARCHY */ (SELECT SE.ORGANIZATION_ID_CHILD FROM PER_ORG_STRUCTURE_ELEMENTS SE WHERE SE.ORG_STRUCTURE_VERSION_ID = IMP.EXP_ORG_STRUCTURE_VERSION_ID CONNECT BY PRIOR SE.ORGANIZATION_ID_CHILD = SE.ORGANIZATION_ID_PARENT AND SE.ORG_STRUCTURE_VERSION_ID = IMP.EXP_ORG_STRUCTURE_VERSION_ID START WITH SE.ORGANIZATION_ID_PARENT = IMP.EXP_START_ORG_ID AND SE.ORG_STRUCTURE_VERSION_ID = IMP.EXP_ORG_STRUCTURE_VERSION_ID UNION SELECT IMP.EXP_START_ORG_ID FROM SYS.DUAL ) UNION ALL /* INACTIVE ORGS WHICH HAVE BEEN USED IN PA BUT ARE NO LONGER IN THE CURRENT HIERARCHY SEPCIFIED FOR EXP IN IMPLEMENTATIONS */ SELECT ORG.ORGANIZATION_ID , ORG.BUSINESS_GROUP_ID , ORG.NAME , ORG.DATE_FROM , ORG.DATE_TO , 'N' FROM HR_ORGANIZATION_UNITS ORG , PA_ALL_ORGANIZATIONS ORG2 , PA_IMPLEMENTATIONS_ALL IMP WHERE ORG2.ORGANIZATION_ID = ORG.ORGANIZATION_ID AND ORG2.ORG_ID = IMP.ORG_ID AND ORG.BUSINESS_GROUP_ID = IMP.BUSINESS_GROUP_ID AND ORG2.PA_ORG_USE_TYPE = 'EXPENDITURES' AND ORG.ORGANIZATION_ID NOT IN /* IN EXP ORG HIERARCHY */ ( (SELECT SE.ORGANIZATION_ID_CHILD FROM PER_ORG_STRUCTURE_ELEMENTS SE WHERE SE.ORG_STRUCTURE_VERSION_ID = IMP.EXP_ORG_STRUCTURE_VERSION_ID CONNECT BY PRIOR SE.ORGANIZATION_ID_CHILD = SE.ORGANIZATION_ID_PARENT AND SE.ORG_STRUCTURE_VERSION_ID = IMP.EXP_ORG_STRUCTURE_VERSION_ID START WITH SE.ORGANIZATION_ID_PARENT = IMP.EXP_START_ORG_ID AND SE.ORG_STRUCTURE_VERSION_ID = IMP.EXP_ORG_STRUCTURE_VERSION_ID UNION SELECT IMP.EXP_START_ORG_ID FROM SYS.DUAL ) INTERSECT ( SELECT INFO.ORGANIZATION_ID FROM HR_ORGANIZATION_INFORMATION INFO WHERE INFO.ORG_INFORMATION1 = 'PA_EXPENDITURE_ORG' AND INFO.ORG_INFORMATION_CONTEXT = 'CLASS' AND INFO.ORG_INFORMATION2 = 'Y' ) )