eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_ORGANIZATIONS_ALL_EXPEND_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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