7/27/2019 Reports Case Study.doc

1/4

Case study for Oracle Reports

7/27/2019 Reports Case Study.doc

2/4

Case Study for Oracle Forms

ABC Service Station is a leading distributor of petrol/diesel in the city. This has got anumber of corporate bodies as its clients. Due to increase in the clientele, ABC wants tocomputerize the petrol distribution system. You are entrusted with the responsibilities ofdeveloping a suitable system in ORACLE8

The project specifications are indicated below. You may change the specifications, ifnecessary. For every modification made you are to give the details of the changes madewith reasons for modification. The general business flow is as follows:

A customer master and the current price of petrol/diesel per ltr has to be maintained inthe proposed system. Every morning, before distribution to customers start, a tankersupplies petrol/diesel and the quantity of oil received should be captured in the system.

After that, depending on the stock available, oil is sold to customers and thesetransactions should be recorded in the system as and when they occur. The systemshould automatically update the available stock of oil as and when it increases ordecreases. There should be a provision to generate and print customer bills for aspecified period. Besides, there should also be a provision to generate a report showing

the daily transaction list.

Master LayoutNeed to create a report as per attached layout design.

_____________________________________________________________________________________

Page 2 of 4

ABC SERVICE STATION - Generate Customer Bill (ABC040S)

Customer Id:

Address:

Pin Code:

From Date: To Date:

Date Petrol Qty Petrol Rate Diesel Qty Diesel Rate Amount

TOTAL

7/27/2019 Reports Case Study.doc

3/4

Case Study for Oracle Forms

Parameters :Parameter Name Mandatory?

Customer Id YDate from N

Date to N

Date to should be greater than date from.

Associated tables for the report is

Tables

T_CUST (PK = CUST_ID)

cust_id number (5) not nullcust_name varchar2(30)cust_add1 varchar2 (30)cust_add2 varchar2 (30)cust_add3 varchar2 (30)pin_code number(6)telephone number(7)cust_from date not nullcust_type varchar2 (1) default I not nullintroducer_id number(5) not null

T_STOCK (PK = DATE_OF_RECEIPT)

date_of_receipt date not nullopen_stock_petrol number(5) not nullopen_stock_diesel number(5) not nullstock_rec_petrol number(5) not nullstock_rec_diesel number(5) not nullavail_stock_petrol number(5) not nullavail_stock_diesel number(5) not null

T_DAILY_TRANSACTION (PK = TRAN_DATE, CUST_ID)

tran_date date not nullcust_id number(4) not nullpetrol_qty number(5)diesel_qty number(5)petrol_rate number(6,2)diesel_rate number(6,2)total_value number(7,2) not null

_____________________________________________________________________________________

Page 3 of 4

7/27/2019 Reports Case Study.doc

4/4

Case Study for Oracle Forms

T_PRICE

petrol_rate number(6,2) not null

diesel_rate number(6,2) not null

(The data for this table can be entered through SQL*Plus or thru Forms)

_____________________________________________________________________________________

Page 4 of 4