Business Challenge
In Oracle Fusion Service CX, there is a need to add a new Descriptive Flexfield (DFF) segment to the Part Line of a Service Request. This new field should enable the selection of a contact associated with the “Return To Party.” The List of Values (LOV) for this segment must dynamically query contacts based on the currently selected “Return To Customer Party” using the following SQL query, where partyId is derived from the selected party:
SELECT TO_CHAR(hr.relationship_id) AS "contactPartytId",
hr.subject_id AS "customerPartyId",
hp.party_name AS "contactName",
hcp.email_address AS "emailAddress"
FROM hz_relationships hr,
hz_parties hp,
hz_contact_points hcp
WHERE hr.relationship_code = 'CONTACT'
AND hr.object_id = hp.party_id
AND hr.status = 'A'
AND hp.party_id = hcp.owner_table_id(+)
AND hcp.owner_table_name(+) = 'HZ_PARTIES'
AND hcp.contact_point_type(+) = 'EMAIL'
AND hcp.primary_flag(+) = 'Y'
AND hr.subject_id = ${$variables.customerPartyId}In Oracle EBS, this could be achieved easily using :BLOCK.FIELD notation or by including standard columns in the DFF definition. However, these options are not available in Oracle Cloud ERP, presenting a challenge for creating dependent DFF segments.
Our Solution
To address this, we define a new DFF segment and extend the Part Lines screen using Visual Builder Studio (VBS). This allows the DFF to dynamically depend on the standard “Return To Party” field without native support.
The high-level steps are:
- Define a Part Line DFF segment named shipToContactId. Configure its value set to display the contact name from the query above.
- Extend the Part Lines screen using Visual Builder Studio:
- Create a new virtual field of type Object (Virtual Field) within the Fields section. Include partRequirementLinesDFF and ReturnFromPartyId inside it.
- Create a new Template for the Virtual Field and associate it with the new field in the Rule Set.
- Create a new fragment to be included inside the template.
- Define two input parameters in the fragment: customerPartyId (passed from the screen, pointing to Return To Party) and lineDFF (reference to the line’s DFF for returning the selected value).
- In the Fragment Designer, add two fields: an inputText (hidden, mapped to customerPartyId) and a Select Single (mapped to a Service Data Provider (SDP) with custom fetch).
- Implement the SDP custom fetch to execute the SQL query using a previously published query utility. Here’s an example of the custom fetch implementation in JavaScript (code below)
- Implement a value change listener on the inputText to clear the LOV when the Party changes
- Implement a value change listener on the Select Single to write back the selected value to the DFF:
- Map the values from the Template to the Fragment.
- Create a new virtual field of type Object (Virtual Field) within the Fields section. Include partRequirementLinesDFF and ReturnFromPartyId inside it.
Code Reference
define([
'vb/action/actionChain',
'vb/action/actions',
'vb/action/actionUtils',
], (
ActionChain,
Actions,
ActionUtils
) => {
'use strict';
class getShipToContactCustomFetch extends ActionChain {
/**
* @param {Object} context
* @param {Object} params
* @param {{hookHandler:'vb/RestHookHandler'}} params.configuration
*/
async run(context, { configuration }) {
const { $fragment, $base, $extension, $global, $constants, $variables } = context;
try {
const text = configuration.hookHandler.context.originalFetchOptions.filterCriterion?.text ?? "";
const keys = configuration.hookHandler.context.fetchOptions?.keys;
const hasKeys = keys?.size > 0;
const whereClausePart =
hasKeys
? `and hr.relationship_id = ${[...keys.values()]?.[0]}`
: `and (UPPER(hp.party_name) like UPPER('%${text}%') or UPPER(hcp.email_address) like UPPER('%${text}%'))`;
const queryString = `--
---------------------------------
-- getShipToContactCustomFetch --
---------------------------------
select to_char(hr.relationship_id) as "contactPartytId"
, hr.subject_id "customerPartyId"
, hp.party_name as "contactName"
, hcp.email_address as "emailAddress"
from hz_relationships hr
, hz_parties hp
, hz_contact_points hcp
where hr.relationship_code = 'CONTACT'
and hr.object_id = hp.party_id
and hr.status = 'A'
and hp.party_id = hcp.owner_table_id(+)
and hcp.owner_table_name(+) = 'HZ_PARTIES'
and hcp.contact_point_type(+) = 'EMAIL'
and hcp.primary_flag(+) = 'Y'
and hr.subject_id = ${$variables.customerPartyId}
${whereClausePart}
offset 0 rows fetch next 10 rows only`;
const queryResult = await Actions.callChain(context, {
chain: 'executeQuery',
params: {
queryName: 'getShipToContactCustomFetch',
queryString,
},
});
return {
"body": {
"items": queryResult
}
};
} catch (e) {
window.console.error(e);
await Actions.fireNotificationEvent(context, {
summary: "Failed to execute ship to contact custom fetch.",
message: e.message,
});
}
}
}
return getShipToContactCustomFetch;
});
This approach leverages VBS to create a dynamic, dependent LOV for the DFF segment, overcoming the limitations in Oracle Cloud ERP.
Conclusion
By using Visual Builder Studio to extend the Redwood UI, we can achieve field dependencies that mimic EBS functionality in Fusion Service CX. This solution ensures the DFF segment is context-aware and user-friendly.
Need assistance with Visual Builder Studio extensions, DFF configurations, or Fusion Service CX customizations? CID Software Solutions can help.
