Remote DBA Plans
Remote DBA Service

EnterpriseDB: Oracle Traker Views

Oracle Tips by Burleson Consulting

CREATE OR REPLACE FORCE VIEW 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) HOURS_WORKED,
       tt_task_item_update.item_date
FROM tt_tasks JOIN tt_projects_tasks
       ON (tt_tasks.task_id = tt_projects_tasks.task_id)
       JOIN tt_tasks_resources
       ON (tt_tasks.task_id = tt_tasks_resources.task_id)
       JOIN tt_resources
       ON (tt_resources.resource_id = tt_tasks_resources.resource_id)
       LEFT OUTER JOIN 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 FORCE VIEW 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 tt_tasks JOIN tt_projects_tasks
       ON (tt_tasks.task_id = tt_projects_tasks.task_id)
       JOIN tt_tasks_resources
       ON (tt_tasks.task_id = tt_tasks_resources.task_id)
       JOIN tt_resources
       ON (tt_resources.resource_id = tt_tasks_resources.resource_id);

CREATE OR REPLACE FORCE VIEW 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 tt_projects JOIN tt_projects_tasks
       ON (tt_projects.project_id = tt_projects_tasks.project_id)
       JOIN tt_tasks ON (tt_tasks.task_id = tt_projects_tasks.task_id);

CREATE OR REPLACE FORCE VIEW PROJ_HOURS_REMAINING
AS
SELECT tt_projects_tasks.project_task_name,
       nvl(tt_projects_tasks.estimated_hours,0) ESTIMATED_HOURS,
       nvl(tt_projects_tasks.estimated_hours -
       sum(tt_task_item_update.hours_worked),0)
            hours_remaining_in_Project
FROM tt_tasks JOIN tt_projects_tasks
       ON (tt_tasks.task_id = tt_projects_tasks.task_id)
       JOIN tt_tasks_resources
       ON (tt_tasks.task_id = tt_tasks_resources.task_id)
       JOIN tt_resources
       ON (tt_resources.resource_id = tt_tasks_resources.resource_id)
       LEFT OUTER JOIN 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 FORCE VIEW PROJ_HOURS_REMAIN_BY_RESOURCE
AS
SELECT PROJECT_TASK_NAME, RESOURCE_NAME, ESTIMATED_HOURS,
       HOURS_WORKED_BY_RESOURCE, HOURS_REMAINING_IN_PROJECT, 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) over (partition by
tt_projects_tasks.project_task_name, tt_resources.resource_name),0)
             hours_worked_by_resource,
       tt_projects_tasks.estimated_hours -
       nvl(sum(tt_task_item_update.hours_worked) over (partition by
tt_projects_tasks.project_task_name), 0)
            hours_remaining_in_Project,
       row_number() over(partition by tt_projects_tasks.project_task_name,
tt_resources.resource_name
order by tt_projects_tasks.project_task_name, tt_resources.resource_name) rn
FROM tt_tasks JOIN tt_projects_tasks
       ON (tt_tasks.task_id = tt_projects_tasks.task_id)
       JOIN tt_tasks_resources
       ON (tt_tasks.task_id = tt_tasks_resources.task_id)
       JOIN tt_resources
     ON (tt_resources.resource_id = tt_tasks_resources.resource_id)
       LEFT OUTER JOIN 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)) )
WHERE rn = 1;

* chap9_oracle_traker_packages.sql

CREATE OR REPLACE PACKAGE tt_calendar_admin
AS
PROCEDURE schedule(
     p_schedule_type IN tt_schedules.schedule_type%TYPE,
     p_sunday IN tt_schedules.sunday%TYPE DEFAULT NULL,
     p_monday IN tt_schedules.monday%TYPE DEFAULT NULL,
     p_tuesday IN tt_schedules.tuesday%TYPE DEFAULT NULL,
     p_wednesday IN tt_schedules.wednesday%TYPE DEFAULT NULL,
     p_thursday IN tt_schedules.thursday%TYPE DEFAULT NULL,
     p_friday IN tt_schedules.friday%TYPE DEFAULT NULL,
     p_saturday IN tt_schedules.saturday%TYPE DEFAULT NULL );

FUNCTION get_schedule_id(
     p_schedule_type IN tt_schedules.schedule_type%TYPE )
     RETURN tt_schedules.schedule_id%TYPE;

FUNCTION get_schedule_type(
     p_schedule_id IN tt_schedules.schedule_id%TYPE )
     RETURN tt_schedules.schedule_type%TYPE;

PROCEDURE populate_calendar(
p_from_date IN DATE DEFAULT sysdate,
p_to_date IN DATE DEFAULT add_months(sysdate, 12));

PROCEDURE change_holiday_flag(
p_date IN DATE );

PROCEDURE change_buisness_day_flag(
p_date IN DATE );

END;
/

CREATE OR REPLACE PACKAGE BODY tt_calendar_admin
AS

PROCEDURE populate_calendar(
p_from_date IN DATE DEFAULT sysdate,
p_to_date IN DATE DEFAULT add_months(sysdate, 12))

IS
    v_date DATE;
    v_quarter PLS_INTEGER;
    v_business_day CHAR;
BEGIN

v_date := trunc(p_from_date) - 1;

FOR i IN 1..trunc(p_to_date) - trunc(p_from_date)
LOOP

v_date := v_date + 1;
v_quarter := to_char(v_date, 'Q');

      IF TO_CHAR(v_date, 'D') IN ('1', '7')
      THEN
        v_business_day := 'N';
      ELSE
        v_business_day := 'Y';
      END IF;

      INSERT INTO tt_calendar (
          date_pk, quarter_ind, holiday_flag,
          business_day_flag)
        VALUES ( v_date,
                 v_quarter,
                 'N',
                 v_business_day );
    END LOOP;

COMMIT;
END;

PROCEDURE change_holiday_flag(
    p_date IN DATE )
AS
BEGIN
    UPDATE tt_calendar
      SET holiday_flag = decode( holiday_flag, 'Y', 'N', 'Y' )
      WHERE date_pk = trunc(p_date);

COMMIT;
END;

PROCEDURE change_buisness_day_flag(
    p_date IN DATE )
AS
BEGIN
    UPDATE tt_calendar
      SET holiday_flag = decode( business_day_flag, 'Y', 'N', 'Y' )
      WHERE date_pk = trunc(p_date);

COMMIT;
END;

PROCEDURE schedule(
    p_schedule_type IN tt_schedules.schedule_type%TYPE,
     p_sunday IN tt_schedules.sunday%TYPE DEFAULT NULL,
     p_monday IN tt_schedules.monday%TYPE DEFAULT NULL,
     p_tuesday IN tt_schedules.tuesday%TYPE DEFAULT NULL,
     p_wednesday IN tt_schedules.wednesday%TYPE DEFAULT NULL,
     p_thursday IN tt_schedules.thursday%TYPE DEFAULT NULL,
     p_friday IN tt_schedules.friday%TYPE DEFAULT NULL,
     p_saturday IN tt_schedules.saturday%TYPE DEFAULT NULL )
AS
BEGIN
    UPDATE tt_schedules
      SET sunday = nvl(p_sunday, sunday),
          monday = nvl(p_monday, monday),
          tuesday = nvl(p_tuesday, tuesday),
          wednesday = nvl(p_wednesday,wednesday),
          thursday = nvl(p_thursday,thursday),
          friday = nvl(p_friday,friday),
        saturday = nvl(p_saturday,saturday)
       WHERE schedule_type = p_schedule_type;

    IF SQL%NOTFOUND
    THEN
      INSERT INTO tt_schedules (
        schedule_id, schedule_type, sunday,
        monday, tuesday, wednesday, thursday,
        friday, saturday)
        VALUES ( tt_general_sequence.nextval,
                 p_schedule_type,
                 nvl(p_sunday, 'Y'),
                 nvl(p_monday, 'Y'),
                 nvl(p_tuesday, 'Y'),
                 nvl(p_wednesday,'Y'),
                 nvl(p_thurSday,'Y'),
                 nvl(p_friday,'Y'),
                 nvl(p_saturday,'Y'));
    END IF;

COMMIT;
END;

FUNCTION get_schedule_id(
     p_schedule_type IN tt_schedules.schedule_type%TYPE )
     RETURN tt_schedules.schedule_id%TYPE
AS
    v_schedule_id tt_schedules.schedule_id%TYPE;
BEGIN
    SELECT schedule_id
      INTO v_schedule_id
      FROM tt_schedules
      WHERE schedule_type = p_schedule_type;

    RETURN v_schedule_id;
EXCEPTION
    WHEN no_data_found
    THEN
      RETURN -1;
END;

FUNCTION get_schedule_type(
     p_schedule_id IN tt_schedules.schedule_id%TYPE )
     RETURN tt_schedules.schedule_type%TYPE
AS
    v_schedule_type tt_schedules.schedule_type%TYPE;
BEGIN
    SELECT schedule_type
      INTO v_schedule_type
      FROM tt_schedules
      WHERE schedule_id = p_schedule_id;

    RETURN v_schedule_type;
EXCEPTION
    WHEN no_data_found
    THEN
      RETURN NULL;
END;

END;
/

CREATE OR REPLACE PACKAGE tt_manage_projects

PROCEDURE create_project(
     p_project_name IN tt_projects.project_name%TYPE,
     p_project_desc IN tt_projects.project_desc%TYPE,
     p_active_flag IN tt_projects.active_flag%TYPE DEFAULT 'Y' );

PROCEDURE create_tasks(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_task_desc IN tt_tasks.task_desc%TYPE,
     p_active_flag IN tt_tasks.active_flag%TYPE DEFAULT 'Y' );

PROCEDURE assign_task_to_project(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_project_name IN tt_projects.project_name%TYPE,
     p_priority IN tt_projects_tasks.priority%TYPE,
     p_estimated_hours IN tt_projects_tasks.estimated_hours%TYPE,
     p_project_task_name IN tt_projects_tasks.project_task_name%TYPE );

PROCEDURE assign_resource_to_task(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_resource_name IN tt_resources.resource_name%TYPE );

PROCEDURE create_item_dependancies(
     p_parent_project_name IN tt_projects.project_name%TYPE,
     p_parent_task_name IN tt_tasks.task_name%TYPE,
     p_dependant_task_name IN tt_tasks.task_name%TYPE,
     p_dependant_project_name IN tt_projects.project_name%TYPE );

FUNCTION get_task_id(
p_task_name IN tt_tasks.task_name%TYPE )
RETURN tt_tasks.task_id%TYPE;

FUNCTION get_project_id(
p_project_name IN tt_projects.project_name%TYPE )
RETURN tt_projects.project_id%TYPE;

PROCEDURE deactivate_task_from_project(
p_task_name IN tt_tasks.task_name%TYPE,
p_project_name IN tt_projects.project_name%TYPE );

PROCEDURE reactivate_task_to_project(
p_task_name IN tt_tasks.task_name%TYPE,
p_project_name IN tt_projects.project_name%TYPE );
END;
/

CREATE OR REPLACE PACKAGE BODY tt_manage_projects

AS

PROCEDURE create_project(
     p_project_name IN tt_projects.project_name%TYPE,
     p_project_desc IN tt_projects.project_desc%TYPE,
     p_active_flag IN tt_projects.active_flag%TYPE DEFAULT 'Y' )
AS
BEGIN
    INSERT INTO tt_projects (
       project_id, project_name, project_desc,
       active_flag)
    VALUES ( tt_general_sequence.nextval, p_Project_name, p_project_desc, p_active_flag);
END;

PROCEDURE create_tasks(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_task_desc IN tt_tasks.task_desc%TYPE,
     p_active_flag IN tt_tasks.active_flag%TYPE DEFAULT 'Y' )
AS
BEGIN
    INSERT INTO tt_tasks (
       task_id, task_name, task_desc,
       active_flag)
    VALUES ( tt_general_sequence.nextval, p_task_name, p_task_desc, p_active_flag);
END;

PROCEDURE assign_task_to_project(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_project_name IN tt_projects.project_name%TYPE,
     p_priority IN tt_projects_tasks.priority%TYPE,
     p_estimated_hours IN tt_projects_tasks.estimated_hours%TYPE,
     p_project_task_name IN tt_projects_tasks.project_task_name%TYPE )
AS
BEGIN
    INSERT INTO tt_projects_tasks (
         project_id, task_id, priority,
         estimated_hours, project_task_name, active_flag)
      VALUES ( tt_manage_projects.get_project_id(p_project_name),
               tt_manage_projects.get_task_id(p_task_name),
               p_priority,
               p_estimated_hours,
               p_project_task_name,
               'Y');
   EXCEPTION
     WHEN dup_val_on_index
     THEN
       reactivate_task_to_project( p_project_name, p_task_name );
END;

PROCEDURE deactivate_task_from_project(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_project_name IN tt_projects.project_name%TYPE )
AS
BEGIN
    UPDATE tt_projects_tasks
      SET active_flag = 'N'
      WHERE project_id = tt_manage_projects.get_project_id(p_project_name)
        AND task_id = tt_manage_projects.get_task_id(p_task_name);
END;

PROCEDURE reactivate_task_to_project(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_project_name IN tt_projects.project_name%TYPE )
AS
BEGIN
    UPDATE tt_projects_tasks
      SET active_flag = 'Y'
      WHERE project_id = tt_manage_projects.get_project_id(p_project_name)
        AND task_id = tt_manage_projects.get_task_id(p_task_name);
END;

PROCEDURE assign_resource_to_task(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_resource_name IN tt_resources.resource_name%TYPE )
AS
BEGIN
    INSERT INTO tt_tasks_resources (
          task_id, resource_id, active_flag)
       VALUES ( tt_manage_projects.get_task_id(p_task_name),
                tt_manage_resources.get_resource_id(p_resource_name),
                'Y' );
END;

PROCEDURE deactivate_resource_from_task(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_resource_name IN tt_resources.resource_name%TYPE )
AS
BEGIN
    UPDATE tt_tasks_resources
      SET active_flag = 'N'
      WHERE task_id = tt_manage_projects.get_task_id(p_task_name)
        AND resource_id = tt_manage_resources.get_resource_id(p_resource_name);
END;

PROCEDURE reactivate_resource_from_task(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_resource_name IN tt_resources.resource_name%TYPE )
AS
BEGIN
    UPDATE tt_tasks_resources
      SET active_flag = 'Y'
      WHERE task_id = tt_manage_projects.get_task_id(p_task_name)
        AND resource_id = tt_manage_resources.get_resource_id(p_resource_name);
END;

PROCEDURE create_item_dependancies(
     p_parent_project_name IN tt_projects.project_name%TYPE,
     p_parent_task_name IN tt_tasks.task_name%TYPE,
     p_dependant_task_name IN tt_tasks.task_name%TYPE,
     p_dependant_project_name IN tt_projects.project_name%TYPE )
AS
BEGIN
    INSERT INTO tt_task_depends (
        project_id, task_id, depend_project_id,
        depend_task_id)
      VALUES ( tt_manage_projects.get_project_id(p_parent_project_name),
               tt_manage_projects.get_task_id(p_parent_task_name),
               tt_manage_projects.get_project_id(p_dependant_project_name),
               tt_manage_projects.get_task_id(p_dependant_task_name) );
END;

PROCEDURE remove_item_dependancies(
     p_parent_project_name IN tt_projects.project_name%TYPE,
     p_parent_task_name IN tt_tasks.task_name%TYPE,
     p_dependant_task_name IN tt_tasks.task_name%TYPE,
     p_dependant_project_name IN tt_projects.project_name%TYPE )
AS
BEGIN

    DELETE FROM tt_task_depends
      WHERE project_id = tt_manage_projects.get_project_id(p_parent_project_name)
        AND task_id = tt_manage_projects.get_task_id(p_parent_task_name)
        AND depend_project_id =
t_manage_projects.get_project_id(p_dependant_project_name)
        AND depend_task_id = tt_manage_projects.get_task_id(p_dependant_task_name);
END;

FUNCTION get_task_id(
     p_task_name IN tt_tasks.task_name%TYPE )
     RETURN tt_tasks.task_id%TYPE
AS
    v_task_id tt_tasks.task_id%TYPE;
BEGIN
    SELECT task_id
      INTO v_task_id
      FROM tt_tasks
      WHERE task_name = p_task_name;

    RETURN v_task_id;

EXCEPTION
    WHEN no_data_found
    THEN
      RETURN -1;
END;

FUNCTION get_project_id(
     p_project_name IN tt_projects.project_name%TYPE )
     RETURN tt_projects.project_id%TYPE
AS
    v_project_id tt_projects.project_id%TYPE;
BEGIN
    SELECT project_id
      INTO v_project_id
      FROM tt_projects
      WHERE project_name = p_project_name;

    RETURN v_project_id;

EXCEPTION
    WHEN no_data_found
    THEN
      RETURN -1;
END;

END;
/
CREATE OR REPLACE PACKAGE tt_manage_resources
AS

PROCEDURE add_resource(
     p_resource_name IN tt_resources.resource_name%TYPE,
     p_schedule_type IN tt_Schedules.schedule_type%TYPE,
     p_allocation_pct IN tt_resources.allocation_pct%TYPE,
     p_hours_per_day IN tt_resources.hours_per_day%TYPE,
     p_job IN tt_resources.job%TYPE,
     p_assignable_flag IN tt_resources.assignable_flag%TYPE,
     p_xml_feed_user_id IN tt_resources.xml_feed_user_id%TYPE,
     p_active_flag IN tt_resources.active_flag%TYPE );

PROCEDURE alter_resource(
     p_resource_name IN tt_resources.resource_name%TYPE,
     p_schedule_type IN tt_Schedules.schedule_type%TYPE,
     p_allocation_pct IN tt_resources.allocation_pct%TYPE,
     p_hours_per_day IN tt_resources.hours_per_day%TYPE,
     p_job IN tt_resources.job%TYPE,
     p_assignable_flag IN tt_resources.assignable_flag%TYPE,
     p_xml_feed_user_id IN tt_resources.xml_feed_user_id%TYPE,
     p_active_flag IN tt_resources.active_flag%TYPE );

PROCEDURE delete_resource(
     p_resource_name IN tt_resources.resource_name%TYPE );

FUNCTION get_resource_id(
     p_resource_name IN tt_resources.resource_name%TYPE )
     RETURN tt_resources.resource_id%TYPE;

END;
/

CREATE OR REPLACE PACKAGE BODY tt_manage_resources
AS

PROCEDURE add_resource(
     p_resource_name IN tt_resources.resource_name%TYPE,
     p_schedule_type IN tt_Schedules.schedule_type%TYPE,
     p_allocation_pct IN tt_resources.allocation_pct%TYPE,
     p_hours_per_day IN tt_resources.hours_per_day%TYPE,

     p_job IN tt_resources.job%TYPE,
     p_assignable_flag IN tt_resources.assignable_flag%TYPE,
     p_xml_feed_user_id IN tt_resources.xml_feed_user_id%TYPE,
     p_active_flag IN tt_resources.active_flag%TYPE )
   AS
     v_schedule_id tt_resources.schedule_id%TYPE;
   BEGIN
     v_schedule_id := tt_calendar_admin.get_schedule_id(p_schedule_type);

      INSERT INTO tt_resources (
        resource_id, schedule_id, resource_name,
        allocation_pct, hours_per_day, job,
        assignable_flag, xml_feed_user_id, active_flag)
      VALUES ( tt_general_sequence.nextval, v_schedule_id, p_resource_name,
          p_allocation_pct, p_hours_per_day, p_job,
          p_assignable_flag, p_xml_feed_user_id, p_active_flag);
   END;

PROCEDURE alter_resource(
     p_resource_name IN tt_resources.resource_name%TYPE,
     p_schedule_type IN tt_Schedules.schedule_type%TYPE,
     p_allocation_pct IN tt_resources.allocation_pct%TYPE,
     p_hours_per_day IN tt_resources.hours_per_day%TYPE,
     p_job IN tt_resources.job%TYPE,
     p_assignable_flag IN tt_resources.assignable_flag%TYPE,
     p_xml_feed_user_id IN tt_resources.xml_feed_user_id%TYPE,
     p_active_flag IN tt_resources.active_flag%TYPE )
   AS
     v_schedule_id tt_resources.schedule_id%TYPE;
   BEGIN
     v_schedule_id := tt_calendar_admin.get_schedule_id(p_schedule_type);

     UPDATE traker.tt_resources
        SET    schedule_id      = v_schedule_id,
               allocation_pct   = p_allocation_pct,
               hours_per_day    = p_hours_per_day,
               job              = p_job,
               assignable_flag = p_assignable_flag,
               xml_feed_user_id = p_xml_feed_user_id,
               active_flag      = p_active_flag
        WHERE resource_name      = p_resource_name
;
   END;

PROCEDURE delete_resource(
     p_resource_name IN tt_resources.resource_name%TYPE )
   AS
   BEGIN
     UPDATE tt_resources
       SET active_flag = 'N'
       WHERE resource_name = p_resource_name;
   END;

FUNCTION get_resource_id(
     p_resource_name IN tt_resources.resource_name%TYPE )
     RETURN tt_resources.resource_id%TYPE
AS
    v_resource_id tt_resources.resource_id%TYPE;
BEGIN
    SELECT resource_id
      INTO v_resource_id
      FROM tt_resources
      WHERE resource_Name = p_resource_name;

    RETURN v_resource_id;

EXCEPTION
  WHEN no_data_found
    THEN
      RETURN -1;
END;


END;
/
CREATE OR REPLACE PACKAGE tt_manage_user_records
AS

   PROCEDURE add_new_item_update(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_resource_name IN tt_resources.resource_name%TYPE,
     p_hours_worked IN tt_task_item_update.hours_worked%TYPE,
     p_item_date IN tt_task_item_update.item_date%TYPE DEFAULT TRUNC(sysdate),
     p_active_flag IN tt_task_item_update.active_flag%TYPE DEFAULT 'Y' );

PROCEDURE add_xml_record(
     p_xml_data IN tt_xml_interface.xml_data%TYPE );

PROCEDURE process_xml_data;

END;
/

CREATE OR REPLACE PACKAGE BODY tt_manage_user_records
AS

   PROCEDURE add_new_item_update(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_resource_name IN tt_resources.resource_name%TYPE,
     p_hours_worked IN tt_task_item_update.hours_worked%TYPE,
     p_item_date IN tt_task_item_update.item_date%TYPE DEFAULT TRUNC(sysdate),
     p_active_flag IN tt_task_item_update.active_flag%TYPE DEFAULT 'Y' )   AS
   BEGIN
     INSERT INTO tt_task_item_update (
            task_id, resource_id, item_date,
            hours_worked, active_flag)
       VALUES ( tt_manage_projects.get_task_id(p_task_name),
                tt_manage_resources.get_resource_id(p_resource_name),
                trunc(p_item_date),
                p_hours_worked,
                p_active_flag);
   EXCEPTION
     WHEN dup_val_on_index
     THEN
       UPDATE tt_task_item_update
         SET hours_worked = p_hours_worked,
             active_flag = p_active_flag
         WHERE trunc(item_date) = trunc(p_item_date)
           AND task_id = tt_manage_projects.get_task_id(p_task_name)
           AND resource_id = tt_manage_resources.get_resource_id(p_resource_name);

   END;

PROCEDURE add_xml_record(
    p_xml_data IN tt_xml_interface.xml_data%TYPE )
AS
BEGIN
    INSERT INTO tt_xml_interface
      (interface_id, date_received, xml_data)
      VALUES (tt_general_sequence.nextval, sysdate, p_xml_data);
END;

PROCEDURE process_xml_data
AS
BEGIN
    FOR ci IN (
      SELECT xml_data, interface_id
        FROM tt_xml_interface )
    LOOP
      add_new_item_update(
        ci.xml_data.Extract('/interface/what/text()').getStringVal(),
        ci.xml_data.Extract('/interface/who/text()').getStringVal(),
        ci.xml_data.Extract('/interface/hours/text()').getStringVal(),
        ci.xml_data.Extract('/interface/when/text()').getStringVal(),
        'Y');

        DELETE FROM tt_xml_interface
          WHERE interface_id = ci.interface_id;

    END LOOP;

END;

END;
/
CREATE OR REPLACE PACKAGE tt_report_output AS

PROCEDURE HOURS_WORKED_BY_RESOURCES;

PROCEDURE PROJECT_RESOURCES;

PROCEDURE PROJECT_TASKS;

PROCEDURE PROJ_HOURS_REMAINING;

PROCEDURE PROJ_HOURS_REMAIN_BY_RESOURCE;

END;
/

CREATE OR REPLACE PACKAGE BODY tt_report_output AS

TYPE local_file_type IS RECORD (
    txt_file UTL_FILE.FILE_TYPE,
    csv_file UTL_FILE.FILE_TYPE );

g_files local_file_type;

TYPE data_for_csv IS RECORD (
    data VARCHAR2(100),
    data_type VARCHAR2(10) );

TYPE a_data IS TABLE OF data_for_csv
    INDEX BY BINARY_INTEGER;

PROCEDURE open_files(
    p_report_name IN VARCHAR2 );

PROCEDURE close_files;

PROCEDURE gen_standard_header(
     p_title IN VARCHAR2,
     p_page IN NUMBER );

FUNCTION center(
     p_string IN VARCHAR2,
     p_length IN NUMBER )
     RETURN VARCHAR2;

FUNCTION csv_it( p_data IN a_data )
     RETURN VARCHAR2;

PROCEDURE write_record(
     p_txt_line IN VARCHAR2,
     p_csv_line IN VARCHAR2 );

PROCEDURE HOURS_WORKED_BY_RESOURCES
IS
    v_data a_data;
    v_line_cntr NUMBER := 0;
    v_page_cntr NUMBER := 1;
    v_txt_line VARCHAR2(100);
    v_csv_line VARCHAR2(100);

    v_report_name VARCHAR2(80) := 'Hours Worked By Resource';
BEGIN
    open_files('hwbr');

    gen_standard_header(v_report_name, v_page_cntr );

    v_txt_line   := center('Project/Task',30) ||
                    center('Hours', 7) ||
                    center('Resource', 30) ||
                    center('Work', 7) ||
                    center('Item Date', 10) ;

    write_record( v_txt_line, null );

    FOR ci IN (
      SELECT SUBSTR(project_task_name,1, 30) project_task_name,
             to_char(estimated_hours) estimated_hours,
             SUBSTR(resource_name,1,30) resource_name,
             to_char(hours_worked) hours_worked,
             to_char(item_date, 'MM/DD/YYYY') item_date
         FROM hours_worked_by_resources)
    LOOP
      v_line_cntr := v_line_cntr + 1;
      v_data(1).data := ci.project_task_name;
      v_data(1).data_type := 'CHAR';
      v_data(2).data := ci.estimated_hours;
      v_data(2).data_type := 'NUMBER';
      v_data(3).data := ci.resource_name;
      v_data(3).data_type := 'CHAR';

      v_data(4).data := ci.hours_worked;
      v_data(4).data_type := 'NUMBER';
      v_data(5).data := ci.item_date;
      v_data(5).data_type := 'DATE';

v_csv_line := csv_it(v_data);

      v_txt_line := rpad(ci.project_task_name,30, ' ') ||
                    lpad(ci.estimated_hours, 5, ' ') || ' ' ||
                    rpad(ci.resource_name, 30, ' ') ||
                    lpad(ci.hours_worked, 5, ' ') || ' ' ||
                    ci.item_date ;

write_record( v_txt_line, v_csv_line );

       IF v_line_cntr >= 56
       THEN
         v_line_cntr := 0;
         v_page_cntr := v_page_cntr + 1;
         gen_standard_header(v_report_name, v_page_cntr );
       END IF;

     END LOOP;

    close_files;
END;

PROCEDURE PROJECT_RESOURCES
IS
    v_data a_data;
    v_line_cntr NUMBER := 0;
    v_page_cntr NUMBER := 1;
    v_txt_line VARCHAR2(150);
    v_csv_line VARCHAR2(150);

    v_report_name VARCHAR2(80) := 'Projects and Resources';
BEGIN
    open_files('par');

    gen_standard_header(v_report_name, v_page_cntr );
        v_txt_line   := center('Project/Task',30) ||
                    center('Est.', 7) ||
                    center('Task',30) ||
                    center('Resource', 30) ||
                    center('Alloc', 7) ||
                    center('Work', 7) ||
                    center('Job',30);
    write_record( v_txt_line, null );

    v_txt_line   := center(' ',30) ||
                    center('Hours', 7) ||
                    center(' ',30) ||
                    center(' ', 30) ||
                    center('PCT', 7) ||
                    center('Day', 7) ||
                    center(' ',30);

write_record( v_txt_line, null );

    FOR ci IN (
      SELECT SUBSTR(project_task_name,1, 30) project_task_name,
             to_char(estimated_hours) estimated_hours,

             SUBSTR(task_name,1, 30) task_name,
             SUBSTR(resource_name,1,30) resource_name,
             to_char(allocation_pct) allocation_pct,
             to_char(hours_per_day) hours_per_day,
             SUBSTR(job,1,30) job
         FROM project_resources)
    LOOP
      v_line_cntr := v_line_cntr + 1;

      v_data(1).data := ci.project_task_name;
      v_data(1).data_type := 'CHAR';
      v_data(2).data := ci.estimated_hours;
      v_data(2).data_type := 'NUMBER';

      v_data(3).data := ci.task_name;
      v_data(3).data_type := 'CHAR';
      v_data(4).data := ci.resource_name;
      v_data(4).data_type := 'CHAR';
      v_data(5).data := ci.allocation_pct;
      v_data(5).data_type := 'NUMBER';
      v_data(6).data := ci.hours_per_day;
      v_data(6).data_type := 'NUMBER';
      v_data(7).data := ci.job;
      v_data(7).data_type := 'CHAR';

      v_csv_line := csv_it(v_data);

      v_txt_line := rpad(ci.project_task_name,30, ' ') ||
                    lpad(ci.estimated_hours, 5, ' ') || ' ' ||
                    rpad(ci.task_name,30, ' ') ||
                    rpad(ci.resource_name, 30, ' ') ||

    lpad(ci.allocation_pct, 5, ' ') || ' ' ||
                    lpad(ci.hours_per_day, 5, ' ') || ' ' ||
                    rpad(ci.job,30, ' ');

       write_record( v_txt_line, v_csv_line );

       IF v_line_cntr >= 56
       THEN
         v_line_cntr := 0;
         v_page_cntr := v_page_cntr + 1;
         gen_standard_header(v_report_name, v_page_cntr );
       END IF;

     END LOOP;

    close_files;
END;

PROCEDURE PROJECT_TASKS
IS
    v_data a_data;
    v_line_cntr NUMBER := 0;
    v_page_cntr NUMBER := 1;
    v_txt_line VARCHAR2(180);
    v_csv_line VARCHAR2(180);

    v_report_name VARCHAR2(80) := 'Projects and Tasks';
BEGIN
    open_files('pat');

    gen_standard_header(v_report_name, v_page_cntr );

    v_txt_line   := center('Project Name',30) ||
                    center('Project Desc',30) ||
                    center('Task Name',30) ||
                    center('Task Desc',30) ||
                    center('Project/Task',30) ||
                    center('Est. Hours', 10);

    write_record( v_txt_line, null );

    FOR ci IN (
      SELECT SUBSTR(project_name,1, 30) project_name,
             SUBSTR(project_desc,1, 30) project_desc,
             SUBSTR(task_name,1, 30) task_name,
             SUBSTR(task_desc,1, 30) task_desc,
             SUBSTR(project_task_name,1, 30) project_task_name,
             to_char(estimated_hours) estimated_hours
         FROM project_tasks
         ORDER BY project_name, task_name)
    LOOP
      v_line_cntr := v_line_cntr + 1;

      v_data(1).data := ci.project_name;
      v_data(1).data_type := 'CHAR';
      v_data(2).data := ci.project_desc;
      v_data(2).data_type := 'CHAR';
      v_data(3).data := ci.task_name;
      v_data(3).data_type := 'CHAR';
      v_data(4).data := ci.task_desc;
      v_data(4).data_type := 'CHAR';
      v_data(5).data := ci.project_task_name;
      v_data(5).data_type := 'CHAR';
      v_data(6).data := ci.estimated_hours;
      v_data(6).data_type := 'NUMBER';

      v_csv_line := csv_it(v_data);

      v_txt_line := rpad(ci.project_name,30, ' ') ||
                    rpad(ci.project_desc,30, ' ') ||
                    rpad(ci.task_name,30, ' ') ||
                    rpad(ci.task_desc,30, ' ') ||
                    rpad(ci.project_task_name,30, ' ') ||
                    lpad(ci.estimated_hours, 5, ' ');

       write_record( v_txt_line, v_csv_line );

       IF v_line_cntr >= 56
       THEN
         v_line_cntr := 0;
         v_page_cntr := v_page_cntr + 1;
         gen_standard_header(v_report_name, v_page_cntr );
       END IF;

     END LOOP;

    close_files;
END;

PROCEDURE PROJ_HOURS_REMAINING
IS
    v_data a_data;
    v_line_cntr NUMBER := 0;
    v_page_cntr NUMBER := 1;
    v_txt_line VARCHAR2(100);
    v_csv_line VARCHAR2(100);

    v_report_name VARCHAR2(80) := 'Hours Remaining In Project';
BEGIN
    open_files('hrip');
        gen_standard_header(v_report_name, v_page_cntr );

    v_txt_line   := center('Project/Task',30) ||
                    center('Hours', 10) ||
                    center('Hours Remaining', 15);

write_record( v_txt_line, null );

    FOR ci IN (
      SELECT SUBSTR(project_task_name,1, 30) project_task_name,
             to_char(estimated_hours) estimated_hours,
             to_char(hours_remaining_in_project) hours_remaining_in_project
         FROM proj_hours_remaining)
    LOOP
      v_line_cntr := v_line_cntr + 1;
      v_data(1).data := ci.project_task_name;
      v_data(1).data_type := 'CHAR';
      v_data(2).data := ci.estimated_hours;
      v_data(2).data_type := 'NUMBER';
      v_data(3).data := ci.hours_remaining_in_project;
      v_data(3).data_type := 'NUMBER';

v_csv_line := csv_it(v_data);

      v_txt_line := rpad(ci.project_task_name,30, ' ') ||
                    lpad(ci.estimated_hours, 5, ' ') || '     ' ||
                    lpad(ci.hours_remaining_in_project, 5, ' ') ;

write_record( v_txt_line, v_csv_line );

       IF v_line_cntr >= 56
       THEN
         v_line_cntr := 0;
         v_page_cntr := v_page_cntr + 1;
         gen_standard_header(v_report_name, v_page_cntr );
       END IF;

     END LOOP;

    close_files;
END;

   PROCEDURE PROJ_HOURS_REMAIN_BY_RESOURCE
IS
    v_data a_data;
    v_line_cntr NUMBER := 0;
    v_page_cntr NUMBER := 1;

    v_txt_line VARCHAR2(100);
    v_csv_line VARCHAR2(100);

    v_report_name VARCHAR2(80) := 'Hours Remaining By Resource';
BEGIN
    open_files('hrbr');

    gen_standard_header(v_report_name, v_page_cntr );

    v_txt_line   := center('Project/Task',30) ||
                    center('Resource', 30) ||
                    center('Est', 7) ||
                    center('Worked', 7) ||
                    center('Remaining', 10) ;

    write_record( v_txt_line, null );

    FOR ci IN (
      SELECT SUBSTR(project_task_name,1, 30) project_task_name,
             SUBSTR(resource_name,1,30) resource_name,
             to_char(estimated_hours) estimated_hours,
             to_char(hours_worked_by_resource) hours_worked_by_resource,
             to_char(hours_remaining_in_project) hours_remaining_in_project
         FROM proj_hours_remain_by_resource)
    LOOP
      v_line_cntr := v_line_cntr + 1;

      v_data(1).data := ci.project_task_name;
      v_data(1).data_type := 'CHAR';
      v_data(3).data := ci.resource_name;
      v_data(3).data_type := 'CHAR';
      v_data(2).data := ci.estimated_hours;
      v_data(2).data_type := 'NUMBER';
      v_data(4).data := ci.hours_worked_by_resource;
      v_data(4).data_type := 'NUMBER';
      v_data(5).data := ci.hours_remaining_in_project;
      v_data(5).data_type := 'NUMBER';

      v_csv_line := csv_it(v_data);

      v_txt_line := rpad(ci.project_task_name,30, ' ') ||
                    rpad(ci.resource_name, 30, ' ') ||
                    lpad(ci.estimated_hours, 5, ' ') || ' ' ||
                    lpad(ci.hours_worked_by_resource, 5, ' ') || ' ' ||
                    lpad(ci.hours_remaining_in_project, 5, ' ');

       write_record( v_txt_line, v_csv_line );

       IF v_line_cntr >= 56
       THEN
         v_line_cntr := 0;
         v_page_cntr := v_page_cntr + 1;
         gen_standard_header(v_report_name, v_page_cntr );
       END IF;

END LOOP;

close_files;
END;

PROCEDURE open_files(
    p_report_name IN VARCHAR2 )
IS
    v_file UTL_FILE.FILE_TYPE;
    v_file_name VARCHAR2(255);
BEGIN

    v_file_name := replace(p_report_name,' ', '_') ||
                   user ||
                   to_char(sysdate, 'YYYYMMDDHH24MISS');

    g_files.txt_file := utl_file.fopen('REPORT_OUTPUT', v_file_name || '.txt', 'w',
2000);
    g_files.csv_file := utl_file.fopen('REPORT_OUTPUT', v_file_name || '.csv', 'w',
2000);

END;

PROCEDURE gen_standard_header(
     p_title IN VARCHAR2,
     p_page IN NUMBER )
IS
    v_header VARCHAR2(100);
    v_date DATE := sysdate;
BEGIN
    -- No header in CSV File

    v_header := to_char(v_date, 'MM/DD/YYYY') ||
                center( p_title, 65 ) ||
                p_page;

    write_record( ' ', NULL );
    write_record( v_header, NULL );
    write_record( ' ', NULL );

END;

FUNCTION center(
     p_string IN VARCHAR2,
     p_length IN NUMBER )
     RETURN VARCHAR2
  IS
    v_data_len NUMBER := trunc(length(p_string)/2);
    v_start_len NUMBER := trunc(p_length/2);
    v_write_len NUMBER := v_start_len - v_data_len;
    v_string VARCHAR2(1000) := ' ';
BEGIN

    v_string := lpad(v_string, v_write_len, ' ');
    v_string := v_string || p_string;
    v_string := rpad(v_string, p_length, ' ');
    RETURN v_string;
END;

PROCEDURE write_record(
     p_txt_line IN VARCHAR2,
     p_csv_line IN VARCHAR2 )
IS
BEGIN
    IF p_txt_line IS NOT NULL
    THEN
      utl_file.put_line(g_files.txt_file, p_txt_line);
    END IF;

IF p_CSV_line IS NOT NULL
    THEN
      utl_file.put_line(g_files.csv_file, p_csv_line);
    END IF;
END;

FUNCTION csv_it( p_data IN a_data )
     RETURN VARCHAR2
IS
    v_return_string VARCHAR2(1000);
BEGIN
    FOR i IN 1..p_data.COUNT
    LOOP
      IF p_data(i).data_type = 'CHAR'
      THEN
        v_return_string := v_return_string || '"' || p_data(i).data || '",';
      ELSE
        v_return_string := v_return_string || p_data(i).data || ',';
      END IF;
    END LOOP;
    RETURN v_return_string;
END;

PROCEDURE close_files
IS
BEGIN
    utl_file.fclose(g_files.txt_file);
    utl_file.fclose(g_files.csv_file);
END;

END;
/
CREATE OR REPLACE PACKAGE tt_user_admin
AS

PROCEDURE create_user(
      p_user_name IN VARCHAR2,
      p_user_password IN VARCHAR2,
      p_user_type IN VARCHAR2 DEFAULT 'USER' );

PROCEDURE change_user(
      p_user_name IN VARCHAR2,
      p_user_password IN VARCHAR2 DEFAULT NULL,
      p_user_type IN VARCHAR2 DEFAULT NULL );

PROCEDURE drop_user(
      p_user_name IN VARCHAR2 );

END;
/

CREATE OR REPLACE PACKAGE BODY tt_user_admin
AS

FUNCTION valid_role(
      p_user_type IN VARCHAR2 DEFAULT 'USER' )
      RETURN BOOLEAN;

FUNCTION get_user_role(
      p_user_type IN VARCHAR2 DEFAULT 'USER' )
      RETURN VARCHAR2;

PROCEDURE create_user(
      p_user_name IN VARCHAR2,
      p_user_password IN VARCHAR2,
      p_user_type IN VARCHAR2 DEFAULT 'USER' )
AS
    v_role_name VARCHAR2(30);
BEGIN
    IF valid_role( p_user_type )
    THEN
      v_role_name := get_user_role(p_user_type);

      EXECUTE IMMEDIATE 'CREATE USER ' || UPPER(p_user_name) ||
         ' IDENTIFIED BY ' || p_user_password;

      EXECUTE IMMEDIATE 'GRANT ' ||
          v_role_name ||
          ' TO ' || p_user_name;

      EXECUTE IMMEDIATE 'GRANT connect, resource ' ||
          ' TO ' || p_user_name;
    ELSE
      dbms_output.put_line( 'Invalid user type: ' || p_user_type );
    END IF;
END;


PROCEDURE change_user(
      p_user_name IN VARCHAR2,
      p_user_password IN VARCHAR2 DEFAULT NULL,
      p_user_type IN VARCHAR2 DEFAULT NULL )
AS
    v_role_name VARCHAR2(30);
BEGIN
    IF p_user_password IS NOT NULL
    THEN
      EXECUTE IMMEDIATE 'alter user ' || UPPER(p_user_name) ||
        ' identified by ' || p_user_password;
    END IF;

    IF p_user_type IS NOT NULL
      AND valid_role( p_user_type )
    THEN
    FOR c1 IN
        (SELECT granted_role role_name
           FROM sys.Remote DBA_role_privs
           WHERE grantee = UPPER(p_user_name)
             AND granted_role IN
             ('TT_USER_ROLE', 'TT_PM_ROLE', 'TT_ADMIN_ROLE' ))
      LOOP
        EXECUTE IMMEDIATE 'revoke ' || c1.role_name ||
            ' from ' || p_user_name;
      END LOOP;

      v_role_name := get_user_role(p_user_type);

      EXECUTE IMMEDIATE 'GRANT ' ||
          v_role_name ||
          ' TO ' || p_user_name;
    END IF;

END;

PROCEDURE drop_user(
      p_user_name IN VARCHAR2 )
AS
BEGIN
    IF p_user_name NOT LIKE 'SYS%'
      AND p_user_name NOT IN ('TRAKER')
    THEN
      EXECUTE IMMEDIATE 'drop user ' || UPPER(p_user_name);
    END IF;
END;

FUNCTION valid_role(
      p_user_type IN VARCHAR2 DEFAULT 'USER' )
      RETURN BOOLEAN
IS
BEGIN
    CASE p_user_type
    WHEN 'USER'
    THEN RETURN TRUE;
    WHEN 'PM'
    THEN RETURN TRUE;
    WHEN 'ADMIN'
    THEN RETURN TRUE;
    ELSE
         RETURN FALSE;
    END CASE;
END;

FUNCTION get_user_role(
      p_user_type IN VARCHAR2 DEFAULT 'USER' )
      RETURN VARCHAR2
AS
BEGIN
    CASE p_user_type
    WHEN 'USER' THEN
      RETURN 'tt_user_role';
    WHEN 'PM' THEN
      RETURN 'tt_pm_role';
    WHEN 'ADMIN' THEN
      RETURN 'tt_admin_role';
    END CASE;
END;

END;
/