DQM & TCA
Written by Anil Passi   
Sunday, 11 February 2007
In this article I will touch base upon the data quality management feature of TCA. This article also contains a link to a DQM presentation that will compliment the Q & A in this article. A combination of this article and thepowerpoint presentation should help you understand the basics of DQM [ Data Quality Management ] in Oracle TCA.
Is DQM a part of TCA?
Yes it is, the setup can be done using responsibility named "Trading Community Manager".

What are the setup steps for DQM?
Note:
These steps will get clearer once you finish reading this article + PowerPoint Presentation
Here are the steps :-
1. Identify the Attributes that you wish to use for Search and Scoring.
2. Define custom attributes for those columns on which you would like to either search or score, but those columns are not in TCA tables.
3. Assign transformation to each column. Define custom transformation rules if need be, by using PL/SQL
4. Decide which transformed values are used for Searching and which transformed values are to be used for [both] Searching and/or just Scoring
The attribute transformations that you wish to search upon, will have "Acquisition" checkbox set to Yes.
5. Define your match rules. You can define as many match rules
6. In Match rules, you specify how the users search criteria[values being used by users] are transformed prior to acquisition.
7. Define scoring/ranking rules
8. Run the staging process.


Give me some examples where DQM is used?

Lets say your organization has 50000 employees and a person named john smith gets recruited for a job. HRMS team must know whether this John Smith has had any kind of interaction with the organization in past. Ideally if John Smith has had any kind of interaction with the company in past, then there must exist an entry for this record in TCA. In this case, HR must ensure that HRMS record can be made to point to existing TCA record. By doing so a 360 degree view of the employee be maintained in your organization.


So DQM is about being able to identify a duplicate entry in your system.
Correct, and this I made possible by applying some transformations as you will see in the presentation.


Is DQM only applicable at party level in TCA?
No way, DQM can be made to work at party address level & contact level too.


Isn't finding duplicates as simple as matching the social security number or national insurance number?
Correct, but say if your payables department wants to pay expenses to a third party for their interview travel expenses, then payables team will not have access to SSN or "NI Number". In this case, they rely upon other attributes of the person to find duplicates.


Ah, it means DQM will not be used merely by HRMS department or the Receivables department?
Certainly not, in an efficient organization, any department that registers a party into oracle apps must have access to DQM functionality.


But what if Payables team wants to use DQM, but with different set of search and ranking criteria altogether?
This is a very common requirement. Hence in this case Oracle lets you define multiple "Match Rules" in DQM.


Fine, we can define different Match Rules, but how will we enforce Payables users to use say "Match Rule 1" and HRMS users to use say "Match Rule 2"?

You can differentiate such requirements by creating different DQM search responsibilities. For each such responsibility you will assign profile option "DQM Match Rule for Search" to respective value.
In case you do not wish to have different responsibilities, though recommended, you can set this profile option at user level too.


But DQM searches for duplication in TCA tables, however "NI Number" and SSN [Social Security] are stored in HRMS tables. How will DQM search data in other modules?
You can define custom attributes in DQM. Custom attribute is nothing but a pseudo column which uses pl/sql to return the value,in this case from per_people_f


Does DQM search data within TCA tables?
Well, after defining rules for DQM setup, you run a concurrent program named "DQM Staging Program". This process de-normalises TCA data into staging tables. Transformed values for columns are stored in staging table. This will be clear when you go through the PowerPoint presentation.


What are the staging tables?
There are a list staging tables where DQM Transformed data is stored. Each transformed value resides in a different column. The transformed values used for Acquisition are indexed by Oracle.
The list of tables are
HZ_STAGED_CONTACTS
HZ_STAGED_CONTACT_POINTS
HZ_STAGED_PARTIES
HZ_STAGED_PARTY_SITES



What is event "oracle.apps.ar.hz.DQM.realtimesync" which keeps on firing when employee records or the TCA records are modified?
As you know, the TCA data is transformed and de-normalized into staging tables. Any changes to the TCA data must update the DQM staging tables, so that changes can be searched upon in DQM. The subscription of this event reads the pending entries in staging interface table HZ_DQM_SYNC_INTERFACE and transfers them to DQM staging tables after having applied the transformations.

NOTE: Although the event name is realtimesync, Oracle has disabled the realtime option for this event subscription. Hence synchronization with DQM staging tables happens in deferred mode only.


Here is the link to DQM presentation
Link for DQM and TCA presentation

Any questions? please ask !



Comments (6)add
...
written by Shasikkumar , February 18, 2007
Hi Anil,

First of all thanks for the good work you are doing thru ur blog.

I am working as a developer in oracle receivables. Please let me know what are all the important topics to be covered in my module. And also other important modules in financials such as GL, AP for a receivables developer.

Apart from this - what all should be my focus - as techno-functional consultant for oracle receivables.


Thanks in anticipation

S
...
written by Anil Passi , February 19, 2007
Hi ShashiKumar

As a AR technofunctional, you should be able to setup the Receivables module for a new org_id and raise invoices with accounting based on auto accounts setup, accept receipts for invoices. Also you should be able to raise credit memos.

A basic understanding of TCA is very important too.

From the techie side, RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL are the key tables where transactions and transaction lines are stored for all possible transaction types in AR.
Also, for accounting of transactions, you need to have a look at RA_CUST_TRX_LINE_GL_DIST_ALL

When receipts are reconciled, AR_PAYMENT_SCHEDULES_ALL will be updated with a value in column AMOUNT_DUE_REMAINING to indicate amount for which payment is yet to be received from the customer.

thanks,
anil
...
written by SANDEEP , February 23, 2007
Hi Anil,

This is the first time i was seeing your blog, Found your articles especially on AR interesting. I am very new to oracle financials just started my carrer in this. I had a question which any newbie has.

I want to learn more stuff on AR both in technical and functional point of view. I would like your valuable suggestions to me wher should i start from , what books or materials are good for oracle financials. I have always believed learning oracle fiancials is not just technical, it should always be learned combined with fucntional concepts then only one learns and understands the subject well.

so it would be great if you could share your experience or advice with me . I hope you will reply to this.

Thanks

regards
Sandeep
...
written by Ashutosh , May 22, 2007
Hi,
I am implementing DQM to merge more than 10K parties with their duplicates. I am facing some issues and would appreciate your suggestion.
1. i have created custom match rule, including the one which uses attribute21 of hz_parties. When I run a batch with these compiled custom match rules, batch is not picking any data.
When same thing is done using SAMPLE : IDENTICAL PERSON match rule then it picks 4 parties correclty.
Please suggest whcy custom match rules are not working.
2. I want match rule to allow Automerge, how to set it up. I have even tried setting Automerge threshhold value for a match rule but still when I create a batch using this match rule then automerge optin is not checked.
3. Performance of Merge program is pathetic, do you know about any possible solution for this. i am already in consultation with Oracle on this but they are not of much help.
Thanks,
Ashutosh

Please reply at earliest.
...
written by Anil Passi , June 10, 2007
Hi Sharmila

For basics of TCA as a business entity, please visit
http://oracle.anilpassi.com/future-of-tca-fusion-release-12-2.html

Creating Party record using API is very straightforward and there are various Notes on Metalink. SImply search metalink for TCA API Examples

Thanks,
Anil Passi
Ar
written by kraghu , January 07, 2008

hi anil,

your site is helpful to everyone. can u tell me which form is largest form in orapps.
You must be logged in to a comment. Please register if you do not have an account yet.

busy