Super Market Project

drop table category;
drop table item_master;
drop table next_num;
drop table customer_master;
drop table invoice_master;
drop table inv_details;
drop table supplier_master;
drop table purchase_master;
drop table purchase_details;
drop table indent_master;
drop table indent_details;
drop table mr_master;
drop table mr_details;

create user rs identified by rs;

grant all privilege to rs;

Create table item_master(
item_cd number(6),
item_nm varchar2(30),
qty number(6,2),
Rate number(6,2),
Re_order_lvl number(6,2),
Unit varchar2(15),
F_s_m_flag char(1),
Xyz_ana char(1),
Abc_ana char(1),
Constraint item_master_pk_item_cd primary key(item_cd));


Create table supplier_master(
supp_cd number(6),
supp_nm varchar2(30),
add1 varchar2(50),
add2 varchar2(50),
city varchar2(15),
state varchar2(15),
country varchar2(15),
pincode number(8),
email varchar2(25),
cr_unit number(8),
constraint supplier_master_pk_supp_cd primary key(supp_cd));


create table category(
category_cd number(6),
category_nm varchar2(20),
constraint category_pk_category_cd primary key(category_cd));


create table customer_master(
cust_cd number(6),
cust_nm varchar2(30),
add1 varchar2(50),
add2 varchar2(50),
city varchar2(15),
state varchar2(15),
country varchar2(15),
pincode number(6),
phone_nm number(20),
category_cd number(1),
category_nm varchar2(20),
constraint customer_master_pk_cust_cd primary key(cust_cd),
constraint fk_category_cd_category_nm foreign key(category_cd)references category);


create table next_num(
type_cd number(6)
type_nm varchar2(30),
constraint next_num_pk_type_cd primary key(type_cd));


Create table purchase_master(
Type_cd number(6),
po_num number(6) unique,
supp_cd number(6),
descr varchar2(50),
dated date,
constraint purchase_master_pk_type_cd primary key(type_cd,po_num),
constraint purchase_master_fk_type_cd foreign key(type_cd)references next_num,
constraint purchase_master_fk_supp_cd foreign key(supp_cd)references supplier_master(supp_cd));

create table purchase_details (
type_cd number(6),
po_num number(6),
item_cd number(6),
qty number(6,2),
rate number(6,2),
descr varchar2(20),
dated date,
delivery_days number(3),
constraints purchase_details_fk_item_cd foreign key(item_cd)references item_master,
constraints details_fk_item_cd_po_num foreign key(type_cd,po_num)references purchase_master);


Create table indent_master(
type_cd number(6),
indent_num number(6),
descr varchar2(50),
dated date,
constraint indent_master_fk_type_cd foreign key(type_cd)references next_num(type_cd),
constraint indent_master_pk_type_cd primary key(type_cd,indent_num))


Create table indent_details(
type_cd number(6),
indent_num number(6),
item_cd number(6),
qty number(6,2),
descr varchar2(50),
num_of_days number(3),
constraint indent_details_fk_type_cd foreign key(type_cd,indent_num)references indent_master,
constraint indent_details_fk_item_cd foreign key(item_cd)references item_master);


create table MR_Master(
type_cd number(6),
Mr_num number(6),
po_num number(6) references purchase_master(po_num),
descr varchar2(50),
dated date,
constraint MR_Master_pk primary key(type_cd,Mr_num),
constraint MR_Master_fk_type_cd foreign key(type_cd)references next_num(type_cd));



Create table mr_details(
type_cd number(6),
mr_num number(6),
item_cd number(6),
qty_required number(6,2),
qty_accept number(6,2),
rate number(6,2),
constraint mr_details_fk_type_cd foreign key(type_cd,mr_num)references mr_master(type_cd,mr_num),
constraint mr_details_fk_item_cd foreign key(item_cd)references item_master);


create table invoice_master(
type_cd number(6),
inv_num number(6),
cust_cd number(6),
dated date,
descr varchar2(50),
constraint invoice_master_pk_type_cd primary key(type_cd,inv_num),
constraint invoice_master_fk_cust_cd foreign key(cust_cd)references customer_master);


create table inv_details(
type_cd number(6),
inv_num number(6),
item_cd number(6),
cust_cd number(6),
qty number(6,2),
rate number(6,2),
discount number(6,2),
descr varchar2(6),
constraint inv_details_fk_type_cd foreign key(type_cd,inv_num)references invoice_master,
constraint inv_details_fk_item_cd foreign key(item_cd)references item_master);


create table user1(
user_id varchar2(15),password varchar2(10));


insert into item_master
values(&item_cd,'&item_nm',&qty,&rate,&re_order_lvl,'&unit',
'&f_s_m','&xyz_ana','&abc_ana');

insert into supplier_master
values(&supp_cd,'&supp_nm','&add1','&add2','&city','&state',
'&country',&pincode,'&email',&cr_unit);


insert into category
values(&category_cd,'&category_nm');

insert into customer_master
values(&cust_cd,'&cust_nm','&add1','&add2','&city','&state',
'&country',&pincode',&phone_nm,&category_cd,'&category_nm');


insert into next_num
values(&type_cd,'&type_nm');

insert into purchase_master
values(&type_cd,&po_num,&supp_cd,'&descr',&dated);


insert into purchase_details
values(&type_cd,&po_num,&item_cd,&qty,&rate,'&descr',&dated,&delivery_days);

insert into indent_master
values(&type_cd,&indent_num,'&descr',&dated);

insert  into indent_details
values(&type_cd,&indent_num,&item_cd,&qty,'&descr',&num_of_days);


insert into mr_master
values(&type_cd,&mr_num,&po_num,'&descr',&dated);

insert into mr_details
values(&type_cd,&mr_num,&item_cd,&qty_required,&qty_accept,&rate);


insert into invoice_master
values(&type_cd,&inv_num,&cust_cd,&dated,'&descr');

insert into inv_details
values(&type_cd,&inv_num,&item_cd,&cust_cd,&qty,&rate,&discount,'&descr');

insert into user1
values('&user_id','&password');