set define '^'

Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem    jstraub     04/15/2009 - Created patch file for bug 8432829

begin
    execute immediate 'create index wwv_flow_region_rpt_col_idx2 on wwv_flow_region_report_column (security_group_id,flow_id)';
exception 
    when others then
        if sqlcode = -955 then
            null;
        else
        	  raise;
        end if;
end;
/

declare
    s varchar2(32767);
begin
    wwv_flow_security.g_security_group_id := 10;

    s:=s||'declare'||chr(10)||
    '  q varchar2(32767) := null;'||chr(10)||
    '  l_static  varchar2(200);'||chr(10)||
    '  l_dynamic varchar2(200);'||chr(10)||
    '  l_not_referenced varchar2(200);'||chr(10)||
    ''||chr(10)||
    'begin'||chr(10)||
    ''||chr(10)||
    'select wwv_flow_lang.system_message(''F4000.STATIC'')'||chr(10)||
    '  into l_static'||chr(10)||
    '  from dual;'||chr(10)||
    ''||chr(10)||
    'select wwv_flow_lang.system_message(''F4000.DYNAMIC'')'||chr(10)||
    '  into l_dynamic'||chr(10)||
    '  from dual;'||chr(10)||
    ''||chr(10)||
    'select wwv_flow_lang.system_message(''F4000.NOT_REFERENCED'')'||chr(10)||
    '  into l_not_referenced'||chr(10)||
    '  from dual;'||chr(10)||
    ''||chr(10)||
    '  q';
    
    s:=s||':= ''select a.id, flow_id, '';'||chr(10)||
    '  q:=q||''decode(substr(lov_query,1,1),''''.'''',''''''||l_static||'''''',''''''||l_dynamic||'''''') t,  '';'||chr(10)||
    '  q:=q||''htf.anchor(''''f?p=4000:4111:''''||:flow_session||''''::NO:4111:F4000_P4111_ID:''''||ID,'';'||chr(10)||
    '  q:=q||''htf.escape_sc(lov_name)) n, '';'||chr(10)||
    '  q:=q||''(select count(*) from wwv_flow_step_items where flow_id = :fb_flow_id and named_lov = lov_name) item_references,'';'||chr(10)||
    '  q:=q||''(select count(*';
    
    s:=s||') from wwv_flow_region_report_column where flow_id = a.flow_id and security_group_id = a.security_group_id and named_lov = a.id) report_column_references,'';'||chr(10)||
    '  q:=q||''(select count(*) from wwv_flow_step_items where flow_id = :fb_flow_id '';'||chr(10)||
    '  q:=q||''and named_lov = lov_name)+(select count(*) from wwv_flow_region_report_column where flow_id = a.flow_id and security_group_id = a.security_group_id and ';
    
    s:=s||'named_lov = a.id) u '';  '||chr(10)||
    '  q:=q||''from WWV_FLOW_LISTS_OF_VALUES$ a '';'||chr(10)||
    '  q:=q||''where  flow_id = :FB_FLOW_ID and (:P198_FIND is null or instr(upper(lov_name),upper(:P198_FIND))>0) '';'||chr(10)||chr(10)||
    '  return q;'||chr(10)||
    'end;';
    
    update wwv_flow_page_plugs
       set plug_source = s
     where id >= 3946306261085798
       and id  < 3946306261085798 + 1
       and flow_id between 4000 and 4009
       and page_id >= 198
       and page_id  < 198 + 1
    ;

    commit;
        
end;
/