Monday, 8 September 2025

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

Tuesday, 2 September 2025

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;