[Home] [Help]
1: package body FND_OAM_BF_UTIL as
2: /* $Header: AFOAMFLB.pls 120.3 2005/08/13 00:49:01 ppradhan noship $ */
3:
4: --
5: -- Debug flag - Set this to FALSE before checking in
51: v_userid number := 0;
52: BEGIN
53: v_userid := get_user_id;
54: select 1 into v_x
55: from fnd_oam_bf_wit_info
56: where item_type = p_item_type;
57:
58: -- update
59: update fnd_oam_bf_wit_info
55: from fnd_oam_bf_wit_info
56: where item_type = p_item_type;
57:
58: -- update
59: update fnd_oam_bf_wit_info
60: set count_errored_items = p_count_errored_items,
61: count_active_items = p_count_active_items,
62: last_updated_by = v_userid,
63: last_update_date = sysdate,
64: last_update_login = 0
65: where item_type = p_item_type;
66: EXCEPTION
67: when no_data_found then
68: insert into fnd_oam_bf_wit_info (
69: item_type,
70: count_errored_items,
71: count_active_items,
72: created_by,
104: v_userid number := 0;
105: BEGIN
106: v_userid := get_user_id;
107: select 1 into v_x
108: from fnd_oam_bf_comp_info
109: where component_type = p_component_type
110: and component_appl_id = p_component_appl_id
111: and component_id = p_component_id;
112:
110: and component_appl_id = p_component_appl_id
111: and component_id = p_component_id;
112:
113: -- update
114: update fnd_oam_bf_comp_info
115: set count_alerts = p_count_alerts,
116: count_errored_requests = p_count_errored_requests,
117: setup_status = p_setup_status,
118: test_status = p_test_status,
127: and component_appl_id = p_component_appl_id
128: and component_id = p_component_id;
129: EXCEPTION
130: when no_data_found then
131: insert into fnd_oam_bf_comp_info (
132: component_type,
133: component_appl_id,
134: component_id,
135: count_alerts,
183: v_userid number := 0;
184: BEGIN
185: v_userid := get_user_id;
186: select 1 into v_x
187: from fnd_oam_bf_rollup_info
188: where biz_flow_key = p_biz_flow_key;
189:
190: -- update
191: update fnd_oam_bf_rollup_info
187: from fnd_oam_bf_rollup_info
188: where biz_flow_key = p_biz_flow_key;
189:
190: -- update
191: update fnd_oam_bf_rollup_info
192: set count_alerts = p_count_alerts,
193: count_errored_requests = p_count_errored_requests,
194: count_errored_work_items = p_count_errored_work_items,
195: setup_status = p_setup_status,
204: last_update_login = 0
205: where biz_flow_key = p_biz_flow_key;
206: EXCEPTION
207: when no_data_found then
208: insert into fnd_oam_bf_rollup_info (
209: biz_flow_key,
210: count_alerts,
211: count_errored_requests,
212: count_errored_work_items,
256: -- - count of running concurrent requests
257: -- - count of active form sessions
258: -- - count of active ssf sessions (last hour)
259: --
260: -- The resulting values will get populated into fnd_oam_bf_comp_info,
261: -- fnd_oam_bf_wit_info tables.
262: --
263: -- Input Arguments
264: --
257: -- - count of active form sessions
258: -- - count of active ssf sessions (last hour)
259: --
260: -- The resulting values will get populated into fnd_oam_bf_comp_info,
261: -- fnd_oam_bf_wit_info tables.
262: --
263: -- Input Arguments
264: --
265: -- Output Arguments
270: PROCEDURE compute_metrics
271: IS
272: cursor c_comps is
273: select distinct component_type, component_appl_id, component_id
274: from fnd_oam_bf_comp
275: union
276: select distinct component_type, component_appl_id, component_id
277: from fnd_oam_bf_comp_cust;
278: cursor c_wits is
273: select distinct component_type, component_appl_id, component_id
274: from fnd_oam_bf_comp
275: union
276: select distinct component_type, component_appl_id, component_id
277: from fnd_oam_bf_comp_cust;
278: cursor c_wits is
279: select distinct item_type
280: from fnd_oam_bf_wit
281: union
276: select distinct component_type, component_appl_id, component_id
277: from fnd_oam_bf_comp_cust;
278: cursor c_wits is
279: select distinct item_type
280: from fnd_oam_bf_wit
281: union
282: select distinct item_type
283: from fnd_oam_bf_wit_cust ;
284: BEGIN
279: select distinct item_type
280: from fnd_oam_bf_wit
281: union
282: select distinct item_type
283: from fnd_oam_bf_wit_cust ;
284: BEGIN
285: -- for each associated components count the
286: -- number of open and new system
287: -- alerts and errored concurrent requests
388: END compute_metrics;
389:
390: --
391: -- Computes the rolled values for the given metrics and loads it into the
392: -- fnd_oam_bf_rollup_info table. Rollup values are based only on
393: -- associations for which the monitored_flag='Y'
394: --
395: -- 1) p_count_alerts - Total count of open/new alerts for this flow
396: -- 2) p_count_err_requests - Total count of errored concurrent requests
406: p_count_form_sessions OUT NOCOPY number,
407: p_count_ssf_sessions OUT NOCOPY number,
408: p_count_active_wi OUT NOCOPY number)
409: IS
410: v_flow_key fnd_oam_bf.biz_flow_key%type := p_flow_key;
411: v_count_alerts number := 0;
412: v_count_err_requests number := 0;
413: v_count_err_wi number := 0;
414: v_count_running_requests number := 0;
417: v_count_active_wi number := 0;
418:
419: -- For all monitored flows under this flow
420: cursor c_monitored_flows is
421: select ba.biz_flow_child_key biz_flow_key from fnd_oam_bf_assoc ba
422: where ba.biz_flow_parent_key = p_flow_key
423: and ((ba.monitored_flag='Y' and 1 not in (
424: select count(*)
425: from fnd_oam_bf_assoc_cust cust
421: select ba.biz_flow_child_key biz_flow_key from fnd_oam_bf_assoc ba
422: where ba.biz_flow_parent_key = p_flow_key
423: and ((ba.monitored_flag='Y' and 1 not in (
424: select count(*)
425: from fnd_oam_bf_assoc_cust cust
426: where cust.biz_flow_child_key = ba.biz_flow_child_key
427: and cust.biz_flow_parent_key = ba.biz_flow_parent_key
428: and cust.monitored_flag = 'N'))
429: or (ba.monitored_flag='N' and 1 in (
427: and cust.biz_flow_parent_key = ba.biz_flow_parent_key
428: and cust.monitored_flag = 'N'))
429: or (ba.monitored_flag='N' and 1 in (
430: select count(*)
431: from fnd_oam_bf_assoc_cust cust
432: where cust.biz_flow_child_key = ba.biz_flow_child_key
433: and cust.biz_flow_parent_key = ba.biz_flow_parent_key
434: and cust.monitored_flag='Y'))
435: )
433: and cust.biz_flow_parent_key = ba.biz_flow_parent_key
434: and cust.monitored_flag='Y'))
435: )
436: union
437: select ba.biz_flow_child_key biz_flow_key from fnd_oam_bf_assoc_cust ba
438: where ba.biz_flow_parent_key = p_flow_key
439: and ba.monitored_flag = 'Y'
440: and ba.biz_flow_child_key not in
441: (select x.biz_flow_child_key from fnd_oam_bf_assoc x
437: select ba.biz_flow_child_key biz_flow_key from fnd_oam_bf_assoc_cust ba
438: where ba.biz_flow_parent_key = p_flow_key
439: and ba.monitored_flag = 'Y'
440: and ba.biz_flow_child_key not in
441: (select x.biz_flow_child_key from fnd_oam_bf_assoc x
442: where x.biz_flow_parent_key = p_flow_key);
443:
444: -- For all monitored components under this flow
445: cursor c_monitored_components is
443:
444: -- For all monitored components under this flow
445: cursor c_monitored_components is
446: select c.component_type, c.component_appl_id, c.component_id
447: from fnd_oam_bf_comp c
448: where c.biz_flow_key = p_flow_key
449: and ((c.monitored_flag='Y' and 1 not in (
450: select count(*)
451: from fnd_oam_bf_comp_cust cust
447: from fnd_oam_bf_comp c
448: where c.biz_flow_key = p_flow_key
449: and ((c.monitored_flag='Y' and 1 not in (
450: select count(*)
451: from fnd_oam_bf_comp_cust cust
452: where cust.component_type = c.component_type
453: and cust.component_appl_id = c.component_appl_id
454: and cust.component_id = c.component_id
455: and cust.biz_flow_key = c.biz_flow_key
455: and cust.biz_flow_key = c.biz_flow_key
456: and cust.monitored_flag='N'))
457: or (c.monitored_flag = 'N' and 1 in (
458: select count(*)
459: from fnd_oam_bf_comp_cust cust
460: where cust.component_type = c.component_type
461: and cust.component_appl_id = c.component_appl_id
462: and cust.component_id = c.component_id
463: and cust.biz_flow_key = c.biz_flow_key
464: and cust.monitored_flag='Y'))
465: )
466: union
467: select c.component_type, c.component_appl_id, c.component_id
468: from fnd_oam_bf_comp_cust c
469: where c.biz_flow_key = p_flow_key
470: and c.monitored_flag = 'Y'
471: and c.component_type || ':' ||
472: c.component_appl_id || ':' ||
473: c.component_id not in
474: (select x.component_type || ':' ||
475: x.component_appl_id || ':' ||
476: x.component_id
477: from fnd_oam_bf_comp x
478: where x.biz_flow_key = p_flow_key);
479:
480: -- For all monitored work item types directly associated with this flow
481: cursor c_monitored_wit is
478: where x.biz_flow_key = p_flow_key);
479:
480: -- For all monitored work item types directly associated with this flow
481: cursor c_monitored_wit is
482: select w.item_type from fnd_oam_bf_wit w
483: where w.biz_flow_key = p_flow_key
484: and ((w.monitored_flag = 'Y' and 1 not in (
485: select count(*) from fnd_oam_bf_wit_cust cust
486: where cust.item_type = w.item_type
481: cursor c_monitored_wit is
482: select w.item_type from fnd_oam_bf_wit w
483: where w.biz_flow_key = p_flow_key
484: and ((w.monitored_flag = 'Y' and 1 not in (
485: select count(*) from fnd_oam_bf_wit_cust cust
486: where cust.item_type = w.item_type
487: and cust.biz_flow_key = w.biz_flow_key
488: and cust.monitored_flag = 'N'))
489: or (w.monitored_flag = 'N' and 1 in (
486: where cust.item_type = w.item_type
487: and cust.biz_flow_key = w.biz_flow_key
488: and cust.monitored_flag = 'N'))
489: or (w.monitored_flag = 'N' and 1 in (
490: select count(*) from fnd_oam_bf_wit_cust cust
491: where cust.item_type = w.item_type
492: and cust.biz_flow_key = w.biz_flow_key
493: and cust.monitored_flag = 'Y'))
494: )
492: and cust.biz_flow_key = w.biz_flow_key
493: and cust.monitored_flag = 'Y'))
494: )
495: union
496: select w.item_type from fnd_oam_bf_wit_cust w
497: where w.biz_flow_key = p_flow_key
498: and w.monitored_flag = 'Y'
499: and w.item_type not in
500: (select x.item_type from fnd_oam_bf_wit x
496: select w.item_type from fnd_oam_bf_wit_cust w
497: where w.biz_flow_key = p_flow_key
498: and w.monitored_flag = 'Y'
499: and w.item_type not in
500: (select x.item_type from fnd_oam_bf_wit x
501: where x.biz_flow_key = p_flow_key);
502:
503: BEGIN
504: if (g_debug) then
517: nvl(count_running_requests,0), nvl(count_form_sessions,0),
518: nvl(count_ssf_sessions,0)
519: into v_temp_alerts, v_temp_err_req, v_temp_running_req,
520: v_temp_form_sessions, v_temp_ssf_sessions
521: from fnd_oam_bf_comp_info
522: where component_type = c.component_type
523: and component_appl_id = c.component_appl_id
524: and component_id = c.component_id;
525: v_count_alerts := v_count_alerts + v_temp_alerts;
543: v_temp_active_wi number := 0;
544: begin
545: select nvl(count_errored_items,0), nvl(count_active_items,0)
546: into v_temp_err_wi, v_temp_active_wi
547: from fnd_oam_bf_wit_info
548: where item_type = w.item_type;
549: v_count_err_wi := v_count_err_wi + v_temp_err_wi;
550: v_count_active_wi := v_count_active_wi + v_temp_active_wi;
551: end;
560: -- check the absolute monitored flag for seeded flow
561: select
562: nvl(fbc.monitored_flag,fb.monitored_flag)
563: into v_abs_cust_mflag
564: from fnd_oam_bf fb, fnd_oam_bf_cust fbc
565: where fb.biz_flow_key = fbc.biz_flow_key (+)
566: and fb.biz_flow_key=fl.biz_flow_key;
567: exception
568: when no_data_found then
568: when no_data_found then
569: -- check the absolute monitored flag for user created flow
570: select fbc.monitored_flag
571: into v_abs_cust_mflag
572: from fnd_oam_bf_cust fbc
573: where fbc.biz_flow_key=fl.biz_flow_key;
574: end;
575:
576: if (v_abs_cust_mflag = 'Y') then
626: end;
627: end loop;
628:
629: -- finally update the out parameters and load the info into
630: -- fnd_oam_bf_rollup_info
631: p_count_alerts := v_count_alerts;
632: p_count_err_requests := v_count_err_requests;
633: p_count_err_wi := v_count_err_wi;
634: p_count_running_requests := v_count_running_requests;
650: -- - count of open system alerts
651: -- - count of errored concurrent requests
652: -- - count of errored work items
653: --
654: -- The resulting values will get populated into fnd_oam_bf_rollup_info
655: -- tables.
656: --
657: -- Input Arguments
658: --
665: IS
666: cursor c_monitored_key_flows is
667: select
668: fb.biz_flow_key biz_flow_key
669: from fnd_oam_bf fb, fnd_oam_bf_cust fbc
670: where fb.biz_flow_key = fbc.biz_flow_key (+)
671: and fb.is_top_level = 'Y'
672: and nvl(fbc.monitored_flag,fb.monitored_flag) = 'Y'
673: union
671: and fb.is_top_level = 'Y'
672: and nvl(fbc.monitored_flag,fb.monitored_flag) = 'Y'
673: union
674: select fbc.biz_flow_key biz_flow_key
675: from fnd_oam_bf_cust fbc
676: where fbc.monitored_flag = 'Y'
677: and fbc.is_top_level = 'Y'
678: and fbc.biz_flow_key not in (
679: select fb.biz_flow_key from fnd_oam_bf fb
675: from fnd_oam_bf_cust fbc
676: where fbc.monitored_flag = 'Y'
677: and fbc.is_top_level = 'Y'
678: and fbc.biz_flow_key not in (
679: select fb.biz_flow_key from fnd_oam_bf fb
680: where fb.is_top_level = 'Y');
681:
682: v_count_alerts number;
683: v_count_err_requests number;
721: -- - count of open system alerts
722: -- - count of errored concurrent requests
723: -- - count of errored work items
724: --
725: -- The resulting values will get populated into fnd_oam_bf_comp_info,
726: -- fnd_oam_bf_wit_info and fnd_oam_bf_rollup_info tables.
727: --
728: -- Input Arguments
729: --
722: -- - count of errored concurrent requests
723: -- - count of errored work items
724: --
725: -- The resulting values will get populated into fnd_oam_bf_comp_info,
726: -- fnd_oam_bf_wit_info and fnd_oam_bf_rollup_info tables.
727: --
728: -- Input Arguments
729: --
730: -- Output Arguments
745: END refresh_metrics;
746:
747: --
748: -- Updates the monitored flag for the given flow
749: -- Updates fnd_oam_bf_cust if record exists for given flow key
750: -- Otherwise, copies entry from fnd_oam_bf to
751: -- fnd_oam_bf_cust and updates the monitored_flag in
752: -- fnd_oam_bf_cust.
753: --
746:
747: --
748: -- Updates the monitored flag for the given flow
749: -- Updates fnd_oam_bf_cust if record exists for given flow key
750: -- Otherwise, copies entry from fnd_oam_bf to
751: -- fnd_oam_bf_cust and updates the monitored_flag in
752: -- fnd_oam_bf_cust.
753: --
754: --
747: --
748: -- Updates the monitored flag for the given flow
749: -- Updates fnd_oam_bf_cust if record exists for given flow key
750: -- Otherwise, copies entry from fnd_oam_bf to
751: -- fnd_oam_bf_cust and updates the monitored_flag in
752: -- fnd_oam_bf_cust.
753: --
754: --
755: PROCEDURE update_bf_monitored_flag (
748: -- Updates the monitored flag for the given flow
749: -- Updates fnd_oam_bf_cust if record exists for given flow key
750: -- Otherwise, copies entry from fnd_oam_bf to
751: -- fnd_oam_bf_cust and updates the monitored_flag in
752: -- fnd_oam_bf_cust.
753: --
754: --
755: PROCEDURE update_bf_monitored_flag (
756: p_flow_key varchar2,
761: v_base_monitored_flag varchar2(1);
762: BEGIN
763: v_userid := get_user_id;
764: begin
765: select 1 into v_cust_flag from fnd_oam_bf_cust
766: where biz_flow_key = p_flow_key;
767: exception
768: when no_data_found then
769: v_cust_flag := 0;
770: end;
771:
772: if (v_cust_flag = 0) then
773: -- select monitored_flag into v_base_monitored_flag
774: -- from fnd_oam_bf
775: -- where biz_flow_key = p_flow_key;
776: --
777: -- if (v_base_monitored_flag <> p_new_flag) then
778: -- -- copy over record to cust table
775: -- where biz_flow_key = p_flow_key;
776: --
777: -- if (v_base_monitored_flag <> p_new_flag) then
778: -- -- copy over record to cust table
779: -- insert into fnd_oam_bf_cust(
780: -- biz_flow_key, monitored_flag, is_top_level,
781: -- created_by, creation_date, last_updated_by, last_update_date,
782: -- last_update_login)
783: -- select biz_flow_key, monitored_flag, is_top_level,
782: -- last_update_login)
783: -- select biz_flow_key, monitored_flag, is_top_level,
784: -- created_by, creation_date, last_updated_by, last_update_date,
785: -- last_update_login
786: -- from fnd_oam_bf
787: -- where biz_flow_key = p_flow_key;
788: -- insert into fnd_oam_bf_cust_tl(
789: -- biz_flow_key, language, flow_display_name, description,
790: -- created_by, creation_date, last_updated_by, last_update_date,
784: -- created_by, creation_date, last_updated_by, last_update_date,
785: -- last_update_login
786: -- from fnd_oam_bf
787: -- where biz_flow_key = p_flow_key;
788: -- insert into fnd_oam_bf_cust_tl(
789: -- biz_flow_key, language, flow_display_name, description,
790: -- created_by, creation_date, last_updated_by, last_update_date,
791: -- last_update_login, source_lang)
792: -- select biz_flow_key, language, flow_display_name, description,
791: -- last_update_login, source_lang)
792: -- select biz_flow_key, language, flow_display_name, description,
793: -- created_by, creation_date, last_updated_by, last_update_date,
794: -- last_update_login, source_lang
795: -- from fnd_oam_bf_tl
796: -- where biz_flow_key = p_flow_key;
797: -- end if;
798: -- now update fnd_oam_bf
799: update fnd_oam_bf set
794: -- last_update_login, source_lang
795: -- from fnd_oam_bf_tl
796: -- where biz_flow_key = p_flow_key;
797: -- end if;
798: -- now update fnd_oam_bf
799: update fnd_oam_bf set
800: monitored_flag = p_new_flag,
801: last_update_date = sysdate,
802: last_updated_by = v_userid
795: -- from fnd_oam_bf_tl
796: -- where biz_flow_key = p_flow_key;
797: -- end if;
798: -- now update fnd_oam_bf
799: update fnd_oam_bf set
800: monitored_flag = p_new_flag,
801: last_update_date = sysdate,
802: last_updated_by = v_userid
803: where biz_flow_key = p_flow_key;
801: last_update_date = sysdate,
802: last_updated_by = v_userid
803: where biz_flow_key = p_flow_key;
804: else
805: -- now update fnd_oam_bf_cust
806: update fnd_oam_bf_cust set
807: monitored_flag = p_new_flag,
808: last_update_date = sysdate,
809: last_updated_by = v_userid
802: last_updated_by = v_userid
803: where biz_flow_key = p_flow_key;
804: else
805: -- now update fnd_oam_bf_cust
806: update fnd_oam_bf_cust set
807: monitored_flag = p_new_flag,
808: last_update_date = sysdate,
809: last_updated_by = v_userid
810: where biz_flow_key = p_flow_key;
817:
818: --
819: -- Updates the monitored flag for the given sub flow in context of the
820: -- given parent flow.
821: -- Updates fnd_oam_bf_assoc_cust if record exists for given parent
822: -- and child. Otherwise, copies entry from fnd_oam_bf_assoc to
823: -- fnd_oam_bf_assoc_cust and updates the monitored_flag in
824: -- fnd_oam_bf_assoc_cust.
825: --
818: --
819: -- Updates the monitored flag for the given sub flow in context of the
820: -- given parent flow.
821: -- Updates fnd_oam_bf_assoc_cust if record exists for given parent
822: -- and child. Otherwise, copies entry from fnd_oam_bf_assoc to
823: -- fnd_oam_bf_assoc_cust and updates the monitored_flag in
824: -- fnd_oam_bf_assoc_cust.
825: --
826: --
819: -- Updates the monitored flag for the given sub flow in context of the
820: -- given parent flow.
821: -- Updates fnd_oam_bf_assoc_cust if record exists for given parent
822: -- and child. Otherwise, copies entry from fnd_oam_bf_assoc to
823: -- fnd_oam_bf_assoc_cust and updates the monitored_flag in
824: -- fnd_oam_bf_assoc_cust.
825: --
826: --
827: PROCEDURE update_bf_monitored_flag (
820: -- given parent flow.
821: -- Updates fnd_oam_bf_assoc_cust if record exists for given parent
822: -- and child. Otherwise, copies entry from fnd_oam_bf_assoc to
823: -- fnd_oam_bf_assoc_cust and updates the monitored_flag in
824: -- fnd_oam_bf_assoc_cust.
825: --
826: --
827: PROCEDURE update_bf_monitored_flag (
828: p_parent_flow_key varchar2,
834: v_base_monitored_flag varchar2(1);
835: BEGIN
836: v_userid := get_user_id;
837: begin
838: select 1 into v_cust_flag from fnd_oam_bf_assoc_cust
839: where biz_flow_parent_key = p_parent_flow_key
840: and biz_flow_child_key = p_child_flow_key;
841: exception
842: when no_data_found then
844: end;
845:
846: if (v_cust_flag = 0) then
847: -- select monitored_flag into v_base_monitored_flag
848: -- from fnd_oam_bf_assoc
849: -- where biz_flow_parent_key = p_parent_flow_key
850: -- and biz_flow_child_key = p_child_flow_key;
851: --
852: -- if (v_base_monitored_flag <> p_new_flag) then
850: -- and biz_flow_child_key = p_child_flow_key;
851: --
852: -- if (v_base_monitored_flag <> p_new_flag) then
853: -- -- copy over record to cust table
854: -- insert into fnd_oam_bf_assoc_cust(
855: -- biz_flow_parent_key, biz_flow_child_key, monitored_flag,
856: -- created_by, creation_date, last_updated_by, last_update_date,
857: -- last_update_login)
858: -- select biz_flow_parent_key, biz_flow_child_key, monitored_flag,
857: -- last_update_login)
858: -- select biz_flow_parent_key, biz_flow_child_key, monitored_flag,
859: -- created_by, creation_date, last_updated_by, last_update_date,
860: -- last_update_login
861: -- from fnd_oam_bf_assoc
862: -- where biz_flow_parent_key = p_parent_flow_key
863: -- and biz_flow_child_key = p_child_flow_key;
864: -- end if;
865: -- now update fnd_oam_bf_assoc
861: -- from fnd_oam_bf_assoc
862: -- where biz_flow_parent_key = p_parent_flow_key
863: -- and biz_flow_child_key = p_child_flow_key;
864: -- end if;
865: -- now update fnd_oam_bf_assoc
866: update fnd_oam_bf_assoc set
867: monitored_flag = p_new_flag,
868: last_update_date = sysdate,
869: last_updated_by = v_userid
862: -- where biz_flow_parent_key = p_parent_flow_key
863: -- and biz_flow_child_key = p_child_flow_key;
864: -- end if;
865: -- now update fnd_oam_bf_assoc
866: update fnd_oam_bf_assoc set
867: monitored_flag = p_new_flag,
868: last_update_date = sysdate,
869: last_updated_by = v_userid
870: where biz_flow_parent_key = p_parent_flow_key
869: last_updated_by = v_userid
870: where biz_flow_parent_key = p_parent_flow_key
871: and biz_flow_child_key = p_child_flow_key;
872: else
873: -- now update fnd_oam_bf_assoc_cust
874: update fnd_oam_bf_assoc_cust set
875: monitored_flag = p_new_flag,
876: last_update_date = sysdate,
877: last_updated_by = v_userid
870: where biz_flow_parent_key = p_parent_flow_key
871: and biz_flow_child_key = p_child_flow_key;
872: else
873: -- now update fnd_oam_bf_assoc_cust
874: update fnd_oam_bf_assoc_cust set
875: monitored_flag = p_new_flag,
876: last_update_date = sysdate,
877: last_updated_by = v_userid
878: where biz_flow_parent_key = p_parent_flow_key
886:
887: --
888: -- Updates the monitored flag for the given component in context of the
889: -- given parent flow.
890: -- Updates fnd_oam_bf_comp_cust if record exists for given parent
891: -- and child. Otherwise, copies entry from fnd_oam_bf_comp to
892: -- fnd_oam_bf_comp_cust and updates the monitored_flag in
893: -- fnd_oam_bf_comp_cust.
894: --
887: --
888: -- Updates the monitored flag for the given component in context of the
889: -- given parent flow.
890: -- Updates fnd_oam_bf_comp_cust if record exists for given parent
891: -- and child. Otherwise, copies entry from fnd_oam_bf_comp to
892: -- fnd_oam_bf_comp_cust and updates the monitored_flag in
893: -- fnd_oam_bf_comp_cust.
894: --
895: --
888: -- Updates the monitored flag for the given component in context of the
889: -- given parent flow.
890: -- Updates fnd_oam_bf_comp_cust if record exists for given parent
891: -- and child. Otherwise, copies entry from fnd_oam_bf_comp to
892: -- fnd_oam_bf_comp_cust and updates the monitored_flag in
893: -- fnd_oam_bf_comp_cust.
894: --
895: --
896: PROCEDURE update_comp_monitored_flag (
889: -- given parent flow.
890: -- Updates fnd_oam_bf_comp_cust if record exists for given parent
891: -- and child. Otherwise, copies entry from fnd_oam_bf_comp to
892: -- fnd_oam_bf_comp_cust and updates the monitored_flag in
893: -- fnd_oam_bf_comp_cust.
894: --
895: --
896: PROCEDURE update_comp_monitored_flag (
897: p_parent_flow_key varchar2,
905: v_base_monitored_flag varchar2(1);
906: BEGIN
907: v_userid := get_user_id;
908: begin
909: select 1 into v_cust_flag from fnd_oam_bf_comp_cust
910: where biz_flow_key = p_parent_flow_key
911: and component_type = p_component_type
912: and component_appl_id = p_component_appl_id
913: and component_id = p_component_id;
917: end;
918:
919: if (v_cust_flag = 0) then
920: -- select monitored_flag into v_base_monitored_flag
921: -- from fnd_oam_bf_comp
922: -- where biz_flow_key = p_parent_flow_key
923: -- and component_type = p_component_type
924: -- and component_appl_id = p_component_appl_id
925: -- and component_id = p_component_id;
925: -- and component_id = p_component_id;
926: --
927: -- if (v_base_monitored_flag <> p_new_flag) then
928: -- -- copy over record to cust table
929: -- insert into fnd_oam_bf_comp_cust(
930: -- biz_flow_key, component_type, component_appl_id, component_id,
931: -- monitored_flag,
932: -- created_by, creation_date, last_updated_by, last_update_date,
933: -- last_update_login)
934: -- select biz_flow_key,component_type,component_appl_id, component_id,
935: -- monitored_flag,
936: -- created_by, creation_date, last_updated_by, last_update_date,
937: -- last_update_login
938: -- from fnd_oam_bf_comp
939: -- where biz_flow_key = p_parent_flow_key
940: -- and component_type = p_component_type
941: -- and component_appl_id = p_component_appl_id
942: -- and component_id = p_component_id;
941: -- and component_appl_id = p_component_appl_id
942: -- and component_id = p_component_id;
943: -- end if;
944: -- now update regular table
945: update fnd_oam_bf_comp set
946: monitored_flag = p_new_flag,
947: last_update_date = sysdate,
948: last_updated_by = v_userid
949: where biz_flow_key = p_parent_flow_key
951: and component_appl_id = p_component_appl_id
952: and component_id = p_component_id;
953: else
954: -- now update cust table
955: update fnd_oam_bf_comp_cust set
956: monitored_flag = p_new_flag,
957: last_update_date = sysdate,
958: last_updated_by = v_userid
959: where biz_flow_key = p_parent_flow_key
984: v_base_monitored_flag varchar2(1);
985: BEGIN
986: v_userid := get_user_id;
987: begin
988: select 1 into v_cust_flag from fnd_oam_bf_wit_cust
989: where biz_flow_key = p_parent_flow_key
990: and item_type = p_item_type;
991: exception
992: when no_data_found then
994: end;
995:
996: if (v_cust_flag = 0) then
997: -- select monitored_flag into v_base_monitored_flag
998: -- from fnd_oam_bf_wit
999: -- where biz_flow_key = p_parent_flow_key
1000: -- and item_type = p_item_type;
1001: --
1002: -- if (v_base_monitored_flag <> p_new_flag) then
1000: -- and item_type = p_item_type;
1001: --
1002: -- if (v_base_monitored_flag <> p_new_flag) then
1003: -- -- copy over record to cust table
1004: -- insert into fnd_oam_bf_wit_cust(
1005: -- biz_flow_key, item_type, monitored_flag,
1006: -- created_by, creation_date, last_updated_by, last_update_date,
1007: -- last_update_login)
1008: -- select biz_flow_key, item_type, monitored_flag,
1007: -- last_update_login)
1008: -- select biz_flow_key, item_type, monitored_flag,
1009: -- created_by, creation_date, last_updated_by, last_update_date,
1010: -- last_update_login
1011: -- from fnd_oam_bf_wit
1012: -- where biz_flow_key = p_parent_flow_key
1013: -- and item_type = p_item_type;
1014: -- end if;
1015: -- now update fnd_oam_bf_assoc
1011: -- from fnd_oam_bf_wit
1012: -- where biz_flow_key = p_parent_flow_key
1013: -- and item_type = p_item_type;
1014: -- end if;
1015: -- now update fnd_oam_bf_assoc
1016: update fnd_oam_bf_wit set
1017: monitored_flag = p_new_flag,
1018: last_update_date = sysdate,
1019: last_updated_by = v_userid
1012: -- where biz_flow_key = p_parent_flow_key
1013: -- and item_type = p_item_type;
1014: -- end if;
1015: -- now update fnd_oam_bf_assoc
1016: update fnd_oam_bf_wit set
1017: monitored_flag = p_new_flag,
1018: last_update_date = sysdate,
1019: last_updated_by = v_userid
1020: where biz_flow_key = p_parent_flow_key
1019: last_updated_by = v_userid
1020: where biz_flow_key = p_parent_flow_key
1021: and item_type = p_item_type;
1022: else
1023: -- now update fnd_oam_bf_assoc_cust
1024: update fnd_oam_bf_wit_cust set
1025: monitored_flag = p_new_flag,
1026: last_update_date = sysdate,
1027: last_updated_by = v_userid
1020: where biz_flow_key = p_parent_flow_key
1021: and item_type = p_item_type;
1022: else
1023: -- now update fnd_oam_bf_assoc_cust
1024: update fnd_oam_bf_wit_cust set
1025: monitored_flag = p_new_flag,
1026: last_update_date = sysdate,
1027: last_updated_by = v_userid
1028: where biz_flow_key = p_parent_flow_key
1034: when others then
1035: raise;
1036: END update_wit_monitored_flag;
1037:
1038: end FND_OAM_BF_UTIL;