How to Personalize / Customize Pages in Oracle Fusion Cloud using Sandboxes

Ever wondered how can we Personalize seeded pages in Oracle Fusion, like we used to do in the eBusiness Suite (EBS) ?

Well, it's possible to achieve this to some extent by leveraging Sandbox feature in ERP Cloud.

Sandbox ? What's that ?

Sandbox is an out-of-box tool provided by Oracle Fusion to perform customizations in isolation without impacting other configurations in the given environment. Sandbox is a logically isolated area in your environment where you can do your Personalizations/Customizations and test them without interfering with those pages in the real world. While you are configuring and testing these changes, other users won't see any of them until you Publish the Sandbox.

Sandbox essentials in a Nutshell:

• Only one sandbox can be active at a given point of time.
• Once the Sandbox is activated, it's name is displayed in the global area.
• Ensure to log out from Oracle Fusion and log back in after activating a Sandbox. This is to ensure that the cache is cleared and it helps avoid conflicts.
• A sandbox will remain active until
   - You exit the sandbox
   - Publish the sandbox
   - Delete the sandbox
   - Set another sandbox as Active
• Oracle Fusion patches usually retain the changes from Published Sandboxes but any unpublished Sandboxes may not work if the Oracle Patch brings new changes to the given page which effective make such Sandboxes obsolete/incompatible. It's better to create new Sandbox in such cases.

Steps to create and activate a Sandbox

1. Click on Navigator, go to Configuration Menu and select Sandboxes

Alternatively, you can enter Sandbox area by clicking User Menu on Top Right and then selecting Edit Pages under Administration menu

If you follow this path, you will see a pop-up message as shown below. Click on "Activate a sandbox" option and it will take you to the Sandbox area (same as above step).

2. Once the Sandboxes window is opened, you can click on Create Sandbox button from Available Sandboxes tab

3. Let's name our Sandbox as "TestSandbox" and set Publishable to Yes. We can select and use many Tools under this Sandbox.
For our example, let's select Page Composer tool and click Create and Enter button.

4. Once you enter a Sandbox, it's name will appear on top indicating that you are now working in a Sandbox environment and whatever changes you do will be visible to you alone.

5. Let's click on Home button to go to our Homepage

6. In our example, we are going to Personalize Create Invoice page under Payables business area

7. Navigate to Payables -> Invoices from within the Sandbox environment (while Yellow Sandbox bar appears on the top of the window)

8. Let's open the Create Invoice page

9. Navigate to Tools menu under Sandbox area and select Page Composer

10. Once you are in Page Composer mode, you will see more tools and table in the top region

11. Click on Select tab

12. Now, let's say we want to Hide the Description field on this page.

      Click on the area around Description field and you will see a small pop-up showing options to Edit this area

13. Click on Edit Component option

14. You will see a pop-up window showing Component Properties for "Description:"

      Here, let's uncheck "Show Component" option and click Ok

15. Once you return to the Create Invoice page, you will see "Description" field has now disappeared

16. With this, we've achieved to Personalize/Customize "Create Invoice" page but remember, this change is only applicable to this Sandbox and is not visible to other users.


17. Now, let's see how we can publish this Sandbox so that this change gets applied across the actual environment and will be visible to all the users.


18. Navigate to right top area on this page and click Close

19. Click on the Sandbox name on top left cornet (TestSandbox in our case) and click Publish

20. You will see below pop-up indicating that you will be taken to Sandbox page to publish this Sandbox. Click Yes

21. Once you land on Sandbox page, you can verify the Sandbox Name and Current Status.

      You can also see/verify the actual page name that was modified as part of this change.

      Click on Publish button and you will see a final pop-up box asking for final confirmation.

      Click Yes to Publish this Sandbox

      Please note: Publishing a Sandbox is an irrevocable activity; so please take this action only after you are certain about all the changes.

22. Once the Sandbox is published, navigate to the same "Create Invoices" page in Payables

      Here, we will see that the "Description" field is now hidden on the actual page as well.

This way we can create and publish a Sandbox to achieve Personalizations/Customizations on Oracle Fusion pages.

Read More

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