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

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

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

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.

Oracle Worker HDL Post Migration Processes

Oracle has documented the processes hence don't want to duplicate the content -
Oracle Worker HDL Post Processes

All HDL Articles -
Oracle HDL Blogs

HDL Keys - HDL Keys Blog