Param Gen Package Body - CTX
Package Body für es Päckli wo d'Erstellig vo ctx source erlichteret
create or replace package body k.rbsc$param_gen#
is
/*
-------------------------------------------------------------------------------------------------------------
Bank: Coutts & Co Ltd.
Business Area: SDA
Description: Used to generate parameterization sources where Avaloq does not provide a source type
-------------------------------------------------------------------------------------------------------------
ID Date Name, Company Issue Affected BUs, Description
-------------------------------------------------------------------------------------------------------------
001
-------------------------------------------------------------------------------------------------------------
*/
------------------------------------------------------------------------------
-- Returns the appropriate CTX_ACTION source name for the given contex, object type and meta type
------------------------------------------------------------------------------
function ctx_action_src_name(
i_ctx_type varchar2
,i_obj_type varchar2
,i_meta_typ varchar2
) return varchar2
is
l_ctx_type varchar2(100) := trim(lower(i_ctx_type));
l_obj_type varchar2(100) := trim(lower(i_obj_type));
l_meta_typ varchar2(100) := trim(lower(i_meta_typ));
l_misc_type varchar2(60);
begin
if nvl(lower(i_ctx_type), 'null') not in ('null', 'obj', 'doc', 'code', 'obj_fdm') then
l_misc_type := l_ctx_type;
l_ctx_type := 'misc';
end if;
-- Validate combination of parameters
case lower(nvl(l_ctx_type,'null'))
when 'obj' then
require(l_meta_typ is null, 'i_meta_typ is null');
when 'doc' then
require(l_obj_type is null, 'i_obj_type is null');
when 'code' then
require(l_meta_typ is null and l_obj_type is null, 'i_meta_typ is null and i_obj_type is null');
when 'obj_fdm' then
require (l_obj_type is null,'i_obj_type is null');
when 'null' then
require (l_meta_typ is null and l_obj_type is null, 'i_meta_typ is null and i_obj_type is null');
else
require (l_ctx_type is not null and l_obj_type is null and l_meta_typ is null, 'i_ctx_type is not null and i_obj_type is null and i_meta_typ is null');
end case;
return rtrim(upper('CTX_ACTION.'||l_ctx_type ||'.'|| l_obj_type||l_meta_typ||l_misc_type), '.');
exception
when others then
raise_fa_err('ctx_action_src_name('||i_ctx_type||','||i_obj_type||','||i_meta_typ||')');
end ctx_action_src_name;
------------------------------------------------------------------------------
-- Generate a script which can be used to generate all CTX_ACTION sources
------------------------------------------------------------------------------
procedure gen_ctx_action_src_script(
i_dir varchar2
,i_rule_set varchar2
,i_warn_ctx_ref boolean
,i_file_name_pfx varchar2
,i_file_name_sfx varchar2
)
is
l_file_name varchar2(200);
begin
require (i_dir is not null ,'i_dir is not null');
require (i_rule_set is not null ,'i_rule_set is not null');
param_gen_lib#.reset;
param_gen_lib#.put_code('set def off');
param_gen_lib#.put_code('set echo off');
param_gen_lib#.put_code('set heading off');
param_gen_lib#.put_code('set termout off');
param_gen_lib#.put_code('set feedback off');
param_gen_lib#.put_code('set linesize 256');
param_gen_lib#.put_code('alter session set current_schema=k;');
param_gen_lib#.put_code('exec dbms_session.reset_package;');
param_gen_lib#.put_code('exec session#.open_session; ');
param_gen_lib#.put_code('set serveroutput on size unlimited format wrapped');
for s in (
select rule_set
,ctx_type
,obj_type
,meta_typ
from (
select cr.rule_set
,lower(cr.ctx_type) ctx_type
,param_gen#.item#intl_id('code_obj_type' ,cr.obj_type) obj_type
,param_gen#.item#intl_id('meta_typ' ,cr.meta_typ) meta_typ
from ctx_action_rule_ld cr
where cr.rule_set = i_rule_set
)
group by rule_set, ctx_type, obj_type, meta_typ
order by rule_set, ctx_type nulls first, obj_type nulls first, meta_typ nulls first
)loop
l_file_name := i_file_name_pfx||ctx_action_src_name(s.ctx_type, s.obj_type, s.meta_typ)||i_file_name_sfx;
l_file_name := upper(rtrim(replace(l_file_name,'..','.'),'.'));
param_gen_lib#.put_code('spool "' || replace(replace(i_dir, '/','\') || '\', '\\', '\') || l_file_name||'"');
param_gen_lib#.put_code('begin');
param_gen_lib#.indent;
param_gen_lib#.new_proc('rbsc$param_gen#.gen_ctx_action_src');
param_gen_lib#.add_proc_par('i_rule_set' ,s.rule_set ,param_gen_lib#.c_type_text);
param_gen_lib#.add_proc_par('i_ctx_type' ,s.ctx_type ,param_gen_lib#.c_type_text);
param_gen_lib#.add_proc_par('i_obj_type' ,s.obj_type ,param_gen_lib#.c_type_text);
param_gen_lib#.add_proc_par('i_meta_typ' ,s.meta_typ ,param_gen_lib#.c_type_text);
param_gen_lib#.add_proc_par('i_warn_ctx_ref' ,iff(i_warn_ctx_ref ,'+') ,param_gen_lib#.c_type_bool ,i_do_null_par => true);
param_gen_lib#.put_proc;
param_gen_lib#.outdent;
param_gen_lib#.put_code('end;');
param_gen_lib#.put_code('/');
param_gen_lib#.put_code('spool off');
param_gen_lib#.put_rtn;
param_gen_lib#.reset;
end loop;
param_gen_lib#.put_code('set termout on');
param_gen_lib#.put_code('set feedback on');
param_gen_lib#.put_code('set echo on');
exception
when others then
raise_fa_err('gen_ctx_action_src_script('
||'i_dir =>'||i_dir
||', i_rule_set => '||i_rule_set
||', i_warn_ctx_ref => '||bool_to_char(i_warn_ctx_ref)
||', i_file_name_pfx => '||i_file_name_pfx
||', i_file_name_sfx => '||i_file_name_sfx
||')'
);
end gen_ctx_action_src_script;
------------------------------------------------------------------------------
-- Generate the BUSINESS TYPE CONFIG source used for maintaining the context actions
-- Based on Avaloq PARAM_GEN# with additional enhancements/corrections
------------------------------------------------------------------------------
procedure gen_ctx_action_src(
i_rule_set varchar2
,i_ctx_type varchar2
,i_obj_type varchar2
,i_meta_typ varchar2
,i_allow_ref_to_mdb boolean -- obsolete
,i_do_load_rule_defer boolean -- obsolete
,i_warn_ctx_ref boolean
)
is
l_ctx_type varchar2(100) := trim(lower(i_ctx_type));
l_obj_type varchar2(100) := trim(lower(i_obj_type));
l_meta_typ varchar2(100) := trim(lower(i_meta_typ));
l_buf buf#.t_buf;
l_src_buf buf#.t_buf;
l_gen_date varchar2(100);
l_line varchar2(32767);
l_done number := 0;
l_skip_lines number := 0;
l_write_to_buf boolean := true;
l_param_cnt number := 1;
l_eof boolean;
l_src_name varchar2(100);
l_other_ctx_ref_cnt number;
l_other_ctx_ref varchar2(8000);
l_wrapped_line varchar2(150);
begin
l_src_name := ctx_action_src_name(l_ctx_type, l_obj_type, l_meta_typ);
l_gen_date := to_char(sysdate, 'DD.MM.YYYY HH24:MI:SS');
param_gen_lib#.reset;
-- Capture content of current dbms_output in buffer
l_buf := buf#.new;
loop
dbms_output.get_line(l_line, l_done);
exit when l_done = 1;
buf#.buf#write(l_buf, l_line||def.rtn);
end loop;
-- Generate source using PARAM_GEN#
param_gen_lib#.put_code('----------------------------------------------------------------------------');
param_gen_lib#.put_code('-- '||l_src_name);
param_gen_lib#.put_code('-- GENERATED ON '||l_gen_date);
param_gen_lib#.put_code('----------------------------------------------------------------------------');
rbsc$param_gen_ctx#.gen_ctx_action_src(
i_rule_set => i_rule_set
,i_ctx_type => l_ctx_type
,i_obj_type => l_obj_type
,i_meta_typ => l_meta_typ
,i_allow_ref_to_mdb => false
,i_order_by_intl_id => false
);
param_gen_lib#.put_code('----------------------------------------------------------------------------');
param_gen_lib#.put_code('-- '||l_src_name||' END');
param_gen_lib#.put_code('----------------------------------------------------------------------------');
-- Read generated source from DBMS_OUTPUT and transform it
l_done := 0;
l_src_buf := buf#.new;
loop
exit when l_done = 1;
dbms_output.get_line(l_line, l_done);
-- Skip duplicate enabling of output
if l_line like '%exec dbms_output.enable(100000000)%' then
l_skip_lines := 1;
l_write_to_buf := false;
-- Skip enabling of SQL feedback
elsif l_line like 'set feedback on%' then
l_skip_lines := 1;
l_write_to_buf := false;
-- For each CTX_ACTION: Check how many references to the same CTX ACTION exist in other sources
elsif l_line like ' -- CONTEXT ACTION %' and i_warn_ctx_ref then
select count(name), listagg(name,', ') within group (order by name)
into l_other_ctx_ref_cnt, l_other_ctx_ref
from (
select distinct rbsc$param_gen#.ctx_action_src_name(ctx_type, obj_type, meta_typ) name
from ctx_action_rule_ld r
where ctx_action = lower(trim(substr(l_line, 21)))
and (nvl(lower(ctx_type),'#') != nvl(l_ctx_type, '#')
or nvl(lower(obj_type),'#') != nvl(l_obj_type, '#')
or nvl(lower(meta_typ), '#') != nvl(l_meta_typ, '#'))
);
buf#.buf#write(l_src_buf, l_line||def.rtn);
l_skip_lines := 1;
l_write_to_buf := false;
-- If there are other sources where the same CTX action exists: add a comment (and wrapp it over multiple lines)
if l_other_ctx_ref_cnt > 0 then
l_line := 'TODO CHECK CONTEXT ACTION DEFINED IN '||l_other_ctx_ref_cnt||' OTHER SOURCES: '||l_other_ctx_ref;
loop
l_wrapped_line := regexp_substr(l_line, '(.{80}[^ ]*)( )+');
exit when l_wrapped_line is null;
buf#.buf#write(l_src_buf, ' -- '||ltrim(l_wrapped_line)||def.rtn);
l_line := substr(l_line, length(l_wrapped_line));
end loop;
buf#.buf#write(l_src_buf, ' -- '||ltrim(l_line)||def.rtn);
end if;
-- For each PARENT: Check how many references to the same CTX PARENT exist in other sources
elsif l_line like ' -- CONTEXT PARENT %' and i_warn_ctx_ref then
select count(name), listagg(name,', ') within group (order by name)
into l_other_ctx_ref_cnt, l_other_ctx_ref
from (
select distinct rbsc$param_gen#.ctx_action_src_name(ctx_type, obj_type, meta_typ) name
from ctx_action_rule_ld r
,ctx_action cp
,ctx_action ca
where cp.intl_id = lower(trim(substr(l_line, 21)))
and ca.parent_id = cp.id
and r.ctx_action = ca.intl_id
and (nvl(lower(ctx_type),'#') != nvl(l_ctx_type, '#')
or nvl(lower(obj_type),'#') != nvl(l_obj_type, '#')
or nvl(lower(meta_typ), '#') != nvl(l_meta_typ, '#'))
);
buf#.buf#write(l_src_buf, l_line||def.rtn);
l_skip_lines := 1;
l_write_to_buf := false;
-- If there are other sources where the same CTX action exists: add a comment (and wrapp it over multiple lines)
if l_other_ctx_ref_cnt > 0 then
l_line := 'TODO CHECK CONTEXT PARENT DEFINED IN '||l_other_ctx_ref_cnt||' OTHER SOURCES: '||l_other_ctx_ref;
loop
l_wrapped_line := regexp_substr(l_line, '(.{80}[^ ]*)( )+');
exit when l_wrapped_line is null;
buf#.buf#write(l_src_buf, ' -- '||ltrim(l_wrapped_line)||def.rtn);
l_line := substr(l_line, length(l_wrapped_line));
end loop;
buf#.buf#write(l_src_buf, ' -- '||ltrim(l_line)||def.rtn);
end if;
end if;
-- If we should skip the current line...
if l_skip_lines > 0 then
l_skip_lines := l_skip_lines - 1;
if l_write_to_buf then
buf#.buf#write(l_src_buf, l_line||case when l_skip_lines = 0 then '*/' end ||def.rtn);
end if;
if l_skip_lines = 0 then
l_write_to_buf := true;
end if;
else
-- Otherwise put it in the source buffer
buf#.buf#write(l_src_buf, l_line||def.rtn);
end if;
end loop;
-- Read buffered output and produce DBMS_OUTPUT again
loop
buf#.buf#read_line(l_line, l_eof, l_buf);
if l_line is null then
dbms_output.put(def.rtn);
else
dbms_output.put_line(l_line);
end if;
exit when l_eof;
end loop;
-- Read source buffer and produce output again
loop
buf#.buf#read_line(l_line, l_eof, l_src_buf);
if l_line is null then
dbms_output.put(def.rtn);
else
dbms_output.put_line(l_line);
end if;
exit when l_eof;
end loop;
buf#.remv(l_src_buf);
buf#.remv(l_buf);
exception
when others then
buf#.remv(l_src_buf);
buf#.remv(l_buf);
install#.log#error('gen_ctx_action_src');
end gen_ctx_action_src;
----------------------------------------------------------------------------
-- WFC business Type config generation script
----------------------------------------------------------------------------
procedure gen_wfc(
i_meta_typ_id pls_integer
,i_rule_set varchar2
,i_wfc_cmd_per_line number
)
is
type t_attr_len_rec is record(
id pls_integer
,name pls_integer
,order_by pls_integer
,intl_id pls_integer
,user_id pls_integer
,stmt pls_integer
,order_type_id pls_integer
,ctx_action_intl_id varchar2(100)
,meta_proc_param pls_integer
,new_wfc_status_id pls_integer
,descn pls_integer
,form_id pls_integer
,extl_sys_id pls_integer
);
l_stmt varchar2(30000);
l_cnt pls_integer;
l_sub_title_set boolean;
i pls_integer;
l_text_rec out_text#.t_text_rec;
l_last_action varchar2(40);
l_lowest_action_prio varchar2(4) := '9999';
l_highest_no_action_prio varchar2(4) := '0000';
l_attr_len_rec t_attr_len_rec;
l_rule_set varchar2(100);
l_cond wfc_rule_ld.cond%type;
l_action wfc_rule_ld.action%type;
l_wfc_stmt wfc_rule_ld.stmt%type;
l_action_text_tab out_text#.t_text_tab;
l_status_text_tab out_text#.t_text_tab;
l_wfc_cmd_tab wfc#.t_wfc_cmd_tab;
l_sub_wfc_stmt varchar2(1000);
l_wfc_stmt_tab t_char4000_tab := t_char4000_tab();
l_wfc_cnt number;
--
function attr#put(
i_attr_val varchar2
,i_attr_val_max_len pls_integer
,i_is_num boolean := false
,i_is_first_par boolean := false
,i_attr_name varchar2 := null
) return varchar2
is
begin
if i_attr_val_max_len = 0 then
return null;
else
return iff(not i_is_first_par, ' ,')
||iff(i_attr_name is not null, i_attr_name||' => ')
||case
when i_is_num then lpad(nvl(i_attr_val, 'null') ,greatest(i_attr_val_max_len, 4/*null*/) ,' ')
else rpad(i_attr_val ,i_attr_val_max_len + 2 /*quotes*/ ,' ')
end
;
end if;
end attr#put;
----------------------------------------------------------------------------
function rec#get_stmt(
i_wfc_cmd_rec wfc#.t_wfc_cmd_rec
) return varchar2
is
l_ret varchar2(4000);
l_parlist_cnt number;
begin
l_ret := '(' || i_wfc_cmd_rec.cmd;
l_parlist_cnt := lib_co.count_item(i_wfc_cmd_rec.parlist);
if l_parlist_cnt > 0
and ( l_parlist_cnt <> 1
or lib_co.get_item(1, i_wfc_cmd_rec.parlist) is not null
)
then
for i in 1 .. l_parlist_cnt loop
l_ret := l_ret || '(' || lib_co.get_item(i, i_wfc_cmd_rec.parlist) || ')';
end loop;
end if;
l_ret := l_ret || ')';
return l_ret;
exception
when others then raise_fa_err('rec#get_stmt');
end rec#get_stmt;
----------------------------------------------------------------------------
procedure stmt#wfc_cmd_tab(
o_wfc_cmd_tab out nocopy t_char4000_tab
,i_stmt varchar2
)
is
l_wfc_cmd_tab wfc#.t_wfc_cmd_tab;
l_sub_wfc_stmt varchar2(1000);
l_wfc_stmt_tab t_char4000_tab;
l_wfc_cnt number;
begin
-- Init
l_wfc_cnt := 0;
l_wfc_stmt_tab := t_char4000_tab();
-- Parse wfc statement
wfc#.action#get_wfc_cmd_list(
o_wfc_cmd_tab => l_wfc_cmd_tab
,i_action => i_stmt
);
for k in 1 .. l_wfc_cmd_tab.count loop
if k = 1 then
l_wfc_stmt_tab.extend;
end if;
l_wfc_cnt := l_wfc_cnt + 1;
l_sub_wfc_stmt := l_sub_wfc_stmt || rec#get_stmt(l_wfc_cmd_tab(k));
l_wfc_stmt_tab(l_wfc_stmt_tab.count) := l_sub_wfc_stmt;
if l_wfc_cnt >= i_wfc_cmd_per_line
and k <> l_wfc_cmd_tab.count
then
l_wfc_cnt := 0;
l_wfc_stmt_tab.extend;
l_sub_wfc_stmt := null;
end if;
end loop;
o_wfc_cmd_tab := l_wfc_stmt_tab;
exception
when others then raise_fa_err('stmt#wfc_cmd_tab');
end stmt#wfc_cmd_tab;
--
begin
require(i_meta_typ_id is not null, 'valid meta type');
require(i_rule_set is not null, 'valid rule set');
-- Checks
param_gen_lib#.chk_wfc_status_compln(i_meta_typ_id=>i_meta_typ_id);
-- Source header
param_gen_lib#.init;
param_gen_lib#.put_code(lpad('-', 78, '-'));
param_gen_lib#.put_comment('[WFC 1.1]');
param_gen_lib#.put_comment(null);
param_gen_lib#.put_comment('WFC FOR META TYPE ' || meta_typ#.meta_typ#intl_id(i_meta_typ_id));
param_gen_lib#.put_comment('GENERATED WITH PARAM_GEN# ON '||to_char(sysdate, 'DD.MM.YYYY HH24:MI:SS'));
-- Generation script
param_gen_lib#.put_comment(null);
param_gen_lib#.put_comment('SOURCE GENERATED USING');
param_gen_lib#.put_code('/*');
param_gen_lib#.put_code('begin');
param_gen_lib#.indent;
param_gen_lib#.put_code('session#.open_session;');
param_gen_lib#.new_proc('rbsc$param_gen#.gen_wfc');
param_gen_lib#.add_proc_par('i_meta_typ_id' , i_meta_typ_id , param_gen_lib#.c_type_text);
param_gen_lib#.add_proc_par('i_rule_set' , i_rule_set , param_gen_lib#.c_type_text);
param_gen_lib#.add_proc_par('i_wfc_cmd_per_line', i_wfc_cmd_per_line, param_gen_lib#.c_type_text);
param_gen_lib#.put_proc;
param_gen_lib#.outdent;
param_gen_lib#.put_code('end;');
param_gen_lib#.put_code('*/');
-- End of source header
param_gen_lib#.put_code(lpad('-', 78, '-'));
param_gen_lib#.put(i_line => ' ');
-- Start of WFC script
param_gen_lib#.put(i_line => 'exec install#.log#write(''' || upper(meta_typ#.meta_typ#intl_id(i_meta_typ_id)) || ' WORKFLOW'');');
param_gen_lib#.put(i_line => 'set def off');
param_gen_lib#.put(i_line => 'set feedback off');
param_gen_lib#.put(i_line => 'exec session#.open_session;');
param_gen_lib#.put(i_line => ' ');
param_gen_lib#.put(i_line => 'begin');
param_gen_lib#.put(i_line => ' ---- OPEN WFC ----');
param_gen_lib#.put(i_line => ' param_gen#.open_wfc('||i_meta_typ_id||','''||upper(i_rule_set)||''');');
param_gen_lib#.put(i_line => ' ');
---- GENERIC WFC ACTIONS ----
select count(*)
into l_cnt
from wfc_action
where meta_typ_id = i_meta_typ_id
and old_wfc_status_id < 0;
if l_cnt > 0 then
param_gen_lib#.put(i_line => ' ---- GENERIC WFC ACTIONS ----');
for s in (
select *
from wfc_status
where meta_typ_id = i_meta_typ_id
and id < 0
order by id
) loop
param_gen_lib#.put(i_line => ' param_gen#.add_wfc_status(i_id => '
|| s.id
|| ', i_name => ' || param_gen_lib#.sql_val(s.name, param_gen_lib#.c_type_text)
|| ', i_user_id => ' || param_gen_lib#.sql_val(s.user_id, param_gen_lib#.c_type_text)
|| iff(s.intl_id is not null ,', i_intl_id => ' || param_gen_lib#.sql_val(s.intl_id, param_gen_lib#.c_type_text))
|| iff(s.evt_status_id is not null ,', i_evt_status_id => ' || param_gen_lib#.sql_val(s.evt_status_id))
|| iff(s.mp_xrate_status_id is not null ,', i_mp_xrate_status_id => ' || param_gen_lib#.sql_val(s.mp_xrate_status_id))
|| iff(s.doc_edit_style_id is not null ,', i_doc_edit_style_id => ' || param_gen_lib#.sql_val(s.doc_edit_style_id))
|| iff(s.is_frozen is not null ,', i_is_frozen => true')
|| iff(s.bgp_link is not null ,', i_bgp_link => true')
|| iff(s.is_in_work is null ,', i_is_frozen => false')
|| iff(s.is_frozen is not null ,', i_is_in_work => true')
|| iff(s.wfc_grp_id is not null ,', i_wfc_grp_id => ' || param_gen_lib#.sql_val(s.wfc_grp_id))
|| iff(s.form_id is not null ,', i_form => ' || param_gen_lib#.sql_val(s.form_id))
|| iff(s.descn is not null ,', i_descn => ' || param_gen_lib#.sql_val(s.descn, param_gen_lib#.c_type_text))
|| ');'
);
end loop;
select nvl(max(length(wa.id )) ,0)
,nvl(max(length(wa.name )) ,0)
,nvl(max(length(wa.order_by )) ,0)
,nvl(max(length(wa.intl_id )) ,0)
,nvl(max(length(wa.user_id )) ,0)
,nvl(max(length(wa.stmt )) ,0)
,nvl(max(length(wa.order_type_id )) ,0)
,nvl(max(length(ca.intl_id )) ,0)
,nvl(max(length(wa.meta_proc_param )) ,0)
,nvl(max(length(wa.new_wfc_status_id)) ,0)
,nvl(max(length(wa.descn )) ,0)
,nvl(max(length(wa.form_id )) ,0)
,nvl(max(length(wa.extl_sys_id )) ,0)
into l_attr_len_rec
from ctx_action ca
,wfc_action wa
where wa.meta_typ_id = i_meta_typ_id
and wa.old_wfc_status_id < 0
and ca.id (+) = wa.ctx_action_id
;
for a in (
select wa.*
,ca.intl_id ctx_action_intl_id
from ctx_action ca
,wfc_action wa
where wa.meta_typ_id = i_meta_typ_id
and wa.old_wfc_status_id < 0
and ca.id (+) = wa.ctx_action_id
order by wa.old_wfc_status_id, wa.order_by, wa.id desc
) loop
if l_attr_len_rec.stmt + l_attr_len_rec.meta_proc_param + l_attr_len_rec.descn = 0 then
param_gen_lib#.put(i_line => ' param_gen#.add_wfc_action('
||attr#put(a.id ,l_attr_len_rec.id ,i_is_num => true ,i_is_first_par => true )
||attr#put(param_gen_lib#.sql_val(a.name, param_gen_lib#.c_type_text) ,l_attr_len_rec.name )
||attr#put(a.order_by ,l_attr_len_rec.order_by ,i_is_num => true ,i_attr_name => 'i_order_by' )
||attr#put(param_gen_lib#.sql_val(a.intl_id, param_gen_lib#.c_type_text) ,l_attr_len_rec.intl_id ,i_attr_name => 'i_intl_id' )
||attr#put(param_gen_lib#.sql_val(a.user_id, param_gen_lib#.c_type_text) ,l_attr_len_rec.user_id ,i_attr_name => 'i_user_id' )
||attr#put(a.order_type_id ,l_attr_len_rec.order_type_id ,i_is_num => true ,i_attr_name => 'i_order_type_id' )
||attr#put(param_gen_lib#.sql_val(a.ctx_action_intl_id, param_gen_lib#.c_type_text) ,l_attr_len_rec.ctx_action_intl_id ,i_is_num => false ,i_attr_name => 'i_ctx_action_intl_id')
||attr#put(a.new_wfc_status_id ,l_attr_len_rec.new_wfc_status_id ,i_is_num => true ,i_attr_name => 'i_new_wfc_status_id' )
||attr#put(a.form_id ,l_attr_len_rec.form_id ,i_is_num => true ,i_attr_name => 'i_form_id' )
||attr#put(a.extl_sys_id ,l_attr_len_rec.extl_sys_id ,i_is_num => true ,i_attr_name => 'i_extl_sys_id' )
||');'
);
else
param_gen_lib#.put(i_line => ' param_gen#.add_wfc_action(');
param_gen_lib#.put(i_line => ' i_id => ' || a.id);
param_gen_lib#.put(i_line => ' ,i_name => ' || param_gen_lib#.sql_val(a.name, param_gen_lib#.c_type_text));
param_gen_lib#.put(i_line => ' ,i_order_by => ' || param_gen_lib#.sql_val(a.order_by));
param_gen_lib#.put(i_line => ' ,i_user_id => ' || param_gen_lib#.sql_val(a.user_id, param_gen_lib#.c_type_text));
if a.stmt is not null then param_gen_lib#.put(i_line => ' ,i_stmt => ' || param_gen_lib#.sql_val(a.stmt, param_gen_lib#.c_type_text)); end if;
if a.wfa_proc is not null then param_gen_lib#.put(i_line => ' ,i_wfa_proc => ' || param_gen_lib#.sql_val(a.wfa_proc, param_gen_lib#.c_type_text)); end if;
if a.intl_id is not null then param_gen_lib#.put(i_line => ' ,i_intl_id => ' || param_gen_lib#.sql_val(a.intl_id, param_gen_lib#.c_type_text)); end if;
if a.hyper_before is not null then param_gen_lib#.put(i_line => ' ,i_hyper_before => ' || param_gen_lib#.sql_val(a.hyper_before, param_gen_lib#.c_type_text)); end if;
if a.hyper_after is not null then param_gen_lib#.put(i_line => ' ,i_hyper_after => ' || param_gen_lib#.sql_val(a.hyper_after, param_gen_lib#.c_type_text)); end if;
if a.order_type_id is not null then param_gen_lib#.put(i_line => ' ,i_order_type_id => ' || param_gen_lib#.sql_val(a.order_type_id, param_gen_lib#.c_type_nr)); end if;
if a.ctx_action_intl_id is not null then param_gen_lib#.put(i_line => ' ,i_ctx_action_intl_id => ' || param_gen_lib#.sql_val(a.ctx_action_intl_id, param_gen_lib#.c_type_text)); end if;
if a.meta_proc_param is not null then param_gen_lib#.put(i_line => ' ,i_meta_proc_param => ' || param_gen_lib#.sql_val(a.meta_proc_param, param_gen_lib#.c_type_text)); end if;
if a.new_wfc_status_id is not null then param_gen_lib#.put(i_line => ' ,i_new_wfc_status_id => ' || param_gen_lib#.sql_val(a.new_wfc_status_id, param_gen_lib#.c_type_nr)); end if;
if a.descn is not null then param_gen_lib#.put(i_line => ' ,i_descn => ' || param_gen_lib#.sql_val(param_gen_lib#.sql_val(a.descn), param_gen_lib#.c_type_text)); end if;
if a.is_inactiv is not null then param_gen_lib#.put(i_line => ' ,i_is_inactiv => true'); end if;
if a.form_id is not null then param_gen_lib#.put(i_line => ' ,i_form_id => ' || param_gen_lib#.sql_val(a.form_id, param_gen_lib#.c_type_nr)); end if;
if a.in_single_login is not null then param_gen_lib#.put(i_line => ' ,i_in_single_login => true'); end if;
if a.extl_sys_id is not null then param_gen_lib#.put(i_line => ' ,i_extl_sys_id => ' || param_gen_lib#.sql_val(a.extl_sys_id, param_gen_lib#.c_type_nr)); end if;
if a.init_new_obj is not null then param_gen_lib#.put(i_line => ' ,i_init_new_obj => true'); end if;
param_gen_lib#.put(i_line => ' );');
end if;
l_text_rec := out_text#.text_rec('WFC_ACTION.' || i_meta_typ_id || '.' || a.id);
if l_text_rec.text.count = 0 then
l_text_rec.text(def_lang.automatic) := a.name;
end if;
l_action_text_tab(a.id) := l_text_rec;
end loop;
param_gen_lib#.put(i_line => ' ');
end if;
---- WFC STATUS ----
for s in (
select *
from wfc_status
where meta_typ_id = i_meta_typ_id
and id >= 0
order by id
) loop
param_gen_lib#.put(i_line => ' ---- WFC_STATUS_ID ' || s.id || ' :: ' || upper(s.name) || ' ----');
param_gen_lib#.put(i_line => ' param_gen#.add_wfc_status(');
param_gen_lib#.put(i_line => ' i_id => '||s.id);
param_gen_lib#.put(i_line => ' ,i_name => '||param_gen_lib#.sql_val(s.name ,param_gen_lib#.c_type_text));
param_gen_lib#.put(i_line => ' ,i_user_id => '||param_gen_lib#.sql_val(s.user_id ,param_gen_lib#.c_type_text));
if s.intl_id is not null then
param_gen_lib#.put(i_line => ' ,i_intl_id => '||param_gen_lib#.sql_val(s.intl_id ,param_gen_lib#.c_type_text));
end if;
if s.evt_status_id is not null then
param_gen_lib#.put(i_line => ' ,i_evt_status_id => '||param_gen_lib#.sql_val(s.evt_status_id));
end if;
if s.mp_xrate_status_id is not null then
param_gen_lib#.put(i_line => ' ,i_mp_xrate_status_id => '||param_gen_lib#.sql_val(s.mp_xrate_status_id));
end if;
if s.doc_edit_style_id is not null then
param_gen_lib#.put(i_line => ' ,i_doc_edit_style_id => '||param_gen_lib#.sql_val(s.doc_edit_style_id));
end if;
if s.is_frozen is not null then
param_gen_lib#.put(i_line => ' ,i_is_frozen => true');
end if;
if s.bgp_link is not null then
param_gen_lib#.put(i_line => ' ,i_bgp_link => true');
end if;
if s.is_in_work is null then
param_gen_lib#.put(i_line => ' ,i_is_in_work => false');
end if;
if s.wfc_grp_id is not null then
param_gen_lib#.put(i_line => ' ,i_wfc_grp_id => '||param_gen_lib#.sql_val(s.wfc_grp_id));
end if;
if s.form_id is not null then
param_gen_lib#.put(i_line => ' ,i_form => '||param_gen_lib#.sql_val(s.form_id));
end if;
if s.descn is not null then
param_gen_lib#.put(i_line => ' ,i_descn => '||param_gen_lib#.sql_val(param_gen_lib#.sql_val(s.descn), param_gen_lib#.c_type_text));
end if;
param_gen_lib#.put(i_line => ' );');
l_text_rec := out_text#.text_rec('WFC_STATUS.' || i_meta_typ_id || '.' || s.id);
if l_text_rec.text.count = 0 then
l_text_rec.text(def_lang.automatic) := s.name;
end if;
l_status_text_tab(s.id) := l_text_rec;
---- WFC ACTION ----
for a in (
select wa.*
,ca.intl_id ctx_action_intl_id
from ctx_action ca
,wfc_action wa
where wa.meta_typ_id = i_meta_typ_id
and wa.old_wfc_status_id = s.id
and ca.id (+) = wa.ctx_action_id
order by wa.order_by, wa.id
) loop
param_gen_lib#.put(i_line => ' param_gen#.add_wfc_action(');
param_gen_lib#.put(i_line => ' i_id => ' || a.id);
param_gen_lib#.put(i_line => ' ,i_name => ' || param_gen_lib#.sql_val(a.name, param_gen_lib#.c_type_text));
param_gen_lib#.put(i_line => ' ,i_order_by => ' || param_gen_lib#.sql_val(a.order_by));
param_gen_lib#.put(i_line => ' ,i_user_id => ' || param_gen_lib#.sql_val(a.user_id, param_gen_lib#.c_type_text));
if a.stmt is not null then
if coalesce(i_wfc_cmd_per_line, 0) > 0 then
stmt#wfc_cmd_tab(
o_wfc_cmd_tab => l_wfc_stmt_tab
,i_stmt => a.stmt
);
for i in 1 .. l_wfc_stmt_tab.count loop
if i = 1 then
param_gen_lib#.put(i_line => ' ,i_stmt => ' || param_gen_lib#.sql_val(l_wfc_stmt_tab(i), param_gen_lib#.c_type_text));
else
param_gen_lib#.put(i_line => ' || ' || param_gen_lib#.sql_val(l_wfc_stmt_tab(i), param_gen_lib#.c_type_text));
end if;
end loop;
else
param_gen_lib#.put(i_line => ' ,i_stmt => ' || param_gen_lib#.sql_val(a.stmt, param_gen_lib#.c_type_text));
end if;
end if;
if a.wfa_proc is not null then param_gen_lib#.put(i_line => ' ,i_wfa_proc => ' || param_gen_lib#.sql_val(a.wfa_proc, param_gen_lib#.c_type_text)); end if;
if a.intl_id is not null then param_gen_lib#.put(i_line => ' ,i_intl_id => ' || param_gen_lib#.sql_val(a.intl_id, param_gen_lib#.c_type_text)); end if;
if a.hyper_before is not null then param_gen_lib#.put(i_line => ' ,i_hyper_before => ' || param_gen_lib#.sql_val(a.hyper_before, param_gen_lib#.c_type_text)); end if;
if a.hyper_after is not null then param_gen_lib#.put(i_line => ' ,i_hyper_after => ' || param_gen_lib#.sql_val(a.hyper_after, param_gen_lib#.c_type_text)); end if;
if a.order_type_id is not null then param_gen_lib#.put(i_line => ' ,i_order_type_id => ' || param_gen_lib#.sql_val(a.order_type_id, param_gen_lib#.c_type_nr)); end if;
if a.ctx_action_intl_id is not null then param_gen_lib#.put(i_line => ' ,i_ctx_action_intl_id => ' || param_gen_lib#.sql_val(a.ctx_action_intl_id, param_gen_lib#.c_type_text)); end if;
if a.meta_proc_param is not null then param_gen_lib#.put(i_line => ' ,i_meta_proc_param => ' || param_gen_lib#.sql_val(a.meta_proc_param, param_gen_lib#.c_type_text)); end if;
if a.new_wfc_status_id is not null then param_gen_lib#.put(i_line => ' ,i_new_wfc_status_id => ' || param_gen_lib#.sql_val(a.new_wfc_status_id, param_gen_lib#.c_type_nr)); end if;
if a.descn is not null then param_gen_lib#.put(i_line => ' ,i_descn => ' || param_gen_lib#.sql_val(param_gen_lib#.sql_val(a.descn), param_gen_lib#.c_type_text)); end if;
if a.is_inactiv is not null then param_gen_lib#.put(i_line => ' ,i_is_inactiv => true'); end if;
if a.form_id is not null then param_gen_lib#.put(i_line => ' ,i_form_id => ' || param_gen_lib#.sql_val(a.form_id, param_gen_lib#.c_type_nr)); end if;
if a.in_single_login is not null then param_gen_lib#.put(i_line => ' ,i_in_single_login => true'); end if;
if a.extl_sys_id is not null then param_gen_lib#.put(i_line => ' ,i_extl_sys_id => ' || param_gen_lib#.sql_val(a.extl_sys_id, param_gen_lib#.c_type_nr)); end if;
if a.init_new_obj is not null then param_gen_lib#.put(i_line => ' ,i_init_new_obj => true'); end if;
param_gen_lib#.put(i_line => ' );');
---- WFC_ACTION - OUT_TEXT ----
l_action_text_tab(a.id) := out_text#.text_rec('WFC_ACTION.' || i_meta_typ_id || '.' || a.id);
if l_action_text_tab(a.id).text.count = 0 then
l_action_text_tab(a.id).text(def_lang.automatic) := a.name;
end if;
end loop;
param_gen_lib#.put(i_line => ' ');
end loop;
param_gen_lib#.put(i_line => 'exception');
param_gen_lib#.put(i_line => ' when others then');
param_gen_lib#.put(i_line => ' rollback;');
param_gen_lib#.put(i_line => ' install#.log#write(''ERROR: ' || upper(meta_typ#.meta_typ#intl_id(i_meta_typ_id)) || ' WORKFLOW PART 1 CRASHED'');');
param_gen_lib#.put(i_line => ' raise;');
param_gen_lib#.put(i_line => 'end;');
param_gen_lib#.put(i_line => '/');
param_gen_lib#.put(i_line => ' ');
param_gen_lib#.put(i_line => 'begin');
param_gen_lib#.put(i_line => ' -------------------------------------------------------------------------------------');
param_gen_lib#.put(i_line => ' ---- WFC_RULE_LD (rule_set = '''||upper(i_rule_set)||''')');
param_gen_lib#.put(i_line => ' -------------------------------------------------------------------------------------');
for s in (
select *
from wfc_rule_ld
where meta_typ_id = i_meta_typ_id
and trim(action) is not null
and lower(rule_set) = lower(i_rule_set)
order by action, nvl(wfc_action,chr(255)), prio desc, nvl(wfc_status,chr(255))
,nvl(cond,chr(255)), order_type, nvl(asset_class,chr(255)), nvl(bp_class,chr(255))
,nvl(cont_class,chr(255)), curry, qty desc, gross desc, has_xrate
) loop
if s.action <> nvl(l_last_action,'-') then param_gen_lib#.put(i_line => ' ---- ' || s.action || ' ---- '); end if;
l_last_action := s.action;
if s.prio < l_lowest_action_prio then l_lowest_action_prio := s.prio; end if;
l_rule_set := upper(s.rule_set);
param_gen_lib#.put(i_line => ' param_gen#.add_wfc_rule(');
param_gen_lib#.put(i_line => ' i_meta_typ_id => ' || param_gen_lib#.sql_val(s.meta_typ_id, param_gen_lib#.c_type_text));
if s.rule_set is not null then param_gen_lib#.put(i_line => ' ,i_rule_set => ' || param_gen_lib#.sql_val(l_rule_set, param_gen_lib#.c_type_text)); end if;
if s.action is not null then param_gen_lib#.put(i_line => ' ,i_action => ' || param_gen_lib#.sql_val(s.action, param_gen_lib#.c_type_text)); end if;
if s.wfc_action is not null then param_gen_lib#.put(i_line => ' ,i_wfc_action => ' || param_gen_lib#.sql_val(s.wfc_action, param_gen_lib#.c_type_text)); end if;
if s.order_type is not null then param_gen_lib#.put(i_line => ' ,i_order_type => ' || param_gen_lib#.sql_val(s.order_type, param_gen_lib#.c_type_text)); end if;
if s.wfc_status is not null then param_gen_lib#.put(i_line => ' ,i_wfc_status => ' || param_gen_lib#.sql_val(s.wfc_status, param_gen_lib#.c_type_text)); end if;
if s.bp_class is not null then param_gen_lib#.put(i_line => ' ,i_bp_class => ' || param_gen_lib#.sql_val(s.bp_class, param_gen_lib#.c_type_text)); end if;
if s.cont_class is not null then param_gen_lib#.put(i_line => ' ,i_cont_class => ' || param_gen_lib#.sql_val(s.cont_class, param_gen_lib#.c_type_text)); end if;
if s.asset_class is not null then param_gen_lib#.put(i_line => ' ,i_asset_class => ' || param_gen_lib#.sql_val(s.asset_class, param_gen_lib#.c_type_text)); end if;
if s.qty is not null then param_gen_lib#.put(i_line => ' ,i_qty => ' || param_gen_lib#.sql_val(s.qty, param_gen_lib#.c_type_text)); end if;
if s.has_xrate is not null then param_gen_lib#.put(i_line => ' ,i_has_xrate => ' || param_gen_lib#.sql_val(s.has_xrate, param_gen_lib#.c_type_text)); end if;
if s.curry is not null then param_gen_lib#.put(i_line => ' ,i_curry => ' || param_gen_lib#.sql_val(s.curry, param_gen_lib#.c_type_text)); end if;
if s.gross is not null then param_gen_lib#.put(i_line => ' ,i_gross => ' || param_gen_lib#.sql_val(s.gross, param_gen_lib#.c_type_text)); end if;
if s.cond is not null then param_gen_lib#.put(i_line => ' ,i_cond => ' || param_gen_lib#.sql_val(s.cond, param_gen_lib#.c_type_text)); end if;
if s.stmt is not null then
if coalesce(i_wfc_cmd_per_line, 0) > 0 then
stmt#wfc_cmd_tab(
o_wfc_cmd_tab => l_wfc_stmt_tab
,i_stmt => s.stmt
);
for i in 1 .. l_wfc_stmt_tab.count loop
if i = 1 then
param_gen_lib#.put(i_line => ' ,i_stmt => ' || param_gen_lib#.sql_val(l_wfc_stmt_tab(i), param_gen_lib#.c_type_text));
else
param_gen_lib#.put(i_line => ' || ' || param_gen_lib#.sql_val(l_wfc_stmt_tab(i), param_gen_lib#.c_type_text));
end if;
end loop;
else
param_gen_lib#.put(i_line => ' ,i_stmt => ' || param_gen_lib#.sql_val(s.stmt, param_gen_lib#.c_type_text));
end if;
end if;
if s.valid_from is not null then param_gen_lib#.put(i_line => ' ,i_valid_from => ' || param_gen_lib#.sql_val(s.valid_from, param_gen_lib#.c_type_text)); end if;
if s.valid_to is not null then param_gen_lib#.put(i_line => ' ,i_prio => ' || param_gen_lib#.sql_val(s.valid_to, param_gen_lib#.c_type_text)); end if;
if s.prio is not null then param_gen_lib#.put(i_line => ' ,i_prio => ' || param_gen_lib#.sql_val(s.prio, param_gen_lib#.c_type_text)); end if;
if s.rule_a is not null then param_gen_lib#.put(i_line => ' ,i_rule_a => ' || param_gen_lib#.sql_val(s.rule_a, param_gen_lib#.c_type_text)); end if;
if s.descn is not null then param_gen_lib#.put(i_line => ' ,i_descn => ' || param_gen_lib#.sql_val(s.descn, param_gen_lib#.c_type_text)); end if;
if s.err is not null then param_gen_lib#.put(i_line => ' ,i_err => ' || param_gen_lib#.sql_val(s.err, param_gen_lib#.c_type_text)); end if;
param_gen_lib#.put(i_line => ' );');
param_gen_lib#.put(i_line => ' ');
end loop;
param_gen_lib#.put(i_line => ' ');
---- GENERIC RULES ----
select count(*)
into l_cnt
from wfc_rule_ld
where meta_typ_id = i_meta_typ_id
and trim(action) is null
and lower(rule_set) = lower(i_rule_set)
order by nvl(wfc_action,chr(255)), prio desc, nvl(wfc_status,chr(255))
,nvl(cond,chr(255)), order_type, nvl(asset_class,chr(255)), nvl(bp_class,chr(255))
,nvl(cont_class,chr(255)), curry, qty desc, gross desc, has_xrate;
if l_cnt > 0 then
param_gen_lib#.put(i_line => ' ---- Generic rules (no action) ---- ');
for s in (
select *
from wfc_rule_ld
where meta_typ_id = i_meta_typ_id
and trim(action) is null
and lower(rule_set) = lower(i_rule_set)
order by nvl(wfc_action,chr(255)), prio desc, nvl(wfc_status,chr(255))
,nvl(cond,chr(255)), order_type, nvl(asset_class,chr(255)), nvl(bp_class,chr(255))
,nvl(cont_class,chr(255)), curry, qty desc, gross desc, has_xrate
) loop
if s.prio > l_highest_no_action_prio then l_highest_no_action_prio := s.prio; end if;
l_rule_set := upper(s.rule_set);
param_gen_lib#.put(i_line => ' param_gen#.add_wfc_rule(');
param_gen_lib#.put(i_line => ' i_meta_typ_id => ' || param_gen_lib#.sql_val(s.meta_typ_id, param_gen_lib#.c_type_text));
if s.rule_set is not null then param_gen_lib#.put(i_line => ' ,i_rule_set => ' || param_gen_lib#.sql_val(l_rule_set, param_gen_lib#.c_type_text)); end if;
if s.wfc_action is not null then param_gen_lib#.put(i_line => ' ,i_wfc_action => ' || param_gen_lib#.sql_val(s.wfc_action, param_gen_lib#.c_type_text)); end if;
if s.order_type is not null then param_gen_lib#.put(i_line => ' ,i_order_type => ' || param_gen_lib#.sql_val(s.order_type, param_gen_lib#.c_type_text)); end if;
if s.wfc_status is not null then param_gen_lib#.put(i_line => ' ,i_wfc_status => ' || param_gen_lib#.sql_val(s.wfc_status, param_gen_lib#.c_type_text)); end if;
if s.bp_class is not null then param_gen_lib#.put(i_line => ' ,i_bp_class => ' || param_gen_lib#.sql_val(s.bp_class, param_gen_lib#.c_type_text)); end if;
if s.cont_class is not null then param_gen_lib#.put(i_line => ' ,i_cont_class => ' || param_gen_lib#.sql_val(s.cont_class, param_gen_lib#.c_type_text)); end if;
if s.asset_class is not null then param_gen_lib#.put(i_line => ' ,i_asset_class => ' || param_gen_lib#.sql_val(s.asset_class, param_gen_lib#.c_type_text)); end if;
if s.qty is not null then param_gen_lib#.put(i_line => ' ,i_qty => ' || param_gen_lib#.sql_val(s.qty, param_gen_lib#.c_type_text)); end if;
if s.has_xrate is not null then param_gen_lib#.put(i_line => ' ,i_has_xrate => ' || param_gen_lib#.sql_val(s.has_xrate, param_gen_lib#.c_type_text)); end if;
if s.curry is not null then param_gen_lib#.put(i_line => ' ,i_curry => ' || param_gen_lib#.sql_val(s.curry, param_gen_lib#.c_type_text)); end if;
if s.gross is not null then param_gen_lib#.put(i_line => ' ,i_gross => ' || param_gen_lib#.sql_val(s.gross, param_gen_lib#.c_type_text)); end if;
if s.cond is not null then param_gen_lib#.put(i_line => ' ,i_cond => ' || param_gen_lib#.sql_val(s.cond, param_gen_lib#.c_type_text)); end if;
if s.stmt is not null then param_gen_lib#.put(i_line => ' ,i_stmt => ' || param_gen_lib#.sql_val(s.stmt, param_gen_lib#.c_type_text)); end if;
if s.valid_from is not null then param_gen_lib#.put(i_line => ' ,i_valid_from => ' || param_gen_lib#.sql_val(s.valid_from, param_gen_lib#.c_type_text)); end if;
if s.valid_to is not null then param_gen_lib#.put(i_line => ' ,i_prio => ' || param_gen_lib#.sql_val(s.prio, param_gen_lib#.c_type_text)); end if;
if s.prio is not null then param_gen_lib#.put(i_line => ' ,i_prio => ' || param_gen_lib#.sql_val(s.prio, param_gen_lib#.c_type_text)); end if;
if s.rule_a is not null then param_gen_lib#.put(i_line => ' ,i_rule_a => ' || param_gen_lib#.sql_val(s.rule_a, param_gen_lib#.c_type_text)); end if;
if s.descn is not null then param_gen_lib#.put(i_line => ' ,i_descn => ' || param_gen_lib#.sql_val(s.descn, param_gen_lib#.c_type_text)); end if;
if s.err is not null then param_gen_lib#.put(i_line => ' ,i_err => ' || param_gen_lib#.sql_val(s.err, param_gen_lib#.c_type_text)); end if;
param_gen_lib#.put(i_line => ' );');
param_gen_lib#.put(i_line => ' ');
end loop;
end if;
l_sub_title_set := false;
if l_highest_no_action_prio > l_lowest_action_prio then
param_gen_lib#.put(i_comment => true, i_line => ' ');
param_gen_lib#.put(i_comment => true, i_line => ' Warning: The following rules with actions may be overridden by no-action-rules with');
param_gen_lib#.put(i_comment => true, i_line => ' higher priority. The possibly overridden rules are:');
param_gen_lib#.put(i_comment => true, i_line => ' ');
for u in (
select *
from wfc_rule_ld
where meta_typ_id = i_meta_typ_id
and prio > l_lowest_action_prio
and prio < l_highest_no_action_prio
and lower(rule_set) = lower(i_rule_set)
order by decode(action,null,0,1), prio desc, wfc_status, order_type, asset_class
) loop
if u.action is null and not l_sub_title_set then
param_gen_lib#.put(i_comment => true, i_line => ' ');
param_gen_lib#.put(i_comment => true, i_line => ' The no-action-rules which possibly override action-rules are listed here:');
param_gen_lib#.put(i_comment => true, i_line => ' ');
l_sub_title_set := true;
end if;
param_gen_lib#.put(i_comment => true, i_line => ' select * from k.wfc_rule_ld where meta_typ_id = '||u.meta_typ_id);
param_gen_lib#.put(i_comment => true, i_line => ' and lower(rule_set) ='''||lower(u.rule_set)||'''');
if u.action is not null then param_gen_lib#.put(i_comment => true, i_line => ' and action = '''||u.action||''''); end if;
if u.order_type is not null then param_gen_lib#.put(i_comment => true, i_line => ' and order_type = '''||u.order_type||''''); end if;
if u.wfc_status is not null then param_gen_lib#.put(i_comment => true, i_line => ' and wfc_status = '''||u.wfc_status||''''); end if;
if u.wfc_action is not null then param_gen_lib#.put(i_comment => true, i_line => ' and wfc_action = '''||u.wfc_action||''''); end if;
if u.bp_class is not null then param_gen_lib#.put(i_comment => true, i_line => ' and bp_class = '''||u.bp_class||''''); end if;
if u.cont_class is not null then param_gen_lib#.put(i_comment => true, i_line => ' and cont_class = '''||u.cont_class||''''); end if;
if u.asset_class is not null then param_gen_lib#.put(i_comment => true, i_line => ' and asset_class = '''||u.asset_class||''''); end if;
if u.qty is not null then param_gen_lib#.put(i_comment => true, i_line => ' and qty = '''||u.qty||''''); end if;
if u.has_xrate is not null then param_gen_lib#.put(i_comment => true, i_line => ' and has_xrate = '''||u.has_xrate||''''); end if;
if u.curry is not null then param_gen_lib#.put(i_comment => true, i_line => ' and curry = '''||u.curry||''''); end if;
if u.gross is not null then param_gen_lib#.put(i_comment => true, i_line => ' and gross = '''||u.gross||''''); end if;
if u.cond is not null then param_gen_lib#.put(i_comment => true, i_line => ' and cond = '''||u.cond||''''); end if;
if u.stmt is not null then param_gen_lib#.put(i_comment => true, i_line => ' and stmt = '''||u.stmt||''''); end if;
if u.valid_from is not null then param_gen_lib#.put(i_comment => true, i_line => ' and valid_from = '''||u.valid_from||''''); end if;
if u.valid_to is not null then param_gen_lib#.put(i_comment => true, i_line => ' and valid_to = '''||u.valid_to||''''); end if;
if u.prio is not null then param_gen_lib#.put(i_comment => true, i_line => ' and prio = '''||u.prio||''''); end if;
if u.rule_a is not null then param_gen_lib#.put(i_comment => true, i_line => ' and rule_a = '''||u.rule_a||''''); end if;
if u.descn is not null then param_gen_lib#.put(i_comment => true, i_line => ' and descn = '''||u.descn||''''); end if;
end loop;
end if;
param_gen_lib#.put(i_line => ' ---- CLOSE WFC ----');
param_gen_lib#.put(i_line => ' param_gen#.close_wfc(' || i_meta_typ_id || ');');
param_gen_lib#.put(i_line => ' commit;');
param_gen_lib#.put(i_line => 'exception');
param_gen_lib#.put(i_line => ' when others then');
param_gen_lib#.put(i_line => ' rollback;');
param_gen_lib#.put(i_line => ' install#.log#write(''ERROR: ' || upper(meta_typ#.meta_typ#intl_id(i_meta_typ_id)) || ' WORKFLOW PART 2 CRASHED'');');
param_gen_lib#.put(i_line => ' raise;');
param_gen_lib#.put(i_line => 'end;');
param_gen_lib#.put(i_line => '/');
param_gen_lib#.put(i_line => ' ');
---- RULE LOADER ----
param_gen_lib#.put(i_line => 'exec install#.log#write(''LOAD ' || upper(meta_typ#.meta_typ#intl_id(i_meta_typ_id)) || ' WORKFLOW RULES'');');
param_gen_lib#.put(i_line => ' ');
param_gen_lib#.put(i_line => 'begin');
param_gen_lib#.put(i_line => ' if not install#.install_mode#is_rel then');
param_gen_lib#.put(i_line => ' ---- OPEN SESSION ----');
param_gen_lib#.put(i_line => ' session#.open_session;');
param_gen_lib#.put(i_line => ' ');
param_gen_lib#.put(i_line => ' ---- LOAD ----');
param_gen_lib#.put(i_line => ' wfc_rule_ld#.ld(''' ||upper(i_rule_set)|| ''', ' || i_meta_typ_id || ');');
param_gen_lib#.put(i_line => ' end if;');
param_gen_lib#.put(i_line => 'exception');
param_gen_lib#.put(i_line => ' when others then');
param_gen_lib#.put(i_line => ' rollback;');
param_gen_lib#.put(i_line => ' install#.log#error(''LOAD OF ' || upper(meta_typ#.meta_typ#intl_id(i_meta_typ_id)) || ' WORKFLOW RULES CRASHED'');');
param_gen_lib#.put(i_line => 'end;');
param_gen_lib#.put(i_line => '/');
---- OUT_TEXT ----
param_gen_lib#.put(i_line => ' ');
param_gen_lib#.put(i_line => 'begin');
param_gen_lib#.put(i_line => ' ---- WFC_STATUS TEXTS ----');
param_gen_lib#.put(i_line => ' out_text#.start_migr;');
param_gen_lib#.put(i_line => ' out_text#.remv(i_like => true, i_key => ''WFC_STATUS.' || i_meta_typ_id || '.%'');');
param_gen_lib#.put(i_line => ' param_gen#.set_text_prefix(''WFC_STATUS.' || i_meta_typ_id || '.'');');
i := l_status_text_tab.first;
while i is not null loop
l_text_rec := l_status_text_tab(i);
l_stmt := ' param_gen#.add_text(i_key => ' || '''' || trim(lpad(i, 10)) || '''';
l_stmt := l_stmt || ',i_text => out_text#.unpack(' || param_gen_lib#.sql_val(out_text#.pack(l_text_rec), param_gen_lib#.c_type_text) ||')';
l_stmt := l_stmt || ');';
param_gen_lib#.put(i_line => l_stmt);
i := l_status_text_tab.next(i);
end loop;
param_gen_lib#.put(i_line => ' ');
param_gen_lib#.put(i_line => ' ---- WFC_ACTION TEXTS ----');
param_gen_lib#.put(i_line => ' out_text#.remv(i_like => true, i_key => ''WFC_ACTION.' || i_meta_typ_id || '.%'');');
param_gen_lib#.put(i_line => ' param_gen#.set_text_prefix(''WFC_ACTION.' || i_meta_typ_id || '.'');');
i := l_action_text_tab.first;
while i is not null loop
l_text_rec := l_action_text_tab(i);
l_stmt := ' param_gen#.add_text(i_key => ' || '''' || trim(lpad(i, 10)) || '''';
l_stmt := l_stmt || ',i_text => out_text#.unpack(' || param_gen_lib#.sql_val(out_text#.pack(l_text_rec), param_gen_lib#.c_type_text) || ')';
l_stmt := l_stmt || ');';
param_gen_lib#.put(i_line => l_stmt);
i := l_action_text_tab.next(i);
end loop;
param_gen_lib#.put(i_line => ' out_text#.end_migr;');
param_gen_lib#.put(i_line => ' commit;');
param_gen_lib#.put(i_line => 'exception');
param_gen_lib#.put(i_line => ' when others then');
param_gen_lib#.put(i_line => ' out_text#.end_migr;');
param_gen_lib#.put(i_line => ' raise;');
param_gen_lib#.put(i_line => 'end;');
param_gen_lib#.put(i_line => '/');
param_gen_lib#.reset;
exception
when others then
raise_fa_err('gen_wfc(' || i_meta_typ_id || ', ''' || i_rule_set || ''')');
end gen_wfc;
end rbsc$param_gen#;
