External bank accounts and owners tables

select * from IBY_EXT_BANK_ACCOUNTS where 1=1 order by creation_date desc

select * from IBY_ACCOUNT_OWNERS where 1=1 order by creation_date desc

How to Load Hire/Termination/Rehire Historical Data with HDL?

 METADATA|Worker|SourceSystemOwner|SourceSystemId|PersonId|ActionCode|ReasonCode|EffectiveStartDate|EffectiveEndDate|PersonNumber|StartDate|DateOfBirth|CountryOfBirth|WaiveDataProtectFlag

MERGE|Worker|HRC_SQLLOADER|40001023PER15||HIRE||1990/07/16|4712/12/31|40001023PER15|1990/07/16|1943/07/12|EE|N

METADATA|PersonLegislativeData|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|LegislationCode|MaritalStatus|Sex

MERGE|PersonLegislativeData|HRC_SQLLOADER|40001023PER15|1990/07/16|4712/12/31|40001023PER15|US||F

METADATA|PersonName|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|LegislationCode|NameType|FirstName|MiddleNames|LastName|Honors|KnownAs|Title|NameInformation15|NameInformation16

MERGE|PersonName|HRC_SQLLOADER|40001023PER15|1990/07/16|2005/05/03|40001023PER15|US|GLOBAL|WorkerTest01|WorkerTest01|WorkerTest01|||MS.||

MERGE|PersonName|HRC_SQLLOADER|40001023PER15|2005/05/04|4712/12/31|40001023PER15|US|GLOBAL|WorkerTest01|WorkerTest01|WorkerTest01||WorkerTest01|MS.|WorkerTest01|

METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|LegalEmployerName|PersonId(SourceSystemId)|DateStart|WorkerType|LegalEmployerSeniorityDate|ActualTerminationDate|EnterpriseSeniorityDate|LastWorkingDate|NotifiedTerminationDate|OnMilitaryServiceFlag|PrimaryFlag|ActionCode|ReasonCode

MERGE|WorkRelationship|HRC_SQLLOADER|WR-40001023PER15|GBI HCM Widgets USA|40001023PER15|1990/07/16|E||2000/12/31|1990/07/16|||N|Y|RESIGNATION|RESIGN_PERSONAL

MERGE|WorkRelationship|HRC_SQLLOADER|WR-40001023PER1501|GBI_GHR_2TMA|40001023PER15|2001/01/01|E||2005/12/31|1990/07/16|||N|Y|RESIGNATION|RESIGN_PERSONAL

MERGE|WorkRelationship|HRC_SQLLOADER|WR-40001023PER1502|GBI_GHR_2TSA|40001023PER15|2006/01/01|E|||1990/07/16|||N|Y|HIRE|

METADATA|WorkTerms|SourceSystemOwner|SourceSystemId|ActionCode|ReasonCode|LegalEmployerName|AssignmentNumber|AssignmentName|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId(SourceSystemId)|PersonTypeCode|AssignmentStatusTypeCode|AssignmentType|BusinessUnitId|NoticePeriod|PrimaryWorkTermsFlag|FreezeStartDate|FreezeUntilDate

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER15|HIRE||GBI HCM Widgets USA|WT-NU-40001023PER15|WT-NA-40001023PER15|1990/07/16|2000/12/31|N|1|WR-40001023PER15|Employee|ACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER15|RESIGNATION|RESIGN_PERSONAL|GBI HCM Widgets USA|WT-NU-40001023PER15|WT-NA-40001023PER15|2001/01/01|4712/12/31|N|1|WR-40001023PER15|Employee|INACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER1501|HIRE||GBI_GHR_2TMA|WT-NU-40001023PER1501|WT-NA-40001023PER1501|2001/01/01|2005/12/31|N|1|WR-40001023PER1501|Employee|ACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER1501|RESIGNATION|RESIGN_PERSONAL|GBI_GHR_2TMA|WT-NU-40001023PER1501|WT-NA-40001023PER1501|2006/01/01|4712/12/31|N|1|WR-40001023PER1501|Employee|INACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER1502|HIRE||GBI_GHR_2TSA|WT-NU-40001023PER1502|WT-NA-40001023PER1502|2006/01/01|2009/12/31|N|1|WR-40001023PER1502|Employee|ACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER1502|ASG_CHANGE||GBI_GHR_2TSA|WT-NU-40001023PER1502|WT-NA-40001023PER1502|2010/01/01|4712/12/31|N|1|WR-40001023PER1502|Employee|ACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

METADATA|Assignment|SourceSystemOwner|SourceSystemId|ActionCode|ReasonCode|LegalEmployerName|AssignmentNumber|AssignmentName|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId(SourceSystemId)|WorkTermsAssignmentId(SourceSystemId)|PersonTypeCode|AssignmentStatusTypeCode|AssignmentType|BusinessUnitId|NoticePeriod|PrimaryAssignmentFlag|FreezeStartDate|FreezeUntilDate

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER15|HIRE||GBI HCM Widgets USA|WA-NU-40001023PER15|WA-NA-40001023PER15|1990/07/16|2000/12/31|N|1|WR-40001023PER15|WT-40001023PER15|Employee|ACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER15|RESIGNATION|RESIGN_PERSONAL|GBI HCM Widgets USA|WA-NU-40001023PER15|WA-NA-40001023PER15|2001/01/01|4712/12/31|N|1|WR-40001023PER15|WT-40001023PER15|Employee|INACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER1501|HIRE||GBI_GHR_2TMA|WA-NU-40001023PER1501|WA-NA-40001023PER1501|2001/01/01|2005/12/31|N|1|WR-40001023PER1501|WT-40001023PER1501|Employee|ACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER1501|RESIGNATION|RESIGN_PERSONAL|GBI_GHR_2TMA|WA-NU-40001023PER1501|WA-NA-40001023PER1501|2006/01/01|4712/12/31|N|1|WR-40001023PER1501|WT-40001023PER1501|Employee|INACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER1502|HIRE||GBI_GHR_2TSA|WA-NU-40001023PER1502|WA-NA-40001023PER1502|2006/01/01|2009/12/31|N|1|WR-40001023PER1502|WT-40001023PER1502|Employee|ACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER1502|ASG_CHANGE||GBI_GHR_2TSA|WA-NU-40001023PER1502|WA-NA-40001023PER1502|2010/01/01|4712/12/31|N|1|WR-40001023PER1502|WT-40001023PER1502|Employee|ACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

When to use Publish Required in Document of Records setup?

Oracle has explained this clearly in one of the 2018 release updates with an example.

Publish Required: You can make a document record visible on the UI from a specific date. The Publish Date field is now configured to be entered using HDL only, and not from the UI. If you want the document to be visible only after a specific date, you must select Yes or No in the Publish Required field on the Create Document Type page. For example, if you want to make the promotion letter visible starting 1-Apr-2018, select Yes on the Create Document Type page. Then, pass the date of 1-Apr-2018 in the Publish Date field using HDL, when creating document records for the worker.

Delete Allocated Checklist HDL sample

METADATA|AllocateChecklist|ChecklistName|ChecklistCategory|ChecklistInstance|PersonNumber
DELETE|AllocateChecklist|Data Handler Acknowledgement|ONBOARD|1|10001
DELETE|AllocateChecklist|Data Handler Acknowledgement|ONBOARD|1|10002

All the tasks will also be deleted automatically.

If you want to delete just a particular task that is also possible through HDL

Alerts Example - Send Consolidated Email to Manager

Create a resource alert from Tools - Alerts Composer

Resource - workers

 

Add Filter

Resource - workers

Expression -

exists ( Select 'x' from per_all_people_f p where p.person_id =${PersonId} and p.person_number in ('1001','1002','1003'))

Click Apply

Add Template

Click on Manage Recipients and Messages

Give the below in Communication Method as Mail, Expression as ${AlertUtils.toString(AlertUtils.empManagerList(workers.PersonId).WorkEmail)}

Group by - ${AlertUtils.toString(AlertUtils.empManagerList(workers.PersonId).WorkEmail)}

Add below text as message Text

This is a test email. The email is being sent to managers of below person numbers.

${AlertUtils.write("<table border=\"1\"> <tbody><tr><th>Person Number</th> <th>Person Name</th>")}

${AlertUtils.loop('workers','<tr><td>${workers.PersonNumber}<br></td><td>${workers.names.DisplayName}</td></tr>')}

${AlertUtils.write("</tbody></table>")}

Click Apply

Click Save and Close

Now run and test the alert.