| Oracle® Database Application Express Advanced Tutorials Release 3.0 Part Number B28842-01 |
|
|
View PDF |
| Oracle® Database Application Express Advanced Tutorials Release 3.0 Part Number B28842-01 |
|
|
View PDF |
This appendix contains DDLs (data definition language) and scripts necessary to complete a number of tutorials in Oracle Database Application Express Advanced Tutorials.
Topics in this section include:
The following DDL creates all the required database objects for the issue tracking application in "How to Build and Deploy an Issue Tracking Application" on page 14-1.
--
-- This DDL creates all the database objects used by the
-- Issue Management Application featured in
--
-- HT_PROJECTS
--
-- The HT_PROJECTS DDL:
-- + creates the projects table with the necessary columns,
-- including a new column for a system generated primary key
-- + declares the new primary key
-- + implements the real primary key, project name, as a unique key
-- + implements a sequence to generate project id
-- + assigns the sequence to populate the project id
-- whenever a new record is created
-- + declares table and column comments
--
CREATE TABLE ht_projects (
project_id INTEGER NOT NULL,
project_name VARCHAR2(100) NOT NULL,
start_date DATE NOT NULL,
target_end_date DATE NOT NULL,
actual_end_date DATE
)
/
ALTER table ht_projects
ADD CONSTRAINT ht_projects_pk
PRIMARY KEY (project_id)
/
ALTER TABLE ht_projects
ADD CONSTRAINT ht_projects_uk
UNIQUE (project_name)
/
CREATE SEQUENCE ht_projects_seq
INCREMENT BY 1
START WITH 40
/
CREATE OR REPLACE TRIGGER bi_ht_projects
BEFORE INSERT ON ht_projects
FOR EACH ROW
BEGIN
IF :new.project_id is null
THEN SELECT ht_projects_seq.nextval
INTO :new.project_id
FROM DUAL;
END IF;
END;
/
COMMENT ON table ht_projects IS
'All projects currently underway.'
/
COMMENT ON column ht_projects.project_id IS
'The system generated unique identifier for the project.'
/
COMMENT ON column ht_projects.project_name IS
'The unique name of the project.'
/
COMMENT ON column ht_projects.start_date IS
'The start date of the project.'
/
COMMENT ON column ht_projects.target_end_date IS
'The targeted end date of the project.'
/
COMMENT ON column ht_projects.actual_end_date IS
'The actual end date of the project.'
/
--
-- HT_PEOPLE
--
-- The HT_PEOPLE DDL:
-- + creates the people table with the necessary columns,
-- including a new column for a system generated primary key
-- + declares the new primary key
-- + implements the real primary key, person name, as a unique key
-- + implements a check constraint to validate the roles that people
-- can be assigned
-- + implements a foreign key to validate that people are assigned to
-- valid projects
-- + implements a check constraint to enforce that all project leads
-- and team members are assigned to projects
-- + implements a sequence to generate person id
-- + assigns the sequence to populate the person id whenever a
-- new record is created
-- + declares table and column comments
--
CREATE TABLE ht_people (
person_id INTEGER NOT NULL,
person_name VARCHAR2(100) NOT NULL,
person_email VARCHAR2(100) NOT NULL,
person_role VARCHAR2(7) NOT NULL,
assigned_project INTEGER
)
/
ALTER TABLE ht_people
ADD CONSTRAINT ht_people_pk
PRIMARY KEY (person_id)
/
ALTER TABLE ht_people
ADD CONSTRAINT ht_people_uk
UNIQUE (person_name)
/
ALTER TABLE ht_people
ADD CONSTRAINT ht_people_role_cc
CHECK (person_role in ('CEO','Manager','Lead','Member'))
/
ALTER TABLE ht_people
ADD CONSTRAINT ht_people_project_fk
FOREIGN KEY (assigned_project)
REFERENCES ht_projects
/
ALTER TABLE ht_people
ADD CONSTRAINT ht_people_assignment_cc
CHECK ( (person_role in ('Lead','Member') and assigned_project is not null)
or (person_role in ('CEO','Manager') and assigned_project is null) )
/
CREATE SEQUENCE ht_people_seq
INCREMENT BY 1
START WITH 40
/
CREATE OR REPLACE TRIGGER bi_ht_people
BEFORE INSERT on ht_people
FOR EACH ROW
BEGIN
IF :new.person_id IS NULL
THEN SELECT ht_people_seq.nextval
INTO :new.person_id
FROM DUAL;
END IF;
END;
/
COMMENT ON table ht_people IS
'All people within the company.'
/
COMMENT ON column ht_people.person_id IS
'The system generated unique identifier for the person.'
/
COMMENT ON column ht_people.person_name IS
'The unique name of the person.'
/
COMMENT ON column ht_people.person_role IS
'The role the person plays within the company.'
/
COMMENT ON column ht_people.assigned_project IS
'The project that the person is currently assigned to.'
/
--
-- HT_ISSUES
--
-- The HT_ISSUES DDL:
-- + creates the table with the necessary columns, including a new column
-- for a system generated primary key
-- + declares the new primary key
-- + implements a foreign key to validate that the issue is identified by a
-- valid person
-- + implements a foreign key to validate that the issue is assigned to a
-- valid person
-- + implements a foreign key to validate that the issue is associated with
-- a valid project
-- + implements a check constraint to validate the status that is assigned
-- to the issue
-- + implements a check constraint to validate the priority that is assigned
-- to the issue
-- + implements a sequence to generate issue id
-- + assigns the sequence to populate the issue id and the creation date
-- whenever a new record is created, records the user creating the
-- row and also assigns status of 'Open' if no status is provided
-- + records the current date and the user whenever an issue is edited and
-- sets the status to 'Closed' if an ACTUAL_RESOLUTION_DATE is
-- provided
-- + declares table and column comments
--
create table ht_issues (
issue_id INTEGER not null,
issue_summary VARCHAR2(200) not null,
issue_description VARCHAR2(2000),
identified_by INTEGER NOT NULL,
identified_date DATE not null,
related_project INTEGER not null,
assigned_to INTEGER,
status VARCHAR2(8) not null,
priority VARCHAR2(6),
target_resolution_date DATE,
progress VARCHAR2(2000),
actual_resolution_date DATE,
resolution_summary VARCHAR2(2000),
created_date DATE not null,
created_by VARCHAR2(60) not null,
last_modified_date DATE,
last_modified_by VARCHAR2(60)
)
/
ALTER TABLE ht_issues
ADD CONSTRAINT ht_issues_pk
PRIMARY KEY (issue_id)
/
ALTER TABLE ht_issues
ADD CONSTRAINT ht_issues_identified_by_fk
FOREIGN KEY (identified_by)
REFERENCES ht_people
/
ALTER TABLE ht_issues
ADD CONSTRAINT ht_issues_assigned_to_fk
FOREIGN KEY (assigned_to)
REFERENCES ht_people
/
ALTER TABLE ht_issues
ADD CONSTRAINT ht_issues_project_fk
FOREIGN KEY (related_project)
REFERENCES ht_projects
/
ALTER TABLE ht_issues
ADD CONSTRAINT ht_issues_status_cc
CHECK (status in ('Open','On-Hold','Closed'))
/
ALTER TABLE ht_issues
ADD CONSTRAINT ht_issues_priority_cc
CHECK (priority in ('High','Medium','Low'))
/
CREATE SEQUENCE ht_issues_seq
INCREMENT BY 1
START WITH 40
/
CREATE OR REPLACE TRIGGER bi_ht_issues
BEFORE INSERT on ht_issues
FOR EACH ROW
BEGIN
IF :new.issue_id IS NULL
THEN SELECT ht_issues_seq.nextval
INTO :new.issue_id
FROM DUAL;
END IF;
IF :new.status IS NULL
THEN :new.status := 'Open';
END IF;
:new.created_date := sysdate;
:new.created_by := nvl(wwv_flow.g_user,user);
END;
/
CREATE OR REPLACE TRIGGER bu_ht_issues
BEFORE UPDATE ON ht_issues
FOR EACH ROW
BEGIN
IF :new.actual_resolution_date IS NOT NULL
THEN :new.status := 'Closed';
END IF;
:new.last_modified_date := sysdate;
:new.last_modified_by := nvl(wwv_flow.g_user,user);
END;
/
COMMENT ON table ht_issues IS
'All issues related to the projects being undertaken by the company.'
/
COMMENT ON column ht_issues.issue_id IS
'The system generated unique identifier for the issue.'
/
COMMENT ON column ht_issues.issue_summary IS
'A brief summary of the issue.'
/
COMMENT ON column ht_issues.issue_description IS
'A full description of the issue.'
/
COMMENT ON column ht_issues.identified_by IS
'The person who identified the issue.'
/
COMMENT ON column ht_issues.identified_date IS
'The date the issue was identified.'
/
COMMENT ON column ht_issues.related_project IS
'The project that the issue is related to.'
/
COMMENT ON column ht_issues.assigned_to IS
'The person that the issue is assigned to.'
/
COMMENT ON column ht_issues.status IS
'The current status of the issue.'
/
COMMENT ON column ht_issues.priority IS
'The priority of the issue. How important it is to get resolved.'
/
COMMENT ON column ht_issues.target_resolution_date IS
'The date on which the issue is planned to be resolved.'
/
COMMENT ON column ht_issues.actual_resolution_date IS
'The date the issue was actually resolved.'
/
COMMENT ON column ht_issues.progress IS
'Any progress notes on the issue resolution.'
/
COMMENT ON column ht_issues.resolution_summary IS
'The description of the resolution of the issue.'
/
COMMENT ON column ht_issues.created_date IS
'Audit Column: Date the record was created.'
/
COMMENT ON column ht_issues.created_by IS
'Audit Column: The user who created the record.'
/
COMMENT ON column ht_issues.last_modified_date IS
'Audit Column: Date the record was last modified.'
/
COMMENT ON column ht_issues.last_modified_by IS
'Audit Column: The user who last modified the record.'
/
The following script populates Issues table for the issue tracking application in "How to Build and Deploy an Issue Tracking Application" on page 14-1.
--
-- Email Integration Issues
--
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(1, 'Midwest call center servers have no failover due to Conn Creek plant fire','',
6, sysdate-80,
3, 6, 'Closed', 'Medium', sysdate-73,
'Making steady progress.', sysdate-73, '')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(2, 'Timezone ambiguity in some EMEA regions is delaying bulk forwarding to mirror sites','',
6, sysdate-100,
3, 14, 'Open', 'Low', sysdate-80,
'','','')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(3, 'Some vendor proposals lack selective archiving and region-keyed retrieval sections','',
6, sysdate-110,
3, 13, 'Closed', 'Medium', sysdate-90,
'', sysdate-95, '')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(4, 'Client software licenses expire for Bangalore call center before cutover','',
1, sysdate-70,
3, 6, 'Closed', 'High', sysdate-60,
'',sysdate-66,'Worked with HW, applied patch set.')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(5, 'Holiday coverage for DC1 and DC3 not allowed under union contract, per acting steward at branch 745','',
1, sysdate-100,
3, 13, 'Closed', 'High', sysdate-90,
'',sysdate-95, 'Worked with HW, applied patch set.')
/
--
-- Employee Satisfaction Survey Issues
--
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(6, 'Review rollout schedule with HR VPs/Directors','',
8, sysdate-30,
5, null, 'Closed', 'Medium', sysdate-15,
'',sysdate-20,'')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(7, 'Distribute translated categories and questions for non-English regions to regional team leads','',
8, sysdate-2,
5, 8, 'Open', 'Medium', sysdate+10,
'currently beta testing new look and feel','','')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(8, 'Provide survey FAQs to online newsletter group','',
1, sysdate-10,
5, 11, 'Open', 'Medium', sysdate+20,
'','','')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(9, 'Need better definition of terms like work group, department, and organization for categories F, H, and M-W','',
1, sysdate-8,
5, null, 'Open', 'Low', sysdate+15,
'','','')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(10, 'Legal has asked for better definitions on healthcare categories for Canadian provincial regs compliance','',
1, sysdate-10,
5, 11, 'Closed', 'Medium', sysdate+20,
'',sysdate-1,'')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(11, 'Action plan review dates conflict with effectivity of organizational consolidations for Great Lakes region','',
1, sysdate-9,
5, 11, 'Open', 'Medium', sysdate+45,
'','','')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(12, 'Survey administration consulting firm requires indemnification release letter from HR SVP','',
1, sysdate-30,
5, 11, 'Closed', 'Low', sysdate-15,
'', sysdate-17, '')
/
--
-- Internal Infrastructure Issues
--
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(13, 'Facilities, Safety health-check reports must be signed off before capital asset justification can be approved','',
4, sysdate-145,
1, 4, 'Closed', 'Medium', sysdate-100,
'',sysdate-110,'')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(14, 'Cooling and Power requirements exceed 90% headroom limit -- variance from Corporate requested','',
4, sysdate-45,
1, 9, 'Closed', 'High', sysdate-30,
'',sysdate-35,'')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(15, 'Local regulations prevent Federal contracts compliance on section 3567.106B','',
4, sysdate-90,
1, 10, 'Closed', 'High', sysdate-82,
'',sysdate-85,'')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(16, 'Emergency Response plan failed county inspector''s review at buildings 2 and 5','',
4, sysdate-35,
1, null, 'Open', 'High', sysdate-5,
'','','')
/
--
-- New Payroll Rollout Issues
--
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(17, 'Training for call center 1st and 2nd lines must be staggered across shifts','',
5, sysdate-8,
2, 5, 'Closed', 'Medium', sysdate+10,
'',sysdate-1,'')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(18, 'Semi-monthly ISIS feed exceeds bandwidth of Mississauga backup site','',
5, sysdate-100,
2, 12, 'On-Hold', 'Medium', sysdate-30,
'pending info from supplier','','')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(19, 'Expat exception reports must be hand-reconciled until auto-post phaseout complete','',
5, sysdate-17,
2, 12, 'Closed', 'High', sysdate+4,
'',sysdate-4,'')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(20, 'Multi-region batch trial run schedule and staffing plan due to directors by end of phase review','',
5, sysdate,
2, null, 'Open', 'High', sysdate+15,
'','','')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(21, 'Auditors'' signoff requires full CSB compliance report','',
5, sysdate-21,
2, 5, 'Open', 'High', sysdate-7,
'','','')
/
--
-- Public Website Operational Issues
--
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(22, 'Review security architecture plan with consultant','',
1, sysdate-60,
4, 7, 'Closed', 'High', sysdate-45,
'',sysdate-40,'')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(23, 'Evaluate vendor load balancing proposals against capital budget','',
7, sysdate-50,
4, 7, 'Closed', 'High', sysdate-45,
'',sysdate-43,'')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(24, 'Some preferred domain names are unavailable in registry','',
7, sysdate-55,
4, 15, 'Closed', 'Medium', sysdate-45,
'',sysdate-50,'')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(25, 'Establish grid management capacity-expansion policies with ASP','',
7, sysdate-20,
4, 16, 'Open', 'Medium', sysdate-5,
'','','')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(26, 'Access through proxy servers blocks some usage tracking tools','',
7, sysdate-10,
4, 15, 'Closed', 'High', sysdate-5,
'',sysdate-1,'')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(27, 'Phase I stress testing cannot use production network','',
7, sysdate-11,
4, 17, 'Open', 'High', sysdate,
'','','')
/
INSERT INTO ht_issues
(issue_id, issue_summary, issue_description,
identified_by, identified_date,
related_project, assigned_to, status, priority, target_resolution_date,
progress, actual_resolution_date, resolution_summary)
VALUES
(28, 'DoD clients must have secure port and must be blocked from others','',
7, sysdate-20,
4, 17, 'On-Hold', 'High', sysdate,
'Waiting on Security Consultant, this may drag on.','','')
/
