Rem
Rem $Header: jobs_ui_runs_pkgdef.sql 15-aug-2006.06:28:22 rdabbott Exp $
Rem
Rem jobs_ui_runs_pkgdef.sql
Rem
Rem Copyright (c) 2003, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      jobs_ui_runs_pkgdef.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rdabbott    08/15/06 - Backport rdabbott_bug-5366754 from main
Rem    rdabbott    07/07/06 - 5366754 - obsolete target guid
Rem    pkantawa    01/10/05 - Change runs queries 
Rem    pkantawa    12/13/04 - Return target name instead of display name 
Rem    pkantawa    10/13/04 - Add job_type_category to jobrun_rowtype 
Rem    rdabbott    01/30/04 - Fix 3256113: create 'active' bucket 
Rem    jberesni    10/28/03 - scheduled_date=>scheduled_time
Rem    tzimmerm    10/27/03 - tzimmerm_bug-3209032 
Rem    tzimmerm    10/27/03 - 
Rem    tzimmerm    10/24/03 - Created
Rem

CREATE OR REPLACE package MGMT_JOB_UI_RUNS
as

--------------------------------------
-- Implements an API for the job runs UI:
-- filter criteria are passed in and strong
-- ref cursor is returned

----------------------------------------
-- declared constants
-- (these will be in another package)
--
TRUE_FLAG   CONSTANT integer := 1;
FALSE_FLAG  CONSTANT integer := 0;

------------------------------------------
-- these are valid target category filters
--
CATEGORY_TARGETS            CONSTANT integer(1) := 3;
CATEGORY_GROUPS             CONSTANT integer(1) := 2;
CATEGORY_TARGETS_IN_GROUP   CONSTANT integer(1) := 1;
CATEGORY_SYS_TARGETLESS     CONSTANT integer(1) := 0;

TIMEFRAME_ALL   constant NUMBER(6) := -100000;  -- almost 300 years


----------- SLPA declarations -------------
ASSERTFAIL     EXCEPTION;
ASSERTFAIL_C   CONSTANT INTEGER := -20999;
PRAGMA EXCEPTION_INIT(ASSERTFAIL, -20999);
PKGNAME_C      CONSTANT VARCHAR2(20) := 'MGMT_JOB_UI_RUNS';

-----------------------------------
-- basic record and cursor types for the UI
--
TYPE jobrun_rowtype IS RECORD
   (job_id           MGMT_JOB.job_id%TYPE
   ,job_name         MGMT_JOB.job_name%TYPE
   ,job_owner        MGMT_JOB.job_owner%TYPE
   ,job_type         MGMT_JOB.job_type%TYPE
   ,scheduled_time   MGMT_JOB_EXEC_SUMMARY.scheduled_time%TYPE
   ,frequency_code   MGMT_JOB_SCHEDULE.frequency_code%TYPE
   ,timezone_region  MGMT_JOB_EXEC_SUMMARY.timezone_region%TYPE
   ,timezone_info    MGMT_JOB_SCHEDULE.timezone_info%TYPE
   ,target_name      MGMT_TARGETS.target_name%TYPE
   ,target_type      MGMT_TARGETS.target_type%TYPE
   ,type_display_name    MGMT_TARGETS.type_display_name%TYPE
   ,target_count     integer
   ,status_bucket    MGMT_JOB_EXEC_SUMMARY.status%TYPE
   ,status_bucket_count  integer
   ,sample_exec_id   MGMT_JOB_EXEC_SUMMARY.execution_id%TYPE
   ,job_type_category MGMT_JOB_TYPE_INFO.job_type_category%TYPE
   );

TYPE jobruns_cvtype IS REF CURSOR RETURN jobrun_rowtype;

------------------------------------------------------
--compute target category based on target type
--
FUNCTION compute_category_from_type(p_target_type_IN IN VARCHAR2)
    RETURN NUMBER;
    
-----------------------------------
-- generic API
--
PROCEDURE open_jobruns
   (job_name_IN    IN  varchar2
   ,Job_owner_IN   IN  varchar2
   ,job_type_IN    IN  varchar2
   ,job_id_IN      IN  varchar2
   ,target_name_IN IN  varchar2
   ,target_type_IN IN  varchar2
   ,target_guid_IN IN  varchar2 -- unsued
   ,target_category_IN IN integer := -1
   ,days_IN        IN  integer   := TIMEFRAME_ALL
   ,status_IN      IN  integer   := MGMT_JOB_ENGINE.STATUS_BUCKET_ALL
   ,ignore_access_IN IN integer  := FALSE_FLAG
   ,jobruns_CV IN OUT  jobruns_cvtype
   );

end MGMT_JOB_UI_RUNS;
/
show errors