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

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: Data Management, "Using PeopleSoft Data Archive Manager"

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

  • CC Activity Archive

  • CC Journals Archive

  • CC Ledgers Archive

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: 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

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 pre-archive 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 pre-archive 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.

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.

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

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

Database Table

History Table

LEDGER_KK

LEDGER_KK_H

LEDGER_KK_FS

LEDGER_KK_FS_H

CC Journals Archive History Tables

Database Table

History Table

KK_BUDGET_HDR

KK_BUDGET_HDR_H

KK_BUDGET_LN

KK_BUDGET_LN_H

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: 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 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.

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.

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.

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

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: Data Management, "Using PeopleSoft Data Archive Manager"

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.