Integrations Lead - Lessons learnt

 Integrations have been my passion for a while but like anything tech there is no credit given when things go right but always heaps of pressure when things go wrong. Putting together some of my lessons learnt -

1. Get the functional specifications signed off by business, these are pushed away due to lack of understanding, time pressures etc.

2. Give special focus for elements which are customer facing like Invoice Print or Invoice numbering for Payment systems. Remittance advices etc

3. Focus on ensuring the COA mapping is accurate to the multiple levels. One level mapping is easy but multi level is always a tricky things to get it right.

4. Focus on ensuring all the elements from source can be pushed into Oracle in one shape or form to aid with reconciliation. Example AR Lockbox ensure rules are defined at customer and transaction number level rather than just one level.

5. No one focuses on a generic end to end test case for loaded data, but having a discussion and capturing the detailed tests will mean no surprises when the interface data is processed after Go Live.

More to be updated.

Oracle BI Get users with a role

 Very handy query to get all users with a given role.

SELECT pu.username,
       prdt.role_id,
       prdt.role_name,
       prd.role_common_name
FROM   per_user_roles pur,
       per_users pu,
       per_roles_dn_tl prdt,
       per_roles_dn prd
WHERE  pu.user_id = pur.user_id
       AND prdt.role_name LIKE 'IT Security Manager'
       AND prdt.role_id = pur.role_id
       AND prdt.role_id = prd.role_id
       AND prdt.language = Userenv ('lang')
       AND pu.active_flag = 'Y'
ORDER  BY pu.username,
          prdt.role_name 

OIC Blogs

 Error Handling - https://www.linkedin.com/pulse/error-handling-oracle-integration-cloud-harris-qureshi/

Oracle ERP HCM Location Based Access Control (LBAC)

Oracle Cloud (ERP/HCM) has Location Based Access Control, which an excellent feature to control user access to tasks & data based on their roles and IP addresses.

Various Oracle blogs related to LBAC which provides all the necessary details -

https://blogs.oracle.com/fusionhcmcoe/post/enabling-lbac-location-based-access-control

https://blogs.oracle.com/fusionhcmcoe/post/lbac-vs-ip-whitelisting

https://docs.oracle.com/en/cloud/saas/human-resources/22d/ochus/overview-of-location-based-access.html#s20068058

How LBAC can be used to secure REST API access - This is very good security feature if external systems are integrating with Oracle ERP/HCM using API's.

https://www.ateam-oracle.com/post/securing-oracle-fusion-applications-rest-apis-with-location-based-access-control-lbac

Oracle cloud ERP/HCM read-only access

Providing read only access to Oracle cloud ERP/HCM is a common requirement. Oracle has provided an easy way to provide this functionality.

To enable read-only mode for a user:

1. In the Setup and Maintenance work area, use the Manage Administrator Profile Values task.

2. In the Search section of the Manage Administrator Profile Values page, enter FND_READ_ONLY_MODE in the Profile Option Code field and click Search.

3. In the FND_READ_ONLY_MODE: Profile Values section of the page, click the New icon.

4. In the new row of the profile values table:

a. Set Profile Level to User.

b. In the User Name field, search for and select the user.

c. Set Profile Value to Enabled to activate read-only access for the selected user.

5. Click Save and Close.

When the user next signs in, a page banner reminds the user that read-only mode is in effect and no changes can be made.

Reference to Oracle documentation -

https://docs.oracle.com/en/cloud/saas/human-resources/22d/ochus/provide-read-only-access.html#s20056769

OIC Monthend Scheduling or Calendar based scheduling

OIC provides a good range of scheduling options using the simple calendar to iCal expressions. What if the scheduling need to based on a calendar or Monthend dates which are defined dates which keeps changing every year.

This can be achieved in a relatively simple fashion using the below approach.

Step 1 : Define the Calendar in a LOOKUP, This is yearly task to update the monthend(ME) dates part of the year end process. This will be referenced in the OIC Integrations.

Step 2 : Obtain the ME dates based on the current month in the Integration using the lookup value function.

Expression example : vCutoff - dvm:lookupValue('oramds:/apps/ICS/DVM/FIN_CALENDAR_LKP.dvm','Month',string(xp20:format-dateTime(/nssrcmpr:schedule/nssrcmpr:startTime,'[MNn,*-3]-[Y0001]')),'ME-Date','ERROR:CALENDAR')

Step 3 : Build a switch statement into the OIC Integration to use the ME date and based on the business requirement perform the necessary action.

Examples -

a. Invoke a process or Integration on ME date only

b. Invoke a process or Integration until ME date

c. Invoke a process or Integration after ME date

d. Invoke a process or Integration until ME date and given week day like Friday

Below example shows use case b. Where the Integration runs every day until ME date and stops gracefully after ME date of given month.

Switch condition is  simple as below.

So in 3 easy steps we could implement Monthend based scheduling in OIC with simple changes to OIC Integration. Happy Integrating!

Oracle Expression Language Samples

Expression laguage is used widely in various placed in Oracle HCM and ERP. Here are some of the samples of usage while exposing Pages/Tiles in HCM and ERP.

#{securityContext.userInRole['TEST_ROLE']}

#{securityContext.userGrantedResource['resourceType=FNDResourceType;resourceName=FND_Scheduled_Processes_Menu;action=launch'] or !securityContext.userInRole['OFD_SALES_REP_CUSTOM_JOB,OFD_SALES_MGR_VP_CUSTOM_JOB']}

#{securityContext.userInAllRoles['role1,role2,roleN']}

#{securityContext.userName=='user 1' || securityContext.userName=='user 2' || securityContext.userName=='user 3'}