Archiving Transactions in Expenses

This chapter provides an overview of archiving in Expenses, an overview of the data types available for archiving, an overview of archive objects definitions, PSQuery objects, and template objects, and discusses how to:

See Also

Enterprise PeopleTools PeopleBook: Data Management, “Using PeopleSoft Data Archive Manager”

Click to jump to parent topicUnderstanding Archiving in Expenses

For organizations where transactional volumes are large, retaining historical data on the PeopleSoft transactional tables can become a burden when the volumes of data reduce processing speed and efficiency in reporting or inquiring on information. To reduce this burden, you can schedule processes to archive and purge historical or obsolete data. Expenses enables you to archive inactive data on the transaction tables to history tables. If you need to access the data later, you can restore it.

PeopleSoft strongly recommends that you consider transaction relationships when you develop an archiving plan. For example:

Click to jump to parent topicUnderstanding Data Types Available for Archiving

This section discusses:

Click to jump to top of pageClick to jump to parent topicData Types

Expenses enables you to archive the inactive data on the transaction tables to the history tables. If you need to access the data later, you can restore it.

Type of Data

Description

Tables

Travel authorization transaction data

Travel authorization data, such as general information, expense transaction lines, and accounting information.

  • EX_TAUTH_DIST

  • EX_TAUTH_HDR

  • EX_TAUTH_LINE

  • EX_TAUTH_SPLIT

Travel authorization approval data

Cross reference data used by AWE to process the travel authorization approval actions.

EX_TAUTH_AW

Cash advance transaction data

Cash advance data, such as general information, approval details, document sequencing (if applicable), and cash advance payment information.

  • EX_ADV_CHK

  • EX_ADV_DIST

  • EX_ADV_DOCSEQ

  • EX_ADV_HDR

  • EX_ADV_LINE

  • EX_ADVNCE_PYMNT

Cash advance approval data

Cross reference data used by AWE to process the cash advance approval actions.

EX_ADV_AW

Expense report transaction data

Expense report transaction data, such as general information, expense transaction lines, approval details, document sequencing (if applicable), attendee information, accounting details, expense report payment information.

Note. Expenses also archives expense report transactions that originated in PeopleSoft Mobile Time and Expense.

  • EX_SHEET_ADV

  • EX_SHEET_ATT

  • EX_SHEET_DIST

  • EX_SHEET_DOCSEQ

  • EX_SHEET_HDR

  • EX_SHEET_LINE

  • EX_SHEET_LN_PD

  • EX_SHEET_PYMNT

  • EX_SHEET_SPLIT

Expense report approval data

Cross reference data used by AWE to process the expense report approval actions.

EX_SHEET_AW

Time report transaction data

Time report transaction data, such as general information, personal and project-related details, prior period adjustment details, and approval details.

Note. Expenses also archives time report transactions that originated in PeopleSoft Mobile Time and Expense.

  • EX_TIME_DTL

  • EX_TIME_DTL_DLY

  • EX_TIME_HDR

  • EX_TIME_POL

  • EX_TIME_POL_DLY

Time report approval data

Cross reference data used by AWE to process the time report approval actions.

EX_TIME_AW

DCAA audit data

DCAA audit log of entries, changes or violations made by employees, authorized reporters, approvers, and auditors.

EX_AUDIT_DCAA

Time report project resource data

Approved time entries that are staged for the PC_EX_TO_PC Application Engine process to pick them up.

EX_PROJ_RES_STG

My Wallet data

My Wallet data, such as expense transactions from a credit card feed or transactions that employees manually entered into My Wallet.

EX_TRANS

Account entry data

Expenses accounting line information.

EX_ACCTG_LINE

Approval history data

Expenses approval action history information.

EX_APRVL_HIST

Click to jump to top of pageClick to jump to parent topicTravel Authorization Transaction Data

To be eligible to archive, travel authorization transactions must be:

You can also archive travel authorization transactions with Closed and Denied status.

Selection for archival is general ledger business unit and accounting date.

Click to jump to top of pageClick to jump to parent topicTravel Authorization Approval Data

Selection for archival is last date modified or last approval date.

Click to jump to top of pageClick to jump to parent topicCash Advance Transaction Data

To be eligible to archive, cash advances must have:

Selection for archival is general ledger business unit and accounting date

Click to jump to top of pageClick to jump to parent topicCash Advance Approval Data

Selection for archival is last date modified or last approval date.

Click to jump to top of pageClick to jump to parent topicExpense Report Transaction Data

Expenses provides two archiving methods for expense reports:

To be eligible to archive, expense report transactions must have:

Selection for archival is general ledger business unit and accounting date.

Click to jump to top of pageClick to jump to parent topicExpense Report Approval Data

Selection for archival is last date modified or last approval date.

Click to jump to top of pageClick to jump to parent topicTime Report Transaction Data

To be eligible to archive, time report transactions must be sent to Project Costing and Time and Labor (if installed) and have a time report status of denied or approved.

Selection for archival is by general ledger business unit and period end date.

Click to jump to top of pageClick to jump to parent topicTime Report Approval Data

Selection for archival is last date modified or last approval date.

Click to jump to top of pageClick to jump to parent topicDCAA Audit Transaction Data

Selection for archival is by DCAA audit date, which is the server date and time when Expenses saved a record.

Click to jump to top of pageClick to jump to parent topicTime Report Project Resource Data

Selection for archival is by general ledger business unit and period end date.

Click to jump to top of pageClick to jump to parent topicMy Wallet Data

Selection for archival is transaction date and that the transaction has been applied to an expense report.

Click to jump to top of pageClick to jump to parent topicAccounting Line Data

The EX_ACCTG_LINE table is archived separate from the transactions that created them in order to enable you to keep the data synchronized with the data in the general ledger. The selection for archival is by the accounting date range.

Click to jump to top of pageClick to jump to parent topicApproval History Data

The selection for archival is by approval date.

Click to jump to top of pageClick to jump to parent topicSuggested Order of Data Removal

There is no required order to archive Expenses data.

Click to jump to parent topicUnderstanding Archive Objects Definitions, Query Definitions, and Template Definitions

Expenses delivers archive object definitions, query definitions, and template definitions to archive:

You can modify them if needed using the Data Archive Manager. You can also use the Data Archive Manager to create your own archive object definitions, query definitions, and template definitions to archive other types of data.

Archive Object Definitions

An archive object definition identifies the tables that contain the data to be archived and the history tables that will be updated for each table. Expenses delivers these archive objects:

Data Type

Archive Object

Travel authorization transaction data

EX_TRAVELAUTH

Travel authorization approval data

EX_TRAVELAUTH_APPR_DATA

Cash advance transaction data

EX_EXADVANCE

Cash advance approval data

EX_EXADVANCE_APPR_DATA

Expense report transaction data

EX_EXSHEET

Expense report approval data

EX_EXSHEET_APPR_DATA

Time report transaction data

EX_TIMEREPORT

Time report approval data

EX_TIMEREPORT_APPR_DATA

DCAA audit transaction data

EX_AUDIT_DCAA

Time report project resource data

EX_TIME_PRJ_RESOURCE

My Wallet transaction data

EX_MYWALLET

Accounting line data

EX_ACCOUNTING

Approval history data

EX_APPROVAL_HIST

Archive Query Definitions

Each archive query definition defines the selection criteria to archive data from transaction tables. Expenses delivers these archive queries:

Data Type

Archive Query

Travel authorization transaction data

EX_TAUTH_ARCH

Travel authorization approval data

EX_TAUTH_APPR_DATA_ARCH

Cash advance transaction data

EX_ADV_ARCH

Cash advance approval data

EX_ADV_APPR_DATA_ARCH

Expense report transaction data

  • EX_EXSHT_ARCH

  • EX_EXSHT_ARCH_NONBILLABLE

Expense report approval data

EX_EXSHT_APPR_DATA_ARCH

Time report transaction data

EX_TIME_ARCH

Time report approval data

EX_TIME_APPR_DATA_ARCH

DCAA audit transaction data

EX_AUDIT_DCAA_ARCH

Time report project resource data

EX_TIME_PRJRES_ARCH

My Wallet transaction data

EX_MYWALLET_ARCH

Accounting line data

EX_ACCTG_ARCH

Approval history data

EX_APPROVAL_HIST_ARCH

Review these queries to determine whether you want to add additional selection criteria for the transactions to be archived.

Archive Template Definitions

When you archive data, you select an archive template definition. Each archive template definition includes one or more archive object definitions and the archive query definitions used to select the data. Expenses delivers these archive templates:

Data Type

Archive Template

Travel authorization transaction data

EX_TAUTH

Travel authorization approval data

EX_TA_AD

Cash advance transaction data

EX_EXADV

Cash advance approval data

EX_CA_AD

Expense report transaction data

EX_EXSHT

Expense report approval data

EX_ER_AD

Time report transaction data

EX_TIME

Time report approval data

EX_TR_AD

DCAA audit transaction data

EX_DCAA

Time report project resource data

EX_PRJRS

My Wallet data

EX_MYWAL

Accounting line data

EX_ACCTG

Approval history data

EX_APPR

Click to jump to parent topicArchiving Data

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

Click to jump to top of pageClick to jump to parent topicUnderstanding the Archive Process Flow

Use the Data Archive Manager to archive transactions. You use the same run control page for each step in the process except for auditing the archive selection and generating archive candidate reports.

Perform these tasks to archive data:

  1. Archive data to the history tables by selecting the archive template and query for the data type that you want to archive on the Archive Data to History page.

    Also, enter the archive date used to select the transactions by clicking Reset Query Find Variables on the Define Query Bind Variables page.

    This table shows what the system compares the archive date to for the different data types:

    Data Type

    Archive Date

    Travel authorization transaction data

    Accounting date

    Travel authorization approval data

    Last date modified or last approval date

    Cash advance transaction data

    Accounting date

    Cash advance approval data

    Last date modified or last approval date

    Expense report transaction data

    Accounting date

    Expense report approval data

    Last date modified or last approval date

    Time report transaction data

    Accounting date

    Time report approval data

    Last date modified or last approval date

    DCAA audit data

    DCAA audit date

    Time report project resource data

    Period end date

    My Wallet transaction data

    Transaction date

    Accounting line data

    Accounting date

    Approval history data

    Approval date

    Note. You can run the process multiple times to create various “what-if” scenarios based on the archive date.

  2. (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 selected the Audit Row Count check box on the Archive Data to History page.

  3. Delete the data from the transaction tables for a specific archive ID (template) and batch number on the Archive Data to History page.

  4. (Optional) Rollback the transaction data from the history tables for a specific archive ID (template) and batch number on the Archive Data to History page if you deleted the transaction data in error.

  5. Remove the transaction data from the history tables for a specific archive ID (template) and batch number on the Archive Data to History page.

Click to jump to top of pageClick to jump to parent topicUnderstanding History Tables

Expenses updates these history tables when you run the selection process:

Data to Archive

History Tables

Travel authorization transaction data

  • EX_TAUT_SPLIT_H

  • EX_TAUTH_DIST_H

  • EX_TAUTH_HDR_H

  • EX_TAUTH_LINE_H

Travel authorization approval data

EX_TAUTH_AW_H

Cash advance transaction data

  • EX_ADV_CHK_H

  • EX_ADV_DIST_H

  • EX_ADV_DOCSEQ_H

  • EX_ADV_HDR_H

  • EX_ADV_LINE_H

  • EX_ADV_PYMT_H

Cash advance approval data

EX_ADV_AW_H

Expense report transaction data

  • EX_SHEET_ADV_H

  • EX_SHEET_ATT_H

  • EX_SHEET_DIST_H

  • EX_SHEET_HDR_H

  • EX_SHEET_LINE_H

  • EX_SHEET_PYMT_H

  • EX_SHT_DOCSEQ_H

  • EX_SHT_LN_PD_H

  • EX_SHT_SPLIT_H

Expense report approval data

EX_SHEET_AW_H

Time report transaction data

  • EX_TIME_DTL_H

  • EX_TIME_HDR_H

  • EX_TIME_POL_H

  • EX_TM_DTL_DLY_H

  • EX_TM_POL_DLY_H

Time report approval data

EX_TIME_AW_H

DCAA audit transaction data

EX_AUDIT_DCAA_H

Time report project resource data

EX_PRJ_RS_STG_H

My Wallet transaction data

EX_TRANS_H

Accounting line data

EX_ACCTG_LINE_H

Approval history data

EX_APRVL_HIST_H

The data in these tables is keyed by the archive ID (template) and batch number for each archive run. If you want to see all transactions that are selected to archive you can run a query on the history tables or you can run one of the Expenses archive candidate reports.

You may not want to delete data from the history tables each time that you select and delete data from the transaction tables. This way the data will be available for a period of time to enable you to restore the data into the transaction tables. We recommend that you create a schedule for deleting data from the history tables. Before you delete data from the history tables, you should use a database utility to copy the contents to a flat file.

Important! If you decide to run the archive selection process again because you didn't select the correct data, you must first use the Data Archive Manager option to remove the data from the history tables.

Click to jump to top of pageClick to jump to parent topicPages Used to Archive Data

Page Name

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

Enter the date used to select the transactions to archive by clicking the Reset Query Bind Variables button.

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.

Click to jump to parent topicRestoring Archived Data

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

Click to jump to top of pageClick to jump to parent topicUnderstanding Data Restoration

The Selection option on the Archive Data to History page places a copy of the data in the transaction tables in the history tables and the Delete option removes the transaction data from the transaction tables. If you delete the data from transaction tables in error, you can restore the data from the history tables by using the Rollback option on the Archive Data to History page. The process restores all data for a specific archive ID (template) and process run. You cannot restore selected data. For example, you cannot specify that you want to restore data for a specific customer or group control.

Important! After you delete data from the history tables, you cannot restore it to the transaction tables unless you first manually restore it to the history tables from a flat file.

Click to jump to top of pageClick to jump to parent topicPage Used to Restore Archived Data

Page Name

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