create or replace package body odwaxist is

/*****************************************************************************************
Purpose         Package to help construct the short_cut bar in every report
                In general it will help answer questions with respect to the
                existence of certain elements

Usage           Called from every CDWP_<element type< package to construct its
                shortcut bar or icon bar

Remarks         Uses NDS, Execute Immediate (8.1.5 and higher only)

Revision history
When        Who                                Construct
  Revision  What
------------------------------------------------------------------------------------------
$REVISON_HISTORY$
24-jul-2000 Lucas Jellema
1.1         Add p_parent_ivid as parameter
24-jul-2000 Lucas Jellema
1.0         initial creation for ODWA 6i
14-jul-2000 Lucas Jellema

1.0
1.1
- add p_ivid as parameter (also in Specification)

*****************************************************************************************/

--
-- private constants
--
REVISION_LABEL        constant varchar2(30) := '$x.y:: 1.1                 ';
PACKAGE_NAME          constant varchar2(30) := 'ODWAXIST';

-- this function tells whether or not elements of the specified type exist
-- as details for the p_pac_irid PAC
-- normally the reference from a detail to its PAC is through the column PAC_REFERENCE in the CI_VIEW
-- however, you pass in a different referencing column, find out about other existence; e.g. to find
-- out whether there are Foreign Keys referencing a certain element ( exists_detail( irid, 'FCO','FOREIGN_TABLE_REFERENCE'))
-- , or Functions using a certain Entity ( exists_detail( irid, 'FUNENT','ENTITY_REFERENCE'))
function exists_detail
( p_pac_irid   in number
, p_pac_ivid   in number   default null
, p_el_type_of in varchar2 default null
, p_el_type_id in varchar2 default null
, p_ref_column in varchar2 default 'PAC_REFERENCE'
, p_extra_where in varchar2 default null
) return boolean
is
  l_tbl_name varchar2(100);
  l_sql      varchar2(4000);
begin
  -- first find out in which table p_el_type_id is hiding
  l_tbl_name:= cdwpbase.ifThenElse
               ( p_el_type_of is null
               , cdwpbase.get_ci_view(p_type_id         => p_el_type_id)
               , cdwpbase.get_ci_view
                 ( p_type_id         => cdwpbase.short_name_to_type_id( p_el_type_of)
                 )
               );
  -- now find out whether l_tbl_name contains at least one entry with pac_ref = p_pac_irid
  l_sql:=
  ' declare
      cursor c_xst is
        select 1
        from   dual
        where  exists
               ( select 1
                 from   '||l_tbl_name
             ||' where  '||p_ref_column||' = '||to_char(p_pac_irid)
             ||cdwpbase.ifThenElse
               ( p_pac_ivid is not null
               , ' and PARENT_IVID = '||to_char(p_pac_ivid)
               )
             ||cdwpbase.ifThenElse
               ( p_extra_where is not null
               , ' and '||p_extra_where
               )
||'            )
        ;
      r_xst c_xst%rowtype;
    begin
      open c_xst;
      fetch c_xst
      into  r_xst;
      odwaxist.g_exist:= c_xst%found;
      close c_xst;
    end;
    ';
  execute immediate l_sql;
  return odwaxist.g_exist;
end; -- exists_detail

end; -- package body odwaxist
/