Oracle fusion ERP - Multi file bursting part2 | Send Multiple files by using Bursting

Usecase: 

Here, will create one data set and create 2 reports using rtf anf xsl templates and then will use 2 bursrting query using union all and Email Delivery channel.

Detailed screenshots:

Create data model >> data set using sql query.

Create email bursting

Create 2 reports pdf and text using rtf and xsl templates.

Create ess job

Run ess job from scheduled processes

Oracle fusion ERP - Multi file bursting | Send Multiple files by using Bursting

Usecase: Here we will split the data block using vendor name as key and send the multiple files using Email delivery channel.

Used Data model SQL query:

SELECT B.VENDOR_NAME, B.VENDOR_ID,

A.INVOICE_ID,

A.INVOICE_NUM,

A.INVOICE_AMOUNT,

A.AMOUNT_PAID,

A.INVOICE_DATE,

A.DESCRIPTION,

A.BATCH_ID,

A.BASE_AMOUNT

FROM AP_INVOICES_ALL A, POZ_SUPPLIERS_V B WHERE A.VENDOR_ID =B.VENDOR_ID AND A.VENDOR_ID IN (300000047414635,300000047414571 3000000047414503,300000047414679)

Used Bursting Query:

Select Vendor_name KEY, -- split by --

'MultiReport1_Temp' TEMPLATE, -- TEMPLATE NAME OR REPORT NAME --

'en-US- LOCALE, -- English Language--

'PDF' OUTPUT_FORMAT, -- PDF, EXCEL,RTF,CSV,HTML,XLS--

VENDOR_ID||'.pdf' OUTPUT_NAME, -- Output File Name --

'EMAIL' DEL_CHANNEL, -- MAIL,FAX,FILE,FTP,PRINT,SFTP --

'Test@test.com' PARAMETER1, -- To mail.--

'Oracle@oracle.com' PARAMETER3 -- FROM MAIL --

'Supplier Invoice Details ' PARAMETER4, -- MAIL SUBJECT --

'Please find attached document for Invoice Details' PARAMETER5, --MAIL BODY--

'true' PARAMETER6 -- MAIL HAVING ATTACHMENT OR NOT--

FROM(

SELECT DISTINCT B.VENDOR_NAME, B.VENDOR_ID FROM AP_INVOICES_ALL A, POZ_SUPPLIERS_V B WHERE A.VENDOR_ID =B.VENDOR_ID AND A.VENDOR_ID IN (300000047414635,300000047414571 3000000047414503,300000047414679)

)

Detailed screenshots:

OIC - Append operation in Data stitch action | Incrementally build a messags payload using Data stitch

Usecase: Here, we will see how to perform append operation using Data stitch action.

We will feed array of ids, for each id, call a another service and check the id exist value true or false in a scope and assign the values to a global oblect variable response item and finally append the item to response array object. Next we will get all the id responses outside of the scope.

Why data stitch:

1. We can incrementally build a message payload from one or more existing  payloads with the stitch.
2. Supports both partial and full replacement of the message payload.
3. Supports both scalar and complex type variables

Implementation steps:

1. Create a rest trigger and create an appdriven orchatration integration and configure  rest request - response payload
2. Take a for each loop and iterates for each data - Id
3. Take a scope
4. Call integration action or rest call to get the details for the give id.
5. Create two golbal variables
1.  One for response Array which will have the array of Id responses
2. One for response Item which will have one id item response
6. Take a stitch action and perform the following
1. Assign the response id to response_item id
2. Assign the response Exists value to Response_item exists
3. Append the response_item result to response_array result.
7. Outside of the scope, map the global responae_array data to final rest response.

Detailed steps with snaps:

Oracle Fusion ERP webservices and operations

Urls:

1. SAAS Home Page :  https://hostname:443/
2. SAAS UCM : https://hostname:443/cs
3. BIP URL  :  https://hostname:443/xmlpserver or analytics
4. BIP Web Service  :  
• https://<fusion app url>/xmlpserver/services/ExternalReportWSSService?wsdl
• https://<fusion app url>/xmlpserver/services/PublicReportWSSService?wsdl
• https://<fusion app url>/xmlpserver/services/ScheduleReportWSSService?wsdl
•  Absolute Path :  Provide complete xdo path
•  Example : /Custom/Financials/int/Reports/samplereport.xdo
5. ERP Adapter :
• Service Catalog :
• https://<fusion app url>/fndAppCoreServices/ServiceCatalogService?wsdl
• Erp Integration :
• https://<fusion app url>/fscmService/ErpIntegrationService
• Event Catalog :
• https://<fusion app url>/soa-infra
• Event Configuration 
• https://<fusion app url>/soa-infra/PublicEvent/catalog
• Event Subscriber Check:
• https://<fusion app url>/soa-infra/PublicEvent/subscriptions
6. Token Relay
• https://<fusion app url>/fscmRestApi/tokenrelay
7. Attachment Service 
• https://<host>.fa.ocs.oraclecloud.com/publicFinancialCommonErpIntegration/ErpObjectAttachmentService?wsdl

HCM Details:

1. You encrypt files using the Oracle HCM Cloud public key.
2. The data-loading process decrypts files using the Oracle HCM Cloud private key.
3. Generate the PGP encryption key pair and download the Oracle HCM Cloud public key to Encrypt the file.  

Submit HCM Data Load

Upload File to UCM using following UCM Service. 

https://hostname:443/idcws/GenericSoapPort?WSDL

• Read the file as an attachment
• Pass the opaque contents to UCM
• Get the content Id from the response.
• Service IdcService="CHECKIN_UNIVERSAL"
• dDocTitle
• dDocAccount
• dDocType
• dSecurityGroup
• dDocAuthor 

Invoke HCM Webservice to Load and Import the data. 

https://hostname/hcmCommonDataLoader/HCMDataLoader?WSDL

• Operation - importAndLoadData
• Pass the content ID in the request message. -- dDocName from UCM upload response. 
• <ns0:Field name="dDocName">UCMFA00422909</ns0:Field>
• Pass the additional Parameters.

Get the Status of HDL load and import job. 

• Operation - getDataSetStatus
• Pass the content id in the input parameter in the following format.
• "ContentId="$UCMContentID
• Exmple : ContentId=12345

To Submit Extract and Get the Output from UCM

• Submit Extract
• URL - https://hostname:443/hcmService/FlowActionsService?WSDL
• Operation 
• SubmitFlow  - To Submit the job
• Provide Extract Name 
• Extract Run ID
• getFlowTaskInstanceStatus  - To Get the  Status
• Provide Extract Name
• Provide Extract Run Id
• getIntegrationContentId  - To Get the content ID
• Provide Extract Name
• Provide Extract Run Id

• Get the Content from UCM.
• URL - https://hostname:443/idcws/GenericSoapPort?WSDL
• Service IdcService="GET_FILE"
• dDocName = ContentId
• Decode the Data and Write the file. 

UCM Details:

• Web Service 
• URL - https://hostname:443/idcws/GenericSoapPort?WSDL

BIP Webservices:

• To get the list of Webservices 
• URL - http://hostname/xmlpserver/services
• To Schedule a BIP Job.
• https://hostname/xmlpserver/services/v2/ScheduleService?wsdl
• Operations
• scheduleReport - To Submit the Job
• getScheduledReportStatus - To get the Status of the Job.
• getAllJobInstanceIDs  - To Get the Job Id's
• getXMLData  - To Get the XML Data 

Sample Payload : 

HCM Extract : 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/" xmlns:flow="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowControllerService/">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:submitFlow>

         <typ:flowName>SAMPLE_EXTRACT_REPORT</typ:flowName>

         <!--Zero or more repetitions:-->

         <typ:parameterValues>

            <flow:ParameterName>Effective Date</flow:ParameterName>

            <flow:ParameterValue>2019-10-17</flow:ParameterValue>

         </typ:parameterValues>

         <typ:parameterValues>

            <flow:ParameterName>LookbackDate</flow:ParameterName>

            <flow:ParameterValue>2019-10-02</flow:ParameterValue>

         </typ:parameterValues>

         <typ:parameterValues>

            <flow:ParameterName>RunDate</flow:ParameterName>

            <flow:ParameterValue>2019-10-17</flow:ParameterValue>

         </typ:parameterValues>    <typ:flowInstanceName>SAMPLE_EXTRACT_REPORT191017084530</typ:flowInstanceName>

         <typ:legislativeDataGroupName>US Legislative Data Group</typ:legislativeDataGroupName>

         <typ:recurringFlag>false</typ:recurringFlag>

      </typ:submitFlow>

   </soapenv:Body>

</soapenv:Envelope>

HCM Get Extract Status :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:getFlowTaskInstanceStatus>         <typ:flowInstanceName>SAMPLE_EXTRACT_REPORT191017084530</typ:flowInstanceName>         <typ:flowTaskInstanceName>SAMPLE_EXTRACT_REPORT</typ:flowTaskInstanceName>

         <typ:legislativeDataGroupName>US Legislative Data Group</typ:legislativeDataGroupName>

      </typ:getFlowTaskInstanceStatus>

   </soapenv:Body>

</soapenv:Envelope>

HCM Get Content ID of an Extract :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/hcm/processFlows/core/flowActionsService/types/">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:getIntegrationContentId>         <typ:flowInstanceName>SAMPLE_EXTRACT_REPORT191017084530</typ:flowInstanceName>         <typ:flowTaskInstanceName>SAMPLE_EXTRACT_REPORT</typ:flowTaskInstanceName>

         <typ:legislativeDataGroupName>US Legislative Data Group</typ:legislativeDataGroupName>         <typ:integrationName>IntegrationIdDefinedinExtractDeliveryType</typ:integrationName>

      </typ:getIntegrationContentId>

   </soapenv:Body>

</soapenv:Envelope>

Sample HDL File Load :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/hcm/common/dataLoader/core/dataLoaderIntegrationService/types/">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:importAndLoadData>

         <typ:ContentId>12345</typ:ContentId>         <typ:Parameters>ImportMaximumErrors=100,LoadMaximumErrors=100,LoadConcurrentThreads=4,FileEncryption=PGPSIGNED,DeleteSourceFile=N</typ:Parameters>

      </typ:importAndLoadData>

   </soapenv:Body>

</soapenv:Envelope>

UCM Get File

<GenericRequest xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" webKey="cs">
<tns:Service IdcService="GET_FILE">
<tns:User/>
<tns:Document>
<tns:Field name="dDocName">Doc_Id233088</tns:Field>
<tns:Field name="RevisionSelectionMethod">Latest</tns:Field>
<tns:Field name="Rendition">Web</tns:Field>
</tns:Document>
</tns:Service>
</GenericRequest>

UCM Search 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM">

   <soapenv:Header/>

   <soapenv:Body>

      <ucm:GenericRequest webKey="CS">

         <ucm:Service IdcService="GET_SEARCH_RESULTS">

            <ucm:Document>

               <ucm:Field name="QueryText">dID &lt;MATCHES&gt; `422909`</ucm:Field>

              </ucm:Document>

         </ucm:Service>

      </ucm:GenericRequest>

   </soapenv:Body>

</soapenv:Envelope>

Upload File To UCM:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM" xmlns:wsse="http://schemas.xmlsoap.org/ws/2003/06/secext">

   <soapenv:Header/>

   <soapenv:Body>

      <ucm:GenericRequest webKey="cs">

         <ucm:Service IdcService="CHECKIN_UNIVERSAL">

            <ucm:User/>

            <ucm:Document>

               <ucm:Field name="dDocTitle">Department2 Load File</ucm:Field>

               <ucm:Field name="dDocType">Document</ucm:Field>

              <!-- <ucm:Field name="dDocAuthor">test1.user</ucm:Field>

-->

               <ucm:Field name="dSecurityGroup">FAFusionImportExport</ucm:Field>

               <ucm:Field name="dDocAccount">hcm$/dataloader$/import$</ucm:Field>

               <ucm:Field name="primaryFile">sampleFile2.zip</ucm:Field>

               <ucm:File href="sampleFile2.zip" name="primaryFile">                  <ucm:Contents>UEsDBBQACAgIAIZyBVUAAAAAAAAAAAAAAAAQAAAAT3JnYW5pemF0</ucm:Contents>

               </ucm:File>

            </ucm:Document>

         </ucm:Service>

      </ucm:GenericRequest>

   </soapenv:Body>

</soapenv:Envelope>

Reference:

https://soavijaya.blogspot.com/2019/03/saas-urls.html?m=1

ERP - How to Use OTBI Analysis in BI Publisher

Usecase: Here. We will see how to use OTBI analysis report SQL query in BIP republisher report.

Steps followed:

1. Create OTBI analysis tabular report using Payable Invoices - Transactios Real time subject area. >> select few columns like Invoice Number, Invoice date, Invoice Type Name, Invoice amount, Supplier name >>Advanced tab >> copy SQL issued.
2. Create a data model >> add data set using SQL Query fetched from step1.  Data source is Oracle BI EE>> add a where clause and add P_Supplier_name parameter. >> add alias and display names for all the columns. 
3. Data tab >> view data , we can see parameter tag also showing >> go to properties and deselect "Include Parameter Tags" and select "include empty Tags for Null Elements." Save as sample data
4.  Create report >> select the columns >> customize report >> delete header part >> save >> view report

Detailed screenshots: