Dynamic Position Title from Jobcode vs Position Data

Dynamic Position Title from Jobcode vs Position Data

On a several different occasion, I was asked how we dynamically derive a position title from the position data table when the position number exists in the job data instead of from jobcode table.

So, I wrote this SQL to return emplID, empl record, and dynamic position title (derived from jobcode vs position data).

You can also make this SQL as record view and you can call it using peoplecode to return a dynamic position title you desire.

For this example, I am querying emplID: KU0010.

Note: replace {less than sign} with <
For some reasons, it keeps translating and displaying it to < (If anyone has suggestion to fix the dispay, please let me know.  I just don't have time to research it right now)

Additionally, I am running this in Oracle database, so sysdate will work just fine.  If you are using MSSQL database, you know what to do.

SELECT job.emplid 
 , job.empl_rcd 
 , CASE pos.descr WHEN '' THEN jobcd.descr ELSE pos.descr END 
  FROM ps_job job LEFT JOIN ( 
 SELECT J1.setid 
 , J1.jobcode 
 , J1.Descr 
  FROM ps_jobcode_tbl J1 
 WHERE J1.effdt = ( 
 SELECT MAX(effdt) 
  FROM ps_jobcode_tbl 
 WHERE setid = J1.setid 
   AND jobcode = J1.jobcode 
   AND effdt {less than sign}= SYSDATE)) jobcd ON jobcd.setid = job.setid_jobcode 
   AND jobcd.jobcode = job.jobcode LEFT JOIN ( 
 SELECT P1.position_nbr 
 , P1.descr 
  FROM ps_position_data P1 
 WHERE P1.effdt = ( 
 SELECT MAX(effdt) 
  FROM ps_position_data 
 WHERE position_nbr = P1.position_nbr 
   AND effdt {less than sign}= SYSDATE)) pos ON pos.position_nbr = job.position_nbr 
 WHERE job.effdt = ( 
 SELECT MAX(effdt) 
  FROM ps_job 
 WHERE emplid = job.emplid 
   AND empl_rcd = job.empl_rcd 
   AND effdt {less than sign}= SYSDATE) 
   AND job.effseq = ( 
 SELECT MAX(effseq) 
  FROM ps_job 
 WHERE emplid = job.emplid 
   AND empl_rcd = job.empl_rcd 
   AND effdt = job.effdt)
 AND job.emplid like 'KU0010';