OPERA Full Purge Utility: Technical Details

OPERA's End-of-Day processing (Night Audit) offers several automatic data purge options that delete non-essential information based on age or other characteristics. However, these processes do not include purge of financial data associated with guest reservations.  Because of accounting and legal requirements, financial data is not commonly purged on a day-to-day basis. Nevertheless, this information can consume large amounts of disk storage space and database table space over time. As a result, it is sometimes acceptable and desirable to purge certain kinds of inactive financial information in a controlled fashion.

The purpose of the OPERA Full Purge utility is to provide a means for removing selected financial and data from the OPERA database when it is no longer needed. The Full Purge routine irrespective of the dates selected will not delete any reservations or associated records that have a departure date less than 2 years from the current business date.

The Full Purge utility includes several failsafe features:

• The routine ensures sufficient space for building purge tables, holding a purge log, and for storing historical copies of purged folios in PDF format.
• Data selected for purge is analyzed prior to processing to identify potential problems (e.g., out-of-balance conditions).
• Once the purge has begun, rollback of any changes is provided in the event processing of any individual reservation data cannot be completed successfully.
• Finally, the purge process provides real-time updating on the status of the run.

Restricted Functionality

• Once a reservation has been fully purged, the user can no longer run the Recalculation of Statistics.  Message populates when the user tries to select a date that has already had a purged reservation.
• When creating a PDF , look to the name_address table for the address info.
• Add name_address to the table purging when PDF is complete.
• FOLHIST has been updated to make sure it calls the PDF of purged reservations.

Accessing the Full Purge Utility

Sites need to be on Version 2.0.33.4 or higher to be able to access the Full Purge functionality via the Utilities menu.

For sites on Version 2.0.33.4 and higher, the Full Purge utility is accessed by selecting Utilities>Data>Full Purge from the OPERA menu bar. The Full Purge form appears. Only users having permission to access the OPERA Utilities, and specifically to access Full Purge, are able to access this feature.

At this point, the user is prompted for a cutoff date for the Full Purge. Financial transactions must have been completed before this date in order to be considered eligible for the purge. Default date will be 2 years prior to the properties current business date. The purge will only allow full years to purge due to the fact that we do not want fractions of a year purged but only full years.  A validation will check to make sure the entered date is 2 years back, when the start button is selected.

In addition, a password is required to run the Full Purge utility. The Full Purge password is provided to clients as needed by Oracle (currently controlled by Support). The password is designed to have a very limited lifetime it may be used only on a given date and only for purging data that was been created before the purge cutoff date. The password prompt is displayed on the Full Purge Utility form.

The affidavit at the end of this topic should be signed and returned to Oracle Support in order to obtain the required password.

After entering the Purge Before date and Password, the user selects Start. The password is verified, and if it is valid, the purge routine commences.  If user selects Close rather than Start, the password is not checked and the user is returned to the main Full Purge form.

Before You Start!

Before starting the full purge, make sure you run reports to verify data after the purge.

We recommend running the following reports:

• Guest Ledger
• Trial Balance
• Package Ledger
• Deposit Ledger
• AR Ledger
• AR aging (araging)
• AR activity (aractivity)
• AR detailed Aging (aragingdet)
• Transfer from FO (artransfer)
• Foiotax Revenue (foliotax_revenue)
• Profile Productivity (Prof_prod_los)
• Profile Productivity Detailed (profile_productivity_detailed)
• OTB Sales Statistics (otb_sales_statistics)
• Reservation Statistics (res_statistics)
• Market code Statiscits (stat_mark_seg)
• Commission reports

Recommended AR check before and after the purge:

Make sure that there is a reservation that will be purged that was settled to an AR account. Check the aging button on the AR account and verify the amount before the reservation is purged. When looking in the AR account and selecting the ALL radio button, verify the invoice is showing. Make sure you can print the invoice (folio) from within the details button of the invoice. The same invoice you should be able to print from Cashiering->Folio History after the purge.

Full Purge Utility Steps

The Full Purge utility performs a series of controlled steps described below. The title of each step displays on the Full Purge Utility form title bar as the step is performed.

1. Checking for available rollback space:

• Verify that enough free disk space is available on rollback segments and that the directory used for storing copies of folios contains sufficient megabytes of free disk space.
• Run the same query that is run in Night Audit.  Function: full_purge.check_free_tblspace(RBS);

  Note: The purge routine halts if there is insufficient space.

2. Checking for available table space:  

• Verify that opera_data table space has at least 10% free space available.
• Function: full_purge.check_free_tblspace(OPERA_DATA).

  Note: The purge routine halts if there is insufficient space.

Checking for Imbalances

Before the Full Purge proceeds, the utility first checks for financial imbalances. The purpose of these checks is to ensure that the data needed to investigate and correct an imbalance is not purged, which would impede resolution of the imbalance. The Full Purge utility starts the imbalance checks with the Guest Ledger and continues with the other OPERA ledgers. When an imbalance is found, a prompt appears notifying you of the date range where the imbalance occurred. The Full Purge stops at this point and you must use the Imbalance Analysis Utility to analyze and fix the imbalance before returning to the Full Purge utility and restarting the full purge. Imbalance dates are not provided for AR imbalances; in this case, you must run the Imbalance Analysis Utility's AR Health Check verification.

Imbalance checks are run for all dates up to the present, regardless of the dates selected for the data purge. For example, if the Full Purge is set to run Up To and Including 12/31/2003, but an imbalance exists for 01/01/05, the Full Purge will stop and will not continue until the imbalance is fixed.

3. Checking Guest Ledger for imbalances:

• Perform imbalance analysis in 5-day increments.
• Check for Guest Ledger and Trial Balance match.
• Function: full_purge.full_purge.check_guest_ledger.

  Note: The purge routine halts if the imbalance check fails.

4. Checking Package Ledger for imbalances:

• Perform imbalance analysis in 5-day increments.
• Check Package Ledger and Trial Balance match.
• Check that Effective Package Ledger is in balance.
• Function: full_purge.check_package_ledger.

  Note: The purge routine halts if the imbalance check fails.

5. Checking Deposit Ledger for imbalances:

• Perform imbalance analysis in 5-day increments.
• Check Deposit Ledger and Trial Balance match.
• Function: full_purge.check_deposit_ledger

  Note: The purge routine halts if the imbalance check fails.

6. Checking AR Ledger for imbalances: (only if the Accounts Receivable function is active)

• Perform imbalance analysis in 5-day increments.
• Check Trial Balance and ARAGING match.
• Function: full_purge.check_ar_ledger.

  Note: The purge routine halts if the imbalance check fails.

7. Checking for invalid Invoices:

• Perform imbalance analysis in 5-day increments.
• Check for invalid bills.
• Function: full_purge.check_invalid_bills.  This procedure will run different scripts depending on which patch the PMS is running.

  Note: The purge routine halts if the imbalance check fails.

8. Checking for checked out guests with balances:

• Perform imbalance analysis in 5-day increments.
• Function: check_cow_withbal.  

  Note: The purge routine halts if the imbalance check fails or if open folio reservations with balance exist.

9. Checking for passerby guest imbalances:

• Perform imbalance analysis in 5-day increments.
• Function: check_passerby.

  Note: The purge routine halts if the imbalance check fails.

10. All checks have been verified and completed.

• All financial and reservation data prior to <date> will be deleted. Do you want to continue?:

  Note: Respond Yes to continue with the purge. Respond No to halt the purge. No data has been purged at this point.

11. Retrieving list of reservations to be purged:

• Search for records in reservation_name that have a departure date prior to the cutoff date specified when the Full Purge process was started.
• For each resv_name_id that matches the above check, completes the following:
• Search through financial_transactions for charges.
• Check the payment method being used.  If it is not AR (direct bill) then continue without further checks.  If AR, then ensure that the invoice has been paid prior to the cutoff date.  If the invoice is still open in AR (there is no outstanding balance on the invoice), then do not purge this resv_name_id and window. If AR license is not on, than all AR data can be purged.
• COMMIT. If any errors are encountered, then ROLLBACK transactions and record in folio_purge_log.
• Process next resv_name_id until all rows have been processed.

12. Total number of reservations to be purged:

• Display the total number of reservations that pass the above checks.  

13. Storing Folio for Guest Last Name>Departure Date; window>#:

• Check how many windows have charges (1 to 4); print a separate PDF folio per window per guest. The PDF folio format used is Style 1 Detailed Folio (Standard).
• Print a folio for each window and store in specified directory. Directory is forms60output path in registry settings. This is the same as night audit. (i.e., D:\micros\opera\export\schema\property\folios\date).  The file name format is:

   RESV_NAME_ID||_||lower(last_name)||_||folio_window

• Check that the size of the PDF generated is larger than 0 bytes; verify that the reports background engine did not error on that folio.   If the folio is not generated, the routine will halt for that guest (showing Failed in the purge log) and continue with the next guest.  Any transactions processed already will rollback for this guest.
• If the user presses Cancel in the report background engine that guest will be marked as Failed in the purge log and all transactions will rollback for that guest.

14. Insert information into folio_purge_history

• As each folio is stored, a row is inserted into table folio_purge_history using the procedure full_purge.insert_into_history.
• Information such as the guest_name, first_name, arrival, departure, folio_view, bill amount and location of the PDF file is stored in this table.  If any part of the process fails, transactions are rolled back.

  Note: If this step fails, the folio will still be stored; however, rollback will result in the data remaining in the system.

15. Removing financial data for Guest Last Name>Departure Date; window>#: (function= delete_financials)

Function: full_purge.delete_financials runs. This function will attempt to delete from the following financial tables. If it fails, the function will return 'N' and will rollback the transaction.

financial_transactions

financial_transactions_jrnl

fixed_charges

Folios

Folio_detail

Folio_tax

Folio_tax_jrnl

financial_stat_daily

fin_tran_action_details

ar_credit_card_settle

fin$_tran_action

cr_card_settle

deferred_package_taxes

ar_invoice_header

Function: full_purge.delete_financials performs the following sequence of actions:

• If AR license is on, the function will check if there are any open invoices in table ar_invoice_header associated to this guest.  If there are open invoices, this resv_name_id will be skipped.
• If AR license is off, the rows are removed from ar_invoice_header without checking for open invoices.
• Deletions are made from the following tables using indexes where available:
  1. Fixed_charges
  2. Folios
  3. Folio_detail
  4. Fin_tran_action_details
  5. Folio_tax
  6. Folio_tax_jrnl
  7. Ar_credit_card_settle
  8. Cr_card_settle
  9. Fin$_tran_action
  10. Financial_transactions (Pass #1 removes taxes first)
  11. Financial_transactions (Pass #2 removes non tax transactions)
  12. Financial_transactions_jrnl

      Note: If this step fails, the folio will still be stored; however, rollback will result in the data remaining in the system.

16. Removing reservation for Guest Last Name>Departure Date; window>#: (function = delete_resv_data)

Function: full_purge.delete_resv_data runs. This function will attempt to delete from the following financial tables. If it fails, the function will return 'N' and will rollback the transaction.

ar_invoice_header_jrnl

asb_proforma_folios

award_profile_requests

award_profile_requests_log

award_requests

bde

bde_exceptions

bm_reservations

bm_reservation_delivery

cc$_transaction_log

commission_process_action

external_references

facility_schedule_dates

ifc_errors

ifc_wake

ifc_keys

ovos_noshow_extensions

ovos_resv_rotation_points

ovos_resv_transactions

ovos_rotation_overrides

ows_last_postings

reservation_awards

reservation_allowance_sharing

reservation_balances

reservation_children

reservation_name

reservation_name_jrnl

reservation_alerts

reservation_cancel_list

reservation_payment_methods

reservation_products

reservation_receipts

reservation_receipts_jrnl

reservation_name

reservation_product_prices

reservation_daily_elements

reservation_daily_element_name

reservation_alerts

reservation_cancel_policy

reservation_extensions

reservation_action

reservation_action_jrnl

reservation_memberships

reservation_deposit_schedule

reservation_virtual#

reservation_special_requests

reservation_comment

reservation_linkage_cnr

ra_data_elements

ra_data_elements_jrnl

reservation_regrets

reservation_promotions

guest_rsv_messages

guest_rsv_traces

Locators

export_bucket_amounts

trx_routing_instructions

voucher_details

voucher_header

vouchers_log

Note: If this step fails, the folio will still be stored; however, rollback will result in the data remaining in the system.

17. Completing purge for Guest Last Name>Departure Date; window>#: (function = delete_other_data)

Function: full_purge.delete_other_data runs. This function will attempt to delete from the following reservation tables. If it fails, the function will return N and will rollback the transaction.

commission_resv_data

computed_commissions

award_requests

call_header

Cc_auth_log

event$reservation

Note: If this step fails, the folio will still be stored; however, rollback will result in the data remaining in the system.

18. COMMIT

Allotment purge:

Purging table allotment_header and allotment_detail please note that this step might take some time as the performance of this step depends on how many records are actually in the allotment tables.

Clean up:

• Insert folio information into folio_purge_history table.
• Purging table folio_purge_log

During the cleanup we display messages to show which step is currently processed so that the user can determine that processing is still going on.

19. Completed. Reservations processed #:

Display the number of reservations that were successfully purged. Percentage completed is also shown.

20. Confirm Indexes to be rebuild

21. Rebuild Indexes

Rebuild Indexes is a separate menu option located in Utilities>Tools>Rebuild Indexes (See Rebuild Indexes for details). All the tables with the respective index will be marked after step 20 is completed. To start the rebuild, simply click on the rebuild button. Please note that a password is needed to start the index rebuild. The password is provided to clients as needed by Oracle (currently controlled by development).

Full Purge Parameters

The full purge options can be set with the parameters located on the Full Purge screen.

Property. Select the down arrow to choose the property for which the purge should run. Available when the OPP_MHOT Multi-property PMS add-on license is active.

Purge Up To and Including. This parameter allows the entry of any date which is at least 730 days + one full year in the past.

This stipulation ensures that OPERA keeps at least 2 FULL years of history. (An exception is made for inactive properties. See Purging Inactive Properties, below.)

For example:

Assume your property's business date = 22 September 2004, so 22 September minus 730 (2 years) = 23 September 2002.

The full purge requires that we keep at least 2 FULL years of history, hence the earliest purge date would be 31 December 2001.

Purging Inactive Properties

You may purge data that is less than 2 years old when the property is inactive. An inactive property is defined as one where there is a difference of at least 90 days between the current system date and the business date of the property (indicating at least 90 days have passed without a night audit).

If the 90-day difference exists, the Purge Up To and Including date will auto populate with the current business date of the property.

Waiting State (in secs). A value entered in this field represents the seconds the purge should wait before purging the next reservation once the purge is started.

Number to Process. If limiting the purge session to a number of reservations the purge will only purge the respective number of reservations and then stop.

Include Recent Stays. Leaving this check box unchecked will allow you to select from the Number of Days drop down list. The values listed in this drop down list are 365, 730,1095 and 1460. Selecting a value from this list means that  the full purge will only purge reservations linked to profiles older than 2 years AND the profile did not have a reservation in the last <number of selected days> e.g., 365, 730, 1095 or 1460. Checking the Include Recent Stays check box means that the full purge will purge reservations linked to profiles that are older than 2 years.

Generate Folios. Marking this check box will create folios and store them as pdf files on the file system.

New Tables

The Full Purge utility creates the following new tables.

FOLIO_PURGE_LOG

This table contains the table_name and number of rows purged for each resv_name_id by table. Note that doing a sum on the column rows_purged will give you a total number of rows deleted from the database. This Table will be purged at the end of the full purge.

Column Name	Type	Size	Description
PURGE_DATE	Date	10	Date purge was run
TABLE_NAME	Varchar2	30	Table being processed
ROWS_PURGED	Number		Total number of rows purged for that table for that reservation
RESORT	Varchar2	20	Property code
RESV_NAME_ID	Number		Resv_name_ID of failed guest
NAME_ID	Number		Name ID of the reservation purged
ERROR_MGS	Varchar2	500	Definition of the error message
PURGE_DATE	Date		Date of the physical purge
LOG_ACTION	Varchar2	20	Action that has been performed
ROWS_PURGED	Number		Number of rows purged

FOLIO_PURGE_RESV_LIST

This table contains details about the reservations purged.

Column Name	Type	Size	Description
RESV_STATUS	Varchar2	20	Reservation status
GUEST_LAST_NAME	Varchar2	80	Last name of guest
GUEST_FIRST_NAME	Varchar2	80	First name of guest
DEPARTURE	Date		Actual departure date
NAME_ID	Number		Name ID (Name Link)
ARRIVAL	Date		Actual arrival date
RESORT	Varchar2	20	Property code
RESV_NAME_ID	Number		Reservation Name ID (Reservation Link)
TRAVEL_AGENT_ID	Number		Travel Agent ID linked to the reservation.
SOURCE_ID	Number		Source ID linked to the reservation
COMPANY_ID	Number		Company ID linked to the reservation

FOLIO_PURGE_HISTORY

Folios can be accessed in Cashiering>Cashier Functions>Folio History.

This table contains the folio data fields.  This table will be added to the folio_history view so that purged profiles can be searched in a seamless fashion using the Folio History Cashiering option.

Index will be on Resort, Last Name, Date, Room Number

Column Name	Type	Size	Description
RESORT	Varchar2	20	Property code
PURGE_DATE	Date		Sys date on which the record was removed from the system
RECORD_TYPE	Varchar2	2	'H'  for reservation history (data migration info). Otherwise 'R' (actual reservation).
FOLIO_NO	Number		Folio number
BILL_NO	Number		Bill number
INVOICE_NO	Number		Invoice number
RESV_NAME_ID	Number		Reservation Name ID
ROOM	Varchar2	20	Room number
NAME_ID	Number		Name ID
LAST_NAME	Varchar2	40	Last name of guest
FIRST_NAME	Varchar2	40	First name of guest
WINDOW	Number		Window #
DEBIT_AMT	Number	22	Credit will equal debit
ARRIVAL	Date		Actual arrival date
DEPARTURE	Date		Actual departure date
FOLIO_PDF_NAME	Varchar2	500	D:\micros\opera\export\schema\property\folios\ date\'resvnameid_lastname_window#.pdf
PAYEE_NAME	Varchar2	80	Payee name
TRAVEL_AGENT_ID	Number		Travel Agent ID linked to the reservation
SOURCE_ID	Number		Source ID linked to the reservation
COMPANY_ID	Number		Company ID linked to the reservation

Assumptions

The following assumptions underlie use of the Full Purge utility.

• Once a date has been processed by the Full Purge utility, you may not run or re-populate the Trail Balance or the Daily Statistics for that date.
• The Room Statistics form (RMPDTL00) in System Configuration>Reservations>Rooms only shows the statistical data for the current fiscal year.  Therefore, the statistical revenue shown here will be unaffected by the Full Purge.
• The imbalance analysis for the full purge will only be checked the first time the form is opened. If the form is closed, than the next time it is opened, recheck the imbalances.
• For sites earlier than Version 2.1, Patch 34, the recalculation of statistics should not be run for any departure date that has already been purged.
• For sites on Version 2.1, Patch 34 or higher, the recalculation of statistics will not be allowed for any date that has already been purged.

Performance Data

A test run on a large database yielded the following performance figures:

Run Time	230 minutes
Folios Stored	1389 (Avg. 1.22 folios per reservation)
Reservations Processed	1142
Total Rows Purged from Database	99628
Average Time per Reservation	12.1 sec
Average Time to Store Each Folio	9.9 sec
Average Delete Rate	7.22 rows per sec

A live beta site with a large database yielded the following performance figures:

Run Time	36.5 hours
Folios Stored	Folio option OFF
Reservations Processed	66319
Total Rows Purged from Database	2.9 million
Average Time per Reservation	1.94 sec sec
Average Time to Store Each Folio	N/A
Average Delete Rate	21.60 rows per sec
Avg. Res Delete Rate	1816 per hour
Archive Log Creation During Purge	1 x 32 archive log created approx. every 5 minutes
Disk Space Returned After Index Rebuilds	Approx. 3 Gig

Balance Analysis

Originally it was thought that the balance form will need to include purged information during execution of the imbalance verification.   As the full purge will only run if the hotel is 100% in balance, the following changes have been made:

1. When the user runs an imbalance check for a date that has purged profiles, the balance form will notify the user that purged profiles exist and it will then change the from date to be the purge date+1 and the end date to be purge date+31.

We know that the hotel is in balance before the full purge was run and that now the purge date has become the new effective 'go-live' date.

2. The users cannot repopulate the trial balance from the balance form or from the backend for dates that are on or before the purge date.  A message will appear advising the user this.

3.  The opening and closing balances on the trial balance will not be affected for the following reasons:

Business Date= '02-jan-2002'
Go live Date='01-jan-1999'
Purge Date <='02-jan-2000'

Say for example the latest departure date for a purged reservation was purged was 25- dec-1999. This date is referenced as the last purge date.   The trial balance cannot be regenerated on or before that date.  IE 26-dec-1999 is the first day the trial balance and be repopulated or regenerated.

Before Full Purge:

25-dec-1999

Trail Balance	GLedger
opening balance	400.00
closing balance	500.00

After Full Purge:

Trail Balance	GLedger
opening balance	400.00
closing balance	500.00

26-dec-1999

Trail Balance	GLedger
opening balance	500.00
closing balance	300.00

If the user repopulates the 26-dec-1999, the opening balance will not change as financial_transactions has not been touched for the 26-dec-1999.  The full purge routine does not update the trial balance during the purging so the 25-dec-1999 opening and closing balances remain the same as it was from before the full purge run.

Release Notes Extract

Full Purge Utility - A new menu item called Full Purge can be found under the Utilities > Data option. This routine allows properties to remove historical reservations and associated data such as financials, journal entries, etc., from the OPERA application. The Full Purge will only delete reservations and associated records having a reservation departure date older than 2 years from the current business date. Preparation for this type of purging was added into the End of Day monthly Profile Purge routine (see the Release Notes OPERA User End-of- Day section).  During the Full Purge routine OPERA will create PDF files of detailed folios for each window belonging to a reservation stay that is being purged. This will in turn allow properties to continue to make use of the folio history option in cashiering for these reservations, and to access the associated financial data these folios contain.

Prior to starting the purge, the routine automatically ensures that sufficient rollback and table space exists for building purge tables, holding the purge details, and for storing historical copies of purged folios in PDF form (dont worry the amount of space necessary to hold this information is minimal). In addition, the routine ensures that all ledgers are in balance. As the Full Purge runs, OPERA dynamically indicates the number of reservations pending purge as well as the percentage completed.

A checkbox has been added to the Full Purge form (RULL_PURGE), if this checkbox is unchecked, no PDF folios will be created.

When the Full Purge runs, OPERA will retrieve all reservations that are eligible for purging (eligible reservations are those that have a departure date greater than the cutoff date specified in the Full Purge setup form).  If the payment method is direct bill, the system will verify that the invoice does not have an open balance or a paid date on the invoice that does not meet the cutoff date specified. If the invoice has an open balance, that reservation and corresponding financial records will not be purged

Next, the folios will be stored as PDFs having a filename in the following format: RESVNAMEID_last name_foliowindow.  These folios are placed in a folder that is appended to the forms output path, for example, D:\micros\opera\export\schema\property\folios\<date>. The <date> portion of the path reflects the departure date of the guest.

After the folio PDF is created, OPERA deletes all rows for the reservation, including all associated financial table rows.

A Folio Purge History table is updated for each profile that is purged in order to be able to call these folios from within Folio History. The following information is recorded in this table.

• Folio number
• Window
• Bill number
• Debit amount
• Invoice number
• Arrival
• Resv_name_id
• Departure
• Room
• Folio_pdf_name
• Last_name
• Payee name
• First_name

When the Full Purge is complete a message informs the user of the number of reservations that have been successfully purged.

The Full Purge option is secured by a special password, which, until the routine has completed its full beta/pilot testing, will be kept under strict control of Oracle Development.

Profile Cleanup Utility - The Utilities > Profile > Profile Cleanup option has gone through some changes.  Not only has the look and feel of the screen been updated, but several procedures behind the purge routine have been both optimized and modified.  Of course, only profiles that meet the criteria will be displayed in the Profile Cleanup form.

Previously, during the course of the Profile Cleanup, Individual profiles would have simply been marked with an Inactive status; however, now when the profile is selected and marked for purge, the same process that would occur in the monthly End of Day Profile Purge routine will take place (i.e., updating the respective tables with the NAME ID Purged-Individual, removal of associated table rows and the deleting of the profile). See the Release Notes OPERA User End-of-Day section for details.

Questions and Answers

Question	Answer
Current date is March 2004: A guest stayed in 1999 once and then never again had any other reservations. Will this profile be deleted after the full purge?	The full purge will not delete any profiles hence the profile will still be available. Deletion of profiles is handled via the daily night audit purge routines and is dependent on the "Save in History" flag and the "Inactive_days_for_guest_purge setting.However, History and Summary records will not show anything anymore for purged dates.
A Guest stayed in 1999 and still has a balance on his AR account. Will any of the financial records be purged for this guest?	No - the full purge checks for any invoices with balances. If there are invoices with balance, the full purge routine will not delete any records linked to this reservation.
Will the full purge check for invoices with balance?	If the AR license is ON the full purge will always check for invoices with balance.
Will I be able to view the folio for a folio which has been purged?	If the full purge is performed with the checkbox "store folios" checked then all purged folios are stored in a PDF file and are accessible for viewing after the purge. If the checkbox "store folios" was not checked during the full purge the folios can no longer be accessed.
After running the purge, can I print Zero Balance AR statement for purged records?	No, however, AR accounts with a balance on the invoice will not be purged so you can still run the AR statement for invoices with balance.
A guest stayed 3 times in total. 1year ago he stayed 2 times and 1 time 3 years ago. Can I still view the folio for this Profile.	Yes, if the “store folio checkbox” had been selected, then you can still preview the folio.
Will I still see the Detailed profile productivity for a profile that has purged reservations?	You will still be able to see profile productivity, however the profile productivity report (profile_productivity_detailed)  will only show productivity for dates which have not been purged.
Can I still print generic statistics reports?	Yes, generic statistic reports which are based on reservation_stat_daily table are not affected by the full purge.
If I purge date (for example) date before 01- 01-2002, does it mean the statistic/histories for a particular profile will also be deleted?	The report Profile Production Statistic (profileproductivitystat) will still show the statistic even if reservations from the past have been purged. This is due to the fact that this report is based on reservation_daily_stat which is not affected by the full purge. The report Profile Productivity Detailed (profile_productivity_detailed) however will be affected by the number of reservations that have been linked to the respective profile.
Can I still Recalculate past daily statistics?	You can only recalculate past statistics to the date for which the full purge has not been run. Hence you could recalculate past statistics only for the last 2 years since the full purge only purges data older then 2 years.
How is the Profile History summary screen (PR_HISTORY) for Individual, Agent, Source and Company affected by the full purge?	The summary screen pr_history is not affected due to the fact that it is pulling information from statistic tables which are not affected by the full purge. However, the detailed records of the pr_history will not tally up with.
Why did my Trial Balance not change?	We do not delete from the Trial Balance Table and we disable the re-population of records for dates already purged.
Why is the pause button not working right away?	The purge finishes the records it is currently working on and before it moves to the next record it will pause the purge.
What downtime is required and what effect does it have on the system speed?	In general there is no downtime required for the purge. The system speed during the purge is still acceptable. However, if system speed becomes increasingly slow then try to use the wait time parameter on the full purge form. A good wait period would be 5 seconds between each record of purge, this will give the database time to “catch up” and the purge will not be noticed as much in terms of system speed. If you use folio printing with your full purge, you do not need to set the wait state since the printing process will give the database some time to “catch up”.
Will I still see the Profile overview?	The profile overview will only be able to show data for years which have not been purged.
Will I still see complete production for all past years in my CRM system?	This depends on your CRM system. Some external CRM systems keep their own history in that case the history will not be deleted in the external CRM system.
Can the purge be stopped?	Yes, simply click the pause button first and then press the close button.
Why is the purge slower when printing folios is on?	If the printing folios flag is checked on the full purge form, folios will be created as pdf files and stored on the Application server (thin client) or on the local client if thick client is used. The printing of the folios takes time and hence the purge takes longer.
Will the purge delete full years only?	Yes, the purge will only be allowed to run for full years, hence, always keeping a full year of history.
Will the full purge delete Profiles?	No, the full purge does not touch profiles at all.
Can you list the statistical tables that are affected by the full purge?	None of the statistical tables are affected by the full purge. Refer to the above list of tables that are purged.
I have compared the profile screen of the same guest before purge and after purge, realized that after the purge the LAST STAY information is also gone.	Once the reservations for a particular profile have been purged, this information is no longer in the system; hence you can also not see it in the profile. If you are concerned about reservations getting purged for guests that have stayed in the last year then simply leave the “include recent stay” flag unchecked and these reservations (all of them) will stay untouched.
Folios that are supposed to purge are not purged (e.g., guest has 2 stays in 1999), based on the criteria selected, the history should be purged, but it is not.	If the reservation was checked out to AR and the AR account still has an open balance, the reservation will not get purged.
I have checked the tables prior to the purge and compared it after the purge and realized that nothing from the name table has been purged.	We do not purge profiles during the full purge; hence the name table will not be touched.
How will the print folio flag on the full purge influence the purge performance?	If you decide to print folios during full purge, the purge will approx. take 4 times longer as without folio printing. Check with the hotel first if there are paper backups already, this will help you with the decision if you need folio printing.
I checked the disk space after the purge but there is no change?	Make sure that the rebuild indexes has been completed after the full purge. Only after completing a rebuild indexes, the disk space will be freed.
My purge date is 31-Dec-2002 and I have a reservation in the system that has stayed in November of 2002 but the AR account has only been paid in February 2003. Will this reservation be purged?	The full purge will not delete any reservations if any of the linked financial transaction records are outside of the purge date range date range. Hence this reservation and all linked financial transactions will NOT be deleted.

Tables Not Affected by Purge

The following are the major statistic tables that are NOT affected by the full purge:

AGENT_STAT_DAILY

COMPANY_STAT_DAILY

CONTACT_STAT_DAILY

GROUP_STAT_DAILY

MARKET_STAT_DAILY

PROFILE_STAT_DAILY

RATE_CODE_STAT_DAILY

REP_MANAGER

RESERVATION_STAT_DAILY

RESERVATION_STAT_DAY_SUM

SOURCE_PROF_STAT_DAILY

SOURCE_STAT_DAILY

THIS STATEMENT MUST BE SIGNED AND RETURNED TO ORACLE SUPPORT BEFORE THE FULL PURGE UTILITY PASSWORD WILL BE PROVIDED.


I hereby confirm that I have read and understand the Full Purge Technical Details document. I am acknowledging that the purging of data removes information permanently from the system and that information will no longer be available for retrieval.

It is strongly recommended that before running the full purge against a live production schema, a satisfactory test run has been completed on a copy of the live production database and a full backup of the live production schema is kept for security.



The full purge should be performed in monthly increments to ensure a better control of timing and database performance.




________________________________ __________________
Authorized Customer Signature Date



________________________________

Printed Name