Monday, March 14, 2022

OIC ERP BI Bursting using FTP delivery channel

UseCase : We will create a Bursting BI report and create a ESS job over the bursting BI and call the ESS job from OIC and save the report in the FTP delivery channel. HERE, we will create one document/single file.

Please follow my previous blog for BI Bursting about and details:

https://soalicious.blogspot.com/2022/03/oic-erp-bi-bursting.html

Bursting Query:

SELECT 'XX' AS "KEY",

'XXSupplierRecord' AS "TEMPLATE",

'en-US' AS "LOCALE",

'TEXT' AS "OUTPUT_FORMAT",

'FTP' AS "DEL_CHANNEL",

'AmazonS3' AS "PARAMETER1",

:p_destination AS "PARAMETER4",

'XXSUPPLIERREPORT_' || TO_CHAR(SYSDATE, 'YYYY-MM-DD_HHMISS') || '.csv'

AS "PARAMETER5",

'true' AS "PARAMETER6"

FROM DUAL

BI report Query:

Select 'XX' AS "KEY",

hp.party_name supplier_name,

poz.segment1 supplier_number,

poz.vendor_id supplier_id,

hp.email_address,

hp.country,

hp.city,

hp.status

from POZ_SUPPLIERS poz, HZ_PARTIES hp where 1=1 

And poz.party_id = hp.party_id

And rownum<=10;

Create BI Bursting Report:

Navigator >> Tools >> Reports and Analytics >> Browse catalog >> traverse Shared Folders -- Custom -- Integrarions -- Poc >> 














Create ESS job:

Navigator >> My Enterprise >> Setup and Maintenance >> search with Manage Enterprise in search tasks



Provided following details:
Name: XXSupplierREP_ESS
Path: /delta/
Application: Application Toolkit
Description: POC bursting report
Job Application name: FscmEss
JOb type: BIPJobType and bursting report
Report id: /Custom/Integratikns/Poc/XXSpplierREP1.xdo
Allow multiple pending submissions: True

Create a Paramter: p_destination.


Call From OIC:

JobPackageName: oracle/apps/ess/custom/delta

JobDefinitionName: XXSupplierREP_ESS

Parameter: Sftp path to save the file.














ERP delivery configuration:

Navigation # Tools >> Reports and Analytics >> Browse Catalog >> Administration 






Bursting for multiple files:



Sample bursting query for sending email:

Select a.Vendor_id KEY,

'Test_supp' TEMPLATE,

'en-US' LOCALE,

'PDF' OUTPUT_FORMAT,

'Supplier Details' OUTPUT_NAME,

'EMAIL' DEL_CHANNEL,

'test@gmail.com' PARAMETER1,

'test@gmail.com' PARAMETER2,

'bipublisher-report@oracle.com' PARAMETER3,

'Supplier Details' PARAMETER4,

'Please find the attached file' PARAMETER5,

'true' PARAMETER6,

'donotreply@oracle.com' PARAMETER7,

From poz_suppliers a


For more details follow oracle docs:

https://docs.oracle.com/cd/E21764_01/bi.1111/e18862/T527073T555155.htm#:~:text=About%20Bursting,-Bursting%20is%20a&text=For%20each%20block%20of%20the,element%20in%20the%20data%20model.



No comments:

Post a Comment

Featured Post

OIC - how can I use XSLT functions to remove leading zeros from numeric and alphanumeric fields?

To remove leading zeros from an numeric field in Oracle Integration Cloud (OIC) using XSLT, you can Use number() Function The number() funct...