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

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...