Query to get Open FYI notifications in Oracle Workflow

SELECT wfn.recipient_role

  ,wfn.notification_id

  ,wfn.status

  ,wfn.mail_status

  ,wfn.subject

  FROM wf_item_activity_statuses wias

      ,wf_notifications wfn

 WHERE wias.notification_id=wfn.notification_id

   AND wfn.status='OPEN'

   AND wias.activity_status='COMPLETE'

   AND wfn.end_date is null

   AND wfn.message_type = 'REQAPPRV'

ORDER BY wfn.recipient_role;

Query to get Date Difference in Seconds in Oracle

DECLARE

    l_start_time   date;

l_end_time     date;

        l_date_difference    NUMBER;

BEGIN

    SELECT SYSDATE 

  INTO l_start_time 

  FROM DUAL;

fnd_file.put_line(fnd_file.log,'Program Start Time  :'||  TO_CHAR(SYSDATE,'HH24:MI:SS'));

    dbms_output.put_line('Program Start Time  :'||  TO_CHAR(SYSDATE,'HH24:MI:SS'));

   

WHILE l_Start_time IS NOT NULL

LOOP

SELECT SYSDATE 

  INTO l_end_time 

  FROM DUAL;

                l_date_difference := l_end_time - l_start_time;

    EXIT WHEN (l_date_difference)*24*60*60 = 180;

END LOOP;

fnd_file.put_line(fnd_file.log,'Program End Time     :'||  TO_CHAR(SYSDATE,'HH24:MI:SS'));

dbms_output.put_line('Program End Time  :'||  TO_CHAR(SYSDATE,'HH24:MI:SS'));

END;

Here control will be released after 180 seconds.