SELECT
  Position.positionID,
  Position.name,
  Position.leadPositionInd,
  Position.jobID,
  OrgUnitPositionLink.organisationStructureID,
  OrgUnitPositionLink.organisationUnitID,
  Position.fromDate,
  Position.toDate,
  Job.name,
  OrganisationStructure.name,
  OrganisationUnit.name,
  Users.fullName,
  Position.recordStatus,
  CASE 
    WHEN PHLcount = 0 
    THEN '1' 
    WHEN PHLcount IS NULL 
    THEN '1' 
    ELSE '0' 
  END
 
INTO
  :positionID,
  :positionName,
  :leadPositionInd,
  :jobID,
  :organisationStructureID,
  :organisationUnitID,
  :fromDate,
  :toDate,
  :jobName,
  :organisationStructureName,
  :organisationUnitName,
  :userFullName,
  :recordStatus,
  :vacantInd 
FROM
  OrganisationStructure,
  OrganisationUnit,
  OrgUnitPositionLink,
  Position 
  
  RIGHT OUTER JOIN
  
  
  ( 
  SELECT
    PositionHolderLink.positionID,
    COUNT(*) AS PHLcount 
  FROM
    PositionHolderLink 
  WHERE PositionHolderLink.userName = :userName 
    AND 
    (
      PositionHolderLink.organisationStructureID = :organisationStructureID 
      OR 
      (
        :organisationStructureIDIsNull <> '0' 
      ) 
    ) 
    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,
  PositionHolderLink,
  Users,
  Job 
WHERE Users.userName = :userName 
  AND PositionHolderLink.userName = Users.userName 
  AND 
  (
    PositionHolderLink.organisationStructureID = :organisationStructureID 
    OR 
    (
      :organisationStructureIDIsNull <> '0' 
    ) 
  ) 
  AND PositionHolderLink.recordStatus = :recordStatus 
  AND PositionHolderLink.fromDate <= :effectiveDate 
  AND 
  (
    PositionHolderLink.toDate >= :effectiveDate 
    OR PositionHolderLink.toDate IS NULL 
  )  
  AND Position.positionID = PositionHolderLink.positionID 
  AND OrgUnitPositionLink.organisationUnitID = OrganisationUnit.organisationUnitID 
  AND OrgUnitPositionLink.organisationStructureID = PositionHolderLink.organisationStructureID 
  AND OrgUnitPositionLink.positionID = Position.positionID 
  AND Job.jobID = Position.jobID 
  AND OrganisationStructure.organisationStructureID = PositionHolderLink.organisationStructureID