Welcome Onboard SQL Query with Brusting in Oracle Cloud HCM

 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)
)

Goal and Goal Plan Details SQL query in Oracle Fusion Cloud HCM

Select 

distinct hg.GOAL_NAME

,hg.DESCRIPTION as Goal_DESCRIPTION

,hg.PUBLISH_DATE

,hg.GOAL_TYPE_CODE

,hgpl.GOAL_PLAN_NAME 

,hgpl.DESCRIPTION as GoalPlan_DESCRIPTION

,to_char(hg.START_DATE,'yyyy/mm/dd') as Goal_START_DATE

,to_char(hg.TARGET_COMPLETION_DATE,'yyyy/mm/dd') TARGET_COMPLETION_DATE

,hg.SUCCESS_CRITERIA

,hg.PERCENT_COMPLETE_CODE

,hg.STATUS_CODE

,hg.APPROVAL_STATUS_CODE

,to_char(hg.ACTUAL_COMPLETION_DATE,'yyyy/mm/dd') ACTUAL_COMPLETION_DATE

,hg.GOAL_VERSION_TYPE_CODE --,to_char(hg.VERSION_DATE,'yyyy/mm/dd') VERSION_DATE

,hg.PRIORITY_CODE GOALPRIORITY_CODE

,hg.COMMENTS

,hg.CREATED_BY GOAL_CREATED_BY

,hg.GOAL_URL

,hg.LEVEL_CODE

,hg.LEVEL_MEANING

,hg.CATEGORY_CODE

,hg.MEASURE_TYPE_CODE

,hg.MEASURE_NAME

,hg.TARGET_VALUE

,hg.TARGET_TYPE

,hg.UOM_CODE

,hg.ACTUAL_VALUE

,hg.GOAL_ACCESS_LEVEL_CODE

,hgpg.WEIGHTING

,hgpg.PRIORITY_CODE PLANPRIORITY_CODE

 from

 HRG_GOALS hg,

 HRG_GOAL_PLAN_GOALS hgpg,

 HRG_GOAL_PLANS_TL hgpl

 where hgpg.GOAL_ID=HG.GOAL_ID

 and hg.GOAL_VERSION_TYPE_CODE='ACTIVE'

 and hgpg.GOAL_PLAN_ID=hgpl.GOAL_PLAN_ID

 order by hg.GOAL_NAME