Archive and Purge

Planning for Archive and Purge

This topic gives a description of the planning required to use Oracle Incentive Compensation's Archive and Purge process.

Archive and Purge Process

Archiving is the process of backing up the historical data that will be deleted during the purge process. During the archive process, data is moved from the main transactional tables to the backup tables. By default the OIC scripts only archives data to backup tables in the same CN schema and leaves the purge process up to the customer.

Purging is the process of freeing up space in the database or of deleting historical data that is not required by the system.

Archive and Purge OIC Periods is a single concurrent program performing the following processes sequentially (i) Archive, Ghost sub-ledger(ii) Purge. The concurrent program has the option to perform an audit (record count) before performing Archive and Purge process.

Project Checklist

Use the following project list when using the archive and purge functionality:

Verifying Data

Obtain sizing information about OIC tables by examining the sizing statistics of OIC tables and indexes to identify the top volume tables. Prioritize these tables for data purge and archive strategy. Use the following methods to verify your data:

Step 1 : Gather Statistics

Use procedures in the DBMS_STATS package to gather statistics for all CN tables and indexes, where CN is the default schema that owns OIC tables. If you created OIC tables in a different schema, please replace CN with your own schema name.

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('CN', DBMS_STATS.AUTO_SAMPLE_SIZE);

Step 2 : Examine Statistics

Run the following script to examine the size of OIC tables and indexes measured by bytes and blocks. The results are sorted by bytes and you can see the largest tables and indexes on the top:

SELECT * FROM DBA_SEGMENTS

WHERE OWNER='CN' ORDER BY BYTES DESC;

Run the following script to examine the size of OIC tables measured by number of rows and blocks, and sorted by blocks. The results can be sorted by number of rows also:

SELECT * FROM DBA_TABLES
WHERE OWNER='CN' ORDER BY BLOCKS DESC;

Run the following script to examine the size of indexes measured by number of distinct keys and number of rows. You can sort the results as you see appropriate to examine the space consumption of OIC indexes:

SELECT * FROM DBA_INDEXES
WHERE OWNER='CN';

Functional Prerequisites

Following are the functional prerequisites for OIC to use the archive and purge:

Ensure Additional Validation Before Permanently Closing a Period

Additional validation is needed prior to permanently closing a period in OIC. Fixes are not possible once a period is permanently closed. If the period is found to be partially unprocessed, it would be too late to make any corrections as the period would have already been permanently closed. Additional validation is required before permanently closing periods to guarantee periods are completely processed.

All selected periods are defined as completely processed when:

Ensure Critical Prerequisites

The following prerequisites are first validated before any purge activities as it is critical to perform these validations prior to permanently closing a period:

Understanding Volume Data Areas

Apart from understanding which CN tables represent the largest consumers of storage, it is necessary to understand what these tables do and whether it is correct or not to purge for your specific customer situation. Users may have customizations or extensions and these need to be considered before implementing OIC's archive and purge functionality.

OIC has different sets of transactional tables that can be archived and purged. There are four main sets of OIC tables that usually contain the largest volume of data and are therefore the top priorities for your ILM strategy and the top candidates for data purge and archive.

Following are the four main sets of large volume data OIC tables:

Focus on the safer large tables that can be archived and purge to limit impact and testing. Review the four main sets of OIC tables and archive and purge conservatively:

Transactional Data

the picture is described in the document text

Created in Notification stage of Collection of Transactions:

Notification is the first stage of collection, and is used to determinate which transactions are eligible for compensation. References to the orders or invoices selected by the notification query are inserted into the table cn_not_trx_all and cn_trx_lines_all.

Note: Tables above can be purged by period if your transactions belong to a permanently close period and end date compensation plans.

Created in Collection of Transactions after Notification stage:

Typical customers with 3rd party sources will insert transactions directly into CN_COMM_LINES_API_ALL

If using Open Collections, mapping definitions are used to populate CN_COMM_LINES_API_ALL

Note: Tables above can be purged by period if your transactions belong to a permanently close period and end date compensation plans.

Created with Transaction Import:

OIC's file based import functionality loads transactions into staging tables prior to inserting into their intended target tables. In the case of a transaction import the intended target is CN_COMM_LINES_API_ALL. These are stored in the following tables.

Note: Tables above can be purged by period if transactions belong to a permanently close period and end date compensation plans. Technically rows can be purged after import has been successfully performed. Note that Import functionality supports more than transactional import so the above tables will include metadata and data from those other imports. These can easily be filtered by type.

Created during Load:

Load picks up transactions from CN_COMMISSION_LINES_API_ALL and inserts them into CN_COMMISSION_HEADERS_ALL based on mapping definitions.

Note: Tables above can be purged by period if your transactions belong to a permanently close period and end date compensation plans.

Created in Calculation Phases:

For each CN_COMMISSION_HEADERS_ALL row, the phases of calculation create one or many CN_COMMISSION_LINES_ALL rows. CN_COMMISSION_LINES_ALL are where representative's transactions and earnings are recorded.

Note: Tables above can be purged by period if your transactions belong to a permanently close period and end date compensation plans.

Created in Payment Phase:

The OIC payment process creates payment transactions either at a plan element summary level (creating one payment transaction for every rep/period/payrun/plan/plan element or in detail for every earning, in CN_PAYMENT_TRANSACTIONS_ALL.

Note: Ensure all records to be purged have already been paid. Purge CN_PAYMENT_TRANSACTIONS_ALL by period using PAY_PERIOD_ID to identify records belonging to a period. Purge CN_PAYMENT_WORKSHEETS_ALL by joining to CN_PAYRUNS_ALL using PAYRUN_ID to get CN_PAYRUNS_ALL.PAY_PERIOD_ID to identify records to be purged. Purge CN_PAYRUNS_ALL using PAY_PERIOD_ID to identify records to be purged.

Test if you are using OIC payment processing.

Subledger Data

the picture is described in the document text

Each salesrep is assigned a plan will have a set of subledgers defined by salesrep, period and plan element. Subledgers are stored in the following tables:

Important: Do not purge CN_SRP_PERIODS_ALL. This will cause balance mismatch issues in Payment processing and prevent the creation of additional payment batches until resolved! As paysheets are created, OIC is designed to enforce data integrity across earnings, subledgers and payment transactions. In the case of the subledger table, the validation query goes against all past periods, so purging rows from CN_SRP_PERIODS_ALL will create a paysheet validation mismatch. Out of the box OIC uses Ghosting to reduce the size of these tables while maintaining necessary balances. Ghosting is discussed in a subsequent section.

Sales Rep Reference Data

the picture is described in the document text

Following are the tales for sales rep reference data:

Note: Tables above can be purged by period if your transactions belong to a permanently close period and end date compensation plans.

CN_SRP_PLAN_ASSIGNS_ALL does not have a PERIOD_ID; can only be purged for decommissioned compensation plans, and by COMP_PLAN_ID.

CN_SRP_QUOTA_ASSIGNS_ALL and CN_SRP_RATE_ASSIGNS_ALL and CN_SRP_QUOTA_RULES_ALL and CN_SRP_RULE_UPLIFTS_ALL - does not have a PERIOD_ID; can only be purged for decommissioned compensation plans, and by SRP_PLAN_ASSIGN_ID (a foreign key to CN_SRP_PLAN_ASSIGNS_ALL).

Processing Data

the picture is described in the document text

The data in these tables are neither transactional nor reference data and could be purged more frequently to improve processing speed of the system. Note that the calculation submission tables should be archived before they are purged, as these records need to be preserved for internal audit and regulatory compliance purposes.

Created with Calculation Submission:

Following tables store the history of calculation submission and processing:

Note: These tables are small in volume and are important to the business in terms of tracking who has done what in calc submission.

Created for Debugging:

The following tables store message logging for debugging purposes:

Note: Set the Debug profile to No, unless debugging purposes is needed. Inserting debug messages slows down the calculation process significantly and can grow the CN_PROCESS_AUDIT_LINES_ALL very quickly. If the debug messages are no longer needed, simply truncate these tables.

Created with Concurrent Calculation Submission and Parallelization:

All records from a given run of calculation can be identified by the same LOGICAL_BATCH_ID and all records from a given batch runner can be identified by the same PHYSICAL_BATCH_ID. Other OIC processes such as the open period process and transaction loading process also use this table to parallelize the workload across multiple physical batches. Following is the table:

CN_PROCESS_BATCHES_ALL

Note: This table can be truncated as it contains processing data only. If needed, please backup or archive the data before truncating the table.

Created with Incremental Calculation:

Incremental calculation is made possible by tracking every change or event in the system that affects calculation and notifying which transactions need to be recalculated due to an event or change. These are store in the following tables:

Note: Rows belonging to a period can be purged if your transactions belong to a permanently close period and end date compensation plans.

Technically CN_NOTIFY_LOG_ALL and CN_EVENT_LOG_ALL rows can be truncated after a sucessful full calculation for the full period and without archiving even as they support transaction processing only. CN_NOTIFY_LOG_ALL.STATUS is flipped from INCOMPLETE to COMPLETE after incremental calculation is performed and CN_NOTIFY_LOG_ALL.STATUS = COMPLETE can be purged.

Created with Collection, Classification and Formula Generation:

PL/SQL packages are generated when collection, classification and formulas are compiled. Following is the table:

CN_SOURCE_ALL - generated collection, classification, and formula packages (PL/SQL code)

Note: As these PL/SQL packages can be regenerated by the system, the data in this table can be truncated without archiving first but does not typically represent any large amount of disk space savings.

Understanding OIC Archive and Purge

This topic gives a description of the scope, architecture, table details, concurrent program, and other details to facilitate understanding of Oracle Incentive Compensation's Archive and Purge process.

Scope

The scope for OIC archive and purge encompasses:

The Archive and Purge architecture includes a set of OIC tables that will be archived and purged as well as a set of OIC tables that will only be purged. For the main OIC subledger table, CN_SRP_PERIODS_ALL, purging is not an option so this table is archived and ghosted.

The archive tables are mirrored against their OIC source tables, e.g., CN_ARC%. The data from each table that is to be purged is archived (appended) into a corresponding/associated archive table. The program purges all the data within tables (pre-identified for purging) based on Start and End periods.

Define Profile OIC: Purge Strategy for Subledger Tables

This profile defines the purge or ghosting strategy for the OIC subledger table, CN_SRP_PERIODS_ALL.This profile value is also used to define whether or not subledger tables are in scope when executing the concurrent request, Archive and Purge OIC Periods.

If the Archive and Purge program is executed without setting the profile "OIC: Purge Strage for Subledger Tables", then the subledger table is neither ghosted nor the sub-ledger and related tables are purged.

Profile Value Description
No Ghosting nor Subledger Purge Default. Archive purge will be restricted to non-subledger tables only. No subledger tables will be archive or purged.
Ghost into Last Purge Period without Looking Back Archive purge will aggregate CN_SRP_PERIODS_ALL records into the last purge period without looking back. Remaining subledger tables will be purged.
Ghost into Last Purge Period Looking Back Archive purge will aggregate all CN_SRP_PERIODS_ALL records looking back in time into the last purge period. Remaining subledger tables will be purged.
Ghost into Last Period of Year Looking Back Archive purge will aggregate all CN_SRP_PERIODS_ALL records looking back in time into the last period of each fiscal year (as defined by OIC GL Calendar) . Remaining subledger tables will be purged.

From a more business perspective, here are 5 key questions to help determine the correct profile value to use.

  1. Do you have confirmed performance degradation in Payment Approvals?

    Subledger tables should be part of your scope as the reduction of the size of CN_SRP_PERIODS_ALL will provide performance gains. So consider 1 of the 2 Ghosting profile values that Look Back.

  2. Do you have impending deadlines or are close to storage limits?

    To reduce scope consider the profile value, No Ghosting nor Subledger Purge.

  3. Do you want annual subledger records?

    Set profile value, Ghost into Last Period of Year Looking Back.

  4. Do you need more than a 12X reduction (assuming monthly periods) in subledger records?

    Use performance testing to confirm you need more than a 12X reduction and set profile value, Ghost into Last Purge Period Looking Back.

  5. Do you want quarterly or semi-annual subledger records and have no payment approval performance degradation?

    Set profile, Ghost into Last Purge Periods without Looking Back, in conjunction with the frequency you run archive purge.

Customer Use Cases for Archive Purge

  1. Reduce Storage Requirements, No Payment Approval Performance Degradation, Minimize Data Continuity Risk

    Archive purge non-subledger tables only with No Ghosting nor Subledger Purge

  2. Reduced Storage Requirements, Confirmed Payment Approval Performance Degradation

    Archive purge non-subledger and subledger tables

  1. Weigh value of quarterly or annual Subledger records versus more than 12X reduction (assuming monthly periods) in subledger records

    If annual subledger records then Ghost into Last Period of Year Looking Back

  2. Benchmark performance; if > 12X reduction needed (assuming monthly periods) then Ghost into Last Purge Period Looking Back

    If quarterly subledger records then Ghost into Last Purge Period without Looking Back with quarterly Archive Purge frequency (not recommended if you have confirmed payment approval performance degradation because only 3X reduction)

Tables Archived and Purged

Transaction data from the following tables will be available to the OIC Archive and Purge program to archive and purge:

Tables Purged Only

Transaction data from the following tables will be available to the OIC Archive and Purge program to purge only. These tables will not be archived:

These are tables are used for internal OIC processing do not require archiving prior to purge.

Additional Information: Transaction data once purged cannot be recovered.

Subledger Table Archived and Ghosted

Transaction data from the following table will be available to the OIC Archive and Purge program to archive and ghost.

  1. Why are subledgers treated differently?

    The subledger table creates a record for every period, paysheet, commission lines, and compensation plan. As subledger tables grow significantly, users can experience performance degradation in payment approval. Paysheet creation performs a validation that aggregates CN_SRP_PERIODS_ALL records for a person all the way back in time, therefore you cannot purge.

  2. What is ghosting?

    Goal of ghosting is to reduce storage requirements while retaining necessary balances. For a rep in a plan+plan element, we aggregate balances across multiple records into a single 'ghost' record in CN_SRP_PERIODS.

    A series of individual records of CN_SRP_PERIODS table are aggregated into a single record with the aggregated totals to retain the three way balance check in payment processing - earnings, subledgers, and payments. Note: All total values are aggregated except for balanced columns.

Architecture of OIC Archive and Purge

Oracle Incentive Compensation's Archive and Purge architecture consists of:

Audit Tables

There are two audit tables, CN_ARC_AUDIT_ALL and CN_ARC_AUDIT_DESC_ALL. These tables are used to capture the concurrent program Archive and Purge OIC periods process details when run with option Archive and Purge.

The audit tables do not store any information when the Archive and Purge OIC Periods concurrent program is run with the Audit Only mode. The row count is reported in the concurrent program view log.

Oracle Incentive Compensation maintains archive and purge processing details in the following audit tables:

These two tables are in the default EBS tablespace.

Archive Tables and Tablespace

CN_ARC% tables are archive tables and are used to archive the associated OIC CN Tables:

For each OIC table to be archived there is a corresponding archive table that is a mirror image of the source table. For instance, CN_COMM_LINES_API will have a corresponding CN_ARC_COMM_LINES_API archive table. For OIC tables that are directly purged, no corresponding archive table is necessary.

All archive tables will reside in a separate tablespace called APPS_TS_ARCHIVE. This will assist DBAs with maintenance of archived data and provide separation from non-archive tables.

Parallelized Processing

Users must inform and discuss with database administrators. OIC purge archive is parallelized. It is delivered with the expectation that parallelism settings will differ for each customer and that the degree of parallelism used is computed by the database optimizer.

This is achieved via parallel hints which will override the PARALLEL_DEGREE_POLICY initialization parameter as OIC archive purge is a very specific use case and should not follow a general database parallelization policy.

Note: It is important to note that if any parallel restrictions are violated, then the hint is ignored

Phases of Archive and Purge Processing

Following are the phases of the Archive and Purge process:

Restart Logic

In case of errors during processing which causes the submitted archive and purge request to fail, users can correct the condition that caused the failure. A re-start scenario for any Operating Unit, continues if either the ARCHIVE_STATUS='N' or the PURGE_STATUS='N' from the prior attempt.

The Archive and Purge mode expects the same parameters of the Operating Unit as the prior failed attempt, so that it can continue from the failed state. If the parameters must match, for the resubmitted archive and purge to continue.

If a restart is determined, OIC will gather necessary variables from audit tables and restart processing from the prior failed state.

Users can view the log of the failed request, verify that the table cn_arc_audit_all has one record with either archive_status=N or purge_status=N (note that there should never be more than one), and resubmit the concurrent request that failed with the same parameters. The Archive and Purge OIC Periods concurrent program recognizes the previous failed execution and resumes processing from where it left off

Validate Eligibility of Periods

Archive and purge is packaged as a concurrent request set driven by Operating Unit and End Period, which must be validated for eligibility. This eligibility is based on:

Pre-requisite validation for the selected periods include:

Failure to meet any of the prerequisites will result in an aborted concurrent program. Validation failures are captured in the concurrent program view log file.

Audit Phase

The purpose of the audit phase is to determine impacted tables and expected row counts to be archived and purged in advance of each actual execution.

The audit tables (CN_ARC_AUDIT_ALL and CN_ARC_AUDIT_DESC_ALL) do not store any information when the Archive and Purge OIC periods concurrent program is run with the Audit Only mode. The row count is reported in the concurrent program view log only.

Archive Phase

The purpose of the archive phase is to archive rows from the CN_% source tables to the corresponding CN_ARC_% tables. All tables are archived before the start of the purge phase. A unique Id (archive_purge_id) is generated at the initial phase of archive for every new submission of program (the ID is re-used when a failed attempt is re-run).

The audit table, CN_AR_AUDIT_DESC_ALL, records the start and the end time of each table archived. After a successful archive, the ARCHIVE_STATUS attribute for each archived table is 'Y'.

Purge Phase

The purpose of the purge phase is to purge rows from the CN_% source tables.

The audit table, CN_AR_AUDIT_DESC_ALL, records the start and end time of each table purged. After a successful purge, the PURGE_STATUS attribute for each purged table is 'Y'.

Confirmation Phase

The last phase is the confirmation. The success status of the Archive and Purge process is reflected in the audit header CN_AR_AUDIT_ALL with ARCHIVE_STATUS = 'Y' and PURGE_STATUS='Y'. The validation status of the archive row count and the purge row count is reported in the program view log. The probability of archive row count matching with the purge row count is very high, in case if it does not match but the ARCHIVE_STATUS='Y' and PURGE_STATUS='Y', there would not be a chance to re-run the program with the same parameters.

Archive and Purge OIC Periods Concurrent Program

With the Incentive Compensation Administrator responsibility, select Submit Requests to get to Concurrent manager. Search for the Archive and Purge OIC Periods concurrent program. Provide parameters requested and submit the request.

Parameters:

The start period is system determined based on the last successful archive purge and is not user enterable.

The archive_status=Y and purge_status=Y determines the successful completion of the program.

If the submitted concurrent request fails, users must correct the condition that caused the failure. The cause of the failure can be viewed in the log of the failed request. Verify that the table cn_arc_audit_all has one record with either archive_status=N or purge_status=N (note that there should never be more than one).

The failed concurrent request must be resubmitted with the same parameters. The Archive and Purge OIC Periods concurrent program recognizes the previous failed execution and resumes processing from where it left off.

See: Archive and Purge, Oracle Incentive Compensation User's Guide.

Public API

Users after testing OIC's Archive and Purge, can leverage this functionality outside the context of a concurrent request. A public API called CN_PURGE_TABLES_PUB.archive_purge_cn_tables is available for this purpose.

Tips and Troubleshooting

Following are some tips to troubleshoot when using Archive and Purge: