Introduction:
This blog has the SQL query that can be used to pull the data of employees who are recalled to join the organization.
Cause of the issue:
Business wants to call back certain former employees requesting them to join back the organization for a specific work on certain conditions. In such case, an application user will update their assignment status as RECALL from TERMINATION. So, using this update business wants a notification to be sent to their personal email with an offer letter attachment.
How do we solve:
Create a report in BI Publisher using below SQL query to extract the details.
–** Report Query **–
SELECT TO_CHAR (PAA.effective_start_date,’Month DD,YYYY’, ‘nls_date_language=American’) EFFECTIVE_START_DATE
,TO_CHAR (PAA.effective_end_date,’Month DD,YYYY’, ‘nls_date_language=American’) EFFECTIVE_END_DATE
,TO_CHAR (SYSDATE,’Month DD,YYYY’, ‘nls_date_language=American’) CURRENT_DATE
,PAP.person_number
,PPN.display_name EMPLOYEE_NAME
,PEA.email_address
,’Hi ‘||PPN.display_name||chr(10)||’,
We are pleased to inform you that you have been recalled and we request you to report at office on ‘||
TO_CHAR (PAA.EFFECTIVE_START_DATE,’MM/DD/YY’, ‘nls_date_language=American’)||’.’ pdfbody
FROM per_all_people_f PAP
,per_all_assignments_m PAA
,per_person_names_f PPN
,per_email_addresses PEA
WHERE 1=1
AND PAP.person_id = PAA.person_id
AND (PAA.effective_end_date > SYSDATE – 30 OR PAA.effective_end_date IS NULL)
AND TRUNC(PAA.last_update_date) BETWEEN TRUNC(SYSDATE)-30 AND TRUNC(SYSDATE)
AND PAA.assignment_type = ‘E’
AND PAP.person_id = PPN.person_id
AND PPN.name_type = ‘GLOBAL’
AND TRUNC(SYSDATE) BETWEEN PPN.effective_start_date AND NVL(PPN.effective_end_date, TRUNC(SYSDATE))
AND PAA.action_code = ‘RECALL’
AND PAA.assignment_status_type = ‘ACTIVE’
AND PEA.person_id = PAP.person_id
AND PEA.EMAIL_TYPE = ‘H1’
AND TRUNC(SYSDATE) BETWEEN PEA.date_from AND NVL(PEA.date_to, TRUNC(SYSDATE))
–** Bursting Query **–
SELECT PAP.person_number KEY
,’Bursting’ TEMPLATE
,’en-us’ LOCALE
,’PDF’ OUTPUT_FORMAT
,’Welcome_Letter’||PAP.person_number OUTPUT_NAME
,’EMAIL’ DEL_CHANNEL
–,PEA.email_address parameter1
,’toemail@xx.com’ parameter1
,’humanresouces@xx.ca’ parameter3 — FROM
,’TEST – Welcome – Recall Notification’ parameter4 — Subject
,’Hi ‘||PPN.display_name||’, We are pleased to inform you that you have been recalled and we request you to report at office on ‘||
TO_CHAR (PAA.EFFECTIVE_START_DATE,’MM/DD/YY’, ‘nls_date_language=American’)||’.’ parameter5
,’true’ parameter6 —BODY
,’ ‘ parameter7 –BCC
FROM per_all_people_f PAP
,per_all_assignments_m PAA
,per_person_names_f PPN
,per_email_addresses PEA
WHERE 1=1
AND PAP.person_id = PAA.person_id
AND (PAA.effective_end_date > SYSDATE OR PAA.effective_end_date IS NULL)
AND PAA.assignment_type = ‘E’
AND PAP.person_id = PPN.person_id
AND PPN.name_type = ‘GLOBAL’
AND TRUNC(SYSDATE) BETWEEN PPN.effective_start_date AND NVL(PPN.effective_end_date, TRUNC(SYSDATE))
AND PAA.action_code = ‘RECALL’
AND PAA.assignment_status_type = ‘ACTIVE’
AND PAA.effective_start_date > SYSDATE – 60
AND PEA.person_id = PAP.person_id
AND PEA.EMAIL_TYPE = ‘H1’
AND TRUNC(SYSDATE) BETWEEN PEA.date_from AND NVL(PEA.date_to, TRUNC(SYSDATE))
