Assignment $1$

This assignment will test your knowledge and skills acquired during weeks $1$ thru $3$ by working with the S$11\_150$ schema.

Environment Instructionss

For these questions you must first study the S$11\_150$ tables. Use the S$11\_150$ schema link.

You have SELECT access on the tables:

SH$\_$CUSTOMER

SH$\_$PART

SH$\_$SALESREP

You have INSERT, UPDATE access on the tables:

SH$\_$ORDERS

SH$\_$ORDERPROD

SH$\_$INVOICE

SH$\_$INVPROD

Assignment Requirements

You are to create the following scripts using the S$11\_150$ tables as described below.

Write a stored procedure $($in your schema$)$that can be used to store a new order and its products in the SH$\_$ORDERS and SH$\_$ORDERPROD tables

You must use IN and OUT parameters $-$ NOT DBMS$\_$OUTPUT.PUT$\_$LINE. Hints

Input parameters include

Customer number

Order date

List of

part numbers

order price

order qty

Output parameters include

Generated order no

Status string which should be one of

SUCCESS

BAD CUSTOMER $-$ for an invalid customer

BAD ORDER DATE $-$ for an order date over $2$ months in the past or one month in the future

BAD PART NUMBER $-$ for an invalid part number from the list

BAD ORDER PRICE $-$ for an order price that is nt within $+/-10\%$ of the PART.unitprice

BAD ORDER QTY $-$ for an order qty of NULL, less than $1$ or greater than $10,000$

DO NOT include any COMMIT in your procedure $-$ you will only COMMIT what you submit

Write a function $($in your schema$)$ that can be used to calculate the unshipped portion of a product on an order using SH$\_$ORDERS, SH$\_$INVOICE, SH$\_$ORDERPROD and SH$\_$INVPROD tables

Return value of $0$ signals no more shipment of the product on the order

Return value of positive number contains maximum allowed ship qty

Return value of $-1$ signals that the product is NOT on the order

Write a stored procedure $($in your schema$)$ that can be used to store a new invoice and its products in the SH$\_$INVOICE and SH$\_$INVPROD tables. Use the function created in step #$2$ to validate ship qtys

You must use IN and OUT parameters $-$ NOT DBMS$\_$OUTPUT.PUT$\_$LINE Hints

Input parameters include

Order number

Invoice date

List of

part numbers

ship qty

Output parameters include

Generated invoice no

Status string which should be one of

SUCCESS

BAD ORDER $-$ for non$-$existant order number

BAD INVOICE DATE $-$ for an invoice date that precedes the order date or is one month beyond it

BAD PART NUMBER $-$ for a part number NOT on the order

BAD SHIP QTY $-$ for an ship qty of NULL or exceeds the unshipped portion of the part

DO NOT include any COMMIT in your procedure $-$ you will only COMMIT what you submit

Write a testing script that uses substitution parameters to perform the following:

Using substitution variables, execute the stored procedure from step #$1$ to create:

a failed order

a correct order with at least $3$ products

Execute S$11\_150.$sh$\_$display$\_$order to display your newly created order

Using substitution variables. execute the stored procedure from step #$3$ to create:

a failed invoice

two invoices using the newly created order and partially shipped products

Execute S$11\_150.$sh$\_$display$\_$invoice to display your newly created invoices

NOTE: At end of each practice execution, use the ROLLBACK; command until you are ready to submit. On your last execution, then use COMMIT; to save your order and invoices in the S$11\_150$ tables.