1 Data Purge

Data purging is based upon logical sets of data. Logical sets of data can be contained in multiple tables. An example of a logical set of data is all the records associated to a particular retail transaction.

A purge of a logical set is not considered complete until all relevant rows of data are deleted.

Data purging is based upon a data-retention schedule whereupon all data existing prior to the computed date is purged. The data within this time frame must meet constraints as required. For example, if a customer wants to retain the last 180 days worth of retail transaction data, then the integer 180 should be passed into the purge retail transaction routine and the system will purge completed transactions more than 180 days old.

The stored procedures read the absolute value of a negative integer. For example, a value of -30 passed into the stored procedures is read as 30, and the data is retained for 30 days.

If no value is passed into the stored procedures, the default value is used. The default value is 30.

The number of data retention days is passed into the stored procedures. The constraints are built into the stored procedures and are therefore not parameterized.

A logical set purge succeed seven if data is not found in an expected table.

The Financial History and Financial Summary data purge scripts do not address the issue of the weekly sum of daily totals that no longer match weekly totals. For example, if the purge occurs on a Wednesday, the sum of the daily totals from Wednesday through Saturday will not match the weekly total that was based upon a Sunday through Saturday time frame.

Caution:

Passing in a zero (0) as a parameter to the purge transaction routines results in the deletion of all completed transactional data. Oracle is not responsible for loss or damage of any sort that might incur from passing in zero as a parameter.

The retailer is fully responsible for the database configuration. Oracle assumes the purge routines operate within the confines of the database configuration, such as the size of the rollback segments and other such parameters that might affect the functioning of the purge routines.

Invoking Stored Procedures

The following are examples of how to invoke stored procedures for an Oracle database.

Note:

It is assumed that the user calling the stored procedures has the necessary privileges to invoke these procedures.

Stored Procedures on Oracle

The following examples show how to invoke the stored procedures on an Oracle database:

Example 1-1 Invoking The Stored Procedures—SQL Plus Method 1

SQL> EXECUTE <procedure name (parameters)>;
 
Example:
 
EXECUTE PURGE_FN_SMY(90);

Example 1-2 Invoking The Stored Procedures—SQL Plus Method 2

SQL> BEGIN
SQL> <procedure name (parameters)>;
SQL> END;
 
Example:
 
SQL> BEGIN
SQL> PURGE_FN_SMY(90);
SQL> END;

Example 1-3 Invoking The Stored Procedures—SQL Plus Method 3

SQL> CALL  <procedure name (parameters)>;
 
Example:
 
CALL PURGE_FN_SMY(90);

You can choose to create a script file that contains these commands and have a scheduler execute the script on a nightly basis. To do this, you must be logged in to the database.

The scheduler must be able to log in to the database to run the scripts, or the login must be the first line in the script.

Calls to Invoke Stored Procedures

Table 1-1 contains the calls to use to invoke the stored procedures.

Table 1-1 Stored Procedure Calls

Subject Area	Procedure Call	Description
Advanced Pricing Rules	PURGE_PRDV(<Number of Retention Days>)	Removes all advanced pricing rules older than the specified number of retention days.
Control Transactions	PURGE_CTL_TRN(<Number of Retention Days>)	Removes all store operation, void, no sale, and bank deposit transactions older than the specified number of retention days.
Cross Channel Order	PURGE_COMMERCE_ORD(<Number of Retention Days>)	Removes all cross channel order-related transactions older than the specified number of retention days.
Duplicate Price Changes	PURGE_DUP_PRC_CHN_EV()	Removes all duplicated price change events older than the current date.
Employee Biometrics	PURGE_EMP_BIO	Removes all employee biometric data no longer associated with an employee.
EJournal	PURGE_EJRL(<Number of Retention Days>)	Removes all EJournal tape records older than the specified number of retention days.
Financial Histories	PURGE_FN_HST(<Number of Retention Days>)	Removes all financial totals from history tables older than the specified number of retention days.
Financial Summaries	PURGE_FN_SMY(<Number of Retention Days>)	Removes all aggregate financial totals from summary tables older than the specified number of retention days.
Financial Transaction	PURGE_FN_TRN(<Number of Retention Days>)	Removes all financial transaction-related tables that are older than the number of retention days specified and that meet the identified constraints. It then executes the PURGE_TRN procedure.
Layaways	PURGE_LY(<Number of Retention Days>)	Removes all layaways in the completed, deleted, suspended, and voided status older than the specified number of retention days.
Orders	PURGE_ORD(<Number of Retention Days>)	Removes all orders in the complete, canceled, suspended, and voided status older than the specified number of retention days.
Orders Common	PURGE_ORDER_COMMON	Removes all immediate order-related child tables that meet the passed in criteria. Note: This routine is only called by other purge routines and not directly executed.
Price Changes	PURGE_PRC_CHN_EV(<Number of Retention Days>)	Removes all permanent price changes older than the specified number of retention days.
Promotions	PURGE_TMP_PRC_EV(<Number of Retention Days>)	Removes all expired temporary price changes (promotions) older than the specified number of retention days.
Retail Transactions	PURGE_RTL_TRN(<Number of Retention Days>)	Removes all sale, return, house account payment, instant credit enrollment, and redeem transaction types older than specified number of retention days.
Retail Transaction Common	PURGE_RTL_TRN_COMMON	Removes immediate retail transaction-related tables and also executes other purges common to all retail transactions that meet the passed in criteria. Note: This routine is only called by other purge routines and not directly executed.
Retail Transaction Line Item	PURGE_RTL_TRN_LTM	Removes all immediate retail transaction-related child tables that meet the passed in criteria. Note: This routine is only called by PURGE_RTL_TRN_COMMON and not directly executed.
Sale Return Line Item	PURGE_SLS_RTN_LTM	Removes all immediate SaleReturnLineItem-related child tables that are older than the number of retention days specified. Note: This routine is only called by PURGE_RTL_TRN_COMMON and not directly executed.
Tender Line Item	PURGE_TND_LTM	Removes tender line items from a specific transaction. In the script, a specific transaction ID has to be specified.
Training Mode Transactions	PURGE_TRG_TRN(<Number of Retention Days>)	Removes all transactions performed in training mode older than the specified number of retention days.
Transaction	PURGE_TRN	Removes all transaction header tables that meet the purge criteria.

Data Purge Scripts

The following data purge scripts are available for Back Office, Central Office, Point-of-Service, and Returns Management:

CreateProcedurePurgeAdvancedPricing.sql
CreateProcedurePurgeControlTransaction.sql
CreateProcedurePurgeCrossChannelOrder.sql
CreateProcedurePurgeDuplicatePriceChangeEvent.sql
CreateProcedurePurgeEJournal.sql
CreateProcedurePurgeEmployeeBiometrics.sql
CreateProcedurePurgeFinancialHistory.sql
CreateProcedurePurgeFinancialSummary.sql
CreateProcedurePurgeFinancialTransaction.sql
CreateProcedurePurgeLayaway.sql
CreateProcedurePurgeOrder.sql
CreateProcedurePurgeOrderCommon.sql
CreateProcedurePurgePriceChangeEvent.sql
CreateProcedurePurgePromotionEvent.sql
CreateProcedurePurgeRetailTransaction.sql
CreateProcedurePurgeRetailTransactionCommon.sql
CreateProcedurePurgeRetailTransactionLineItem.sql
CreateProcedurePurgeSaleReturnLineItem.sql
CreateProcedurePurgeTenderLineItem.sql
CreateProcedurePurgeTrainingModeTransaction.sql
CreateProcedurePurgeTransaction.sql

Restricting Access To Data Purge Scripts

For more information about restricting access to data purge scripts, see the Oracle Retail POS Suite Security Guide.