| Oracle® Retail Store Inventory Management Operations Guide Release 14.0 E50034-02 |
|
 Previous |
 Next |
| Oracle® Retail Store Inventory Management Operations Guide Release 14.0 E50034-02 |
|
Previous |
Next |
Data purging is based upon a data-retention schedule whereupon all data existing prior to the computed date are purged. The data within this timeframe must meet data integrity constraints as well as functional requirements.
SIM Data purging programs are meant to provide the basic implementation of logical selection of sets of data based on functional requirement and data retention periods. The data retention periods are defined in SIM system configuration table. Logical sets of data may exist in multiple tables.
With this assumption, SIM purge shell scripts execute the java EJB which calls the oracle PL/SQL stored procedures to delete the records. The purging logics are resides in PL/SQL stored procedures, Customer may choose the their preferred purging strategy and implement their own purging implementation.
Relevant progress messages are logged with regard to purge batch program runtime information. The location of sim batch log and logging levels can be configured in log4j.xml file which is located in <sim_batch_location>/resources/conf/log4j.xml.
The user running the batch process must have write permission on the directory into which the sim batch log is written, or the batch process will not run. If it is not acceptable to give the batch user permission for the default log directory, log4j.xml must be configured to use a different directory.
|
Note: The purge PL/SQL programs may save the detailed exceptions into PURGE_ERROR_LOG table. DBA needs to purge the data periodically. |
The following section summarizes SIM's data Purge programs and includes both an overview of each purge program business functionality, and scheduling notes.
Table 5-1 Data Purge Program List and Dependencies
| Purge Name | Description | Dependencies |
|---|---|---|
|
PurgeAdHocStockCount |
Deletes AD HOC stock counts with a status of in progress which schedule date was X days in the past. |
No dependencies |
|
PurgeAll |
Deletes records from the SIM application that meet certain business criteria. |
No dependencies |
|
PurgeAudits |
This batch process deletes audits. |
No dependencies |
|
PurgeBatchImpExp |
This batch process deletes batch import export records. |
No dependencies |
|
PurgeCompletedUINDetail |
This batch deletes completed UIN Detail records. |
No dependencies |
|
PurgeDeletedUsers |
This batch program deletes users that have a status of deleted and have been held in the system for a number of system defined days. |
No dependencies |
|
PurgeDSDReceivings |
This batch process deletes the Direct Store Delivery receiving. |
No dependencies |
|
PurgeFulfillmentOrders |
This batch deletes closed fulfillment order records. |
No dependencies |
|
PurgeInvalidUserRoles |
This batch program removes all expired or orphaned roles from the users in the system. |
No dependencies |
|
PurgeInventoryAdjustments |
This batch process deletes inventory adjustments. |
No dependencies |
|
PurgeInventoryAdjustTemplate |
This batch deletes inventory adjustment templates. |
No dependencies |
|
PurgeItem |
This batch program deletes the items that are in deleted status (D). |
No dependencies |
|
PurgeItemBaskets |
This batch process deletes item baskets. |
No dependencies |
|
PurgeItemPrice |
This batch deletes expired or deleted item prices. |
No dependencies |
|
PurgeItemRequests |
This batch process deletes item requests. |
No dependencies |
|
PurgeItemTickets |
This batch process deletes item tickets. |
No dependencies |
|
PurgeLockings |
This batch process deletes lockings. |
No dependencies |
|
PurgePriceChangesWorksheet |
This batch deletes completed/rejected price change work sheet records. |
No dependencies |
|
PurgePriceHistories |
This batch process deletes price histories. |
No dependencies |
|
PurgePurchaseOrders |
This batch process deletes purchase order records. |
No dependencies |
|
PurgeReceivedTransfers |
This batch process deletes received transfers. |
No dependencies |
|
PurgeRelatedItems |
This batch will delete related items. |
No dependencies |
|
PurgeResolvedUINProblems |
This batch process deletes resolved item serial number problems. |
No dependencies |
|
PurgeSalesPosting |
This purges the sales, returns, void sales and void returns transaction from the staging table. |
No dependencies |
|
PurgeShelfReplenishment |
This batch deletes completed or canceled Shelf replenishment records. |
No dependencies |
|
PurgeStagedMessage |
This batch process removes processed integration staging records (MPS Staged Messages). |
No dependencies |
|
PurgeStockCounts |
This batch process deletes stock counts. |
No dependencies |
|
PurgeStockReturns |
This batch process deletes stock returns. |
No dependencies |
|
PurgeStockItemStockHistory |
This batch will delete store item stock history inventory movement records. |
No dependencies |
|
PurgeTemporaryUINDetail |
This batch process deletes temporary UIN detail records. |
No dependencies |
|
PurgeUINDetailHistories |
This batch process deletes UIN detail history records (UIN Audit information). |
No dependencies |
|
PurgeUserCache |
This batch program deletes expired user cache records. |
No dependencies |
|
PurgeUserPasswordHistory |
This batch program deletes user password history records. |
No dependencies |
|
PurgeWHDReceivings |
This batch process deletes the Warehouse delivery receiving records. |
No dependencies |
Most SIM purge programs can be scheduled to run at any time (ad hoc) with no particular order, while some of purge programs may need to run in a particular order to provide optimal results. Table 5-2, "Purge Scheduling Notes" provides some scheduling recommendations:
Table 5-2 Purge Scheduling Notes
| Batch Name | Schedule Type | Successor Depends on Success of Predecessor | Notes |
|---|---|---|---|
|
DeactivateOldUsers�PurgeDeletedUsers�PurgeUserPasswordHistory�PurgeInvalidUserRoles�PurgeUserCache � |
Ad hoc |
Not required.�The successor batch runs regardless of success/failure of the predecessor batch. |
These batches should run on a continuous basis to ensure tight security and appropriate access to SIM. |
|
PurgeDSDReceivings�PurgePurchaseOrders�PurgeReceivedTransfers�PurgeStockReturns�PurgeWHDReceivings�PurgeFulfillmentOrders |
Ad hoc |
Not Required |
Run these purge batches in logical order to provide optimal results. |
|
PurgePriceChangesWorksheetPurgeItemPrice |
Daily |
Not Required |
These batch should run at least once per day to remove the expired item price from the transaction table to ensure to optimized price retrieving response time. |
|
PurgeItem |
Ad hoc |
Not Required |
This purge should run after all other purges. |
The following section summarizes SIM's purge processes and includes both an overview of each purge process business functionality, assumptions, and scheduling notes for each purge program.
This batch program deletes ad hoc stock counts with a status of in progress. Any ad hoc stock count with a creation date/time stamp older than the Days to Hold In Progress Ad Hoc Counts parameter value will be deleted. For example, the default value is 1. If the batch program is run with the default value, the batch program would delete all in-progress counts more than 24 hours old.
This process deletes records from the SIM application that meet certain business criteria (for example, records that are marked for deletion by the application user, records that linger in the system beyond certain number of days, and so on).
It is the wrapper batch which contains all purge batches which listed in the purge list.
This batch process deletes audit records. Any audit record with a create date/timestamp older than the Days To Hold Audit Records parameter value is deleted. For example, if the default value is 30 and the batch program is run with the default value, the batch program would delete all the audit records that are more than 30 days old.
This batch process deletes batch import export records. Any import export record with an end date/timestamp older than the Days To Hold Completed Staging Records parameter value and with the Status value of 2 (COMPLETED) is deleted. For example, if the default value is 30 and the batch program is run with the default value, the batch program would delete all the records that are more than 30 days old and are in completed status.
This batch program deletes completed UIN Detail records. A completed UIN is any UIN with a status of Removed from Inventory, Missing, Sold, Shipped to Vendor, or Shipped to Warehouse. Any UIN detail record with a complete status and update date at least X days in the past (where X is with system parameter DAYS_TO_HOLD_COMPLETED_UINS) will be deleted from ITEM_UIN and ITEM_UIN_PROBLEM table.
This batch process finds users marked as deleted with an end date that is at least X days in the past (where X is the system parameter SECURITY_DAYS_TO_HOLD_DELETED_USERS). These users and all associated data are deleted from SECURITY_USER_ROLE, SECURITY_USER_STORE, SECURITY_USER_PASSWORD, and SECURITY_USER tables.
This batch process deletes the Direct Store Delivery receivings.
Any DSD record which is in Closed/Cancelled status and which has a complete date older than Days to Hold Received Shipments is an eligible record for purge.
However, before a DSD record is purged, checks are made to ensure that the purchase order associated with a particular DSD is also completed and is older than Days to Hold Purchase Orders.
In effect a DSD record can be purged only if its associated PO records can be purged.
This batch process will delete all the fulfillment order records which are not in New or In Progress status and for which the update date has expired the purge_date by number of days more than Days to Hold Customer Order parameter value. Additionally, only those fulfillment orders will be deleted for which customer order ID and fulfillment order ID combination does not exist for any Transfer, Return, Purchase Order, and Warehouse delivery transaction.
This batch program removes all expired user roles and orphaned user roles (roles that were deleted by removing a store) from the SIM system.
The batch process finds user role assignments that have an end date that is at least X days in the past (where X is specified by the system parameter SECURITY_DAYS_TO_HOLD_EXPIRED_USER_ROLES), and deletes these expired role assignments. The users (excluding super users) with role assignments that have no matching store assignments (orphaned role assignments) are also deleted from SECURITY_USER_ROLE table.
This batch process deletes inventory adjustments. Any inventory adjustment record with a create date/timestamp older than Days To Hold Completed Inventory Adjustments parameter value will be deleted. For example, the default value is 30. If the batch program is run with the default value, the batch program would delete all the inventory adjustment records, which are more than 30 days old.
This batch process deletes inventory adjustment template records for which the status is cancelled and the record (approve date) has satisfied the DAYS_TO_HOLD_CANCELLED_TEMPLATE value which is specified in CONFIG_SYSTEM table.
This batch program deletes items with a status of Delete (D).
There are two segments in the PurgeItem Batch which do the following different tasks:
Validate if the Item should be deleted.
Delete item from all associated tables if validation check is passed.
Validate if the item should be deleted. The Validations include:
If SOH of item, item parent and item grandparent is 0
If any transfers exist for item, item parent and item grandparent
If any RTV exists for item, item parent and item grandparent
If any Inventory adjustment exists for item, item parent and item grandparent and so on.
If any Item Basket exists for the item.
If any Product Group exists for the item.
If any Stock Count exists for the item.
If any Store Order exists for the item.
If any Item Request exists for the item.
If any Direct Store Delivery exists for the item.
If any Warehouse Delivery exists for the item.
Delete item from all associated table. If the validations checks are met, the records related to the item which is marked for the purge action are deleted.
This batch process deletes item baskets. Any item basket record with a process date or timestamp older than batch date value is deleted.
This batch process purges records which were expired or were marked as deleted from ITEM_PRICE table based on the retention period. The retention period is specified by system configuration parameter DAYS_TO_HOLD_EXPIRED_ITEM_PRICE.
Rules defining records to be purged:
Regular Price Change: Has status of completed or deleted, effective date was X number of days in the past (relative to the specified date if specified). At any given time, at least one completed latest regular price must be retained in ITEM_PRICE for a store item.
Promotion Change: Has status of completed or deleted, and end date is number of days in the past (relative to the specified date if specified).
Clearance Change: Has status of completed or deleted, and end date is number of days in the past (relative to the specified date if specified).
This batch process deletes item requests which are in Cancelled/Completed status. Any item request record with a process date/timestamp older than DAYS_TO_HOLD_CANCELLED_ITEM_REQUESTS system configuration value will be deleted. For example, the default value is 30. If the batch program is run with the default value, the batch program would delete all the item request records, which are more than 30 days old.
This batch process deletes item tickets which are in Printed/Canceled status. Any item tickets record with a status date/timestamp older than DAYS_TO_HOLD_ITEM_TICKETS system configuration value will be deleted. For example, the default value is 30. If the batch program is run with the default value, the batch program would delete all the item ticket records, which are more than 30 days old.
This batch process deletes lockings records from ACTIVITY_LOCK table. Any lock record with a lock date/timestamp older than DAYS_TO_HOLD_SHELF_REPLENISHMENT system configuration value will be deleted. For example, the default value is 30. If the batch program is run with the default value, the batch program would delete all the shelf replenishment records, which are more than 30 days old.
This batch process deletes price change worksheet records which are in Rejected/Completed status. Any price change record with an effective date/timestamp older than Days To Hold Price Changes parameter value will be deleted. For example, the default value is 30. If the batch program is run with the default value, the batch program would delete all the price change records, which are more than 30 days old.
This batch process deletes price histories. At least a minimum of 4 historical prices are maintained for an item/store. Days To Hold Price History will determine the number of days that price histories can be kept in the database.
This batch process deletes closed purchase order records. The purchase order records which are in closed status and complete date is at least X days in the past (where X is system parameter DAYS_TO_HOLD_COMPLETED_PURCHASE_ORDERS) are deleted from the database.
This batch process deletes received transfers. The transfer in and transfer out transactions will be purged from the database. The transfer out transactions which are in Rejected/Cancelled Request/In Progress/Received/Cancelled Transfer will be purged if the records are older than Days To Hold Received Transfer Records parameter. Also, the Purge Received Transfers parameter must be set to Yes in the admin screen to enable purging of the received transfers.
This batch process deletes the related items for which the end date has expired for more than Days To Hold Related Items system configuration value.
This batch process deletes resolved UIN exception records. UIN exception records with status of resolved and resolved date is at least X days in the past (where X is system parameter DAYS_TO_HOLD_RESOLVED_UIN_EXCEPTIONS) are deleted from ITEM_UIN_PROBLEM table.
This batch process deletes the Point-of-Service transaction from the Oracle Retail Point-of-Service transaction staging table. It reads the Days to Hold Sales Posting configuration parameter and all the transactions which are present beyond the configuration parameter are deleted. It also purges the POS transaction logs for the request IDs that are in processed status.
This batch process deletes shelf replenishment lists which are in Completed/Cancelled state. Any shelf replenishment list record with a post date/timestamp older than Days To Hold Pick Lists parameter value will be deleted. For example, the default value is 30. If the batch program is run with the default value, the batch program would delete all the pick list records, which are more than 30 days old.
This batch finds integration staging records that are marked as processed or deleted, and update date is at least X days in the past (where X is the system parameter DAYS_TO_HOLD_COMPLETED_STAGING_RECORDS), the batch process deletes these records from MPS_STAGED_MESSAGE table.
Rebuilding the indexes on the MPS_STAGED_MESSAGE table each day is recommended after batch process completes.
PurgeStagedMessageJob.sh <purge_date>
Where purge_date is optional and the date format must be dd/MM/yyyy if purge_date is specified.
|
Note: The optional date is the point of reference the script should use. The script uses current date for the point of reference by default, but can be run from any reference point.Therefore, if purging records that are 10 days old, and running the script without the optional date, the process removes all records older than 10 days from the current date. If the optional date argument is specified, records 10 days older than the specified optional date are purged. |
This batch process deletes stock counts which are in Completed/Cancelled status. Any stock count with a schedule date/timestamp older than Days To Hold Completed Stock Counts parameter value will get deleted. For example, the default value is 30.If the batch program is run with the default value, the batch program would delete all the stock return records, which are more than 30 days old.
This batch process deletes stock returns which are in Dispatched/Cancelled status. Any stock return record with a completed date/timestamp older than DAYS_TO_HOLD_RETURNS system configuration value will be deleted. For example, the default value is 30.If the batch program is run with the default value, the batch program would delete all the stock return records, which are more than 30 days old
This batch process deletes store item stock history records which have been kept for more than Days To Hold Transaction History which is specified by system configuration. For example, the default value is 30. If the batch program is run with the default value, the batch program would delete all the stock history records, which are more than 30 days old.
This batch process deletes temporary UIN detail records. UIN detail records with no status and update date is at least X days in the past (where X is system parameter DAYS_TO_HOLD_TEMPORARY_UINS) are deleted from ITEM_UIN table.
This batch process deletes UIN detail history records (UIN Audit Information). UIN Audit information could be purged for a UIN while the UIN is still open within the system. Open UIN is any UIN that is in one of the following statuses:
In Stock
In Receiving
Reserved for Shipping
Unavailable
UIN history records with open status and an update date at least X days in the past (where X is system parameter DAYS_TO_HOLD_UIN_AUDIT_INFORMATION) are deleted from ITEM_UIN_HISTORY table.
This batch program deletes expired user cache records which include expired user authentication caches and authorization cache records.
The cached user passwords with a cache date that is at least X hours in the past (where X is the system parameter SECURITY_USER_AUTHENTICATION_CACHE_HOURS) will be deleted.
The batch process also find users with a cache date that is at least X hours in the past (where X is the system parameter SECURITY_USER_AUTHORIZATION_CACHE_HOURS). All cached role and store assignments are deleted for these users.
If the users still have existing role assignments, store assignments, or passwords then the user's cache date is set to null.
If the user has no existing assignments or passwords then the user is deleted from AC_USER_PASSWORD, AC_USER_ROLE, AC_USER_STORE, and AC_USER tables.
This batch process finds users with more than X passwords (where X is the system parameter PASSWORD_NUMBER_OF_PREVIOUS_TO_DISALLOW), and deletes the oldest passwords that exceed this limit.
|
 Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|