APPS.FND_OAM_BF_UTIL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 26

	select fnd_global.conc_request_id into v_conc_req_id from dual;

Line: 28

	  select fcr.requested_by into v_userid
	    from fnd_concurrent_requests fcr
	    where fcr.request_id = v_conc_req_id;

Line: 32

	  select fnd_global.user_id into v_userid from dual;

Line: 54

	select 1 into v_x
	  from fnd_oam_bf_wit_info
	  where item_type = p_item_type;

Line: 58

	-- update
	update fnd_oam_bf_wit_info
		set count_errored_items = p_count_errored_items,
		    count_active_items = p_count_active_items,
		    last_updated_by = v_userid,
		    last_update_date = sysdate,
		    last_update_login = 0
		where item_type = p_item_type;

Line: 68

		insert into fnd_oam_bf_wit_info (
			item_type,
			count_errored_items,
			count_active_items,
			created_by,
			creation_date,
			last_updated_by,
			last_update_date,
			last_update_login)
		values (
			p_item_type,
			p_count_errored_items,
			p_count_active_items,
			v_userid,
			sysdate,
			v_userid,
			sysdate,
			0);

Line: 107

	select 1 into v_x
	  from fnd_oam_bf_comp_info
	  where component_type = p_component_type
	  and component_appl_id = p_component_appl_id
	  and component_id = p_component_id;

Line: 113

	-- update
	update fnd_oam_bf_comp_info
		set count_alerts = p_count_alerts,
		    count_errored_requests = p_count_errored_requests,
		    setup_status = p_setup_status,
		    test_status = p_test_status,
		    diagnostic_test_status = p_diagnostic_test_status,
		    count_running_requests = p_count_running_requests,
		    count_form_sessions = p_count_form_sessions,
		    count_ssf_sessions = p_count_ssf_sessions,
		    last_updated_by = v_userid,
		    last_update_date = sysdate,
		    last_update_login = 0
		where component_type = p_component_type
	  	    and component_appl_id = p_component_appl_id
	  	    and component_id = p_component_id;

Line: 131

		insert into fnd_oam_bf_comp_info (
			component_type,
			component_appl_id,
			component_id,
			count_alerts,
			count_errored_requests,
			setup_status,
			test_status,
			diagnostic_test_status,
			count_running_requests,
			count_form_sessions,
			count_ssf_sessions,
			created_by,
			creation_date,
			last_updated_by,
			last_update_date,
			last_update_login)
		values (
			p_component_type,
			p_component_appl_id,
			p_component_id,
			p_count_alerts,
			p_count_errored_requests,
			p_setup_status,
			p_test_status,
			p_diagnostic_test_status,
			p_count_running_requests,
			p_count_form_sessions,
			p_count_ssf_sessions,
			v_userid,
			sysdate,
			v_userid,
			sysdate,
			0);

Line: 186

	select 1 into v_x
	  from fnd_oam_bf_rollup_info
	  where biz_flow_key = p_biz_flow_key;

Line: 190

	-- update
	update fnd_oam_bf_rollup_info
		set count_alerts = p_count_alerts,
		    count_errored_requests = p_count_errored_requests,
		    count_errored_work_items = p_count_errored_work_items,
		    setup_status = p_setup_status,
		    test_status = p_test_status,
		    diagnostic_test_status = p_diagnostic_test_status,
		    count_running_requests = p_count_running_requests,
		    count_form_sessions = p_count_form_sessions,
		    count_ssf_sessions = p_count_ssf_sessions,
		    count_active_work_items = p_count_active_work_items,
		    last_updated_by = v_userid,
		    last_update_date = sysdate,
		    last_update_login = 0
		where biz_flow_key = p_biz_flow_key;

Line: 208

		insert into fnd_oam_bf_rollup_info (
			biz_flow_key,
			count_alerts,
			count_errored_requests,
			count_errored_work_items,
			setup_status,
			test_status,
			diagnostic_test_status,
			count_running_requests,
			count_form_sessions,
			count_ssf_sessions,
			count_active_work_items,
			created_by,
			creation_date,
			last_updated_by,
			last_update_date,
			last_update_login)
		values (
			p_biz_flow_key,
			p_count_alerts,
			p_count_errored_requests,
			p_count_errored_work_items,
			p_setup_status,
			p_test_status,
			p_diagnostic_test_status,
			p_count_running_requests,
			p_count_form_sessions,
			p_count_ssf_sessions,
			p_count_active_work_items,
			v_userid,
			sysdate,
			v_userid,
			sysdate,
			0);

Line: 273

	  select distinct component_type, component_appl_id, component_id
		from fnd_oam_bf_comp
	  union
	  select distinct component_type, component_appl_id, component_id
		from fnd_oam_bf_comp_cust;

Line: 279

	  select distinct item_type
		from fnd_oam_bf_wit
	  union
	  select distinct item_type
		from fnd_oam_bf_wit_cust ;

Line: 297

	    select count(distinct(flue.unique_exception_id))
	      into v_alerts
	      from fnd_log_unique_exceptions flue,
	        fnd_log_messages flm,
		fnd_log_exceptions fle,
		fnd_log_transaction_context fltc
	      where fltc.transaction_context_id = flm.transaction_context_id
		and flm.log_sequence = fle.log_sequence
		and fle.unique_exception_id = flue.unique_exception_id
		and fltc.component_type = comp.component_type
		and fltc.component_id = comp.component_id
		and nvl(fltc.component_appl_id, -1) = comp.component_appl_id
		and flue.status in ('O','N');

Line: 314

	      select count(*) into v_err_requests
		from fnd_concurrent_requests
		where phase_code='C'
		and status_code='E'
		and concurrent_program_id = comp.component_id
		and program_application_id = comp.component_appl_id
		and actual_completion_date > sysdate - 1;

Line: 322

	      select count(*) into v_running_requests
		from fnd_concurrent_requests
		where phase_code = 'R'
		and concurrent_program_id = comp.component_id
		and program_application_id = comp.component_appl_id;

Line: 331

	      select count(*) into v_form_sessions
		from fnd_form_sessions_v
		where form_id = comp.component_id
		and form_appl_id = comp.component_appl_id;

Line: 340

	      select count(*) into v_ssf_sessions
		from icx_sessions
		where function_id = comp.component_id
		and last_connect > sysdate - 1/24;

Line: 368

	    select count(distinct(item_key))
		into v_err_wi
		from wf_item_activity_statuses wias,
		     wf_item_types wit
		where wias.activity_status = 'ERROR'
		     and wias.item_type = wit.name
		     and wias.item_type = wit_x.item_type;

Line: 376

	   select count(distinct(item_key))
		into v_active_wi
		from wf_items i
		where i.end_date is null
		and i.item_type = wit_x.item_type;

Line: 421

	select ba.biz_flow_child_key biz_flow_key from fnd_oam_bf_assoc ba
	  where ba.biz_flow_parent_key = p_flow_key
	and ((ba.monitored_flag='Y' and 1 not in (
	select count(*)
	  from fnd_oam_bf_assoc_cust cust
	  where cust.biz_flow_child_key = ba.biz_flow_child_key
	    and cust.biz_flow_parent_key = ba.biz_flow_parent_key
	    and cust.monitored_flag = 'N'))
	or (ba.monitored_flag='N' and 1 in (
	select count(*)
	  from fnd_oam_bf_assoc_cust cust
	  where cust.biz_flow_child_key = ba.biz_flow_child_key
	    and cust.biz_flow_parent_key = ba.biz_flow_parent_key
	    and cust.monitored_flag='Y'))
	)
	union
	select ba.biz_flow_child_key biz_flow_key from fnd_oam_bf_assoc_cust ba
	  where ba.biz_flow_parent_key = p_flow_key
	  and ba.monitored_flag = 'Y'
	  and ba.biz_flow_child_key not in
		(select x.biz_flow_child_key from fnd_oam_bf_assoc x
		   where x.biz_flow_parent_key = p_flow_key);

Line: 446

	select c.component_type, c.component_appl_id, c.component_id
	   from fnd_oam_bf_comp c
	   where c.biz_flow_key = p_flow_key
	and ((c.monitored_flag='Y' and 1 not in (
	select count(*)
	  from fnd_oam_bf_comp_cust cust
	  where cust.component_type = c.component_type
	    and cust.component_appl_id = c.component_appl_id
	    and cust.component_id = c.component_id
	    and cust.biz_flow_key = c.biz_flow_key
	    and cust.monitored_flag='N'))
	or (c.monitored_flag = 'N' and 1 in (
	select count(*)
	  from fnd_oam_bf_comp_cust cust
	  where cust.component_type = c.component_type
	    and cust.component_appl_id = c.component_appl_id
	    and cust.component_id = c.component_id
	    and cust.biz_flow_key = c.biz_flow_key
	    and cust.monitored_flag='Y'))
	)
    	union
	select c.component_type, c.component_appl_id, c.component_id
	  from fnd_oam_bf_comp_cust c
	  where c.biz_flow_key = p_flow_key
	  and c.monitored_flag = 'Y'
	  and c.component_type || ':' ||
	      c.component_appl_id || ':' ||
	      c.component_id not in
		(select x.component_type || ':' ||
		        x.component_appl_id || ':' ||
		 	x.component_id
		 from fnd_oam_bf_comp x
		 where x.biz_flow_key = p_flow_key);

Line: 482

	select w.item_type from fnd_oam_bf_wit w
	  where w.biz_flow_key = p_flow_key
	and ((w.monitored_flag = 'Y' and 1 not in (
	select count(*) from fnd_oam_bf_wit_cust cust
	 where cust.item_type = w.item_type
	  and cust.biz_flow_key = w.biz_flow_key
	  and cust.monitored_flag = 'N'))
	or (w.monitored_flag = 'N' and 1 in (
	select count(*) from fnd_oam_bf_wit_cust cust
	 where cust.item_type = w.item_type
	  and cust.biz_flow_key = w.biz_flow_key
	  and cust.monitored_flag = 'Y'))
	)
	union
	select w.item_type from fnd_oam_bf_wit_cust w
	where w.biz_flow_key = p_flow_key
	and w.monitored_flag = 'Y'
	and w.item_type not in
		(select x.item_type from fnd_oam_bf_wit x
		  where x.biz_flow_key = p_flow_key);

Line: 516

	    select nvl(count_alerts,0), nvl(count_errored_requests,0),
		 nvl(count_running_requests,0), nvl(count_form_sessions,0),
		 nvl(count_ssf_sessions,0)
		into v_temp_alerts, v_temp_err_req, v_temp_running_req,
		     v_temp_form_sessions, v_temp_ssf_sessions
		from fnd_oam_bf_comp_info
		where component_type = c.component_type
		and component_appl_id = c.component_appl_id
		and component_id = c.component_id;

Line: 545

	    select nvl(count_errored_items,0), nvl(count_active_items,0)
		into v_temp_err_wi, v_temp_active_wi
		from fnd_oam_bf_wit_info
		where item_type = w.item_type;

Line: 561

	     select
		   nvl(fbc.monitored_flag,fb.monitored_flag)
	      into v_abs_cust_mflag
	      from fnd_oam_bf fb, fnd_oam_bf_cust fbc
	      where fb.biz_flow_key = fbc.biz_flow_key (+)
		and fb.biz_flow_key=fl.biz_flow_key;

Line: 570

	       select fbc.monitored_flag
	        into v_abs_cust_mflag
	        from fnd_oam_bf_cust fbc
	        where fbc.biz_flow_key=fl.biz_flow_key;

Line: 629

	-- finally update the out parameters and load the info into
	-- fnd_oam_bf_rollup_info
	p_count_alerts := v_count_alerts;

Line: 667

	  select
	      fb.biz_flow_key biz_flow_key
	      from fnd_oam_bf fb, fnd_oam_bf_cust fbc
	      where fb.biz_flow_key = fbc.biz_flow_key (+)
		and fb.is_top_level = 'Y'
		and nvl(fbc.monitored_flag,fb.monitored_flag) = 'Y'
	  union
	  select fbc.biz_flow_key biz_flow_key
	      from fnd_oam_bf_cust fbc
	      where fbc.monitored_flag = 'Y'
		and fbc.is_top_level = 'Y'
		and fbc.biz_flow_key not in (
		   select fb.biz_flow_key from fnd_oam_bf fb
			where fb.is_top_level = 'Y');

Line: 755

  PROCEDURE update_bf_monitored_flag (
	p_flow_key varchar2,
	p_new_flag varchar2)
  IS
    v_userid number;

Line: 765

      select 1 into v_cust_flag from fnd_oam_bf_cust
        where biz_flow_key = p_flow_key;

Line: 798

	-- now update fnd_oam_bf
    	update fnd_oam_bf set
		monitored_flag = p_new_flag,
		last_update_date = sysdate,
		last_updated_by = v_userid
		where biz_flow_key = p_flow_key;

Line: 805

	-- now update fnd_oam_bf_cust
    	update fnd_oam_bf_cust set
		monitored_flag = p_new_flag,
		last_update_date = sysdate,
		last_updated_by = v_userid
		where biz_flow_key = p_flow_key;

Line: 816

  END update_bf_monitored_flag;

Line: 827

  PROCEDURE update_bf_monitored_flag (
	p_parent_flow_key varchar2,
	p_child_flow_key varchar2,
	p_new_flag varchar2)
  IS
    v_userid number;

Line: 838

      select 1 into v_cust_flag from fnd_oam_bf_assoc_cust
        where biz_flow_parent_key = p_parent_flow_key
	and biz_flow_child_key = p_child_flow_key;

Line: 865

	-- now update fnd_oam_bf_assoc
    	update fnd_oam_bf_assoc set
		monitored_flag = p_new_flag,
		last_update_date = sysdate,
		last_updated_by = v_userid
		where biz_flow_parent_key = p_parent_flow_key
		and biz_flow_child_key = p_child_flow_key;

Line: 873

	-- now update fnd_oam_bf_assoc_cust
    	update fnd_oam_bf_assoc_cust set
		monitored_flag = p_new_flag,
		last_update_date = sysdate,
		last_updated_by = v_userid
		where biz_flow_parent_key = p_parent_flow_key
		and biz_flow_child_key = p_child_flow_key;

Line: 884

  END update_bf_monitored_flag;

Line: 896

  PROCEDURE update_comp_monitored_flag (
	p_parent_flow_key varchar2,
	p_component_type varchar2,
        p_component_appl_id number,
	p_component_id number,
	p_new_flag varchar2)
  IS
    v_userid number;

Line: 909

      select 1 into v_cust_flag from fnd_oam_bf_comp_cust
        where biz_flow_key = p_parent_flow_key
	and component_type = p_component_type
	and component_appl_id = p_component_appl_id
	and component_id = p_component_id;

Line: 944

	-- now update regular table
    	update fnd_oam_bf_comp set
		monitored_flag = p_new_flag,
		last_update_date = sysdate,
		last_updated_by = v_userid
		where biz_flow_key = p_parent_flow_key
		and component_type = p_component_type
		and component_appl_id = p_component_appl_id
		and component_id = p_component_id;

Line: 954

  	-- now update cust table
    	update fnd_oam_bf_comp_cust set
		monitored_flag = p_new_flag,
		last_update_date = sysdate,
		last_updated_by = v_userid
		where biz_flow_key = p_parent_flow_key
		and component_type = p_component_type
		and component_appl_id = p_component_appl_id
		and component_id = p_component_id;

Line: 969

  END update_comp_monitored_flag;

Line: 977

  PROCEDURE update_wit_monitored_flag (
	p_parent_flow_key varchar2,
	p_item_type varchar2,
	p_new_flag varchar2)
  IS
    v_userid number;

Line: 988

      select 1 into v_cust_flag from fnd_oam_bf_wit_cust
        where biz_flow_key = p_parent_flow_key
	and item_type = p_item_type;

Line: 1016

    	update fnd_oam_bf_wit set
		monitored_flag = p_new_flag,
		last_update_date = sysdate,
		last_updated_by = v_userid
		where biz_flow_key = p_parent_flow_key
		and item_type = p_item_type;

Line: 1023

	-- now update fnd_oam_bf_assoc_cust
    	update fnd_oam_bf_wit_cust set
		monitored_flag = p_new_flag,
		last_update_date = sysdate,
		last_updated_by = v_userid
		where biz_flow_key = p_parent_flow_key
		and item_type = p_item_type;

Line: 1036

  END update_wit_monitored_flag;