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:

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

Directories

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

Modules

The application consists of the following program modules:

IMPORT_ORDER.BAT

GitHub Repository - 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

GitHub Repository - 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!'); 
    COMMIT;  /* No errors, so explicitly commit new order data to database */
  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

PL/SQL - import package spec
PL/SQL - import package body

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.

The PL/SQL program ord_imp does not issue a COMMIT; the caller (or client) is responsible for committing. If SQL*Plus is configured with EXITCOMMIT ON, exiting the client will commit outstanding work.

The ord_imp function processes the CSV data as follows:

1. Load CSV data into staging table IMPORTCSV:
1. Call the package function UTIL_FILE.LOAD_CSV to load order data from a CSV file into the IMPORTCSV staging table.
2. The load_csv function returns an integer FILEID, which identifies the group of records loaded from the CSV file into the staging table.
3. If the file is not found:
1. Log the error in APPLOG, and IMPORTERROR tables.
2. Return to calling application with status FALSE (failed).
2. Validate the data in the staging table for the current FILEID:
1. A Boolean variable “valid” is initially set to TRUE.
2. All rows in the staging table are validated.
3. 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.
4. For each field that fails validation:
1. Insert an error message into the IMPORTERROR table (include the KEY_VALUE column).
2. Valid is set to FALSE.
3. Validation continues until all rows in the staging table have been checked, and all errors logged.
3. If validation has failed (valid is FALSE):
1. Insert an error message into the APPLOG table.
2. Delete rows from the staging table IMPORTCSV for the FILEID currently being processed. Note that no data has been committed to the database. A rollback cannot be used to remove the data from IMPORTCSV as that would also remove all the error messages in IMPORTERROR.
3. No data is inserted into the ORD and ITEM tables.
4. Move the CSV file to the directory data_in/error.
5. Return to the calling application with status FALSE (failed).
4. Else if validation succeeded (valid is TRUE):
1. Insert data from the staging table into the ORD and ITEM tables.
2. Delete the rows in the staging table IMPORTCSV for the FILEID being processed.
3. Delete error messages for previous failed import attempts from IMPORTERROR. Match with the KEY_VALUE column of IMPORTCSV.
4. Move the CSV file to the directory data_in/processed.
5. Return to the calling application with status TRUE (succeeded).
6. If an unexpected error occurs:
1. Raise an application error.
2. Rollback transactions (ORD, ITEM, IMPORTCSV) to the initial savepoint prior to data being loaded into the staging table.
3. There will be no error messages in IMPORTERROR pending commit, as validation passed.
4. Log an error by inserting rows into APPLOG and IMPORTERROR tables.
5. Move the CSV file to directory data_in/error.
6. Return to calling application with status FALSE (failed).

Control returns to the calling SQL*Plus application.

1. A message is displayed indicating whether import failed or succeeded, depending on the status returned by PL/SQL function ord_imp.
2. COMMIT is explicitly issued:
• If validation failed, the only data pending commit are error messages in the IMPORTERROR and APPLOG tables.
• If validation passed, either everything worked and the commit is applied to the new rows inserted into ORD an ITEM, or there was an unexpected error.
• In the case of an unexpected error following successful validation, there will be no validation errors pending commit. The PL/SQL function issued a rollback to the state when the program started, prior to data being loaded into the staging table. Any pending changes to the ORD and ITEM tables in the current transaction were discarded by the rollback. There will be error messages awaiting commit in APPLOG and IMPORTERROR.
• Staging table IMPORTCSV rows marked for deletion are deleted.