nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

How to pass ESS requestid to BIP report

Amitabha

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?

Find more posts tagged with

Reporting

Accepted answers

Sourav_Mondal

Solution :

Create a parameter in BIP datamodel with the name as is : fusionapps_request_id
You can give any display name. This parameter can be mapped to any third party request.
In the master ESS job , don't add this parameter.

All comments

Skolli-Oracle

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

User_B8VQC

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

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

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

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

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

@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

@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

@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

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