PeopleSoft General Ledger 9.1 PeopleBook

Archiving for Ledgers and Journals

This chapter provides an overview of archiving for ledgers and journals and discusses how to:

Archive ledgers and journals.
Restore archived ledgers and journals.

Understanding Archiving for Ledgers and Journals

This section discusses:

Archive query.
History tables.
Archive objects.
Preprocesses and postprocesses.
Archive templates.
Archive results.
Performance.

Because archiving procedures are extensions of the PeopleSoft PeopleTools Data Archive Manager functionality and because the general ledger 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 ledgers and journals.

PeopleSoft General Ledger archiving makes use of queries to provide selection criteria and the prompts for that selection criteria. For example, your query can specify business units and dates for ledger and journal data to be archived when you run the archive process. Archiving also uses preprocesses to enforce certain data conditions before the archive process is run, such as not allowing the archiving of journals with open items or the archiving of ledger data in an open period. You can also use postprocesses to produce such things as a listing of the data archived.

History tables are provided to receive journal and ledger data. Archive objects identify a base record with its associated records and provides for the association of the actual tables with the history tables.

An archive template ties the query, the archive object, and the pre- and postprocess together for use by the PeopleTools Archive Processor (PSARCHIVE).

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

Archive Query

Archive query definitions define the selection criteria to archive data from ledger and journal tables. Data Archive Manager uses PeopleSoft Query to define selection criteria and prompts for the base table for the base archive object. For example, you might choose to archive all rows in the LEDGER table where the business unit is FRA01.

Archive queries are defined only for base tables because nonbase tables are archived based on the archived data of the related base table. Refer to Archive Object topic in this chapter and to PeopleTools documentation for information on base and nonbase tables as they apply to archiving.

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

Many possible permutations of prompt and inclusion or exclusion logic exist. Always modify these queries or create new archive queries using the information provided in PeopleTools documentation.

Note. Use the delivered queries as examples and modify them according to your corporate archive strategy. Evaluate and modify the queries, archive templates, preprocess and postprocess as a whole.

The following archive query definitions are delivered as sample system data:

Query Name	Description of Prompt and Exclusion Logic
GL_ARCH_JRNL	The query has these prompts: Business Unit From Business Unit To Ledger Group Archive Date Option (Current, Process, and As of Date) As of Date Retention days Archive Begin Date
GL_ARCH_LEDGER	The query has these prompts: Business Unit From Business Unit To Ledger Fiscal Year Accounting Period To Include Closing Adjustments (Yes or No) Adjustment Period From Adjustment Period To
GL_ARCH_LED_ADB	The query has the same prompt as the above.
GL_ARCH_LED_ADB_MTD	The query has the same prompt as the above.
GL_ARCH_LED_ADB_QTD	The query has the same prompt as the above.
GL_ARCH_LED_ADB_YTD	The query has the same prompt as the above.

Journal Archive Query Parameter Descriptions

This table provides additional information about journal archive query parameters:

Journal Query Parameter	Description
Business Unit To and Business Unit From	Specifies the from and to anchor business units of the journals you want to archive. The anchor business unit is the business unit that is on the journal entry header page.
Ledger Group	This is the Ledger Group of the journals to be archived.
Archive Date Option	Use this option to determine the Archive To Date, which can be: Current Date: This is the process running date, or today's date. Process Date: This is the Process Date defined on the GL Business Unit definition. As of Date: This is the date specified in the As Of Date prompt.
As Of Date	Use this date as the Archive To Date if the archive date option you selected is As of Date.
Retention Days	If non-zero, this number of days is subtracted from the Archive To Date that you previously determined.
Archive Begin Date	This is optional. If you leave it blank, the preprocessor populates with the first date in the same fiscal year of the Archive To Date you previously determined.

Ledger Archive Query Parameter Descriptions

This table provides additional information about ledger archive query parameters:

Ledger Query Parameter	Description
Business Unit To and Business Unit From	Specify the from and to business units of the ledger to be archived.
Ledger	Specifies the name of the ledger to be archived.
Fiscal Year	Specifies the fiscal year of the ledger to be archived.
Accounting Period To	Ledger Archive will archive from Period 0 to the Accounting Period To that you specify.
Include Closing Adjustment	Select to included closing adjustment period 999 in the ledger archive.
Adjustment Period From and Adjustment Period To	If both of these are non-zero, the system includes the specified range of adjustment periods in the ledger archive process.

History Tables

General Ledger delivers history tables as system data to be used with the delivered archive procedures.

You can change or configure history tables to correspond to any special requirements or configuration of your system by 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.

Archiving places copies of ledger and journal records in the history tables, and at that point the data exists in the database and the history tables. You can use the delete and remove from history action available on the archive run control to remove archived information from the database tables and when you no longer need the information, remove the archived data from the history tables.

Journal Archive History Tables

The following are the delivered sample system data history tables for journal archive:

Active Database Table	History Table
JRNL_HEADER	JRNL_HDR_H
JRNL_LN	JRNL_LN_H
JRNL_VAT	JRNL_VAT_H
JRNL_CF_BAL_TBL	JRNL_CF_BAL_H
JRNL_IU_ANCHOR	JRNL_IUAC_H
OPEN_ITEM_GL	GL_OITEM_H

Ledger Archive History Tables

The following are the delivered sample system data history tables for ledger archive:

Active Database Table	History Table
LEDGER	LEDGER_H
LEDGER_ADB	LED_ADB_H
LEDGER_ADB_MTD	LED_ADB_MTD_H
LEDGER_ADB_QTD	LED_ADB_QTD_H
LEDGER_ADB_YTD	LED_ADB_YTD_H

Archive Objects

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, "Using PeopleSoft Data Archive Manager," Managing Archive Objects.

General Ledger delivers the following archive object definitions as system data:

Archive Object	Description	Base Table
GL_JOURNAL	Archives journal tables: JRNL_HEADER JRNL_LN JRNL_VAT JRNL_CF_BAL_TBL JRNL_IU_ANCHOR OPEN_ITEM_GL	JRNL_HEADER
GL_LEDGER	Archives detail ledger table, LEDGER.	LEDGER
GL_LED_ADB	Archives detail ADB ledger table, LEDGER_ADB.	LEDGER_ADB
GL_LED_ADB_MTD	Archives incremental ADB ledger table, LEDGER_ADB_MTD.	LEDGER_ADB_MTD
GL_LED_ADB_QTD	Archives incremental ADB ledger table, LEDGER_ADB_QTD.	LEDGER_ADB_QTD
GL_LED_ADB_YTD	Archives incremental ADB ledger table, LEDGER_ADB_YTD.	LEDGER_ADB_YTD

Preprocesses and Post Processes

This is a list of the pre- and post- Application Engine processes that are delivered as system data and that you can use as is or modify to meet different requirements:

Process Name	Object Name	Description
Journal Archive Selection Preprocess	GLARC_JRL1S	The process validates the journal archive selection and generates warnings or errors: An error is generated if a journal selected for archive has open item data not yet closed. An error is generated if archive dates fall within an open period. A warning is issued if there are journals within archive selection criteria with journal status other than Posted, Unposted, Deleted or Upgraded. Such journals are permitted to be archived but the preprocessor logs a warning message in the process monitor, and the archive process continues to run.
Journal Archive Selection Post Process	GLARC_JRL2S	The process generate statistics for the archived journals in the process monitor. You can see data statistics recorded in the archive process in the process monitor: Archive From Date. Archive To Date. Ledger Group. Business Unit. Total Debit Amount. Total Credit Amount.
Ledger Archive Selection Preprocess	GLARC_LED1S	The process validates the ledger archive selection and generates errors if: An ADB ledger is selected for archive but no averages were calculated for the fiscal year. If archive periods fall within open periods. If one of the Adjustment From or Adjustment To Periods is zero but the other is not zero. Both must be zero or both non-zero for proper archiving.
Ledger Archive Selection Post Process	GLARC_LED2S	The process generate statistics for the archived ledgers in the process monitor. You can see data statistics recorded in the archive process in the process monitor: Business Unit. Ledger. Fiscal Year. Accounting Periods. Base Currency. Total Base Credit. Total Base Debit. Total Base Amount.

Archive Templates

When you archive journals and ledgers, 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 preprocessing and postprocessing application engine processes.

The following archive template definitions are delivered as system data:

Archive Template	Archive Object	Pre- or Postprocesses
GL_JRNL	GL_JOURNAL	GLARC_JRL1S (preprocess) GLARC_JRL2S (postprocess)
GL_LED	GL_LEDGER	GLARC_LED1S (preprocess) GLARC_LED2S (postprocess)
GLADB	GL_LEDGER_ADB	GLARC_LED1S (preprocess) GLARC_LED2S (postprocess)
GLADBMTD	GL_LED_ADB_MTD	GLARC_LED1S (preprocess) GLARC_LED2S (postprocess)
GLADBQTD	GL_LED_ADB_QTD	GLARC_LED1S (preprocess) GLARC_LED2S (postprocess)
GLADBYTD	GL_LED_ADB_YTD	GLARC_LED1S (preprocess) GLARC_LED2S (postprocess)

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 might slow down the performance. Test your queries for performance before implementing them in the archive process.

Archiving Ledgers and Journals

To archive ledgers and journals use the Archive Data to History (PSARCHRUNCNTL), Define Query Bind Variables (PSARCHRUNQRYBND), and Audit Archiving (PSARCHIVEAUDIT) components.

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

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 ledgers and journals for General Ledger.

Archive to the history tables by selecting the archive template and query on the Archive Data to History page for the type of 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.
Your DBA off loads data from 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.
If you want to see data in the tables selected to be archive, you can run a query on the history tables.

The data in the history tables is keyed by the archive ID (template) and batch number for each archive run.

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

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

Pages Used to Archive Ledgers and Journals

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 the Define Binds link 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 Archived Ledgers and Journals

To restore archived ledgers and journals use the Archive Data to History component (PSARCHRUNCNTL).

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

Understanding Restoring Ledgers and Journals

The Selection option on the Archive Data to History page places a copy of the data located in the active source ledger and journal records and tables into 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.

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

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

Page Used to Restore Archived Ledger and Journal

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.