Friday, 31 December 2021

Oracle Fusion ERP - Update Event Subscriber URL



HTTP Method: PUT

Sample Input:


"id": 102,

"name": "{}EssJobRequest",

"filter": "/ns2:a/ns2:b < 200",

"endpointURL": "",

"state" : true


Wednesday, 8 December 2021

Oracle Fusion - Callback using Event Subscription

When we design a callback integration using ERP Adapter, we can select the Receive Callback Message upon completion of FBDI bulk import job submitted via another integration option on the Request page.

After selecting this option, we must select the specific bulk data import process for which the callback event is received. When this callback integration is activated, event subscription automatically occurs.

Once the import job completes, the event is raised by the Oracle ERP Cloud application. The Oracle ERP Cloud application then invokes the Oracle Integration endpoint that is the callback integration.

Since it follows event subscription mechanism , we need to configure CSF key in ERP to invoke the integration.

In order to generate the CSF key , we need to capture identity domain details. These details will be available in OCI console. 

  • Login into OIC console.
  • Click on the user icon on the right corner. 
  • Click on about
  • Get the Service Instance Name - vijaya-dvp-oic
  • Get the Identity Domain Details - idcs-xxxxxxxx62941exxxxxxxvvvvvv
  • CSF-KEY = idcs-xxxxxxxx62941exxxxxxxvvvvvvvijaya-dvp-oic

  • Login into SaaS page using following soa composer URL.
  • Click on the Manage Security from right side of the page and enter value as below and click on the Register button
    • csf-key: Enter the csf-key which we created above
    • Username: Enter OIC username
    • Password: Enter OIC password
    • Confirm Password: Enter OIC password

Thursday, 2 December 2021

Oracle API Platform Cloud Service - Groovy Script to Check Payload and SOAP Header

Steps to Get Payload Data: 

def apiRequestBody = context.ApiRequest.getBody().asString()

def soapEnv = new XmlSlurper().parseText(apiRequestBody)

def clientSystem = soapEnv['Header']['XXHeader']['XXSystem'].toString()

if ( clientSystem != 'SNV-Vijaya' )


  throw new oracle.apiplatform.policies.sdk.exceptions.PolicyProcessingException('Forbidden', 403, 'Access Denied')


def sourceEnv = soapEnv['Header']['XXHeader']['XXEnvironment'].toString()

def corrId = soapEnv['Header']['XXHeader']['XXId'].toString()

Set Custom Header Values: 

context.ServiceRequest.setHeader("x-intg-source-system", clientSystem )

context.ServiceRequest.setHeader("x-intg-source-env", sourceEnv)

context.ServiceRequest.setHeader("x-intg-corrid", corrId)

Sample Payload : 

<soapenv:Envelope  xmlns:soapenv="" xmlns:xxs="">

      <xxintegration:XXHeader xmlns:xxintegration="">

Unix - Command Execution Status Check


mvn deploy


if [ $STATUS -eq 0 ]; then

echo "Deployment Successful"


echo "Deployment Failed"


Tuesday, 16 November 2021

Weblogic - Enable Two Way SSL

Weblogic SSL Implementation:

Basic Terminologies :

SSL Termination: You can terminate SSL at Load Balancer and communication from Load Balancer to WebLogic Server as non SSL (In this case Load Balancer is configured to listen on SSL but WebLogic Server is configured to listen on NON SSL)

Identity/Keystore: A server which hosts traffic on a port which has SSL/TLS enabled, has an identity keystore. This identity keystore contains a private key and a public key/certificate. The public key/certificate can safely be given to other parties. With websites when visiting an HTTPS website (HTTP with SSL enabled), the public key is send to you. The other party / client can use the public key to encrypt messages meant for the server. The only one who can decrypt the messages is the one having the private key of the server. This is usually only the server.

Trust : Can you trust a server? You can use a certificate authority - CA to create a signed public key. If someone trust the certificate authority, that someone also automatically trusts the signed key. With websites you often see a green lock when a certain website uses HTTPS with a public certificate signed by a (by your we browser) trusted certificate authority.

Usually a trust store is used to store trusted certificate authorities -CA or specific trusted certificates. If you have many servers in your application landscape, it is recommended to use a certificate authority since it is cumbersome to load every public key of every server in every trust store. Trusting a single certificate authority makes things a lot easier.

If you are accessing Public Servers (Example Microsoft , Google etc.) then server will verify these certificates with Public CA and trust the connections automatically.  In case if you are accessing non Public Servers (Internal Servers) then we need to add the public certificates of these Servers into Trust store to Trust the Server and Connection. 

Default JRE cacerts (%JAVA_HOME%\jre\lib\security\cacertswill contain collection of trusted CA certificates and trust the connection. We don't need to add CA certificates explicitly 

We can also create a separate Keystore to store Identity and Trust Keys. 

SSL Connection - One Way:  Anytime when a client makes a SSL connection request to Server/Origin, Server will present the Key stored in Keystore/Identity Store.  Client will verify the presented Key by comparing it with the keys stored in Trust Store. 

SSL Connection - Two WayAnytime when a client makes a SSL connection request to Server/Origin, Server will present the Key stored in Keystore/Identity Store.  Client will verify the presented Key by comparing it with the keys stored in Trust Store. In two way SSL client also has to authenticate itself and it has to present the Key stored in Keystore and Server will validate the Key with the keys stored in Server Trust Store. 

Keystore Setup: 

Create Identity Key

keytool -genkey -keystore <keystore_name>.jks -alias <privatekey_alias_name> -keyalg RSA -validity <validity in days> -keysize 2048

Note down the password. 

Create CSR Request:

keytool -certreq -alias <privatekey_alias_name> -keystore <keystore_name>.jks -file <csr_file_name>.csr

Send the CSR request file to signing authority.  Get the signed certificate and import the certificates into identity key. 

Import Signed Certificate and Root Certificate. 

keytool -import -alias signedCer -keystore <keystore_name>.jks -file <signed_certificate>.crt

keytool -import -trustcacerts -alias root -file <root_certificate>.cer -keystore <keystore_name>.jks

Export Public Certificate From Keystore. 

keytool -export -alias <privatekey_alias_name> -keystore <keystore_name>.jks  -file <pub_cert_name>.cer

Create Trust Store Key and Import Client Cert:

keytool -import -alias <alias_name> -trustcacerts -file  <client_certificate> -keystore <trust_store_file_name>.jks

Note down the password. 

Weblogic Server Configurations:

Servers --> Managed Server   --> Kestore --> (Change Keystore Type to Custom Identity and Custom Trust)

Go to SSL Tab --> Provide Private Key Alias and Pass Phrase. 

Select Advanced --> Two Way Client Cert Behavior: ---> Client Certs Requested And Enforced

NoteSelecting Client Certs Requested But Not Enforced enables two-way SSL. With this option, the server requests a certificate from the client, but the connection continues if the client does not present a certificate. 

Selecting Client Certs Requested And Enforced also enables two-way SSL and requires a client to present a certificate. However, if a certificate is not presented, the SSL connection is terminated.

Reference :

How WebLogic Server Locates Trust

  • If the keystore is specified by the command-line argument, load the trusted CA certificates from that keystore.
  • Else if the keystore is specified in the configuration file (config.xml), load trusted CA certificates from the specified keystore. If the server is configured with DemoTrust, trusted CA certificates will be loaded from the WL_HOME\server\lib\DemoTrust.jks and the JDK cacerts keystores.
  • Else if the trusted CA file is specified in the configuration file (config.xml), load trusted CA certificates from that file (this is only for compatibility with 6.x SSL configurations).
  • Else load trusted CA certificates from WL_HOME\server\lib\cacerts keystore.

Saturday, 3 July 2021

Oracle Fusion - User and Role Sync from Fusion to IDCS

Refer my previous blogs for configuring SSO and OAuth authentication between IDCS and Fusion applications. 

In this blog , I will explain how to sync Users (Can be used to migrate Existing Users from Fusion to IDCS) and Roles from Fusion. 

Note:  As I explained in my earlier blogs, user can be originated in Fusion or IDCS. In case users are getting created in SaaS and then it requires to Sync with IDCS then we can use the following Sync configurations.  This configuration can also be used as a pre cutover activity to Sync an existing users from fusion and then use IDCS as an user Origin.  Once the Sync is done , we can disable this configuration. 

As we all know , roles will be originated only in Fusion and these roles can be synched to IDCS to create corresponding roles in IDCS.  

Pre Requisite : 
  • Create an IDCS client application with User Admin roles. 
  • Login to IDCS
  • Go to Applications
  • Create Confidential Application.
  • Provide Name as - Fusion IDCS Application. 
  • Next Page - Select configure this application as Client Now.
    • Select Allowed Grant Type - Client Credentials
    • Client Type - Confidential
    • Under Grants 
      • Select User Administrator role.
    • Select Next and Finish
    • Save 
    • Activate.
  • Note down the client id and secret. 

Oracle Fusion Setup:
  • Login to Fusion Application. Use Admin User
  • Go to Setup and Maintenance
  • Select Tasks
  • Select Manage Setup Content
    • Under Topology Definition 
    • Select Manage Integration of Additional Applications
    • Select Create ( + ) 
    • Create Application Integration page will open.
      • Application Name - IDCS_REST_APP
      • Full URL : https://<IDCS-HOST-NAME>/admin/v1
      • Partner Name : IDCS
      • Security Policy : Select - oracle/wss_username_token_over_ssl_client_policy
      • User Name : Enter the Client Id 
      • Password :  Client Id Secret. 
        • Refer Above created IDCS applications for Client Id and Secret. 
      • Apply 
      • Save and Close.

  • Create Task Lists and Tasks:
    • Go to Setup and Maintenance
    • Select Tasks
    • Select Manage Setup Content
      • Under Functional Definition
        • Select Manage Task Lists and Tasks
        • Select Create Task
        • Provide following details - You must use the same values. These values are seeded values. 
        • Click Save, but don’t close yet.
        • To save the Oracle Identity Cloud Service access credentials in the Oracle Fusion Applications Cloud Service credential store, click Test Go to Task.
        • In the Fusion Applications IDCS Sync App Credentials dialog, 
        • enter the Oracle Identity Cloud Service Admin console in the URL in the URL field. 
        • Enter the client ID of the Oracle Identity Cloud Service Application as the user name. 
        • Enter the secret key of the Oracle Identity Cloud Service application as the password.

Field Name

Field Value


Fusion Applications IDCS Sync App Credentials




Fusion Applications IDCS Sync App Credentials

Deployment Method


Program Name


Perform Task

After Import

Enterprise Application






Task Type

Data Entry

Uses user interface


Open In

Standard view


We can update the Sync profile to control the batch process. Use the following steps to update the profile options. 

Modify Profile Options for User Sync. 

  • Go to Setup and Maintenance and search for task: Manage Administrator Profile Values
  • Edit the below Profile options at Site level
      • USER - To Sync Only User
      • ROLE - To Sync Only Roles
      • ALL -  To Sync Both User and Role. 
    • FND_USER_MIGRATION_FA_FEDERATION=True  -- Default value is True
Schedule User Or Role Sync Job
  • Execute the Scheduled Process: 
  • User identity synchronization from this SaaS instance to the PaaS Identity Store
  • Schedule this Job to Run Every day

Add Specific Roles to be Synched to IDCS
  • From the Setup and Maintenance panel of your service, search for the task 
  • Migrate Enterprise Roles and Assignments to PaaS Identity.
  • Add all the Roles to be synchronized in this table

Wednesday, 23 June 2021

ORACLE - FUSION : Common Lookup DFF Configuration.

 Following steps will let you know how to configure DFF's for common lookups and use them while configuring common lookups. 

Normal common lookups will let you configure lookup code and corresponding meaning.  If you want to configure additional values for a lookup code then you can use DFF's to configure context and use them in lookups. 


  • Login into Fusion. 
  • Go to Manage and Setup 
  • Search for Manage Descriptive Flexfields
  • Search for Lookup values descriptive flexfield (Name)
  • Edit - Lookup values descriptive flexfield
  • Manage Contexts
  • Search for existing contexts or Create a new one. 
  • Create
    • Give a display name  - XXSCM_SHOP_ORG_MAP_LOOKUP
    • API Name - XxscmShopOrgMapLookup
  • Save
  • Under Context Sensitive Segments add segments - ( additional lookup values )
    • Give a Name - Ex: Inventory_Organization
    • Data Type as Character
    • Table Column will auto populated - Attribute1
    • Under Value Set : Either select an existing one or create new
    • Create a New Value Set
      • Value Set Code - XXSCM_INV_ORG_NAME
      • Module - Inventory Management
      • Validation Type : Table   -- This will allow us to define table, column and where clause. 
      •  Under Definition 
        • From Clause - Provide Table Name - INV_ORGANIZATION_DEFINITIONS_V
        • Value Column Name - Provide Column Name - ORGANIZATION_CODE
        • ID Column Name :  ORGANIZATION_ID
        • WHERE Clause :  INVENTORY_ENABLED_FLAG = 'Y'
      • Save 
        • This will validate TABLE and Column Values. 
      • Save and Close the value set
      • Save and Close the Segment
    • Follow the above steps to create a new segment for inventory.
    • Save and Close the Context

  • Go to Manage and Setup 
  • Search for Manage Common Lookups
  • Add Common Lookup +
  • Meaning : Give some valid meaning 
  • Description : Give some value Description
  • Module : Common
  • REST Access Secured :  Authenticated. 
    • Lookup Code
      • Add 
        • Lookup Code : Shop1
        • Meaning : Give a Name
        • Enable
        • Expand the lookup code
          • Select the context created earlier
          • Select the Org 
          • Select the Inventory

Sunday, 6 June 2021

Oracle OIC - ERP Adapter Filter Business Events

We can use ERP adapter to consume Business Events from Fusion and process them. We can use the ERP adapter at trigger point and select Business Events Operations and select the particular business event. 

Example : Invoice Hold Applied. 

This will create a subscriber entry in Fusion.  We can verify subscriber details using following URL. This will ask for credentials and need to provide valid credentials.

All events are triggered from Fusion.  Fusion will use the subscriber details and invoke integrations.  

Sample Invoice Hold Payload:

<ns01:onEvent xmlns:ns01="">   <ns0:ApInvHoldAppliedInfo xmlns:ns0="">      <ns0:InvoiceId>         <ns0:newValue value="XXXXXXX00000001"/>         <ns0:oldValue/>      </ns0:InvoiceId>      <ns0:OrgId>         <ns0:newValue value="XXXXXXX00000002"/>         <ns0:oldValue/>      </ns0:OrgId>      <ns0:HoldId>         <ns0:newValue value="72011"/>         <ns0:oldValue/>      </ns0:HoldId>      <ns0:HoldLookupCode>         <ns0:newValue value="AMT REC"/>         <ns0:oldValue/>      </ns0:HoldLookupCode>      <ns0:HoldReason>         <ns0:newValue value="Amount billed exceeds amount received."/>         <ns0:oldValue/>      </ns0:HoldReason>      <ns0:LineNumber>         <ns0:newValue value=""/>         <ns0:oldValue/>      </ns0:LineNumber>      <ns0:HeldBy>         <ns0:newValue value="5"/>         <ns0:oldValue/>      </ns0:HeldBy>      <ns0:HoldDate>         <ns0:newValue value="2021-06-04T06:27:33"/>         <ns0:oldValue/>      </ns0:HoldDate>      <ns0:LineLocationId>         <ns0:newValue value="XXXXXXX00000003"/>         <ns0:oldValue/>      </ns0:LineLocationId>      <ns0:RcvTransactionId>         <ns0:newValue value=""/>         <ns0:oldValue/>      </ns0:RcvTransactionId>   </ns0:ApInvHoldAppliedInfo></ns01:onEvent>

Use the following Filter Expression to filter a specific Hold. 

<xpathExpr xmlns:ns0="">$eventPayload/ns0:HoldLookupCode/ns0:newValue/@value='AMT REC'</xpathExpr>

$eventPayload: Inbuilt variable which will contain the payload. 

Friday, 21 May 2021

Oracle OIC - ERP Adapter with OAuth Authentication

Oracle has introduced an OAuth authentication mechanism to access ERP application using ERP adapter in OIC. 

This will solve the following security issues. 

  1. Can maintain user credentials in IDCS. 
  2. Wont require to reset the passwords during P2T refresh. 
  3. Better security compared to basic authentication. 
  4. Authentication will work even password will get expired in IDCS or Fusion. 

  • Create an ERP Enterprise resource application. 


  • Create a confidential application 
    • Select Configure as a client application
    •  Select appropriate Grant Types
    • Provide callback URL 
      • https://<OIC_HOST_NAME>/icsapis/agent/oauth/callback
    • Select Client Type as Trusted if required and import SaaS certificate 
    • Add Scope
      • Select ERP Enterprise application which we created earlier. 
      • Select the scope
    • Save changes
    • Activate the application
    • Collect Client Id and Secret. 
  • OIC Configurations:
    • Login into OIC using admin or developer access.
      • Make sure this user has got an access to Oracle Fusion as well. 
    • Create ERP Adapter with Invoke Operation. 
      • Provide SaaS URL
      • Select Authentication type as OAuth.
        • Provide Client Id and Secret which we got above. 
        • Provide Authorization  and Token URL.
          • https://idcs-<Id>
          • https://idcs-<Id>
        • Provide Scope Value (Get it from IDCS client application.)
        • Add offline_access to the scope. 
          • https://<SaaS_Host_Name> offline_access
      • Select Provide Consent.
        • Provide IDCS user credentials. 
      • Save and Test the connection.

Wednesday, 19 May 2021


 We can IDCS search API to get the required attributes and limited user list using pagination logic. 

Use the following API details to fetch the data from IDCS. 

URL: https://idcs-<id>

Operation : POST

IDCS Scope : urn:opc:idm:__myscopes__

Sample Request Payload:


  "schemas" : [ "urn:ietf:params:scim:api:messages:2.0:SearchRequest" ],

  "attributes" : [ "displayName", "userName", "emails", "active", "urn:ietf:params:scim:schemas:extension:enterprise:2.0:User:employeeNumber" ],

  "filter" : "((urn:ietf:params:scim:schemas:extension:enterprise:2.0:User:employeeNumber pr))",

  "startIndex" : 1,

  "count" : 100


OIC Expression  for Filter: 

filter :   conct("meta.lastModified ge ", '"', $varStartDate, '"', " and meta.lastModified le ", '"', $varEndDate, '"')

Tuesday, 11 May 2021

ORACLE OCI - Object Storage Multi Part Upload

In some cases we may have to upload larger file and uploading larger file may take more time and bandwidth.  In such cases we can split the file into multi parts and upload them in parallel. 

We can use the following steps to split and upload the files to object storage. 

  • First split the file based on the required size using split command. 
    • split -b 5M -d /tmp/bigfilename.txt /tmp/bigfilename.split
  • Create a multi part upload request. 
    • POST
    • /n/{namespaceName}/b/{bucketName}/u
    • Request Payload

  "object": "example_object1"

    • Response Payload 


  "namespace": "ansh8lvru1zp",
  "bucket": "MyBucket",
  "object": "MyObject1",
  "uploadId": "c892336f-ccvb-1bb8-6e75-a5649fd91178"
            •  Use the above upload Id to upload the files. 
            • Loop over each file and upload the files 
              • PUT
              • /n/{namespaceName}/b/{bucketName}/u/{FinalFileName}?uploadid={UploadID}&uploadPartNum={partNum/SequenceNum}
            • After Upload , get all the multi part upload details. 
              • GET
              • /n/{namespaceName}/b/{bucketName}/u/{FinalFileName}?uploadid={UploadID}
              • Sample Response
                "partNumber": 1,
                "etag": "3d240a5a-a2b0-45b2-bcvb-2ac6a02b422c",
                "md5": "rvr3UC1SmUw7cvb2NqPN0g==",
                "size": 8
                "partNumber": 2,
                "etag": "15de104e-7cvb-3513-8da1-3b5e75a65ad7",
                "md5": "3poFVtJezCVBOi8RzhUB8Q==",
                "size": 8

            • Finally commit the upload. 
              • POST
              • /n/{namespaceName}/b/{bucketName}/u/{FinalFileName}?uploadid={UploadID}
              • Request Payload
              "partsToCommit": [
                  "partNum": 1,
                  "etag": "3d240a5a-a2b0-45b2-bcvb-2ac6a02b422c"
                  "partNum": 2,
                  "etag" : "15de104e-7cvb-3513-8da1-3b5e75a65ad7"

                                                                Monday, 10 May 2021

                                                                ORACLE IDCS - OAUTH2 - Get Token

                                                                Use the following details to get the OAuth Token from IDCS and invoke the service. 

                                                                Authentication URL:

                                                                Authentication Token URL:

                                                                Scope:  From IDCS application. 

                                                                Friday, 2 April 2021

                                                                Python - Generate SSL Certificate and Invoke SOAP/REST Using SSL certificate.

                                                                Steps to generate Keystore , CSR and Import Signed Certificates. 

                                                                • Login into Unix Box 
                                                                • Go to Java Home Dir
                                                                  • cd %JAVA_HOME%/jre/bin
                                                                • Run the Keytool Command to Create Kestore - JKS
                                                                  • keytool -genkey -keyalg RSA -alias <aliasname> -keystore identityKeystore.jks -storepass <replace_with_strong_password> -validity 900 -keysize 2048 
                                                                  • <aliasName>  = Meaningfull Alias
                                                                  • <replace_with_strong_password>  = Ketstore Password
                                                                  • When prompted, change the values provided based on your company's security policy

                                                                What is your first and last name?
                                                                  [Unknown]:  VijayaKumar
                                                                What is the name of your organizational unit?
                                                                  [Unknown]:  Development
                                                                What is the name of your organization?
                                                                  [Unknown]:  ICS
                                                                What is the name of your City or Locality?
                                                                  [Unknown]:  Bangalore
                                                                What is the name of your State or Province?
                                                                  [Unknown]:  KA
                                                                What is the two-letter country code for this unit?
                                                                  [Unknown]:  IN
                                                                Is CN=<>, OU=<>, O=<>, L=Redwood Shores, ST=California, C=US correct?
                                                                  [no]:  yes 
                                                                Enter key password for <aliasName>
                                                                        (RETURN if same as keystore password):


                                                                • List the file generated using ls command. Now you should see jks file
                                                                  • ls
                                                                • Generate CSR file for signing authority. 
                                                                  • keytool -certreq -alias <aliasName> -keystore identityKeystore.jks -storepass <replace_with_strong_password>  -storetype JKS -file icsclient.csr
                                                                • List the files generated using ls command.  Now you should see both jks and csr file. 
                                                                  • ls
                                                                • Share the CSR file with signing authority.  Or generate one using following URL.
                                                                • In case if you receive the root and intermediate certificates from signing authrity us the followin commands to Import Root Certificates: 
                                                                  • keytool -import -keystore identityKeystore.jks -file <root_certificate_CA>.crt -alias DigiCertCARoot
                                                                • Import Intermediate Certificates : 
                                                                  • keytool -import -keystore identityKeystore.jks -file <intermediate_certificate_CA>.crt -alias DigiCertCAInter
                                                                  • keytool -import -keystore identityKeystore.jks -file <my_company_signedcert.crt/pem> -alias icslientcert
                                                                • Use the following command to generate Client or Server certificate using Keystore. 
                                                                  • keytool -export -alias <aliasName> -storepass <replace_with_strong_password> -keystore identityKeystore.jks -file  icsclient.cer/pem 
                                                                  • keytool -export -alias <aliasName> -storepass <replace_with_strong_password> -keystore identityKeystore.jks -file  icsclient.cer/pem
                                                                • List all the certificates from JKS file. 
                                                                  • keytool -list -keystore identityKeystore.jks

                                                                Python Code : 

                                                                headers = {'SOAPAction': '<wsdl_action>', 'Content-Type': 'text/text; charset=utf-8'}

                                                      'https://vijayakumarkv/ics/', headers=headers,data=body.encode('utf-8'),cert=('/path/client.cert', '/path/client.key'))

                                                                Thursday, 18 March 2021

                                                                DATABASE - DROP OBJECTS SCRIPT


                                                                for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name as stmt

                                                                from all_objects

                                                                where object_type in ('PACKAGE') and owner = 'SCHEMA_OWNER') loop

                                                                execute immediate i.stmt;

                                                                end loop;




                                                                for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name || ' Cascade constraints purge' as stmt

                                                                from all_objects

                                                                where object_type in ('TABLE') and owner = 'SCHEMA_OWNER') loop

                                                                execute immediate i.stmt;

                                                                end loop;




                                                                for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name || ' force' as stmt

                                                                from all_objects

                                                                where object_type in ('TYPE') and owner = 'SCHEMA_OWNER' ) loop

                                                                execute immediate i.stmt;

                                                                end loop;




                                                                for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name  as stmt

                                                                from all_objects

                                                                where object_type in ('SEQUENCE') and owner = 'SCHEMA_OWNER' ) loop

                                                                execute immediate i.stmt;

                                                                end loop;



                                                                Monday, 8 February 2021

                                                                ORACLE OIC - OCI Object Storage List and Download Files

                                                                We can use REST adapter to invoke OCI Object Storage API's to list the files under a given bucket and download the files. 

                                                                Following API will provide all the files with a specific prefix. 


                                                                Operation = GET.

                                                                Add a request parameter . 

                                                                Name = prefix 

                                                                Type = string. 

                                                                Note:  In the request mapping provide the values for tenancy_name , bucket_name and prefix elements. 

                                                                Use the following sample JSON response message to receive and parse the response message. 

                                                                Sample Response Message : 


                                                                  "objects" : [ {

                                                                    "name" : ""

                                                                  } ]


                                                                If there are files in object storage then response will return an array of file names. Use the loop activity to loop over each file and download the file from Object Storage. 

                                                                To download the file use the following rest URI and GET operation. 


                                                                While defining response type select Binary value this will auto select media type.  

                                                                Use the response and write a file using stage activity.  Use an opaque schema element to write a file. 

                                                                While mapping , user the reference element from OCI response and add reference encoding function and map it to write schema element. 

                                                                Saturday, 2 January 2021

                                                                OIC - New Features

                                                                Asserter to store the run results and can be used to replay the request. 

                                                                Oracle OIC agent framework uses OIC messaging channel , Oracle SaaS agent and one or more connectivity agents.

                                                                Agents polls for outbound invocations. It also support triggers configured with Agents. 

                                                                One Agent is enough to connect with all your on premise applications.  We dont need to have multiple agents for each connection. 

                                                                Agent group can contain multiple agents configured in different hosts in on premise server and they can act like HA server. 

                                                                ATP Connections : 

                                                                1. Login to OCI
                                                                2. Go to Autonomous Database
                                                                3. Select a  compartment
                                                                4. It will display the configured ATP.
                                                                5. Select the DB connection option on top
                                                                6. Download the Wallet 
                                                                7. Provide the Password while downloading the wallet. Zip file will get downloaded
                                                                8. Extract the zip file and you will find TNS file.  
                                                                9. TNS file will have an entry for each consumer type - HIGH , LOW , MEDIUM
                                                                10.  Get the required connection details from tns file
                                                                11. Select Authentication type as JDBC over SSL.
                                                                12. Updload the Wallet Zip file 
                                                                13. Provide Wallet password. 
                                                                14. Provide DB user name and password. 

                                                                Oracle Fusion - User Creation or Migration and Role Provisioning

                                                                Oracle Fusion Users can be originated in Fusion Application where in users are created in Fusion (Security Console) or can be provisioned from identity providers like IDCS to Fusion Application. 

                                                                In case users are originating at IDCS and then provisioned or synced with Fusion Application then it is required  to setup SSO application in IDCS and enable User Sync from IDCS to FA. 

                                                                It is required to configure Auto Role Provisioning to all the users and assign Employee role or Contingent worker role to User.  It will assign a default role to user when an User account is created in Fusion. 

                                                                In case users are not assigned with any role and by chance if you run a seeded job - "Send Pending LDAP Request"  or if it gets kicked off due to some other sync job like Role Assignment then it will remove the users which are not assigned with any roles.  

                                                                "Send Pending LDAP Request" job will perform following tasks. 

                                                                • Create , Suspend and Reactivate User Accounts. 
                                                                • User create will be triggered when Person record is created for a Worker. 
                                                                • User will be suspended when roles are removed from a User. 
                                                                • User will be Reactivated when a User will be rehired. 

                                                                Note: These jobs will triggered when automatic user creation and management is enabled in Fusion. 

                                                                It is not required to run this Job if the user is created manually or synced from IDCS.  It is required only if we need to create user automatically using Employee records. 

                                                                Enable Auto Creation and Management :  FA > Setup and Maintenance > Manager Enterprise HCM Information > Edit and Set > User Account Creation as - Both Person and Party User. 

                                                                Automatic Role Provisioning >  FA > Setup and Maintenance > Manager Enterprise HCM Information > Edit and Set > User Account Role Provisioning as - Both Person and Party User. 

                                                                Provision Roles to  Users Automatically. 
                                                                • Login into FA
                                                                • Setup and Maintenance
                                                                • Tasks
                                                                • Search > Manage Role Provisioning Rules / Manager Role Mapping
                                                                • + Create
                                                                • Give a Mapping Name - Employee Prov
                                                                • Update Following Fields.  This will assign the roles to Active Employee
                                                                  • System Person Type =  Employee
                                                                  • HR Assignment Status = Active.
                                                                • Under Associated role
                                                                  • + Add Row
                                                                  • Role Name >  Search for a Role - Employee Role
                                                                  • Add
                                                                  • Select Auto Provision Option. 
                                                                  • Save and Close. 
                                                                • Follow Above Steps for Contingent Worker.
                                                                  • Add Mapping Name - Contingent Work
                                                                    • System Person Type = Contingent Worker
                                                                    • HR Assignment Status = Active
                                                                  • Add Role 
                                                                  • Select Contingent Worker Role
                                                                  • Select Auto Provision Option. 
                                                                  • Save and Close. 

                                                                When you schedule "Send Pending LDAP Request" it will perform Auto Role Provisioning.