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:
Log in to your Salesforce account.
Create a custom tab on the Dynamic Queries object. Select the tab, and then New.
In the New Dynamic Query page, specify the From Object. This indicates the source object of the query.
Specify a Query Name. This is the alias of the query to be used in evaluation expressions.
In the Where Clause, select a lookup to the query criteria.
Specify a Group By parameter that determines the field grouping.
Specify the Record Limit. This is the number of records that can be returned by the query.
Specify the Order By option. This can be ASC or DESC for ascending or descending order respectively.
Select Save.
Specifying the dynamic query data
In the Dynamic Query Detail page:
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.
Specify the Alias to be used if the dynamic query data is to be used in other queries.
Select the Filter Type.
Filter TypeDescriptionSelect_Field required for aggregate functions Expire required for query conditions Select the Aggregate Function to be used in an aggregate query. Values include Sum, Avg, Count, Min, Max, and Count_Distinct.
Specify the Field Name. This is the field used in the aggregate function or expression
Select the Value. This is the parameter used in the expression.
Select the Expression Left and Expression Right. These configurations enable financial institutions to join additional queries/filters to the current query configuration.
Select the arithmetic Operator to be used in the expression.
Select Save.
Specifying the dynamic query parameters
In the Dynamic Query Detail page:
Scroll to the Dynamic Query Parameters section, and select New Dynamic Query Parameter.
Specify a Parameter Name. The Parent Query displays the current query's name. You can change this if required.
Select the parameter Type. For example, Constant or Sub-query.
Parameter TypeDescriptionContext Value indicates that the current (active context) value will be used Constant 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 Specify the comparison Value to be used in the expression
Select the Parameter Query Data. This is the query data you specified in the preceding set of steps.
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
SOQL Query
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
SOQL Query
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.
App# | Loan Amount | Account Name | Borrower | Guarantor |
---|---|---|---|---|
1 | 150000 | DELL Financial Services | DELL Financial Services | |
2 | 100000 | DELL Financial Services | DELL Financial Services | |
3 | 12000 | DELL Financial Services | DELL Financial Services | |
4 | 30000 | DELL Financial Services | DELL Financial Services, DELL Inc | |
5 | 30000 | DELL Financial Services | DELL Financial Services | DELL Inc |
6 | 23000 | DELL Inc | DELL Inc | DELL Financial Services |
The credit exposure at Account Level for DELL Inc. and DELL Finance Services would be as shown below.
Borrower | Direct Proposed Exposure | Indirect Proposed Exposure |
---|---|---|
DELL Financial Services | 322000 | 23000 |
DELL Inc. | 53000 | 30000 |
Direct Exposure
All the loan balances where the entity is the primary borrower.
Dynamic Query - Direct Exposure
SOQL Query
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
SOQL Query
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.
Contract | Principal Balance | Account Name | Borrower | Guarantor |
---|---|---|---|---|
Contract 1 | 5000 | DELL Financial Services | DELL Financial Services | |
Contract 2 | 7000 | DELL Financial Services | DELL Financial Services | DELL Inc |
Contract 3 | 35000 | DELL Inc | DELL Inc | DELL Financial Services |
The credit exposure at Account Level for DELL Inc. and DELL Finance Services would be as shown below.
Borrower | Direct Exposure | Indirect Exposure |
---|---|---|
DELL Financial Services | 12000 | 35000 |
DELL Inc. | 35000 | 7000 |
Borrower | Direct Proposed Exposure | Indirect Proposed Exposure |
DELL Financial Services | 334000 | 58000 |
DELL Inc. | 88000 | 37000 |
Direct & Indirect proposed Exposure is inclusive of Direct & Indirect Exposure respectively.
For Direct & Indirect Exposure, it is a must to have Interface Contract & Interface Party added in the system.
User can also add Bank account details through Interface Bank Account, the added details will get reflected under Financial Accounts in Accounts. To find net exposure, the calculation set can be updated to reduce the Bank Balance from the Credit Exposure.
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:
Direct Exposure (Relationship) | Indirect Exposure (Relationship) | |
---|---|---|
DELL Financial Services | 12000 | 35000 |
DELL Inc. | 35000 | 7000 |