In this article we will look into an unexplored option of using Messages and passing bind variables to get the notifications triggered to users using BI Publisher Report.
This idea of using Messages and passing bind variables has been shared by our Telegram group Member John. If you are not part of it yet, please do join the group and get such innovative stuff.
Let’s get into the details of the functionality.
Messages can accept Message Tokens but those can set in Fast Formula only to get the message displayed on the application.
Ex: If Message is configured with text as “Absence validation failed for {PERSON_NUMBER}” where the “PERSON_NUMBER” is a Message Token and should be set on the Fast Formula as per the requirement.
We can query these messages in the BI Report, however we cannot pass the message tokens {PERSON_NUMBER} to the Message and get the complete text using any package.
In order to achieve this requirement, we are going to implement a workaround using which we can pass the values to message and display text dynamically. This way we can skip writing logic in the BI template and can handle this efficiently in Messages.
Table of Contents
Advantages of this approach:
- Easy Maintenance – End users will have full control over the Message Text and can be changed whenever there is a change in the requirement.
- Non Technical – Even the functional analysts/End Users can configure the Messages to be used and the technical folks can code them in BI Report.
- Supports html formatting – Adding underline, bullet points, etc.
Here is how the message will look like:
Here is the Short Text and the Cause:
Short Text
{FIRST_NAME} {LAST_NAME} (EMPLOYEE_NUMBER} - Probation ExtendedCause
<html>
<body>{MANAGER_FIRST_NAME} {MANAGER_LAST_NAME} has confirmed that your probation period has been extended to {PROABATION_EXTENDED_DATE}. <br><br>If you have any questions please reach out to your manager.
</body>
</html>SQL Query to fetch Messages:
select fnm.message_number,
fnm.message_name,
fnm.message_text,
fmt.token_name,
fmt.data_type,
fmt.description
from fnd_new_messages fnm,
fnd_message_tokens fmt
where fnm.message_name = fmt.message_name
and fnm.message_name = 'SM_TEST_NOTIFICATION'
and fnm.language_code = 'US'If we run this query we would get the message tokens as-is.. Now we have to use replace command to replace these tokens with actual field values.
Updated SQL Query to fetch bind values for Message:
SELECT person_number
, manager_email_address
, employee_email_address,
(SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(message_text
,'{SALUTATION}' ,title)
,'{DISPLAY_NAME}' ,display_name)
,'{EMPLOYEE_NUMBER}' ,person_number)
,'{START_DATE}' ,TO_CHAR(Start_Date,'DD-Mon-YYYY','NLS_DATE_LANGUAGE = ENGLISH') )
,'{FIRST_NAME}' ,first_name)
,'{PREFERRED_NAME}' ,first_name)
,'{LAST_NAME}' ,last_name)
,'{FULL_NAME}' ,full_name)
,'{MIDDLE_NAME}' ,middle_names)
,'{PROBATION_EXTENDED_DATE}',TO_CHAR(probation_end_date,'DD-Mon-YYYY','NLS_DATE_LANGUAGE = ENGLISH') )
,'{MANAGER_FIRST_NAME}' ,manager_first_name)
,'{MANAGER_PREFERRED_NAME}' ,manager_first_name)
,'{MANAGER_LAST_NAME}' ,manager_last_name)
,'{MANAGER_DISPLAY_NAME}' ,manager_display_name)
,'{MANAGER_FULL_NAME}' ,manager_full_name)
,'{MANAGER_MIDDLE_NAME}' ,manager_middle_names)
FROM fnd_messages_tl
WHERE message_name ='SM_TEST_NOTIFICATION') Message_subject
, (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(message_cause
,'{SALUTATION}' ,title)
,'{DISPLAY_NAME}' ,display_name)
,'{EMPLOYEE_NUMBER}' ,person_number)
,'{START_DATE}' ,TO_CHAR(Start_Date,'DD-Mon-YYYY','NLS_DATE_LANGUAGE = ENGLISH') )
,'{FIRST_NAME}' ,first_name)
,'{PREFERRED_NAME}' ,first_name)
,'{LAST_NAME}' ,last_name)
,'{FULL_NAME}' ,full_name)
,'{MIDDLE_NAME}' ,middle_names)
,'{PROBATION_EXTENDED_DATE}',TO_CHAR(probation_end_date,'DD-Mon-YYYY','NLS_DATE_LANGUAGE = ENGLISH') )
,'{MANAGER_FIRST_NAME}' ,manager_first_name)
,'{MANAGER_PREFERRED_NAME}' ,manager_first_name)
,'{MANAGER_LAST_NAME}' ,manager_last_name)
,'{MANAGER_DISPLAY_NAME}' ,manager_display_name)
,'{MANAGER_FULL_NAME}' ,manager_full_name)
,'{MANAGER_MIDDLE_NAME}' ,manager_middle_names)
FROM fnd_messages_tl
WHERE message_name ='SM_TEST_NOTIFICATION') Message_body
FROM (
SELECT papf.person_number
, ppnf.title
, NVL(ppnf.known_as, ppnf.first_name) first_name
, ppnf.last_name
, ppnf.display_name
, ppnf.full_name
, ppnf.middle_names
, NVL(ppnfm.known_as, ppnfm.first_name) manager_first_name
, ppnfm.last_name manager_last_name
, ppnfm.display_name manager_display_name
, ppnfm.full_name manager_full_name
, ppnfm.middle_names manager_middle_names
, rw.pdate001 probation_end_date
, papf.Start_Date start_date
, peae.email_address employee_email_address
, peam.email_address manager_email_address
FROM per_all_assignments_m paam
,per_person_names_f ppnf /* for employee */
,per_all_people_f papf
,per_email_addresses peae /* for employee email id*/
,per_assignment_supervisors_f pasf
,per_email_addresses peam /* for Manager email id*/
,per_person_names_f ppnfm /* for Manager */
,per_all_people_f papfm /* for manager */
where <all joins for the tables listed above>)Now when we use this query, it will populate the FIRST_NAME, LAST_NAME, MANAGER_FIRST_NAME, MANAGER_LAST_NAME, etc from the query that we have written and the output will be user centric. We need to use this replace command in both Data Model SQL Query and the bursting query as we are trying to use the dynamic subject as well.
In the template, we need to use the below function to get the text displayed in html format. MESSAGE_BODY is the tag for the selected field from Data Model.
<?html2fo:MESSAGE_BODY?>Hope this helps when you have similar requirement.
Have you come across any use cases like this? Please post your feedback in comments section.
