Configuring credit exposure calculations

Exposure is calculated for all relationships on a daily basis (batch job). Q2 Origination users can also calculate exposure on-demand by selecting the Calculate Exposure button on the Account screen. You can configure the calculation based on your business parameters, leveraging Q2 Origination objects and fields and defining evaluation expressions around these.

Dynamic queries

A critical elements of defining and performing exposure calculations in Q2 Origination is the configuration of Dynamic Queries. Dynamic Queries are the foundations of the credit exposure calculations defined in the evaluation expression configured on each exposure calculation. Dynamic Queries also consists of additional configuration that define the construction of the query for the exposure calculation, including:

• Dynamic Query Data - This configuration contains the details and conditions of the query, and enables financial institutions to leverage aggregate functions, configure query conditions, define aliases that allow admins to leverage the query data in other queries, and join other queries.

• Dynamic Query Parameters - These parameters enable financial institutions to define parameters to be used within a query. For example, to leverage the current Account ID as a parameter in a query, you can set the Account ID context in the parameter configuration.

After you create a dynamic query, you can specify these details. 

Prerequisites

None.

Steps

Perform the following steps to configure the dynamic query:

1. Log in to your Salesforce account.

2. Create a custom tab on the Dynamic Queries object. Select the tab, and then New.

3. In the New Dynamic Query page, specify the From Object. This indicates the source object of the query.

4. Specify a Query Name. This is the alias of the query to be used in evaluation expressions.

5. In the Where Clause, select a lookup to the query criteria.

6. Specify a Group By parameter that determines the field grouping.

7. Specify the Record Limit. This is the number of records that can be returned by the query.

8. Specify the Order By option. This can be ASC or DESC for ascending or descending order respectively.

9. Select Save.

Specifying the dynamic query data

In the Dynamic Query Detail page: 

1. Scroll to the Dynamic Query Data section, and select New Dynamic Query Data. The Parent Query displays the current query's name. You can change this if required.

2. Specify the Alias to be used if the dynamic query data is to be used in other queries.

3. Select the Filter Type.

   Filter Type	Description
   Select_Field	required for aggregate functions
   Expire	required for query conditions

4. Select the Aggregate Function to be used in an aggregate query. Values include Sum, Avg, Count, Min, Max, and Count_Distinct. 

5. Specify the Field Name. This is the field used in the aggregate function or expression 

6. Select the Value. This is the parameter used in the expression.

7. Select the Expression Left and Expression Right. These configurations enable financial institutions to join additional queries/filters to the current query configuration.

8. Select the arithmetic Operator to be used in the expression.

9. Select Save.

Specifying the dynamic query parameters

In the Dynamic Query Detail page:

1. Scroll to the Dynamic Query Parameters section, and select New Dynamic Query Parameter.

2. Specify a Parameter Name. The Parent Query displays the current query's name. You can change this if required.

3. Select the parameter Type. For example, Constant or Sub-query.

   Parameter Type	Description
   Context Value	This field indicates that the current (active context) value will be used
   Constant	This field indicates a specific value
   Function
   Existing Query	indicates that an existing query will be used (and points to the parameter query data lookup)
   Date/Datetime	indicates a date value

4. Specify the comparison Value to be used in the expression

5. Select the Parameter Query Data. This is the query data you specified in the preceding set of steps.

6. Select Save.

Calculation Sets- You can use calculation sets to configure the computation formulas, and the records to be fetched at the run time for the computations. The order of configuration and execution are opposite to each other. You need to define dynamic query first, then expression assignments, and then calculation set. When a calculation set is evaluated, then all the linked expression assignment records are fetched, parsed to get associated dynamic query records. Then the code fetches the records based on the dynamic queries, and expression assignments are evaluated based on these values. (Ref: Configure calculation sets)

Based on the evaluation Expression, System would first look for the Query ($BorrowerContracts) and would analyze/compute the query. The computed value gets stored in the Object (Account -> clcommon__Direct_Exposure__c) as per the field Alias (PrincipalBalance).

Prerequisites to calculate Credit Exposure

• Calculate Exposure works in classic mode only (There is an existing issue with Salesforce Lightning)

• Add calculateExposure Button and VisualForce Page (Create New Section & Add: AccountRelationDashboard) to Account’s UI

  • Relationship: VisualForce Pages = AccountRelationDashboard, AccountRelationDashboardWrapper

  • Exposure: Check Accounts -> Buttons -> Calculate Exposure

• Check if the following objects are available: Interface Contract, Interface Party and Interface Bank Account

• Create a tab for the above objects

• Make sure that the fields as displayed below are added and values have been provided.

  • Interface Contract Details

  • Interface Party Details

  • Interface Bank Details

Proposed (Direct or Indirect) Exposure

The exposure value due to the loans currently in origination.

Dynamic Query - Direct Proposed Exposure

SELECT SUM(genesis__Loan_Amount__c) FROM genesis__Applications__c WHERE ID in (SELECT genesis__Application__c FROM clcommon__Party__c where clcommon__Type__c = BORROWER AND genesis__Party_Name__c = 'ABC')

Dynamic Query - Indirect Proposed Exposure

SELECT SUM(clcommon__Contingent_Liability__c) FROM clcommon__Party__c WHERE clcommon__Type__r.Name = GUARANTOR AND clcommon__Account__r.Name = 'ABC'

Use Case:

DELL Financial Services, subsidiary of DELL Inc., wants to avail loans and have created multiple loan applications in Q2 Origination while DELL Inc. already have few loan applications created in Q2 Origination as given below.

The credit exposure at Account Level for DELL Inc. and DELL Finance Services would be as shown below.

Direct Exposure

All the loan balances where the entity is the primary borrower.

Dynamic Query - Direct Exposure

SELECT SUM (staging__Interface_Contract__r.staging__ Principal_Balance__c) FROM staging__Interface_Party__c WHERE staging__Account__r.Name = 'ABC' AND staging__Type__r.Name = BORROWER

Indirect Exposure

All the loan balances where the entity is associated in some capacity with the loan or lease. For example, as a co-borrower or guarantor. 

Dynamic Query - Indirect Exposure

SELECT SUM staging__Interface_Contract__r.staging__ Principal_Balance__c) FROM staging__Interface_Party__c WHERE staging__Account__r.Name = 'ABC' AND staging__Type__r.Name = GUARANTOR

Use Case:

On top of loan applications in Q2 Origination, DELL Inc & DELL Financial Services have already availed loans from Banks or other Financial Entities. The details are as given below.

The credit exposure at Account Level for DELL Inc. and DELL Finance Services would be as shown below.

Direct & Indirect Exposure (Relationship)

Based on Relationships & Relationships (Related Entity), the system calculates the direct and Indirect exposure. As per above the use case, DELL Financial Services should be created as a Subsidiary to DELL Inc. in accounts and the result would be: