Sales Posting

14 Sales Posting

If not using Sales Audit, such as if you are using a 3rd party sales auditing solution, sales and returns from your point-of-sale (POS) and order management (OMS) solutions can be integrated directly to Merchandising using the Upload Sales job.

Once the data is staged in Merchandising, other modules take over the posting of that data to sales transaction, sales history, and stock-on-hand tables. This is the processing used by data integrated from the Oracle Retail Sales Audit solution (as part of the Merchandising Foundation Cloud Service), as well.

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

Upload POSU File for Processing (uploadsales.ksh) reads the input file and writes its contents to a series of staging tables. Process Multiple POSU Files (uploadsales_all.ksh) wraps uploadsales.ksh to process multiple files.
Main Processing of Staged Sales/Returns (salesprocess.ksh) reads the staged data and performs major validation, financial and inventory processing.
Reject POSU Transactions (salesgenrej.ksh) creates a reject file for transactions that fail validation.
Archive Successfully Posted Transactions (salesuploadarch.ksh) archives successfully processed transactions and clears them out of the staging tables.
Purge Aged Archived POSU Transactions (salesuploadpurge.ksh) purges transactions from the archive tables after the transactions age out of the system.

Program Summary

The following batch designs are included in this chapter

These integration programs are described in the Merchandising Operations Guide Volume 2:

uploadsales.ksh (Upload POSU File for Processing)
uploadsales_all.ksh (Process Multiple POSU Files)
salesgenrej.ksh (Reject POSU Transactions)

Archive Successfully Posted Transactions (salesuploadarch.ksh)

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

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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

Performance Considerations

Since the archive tables would be handling a large volume of data. Administrators should consider enlarging the tablespace to accommodate the average volume of data.

Design Assumptions

N/A

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

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
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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. 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 Merchandising
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

POSU Chunking

Table 14-1 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 Merchandising. 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.

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 you can use to upload again to the staging table.

Security Considerations

N/A

Performance Considerations

The number of threads, the amount of waiting time, number for retries, and average volume of data should be considered.

Be careful when increasing the number of threads. When the number exceeds the capacity of the server, new jobs wouldn't be able to start when this program is running and would impact other users of the system.

Because this is multithreaded and can be executed during the store day, it is prone to locking errors. Record locking errors would happen if the thread reached the maximum number of retries (RETRY_LOCK_ATTEMPT) to fetch the lock. To prevent this, increase the value of the retries and let the value of RETRY_WAIT_TIME remain at 1. This means that it would retry every second until the maximum number of retries have been reached.

It is also important to know the average volume of data. It is a determinant of what would be the chunk size. If the chunk is too small, it couldn't utilize processing the records in bulk. If the chunk size is too large, in such that, all records would be in one chunk, it wouldn't utilize the multithreaded approach and thus, be inefficient.

I/O Specification

Integration Type	Upload to Merchandising
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 Sales Audit. These tax details will be passed on to Merchandising in the TTAX lines of the POSU file. Even though POS can pass multiple IGTAX/TTAX lines to Sales Audit and from Sales Audit to Merchandising, Merchandising only supports one tax code per item. If multiple taxes for an item are sent from POS to Sales Audit, they will be summed to a single tax in Merchandising sales upload process and assigned one of the applicable tax codes when writing tran_data 88.

Financial Transactions

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 Merchandising Operations Guide), for the column TRAN_CODE, salesupload.ksh writes the following:

Table 14-2 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 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.

Purge Aged Archived POSU Transactions (salesuploadpurge.ksh)

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

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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

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.

Design Assumptions

N/A

Purge FILE_UPLOAD_STATUS and FILE_UPLOAD_ERRORS Tables (file_upload_errors_purge.ksh)

Module Name	file_upload_errors_purge.ksh
Description	Purge FILE_UPLOAD_STATUS and FILE_UPLOAD_ERRORS Tables.
Functional Area	Administration
Module Type	Admin
Module Technology	Ksh
Catalog ID	N/A
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The purpose of this program is to purge FILE_UPLOAD_STATUS and FILE_UPLOAD_ERRORS tables regularly in Merchandising.

To validate the status of sales file upload process in Merchandising, the error handling in sales upload process has been enhanced to capture the following attributes of file upload status in FILE_UPLOAD_STATUS and FILE_UPLOAD_ERRORS tables.

Filename
Status
# of lines in file
# of Records uploaded
# of Records failed processing
Date/Time process started
Date/Time processing completes
Location (store or warehouse where file originated). For stock counts this would be the physical warehouse.

If errors are identified, the error message, line text and line ID are captured in the FILE_UPLOAD_ERRORS table. The FILE_UPLOAD_STATUS and FILE_UPLOAD_ERRORS tables are replicated thru golden gate, so that customer can verify the upload file results through DAS views.

The file_upload_errors_purge.ksh script is scheduled to run as part of the nightly batch, to purge FILE_UPLOAD_STATUS and FILE_UPLOAD_ERRORS tables regularly in Merchandising based on the retention days input parameter.

Restart/Recovery

This program does not contain restart/recovery logic.

I/O Specification

N/A

Design Assumptions

N/A