VIEW: APPS.AHL_UE_ORGS_DEPTS_V

Source

View Text - Preformatted

(
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
   

View Text - HTML Formatted

( 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