REM +======================================================================+
REM | Copyright (c) 2000 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM
REM NAME
REM     wfeventv.sql  -  create WorkFlow EVENT Manager Views.
REM
REM	09/2002 VARRAJAR Bug 2558446 - Added new columns CUSTOMIZATION_LEVEL
REM			 and LICENSED_FLAG
REM +======================================================================+
REM Connect to base account
REM (autopatch will run all scripts in apps account)

DEFINE hdr = "$Header: wfeventv.sql 26.2 2004/04/28 01:20:38 yohuang ship $"

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

/*
** WF_EVENTS_VL
*/
create or replace force view WF_EVENTS_VL (
    row_id,
    guid,
    name,
    type,
    status,
    generate_function,    
    JAVA_GENERATE_FUNC,
    owner_name,
    owner_tag,
    customization_level,
    licensed_flag,
    display_name,
    description
  ) as select /* $Header: wfeventv.sql 26.2 2004/04/28 01:20:38 yohuang ship $ */
    b.rowid row_id,
    b.guid,
    b.name,
    b.type,
    b.status,
    b.generate_function,
    b.java_generate_func, 
    b.owner_name,
    b.owner_tag,
    b.customization_level,
    b.licensed_flag,
    tl.display_name,
    tl.description
  from  wf_events b, 
        wf_events_tl tl
  where tl.language = userenv('LANG')
  and   tl.guid = b.guid;

/*
 WF_ACTIVE_SUBSCRIPTIONS_V
*/
CREATE OR REPLACE FORCE VIEW WF_ACTIVE_SUBSCRIPTIONS_V 
(  EVENT_NAME                      ,
   GENERATE_FUNCTION               ,
   SYSTEM_GUID                     ,
   SUBSCRIPTION_GUID               ,
   SUBSCRIPTION_SOURCE_TYPE        ,
   SUBSCRIPTION_SOURCE_AGENT_GUID  ,
   SUBSCRIPTION_PHASE              ,
   SUBSCRIPTION_RULE_DATA          ,
   SUBSCRIPTION_OUT_AGENT_GUID     ,
   SUBSCRIPTION_TO_AGENT_GUID      ,
   SUBSCRIPTION_PRIORITY           ,
   SUBSCRIPTION_RULE_FUNCTION      ,
   WF_PROCESS_TYPE                 ,
   WF_PROCESS_NAME                 ,
   SUBSCRIPTION_PARAMETERS         
) AS
SELECT evt.name EVENT_NAME, 
       DECODE(evt.generate_function, NULL, DECODE(evt.JAVA_GENERATE_FUNC, NULL, NULL,
                                                                          'java://' || evt.JAVA_GENERATE_FUNC),
                                     evt.generate_function) GENERATE_FUNCTION,
       sub.system_guid SYSTEM_GUID, sub.guid SUBSCRIPTION_GUID, 
       sub.source_type SUBSCRIPTION_SOURCE_TYPE, sub.source_agent_guid SUBSCRIPTION_SOURCE_AGENT_GUID, 
       NVL(sub.phase, 0) SUBSCRIPTION_PHASE, sub.rule_data SUBSCRIPTION_RULE_DATA, 
       sub.out_agent_guid SUBSCRIPTION_OUT_AGENT_GUID, sub.to_agent_guid SUBSCRIPTION_TO_AGENT_GUID, sub.priority SUBSCRIPTION_PRIORITY, 
       DECODE(sub.rule_function, NULL, DECODE(sub.JAVA_RULE_FUNC, NULL, NULL,
                                                                  'java://' || sub.JAVA_RULE_FUNC),
                                 sub.rule_function) SUBSCRIPTION_RULE_FUNCTION, 
       sub.wf_process_type WF_PROCESS_TYPE, sub.wf_process_name WF_PROCESS_NAME, sub.parameters SUBSCRIPTION_PARAMETERS
FROM  wf_event_subscriptions sub, wf_events evt 
WHERE sub.status = 'ENABLED' 
AND   sub.licensed_flag = 'Y' 
AND   sub.event_filter_guid = evt.guid 
AND   evt.type = 'EVENT' 
AND   evt.status = 'ENABLED' 
AND evt.licensed_flag = 'Y' 
UNION ALL 
(SELECT evt.name EVENT_NAME,  
        DECODE(evt.generate_function, NULL, DECODE(evt.JAVA_GENERATE_FUNC, NULL, NULL,
                                                                          'java://' || evt.JAVA_GENERATE_FUNC),
                                     evt.generate_function) GENERATE_FUNCTION,
        sub.system_guid SYSTEM_GUID, sub.guid SUBSCRIPTION_GUID, 
        sub.source_type SUBSCRIPTION_SOURCE_TYPE, sub.source_agent_guid SUBSCRIPTION_SOURCE_AGENT_GUID, 
        NVL(sub.phase, 0) SUBSCRIPTION_PHASE, sub.rule_data SUBSCRIPTION_RULE_DATA, 
        sub.out_agent_guid SUBSCRIPTION_OUT_AGENT_GUID, sub.to_agent_guid SUBSCRIPTION_TO_AGENT_GUID, sub.priority SUBSCRIPTION_PRIORITY,  
        DECODE(sub.rule_function, NULL, DECODE(sub.JAVA_RULE_FUNC, NULL, NULL,
                                                                  'java://' || sub.JAVA_RULE_FUNC),
                                 sub.rule_function) SUBSCRIPTION_RULE_FUNCTION, 
        sub.wf_process_type WF_PROCESS_TYPE, sub.wf_process_name WF_PROCESS_NAME, sub.parameters SUBSCRIPTION_PARAMETERS
 FROM   wf_event_subscriptions sub, wf_event_groups egrp,
        wf_events grp, wf_events evt 
 WHERE  sub.status = 'ENABLED' 
 AND    sub.licensed_flag = 'Y' 
 AND    grp.guid = sub.event_filter_guid 
 AND    grp.type = 'GROUP' 
 AND    grp.status = 'ENABLED' 
 AND    egrp.group_guid = grp.guid 
 AND    egrp.member_guid = evt.guid 
 AND    evt.type = 'EVENT' 
 AND    evt.status = 'ENABLED' 
 AND    evt.licensed_flag = 'Y') 
ORDER BY 7 ;

commit;
exit;