16 Sales Posting

Oracle Retail Merchandising System (RMS) includes a convenient interface with your point-of-sale system (POS) that allows you to efficiently upload sales transaction data. Once the data enters RMS, other modules take over the posting of that data to sales transaction, sales history, and stock-on-hand tables. This overview describes the upload and validation of sales transaction data from your POS to RMS and the relevant processes.

Creating a POSU File

The RMS Sales Posting module, uploadsales.ksh requires a POSU file that is rolled up to the item/store/price point level. There are a variety of ways to create this file:

• If you use Oracle Retail Xstore Point of Service, the integration via Oracle Retail Sales Audit (ReSA) will create appropriate POSU files.

• If you integrate your POS and Oracle Retail Sales Audit (ReSA), out of the box integration between ReSA and RMS will produce POSU files.

• If you integrate your OMS (Order Management System) and Oracle Retail Sales Audit (ReSA), out of the box integration between ReSA and RMS will produce POSU files.

• If you use a 3rd party POS or Order Management System (OMS) and do not use ReSA, you must use a custom process to roll up data to an item/store/price point level

• Additional information about the structure of the POSU file is available in the detailed discussion of the uploadsales.ksh process.

Sales Posting Business Process

The Sales Posting Process consists of a number of related programs.

1. uploadsales.ksh reads the POSU file and writes it's contents to a series of staging tables.

   1. uploadsales_all.ksh wraps uploadsales.ksh to simplify the process of running uploadsales.ksh for groups of POSU files.

2. salesprocess.ksh reads the staged data and performs major validation, financial and inventory processing. Details of this processing are below in the detailed discussion of salesprocess.ksh.

3. salesgenrej.ksh creates a reject file for transactions that fail salesprocess.ksh validation.

4. salesuploadarch.ksh archives successfully processed transactions and clears them out of the staging tables.

5. salesuploadpurge.ksh purges transactions from the archive tables after the transactions age out of the system.

   Figure 16-1 Sales Posting Business Process

   
   

Batch Design Summary

The following batch designs are included in this chapter

• uploadsales.ksh (Upload POSU File for Processing)

• uploadsales_all.ksh (Process Multiple POSU Files)

• salesprocess.ksh (Main Processing of Staged Sale/Return Transactions)

• salesgenrej.ksh (Reject POSU Transactions)

• salesuploadarch.ksh (Archive Successfully Posted Transactions)

• salesuploadpurge.ksh (Purge Aged Archived POSU Transactions)

• sales_reprocess.ksh (Re-processing of Sale/Return Transactions Due to Chunk Not Process Issue)

uploadsales.ksh (Upload POSU File for Processing)

Module Name	uploadsales.ksh
Description	Upload POSU File for Processing
Functional Area	Sales Posting
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS112
Runtime Parameters	NA

Design Overview

The purpose of this module is to upload the contents of the POSU file from ReSA or 3rd Party POS to the staging table for further processing.

Scheduling Constraints

Table 16-1 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This program runs in the background. When a POSU file comes in and is detected, this module initiates the sales posting process
Pre-Processing	saexprms.pc (if the client uses ReSA to produce POSU files)
Post-Processing	salesprocess.ksh
Threading Scheme	NA

Restart/Recovery

NA

Locking Strategy

NA

Security Considerations

NA

Key Tables Affected

Table 16-2 Key Tables Affected

Table	Select	Insert	Update	Delete
SVC_POSUPLD_LOAD	No	Yes	No	No
SVC_POSUPLD_STATUS	No	Yes	No	No
SVC_POSUPLD_STAGING	Yes	Yes	Yes	No
V_SVC_POSUPLD_LOAD	Yes	No	No	No

Security Considerations

NA

Integration Contract

Integration Type	Upload to RMS
File Name	POSU_<store>_<tran_date>_<sysdate>.<thread_val>
Integration Contract	IntCon000044

Input File Layout

Table 16-3 Input File

Record Name	Field Name	Field Type	Default Value	Description
File Header	File Type Record Descriptor	Char(5)	FHEAD	Identifies file record type
	File Line Identifier	Number(10)	Specified by external system	ID of current line being processed by input file
	File Type Definition	Char(4)	POSU	Identifies file as ’POS Upload'
	File Create Date	Char(14)	NA	Date file was written by external system
	Location Number	Number(10)	NA	Store identifier
	Vat include indicator	Char(1)	NA	Determines whether or not the store stores values including vat. Not required but populated by Oracle Retail sales audit
	Vat region	Number(4)	NA	Vat region the given location is in. Not required but populated by Oracle Retail Sales Audit
	Currency code	Char(3)	NA	Currency of the given location. Not required but populated by Oracle Retail sales audit
	Currency retail decimals	Number(1)	NA	Number of decimals supported by given currency for retails. Not required but populated by Oracle Retail sales audit
Transaction Header	File Type Record Descriptor	Char(5)	THEAD	Identifies transaction record type
	File Line Identifier	Number(10)	Specified by external system	ID of current line being processed by input file
	Transaction Date	Char(14)	Transaction date	Date sale/return transaction was processed at the POS
	Item Type	Char(3)	REF or ITM	Item type will be represented as a REF or ITM
	Item Value	Char(25)	NA	The ID number of an ITM or REF
	Dept	Number(4)	NA	Dept of item sold or returned. Not required but populated by Oracle Retail Sales Audit
	Class	Number(4)	NA	Class of item sold or returned. Not required but populated by Oracle Retail Sales Audit
	Subclass	Number(4)	NA	Subclass of item sold or returned. Not required but populated by Oracle Retail Sales Audit
	Pack Indicator	Char(1)	NA	Pack indicator of item sold or returned. Not required but populated by Oracle Retail Sales Audit
	Item level	Number(1)	NA	Item level of item sold or returned. Not required but populated by Oracle Retail Sales Audit
	Tran level	Number(1)	NA	Tran level of item sold or returned. Not required but populated by Oracle Retail Sales Audit
	Wastage Type	Char(6)	NA	Wastage type of item sold or returned. Not required but populated by Oracle Retail Sales Audit
	Wastage Percent	Number(12)	NA	Wastage Percent*10000 (4 implied decimal places.), wastage percent of item sold or returned. Not required but populated by Oracle Retail Sales Audit
	Transaction Type	Char(1)	S - sales R - return	Transaction type code to specify whether transaction is a sale or a return
	Drop Shipment Indicator	Char(1)	Y N	Indicates whether the transaction is a drop shipment or not. If it is a drop shipment, indicator will be 'Y'. This field is not required, but will be defaulted to 'N' if blank
	Total Sales Quantity	Number(12)	NA	Total sales quantity * 10000 (4 implied decimal places), number of units sold at a particular location
	Sales Sign	Char(1)	P - positive	UOM at which this item was sold
	Sales Sign	Char(1)	P - positive N - negative	Determines if the Total Sales Quantity and Total Sales Value are positive or negative
	Total Sales Value	Number(20)	NA	Total Sales Value * 10000 (4 implied decimal places), sales value, net sales value of goods sold
	Last Modified Date	Char(14)	NA	For VBO future use
	Catchweight Indicator	Char(1)	NULL	Indicates if the item is a catch weight item. Valid values are ’Y' or NULL
	Actual Weight Quantity	Number(12)	NULL	Actual Weight Quantity*10000 (4 implied decimal places), the actual weight of the item, only populated if catchweight_ind = ’Y'
	Sub Trantype Indicator	Char(1)	NULL	Tran type for ReSA Valid values are ’A', ’D', NULL
	Total Igtax Value	Number(20)	NA	Total Igtax Value * 10000 (4 implied decimal places), goods sold or returned
	Sales Type	Char(1)	NA	Indicates whether the line item is a Regular Sale, a customer order serviced by OMS (External CO) or a customer order serviced by a store (In Store CO). Valid values are ’R','E', or 'I'
	No Inventory Return Indicator	Char(1)	NA	Contains an indicator that identifies a return without inventory. This is generally a non-required column, but in case of Returns, this is required. Valid values are ’Y' or 'N'
	Return Disposition	Char(10)	NA	Contains the disposition code published by RWMS as part of the returns upload to OMS
	Return Warehouse	Number(10)	NA	Contains the physical warehouse ID for the warehouse identifier where the item was returned
Transaction Tax	File Type Record Descriptor	Char(5)	TTAX	Identifies the file record type
	File Line Identifier	Number(10)	Specified by external system	Sequential file line number
	Tax Code	Char(6)	NA	Holds the tax code associated to the item
	Tax Rate	Number(20)	NA	Tax rate*10000000000(10 implied decimal places), holds the tax rate for the tax code associated to the item
	Total Tax Value	Number(20)	NA	Total Tax value*10000(4 implied decimal places), total tax amount for the line item
Transaction Detail	File Type Record Descriptor	Char(5)	TDETL	Identifies transaction record type
	File Line Identifier	Number(10)	Specified by external system	ID of current line being processed by input file
	Promotional Tran Type	Char(6)	NA	Code for promotional type from code_detail, code_type = ’PRMT'
	Promotion Number	Number(10)	NA	Promotion number from the RMS
	Sales Quantity	Number(12)	NA	Sales quantity*10000 (4 implied decimal places.), number of units sold in this prom type
	Sales Value	Number(20)	NA	Sales value*10000 (4 implied decimal places.), value of units sold in this prom type
	Discount Value	Number(20)	NA	Discount quantity*10000 (4 implied decimal places.), value of discount given in this prom type
	Promotion Component	Number(10)	NA	Links the promotion to additional pricing attributes
Transaction Trailer	File Type Record Descriptor	Char(5)	TTAIL	Identifies file record type
	File Line Identifier	Number(10)	Specified by external system	ID of current line being processed by input file
	Transaction Count	Number(6)	Specified by external system	Number of TDETL records in this transaction set
File Trailer	File Type Record Descriptor	Char(5)	FTAIL	Identifies file record type
	File Line Identifier	Number(10)	Specified by external system	ID of current line being processed by input file
	File Record Counter	Number(10)	NA	Number of records/transactions processed in current file (only records between fhead & ftail)

Design Assumptions

Multiple taxes for an item if sent from POS to ReSA, will be summed to a single tax in RMS and assigned one of the applicable tax codes.

Rolling up transactions to the item/store/price point

The program uploadsales.ksh requires that transactions be rolled up the item/store/price point level. The tables below give a hypothetical (though not particularly realistic) example of the type of rollup required by upload_sales.ksh.

Table 16-4 Sales for Item Number 1234 (at one store during one period of the day)

Transaction Number	Number of Items Sold	Amount (in specified currency unit)	Price point (price reason)
167	1	9.99	Regular
395	2	18.00	Promotional
843	1	7.99	Clearance
987	3	27.00	Promotional
1041	1	9.99	Regular
1265	4	31.96	Clearance

Note: The variation of the price per item in different transactions. This is the result of the price applied at the time of sale—the price point. Now look at the next table that shows the same transactions rolled up by item and price point.

Table 16-5 Sales for Item Number 1234

Number of Items Sold	Price Reason (price point)	Price point (price reason) Total Amount for Item Price point (in currency)
2	Regular price	19.98
5	Promotional price	45.00
5	Clearance price	39.95

uploadsales.ksh takes the totals and looks for any discounts for transactions in the POSU file. It applies the discounts to an expected total dollar amount using the price listed for that item from the pricing table (PRICE_HIST). It next compares this expected total against the reported total. If the program finds a discrepancy between the two amounts, it is reported. If the two totals match, the rollup is considered valid. If value-added tax (VAT) is included in any sales transaction amounts, it is removed from those transactions prior to the validation process.

uploadsales_all.ksh (Process Multiple POSU Files)

Module Name	uploadsales_all.ksh
Description	Process Multiple POSU Files
Functional Area	Sales Posting
Module Type	Integration
Module Technology	Ksh
Catalog ID	RMS157
Runtime Parameters	NA

Design Overview

The purpose of this script is to execute the uploadsales.ksh module for all POSU files that are for upload. This wrapper will simplify the sales upload process for multiple POSU files, removing the need to call the uploadsales.ksh individually for each file.

Scheduling Constraints

Table 16-6 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This program runs in the background. When a POSU file comes in and is detected, this module initiates the sales posting process.
Pre-Processing	saexprms.pc (if the client uses ReSA to produce POSU files)
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Locking Strategy

NA

Security Considerations

NA

Key Tables Affected

Table 16-7 Key Tables Affected

Table	Select	Insert	Update	Delete
SVC_POSUPLD_LOAD	No	Yes	No	No
SVC_POSUPLD_STATUS	No	Yes	No	No
SVC_POSUPLD_STAGING	No	Yes	No	No
V_SVC_POSUPLD_LOAD	Yes	No	No	No

Security Considerations

NA

Integration Contract

Integration Type	Upload to RMS
File Name	POSU_<store>_<tran_date>_<sysdate>.<thread_val>
Integration Contract	IntCon000044

Input File Layout

Refer to the Input File Layout section in uploadsales.doc.

salesprocess.ksh (Main Processing of Staged Sale/Return Transactions)

Module Name	salesprocess.ksh
Description	Main Processing of Staged Sale/Return Transactions
Functional Area	Sales Posting
Module Type	Business Processing
Module Technology	ksh
Catalog ID	RMS151

Design Overview

The purpose of the SALESPROCESS.KSH module is to process sales and return details from an external point of sale system (either POS or OMS). The sales/return transactions will be validated against Oracle Retail item/store relations to ensure the sale is valid, but this validation process can be eliminated if the sales that are being passed in, have been screened by sales auditing (ReSA). The following common functions will be performed on each sales/return record read from the input file:

• Read sales/return transaction record

• Lock associated record in RMS

• Validate item sale

• Check whether TAX maintenance is required, and if so determine the TAX amount for the sale.

• Write all financial transactions for the sale and any relevant markdowns to the stock ledger.

• Post item/location/week sales to the relevant sales history tables

• Perform last sales processing to maintain accurate sales information in the system

Scheduling Constraints

Table 16-8 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This program is a trickle polled as point-of-sales data, in the form of the POSU file, becomes available. It can be run multiple times a day in a trickle-polling environment Can be run ad hoc to trickle poll sales
Pre-Processing	uploadsales.ksh
Post-Processing	salesgenrej.ksh salesuploadarch.ksh
Threading Scheme	The number of threads running in parallel is based on value in the column RMS_PLSQL_BATCH_CONFIG.MAX_CONCURRENT_THREADS with the program name ”CORESVC_SALES_UPLOAD_SQL”. Threading is based on chunks Each chunk would have a defined size. This is defined in RMS_PLSQL_BATCH_CONFIG.MAX_CHUNK_SIZE. Chunks could be made up of a single or multiple THEAD/Items. Because multithreading logic based on chunks is applied, it is possible that a record is locked by another thread. Without a mechanism to perform waiting/retrying, record locking errors would happen more frequently In the table RMS_PLSQL_BATCH_CONFIG, RETRY_LOCK_ATTEMPTS contains the number of times the thread will try to acquire the lock for a table and RETRY_WAIT_TIME is the number of seconds the thread will wait before it retries

POSU Chunking

Table 16-9 Concurrent Threads and Chunk Size

MAX_Concurrent Threads	MAX_CHUNK_SIZE
2	3

Number of Threads: 11

Thread 1	Chunk 1	THEAD 1	Item 1
Thread 1	Chunk 1	THEAD 2	Item 1
Thread 1	Chunk 1	THEAD 3	Item 2
Thread 1	Chunk 1	THEAD 4	Item 2
Thread 1	Chunk 1	THEAD 5	Item 3
Thread 2	Chunk 2	THEAD 6	Item 5
Thread 2	Chunk 2	THEAD 7	Item 6
Thread 2	Chunk 2	THEAD 8	Item 7
Thread 3	Chunk 3	THEAD 9	Item 8
Thread 3	Chunk 3	THEAD 10	Item 9
Thread 3	Chunk 3	THEAD 11	Item 10

In this run, threads would be allocated first to chunks 1 and 2. The other threads would only be picked up once either thread 1 or 2 has finished its processing.

Restart/Recovery

The logical unit of work for salesprocess.ksh is a set of a single or multiple valid item sales transactions at a given store location. This set is defined as a chunk. Based on the example above, if for some reason, chunk 2 raised an error, THEAD 4, 5, and 6 wouldn't be posted in RMS. Other chunks, if there are no errors, would be processed. User has to correct the transaction details and upload the updated POSU file that includes the affected THEAD lines for reprocessing.

Locking Strategy

Since the sales upload processes are run multiple times a day in a trickle-polling system, a locking mechanism is put in place to allow on-line transactions and the salesprocess.ksh module to run at the same time. The following tables would be locked for update:

• ITEM_LOC_SOH

• ITEM_LOC_HIST

• ITEM_LOC_HIST_MTH

• VAT_HISTORY

• EDI_DAILY_SALES

• DEAL_ACTUALS_ITEM_LOC

• DAILY_SALES_DISCOUNT

• INVC_MERCH_VAT

• RTV_HEAD

Because multithreading logic based on chunks is applied, it is possible that a record is locked by another thread. Without a mechanism to perform waiting/retrying, record locking errors would happen more frequently.

In the table RMS_PLSQL_BATCH_CONFIG, RETRY_LOCK_ATTEMPTS is the number of times the thread will try to acquire the lock for a table and RETRY_WAIT_TIME is the number of seconds the thread will wait before it retries. Once the number of retries is equal to the limit defined, the whole chunk wouldn't be processed. This would create a reject file with which the user can use to upload again to the staging table.

Security Consideration

NA

Key Tables Affected

Table 16-10 Key Tables Affected

Table	Select	Insert	Update	Delete
VAT_HISTORY	No	Yes	Yes	No
DAILY_SALES_DISCOUNT	No	Yes	Yes	No
LOAD_ERR	No	Yes	No	No
STORE	Yes	No	No	No
CURRENCIES	Yes	No	No	No
CLASS	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
DEPS	Yes	No	No	No
RPM_PROMO	Yes	No	No	No
RPM_PROMO_COMP	Yes	No	No	No
DEAL_HEAD	Yes	No	No	No
DEAL_COMP_PROM	Yes	No	No	No
DEAL_ACTUALS_FORECAST	Yes	No	No	No
ITEM_LOC	Yes	No	No	No
ITEM_LOC_SOH	Yes	No	Yes	No
VAT_ITEM	Yes	No	No	No
ITEM_SUPP_COUNTRY	Yes	No	No	No
ITEM_SUPPLIER	Yes	No	No	No
SUPS	Yes	No	No	No
TERMS	Yes	No	No	No
PRICE_HIST	Yes	No	No	No
TEMP_TRAN_DATA	No	Yes	No	No
ITEM_LOC_HIST	Yes	Yes	Yes	No
ITEM_LOC_HIST_MTH	Yes	Yes	Yes	No
EDI_DAILY_SALES	Yes	Yes	Yes	No
ORDHEAD	Yes	Yes	No	No
INVC_HEAD	Yes	Yes	No	No
INVC_MERCH_VAT	Yes	Yes	Yes	No
INVC_XREF	No	Yes	No	No
INVC_DETAIL_TEMP2	No	Yes	No	No
INVC_DETAIL	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No
UOM_CLASS	Yes	Yes	No	No
ITEM_XFORM_HEAD	Yes	No	No	No
ITEM_XFORM_DETAIL	Yes	No	No	No
ITEM_SUPP_COUNTRY_LOC	Yes	No	No	No
TRAN_DATA	No	Yes	No	No
INVC_DETAIL_TEMP	No	Yes	No	No
INVC_HEAD_TEMP	No	Yes	No	No
CONCESSION_DATA	No	Yes	No	No
DEAL_ACTUALS_ITEM_LOC	Yes	Yes	Yes	No
V_PACKSKU_QTY	Yes	No	No	No
IF_ERRORS	No	Yes	No	No
RTV_HEAD	Yes	No	No	No
SVC_POSUPLD_LOAD	Yes	Yes	Yes	No
SVC_POSUPLD_STATUS	Yes	Yes	Yes	Yes
SVC_POSUPLD_STAGING	Yes	No	Yes	Yes
RMS_PLSQL_BATCH_CONFIG	Yes	No	No	No
V_SVC_POSUPLD_LOAD	Yes	No	No	No
SVC_POSUPLD_STAGING_REJ	No	Yes	No	No

Integration Contract

Integration Type	Upload to RMS
File Name	N/A; at this point, the POSU data has already been uploaded to the staging tables
Integration Contract	IntCon000103

The module will have the ability to re-process a POSU reject file directly. The file format will therefore be identical to the input file layout for the uploadsales.ksh process. A reject line counter will be kept in the program and is required to ensure that the file line count in the trailer record matches the number of rejected records. If no errors occur, no reject files would be generated.

Design Assumptions

Tax Handling:

POS can send either transactional level tax details in TTAX lines or item-level tax details in IGTAX lines through the RTLOG file to ReSA. These tax details will be passed on to RMS in the TTAX lines of the POSU file. Even though POS can pass multiple IGTAX/TTAX lines to ReSA and from ReSA to RMS, RMS only supports one tax code per item. If multiple taxes for an item are sent from POS to ReSA, they will be summed to a single tax in RMS sales upload process and assigned one of the applicable tax codes when writing tran_data 88.

Financial Transactions

The salesprocess.ksh writes transaction records to the TRAN_DATA table primarily through its write_tran_data function. From the entire list of valid transaction codes (For the full list of transaction codes, see the chapter ”General ledger batch” in this volume of the RMS Operations Guide), for the column TRAN_CODE, salesupload.ksh writes the following:

Table 16-11 Transaction Records

Transaction Code	Description
01	Net Sales (retail & cost)
02	Net sales (retail & cost) where - retail is always VAT exclusive, written only if system_options.stkldgr_vat_incl_retl_ind = Y
03	Non-inventory Items Sales/Returns
04	Customer Returns (retail & cost)
05	Non-inventory VAT Exclusive Sales
06	Deal Income (sales)
11	Markup (retail only)
12	Markup cancel (retail only)
13	Permanent Markdown (retail only)
14	Markdown cancel (retail only)
15	Promotional Markdown (retail only), including ’in-store' markdown
20	Purchases (retail & cost)
24	Return to Vendor (RTV) from inventory (retail & cost)
60	Employee discount (retail only)

Note: Where value-added-tax is enabled (system_options table, stkldgr_vat_incl_retl_ind column shows ’Y') and the retail accounting method is also enabled, salesupload.ksh writes an additional transaction record for code 02. Any items sold on consignment—where the department's items are stocked as consignment, rather than normal (see the DEPS table, profit_calc_type column)—are written as a code 20 (Purchases) as well as a 01 (Net Sales) along with all other applicable transactions, like returns. The 20 reflects the fact that the item is purchased at the time it is sold, in other words, a consignment sale.

salesgenrej.ksh (Reject POSU Transactions)

Module Name	salesgenrej.ksh
Description	Reject POSU Transactions
Functional Area	Sales Posting
Module Type	Business Processing
Module Technology	KSH
Catalog ID	RMS338

Design Overview

The purpose of this module is to archive the rejected transactions and create a reject file based on the recently processed POSU file which is still in the staging table.

Scheduling Constraints

Table 16-12 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This program is executed after salesprocess.ksh Can be run ad hoc to trickle poll sales
Pre-Processing	salesprocess.ksh
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 16-13 Key Tables Affected

Table	Select	Insert	Update	Delete
SVC_POSUPLD_LOAD	Yes	No	No	No
SVC_POSUPLD_STAGING	Yes	Yes	No	Yes
SVC_POSUPLD_REJ_RECS	No	Yes	No	No
V_SVC_POSUPLD_LOAD	Yes	No	No	No

Reject File:

The module will have the ability to re-process the reject file directly. The file format will therefore be identical to the input file layout. A reject line counter will be kept in the program and is required to ensure that the file line count in the trailer record matches the number of rejected records. If no errors occur, no reject files would be generated.

salesuploadarch.ksh (Archive Successfully Posted Transactions)

Module Name	salesuploadarch.ksh
Description	Archive Successfully Posted Transactions
Functional Area	Sales Processing
Module Type	Admin
Module Technology	Ksh
Catalog ID	RMS340

Design Overview

The purpose of this module is to archive the successfully posted transactions, and clear the staging table.

Scheduling Constraints

Table 16-14 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	Can be run ad hoc to trickle poll sales
Pre-Processing	alesprocess.ksh
Post-Processing	NA
Threading Scheme	NA

Key Tables Affected

Table 16-15 Key Tables Affected

Table	Select	Insert	Update	Delete
SVC_POSUPLD_LOAD	Yes	No	No	Yes
SVC_POSUPLD_STAGING	Yes	Yes	No	Yes
V_SVC_POSUPLD_LOAD	Yes	No	No	No
SVC_POSUPLD_LOAD_ARCH	No	Yes	No	No

salesuploadpurge.ksh (Purge Aged Archived POSU Transactions)

Module Name	salesuploadpurge.ksh
Description	Purge Aged Archived POSU Transactions
Functional Area	Sales Processing
Module Type	Admin
Module Technology	Ksh
Catalog ID	RMS341

Design Overview

The purpose of this module is delete the archive tables for the rejects and the posted transaction based on the given retention period.

Scheduling Constraints

Table 16-16 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This data administration program does not have any interdependencies with other sales upload processing programs and can be run ad hoc with other purge programs
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Performance Considerations

The retention period for the archived data should be carefully considered. Disregarding this would result in the tablespace size reaching its limit and would not be able to accommodate additional archive records.

Key Tables Affected

Table 16-17 Key Tables Affected

Table	Select	Insert	Update	Delete
SVC_POSUPLD_REJ_RECS	No	No	No	Yes
SVC_POSUPLD_LOAD_ARCH	No	No	No	Yes

sales_reprocess.ksh (Re-processing of Sale/Return Transactions Due to Chunk Not Process Issue)

Module Name	sales_reprocess.ksh
Description	Re-processing of Sale/Return Transactions Due to Chunk Not Process Issue
Functional Area	Sales Posting
Module Type	Business Processing
Module Technology	ksh
Catalog ID	NA

Design Overview

The purpose of the SALES_REPROCESS.KSH module is to reprocess sales and return details that were marked in error with error chunk not process.

Scheduling Constraints

Table 16-18 Scheduling Constraints

Schedule Information	Description
Processing Cycle	Phase 2 (minimum) Can also be run Ad Hoc
Frequency	Daily
Scheduling Considerations	This program should run right after salesprocess.ksh. It should be run in at least phase 2. Can also be run ad hoc to trickle poll sales.
Pre-Processing	salesprocess.ksh
Post-Processing	salesgenrej.ksh salesuploadarch.ksh
Threading Scheme	Run one Thread; Expecting low volume of POSU files needing reprocessing on chunk not processed issue.

Restart/Recovery

The logical unit of work for sales_reprocess.ksh is a chunk.

Locking Strategy

NA

Security Considerations

NA

Performance Considerations

NA

Key Tables Affected

Table 16-19 Key Tables Affected

Table	Select	Insert	Update	Delete
SVC_POSUPLD_LOAD	Yes	Yes	Yes	No
SVC_POSUPLD_LOAD_ARCH	Yes	No	No	Yes
SVC_POSUPLD_STAGING	Yes	Yes	Yes	No
SVC_POSUPLD_STAGING_REJ	No	No	No	Yes
SVC_POSUPLD_STATUS	Yes	No	Yes	No

Integration Contract

Integration Type	NA
File Name	NA
Integration Contract	NA

Design Assumptions

NA