List BIP report requests recorded in ESS

 WITH report_properties AS (

    SELECT DISTINCT

           rp.REQUESTID,

           rp.VALUE AS REPORT_PATH

    FROM FUSION_ORA_ESS.REQUEST_PROPERTY rp

    WHERE rp.NAME = 'report_url'

)

SELECT rh.REQUESTID       AS REQUEST_ID,

       rh.PARENTREQUESTID AS PARENT_REQUEST_ID,

       rh.NAME            AS JOB_NAME,

       rh.DEFINITION      AS JOB_DEFINITION,

       rp.REPORT_PATH     AS REPORT_PATH,

       rh.SUBMITTER       AS SUBMITTED_BY,

       rh.STATE           AS REQUEST_STATE_CODE,

       rh.PROCESSSTART    AS PROCESS_START,

       rh.PROCESSEND      AS PROCESS_END

FROM FUSION_ORA_ESS.REQUEST_HISTORY rh

JOIN report_properties rp

  ON rp.REQUESTID = rh.REQUESTID

WHERE (:P_REPORT_PATH IS NULL

       OR :P_REPORT_PATH = '*'

       OR rp.REPORT_PATH = :P_REPORT_PATH)

  AND (:P_SUBMITTER IS NULL

       OR :P_SUBMITTER = '*'

       OR rh.SUBMITTER = :P_SUBMITTER)

ORDER BY rh.REQUESTID DESC