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: Traker Packages

Oracle Tips by Burleson Consulting
 

* chap9_edb_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 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' );

  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 NUMBER;    

  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 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 NUMBER;      

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 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 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_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 INTEGER;
    v_business_day CHAR;
  BEGIN  
 
    v_date := trunc(p_from_date) - 1;
 
    FOR i IN 1..to_char(trunc(p_to_date),'J')::integer –
                       to_char(trunc(p_from_date),'J')::integer
    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 BODY tt_user_admin
AS 

  FUNCTION valid_role(
      p_user_type IN VARCHAR2 DEFAULT 'USER',
      p_role_name OUT VARCHAR2 )
      RETURN BOOLEAN
  IS

  BEGIN
    CASE p_user_type
    WHEN 'USER'
    THEN p_role_name := 'TT_USER_ROLE';
         dbms_output.put_line( 'v_role is: ' || p_role_name );
         RETURN TRUE;
    WHEN 'PM'
    THEN p_role_name := 'TT_PM_ROLE';
         dbms_output.put_line( 'v_role is: ' || p_role_name );
         RETURN TRUE;
    WHEN 'ADMIN'
    THEN p_role_name := 'TT_ADMIN_ROLE';
         dbms_output.put_line( 'v_role is: ' || p_role_name );
         RETURN TRUE;
   
ELSE
         dbms_output.put_line( 'v_role is: ' || p_role_name );
         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;

  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(300) := ' ';
  BEGIN
    IF valid_role( p_user_type, v_role_name )
    THEN
      v_role_name := get_user_role(p_user_type);
      dbms_output.put_line( 'v_role is: ');
      dbms_output.put_line( v_role_name );
      dbms_output.put_line( '|' ); 

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

      EXECUTE IMMEDIATE 'GRANT ' ||
          v_role_name ||
          ' TO ' || p_user_name;
      dbms_output.put_line( 'GRANT ' ||
          v_role_name ||
          ' TO ' || p_user_name );

      EXECUTE IMMEDIATE 'GRANT connect, resource ' ||
          ' TO ' || p_user_name;
      dbms_output.put_line( '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, v_role_name )
    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;     

      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')
      EXECUTE IMMEDIATE 'drop user ' || UPPER(p_user_name);
    END IF;
  END;

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_txt_file UTL_FILE.FILE_TYPE;
    g_csv_file
UTL_FILE.FILE_TYPE; 

  g_space VARCHAR2(1) := ' '; 

--  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 )
  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_txt_file := utl_file.fopen('REPORT_OUTPUT', v_file_name || '.txt', 'w', 32000);
    g_csv_file := utl_file.fopen('REPORT_OUTPUT', v_file_name || '.csv', 'w', 32000);

  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, g_space);
    v_string := v_string || p_string;
    v_string := rpad(v_string, p_length, g_space);
    RETURN v_string;

  END;

  PROCEDURE write_record(
     p_txt_line IN VARCHAR,
     p_csv_line IN VARCHAR )
  IS
  BEGIN
    IF p_txt_line IS NOT NULL
    THEN
      utl_file.put_line(g_txt_file, p_txt_line, TRUE);
    END IF;    

    IF p_CSV_line IS NOT NULL
    THEN
      utl_file.put_line(g_csv_file, p_csv_line, TRUE);
    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_txt_file);
    utl_file.fclose(g_csv_file);
  END;

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

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

    CURSOR c1 IS
      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;

  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 c1
    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, g_space) ||
                    lpad(ci.estimated_hours, 5, g_space) || '  ' ||
                    rpad(ci.resource_name, 30, g_space) ||
                    lpad(ci.hours_worked, 5, g_space) || '  ' ||
                    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;

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)::numeric,
                tt_manage_resources.get_resource_id(p_resource_name)::numeric,
                trunc(p_item_date)::timestamp without time zone,
                p_hours_worked::numeric,
                p_active_flag::text);
   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
    CURSOR c1 IS
      SELECT xml_data, interface_id
        FROM tt_xml_interface;

  BEGIN 

    FOR ci IN c1
    LOOP   
      add_new_item_update(
        xpath_string(ci.xml_data, '/interface/what')::TEXT,
        xpath_string(ci.xml_data, '/interface/who')::TEXT,
        xpath_number(ci.xml_data, '/interface/hours')::INTEGER,
        to_date(xpath_string(ci.xml_data, '/interface/when'), 'DD-MON-YYYY')::TIMESTAMP WITHOUT
IME ZONE,
        'Y'::TEXT);       

        DELETE FROM tt_xml_interface
          WHERE interface_id = ci.interface_id;         

    END LOOP;
   
  END;
        
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 NUMBER
  AS
    v_resource_id NUMBER;
  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 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 = tt_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 NUMBER
  AS
    v_task_id NUMBER;
  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;

 
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