INTRODUCTION TO OIC

                                                         INTRODUCTION TO OIC

-What is OIC?  (Oracle Integration CLoud)

-Why OIC?

-Usecase?

-Inboud Integrations(FBDI,WebServices(SOAP,REST),HDL)

-Outbound Integrations(BIP,WebServices(SOAP,REST),HCM Extract,Atomfeed)

-Event Integrations

-Schedule Integrations

-APllication Style Integration

-FTP Encryption and Decryption Integration

-Integrations using DataBase(ATP)

-Calling BIP Report with and without parameters and sending data to multiple applications

- Performing CRUD operations using WebServices

SAAS - at every month we need to pay for what we are using - Fusion

PAAS - OIC - ICS+VBCS+PCS /opa

Iaas - OCI

OIC - integration 

cloud - Cloud

cloud -non cloud

oracle -non oracle 

-Building OIC integrations is much more easier when compared to other products like SOA.

-We can move data from onpremise to cloud and viceversa easily.

-In other applications process automation is not aligned with Integration. (SOA,Delbhumi)

-

Steps To build Integration :

-> Create Connections(adapters) - To establish connectivity between Source and Target

-> Create Integration

Types:

1.Inbound : Sending data to Fusion  from Thirdparty 

2.Outbound : Extracting data from fusion

                     FSCM                                        HCM 

Inbound Approaches : 1.FBDI 2.Webservice(SOAP/REST)             HDL,Webservice

Oubound Approaches : BIP,Webservice,OTBI                        HCM Extract, atomfeed  - 

OIC - PaaS - Platform as a Service

How to integrate

1) Oracle SaaS (ERP) with another Oracle SaaS (HCM)

2) Oracle SaaS (ERP) with Non Oracle SaaS (Salesforce)

3) Oracle SaaS (ERP)/Non Oracle SaaS (Salesforce) with On Premise App (EBS, Peoplesoft etc.)

Integrations Designer Page - 70% time

Salesforce with ERP Cloud

Whenever customer is created in Salesforce, same customer needs to be created in ERP Cloud - without time gap and no manual intervention

1) Create connections - Salesforce, ERP Cloud

2) Build integration flow using connections

OIC Gen 3 - Old Gen 2 

Integration Styles :

GEN3

1. Appplication : Trigger from ecternal application

2. Schedule : Based on schedule 

3. Event - Publish , Subscriber : Based on the event occured/bussiness event raised within the ERP Cloud

GEN2

1.App Driven

2.Schedule

3.Publish to OIC

4.Subscribe to OIC

5.File Transfer

6.Basic Routing

If your client has:

a) Only one Integration Team -> Build Integrations in Design screen

b) Multiple Integration Teams -> 

1) US Integration Team/UK Integration Team

2) FIN Integration Team/HCM Integration Team

Notification/Error Handler

Create Vs Import

100 Integrations:

Dev -> SIT -> UAT -> Prod

unogeeksdevoic -> unogeekssitoic

ERP Cloud - 70 - ERP_PKG

HCM Cloud - 20 - HCM_PKG

Sales Cloud - 10 - SALES_PKG

Integration Style:

1) You will pick up a style when you create it and this cannot be changed later on

2) Integration style will decide what you can/cannot do within the integration

Trigger - First node in integration

Invoke - Any subsequent node in integration

------------------------

App Driven Orchestration:

------------------------

Billing Systems -> AR Invoices -> Trigger Integration built in OIC and pass invoices payload in xml/json format -> create AR Invoices into Cloud

-----------------------

Scheduled Orchestration:

-----------------------

Use Case 1:

App A (WMS) -> Items csv Files -> SFTP Server 1 (On Premise, with in client network)

File Transfer service -> Pick up file from SFTP 1 and drop it in SFTP 2 -> Schedule the service

                                 SFTP Server 2 (Outside of client network) ->  App B (OTM)

Managed File Transfer (MFT)

Use Case 2:

Core Banking System -> Journals File -> SFTP Server -> 3:00 AM

3:30 AM -> Scheduled Orchestration -> Pick up file from SFTP -> Transform/enrich/validate file -> FBDI -> ERP GL Cloud

Use Case 3:

Custom .Net/Java App -> HCM Emps/Depts File -> SFTP Server -> 3:00 AM

3:30 AM -> Scheduled Orchestration -> Pick up file from SFTP -> Transform/enrich file -> HCM Data Loader -> HCM Cloud

-------------------

Event Orchestration:

-------------------

Whenever Item updated in ERP Cloud -> Merge item details into ATP DB table, Create a file and place it in SFTP, Call a REST API to OTM App

1) Publisher Service -> ERP Cloud Conn as trig conn -> Subs to item update business event -> item details will be published to OIC Channel

2) Subscriber Service 1 -> select publish to oic service created above -> Invoke DB Conn and merge records into DB table

3) Subscriber Service 2 -> select publish to oic service created above -> Invoke FTP Conn and place the file in SFTP

4) Subscriber Service 3 -> select publish to oic service created above -> Invoke REST Conn

Lookups:

1) Used to store common properties like Email Ids, FTP Paths etc. 

GL User -> tim@oracle.com - steve@oracle.com

lookupValue('UG58_Common_Properties_Lookup', 'Property_Name', 'GL_JOURNAL_TO_EMAIL', 'Property_Value', '')

2) Used to store mappings between different systems:

ERP Cloud -> EBS

ITEM A    -> ITEM 1

ITEM B    -> ITEM 2

ITEM C    -> ITEM 3

Prepare to pass exam: 1Z0-1042-23

https://education.oracle.com/oracle-cloud-infrastructure-2023-application-integration-professional/pexam_1Z0-1042-23

Getting Started with OIC Integrations 18%

Using OIC Connections and Adapters 21%

Creating Orchestrated Integrations 40%

Creating Scheduled Integrations 11%

Testing, Monitoring, and Troubleshooting Integrations 16%

 ERP,SOAP,REst,FTP - fizezilla ,winscp ,ATP - Database

 HCM 

 

 1 2 3 4 trigger,invoke

SQL : Create Table Requirement with Query

Scenario for below topics 

• Create table syntax 
• Constraints 
• Data Types
• Indexes
• Who Columns 

Requirement 

DROP TABLE miscn.miscn_salesreps_stg;

CREATE TABLE miscn.miscn_salesreps_stg (

    srp_stg_id                     NUMBER,

    tenant_code                  VARCHAR2(240) NOT NULL,

    email_address              VARCHAR2(1000) NOT NULL,

    person_id                     VARCHAR2(240)  , 

    active_flag                   VARCHAR2(1) NOT NULL,

    srep_name                   VARCHAR2(1000) NOT NULL,

    start_date_active          DATE NOT NULL,

    end_date_active            DATE,

    non_employee_flag      VARCHAR2(1) NOT NULL,

    change_number           NUMBER  NOT NULL,

    processing_request_id   NUMBER  NOT NULL,

    processing_status          VARCHAR2(240) NOT NULL,

    processing_message      VARCHAR2(4000),

    creation_date                 DATE NOT NULL,

    last_update_date           DATE NOT NULL,

    created_by                    VARCHAR2(240) NOT NULL,

    last_updated_by           VARCHAR2(240) NOT NULL,

    CONSTRAINT miscn_salesreps_stg_u1 PRIMARY KEY ( srp_stg_id )

);

CREATE UNIQUE

  INDEX MISCN.MISCN_SALESREPS_STG_U2 ON MISCN.MISCN_SALESREPS_STG

  (

    TENANT_CODE,

    EMAIL_ADDRESS

  ); 

  

 CREATE SEQUENCE MISCN.MISCN_SALESREPS_STG_SEQ START WITH 1 INCREMENT BY 1;

Query to fetch incremental data/delta data/ESS job last run date

 Query to fetch incremental data/delta data/ESS job last run date

WHERE last_update_date > (

select  

  max(R.COMPLETEDTIME)

from 

  (

    select 

      v1.requestid, 

      v1.value report_path 

    from 

      fusion.ess_request_property v1 

    where 

      1 = 1 

      AND v1.name LIKE 'report_url' 

      AND v1.value = '/Custom/Pavani/EFACost_Report.xdo' — replace report name 

  ) F1, 

  (

    select 

      v2.requestid, 

      v2.value job_name 

    from 

      fusion.ess_request_property v2 

    where 

      name = 'ujobname' 

      and value = 'FA' — replace your job name 

  ) F2 ,

  ess_request_history r 

where 

  F1.requestid = F2.requestid 

  AND F1.requestid = r.requestid

  AND EXECUTABLE_STATUS='SUCCEEDED'

  

  )

Differences between OTBI and BIP reports

 Main Differences between OTBI and BIP reports

1.BIP Reports is less user friendly as we need some technical expertise to develop the BIP reports but OTBI reports are more user friendly , we don't need technical expertise. We can develop the OTBI report with just drag and drop option.

2.BIP report is the combination of two Objects (Data Model & Report) but OTBI report has only one object Analysis.

3.For BIP reports , we need to write the SQL Query to extract the Data from Oracle fusion but in OTBI reports , we don't need to write the SQL query , except we just select the column which we want to show in the report using drag and drop and create the OTBI reports.

4. BIP reports mostly suitable for complex reports where we have to extract the data from multiple tables but  OTBI report only suitable for simple data scenarios where we have two extract data from 1 or 2 tables.

5. In BIP reports , we have the more flexibility in terms of data extraction as we can write the sql query as per our requirement but in OTBI reports , we have no flexibility ,  we can only extract those data which is available in OTBI reports , we cannot add or do changes in the Data Extraction.

6. In OTBI reports , system write the sql queries automatically based on our column selection for Data extraction but in the BIP report , we have to write the sql's statements as per our requirment.

7. OTBI reports more about simple data extraction tool but BIP is more robust tool as we can extract the design the reports layout as per our requirements.

8. In OTBI reports , we cannot design the Complex Reports Layouts for Data extraction , we can only design table based data extraction layouts but in BIP reports we can design any report layout as per our requirement.

9. We cannot design the statuary reports in OTBI reports. We have to use the BIP reports tool for statuary reports.

10.From user prospective , BIP reports GUI is much better as compared to OTBI reports.

11. OTBI reports can design very quickly but BIP reports development can take some time.

12.In BIP reports we can use the feature of report bursting but in OTBI reports we cannot use Report bursting.

13. In BIP reports, we can design multiple Templates but in the OTBI reports we can have only single template layout.

14. OTBI Reports access related to Data roles assign to our user account , based on that we can access the data in the Oracle Fusion application but in BIP reports , there is no relation of Data roles assigned to user , so user can access data irrespective of Data roles or access.

15. In terms of security , OTBI reports are more secure as compared to BIP Reports.

Using Sandbox Personalization default alias field in supplier page by concatenating Supplier name and supplier number.

 REQUIREMENT:

Need to generate the value for alias column in supplier page for (supplier name and supplier number) using concatenate.

To avoid the duplicates for supplier in EBS

Example: Supplier Name: Bala Krishna

                 Supplier Number:10090034

They need to default the alias like (Bala Kirshna_10090034)

Note: (“_”) Underscore is used to concatenate the supplier name and supplier number.

 

To Populate the Alias Value Using Sandbox Personalization:

Step 1: Create the Sandbox and select page composer from tools and enter supplier page with edit mode.

Step2: Click on Edit Pages

 

 

Step 3: Select Structure at top of supplier page.

Step 4: Right click on Alias fields as highlighted. 

Step 5: Right click on Value select Expression Builder as highlighted.

Step 6: Write the Expression to concatenate the Supplier Name and Supplier Number.

Step 7: Expression for Alias #{bindings.SupplierName.inputValue}_#{bindings.Segment1.inputValue}

 Click on Test and Ok

 

Step 8: After that Apply it and click on Ok.

Step 9: Save changes and submit it.

Step 10: Publish the Sandbox once the Modification is done.

 

Step 11: The alias value got defaulted in supplier page.

Schedule OIC integration Using ESS Job/BIP Report

 Is it possible to Schedule OIC integration Using ESS Job/BIP Report ?  Yes