Getting one record per Emp_ID I wrote a query whereby I  joined multiple tables to get  one row per employee id and the relevant information for each employee job code, job code etc 

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID
  FROM PS_NPS_VIEW_ALL A, PS_PERSON_NAME B, PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT &lt;= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.PAYGROUP NOT IN ('SUM','CWR'))


I wanted to get another field, JPM_DESCR90 into my query by joining the table PS_NPS_LIC_VW_RPT by EMPLID with one of my other tables in the original. I tried a left outer  join between PS_PERSON_NAME B and PS_NPS_LIC_VW_RPT where by PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, C.JPM_DESCR90
  FROM PS_NPS_VIEW_ALL A, (PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID ), PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT &lt;= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.PAYGROUP NOT IN ('SUM','CWR')


However I got multiple rows for each  EMPLID since JPM_DESCR90 has multiple values for each EMPLID in PS_NPS_LIC_VW_RPT C .

I also tried a standard join between the two whereby B.EMPLID = C.EMPLID between
PS_PERSON_NAME B, and  PS_NPS_LIC_VW_RPT C

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, C.JPM_DESCR90
  FROM PS_NPS_VIEW_ALL A, PS_PERSON_NAME B, PSOPRDEFN E, PS_NPS_LIC_VW_RPT C
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT &lt;= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.PAYGROUP NOT IN ('SUM','CWR')
     AND B.EMPLID = C.EMPLID)


I'm tried to get JPM_DESCR90 from PS_NPS_LIC_VW_RPT into my original query so that I can get one value per EMPLID .I know that its probably a simple solution but would appreciate some help
I was able to solve my problem by adding the completion dates for JPM_DESCR90 (which is a field decription/name for certifications) in my query i.e. and then looked for max values of those dates in my query by adding MAX( TO_CHAR(C.JPM_DATE_3,'YYYY-MM-DD')) to the SELECT statement. This gives me the most recently acquired certification for each employee. I have attached a snapshot of my result as a jpeg image above

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, MAX( TO_CHAR(C.JPM_DATE_3,'YYYY-MM-DD'))
  FROM PS_NPS_VIEW_ALL A, (PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID ), PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT &lt;= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.EMPL_STATUS='A'
     AND A.PAYGROUP NOT IN ('SUM','CWR'))
  GROUP BY  A.EMPLID,  A.JOBCODE,  A.JOBCODE_DESCR,  A.EMPL_STATUS,  A.LOCATION,  A.LOCATION_DESCR,  A.NAME,  E.EMAILID,  B.EMPLID,  E.OPRID



However, I have one more question. Is there a way to get all the certifications(JPM_DESCR90 aka Jobcodedescr column( in one row rather than in multiple rows for by getting each value of JPM_DESCR90 separated by ':' character for each employee irrespective of certification completion dates (C.JPM_DATE_3) ?