Dear Friends ,
Here is some important query to Extract all BIP reports which are currently scheduled in ESS jobs in Oracle Application Cloud .
You can create a BIP Report and get the details .
SELECT r.requestid
,r.REQUESTEDSTART
,r.processstart
,r.processend
,r.executable_status
,v.name property_name
,vtwo.value report_params
,vthree.value report_url
--,extractValue(xmlparse(content v.value), '/PARAMETER1') as param1
,regexp_replace(regexp_substr(v.value, '<PARAMETER1>.*</PARAMETER1>'), '(<PARAMETER1>)(.*)(</PARAMETER1>)', '\2') to_email
,regexp_replace(regexp_substr(v.value, '<PARAMETER2>.*</PARAMETER2>'), '(<PARAMETER2>)(.*)(</PARAMETER2>)', '\2') cc
,regexp_replace(regexp_substr(v.value, '<PARAMETER5>.*</PARAMETER5>'), '(<PARAMETER5>)(.*)(</PARAMETER5>)', '\2') message
,regexp_replace(regexp_substr(v.value, '<PARAMETER5>.*</PARAMETER5>'), '(<PARAMETER5>)(.*)(</PARAMETER5>)', '\2') subject
,regexp_replace(regexp_substr(v.value, '<TEMPLATE>.*</TEMPLATE>'), '(<TEMPLATE>)(.*)(</TEMPLATE>)', '\2') report_template
,r.submitter from_user
FROM fusion.ess_request_history r
,fusion.ess_request_property v
,fusion.ess_request_property vtwo
,fusion.ess_request_property vthree
WHERE r.requestid = v.requestid(+)
AND r.requestid = vtwo.requestid(+)
AND r.requestid = vthree.requestid(+)
AND v.name IN ('CONTROL_XML')
AND vtwo.name IN ('report_params')
AND vthree.name = 'report_url'
AND r.APPLICATION = 'bipublisher'
Comments
Post a Comment