Below is the SQL query with Brusting for the Welcome Onboard to Company in Oracle Cloud HCM.
Step1-->SQL Query
SELECT
PERSON_NUMBER KEY,
PAPF.PERSON_NUMBER,
PPNF.DISPLAY_NAME,
PEA.EMAIL_ADDRESS ,
TO_CHAR(PAAM.PROJECTED_START_DATE,'DD-MM-YYYY') DOJ
FROM
PER_ALL_ASSIGNMENTS_M PAAM,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_EMAIL_ADDRESSES PEA
WHERE 1=1
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.ASSIGNMENT_TYPE = ('P')
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PPNF.PERSON_ID = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND PEA.PERSON_ID = PAPF.PERSON_ID
AND EMAIL_TYPE ='H1'
AND paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND TRUNC(PAAM.PROJECTED_START_DATE) = TRUNC(SYSDATE)
Step2--> Brusting Query.
SELECT
KEY KEY,
'Welcome' TEMPLATE,
'en-us' LOCALE,
'HTML' OUTPUT_FORMAT,
'Notification' OUTPUT_NAME,
'EMAIL' DEL_CHANNEL,
EMAIL_ADDRESS PARAMETER1,
'sathwikit@gmail.com' PARAMETER2,
'no-reply@oracle.com' PARAMETER3,
'Welcome to SIT Solutions !' PARAMETER4,
'' PARAMETER5,
'TRUE' PARAMETER6
FROM(
SELECT
PERSON_NUMBER KEY,
PAPF.PERSON_NUMBER,
PPNF.DISPLAY_NAME,
PEA.EMAIL_ADDRESS ,
TO_CHAR(PAAM.PROJECTED_START_DATE,'DD-MM-YYYY') DOJ
FROM
PER_ALL_ASSIGNMENTS_M PAAM,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_EMAIL_ADDRESSES PEA
WHERE 1=1
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.ASSIGNMENT_TYPE = ('P')
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PPNF.PERSON_ID = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND PEA.PERSON_ID = PAPF.PERSON_ID
AND EMAIL_TYPE ='H1'
AND paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND TRUNC(PAAM.PROJECTED_START_DATE) = TRUNC(SYSDATE)
)
