Oracle Application R12

After archiving account balances and journal detail, purge the data. Except for translated balances, you can only purge data for accounting periods that have been archived. Translated balances cannot be archived; they can only be purged.
If possible, run the purge utility when there are no users on the system. This avoids degrading performance and response time during business hours.

Prerequisites

Ø Archive your balances, journal details and associated references.
Ø Use the ORACLE RDBMS export utility to export balance and journal archive tables from your database to an operating system file. Save the operating system file to tape.

To purge account balances:

1. Navigate to the Archive and Purge window.
General Ledger displays your Set of Books Name. You can only purge data for the current set of books.
2. Select Balances from the poplist.
General Ledger automatically displays (in the field, Periods From) the earliest accounting period in your set of books that has not already been successfully purged. You must purge data for your oldest period first.
3. Select the Purge Balances checkbox.

4. Select the Balance Type you want to purge. You can purge Actual, Budget, or Encumbrance balances. If you choose to purge budget balances, you must also enter a Budget name. You cannot enter All.
5. Enter the Period To which you want to purge. General Ledger will purge all periods in the range specified in the Period From and Period To fields.
6. Choose Archive/Purge. General Ledger submits a concurrent request to delete the archived records from the GL_BALANCES table for the range of periods you requested.
7. Review the Archive and Purge Audit Report to ensure that the purge process completes successfully. Compare the number of records purged to the number of records archived for each period.
8. Export, drop and reimport the new GL_BALANCES table to shrink the size of the table on your system and reclaim disk space. You will also notice increased performance by reducing fragmentation.

Export the purged GL_BALANCES table and verify the export.

Drop your archive tables. To drop your Balances table, run the following SQL command: drop table GL_ARCHIVE_BALANCES;

Import the GL_BALANCES table

To purge journals:

1. Navigate to the Archive and Purge window.
General Ledger displays your Set of Books Name. You can only purge data for the current set of books.
2. Select Journals from the poplist.
General Ledger automatically displays (in the field, Periods From) the earliest accounting period in your set of books that has not already been successfully purged. You must purge data for your oldest period first.
3. Select the Purge Journals checkbox.

4. Select the Balance Type you want to purge. You can purge Actual, Budget, or Encumbrance journals. If you choose to purge budget journals, you must also enter a Budget name. You cannot enter All.
5. Enter the Period To which you want to purge. General Ledger will purge all periods in the range specified in the Period From and Period To fields.
6. Choose Archive/Purge. General Ledger submits a concurrent request to delete the archived records from the GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES, and GL_IMPORT_REFERENCES tables.
7. Review the Archive and Purge Audit Report to ensure that the Purge process completed successfully. Compare the number of records purged to the number of records archived for each period.
8. Export, drop and reimport the new GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES, and GL_IMPORT_REFERENCES tables to shrink the size of these tables and reclaim disk space. You will also notice increased performance by reducing fragmentation.

Export the purged GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES, and GL_IMPORT_REFERENCES tables and verify the export.

Drop your archive tables. To drop your Journal Details and References tables, run the following SQL command, substituting GL_ARCHIVE_HEADERS, GL_ARCHIVE_LINES, or GL_ARCHIVE_REFERENCES for the table name:

drop table [table name],

Import the GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES, and GL_IMPORT_REFERENCES tables.

To purge translated balances:

1. Navigate to the Archive and Purge window.
General Ledger displays your Set of Books Name. You can only purge data for the current set of books.
2. Select Translated Balances from the poplist.
3. Select the Balance Type you want to purge. You can purge translated Actual or Budget balances. If you choose to purge translated budget balances, you must also enter a Budget name. You cannot enter All.

4. Enter the Currency whose translated balances you want to purge.
General Ledger automatically displays (in the field, Periods To) the latest translated period for the balance type and the currency. You cannot change this value.
5. Enter the Period From which you want to purge. You change this to any earlier period, going back as far as the earliest translated period for the balance type and currency. General Ledger will purge all periods in the range specified in the Period From and Period To fields.
6. Choose Purge. General Ledger submits a concurrent request to delete the translated balances for the range of periods you requested.
7. Review the Archive and Purge Audit Report to ensure that the purge process completes successfully.

Understanding the Archive

Archiving is the process of inserting data into archive tables after gathering it from live production tables based on the criteria fed to the process. Data can be removed from the live tables or left to remain there. Live tables are those that are used to store and retrieve information that is current and is required in the operation of day-to-day business transactions. Database performance is improved by moving historical data from live tables into archive tables. Archive tables are those that are used to store historical information.

Archiving Account Balances and Journal Detail

You can archive and purge account balances, as well as journal batches, entries, lines, and associated journal references for one or more accounting periods, provided the periods are permanently closed.

Prerequisites:

Permanently close all periods that you want to archive or purge. You do not have to permanently close periods to purge only translated balances.

Run all of your standard accounting reports to maintain a printed record of the transactions you will archive and purge.

Create the appropriate tablespace and set your storage parameters to hold your archived data.(See Notes Below)

Ensure that you have exported any previously archived data to an operating system file and saved the file to tape.

To archive account balances:

1. Make sure you are using the set of books for the data you want to archive. You can only archive and purge data for the current set of books.

2. Navigate to the Archive and Purge window.

Responsibility General Ledger >> Setup>>System>>Purge>>Balances

3. Select Balances from the poplist.

4. Select the Archive Balances checkbox.

5. Select the Balance Type you want to archive. You can archive Actual, Budget, or Encumbrance balances. If you choose to archive budget balances, you must also enter a Budget name. You cannot enter All.

6. Enter the Period To which you want to archive. General Ledger will archive all periods in the range specified in the Period From and Period To fields.

7. Choose Archive/Purge. General Ledger submits a concurrent request to archive your balances. The Archive program copies account balances from the GL_BALANCES table to the GL_ARCHIVE_BALANCES table for the range of periods you requested.

8. Review the Archive and Purge Audit Report to verify that the data for all periods you requested was successfully archived. We also recommend that you keep a written confirmation of the number of records you archived. You can compare this number to the number of records purged when you run the purge utility.

9. Export the archive tables and copy the export files to tape.

10. Purge your data.

To archive journals:

1. Make sure you are using the set of books for the data you want to archive. You can only archive and purge data for the current set of books.

2. Navigate to the Archive and Purge window.

Responsibility General Ledger >> Setup>>System>>Purge>>Journals

3. Select Journals from the poplist.

General Ledger automatically displays (in the field, Periods From) the earliest accounting period in your set of books that has not already been successfully archived and purged. You must archive and purge data for your oldest period first.

4. Select the Archive Journals checkbox.

5. Select the Balance Type you want to archive. You can archive Actual, Budget, or Encumbrance journals. If you choose to archive budget journals, you must also enter a Budget name. You cannot enter All.

6. Enter the Period To which you want to archive. General Ledger will archive all periods in the range specified in the Period From and Period To fields.

7. Choose Archive/Purge. General Ledger submits a concurrent request to copy journal details from the GL_JE_BATCHES, GL_JE_HEADERS and GL_JE_LINES tables to the GL_ARCHIVE_BATCHES, GL_ARCHIVE_HEADERS and GL_ARCHIVE_LINES tables for the accounting periods you requested. If you imported journal references along with your actual journal entries, General Ledger also copies reference details from the GL_IMPORT_REFERENCES table to the GL_ARCHIVE_REFERENCES table.

9. Export the archive tables and copy the export files to tape.

10. Purge your data.

Notes related To Archive:

Creating a Tablespace for Archived Data

Before you archive data, consult your Database Administrator to create a tablespace large enough to hold the data you want to archive.

To calculate the amount of space you need for archiving:

1. Determine how many actual rows for your set of books are in the GL_BALANCES table for the fiscal year you want to archive using the following SQL statement:

select count(*)
from GL_BALANCES
where PERIOD_YEAR = [your archive year]
and ACTUAL_FLAG = 'A'
and SET_OF_BOOKS_ID =
(select SET_OF_BOOKS_ID
from GL_SETS_OF_BOOKS
where NAME=[your set of books name];

2. Determine how many rows are in the GL_JE_BATCHES table for the fiscal year you want to archive using the following SQL statement:

select count(*)
from GL_JE_BATCHES
where DEFAULT_PERIOD_NAME in [list of periods]
and ACTUAL_FLAG='A'
and SET_OF_BOOKS_ID =
(select SET_OF_BOOKS_ID
from GL_SETS_OF_BOOKS
where NAME=[your set of books name];

3. To determine how many rows are in the GL_JE_HEADERS or GL_JE_LINES tables for the fiscal year you want to archive, use the previous SQL statement, substituting GL_JE_HEADERS or GL_JE_LINES for GL_JE_BATCHES and PERIOD_NAME for DEFAULT_PERIOD_NAME.

4. Determine how many rows are in GL_IMPORT_REFERENCES table for the fiscal year you want to archive, using the following SQL statement:

select count(*)
from GL_IMPORT_REFERENCES
where JE_BATCH_ID =
(select JE_BATCH_ID
from GL_JE_BATCHES
where DEFAULT_PERIOD_NAME in [list of periods]
and ACTUAL_FLAG='A'
and SET_OF_BOOKS_ID =
(select SET_OF_BOOKS_ID
from GL_SETS_OF_BOOKS
where NAME=[your set of books name];

5. Determine the amount of space needed to archive the rows you want from the appropriate table(s). To do this, you must determine the average size of a row in each of those tables. First, determine the total number of rows in each table using the following SQL statement:

select count (*)
from [table name]

6. Consult your System Administrator to determine the total table size. Divide this table size by the total number of rows in the table to get the average size of a row in that table. Finally, multiply that average size by the number of rows you want to archive, as determined above.

Attention: Contact your Database Administrator if your tablespace is not large enough to store your archive data.

7. Select the archive tablespace and storage parameters for which to store the following interim tables using the Storage Parameters window:

GL_ARCHIVE_BALANCES
GL_ARCHIVE_BATCHES
GL_ARCHIVE_HEADERS
GL_ARCHIVE_LINES
GL_ARCHIVE_REFERENCES

STORAGE PARAMETERS FOR INTERIM TABLES

You can change the storage parameters for all interim tables and indexes in General Ledger. Several concurrent programs in General Ledger use interim tables as temporary storage space for transaction data. These programs create interim tables when they start and drop them when they finish.

Although the default storage parameters meet the needs of most installations, you can increase interim table allocations if the default parameters are inadequate.

The following General Ledger concurrent programs use interim tables:

Posting: GL_POSTING_INTERIM

MassAllocations: GL_ALLOC_INTERIM

MassBudgets: GL_ALLOC_INTERIM

Translation: GL_TRANSLATION_INTERIM

Archive and Purge: GL_ARCHIVE_BALANCES, GL_ARCHIVE_BATCHES, GL_ARCHIVE_HEADERS, GL_ARCHIVE_LINES

Budget Posting: GL_BUDGET_INTERIM, GL_BUDGET_RANGE_INTERIM

Create Summary Accounts: GL_SUMMARY_INTERIM

Prerequisite

Ø Determine the amount of storage space that you want to allocate to interim tables and indexes.

To set the storage parameters:

1. Navigate to the Storage Parameters window.

General Ledger automatically displays all the interim tables and indexes it uses and the corresponding default storage parameters. General Ledger indicates the Object Type (Table or Index) and the Object Name.

2. Enter the Tablespace where you want the interim table or index to reside.

3. Enter the size (in kilobytes) of the Initial Extent you want General Ledger to allocate when it creates the interim table or index.

4. Enter the size (in kilobytes) of the Next Extent you want General Ledger to allocate for the interim table or index. This size is a base value which may remain constant for each subsequent extent, or may change depending on the value you enter for percent increase. The default extent values vary with the individual table or index. To see an explanation for the default value of a particular table or index, refer to the Description.

5. Enter the Maximum number of extents allowed for the interim table or index.

6. Enter the Pctincrease, or percentage for which you want each next extent size to increase over the last extent allocated. If percentage increase is zero (0), then the size of each additional extent remains constant.

Suggestion: We recommend that you specify a percent increase of either 0 or 100 for your interim tables. Other values can increase the rate of fragmentation of your interim tablespace.