Package body blocking task
dr package body zum blocking task
create or replace package body k.rbsc$blocking
is
/*
-------------------------------------------------------------------------------------------------------------
Bank: Coutts & Co Ltd.
Business Area: SDA
Description: Static Data API for blocking objects
-------------------------------------------------------------------------------------------------------------
ID Date Name, Company Issue Affected BUs, Description
-------------------------------------------------------------------------------------------------------------
001
-------------------------------------------------------------------------------------------------------------
*/
-------------------------------------------------------------------------------------------------------------
-- BUILD DTM MAIN PROCEDURE
-------------------------------------------------------------------------------------------------------------
procedure do_layout(
i_out_job_id pls_integer
,i_layout_list_id pls_integer
,i_bu_id pls_integer
,i_obj_status_id pls_integer
,i_obj_type_list_id pls_integer
,i_obj_sub_type_incl_id pls_integer
,i_obj_sub_type_excl_id pls_integer
,i_block_incl_id pls_integer
,i_block_excl_id pls_integer
)
is
l_dtm lib_co_sel.extn_rec;
------------------------------------------------
-- INIT
------------------------------------------------
procedure init
is
begin
if i_layout_list_id is not null then
layout#.chk_param(i_layout_list_id);
end if;
exception
when co_err.ui_err then raise;
when others then raise_fa_err('rbsc$blocking.init');
end init;
------------------------------------------------
-- GENERATE DTM
------------------------------------------------
procedure gen_dtm
is
begin
l_dtm.decl_stmt := '';
---- SELECT ----
l_dtm.sel_clause := '/*+leading(ob)*/
ob.bu_id
,ob.obj_type_id
,ob.obj_sub_type_id
,ob.id obj_id
,ob.block_id
,ob.block block_text
,r.remark';
---- FROM ----
l_dtm.from_clause := '
obj ob
,obj_remark r';
---- WHERE ----
l_dtm.where_clause := 'ob.block_id is not null
and r.obj_id (+)= ob.id
and r.remark_type_id is null
and r.timestamp (+)= to_date(1, ''j'')';
if i_bu_id is not null then
l_dtm.where_clause := l_dtm.where_clause || ' and ob.bu_id = ' || i_bu_id;
end if;
if i_obj_status_id = def_const.activ then
l_dtm.where_clause := l_dtm.where_clause || ' and ob.close_date is null';
elsif i_obj_status_id = def_const.inactiv then
l_dtm.where_clause := l_dtm.where_clause || ' and ob.close_date is not null';
end if;
if i_obj_type_list_id is not null then
l_dtm.where_clause := l_dtm.where_clause || ' and ob.obj_type_id in (select val from list_number where id = ' || i_obj_type_list_id || ')';
end if;
if i_obj_sub_type_incl_id is not null then
l_dtm.where_clause := l_dtm.where_clause || ' and nvl(ob.obj_sub_type_id,0) in (select mod(val, 10000) from list_number where id = ' || i_obj_sub_type_incl_id || ')';
end if;
if i_obj_sub_type_excl_id is not null then
l_dtm.where_clause := l_dtm.where_clause || ' and nvl(ob.obj_sub_type_id,-1) not in (select mod(val, 10000) from list_number where id = ' || i_obj_sub_type_excl_id || ')';
end if;
if i_block_incl_id is not null then
l_dtm.where_clause := l_dtm.where_clause || ' and ob.block_id in (select val from list_number where id = ' || i_block_incl_id || ')';
end if;
if i_block_excl_id is not null then
l_dtm.where_clause := l_dtm.where_clause || ' and ob.block_id in (select val from list_number where id = ' || i_block_excl_id || ')';
end if;
exception
when others then
raise_fa_err('gen_dtm');
end gen_dtm;
------------------------------------------------
-- GENERATE LAYOUT
------------------------------------------------
procedure gen_layout
is
begin
layout#.do(
i_task_id => i_out_job_id
,i_lang_id => co_session.get_lang_id
,i_dtm => l_dtm
,i_layout_list_id => i_layout_list_id
);
exception
when others then
raise_fa_err('gen_layout');
end gen_layout;
------------------------------------------------
-- DO LAYOUT
------------------------------------------------
begin
init;
gen_dtm;
gen_layout;
exception
when others then
raise_fa_err('do_layout');
end do_layout;
end rbsc$blocking;
/
