Understanding Archiving for Ledgers and Journals

These topics provide an overview of archiving for ledgers and journals and discuss how to:

  • Archive ledgers and journals.

  • Restore archived ledgers and journals.

Topic details include:

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

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 topic and to PeopleTools documentation for information on base and nonbase tables as they apply to archiving.

See PeopleTools documentation: 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.

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

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

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

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)

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