SELECT
  Position.positionID,
  Position.name,
  Position.leadPositionInd,
  Position.jobID,
  OrgUnitPositionLink.organisationStructureID,
  OrgUnitPositionLink.organisationUnitID,
  Position.fromDate,
  Position.toDate,
  Job.name,
  OrganisationStructure.name,
  OrganisationUnit.name,
  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,
  :recordStatus,
  :vacantInd 
FROM
  OrganisationStructure,
  OrganisationUnit,
  OrgUnitPositionLink,
  Position 
  
  LEFT OUTER JOIN
  
  
  ( 
  SELECT
    PositionHolderLink.positionID,
    COUNT(*) AS PHLcount 
  FROM
    Position,
    PositionHolderLink 
  WHERE Position.jobID = :jobID 
    AND PositionHolderLink.positionID = Position.positionID 
    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,
  Job 
WHERE Job.jobID = :jobID 
  AND Position.jobID = Job.jobID 
  AND OrgUnitPositionLink.positionID = Position.positionID 
  AND 
  (
    OrgUnitPositionLink.organisationStructureID = :organisationStructureID 
    OR 
    (
      :organisationStructureIDIsNull <> '0' 
    ) 
  ) 
  AND OrganisationUnit.organisationUnitID = OrgUnitPositionLink.organisationUnitID 
  AND OrgUnitPositionLink.organisationStructureID = OrganisationStructure.organisationStructureID 
  AND OrganisationStructure.statusCode = :orgStructureRecordStatus