There are many reasons for separating data – each has its own challenges and business considerations. The first step in the process is to determine the business requirement, then find the relevant data, determine the criteria for filtering the data out, and determine what happens to the filtered data. Suppose for example, that you want to separate the data because your company has decided to sell off a division. Division is a segment in your accounting flexfield, so maybe you just determine the segment value of the division to be sold off, identify all the code combinations that have the value for that division, and then find all the tables that have the relevant code combination ID. Oh, you say, that’s easy. I can just write a select statement with a where clause (for division number 100) and my Oracle® Applications will be separated. Not so fast. It’s not as easy as it sounds because all the transactions cannot be filtered easily based on the above condition.
- Not all the transaction tables carry the code combinations related to the division. E.g. you may have invoices with the division code but the related payments do not have the same.
- You may have invoices with multiple invoice lines with separate division codes.
- You may have an invoice line with distributions assigned to multiple divisions.
- You may have payments that pay invoices related to different divisions.
- In-flight transactions may pose challenges. E.g. checks may have been entered but not cleared.
- Taking part of a transaction may affect the balancing segment. Adjustments need to be made so that the debits and credits are equal for each balancing segment value and the retained earnings are not affected.
- How are you going to handle the history?
What about creating an archive? Surely, that is easy. For this example, assume that you want to archive all transactions that are more than 3 years old. All you have to do is pull all the transactions by date. Was that date created? Date modified? Date closed? Date of the invoice? Date of the payment? Date the check cleared? What about the in-process transactions? How long should they stay open? When you post in summary, and then try to go back to find the detail, how will you reconcile? If you post in detail, what adjusting entries will you need to make so that the year balances correctly and still maintains your audit trail for Sarbanes Oxley? What happens if you upgrade your applications and then need to restore the archived data? Will you need to go back and reopen all the periods and repost? How can you be sure that you aren’t creating double entries and what about all those adjusting entries you made to balance – will you need to reverse them? Maybe you are keeping the archived transactions in a separate Oracle Applications instance (the same version as the time of the archive). Is that version still supported by Oracle? Do your current users know how to navigate through the older version? Subsetting the data has similar issues:
- When you are selecting a portion of the data, you need to make sure that you are selecting all the related data – configuration, master, and transaction, or the integrity of the database may be compromised.
- You will probably want both open and closed transactions so that you can test for a complete business cycle (purchase to pay, recruit to retire, etc.) That may include workflow elements like approvals that are not directly related to the filter criteria.
- Security rules, data groups, organization units, current responsibilities, enhancements and bolt-ons will have to be evaluated to see if they need to be revised to accommodate the test cycle.
The takeaway here is that separating the data for any reason is a business issue – not just a technology issue. The business must identify the requirements, do the analysis, and be an integral part of the solution.
