Inquiry Regarding Dynamic User Inclusion in Report Emails via Bursting

Sai Sreeram Gundepudi

Summary:

Hi Oracle Experts,

We have received a request from our users to include the username of the individual executing a report in the email body after the "Thanks" section within the bursting logic. This inclusion should occur dynamically whenever a report is executed, whether through a scheduling job or an ESS job.

Could you please confirm if this is feasible? We attempted to implement it using the following SQL, but it did not yield the desired results.

Code Snippet (add any code snippets that support your topic, if applicable):

SELECT
q1.c_q2_delivery_id AS "KEY",
'MT PackingSlip Report' AS TEMPLATE,
'en-US' AS LOCALE,
'PDF' AS OUTPUT_FORMAT,
'EMAIL' AS DEL_CHANNEL,
q1.CUSTOMER_PO_NUMBER AS OUTPUT_NAME,
'true' AS SAVE_OUTPUT,
'user1@xyz.com' AS PARAMETER1,
'user2@xyz.com,user3@xyz.com,user4@xyz.com,user5@xyz.com' AS PARAMETER2,
'bipublisher-report@oracle.com' AS PARAMETER3,
'Shipping Notification - ' || q1.CUSTOMER_PO_NUMBER AS PARAMETER4,
'********** THIS IS A POC EMAIL PLEASE IGNORE **********' || chr(13) || chr(10) ||
chr(13) || chr(10) || 'Hi' || chr(13) || chr(10) ||
chr(13) || chr(10) || 'This email is part of our Proof of Concept (POC) testing. Please disregard these emails as they are only for testing purposes.' ||
chr(13) || chr(10) || chr(13) || chr(10) || 'Thank you for your understanding.' ||
chr(13) || chr(10) || chr(10) || 'xdo_user_name - ' || (SELECT NVL(:xdo_user_name, 'No Data Available') FROM dual) ||
chr(13) || chr(10) || 'xdo_user_report_oracle_lang - ' || (SELECT NVL(:xdo_user_report_oracle_lang, 'No Data Available') FROM dual) ||
chr(13) || chr(10) || 'xdo_user_report_locale - ' || (SELECT NVL(:xdo_user_report_locale, 'No Data Available') FROM dual) ||
chr(13) || chr(10) || 'xdo_user_ui_oracle_lang - ' || (SELECT NVL(:xdo_user_ui_oracle_lang, 'No Data Available') FROM dual) ||
chr(13) || chr(10) || 'xdo_user_ui_locale - ' || (SELECT NVL(:xdo_user_ui_locale, 'No Data Available') FROM dual) ||
chr(13) || chr(10) || 'xdo_user_timezone - ' || (SELECT NVL(:xdo_user_timezone, 'No Data Available') FROM dual) ||
chr(13) || chr(10) || chr(10) || 'Thanks & Regards' || chr(13) || chr(10) ||
chr(10) || 'Test Person' AS PARAMETER5,
'True' AS PARAMETER6,
'user1@xyz.com' AS PARAMETER7,
NULL AS PARAMETER8
FROM [Rest of the query...].

Attaching output Result

Mallikarjuna Kuppauru-Oracle

Hi @Sai Sreeram Gundepudi

you can try as below in your bursting query -

select:xdo_user_name as USER_ID,:xdo_user_roles as USER_ROLES,:xdo_user_report_oracle_lang as REPORT_LANGUAGE,:xdo_user_report_locale as REPORT_LOCALE,:xdo_user_ui_oracle_lang as UI_LANGUAGE,:xdo_user_ui_locale as UI_LOCALEfrom dual

Refer -

Including User Information Stored in System Variables in Your Report Data

Regards,

Arjun

_vero_

Hello,

please check

https://docs.oracle.com/middleware/12213/bip/BIPDM/GUID-A926D588-426F-47C7-8A9A-B0A7959C5814.htm#BIPDM344

Have you tried inserting a :USER session variable into the bursting query so that it reads the logged in user and consequently who executed the job?

Thanks