course project 8

More Validation Talk – Week 5

By now, the project applications should be starting to make some sense and things should be coming along. Since we discussed quite a bit about the validation process needed for the PL/SQL applications last week then we will look at just a few more specific things this week and then get ready for the next deliverable next week.

Looking at the SEQ_PROC Procedure

Back to Top

When you first look at the code for the SEQ_PROC procedure, it may not make much sense to you, but if you take a minute to read through the comments that have been placed in the code, you will probably start to get a better feeling for the processing taking place. This application is actually processing both the order and the order line files. This should make the process of dealing with the validation needed a little easier since you will only need to work with the one application procedure.

From a process standpoint, the procedure is going to deal with the order file first and then process the order line file. The specific validation that needs to be done was outlined in the introduction to the project in Week 1. In the following discussion, we will look at some of the specific processes that will be needed to accomplish the validation.

Validation of PO Number and Customer Number

Back to Top

The validation of the PO Number and the Customer Number data can be handled in the very same manner. This process was already covered in the previous week’s project lecture, and the syntax for the validation routine would be the same in either case. Obviously, there would be some differences in variable names and such, but the structure of the code will be identical.

Remember that in using the select statement with the COUNT function to determine if the current record is already present in the database, you are looking to see if the function returns a 1. In both cases, the PO Number and the Customer Number are primary keys in the respective ORDER_ENTRY and CUST_ENTRY tables, so this piece of data can only exist in the table once. If it were already there, then trying to insert it a second time would cause an error. Part of the intent on setting up the data validation the way it is being done is to prevent these types of scenarios from happening.

Partial Ship and Taxable Indicators

Back to Top

The partial ship and taxable indicators are two of the easier pieces of data to validate. Since each piece of data has the option of being either an ‘N’ ,’n’,’Y’, or ‘y’, the validation is simply a case of setting up an IF statement that checks for each of the listed possibilities. If any of the four exists, then the processing moves on to the next piece of data. If none of the four possibilities exist, then the error data is inserted into the ERROR_AUDIT table before moving on. Oh yes, and do not forget to set the valid record switch so that we know we have an invalid record when the data does not pass validation.

One thing that bears mentioning here is that PL/SQL does have one restriction that is easily overlooked when there are multiple things to compare in a single IF statement. The variable that is holding the data being compared must be repeated for each condition. For example, if our variable name for the partial ship indicator was v_part_ship_ind, then the comparison statement might look similar to the following.

IF v_part_ship_ind = ‘Y’ OR v_part_ship_ind =’y’ OR v_part_ship_ind = ‘N’ OR v_part_ship_ind =’n’ THEN

Admittedly, this is a little wordy, but at least it is easy to read and should make sense just by the construction. However, the same result could be achieved by using the IN key word and placing the acceptable values in a list as shown in the following example.

IF v_part_ship_ind IN (‘Y’,’y’,’N’,’n’) THEN

The IF statement is much shorter now and should be just as understandable as the first example. The validation result of both statements is identical.

Validation of Dates

Back to Top

When you look at the requirements for the two date fields in the order file, they seem simple, and in reality they are. It really comes down to a simple matter of comparing the dates in the file to the system date. In the case of the purchase date, this date should be prior to or equal to the current system date. The validation is a simple case of creating a condition that checks to see if the purchase date value on the input file is greater than ( > ) the current system date (using the system variable SYSDATE). Your code might look similar to the following example.

Example of Date Validation Routine

The following example may contain variable names different from those you might use. Remember that the variables must be declared in the DECLARE section of the code before they can be used in the BEGIN section.

— check for purchase_date current or older than current date

IF purchase_date > SYSDATE THEN

INSERT INTO error_audit

VALUES(‘OE103’, USER, SYSDATE, order_entry_file, v_po_number,

‘The purchase date after current date.’);

V_VALID := ‘N’;


The validation routine to check the hold date would be identical to the one above only you would want to reverse the condition. The hold date must be greater than or equal to the current date, so if it is less than the current date, it is invalid. In addition, as noted in last week’s project discussion, notice that the error message reference identification is OE103 to indicate that this is an error from the order entry file and is the third validation in the application for that file.

Data Conversion

Back to Top

There is one situation in the sequential procedure that will require you to manipulate the data coming into the program before you send it on to the database. This data conversion has to do with the quantity code that will be coming in on the input file for the order line data. As you will see when you work with the Oracle Form for this data, the quantity column in the order form shows a code and a quantity amount for the order. The figure below gives you an example of what the form looks like at this point.

The value that comes into the procedure on the input file is the code, not the quantity. Within the processing of the data, there will need to be two actions taken on this data item; first, validate the code to be between ‘A’ and ‘K’, and second, to convert the code to the correct quantity based on the quantity data in the QTY_CONVERSION table in the database. We have already discussed processes similar to what will be needed to determine if the code is valid. To convert quantity from a code to an amount is a simple process of querying the QTY_CONVERSION table for the quantity amount (column name is quantity) based on a match between the code on the input file and the code in the CONV_CODE column in the table. Remember that from other examples we have looked at so far you will need to SELECT the quantity code INTO a predefined variable FROM the table WHERE there is a match. Leaving out the INTO statement will cause an error, not to mention you will have no way of getting your data. Once you have the amount retrieved into the variable then you can use the variable in the final statement to insert the data into the ORDER_LINE table. Your code might look similar to the following example.

Example of Date Validation Routine

The following example will convert a valid quantity code on the input file to a numerical amount to be inserted into the database. The variable v_quantity is already in the table insert statement at the bottom of the SEQ_PROC code.

— check for valid quantity code between letters ‘A’ and ‘K’

IF v_conv_code BETWEEN 'A' and 'K' THEN
   SELECT quantity
   INTO v_quantity
   FROM qty_conversion
   WHERE conv_code = v_conv_code;
   INSERT INTO error_audit 
   VALUES('OL102', USER, SYSDATE, order_line_file, v_po_number, 
   'The quantity code on the file is invalid.');
    V_VALID := 'N';

Code Due this Week

Back to Top

As a final note for the project discussion this week, we need to discuss briefly what you will be expected to submit for the deliverable this week (Week 5). This is the week that all of your coding, both the programs you created and those you modified, are to be completed and ready for review. This means that for any application that the development team either wrote or edited, you will be expected to submit a code listing so that the code can be reviewed. The only exception to this will be is if you simply have to change the directory path in the application, as will be the case with several of the programs you are given. The following is a list of the applications you should be turning in:

  1. XML_PROC – you added validation routines to this
  2. SEQ_PROC – you added validation routines to this
  3. INV_GRAB – this application was written from scratch
  4. INV_UPDATE – this application was written from scratch

It is extremely important that your code be done by the end of this week in order to be able to complete the testing requirements on time.

Remember that there is also the Project Team Responsibilities document that must be filled out by the development team lead. This document should outline what each team member did to contribute to the completion of the deliverable. Omission of a team member from this document will result in a possible zero-grade for the assignment. Please be accurate and honest when detailing what the contributions of the team members were.

"Is this question part of your assignment? We can help"