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'}

Oracle HCM Salary Query / Element Entries Query

Query to fetch the salary and other element entries from HCM. Original query owner to a friend of mine.


SELECT DISTINCT
prg.assignment_number,
prg.assignment_id,
pet.base_element_name element_name,
pee.entry_type entry_type,
pee.effective_start_date entry_start_date,
pee.effective_end_date entry_end_date,
pee.multiple_entry_count multiple_entry_count,
pee.element_entry_id element_entry_id,
(
CASE
WHEN base_element_name = 'Hourly Paid' THEN
round(peev.screen_entry_value * 1820, 2)
ELSE
to_number(peev.screen_entry_value)
END
) screen_entry_value,
piv.base_name base_name,
pee.person_id person_id,
papf.person_number person_number
FROM
pay_rel_groups_dn prg,
pay_entry_usages peu,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_vl piv,
pay_element_types_vl pet,
per_all_people_f papf
WHERE
1 = 1
AND papf.person_id = pee.person_id
AND papf.person_number = 123456
--AND prg.assignment_number = 'E123456'
AND prg.payroll_relationship_id = peu.payroll_relationship_id
AND prg.relationship_group_id = peu.payroll_assignment_id
AND peu.element_entry_id = pee.element_entry_id
AND pee.element_entry_id = peev.element_entry_id
AND piv.input_value_id = peev.input_value_id
AND pee.element_type_id = pet.element_type_id
AND piv.base_name = 'Amount'
AND pet.base_element_name IN ( 'Basic Salary', 'Hourly Paid' )
AND trunc(pee.effective_start_date) BETWEEN trunc(piv.effective_start_date) AND trunc(piv.effective_end_date)
AND trunc(pee.effective_start_date) BETWEEN trunc(peev.effective_start_date) AND trunc(peev.effective_end_date)
AND trunc(pee.effective_start_date) BETWEEN trunc(pet.effective_start_date) AND trunc(pet.effective_end_date)
AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)

OIC HealthCheck URL

I found the following URL randomly when OIC timedout on me which was able to provide the status of OIC. This needed me to login into the OIC service.

https://*********/ic/integration/home/ping.json

The result is as per below -

{"status": "ok"}

XML to XSD convertor

For OIC Integrations we need to create XSD from the XML (reverse engineer) in various scenarios. The below site worked for me like a charm.
https://www.liquid-technologies.com/online-xml-to-xsd-converter

As part of the tags xs:sequence is used quite a lot which can cause issues if the elements are not in the exact order. I found using xs:all handy to avoid that issue.

Delete duplicate from Oracle Database table

There are various scenarios where data gets duplicated in oracle database tables. Below query can be used to identify the duplicate data based on the unique key and remove the duplicated rows.

DELETE FROM target STG
WHERE ROWID IN ( SELECT rid
FROM (SELECT rowid rid,
row_number() over (partition by order_id order by rowid) rn
FROM target)
WHERE rn <> 1);

The order_id is the unique key used to identify the duplicates.