PeopleSoft Commitment Control 9.1 PeopleBook

Archiving for Commitment Control

This chapter provides an overview of archiving for Oracle's PeopleSoft Commitment Control and discusses how to:

Archive commitment control records and tables.
Restore archived records and tables.

Understanding Archiving for Commitment Control

This section discusses:

Delivered archive procedures for commitment control.
Preprocess for the CC Activity Archive.
History tables.
Archive Object.
Archive Query.
Archive Template
Archive Results
Performance

Prerequisites

Because commitment control archiving procedures are extensions of the PeopleSoft PeopleTools Data Archive Manager functionality and because the commitment control archive procedures are totally dependant on that functionality, it is assumed that you have read the PeopleTools archiving documentation and thoroughly understand the terminology, the functionality, and have developed an archive strategy before reading this documentation and proceeding to archive commitment control activity, ledgers, and journals.

See PeopleTools PeopleBook: Data Management, "Using PeopleSoft Data Archive Manager"

Delivered Archive Procedures for Commitment Control

Three archive procedures are delivered for the archiving of commitment control records and tables:

CC Activity Archive
CC Journals Archive
CC Ledgers Archive

The following lists these procedures in the order that an archive is logically done and describes the tables archived with descriptions of the selection criteria and rules governing the archiving of commitment control records and tables:

Archive Procedure	Tables Archived	Prompts and Inclusion or Exclusion Rules
CC Activity Archive: The Archive Preprocess Application Engine process (KK_AR_AC_PRE) must be run to update the Process Instance of the KK_SOURCE_HDR for rows that are to be archived, based on the Archive Query selection criteria, which can be user-defined, and the implicit rules described for the commitment control activity tables. No post-process is necessary.	KK_SOURCE_HDR KK_SOURCE_LN KK_ACTIVITY_LOG KK_ACT_LOG_FS KK_TRANS_LOG KK_REFERENCED KK_EXCPTN_TBL KK_OVERRIDE_TBL KK_LIQUIDATION Product KK Source Header Tables (see list below)	Archive Query prompt selection criteria: Business unit from and to. Transaction date. Inclusion and Exclusion Rules: The Archive Preprocess Application Engine program (KK_AR_AC_PRE) must be used to select source transactions and their related transactions that are to be archived. Source documents must be fully liquidated to be available for archive. Other user-defined rules can be imposed in the associated Archive Query.
Commitment Control Journals:The KK_BUDGET_HDR is the base table and the KK_BUDGET_LN is the nonbase table. No preprocess or post-process are required.	KK_BUDGET_HDR KK_BUDGET_LN	Archive Query prompt selection criteria: Business unit from and to. Ledger group from and to. Journal Date (on or before). Inclusion and Exclusion Rules (user-configurable through the Archive Queries), for example, you require that all journals have a status of posted or unposted.
Commitment Control Ledgers: No preprocess or post-process is necessary. The LEDGER_KK rows that are to be archived are based on the Archive Query selection criteria, which can be user-defined.	LEDGER_KK LEDGER_KK_FS	Archive Query prompt selection criteria: Business unit from and to. Ledger group from and to. Fiscal year (data on or before the specified year). Inclusion and Exclusion Rules (all are user-configurable through the Archive Queries): Related commitment control activity tables must already be archived (as addressed in delivered Query KK_ARCHIVE_LEDGER_EXCL2). Budget must be closed (as addressed in delivered Query KK_ARCHIVE_LEDGER_EXCL1).

These commitment control archive procedures are delivered as sample data with all the necessary history tables, preprocesses, archive object definitions, query definitions, and template definitions necessary to archive commitment control activity, ledgers, and journals.

If other archiving needs should arise, you can modify or create new objects using the Data Archive Manager and Application Designer as described in PeopleSoft PeopleTools documentation.

See PeopleTools PeopleBook: Data Administration Tools, "Using PeopleSoft Data Archive Manager"

The following lists the product KK Source Header Table name for each Source Transaction.

Source Transaction	Product KK Source Header Table
AP_VOUCHER	KK_SHDR_APVCHR
AP_ACCT_LN	KK_SHDR_APACTLN
AP_ACCTDSE	KK_SHDR_APACTLN
AP_ACTDSEC	KK_SHDR_APACTLN
AP_VCHR_NP	KK_SHDR_APVCHR
AR_MISCPAY	KK_SHDR_ARMPAY
AR_REVEST	KK_SHDR_ARRVEST
BI_INVOICE	KK_SHDR_BIINV
CM_TRNXTN	KK_SHDR_CMTRN
EX_EXCLOSE	KK_SHDR_EXSHEET
EX_EXSHEET	KK_SHDR_EXSHEET
EX_TRVAUTH	KK_SHDR_EXTRATH
GENERIC	KK_SHDR_GEN
GL_BD_JRNL	KK_SHDR_GLJRNL
GL_JOURNAL	KK_SHDR_GLJRNL
GM_FA	KK_SHDR_GMFA
GM_FA_UPG	KK_SHDR_GMFA
HR_PAYROLL	KK_SHDR_HRPAY
PC_BUDGET	KK_SHDR_PCJRNL
PC_JOURNAL	KK_SHDR_PCJRNL
PO_POENC	KK_SHDR_POENC
PO_POENCNP	KK_SHDR_POENC
PO_PROCARD	KK_SHDR_POCARD
PO_RAENC	KK_SHDR_PORA
PO_RAEXP	KK_SHDR_PORA
REQ_PRECNP	KK_SHDR_REQ
PEQ_PREENC	KK_SHDR_REQ

Preprocess for the CC Activity Archive

The delivered CC Activity Archive Preprocess Application Engine program (KK_AR_AC_PRE) is unique to CC Activity Archive. The preprocess is necessary because related records to be archived from the activity tables must be selected by source transaction type and transaction activity from the overall accumulation of to-date activity that is stored in the activity tables.

You include the preprocess in the archive template and it is run as part of the overall commitment control activity archive procedure.

No other prearchive or post-archive programs are delivered and no other pre- or post-processing is needed for the delivered commitment control archive procedures.

However, if you have special requirements, you can create additional prearchive or post-archive application engine processing programs and included them in the archiving of your database records and tables by including them in the appropriate archive template.

History Tables

Commitment control delivers history tables as system data to be used with the delivered commitment control archive procedures.

You can change or configure history tables to correspond to any special requirements or configuration of your system using the PeopleSoft Application Designer and by following the instructions located in the PeopleTools documentation.

However, history tables are by definition copies of your database tables. To successfully archive and restore records and tables to and from the history tables, the history tables must mirror your specific database tables. This requirement must be considered before reconfiguring or customizing such things as ChartFields, fields, and tables. If you reconfigure or customize your tables or upgrade you system, refer to the PeopleSoft upgrade documentation for information dealing with these situations.

Archiving produces place copies of database information in the history tables and at that point, the data exists in the database and the history tables. You can remove the archived information from the database tables and remove the archived data from the history tables when you no longer need it by using the delete and the remove from history features available on the archive run control.

CC Activity Archive History Tables

The CC Activity Archive Preprocess Application Engine program (KK_AR_AC_PRE) selects source transaction types and their related transactions according to the parameters you specify and copies the selected records to their corresponding history tables.

The following lists the database tables from which records are archived for commitment control activity and the corresponding history tables:

Database Table	History Table
KK_ACTIVITY_LOG	KK_ACTVTY_LOG_H
KK_ACT_LOG_FS	KK_ACT_LOG_FS_H
KK_EXCPTN_TBL	KK_EXCEPTION_H
KK_LIQUIDATION	KK_LIQUIDATN_H
KK_OVERRIDE_TBL	KK_OVERRIDE_H
KK_REFERENCED	KK_REFERENCED_H
KK_SOURCE_HDR	KK_SOURCE_HDR_H
KK_SOURCE_LN	KK_SOURCE_LN_H
KK_TRANS_LOG	KK_TRANS_LOG_H
KK_ACT_LOG_FS	KK_ACT_LOG_FS_H

The following lists the history tables for the product KK source header tables:

Source Transaction	Description	Source Header Sibling	History Table
AP_VOUCHER	Voucher	KK_SHDR_APVCHR	KK_SHDR_APVCH_H
AP_ACCT_LN	Voucher (Gain, Loss, Close)	KK_SHDR_APACTLN	KK_SHDR_APALN_H
AP_ACCTDSE	Voucher - Discount Earned	KK_SHDR_APACTLN	KK_SHDR_APALN_H
AP_ACTDSEC	Voucher - Disc Earn - PO close	KK_SHDR_APACTLN	KK_SHDR_APALN_H
AP_VCHR_NP	Voucher (Non-Prorated Item)	KK_SHDR_APVCHR	KK_SHDR_APVCH_H
AR_MISCPAY	Miscellaneous Payment	KK_SHDR_ARMPAY	KK_SHDR_ARMPY_H
AR_REVEST	Revenue Estimate	KK_SHDR_ARRVEST	KK_SHDR_ARRVE_H
BI_INVOICE	Billing Invoice	KK_SHDR_BIINV	KK_SHDR_BIINV_H
CM_TRNXTN	Cost Management Transaction	KK_SHDR_CMTRN	KK_SHDR_CMTRN_H
EX_EXCLOSE	Closed Expense Reports	KK_SHDR_EXSHEET	KK_SHDR_EXSH_H
EX_EXSHEET	Expense Sheet	KK_SHDR_EXSHEET	KK_SHDR_EXSH_H
EX_TRVAUTH	Travel Authorization	KK_SHDR_EXTRATH	KK_SHDR_EXTAU_H
GENERIC	Generic Transactions	KK_SHDR_GEN	KK_SHDR_GEN_H
GL_BD_JRNL	GL Budget Entry	KK_SHDR_GLBDJNL	KK_SHDR_GLBJ_H
GL_JOURNAL	General Ledger Journal	KK_SHDR_GLJRNL	KK_SHDR_GLJ_H
GM_FA	Facilities and Administration	KK_SHDR_GMFA	KK_SHDR_GMFA_H
GM_FA_UPG	Facilities and Administration	KK_SHDR_GMFA	KK_SHDR_GMFA_H
HR_PAYROLL	Payroll Transaction	KK_SHDR_HRPAY	KK_SHDR_HRPAY_H
PC_BUDGET	Project Budget	KK_SHDR_PCJRNL	KK_SHDR_PCJNL_H
PC_JOURNAL	Project Journal	KK_SHDR_PCJRNL	KK_SHDR_PCJNL_H
PO_POENC	Purchase Order	KK_SHDR_POENC	KK_SHDR_POENC_H
PO_POENCNP	PO (NonProrate Item)	KK_SHDR_POENC	KK_SHDR_POENC_H
PO_PROCARD	Procurement Card	KK_SHDR_POCARD	KK_SHDR_POCRD_H
PO_RAENC	Receipt Accruals - Encumbrance	KK_SHDR_PORA	KK_SHDR_PORA_H
PO_RAEXP	Receipt Accruals - Expense	KK_SHDR_PORA	KK_SHDR_PORA_H
REQ_PRECNP	Requisition (Non-Prorated)	KK_SHDR_REQ	KK_SHDR_REQ_H
REQ_PREENC	Purchase Requisition	KK_SHDR_REQ	KK_SHDR_REQ_H
EX_ACENC	Expenses Accrual Encumbrance	KK_SHDR_EXACENC	KK_SHDR_EXACE_H
EX_ACEXP	Expense Sheet Accrual Expense	KK_SHDR_EXACENC	KK_SHDR_EXACE_H
AP_ACENC	Voucher Accrual Encumbrance	KK_SHDR_APACENC	KK_SHDR_APENC_H
AP_ACEXP	Voucher Accrual Expense	KK_SHDR_APACENC	KK_SHDR_APENC_H

CC Ledger Archive History Tables

The following lists the database ledger tables that are archived for commitment control and the corresponding history tables:

Database Table	History Table
LEDGER_KK	LEDGER_KK_H
LEDGER_KK_FS	LEDGER_KK_FS_H

CC Journals Archive History Tables

The following lists the journal records that are archived for commitment control and the corresponding history tables:

Database Table	History Table
KK_BUDGET_HDR	KK_BUDGET_HDR_H
KK_BUDGET_LN	KK_BUDGET_LN_H

Archive Object

An archive object definition identifies the tables that contain the data to be archived and the history tables that are to be updated for each table.

It is important to understand the base table and nonbase table relationship. PeopleTools documentation explains in detail the concept and relationship of base to nonbase tables.

See PeopleTools PeopleBook: Data Management, "Managing Archive Objects," Understanding the Base Table and Non-base Tables

Commitment control delivers the following archive object definitions as system data:

Archive Object	Base Table	Description
KK_ARCHIVE_ACTIVITY	KK_SOURCE_HDR	Used in the archive of commitment control activity tables. See the list of CC Activity Archive history tables for a complete listing of the base table and the nonbase tables.
KK_ARCHIVE_LEDGER	LEDGER_KK	Used in the archive of commitment control ledger tables. See the list of CC Ledger Archive history tables for a listing of the base and associated nonbase tables.
KK_ARCHIVE_JOURNAL	KK_BUDGET_HDR	Used in the archive of commitment control journal tables. See the list of CC Journal Archive history tables for a listing of the base and associated nonbase tables.

Archive Query

Archive query definitions define the selection criteria to archive data from the database tables.

Archive Queries are defined only for base tables because nonbase tables are archived based on the archived data of the related base table.

Data Archive Manager uses PeopleSoft Query to define selection criteria for the base table of the base archive object. For example, you might choose to archive all rows in KK_BUDGET_HDR where business unit is FRA01.

There are many possible permutations of prompt and inclusion or exclusion logic. Always modify delivered queries or create new archive queries using the information provided in PeopleTools documentation. Do not use the delivered queries as is in your production environment because they are for examples only.

The following archive query definitions are delivered with the delivered archive procedures as system data.

Query Name	Description of Prompt and Exclusion Logic
KK_ARCHIVE_ACTIVITY	This activity archive query is created with the Prompt of Process Instance, Business Unit From/To, and Transaction Date. No exclusion logic is included. Note. For the Archive Query used with CC Activity Archive, the first Prompt field must be Process Instance. The preprocess populates it with a value for the subsequent archive process to be used.
KK_ARCHIVE_ACTIVITY_EXCL1	This activity archive query has the same Prompt as above, but it joins the PO_HDR table to exclude purchase orders with statuses other than completed or canceled. Note. For the Archive Query used with CC Activity Archive, the first Prompt field has must be Process Instance. The preprocess populates it with a value for the subsequent archive process to use.
KK_ARCHIVE_LEDGER	This ledger archive query has the Prompt of Business Unit From/To, Ledger Group From/To, and Fiscal Year. No exclusion logic is included.
KK_ARCHIVE_LEDGER_EXCL1	This ledger archive query has the same Prompt as above, but only include budgets in the archive for which you have run the budget close process.
KK_ARCHIVE_LEDGER_EXCL2	This ledger archive query has the same Prompt as above, but only includes budgets for which the commitment control activity is archived.
KK_ARCHIVE_JOURNAL	This journal archive is defined with the Prompt of Business Unit From/To, Ledger Group From/To, and Journal Date. No exclusion is included.

Archive Template

When you archive commitment control records and tables, you can select one of the delivered system data templates or if necessary, create a new archive template definition. Each archive template definition includes one or more archive object definitions and corresponding archive query definitions. You can also specify in the archive template any preprocessing or post-processing application engine processes.

The following archive template definitions are delivered as system data to be used with the delivered commitment control archive procedures:

Archive Template	Archive Object	Pre or Post Processes
KKACTVTY	KK_ARCHIVE_ACTIVITY	KK_AR_AC_PRE (preprocess)
KKLEDGER	KK_ARCHIVE_LEDGER	None
KKBDJRNL	KK_ARCHIVE_JOURNAL	None

Archive Results

Use the Audit Archive page provided by PeopleTools Data Archive Manager to review archive result.

You can also create your own queries against the history tables to verify archive results.

See PeopleTools PeopleBook: Data Management, "Using PeopleSoft Data Archive Manager"

Performance

Parallel processing can be done by archiving multiple business units in separate ranges in the run controls.

While you can create additional logic in the Archive Queries it could slow down the performance. Test your queries for performance before implementing them in the archive process.

Archiving Commitment Control Records and Tables

This section provides an overview of the archive process flow and lists the pages used to archive data.

To archive commitment control records and tables, use the Archive Data to History (PSARCHRUNCNTL), Define Query Bind Variables (PSARCHRUNQRYBND), and the Audit Archiving (PSARCHIVEAUDIT) components.

Understanding the Archive Process Flow

You use the Data Archive Manager to perform an archive.

Use the same run control page for each step in the process except for auditing the archive selection.

Perform these tasks to archive commitment control records and tables.

Archive to the history tables by selecting the archive template and query on the Archive Data to History page for the type of commitment control archive procedure that you want to accomplish.

By clicking Reset Query Find Variables on the Define Query Bind Variables page you can reset criteria.

Note. You can run the archive process multiple times to create various what-if scenarios based on the archive date.
(Optional) Review the number of rows that were selected for archiving for each table on the Audit Archiving page.

This page lists the number of rows that were selected for archiving for each archive ID (template), archive batch number, and table combination.

Note. This page displays data only if you select the Audit Row Count check box on the Archive Data to History page.
Delete the archived records and tables from the active database tables for a specific archive ID (template) and batch number on the Archive Data to History page.
(Optional) You can rollback archived data from the history tables for a specific archive ID (template) and batch number using the Archive Data to History page if, for example, you delete data from records and tables from the active database in error.
Off load data in the history tables to another database or to flat files.
Remove data from the history tables for a specific archive ID (template) and batch number using the Archive Data to History page.

The data in the history tables is keyed by the archive ID (template) and batch number for each archive run. If you want to see data in the tables selected to be archive, you can run a query on the history tables

Important! If you decide to run the archive selection process again, for example, because you did not select the correct data, you must first use the Data Archive Manager option to remove the previous data from the history tables.

Pages Used to Archive Commitment Control Records and Tables

Page Name	Definition Name	Navigation	Usage
Archive Data to History	PSARCHRUNCNTL	PeopleTools, Data Archive Manager, Archive Data to History	Select an archive template and query and select the action you want to take in the archiving process.
Define Query Bind Variables	PSARCHRUNQRYBND	Click Define Binds on the Archive Data to History page.	Click the Reset Query Bind Variables button and enter the values that are used to select the data to be archived.
Audit Archiving	PSARCHIVEAUDIT	PeopleTools, Data Archive Manager, Audit Archiving	View the number of rows selected to be archived for each table for a specific archive run.

Restoring Commitment Control Records and Tables

This section provides an overview of data restoration and lists the page used to restore data.

To restore commitment control records and tables, use the Archive Data to History component (PSARCHRUNCNTL).

Understanding Restoring Commitment Control Records and Tables

The Selection option on the Archive Data to History page places a copy of the data located in the active source records and tables in the history tables and the Delete option removes the archived data from the active source database tables. If you delete data from the source tables in error, you can restore the data from the history tables by using the Rollback option on the Archive Data to History page.

You cannot restore selected parts of the data from the history tables because the process restores all data for a specific archive ID (template) and process run. For example, you cannot specify that you want to restore data for a particular transaction to the source tables.

While you can restore data to the source tables from the history tables, the compatible structure of the source and history tables must be maintained over time to successfully restore data.

Important! After you delete data from the history tables, you cannot restore the data to the active source database tables unless you maintain flat files as backup and manually restore the data to the history tables from the flat file and then to the active database tables from the history tables.

Page Used to Restore Commitment Control Records and Tables

Page Name	Definition Name	Navigation	Usage
Archive Data to History	PSARCHRUNCNTL	PeopleTools, Data Archive Manager, Archive Data to History	Select an archive ID (template) and the batch number for the process run of the data that you want to restore.