I enjoyed returning to the APEX Alpe Adria conference to chat with and learn from members of the APEX community. If you joined me in Maribor, thanks for your interest in extending Fusion Applications with Oracle APEX. You can download the slides from my session here. In a nutshell, using Fusion Apps business objects in APEX is now as easy as working with local tables.
Fusion Apps (FA) business object REST APIs support filtering using SQL-like predicates and sorting using object attribute names. APEX delegates all searching and sorting to the FA service by automatically using these features. In addition, APEX requests only the data for attributes the current region is using to keep payloads small and fast. These optimizations help you deliver a great user experience. This article explains how to use these features in APEX pages, List of Values, and in PL/SQL business logic.
Setting the Stage
Each Fusion Apps pillar – HCM, ERP, CX, SCM, etc. – has a set of standard business objects. It also lets admins with appropriate privilege extend the app data model. To manage data specific to a company’s unique requirements, an FA customer admin can add new custom attributes to standard objects. In some pillars, they can also create new custom business objects and relate them to other objects.
Each standard or custom object has a corresponding REST API. All share a common set of features and include custom attributes automatically. An FA business object REST service can describe itself, retrieve filtered data, and handle create, update, and delete operations. It also supports bulk operations, where a batch of changes can be done in a single transaction.
Whenever a client uses an FA REST API to create or change data, the service evaluates all appropriate business object validation rules and executes any processing logic. These include behaviors defined by the FA developers at the Oracle “factory”as well as ones added by FA admins as customizations. If the requested operation fails, the response includes one or more error messages to help the end-user understand what requires attention before submitting again. APEX automatically presents these messages to the end-user in a familiar way.
Lead Tracking for an Art Gallery
An art gallery using the Fusion CX suite might use the standard Leads object to track potential customers who have shown interest in purchasing art, together with custom objects like Artwork_c, ArtMedium_c, Artist_c, and InterestedArtworks_c to track the artworks, media, artists, and pieces a particular lead is interested in purchasing. The Resources standard object stores the staff members (a.k.a. human resources) working at the gallery who follow-up after taking ownership of a lead. The following diagram shows a simplified version of the business object model. Notice the convention that the names of custom objects and custom attributes have the _c suffix to distinguish them from the names of the standard objects and attributes.
FA REST Filter Predicates
Fusion Apps business objects REST APIs support SQL-like filter predicates. You phrase them in terms of the case-sensitive business object attribute names. For example, to retrieve leads with a budget greater than or equal to 10000, you would use the following filter expression with the leads endpoint:
Budget_c >= 10000You can combine conditions using conjunctions and group conditions using parentheses. So, to find leads with names that start with ‘A’ or ‘Z’ in addition to having the above budget, you can write:
Budget_c >= 10000 and (Name like 'A%' or Name like 'Z%')For case-insensitive comparisons, use the upper() function around the attribute name on the left-hand-side of the expression. To find leads containing a letter a or z somewhere in the name, you can use:
Budget_c >= 10000 and (upper(Name) like '%A%' or upper(Name) like '%Z%')Each filter predicate has the name of an attribute on the left hand side –optionally surrounded by upper() – followed by an operator, and finally a literal operand value on the right hand side. When using the between operator, you provide two literal values as in this example:
(Budget_c between 7000 and 1000) and (Name like '%A%' or Name like '%Z%')When filtering on date or timestamp values, the literal values must be strings in the ISO8601 format, so to find leads created any time on December 31st, 2023 you use the following filter. It references one of the common audit attributes that include CreationDate, CreatedBy, LastUpdateDate, and LastUpdatedBy:
CreationDate between '2023-12-31T00:00:00' and '2023-12-31T23:59:59'To filter on a date field having no time component, leave off the time portion of the literal date string. For example, to find leads whose decision date is before May 1st, 2024, use:
DecisionDate_c < '2024-05-01'To find leads with qualified status having a rank of warm or hot, you can write a filter like:
StatusCode = 'QUALIFIED' and Rank in ('WARM','HOT')To find leads that have specified some decision date, you can use:
DecisionDate_c is not nullConversely, to check for leads with no decision date provided, use:
DecisionDate_c is nullWhen an FA REST service exposes so-called accessor attributes to related objects, you can reference them in a filter predicate. For example, to find any leads who have shown interest in artwork whose price is between 500 and 1000, you can write the filter:
InterestedArtworks_c.ArtworksObject_c.Price_c between 500 and 1000Using an External Filter on a Page
After defining a REST Data Source of type Oracle Cloud Applications (SaaS) REST Service for a particular FA REST API, you can use it in any APEX region. Whenever a region uses a REST Data Source, the Property Editor shows the External Filter and Order By section with an Enable switch. After flipping it to the on position, you can enter an external filter clause and/or external order by clause. The syntax of both is specific to the type of the REST data source. One created for an FA REST API using the Oracle Cloud Applications (SaaS) REST Service type lets you specify any legal filter predicate we saw above.
When writing the one or more predicate expressions in your external filter, you can use a literal value for the right-hand-side operand. Alternatively, you can reference a value from APEX session state using substitution syntax like this:
(DecisionDate_c < &P3_DECISION_DATE.) and
(InterestedArtworks_c.ArtworksObject_c.Price_c
between &P3_LOW_PRICE. and &P3_HIGH_PRICE.)
and Budget_c > 5000 The value of the page items you reference in the external where clause can be computed by page processes or computations in the Pre-Rendering section of the page. This would come in handy if, for example, the operand for filtering on the lead’s DecisionDate_c field needed to be a function of the current date (formatted in ISO8601 format).
If the region type where you’ve configured an external filter allows end-user filtering operations as well, then any filters added by the end user are combined with the external filter using the and conjunction in the filter expression that APEX sends to the FA REST endpoint.
Using an External Order By on a Page
To configure a particular sort order for a region based on an FA REST API, use an external order by clause. The syntax is simple. Just provide a comma-separated list of case-sensitive attribute names. For example, to sort on the lead name, just mention the attribute name like this:
NameTo sort descending by budget amount, you can use the additional descqualifier:
Budget_c:descIf you do not provide a sort direction qualifier, then an ascending sort is assumed, but you can always explicitly specify the asc qualifier, too. For example to sort descending by budget and then ascending by name, you can configure the external order by to be:
Budget_c:desc,Name:ascExternal Filter & Order By for LOVs
When editing a List of Values on a Fusion Apps REST Data Source, you can use the same external where clause and external order by clause features explained above. Just look for the appropriate fields in the LOV’s Advanced REST Data Source Attributes section.
If your LOV is based on an FA REST data source with infrequently-changing lookup data, you can markedly improve performance by enabling the Use Synchronization Table switch. This will appear after you’ve configured REST Synchronization for the FA REST data source in question. Flipping this switch to the on position causes the LOV to use the locally synchronized data instead of making a REST call on each access. In this case, the External Filter and External Order By fields are no longer applicable.
If you need to apply an external filter to the REST synchronization process itself, you can do that using the p_sync_external_filter_expr parameter of the dynamic_synchronize_data() procedure in the apex_rest_source_sync package. By combining this with a scheduled automation, you can perform the REST synchronization on an appropriate schedule using your custom external filter whenever the synchronization runs against the Fusion Apps REST APIs to which the data source is related.
Using Fusion Apps REST APIs in PL/SQL
Using the procedures and functions in the apex_exec package, you can work programmatically with any REST Data Source. When working with Fusion Apps REST data sources, I like to work with the data using the business object attribute names instead of having to remember the corresponding data profile column names that apex_exec uses. The sample download at the end includes a helper package I created named oca_ds, which is short for Oracle Cloud Applications Data Source. It complements the apex_exec package to simplify working with Fusion Apps REST Data Sources programmatically.
Assuming that leads is the static id of a REST Data Source for working with the Fusion Apps leads endpoint, we can query the top 5 qualified leads with a rank of warm or hot having both a budget and decision date specified like this:
declare
l_ctx apex_exec.t_context;
type t_lead is record (
name varchar(255),
budget number,
created timestamp with time zone);
type t_leads is table of t_lead index by pls_integer;
l_leads t_leads;
begin
l_ctx := oca_ds.open_query(
p_select => 'Name,Budget_c,CreationDate',
p_from => 'leads',
p_where => q'~
StatusCode = 'QUALIFIED' and
Rank in ('WARM','HOT') and
Budget_c is not null and
DecisionDate_c is not null
~',
p_order_by => 'Budget_c:desc',
p_max_rows => 5);
while apex_exec.next_row(l_ctx) loop
l_leads(l_leads.count+1) :=
t_lead(oca_ds.get_varchar2(l_ctx,'Name'),
oca_ds.get_number(l_ctx,'Budget_c'),
oca_ds.get_timestamp_tz(l_ctx,'CreationDate'));
end loop;
oca_ds.close(l_ctx);
-- Work with top 5 leads in l_leads here...
exception
when others then
oca_ds.close(l_ctx);
raise;
end;Inserting New Leads in PL/SQL
Of course you can use a Form or Interactive Grid region to easily insert, update, or delete data from the Leads REST data source without writing any code, but should you need to do so programmatically, it’s easy. To insert data using an FA REST data source, use the oca_ds.open_dml function to create an apex_exec.t_context object that works like a temporary rowset. Then for each new row you want to add, call oca_ds.insert_row() and then use oca_ds.set_value() to set the value of each attribute necessary. Finally, to process the pending changes call apex_exec.execute_dml().
For example, you can write the following code to insert two new leads:
declare
l_ctx apex_exec.t_context;
begin
-- Insert two new leads
l_ctx := oca_ds.open_dml(
p_attributes =>'Name,Budget_c,DecisionDate_c',
p_from => 'leads');
-- Insert first lead
oca_ds.insert_row(l_ctx);
oca_ds.set_value(l_ctx,'Name','Remy Ratatouille');
oca_ds.set_value(l_ctx,'DecisionDate_c',date'2024-07-31');
oca_ds.set_value(l_ctx,'Budget_c',1800);
-- Insert second lead
oca_ds.insert_row(l_ctx);
oca_ds.set_value(l_ctx,'Name','Anton Ego');
oca_ds.set_value(l_ctx,'DecisionDate_c',date'2024-05-15');
oca_ds.set_value(l_ctx,'Budget_c',800);
-- Save the changes
apex_exec.execute_dml(l_ctx);
oca_ds.close(l_ctx);
exception
when others then
oca_ds.close(l_ctx);
raise;
end;Updating a Lead in PL/SQL
Updating data using an FA REST data source follows a similar pattern. Use the oca_ds.open_dml function to create an apex_exec.t_context object. Then for each row you want to update, call oca_ds.update_row() and then use oca_ds.set_value() to set the value of each attribute necessary. Finally, to process the pending changes call apex_exec.execute_dml(). Often you will start by querying the row first to consider its existing values first, before updating some of them. In that case you can call apex_exec.set_values() to copy all the row values from the queried row into the row to be updated.
For example, to update a lead named ‘Anton Ego’ to increase his budget by 20% and adjust his decision date to be a week (7 days) farther in the future, you can write the following code. Notice that we include the APEX$ResourceKey attribute in the list since it’s the unique identifier of the row that’s needed to perform the update.
declare
l_ctx apex_exec.t_context;
l_dml_ctx apex_exec.t_context;
-- other declarations here
begin
-- Retrieve and update opportunity with resource key 98765
-- to increase its amount by 20% and close date by one week
l_ctx := oca_ds.open_query(
p_select => 'APEX$ResourceKey,Budget_c,DecisionDate_c',
p_from => 'leads',
p_where => q'~Name='Anton Ego'~');
if not apex_exec.next_row(l_ctx) then
raise_application_error(-20001,'Lead Anton Ego not found');
end if;
l_dml_ctx := oca_ds.open_dml(
p_from => 'leads',
p_attributes => 'APEX$ResourceKey,Budget_c,DecisionDate_c',
p_lost_update_detection => true);
oca_ds.update_row(l_dml_ctx);
-- Set the row version checksum used for lost update protection
apex_exec.set_row_version_checksum(
p_context => l_dml_ctx,
p_checksum => apex_exec.get_row_version_checksum(l_ctx));
-- Copy the values of the fetched lead into the update row
apex_exec.set_values(
p_context => l_dml_ctx,
p_source_context => l_ctx );
-- Adjust the budget and decision date appropriately
oca_ds.set_value(l_dml_ctx,'Budget_c',
oca_ds.get_number(l_dml_ctx,'Budget_c') * 1.2);
oca_ds.set_value(l_dml_ctx,'DecisionDate_c',
oca_ds.get_date(l_dml_ctx,'DecisionDate_c') + 7);
-- Save the changes
apex_exec.execute_dml(l_dml_ctx);
oca_ds.close(l_dml_ctx);
oca_ds.close(l_ctx);
exception
when others then
oca_ds.close(l_dml_ctx);
oca_ds.close(l_ctx);
raise;
end;Deleting a Lead in PL/SQL
Deleting data using an FA REST data source is a bit simpler. As before, use the oca_ds.open_dml function to create an apex_exec.t_context object. Then for each row you want to delete, call oca_ds.delete_row() and then use oca_ds.set_value() to set the value of the resource key APEX$ResourceKey. Finally, to process the pending changes call apex_exec.execute_dml().
Assuming the system-assigned resource key for the Anton Ego lead is 4, the following code deletes this lead:
declare
l_dml_ctx apex_exec.t_context;
begin
-- Delete lead with resource key '4'
l_dml_ctx := oca_ds.open_dml(
p_from => 'leads',
p_attributes => 'APEX$ResourceKey');
oca_ds.delete_row(l_dml_ctx);
-- Set the resource key of the row to be deleted
oca_ds.set_value(l_dml_ctx,'APEX$ResourceKey', '4');
-- Save the changes
apex_exec.execute_dml(l_dml_ctx);
oca_ds.close(l_dml_ctx);
exception
when others then
oca_ds.close(l_dml_ctx);
raise;
end;Finding the Logged-in User’s PartyId
As a final use case to explore, let’s imagine we are an art gallery salesperson logging in to the APEX app to work our leads for the day. Fusion Apps represents each art gallery staff member – i.e. human resource – as a Resource object in the system. When a staff member is associated with other business objects, that resource’s PartyId attribute value is used as a foreign key.
For example, a lead gets assigned to an “owner” to contact the potential customer to qualify if there is a real opportunity to sell some art. To find the list of leads owned by a particular salesperson like “Matt Hooper”, we need to know the value of Matt’s PartyId and then query the leads REST API to filter for leads where OwnerId equals the logged-in user’s PartyId value.
To simplify using the current APEX user’s PartyId in external filters on various pages of the app, we can look it up using their Username from the resources REST API after authentication and store that PartyId value in APEX session state for the duration of the user’s session. Assume we’ve created a REST Data Source of type Oracle Cloud Applications (SaaS) REST Service for the Fusion Apps resources endpoint and assigned it a static id of resources. Furthermore, assume we’ve created shared component application items named APP_USER_PARTYID and APP_USER_FULLNAME. You can write the following code in a shared component Application Process with the After Authentication process point to lookup the current user’s PartyId and PartyName from the resources REST data source and store those values in the application items:
declare
l_ctx apex_exec.t_context;
begin
l_ctx := oca_ds.open_query(
p_select=> 'PartyId,PartyName',
p_from => 'resources',
p_where => 'upper(Username) = '||
apex_exec.enquote_literal(:APP_USER));
if apex_exec.next_row(l_ctx) then
:APP_USER_FULLNAME := oca_ds.get_varchar2(l_ctx,'PartyName'));
:APP_USER_PARTYID := oca_ds.get_number(l_ctx,'PartyId'));
end if;
oca_ds.close(l_ctx);
end set_session_info_for_user;After setting this up, other pages in the app can reference the value of APP_USER_PARTYID in external filters using substitution syntax as we saw earlier in this article. For example, in a region based on the Leads REST data source, to filter the leads to show only ones owned by the logged-in user, we could use an external filter of:
OwnerId = &APP_USER_PARTYID.Can Any Attribute Appear in a Filter?
A last consideration worth mentioning is that not all business object attributes can be used in a filter predicate. To discover which attributes you can use, consult the Oracle Cloud Apps Data Source Explorer utility app. There you can see if the attribute has a Y or an N in the Queryable? column. The adjective “queryable” is a bit of a misnomer. An attribute with an N in the Queryable? column can still participate in the query in the select list. However, it cannot appear in a filter predicate. For example, as shown below, the InventoryItemDescription attribute in the Leads object is not queryable.
An attempt to reference InventoryItemDescription in a filter predicate results in the error:
Search filter includes a non-queryable column or unsupported operatorTo install the Oracle Cloud Apps Data Source Explorer app visit the Gallery > Utility Apps tab and click the Install button like you’ve done for other sample apps. It helps you explore interesting information about all the Fusion Apps REST APIs your applications use.
Downloading the Helper Package
Hopefully, this information about Fusion Apps REST API filters and how to use them will come in handy the next time you create an APEX application that works with these powerful business object APIs. You can download the source of my oca_ds helper package here. For more information, see the Working with REST Data Sources for Oracle Cloud SaaS Applications section of the APEX developer documentation. It explains all the features for working easily with Fusion Applications REST APIs.
Dive Into APEX 