CREATE OR REPLACE PACKAGE generate_wf AS

  -- Copyright 2006 TS Fifteen Ltd / WorkflowFAQ.com
  -- Written by Matthew Searle
  -- http://www.workflowfaq.com
  
  -- This version release 1.0 - 2006/08/07

  g_log_pkg              VARCHAR2(75) DEFAULT 'wf_log_pkg.string';

  CURSOR c_get_function ( cp_item_type IN VARCHAR2
                        , cp_module    IN VARCHAR2 ) IS
    SELECT DISTINCT UPPER(wfa.function)                   function
    ,      DECODE(wfa.result_type,'*',NULL,result_type)   result_type
    FROM   wf_process_activities wpa
    ,      wf_activities         wfa
    ,      wf_item_types_tl      wit
    WHERE  wpa.activity_item_type = wfa.item_type
    AND    wpa.activity_name      = wfa.name
    AND    wfa.version            = ( SELECT max(version)
                                      FROM   wf_activities wfa1
                                      WHERE  wpa.activity_item_type = wfa1.item_type
                                      AND    wpa.activity_name      = wfa1.name )
    AND    wpa.process_item_type  = cp_item_type
    AND    wpa.process_item_type  = wit.name
    AND    wfa.type               = 'FUNCTION'
    --AND    wfa.item_type          = cp_item_type
    AND    wfa.function IS NOT NULL
    AND    UPPER(wfa.function) NOT LIKE 'WF_STANDARD.%'
    AND    UPPER(wfa.function) LIKE cp_module
    ORDER BY UPPER(wfa.function);
     
  CURSOR c_get_activities ( cp_item_type IN VARCHAR2 ) IS
    SELECT DISTINCT wfa.item_type                         name
    ,      wfa.name                                       activity_name
    ,      DECODE(wfa.result_type,'*',NULL,result_type)   result_type
    ,      wfa.version
    FROM   wf_process_activities wpa
    ,      wf_activities         wfa
    ,      wf_item_types_tl      wit
    WHERE  wpa.activity_item_type = wfa.item_type
    AND    wpa.activity_name      = wfa.name
    AND    wfa.version            = ( SELECT max(version)
                                      FROM   wf_activities wfa1
                                      WHERE  wpa.activity_item_type = wfa1.item_type
                                      AND    wpa.activity_name      = wfa1.name )
    AND    wpa.process_item_type  = cp_item_type
    AND    wpa.process_item_type  = wit.name
    AND    wfa.type               = 'FUNCTION'
    --AND    wfa.item_type          = cp_item_type
    AND    wfa.function IS NULL;

  PROCEDURE generate_header ( p_item_type IN VARCHAR2
                            , p_module    IN VARCHAR2 DEFAULT '%'
                            , p_update_wf IN BOOLEAN DEFAULT FALSE );
  
  PROCEDURE generate_body ( p_item_type IN VARCHAR2
                          , p_module    IN VARCHAR2 DEFAULT '%'
                          , p_update_wf IN BOOLEAN DEFAULT FALSE );
  
END generate_wf;
/
SHO ERR