create table package_sup(
sup_no number(10) primary key,
sup_name varchar2(100) not null,
sup_add varchar2 (100),
sup_tel1 number(20),
sup_tel2 number(20),
sup_fax number(20),
sup_email varchar2(100) unique check (sup_email like '%@%.%'),
sup_tax_card_no varchar2(100),
sup_tax_file_no varchar2(100),
con_per_name1 varchar2(100),
con_per_name2 varchar2(100),
con_per_name3 varchar2(100),
con_per_position1 varchar2(100),
con_per_position2 varchar2(100),
con_per_position3 varchar2(100),
con_per_mobile1 number(20),
con_per_mobile2 number(20),
con_per_mobile3 number(20),
con_per_email1 varchar2(100) unique check (con_per_email1 like '%@%.%'),
con_per_email2 varchar2(100) unique check (con_per_email2 like '%@%.%'),
con_per_email3 varchar2(100) unique check (con_per_email3 like '%@%.%'),
activity1 varchar2(100),
activity2 varchar2(100),
activity3 varchar2(100),
activity4 varchar2(100),
activity5 varchar2(100),
notes varchar2(1500));



create table raw_sup(
Rsup_no number(10) primary key,
Rsup_name varchar2(100) not null,
Rsup_add varchar2 (100),
Rsup_tel1 number(20),
Rsup_tel2 number(20),
Rsup_fax number(20),
Rsup_email varchar2(100) unique check (Rsup_email like '%@%.%'),
Rsup_tax_card_no varchar2(100),
Rsup_tax_file_no varchar2(100),
Rno_of_feddans varchar2(1000),
Rcon_per_name1 varchar2(100),
Rcon_per_name2 varchar2(100),
Rcon_per_name3 varchar2(100),
Rcon_per_position1 varchar2(100),
Rcon_per_position2 varchar2(100),
Rcon_per_position3 varchar2(100),
Rcon_per_mobile1 number(20),
Rcon_per_mobile2 number(20),
Rcon_per_mobile3 number(20),
Rcon_per_email1 varchar2(100) unique check (Rcon_per_email1 like '%@%.%'),
Rcon_per_email2 varchar2(100) unique check (Rcon_per_email2 like '%@%.%'),
Rcon_per_email3 varchar2(100) unique check (Rcon_per_email3 like '%@%.%'),
Ractivity1 varchar2(100),
Ractivity2 varchar2(100),
Ractivity3 varchar2(100),
Ractivity4 varchar2(100),
Ractivity5 varchar2(100),
Rnotes varchar2(1500));

create table users(
user_id varchar2(100),
user_password varchar2(100));




create table or_package(
order_no number(10) not null,
odate date not null,
osup_no number(10) references package_sup(sup_no),
prod_name varchar2(100),
payment varchar2(100),
prod_type_no1 number(10),
prod_type_no2 number(10),
prod_type_no3 number(10),
prod_type_no4 number(10),
prod_type_no5 number(10),
prod_type_no6 number(10),
prod_type_no7 number(10),
prod_type1 varchar2(100),
prod_type2 varchar2(100),
prod_type3 varchar2(100),
prod_type4 varchar2(100),
prod_type5 varchar2(100),
prod_type6 varchar2(100),
prod_type7 varchar2(100),
prod_unit1 varchar2(100),
prod_unit2 varchar2(100),
prod_unit3 varchar2(100),
prod_unit4 varchar2(100),
prod_unit5 varchar2(100),
prod_unit6 varchar2(100),
prod_unit7 varchar2(100),
prod_qty1 number (10,5),
prod_qty2 number (10,5),
prod_qty3 number (10,5),
prod_qty4 number (10,5),
prod_qty5 number (10,5),
prod_qty6 number (10,5),
prod_qty7 number (10,5),
prod_price1 number (10,5),
prod_price2 number (10,5),
prod_price3 number (10,5),
prod_price4 number (10,5),
prod_price5 number (10,5),
prod_price6 number (10,5),
prod_price7 number (10,5),
tax_price number (10,5));


create table or_raw(
rorder_no number(10) not null,
rodate date not null,
orsup_no number(10) references raw_sup(rsup_no),
rprod_name varchar2(100),
rpayment varchar2(100),
rprod_type_no1 number(10),
rprod_type_no2 number(10),
rprod_type_no3 number(10),
rprod_type_no4 number(10),
rprod_type_no5 number(10),
rprod_type_no6 number(10),
rprod_type_no7 number(10),
rprod_type1 varchar2(100),
rprod_type2 varchar2(100),
rprod_type3 varchar2(100),
rprod_type4 varchar2(100),
rprod_type5 varchar2(100),
rprod_type6 varchar2(100),
rprod_type7 varchar2(100),
rprod_unit1 varchar2(100),
rprod_unit2 varchar2(100),
rprod_unit3 varchar2(100),
rprod_unit4 varchar2(100),
rprod_unit5 varchar2(100),
rprod_unit6 varchar2(100),
rprod_unit7 varchar2(100),
rprod_qty1 number (10,5),
rprod_qty2 number (10,5),
rprod_qty3 number (10,5),
rprod_qty4 number (10,5),
rprod_qty5 number (10,5),
rprod_qty6 number (10,5),
rprod_qty7 number (10,5),
rprod_price1 number (10,5),
rprod_price2 number (10,5),
rprod_price3 number (10,5),
rprod_price4 number (10,5),
rprod_price5 number (10,5),
rprod_price6 number (10,5),
rprod_price7 number (10,5),
rtax_price number (10,5));


create table qu_package(
quotation_no number(10) not null,
qdate date not null,
qsup_no number(10) references package_sup(sup_no),
qprod_name varchar2(100),
qpayment varchar2(100),
qprod_type_no1 number(10),
qprod_type_no2 number(10),
qprod_type_no3 number(10),
qprod_type_no4 number(10),
qprod_type_no5 number(10),
qprod_type_no6 number(10),
qprod_type_no7 number(10),
qprod_type1 varchar2(100),
qprod_type2 varchar2(100),
qprod_type3 varchar2(100),
qprod_type4 varchar2(100),
qprod_type5 varchar2(100),
qprod_type6 varchar2(100),
qprod_type7 varchar2(100),
qprod_unit1 varchar2(100),
qprod_unit2 varchar2(100),
qprod_unit3 varchar2(100),
qprod_unit4 varchar2(100),
qprod_unit5 varchar2(100),
qprod_unit6 varchar2(100),
qprod_unit7 varchar2(100),
qprod_qty1 number (10,5),
qprod_qty2 number (10,5),
qprod_qty3 number (10,5),
qprod_qty4 number (10,5),
qprod_qty5 number (10,5),
qprod_qty6 number (10,5),
qprod_qty7 number (10,5),
qprod_price1 number (10,5),
qprod_price2 number (10,5),
qprod_price3 number (10,5),
qprod_price4 number (10,5),
qprod_price5 number (10,5),
qprod_price6 number (10,5),
qprod_price7 number (10,5),
qtax_price number (10,5));


create table qu_raw(
qrquotation_no number(10) not null,
qrdate date not null,
qrsup_no number(10) references raw_sup(rsup_no),
qrprod_name varchar2(100),
qrpayment varchar2(100),
qrprod_type_no1 number(10),
qrprod_type_no2 number(10),
qrprod_type_no3 number(10),
qrprod_type_no4 number(10),
qrprod_type_no5 number(10),
qrprod_type_no6 number(10),
qrprod_type_no7 number(10),
qrprod_type1 varchar2(100),
qrprod_type2 varchar2(100),
qrprod_type3 varchar2(100),
qrprod_type4 varchar2(100),
qrprod_type5 varchar2(100),
qrprod_type6 varchar2(100),
qrprod_type7 varchar2(100),
qrprod_unit1 varchar2(100),
qrprod_unit2 varchar2(100),
qrprod_unit3 varchar2(100),
qrprod_unit4 varchar2(100),
qrprod_unit5 varchar2(100),
qrprod_unit6 varchar2(100),
qrprod_unit7 varchar2(100),
qrprod_qty1 number (10,5),
qrprod_qty2 number (10,5),
qrprod_qty3 number (10,5),
qrprod_qty4 number (10,5),
qrprod_qty5 number (10,5),
qrprod_qty6 number (10,5),
qrprod_qty7 number (10,5),
qrprod_price1 number (10,5),
qrprod_price2 number (10,5),
qrprod_price3 number (10,5),
qrprod_price4 number (10,5),
qrprod_price5 number (10,5),
qrprod_price6 number (10,5),
qrprod_price7 number (10,5),
qrtax_price number (10,5));