Image of CU_BAR_HR_00004:

 

Query SQL:

 Query SQL:
 
SELECT A.DEPTID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.DESCR, A.MANAGER_ID, A.MANAGER_POSN, B.NAME,A.SETID
FROM PS_DEPT_TBL A, PS_EMPLOYEES B, PS_EMPLMT_SRCH_QRY B1
WHERE B.EMPLID = B1.EMPLID
AND B.EMPL_RCD = B1.EMPL_RCD
AND B1.OPRID = '23420211'
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_DEPT_TBL A_ED
WHERE A.SETID = A_ED.SETID
AND A.DEPTID = A_ED.DEPTID
AND A_ED.EFFDT <= SYSDATE)
AND A.SETID = 'BAR01'
AND A.SETID_LOCATION = 'BAR01'
AND A.EFF_STATUS = 'A'
AND A.DEPTID = B.DEPTID
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_EMPLOYEES B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_EMPLOYEES B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.EMPL_RCD = B_ES.EMPL_RCD
AND B.EFFDT = B_ES.EFFDT)
AND A.MANAGER_POSN = B.POSITION_NBR )
ORDER BY 1
  • No labels