Maintenance

Mass Maintenance

Use Mass Maintenance to move balances by period from one account to another or merge balances by period from multiple accounts into a single account. The moved/merged balances are added to the existing balances in your target accounts. If you change your mind about a move/merge, you can reverse it and restore your account balances to their previous amounts.

If you use ledger sets, you can move/merge balances for multiple ledgers in a ledger set.

During a move/merge operation the financial integrity between General Ledger and its subledgers is maintained, so you can still drill down to your subledger details after the move/merge is complete.

Note: To drill down, perform an account inquiry from the move/merge target account. From there, you can drill down to the move/merge source account, then to the subledger detail.

You can also use Mass Maintenance's mass creation feature to create new accounts automatically based on existing accounts. For example, if you add a new cost center to your organization you can use mass creation to create all the accounts you need by modeling one of your other cost centers.

Data Access Sets

The move/merge operation adheres to data access set security. The data access set assigned to your responsibility must provide full ledger access in order to execute a move/merge operation. Full ledger access means having full read and write access to the ledger and all of its balancing segment values or management segment values.

For more information, see Data Access Sets, Oracle General Ledger Implementation Guide.

Related Topics

Moving or Merging Account Balances

Defining a Move/Merge Request

Reversing a Move/Merge

Purging Move/Merge Tables

Creating New Accounts with Mass Creation

Defining Ledger Sets, Oracle General Ledger Implementation Guide

Data Access Sets, Oracle General Ledger Implementation Guide

Moving or Merging Account Balances

A move operation transfers balances from one or more source accounts to one or more target accounts. In the Mass Maintenance Workbench window, you use one account specification each to define the source accounts and the target accounts.

For example, assume your account has a cost center segment that you use to represent operations centers. Assume also that you have just closed one of your centers (#683) and want another center (#357) to absorb center #683's inventory and operations. For accounting and reporting purposes, you now want center #683's account balances reflected in center #357's accounts. You can do this with a move operation. Assuming you use a four segment account, with cost center as the second segment, the source and target specifications in the Mass Maintenance Workbench window are:

Source specification: 01. 683 . . Target specification: 01 . 357 . .

Note that you only have to specify a value for the cost center segment and the balancing segment. You cannot leave the balancing segment blank. By leaving the other segments blank, your move/merge operation will move all account balances for all values of the other segments when the cost center value is 683.

A merge operation transfers balances from multiple source accounts into one or more target accounts. In the Mass Maintenance Workbench window, multiple account specifications are used to define the source accounts while the same account specification is used to define the target accounts.

For example, assume that you want to merge the balances from three costs centers (575, 683, and 937) into one (357). The source and target specifications in the Mass Maintenance Workbench window are:

Source specification: 01. 575 . . Target specification: 01. 357 . .

Source specification: 01. 683 . . Target specification: 01. 357 . .

Source specification: 01. 937 . . Target specification: 01. 357 . .

Business Rules

Reporting Currencies

When you move/merge balances for a ledger, the balances for all of the ledger’s reporting currencies (Journal or Subledger level) will also be moved/merged. You cannot execute a move/merge operation for a reporting currency directly. A reporting currency’s balances can only be moved/merged through its source ledger, such as a primary ledger or secondary ledger.

Note: The translated balances maintained in balance level reporting currencies will not be automatically moved/merged. You will need to re-run translation for each balance level reporting currency to update its balances.

When you execute a move/merge operation for a ledger that has reporting currencies assigned (Journal or Subledger level), move/merge will be executed in each reporting currency, regardless if your data access set provides full read and write access to the reporting currency

Note: For a move/merge operation to complete successfully, the ledger and its reporting currencies must have the same last opened period. They do not have to have the same last purged periods.

What Move/Merge Does

If no errors are encountered, a move/merge will perform the following steps before it finishes processing:

Translated Balances

After a successful move/merge operation, translated balances will be out of date. You must run General Ledger's Translation program for each balance level reporting currency to update your translated balances.

Related Topics

Defining a Move/Merge Request

Reversing a Move/Merge

Purging Move/Merge Tables

Defining a Move/Merge Request

Prerequisites

To move or merge account balances:

  1. Navigate to the Mass Maintenance Workbench window.

  2. Select Move/Merge as the Request Type.

  3. Enter a Request name and Description for your move/merge.

  4. In the Ledger field, select a ledger or ledger set.

    Your data access set must provide full ledger access to the ledger or ledger set. Full ledger access means having full read and write access to the ledger and all of its balancing segment values or management segment values.

    Submitting a move/merge request for a ledger set will automatically create a separate request for each ledger in the ledger set. These requests will have the same request name, but for different ledgers. You can track the status of the request for each ledger independently. If you need to reverse the request, you must do so separately for each ledger.

  5. Enter a Line number for the source-to-target account specification.

  6. Enter a unique Source account specification from which to move/merge balances. You can also select your account segment values from the list of values.

    Note: If you enter values for all of the account segments, the account must exist and must be enabled. If you enter values only for some of the segments, the values you enter must exist and be enabled.

    Note: Choose the Segment Values button if you want to review your segment values. This will take you to the Segment Values window. Use the menus to return to the Mass Maintenance Workbench window.

  7. Enter the Target account specification to which you want to move/merge balances. You can also select your account segment values from the list of values.

    Note: The format of the target account specification must be the same as the format you use for the source account. For example, if you enter values only for some of your source segments, you must enter values for the same segments of your target account specification. If you enter a complete account as your source, you must enter a complete account for your target.

    Note: Target accounts that do not exist will be created if they pass segment security rules and cross-validation checking.

  8. Continue entering account specifications until you are done.

  9. (Optional) Choose the Prevalidate button to run the prevalidation process. Review the execution report and correct any errors before submitting the move/merge.

    See: Validation and Prevalidation

    Note: Generally, you should prevalidate your account specifications if you plan to run your move/merge unattended, such as overnight. The prevalidation process helps ensure that your move/merge request completes successfully. If you do not prevalidate, General Ledger will still validate your account specifications when you submit the move/merge.

  10. Save your work.

Related Topics

Submitting a Move/Merge Request

Mass Maintenance Statuses

Validation and Prevalidation

Reversing a Move/Merge

Purging Move/Merge Tables

Creating New Accounts with Mass Creation

Submitting a Move/Merge Request

  1. Navigate to the Mass Maintenance Workbench window.

  2. Query the move/merge definition that you want to submit.

  3. Choose the Submit button to start the move/merge process.

  4. Review the Mass Maintenance Execution Report for any errors. If necessary, correct any errors then resubmit the move/merge request.

  5. (Optional) Purge the interim move/merge tables that were created during the move/merge process.

    Note: We recommend that you purge tables only after you have verified that the moved balances are correct. Once you purge the tables, you cannot reverse the move/merge operation.

Conflicts with Other Processes and Activities

You should only run move/merge operations when no conflicting activity is taking place in the same ledger. There are several categories of activities that conflict with move/merge:

Journal Creation: includes entering manual or budgetary control journals, importing journals, revaluation, MassAllocations, and recurring formulas.

Balance Processing: includes posting, translation, summarization, open period, and purge.

Account Creation: includes creating, modifying, disabling, and enabling accounts.

All concurrent processes and online activity that fall into the above categories should be completed before you initiate a move/merge operation. To minimize the chance of conflicts occurring, we recommend that you establish and enforce procedures for scheduling move/merge operations. We also recommend that you establish and enforce procedures over account creation and modification.

Note: Consider excluding the move/merge concurrent program from the standard concurrent manager. Instead, assign the program to a special concurrent manager queue that becomes active only at specified times.

Related Topics

Defining a Move/Merge Request

Mass Maintenance Statuses

Validation and Prevalidation

Reversing a Move/Merge

Purging Move/Merge Tables

Creating New Accounts with Mass Creation

Mass Maintenance Statuses

Any Mass Maintenance process (prevalidation, move/merge, reversal, mass creation, and purge) will have one of four statuses displayed in the Status field of the Mass Maintenance Workbench window:

New: Displayed when you define a new move/merge or mass creation.

In Process: The process is currently active.

Completed: The process has completed successfully.

Failed: The process has completed unsuccessfully.

Related Topics

Moving or Merging Account Balances

Defining a Move/Merge Request

Validation and Prevalidation

Reversing a Move/Merge

Purging Move/Merge Tables

Creating New Accounts with Mass Creation

Validation and Prevalidation

During validation and prevalidation, General Ledger will determine all the accounts that are defined by your source and target account specifications, perform validation checking on those accounts, and produce a Mass Maintenance Execution Report, showing any errors, such as:

Validation is done automatically when you submit a move/merge request. However, since it is generally a good idea to run your move/merge operations during off-peak hours, you can choose to prevalidate before you submit the move/merge.

Running move/merge during off-peak hours minimizes system performance degradation and the possibility of the move/merge conflicting with other processes. Prevalidating helps ensure that an unattended move/merge operation completes successfully.

If a prevalidation request is successful, General Ledger will create your new target accounts if they do not already exist. The status displayed in the Mass Maintenance Workbench window will be Completed.

If a prevalidation request fails, General Ledger will only create those new target accounts which passed validation. Target accounts that do not pass validation will not be created. The status will be Failed. Review the Mass Maintenance Execution report and correct any noted problems before you submit your move/merge.

Note: If you update your segment values or accounts after a successful prevalidation, your move/merge operation may fail.

Related Topics

Moving or Merging Account Balances

Defining a Move/Merge Request

Mass Maintenance Statuses

Reversing a Move/Merge

If you perform a move/merge operation then later change your mind, you can use Mass Maintenance to reverse the move/merge and restore your original balances.

Caution: Do not purge the interim move/merge tables until you are satisfied with the move/merge results. If you purge these tables, you cannot reverse the move/merge later.

To reverse a move/merge:

  1. Navigate to the Mass Maintenance Workbench window.

  2. Query the move/merge request that you want to reverse.

  3. Choose the Reverse button.

    If the reverse request completes successfully your balances will be restored, the interim tables will be deleted, and new move/merge reversal audit journals will be created.

    Note: Any transactions you entered and posted to your target accounts after the initial move/merge operation will not be reversed. You must reverse these separately or create a journal entry to move the totals of those transactions back to the source accounts.

Related Topics

Moving or Merging Account Balances

Defining a Move/Merge Request

Mass Maintenance Statuses

Purging Move/Merge Tables

A move/merge operation populates two interim tables, GL_MOVEMERGE_BAL_<move/merge request id> and GL_MOVEMERGE_DAILY_BAL_<move/merge request id>, with the balances being moved or merged. The information in these tables is retained after a successful move/merge because it is needed to reverse the move/merge.

When you are satisfied that the results of a move/merge are correct and that you want to retain the new balances, you should purge the tables. Do not purge the tables if you think you may want to reverse the move/merge later..

Note: If you reverse a move/merge operation, the interim tables will be purged automatically.

To purge the interim move/merge tables:

  1. Navigate to the Mass Maintenance Workbench window.

  2. Query the move/merge request whose interim tables you want to purge.

  3. Choose the Purge button.

Related Topics

Moving or Merging Account Balances

Defining a Move/Merge Request

Mass Maintenance Statuses

Reviewing Move/Merge Audit Journals

When you perform a move/merge operation or a move/merge reversal, General Ledger creates an audit journal for each period for which balances are moved or merged. These journals are not used to update your target accounts. They are created only to provide an audit trail of all move/merge operations. These audit journals can be queried and included in a report.

Note: If average balance processing is enabled for your ledger, move/merge audit journals are created for each calendar day. Consolidation ledgers will have two sets of audit journals; one for standard balances and one for average balances.

You can identify move/merge audit journals by their journal source, journal category, and batch name. The journal source and category are either Move/Merge or Move/Merge Reversal. The batch name follows this format:

[request type]: [request name] [request id] [balance type] [period]: [batch date and time].

The parameter values for the batch name are shown in the table below:

Parameter Value
[request type] Move/Merge or Move/Merge Reversal
[request name] name of the move/merge request
[request id] move/merge concurrent request ID
[balance type] Standard or Average
[period] accounting period of moved balances
[batch date and time] date/time audit journal was created

For example, a move/merge batch might be named:

Move/Merge: CC357Merge 1687 Standard JUN-97: JUN 30, 1997 2:35:45

To query move/merge audit journals:

Use the query capabilities in the Enter Journals window. See: Performing a Journal Entry Inquiry.

Tip: In the Find Journals window, narrow your query by entering Move/Merge or Move/Merge Reversal as the journal source or journal category. Narrow the query further by entering the move/merge audit journal batch name.

Note: Move/merge audit journals will not appear in the Reverse Journals window. To reverse a move/merge operation, you must use the Mass Maintenance reversal feature.

When you review the journal details, you will find that:

Related Topics

Moving or Merging Account Balances

Defining a Move/Merge Request

Reversing a Move/Merge

Performing a Journal Entry Inquiry

Creating New Accounts with Mass Creation

Use Mass Maintenance's mass creation feature to create new accounts automatically based on existing accounts. For example, if you add a new cost center to your organization you can use mass creation to create all the accounts you need based upon one of your other cost centers.

Note: Mass creation does not update any accounts and account ranges used in General Ledger definitions, such as recurring journals, mass allocations, consolidation mappings, and summary accounts.

Note: Newly created account code combinations do not inherit attributes from the original account code combination. Instead, the newly created account segments inherit the attributes from the segments in the new account code combination. The attributes for the new account code combination are governed by the most restrictive segment attribute.

Note: If the original account is disabled, a new account combination will be created as long as all the target segment values are enabled.

In the example shown in the tables below, the original account code combination allowed posting. The 01 segment did not allow posting. The new 01 segment inherits the no posting allowed attribute. Therefore, the account code combination will not allow posting.

Original Account
Original Account Enabled Posting Allowed Budget Entry Allowed
01-000-1000 yes yes yes
01 yes NO yes
000 yes yes yes
100 yes yes yes
New Account
New Account Enabled Posting Allowed Budget Entry Allowed
01-100-1000 yes NO yes
01 yes NO yes
100 yes yes yes
100 yes yes yes

Prerequisites

To create accounts using mass creation:

  1. Navigate to the Mass Maintenance Workbench window.

  2. Select Mass Creation as the Request Type.

  3. Enter a Request name and Description for your mass creation.

  4. Enter a Line number for the source-to-target account specification.

  5. Enter a Source account specification to use to model your target accounts. You can also select your account segment values from the list of values.

    Note: If you enter values for all of the account segments, the account must exist and must be enabled. If you enter values only for some of the segments, the values you enter must exist and be enabled. Therefore, if you need to create a new account combination from disabled accounts, you must enter a range that includes the disabled account since it is not possible to enter the disabled account.

  6. Enter the Target account specification for the accounts you want to create. You can also select your account segment values from the list of values.

    Note: The format of the target account specification must be the same as the format you use for the source account. For example, if you enter values only for some of your source segments, you must enter values for the same segments of your target account specification. If you enter a complete account as your source, you must enter a complete account for your target.

    Note: Target accounts that do not exist will be created if they pass cross-validation checking and segment security rules. Target accounts that exist but which are disabled will remain disabled after the mass creation operation.

  7. Continue entering account specifications until you are done.

  8. Choose the Submit button to save your work and start the mass creation process.

  9. Review the Mass Creation Execution Report for any errors. If necessary, correct any errors then resubmit the mass creation request.

    Note: Errors will occur if your target accounts violate cross-validation rules and segment security rules or if your mass creation transactions violate the prerequisites.

    If there are no errors, mass creation creates the new accounts and updates the status to Completed. If there are errors, no accounts are created and the status is changed to Failed.

Related Topics

Moving or Merging Account Balances

Defining a Move/Merge Request

Mass Maintenance Statuses

Correcting Misclassified Account Types

In the event you have an account with a misclassified account type that results in an erroneous Retained Earnings calculation, you can correct your account balances and the misclassified account type.

For example, after running a trial balance for the first period of your new fiscal year, you notice that your Cash account balance is zero. You discover that your Cash account was originally created with an account type of Expense rather than Asset. Therefore, when you opened the first period of your new fiscal year, General Ledger automatically closed out your Cash account balance to Retained Earnings.

Note: Follow the steps below in the exact order. If you changed Account Types from incorrect to correct values before starting these steps, you must reverse any changes before following these steps or the steps will not function properly.

To correct balances for a misclassified account:

  1. Reopen the last period of your prior fiscal year, if it is closed.

  2. Create a journal entry that brings the misclassified account balance to zero for the last day of the last period of your prior fiscal year. Use a temporary account such as Suspense for the offsetting amount.

    Note: If you have multiple currencies in the account balance, create journal entries to zero out entries in your ledger currency and to zero out entries for each foreign currency.

  3. Post the journal entry on the last day of the last period of your prior fiscal year.

  4. Verify that the misclassified account balance is zero by reviewing account balances online or in reports.

  5. Correct the account type of the misclassified account by changing the segment value qualifiers.

  6. Ask your System Administrator to correct the account type of all accounts referencing the misclassified account by updating the ACCOUNT_TYPE column in the GL_CODE_COMBINATIONS table using SQL*Plus.

  7. Restore the misclassified account balance by reversing the journal entry you posted in Step 3 above. Reverse the journal entry into the last day of the same period that it was originally posted.

  8. Post the reversing journal entry.

    Using the previous example, the Cash account type is now Asset so when you post the reversing journal entry, General Ledger rolls forward your Cash and Retained Earnings balances into the first period of the new fiscal year.

  9. Review the corrected account balances online or in reports.

Average Daily Balance Installations

If you have a misclassified account type, and the account type is misclassified to be an income statement account instead of a balance sheet account, or a balance sheet account instead of an income statement account, reverse all journals in all periods that have been posted to that account. Once you have posted the reversals, you can correct the account type, reverse the reversals, and then post.

You do not have to follow this procedure if you have a misclassified account type, and the account type is misclassified to be within the income statement group or within the balance sheet group of accounts.

Historical Rates

If, for example, you are reclassifying an account from an equity account to an expense account, and this account has an entry in the GL_HISTORICAL_RATES table, delete the historical rate information for this account if it is inappropriate. Re-enter the appropriate rates. The balances will be marked for re-translation.

Related Topics

Designing Your Accounting Flexfield, Oracle General Ledger Implementation Guide

Opening and Closing Accounting Periods, Oracle General Ledger Implementation Guide

Entering Journals

Posting Journals

Reversing Journals

Performing an Account Inquiry

Performing a Journal Entry Inquiry

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.

You can archive and purge actual, budget, or encumbrance balances. In addition, for translated actual and budget balances, you can purge them before you rerun your translation. Except for translated balances, you must archive balances or journals before you can purge them.

Note: If you have average balance processing enabled in your ledger, your average balances will be archived and/or purged automatically, at the same time that you archive and purge your standard balances.

To ensure better control over your archiving and purging process, we recommend that you perform your archive and purge in separate steps. If possible, run the archive utility when no users are on the system. This avoids degrading performance and response time during business hours.

Oracle General Ledger prevents you from archiving twice for the same period and ledger. However, if your archive process does not complete successfully, you can rerun the Archive program.

Data Access Set

Your data access set must provide full ledger access to the ledger in order to archive and purge its balances and journals. Full ledger access means that you have full read and write access to the ledger and all of its balancing segment values or management segment values.

Prerequisites

To archive account balances:

  1. Navigate to the Archive and Purge window.

  2. Select the Balances tab.

  3. Select the Archive Balances checkbox.

  4. Select a ledger.

    Your data access set must provide full read and write access to the ledger and all of its balancing segment values or management segment values.

  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. Navigate to the Archive and Purge window.

  2. Select the Journals tab.

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

  3. Select the Archive Journals checkbox.

  4. Select a ledger.

    Your data access set must provide full read and write access to the ledger and all of its balancing segment values or management segment values.

  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.

  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.

Related Topics

Creating a Tablespace for Archived Data

Exporting Archived Data

Purging Archived Account Balances and Journal Detail

Opening and Closing Periods, Oracle General Ledger Implementation Guide

Archive and Purge Audit Report

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.

Note: 'A' is for Actuals, 'B' is for Budgets, 'E' is for Encumbrances.

To calculate the amount of space you need for archiving:

  1. Determine how many actual rows for your ledger 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' or 'E'] 
    	and LEDGER_ID = 
    (select LEDGER_ID 
    from GL_LEDGERS 
    where NAME=[your ledger name]);	
    	

    For Budgets:

    select count(*)
    from GL_BALANCES
    where PERIOD_YEAR = [your archive year]	 
    and ACTUAL_FLAG = 'B'
    and BUDGET_VERSION_ID = 
    (select BUDGET_VERSION_ID from GL_BUDGET_VERSION 
    	where BUDGET_NAME = [your budget name])
    and LEDGER_ID = 
    (select LEDGER_ID
    from GL_LEDGERS
    where NAME=[your ledger 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'or'E']
    and LEDGER_ID = 
    (select LEDGER_ID 
    from GL_LEDGERS
    where NAME=[your ledger name]);
    
    

    For Budgets:

    select count(*)
    from GL_JE_BATCHES jeb 
    where DEFAULT_PERIOD_NAME in [list of periods]
    and ACTUAL_FLAG = 'B'
    and LEDGER_ID = 
    (select LEDGER_ID
    from GL_SETS_OF BOOKS
    where NAME = [your ledger name])
    and not exists
    (select 'Has wrong budget'
    from GL_JE_HEADERS jeh
    where JEH.JE_BATCH_ID = JEB.JE_BATCH_ID
    and BUDGET_VERSION_ID!=
    (select BUDGET_VERSION_ID from GL_BUDGET_VERSIONS
    where BUDGET_NAME = [your budget name]))
  3. Determine how many rows are in the GL_JE_HEADERS table for the fiscal year you want to archive using the following SQL statement:

    select count(*)
    from GL_JE_HEADERS
    where PERIOD_NAME in [list of periods]
    and ACTUAL_FLAG = ['A' or 'E']
    and LEDGER_ID = 
    (select LEDGER_ID
    from GL_SETS_OF BOOKS
    where NAME = [your ledger name]);

    For Budgets:

    select count(*)
    from GL_JE_HEADERS
    where PERIOD_NAME in [list of periods]
    and ACTUAL_FLAG = 'B'
    and BUDGET_VERSION_ID = 
    (select BUDGET_VERSION_ID from GL_BUDGET_VERSIONS
    where BUDGET_NAME = [your budget name]
    and LEDGER_ID = 
    (select LEDGER_ID
    from GL_SETS_OF BOOKS
    where NAME = [your ledger name])
  4. To determine how many rows are in the GL_JE_LINES table for the fiscal year you want to archive, use the following SQL statement:

    select count(*)
    from GL_JE_LINES
    where JE_HEADER_ID in
    (select JE_HEADER_ID from GL_JE_HEADERS
    where PERIOD_NAME in [list of periods]
    and ACTUAL_FLAG = ['A' or 'E']
    and LEDGER_ID in
    (select LEDGER_ID from GL_LEDGERS
    where NAME = [your ledger name'));

    For Budgets:

    select count(*)
    from GL_JE_LINES
    where JE_HEADER_ID in
    (select JE_HEADER_ID from GL_JE_HEADERS
    where PERIOD_NAME in [list of periods]
    and ACTUAL_FLAG = 'B'
    and BUDGET_VERSION_ID =
    (select BUDGET_VERSION_ID
    from GL_BUDGET_VERSIONS
    where BUDGET_NAME = [your budget name]
    and LEDGER_ID in
    (select LEDGER_ID from GL_LEDGERS
    where NAME = [your ledger name]));
  5. 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 in
    (select JE_BATCH_ID
    from GL_JE_BATCHES
    where DEFAULT_PERIOD_NAME in  [list of periods]
    and ACTUAL_FLAG= ['A'or'E']
    and LEDGER_ID = 
    (select LEDGER_ID 
    from GL_LEDGERS
    where NAME=[your ledger name]));

    For Budgets:

    select count(*)
    from GL_IMPORT_REFERENCES
    where JE_HEADER_ID in
    (select JE_HEADER_ID
    from GL_JE_HEADERS
    where DEFAULT_PERIOD_NAME in  [list of periods]
    and ACTUAL_FLAG = 'B'
    and BUDGET_VERSION_ID = 
    (select BUDGET_VERSION_ID from GL_BUDGET_VERSIONS 
    where BUDGET_NAME = [your budget name])
    and LEDGER_ID = 
    (select LEDGER_ID 
    from GL_LEDGERS
    where NAME=[your ledger name]));
  6. 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]
  7. 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.

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

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

Related Topics

Setting the Storage Parameters, Oracle General Ledger Implementation Guide

Archiving Account Balances and Journal Detail

Exporting Archived Data

After archiving your account balance and journal data, your Database Administrator should export the archive tables from your database to an operating system file.

To export the archive tables:

  1. Make sure there is enough disk space for your export file. You will need about 75% of the space you needed for your tables.

  2. Use the ORACLE RDBMS export utility to export the archive tables.

  3. Ensure your export was successful and verify that there are no errors. If you continue with the following steps without a successful export, you might lose valuable financial data. All of these conditions MUST be satisfied for a successful export:

    • Ensure that an export file has been created in your directory and that the file is not empty.

    • Look for normal completion of the export file (the last word of your export file should be 'EXIT').

    • Carefully monitor the export process while it is running, and look for error messages.

    • Ensure that the number of rows exported (as counted by the export program) is the same as the number of rows that you counted for the range of periods being archived.

    • Look for other errors in the log file, such as invalid parameters, errors in the export command line, not enough disk space for the export file, etc. The export process stops whenever it finds an error record.

    If there are errors, correct them and rerun the export.

  4. Copy the export file to tape.

    Note: Verify that your backup procedure is successful. If you fail to backup the export file successfully and continue with the next step, you might lose valuable financial data.

  5. After successfully completing the previous steps, delete the export file from your directory.

Related Topics

ORACLE RDBMS Utilities User's Guide

Purging Archived Account Balances and Journals

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

To purge account balances:

  1. Navigate to the Archive and Purge window.

    General Ledger displays your Ledger Name. You can only purge data for the current ledger.

  2. Select the Balances tab.

  3. Select the Purge Balances checkbox.

  4. Select a Ledger.

    Your data access set must provide full read and write access to the ledger and all of its balancing segment values or management segment values.

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

  6. Enter the Period To that you want to purge. General Ledger will purge balances for 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 delete the archived records from the GL_BALANCES table for the range of periods you requested.

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

  9. 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 GL_BALANCES table the following SQL command:

       drop table GL_BALANCES;
      
    • Import the GL_BALANCES

    • In order to archive the next period you need to drop the Archive tables.

      To drop your GL_ARCHIVE_BALANCES table run the following SQL command, substituting GL_JE_BATCHES, GL_ARCHIVE_HEADERS,GL_ARCHIVE_LINES, or GL_ARCHIVE_REFERENCES for the table name:

      drop table <table name>;

To purge journals:

  1. Navigate to the Archive and Purge window.

  2. Select the Journals tab.

  3. Select the Purge Journals checkbox.

  4. Select a Ledger.

    Your data access set must provide full read and write access to the ledger and all of its balancing segment values or management segment values

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

  6. Enter the Period To that you want to purge. General Ledger will only purge journals for one fiscal year at a time. To purge journals for multiple fiscal years, you must repeat the process for every fiscal year that you want to purge.

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

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

  9. 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_JE_BATCHES, 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.

  2. Select the Translated Balances tab.

  3. In the Ledger field, select a balance level reporting currency.

    Your data access set must provide full ledger access to the source ledger in order to purge translated balances for the ledger’s balance level reporting currency. Full ledger access means that you have full read and write access to the ledger and all of its balancing segment values or management segment values.

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

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

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

  7. Choose Purge. General Ledger submits a concurrent request to delete the translated balances for the range of periods you requested.

  8. Review the Archive and Purge Audit Report to ensure that the purge process completes successfully.