Oracle HCM Cloud – Bursting & Sending Data Over Email

Query for sending data to Employees to fill their Goals

——————————————————————————

select

b.person_id,

b.person_number,

c.full_name,

PEA.EMAIL_ADDRESS work1

from

per_all_assignments_m a,

per_all_people_f b,

per_person_names_f c,

per_jobs_f_tl d,

hr_organization_units_f_tl houft

,PER_EMAIL_ADDRESSES_V PEA

where

a.person_id=b.person_id

and a.person_id=c.person_id

and a.job_id=d.job_id

and d.language=’US’

—-and (trunc(a.last_update_date)= trunc(sysdate-1) and trunc(a.effective_start_date) >= trunc(sysdate-30))

and sysdate between b.effective_start_date and b.effective_end_date

and sysdate between a.effective_start_date and a.effective_end_date

and a.effective_start_date between d.effective_start_date and d.effective_end_date

and trunc(sysdate-10) = (select trunc(min(date_start)) from per_periods_of_service paam

where paam.person_id = a.person_id

)

and a.assignment_type=’E’

and a.assignment_status_type=’ACTIVE’

and PEA.person_id = a.person_id

and PEA.EMAIL_TYPE = ‘W1’

AND a.business_unit_id = houft.organization_id

AND houft.LANGUAGE = ‘US’

and houft.name Not in (”)

AND SYSDATE BETWEEN houft.effective_start_date

AND houft.effective_end_date

and c.name_type = ‘GLOBAL’

and a.legislation_code = ‘IN’

=============================================

BURSTING FOR THE QUERY TO SEND EMAIL TO EMPLOYEE

select main.person_id KEY,

–,’Burst’ TEMPLATE

‘Bursting_Layout’ TEMPLATE

,’en-US’ LOCALE

, ‘html’ OUTPUT_FORMAT

, ‘EMAIL’ DEL_CHANNEL

, ‘TRS’ OUTPUT_NAME

, main.work1 PARAMETER1

——, ‘[email protected]’ PARAMETER1

—,’[email protected]’ PARAMETER8

,’Test HR <[email protected]>’ PARAMETER3

, ‘Email to New Joiners for KRA Setting’ PARAMETER4

, ” PARAMETER5

–,DECODE(MAIN.STEP_CODE,’WSEVAL’,’Your Worker Evaluation is not Completed’,’MGREVAL’,’The Manager assessment is Pending’,’ ‘) PARAMETER5

, ‘true’ PARAMETER6

From (select

b.person_id,

b.person_number,

c.full_name,

PEA.EMAIL_ADDRESS work1

from

per_all_assignments_m a,

per_all_people_f b,

per_person_names_f c,

per_jobs_f_tl d,

hr_organization_units_f_tl houft

,PER_EMAIL_ADDRESSES_V PEA

where

a.person_id=b.person_id

and a.person_id=c.person_id

and a.job_id=d.job_id

and d.language=’US’

—-and (trunc(a.last_update_date)= trunc(sysdate-1) and trunc(a.effective_start_date) >= trunc(sysdate-30))

and sysdate between b.effective_start_date and b.effective_end_date

and sysdate between a.effective_start_date and a.effective_end_date

and a.effective_start_date between d.effective_start_date and d.effective_end_date

and trunc(sysdate-566) = (select trunc(min(date_start)) from per_periods_of_service paam

where paam.person_id = a.person_id

)

and a.assignment_type=’E’

and a.assignment_status_type=’ACTIVE’

and PEA.person_id = a.person_id

and PEA.EMAIL_TYPE = ‘W1’

AND a.business_unit_id = houft.organization_id

AND houft.LANGUAGE = ‘US’

and houft.name Not in (”)

AND SYSDATE BETWEEN houft.effective_start_date

AND houft.effective_end_date

and c.name_type = ‘GLOBAL’

and a.legislation_code = ‘IN’

) Main

Share this:

• X
• Facebook

Like Loading…