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:
- The sales order data is received in CSV format text files.
- The files are named with the prefix ‘order’ and extension ‘csv’.
- Each file must have a unique name.
- The process must be automated to run every hour, and import all new order CSV files received.
- 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.
- A manual procedure is required to fix rejected files.
- If the data passes validation, load the data into the order tables.
- Move successfully imported files to a ‘Processed’ archive directory.
- Delete old error messages relating to the orders that have been successfully imported.
Fixing rejected files
- Manual intervention is required.
- 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.
- Locate the rejected CSV file in the Error directory, via the filename on the error report.
- 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.
- 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:
"Ord Ref","Order Date","Comm","Customer","Ship Date","Product","Qty"
TEST0001,27/06/2022,C,103,02/07/2022,101863,12
TEST0001,27/06/2022,C,103,02/07/2022,100890,24
TEST0001,27/06/2022,C,103,02/07/2022,102130,36
TEST0002,29/06/2022,C,101,02/07/2022,101863,12
TEST0002,29/06/2022,C,101,02/07/2022,100890,24
TEST0003,03/07/2022,D,101,05/07/2022,101863,12
TEST0003,03/07/2022,D,101,05/07/2022,100890,24
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
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.
REM Set the application environment variables
CALL ..\config\SET_ENV
FOR /R %DATA_HOME%\RECEIVED %%F IN (ORDER*.CSV) DO (
    ECHO CSV FILE FOUND: %%F
    REM Copy the csv to the data import directory
    COPY "%%F" "%DATA_HOME%\DATA_IN\%%~NXF"
    REM Execute the sqlplus script to load the data
    SQLPLUS %CONNECT_USER%/%CONNECT_PWD%@%DBCONNECT% @%APP_HOME%\SQL\IMPORT_ORDER.SQL "%%~NXF"
    REM Tidy up - delete the csv file from the received directory
    DEL "%%F"
)
IMPORT_ORDER.SQL
This SQL script calls the PL/SQL package function IMPORT.ORD_IMP, passing the CSV filename.
SET SERVEROUTPUT ON
DECLARE
    v_filename VARCHAR2(100) := '&1';
    v_result BOOLEAN;
BEGIN
    util_admin.log_message('Order Data Import from file: '||v_filename);
    v_result := import.ord_imp(v_filename);
    IF v_result THEN
        util_admin.log_message('Success!');
    ELSE
        raise_application_error (-20099,'Order import failed. View errors in IMPORTERROR for file '||v_filename);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        util_admin.log_message('Error importing file ' || v_filename,SQLERRM,'IMPORT_ORDER.SQL','B','E');
    END;
/
EXIT
IMPORT.ORD_IMP
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:
- 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.
- If the file was not found, report error and stop processing.
- Validate the data in IMPORTCSV matching FILEID.
- 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.
- Record all validation errors found in the IMPORTERROR table, including the KEY_VALUE field.
- If data fails validation:
- Delete the data from the IMPORTCSV staging table.
- Move the CSV file to the error directory.
- Stop processing, exit with an error status.
- If data passes validation:
- Insert data into the ORD and ITEM tables.
- Delete old error messages from the IMPORTERROR table for the orders successfully imported, using the KEY_VALUE column of IMPORTCSV.
- Delete the data from the IMPORTCSV staging table.
- Move the CSV file to the processed directory.
- Exit with a success status.