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.

[Home] [Help]

View: AHL_UE_ORGS_DEPTS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:
Implementation/DBA Data:	APPS.AHL_UE_ORGS_DEPTS_V

View Text

( SELECT AUE.UNIT_EFFECTIVITY_ID UNIT_EFFECTIVITY_ID
, 'N' SIM_UE_FLAG
, AUE.MR_HEADER_ID UE_MR_HDR_ID
, AMH.TITLE
, AMO.MR_TITLE
, AUE.FLEET_HEADER_ID UE_FLT_HDR_ID
, AFH.NAME FLEET_NAME
, AFH.OPERATING_ORG_ID FLEET_OP_ORG_ID
, AFH.DEPARTMENT_ID FLEET_DEPT_ID
, AMO.OPERATING_ORG_ID MR_OP_ORG_ID
, HRU2.NAME MR_OPERATING_ORG_NAME
, (CASE WHEN AUE.FLEET_HEADER_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL THEN NULL WHEN AUE.FLEET_HEADER_ID IS NOT NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND AFH.OPERATING_ORG_ID != AMO.OPERATING_ORG_ID THEN AFH.OPERATING_ORG_ID WHEN AUE.FLEET_HEADER_ID IS NOT NULL
AND AMO.MR_TITLE IS NULL THEN AFH.OPERATING_ORG_ID ELSE AMO.MAINTENANCE_ORG_ID END) MR_MAINTENANCE_ORG_ID
, (CASE WHEN AUE.FLEET_HEADER_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL THEN NULL WHEN AUE.FLEET_HEADER_ID IS NOT NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND AFH.OPERATING_ORG_ID != AMO.OPERATING_ORG_ID THEN (SELECT NAME
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID= AFH.OPERATING_ORG_ID) WHEN AUE.FLEET_HEADER_ID IS NOT NULL
AND AMO.MR_TITLE IS NULL THEN (SELECT NAME
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID= AFH.OPERATING_ORG_ID) ELSE HRU1.NAME END) MR_MAINTENANCE_ORG_NAME
, (CASE WHEN AUE.FLEET_HEADER_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL THEN NULL WHEN AUE.FLEET_HEADER_ID IS NOT NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND AFH.OPERATING_ORG_ID != AMO.OPERATING_ORG_ID THEN AFH.DEPARTMENT_ID WHEN AUE.FLEET_HEADER_ID IS NOT NULL
AND AMO.MR_TITLE IS NULL THEN AFH.DEPARTMENT_ID ELSE AMO.MAINTENANCE_DEPT_ID END) MR_MAINTENANCE_DEPT_ID
, (CASE WHEN AUE.FLEET_HEADER_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL THEN NULL WHEN AUE.FLEET_HEADER_ID IS NOT NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND AFH.OPERATING_ORG_ID != AMO.OPERATING_ORG_ID THEN (SELECT DESCRIPTION
FROM BOM_DEPARTMENTS
WHERE DEPARTMENT_ID = AFH.DEPARTMENT_ID) WHEN AUE.FLEET_HEADER_ID IS NOT NULL
AND AMO.MR_TITLE IS NULL THEN (SELECT DESCRIPTION
FROM BOM_DEPARTMENTS
WHERE DEPARTMENT_ID = AFH.DEPARTMENT_ID) ELSE ADSV.DEPT_DESCRIPTION END) MR_MAINTENANCE_DEPT_NAME FROM AHL_UNIT_EFFECTIVITIES_B AUE
, AHL_MR_HEADERS_B AMH
, AHL_MR_ORGANIZATIONS AMO
, AHL_FLEET_HEADERS_B AFH
, HR_ORGANIZATION_UNITS HRU1
, HR_ORGANIZATION_UNITS HRU2
, AHL_DEPARTMENT_SHIFTS_V ADSV WHERE AUE.MR_HEADER_ID = AMH.MR_HEADER_ID (+) AND AMH.TITLE = AMO.MR_TITLE (+) AND AUE.FLEET_HEADER_ID = AFH.FLEET_HEADER_ID (+) AND AMO.MAINTENANCE_ORG_ID = HRU1.ORGANIZATION_ID (+) AND AMO.OPERATING_ORG_ID = HRU2.ORGANIZATION_ID (+) AND AMO.MAINTENANCE_DEPT_ID = ADSV.DEPARTMENT_ID (+) AND ( (AUE.FLEET_HEADER_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NULL) OR (AFH.OPERATING_ORG_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND (0 = (SELECT COUNT(MR_ORGANIZATION_ID)
FROM AHL_MR_ORGANIZATIONS
WHERE MR_TITLE = AMO.MR_TITLE
AND OPERATING_ORG_ID IS NULL))) OR (AUE.FLEET_HEADER_ID IS NOT NULL
AND ((AFH.OPERATING_ORG_ID = AMO.OPERATING_ORG_ID) OR ( (0 = (SELECT COUNT(MR_ORGANIZATION_ID)
FROM AHL_MR_ORGANIZATIONS
WHERE MR_TITLE = AMO.MR_TITLE
AND OPERATING_ORG_ID = AFH.OPERATING_ORG_ID))
AND AFH.OPERATING_ORG_ID != NVL(AMO.OPERATING_ORG_ID
, -1)
AND AMO.OPERATING_ORG_ID IS NULL))) OR (AUE.FLEET_HEADER_ID IS NOT NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND ( (0 = (SELECT COUNT(MR_ORGANIZATION_ID)
FROM AHL_MR_ORGANIZATIONS
WHERE MR_TITLE = AMO.MR_TITLE
AND (OPERATING_ORG_ID IS NULL OR OPERATING_ORG_ID = AFH.OPERATING_ORG_ID))))) ) UNION SELECT AUS.SIMULATION_UE_ID UNIT_EFFECTIVITY_ID
, 'Y' SIM_UE_FLAG
, AUS.MR_HEADER_ID UE_MR_HDR_ID
, AMH.TITLE
, AMO.MR_TITLE
, AUS.FLEET_HEADER_ID UE_FLT_HDR_ID
, AFH.NAME FLEET_NAME
, AFH.OPERATING_ORG_ID FLEET_OP_ORG_ID
, AFH.DEPARTMENT_ID FLEET_DEPT_ID
, AMO.OPERATING_ORG_ID MR_OP_ORG_ID
, HRU2.NAME MR_OPERATING_ORG_NAME
, (CASE WHEN AUS.FLEET_HEADER_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL THEN NULL WHEN AUS.FLEET_HEADER_ID IS NOT NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND AFH.OPERATING_ORG_ID != AMO.OPERATING_ORG_ID THEN AFH.OPERATING_ORG_ID WHEN AUS.FLEET_HEADER_ID IS NOT NULL
AND AMO.MR_TITLE IS NULL THEN AFH.OPERATING_ORG_ID ELSE AMO.MAINTENANCE_ORG_ID END) MR_MAINTENANCE_ORG_ID
, (CASE WHEN AUS.FLEET_HEADER_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL THEN NULL WHEN AUS.FLEET_HEADER_ID IS NOT NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND AFH.OPERATING_ORG_ID != AMO.OPERATING_ORG_ID THEN (SELECT NAME
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID= AFH.OPERATING_ORG_ID) WHEN AUS.FLEET_HEADER_ID IS NOT NULL
AND AMO.MR_TITLE IS NULL THEN (SELECT NAME
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID= AFH.OPERATING_ORG_ID) ELSE HRU1.NAME END) MR_MAINTENANCE_ORG_NAME
, (CASE WHEN AUS.FLEET_HEADER_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL THEN NULL WHEN AUS.FLEET_HEADER_ID IS NOT NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND AFH.OPERATING_ORG_ID != AMO.OPERATING_ORG_ID THEN AFH.DEPARTMENT_ID WHEN AUS.FLEET_HEADER_ID IS NOT NULL
AND AMO.MR_TITLE IS NULL THEN AFH.DEPARTMENT_ID ELSE AMO.MAINTENANCE_DEPT_ID END) MR_MAINTENANCE_DEPT_ID
, (CASE WHEN AUS.FLEET_HEADER_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL THEN NULL WHEN AUS.FLEET_HEADER_ID IS NOT NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND AFH.OPERATING_ORG_ID != AMO.OPERATING_ORG_ID THEN (SELECT DESCRIPTION
FROM BOM_DEPARTMENTS
WHERE DEPARTMENT_ID = AFH.DEPARTMENT_ID) WHEN AUS.FLEET_HEADER_ID IS NOT NULL
AND AMO.MR_TITLE IS NULL THEN (SELECT DESCRIPTION
FROM BOM_DEPARTMENTS
WHERE DEPARTMENT_ID = AFH.DEPARTMENT_ID) ELSE ADSV.DEPT_DESCRIPTION END) MR_MAINTENANCE_DEPT_NAME FROM AHL_UE_SIMULATIONS AUS
, AHL_MR_HEADERS_B AMH
, AHL_MR_ORGANIZATIONS AMO
, AHL_FLEET_HEADERS_B AFH
, HR_ORGANIZATION_UNITS HRU1
, HR_ORGANIZATION_UNITS HRU2
, AHL_DEPARTMENT_SHIFTS_V ADSV WHERE AUS.MR_HEADER_ID = AMH.MR_HEADER_ID (+) AND AMH.TITLE = AMO.MR_TITLE (+) AND AUS.FLEET_HEADER_ID = AFH.FLEET_HEADER_ID (+) AND AMO.MAINTENANCE_ORG_ID = HRU1.ORGANIZATION_ID (+) AND AMO.OPERATING_ORG_ID = HRU2.ORGANIZATION_ID (+) AND AMO.MAINTENANCE_DEPT_ID = ADSV.DEPARTMENT_ID (+) AND ( (AUS.FLEET_HEADER_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NULL) OR (AFH.OPERATING_ORG_ID IS NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND (0 = (SELECT COUNT(MR_ORGANIZATION_ID)
FROM AHL_MR_ORGANIZATIONS
WHERE MR_TITLE = AMO.MR_TITLE
AND OPERATING_ORG_ID IS NULL))) OR (AUS.FLEET_HEADER_ID IS NOT NULL
AND ((AFH.OPERATING_ORG_ID = AMO.OPERATING_ORG_ID) OR ( (0 = (SELECT COUNT(MR_ORGANIZATION_ID)
FROM AHL_MR_ORGANIZATIONS
WHERE MR_TITLE = AMO.MR_TITLE
AND OPERATING_ORG_ID = AFH.OPERATING_ORG_ID))
AND AFH.OPERATING_ORG_ID != NVL(AMO.OPERATING_ORG_ID
, -1)
AND AMO.OPERATING_ORG_ID IS NULL))) OR (AUS.FLEET_HEADER_ID IS NOT NULL
AND AMO.OPERATING_ORG_ID IS NOT NULL
AND ( (0 = (SELECT COUNT(MR_ORGANIZATION_ID)
FROM AHL_MR_ORGANIZATIONS
WHERE MR_TITLE = AMO.MR_TITLE
AND (OPERATING_ORG_ID IS NULL OR OPERATING_ORG_ID = AFH.OPERATING_ORG_ID))))) ) ) ORDER BY UNIT_EFFECTIVITY_ID