BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

EnterpriseDB: Create Traker Database Objects

Oracle Tips by Burleson Consulting
 

* chap9_edb_create_traker_db_objects.sql

CREATE ROLE tt_user_role;
CREATE ROLE tt_pm_role;
CREATE ROLE tt_admin_role;
 
CREATE SEQUENCE traker.tt_general_sequence;
 
CREATE TABLE TRAKER.TT_SCHEDULES
(
  SCHEDULE_ID    NUMBER(12)                     NOT NULL,
  SCHEDULE_TYPE  VARCHAR2(10),
  MONDAY         VARCHAR2(1),
  TUESDAY        VARCHAR2(1),
  WEDNESDAY      VARCHAR2(1),
  THURSDAY       VARCHAR2(1),
  FRIDAY         VARCHAR2(1),
  SATURDAY       VARCHAR2(1),
  SUNDAY         VARCHAR2(1),
  PRIMARY KEY
 (SCHEDULE_ID),
  CONSTRAINT TT_SCHEDULES_UK1
 UNIQUE (SCHEDULE_TYPE)
);
 
CREATE TABLE TRAKER.TT_XML_INTERFACE
(
  INTERFACE_ID   NUMBER(12)                     NOT NULL,
  DATE_RECEIVED  TIMESTAMP(6),
  XML_DATA       TEXT,
  PRIMARY KEY
 (INTERFACE_ID)
);
 
CREATE TABLE TRAKER.TT_TASKS
(
  TASK_ID      NUMBER(12)                       NOT NULL,
  TASK_NAME    VARCHAR2(100),
  TASK_DESC    VARCHAR2(4000),
  ACTIVE_FLAG  VARCHAR2(1),
  PRIMARY KEY
 (TASK_ID),
  CONSTRAINT TT_TASKS_UK1
 UNIQUE (TASK_NAME)
); 

CREATE TABLE TRAKER.TT_PROJECTS

(
  PROJECT_ID    NUMBER(12)                      NOT NULL,
  PROJECT_NAME  VARCHAR2(100),
  PROJECT_DESC  VARCHAR2(4000),
  ACTIVE_FLAG   VARCHAR2(1),


  PRIMARY KEY
 (PROJECT_ID),
  CONSTRAINT TT_PROJECTS_UK1
 UNIQUE (PROJECT_NAME)
);
 
CREATE TABLE TRAKER.TT_CALENDAR
(
  DATE_PK            DATE                       NOT NULL,
  QUARTER_IND        NUMBER(2),
  HOLIDAY_FLAG       VARCHAR2(1),
  BUSINESS_DAY_FLAG  VARCHAR2(1),
  PRIMARY KEY
 (DATE_PK)
);
 
CREATE TABLE TRAKER.TT_RESOURCES
(
  RESOURCE_ID       NUMBER(12)                  NOT NULL,
  SCHEDULE_ID       NUMBER(12)                  NOT NULL,
  RESOURCE_NAME     VARCHAR2(100),
  ALLOCATION_PCT    NUMBER(3),
  HOURS_PER_DAY     NUMBER(6,4),
  JOB               VARCHAR2(100),
  ASSIGNABLE_FLAG   VARCHAR2(1),
  XML_FEED_USER_ID  VARCHAR2(100),
  ACTIVE_FLAG       VARCHAR2(1),
  PRIMARY KEY
 (RESOURCE_ID),
  CONSTRAINT TT_RESOURCES_UK1
 UNIQUE (RESOURCE_NAME),


 UNIQUE (XML_FEED_USER_ID),
  FOREIGN KEY (SCHEDULE_ID)
 REFERENCES TRAKER.TT_SCHEDULES (SCHEDULE_ID)
);
 
CREATE TABLE TRAKER.TT_PROJECTS_TASKS
(
  PROJECT_ID         NUMBER(12)                 NOT NULL,
  TASK_ID            NUMBER(12)                 NOT NULL,
  PRIORITY           NUMBER(5),
  ESTIMATED_HOURS    NUMBER(12),
  PROJECT_TASK_NAME  VARCHAR2(100),
  ACTIVE_FLAG        VARCHAR2(1),
  PRIMARY KEY
 (PROJECT_ID, TASK_ID),
  FOREIGN KEY (TASK_ID)
 REFERENCES TRAKER.TT_TASKS (TASK_ID),
  FOREIGN KEY (PROJECT_ID)
 REFERENCES TRAKER.TT_PROJECTS (PROJECT_ID)
);
 
CREATE TABLE TRAKER.TT_TASKS_RESOURCES
(
  TASK_ID      NUMBER(12)                       NOT NULL,
  RESOURCE_ID  NUMBER(12)                       NOT NULL,
  ACTIVE_FLAG  VARCHAR2(1),
  PRIMARY KEY
 (TASK_ID, RESOURCE_ID),
  FOREIGN KEY (RESOURCE_ID)
 REFERENCES TRAKER.TT_RESOURCES (RESOURCE_ID),
  FOREIGN KEY (TASK_ID)
 REFERENCES TRAKER.TT_TASKS (TASK_ID)
);
 
CREATE TABLE TRAKER.TT_TASK_ITEM_UPDATE
(
  TASK_ID       NUMBER(12)                      NOT NULL,
  RESOURCE_ID   NUMBER(12)                      NOT NULL,
  ITEM_DATE     DATE                            NOT NULL,
  HOURS_WORKED  NUMBER(6,4),
  ACTIVE_FLAG   VARCHAR2(1),
  PRIMARY KEY
 (TASK_ID, RESOURCE_ID, ITEM_DATE),
  FOREIGN KEY (TASK_ID, RESOURCE_ID)
 REFERENCES TRAKER.TT_TASKS_RESOURCES (TASK_ID,RESOURCE_ID)
);
 
CREATE TABLE TRAKER.TT_TASK_DEPENDS
(
  PROJECT_ID         NUMBER(12)                 NOT NULL,
  TASK_ID            NUMBER(12)                 NOT NULL,
  DEPEND_PROJECT_ID  NUMBER(12)                 NOT NULL,
  DEPEND_TASK_ID     NUMBER(12)                 NOT NULL,

 PRIMARY KEY
 (PROJECT_ID, TASK_ID),
  FOREIGN KEY (DEPEND_PROJECT_ID, DEPEND_TASK_ID)
 REFERENCES TRAKER.TT_PROJECTS_TASKS (PROJECT_ID,TASK_ID),


 REFERENCES TRAKER.TT_PROJECTS_TASKS (PROJECT_ID,TASK_ID)
);
 
CREATE INDEX TT_CALENDAR_QTR_NDEX ON TRAKER.TT_CALENDAR
(QUARTER_IND);
 
CREATE INDEX TT_CALENDAR_HOL_NDX ON TRAKER.TT_CALENDAR
(HOLIDAY_FLAG);
 
CREATE INDEX TT_CALENDAR_BUS_NDX ON TRAKER.TT_CALENDAR
(BUSINESS_DAY_FLAG);
 
CREATE UNIQUE INDEX TT_PROJECTS_TASKS_NAME_NDX ON TRAKER.TT_PROJECTS_TASKS
(PROJECT_TASK_NAME, ACTIVE_FLAG);

CREATE INDEX TT_TASK_DEPENDS_ALL
_NDX ON TRAKER.TT_TASK_DEPENDS
(DEPEND_PROJECT_ID, DEPEND_TASK_ID, PROJECT_ID, TASK_ID);

CREATE INDEX TT_XML_INTERFACE_DT_NDX ON TRAKER.TT_XML_INTERFACE
(DATE_RECEIVED, INTERFACE_ID);
 
~production directive: code file:  chap9_edb_traker_views.sql

CREATE OR REPLACE VIEW traker.hours_worked_by_resources AS
 SELECT tt_projects_tasks.project_task_name,
        tt_projects_tasks.estimated_hours,
        tt_resources.resource_name,
        nvl(tt_task_item_update.hours_worked, (0)::numeric) AS hours_worked,
        tt_task_item_update.item_date
  FROM ((((traker.tt_tasks JOIN traker.tt_projects_tasks
           ON ((tt_tasks.task_id = tt_projects_tasks.task_id)))
  JOIN traker.tt_tasks_resources
           ON ((tt_tasks.task_id = tt_tasks_resources.task_id)))
  JOIN traker.tt_resources ON ((tt_resources.resource_id = tt_tasks_resources.resource_id)))
 
  LEFT JOIN traker.tt_task_item_update
           ON (((tt_tasks_resources.task_id = tt_task_item_update.task_id)
              AND (tt_tasks_resources.resource_id = tt_task_item_update.resource_id))));
 
 
CREATE OR REPLACE VIEW traker.proj_hours_remain_by_resource AS
 SELECT foo.project_task_name,
        foo.resource_name,
        foo.estimated_hours,
        foo.hours_worked_by_resource,
        1 AS rn
  FROM (
         SELECT tt_projects_tasks.project_task_name,
                tt_resources.resource_name,
                tt_projects_tasks.estimated_hours,
                nvl(sum(tt_task_item_update.hours_worked), (0)::numeric) AS
hours_worked_by_resource
           FROM ((((traker.tt_tasks
           LEFT JOIN traker.tt_projects_tasks
                ON ((tt_tasks.task_id = tt_projects_tasks.task_id)))
           LEFT JOIN traker.tt_tasks_resources
                ON ((tt_tasks.task_id = tt_tasks_resources.task_id)))
           LEFT JOIN traker.tt_resources
                ON ((tt_resources.resource_id = tt_tasks_resources.resource_id)))
           LEFT JOIN traker.tt_task_item_update
                ON (((tt_tasks_resources.task_id = tt_task_item_update.task_id)
                AND (tt_tasks_resources.resource_id = tt_task_item_update.resource_id))))
           GROUP BY tt_projects_tasks.project_task_name, tt_resources.resource_name,
tt_projects_tasks.estimated_hours) as foo;
 
CREATE OR REPLACE VIEW traker.proj_hours_remaining AS
 SELECT tt_projects_tasks.project_task_name, |
        nvl(tt_projects_tasks.estimated_hours, (0)::numeric) AS estimated_hours,
        nvl((tt_projects_tasks.estimated_hours -
            sum(tt_task_item_update.hours_worked)), (0)::numeric) AS
hours_remaining_in_project
  FROM ((((traker.tt_tasks
  JOIN traker.tt_projects_tasks
           ON ((tt_tasks.task_id = tt_projects_tasks.task_id)))
  JOIN traker.tt_tasks_resources
           ON ((tt_tasks.task_id = tt_tasks_resources.task_id)))
  JOIN traker.tt_resources
 
           ON ((tt_resources.resource_id = tt_tasks_resources.resource_id)))
  LEFT JOIN traker.tt_task_item_update
           ON (((tt_tasks_resources.task_id = tt_task_item_update.task_id)
              AND (tt_tasks_resources.resource_id = tt_task_item_update.resource_id))))
  GROUP BY tt_projects_tasks.project_task_name, tt_projects_tasks.estimated_hours;
 
CREATE OR REPLACE VIEW traker.project_resources AS
 SELECT tt_projects_tasks.project_task_name,
        tt_projects_tasks.estimated_hours,
        tt_tasks.task_name,
        tt_resources.resource_name,
        tt_resources.allocation_pct,
        tt_resources.hours_per_day,
        tt_resources.job
  FROM (((traker.tt_tasks
  JOIN traker.tt_projects_tasks
           ON ((tt_tasks.task_id = tt_projects_tasks.task_id)))
  JOIN traker.tt_tasks_resources
           ON ((tt_tasks.task_id = tt_tasks_resources.task_id)))
  JOIN traker.tt_resources
           ON ((tt_resources.resource_id = tt_tasks_resources.resource_id)));
 
CREATE OR REPLACE VIEW traker.project_tasks AS
 SELECT tt_projects.project_name,
        tt_projects.project_desc,
        tt_tasks.task_name,
        tt_tasks.task_desc,
        tt_projects_tasks.project_task_name,
        tt_projects_tasks.estimated_hours

  FROM ((traker.tt_projects
  JOIN traker.tt_projects_tasks
           ON ((tt_projects.project_id = tt_projects_tasks.project_id)))
  JOIN traker.tt_tasks
           ON ((tt_tasks.task_id = tt_projects_tasks.task_id)));
 




This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter