Saturday, June 06, 2015

GL Data Purge


In order to Purge GL Data, Following steps are needed.

  1. Close GL Period Permanently. Follow these steps to close GL Period Permanently. when you update status of a period to Permanently Closed, all other prior periods too get permanently closed. When you save the changes, form will submit concurrent program Periods - Permanently Close Period

  2. Before purging, set storage parameter for GL Archive tables using form "GLXSTSPM". This form is available to responsibility General Ledger Super User. Navigation for this form is "Setup -> System -> Storage".

  3. GL Data can be archived and Purged using form GLXSTPRG. This form is accessible to responsibility General Ledger Super User. Navigation for this form is "Setup -> System -> Purge". If you do not have access to responsibility General Ledger Super User then you may use this link to determine which other responsibility have access to that form.

  4. In order to Purge GL data, it must be archived first. GL Data is purged from following six tables

    1. GL_BALANCES
    2. GL_JE_BATCHES
    3. GL_JE_HEADERS
    4. GL_JE_LINES
    5. GL_IMPORT_REFERENCES
    6. GL_JE_SEGMENT_VALUES

  5. The GL data from above tables is archived into following tables

    1. GL_ARCHIVE_BALANCES
    2. GL_ARCHIVE_BATCHES
    3. GL_ARCHIVE_HEADERS
    4. GL_ARCHIVE_LINES
    5. GL_ARCHIVE_REFERENCES
    6. GL_ARCHIVE_SEGMENT_VALS

  6. Data could be archived for a range of periods. Once data is archived, it could be exported for future reference

  7. After archiving GL data for a period or range of periods, you can not archive GL data for another period. Before you could archive another period, You need to do the following

    1. Purge GL data. If GL data is archived for multiple periods then purge could be performed one period at time

    2. Export archived data for future reference. This data not be loaded back into GL tables. Oracle does not recommend it. You have to develop custom programs if you intend to review archived data. This step is optional

    3. When all archived data is purged, Truncate/Delete archived data

  8. GL_ARCHIVE_HISTORY: This table has information about archive and purge runs. This table is used for List of value for Archive and Purge "Periods To" in "Archive and Purge" form (GLXSTSPM)

  9. When we purged GL data, Purge process was slow. We disabled few indexes on GL_BALANCES and GL_JE_LINES and purge performance was acceptable.

1 comment:

  1. If any custom package for the same requirement purge and archive. If any please post here.

    ReplyDelete