SELECT
  Position.positionID,
  Position.name,
  Position.leadPositionInd,
  Position.jobID,
  Position.fromDate,
  Position.toDate,
  Job.name,
  Users.fullName,
  CASE 
    WHEN PHLcount = 0 
    THEN '1' 
    WHEN PHLcount IS NULL 
    THEN '1' 
    ELSE '0' 
  END
 
INTO
  :positionID,
  :positionName,
  :leadPositionInd,
  :jobID,
  :fromDate,
  :toDate,
  :jobName,
  :userFullName,
  :vacantInd 
FROM
  Position 
  
  LEFT OUTER JOIN
  
  
  ( 
  SELECT
    PositionHolderLink.positionID,
    COUNT(*) AS PHLcount 
  FROM
    PositionReportingLink,
    Position,
    PositionHolderLink 
  WHERE PositionReportingLink.positionID = :positionID 
    AND PositionReportingLink.organisationStructureID = :organisationStructureID 
    AND Position.positionID = PositionReportingLink.reportsToID 
    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,
  PositionReportingLink,
  Job,
  PositionHolderLink,
  Users 
WHERE PositionReportingLink.positionID = :positionID 
  AND PositionReportingLink.organisationStructureID = :organisationStructureID 
  AND PositionReportingLink.reportsToID = Position.positionID 
  AND Job.jobID = Position.jobID 
  AND Position.positionID = PositionHolderLink.positionID 
  AND PositionHolderLink.organisationStructureID = :organisationStructureID 
  AND PositionHolderLink.userName = Users.userName