249: CURSOR GET_MAP_CODE IS
250: SELECT DISTINCT b.STATUS,
251: EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_XML_MAP_CODE',b.GUID) map_code
252: FROM wf_events_vl a,
253: wf_event_subscriptions b
254: WHERE a.guid=b.EVENT_FILTER_GUID
255: -- Namit S. Bug#4917171 Added the following 2 clauses.
256: -- Table wf_event_subscriptions has index on EVENT_FILTER_GUID, source_type, system_guid.
257: -- Adding the 2 clauses removes Full Table Scan of wf_event_subscriptions.
252: FROM wf_events_vl a,
253: wf_event_subscriptions b
254: WHERE a.guid=b.EVENT_FILTER_GUID
255: -- Namit S. Bug#4917171 Added the following 2 clauses.
256: -- Table wf_event_subscriptions has index on EVENT_FILTER_GUID, source_type, system_guid.
257: -- Adding the 2 clauses removes Full Table Scan of wf_event_subscriptions.
258: AND b.source_type = 'LOCAL'
259: AND b.system_guid = HEXTORAW(wf_core.TRANSLATE('WF_SYSTEM_GUID'))
260: AND b.RULE_FUNCTION ='EDR_PSIG_RULE.PSIG_RULE'
253: wf_event_subscriptions b
254: WHERE a.guid=b.EVENT_FILTER_GUID
255: -- Namit S. Bug#4917171 Added the following 2 clauses.
256: -- Table wf_event_subscriptions has index on EVENT_FILTER_GUID, source_type, system_guid.
257: -- Adding the 2 clauses removes Full Table Scan of wf_event_subscriptions.
258: AND b.source_type = 'LOCAL'
259: AND b.system_guid = HEXTORAW(wf_core.TRANSLATE('WF_SYSTEM_GUID'))
260: AND b.RULE_FUNCTION ='EDR_PSIG_RULE.PSIG_RULE'
261: AND a.name = p_event_name
263: ORDER BY b.STATUS DESC;
264: BEGIN
265: SELECT COUNT(*) INTO l_cnt
266: FROM wf_events_vl a,
267: wf_event_subscriptions b
268: WHERE a.guid=b.EVENT_FILTER_GUID
269: -- Namit S. Bug#4917171 Added the following 2 clauses.
270: AND b.source_type = 'LOCAL'
271: AND b.system_guid = HEXTORAW(wf_core.TRANSLATE('WF_SYSTEM_GUID'))