SELECT
  ou.organisationUnitID,
  ou.name,
  ou.statusCode,
  ou.creationDate,
  ouparentlink.organisationStructureID
INTO
  :orgUnitID,
  :orgUnitName,
  :orgUnitStatusCode,
  :orgUnitCreationDate,
  :orgStructureID 
FROM
  OrganisationUnit ou,
  OrgUnitParentLink ouparentlink 
WHERE ou.organisationUnitID = ouparentlink.organisationUnitID 
  AND ouparentlink.recordStatus <> :status 
  AND ou.organisationUnitID IN 
  ( 
  SELECT
    organisationUnitID 
  FROM
    OrgUnitPositionLink opl1,
    PositionHolderLink phl1,
    Position p1 
  WHERE p1.positionID = phl1.positionID 
    AND p1.leadPositionInd = '1' 
    AND phl1.positionID = opl1.positionID 
    AND phl1.userName = :userName 
    AND opl1.recordStatus <> :status 
    AND phl1.recordStatus <> :status 
  
  UNION
    
  SELECT
    organisationUnitID 
  FROM
    OrgUnitParentLink opar 
  WHERE opar.recordStatus <> :status 
    AND opar.parentOrganisationUnitID IN 
    ( 
    SELECT
      organisationUnitID 
    FROM
      OrgUnitPositionLink opl2,
      PositionHolderLink phl2,
      Position position1 
    WHERE position1.positionID = phl2.positionID 
      AND phl2.positionID = opl2.positionID 
      AND phl2.userName = :userName 
      AND position1.leadPositionInd = '1' 
      AND opl2.recordStatus <> :status 
      AND phl2.recordStatus <> :status 
    ) 
  ) 
  AND ou.recordStatus <> :status 
ORDER BY ou.name