1 Reply Latest reply: Feb 13, 2013 2:23 AM by HakanBiroglu RSS

    Dynamic Search Record with iteration

    crsthn
      Hi.


      I need a search record that show all subordinates of a current oprid logged in. I write this query for a search view, but don't work

      SELECT EMPLID , EMPL_RCD , EFFDT , LEVEL
      FROM (
      SELECT EMPLID ,EMPL_RCD ,EFFDT ,POSITION_NBR ,REPORTS_TO FROM PS_JOB J
      WHERE EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB J1 WHERE J.EMPLID = J1.EMPLID AND J.EMPL_RCD = J1.EMPL_RCD)
      AND EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB J1 WHERE J.EMPLID = J1.EMPLID AND J.EMPL_RCD = J1.EMPL_RCD AND J.EFFDT = J1.EFFDT) ) J
      START WITH EMPLID = ( SELECT EMPLID FROM PSOPRALIAS WHERE OPRID = %OperatorId)
      CONNECT BY NOCYCLE PRIOR POSITION_NBR = REPORTS_TO

      Thanks in advise

      Edited by: crsthn on 12-feb-2013 13:39
        • 1. Re: Dynamic Search Record with iteration
          HakanBiroglu
          What most people do not know is that PeopleSoft automatically filters data of search records when the OPRID is a key and not a list item by expending the where clause with OPRID=%OperatorID, based on the user logged in, without any coding!
          Create an ordinary view and add OPRID as a keyfield and PeopleTools will do the rest for you. ;)

          Hakan