Working with ERP Cloud Business Events in Oracle Integration 2 (OIC Gen 2)

6
1
Send lab feedback

Introduction

This lab highlights one of the most common customer use cases of using Oracle Integration to address the needs for real-time data synchronization between upstream and downstream systems or applications.

What's real-time synchronization integration pattern?

view in browser

This lab explores the use of Oracle Integration (OIC) Gen 2 to subscribe to Oracle ERP Cloud Events and push and orchestrate the data to a target database table (i.e. Oracle Autonomous Database). As part of the lab you will build the following use case scenario:

  • You create and activate an integration that subscribes to an ERP Cloud Purchase Order (PO) event.

  • You then create a PO in ERP Cloud and a PO event is triggered.

  • Your integration receives the PO event and pushes the data into an Oracle Autonomous Data Warehouse (ADW).

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

What is OCI Integration & EPR Cloud Adapter

With Oracle Integration (OIC) Gen 2, you have the power to integrate your cloud and on-premises applications, automate business processes, gain insight into your business processes, develop visual applications, use an SFTP-compliant file server to store and retrieve files, and exchange business documents with a B2B trading partner — all in one place.

The Oracle ERP Cloud Adapter enables you to easily integrate on-premises or SaaS applications with Oracle ERP Cloud without having to know about the specific details involved in the integration.

The Oracle ERP Cloud Adapter provides the following key benefits:

  • Provides declarative support for subscribing to business events raised by various modules in Oracle ERP Cloud and Oracle Supply Chain Management Cloud.
  • Integrates easily with the Oracle ERP Cloud application’s WSDL file to produce a simplified, integration-centric WSDL.
  • Generates automatic mapping to the selected business object, event subscription, or business (REST) API.
  • Simplifies connection creation by automatically identifying the required service resources based on the Oracle ERP Cloud host name you specify when creating a new connection on the Connections page.
  • Supports several security policies for selection during Oracle ERP Cloud Adapter connection configuration:
    • Username Password Token With PGP Key Support
    • Username Password Token
    • OAuth Authorization Code Credentials
  • Provides standard error handling capabilities.
  • Enables you to upload a file in the Oracle ERP Cloud business tables.
  • Enables you to upload files to Oracle WebCenter Content (Universal Content Manager) in encrypted or unencrypted format.

Task 1: Create Your Oracle Integration (OIC) Gen 2 Instance

Walkthrough demo video

view in browser

You will be using Luna's virtual desktop at the right of your screen to work on your labs.

  1. Click on the Luna Lab icon located on the virtual desktop on your right.

  1. Here you will find the login tenant information required for this lab. Now click on the OCI Console to logon.

  1. Back to the Luna Lab tab on top of your browser and copy and paste the credentials (username and password) to the login page. NOTE: Do not use your Oracle SSO account if you are Oracle employee.

  1. Now you are on OCI mainpage. Click the hamburger icon at the top left corner. Select Developer Services on the left menu, then Integration.

  1. Click Compartment on the left. Expand the directory by clicking the + sign to find Luna-Labs sub-directory. Click on the + and you will see a list of ephemeral accounts sharing the same tenancy. Scroll down the list and find yours by cross-checking the username assigned to you on the Luna Lab tab. Then click on Create button on the right to create an OIC instance.

  1. The Create Integration Instance window displays. In Name field, give a name to your OIC instance. Version: choose Oracle Integration Gen 2 which is the version we will use for this lab. Edition is Enterprise, License Type is Subscribe to a new Oracle Integration license. Then click Create at the bottom of the form.

  1. It takes around 10-15 minutes for a new OIC Gen 2 instance to be created for you. Once it is done, the state will be changed from creating to active and you will be able to see a green dot.

  1. Click on your instance link under Display Name. Click the Service Console to activate the OIC instance.

  1. Now you are at Oracle Integration (OIC) mainpage.

Task 2: Provision an Oracle Autonomous Database (ADW)

Walkthrough demo video

view in browser

This task walks you through the steps to get started using the Oracle Autonomous Database (ADW) on Oracle Cloud. In this task, you will provision a new ADW instance.

Note: While this lab uses ADW, the steps are the same for creating an ATP database.

  1. Return to Luna Lab tab on top of your browser. Click OCI Console link again to start a new tab for OCI main page.
  2. Click the navigation menu in the upper left to show top level navigation choices.

  1. Click Oracle Database, then Autonomous Data Warehouse.

  1. This time your ephemeral username has been chosen in the compartment field. You don't have to change it if that is correct. Make sure your Workload Type field on the left pane is Data Warehouse as default.

  1. Click Create Autonomous Database to start the instance creation process.

  1. This brings up the Create Autonomous Database screen where you will specify the configuration of the instance.

  2. Specify basic information for the autonomous database:

    • Compartment - Leave the default compartment.
    • Display Name - Enter a memorable name for the database for display purposes. For this lab, enter
      ADWFinanceMart
    • Database Name, enter
      LunaADW

  3. Choose a workload type. Select Data Warehouse.

  1. Choose a deployment type. Select Shared Infrastructure as the deployment type.

  1. Configure the database:

    • Luna has pre-configured the resources for you in this lab. Just review the info and leave it as is.

  2. Create administrator credentials:

    • Password and Confirm Password - Specify the password for ADMIN user of the service instance. Enter:
      DBWelcome12345

  3. Choose network access:

    • Accept the default, Secure access from everywhere

  1. Choose a license type. For this lab, choose License Included.

  1. For this lab, do not provide a contact email address. The "Contact Email" field allows you to list contacts to receive operational notices and announcements as well as unplanned maintenance notifications.

  1. Click Create Autonomous Database.

  1. Your instance will begin provisioning. In a few minutes, the state will turn from Provisioning to Available. At that point of time, your Autonomous Data Warehouse database is ready to use! Have a look at your instance's details here including its name, database version, OCPU count, and storage size.

Task 3: Download the Database Wallet & Create a Database Table

Walkthrough demo video

view in browser

As the ADB instance is ready to use, let's follow these steps to download the database connection details and then create a DB table which will be used as part of this workshop.

Obtain Database Connection Details

  1. Click the DB Connection button.

  1. In the displayed Database Connection dialog, click Download Wallet.

  2. Provide a Wallet password, enter DBWelcome12345 then click Download. This file will be saved into Luna desktop's Download folder. It will be used later when creating the Autonomous Database connection in Oracle Integration.

  3. Under Connection Strings, it shows couple of TNS Name entries. We will use one of them later when creating the Autonomous Database connection in Oracle Integration.

  4. Click Close.

Create a Database Table Using a SQL Script

  1. Click the Database Actions button.

Note: If the page doesn't open after the click, click once more. Also, a sign-in page may open. If you are asked to login, use your database instance's default admin account, Username = ADMIN and click Next. Enter DBWelcome12345 as the password we entered earlier.

  1. The Database Actions page opens. In the Development box, click SQL.

  1. The SQL Worksheet appears. Copy below code snippet:

    CREATE TABLE PURCHASEORDER 
(
POHEADERID NUMBER GENERATED BY DEFAULT AS IDENTITY INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20 NOT NULL 
, ORDERNUMBER VARCHAR2(20 BYTE) 
, SOLDTOLEGALENTITYID NUMBER 
, CREATIONDATE DATE 
, STATUS VARCHAR2(20 BYTE) 
, TOTAL NUMBER(*, 2) 
, CONSTRAINT PURCHASEORDER_PK PRIMARY KEY 
(
    POHEADERID 
)
USING INDEX 
(
    CREATE UNIQUE INDEX PURCHASEORDER_PK ON PURCHASEORDER (POHEADERID ASC) 
    LOGGING 
    TABLESPACE DATA 
    PCTFREE 10 
    INITRANS 20 
    STORAGE 
    ( 
        INITIAL 65536 
        NEXT 1048576 
        MINEXTENTS 1 
        MAXEXTENTS UNLIMITED 
        BUFFER_POOL DEFAULT 
    ) 
    NOPARALLEL 
)
ENABLE 
) 
LOGGING 
TABLESPACE DATA 
PCTFREE 10 
INITRANS 10 
STORAGE 
( 
INITIAL 65536 
NEXT 1048576 
MINEXTENTS 1 
MAXEXTENTS UNLIMITED 
BUFFER_POOL DEFAULT 
) 
COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING 
NO INMEMORY 
NOPARALLEL;

  2. Paste the script in SQL Worksheet, then click Run Script button. This will create the PURCHASEORDER table. The table is created successfully when you see the notification in the Script Output.

Now you have an external database table which will be used in the demo flow.

Task 4: Create an Oracle ERP Cloud Adapter Connection

Walkthrough demo video

view in browser

As our database table is established, we are now moving to Oracle Integration (OIC) environment to build an integration. You have to create the connections to the applications with which you want to share data. Follow these steps to create an ERP Cloud connection within OIC.

  1. Return to the Integration tab on top of your browser. Your OIC instance should have been created when you see the state is Active.

  2. Click the link of your OIC instance name.

  1. Click the Service Console button to go to OIC landing page.

  2. Click the hamburger button on the top left corner to display the left navigation menu and click Integrations > Connections.

  3. Click Create.

  4. In the Create Connection - Select Adapter dialog, select the Oracle ERP Cloud adapter to use for this connection. To find the adapter, enter erp in the search field. Click on the highlighted adapter and hit Select.

  1. In the Create Connection dialog, enter the following information:

    • Name:
      LLDemo_ERP
    • Description:
      ERP Connection for Luna Lab demo

    Keep all other values as default.

In the Oracle ERP Cloud Connection dialog, you need to provide your Fusion instance details in order for OIC Integration to access your ERP Cloud environment. Find out the details by following below steps:

  1. ERP Cloud Host: This requires your fusion instance URL. Return to Luna Lab tab on top of your browser. Select Demo Cloud tab. This will show you the details of your assigned Fusion environment including the Launchpad and login credentials. We are going to use the cloned user of casey.brown that provides you with ability to access EPR Cloud and then create a purchase order from there.

  1. Click on Fusion Launchpad under Resource URLs. It will then open the Fusion Applications login page.

  2. Click ERP Cloud and SCM Cloud Login tab to login. A sign in page appears. Return to Luna Lab tab and copy and paste the system-generated username and password to the sign in fields.

  1. Copy the first part of the URL (e.g. http....com) and paste it into EPR Cloud Host field in OIC.

  1. Security Policy: leave it as default - Username Password Token

  2. Username and Password: return to Luna Lab tab and copy and paste the system-generated username and password.

It should look like this now:

  1. Click on Test, followed by Save. Exit the connection canvas by clicking the < button on the top left side of the screen.

Task 5: Create an Oracle Autonomous Data Warehouse Connection

Walkthrough demo video

view in browser

Let's create a connection with the Oracle Autonomous Data Warehouse Adapter within OIC.

  1. From the Connections window, click Create.

  2. In the Create Connection - Select Adapter dialog, select the Oracle ADW adapter to use for this connection. To find the adapter, enter adw in the search field. Click on the highlighted adapter and hit Select.

  1. In the Create Connection dialog, enter the following information:

    • Name:
      LLDemo_ADW
    • Description:
      ADW Connection for Luna Lab demo

    Keep all other values as default. Hit Create.

  2. In the Oracle ADW Connection dialog, enter the following information:

    FieldValues
    Service Namelunaadw_low
    Security Policyas default - JDBC Over SSL
    Walletclick upload icon to upload the zip file. Find the zip file by clicking Home on the left, then Downloads, select the file and click Select
    Wallet PasswordDBWelcome12345
    Database Service UsernameADMIN
    Database Service PasswordDBWelcome12345

  1. Click on Test, followed by Save. Exit the connection canvas by clicking the back button on the top left side of the screen.

Task 6: Initiate an App Driven integration Flow for the Purchase Order Event

The following tasks will walk you through the steps to create an end-to-end integration of receiving ERP Purchase Order events and persisting the data in an ADW table. We will start by creating a new integration and adding some basic info.

  1. In the left Navigation pane, and click Integrations > Integrations.
  2. On the Integrations page, click Create.
  3. On the Integration Style dialog, select App Driven Orchestration, followed by Select.

  1. In the Create New Integration dialog, enter the following information:

    • Name:
    LLERPEventDemo
    • Description:
    ERP Event integration for Luna Lab demo

    Accept all other default values.

  2. Click Create.

  3. Click Save to persist changes.

Task 7: Define ERP Purchase Order (PO) Event Trigger

Walkthrough demo video

view in browser

Our aim for this task is to add ERP PO Event trigger to the empty integration canvas.

  1. Click the + sign below START in the integration canvas.

  2. Select the configured ERP Cloud adapter. This invokes the Oracle ERP Cloud Endpoint Configuration Wizard.

  3. On the Basic Info page, for What do you want to call your endpoint? element, enter

    ERP_POEvent

  4. Click Next.

  5. On the Request page, select the following values:

    • Define the purpose of the trigger: Choose Receive Business Events raised within ERP Cloud
    • Business Event for Subscription: Choose Purchase Order Event
    • Filter Expr for Purchase Order Event
      <xpathExpr xmlns:ns0="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/" xmlns:ns2="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/">$eventPayload/ns2:result/ns0:Value/ns0:DocumentDescription="demo"</xpathExpr>

    Note: You can use a custom filter expression by inserting a different value under DocumentDescription. The value you enter is case sensitive.

  6. Click Next.

  7. On the Response page, for Response Type element, choose None. Click Next.

  8. On the Summary page, click Done.

  9. On the integration canvas, from the Layout list, choose Horizontal.

  10. Click Save to persist changes.

Task 8: Add the ADW Invoke Activity

Now, We move on to add the Oracle Autonomous Data Warehouse Adapter invoke to the integration canvas to complete the integration setup.

  1. Hover you cursor over the + sign that is displayed after the trigger activity in the integration canvas. Click the + sign and select the ADW connection you created previously.

    This invokes the Oracle Autonomous Data Warehouse Endpoint Configuration Wizard.

  2. On the Basic Info page, select the following values:

    ElementValue
    What do you want to call your endpoint?ADW_InsertPO
    What operation do you want to perform?Perform an Operation on a Table
    What operation do want to perform on Table?Insert

    Click Next

  3. On the Table Operation page, select the following values:

    ElementValue
    SchemaADMIN
    Table TypeTABLE
    Table Name(keep blank) and click Search
    AvailablePURCHASEORDER and click > to move the table to the Selected column

  4. Click on Import Tables, wait and press Next.

  5. When the Select the parent database table element appears, click Next.

  6. On the Summary page, click Done.

  7. Click Save to persist changes.

Task 9: Map data between ERP Trigger and ADW Invoke

Walkthrough demo video

view in browser

We are going to use the OIC mapper to drag fields from the source structure (ERP PO event data) to the target structure (ADW PO table) to map elements between the two systems (ERP Cloud and ADW) in order to enable the flow of data when the event arises.

When we added the ADW invoke to the integration, a map icon was automatically added.

  1. Hover you cursor over the ERP-ADW Mapper icon, and click Edit.

  2. Use the mapper to drag element nodes in the source ERP Cloud structure to element nodes in the target Oracle ADW structure.

    • Expand the Source node per below clicks:

      ERP_POCreateEvent Request > Get Purchase Order Response > Result > #2 sequence > Value

    • Expand the Target node per below clicks:

      ADW_InsertPO Request > Purchaseorder

    Complete the mapping as below:

    Source (ERP_POCreateEvent)Target (ADW_InsertPO)
    PO Header Idpoheaderid
    Order Numberordernumber
    Sold To Legal Entity Idsoldtolegalentityid
    Creation Datecreationdate
    Document Statusstatus
    Total Amounttotal

  3. Click Validate, then wait for the confirmation message Mapping is valid and ready to use.

  4. Click Close

  5. Click Save to persist changes.

Task 10: Define Tracking Fields

We need to identify at least one business identifier in OIC that enables you to track fields in messages during runtime.

A red number icon is displayed at the top of the Integration canvas when identifiers have not been set.

  1. Click the Actions menu on the top right, then select Tracking.

  2. From the Source section on the left, expand getPurchaseOrderResponse > result, click Load more, expand Value, and click Load more. Drag the OrderNumber field from ERP PO source to the Drag a trigger field here section:

  3. Click Save.

  4. On the Integration canvas, click Save, followed by Close.

Task 11: Activate the integration

  1. On the Integrations page, click on the Activate icon.

  2. On the Activate Integration dialog, select Enable Tracing, followed by Include Payload options.

  3. Click Activate. The activation will complete in a few seconds. If activation is successful, a status message is displayed in the banner at the top of the page, and the status of the integration changes to Active.

You have completed the final step of creating integration flow. Please proceed to the next task.

Task 12: Create a Purchase Order in ERP Cloud

Walkthrough demo video

view in browser

The following few tasks will walk you through the steps to create an ERP Purchase Order in ERP Cloud environment and validate how the data is processed in the integration flow.

  1. Click Click Oracle Fusion Cloud Applications (jump to step 4 if you don't need to log in again) or Launch Pad tab on top of your browser to return to the Fusion login page.

  2. Choose ERP and SCM Cloud Login.

  1. Click Luna Lab tab on top of the browser. Select Demo Cloud tab to find casey.brown's credentials. Copy and paste the Username and Password to the ERP Login Page.

  2. When you are in ERP Cloud, navigate to the Procurement Tab.

  3. Click Purchase Orders.

  4. In the Overview section, click Tasks button on the right.

    This opens the Tasks menu.

  5. Under the Orders section, select Create Order.

The Create Order dialog is displayed.

  1. Requisitioning BU is a mandatory field. Choose US1 Business Unit.

  2. Enter a valid entry in the Supplier field, enter ABC Consulting, and select the corresponding supplier in the drop down.

    Tip: You can also search for valid suppliers using the Search icon.

  3. Click Create.

    This opens the Edit Document (Purchase Order) page.

  4. Under General section, in the Description field on the right, enter the same value that we used to define the Filter Expr for Purchase Order Event. That is demo. Only with this value inputted correctly then it will trigger the OIC integration to work.

    demo

  1. In the Lines Tab, click + to add a Purchase Order row.

  2. Enter values in the below fields (sample values provided)

    FieldValue
    Line1 (Default)
    TypeGoods
    ItemStart typing AS1, then select an item from the drop-down (or hit the search button to select a valid item)
    Description(keep default)
    QuantityEnter a valid number, eg. 2
    UOMEa (default)
    Base PriceEnter a valid number, eg. 200

  3. Click the EDIT button under Lines section.

    This opens the Edit Line page for the current purchase order line.

  4. Enter a future date in either Requested Delivery Date or Promised Delivery Date fields.

  5. Click OK at the top right of the Edit Line page and return to the parent window.

  6. Click Submit to initiate the the Purchase Order processing.

    After submitting the Purchase Order, a confirmation message should appear with the PO number. Remember this PO number as you will need it in next task.

  7. Click OK to close the confirmation dialog.

Task 13: Validate Purchase Order Status

After the PO is submitted, the initial status becomes Pending Approval. The PO Create event will occur once the status changes to Open.

  1. In the Overview section, click Tasks button on the right. This opens the Tasks menu.

  2. Under the Orders section, click on Manage Orders.

  3. Click Search. You should see the Purchase Orders for the current user.

  4. Look for your Purchase Order in the list with the PO number displayed in the previous task.

    Tip: The last created PO should generally be the top one in the list.

  5. Validate the PO Status. If it's Open, then the Business Event has occurred.

    Note: If PO has another Status, such as Pending Approval, then wait a couple of minutes and keep refreshing the page until the desired PO Status appears.

Task 14: Track message flow triggered by the PO Create Event in OIC

Walkthrough demo video

view in browser

Let's return to Oracle Integration to use its dashboard to see the data flow resulting from the create Purchase Order event in ERP Cloud.

  1. Click Oracle Integration tab on top of your browser.

  2. In the Integration navigation pane, click Home > Monitoring > Integrations > Tracking

  3. Find our corresponding Integration Instance, by matching the PO number which was generated in ERP Cloud. This should be under the column Primary Identifier or Business Identifiers. (If it doesn't show up yet, click the refresh button at the upper right corner next to the date/time.)

  4. Click on your Order Number link to open the corresponding instance.

The flow ran successfully if it is displayed with a green line.

  1. Click on the Actions menu on the top right of the screen, and select View Activity Stream.

  2. In the Activity Steam window, click on the different Message links to review the flow of request and response messages.

  3. Click Close after reviewing the Activity Stream.

Task 15: Verify PO Record in ADW Table

Follow these steps to view the PO record in the designated DB table.

  1. Return to the SQL tab on top of your browser.

    Note: Sign-in with your database instance if required: Username = ADMIN and Password = DBWelcome12345.

  2. Click Database Actions on the top bar to return to the ADW mainpage to refresh the page.

  1. Select SQL box.

  2. In the Navigator on the left, select the PURCHASEORDER table, then right-click on Open.

    This opens the ADMIN.PURCHASEORDER table window.

  3. Click on Data in the left menu to display the table data. Verify the PO record is available.

Congratulations! You have completed the final step of this workshop. Thank you!

SSR