Sales Order Import

You can view the documentation for the order import application by clicking on the links below:

Order Import Technical Specification     Order Import User Guide    

The requirements for our sales order data import process are as follows:

1. The sales order data is received in CSV format text files.
2. The files are named with the prefix ‘order’ and extension ‘csv’.
3. Each file must have a unique name.
4. The process must be automated to run every hour, and import all new order CSV files received.
5. The data must be validated, and if there are any errors, the entire CSV file must be rejected, moved to an ‘Error’ directory, and all errors reported.
6. A manual procedure is required to fix rejected files.
7. If the data passes validation, load the data into the order tables.
8. Move successfully imported files to a ‘Processed’ archive directory.
9. Delete old error messages relating to the orders that have been successfully imported.

Fixing rejected files

1. Manual intervention is required.
2. An import error report will show the following information for each rejected file:
• Filename.
• A key value that uniquely identifies the order: Order Reference.
• CSV data record (all fields).
• An error message identifying the invalid data.
• Date and time error reported.
3. Locate the rejected CSV file in the Error directory, via the filename on the error report.
4. Edit the CSV file, and manually correct each error reported.
• Order Reference may be altered, but you must check that there are no existing orders with the same reference.
• Dates must be in the format DD/MM/YYYY.
• Ship Date must be on or later than the Order Date, in the format DD/MM/YYYY.
• Customer ID invalid: contact the sales department to either obtain the correct code, or have a new account created.
• Product ID invalid: contact the sales department for the correct code.
5. Move the corrected file to the ‘Received’ directory to be re-processed.

Example CSV File

The order data is received in CSV files, similar to the following example:

The first row of the CSV file is the header, containing a list of field names. The body of the file contains the order data, with a row for each order line. Each CSV file can contain one or more orders, each order having one or more lines.

Field No	Name	Data Type	Size	Description
1	Ord Ref	Char	10	Order Reference. Maximum length 10 characters. Must be unique per order.
2	Order Date	Date	10	Date format DD/MM/YYYY.
3	Commission	Char	1	A to Z, may be NULL.
4	Customer ID	Number	6	Must exist on the Customer table.
5	Ship Date	Date	10	Date format DD/MM/YYYY. Must be on or later than the Order Date.
6	Product ID	Number	6	Must exist on the Product table.
7	Qty	Number	8	Quantity of product ordered. Must be a valid whole number > 0, and <= 99999999.

Directories

The following Directory objects have been created in the database, to allow the applications to access the CSV files, process, and move them.

Directory Name	Path
DATA_IN	D:\USER_DATA\XEPDB1\appsdemo\data\DATA_IN
DATA_IN_ERROR	D:\USER_DATA\XEPDB1\appsdemo\data\DATA_IN\error
DATA_IN_PROCESSED	D:\USER_DATA\XEPDB1\appsdemo\data\DATA_IN\processed
DATA_OUT	D:\USER_DATA\XEPDB1\appsdemo\data\DATA_OUT

Modules

The application consists of the following program modules:

Module Name	Description
IMPORT_ORDER.BAT	A DOS script that finds order CSV files, moves each file in turn to the DATA_IN directory, and runs the SQL*Plus script IMPORT_ORDER.SQL to process each file.
IMPORT_ORDER.SQL	SQL script that calls a PL/SQL package function to validate, and import the CSV data.
IMPORT.ORD_IMP	PL/SQL package function. Validates the CSV data. Records errors in the IMPORTERROR table. If there are no errors, the data is imported into the order tables of the database, and the CSV file is moved to DATA_IN_PROCESSED. If errors are found the file is moved to DATA_IN_ERROR.

IMPORT_ORDER.BAT

import_order.bat

This is a Windows Batch Script that does the following:

Search the DATA_HOME\RECEIVED directory for CSV files containing order data. For each CSV file found with the name ‘order*.csv’:

• Copy the CSV file to the DATA_IN import directory.
• Run PL/SQL: execute script IMPORT_ORDER.SQL passing filename.
• Delete the CSV file from the received directory.

IMPORT_ORDER.SQL

import_order.sql

This SQL script calls the PL/SQL package function IMPORT.ORD_IMP, passing the CSV filename.

IMPORT.ORD_IMP

PL/SQL Package: import.sql

The ord_imp PL/SQL function, in the import package, loads the CSV data into a staging table, validates the data, and loads valid data into the order tables. If validation fails, the errors are recorded, the CSV file is rejected, and no data is loaded into the order tables.

There are two functions that are specific to the order import process: ord_imp, and ord_valid.

The following generic functions are used by the import process. These functions can be re-used when creating additional data import processes.

Package	Function/Procedure	Description
import	delete_error	Delete old error messages for orders that have been successfully imported.
import	import_error	Record validation error message on IMPORTERROR table.
util_file	load_csv	Load CSV file data into the IMPORTCSV table.
util_file	delete_csv	Delete rows from IMPORTCSV for each CSV file that has been processed.
util_file	rename_file	Rename the CSV file by moving it to directory DATA_IN_PROCESSED if order imported, or DATA_IN_ERROR if it failed validation.
util_string	get_field	Extract Nth field from a delimited string (the CSV record).
util_admin	log_message	Record errors in the application log table APPLOG.
orderrp	currentprice	Finds the current price for the specified product.

The ord_imp function processes the CSV data as follows:

1. Call the package function UTIL_FILE.LOAD_CSV to load order data from a CSV file into the IMPORTCSV staging table. The load_csv function returns an integer FILEID, which identifies the group of records loaded from the CSV file into the staging table.


2. If the file was not found, report error and stop processing.


3. Validate the data in IMPORTCSV matching FILEID.
   1. Set column KEY_VALUE in table IMPORTCSV to a unique value, that identifies each order, in this case it will be the first field in the CSV file, ORDREF.
   2. Record all validation errors found in the IMPORTERROR table, including the KEY_VALUE field.


4. If data fails validation:
   1. Delete the data from the IMPORTCSV staging table.
   2. Move the CSV file to the error directory.
   3. Stop processing, exit with an error status.


5. If data passes validation:
   1. Insert data into the ORD and ITEM tables.
   2. Delete old error messages from the IMPORTERROR table for the orders successfully imported, using the KEY_VALUE column of IMPORTCSV.
   3. Delete the data from the IMPORTCSV staging table.
   4. Move the CSV file to the processed directory.
   5. Exit with a success status.