Oracle Integration - ERP Cloud BIP Report Lab

Background

Oracle Integration provides native connectivity to Oracle and non-Oracle Software as a Service (SaaS) and On-premises applications, such as Oracle ERP Cloud, Oracle Service Cloud, Oracle HCM Cloud, Salesforce.com, Workday, EBS, SAP, NetSuite and so on. Oracle Integration adapters simplify best practice connectivity to these applications by handling many of the underlying technical complexities often encountererd when integrating applications.

Use Case

This lab describes how to use Oracle Integration with Oracle ERP Business Intelligence Publish (BIP) report.

The user creates a report in Oracle ERP Cloud and uploads it to a shared folder. Oracle Integration connects to the ERP BIP service, pulls the report, decodes the response, applies the required transformations and writes it into the REST client.

The following diagram shows the interaction between the different systems in this use case:

Before You Begin

Some steps in this tutorial have a icon next to them. Click this icon to copy the snippet of text to your clipboard and then paste where needed.

Your instructor will provide you the information you need to access the environments used in this lab.

For this lab we will provide you the following:

• Oracle Integration
• ERP Cloud

Get Your Oracle Integration (OIC) Environment

Select your OIC Environment and enter your User Number to get the information needed to access Oracle Integration.

Select your OIC environment: 

User Number:  OK

• OIC URL:

  Select your OIC Environment to get your OIC URL.

• OIC User Name:

  Select your OIC Environment to get your OIC Username.

  Enter your User Number to get your OIC Username.

• OIC Password: Your password will be provided by the instructor.

Sign In

1. Open your Oracle Integration instance.

   Select your OIC Environment to get your OIC URL.

2. Enter your user name and password.

   Select your OIC Environment to get your OIC Username.

   Enter your User Number to get your OIC Username.

   Your password will be provided by the instructor.

3. Click Sign In.

   The Home page appears.

   You are now logged in and ready to use Oracle Integration.

   

Create the Short BIP Report Integration

1. On the Home page, click Integrations, and then click Integrations.
2. On the Integrations page, click Create.
3. In the Select Integration Style dialog, select App Driven Orchestration.
4. Click Select.
5. In the Create New Integration dialog, configure the integration:

   1. What do you want to call your integration?

      Enter Short BIP Report

      Enter your User Number to specify your unique integration name.

   2. In the What does this integration do? field, enter:

      This integration shows you how to connect to the ERP BIP service, pulls a report, decodes the response, and return the report results.

6. Click Create.

Create the REST Trigger

1. Hover over START and click .
2. Begin typing REST Interface in the Search field to find the connection to your REST interface.

3. Select the connection named REST Interface.

   The Configure REST Endpoint wizard appears.

4. Name your endpoint ShortBIPReport.

5. In the What does this endpoint do? field, enter:

   This endpoint defines the REST interface.

6. Click Next.
7. On the Resource Configuration page, configure the following properties:
   1. What does this operation do?: Retrieves ledgers from the ERP system.
   2. What is the endpoint's relative resource URI: /BIP
   3. What action do you want to perform on the endpoint?: select GET.
   4. Select Add and review parameters for this endpoint.
   5. Select Configure this endpoint to receive the response.
8. Click Next.

9. On the Request Parameters page, in the Specify Query Parameters section, click .

   A new row appears.

10. In the Name column, enter LedgerName.
11. From the list in the Data Type column, select string.

12. In the same way, create a Query Parameter named ReportAbsolutePath of type string.

13. Click Next.

14. On the Response page, configure the following values:

    1. From the Select the request payload format list, select JSON Sample.

    2. Click the <<<inline>>> link.
    3. Enter the following JSON sample:

       [
           {
               "LEDGERNAME" : "US Primary Ledger",
               "SEGMENT3" : 31,
               "SEGMENT4" : 21,
               "FIN_CATEGORY" : "FREIGHT"
           },
           {
               "LEDGERNAME" : "US Primary Ledger",
               "SEGMENT3" : 31,
               "SEGMENT4" : 21,
               "FIN_CATEGORY" : "FREIGHT"
           }
       ]

    4. Click OK.

15. Click Next.

    A summary of the properties you configured appears.

16. Review the summary and click Done.

17. On the Integration canvas, from the Layout list, select Horizontal.

    

18. Click Save to save the integration.

Get the BIP Report

1. Hover over the outgoing arrow for the ShortBIPReport activity and click .
2. Begin typing ERP Cloud External Report Service in the Search field.
3. Select ERP Cloud External Report Service.
4. In the Basic Info page, name your endpoint GetBIPReport.
5. Click Next.
6. On the Operations page, from the Select the Operation list, select runReport.
7. Click Next.
8. On the Headers page, click Next.
9. Review the summary and click Done.
10. Click Save to save the integration.

Define the Data Mapping

A Map action named Map to GetBIPReport is automatically created. We'll define this data mapping.

1. Select the Map to GetBIPReport action.

2. Click .

   The Data Mapping page appears.

3. In the Sources section, expand execute, and then QueryParameters.
4. In the Target section, under OutboundSOAPRequestDocument expand Body, and then expand runReport.

5. Map the ReportAbsolutePath field:

   1. In the Target section, expand reportRequest.

   2. Map the ReportAbsolutePath field in the Sources section, to the reportAbsolutePath field in the Target section.

6. Assign a value to the name field:

   1. In the Target section, expand parameterNameValues, and then expand item.
   2. Right click the name field.
   3. Click Create Target Node.
   4. In the Expression for name panel, enter "LedgerName".
   5. Click .
7. In the Target section, under parameterNameValues expand values.

8. Map the LedgerName field in the Sources section, to the item field located under values in the Target section.

9. Assign a value to the sizeOfDataChunkDownload field:

   1. Right click the sizeOfDataChunkDownload field.
   2. Click Create Target Node.
   3. In the Expression for sizeOfDataChunkDownload panel, enter -1.
   4. Click .

10. Your map should like this:

    
    

    

11. Click Validate.

    A confirmation message appears.

12. Click Test.
13. Click Generate Inputs.
14. Click Execute.

15. Verify the Target payload contains sample data for the data elements you mapped. Specifically:

    1. name
    2. item
    3. reportAbsolutePath
    4. sizeOfDataChunckDownload
    
    

    

16. Click Close.
17. Click Save to save the integration.

Write the File

1. Download the opaque_schema.xsd file.
2. Hover over the outgoing arrow for the GetBIPReport activity and click .
3. Type Stage File in the Search field.

4. Select the action named Stage File.

5. In the Basic Info page, name your action StageFileWrite.
6. Click Next.

7. On the Configure Operation page, from the Choose Stage File Operation list, select Write File.

8. Specify the file name:

   1. Click next to the Specify the File Name field.

      The Configure Stage File Action dialog appears.

   2. In the Expression field, enter "temp.csv".

   3. Click Save.

      A confirmation message appears.

   4. Click OK.
   5. Click Exit Expression Builder

9. Specify the output directory:

   1. Click next to the Specify the Output Directory field.

      The Configure Stage File Action dialog appears.

   2. In the Expression field, enter "/tmp".

   3. Click Save.

      A confirmation message appears.

   4. Click OK.
   5. Click Exit Expression Builder
10. Click Next.
11. On the Schema Options page, select XML Schema (XSD) document
12. Click Next.
13. On the Format Definition page, click Choose File and select the opaque_schema.xsd you downloaded.
14. Click Next.
15. Review the summary and click Done.
16. Click Save to save the integration.

Define the Data Mapping

A Map action named Map to StageFileWrite is automatically created. We'll define this data mapping.

1. Select the Map to StageFileWrite action.

2. Click .

   The Data Mapping page appears.

3. In the Sources section, expand $GetBIPReport, then runReportResponse, and then runReportReturn.
4. Map the reportBytes field in the Sources section, to the opaqueElement field in the Target section.

5. Click Validate.

   A confirmation message appears.

6. Click Close.
7. Click Save to save the integration.

Read the File from Stage

1. Download the GLCCReport.csv file.

2. Click and from the Data section, drag and drop Stage File to the Integration canvas, and place it after the StageFileWrite activity.

   

   The Configure Stage File Action wizard appears.

3. On the Basic Info page, name the activity ReadFileFromStage.
4. Click Next.

5. On the Configure Operation page, from the Choose Stage File Operation list, select Read Entire File.

6. Specify the file name:

   1. Click next to the Specify the File Name field.

      The Configure Stage File Action dialog appears.

   2. In the Expression field, enter "temp.csv".

   3. Click Save.

      A confirmation message appears.

   4. Click OK.
   5. Click Exit Expression Builder.

7. Specify the output directory:

   1. Click next to the Specify the Output Directory field.

      The Configure Stage File Action dialog appears.

   2. In the Expression field, enter "/tmp".

   3. Click Save.

      A confirmation message appears.

   4. Click OK.
   5. Click Exit Expression Builder.
8. Click Next.
9. On the Schema Options page, click Next.

10. On the Format Definition page, specify the following values:

    1. Click Choose File and select the GLCCReport.csv file you downloaded.
    2. In the Enter Record Name field, enter Ledger.
    3. In the Enter Recordset Name field, enter LedgerSet.

11. Click Next.

    A summary of the properties you configured appears.

12. Review the summary and click Done.
13. Click Save to save the integration.

Define the Data Mapping

A Map action named Map to ShortBIPReport is automatically created. We'll define this data mapping.

1. Select the Map to ShortBIPReport action.

2. Click .

   The Data Mapping page appears.

3. In the Sources section, expand $ReadFileFromStage, then ReadResponse, and then LedgerSet.
4. In the Target section, expand response-wrapper.
5. Map the Ledger field in the Sources section, to the topLevelArray field in the Target section.
6. In the Sources section, expand Ledger.
7. In the Target section, expand topLevelArray.

8. Map the following fields from the Sources section to the fields in the Target section:

   Source	Target
   LEDGERNAME	LEDGERNAME
   SEGMENT3	SEGMENT3
   SEGMENT4	SEGMENT4
   FIN_CATEGORY	FIN_CATEGORY

9. Your map should like this:

   
   

   

10. Click Validate.

    A confirmation message appears.

11. Click Close.
12. Click Save to save the integration.

Define the Tracking Fields

1. In the Integration canvas, you should see an error icon next to the Last Saved field.

   The icon shows the number of errors you need to fix in your integration. You should have one error. To fix this error you need to add business tracking identifiers.

2. On the Integration canvas, click , and then select Tracking.

   

   The Business Identifiers for Tracking dialog appears.

3. From the Source section, select LedgerName, and then click .

4. Click Save.

   Notice there are no errors.

5. On the Integration canvas, click Save, and then click Close.

Activate the Short BIP Report Integration

1. On the Integrations page, locate your integration and hover over it.

   The name of your integration is Short BIP Report .

   Enter your User Number to get your unique integration name.

   When you hover over the integration, a set of actions appear in the Status column.

   
2. Click .

3. Select Enable Tracing, and then select Include Payload.

   Typically these options are disabled in production environments, but we'll enable them for the purpose of this lab.

4. Click Activate.

   The activation will complete in a few seconds. If the activation succeeds, a confirmation message appears at the top of the page, and the status will change to ● Active.

Run the Integration

1. We can test our integration right from Oracle Integration.

2. On the Integrations page, locate your integration and hover over it.

   The name of your integration is Short BIP Report .

   Enter your User Number to get your unique integration name.

   When you hover over the integration, a set of actions appear in the Status column.

   
3. Click .

4. Click Test.

5. Configure the URI parameters:

   • ReportAbsolutePath: /Custom/Financials/GLCCReport.xdo
   • LedgerName: US Primary Ledger
6. Click Test (in the upper right of the page).
7. Expand the Response section to verify that you got a successful response and verify the Status is 200 OK.

8. Click to open the navigation panel.

   
   

   

9. Click Integrations in the top level navigation pane to return to Home.

   
   

   

10. From Home, click Monitoring, then Integrations, then Tracking.
11. Find the integration with your integragtion name and click the Ledger Name link to open the integration instance.

    The flow is colored in green to indicate that it ran successfully.

12. Click and select View Activity Stream.

    
    

    

13. Review the flow and messages from your integration.
14. Click Close when done reviewing.

Congratulations!

Congratulations! You have invoked an integration from a web client using REST. On the Oracle Integration side, you've called a service, convert the response to JSON, and sent the data back to the web client.