1.50.3 Transformation for FLAT_MAPPING (Computed Facts)

This topic describes about the Transformation for FLAT_MAPPING (Computed Facts).

This topic describes the behavior when transformation is enabled for a FLAT_MAPPING entry. In standard flat mapping, the destination fact value is populated directly from the transported source table row. In transformation-enabled flat mapping, the destination fact value is derived by executing a configured SQL query and storing the computed result in the destination facts table.
When is transformation applied?
Transformation is applied only when all of the following conditions are met:
  • The mapping Transformation Configuration Type is FLAT_MAPPING.
  • A Transformation Class is configured for the mapping.
How is the fact value derived?
When transformation is enabled:
  1. The system identifies the destination fact from the flat mapping (Value field).

    Example: cutoffLiabBal, todayLiabBal. either by:

  2. The system resolves a transformation query using the destination fact identifier in the

    format: <EntityName>.<factName>

    Example: Party.cutoffLiabBal.

  3. The transformation class executes the resolved query and obtains the computed result. (<ENTITY>_FACTS),
  4. The computed result is persisted as the destination fact value in the destination facts table
    • inserting a new fact row when the fact does not exist for the entity instance, or
    • updating the existing fact row when the fact is already present.
Seed table for transformation queries

The transformation query is maintained in a seed table which maps a destination fact identifier to the SQL query to be executed.

Table name : "CMNCORE"."TRANSPORT_FACT_QUERY_MAPPING"

Table 3-116 Transport Fact Query Mapping - Field Description

Field Description
Fact Specifies the destination fact identifier in the format <Entity>.<factName>. Example: Party.cutoffLiabBal.
Query Specifies the SQL query used to derive the fact value. The query must return a single computed value (for example, an aggregate amount) which is stored as the fact value during augmentation.
Example Query : 
SELECT SUM(JSON_VALUE(a.FACT_DATA, '$.cutofBalance' RETURNING NUMBER)) 
AS total_amount FROM PARTY_FACTS p, ACCOUNT_FACTS a 
WHERE a.PARTY_ID = :CUSTOMER_NO and p.PARTY_ID = a.PARTY_ID GROUP BY a.PARTY_ID

Note:

  • The query can contain bind parameters (for example : CUSTOMER_NO). These parameters are populated during batch execution using the relevant values from the transported source row and mapping configuration.
  • The transformation class is system-provided and is displayed as a read-only value in the UI when transformation is enabled.

Parent topic: Track Receivable Priority Code

Parent topic: Transport Fact Mapping