About Match Type Views for Data Sources

A Match Type view is created for each approved match type. This view contains all the transactions across all data sources within the match type.

Match Type views simplify the creation of reports on all transactions for a particular match type. The Match Type view for a particular match type contains attributes from all the data sources within that match type. Use the SQL query of this view to obtain the column names for each data source. You can then create a report query that includes all transactions across all data sources within a particular match type. Running custom reports based on this report query enables you to filter and view data about all transactions for a match type in one place. See Generating Custom Reports in Administering Oracle Account Reconciliation.

A Match Type view can be joined with other tables. Report queries based on Match Type views are portable. If the match type configuration is same between two applications, the same report query will work in both applications even if the applications are not exact clones.

The Match Type view is created or updated when a match type is approved.

Structure of the Match Type View for Data Sources

The name of a Match Type view is its match type ID prefixed with "TM_". For example, the Match Type view name for a match type with ID POtoInv is TM_POtoInv. The maximum length for the view name is 30 bytes.

The maximum number of columns supported in a Match Type view is 300.

The Match Type view contains fixed system-defined attributes and data source attributes from all data sources within the match type. Column names must not exceed 30 bytes. The column name for a data source attribute is the data source attribute ID suffixed with an underscore and a letter that represents the data type of the column. See TM_<MATCH_TYPE_ID> in Tables and Views for Account Reconciliation for details about the columns in the Match Type view.

Note:

If the match type ID or the data source attribute ID contains a period (.), or curly brackets ('{' or '}'), this character is replaced with an underscore in the Match Type view. For example, if a match type ID is InterCompany1.2, the name of its Match Type view is TM_InterCompany1_2.

Examples: Queries for Reporting on Matched Transactions for Match Types

Example 1: Select all transactions for a specific data source

Use any of the following report queries to retrieve all transactions for the match type TM_GL_POS_BANK where the data source is 'Bank'.

SELECT * FROM "TM_GL POS Bank" TM_GL_POS_BANK WHERE TM_GL_POS_BANK.SOURCE = 'Bank';

SELECT * FROM "TM_GL POS Bank" TM_GL_POS_BANK WHERE TM_GL_POS_BANK.SOURCE = 'Bank' and 
TM_GL_POS_BANK.RECON_ID IN
(SELECT recon.recon_id FROM tm_recon recon, tm_recon_type recon_type, TM_DATA_SOURCE data_source 
     WHERE recon.recon_type_id = recon_type.recon_type_id AND recon.recon_type_id = data_source.recon_type_id 
     AND data_source.recon_type_id = recon_type.recon_type_id AND data_source.DATA_SOURCE_ID = 2009);

Example 2: Select unmatched transactions for a match type

The following report query returns all unmatched transactions for the match type TM_GL_POS_BANK across all data sources.

select * FROM "TM_GL POS Bank" TM_GL_POS_BANK where TM_GL_POS_BANK.match_status_enum is null or TM_GL_POS_BANK.match_status_enum = 6

Example 3: Select all matched transactions between two specified dates

The following report query returns all matched transactions for match type TM_GL_POS_BANK created between the dates. This result includes transactions from all data sources in the match type.

 SELECT * FROM "TM_GL POS Bank" TM_GL_POS_BANK LEFT OUTER JOIN TM_MATCH MATCH ON 
   (TM_GL_POS_BANK.RECON_ID = MATCH.RECON_ID AND MATCH.MATCH_ID = TM_GL_POS_BANK.MATCH_ID) 
   WHERE MATCH.CREATE_DATE > ~MATCH_DATE_LOW~ and MATCH.CREATE_DATE <= ~MATCH_DATE_HIGH~