Pull Data from another attribute in Extract into a Fast formula

 /*******************************************************************
FORMULA NAME : GET_WORK_EMAIL_FF
FORMULA TYPE : Extract Rule
 DESCRIPTION : Get value set value for Email
Change History:
Name                   Date         Comments
--------------------------------------------------------------------------------------------------------------------------

*******************************************************************
*/
DEFAULT FOR DATA_ELEMENTS IS EMPTY_TEXT_TEXT
INPUTS ARE DATA_ELEMENTS (TEXT_TEXT)
l_per_id = ' '
/* To retrieve 'Data element' from attribute */
 
IF (DATA_ELEMENTS.EXISTS('Person_ID') ) THEN
(
   l_per_id = DATA_ELEMENTS['Person_ID'] 
    
   rule_value = GET_VALUE_SET('GET_WORK_EMAIL_VS','|=P_PERSON_ID='''||l_per_id||'''')
   
)

RETURN rule_value

Query to fetch Checklist or Journey related Questionnaire responses

 select responsibility_type,question_text,answer,full_name
from
(
select task.responsibility_type,question_text,nvl2(resp.ANSWER_CODE,resp.LONG_TEXT,resp.ANSWER_CLOB) answer,ppnf.full_name
from Per_allocated_tasks task,
per_allocated_checklists pac,
HRQ_QSTNR_PCPT_RESPONSES_V resp,
HRQ_QUESTIONS_VL ques,
per_person_names_f ppnf
where 1=1
and task.allocated_checklist_id = pac.allocated_checklist_id
and task.questionnaire_id = resp.questionnaire_id
and task.action_type = 'ORA_CHK_QUESTIONNAIRE'
and resp.question_code = ques.question_code
and pac.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL'
and task.allocated_task_id = resp.participant_id
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and task.allocated_checklist_id = :P_ALLOCATED_CHECKLIST_ID
)
order by responsibility_type,question_text

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.