Operation Details
Description: Returns all position related information, including, organization unit name, job name, and reportsTo name.
Sterotype: ns
SQL 
SELECT
  P.positionID,
  RP.name,
  P.name,
  P.leadPositionInd,
  P.jobID,
  OrgUnitParentLink.organisationUnitID,
  OrgUnitParentLink.parentOrganisationUnitID,
  P.fromDate,
  P.toDate,
  Job.name,
  OrganisationUnit.name,
  PositionReportingLink.reportsToID,
  P.comments,
  P.recordStatus,
  P.versionNo
INTO
  :positionID,
  :reportsToName,
  :positionName,
  :leadPositionInd,
  :jobID,
  :organisationUnitID,
  :parentOrganisationUnitID,
  :fromDate,
  :toDate,
  :jobName,
  :organisationUnitName,
  :reportsTo,
  :comments,
  :recordStatus,
  :versionNo 
FROM
  Position P 
  
  LEFT OUTER JOIN
  
  PositionReportingLink 
  ON (P.positionID = PositionReportingLink.positionID 
  AND PositionReportingLink.organisationStructureID = :organisationStructureID 
  AND PositionReportingLink.recordStatus = :recordStatus) 
  
  LEFT OUTER JOIN
  
  Position RP 
  ON RP.positionID = PositionReportingLink.reportsToID,
  OrgUnitPositionLink,
  OrgUnitParentLink,
  OrganisationUnit,
  Job 
WHERE P.positionID = :positionID 
  AND OrgUnitPositionLink.positionID = P.positionID 
  AND OrgUnitPositionLink.organisationStructureID = :organisationStructureID 
  AND OrgUnitParentLink.organisationUnitID = OrgUnitPositionLink.organisationUnitID 
  AND OrgUnitParentLink.organisationStructureID = :organisationStructureID 
  AND OrganisationUnit.organisationUnitID = OrgUnitParentLink.organisationUnitID 
  AND Job.jobID = P.jobID