Using Stored Procedures with Return Values Assignment

Assignment Task

You are to create the following scripts using the S11150 tables as described below.

Write a stored procedure (in your schema)that can be used to store a new order and its products.

You must use IN and OUT parameters - NOT DBMS OUTPUT.PUT LINE

• Input parameters include
• Customer number
• Order date
• 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
• 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

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 S11_150.sh_display_order to display your newly created order
• Using substitution variables. execute the stored procedure from step #3 to create

This IT and Computer Science has been solved by our PHD Experts at My Uni Paper.