• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:November 11, 2020
  • Reading time:8 mins read
You are currently viewing Passing Message Tokens for Messages in BI Report for Dynamic Notification
Passing Message Tokens for Messages in BI Report for Dynamic Notification

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:

image 6 1024x438 - Passing Message Tokens for Messages in BI Report for Dynamic Notification

Here is the Short Text and the Cause:

Short Text

{FIRST_NAME} {LAST_NAME} (EMPLOYEE_NUMBER} - Probation Extended

Cause

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