Rem Rem $Header: jobs_ui_runs_pkgbody.sql 09-oct-2006.14:52:18 rdabbott Exp $ Rem Rem jobs_ui_runs_pkgbody.sql Rem Rem Copyright (c) 2003, 2006, Oracle. All rights reserved. Rem Rem NAME Rem jobs_ui_runs_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem 1. Currently we return 'Mixed' if the set of target types is not Rem the same across all executions in the run. Rem We should define a constant in job_types or somewhere and use that Rem and we should catch that constant in the UI and display an NLS string. Rem 2. 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 10/09/06 - Backport rdabbott_bug-5478937 from main Rem rdabbott 08/15/06 - Backport rdabbott_bug-5366754 from main Rem rdabbott 09/06/06 - fix bug 5478937: rm hints from job runs query Rem rdabbott 07/07/06 - fix 5366754 - see jobs on hidden targets Rem rdabbott 08/29/05 - rm extra join Rem rdabbott 08/25/05 - fix 4364896: add cases for no job, no tgt info 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 pkantawa 02/25/05 - Fix 4162837: improve performance Rem dcawley 02/21/05 - Replace enter super user mode Rem pkantawa 01/10/05 - Change runs 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 pkantawa 12/13/04 - Return target name instead of display name Rem skini 10/27/04 - Versioning changes, part 2 Rem pkantawa 10/12/04 - account for Multi Task jobs in queries Rem ramalhot 08/24/04 - cutover to new assoc tables Rem skini 07/29/04 - Filter out waiting executions from runs page 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 12/06/03 - fix 3262649: rm outer join Rem rdabbott 12/05/03 - Fix 3272738: show one exec count for non Rem jberesni multiplexed jobs Rem rdabbott 11/17/03 - fix 3260449: security - only show jobs on Rem targets the user can view Rem rdabbott 11/15/03 - Fix 32566687: all query isneeds order by Rem rdabbott 11/07/03 - jb merge comments for 3238484 Rem rdabbott 11/05/03 - fix 3238484: return target type for a run Rem jberesni 10/31/03 - fix/tune targetless jobs Rem jberesni 10/28/03 - JobRunObjType.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 body MGMT_JOB_UI_RUNS as TYPE TName_rectype is RECORD (target_name mgmt_targets.target_name%TYPE ,target_type mgmt_targets.target_type%TYPE); --------------------------------------------------- -- forward declaration of SLPA -- PROCEDURE assert (bool_IN IN BOOLEAN ,msg_IN IN VARCHAR2 := null); ---------------------------------------------------- -- guid_OK -- helper function to validate GUIDs as valid -- varchar-hex representations of RAW(16) -- function guid_OK (guid_in in varchar2) return boolean is l_guid RAW(16); begin l_guid := HEXTORAW(guid_in); if l_guid is not null then return TRUE; else return FALSE; end if; exception when others then return FALSE; end guid_OK; -------------------------------------------------- -- helper function to verify non-wild input -- function nice_string (string_in varchar2) return boolean is begin if length(translate(string_in,'3%','3')) > 0 and substr(string_in,1,1) not in ('_','%') then return true; else return false; end if; end nice_string; ------------------------------------------------------ --compute target category based on target type -- FUNCTION compute_category_from_type(p_target_type_IN IN VARCHAR2) RETURN NUMBER IS l_target_category NUMBER := CATEGORY_TARGETS; l_target_type MGMT_TARGETS.target_type%TYPE; BEGIN --ASSUMPTION: In 10.2, -- a job can be submitted against one type and run on another -- for example a group and it's members -- or a job can be submitted and execute against the same type -- for example a database or host --if there are jobs executed against this target type, return CATEGORY_TARGETS --else return CATEGORY_GROUPS IF nice_string(p_target_type_IN) THEN BEGIN SELECT t.target_type INTO l_target_type FROM MGMT_TARGETS t, MGMT_JOB_TARGET jt, MGMT_JOB j WHERE jt.execution_id!=MGMT_JOB_ENGINE.NO_EXECUTION AND jt.target_guid=t.target_guid AND t.target_type=p_target_type_IN AND jt.job_id=j.job_id AND j.is_library+0=0 AND j.system_job+0=0 AND rownum=1; EXCEPTION WHEN no_data_found THEN l_target_category := CATEGORY_GROUPS; END; END IF; return l_target_category; END; ---------------------------------------------- -- open_jobruns_status -- don't filter on job or target info -- currently supports CATEGORY_TARGETS only -- PROCEDURE open_jobruns_status (target_category_IN IN integer := CATEGORY_TARGETS ,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 ,jobruns_CV IN OUT jobruns_cvtype ) is begin IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('in open_jobruns_status:'||status_IN, MGMT_JOB_ENGINE.MODULE_NAME); END IF; -- preconditions -- only allow target name filtering assert(TARGET_CATEGORY_IN = CATEGORY_TARGETS,'open_jobruns_status:CATEGORY=TARGETS'); open jobruns_CV for SELECT JobSubmission.job_id ,JobSubmission.job_name ,JobSubmission.job_owner ,JobSubmission.job_type ,exec_summ.scheduled_time ,JobSubmission.frequency_code ,MAX(exec_summ.timezone_region) as timezone_region ,JobSubmission.timezone_info ,JobSubmission.target_name ,JobSubmission.target_type ,JobSubmission.type_display_name ,JobSubmission.submit_count as target_count ,exec_summ.status_bucket as status_bucket ,COUNT(DISTINCT exec_summ.execution_id) as status_bucket_count ,MAX(exec_summ.execution_id) as sample_exec_id ,JobSubmission.job_type_category FROM -- >----------------------> assemble job submission info (select 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 ,decode(COUNT(*),1,MAX(targets.target_name),'('||COUNT(*)||')') as target_name ,DECODE(MAX(targets.target_type) ,MIN(targets.target_type) ,MAX(targets.target_type) ,'Mixed') as target_type ,DECODE(MAX(targets.type_display_name) ,MIN(targets.type_display_name) ,MAX(targets.type_display_name) ,'Mixed') as type_display_name ,COUNT(*) as submit_count ,jobtype_info.job_type_category as job_type_category from mgmt_job jobs ,mgmt_job_target submit_targets ,mgmt_targets targets ,mgmt_job_schedule schedule ,mgmt_job_type_info jobtype_info ,mgmt_job_type_max_versions max_versions where submit_targets.execution_id = MGMT_JOB_ENGINE.NO_EXECUTION and submit_targets.job_id = jobs.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 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 -- group by Jobs.job_id ,jobtype_info.job_type_category ) JobSubmission -- >------------------------------------> join in execution info ,mgmt_job jobs ,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 = jobs.job_id and exec_targets.target_guid = target.target_guid -- and JobSubmission.job_id = jobs.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 ,JobSubmission.job_name ,JobSubmission.job_owner ,JobSubmission.job_type ,exec_summ.scheduled_time ,JobSubmission.frequency_code ,JobSubmission.timezone_info ,JobSubmission.target_name ,JobSubmission.target_type ,JobSubmission.type_display_name ,JobSubmission.submit_count ,exec_summ.status_bucket ,JobSubmission.job_type_category ORDER BY exec_summ.scheduled_time DESC ,JobSubmission.job_id ,exec_summ.status_bucket; end open_jobruns_status; ---------------------------------------------- ---------------------------------------------- -- open_jobruns_jobname -- currently supports CATEGORY_TARGETS only -- PROCEDURE open_jobruns_jobname (job_name_IN IN varchar2 ,job_type_IN IN varchar2 := '%' ,job_owner_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 ,target_name_IN IN varchar2 := '%' ,target_type_IN IN varchar2 := '%' ,target_category_IN IN integer := CATEGORY_TARGETS ,jobruns_CV IN OUT jobruns_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 -- preconditions assert( nice_string(JOB_NAME_IN) or nice_string(JOB_OWNER_IN) or nice_string(JOB_TYPE_IN) ,'open_jobruns_jobname:nice_string(JOB_NAME_IN|OWNER|TYPE)' ); -- only allow target name filtering assert(TARGET_CATEGORY_IN = CATEGORY_TARGETS,'open_jobruns_jobname:CATEGORY=TARGETS'); --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 jobruns_CV for SELECT --+ ordered index(exec_targets) index(exec_summ) JobSubmission.job_id ,JobSubmission.job_name ,JobSubmission.job_owner ,JobSubmission.job_type ,exec_summ.scheduled_time ,JobSubmission.frequency_code ,MAX(exec_summ.timezone_region) as timezone_region ,JobSubmission.timezone_info ,JobSubmission.target_name ,JobSubmission.target_type ,JobSubmission.type_display_name ,JobSubmission.submit_count as target_count ,exec_summ.status_bucket as status_bucket ,COUNT(DISTINCT exec_summ.execution_id) as status_bucket_count ,MAX(exec_summ.execution_id) as sample_exec_id ,JobSubmission.job_type_category FROM -- >----------------------> assemble job submission info (select --+ index(jobs) index(submit_targets) 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 ,decode(COUNT(*),1,MAX(targets.target_name),'('||COUNT(*)||')') as target_name ,DECODE(MAX(targets.target_type) ,MIN(targets.target_type) ,MAX(targets.target_type) ,'Mixed') as target_type ,DECODE(MAX(targets.type_display_name) ,MIN(targets.type_display_name) ,MAX(targets.type_display_name) ,'Mixed') as type_display_name ,COUNT(*) as submit_count ,jobtype_info.job_type_category as job_type_category from mgmt_job jobs ,mgmt_job_target submit_targets ,mgmt_targets targets ,mgmt_job_schedule schedule ,mgmt_job_type_info jobtype_info ,mgmt_job_type_max_versions max_versions where submit_targets.execution_id = MGMT_JOB_ENGINE.NO_EXECUTION and submit_targets.job_id = jobs.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 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 jobtype_info.job_type_category in (l_category_a, l_category_b) -- and jobs.job_name like JOB_NAME_IN and jobs.job_type like l_job_type_in and jobs.job_owner like JOB_OWNER_IN -- group by Jobs.job_id ,jobtype_info.job_type_category ) JobSubmission -- >------------------------------------> join in execution info ,mgmt_job jobs ,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 = jobs.job_id and exec_targets.target_guid = target.target_guid -- and JobSubmission.job_id = exec_targets.job_id -- and jobs.job_name like JOB_NAME_IN and jobs.job_type like l_job_type_in and jobs.job_owner like JOB_OWNER_IN and JobSubmission.job_id = jobs.job_id -- and target.target_name like TARGET_NAME_IN and target.target_type like TARGET_TYPE_IN -- 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 ,JobSubmission.job_name ,JobSubmission.job_owner ,JobSubmission.job_type ,exec_summ.scheduled_time ,JobSubmission.frequency_code ,JobSubmission.timezone_info ,JobSubmission.target_name ,JobSubmission.target_type ,JobSubmission.type_display_name ,JobSubmission.submit_count ,exec_summ.status_bucket ,JobSubmission.job_type_category ORDER BY exec_summ.scheduled_time DESC ,JobSubmission.job_id ,exec_summ.status_bucket; end open_jobruns_jobname; ---------------------------------------------- -- When JOB_ID known -- currently supports CATEGORY_TARGETS only -- PROCEDURE open_jobruns_jobid (job_id_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 ,target_name_IN IN varchar2 := '%' ,target_type_IN IN varchar2 := '%' ,target_category_IN IN integer := CATEGORY_TARGETS ,jobruns_CV IN OUT jobruns_cvtype ) is begin -- preconditions assert(guid_OK(JOB_ID_IN),'open_jobruns_jobid:guid_OK(job_id_in)'); -- only allow target name filtering assert(TARGET_CATEGORY_IN = CATEGORY_TARGETS,'open_jobruns_jobid:CATEGORY=TARGETS'); open jobruns_CV for SELECT --+ index(exec_targets) index(exec_summ) JobSubmission.job_id ,JobSubmission.job_name ,JobSubmission.job_owner ,JobSubmission.job_type ,exec_summ.scheduled_time ,JobSubmission.frequency_code ,MAX(exec_summ.timezone_region) as timezone_region ,JobSubmission.timezone_info ,JobSubmission.target_name ,JobSubmission.target_type ,JobSubmission.type_display_name ,JobSubmission.submit_count as target_count ,exec_summ.status_bucket as status_bucket ,COUNT(DISTINCT exec_summ.execution_id) as status_bucket_count ,MAX(exec_summ.execution_id) as sample_exec_id ,JobSubmission.job_type_category FROM -- >----------------------> assemble job submission info (select --+ index(jobs) index(submit_targets) 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 ,decode(COUNT(*),1,MAX(targets.target_name),'('||COUNT(*)||')') as target_name ,DECODE(MAX(targets.target_type) ,MIN(targets.target_type) ,MAX(targets.target_type) ,'Mixed') as target_type ,DECODE(MAX(targets.type_display_name) ,MIN(targets.type_display_name) ,MAX(targets.type_display_name) ,'Mixed') as type_display_name ,COUNT(*) as submit_count ,jobtype_info.job_type_category as job_type_category from mgmt_job jobs ,mgmt_job_target submit_targets ,mgmt_targets targets ,mgmt_job_schedule schedule ,mgmt_job_type_info jobtype_info ,mgmt_job_type_max_versions max_versions where submit_targets.execution_id = MGMT_JOB_ENGINE.NO_EXECUTION and submit_targets.job_id = HEXTORAW(JOB_ID_IN) and jobs.job_id = HEXTORAW(JOB_ID_IN) 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 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 -- group by Jobs.job_id ,jobtype_info.job_type_category ) JobSubmission -- >------------------------------------> join in execution info ,mgmt_job_ext_targets exec_targets ,mgmt_job_exec_summary exec_summ ,mgmt_targets target WHERE scheduled_time >= sysdate + DAYS_IN and exec_targets.execution_id = exec_summ.execution_id and exec_targets.job_id = HEXTORAW(JOB_ID_IN) and exec_summ.job_id = HEXTORAW(JOB_ID_IN) and exec_targets.target_guid = target.target_guid -- and target.target_name like TARGET_NAME_IN and target.target_type like TARGET_TYPE_IN -- -- 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 ,JobSubmission.job_name ,JobSubmission.job_owner ,JobSubmission.job_type ,exec_summ.scheduled_time ,JobSubmission.frequency_code ,JobSubmission.timezone_info ,JobSubmission.target_name ,JobSubmission.target_type ,JobSubmission.type_display_name ,JobSubmission.submit_count ,exec_summ.status_bucket ,JobSubmission.job_type_category ORDER BY exec_summ.scheduled_time DESC ,JobSubmission.job_id ,exec_summ.status_bucket; end open_jobruns_jobid; ---------------------------------- -- open_jobruns_group -- supports CATEGORY_GROUPS only -- PROCEDURE open_jobruns_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 ,jobruns_CV IN OUT jobruns_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 --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 jobruns_CV for SELECT --+ index(exec_targets) index(exec_summ) JobSubmission.job_id ,JobSubmission.job_name ,JobSubmission.job_owner ,JobSubmission.job_type ,exec_summ.scheduled_time ,JobSubmission.frequency_code ,MAX(exec_summ.timezone_region) as timezone_region ,JobSubmission.timezone_info ,JobSubmission.target_name ,JobSubmission.target_type ,JobSubmission.type_display_name ,JobSubmission.submit_count as target_count ,exec_summ.status_bucket as status_bucket ,COUNT(DISTINCT exec_summ.execution_id) as status_bucket_count ,MAX(exec_summ.execution_id) as sample_exec_id ,JobSubmission.job_type_category FROM -- >----------------------> assemble group submission info (select --+ index(submit_targets) index(jobs) 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 ,decode(COUNT(*),1,MAX(targets.target_name),'('||COUNT(*)||')') as target_name ,DECODE(MAX(targets.target_type) ,MIN(targets.target_type) ,MAX(targets.target_type) ,'Mixed') as target_type ,DECODE(MAX(targets.type_display_name) ,MIN(targets.type_display_name) ,MAX(targets.type_display_name) ,'Mixed') as type_display_name ,COUNT(*) as submit_count ,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 = MGMT_JOB_ENGINE.NO_EXECUTION and submit_targets.job_id = jobs.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 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 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 -- >------------------------------------> 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 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 ,JobSubmission.job_name ,JobSubmission.job_owner ,JobSubmission.job_type ,exec_summ.scheduled_time ,JobSubmission.frequency_code ,JobSubmission.timezone_info ,JobSubmission.target_name ,JobSubmission.target_type ,JobSubmission.type_display_name ,JobSubmission.submit_count ,exec_summ.status_bucket ,JobSubmission.job_type_category ORDER BY exec_summ.scheduled_time DESC ,JobSubmission.job_id ,exec_summ.status_bucket; end open_jobruns_group; ---------------------------------------------- -- open_jobruns_targetless -- supports CATEGORY_SYS_TARGETLESS only -- PROCEDURE open_jobruns_targetless (job_name_IN IN varchar2 ,job_type_IN IN varchar2 := '%' ,job_owner_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 ,jobruns_CV IN OUT jobruns_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 -- preconditions -- NOTE: although all targetless jobs have one execution per run in general -- we must still assemble the run info in case an execution was retried -- since a retried execution would lead to multiple executions in the run --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 jobruns_CV for select --+ index(exec_summ) index(jobs) jobs.job_id ,jobs.job_name ,jobs.job_owner ,jobs.job_type ,exec_summ.scheduled_time ,schedule.frequency_code ,MAX(exec_summ.timezone_region) as timezone_region ,schedule.timezone_info ,null as target_name ,null as target_type ,null as type_display_name ,0 as target_count ,exec_summ.status_bucket as status_bucket ,COUNT(DISTINCT exec_summ.execution_id) as status_bucket_count ,MAX(exec_summ.execution_id) as sample_exec_id ,jobtype_info.job_type_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 = MGMT_JOB_ENGINE.NO_EXECUTION ) -- 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 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 jobs.job_id ,jobs.job_name ,jobs.job_owner ,jobs.job_type ,exec_summ.scheduled_time ,schedule.frequency_code ,schedule.timezone_info ,exec_summ.status_bucket ,jobtype_info.job_type_category order by exec_summ.scheduled_time DESC ,jobs.job_id ,exec_summ.status_bucket; end open_jobruns_targetless; ---------------------------------- -- JobRunRowset function -- assembles execution summary information -- (excluding job submission data) into an -- object table -- for CATEGORY_TARGETS_IN_GROUP -- and CATEGORY_TARGETS -- -- function JobRunRowSet (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_category_IN IN integer := CATEGORY_TARGETS ,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 ) return JobRunObjTbl is l_return_curvar jobruns_cvtype; 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; l_JobRunObjTbl JobRunObjTbl; begin -- preconditions assert(TARGET_CATEGORY_IN in (CATEGORY_TARGETS,CATEGORY_TARGETS_IN_GROUP) ,'JobRunRowset:CATEGORY=(TARGETS|TARGETS_IN_GROUP)'); assert(TARGET_NAME_IN is not null,'JobRunsRowset:TARGET_NAME_IN not null'); -- assert( nice_string(TARGET_NAME_IN) -- or nice_string(TARGET_TYPE_IN) -- or STATUS_IN != MGMT_JOB_ENGINE.STATUS_BUCKET_ALL -- ,'JobRunsRowset:TARGET_NAME,TARGET_TYPE or STATUS filter'); --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; if TARGET_CATEGORY_IN = CATEGORY_TARGETS then select --+ index(exec_summ) index(exec_targets) index(jobs) JobRunObjType (exec_summ.job_id ,MAX(jobs.job_name) ,MAX(jobs.job_owner) ,MAX(jobs.job_type) ,exec_summ.scheduled_time ,MAX(schedule.frequency_code) ,MAX(exec_summ.timezone_region) ,MAX(schedule.timezone_info) ,to_char(null) ,to_char(null) ,to_char(null) ,to_number(null) ,exec_summ.status_bucket ,COUNT(DISTINCT exec_summ.execution_id) ,MAX(exec_summ.execution_id) ,jobtype_info.job_type_category ) bulk collect into l_JobRunObjTbl from mgmt_targets target ,mgmt_job_ext_targets exec_targets ,mgmt_job_exec_summary exec_summ ,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.execution_id = exec_summ.execution_id and exec_targets.job_id = exec_summ.job_id and exec_targets.target_guid = target.target_guid -- 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 target.target_name like TARGET_NAME_IN and target.target_type like TARGET_TYPE_IN -- 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 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.scheduled_time ,exec_summ.status_bucket ,jobtype_info.job_type_category order by exec_summ.scheduled_time DESC ,exec_summ.job_id ,exec_summ.status_bucket; elsif TARGET_CATEGORY_IN = CATEGORY_TARGETS_IN_GROUP then select --+ index(exec_summ) index(exec_targets) index(jobs) JobRunObjType (exec_summ.job_id ,MAX(jobs.job_name) ,MAX(jobs.job_owner) ,MAX(jobs.job_type) ,exec_summ.scheduled_time ,MAX(schedule.frequency_code) ,MAX(exec_summ.timezone_region) ,MAX(schedule.timezone_info) ,to_char(null) ,to_char(null) ,to_char(null) ,to_number(null) ,exec_summ.status_bucket ,COUNT(DISTINCT exec_summ.execution_id) ,MAX(exec_summ.execution_id) ,jobtype_info.job_type_category ) bulk collect into l_JobRunObjTbl from mgmt_flat_target_assoc group_members ,mgmt_targets target ,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 = target.target_guid OR group_members.source_target_guid = target.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 = target.target_guid -- 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.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 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.scheduled_time ,exec_summ.status_bucket ,jobtype_info.job_type_category order by exec_summ.scheduled_time DESC ,exec_summ.job_id ,exec_summ.status_bucket; end if; RETURN l_JobRunObjTbl; end JobRunRowSet; ---------------------------------------------------- -- open_JobRunRowSet -- -- private procedure to take JobRunRowSet generated by -- JobRunRowSet and add job submission target info to -- the set, returning a jobruns_CVtype cursor variable -- procedure open_JobRunRowSet (JobRunRowSet_IN JobRunObjTbl, jobruns_CV IN OUT jobruns_cvtype) is Run_tbl JobRunObjTbl; Submit_tbl JobRunObjTbl; begin Run_tbl := JobRunRowSet_IN; select --+ index(submit_targets) JobRunObjType (jobs.job_id, null,null,null,null,null, null, null ,MAX(targets.target_name) ,DECODE(MAX(targets.target_type) ,MIN(targets.target_type) ,MAX(targets.target_type) ,'Mixed') ,DECODE(MAX(targets.type_display_name) ,MIN(targets.type_display_name) ,MAX(targets.type_display_name) ,'Mixed') ,COUNT(*) ,null,null,null ,jobs.job_type_category ) bulk collect into Submit_tbl from (select distinct job_id, job_type_category from TABLE(CAST (Run_tbl as JobRunObjTbl) ) ) jobs ,mgmt_job_target submit_targets ,mgmt_targets targets where submit_targets.execution_id = MGMT_JOB_ENGINE.NO_EXECUTION and submit_targets.job_id = jobs.job_id and submit_targets.target_guid = targets.target_guid group by jobs.job_id ,jobs.job_type_category; open jobruns_CV for select --+ Run.job_id ,Run.job_name ,Run.job_owner ,Run.job_type ,Run.scheduled_time ,Run.frequency_code ,Run.timezone_region ,Run.timezone_info ,decode(Submit.target_count,1,Submit.target_name ,'('||Submit.target_count||')') as target_name ,Submit.target_type ,Submit.type_display_name ,Submit.target_count ,Run.status_bucket ,Run.status_bucket_count ,Run.sample_exec_id ,Run.job_type_category from TABLE(CAST(Run_tbl as JobRunObjTbl) ) Run ,TABLE(CAST(Submit_tbl as JobRunObjTbl) ) Submit where Submit.job_id = Run.job_id order by Run.scheduled_time DESC ,Run.job_id ,Run.status_bucket; end open_JobRunRowSet; ---------------------------------------------------- -- open_jobruns -- -- Public procedure to support general filtering options -- which calls more specialized procedures for specific -- filter sets and target categories -- 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 -- not used ,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 ) 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_category_IN; IF EMDW_LOG.p_is_debug_set THEN 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('job_id_IN:'||JOB_ID_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('target_name_IN:'||TARGET_NAME_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('target_type_IN:'||TARGET_TYPE_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('target_guid_IN:'||TARGET_GUID_IN,MGMT_JOB_ENGINE.MODULE_NAME); EMDW_LOG.debug('target_category_IN:'||TARGET_CATEGORY_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('ignore_access_IN:'||IGNORE_ACCESS_IN,MGMT_JOB_ENGINE.MODULE_NAME); END IF; -- validate preconditions assert(TARGET_CATEGORY_IN in (-1, CATEGORY_TARGETS, CATEGORY_GROUPS ,CATEGORY_TARGETS_IN_GROUP, CATEGORY_SYS_TARGETLESS) ,'open_jobruns:CATEGORY=valid'); --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 := 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, for now -- todo: allow this usage which means from now forward (scheduled) -- if NVL(DAYS_IN,0) = 0 then RAISE_APPLICATION_ERROR(1234, 'DAYS_IN=0 not supported'); end if; if l_target_category = CATEGORY_GROUPS then open_jobruns_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 ,jobruns_CV => jobruns_CV ); elsif l_target_category IN (CATEGORY_TARGETS, CATEGORY_TARGETS_IN_GROUP) then if l_target_category = CATEGORY_TARGETS then if guid_OK(JOB_ID_IN) then open_jobruns_jobid (job_id_IN => JOB_ID_IN ,days_IN => days_IN ,status_IN => STATUS_IN ,status_min_IN => l_status_min ,status_max_IN => l_status_max ,target_name_IN => l_target_name ,target_type_IN => l_target_type ,target_category_IN => l_target_category ,jobruns_CV => jobruns_CV ); elsif nice_string(JOB_NAME_IN) or nice_string(JOB_OWNER_IN) or nice_string(JOB_TYPE_IN) then open_jobruns_jobname (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 ,target_category_IN => l_target_category ,days_IN => days_IN ,status_IN => status_IN ,status_min_IN => l_status_min ,status_max_IN => l_status_max ,jobruns_CV => jobruns_CV ); else if job_name_IN = '%' and Job_owner_IN = '%' and job_type_IN = '%' and l_target_name = '%' and l_target_type = '%' then open_jobruns_status (target_category_IN => l_target_category ,days_IN => days_IN ,status_IN => status_IN ,status_min_IN => l_status_min ,status_max_IN => l_status_max ,jobruns_CV => jobruns_CV ); else open_JobRunRowSet(JobRunRowSet (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 ,target_category_IN => l_target_category ,days_IN => days_IN ,status_IN => STATUS_IN ,status_min_IN => l_status_min ,status_max_IN => l_status_max ) ,jobruns_CV); end if; end if; -- CATEGORY_TARGETS else -- CATEGORY_TARGETS_IN_GROUP open_JobRunRowSet(JobRunRowSet (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 ,target_category_IN => l_target_category ,days_IN => days_IN ,status_IN => STATUS_IN ,status_min_IN => l_status_min ,status_max_IN => l_status_max ) ,jobruns_CV); end if; else -- CATEGORY_SYS_TARGETLESS open_jobruns_targetless (job_name_IN => job_name_IN ,job_type_IN => job_type_IN ,job_owner_IN => job_owner_IN ,days_IN => days_IN ,status_IN => status_IN ,status_min_IN => l_status_min ,status_max_IN => l_status_max ,jobruns_CV => jobruns_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_jobruns; ----------- SLPA implementation ------------ PROCEDURE assert (bool_IN IN BOOLEAN ,msg_IN IN VARCHAR2 := null) IS BEGIN IF NOT NVL(bool_IN,FALSE) -- fail on null input THEN RAISE_APPLICATION_ERROR ( ASSERTFAIL_C, 'ASSERTFAIL:'|| PKGNAME_C||':'||SUBSTR(msg_IN,1,200) ) ; END IF; END assert; ----------- SLPA implementation ------------ end MGMT_JOB_UI_RUNS; / show errors;