Inventory and Billing Processing System

[Sumeet Sinha]

Project is all about

EMERGING AGRICULTURE OF INDIA is a local shop dealing in agricultural products like seeds, fertilizers, electrical equipments.

The basic objective the proposed project development is to automate the Inventory management and Billing Process system of EMERGING AGRICULTURE OF INDIA. The project is intended to provide systematic record keeping of the stock, products sold by the shop, list of customers, list of suppliers and to improve the billing process of the products sold by the shop, along with proper report generation facility of sales record.

Frond end; JAVA awt/swings
RDBMS: My SQL.

MODULE DESCRIPTION


I propose to use the following modules in my project
 CIMS – Customer Information Management System

 SRMS – Supplier Record Management System

 ERMS – Expenses Record Management System

 PCRS – Product & Category Record System

 BWMS – Branch & Warehouse Management System

 EMS – Employee Management System

 TPS – Transaction Process System

1. Customer Information Management System

In this module we deal with the information records of the customers coming regularly to the shop. In this module we have a database that deals with entry of the information for new customers.

CUSTOMER RECORD
Table Name: TBLCUSTOMER_MSTR
Primary Key: TBLCUSTOMER_MSTR.CUST_ID

Column Name Data Type Width Characteristic
CUST_ID Text 25 PRIMARY KEY
NAME Char 40 Not Null
ADDRESS Text 30 Not Null
CITY Char 20 Not Null
STATE Char 20 Not Null
PIN Number 10 Not Null
PH_NO Number 10 Not Null
FAX_NO Number 10 Null




2. Supplier Record Management System

In this module we deal with the information records of the suppliers delivering various products to the shop for reselling. In this module we have a database that deals with record keeping of the detailed information of the suppliers.

SUPPLIER RECORD
Table Name: TBLSUPPLIER_MSTR
Primary Key: TBLSUPPLIER_MSTR.SUP_ID

Column Name Data Type Width Characteristic
SUP_ID Text 25 PRIMARY KEY
COMP_NAME Char 40 NOT NULL
CONT_NAME Char 40 NOT NULL
STREET Text 30 NOT NULL
CITY Char 20 NOT NULL
STATE Char 20 NOT NULL
PIN Number 10 NOT NULL
PH_NO Number 10 NOT NULL
FAX_NO Number 10 NULL


3. Employee Management System

In this module we deal with the information records of the employees working in the shop. In this module we have a GUI form for the detailed information keeping of employees employed at the shop. To assist record keeping we use the following database that keeps the record of the employees.

EMPLOYEE RECORD
Table Name: TBLEMP_MSTR
Primary Key: TBLEMP_MSTR.EMP_ID









Column Name Data Type Width Characteristic
EMP_ID Text 25 PRIMARY KEY
LAST_NAME Char 40 NOT NULL
FIRST_NAME Char 30 NOT NULL
TITLE Char 10 NOT NULL
DOB Date/Time NOT NULL
DOH Date/Time NOT NULL
ADD Char 10 NOT NULL
CITY Char 15 NOT NULL
STATE Char 15 NOT NULL
PIN Number 10 NOT NULL
COUNTRY Char 10 NOT NULL
PH_NO Number 10 NOT NULL
SALARY Number 8,3 NOT NULL
REPORTS_TO Text 20 NOT NULL

4. Expense Record Management System

In this module we deal with the information records of the expenses incurred under various head. In this module we have a database that deals with record keeping of the detailed information of the expenses.

EXPENSE RECORD
Table Name: TBLEXPENSE_MSTR
Primary Key: TBLEXPENSE_MSTR.EX_ID

Column Name Data Type Width Characteristic
EX_ID Text 10 PRIMARY KEY
PURPOSE Char 40 NOT NULL
EMP_ID Text 10 FOREIGN KEY
DATE_EXP Date/Time NOT NULL
AMT Number 8,3 NOT NULL








5. Product & Categories Record System

In this module we deal with the information records of the products and their various categories sold at the shop. In this module the databases that deals with record keeping of the products and their categories are discussed below:

PRODUCT RECORD
Table Name: TBLPRODUCT_MSTR
Primary Key: TBLPRODUCT_MSTR.PROD_ID
Secondary Key: TBLPRODUCT_MSTR.SUP_ID ->
TBLSUPPLIER_MSTR.SUP_ID
TBLPRODUCT_MSTR.SUP_ID ->
TBLCATEGORY_MSTR.CAT_ID

Column Name Data Type Width Characteristic
PROD_ID Text 10 PRIMARY KEY
PROD_NAME Char 40 NOT NULL
SUP_ID Text 10 FOREIGN KEY
CAT_ID Text 10 FOREIGN KEY
QT_PER_PRICE Number 8,3 NOT NULL
UNIT_PRICE Number 8,3 NOT NULL
UNIT_STOCK Number 10 NOT NULL
UNIT_ORDER Number 10 NOT NULL
REORDER_LEVEL Number 10 NOT NULL

CATEGORY RECORD
Table Name: TBLCATEGORY_MSTR
Primary Key: TBLCATEGORY_MSTR.CAT_ID

Column Name Data Type Width Characteristic
CAT_ID Text 10 PRIMARY KEY
CAT_NAME Char 40 NOT NULL
DESC Text 10 NOT NULL








6. Branch & Warehouse Management System

In this module we deal with the information records of the shops and its various warehouses. In this module the databases that deals with record keeping of the shops branches and its warehouses are discussed below:

BRANCH RECORD
Table Name: TBLBRANCH_MSTR
Primary Key: TBLBRANCH_MSTR.BRANCH_ID

Column Name Data Type Width Characteristic
BRANCH_ID Text 10 PRIMARY KEY
B_NAME Char 40 NOT NULL
INCHARGE Text 20 NOT NULL
ADDRESS1 Text 40 NOT NULL
ADDRESS2 Text 40 NOT NULL
CITY Char 15 NOT NULL
STATE Char 15 NOT NULL
PIN Number 10 NOT NULL
PH_NO Number 10 NOT NULL
E_MAIL Text 40 NOT NULL

WAREHOUSE RECORD
Table Name: TBLWAREHOUSE_MSTR
Primary Key: TBLWAREHOUSE_MSTR.WH_ID

Column Name Data Type Width Characteristic
WH_ID Text 10 PRIMARY KEY
INCHG_NAME Char 30 NOT NULL
ADDRESS Char 40 NOT NULL
CITY Char 15 NOT NULL
STATE Char 15 NOT NULL
PIN Number 10 NOT NULL
PH_NO Number 10 NOT NULL







7. Transaction Process System

In this module we deal with the information records of all the transactions taking place at the shop i.e. the billing process and the supplier payment process. In this module the databases that deals with record keeping of the transactions are given below:

BILLING RECORD
Table Name: TBLBILLING_MSTR
Primary Key: TBLBILLING_MSTR.BILL_NO

Column Name Data Type Width Characteristic
BILL_NO Text 10 PRIMARY KEY
ITEM_PRICE1 Number 10 NOT NULL
ITEM_PRICE2 Number 10 NOT NULL
ITEM_PRICE3 Number 10 NOT NULL
CUS_NAME Char 40 NOT NULL
AMT
Number 10 NOT NULL
TAX Number 10 NOT NULL
TOT_AMT Number 10 NOT NULL

SUPPLIER PAYMENT RECORD
Table Name: TBLSPAY_MSTR
Primary Key: TBLSPAY_MSTR.SP_ID

Column Name Data Type Width Characteristic
SP_ID Text 10 PRIAMRY KEY
SUPP_NAME Char 30 NOT NULL
ADDRESS Char 40 NOT NULL
SUPP_DETAILS Char 20 NOT NULL
TOT_AMT Number 10 NOT NULL
DATE Date/Time 10 NOT NULL











SYSTEM TEMPLATE
(INPUT, PROCESS, OUTPUT)



The process logic applied to arrive at output from input has been shown in the System Templates tables shown below:

NEW CUSTOMER ENTRY FORM

Inputs Process Output
• New Customer Record Entry Verify Validate Check Accept/
Reject -Updated Customer
Database
-Customer Welcome
Letter
-Update sales
Record
Keep the Record on Hold till Bill is cleared at cash counter.



NEW EMPLOYEE ENTRY FORM

Inputs Process Output
• New Employee Record Entry Verify Validate Check Accept/
Reject -Updated Employee
Database
-Employee Id
Allocation
-Password
Allocation

Keep the Record on Hold till Appointment letter is issued






NEW PRODUCT ENTRY FORM

Inputs Process Output
• Log-In using Employee Password
• Enter new
Product
Details Verify Validate
Vouchers -Updated
Product Record
Database
-Updated Related
Tables

Keep the Record Update on Hold till Verification


EXPENSES UPDATION FORM

Inputs Process Output
• Log-In using Employee Password
• Enter details of the expense Verify/ Validate
Vouchers -Updated Expenses



Database

Keep the Record Update on Hold till Verification






SALES TRANSACTION ENTRY FORM

Inputs Process Output
• Enter Sales Transaction Details Calculate Total Amount to be collected -Updated Sales
Database
-Sale Receipt
-Sales Report
Generation
Verify Stock Database and update transaction



---------------
I hope all these information will be enough for you to make the project. Please use all tables information to make the project.