This topic gives a description of the planning required to use Oracle Incentive Compensation's 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.
Use the following project list when using the archive and purge functionality:
Verify your data.
Ensure functional Oracle Incentive Compensation prerequisites are met.
Understand areas with volume data in Oracle Incentive Compensation.
Consider partitioning strategies.
Permanently close periods.
Understand and set the profile option - OIC: Purge Strategy for Subledger Tables.
Plan data archive and purge with database assistance.
Always test and document.
Audit before archive and purge.
Validate data integrity.
Archive and purge.
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:
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);
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';
Following are the functional prerequisites for OIC to use the archive and purge:
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:
Should a sales representative be on a payrun, then archive periods in question should not have unposted commission lines.
You can ensure there are no active plan elements with Group by Interval formula (embedded or otherwise).
You can ensure compensation plans included in periods to be purged have end dates.
The following prerequisites are first validated before any purge activities as it is critical to perform these validations prior to permanently closing a period:
Before permanently closing a period for pay by transaction mode customers, ensure no unposted transactions in CN_COMMISSION_LINES_ALL for purge periods
Before permanently closing a period, ensure no active plan elements with Group by Interval formula for purge periods.
Before permanently closing a period, ensure compensation plans included in periods to be purged have end dates.
Before purging periods ensure the periods are fully processed and permanently closed.
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:
Transaction Tables which contain incentive compensation transaction and payment records.
Subledger Tables which contain calculation results summarized by compensation setup data such as plan elements and participants or payees.
Reference Tables which contain large volume of reference data if the number of participants or the number of plan elements, or the number of revenue classes, or the number of periods in a year is high.
Processing Tables which contain a large volume of information that facilitates OIC transaction processing.
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:
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.
CN_TRX_LINES_ALL - used by seeded EBS OM/AR/AIA integrations
CN_TRX_SALES_LINES_ALL - used by seeded EBS OM/AR/AIA integrations
CN_TRX_ALL
CN_NOT_TRX_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.
CN_IMP_HEADERS_ALL - import metadata
CN_IMP_LINES_ALL - import data
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.
CN_PAYMENT_TRANSACTIONS_ALL
CN_PAYMENT_WORKSHEETS_ALL
CN_PAYRUNS_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.
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:
CN_SRP_PERIOD_QUOTAS_ALL - stores calculation results summarized by salesrep, plan, plan element & period
CN_SRP_PERIOD_QUOTA_RC_ALL - further summarizes calculation results by adding rev class as another summary criterion in addition to the 4 summary criteria used in CN_SRP_PERIOD_QUOTAS_ALL
CN_SRP_PERIOD_QUOTAS_EXT_ALL - only used for multiple input expressions or multi-dimensional rate tables; to track PTD and ITD values corresponding to input expressions other than the first one
CN_SRP_PERIODS_ALL - tracks payment information summarized mainly by payee, plan element and period
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.
Following are the tales for sales rep reference data:
CN_SRP_PLAN_ASSIGNS_ALL - plan assignments to sales rep
CN_SRP_QUOTA_ASSIGNS_ALL - comp plan + plan element assignments to sales rep
CN_SRP_RATE_ASSIGNS_ALL - personalized rate tables for a sales rep
CN_SRP_QUOTA_RULES_ALL - revenue class based compensation parameters at sales rep level
CN_SRP_RULE_UPLIFTS_ALL - revenue class based compensation parameters at sales rep level
CN_SRP_PAYEE_ASSIGNS_ALL - payee assignment for a sales rep
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).
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:
CN_CALC_SUBMISSION_BATCHES_ALL
CN_CALC_SUBMISSION_ENTRIES_ALL
CN_CALC_SUB_QUOTAS_ALL
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:
CN_PROCESS_AUDITS_ALL
CN_PROCESS_AUDITS_LINES_ALL
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:
CN_NOTIFY_LOG_ALL- events triggering incremental calculation
CN_EVENT_LOG_ALL - events triggering incremental calculation
CN_SRP_INTEL_PERIODS_ALL - salesreps periods requiring incremental calculation
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.
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.
The scope for OIC archive and purge encompasses:
a set of OIC tables that will be archived and purged.
a set of OIC tables that will only be purged.
a set of OIC subledger tables that will be archive and purged.
a single OIC subledger table (CN_SRP_PERIODS_ALL) that will be archived and ghosted.
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.
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.
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.
Do you have impending deadlines or are close to storage limits?
To reduce scope consider the profile value, No Ghosting nor Subledger Purge.
Do you want annual subledger records?
Set profile value, Ghost into Last Period of Year Looking Back.
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.
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.
Reduce Storage Requirements, No Payment Approval Performance Degradation, Minimize Data Continuity Risk
Archive purge non-subledger tables only with No Ghosting nor Subledger Purge
Reduced Storage Requirements, Confirmed Payment Approval Performance Degradation
Archive purge non-subledger and subledger tables
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
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)
Transaction data from the following tables will be available to the OIC Archive and Purge program to archive and purge:
CN_COMMISSION_HEADERS_ALL
CN_COMM_LINES_API_ALL
CN_COMMISSION_LINES_ALL
CN_INVOICE_CHANGES_ALL
CN_LEDGER_JOURNAL_ENTRIES_ALL
CN_NOT_TRX_ALL
CN_PAY_APPROVAL_FLOW_ALL
CN_PAYMENT_API_ALL
CN_PAYMENT_WORKSHEETS_ALL
CN_PAYMENT_TRANSACTIONS_ALL
CN_PAYRUNS_ALL
CN_POSTING_DETAILS_ALL
CN_POSTING_DETAILS_SUM_ALL
CN_PROCESS_BATCHES_ALL
CN_TRX_ALL
CN_TRX_LINES_ALL
CN_TRX_SALES_LINES_ALL
CN_WORKSHEET_BONUSES_ALL
CN_WORKSHEET_QG_DTLS_ALL
CN_SRP_PER_QUOTA_RC_ALL
CN_SRP_PERIOD_QUOTAS_ALL
CN_SRP_PERIOD_QUOTAS_EXT_ALL
CN_SRP_PERIOD_PAYEES_ALL
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:
CN_IMP_HEADERS
CN_IMP_LINES
CN_NOTIFY_LOG_ALL
CN_PROCESS_AUDITS_ALL
CN_PROCESS_AUDIT_LINES_ALL
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.
Transaction data from the following table will be available to the OIC Archive and Purge program to archive and ghost.
CN_SRP_PERIODS_ALL
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.
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.
Oracle Incentive Compensation's Archive and Purge architecture consists of:
a profile, OIC: Purge Strategy for Subledger Tables
a set of audit tables, e.g., CN_ARC_AUDIT%
a set of archive tables mirrored against their OIC source tables, e.g., CN_ARC%
a tablespace for the set of archive tables, APPS_TS_ARCHIVE
parallelized processing
a concurrent program, Archive and Purge OIC Periods
a public API, CN_PURGE_TABLES_PUB.archive_purge_cn_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:
CN_ARC_AUDIT_ALL: records/stores the details of every execution of the Archive and Purge OIC Periods concurrent program
CN_ARC_AUDIT_DESC_ALL: stores the audit details of all the tables involved with archive and purge. It stores a row for every table for every execution
These two tables are in the default EBS tablespace.
CN_ARC% tables are archive tables and are used to archive the associated OIC CN Tables:
CN_ARC_COMMISSION_HEADERS
CN_ARC_COMMISSION_LINES
CN_ARC_COMM_LINES_API
CN_ARC_INVOICE_CHANGES
CN_ARC_LEDGER_JOURNAL_ENTRIES
CN_ARC_NOT_TRX
CN_ARC_PAYMENT_API
CN_ARC_PAY_APPROVAL_FLOW
CN_ARC_PAYRUNS
CN_ARC_PAYMENT_TRANSACTIONS
CN_ARC_PAYMENT_WORKSHEETS
CN_ARC_POSTING_DETAILS
CN_ARC_POSTING_DETAILS_SUM
CN_ARC_PROCESS_BATCHES
CN_ARC_TRX
CN_ARC_TRX_LINES
CN_ARC_TRX_SALES_LINES
CN_ARC_WORKSHEET_BONUSES
CN_ARC_WORKSHEET_QG_DTLS
CN_ARC_SRP_PER_QUOTA_RC_ALL
CN_ARC_SRP_PERIOD_QUOTAS_ALL
CN_ARC_SRP_PERIOD_QUOTAS_EXT_ALL
CN_ARC_SRP_PERIOD_PAYEES_ALL
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.
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
Following are the phases of the Archive and Purge process:
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
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:
The start period is determined by OIC. For the very first time, it is the start period of the OIC Application calendar. For successive runs, it is the next period of the last successful of the Archive and Purge OIC Periods run.
All periods between the start and end period must be Permanently Closed.
There are no un-purged periods prior to the start period.
Pre-requisite validation for the selected periods include:
For pay by transaction mode Customers, ensure no unposted transactions in CN_COMMISSION_LINES_ALL
Ensure no active plan elements with Group by Interval formula (embedded or otherwise).
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.
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.
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'.
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'.
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.
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:
Organization: This is an operating unit the user has access to.
End Period: This is end period for archive purge.
Run Mode: Audit Only or Archive and Purge. Select Audit Only to collect archive purge statistics for analysis only. Select Archive and Purge to execute archive and purge.
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.
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.
Following are some tips to troubleshoot when using Archive and Purge:
If the Archive and Purge OIC Periods concurrent request fails in Audit Only mode:
This failure must be due to the non-availability of the resource for executing the process or other issue with the tech-stack. Re-run after resolving issues with the System Administrator for your resource issues. The program does not generate any new process id (archive_purge_id) nor creates an entry into the cn_arc_audit_all table.
If the Archive and Purge OIC Periods concurrent request fails in Archive and Purge mode:
Prior to the Archive process: Determine the cause from the concurrent program view log, resolve the issue with the system administrator and re-run the concurrent program.
During an Archive process : Determine the cause from the concurrent program view log, resolve the issue with the system administrator and re-run the concurrent program. The concurrent program expects the same parameters (end-date) of the prior failure run. The program determines the table that failed to archive from the cn_arc_audit_all table, it deletes the data from the corresponding arc table for the program selected period. It performs the archive for the program selected period and resumes the rest of the processes, last table to archive (CN_PROCESS_BATCHES_ALL).
During the Purge process : Determine the cause from the concurrent program view log, resolve the issue with the system administrator and re-run the concurrent program. The concurrent program expects the same parameters (end-date) of the prior failure run. The program determines the table that failed to purge from the cn_arc_audit_all table. The predicate that was used to purge during the prior run will be re-executed and the program resumes the rest of the processes, until the last table is purged(CN_NOTIFY_LOG_ALL).
Number of periods that can be submitted for archive and purge differs depending on users which is why users must:
Use the Audit Only mode to gather statistics to analyze the amount of information to be archived and purged. Perform a test to determine the number of periods depending on transaction volumes in various periods, available resources and time windows.
Never run Archive and Purge OIC Periods concurrent program in production before thorough testing.