Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/jobs/jobs_ui_execs_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2008/11/19 15:06:01 rdabbott Exp $ Rem Rem jobs_ui_execs_pkgbody.sql Rem Rem Copyright (c) 2003, 2008, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem jobs_ui_execs_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem 1. We do not exclude corrective actions by checking Rem is_corrective_actions=0. We rely on the fact that the join with Rem Schedule table will filter out CAs, since CAs dont have a schedule. Rem Rem MODIFIED (MM/DD/YY) Rem rdabbott 11/18/08 - XbranchMerge rdabbott_metaln from Rem st_emcore_10.2.0.1.0 Rem rdabbott 11/11/08 - rename mtalnk Rem rdabbott 07/07/06 - fix 5366754 - see jobs on hidden targets Rem rdabbott 08/15/06 - Backport rdabbott_bug-5366754 from main Rem pkantawa 07/27/05 - Fix 4504924: fix open_j_execs_groups Rem pkantawa 07/15/05 - Exclude executions in delete pending status Rem pkantawa 06/23/05 - Fix 4448379: Do exact match when access is Rem ignored Rem pkantawa 06/07/05 - Add comment about excluding CAs Rem pkantawa 03/08/05 - exclude jobs in delete pending status Rem kmanicka 03/04/05 - move to EMDW_LOG from dbms_output Rem dcawley 02/21/05 - Replace enter super user mode Rem ashugupt 01/13/05 - added execution error code info to output Rem pkantawa 01/11/05 - Change executions queries Rem pkantawa 12/23/04 - change jobs page query Rem pkantawa 12/20/04 - Fix 4074662: Change query to do an exact match Rem skini 10/27/04 - Versioning changes, part 2 Rem pkantawa 10/12/04 - account for Multi Task jobs in queries Rem skini 09/09/04 - Exclude waiting executions Rem ramalhot 08/24/04 - cutover to new assoc tables Rem dcawley 07/07/04 - Increase user name size Rem rdabbott 02/18/04 - keep code for both active bucket queries Rem rdabbott 02/02/04 - disallow special case 0s Rem rdabbott 01/31/04 - use indexed function Rem rdabbott 01/30/04 - Fix 3256113: create 'active' bucket Rem rdabbott 01/26/04 - show all system jobs Rem rdabbott 12/05/03 - fix (perf) 3262649: change join order Rem rdabbott 11/17/03 - fix 3260449: security - only show jobs on Rem targets the user can view Rem rdabbott 11/15/03 - hide runs_in_exec until used Rem rdabbott 11/12/03 - fix 3241333: run details filters Rem rdabbott 11/07/03 - jb review comments 3238484 Rem rdabbott 11/06/03 - fix 3238484: return target type when possible Rem jberesni 10/31/03 - tune targets_in_group query Rem jberesni 10/30/03 - tune targetless query Rem jberesni 10/30/03 - replace -999 with STATUS_BUCKET_ALL Rem jberesni 10/28/03 - hint open_j_execs_groups Rem tzimmerm 10/27/03 - tzimmerm_bug-3209032 Rem tzimmerm 10/27/03 - Rem tzimmerm 10/27/03 - Rem tzimmerm 10/27/03 - Created Rem CREATE OR REPLACE package body MGMT_JOB_UI_EXECS as ---------------------------------------------------- -- -- -- Private procedure to support basic text filtering -- as a fall-through in absence of more specialized -- procedure. -- PROCEDURE open_j_execs_targets (job_name_IN IN varchar2 ,job_owner_IN IN varchar2 ,job_type_IN IN varchar2 ,target_name_IN IN varchar2 ,target_type_IN IN varchar2 := '%' ,days_IN IN integer := TIMEFRAME_ALL ,status_IN IN integer := MGMT_JOB_ENGINE.STATUS_BUCKET_ALL ,status_min_IN IN integer := MGMT_JOB_ENGINE.ALL_BUCKET_START ,status_max_IN IN integer := MGMT_JOB_ENGINE.ALL_BUCKET_END ,SYSTEM_JOB_IN IN integer := 0 ,jobexec_CV IN OUT jobexec_cvtype ) is l_category_a MGMT_JOB_TYPE_INFO.job_type_category%TYPE; l_category_b MGMT_JOB_TYPE_INFO.job_type_category%TYPE; l_job_type_in MGMT_JOB_TYPE_INFO.job_type%TYPE := JOB_TYPE_IN; BEGIN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('IN RDAs open_j_execs_targets PROCEDURE:',MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('-----------------------------------',MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_name_IN = ' || job_name_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_owner_IN = ' || job_owner_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_type_IN = ' || job_type_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('target_type_IN = ' || target_type_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('target_name_IN = ' || target_name_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('days_IN = ' || days_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_IN = ' || status_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_min_IN = ' || status_min_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_max_IN = ' || status_max_IN,MGMT_JOB_ENGINE.MODULE_NAME); END IF; --compute allowable categories MGMT_JOB_UI.compute_jobtype_categories(JOB_TYPE_IN, l_category_a, l_category_b); --if job type is multitask, set it to wildcard because query will be driven --by category IF l_job_type_in = MGMT_JOB_ENGINE.MULTITASK_JOB_TYPE THEN l_job_type_in := '%'; END IF; open jobexec_CV for select --+ exec_summ.job_id ,exec_summ.execution_id ,jobs.job_name ,jobs.job_owner ,jobs.job_type ,exec_summ.scheduled_time ,schedule.frequency_code ,schedule.timezone_info ,exec_summ.timezone_region ,MAX(targets.target_name) target_name ,DECODE(MAX(targets.target_type) ,MIN(targets.target_type) ,MAX(targets.target_type) ,'Mixed') target_type ,DECODE(MAX(targets.type_display_name) ,MIN(targets.type_display_name) ,MAX(targets.type_display_name) ,'Mixed') type_display_name ,COUNT(targets.target_name) target_count ,exec_summ.status ,jobtype_info.job_type_category ,exec_summ.status_code ,exec_summ.status_code_category from mgmt_job_exec_summary exec_summ ,mgmt_job_ext_targets exec_targets ,mgmt_targets targets ,mgmt_job jobs ,mgmt_job_schedule schedule ,mgmt_job_type_info jobtype_info ,mgmt_job_type_max_versions max_versions where scheduled_time >= sysdate + DAYS_IN and exec_targets.job_id = exec_summ.job_id and exec_targets.execution_id = exec_summ.execution_id and exec_targets.target_guid = targets.target_guid and targets.target_name like TARGET_NAME_IN and targets.target_type like TARGET_TYPE_IN -- and jobs.schedule_id = schedule.schedule_id and jobs.is_library+0 = 0 and jobs.system_job+0 <= SYSTEM_JOB_IN and jobs.system_job+0 <= SYSTEM_JOB_IN*10 and jobs.job_status != MGMT_JOB_ENGINE.JOB_STATUS_DELETE_PENDING -- and job_name like JOB_NAME_IN and job_owner like JOB_OWNER_IN and jobs.job_type like l_job_type_in and jobs.job_id = exec_summ.job_id and jobs.job_type = max_versions.job_type and jobs.job_type_major_version = max_versions.major_version and max_versions.job_type_id = jobtype_info.job_type_id and jobtype_info.job_type_category in (l_category_a, l_category_b) -- and exec_summ.status NOT IN (MGMT_JOB_ENGINE.WAITING_STATUS, MGMT_JOB_ENGINE.DELETE_PENDING_STATUS) and ( (exec_summ.status = STATUS_IN AND STATUS_IN >= 0) OR (exec_summ.status_bucket = STATUS_IN AND STATUS_IN <0) OR (NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) OR ((NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) AND exec_summ.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED) ) ) -- todo - convert to new status values and rm the get_status_2_from_status() call -- and STATUS_MIN_IN <= MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- and MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- <= STATUS_MAX_IN group by exec_summ.job_id ,exec_summ.execution_id ,jobs.job_name ,jobs.job_owner ,jobs.job_type ,exec_summ.scheduled_time ,schedule.frequency_code ,schedule.timezone_info ,exec_summ.timezone_region ,exec_summ.status ,jobtype_info.job_type_category ,exec_summ.status_code ,exec_summ.status_code_category order by exec_summ.scheduled_time DESC ,exec_summ.job_id ,exec_summ.status; end open_j_execs_targets; ---------------------------------------------------- -- open_j_execs_groups -- -- Private procedure to support basic text filtering -- as a fall-through in absence of more specialized -- procedure. -- PROCEDURE open_j_execs_groups (job_name_IN IN varchar2 ,job_owner_IN IN varchar2 ,job_type_IN IN varchar2 ,target_name_IN IN varchar2 ,target_type_IN IN varchar2 := '%' ,days_IN IN integer := TIMEFRAME_ALL ,status_IN IN integer := MGMT_JOB_ENGINE.STATUS_BUCKET_ALL ,status_min_IN IN integer := MGMT_JOB_ENGINE.ALL_BUCKET_START ,status_max_IN IN integer := MGMT_JOB_ENGINE.ALL_BUCKET_END ,SYSTEM_JOB_IN IN integer := 0 ,jobexec_CV IN OUT jobexec_cvtype ) is l_category_a MGMT_JOB_TYPE_INFO.job_type_category%TYPE; l_category_b MGMT_JOB_TYPE_INFO.job_type_category%TYPE; l_job_type_in MGMT_JOB_TYPE_INFO.job_type%TYPE := JOB_TYPE_IN; BEGIN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('IN RDAs open_j_execs_groups PROCEDURE:',MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('-----------------------------------',MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_name_IN = ' || job_name_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_owner_IN = ' || job_owner_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_type_IN = ' || job_type_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('target_type_IN = ' || target_type_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('target_name_IN = ' || target_name_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('days_IN = ' || days_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_IN = ' || status_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_min_IN = ' || status_min_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_max_IN = ' || status_max_IN,MGMT_JOB_ENGINE.MODULE_NAME); END IF; --compute allowable categories MGMT_JOB_UI.compute_jobtype_categories(JOB_TYPE_IN, l_category_a, l_category_b); --if job type is multitask, set it to wildcard because query will be driven --by category IF l_job_type_in = MGMT_JOB_ENGINE.MULTITASK_JOB_TYPE THEN l_job_type_in := '%'; END IF; open jobexec_CV for SELECT --+ ordered no_merge (JobSubmission) JobSubmission.job_id ,exec_summ.execution_id as execution_id ,JobSubmission.job_name ,JobSubmission.job_owner ,JobSubmission.job_type ,exec_summ.scheduled_time ,JobSubmission.frequency_code ,JobSubmission.timezone_info as timezone_info ,exec_summ.timezone_region as timezone_region ,MAX(target.target_name) target_name ,DECODE(MAX(target.target_type) ,MIN(target.target_type) ,MAX(target.target_type) ,'Mixed') target_type ,DECODE(MAX(target.type_display_name) ,MIN(target.type_display_name) ,MAX(target.type_display_name) ,'Mixed') type_display_name ,COUNT(target.target_name) target_count ,exec_summ.status as status ,JobSubmission.job_type_category ,exec_summ.status_code ,exec_summ.status_code_category FROM -- >----------------------> assemble group submission info (select --+ ordered jobs.job_id as job_id ,MAX(jobs.job_name) as job_name ,MAX(jobs.job_owner) as job_owner ,MAX(jobs.job_type) as job_type ,MAX(schedule.frequency_code) as frequency_code ,MAX(schedule.timezone_region) as timezone_region ,MAX(schedule.timezone_info) as timezone_info ,jobtype_info.job_type_category as job_type_category from mgmt_targets targets ,mgmt_job_target submit_targets ,mgmt_job jobs ,mgmt_job_schedule schedule ,mgmt_job_type_info jobtype_info ,mgmt_job_type_max_versions max_versions where submit_targets.execution_id = hextoraw('0000000000000000') and submit_targets.job_id = jobs.job_id and submit_targets.target_guid = targets.target_guid and jobs.schedule_id = schedule.schedule_id and jobs.is_library+0 = 0 and jobs.system_job+0 = 0 and jobs.job_status != MGMT_JOB_ENGINE.JOB_STATUS_DELETE_PENDING -- and jobs.job_type = max_versions.job_type and jobs.job_type_major_version = max_versions.major_version and max_versions.job_type_id = jobtype_info.job_type_id and jobtype_info.job_type_category in (l_category_a, l_category_b) -- and job_name like JOB_NAME_IN and job_owner like JOB_OWNER_IN and jobs.job_type like l_job_type_in -- and targets.target_name like TARGET_NAME_IN and targets.target_type like TARGET_TYPE_IN -- group by Jobs.job_id ,jobtype_info.job_type_category ) JobSubmission -- >-----------------------------------c-> join in execution info ,mgmt_job_ext_targets exec_targets ,mgmt_targets target ,mgmt_job_exec_summary exec_summ WHERE scheduled_time >= sysdate + DAYS_IN and exec_targets.execution_id = exec_summ.execution_id and exec_targets.job_id = exec_summ.job_id and exec_targets.target_guid = target.target_guid -- and JobSubmission.job_id = exec_summ.job_id and JobSubmission.job_id = exec_targets.job_id -- and exec_summ.status NOT IN (MGMT_JOB_ENGINE.WAITING_STATUS, MGMT_JOB_ENGINE.DELETE_PENDING_STATUS) and ( (exec_summ.status = STATUS_IN AND STATUS_IN >= 0) OR (exec_summ.status_bucket = STATUS_IN AND STATUS_IN <0) OR (NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) OR ((NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) AND exec_summ.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED) ) ) -- todo - convert to new status values and rm the get_status_2_from_status() call -- and STATUS_MIN_IN <= MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- and MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- <= STATUS_MAX_IN GROUP BY JobSubmission.job_id ,exec_summ.execution_id ,JobSubmission.job_name ,JobSubmission.job_owner ,JobSubmission.job_type ,exec_summ.scheduled_time ,JobSubmission.frequency_code ,JobSubmission.timezone_info ,exec_summ.timezone_region ,exec_summ.status ,JobSubmission.job_type_category ,exec_summ.status_code ,exec_summ.status_code_category ORDER BY exec_summ.scheduled_time DESC ,JobSubmission.job_id ,exec_summ.status; end open_j_execs_groups; ---------------------------------------------------- -- open_j_execs_targetless -- -- Private procedure to support basic text filtering -- as a fall-through in absence of more specialized -- procedure. -- PROCEDURE open_j_execs_targetless (job_name_IN IN varchar2 ,job_owner_IN IN varchar2 ,job_type_IN IN varchar2 ,days_IN IN integer := TIMEFRAME_ALL ,status_IN IN integer := MGMT_JOB_ENGINE.STATUS_BUCKET_ALL ,status_min_IN IN integer := MGMT_JOB_ENGINE.ALL_BUCKET_START ,status_max_IN IN integer := MGMT_JOB_ENGINE.ALL_BUCKET_END ,SYSTEM_JOB_IN IN integer := 0 ,jobexec_CV IN OUT jobexec_cvtype ) is l_category_a MGMT_JOB_TYPE_INFO.job_type_category%TYPE; l_category_b MGMT_JOB_TYPE_INFO.job_type_category%TYPE; l_job_type_in MGMT_JOB_TYPE_INFO.job_type%TYPE := JOB_TYPE_IN; BEGIN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('IN RDAs open_j_execs_targetless PROCEDURE:',MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('-----------------------------------',MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_name_IN = ' || job_name_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_owner_IN = ' || job_owner_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_type_IN = ' || job_type_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('days_IN = ' || days_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_IN = ' || status_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_min_IN = ' || status_min_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_max_IN = ' || status_max_IN,MGMT_JOB_ENGINE.MODULE_NAME); END IF; --compute allowable categories MGMT_JOB_UI.compute_jobtype_categories(JOB_TYPE_IN, l_category_a, l_category_b); --if job type is multitask, set it to wildcard because query will be driven --by category IF l_job_type_in = MGMT_JOB_ENGINE.MULTITASK_JOB_TYPE THEN l_job_type_in := '%'; END IF; open jobexec_CV for select --+ ordered exec_summ.job_id ,exec_summ.execution_id ,jobs.job_name ,jobs.job_owner ,jobs.job_type ,exec_summ.scheduled_time ,schedule.frequency_code ,schedule.timezone_info ,exec_summ.timezone_region ,null as target_name ,null as target_type ,null as type_display_name ,0 as target_count ,exec_summ.status ,jobtype_info.job_type_category ,exec_summ.status_code ,exec_summ.status_code_category from mgmt_job jobs ,mgmt_job_schedule schedule ,mgmt_job_exec_summary exec_summ ,mgmt_job_type_info jobtype_info ,mgmt_job_type_max_versions max_versions where scheduled_time >= sysdate + DAYS_IN and NOT EXISTS ( select 1 from mgmt_job_target submit_targets where submit_targets.job_id = jobs.job_id and submit_targets.execution_id = hextoraw('0000000000000000') ) -- and jobs.schedule_id = schedule.schedule_id and jobs.is_library+0 = 0 and jobs.system_job+0 = SYSTEM_JOB_IN and jobs.job_status != MGMT_JOB_ENGINE.JOB_STATUS_DELETE_PENDING -- and job_name like JOB_NAME_IN and job_owner like JOB_OWNER_IN and jobs.job_type like l_job_type_in and jobs.job_id = exec_summ.job_id and jobs.job_type = max_versions.job_type and jobs.job_type_major_version = max_versions.major_version and max_versions.job_type_id = jobtype_info.job_type_id and jobtype_info.job_type_category in (l_category_a, l_category_b) -- and exec_summ.status NOT IN (MGMT_JOB_ENGINE.WAITING_STATUS, MGMT_JOB_ENGINE.DELETE_PENDING_STATUS) and ( (exec_summ.status = STATUS_IN AND STATUS_IN >= 0) OR (exec_summ.status_bucket = STATUS_IN AND STATUS_IN <0) OR (NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) OR ((NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) AND exec_summ.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED) ) ) -- todo - convert to new status values and rm the get_status_2_from_status() call -- and STATUS_MIN_IN <= MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- and MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- <= STATUS_MAX_IN order by exec_summ.scheduled_time DESC ,exec_summ.job_id ,exec_summ.status; end open_j_execs_targetless; ---------------------------------------------------- -- open_j_execs_targets_in_group -- -- Private procedure to support basic text filtering -- as a fall-through in absence of more specialized -- procedure. -- PROCEDURE open_j_execs_targets_in_group (job_name_IN IN varchar2 ,job_owner_IN IN varchar2 ,job_type_IN IN varchar2 ,target_name_IN IN varchar2 ,target_type_IN IN varchar2 := '%' ,days_IN IN integer := TIMEFRAME_ALL ,status_IN IN integer := MGMT_JOB_ENGINE.STATUS_BUCKET_ALL ,status_min_IN IN integer := MGMT_JOB_ENGINE.ALL_BUCKET_START ,status_max_IN IN integer := MGMT_JOB_ENGINE.ALL_BUCKET_END ,SYSTEM_JOB_IN IN integer := 0 ,jobexec_CV IN OUT jobexec_cvtype ) is l_category_a MGMT_JOB_TYPE_INFO.job_type_category%TYPE; l_category_b MGMT_JOB_TYPE_INFO.job_type_category%TYPE; l_job_type_in MGMT_JOB_TYPE_INFO.job_type%TYPE := JOB_TYPE_IN; BEGIN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('IN RDAs open_j_execs_targets_in_group PROCEDURE:',MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('-----------------------------------',MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_name_IN = ' || job_name_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_owner_IN = ' || job_owner_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('job_type_IN = ' || job_type_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('target_type_IN = ' || target_type_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('target_name_IN = ' || target_name_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('days_IN = ' || days_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_IN = ' || status_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_min_IN = ' || status_min_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('status_max_IN = ' || status_max_IN,MGMT_JOB_ENGINE.MODULE_NAME); END IF; --compute allowable categories MGMT_JOB_UI.compute_jobtype_categories(JOB_TYPE_IN, l_category_a, l_category_b); --if job type is multitask, set it to wildcard because query will be driven --by category IF l_job_type_in = MGMT_JOB_ENGINE.MULTITASK_JOB_TYPE THEN l_job_type_in := '%'; END IF; open jobexec_CV for select --+ ordered exec_summ.job_id ,exec_summ.execution_id ,jobs.job_name ,jobs.job_owner ,jobs.job_type ,exec_summ.scheduled_time ,schedule.frequency_code ,schedule.timezone_info ,exec_summ.timezone_region ,MAX(targets.target_name) target_name ,DECODE(MAX(targets.target_type) ,MIN(targets.target_type) ,MAX(targets.target_type) ,'Mixed') target_type ,DECODE(MAX(targets.type_display_name) ,MIN(targets.type_display_name) ,MAX(targets.type_display_name) ,'Mixed') type_display_name ,COUNT(targets.target_name) target_count ,exec_summ.status ,jobtype_info.job_type_category ,exec_summ.status_code ,exec_summ.status_code_category from mgmt_flat_target_assoc group_members ,mgmt_targets targets ,mgmt_job_ext_targets exec_targets ,mgmt_job_exec_summary exec_summ ,mgmt_job jobs ,mgmt_job_schedule schedule ,mgmt_targets source_target ,mgmt_job_type_info jobtype_info ,mgmt_job_type_max_versions max_versions where scheduled_time >= sysdate + DAYS_IN -- and source_target.target_name like TARGET_NAME_IN and source_target.target_type like TARGET_TYPE_IN and group_members.source_target_guid = source_target.target_guid and group_members.is_membership = 1 -- and ( -- need to see both group and member targets... group_members.assoc_target_guid = targets.target_guid OR group_members.source_target_guid = targets.target_guid ) and group_members.assoc_target_guid = exec_targets.target_guid -- and exec_targets.execution_id = exec_summ.execution_id and exec_targets.job_id = exec_summ.job_id and exec_targets.target_guid = targets.target_guid -- and jobs.schedule_id = schedule.schedule_id and jobs.is_library+0 = 0 and jobs.system_job+0 = SYSTEM_JOB_IN and jobs.job_status != MGMT_JOB_ENGINE.JOB_STATUS_DELETE_PENDING -- and job_name like JOB_NAME_IN and job_owner like JOB_OWNER_IN and jobs.job_type like l_job_type_in and jobs.job_id = exec_summ.job_id and jobs.job_type = max_versions.job_type and jobs.job_type_major_version = max_versions.major_version and max_versions.job_type_id = jobtype_info.job_type_id and jobtype_info.job_type_category in (l_category_a, l_category_b) -- and exec_summ.status NOT IN (MGMT_JOB_ENGINE.WAITING_STATUS, MGMT_JOB_ENGINE.DELETE_PENDING_STATUS) and ( (exec_summ.status = STATUS_IN AND STATUS_IN >= 0) OR (exec_summ.status_bucket = STATUS_IN AND STATUS_IN <0) OR (NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) OR ((NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) AND exec_summ.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED) ) ) -- todo - convert to new status values and rm the get_status_2_from_status() call -- and STATUS_MIN_IN <= MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- and MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- <= STATUS_MAX_IN group by exec_summ.job_id ,exec_summ.execution_id ,jobs.job_name ,jobs.job_owner ,jobs.job_type ,exec_summ.scheduled_time ,schedule.frequency_code ,schedule.timezone_info ,exec_summ.timezone_region ,exec_summ.status ,jobtype_info.job_type_category ,exec_summ.status_code ,exec_summ.status_code_category order by exec_summ.scheduled_time DESC ,exec_summ.job_id ,exec_summ.status; end open_j_execs_targets_in_group; ---------------------------------------------------- -- open_job_executions -- -- Public procedure to support general filtering options -- which calls more specialized procedures for specific -- filter sets. -- PROCEDURE open_job_executions (job_name_IN IN varchar2 ,Job_owner_IN IN varchar2 ,job_type_IN IN varchar2 ,target_name_IN IN varchar2 ,target_type_IN IN varchar2 := '%' ,target_cat_IN IN integer := -1 ,days_IN IN integer := TIMEFRAME_ALL ,status_IN IN integer := MGMT_JOB_ENGINE.STATUS_BUCKET_ALL ,SYSTEM_JOB_IN IN integer := 0 ,ignore_access_IN IN integer := FALSE_FLAG ,jobexec_CV IN OUT jobexec_cvtype ) is tmp_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER; l_target_name MGMT_TARGETS.target_name%TYPE; l_target_type MGMT_TARGETS.target_type%TYPE; l_target_category NUMBER; l_status_min NUMBER(5); l_status_max NUMBER(5); begin l_target_name := target_name_IN; l_target_type := target_type_IN; l_target_category := target_cat_IN; --always check for an exact match for target name, type --if there is no match, we skip the rest of the code --if there is an exact match, our subsequent queries will be faster BEGIN SELECT target_name, target_type INTO l_target_name, l_target_type FROM mgmt_targets WHERE target_name LIKE target_name_in AND target_type LIKE target_type_in; EXCEPTION WHEN no_data_found THEN -- no targets found matching this filter RAISE_APPLICATION_ERROR( MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR, MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR_M || ' Target name = ' || target_name_in || ' and Target type = ' || target_type_in); WHEN too_many_rows THEN BEGIN -- more than one target found, -- raise_error only when ignoring access (OK for normal queries) if ignore_access_in = TRUE_FLAG then -- insure only one target can be returned RAISE_APPLICATION_ERROR( MGMT_GLOBAL.ONLY_ONE_TARGET_ERR, MGMT_GLOBAL.ONLY_ONE_TARGET_ERR_M); else -- reset to original values l_target_name := target_name_IN; l_target_type := target_type_IN; end if; END; END; -- turn VPD check off (?) -- if ignore_access_in = TRUE_FLAG then SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, MGMT_USER.OP_SET_IDENTIFIER); end if; --if category is not specified, determine based on target type if l_target_category = -1 then l_target_category := MGMT_JOB_UI_RUNS.compute_category_from_type(target_type_IN); end if; MGMT_JOB_ENGINE.get_status_2_range(p_status_in => status_IN, p_status_2_min_out => l_status_min, p_status_2_max_out => l_status_max); -- disallow GUI sending days_in=0 for ALL -- if NVL(DAYS_IN,0) = 0 then RAISE_APPLICATION_ERROR(1234, 'DAYS_IN==0 not supported'); end if; IF CATEGORY_TARGETS = l_target_category THEN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('targets query',MGMT_JOB_ENGINE.MODULE_NAME); END IF; open_j_execs_targets (job_name_IN => job_name_IN ,job_owner_IN => job_owner_IN ,job_type_IN => job_type_IN ,target_name_IN => l_target_name ,target_type_IN => l_target_type ,days_IN => days_IN ,status_IN => status_IN ,status_min_IN => l_status_min ,status_max_IN => l_status_max ,SYSTEM_JOB_IN => SYSTEM_JOB_IN ,jobexec_CV => jobexec_CV ); ELSIF CATEGORY_GROUPS = l_target_category THEN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('groups query',MGMT_JOB_ENGINE.MODULE_NAME); END IF; open_j_execs_groups (job_name_IN => job_name_IN ,job_owner_IN => job_owner_IN ,job_type_IN => job_type_IN ,target_name_IN => l_target_name ,target_type_IN => l_target_type ,days_IN => days_IN ,status_IN => status_IN ,status_min_IN => l_status_min ,status_max_IN => l_status_max ,SYSTEM_JOB_IN => SYSTEM_JOB_IN ,jobexec_CV => jobexec_CV ); ELSIF CATEGORY_SYS_TARGETLESS = l_target_category THEN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('targetless query',MGMT_JOB_ENGINE.MODULE_NAME); END IF; open_j_execs_targetless (job_name_IN => job_name_IN ,job_owner_IN => job_owner_IN ,job_type_IN => job_type_IN ,days_IN => days_IN ,status_IN => status_IN ,status_min_IN => l_status_min ,status_max_IN => l_status_max ,SYSTEM_JOB_IN => SYSTEM_JOB_IN ,jobexec_CV => jobexec_CV ); ELSIF CATEGORY_TARGETS_IN_GROUP = l_target_category THEN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('targets in group query',MGMT_JOB_ENGINE.MODULE_NAME); END IF; open_j_execs_targets_in_group (job_name_IN => job_name_IN ,job_owner_IN => job_owner_IN ,job_type_IN => job_type_IN ,target_name_IN => l_target_name ,target_type_IN => l_target_type ,days_IN => days_IN ,status_IN => status_IN ,status_min_IN => l_status_min ,status_max_IN => l_status_max ,SYSTEM_JOB_IN => SYSTEM_JOB_IN ,jobexec_CV => jobexec_CV ); END IF; -- Revert from super-user to actual user when ignore-access -- if ignore_access_in = TRUE_FLAG then SETEMUSERCONTEXT(tmp_current_user, MGMT_USER.OP_SET_IDENTIFIER); end if; EXCEPTION WHEN OTHERS THEN IF tmp_current_user IS NOT NULL THEN SETEMUSERCONTEXT(tmp_current_user, MGMT_USER.OP_SET_IDENTIFIER); END IF; RAISE; -- Re-raise the exception end open_job_executions; ---------------------------------------------------- -- open_job_executions_in_run -- -- Public procedure to support filtering the executions in a single run -- PROCEDURE open_job_executions_in_run (job_id_IN IN varchar2 ,sample_exec_id_IN IN varchar2 ,target_name_IN IN varchar2 := '%' ,status_IN IN integer := MGMT_JOB_ENGINE.STATUS_BUCKET_ALL ,cursor IN OUT REF_CURSOR ) is l_scheduled_at MGMT_JOB_EXEC_SUMMARY.scheduled_time%TYPE; l_hasTargets number(1); -- although these are locals, let the naming match the rest of the file STATUS_MIN_IN NUMBER(5); STATUS_MAX_IN NUMBER(5); begin SELECT scheduled_time INTO l_scheduled_at FROM MGMT_JOB_EXEC_SUMMARY WHERE execution_id=hextoraw (SAMPLE_EXEC_ID_IN); MGMT_JOB_ENGINE.get_status_2_range(p_status_in => status_IN, p_status_2_min_out => STATUS_MIN_IN, p_status_2_max_out => STATUS_MAX_IN); -- we expect to find targets l_hasTargets := 1; -- this test is not 'necessary', but is it faster for the true case? -- BEGIN -- SELECT 1 -- FROM mgmt_job_target submit_targets -- WHERE submit_targets.job_id = JOB_ID_IN -- and submit_targets.execution_id = hextoraw('0000000000000000'); -- -- EXCEPTION -- WHEN NO_DATA_FOUND THEN -- not compiling -- -- BEGIN -- SELECT 1 -- FROM mgmt_job_ext_targets exec_targets -- WHERE exec_targets.job_id = JOB_ID_IN -- and exec_targets.execution_id = hextoraw (SAMPLE_EXEC_ID_IN); -- -- It's OK to check SAMPLE_EXEC_ID_IN since there's -- -- only one exec for targetless jobs -- -- EXCEPTION -- WHEN NO_DATA_FOUND THEN -- l_hasTargets := 0; -- END; -- END; IF l_hasTargets = 0 THEN -- targetless case -- no submit or execution targets (eg, UpdateARUTables) open cursor for select exec_summ.execution_id ,exec_summ.status ,exec_summ.start_time ,exec_summ.end_time ,(( DECODE ( exec_summ.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, exec_summ.end_time ) -exec_summ.start_time)*24*60*60) run_time_seconds ,exec_summ.timezone_region ,null as target_name ,null as target_type ,null as type_display_name ,0 as target_count from mgmt_job_exec_summary exec_summ where exec_summ.job_id = JOB_ID_IN and exec_summ.scheduled_time = l_scheduled_at and exec_summ.status NOT IN (MGMT_JOB_ENGINE.WAITING_STATUS, MGMT_JOB_ENGINE.DELETE_PENDING_STATUS) -- and ( (exec_summ.status = STATUS_IN AND STATUS_IN >= 0) OR (exec_summ.status_bucket = STATUS_IN AND STATUS_IN <0) OR (NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) OR ((NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) AND exec_summ.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED) ) ) -- todo - convert to new status values and rm the get_status_2_from_status() call -- and STATUS_MIN_IN <= MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- and MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- <= STATUS_MAX_IN order by exec_summ.start_time; ELSE -- target case open cursor for select exec_summ.execution_id ,exec_summ.status ,exec_summ.start_time ,exec_summ.end_time ,(( DECODE ( exec_summ.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, exec_summ.end_time ) -exec_summ.start_time)*24*60*60) run_time_seconds ,exec_summ.timezone_region ,MAX(targets.target_name) target_name ,DECODE(MAX(targets.target_type) ,MIN(targets.target_type) ,MAX(targets.target_type) ,'Mixed') target_type ,DECODE(MAX(targets.type_display_name) ,MIN(targets.type_display_name) ,MAX(targets.type_display_name) ,'Mixed') type_display_name ,COUNT(targets.target_name) target_count from mgmt_job_exec_summary exec_summ ,mgmt_job_ext_targets exec_targets ,mgmt_targets targets where exec_targets.execution_id = exec_summ.execution_id and exec_targets.target_guid = targets.target_guid and targets.target_name like TARGET_NAME_IN -- and exec_summ.job_id = JOB_ID_IN and exec_summ.scheduled_time = l_scheduled_at -- and exec_summ.status NOT IN (MGMT_JOB_ENGINE.WAITING_STATUS, MGMT_JOB_ENGINE.DELETE_PENDING_STATUS) and ( (exec_summ.status = STATUS_IN AND STATUS_IN >= 0) OR (exec_summ.status_bucket = STATUS_IN AND STATUS_IN <0) OR (NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) OR ((NVL(STATUS_IN, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) AND exec_summ.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED) ) ) -- todo - convert to new status values and rm the get_status_2_from_status() call -- and STATUS_MIN_IN <= MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- and MGMT_JOB_ENGINE.get_status_2_from_status(exec_summ.status) -- <= STATUS_MAX_IN group by exec_summ.execution_id ,exec_summ.status ,exec_summ.start_time ,exec_summ.end_time ,exec_summ.timezone_region order by exec_summ.start_time; END IF; end open_job_executions_in_run; end MGMT_JOB_UI_EXECS; / show errors; /