In this post let’s try to understand the Transaction Matching module data model. Unlike other Oracle EPM products, ARCS (Account Reconciliation Cloud Service) uses relational database. So we have tables and views and it is pretty easy to create custom reports using BI Publisher. This opens up lot of possibilities. If you are interested to learn more about creating custom reports in ARCS, check out my YouTube tutorial series.
For most updated list of tables and views, you can check out the Oracle Documentation here.
Predefined Tables
There are 19 pre-defined tables at the time of writing this blog. Here are a few that I would like to discuss, since these are the most commonly used tables in the implementations that I have completed so far.
- TM_RECON_TYPE : This table stores the match types you have defined in ARCS.
- TM_MATCH_RULE : All the match rules are available in this table.
- TM_MATCH_RULE_COND : The match rule conditions are stored in this table.
- TM_DATA_SOURCE : Details of the data source you define as part of the match type are stored in this table.
- TM_DATA_SOURCE_ATTRIB : The different attributes you define for the data sources are captured in this table.
- TM_MATCH : This table captures the transaction matching matches and match statuses. The MATCH_STATUS_ENUM column can be used to identify the Match Status. If column is NULL, it is Un Matched.
Here is the meaning of other values populated in the column — 1=SUGGESTED_MATCH, 2=CONFIRMED_MATCH, 3=CONFIRMED_ADJUST, 4=SUGGESTED_ADJUST, 6=SUPPORTED, 7=BOTH
Apart from the above tables there are others that store adjustment, balances and support related details. You can check those out in the Oracle Documentation.
Dynamic Tables
For each data source you define a dynamic table is created. The table name format is TM_TRANS_<NUMBER>. For e.g, TM_TRANS_2001. To identify the table name for a particular data source you have to query the TM_DATA_SOURCE table. Use the below query to get the Table names for the data sources defined for a particular Match Type.
SELECT
TRT.NAME MATCH_TYPE_NAME
, TDS.NAME DATA_SOURCE_NAME
, TDS.DYNAMIC_TABLE_NAME
FROM
TM_DATA_SOURCE TDS
, TM_RECON_TYPE TRT
WHERE
TDS.RECON_TYPE_ID = TRT.RECON_TYPE_ID
AND UPPER(TRT.NAME) = UPPER(<Match Type Name>)
Once you check out the table for a particular data source you will realize that the column names for the attributes have a format like C_<NUMBER>. For e.g., C_1234 could be the column name that stores the Transaction Date attribute you have defined for the data source. Here is a SQL that gives you the attribute name and the corresponding column name in the data source table.
SELECT
TDS.NAME DATA_SOURCE_NAME
, TDS.DYNAMIC_TABLE_NAME
, 'C_'||TDSA.DATA_SOURCE_ATTRIB_ID COLUMN_NAME
FROM
TM_DATA_SOURCE TDS
, TM_DATA_SOURCE_ATTRIB TDSA
WHERE
TDS.RECON_TYPE_ID = TRT.RECON_TYPE_ID
AND TDSA.DATA_SOURCE_ID = TDS.DATA_SOURCE_ID
AND UPPER(TDS.NAME) = UPPER(<Data Source Name>)
In my next YouTube video I will explaining the Data Source and Data Source Attribute Tables in more detail. Let me know your feedback. Keep learning!!