Exposing On-Premise Oracle Integrated SOA Gateway Services to Oracle Integration Cloud

In part 1 of this ‘ISG Series’ we looked at getting started with ISG, calling authentication services and a seeded ISG REST service. In the second part we explored how to expose a custom PL/SQL procedure as a REST service.

In this final blog of the ‘ISG Series’ of guides we bring together what we covered and see how, once we have configured both seeded and custom REST ISG services on our Oracle E-Business Suite instance, we can execute them as a series of services, orchestrating them in a cloud-based integration process hosted on Oracle Integration Cloud (Gen1).

I started to look into this as I wanted to play with some Oracle Cloud credits I’d been given.

It was one of my first attempts of creating a custom integration in the Oracle Integration Cloud. I also wanted to see how well the OIC Connectivity Agent worked, and to get a feel for how quickly I could bring together a very low code option to our customers, so that they could consider securely exposing REST services of an on-premise Oracle E-Business Suite, but also how we could orchestrate running a series of them in the Cloud as a REST service process.

In this scenario, we are going to execute some ISG REST services to:

  • login a user (seeded ISG authentication service),
  • obtain some extra details about them (custom service) and then
  • query back some payslip data (seeded payroll services).

The final process we will create will be exposed on the Oracle Integration Cloud as a REST service too. Perhaps this type of custom process could be used in an APEX, ADF or Visual Builder application.

This blog post is far from being a proper and mature exemplar. I have learnt so much about proper REST API service etiquette since doing this initial dive into ISG and REST services back in late 2019, so the main purpose of this post is only to give you, the reader, some knowledge and (hopefully) confidence to try something like this out yourself on your E-Business Suite 12.2.x instance.

Environment used

Oracle E-Business Suite Vision Install 12.2.8 – with ISG. Refer to the following blog: EBS 12.2.8 VM Virtual Appliance Now Available.

Follow the guide to help you get the Vision instance running.

Download and install Postman.

Oracle Integration Cloud – see Useful Notes at the end for setup details.

Assumptions

Your on-premise E-Business Suite Application or VM has already been patched to run the Integrated SOA Gateway (reference to Oracle Support Document 1311068.1).

You have provisioned an Oracle Integration Cloud instance – see Useful Notes at the end for setup details – and have a little experience of OIC, especially in creating Connections and basic Integrations.

You have configured an OIC Connectivity Agent – see Useful Notes at the end for setup details.

You have provisioned on the EBS instance a custom REST service EXTRA_USER_DETAILS (POST) (XXOIC_USER_PERSON_INFO) – see Useful Notes at the end of this blog for source code, and the companion blog post “Making use of Oracle Integrated SOA Gateway – Custom REST Services” for details on how to install the custom ISG service.

The short video shows you the high level path our final process flow will take.

We will expose a very simple REST service on OIC. This REST service process will then:

  • authenticate the user/employee (get an EBS session token)
  • call a custom service to get more information about the employee
  • call a seeded EBS ISG REST service to get a list of available payslips
  • call a seeded EBS ISG REST service to get GROSS payslip values for the requested month
  • call a seeded EBS ISG REST service to get DEDUCTION payslip values for the requested month
  • present back a JSON payload output back to the call.
ISG3 – High Level Process Flow

Create New REST Service Connections

As well as creating a custom ISG REST service EXTRA_USER_DETAILS (POST) (for obtaining additional information about the person), we are also going to use the following seeded ISG REST services that belong to the security and payroll schema.

  • EBS Login (get accessToken)
  • getPayslipListDetails
  • getPayslipDataDetails (GROSS and DEDUCTIONS)

To make these available to an Oracle Integration Cloud process (or any other process for that matter) we need to expose them via Connections first.

As we will make use of the OIC Connectivity Agent, it needs to be started before you continue (see Useful Notes at the end for details).

Create Connections in OIC

Login to OIC, then navigate (NAV) to:

NAV > OIC Home > Integrations > Connections

  1. Press the Create Button
  2. Search for and select the REST adapter 

3. Create a REST adapter connection as follows:

REST_EBS_Login Connection

Name:                         REST_EBS_Login

Identifier:                 [take default]

Role:                            Invoke

Description:             Obtain an accessToken from EBS ISG (Local)

Connection Type:   REST API Base URL

Connection URL:     http://[Your EBS REST Endpoint]/webservices/rest/login

Security Policy:       No Security Policy

Agent Group:           fujitsuyannov19 Agent Group

4. Test, Save and Close the Connection screen in OIC.


Repeat the above for the following connections too.

REST_EBS_postExtraPersonDetails Connnection

Name:                         REST_EBS_postExtraPersonDetails

Identifier:                [take default]

Role:                          Invoke

Description:             The REST service is a custom pl/sql procedure residing on the EBS ISG layer. This custom PL/SQL package will be executed to obtain user / person context information for use in REST calls.

Connection Type:    REST API Base URL

Connection URL:     http://%5BYour EBS REST Endpoint]/webservices/ user_person_info/extra_user_details

Security Policy:       No Security Policy

Agent Group:           fujitsuyannov19 Agent Group

REST_EBS_getPayslipListDetails Connection

Name:                        REST_EBS_getPayslipListDetails

Identifier:                [take default]

Role:                           Invoke

Description:            Gets a list of payslips for the last 12 months of the logged in user.

Connection Type:     REST API Base URL

Connection URL:      http://%5BYour EBS REST Endpoint]/webservices/ payslips/getPayslipListDetails

Security Policy:            No Security Policy

Agent Group:              fujitsuyannov19 Agent Group

REST_EBS_getPayslipData Connection

Name:                         REST_EBS_getPayslipData

Identifier:                     [take default]

Role:                            Invoke

Description:                  Gets the payslip details for a given action context ID and category.

Connection Type:        REST API Base URL

Connection URL:         http://%5BYour EBS REST Endpoint]/webservices/ payslips/getPayslipDataDetails

Security Policy:            No Security Policy

Agent Group:              fujitsuyannov19 Agent Group


Create a New Integration (App Driven Orchestration)

Note: unless otherwise stated all configurations use default settings.

Now the Connections are available they can be used in the orchestration.

NAV > OIC Home > Integrations > Create > ‘App Driven Orchestration’.

For reference on what the integration will look like, the first part of this integration looks like this.

payDetailsRequest – Trigger (REST)

Basic Info

What do you want to call your endpoint? payDetailsRequest

What does the endpoint do? Takes username, password, month and year and returns payslip data if available.

What is the endpoint’s relative resource URI? /payDetailsRequest

What action do you want to perform on the endpoint? POST

Configure a request payload for this endpoint? Yes            

Configure this endpoint to receive the response? Yes

Request

Select the request payload format – JSON Sample (inline)

Example:

{
"payDetailsRequest": {
"userName": "WDANIELS",
"password": "Fred4321",
"monthNum": "07",
"year": "2019"
}
}

Select the media type which you want the endpoint to receiveJSON

Response

Select the request payload formatJSON Sample (inline)

Example:

{
"payDetailsResponse": {
"userName": "WDANIELS",
"accessToken": "4bvQNlPOWOp413Ft9oqYLebzhY",
"accessTokenName": "EBSDB",
"ebsVersion": "12.2.8",
"monthNum": "07",
"year": "2019",
"EffectiveDate": "2019-07-29T00:00:00.000-04:00",
"PayslipChoice": "27 - Run 1",
"ActionContextId": "16136420",
"PersonId": "7453",
"Currency": "GBP",
"NetPay": "726.9",
"GBEarningsList": {
"GBEarnings": [
{
"TotalCurrentAmount": "945.29"
},
{
"ElementName": "Salary",
"CurrentAmount": "945.29"
}
]
},
"GBDeductionsList": {
"GBDeductions": [
{
"TotalCurrentAmount": "218.39"
},
{
"ElementName": "PAYE",
"CurrentAmount": "189.2"
},
{
"ElementName": "NI A",
"CurrentAmount": "29.19"
}
]
}
}
}

Select the media type which you want the endpoint to replyJSON


CreateAndInitializeVariables – Assign

VARIABLEDATA
TYPE
VALUE
AuthorizationStringstringconcat(‘basic ‘,oraext:encodebase64(
concat(/nssrcmpr:execute/
nsmpr2:request-wrapper/
nsmpr2:paydetailsrequest/
nsmpr2:username,
concat(‘:’,/nssrcmpr:execute/
nsmpr2:request-wrapper/
nsmpr2:paydetailsrequest/nsmpr2:password))))
ActionContextIdstring“0”
NetPaystring“0”

MAP to getLoginRequest

TARGETMAPPING
execute>HTTPHeaders>Authorization$AuthorizationString

getLoginRequest – Call to ISG REST Service

Basic Info

What do you want to call your endpoint? getLoginRequest

What does the endpoint do? Sends a login request to the EBS REST endpoint and gets an accessToken back.

What is the endpoint’s relative resource URI? /getLoginRequest

What action do you want to perform on the endpoint? GET

Configure a request payload for this endpoint No

 Configure this endpoint to receive the response Yes

Configure Request Headers? Yes

Request Headers

HTTP Header Name Authorization

Response

Select the request payload format XML Sample (Single or No Namepace)

Example (create in a local file ‘ISG_Login_Response.xml’):

<?xml version = '1.0' encoding = 'UTF-8'?>
 <data>
     <accessToken>uHtmATSoJFWlcOsipGyAE5cQxG</accessToken>
     <accessTokenName>EBSDB</accessTokenName>
     <ebsVersion>12.2.8</ebsVersion>
     <userName>WDANIELS</userName>
 </data>

Select the media type which you want the endpoint to reply XML


For reference on what the integration will look like, and following on from the first part, the second part of the process looks like this.

MAP to ObtainExtraUserDetails

TARGETMAPPING
execute>HTTPHeaders>
StandardHTTPHeaders>
Authorization
$AuthorizationString
request-wrapper>InputParameters>
P_USER_NAME
userName
equest-wrapper>InputParameters> P_RESPONSIBILITY_NAME“Employee Self Service V3”

ObtainExtraUserDetails – Call to ISG REST Service

Basic Info

What do you want to call your endpoint? ObtainExtraPersonDetails

What does the endpoint do? Obtain user / person context information for use in downstream REST calls.

What is the endpoint’s relative resource URI? /user_person_info/extra_user_details

What action do you want to perform on the endpoint? POST

Configure a request payload for this endpoint Yes

Configure this endpoint to receive the response Yes

Configure Request Headers? Yes

Request

Select the request payload format JSON Sample (inline)

Example:

{
 "InputParameters" : {
 "@xmlns" : "http://xmlns.oracle.com/apps/fnd/soaprovider/plsql/rest/xxoic_user_person_info_rest/",
                 "@xmlns:xsi" : "http://www.w3.org/2001/XMLSchema-instance",
                     "P_USER_NAME" : "WDANIELS",
                     "P_RESPONSIBILITY_NAME" : "Employee Self Service V3"
               }
 }

Select the media type which you want the endpoint to send JSON

Request Headers

HTTP Header Name Authorization

Response

Select the response payload format JSON Sample            

Example:

{
           "OutputParameters" : {
 "@xmlns" : "http://xmlns.oracle.com/apps/fnd/rest/user_person_info/extra_user_details/",
             "@xmlns:xsi" : "http://www.w3.org/2001/XMLSchema-instance",
                 "P_USER_NAME" : "WDANIELS",
 "P_RESPONSIBILITY_ID" : "22910",
                 "P_APPLICATION_ID" : "800",
                 "P_SECURITY_GROUP" : "0",
                 "P_PERSON_ID" : "7453",
                 "P_ORGANIZATION_ID" : "2788",
                 "P_BUSINESS_GROUP_ID" : "1668",
                 "P_LEGISLATION_CODE" : "GB",
                 "P_EXTRA_USER_DETAILS_1" : "0",
                 "P_EXTRA_USER_DETAILS_2" : "0",
                 "P_EXTRA_USER_DETAILS_3" : "0",
                 "P_EXTRA_USER_DETAILS_4" : "0",
                 "P_EXTRA_USER_DETAILS_5" : "0",
                 "P_EXTRA_USER_DETAILS_6" : "0",
                 "P_EXTRA_USER_DETAILS_7" : "0",
                 "P_EXTRA_USER_DETAILS_8" : "0",
                 "P_EXTRA_USER_DETAILS_9" : "0",
                 "P_EXTRA_USER_DETAILS_10" : "0",
                 "P_EXTRA_USER_DETAILS_11" : "0",
                 "P_EXTRA_USER_DETAILS_12" : "0",
                 "P_EXTRA_USER_DETAILS_13" : "0",
                 "P_EXTRA_USER_DETAILS_14" : "0",
                 "P_EXTRA_USER_DETAILS_15" : "0",
                 "P_EXTRA_USER_DETAILS_16" : "0",
                 "P_EXTRA_USER_DETAILS_17" : "0",
                 "P_EXTRA_USER_DETAILS_18" : "0",
                 "P_EXTRA_USER_DETAILS_19" : "0",
                 "P_EXTRA_USER_DETAILS_20" : "0",
                 "X_RETURN_STATUS" : "S",
                 "X_MSG_DATA" : null
               }
 }

Select the media type which you want the endpoint to reply JSON


MAP to getPayslipListDetails

TARGETMAPPING
execute>HTTPHeaders>
StandardHTTPHeaders>
Authorization
$AuthorizationString
request-wrapper>InputParameters>
personId
P_PERSON_ID
request-wrapper>InputParameters>
legCode
P_LEGISLATION_CODE
request-wrapper>InputParameters>
bgID
P_BUSINESS_GROUP_ID

getPayslipListDetails – Call to ISG REST Service

Basic Info

What do you want to call your endpoint? getPayslipListDetails

What does the endpoint do? Gets a list of payslips for the last 12 months of the logged in user.

What is the endpoint’s relative resource URI? /payslips/getPayslipListDetails

What action do you want to perform on the endpoint? POST

Configure a request payload for this endpoint Yes

Configure this endpoint to receive the response Yes

Configure Request Headers? Yes

Request

Select the request payload format JSON Sample (inline)            

Example:

{
           "InputParameters" : {
                 "personId" : "7453",
                  "legCode" : "GB",
                 "bgID" : "1668"
               }
 }

Select the media type which you want the endpoint to send JSON

Request Headers

HTTP Header Name Authorization

Response

Select the response payload format JSON Sample            

Example:

{
   "getPayslipListDetails_Output" : {
                 "OutputParameters" : {
                       "Output" : {
                         "PayslipListBean" : [ {
                           "EffectiveDate" : "2019-07-29T00:00:00.000-04:00",
                           "PayslipChoice" : "27 - Run 1",
                           "ActionContextId" : "16136420",
                           "PersonId" : "7453",
                           "Currency" : "GBP",
                           "NetPay" : "726.9"
                         }, {
                           "EffectiveDate" : "2019-06-28T00:00:00.000-04:00",
                           "PayslipChoice" : "27 - Run 1",
                           "ActionContextId" : "16136292",
                           "PersonId" : "7453",
                         "Currency" : "GBP",
                          "NetPay" : "727.1"
                         }, {
                           "EffectiveDate" : "2019-05-29T00:00:00.000-04:00",
                           "PayslipChoice" : "27 - Run 1",
                           "ActionContextId" : "16136164",
                           "PersonId" : "7453",
                           "Currency" : "GBP",
                           "NetPay" : "727.1"
                         } ]
                           },
                       "ControlBean" : {
                     "fields" : "",
                     "filter" : "",
                        "limit" : "",
                        "offset" : "",
                     "sort" : ""
                   }
                }   
   }
 }

Select the media type which you want the endpoint to reply JSON


For reference on what the integration will look like, and following on from the second part, the third part of the process looks like this.

The third part of the process contains a ForEach loop. Since there could be a number of potential payslips for an employee we will need to loop around the results returned from the call to getPayslipListDetails, and select the correct one for further processing.


Edit Action

Name ForEachPayslipListBean

Description Loop around the result of getPayslipListDetails in order to set the ActionContextId and NetPay values when the month and year of the input values match

Repeating Element $getPayslipListDetails/nssrcmpr:executeResponse/nsmpr10:response-wrapper/nsmpr10:getPayslipListDetails_Output/nsmpr10:OutputParameters/nsmpr10:Output/nsmpr10:PayslipListBean

Current Element Name PayslipRec


IF MatchPayslipListDetailDateToInputYearAndMonth

Match = All of

substring($PayslipRec/
nsmpr10:PayslipListBean/
nsmpr10:EffectiveDate, 1.0, 4.0)
=/nsmpr12:execute/
nsmpr14:request-wrapper/
nsmpr14:payDetailsRequest/
nsmpr14:year
substring($PayslipRec/
nsmpr10:PayslipListBean/
nsmpr10:EffectiveDate, 6.0, 2.0)
=/nsmpr12:execute/
nsmpr14:request-wrapper/
nsmpr14:payDetailsRequest/
nsmpr14:monthNum

Note that the first switch statement contains 2 routing rules: IF and Otherwise.

The IF is MatchPayslipListDetailDateToInputYearAndMonth which looks at each loop in the For Each record and compares elements of the EffectiveDate of the payslip with the arguments made in the initial REST execution.

The Otherwise route does not contain any actions.

The IF route will now be configured to contain the following actions:

setVars – ASSIGN

VARIABLEDATA
TYPE
VALUE
ActionContextIdstring$PayslipRec/nsmpr10:PayslipListBean/
nsmpr10:ActionContextId
NetPaystring$PayslipRec/nsmpr10:PayslipListBean/
nsmpr10:NetPay

MAP to getPayslipDataDetails_GROSS

TARGETMAPPING
execute>HTTPHeaders>
StandardHTTPHeaders>
Authorization
$AuthorizationString
request-wrapper>InputParameters>
personId
P_PERSON_ID
request-wrapper>InputParameters>
legCode
P_LEGISLATION_CODE
request-wrapper>InputParameters>
bgID
P_BUSINESS_GROUP_ID
request-wrapper>InputParameters>
category
“GROSS”
request-wrapper>InputParameters>
actContextID
$ActionContectId

getPayslipDataDetails_GROSS – Call to ISG REST Service

Basic Info

What do you want to call your endpoint? getPayslipDataDetails_GROSS

What does the endpoint do? Gets the payslip details for a given action context ID and category.

What is the endpoint’s relative resource URI? /payslips/getPayslipDataDetails

What action do you want to perform on the endpoint? POST

Configure a request payload for this endpoint Yes

Configure this endpoint to receive the response Yes

Configure Request Headers? Yes

Request

Select the request payload format JSON Sample (inline)            

Example:

{
           "InputParameters" : {
                 "personId" : "7453",
                 "legCode" : "GB",
                  "bgID" : "1668",
                 "category" : "GROSS",
                  "actContextId" : "16136420"
               }
 }

Select the media type which you want the endpoint to send JSON

Request Headers

HTTP Header Name Authorization

Response

Select the response payload format JSON Sample            

Example:

{
           "getPayslipDataDetails_Output" : {
                 "OutputParameters" : {
                     "Output" : {
                         "PayslipDataBean" : [ {
                               "GBEarningsList" : {
                                 "GBEarnings" : [ {
                                   "TotalCurrentAmount" : "945.29"
                                 }, {
                                   "ElementName" : "Salary",
                                   "CurrentAmount" : "945.29"
                                     } ]
                               }
                         } ]
                       },
                       "ControlBean" : {
                         "fields" : "",
                        "filter" : "",
                         "limit" : "",
                         "offset" : "",
                         "sort" : ""
                           }
                     }
               }
 }

Select the media type which you want the endpoint to reply JSON


MAP to getPayslipDataDetails_DEDUCTIONS

TARGETMAPPING
execute>HTTPHeaders>
StandardHTTPHeaders>Authorization
$AuthorizationString
request-wrapper>InputParameters>
personId
P_PERSON_ID
request-wrapper>InputParameters>
legCode
P_LEGISLATION_CODE
request-wrapper>InputParameters>
bgID
P_BUSINESS_GROUP_ID
request-wrapper>InputParameters>
category
DEDUCTIONS
request-wrapper>InputParameters>
actContextID
$ActionContectId

getPayslipDataDetails_DEDUCTIONS – Call to ISG REST Service

Basic Info

What do you want to call your endpoint? getPayslipDataDetails_DEDUCTIONS

What does the endpoint do? Gets the payslip details for a given action context ID and category.

What is the endpoint’s relative resource URI? /payslips/getPayslipDataDetails

What action do you want to perform on the endpoint? POST

Configure a request payload for this endpoint Yes

Configure this endpoint to receive the response? Yes

 Configure Request Headers? Yes

Request

Select the request payload format JSON Sample (inline)            

Example:

{
           "InputParameters" : {
                 "personId" : "7453",
                 "legCode" : "GB",
                  "bgID" : "1668",
                 "category" : "DEDUCTIONS",
                  "actContextId" : "16136420"
               }
 }

Select the media type which you want the endpoint to send JSON

Request Headers

HTTP Header Name Authorization

Response

Select the response payload format JSON Sample            

Example:

{
               "getPayslipDataDetails_Output" : {
                 "OutputParameters" : {
                   "Output" : {
                     "PayslipDataBean" : [ {
                       "GBDeductionsList" : {
                         "GBDeductions" : [ {
                           "TotalCurrentAmount" : "218.39"
                             }, {
                           "ElementName" : "PAYE",
                           "CurrentAmount" : "189.2"
                         }, {
                           "ElementName" : "NI A",
                           "CurrentAmount" : "29.19"
                         } ]
                       }
                     } ]
                   },
                   "ControlBean" : {
                     "fields" : "",
                     "filter" : "",
                     "limit" : "",
                     "offset" : "",
                     "sort" : ""
                   }
                  }
              }
 }

MAP to payDetailsRequest

TARGETMAPPING
executeResponse>response-wrapper>
payDetailsResponse>username
$getLoginRequest>executeResponse>
data> username
executeResponse>response-wrapper>
payDetailsResponse>accessToken
$getLoginRequest>executeResponse>
data> accessToken
executeResponse>response-wrapper>
payDetailsResponse>accessTokenName
$getLoginRequest>executeResponse>
data> accessTokenName
executeResponse>response-wrapper>
payDetailsResponse>ebsVersion
$getLoginRequest>executeResponse>
data> ebsVersion
executeResponse>response-wrapper>
payDetailsResponse>monthNum
substring($PayslipRec/nsmpr10:
PayslipListBean/nsmpr10:EffectiveDate,
6.0, 2.0)
executeResponse>response-wrapper>
payDetailsResponse>year
substring($PayslipRec/nsmpr10:
PayslipListBean/nsmpr10:EffectiveDate,
1.0, 4.0)
executeResponse>response-wrapper>
payDetailsResponse>EffectiveDate
$PayslipRec>PayslipListBean>
EffectiveDate
executeResponse>response-wrapper>
payDetailsResponse>PayslipChoice
$PayslipRec>PayslipListBean>
PayslipChoice
executeResponse>response-wrapper>
payDetailsResponse>ActionContextId
$PayslipRec>PayslipListBean>
ActionContextId
executeResponse>response-wrapper>
payDetailsResponse>PersonId
$PayslipRec>PayslipListBean>
PersonId
executeResponse>response-wrapper>
payDetailsResponse>Currency
$PayslipRec>PayslipListBean>
Currency
executeResponse>response-wrapper>
payDetailsResponse>NetPay
$PayslipRec>PayslipListBean>
NetPay
executeResponse>response-wrapper>
payDetailsResponse>GBEarningsList>
GBEarnings
for-each($getPayslipDataDetails_GROSS>
executeResponse>response-wrapper>
getPayslipDataDetails_Output>
OutputParameters>Output>
PayslipDataBean>GBEarningsList>
GBEarnings)
executeResponse>response-wrapper>
payDetailsResponse>GBEarningsList>
GBEarnings>TotalCurrentAmount
$getPayslipDataDetails_GROSS>
executeResponse>response-wrapper>
getPayslipDataDetails_Output>
OutputParameters>Output>
PayslipDataBean>GBEarningsList>
GBEarnings>TotalCurrentAmount
executeResponse>response-wrapper>
payDetailsResponse>GBEarningsList>
GBEarnings>ElementName
$getPayslipDataDetails_GROSS>
executeResponse>response-wrapper>
getPayslipDataDetails_Output> OutputParameters>Output>
PayslipDataBean>GBEarningsList>
GBEarnings> ElementName
executeResponse>response-wrapper>
payDetailsResponse>GBEarningsList>
GBEarnings>CurrentAmount
$getPayslipDataDetails_GROSS>
executeResponse>response-wrapper>
getPayslipDataDetails_Output>
OutputParameters>Output>
PayslipDataBean>GBEarningsList>
GBEarnings> CurrentAmount
executeResponse>response-wrapper>
payDetailsResponse>GBDeductionsList>
GBDeductions
for-each($getPayslipDataDetails_DEDUCTIONS>
executeResponse>response-wrapper>
getPayslipDataDetails_Output>
OutputParameters>Output>
PayslipDataBean>GBDeductionsList>
GBDeductions)
executeResponse>response-wrapper>
payDetailsResponse>GBDeductionsList>
GBDeductions> TotalCurrentAmount
$getPayslipDataDetails_DEDUCTIONS>
executeResponse>response-wrapper>
getPayslipDataDetails_Output>
OutputParameters>Output>
PayslipDataBean>GBDeductionsList>
GBDeductions>TotalCurrentAmount
executeResponse>response-wrapper>
payDetailsResponse>GBDeductionsList>
GBDeductions> ElementName
$getPayslipDataDetails_DEDUCTIONS>
executeResponse>response-wrapper>
getPayslipDataDetails_Output>
OutputParameters>Output>
PayslipDataBean>GBDeductionsList>
GBDeductions> ElementName
executeResponse>response-wrapper>
payDetailsResponse>GBDeductionsList>
GBDeductions> CurrentAmount
$getPayslipDataDetails_DEDUCTIONS>
executeResponse>response-wrapper>
getPayslipDataDetails_Output> OutputParameters>Output>
PayslipDataBean>GBDeductionsList>
GBDeductions> CurrentAmount

The final part of the process is primarily concerned with setting the return JSON payload of the OIC REST service.

At the beginning of the process, the Assign function ‘CreateAndInitializeVariable’ set the value of ActionContextId to a value of “0”. Within the for-each loop, if the condition was satisfied, the value of ActionContextId would be set to another value, other than “0”.

The IF NoPayslipsFoundForRequestedMonthAndYear switch statement will be triggered if the token has traversed down the previous ‘otherwise’ route.


IF NoPayslipsFoundForRequestedMonthAndYear

$ActionContextId=0.0

MAP to getPayslipDataDetails_DEDUCTIONS

TARGETMAPPING
executeResponse>response-wrapper>payDetailsResponse>
username
$getLoginRequest>executeResponse>data>
username
executeResponse>response-wrapper>payDetailsResponse>
accessToken
$getLoginRequest>executeResponse>data>
accessToken
executeResponse>response-wrapper>payDetailsResponse>
accessTokenName
$getLoginRequest>executeResponse>data>
accessTokenName
executeResponse>response-wrapper>payDetailsResponse>
ebsVersion
$getLoginRequest>executeResponse>data>
ebsVersion
executeResponse>response-wrapper>payDetailsResponse>
monthNum
substring($PayslipRec/nsmpr10:PayslipListBean/
nsmpr10:EffectiveDate, 6.0, 2.0)
executeResponse>response-wrapper>payDetailsResponse>
year
substring($PayslipRec/nsmpr10:PayslipListBean/
nsmpr10:EffectiveDate, 1.0, 4.0)
executeResponse>response-wrapper>payDetailsResponse>
PayslipChoice
$PayslipRec>PayslipListBean>
PayslipChoice
executeResponse>response-wrapper>payDetailsResponse>
ActionContextId
“No payslips exist on the system for this user for the requested month and year.”

The final process looks like this:

Before the OIC REST service can be activated, a Tracking Business Identifier must be defined.

SOURCETRACKING VARIABLE
execute>request-wrapper>
payDetailsRequest>userName
tracking_var_1

Positive Test Using Postman

  1. Ensure that the OIC Connectivity Agent has started.

2 .Ensure that our new Integration is in an Active state (use Enable Tracing and Include Payload options when doing testing).

3. Since I only have the one user in OIC set up I will use that to authorise the execution of the service.

The REST method is POST, and the URL is obtained from the ‘How to Run’ page on the active OIC process.

4. As we are passing in a JSON payload, set the Content-Type = application/json

5. Add the JSON payload.

{
     "payDetailsRequest": {
         "userName": "WDANIELS",
         "password": "Fred4321",
         "monthNum": "05",
         "year": "2019"
     }
}

6. Press the Send button. After a few moments, you will get a response back, and if the Status is 200 OK, then you should get a JSON payload back too.

7. In OIC (Home > Integrations > Monitoring > Tracking) the token has followed the routing required when a payslips for a given month/year has been found for that user.

Negative Test Using Postman

  1. In this test we will choose a month or year that doesn’t have a payslip available.

2. Press the Send button. After a few moments, you will get a response back, and if the Status is 200 OK, then you should get a JSON payload back too.

3. This time we get the message back that no payslip data exists for the user for that request month and year.

4. In OIC (Home > Integrations > Monitoring > Tracking) the token has followed the routing required when no payslips for a given month/year have been found for that user.

For those that are still here, here’s a link to a video I took of me testing my ISG integration to OIC via the OIC Connectivity Agent.

DEMO – Integrated SOA Gateway – On-premise ISG with OIC

That’s it!!! Of course, not quite.

As is becoming a habit I’ve added some more Useful Notes below which may help you.



Useful Notes

Oracle Integration Cloud – Notes on setup

(I was using a Gen 1 OIC instance back in late 2019. Your experience of commissioning an OIC instance will vary over time as Oracle update their Oracle Cloud Infrastructure and method of requesting an integration platform.)

Once you have signed up for your Oracle Cloud account you’ll get confirmation of your Access Details via email. These consist of:

Username, Password and Cloud Account. These are the 3 main credentials you’ll need on your trial Oracle Cloud account.

My Oracle Cloud (tenancy) Account name was: fujitsuyannov19

It is worth noting that it can take up to an hour for the Oracle Cloud account to become fully provisioned, making all options, such as ‘Platform Services’ being available from the main menu.

After an hour from opening your new Oracle Cloud account you can log in.

For example, https://console.uk-london-1.oraclecloud.com/

This gets you to the main Oracle Cloud console. It is from here that you can use your credit to purchase platforms, compute space and storage. It is also the place where you can request your Oracle Integration Cloud instance.

Getting Started

A useful guide to follow is https://docs.oracle.com/en/cloud/paas/integration-cloud/tutorials.html, although the main steps to take are summarised here.

  1. Sign in to your Oracle Cloud account.
  2. Click Navigation menu icon in the top left corner of the Oracle Cloud Infrastructure Console.
  3. Click Platform Services > Integration.
  4. In the upper right corner, click QuickStarts. The QuickStarts page is displayed.
  5. Choose and Instance Name (I kept the default): Integration189160. My intention was to use the ‘Process Automation’ capability so I chose ‘Oracle Integration – Enterprise’.
  6. You are returned to the previous screen, where you will see the message:

“Your request for a new instance has been received. You will be sent a notification email when provisioning is complete.”

Again, it may take a little while for the Oracle Integration Cloud instance to be fully provisioned, but once created you will be able to connect to it at this URL:

e.g. https://Integration189160-fujitsuyannov19.integration.ocp.oraclecloud.com/ic/home


On-Premise OIC Connectivity Agent – Notes on setup

  1. Login to the ICS/OIC Home page and view landing page.
  2. NAV > Home > Integrations > Agents
  3. Click on “Create Agent Group”
  4. Enter New Agent Group – Information

                Agent Group Name:           fujitsuyannov19 Agent Group

                Identifier:                             FJS_YAN_NOV_19_AGENT_GROUP

                Agent Type:                          Connectivity Agent

                Description:                          Connectivity Agent for Yan’s Nov 19 OIC test instance

  •  Press Create (this can take a few seconds)  
  •  On the Agents page, highlight the new Agent Group you just created (fujitsuyannov19 Agent Group), and hit the Download (top right) > Connectivity Agent.
  •  The file ‘oic_connectivity_agent.zip‘ will download (approx 153 MB)
  •  Unzip the contents of ‘oic_connectivity_agent.zip’. I unzipped the zip file to C:\Oracle\oic_connectivity_agent
  • Using Notepad ++ I updated the InstallerProfile.cfg file.

oic_URL=https://integration189160-fujitsuyannov19.integration.ocp.oraclecloud.com:443

agent_GROUP_IDENTIFIER=FJS_YAN_NOV_19_AGENT_GROUP

For ease of startup, I added the following lines too:

oic_USER=ian.scorrer@fujitsu.com

oic_PASSWORD={myOICPassword}

  1. I left the proxy settings alone for now.
  2. Open a command window (cmd)   
  3. Run the following:

C:\Oracle\oic_connectivity_agent> cd C:\Oracle\oic_connectivity_agent

C:\Oracle\oic_connectivity_agent> java -jar connectivityagent.jar

I got this output….

Proceeding to install a new agent …
 No Proxy Configuration Detected
 Checking for trusted certificates …
 Making call to check OIC Version …
 Making call to check Agent group availability …
 Updating Agent with configuration details …
 Making call to register new agent instance …
 Making call for getting agent app id & keys…
 Done with Agent installation & configuration… Starting Agent for message processing.
 Agent started successfully…Now available for new messages…

The OIC Connectivity Agent was now started succesfully and I was able to transact with my OIC instances.

Note: Once your OIC Connectivity Agent has been started for the first time the entries for oic_USER and oic_PASSWORD in the configuration file InstallerProfile.cfg are “mangled” for security purposes.


XXOIC_USER_PERSON_INFO_REST

I’ve attached the custom package specification and body that we exposed via a custom REST service on ISG. (Due to limitations in WordPress they are in a PDF format…but you should be able to open them ok).

Please refer to my previous blog post on how to deploy them to your Integrated SOA Gateway.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s