Working With Reference Data In A Custom Extension

Introduction and Use-Case

This article provides a fictitious example of one possible option for integrating data from an external system with Oracle Sales Cloud. It’s intended to highlight particular considerations involved and provide an illustration.

Let’s setup a simple use-case. Our business users would like to see some financial performance data when dealing with their customers. This will help them make more informed decisions when managing customer account records. The financial data is held in a secondary proprietary accounting system.  The information required is summary-like data, and as such aggregation and calculation will be required on the source data. The source accounting data is quite complex and the number of records will most likely be high.

Users want this extra information available in-context, ideally inside the Account details page in Sales Cloud, and have asked for fast performance and no additional navigation. This extra financial data is for reference-use only, and no creation or update will ever be made to this data from Sales Cloud. Examples of what users would like to see include:

  • Largest Ever Spend
  • Average Spend
  • Average Spend Last Year
  • Number of Orders Last Year

Implementation Choices

One option might be to setup a live web service call to the secondary financial system to get the accounting data for display. As mentioned the existing system is proprietary and it’s unlikely the exact set of services needed is already available, meaning more development as well as some security concerns in adding API’s to this system. In addition, with a need for multiple calculated values on the page, each web service call and subsequent processing will have a performance overhead. As such this option may not be the best choice here.

A second option to get live data is to create a UI mash-up where a sub-tab is added to the Account object page layout. This has an iFrame region that embeds the user interface of the secondary financial system. This solution is also unsuitable because the need is just for a few data points and not actually financial system access for the sales users.

A final option is to get the results of specific queries on the financial system into Sales Cloud. In this use-case the users do not actually require live data, as such a query can be executed on a regular interval to refresh the data shown (e.g. daily). The query can simplify and denormalize the complex data structures in the financial system (e,g, headers-lines) into a single business object records. It can ensure only the appropriate data is included and that is it executed only by an authorized user. As such this implementation was chosen for use. Let’s look at how this was actually done.

Data Adding and Importing

Firstly a custom object – Invoice_c – is created in Sales Cloud to hold the imported data. In this example we have just a few fields for use in the calculations needed.

Note that the Customer field is a Dynamic Choice List on the Account object. This is because it will ensure the existing customer names are used, and it allows access to both the customer name String and the PartyId stored as the foreign key in the auto-generated Customer_Id_c field. 

The implementation does NOT include generating any ‘Pages’ for this custom object, as it’s just for reference data storage. We do not want anyone creating or updating this reference data through any methods outside our integration solution – like through a UI page.

In the initial population of a high volume of records into this object we use the File-based Loader process. This is described in detail in these articles. Incremental loads can be done using the SOAP or REST custom object web services. First a process queries the financial system to get new data and outputs the records as pre-prepared JSON files. The files are then used by a second process to perform HTTP POST requests to the REST resource at /salesApi/resources/latest/Invoice_c/ with MediaType as application/vnd.oracle.adf.resourceitem+json. An example single record payload is:

{ "RecordName" : "Invoice for April 17", "InvDate_c":"2017-04-18", 
"Customer_c":"Oracle1", "Paid_c":"Y", "OrderID_c":"12347", 
"TotalAmountUSD_c":"2200.50" } 

Now the data is there, we need to display it.

Field Creation

To meet users needs several Formula Fields are added to the Account object, and displayed in the Details page layout. The fields query the Invoice object records, do simple calculation, and display the result.

Formula fields do not actually store resulting values and re-calculate whenever the field is displayed on a page. As such a Field Group was used to reduce the UI clutter and ensure the very latest values are shown when the calculations fire when the group is expanded. Note that the default state for Field Groups is expanded, as such this was deselected in the creation screen so it is collapsed by default.

It should be noted that all formula fields do fire when the page is first rendered, even when inside a collapsed field group. As such performance must be optimized at all times.

Here is our Account page with the Financial Data group and values shown.

Formula Fields

The four fields shown above include the following groovy scripts.

Invoice Average (Lifetime). The script used here instantiates the Invoice_c view object and uses a simple ViewCriteria query based on matching the current customer Account and ensuring the invoices are paid. It then cycles through the results summing the invoice amount values. Finally it divides by the record count and presents in a rounded two decimal value. Note care is taken to check for null values where an Account has no invoices yet.

def amt =0.00 
def vo = newView('Invoice_c')
def thisAcctid = PartyId
vo.appendViewCriteria("Customer_Id_c = ${thisAcctid} AND Paid_c = 'Y'")
vo.executeQuery()
def num = vo.getEstimatedRowCount()
while(vo.hasNext()) {
  def curInv = vo.next()
  amt = amt + nvl(curInv.TotalAmountUSD_c,0)  
 }
if(num>0){
  def rslt= (amt / num)
  return Math.round(rslt * 100) / 100  
 }
return 0

Invoice Average (Last Year). This is mostly the same, with a condition to check the record InvDate matches the current year. It is not possible to convert the year
inside the viewCriteria string therefore it is done later.

def amt =0.00 
def count=0
def vo1 = newView('Invoice_c')
def thisAcctid = PartyId
vo1.appendViewCriteria("Customer_Id_c = ${thisAcctid} AND Paid_c = 'Y'")
vo1.executeQuery()
while(vo.hasNext()) {
 def curInv = vo.next()
 if(year(nvl(curInv.InvDate_c,9999)) == year(today())){
  count++
  amt = amt + nvl(curInv.TotalAmountUSD_c,0)  } 
 }
if(count>0){
 def rslt= (amt / count)
 return Math.round(rslt * 100) / 100
}
return 0

Largest Invoice (Lifetime). Using the same query we simply sort the resulting rows by TotalAmount / descending and get the data from the first record. Because we also (helpfully) return the OrderID_c value in the result of this script it was implemented as a Text Formula field.

def vo = newView('Invoice_c')
def thisAcctid = PartyId
vo.appendViewCriteria("Customer_Id_c = ${thisAcctid} AND Paid_c = 'Y'")
vo.setSortBy('TotalAmountUSD_c desc')
vo.executeQuery()
def curInv = vo.first()
if(curInv){
 def txt = nvl(curInv.TotalAmountUSD_c,0) + ' for OrderID ' 
  + nvl(curInv.OrderID_c,'none')
 return txt
}
return "None"

Invoice Count (Last Year). This simple script combines elements of those above, querying records and counting when the year values match.

def count=0
def vo1 = newView('Invoice_c')
def thisAcctid = PartyId
vo1.appendViewCriteria("Customer_Id_c = ${thisAcctid} AND Paid_c = 'Y'")
vo1.executeQuery()
while(vo1.hasNext()) {
 def curInv = vo1.next()
  if(year(nvl(curInv.InvDate_c,2000)) == year(today())){
   count++
  } 
}
return count

Extra Security

Whilst not mentioned in our use-case requirements, it is possible that not everyone should be able to see this financial data. As such field level data security could be applied in the formula field scripts. The simplest implementation is a preliminary groovy condition to check the users job role, and if not appropriate return an empty string. This technique is discussed here and an example might be:

def secCtx = adf.context.getSecurityContext()
def rslt = secCtx.isUserInRole('ZCA_SENIOR_ACCOUNT_MANAGER')
if(rslt==true){  
 ...scripts above... 
}
return ""


Powered by WPeMatico

This entry was posted in oracle.

About

You may also like...

Comments are closed.