0 Replies Latest reply: Jun 3, 2014 3:41 AM by user12058251 RSS

    OAF Jdeveloper 9.0.3.5: Passing Logged in Employee Number to SQL in VO

    user12058251

      Hi Experts,

       

      I'm very new to OAF. I have created an employee leave details self-service search page with options to search by different parameters. However, I don't know how to restrict this search only to the logged in user ID and not allow access to all employee leave details.

      After browsing a lot, I got to know that we have to use one of these to get the value:

      pageContext.getEmployeeId()

      pageContext.getLoginId()

      pageContext.getUserName()


      I got that, but how do I pass it to the SQL statement. Please advice. Thanks:

       

      This is my SQL:

       

      WITH h1 AS

      (SELECT REPLACE (subject, '@Leave Of Absence for ') applicant,

      to_user pending_at,

      SUBSTR (REPLACE (CONTEXT, 'HRSSA:'),

      1,

      INSTR (REPLACE (CONTEXT, 'HRSSA:'), ':', 1) - 1

      ) item_key

      FROM wf_notifications wn

      WHERE wn.MESSAGE_TYPE = 'HRSSA'

      --AND WN.CONTEXT LIKE 'HRSSA:321552%'

      AND UPPER (subject) LIKE '%LEAVE%'

      AND wn.status = 'OPEN'

      AND wn.from_role <> 'SYSADMIN'

      ORDER BY wn.notification_id DESC),

      h2 AS

      (SELECT item_key,

      MAX (CASE

      WHEN NAME = 'P_DATE_START'

      THEN date_value

      END) st_dt,

      MAX (CASE

      WHEN NAME = 'P_DATE_END'

      THEN date_value

      END) end_dt,

      MAX (CASE

      WHEN NAME = 'P_COMMENTS'

      THEN varchar2_value

      END) cmts,

      MAX (CASE

      WHEN NAME = 'P_PERSON_ID'

      THEN number_value

      END) person_id

      FROM hr_api_transaction_steps has, hr_api_transaction_values hav

      WHERE item_type = 'HRSSA'

      AND has.transaction_step_id = hav.transaction_step_id

      GROUP BY item_key)

      SELECT h2.person_id,h1.applicant, h1.pending_at, h2.st_dt start_date, h2.end_dt end_date,

      h2.cmts applicant_comments

        FROM h1, h2

      WHERE h1.item_key = h2.item_key