How to configure Email Delivery in OCI and send emails from APEX deployed in Oracle Autonomous Database

In this Blog, we will learn how to set up Email Delivery configurations in Oracle Cloud Infrastructure (OCI) and use the same in APEX which is deployed in Autonomous Database on this OCI environment.

Part 1: Configurations in OCI

Step 1: Creating a Group

Navigate to Identity & Security -> Groups

Create a new Group. We'll name it MailGroup

Step 2: Creating an User

Navigate to Identity & Security -> Users

Now, let's create a new Identity and Access Management (IAM) User as shown below. We'll name it mailsender@dummydomain.com

Step 3: Adding User to the Group

- Navigate to Groups.
- Open MailGroup
- Click on Add User to Group
- Select mailsender@dummydomain.com to add it to this group

Step 4: Create Email Policy / User Permissions

Navigate to Identity & Security -> Policies

- Create a new Policy. We'll name it MailPolicy
- Set Policy use case to Email Management
- Select/assign MailGroup under Groups section

Step 5: Create SMTP Credentials

- Navigate to User Profile
- Click on User Settings

- Navigate to Resources section and click on SMTP Credentials

- Click on Generate SMTP Credentials

- Enter a Description of the SMTP Credentials in the dialog box. We'll name it MailSender

- Click Generate SMTP Credentials. A user name and password is displayed.


IMPORTANT:

Copy the user name and password for your records before closing the dialog box. This is very important because you can't retrieve the password again after closing the dialog box for security reasons.


Step 6: Create Email Domain

- Navigate to Developer Services -> Email Delivery

- Click on Create Email Domain
- We'll name it dummydomain.com
- Create

Step 7: Create an Approved Sender

- Navigate to Approved Senders section
- Create Approved Sender
- We'll name it mailsender@dummydomain.com
- Please note that this will be the email address which will be used to send emails from OCI and/or APEX. 

Step 8: Check SMTP Configuration

- Navigate to Configuration under Email Delivery section
- Copy the Public Endpoint from SMTP Configuration

Part 2: Configurations in APEX

Step 1: Set APEX Instance Parameters

- Connect to the Autonomous Database as ADMIN user (SQL Developer in OCI)

- Run the following command using the Public Endpoint, Username and Password generated in above steps:

BEGIN

APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.email.us-ashburn-1.oci.oraclecloud.com');

APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', '<username from above steps>');

APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', '<password from above steps>');

COMMIT;

END;

/

We've now completed all the configurations pertaining to OCI and APEX for Email Delivery.

Now, let's see how to send emails from APEX application.


Step 2: Send Email

- To send emails in APEX, we've to use APEX_MAIL.SEND API

Here's the API definition:

APEX_MAIL.SEND(
    p_to                        IN    VARCHAR2,
    p_from                      IN    VARCHAR2,
    p_body                      IN  [ VARCHAR2 | CLOB ],
    p_body_html                 IN  [ VARCHAR2 | CLOB ] DEFAULT NULL,
    p_subj                      IN    VARCHAR2 DEFAULT NULL,
    p_cc                        IN    VARCHAR2 DEFAULT NULL,
    p_bcc                       IN    VARCHAR2 DEFAULT NULL,
    p_replyto                   IN    VARCHAR2);

- Navigate to APEX on OCI

- Navigate to SQL Commands window under SQL Workshop

- Enter below code and run

BEGIN

APEX_MAIL.SEND

(

    p_to   => 'xyz@gmail.com'

   ,p_from => 'mailsender@dummydomain.com'

   ,p_subj => 'Test Notification Subject'

   ,p_body => 'Test Notification Body'

);

COMMIT;

APEX_MAIL.PUSH_QUEUE;

END;

/

Please note that we've used mailsender@dummydomain.com as the From Email as it was configured as an Approved Sender in OCI. Email delivery will fail if any other Email Id is used for From Email parameter.

- Once above steps are completed, the Email Notification should be successfully delivered to the designated mailbox:

Read More

How to utilize session variables inside BI publisher reports in Oracle Fusion Cloud

So, you are trying to create a custom report in Oracle Fusion based on it's older EBS version but the EBS query contains references to session variables like USER_ID, RESP_ID.

But in Fusion, we cannot derive such session/user specific information using profile options like we used to do in EBS.

Let's see how to derive this information in Fusion BI reports.

The BI Publisher stores information about the current user that can be accessed by the report data model.

The user information is stored in system variables as described below.

Note:
BI Publisher populates all the system variables in an online report.

In case of a scheduled job, publisher doesn’t populate below system variables:

XDO_USER_REPORT_LOCALE
XDO_USER_UI_LOCALE
XDO_USER_UI_ORACLE_LANG

If we want to add the user information to a custom data model, we can define the variables as parameters and then define the parameter value as an element in the data model.

We can also add the variables as parameters then reference the parameter values in the report.

For example, if we use below query in Data Model:

select
:xdo_user_name as USER_NAME,
: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_LOCALE
from dual

It will return below output:

XML Output:

<?xml version="1.0" encoding="UTF-8"?>
<! - Generated by Publisher - >
<DATA_DS>
<G_1>
<REPORT_LANGUAGE>US</REPORT_LANGUAGE>
<REPORT_LOCALE>en_US</REPORT_LOCALE>
<UI_LANGUAGE>US</UI_LANGUAGE>
<UI_LOCALE>en_US</UI_LOCALE>
<USER_NAME>john.doe</USER_ID>
</G_1>
</DATA_DS>

Now, let's see a practical example where we can use such derivations.

Suppose below is the business requirement:

- Identify the current user name

- Find out all roles associated with this user

- Find out all the business units associated with above role(s)

Then we would write below query to achieve this:

SELECT   DISTINCT bu.bu_name

 FROM     fusion.FUN_ALL_BUSINESS_UNITS_V     bu,

                 fusion.FUN_USER_ROLE_DATA_ASGNMNTS  role,

                 fusion.per_users pu

 WHERE role.org_id = bu.bu_id

AND        pu.USER_GUID = role.USER_GUID

AND        pu.username = :xdo_user_name

AND        role.active_flag = 'Y'

The above query can be extrapolated to further utilize this business unit information to link in a bigger derivation/query based on the requirements.

This is how we can make use of session/system variables in BI publisher report in Oracle Fusion.

Read More

How to Customize the Seeded Reports and associated Data Models in Oracle Fusion Cloud

Let's see how to customize Data Models of any seeded report in Oracle Fusion.

- Login to Fusion environment.

- Navigate to Menu and open Tools sub-menu

- Under Tools, find and click on 'Reports and Analytics' option

- This will open the Analytics server (Transactional Business Intelligence) home page

- You will notice the URL will have /analytics in the end

- We need to change /analytics to /xmlpserver and press enter

- This will give you access to XMLP server

- Now, expand the Shared Folders directory and navigate to your desired folder in this seeded area.

e.g. Let's assume I want to customize a seeded AP report, so I will navigating to Financials -> Payables -> Invoices and locate the desired report

- Let's say we want to customize data model of PayablesInvoicesSuppliersExtract report

- Click on More and then select Customize from the context menu

- This will create a custom version of this report and you can find it under /Shared Folders/Custom path instead of seeded /Financials path

e.g. in this case, navigate to Shared Folders/Custom/Financials/Payables/Invoices to see the custom version of above report

- We can rename this to some custom name, if needed

- Now, while we are in the Custom area, let's create a folder to store data model, just like the seeded area

- Click + sign on the left and select Folder

Enter name as 'Data Models' and click Create

- This will create Data Models folder in custom path /Shared Folders/Custom/Financials/Payables/Invoices

- Now, we need to head back to the seeded area to copy the original Data Model

- Navigate to  /Shared Folders/Financials/Payables/Invoices/Data Models

- Locate the Data Model associated with our report

- Click on More and select Copy from the context menu

- Now, let's go back to the Custom area so that we can Paste this data model there

- Navigate to  /Shared Folders/Custom/Financials/Payables/Invoices and Click on Paste option in the Tasks area

- Let's rename this Data Model to some custom name

- Click More and select Rename from the context menu

- Now, let's associate this Custom Data Model with our Custom Report


- Navigate to Catalog and locate our custom report


- Click on Edit option

- Here you will see the Custom report is still pointing to Seeded Data Model

- Click on the Select Data Model button (magnifying glass)

- In the popup window, navigate to Custom area and locate the desired folder in the left pane (/Shared Folders/Custom/Financials/Payables/Invoices/Data Models in our case)

- Select the Custom Data Model from the right pane and click Open

- Now, you will see the data model name has changes to the Custom one and the navigation also points to Custom path

- Click on the Data Model link

- This will open the definition of the Data Model where we can see the underlying query and associated parameters etc.

- Here, we can modify, add or remove data sets/queries, add/modify parameters, List of Values (LOVs) as well as Bursting configurations.

- Navigate back to Catalog, locate the custom report and click on Open to run the report

This is how you can customize a seeded report and the associated data model in Oracle Fusion.

Read More

How to customize Workflow Notifications in Oracle Fusion Cloud

Wondering how to customize an existing approval workflow notification to add a new field ?
Let's see how it can be achieved in Oracle ERP Cloud.

Let’s say we want to add GL Account Description to the GL Journal Approval Workflow Notification.

Normally, most of the workflow notification are based on BI Publisher model. So let's see which one is the seeded model for GL Journal Approval.

- Navigate to BI Publisher Catalog in your environment

- In the Folders pane, expand the Shared Folders -> Financials -> Workflow Notifications -> General Ledger -> Journals folder

- Locate ' Journal Approval Report

- Click More and select Customize option

- This will automatically create a custom copy of this report under Shared Folders -> Custom -> Financials -> Workflow Notifications -> General Ledger -> Journals folder

- Now, lets modify this report to add our new customizations so that they will reflect in the email notifications.

- Open the report and click on Edit button

- This will download the template file (RTF)

- Open the template file. It should look like this -

- Modify the template and add the new column COMBINATION_DESCRIPTION under CLINE_ACCOUNT column

- This field should pertain to COMBINATION_DESCRIPTION column which is already available in the seeded data model

- Save the template and upload to the custom version of Journal Approval Report. You would need to delete the existing template and upload a new one with the same name as original 'Journal Approval Email Notification' by using the modified RTF file.

- Once you click Upload, the Journal Approval Report page will return to the thumbnail view of the report layout templates.

- You can review/verify the newly uploaded template details by clicking the View a list link. Save Report to finalize the changes.

- Once these steps are done, the new customization is in place and should automatically take effect.

NOTE -

Configurable workflow notifications are refreshed every 24 hours so that they perform better for your users. But when you're making changes to reports, sub-templates, or data models, you can apply your changes sooner so they're available for testing. Create profile options to control when notifications reflect your changes after you upload them to the BI catalog. When you're done configuring notifications, use the same profile options to turn the refresh back to every 24 hours, to optimize performance. But even if you don't, the refresh automatically resets to 24 hours when it's been more than eight hours since you set the profile options.

If you don't wish to wait for 24 hours :

You can Create Profile Options to Control the Refresh

Your profile options can apply to all workflow tasks, a product family, or a product. Based on the scope you want, your profile option must have a profile option code that follows a certain format.

- Once the customization is in place, it should reflect in the subsequent workflow notifications. We can see the account description is shown in the Journal Approval notification with this change -

Read More

How to create a Value Set in Oracle Fusion Cloud

 Let's see how to create a Value Set in Oracle Fusion/ERP Cloud.

- Go to Setup and Maintenance -

- Set the desired business area e.g. Financials -

 

- Search for task Manage%Value%Set% and locate it as below -

- Open the task 

- Click on + sign to create a new Value Set -

- Enter all the necessary details for Value Set definition -

- Click on Manage Values to maintain Independent Value Set values

- Click on + sign to create new values -

- Click on + sign to create new values -

Read More