Saturday, December 4, 2021

Use case: Create the integration which records the files entry in ATP Database, record errors in ATP Database, send an error email notification using lookups and moves files to archive folder using Oracle Integration Cloud Service – OIC.

 Let us start !

  • Create lookups for common values

Navigation:

Integration --> Lookups --> Create 


New page will appear to create the required lookup. First of all we will create common lookups to define sender's email and receiver's email. 


Now we will create lookups other lookups which we will use in this integration

  • Create Integration using Schedule Orchestration
Navigation:

 Integration --> Create 


Click Select Button and New page will appear. Fill the required details and select the package as container and press button Create. 


  • Download Wallet.zip file
Navigation:
 
Oracle Database --> Autonomous Database.         


Click on ATPDB hyper link --> DB Connection --> Download wallet

  • Create SQL Developer Connection
Open SQL Developer --> Click on Plus icon 

Fill the required details in connection page, browse wallet.zip, press button to test and connect the ATP database.

  • Create schema in ATP Database if it does not exist.

Run below script to create an schema 

create user test identified by "Shahani@0312";
  • Grant privileges to the schema
grant create session to test;
grant create table to test;
grant create sequence to test;
alter user test quota 100M on USERS;
  • Create required objects
  CREATE TABLE "XXC_ERR_TBL" 
   ( "FILE_ID" NUMBER, 
"FLOW_ID" NUMBER,
"SOURCE_FILE_NAME" VARCHAR2(200 BYTE), 
"ERR_CODE" VARCHAR2(500 BYTE), 
"ERR_MSG" VARCHAR2(1000 BYTE), 
"CREATION_DATE" DATE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

  CREATE TABLE "XXC_FILE_ENTRY_TBL" 
   ( "LOAD_FLOW_ID" NUMBER, 
"FILE_ID" NUMBER, 
"FILE_NAME" VARCHAR2(1000 BYTE),  
"CREATEION_DATE" DATE, 
PRIMARY KEY ("FILE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
  
  create sequence XXC_FILE_ENTRY_seq;
  create sequence XXC_ERR_seq;
  • Add Assign action for global variables
Navigation:

Action --> Data --> Assign and Drag and Drop it on the integration. 

Enter the Global Variable name and press button Create

After pressing Create button new popup will open and assign variables to the integration.


  • Create Database connection
Navigation:

Integrations --> Connection --> Click button Create --> pop up will display, search for Oracle Database adapter, click on adapter and Press Button select.


Once you will press select button new pop up will display, upload wallet.zip file, enter wallet password, database service user such as ADMIN and database service password. Press button Test and if test is successful then press Button Save

  • Create FTP connection
Navigation:

Integrations --> Connection --> Click button Create, popup will display, search for FTP adapter. Select FTP adapter and press button Select.

 

Once you will press select button new pop up will display, enter the required details. Press button Test and if test is successful then press Button Save.

Let us to back to our integration and complete our integration since all the required connections have been created.
  • Add FTP connection for listing the files and perform mapping
In order to get list the file we need to drag and drop the FTP connection

Navigation:

Invokes --> FTP -->  FTP Connection

New popup will display and follow the steps in below listed snapshots.




Now we will map the source and target as

Navigation:

Click on --> Map to listFiles

New popup will appear

Expand --> listFiles Request (FTP)

Expand -->ListRequest --> FTPListHeader

Right click on fileNamePatter--> select menu -->Create Target Node 

 Write the Expression for fileNamePattern using lookupValue

dvm:lookupValue ("tenant/resources/dvms/FixedVal", "IntegrationCode", $varIntegrationCode, "fileNamePattern", "" ) 

Same way we will write expression for directory

dvm:lookupValue ("tenant/resources/dvms/FixedVal", "IntegrationCode", $varIntegrationCode, "inputDir", "" )            

 Press button Validate and close

  • Add For-Each loop
Navigation:

 Action --> Collection --> For-Each -->Drag and Drop it on the integration.

New popup page will display fill the required info as required.

Click button Create and it will display as below

  • Add ATPDB connection to get sequence value
In order to get sequence value we need to invoke the ATPDB connection

Navigation:

Invokes --> Oracle ATP -->  ATPDB

 Drag and drop the ATPDB connection, new popup will display and fill the details





Remove mapper as it is not required.
 

  • Add ATPDB connection to record the file entry and perform mapping.
In order to perform insert operation we need to invoke the ATPDB connection

Navigation:

Invokes --> Oracle ATP -->  ATPDB

 

Drag and drop the ATPDB connection, new popup will display and fill the details






 Now it is time to map the source and target using mapper

Click on Mapper --> Edit  and map the fields as 
  

Source

Target

getfileldfromseq Response à nextVal

fileId

Toggle Functions àSearch à getflowid

loadflowId

CurrentFilesàFileàfilename

fileName

Toggle Functions àSearch à current-date

Creation

  • Add connection to read files and perform mapping
In order to read the file/s we need to drag and drop the FTP connection

Navigation:

Invokes --> FTP -->  FTP Connection

New popup will display and follow the steps in below listed snapshots.



Now we will map the source and target as

Navigation:

Click on --> Map to readFiles

  • Add connection to write files and perform mapping
In order to write the file/s we need to drag and drop the FTP connection
Navigation:

Invokes --> FTP -->  FTP Connection
 

New popup will display and follow the steps in below listed snapshots.





Now we will map the source and target as

Navigation:

Click on --> Map to writeFiles


  • Add connection to archive/ move files and perform mapping
In order to write the file/s we need to drag and drop the FTP connection
Navigation:

Invokes --> FTP -->  FTP Connection

 New popup will display and follow the steps in below listed snapshots.




 Now we will map the source and target as

Navigation:

Click on --> Map to moveFiles

  • Add Scope
Navigation:

Action --> Data --> Assign and Drag and Drop it on the integration.

Enter the Scope name and press button Create

  •  Add Fault Handler

 Click on the Fault Handler Button and again click on Fault Handler. New Pop Will display

  • Add ATPDB to get sequence value
In order to get sequence value we need to invoke the ATPDB connection

Navigation:

Invokes --> Oracle ATP -->  ATPDB

 Drag and drop the ATPDB connection, new popup will display and fill the details






Remove mapper as it is not required.

  • Add ATPDB to record error in database and perform mapping
In order to perform insert operation we need to invoke the ATPDB connection

Navigation:

Invokes --> Oracle ATP -->  ATPDB

Drag and drop the ATPDB connection, new popup will display and fill the details









Now it is time to map the source and target using mapper

Click on Mapper --> Edit  and map the fields as 
 

Source

Target

Geterrseqval Response à nextVal

fileId

Toggle Functions àSearch à getflowid

flowId

 

SourceFileName

errorScopeFaultObjectàFaultàerrorCode

errCode

errorScopeFaultObjectàFaultàreason

errMsg

Toggle Functions àSearch à current-date

Creation

  • Use Notification Action to send email notification
Navigation:

 Action --> General --> Notification -->Drag and Drop it on the integration.

New popup page will display fill the required info as required.


 New popup page will display fill the required info as required.

  • Add Tracking
Navigation:

Action --> Tracking 

  • Save and activate your integration
  • Monitor activity stream
Happy Learning!