Thursday, 21 September 2023

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.

Wednesday, 19 April 2023

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 

Tuesday, 3 January 2023

OIC Blogs

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

Tuesday, 13 December 2022

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



Friday, 25 March 2022

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!



Tuesday, 1 February 2022

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


Tuesday, 18 January 2022

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)

Wednesday, 12 January 2022

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


Monday, 10 January 2022

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.


Wednesday, 5 January 2022

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.

Tuesday, 14 September 2021

OIC Replace connection in existing integration

In OIC we end up creating multiple connections during the development. When it comes to PROD deployment we would want to tidy up the connections and make sure connections are not duplicated.

OIC has a feature to change a connection in the integration without the need for redeveloping the integration. The connection need to be of the same type (of course)!
> Deactivate the integration
> Click on Configure in the integration options
> It open the configuration manager, when a package is involved all integrations are diplayed.
> And you will be able to selected the replacement connection.



Oracle Integration Cloud (OIC) Date formatting

Formatting dates is one of common operations needed in OIC.

To format the date OIC has provided the following function OOTB.

xp20:format-dateTime( Start Time, "[Y0001][M01][D01][H01][m01][s01]")

The format covers the Year/Month/Date/Hour/Minute/Seconds. Additional options are also available.

Some popular date formats courtesy Oracle support.

Expression

Result

xp20:format-dateTime(xp20:current-dateTime(),"[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01].[f001][Z]”)

2012-04-12T17:37:57.000-0600

xp20:format-dateTime(xp20:current-dateTime(),”[D1] [MI] [Y]”)

12 4 2012

xp20:format-dateTime(xp20:current-dateTime(),”[D1o] [MNn], [Y]”)

12 April, 2012

xp20:format-dateTime(xp20:current-dateTime(),”[h]:[m01]:[s01] o'clock”)

5:37:57 o'clock

xp20:format-dateTime(xp20:current-dateTime(),”[D01] [MN,*-3] [Y0001]”)

12 APR 2012

xp20:format-dateTime(xp20:current-dateTime(),”[FNn] [D] [MNn] [Y]”)

Thursday 12 April 2012

xp20:format-dateTime(xp20:current-dateTime(),”[[[Y0001]-[M01]-[D01]]]”)

[2012-04-12]

xp20:format-dateTime(xp20:current-dateTime(),”[YWw]”)

Two Thousand and Twelve

xp20:format-dateTime(xp20:current-dateTime(),”[Dwo] [MNn]”)

twelve April

xp20:format-dateTime(xp20:current-dateTime(),”[h]:[m01] [PN]”)

5:37 PM

xp20:format-dateTime(xp20:current-dateTime(),”[h]:[m01]:[s01] [Pn]”)

5:37:57 pm

xp20:format-dateTime(xp20:current-dateTime(),”[h]:[m01]:[s01] [PN] [ZN,*-3]”)

5:37:57 PM -0600

xp20:format-dateTime(xp20:current-dateTime(),”[H01]:[m01]”)

17:37

xp20:format-dateTime(xp20:current-dateTime(),”[H01]:[m01]:[s01].[f001]”")

17:37:57.000

xp20:format-dateTime(xp20:current-dateTime(),”[H01]:[m01]:[s01] [z]”)

17:37:57 GMT-06:00

xp20:format-dateTime(xp20:current-dateTime(),”[H01]:[m01] Uhr [z]”)

17:37 Uhr GMT-06:00

xp20:format-dateTime(xp20:current-dateTime(),”[h].[m01][Pn] on [FNn], [D1o] [MNn]”)

5.37pm on Thursday, 12 April

xp20:format-dateTime(xp20:current-dateTime(),”[M01]/[D01]/[Y0001] at [H01]:[m01]:[s01]”)

04/12/2012 at 17:37:57



Thursday, 2 September 2021

HCM Employee Purge in Test Environments

HCM Cloud provides an handy purge feature OOTB to purge person data from test environments. This can be used for a variety of reasons particularly for complying with GDPR.

Below are steps needed for running the purge process.

1. Raise an Oracle SR to get the Purge Key. This can only be done in TEST environments.

2. Configure the Purge Key in Configure HCM Data Loader > Purge Person Enabled Key

3. Run the Purge Person Data in Test Environments process using variety of parameters.
- Person numbers can be provided using % like 1001% along with full person numbers like 1234
- To remove all eligible person data a simple query like 'select person_is from per_all_people_f' can be used.
- Save 'N' lists down the eligible person number before purging.
- Save 'Y' purges the person numbers eligible.

The following rules apply for the purging process to work.
A person record cannot be deleted if it:
- Was created using File-Based Loader (FBL)
- Has been processed in a Payroll run
- Is the record of a contact who is enrolled in a Benefits program

Happy GDPR.

Wednesday, 25 August 2021

HCM Actions - Assignment Change / Termination / New Hire

Base table for HCM Actions for various type of transactions like Assignment Change / Termination / New Hire is PER_ACTIONS_B

Wednesday, 4 August 2021

FlowActionsService SOAP Error

Trying to submit the FlowActionsService SubmitFlow API and java.lang.IllegalArgumentException popped up and there was no clue what was going wrong. Finally fingured with that the following tag is also needed.
typ:recurringFlag false /typ:recurringFlag

Tuesday, 3 August 2021

Query Get HCM Payroll Name

Query to get payroll name of the payroll assigned with person number -

select p.person_number, py.payroll_name
from PAY_ASSIGNED_PAYROLLS_DN ap
, pay_PAYROLL_TERMS pt
, PAY_PAY_RELATIONSHIPS_DN pr
, PAY_ALL_PAYROLLS_F py
, PER_ALL_PEOPLE_F p
where ap.payroll_term_id = pt.payroll_term_id
and pt.payroll_relationship_id = pr.payroll_relationship_id
and ap.payroll_id = py.payroll_id
and pr.person_id = p.person_id
and p.person_number = :person_num

Thursday, 25 February 2021

HCM Extract / Fast Formulas scribblings

HCM Extracts -

Add Parent Data Group
- Select the main UE

To add a Child data group -> Right click on the Parent group area and 'Add Child Data Group'
- Define the Child Data group (UE, Filters etc)
- Connect the Data groups

To add a Record -> Righ Click and 'Add Record'
- Define the details, Save and Add Attributes

To add a fast formula column add the columnd as Rule

DEFAULT FOR PER_ASG_REL_DATE_START IS '4712/12/31 00:00:00' (date)

l_rel_date_start = PER_ASG_REL_DATE_START

l_effective_date = GET_PARAMETER_VALUE_DATE('EFFECTIVE_DATE')

--RULE_VALUE is default return for Extract Rule FF Type
return RULE_VALUE

Cloud HCM Use Valueset in Fast Formula

You can use valueset(Ex-ORG_LEVEL1) to use the query table functionality in Fast Formulas. Below is sample how you pass parameters(P_ORG_ID) and invoke valuesets to FFs. The Fast Formula will be of type Extract Rule.

Sample FF Code :
DEFAULT FOR PER_ASG_DEPARTMENT_ID IS 0

l_organization_id=to_char(PER_ASG_DEPARTMENT_ID)
l_level1_org=GET_VALUE_SET('ORG_LEVEL1','|=P_ORG_ID='''||l_organization_id||'''')

RULE_VALUE=l_level1_org

return RULE_VALUE


In you Valueset where clause the parameter is used as below :
and level1.organization_id=:{PARAMETER.P_ORG_ID}
Make sure the ID and Value columns are populated in Valuset otherwise empty values are returned

HCM GET ORG AT PARTICULAR DEPTH

Query to get a org at a particular depth -

SELECT haou.name, haou.organization_id
, ANCESTOR_PK1_VALUE, DISTANCE
,haou1.name LEVEL1_ORG
FROM per_org_tree_node_rf prf, hr_all_organization_units haou
, hr_all_organization_units haou1
WHERE 1=1
AND pk1_value = haou.organization_id
AND ANCESTOR_PK1_VALUE = haou1.organization_id
AND DISTANCE = (CASE WHEN haou.name LIKE '%IDEN1%' THEN 1
WHEN haou.name LIKE '%IDEN2%' THEN 2
WHEN haou.name LIKE '%IDEN3%' THEN 3
ELSE 1 END
)

Instead of the hard coded distance calculation you could use the current node depth and get the node at a given level above.

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 pres...