Designing the Doctor Appointments Made Easy Application
Now that we understand the steps and sequences in the business flow, we will design the application.
NOTE: It is assumed that the users logging in into the application at run time, i.e, the doctors, patients, and hospital staff, have already been created beforehand. Create the following users using the Manage Users And Groups menu option under Workspace Administration:
| Persona | User Name |
|---|---|
| Doctors | ROBERT |
| ANACHATT | |
| JANE | |
| BO | |
| PAT | |
| MARTIN | |
| Patients | SUSIE |
| DARYL | |
| RAJESH | |
| Hospital Staff |
STEVE |
.
Create the App
- Navigate to App Builder
- Click Create
- Click New Application
For Name – enter Doctor Appointments Made Easy!
Sample Table and Sample Data
Install the Dataset
- Navigate to SQL Workshop and click SQL Scripts
- Click Create
- Copy and Paste the commands given below into the Script Editor to create
- Table Doctor and insert doctor records into it.
- Table Appointment to store patient’s appointment details.
- Table Patient_Feedback to store feedbacks from patients.
- Click Run
begin
execute immediate 'drop table appointment';
execute immediate 'drop table doctor';
execute immediate 'drop table patient_feedback';
execute immediate 'drop sequence appoint_seq';
execute immediate 'drop sequence patient_seq';
exception
when others then
dbms_output.put_line('DB Objects not exist');
end;
/
CREATE TABLE "APPOINTMENT"
( "BOOKING_ID" NUMBER(*,0),
"PATIENT_USERNAME" VARCHAR2(40) NOT NULL ENABLE,
"DOCTOR_NO" NUMBER(*,0) NOT NULL ENABLE,
"SCHEDULE" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE,
"PATIENT_EMAIL" VARCHAR2(40) NOT NULL ENABLE,
"DOCTOR_EMAIL" VARCHAR2(40) NOT NULL ENABLE,
"STATUS" VARCHAR2(10),
"FEE" NUMBER,
"WORKFLOW_ID" NUMBER,
PRIMARY KEY ("BOOKING_ID")
USING INDEX ENABLE
) ;
CREATE TABLE "DOCTOR"
( "DNO" NUMBER(*,0),
"DNAME" VARCHAR2(20),
"SPECIALIZATION" VARCHAR2(20),
"QUALIFICATION" CHAR(15),
"DOC_EMAIL" VARCHAR2(100) DEFAULT 'dname@abc.com',
PRIMARY KEY ("DNO")
USING INDEX ENABLE
) ;
CREATE TABLE "PATIENT_FEEDBACK"
( "ID" NUMBER(*,0),
"PATIENT_NAME" VARCHAR2(40),
"DOCTOR_NO" VARCHAR2(100),
"APPOINTMENT" TIMESTAMP (6) WITH TIME ZONE,
"FEEDBACK" VARCHAR2(4000),
"RATING" NUMBER(*,0) NOT NULL ENABLE,
"CREATED_AT" TIMESTAMP (6) WITH TIME ZONE,
"UPDATED_AT" TIMESTAMP (6) WITH TIME ZONE
) ;
CREATE SEQUENCE "APPOINT_SEQ" MINVALUE 1 MAXVALUE 9999999 INCREMENT BY 1 START WITH 901 CACHE 100 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE "PATIENT_SEQ" MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH 201 CACHE 100 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE OR REPLACE EDITIONABLE TRIGGER "APPOINTMENT_T"
before
insert or update or delete on "APPOINTMENT"
for each row
begin
if inserting then
if :new.booking_id is null then
:new.booking_id := appoint_seq.nextval;
end if;
end if;
end;
/
ALTER TRIGGER "APPOINTMENT_T" ENABLE;
CREATE OR REPLACE EDITIONABLE TRIGGER "PATIENT_FEEDBACK_T"
before
insert or update or delete on "PATIENT_FEEDBACK"
for each row
begin
if inserting then
if :new.id is null then
:new.id := patient_seq.nextval;
end if;
end if;
end;
/
ALTER TRIGGER "PATIENT_FEEDBACK_T" ENABLE;
-- insert doc data
insert into doctor values
(10, 'ROBERT', 'ONCOLOGY','MS','robert@abc.com');
insert into doctor values
(20, 'BO', 'CARDIOLOGY', 'FRCS','bo@abc.com');
insert into doctor values
(30, 'JANE', 'ENT','MBBS','jane@abc.com');
insert into doctor values
(40, 'ANACHATT', 'PSYCHIATRY','MS','anne@abc.com');
insert into doctor values
(50, 'PAT', 'GENERAL','MBBS','pat@abc.com');
insert into doctor values
(60, 'MARTIN', 'RENOLOGY','MS','martin@abc.com');
4. Navigate back to SQL Workshop and click SQL Scripts
5. Click Create
6. Copy and Paste the commands given below into the Script Editor to create a package spec eba_demo_wf_doc_apt.sql that contains the procedures for creating and updating the appointments , checking doctor’s availability, etc.
set define '^' verify off
prompt ...eba_demo_wf_doc_apt
create or replace package eba_demo_wf_doc_apt authid current_user as
function check_availability(
p_doctor_id in number,
p_request_date in timestamp with time zone)
return varchar2;
function confirm_appointment(
p_doctor_id in number,
p_request_date in timestamp with time zone,
p_doctor_email in varchar2,
p_patient_name in varchar2,
p_patient_email in varchar2,
p_workflow_id in number)
return number;
function update_fees(
p_doctor_id in number,
p_request_date in varchar2,
p_booking_id in number,
p_patient_name in varchar2)
return number;
procedure update_appointment(
p_booking_id in number,
p_status in varchar2 default null,
p_fee in number default null);
end eba_demo_wf_doc_apt;
/
show err;
7. Click Run
8. Navigate back to SQL Workshop and click SQL Scripts
9. Click Create
10. Copy and Paste the commands given below into the Script Editor to create the package body eba_demo_wf_doc_apt.plb that contains the implementations for the procedures in the package spec created above.
set define '^' verify off
prompt ...eba_demo_wf_doc_apt
create or replace package body eba_demo_wf_doc_apt as
function check_availability(
p_doctor_id in number,
p_request_date in timestamp with time zone)
return varchar2
is
l_no number := 0;
begin
select doctor_no
into l_no
from appointment
where schedule = p_request_date
and doctor_no = p_doctor_id;
return 'BUSY';
exception
when no_data_found then
return 'AVAILABLE';
end check_availability;
function confirm_appointment(
p_doctor_id in number,
p_request_date in timestamp with time zone,
p_doctor_email in varchar2,
p_patient_name in varchar2,
p_patient_email in varchar2,
p_workflow_id in number)
return number
is
l_booking_id number;
begin
insert into appointment (
patient_username,
doctor_no,
schedule,
patient_email,
doctor_email,
status,
workflow_id)
values (
p_patient_name,
p_doctor_id,
p_request_date,
p_patient_email,
p_doctor_email,
'CONFIRMED',
p_workflow_id
)
returning booking_id into l_booking_id;
return l_booking_id;
end confirm_appointment;
function update_fees(
p_doctor_id in number,
p_request_date in varchar2,
p_booking_id in number,
p_patient_name in varchar2)
return number
is
l_fee number := 500;
begin
-- check that this is not a case of followup
select 0
into l_fee from dual
where exists (select patient_username
from appointment
where patient_username = p_patient_name
and doctor_no = p_doctor_id
and schedule >= to_timestamp_tz(p_request_date,'DD-MON-YYYY HH24:MI:SS') - 7
and status = 'PAID');
-- update the appointment table to reflect the revised fee for the consultation
update_appointment(
p_booking_id => p_booking_id,
p_fee => l_fee);
return l_fee;
exception
when no_data_found then
-- this is not a followup
return l_fee;
end update_fees;
procedure update_appointment(
p_booking_id in number,
p_status in varchar2 default null,
p_fee in number default null)
is
begin
update appointment
set status = coalesce(p_status, status),
fee = coalesce(p_fee, fee)
where booking_id = p_booking_id;
end update_appointment;
end eba_demo_wf_doc_apt;
/
show err;
In the next section, we will create the Doctor Appointment Workflow.