SELECT
  Position.positionID,
  Position.name,
  Position.leadPositionInd,
  Position.jobID,
  OrgUnitPositionLink.organisationUnitID,
  Position.fromDate,
  Position.toDate,
  Job.name,
  OrganisationUnit.name,
  Position.recordStatus,
  CASE 
    WHEN PHLcount = 0 
    THEN '1' 
    WHEN PHLcount IS NULL 
    THEN '1' 
    ELSE '0' 
  END
  AS VACANT
INTO
  :positionID,
  :positionName,
  :leadPositionInd,
  :jobID,
  :organisationUnitID,
  :fromDate,
  :toDate,
  :jobName,
  :organisationUnitName,
  :recordStatus,
  :vacantInd 
FROM
  OrganisationUnit,
  Position 
  
  LEFT OUTER JOIN
  
  
  ( 
  SELECT
    PositionHolderLink.positionID,
    COUNT(*) AS PHLcount 
  FROM
    OrgUnitPositionLink,
    Position,
    PositionHolderLink 
  WHERE OrgUnitPositionLink.organisationStructureID = :organisationStructureID 
    AND OrgUnitPositionLink.organisationUnitID = :organisationUnitID 
    AND Position.positionID = OrgUnitPositionLink.positionID 
    AND Position.recordStatus = :recordStatus 
    AND PositionHolderLink.organisationStructureID = :organisationStructureID 
    AND PositionHolderLink.positionID = Position.positionID 
    AND PositionHolderLink.recordStatus = :recordStatus 
    AND PositionHolderLink.fromDate <= :effectiveDate 
    AND 
    (
      PositionHolderLink.toDate >= :effectiveDate 
      OR PositionHolderLink.toDate IS NULL 
    ) 
  GROUP
    BY PositionHolderLink.positionID 
  ) VPHL 
  ON Position.positionID = VPHL.positionID,
  OrgUnitPositionLink,
  Job 
WHERE OrgUnitPositionLink.organisationStructureID = :organisationStructureID 
  AND OrgUnitPositionLink.organisationUnitID = :organisationUnitID 
  AND OrgUnitPositionLink.organisationUnitID = OrganisationUnit.organisationUnitID 
  AND OrgUnitPositionLink.positionID = Position.positionID 
  AND Job.jobID = Position.jobID 
  AND Position.recordStatus = :recordStatus 
ORDER BY Position.leadPositionInd DESC, 
  VACANT ASC, 
  Position.name ASC