Question

An online company sells hundreds of office supply products on its e-Commerce store. It has asked you to design and implement a sales order processing system which will be used by users in our Operations department to process sales orders. Here is an overview of the sales order process. Customers make purchases by placing orders. Each customer has a customer number and profile (such as name, shipping address). To simplify the matter, each time an order is placed, only one item is ordered. The company maintains hundreds of products at various warehouses throughout the United States. Each warehouse has a warehouse code, address and phone number. The company keeps track of products carried in various warehouses and their current inventory level.

create table Customer
(CustomerNumber number(10,0), LastName varchar2(15), FirstName varchar2(15), Address varchar2(50), City varchar2(30), State char(2), ZipCode char(5), PhoneNumber varchar2(12),
constraint customer_pk primary key(CustomerNumber));

create table Warehouse
(WarehouseID varchar2(15), Address varchar2(30), City varchar2(50), State char(2), ZipCode char(5), PhoneNumber varchar2(12),
constraint Warehouse_pk primary key(WarehouseID));

create table Product
(ProductID varchar2(5), Description varchar2(30), Price decimal(4,2),
constraint Product_pk primary key(ProductID));

create table Orders
(OrderID int GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1), CustomerNumber number(10,0), ProductID varchar2(5), Quantity int, OrderDate date default sysdate, OrderStatus varchar2(10), ShippingDate date,
constraint Orders_pk primary key(OrderNum),
constraint Orders_fk1 foreign key(CustomerNumber) references Customer(CustomerNumber)
constraint Orders_fk2 foreign key(ProductID) references Product(ProductID));

create table Inventory(ProductID varchar2(5), WarehouseID varchar2(5), Inventory int,
constraint Inventory_pk primary key(WarehouseID, ProductID),
constraint Inventory_fk1 foreign key(ProductID) references Product(ProductID),
constraint Inventory_fk2 foreign key(WarehouseID) references Warehouse(WarehouseID));

Program 1. open.sql To place an order, the user runs the open.sql program. • The system first prompts the greeting message and automatically displays today's date. • The system then prompts the user to enter the customer number. Customer name (last name first, comma, then first name), address and phone are displayed. • The system then prompts for the item number. Item description and unit price is displayed. • The system then asks for the quantity ordered. All warehouses that currently carry this product where inventory is equal or higher than the order quantity are displayed The user then chooses one warehouse from the list. At this point, the system updates the sales order data to the database, and assigned a unique order number to this order. Use Identity column to generate the order id. The order status is "OPEN'. The order number and order status then is displayed for the user. A sample screen is as follows. The bold underlined text indicates user input; everything else is displayed automatically. SQL> start i lopen.sql *** OPEN AN ORDER Today's Date: 22-MAR-2019 Enter the Customer Number: 102 Customer Name: Jones, Elaine Shipping address 123 Main Street City State Zip Los Angeles, CA 91001 Phone: (323) 511-1199 Enter the Item Number: pl Item Number pl Item Description: Pencil Unit Price: $2.00 Enter the quantity ordered: 20 Amount Orderedt $40.00 Please choose from the following warehouses: Warehouse City, State Zip Code Phoenix, AZ 33415 San Diego, CA 92331 Boston, MA nter the warehouse code: w6 ** Order status: OPEN **** Order number 1 - > 1002 Notes. The screen shots here are for your reference. You need to display all relevant information in a presentable format.

Answer 1

DECLARE

cust_no number

data Customer%rowtype

item_no number

Item Products%rowtype

quantity number

rc sys_refcursor;

Warehoulse_code varchar2

OrdID number Orders.OrderID%type

BEGIN

cust_no:=&cust_no;

select * into data from Customer WHERE CustomerNumber = cust_no;

dbms_output.put_line('Customer Name: '||data.CustomerNumber );

dbms_output.put_line('Shipping Address: '||data.Address );

dbms_output.put_line('City, State Zip: '||data.City||', '||data.State||' '||data.ZipCode );

dbms_output.put_line('Phone:'||data.PhoneNumber);

item_no:=&item_no

select * into Item from Products where Products.ProductID = item_no

dbms_output.put_line(Item Number: '||item.ProductID);

dbms_output.put_line(Item Description: '||item.Description);

dbms_output.put_line(Unit Price: '||item.Price);

quantity:=&quantity

dbms_output.put_line('Amount Ordered: '||(item.Price)*(item.quantity))

dbms_output.put_line('Please choose from the following Wareshouses:')

open rc for select W.WarehouseID,concat(W.City,",",W.State) as "City,State", Zip as ZipCode, I.Inventory from Warehouse W join Inventory I on (I.wid = W.WarehoulseID);

dbms_sql.return_result(rc);

Warehoulse_code:= &Warehoulse_code ;

insert into Orders (CustomerNumber, ProductID, Quantity, OrderStatus) values(cust_no,item_no,quantity,'Open');

select OrderID into OrdID from Orders where CustomerID = cust_no

dbms_output.put_line('******** Order Status: '||'Open')

dbms_output.put_line('******** Order number is: '||OrdID)

END