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.

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

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.

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