Oracle Analytics Publisher

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Publisher

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to pass ESS requestid to BIP report

Accepted answer
3250
Views
18
Comments
Amitabha
Amitabha Rank 3 - Community Apprentice
edited Aug 5, 2025 7:12AM in Oracle Analytics Publisher

Summary

Is it possible to pass the ESS job id/process id/requestid as a parameter to BIP report

Content

In fusion application, an ESS Job is defined as BIPJobType which calls a BIP report. We want to populate a parameter in BIP Report with the ESS process id. Is there a way to achieve this?

Tagged:

Best Answer

«1

Answers

  • Skolli-Oracle
    Skolli-Oracle Rank 4 - Community Specialist

    The table ESS_REQUEST_HISTORY contains the information of the scheduled ESS jobs.

  • User_B8VQC
    User_B8VQC Rank 4 - Community Specialist

    Hi Amitabha,

     You can get the ESS requestid from below two tables.

      FUSION.ESS_REQUEST_HISTORY
     FUSION.ESS_REQUEST_PROPERTY

    To get the job id based on their status then use STATE column. For Example STATE=3 for running jobs.

     

    Sample query: 

    SELECT requestid
                              FROM (SELECT rq1.requestid
                                      FROM FUSION_ORA_ESS.REQUEST_HISTORY rq,
                                   FUSION_ORA_ESS.REQUEST_HISTORY rq1
                                     WHERE  rq.STATE = '3'
                               AND rq.COMPLETEDTIME IS NULL
                               AND rq1.REQUESTID = rq.PARENTREQUESTID
                                 ORDER BY rq.PROCESSSTART DESC) WHERE ROWNUM = 1

  • Amitabha
    Amitabha Rank 3 - Community Apprentice

    Thanks Sudhir and Anil for your comments. This workaround will work unless users are submitting multiple requests of same job simultaneously. Just wanted to check if ESS internally passes the id to BIP to uniquely identify the id, which looks like not available.

  • Skolli-Oracle
    Skolli-Oracle Rank 4 - Community Specialist

    Please review if username and Definition (Job Definition) filters and the latest job id with the SYSDATE can help with the requirement.

    Also, please post this Idea in Idea Labs as well:
    Please review, "https://cloudcustomerconnect.oracle.com/pages/64f1844655"

  • Mihaela M
    Mihaela M Rank 3 - Community Apprentice

    This is not true. ESS is the actual wrapper which triggers the jobs. Each job has a unique ID (let's call it Parent ID), and associated children IDs. For each parent ESS ID you have a corresponding BIP job id. However, your business scenario is little bit odd and needs further clarification. What do you actually want to achieve? You want to display the maching ESS job id in the BIP report?

  • Amitabha
    Amitabha Rank 3 - Community Apprentice

    Our requirement is a complex one, however displaying the triggering ESS job id in the BIP Report Output can be simple requirement which needs access to the value of ESS Job Id from BIP. 

    Just to elaborate on my requirement, by BIP report has external data source - which initiates a SOAP call to trigger ICS Integration. The integration uses multiple database adapters, I need to pass the ESS Job Id to ICS for logging the processing in ICS. Supporting the integration in a live environment becomes much easier with presence of the ESS Job Id, because that is the only unique identifier present in both back-end and front-end.

  • Joydeb Saha-Oracle
    Joydeb Saha-Oracle Rank 1 - Community Starter

    @Amitabha did you get a solution for this? I am also trying to pass the ESS JOB ID to integration layer and was wondering if there is any way ESS Job passes the id to BIP report.

  • Amitabha
    Amitabha Rank 3 - Community Apprentice

    @Joydeb Saha-Oracle - No we did not get a robust solution, and had to use workaround (i.e. from BI find last submitted/running ESS Job Id) and with the hope that the Job will not have concurrent submissions.

  • PareshSharma
    PareshSharma Rank 2 - Community Beginner

    @Amitabha You can use the below logic present under the where clause to fetch the latest ESS job id. Let me know if found helpful. please modify it as per your need.

    SELECT

    a.payment_service_request_id,

      SUM(a.payment_amount) amt

    FROM

      hr_organization_units  hou,

      iby_payments_all     a,

      iby_pay_service_requests ipsr

    WHERE

        1 = 1

      AND a.org_id = hou.organization_id

      AND payment_status IN ( 'FORMATTED', 'ISSUED' )

      AND ipsr.payment_service_request_id = a.payment_service_request_id

      AND CAST(ipsr.last_update_date AS TIMESTAMP) BETWEEN nvl((

        SELECT

          MAX(processstart)

        FROM

          ess_request_history

        WHERE

            jobtype = 'JobType://oracle/as/ess/ext/BIPJobType'

          AND definition = 'JobDefinition://oracle/apps/ess/custom/shared/Custom/Financials/Payments/Payment File Format/XXNEW'

          AND executable_status = 'SUCCEEDED'

      ),

     CAST(ipsr.last_update_date AS TIMESTAMP)) AND sysdate

    GROUP BY

      a.payment_service_request_id,

      ipsr.notification_message_id,

      hou.name

    )

  • kkounder
    kkounder Rank 1 - Community Starter

    in the BIP parameter you can use fusionapps_request_id this will have the request id of the parent ess job