View: PA_ORGANIZATIONS_ALL_EXPEND_V

Product:	PA - Projects
Description:	View that shows the organizations that can be used as expenditure organization in Cross Charge Options Setup Form .
Implementation/DBA Data:	APPS.PA_ORGANIZATIONS_ALL_EXPEND_V

View Text

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' ) )

Columns