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:
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,
SUBSTR (REPLACE (CONTEXT, 'HRSSA:'),
INSTR (REPLACE (CONTEXT, 'HRSSA:'), ':', 1) - 1
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),
WHEN NAME = 'P_DATE_START'
WHEN NAME = 'P_DATE_END'
WHEN NAME = 'P_COMMENTS'
WHEN NAME = 'P_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,
FROM h1, h2
WHERE h1.item_key = h2.item_key