SELECT
  OrganisationUnit.businessTypeCode,
  OrganisationUnit.name,
  OrganisationUnit.recordStatus,
  OrganisationUnit.statusCode,
  OrganisationUnit.locationID,
  DefaultLocation.name,
  DefaultLocation.locationStructureID,
  ParentOrganisationUnit.parentOrganisationUnitID,
  ParentOrganisationUnit.name,
  Location.locationCount
INTO
  :businessTypeCode,
  :name,
  :recordStatus,
  :statusCode,
  :defaultLocationID,
  :defaultLocationName,
  :defaultLocationStructureID,
  :parentOrgUnitID,
  :parentOrgUnitName,
  :locationCount
FROM
  OrganisationUnit
LEFT OUTER JOIN
  (SELECT
    OrgUnitParentLink.parentOrganisationUnitID,
    OrganisationUnit.name,
    OrgUnitParentLink.organisationUnitID
  FROM
    OrgUnitParentLink,
    OrganisationUnit
  WHERE OrgUnitParentLink.organisationStructureID = :organisationStructureID 
    AND OrgUnitParentLink.organisationUnitID = :organisationUnitID
    AND OrganisationUnit.organisationUnitID = OrgUnitParentLink.parentOrganisationUnitID) ParentOrganisationUnit ON ParentOrganisationUnit.organisationUnitID = OrganisationUnit.organisationUnitID
LEFT OUTER JOIN Location DefaultLocation ON DefaultLocation.locationID = OrganisationUnit.locationID
LEFT OUTER JOIN 
  (SELECT
    OrganisationUnit.organisationUnitID,
    COUNT(DISTINCT Location.locationID) locationCount
  FROM
    Location,
    OrganisationUnit,
    OrgUnitPositionLink,
    Position,
    PositionLocationLink,
    OrgUnitParentLink 
  WHERE OrgUnitPositionLink.organisationStructureID = :organisationStructureID 
    AND OrgUnitPositionLink.organisationUnitID = OrganisationUnit.organisationUnitID 
    AND OrgUnitPositionLink.recordStatus = :recordStatus
    AND OrgUnitPositionLink.positionID = Position.positionID 
    AND PositionLocationLink.locationID = Location.locationID 
    AND PositionLocationLink.positionID = Position.positionID 
    AND OrgUnitPositionLink.organisationStructureID = OrgUnitParentLink.organisationStructureID 
    AND OrgUnitPositionLink.organisationUnitID = OrgUnitParentLink.organisationUnitID 
    AND 
    (
      OrgUnitPositionLink.organisationUnitID = :organisationUnitID 
      OR OrgUnitParentLink.parentOrganisationUnitID = :organisationStructureID 
    )
    GROUP BY OrganisationUnit.organisationUnitID) Location ON Location.organisationUnitID = OrganisationUnit.organisationUnitID
WHERE OrganisationUnit.organisationUnitID = :organisationUnitID