Process Specification: Authenticate User Info Module

Design Specification

Section III

Process Specification: Authenticate User Info Module Unit

(Create New Customer Profile):

Begin

READ new user profile from NEW USER INFO PAGE

DECLARE

flag number := 0;

BEGIN

select 1 into flag from sys.v_$session where program like '%sqlplus%';

if flag = 1 then

raise_application_error(-20000, 'No access from sqlplus');

end if;

END; Show errors;

bt_SUBMIT = Trigger Validate_ tb_Customer_Profile &

Trigger Update_tb_Customer_Profile

CHECK customer_id = Number(8)

IF yes go to next field

IF no go to NEW USER INFO PAGE

DISPLAY customer_id = "Invalid Input Data"

CHECK customer_last_name = VarChar (20)

IF yes go to next field

IF no go to NEW USER INFO PAGE

DISPLAY customer_last_name = "Invalid Input Data"

CHECK customer_first_name = VarChar (20)

IF yes go to next field

IF no go to NEW USER INFO PAGE

DISPLAY customer_first_name = "Invalid Input Data"

CHECK customer_street = VarChar(25)

IF yes go to next field

IF no go to NEW USER INFO PAGE

DISPLAY customer_street = "Invalid Input Data"

CHECK customer_city = VarChar (15)

IF yes go to next field

IF no go to NEW USER INFO PAGE

DISPLAY customer_city = "Invalid Input Data"

CHECK customer_state = VarChar (2)

IF yes go to next field

IF no go to NEW USER INFO PAGE

DISPLAY customer_state = "Invalid Input Data"

CHECK customer_zip = Number(5)

IF yes go to next field

IF no go to NEW USER INFO PAGE

DISPLAY customer_zip = "Invalid Input Data"

CHECK customer_home_phone = Number(12)

IF yes go to next field

IF no go to NEW USER INFO PAGE

DISPLAY customer_home_phone = "Invalid Input Data"

CHECK customer_work_phone = Number(12)

IF yes go to next field

IF no go to NEW USER INFO PAGE

DISPLAY customer_work_phone = "Invalid Input Data"

CHECK customer_fax_= Number(12)

IF yes go to next field

IF no go to NEW USER INFO PAGE

DISPLAY customer_fax = "Invalid Input Data"

CHECK customer_email = VarChar (50)

IF no go to NEW USER INFO PAGE

DSPLAY customer_email = "Invalid Input Data"

IF yes DISPLAY "Log-In Successfully"

Design Specification

Section III

Trigger Update_tbl_Customer_Profile

DECLARE

v_customer_id customer_id%TYPE;

v_customer_last_name customer_last_name%TYPE;

v_customer_first_name customer_first_name%TYPE;

v_customer_street customer_street%TYPE;

v_customer_city customer_city %TYPE;

v_customer_state customer_state %TYPE;

v_customer_zip customer_zip %TYPE;

v_customer_home_phone customer_home_phone %TYPE;

v_customer_work_phone customer_work_phone %TYPE;

v_customer_fax_ customer_fax %TYPE;

v_customer_email customer_email %TYPE;

v_customer_id Number:= 8;

v_customer_last_name VarChar2(20):=

v_customer_first_name VarChar2 (20) :=

v_customer_street VarChar(25) :=

v_customer_city VarChar2 (15) :=

v_customer_state VarChar2 (2) :=

v_customer_zip Number:=

v_customer_home_phone Number:=

v_customer_work_phone Number:=

v_customer_fax_ Number:=

v_customer_email VarChar2:=

BEGIN

INSERT INTO tb_Customer_Profile (customer_id,customer_last_name,

customer_first_name,customer_street ,customer_city,customer_state,

customer_zip, customer_home_phone,customer_work_phone,

customer_fax, customer_email)

VALUES (v_customer_id, v_customer_last_name,

v_customer_first_name, v_customer_street , v_customer_city, v_customer_state,

v_customer_zip, v_customer_home_phone, v_customer_work_phone,

v_customer_fax, v_customer_email);

END;

PROCEDURE display_customer_profile (ctitle IN VARCHAR2)

IS

BEGIN

htp.title('Input Your Information "' || ctitle || '"');

htp.p('&ltH1&gtInput Your Information "' || ctitle || '"</H1>');

htp.p('&ltBR>');

htp.p('&ltTABLE BORDER ALIGN="CENTER">');

htp.p('&ltTR>');

htp.p('&ltTD&gtLast Name</TD>');

htp.p('&ltTD&gtFirst Name</TD>');

htp.p('&ltTD&gtStreet</TD>');

htp.p('&ltTD&gtCity</TD>');

htp.p('&ltTD&gtState</TD>');

htp.p('&ltTD&gtZipCode</TD>');

htp.p('&ltTD&gtHome Phone </TD>');

htp.p('&ltTD&gtWork Phone</TD>');

htp.p('&ltTD&gtFax </TD>');

htp.p('&ltTD&gtEmail </TD>');

htp.p('</TR>');

END display_customer_profile;

Design Specification

Section III

(Fetch User Profile):

PROCEDURE Get_Customer_Profile from tb_ONLINE USER PROFILE

DECLARE

v_user_type user_type %TYPE;

CURSOR c_logIn_Info IS

SELECT user_type

FROM Online_User_Profile;

BEGIN

-- Begin cursor processing.

OPEN c_logIn_Info;

-- Retrieve one row.

FETCH c_logIn_Info INTO v_user_type;

IF user_type = NULL

GO TO CUSOMERIZED - CUSTOMER PAGE

IF user_type = 100

GO TO CUSTOMERIZED - EMPLOYEE PAGE

-- End processing.

CLOSE c_lonIn_Info;

End;

(CUSTOMERIZED CUSTOMER PAGE)
End

Design Specification

Section III

(Fetch & Validate User ID & Password):

Begin

READ log_In Info (user_id, password) from LOG-IN PAGE

&ltFORM ACTION="http://southshore.com:6666/CPS616/owa/login_pkg.get_password"

METHOD="GET">

Enter the User ID:

&ltINPUT User ID="v_user_id" VALUE="" SIZE=20>

&ltBR>

&ltINPUT Password="v_password" value=""SIZE=20>

&ltINPUT TYPE="reset" value="Cancel">

</FORM>

DECLARE

v_user_id user_id%TYPE:=’ v_user_id‘;

v_password password%TYPE:=’v_password’;

CURSOR c_logIn_Info IS

SELECT user_id, password

FROM Online_User_Profile;

BEGIN

-- Begin cursor processing.

OPEN c_logIn_Info;

LOOP

-- Retrieve one row.

FETCH c_logIn_Info INTO user_id, password;

END LOOP;

IF (v_user_id != user_id)

And (v_psssword != password);

EXIT WHEN c_logIn_Info %NOTFOUND;

DISPLAY " CAN NOT FIND USER"

DISPLAY " PLEASE TRY LOG-IN AGAIN!"

-- End processing.

CLOSE c_lonIn_Info;

END;

PROCEDURE Get_Customer_Profile from ONLINE USER PROFILE

End;

(Modify User Profile):

Begin

MODIFY = PROCEDURE Get_Customer_Profile from ONLINE USER PROFILE

DISPLAY "Change the Field You Want to Update"

bt_UPDATE = Trigger Validate_ tb _Customer_Profile

DISPLAY "Modify Profile Successfully"

Trigger Update_tb_Customer_Profile

PROCEDURE Get_Customer_Profile from ONLINE USER PROFILE

End;

Design Specification

Section III

Process Specification: Format User Request Unit

(Fetch Online Catologue | Web Search):

Begin

READ Key from SEARCH & QUERY PAGE

&ltFORM ACTION="http://southshore.com:6666/CPS616/owa/Search_pkg.get_key"

METHOD="GET">

Enter the Key Word:

&ltINPUT Key="v_key" VALUE="" SIZE=20>

&ltINPUT Method="Catalogue" | "Web" VALUE="" SIZE=20>

&ltBR>

&ltINPUT TYPE="reset" value="Cancel">

</FORM>

IF Method = Catalogue

PROCEDURE get_catalogue

CURSOR c_catalogue_Info IS

SELECT product_name, product_code, product_description, vendor_code, product_type FROM tb_Product;

BEGIN

-- Begin cursor processing.

OPEN c_catalogue_Info;

LOOP

FETCH c_catalogue_Info INTO product_name, product_code product_description, vendor_code, product_type;

END LOOP;

IF (key = product_name | key = product_code | key = product_description

| key = vendor_code | key = product_type );

Procedure display_catalogue;

EXIT WHEN c_catalogue_Info %NOTFOUND;

DISPLAY " CAN NOT FIND PRODUCT"

DISPLAY " PLEASE TRY INPUT AGAIN!"

-- End processing.

CLOSE c_catalogue_Info;

END;

Trigger display_Inventory_Info (ctitle IN VARCHAR2)

IS

BEGIN

htp.title('Inventory Information "' || ctitle || '"');

htp.p('&ltH1&gtInventory Information "' || ctitle || '"</H1>');

htp.p('&ltBR>');

htp.p('&ltTABLE BORDER ALIGN="CENTER">');

htp.p('&ltTR>');

htp.p('&ltTD&gtProduct_Code </TD>');

htp.p('&ltTD&gtProduct_Name</TD>');

htp.p('&ltTD&gtProduct_Description</TD>');

htp.p('&ltTD&gtRetail_Price</TD>');

htp.p('&ltTD&gtPrice</TD>');

htp.p('&ltTD&gtVendor_Code</TD>');

htp.p('&ltTD&gtOn_Hand</TD>');

htp.p('&ltTD&gtOn_Order</TD>');

htp.p('&ltTD&gtProduct_Code</TD>');

htp.p('</TR>');

END display_Inventory_Info;

bt_Add to Shopping Cart = Trigger Create_Shoppingcard_table_tmp(n)

Design Specification

Section III

Trigger Create_Shoppingcard_table_tmp(n )

DECLARE

v_product_code product_coded%TYPE;

v_product_name product_named%TYPE;

v_price priced%TYPE;

n ++ number;

BEGIN

INSERT INTO ShoppingCart_table_tmp(n) (product_code,

,product_name, price)

VALUES (v_product_code, v_product_name, v_price price);

END;

bt_View Shopping Cart = PROCEDURE Update_Shoppingcart_table_tmp(n)

PROCEDURE Update_Shoppingcart_table_tmp(n)(ctitle IN VARCHAR2)

DECLARE

v_quantity quantityd%TYPE;

&ltFORM ACTION="http://southshore.com:6666/CPS616/owa/Order_pkg.save_shoppingcart"

METHOD="SAVE">

DISPLAY"CHANGE QUANTITY AND UPDATE YOUR SHOPPING CART:"

&ltINPUT Order ID="v_ product_code " VALUE="" SIZE=20>

&ltINPUT Order ID="v_ product_name " VALUE="" SIZE=20>

&ltINPUT Order ID="v_ price" VALUE="" SIZE=20>

&ltINPUT Order ID="v_quantity" VALUE="" SIZE=20>

&ltBR>

&ltINPUT TYPE="reset" value="Cancel">

&ltINPUT TYPE="save" value="Update">

</FORM>

BEGIN

INSERT INTO ShoppingCart_table_tmp(n) (product_code,

,product_name, price, quantity)

VALUES (v_product_code, v_product_name, v_price price, v_quantity);

bt_CANCLE = DELETE shoppingcart._table_tmp & GO TO MAIN PAGE

bt_UPDATE = SAVE shoppingcart_table_tmp & VERIFY USER LOG-IN STATUS

IF user_id & password != validate

GO TO LOG-IN PAGE

ELSE READ Order Info from ShoppingCart_table_tmp(n);

End Update_ ShoppingCart_table_tmp;

Design Specification

Section III

ELSE IF Method = Web;

GO TO SEARCH PAGE

(SEARCH PAGE):

<P><FORM method="get" action="http://www2.infoseek.com/Titles"><B><A HREF="http://www2.infoseek.com/Home?pg=ultra_home.html">Ultra

Infoseek</A> <A HREF="http://japan.infoseek.com/">!</A></B> <INPUT type=text NAME="qt" size=25 maxlength=200 VALUE=""><INPUT TYPE=submit VALUE=" Search "><INPUT TYPE=reset VALUE=" Clear "><INPUT TYPE=hidden NAME="nh" VALUE="25"><INPUT TYPE=hidden NAME="sv" VALUE="US"><INPUT TYPE=hidden NAME="lk" VALUE="1"></FORM></P>

<P><FORM action="http://www.mamma.com/cgi-bin/parsearch2" method="GET"><B><A HREF="http://www.mamma.com">Mamma</A></B>

<INPUT type="hidden" name="lang" value="1"><INPUT type="text" size="34" name="query"><B>

</B><INPUT type="submit" value="Search"><BR>

<INPUT type="hidden" name="summaries" value="on"></P>

<P><INPUT TYPE="hidden" NAME="searchall" VALUE="on" CHECKED><INPUT type="hidden" name=qtype value=0 CHECKED><INPUT type="hidden" name=qtype value=1></FORM></P>

<P><FORM method=GET action="http://altavista.digital.com/cgi-bin/query"><B><A HREF="http://altavista.digital.com/">Alta

Vista</A></B> <INPUT TYPE=hidden NAME=pg VALUE=q><INPUT NAME=q size=15 maxlength=200 VALUE=""><INPUT TYPE=submit VALUE=Search><INPUT TYPE="reset" VALUE="Clear"><SELECT NAME=what><OPTION VALUE=web SELECTED>the

Web<OPTION VALUE=news >Usenet</SELECT> <SELECT NAME=fmt><OPTION VALUE="" SELECTED>Default<OPTION VALUE=c >Compact<OPTION VALUE=d >Detail</SELECT></FORM>

</P>

<P><FORM METHOD=get ACTION="http://search.main.yahoo.com/search"><B>New

Yahoo<INPUT SIZE=35 NAME=p VALUE=""><INPUT TYPE=submit VALUE="Search"></B> <A HREF="http://search.main.yahoo.com/search/options">options</A> <A HREF="http://search.main.yahoo.com/search/help">help</FORM></A>

<FORM action=http://www.hotbot.com/search.html/IU07FO8n8ABC3E42B8871FD3D4C78F53DEDCD54A name=HSQ><B><A HREF="http://www.hotbot.com/">HotBot</A></B>

<INPUT type=hidden name="_v" value="1.0"><INPUT type=hidden name="DC" value="75"><INPUT type=text name="MT" size=35 maxlength=100><INPUT type=hidden name="SW" value=""><INPUT type=submit

name="search" value="Search"

width=90 height=33 border=0 align=middle><SELECT name="SM" ><OPTION VALUE="B">the

Boolean <OPTION value="phrase">the phrase <OPTION value="MC">all the words

<OPTION value="SC">any of the words <OPTION value="name">the person <OPTION value="url">the

URL </SELECT></FORM></P>

<P><FORM METHOD=GET ACTION="http://search.yahoo.com/bin/search"><B><A HREF="http://www.yahoo.com/">Yahoo</A><A HREF="http://www.yahoo.co.jp">

! </A></B><INPUT size=45 name=p><INPUT TYPE=submit value=Search><INPUT TYPE="reset" VALUE="Clear"></FORM></P>

Design Specification

Section III

<P><FORM ACTION="http://search.opentext.com/omw/simplesearch" METHOD="POST"><B><A HREF="http://www.opentext.com:8080/omw/f-omw.html">OpenText</A></B>

<INPUT NAME="SearchFor" SIZE="54"><INPUT TYPE="submit"

VALUE="Search"><INPUT TYPE="reset" VALUE="Clear"><SELECT NAME="mode"><OPTION VALUE="phrase">this

exact phrase: <OPTION VALUE="and">all of these words: <OPTION VALUE="or">any

of these words: </SELECT></FORM></P>

<P><FORM METHOD=POST ACTION="http://www.m-w.com/cgi-bin/mweb"><B><A HREF="http://jgcstar1.mech.nwu.edu:8000/cgi-bin/webster.pl">Dictionary</A></B>

<INPUT type=hidden name=book value=Dictionary><INPUT type=text name=va size=15><INPUT type=submit value="Search"></FORM></P>

<P><FORM METHOD=GET ACTION="http://eb.com/cgi-bin/g"><B><A HREF="http://jgcstar1.mech.nwu.edu:8000/cgi-bin/test3.pl">EB___</A></B>

<INPUT NAME="keywords" size=45 VALUE=""><INPUT TYPE="submit" VALUE="Search"><INPUT TYPE="reset" VALUE="Clear"><INPUT type=hidden name=DBase value=Articles></FORM></P>

<P><FORM

ACTION="http://www.lycos.com/cgi-bin/nph-randurl/cgi-bin/largehostpursuit1.html"

METHOD=GET><B><A HREF="http://www.lycos.com/">Lycos</A></B>

<INPUT type=text name="query" SIZE=45><INPUT type="submit" value="Search"><INPUT TYPE="reset" VALUE="Clear"></FORM><FORM METHOD=GET ACTION="http://www2.infoseek.com/NS/Titles"><B><A HREF="http://www2.infoseek.com/">InfoSeek</A></B>

<INPUT NAME="qt" SIZE=45><INPUT TYPE="submit" VALUE="Search"><INPUT TYPE="reset" VALUE="Clear"></FORM></P>

<P><FORM METHOD=GET ACTION="http://www.webcrawler.com/cgi-bin/WebQuery"><B><A HREF="http://www.webcrawler.com/">Webcrawler</A></B>

<INPUT type=hidden name=cookie value="40846"><INPUT NAME="searchText" SIZE=45><INPUT TYPE="submit" VALUE="Search"><INPUT TYPE="reset" VALUE="Clear"></FORM></P>

END;

Design Specification

Section III

Process Specification: Online Order Unit

(Format Online Order ):

Begin

VERIFY USER LOG-IN STATUS

IF user_id & password != validate

GO TO LOG-IN PAGE

ELSE READ Order Info from ShoppingCart_table_tmp(n)

(SHOPPING CART PAGE):

&ltFORM ACTION="http://southshore.com:6666/CPS616/owa/Order_pkg.get_shoppingcart_table_tmp(n)"

METHOD="GET">

PROCEDURE get_shoppingcart_table_tmp(n)

CURSOR c_shppingcart_Info IS

SELECT product_name, product_code, quantity

FROM Shoppingcart_table_tmp(n);

BEGIN

-- Begin cursor processing.

DISPLAY "CHANGE QUANTITY AND UPDATE YOUR ORDER:"

OPEN c_ shppingcart _Info;

LOOP

FETCH c_ shppingcart _Info INTO product_name, quantity;

&ltINPUT Order ID="product_name " VALUE="" SIZE=20>

&ltINPUT Order ID="price" VALUE="" SIZE=20>

&ltINPUT Order ID="quantity" VALUE="" SIZE=20>

&ltBR>

END LOOP;

EXIT WHEN c_ shoppingcart _Info %NOTFOUND;

DISPLAY " CAN NOT FIND PRODUCT"

DISPLAY " PLEASE TRY INPUT AGAIN!"

-- End processing.

CLOSE c_ shoppingcart _Info;

END;

&ltINPUT TYPE="reset" value="Cancel">

&ltINPUT TYPE="refresh" value="Back">

&ltINPUT TYPE="save" value="Update">

</FORM>

bt_CANCLE = DELETE shoppingcart_table_tmp(n) & GO TO MAIN PAGE

bt_BACK = GO TO SHOPPING CART PAGE &

PROCEDURE Update_Shoppingcart_table_tmp(n)

bt_UPDATE = SAVE shoppingcart_table_tmp(n) & GO TO ORDER FORM PAGE

Design Specification

Section III

(ORDER FORM PAGE):

&ltFORM ACTION="http://southshore.com:6666/CPS616/owa/Order_pkg.calc_order"

METHOD="CALCULATE">

DISPLAY "VIEW YOUR ORDER AND CHOOSE PAYMENT DETAILS":

PROCEDURE get_shoppingcart_table_tmp(n)

DECLARE

v_subtotal subtotald%TYPE;

v_total totald%TYPE;

v_tax taxd%TYPE;

v_discount discountd%TYPE;

v_payment_type payment_typed%TYPE;

v_creadcard_number creditcard_numberd%TYPE;

v_expiration_date expiration_dated%TYPE;

v_comments commentsd%TYPE;

` CURSOR c_order _Info IS

SELECT product_code, price, quantity

FROM Shoppingcart_table_tmp(n);

BEGIN

-- Begin cursor processing.

OPEN c_ order _Info;

LOOP

FETCH c_ order Info INTO v_product_code, v_price, v_quantity;

END LOOP;

v_subtotal = v_price * v_quantity;

v_total = v_subtotal * v_tax * v_discount;

EXIT WHEN c_ order _Info %NOTFOUND;

DISPLAY " CAN NOT FIND PRODUCT"

DISPLAY " PLEASE TRY INPUT AGAIN!"

-- End processing.

CLOSE c_ order _Info;

END get_shoppingcart_table_tmp(n);

PROCEDURE Update_ shoppingcart_table_tmp (ctitle IN VARCHAR2)

IS

BEGIN

htp.title('Order Information "' || ctitle || '"');

htp.p('&ltH1&gtOrder Information "' || ctitle || '"</H1>');

htp.p('&ltBR>');

htp.p('&ltTABLE BORDER ALIGN="CENTER">');

htp.p('&ltTR>');

htp.p('&ltTD&gtv_subtotal </TD>');

htp.p('&ltTD&gtv_freight</TD>');

htp.p('&ltTD&gtv_total</TD>');

htp.p('</TR>');

END display_Order_Info;

&ltINPUT Order ID="v_payment_type" VALUE="check | money order | credit card" BUTTON=>

&ltINPUT Order ID="v_creditcard_number" VALUE=" "SIZE=16>

&ltINPUT Order ID="v_expiration_date" VALUE=" "SIZE=6>

&ltINPUT Order ID="v_comments" VALUE="" SIZE=100>

&ltBR>

Design Specification

Section III

&ltINPUT TYPE="reset" value="Cancel">

&ltINPUT TYPE="refresh" value="Back">

&ltINPUT TYPE="save" value="Update">

</FORM>

INSERT INTO shoppingcart_table_tmp(n) (subtotal, freight, total, payment_type,

creditcard_number, expiration_date, comments)

VALUES (v_subtotal, v_freight, v_total, v_v_payment_type,

creditcard_number, v_expiration_date, v_comments);

End Update_ shoppingcart_table_tmp;

bt_CANCLE = DELETE shoppingcart_table_tmp(n) & GO TO SHOPPING CART PAGE

bt_BACK = GO TO SHOPPING CART PAGE

bt_UPDATE = SAVE shoppingcart_table_tmp(n) & PROCEDURE

Creat_Order_form(p) & GO TO VIEW ORDER PAGE

End Update_ shoppingcart_table_tmp;

End;

(OnlineOrder):

Begin

PROCEDURE Create_Order_ form (p)

DECLARE

p++ number;

CURSOR c_orderform_Info IS

SELECT product_name, product_code, price, subtotal, freight, total payment_type, creditcard_number, expiration_date, v_comments

FROM Shoppingcart_table_tmp(n);

BEGIN

-- Begin cursor processing.

OPEN c_ orderform _Info;

LOOP

FETCH c_ orderform _Info INTO product_name, product_code, price, subtotal, freight, total payment_type, creditcard_number, expiration_date, v_comments;

INSERT INTO Order_ form (p) (product_name, product_code, price, subtotal, freight, total payment_type, creditcard_number, expiration_date, v_comments)

VALUES (v_product_name, v_product_code, v_price, v_subtotal, v_freight, v_total v_payment_type, v_creditcard_number, v_expiration_date, v_comments);

END LOOP;

IF (payment_type = null | (payment_type = creditcard & creditcad_number = null | expiration_date = null))

DISPLAY " Please Enter Your Payment Type")

ELSE Trigger view_order;

EXIT WHEN c_ orderform _Info %NOTFOUND;

DISPLAY " CAN NOT FIND ORDER"

DISPLAY " PLEASE TRY INPUT AGAIN!"

-- End processing.

CLOSE c_ orderform _Info;

END Create_Order_ form (p)

;

Design Specification

Section III

(VIEW ORDER PAGE):

&ltFORM ACTION="http://southshore.com:6666/CPS616/owa/Order_pkg.get_order_view"

METHOD="GET">

DISPLAY "VIEW YOUR ORDER CONFIRM OR GO BACK TO CHANGE":

Trigger view_orderform_Info (ctitle IN VARCHAR2)

IS

BEGIN

htp.title('Inventory Information "' || ctitle || '"');

htp.p('&ltH1&gtInventory Information "' || ctitle || '"</H1>');

htp.p('&ltBR>');

htp.p('&ltTABLE BORDER ALIGN="CENTER">');

htp.p('&ltTR>');

htp.p('&ltTD&gt v_ product_name </TD>');

htp.p('&ltTD&gt v_ price </TD>');

htp.p('&ltTD&gt v_quantity </TD>');

htp.p('&ltTD&gt v_ freight </TD>');

htp.p('&ltTD&gt v_ tax </TD>');

htp.p('&ltTD&gt v_ discount </TD>');

htp.p('&ltTD&gt v_ total </TD>');

htp.p('&ltTD&gt v_comments </TD>');

htp.p('</TR>');

END display_Inventory_Info;

&ltINPUT TYPE="reset" value="Cancel">

&ltINPUT TYPE="submit" value="Confirm">

</FORM>

DISPLAY "PRESS CONFIRM BUTTON ONCE AND ORDER WILL BE COMPLETE!"

bt_CANCLE = DELETE shoppingcart.tmp & GO TO MAIN PAGE

bt_CONFIRM = SAVE Order_form(p) & GO TO THANKS INFO PAGE

(THANKS INFO PAGE):

DISPLAY "Thank you for your order!"

DISPLAY "Your order will be sent by email, please check you email later"

End;

Design Specification

Section III

Process Specification: Screen User Request Unit

(Screen Tracking & Order Info):

Begin

READ Order ID from CUSTOMERIZED PAGE

&ltFORM ACTION="http://southshore.com:6666/CPS616/owa/Customerized_pkg.get_orderid"

METHOD="GET">

Enter Your Order ID:

&ltINPUT Order ID="v_order_id" VALUE="" SIZE=20>

&ltBR>

&ltINPUT TYPE="reset" value="Cancel">

</FORM>

DECLARE

v_order_id order_id%TYPE:=’ v_order_id‘;

CURSOR c_order_Info IS

SELECT order_id

FROM tb_Order_Status;

BEGIN

-- Begin cursor processing.

OPEN c_order_Info;

LOOP

-- Retrieve one row.

FETCH c_order_Info INTO order_id;

END LOOP;

IF (v_order_id != order_id);

EXIT WHEN c_order_Info %NOTFOUND;

DISPLAY " CAN NOT FIND ORDER ID"

DISPLAY " PLEASE TRY INPUT AGAIN!"

-- End processing.

CLOSE c_order_Info;

END;

PROCEDURE display_order_status (ctitle IN VARCHAR2)

IS

BEGIN

htp.title('Your Order Information "' || ctitle || '"');

htp.p('&ltH1&gtYour Order Information "' || ctitle || '"</H1>');

htp.p('&ltBR>');

htp.p('&ltTABLE BORDER ALIGN="CENTER">');

htp.p('&ltTR>');

htp.p('&ltTD&gtDate </TD>');

htp.p('&ltTD&gtTime</TD>');

htp.p('&ltTD&gtActivity</TD>');

htp.p('&ltTD&gtLocation</TD>');

htp.p('&ltTD&gtExtra_Information</TD>');

htp.p('</TR>');

END display_order_status;

END;

Design Specification

Section III

(Screen System Info):

Begin

PROCEDURE Get_Customer_Profile from tb_Online User Profile

DECLARE

v_user_type user_type d%TYPE;

CURSOR c_logIn_Info IS

SELECT user_type

FROM tb_Online_User_Profile;

BEGIN

-- Begin cursor processing.

OPEN c_logIn_Info;

-- Retrieve one row.

FETCH c_logIn_Info INTO v_user_type;

IF user_type = 000

GO TO CUSOMER PAGE

IF user_type = 100

GO TO EMPLOYEE PAGE

-- End processing.

CLOSE c_lonIn_Info;

End;

Design Specification

Section III

(Validate Item/Price Change):

READ item from CUSTOMERIZED PAGE

&ltFORM ACTION="http://southshore.com:6666/CPS616/owa/Customerized_pkg.get_order_Info"

METHOD="GET">

Enter the Item.& Price:

&ltINPUT Item="v_item" VALUE="" SIZE=20>

&ltINPUT Price="v_price" VALUE="" SIZE=20>

&ltBR>

&ltINPUT TYPE="reset" value="Cancel">

</FORM>

DECLARE

v_price priced%TYPE:=’ v_price‘;

v_item itemd%TYPE:=’ v_item‘;

CURSOR c_Inventory_Info IS

SELECT item, price

FROM tb_Product;

BEGIN

-- Begin cursor processing.

OPEN c_Inventory_Info;

LOOP

-- Retrieve one row.

FETCH c_Inventory_Info INTO itme;

END LOOP;

IF (v_item != item);

EXIT WHEN c_Inventory_Info %NOTFOUND;

DISPLAY " CAN NOT FIND ITEM"

DISPLAY " PLEASE TRY INPUT AGAIN!"

-- End processing.

CLOSE c_Inventory_Info;

END;

PROCEDURE display_Inventory (ctitle IN VARCHAR2)

IS

BEGIN

htp.title('Inventory Information "' || ctitle || '"');

htp.p('&ltH1&gtInventory Information "' || ctitle || '"</H1>');

htp.p('&ltBR>');

htp.p('&ltTABLE BORDER ALIGN="CENTER">');

htp.p('&ltTR>');

htp.p('&ltTD&gtProduct_Code </TD>');

htp.p('&ltTD&gtProduct_Name</TD>');

htp.p('&ltTD&gtProduct_Description</TD>');

htp.p('&ltTD&gtRetail_Price</TD>');

htp.p('&ltTD&gtPrice</TD>');

htp.p('&ltTD&gtVendor_Code</TD>');

htp.p('&ltTD&gtOn_Hand</TD>');

htp.p('&ltTD&gtOn_Order</TD>');

htp.p('&ltTD&gtProduct_Code</TD>');

htp.p('</TR>');

END display_Inventory_Info;

Design Specification

Section III

READ price from CUSTOMERIZED PAGE

PROCEDURE Change_Inventory_Info

DECLARE

v_item itmed %TYPE;

v_price priced %TYPE;

CURSOR c_Inventory_Info IS

SELECT item, price

FROM tb_Product;

BEGIN

-- Begin cursor processing.

OPEN c_Inventory_Info;

-- Retrieve one row.

FETCH c_lnventory_Info INTO v_item, v_price;

IF v_item = item;

Then

v_price =temp;

temp = price;

v_price=price;

-- End processing.

CLOSE c_Inventory_Info;

End;

End;