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.

REST api checklists

Invoke POST method to allocate checklist : hcmRestApi/resources/11.13.18.05/allocatedChecklists/action/allocateChecklist

Use Header Key Content-type = application/vnd.oracle.adf.action+json

Request payload
{
  "checklistName" : "Employee Agreement",
  "checklistCategory" : "ONBOARD",
  "personNumber" : "12345",
  "allocationDate" : "2023-01-01"
}

Alert notifications

If worklist is selected - both to email and notification in application are sent

in case of mail is selected - only email is sent, irrespective of setup in BPM to send to none/All/email/in-app. Note that the expression should be different from ${RecipientUserName} and should pull the work/home email

Absence query to get absences in previous month(absence will be split if spread across 2 months)

 select * from 
(
select 
papf.person_number,
ppnf.Full_name,
aatft.NAME AbsenceTypeName,
apae.PER_ABSENCE_ENTRY_ID, 
(extract(month from pl_ent.start_date))||'-'||(extract(year from pl_ent.start_date)) MON_YYYY,
min(pl_ent.start_date) start_date,
max(pl_ent.end_date) end_date,
sum(ABS_UNITS) duration
--pl_ent.assignment_id,
--(extract(month from pl_ent.start_date))||'-'||(extract(year from pl_ent.start_date)) MON_YYYY
from 
PER_ALL_PEOPLE_F papf ,
PER_ALL_ASSIGNMENTS_f paam,
PER_PERSON_NAMES_F ppnf ,
ANC_PER_ABS_ENTRIES apae,
ANC_ABSENCE_TYPES_VL aatft,
--ANC_ABSENCE_TYPES_F aatf,
ANC_PER_ABS_PLN_SUMM_ENT summ,
ANC_PER_ABS_PLAN_ENTRIES pl_ent
where 1=1
and papf.person_id = paam.person_id
and papf.person_id = ppnf.person_id
AND paam.person_id = apae.person_id
and apae.absence_type_id = aatft.absence_type_id
and apae.absence_type_id = aatft.absence_type_id
and apae.PER_ABSENCE_ENTRY_ID = summ.PER_ABSENCE_ENTRY_ID
and summ.PER_ABS_PLN_SUMM_ENTRY_ID =  pl_ent.PER_ABS_PLN_SUMM_ENTRY_ID
and paam.assignment_id = pl_ent.assignment_id
and paam.assignment_type='E' 
and paam.effective_latest_change='Y' 
and ppnf.name_type='GLOBAL'
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and trunc(sysdate) between aatft.effective_start_date and aatft.effective_end_date
--and paam.legal_entity_id = 300000115626584
--and aatft.absence_type_id IN(300000120663723,300000001955092) 
/*and (
(to_number(extract(year from sysdate)) = to_number(extract(year from apae.start_date)) 
and to_number(extract(month from sysdate)-1) = to_number(extract(month from apae.start_date)) 
and to_number(extract(day from apae.start_date)) between 01 and 31 )
OR 
(to_number(extract(year from sysdate)) = to_number(extract(year from apae.end_date)) 
and to_number(extract(month from sysdate)-1) = to_number(extract(month from apae.end_date))
and to_number(extract(day from apae.end_date)) between 01 and 31  )
)*/
and papf.person_number='5105196'
--order by papf.person_number,pl_ent.start_date
group by papf.person_number,ppnf.FULL_NAME,aatft.NAME,apae.PER_ABSENCE_ENTRY_ID,(extract(month from pl_ent.start_date))||'-'||(extract(year from pl_ent.start_date))
)
WHERE 1=1
---add more conditions here
and trunc(start_date, 'mm') = trunc(add_months(sysdate,-1), 'mm') 
order by PERSON_NUMBER,start_date

Element Entry HDL

METADATA|ElementEntry|EffectiveStartDate|EffectiveEndDate|ElementName|LegislativeDataGroupName|EntryType|AssignmentNumber|MultipleEntryCount

MERGE|ElementEntry|2023/04/01||Assured Bonus|IN Legislative Data Group|E|E5105196-2|1
MERGE|ElementEntry|2023/04/01||Leave Encashment Days|IN Legislative Data Group|E|E5105196-2|1
MERGE|ElementEntry|2023/04/01||ESPP|IN Legislative Data Group|E|E5105196-2|1
MERGE|ElementEntry|2023/04/01||Loan|IN Legislative Data Group|E|E5105196-2|1
MERGE|ElementEntry|2023/04/01|2023/12/31|Maternity Allowance|IN Legislative Data Group|E|E5105196-2|1

METADATA|ElementEntryValue|EffectiveStartDate|EffectiveEndDate|InputValueName|ScreenEntryValue|AssignmentNumber|ElementName|LegislativeDataGroupName|LegislationCode|MultipleEntryCount|EntryType

MERGE|ElementEntryValue|2023/04/01||Amount|1000|E5105196-2|Assured Bonus|IN Legislative Data Group|IN|1|E
MERGE|ElementEntryValue|2023/04/01||Recovery Months|12|E5105196-2|Assured Bonus|IN Legislative Data Group|IN|1|E
MERGE|ElementEntryValue|2023/04/01||Days|23|E5105196-2|Leave Encashment Days|IN Legislative Data Group|IN|1|E
MERGE|ElementEntryValue|2023/04/01||Amount|2000|E5105196-2|ESPP|IN Legislative Data Group|IN|1|E
MERGE|ElementEntryValue|2023/04/01||Amount|50000|E5105196-2|Loan|IN Legislative Data Group|IN|1|E
MERGE|ElementEntryValue|2023/04/01||Installments|5|E5105196-2|Loan|IN Legislative Data Group|IN|1|E
MERGE|ElementEntryValue|2023/04/01||Percentage|10|E5105196-2|Loan|IN Legislative Data Group|IN|1|E
MERGE|ElementEntryValue|2023/04/01|2023/12/31|Amount|3000|E5105196-2|Maternity Allowance|IN Legislative Data Group|IN|1|E

Legal Employer, Payroll, Address Query Oracle HCM

select papf.person_number,
paaf.assignment_number,
ppnf.full_name,
PAP.PAYROLL_NAME,
PLE.NAME LE_NAME,
paaf.assignment_status_type,
ppos.actual_termination_date,
addr.address_type,
paf.ADDRESS_LINE_1,
paf.ADDRESS_LINE_2,
paf.ADDRESS_LINE_3,
paf.TOWN_OR_CITY,
paf.REGION_2 STATE,
paf.COUNTRY,
paf.POSTAL_CODE,
paf.address_id,
addr.PERSON_ADDR_USAGE_ID
from
per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_f paaf,
PER_PERIODS_OF_SERVICE ppos,
pay_payroll_assignments ppasg,
PAY_ASSIGNED_PAYROLLS_DN papd,
pay_all_payrolls_f pap,
per_legal_employers ple,
per_person_addr_usages_f addr,          
per_addresses_f paf 
where 1=1
and papf.person_id = paaf.person_id
and papf.person_id = ppnf.person_id
and papf.person_id = ppos.person_id
and paaf.period_of_service_id = ppos.period_of_service_id
and paaf.assignment_id = ppasg.hr_assignment_id
AND paaf.work_terms_assignment_id = ppasg.hr_term_id
--and paaf.person_id = ppasg.person_id
and ppasg.payroll_term_id = papd.payroll_term_id(+)
and papd.payroll_id = pap.payroll_id(+)
and paaf.legal_entity_id = ple.organization_id
and paaf.person_id = addr.person_id(+)
and addr.address_id = paf.address_id
and addr.address_type(+) = 'HOME'
and ple.status = 'A'
and paaf.PRIMARY_FLAG ='Y'
--and paaf.ASSIGNMENT_TYPE = 'E'
and ppnf.name_type='GLOBAL'
AND PPOS.date_start = (SELECT max(date_start) FROM PER_PERIODS_OF_SERVICE PPOS_IN WHERE PPOS_IN.person_id = PAPF.person_id)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and Nvl(ppos.actual_termination_date, trunc(sysdate)) between paaf.effective_start_date and paaf.effective_end_date
AND  Nvl(ppos.actual_termination_date, trunc(sysdate)) BETWEEN ppasg.start_date AND ppasg.end_date
AND  Nvl(ppos.actual_termination_date, trunc(sysdate)) BETWEEN papd.start_date(+) AND papd.end_date(+)
--AND Nvl(ppos.actual_termination_date, trunc(sysdate)) BETWEEN ppasg.start_date AND ppasg.end_date
and trunc(sysdate) between addr.effective_start_date(+) and addr.effective_end_date(+)
and trunc(sysdate) between paf.effective_start_date(+) and paf.effective_end_date(+)
--and PLE.NAME = 'US1 LE'
--and papf.person_number in ('123123')