--
-- Revision History
-- Label      Date         Who                  What
------------------------------------------------------------------------------------------
--            18-jul-2002  ReposDev             Changes to enable for RAU load
------------------------------------------------------------------------------------------

create or replace package body parse4deps is

/*****************************************************************************************
Purpose         Parse structured elements for dependencies according to the Meta Model Definition
                Recognize the XML style user defined dependencies in Multi Line Text

Usage           Installed under Repository Owner schema

Revision history
When        Who                                Construct
  Revision  What
------------------------------------------------------------------------------------------
$REVISION_HISTORY$
21-mar-2001   Lucas Jellema                     
  1.0         Initial Creation
*****************************************************************************************/

g_deptype_provides number(38);
g_deptype_uses number(38);
g_typeid_sdd_global_names number;

TYPE t_num_tbl IS 
 TABLE OF number(38)
 index by binary_integer
 ;
 
 g_det_type_tbl t_num_tbl;
 g_ref_type_tbl t_num_tbl;
 

cursor c_details
       ( b_type_id in number
       ) is
  select distinct
         'i$'||tab2.table_name tab_name
  ,      tab2.irid tab_irid
  ,      con2.is_owning_fk
  ,      col.column_name
  ,      et2.irid  detail_type_id
  from   i$RM_SQL_TABLES tab1
  ,      i$RM_SQL_TABLES tab2
  ,      i$RM_SQL_CONSTRAINTS con1
  ,      i$RM_SQL_CONSTRAINTS con2
  ,      rm_element_types et
  ,      i$rm_sql_row_types srt
  ,      rm_element_types et2
  ,      i$rm_sql_row_types srt2
  ,      i$RM_SQL_CONS_COLUMNS col
  where  et.irid = b_type_id
  and    srt.id  = et.primary_row_type
  and    con1.table_irid  = srt.table_mapped
  and    tab2.irid=con2.table_irid
  and    con2.r_constraint_name=con1.constraint_name
  and    con2.is_owning_fk!='N'
  and    col.constraint_irid=con2.irid
  and    tab2.irid!= con1.table_irid
  and    srt2.table_mapped = tab2.irid
  and    et2.primary_row_type = srt2.id
  ;


   cursor c_prp_in_ete
   ( b_tbl_irid in  number
   , b_type_id  in number
   )
   is
   select distinct
          prp.name        prp_name
   ,      prp.id
   ,      col.column_name
   ,      dtt.data_type
   ,      pex.nls_name      -- PROMPT
   ,      pex.display_sequence
   ,      prp.user_extension
--   ,      prp.defined_against  -- element_type ; compare with value of types property
   ,      lnk_from.id          lnk_from_id
   ,      lnk_to.defined_against reffed_type_id -- the referenced element
   ,      lnk_from.arc_no
   from   rm_data_types   dtt
   ,      rm_property_extensions pex
   ,      i$rm_sql_columns       col
   ,      i$rm_property_maps     pm
   ,      i$rm_properties        prp
   ,      rm_link_properties     lnk_from
   ,      rm_link_properties     lnk_to
   where  col.table_irid       = b_tbl_irid
   and    col.id               = pm.in_column
   and    pm.property          = prp.id
   and    pex.for_property    = prp.id
   and    prp.of_domain       = dtt.id
   and    prp.defined_against  in ( select id from rm_Element_Types
                                           start with id = b_type_id
                                           connect by id = prior supertypes)
   and    prp.is_stored       = 'Y'
   and    prp.id              = lnk_from.id
   and    lnk_to.link_type    = lnk_from.link_type
   ;
  
  
 cursor c_gnm
        ( b_ivid in number
        ) is
  select nm.name            global_name
  ,      nmd.name           global_name_domain_name
  ,      et.name            element_type_name
  ,      et.id              element_type_id
  ,      nmd.irid           name_domain_irid
  ,      nvl( nmd.name, et.name) type_label
  ,      d.supplier_ivid    
  ,      d.supplier_irid    
  ,      nm.irid            nm_irid
  ,      nm.ivid            nm_ivid
  from   sdd_dependencies d
  ,      sdd_global_names nm
  ,      sdd_global_name_domains nmd
  ,      rm_element_types et
  where  nm.irid = d.client_irid
  and    d.supplier_ivid = b_ivid
  and    nm.domain_irid = nmd.irid(+)
  and    nm.type_id     = et.id(+)
  and    d.dependency_type_irid = g_deptype_provides
  ;
 cursor c_gnm_for_irid
        ( b_irid in number
        ) is
  select nm.name            global_name
  ,      nmd.name           global_name_domain_name
  ,      et.name            element_type_name
  ,      et.id              element_type_id
  ,      nmd.irid           name_domain_irid
  ,      nvl( nmd.name, et.name) type_label
  ,      d.supplier_ivid    
  ,      d.supplier_irid    
  ,      nm.irid            nm_irid
  ,      nm.ivid            nm_ivid
  from   sdd_dependencies d
  ,      sdd_global_names nm
  ,      sdd_global_name_domains nmd
  ,      rm_element_types et
  where  nm.irid = d.client_irid
  and    d.supplier_irid = b_irid
  and    nm.domain_irid = nmd.irid(+)
  and    nm.type_id     = et.id(+)
  and    d.dependency_type_irid = g_deptype_provides
  ;
 r_gnm c_gnm%rowtype;  

 cursor c_ov
        ( b_ivid in number
        ) is
  select ov.irid
  ,      ov.logical_type_id
  ,      ov.name
  ,      ov.ivid
  ,      ov.notm
  ,      ov.obj_notm
  ,      ov.obj_notm_when_analyzed
  from   i$sdd_object_versions ov
  where  ov.ivid = b_ivid
  ;
 r_ov c_ov%rowtype;  

 cursor c_dte -- dependency_types
        ( b_type in varchar2
        , b_subtype in varchar2
        ) is
   select irid
   ,      ivid
   ,      type
   ,      subtype
   from   sdd_dependency_types dte
   where  lower(dte.type) = lower(b_type)
   and    nvl(lower(dte.subtype),'x') = nvl(lower(b_subtype),'x')
   ;
 r_dte c_dte%rowtype;

 procedure reset_type_tbl
 is
 begin
   g_det_type_tbl.delete;
   g_ref_type_tbl.delete;
 end;
 
 procedure add_det_type
 ( p_type_id in number
 ) is
 begin
   g_det_type_tbl( g_det_type_tbl.count + 1):= p_type_id;
 end;
 
 function exists_in_det_table
 ( p_type_id in number
 ) return boolean
 is
 begin
   for i in 1..g_det_type_tbl.count loop
     if g_det_type_tbl(i) = p_type_id
     then
       return true;
     end if;
   end loop;
   return false;
 end;
 
 
 procedure add_ref_type
 ( p_type_id in number
 ) is
 begin
   g_ref_type_tbl( g_ref_type_tbl.count + 1):= p_type_id;
 end;
 
 function exists_in_ref_table
 ( p_type_id in number
 ) return boolean
 is
 begin
   for i in 1..g_ref_type_tbl.count loop
     if g_ref_type_tbl(i) = p_type_id
     then
       return true;
     end if;
   end loop;
   return false;
 end;

 
procedure get_gnm
( p_ivid in number
) is
begin
  if nvl(r_gnm.supplier_ivid,-1) <> p_ivid
  then
    open c_gnm( b_ivid=> p_ivid);
    fetch c_gnm
    into  r_gnm;
    close c_gnm;
  end if;
end; -- get_gnm  

procedure get_gnm_for_irid
( p_irid in number
) is
begin
  if nvl(r_gnm.nm_irid,-1) <> p_irid
  then
    open c_gnm_for_irid( b_irid=> p_irid);
    fetch c_gnm_for_irid
    into  r_gnm;
    close c_gnm_for_irid;
  end if;
end; -- get_gnm_for_irid

procedure get_ov
( p_ivid in number
) is
begin
  if nvl(r_ov.ivid,-1) <> p_ivid
  then
    open c_ov( b_ivid=> p_ivid);
    fetch c_ov
    into  r_ov;
    close c_ov;
  end if;
end; -- get_ov


function ifThenElse
( p_if   in boolean
, p_then in varchar2
, p_else in varchar2 default null
) return varchar2
is
begin
  if p_if
  then
    return p_then;
  else
    return p_else;
  end if;
end; -- ifThenElse

-- this functions returns a number based on the true or falseness of p_condition
function ifThenElse
( p_if   in boolean
, p_then in number
, p_else in number default null
) return number
is
begin
  if p_if
  then
    return p_then;
  else
    return p_else;
  end if;
end; -- ifThenElse

  
function get_global_name
( p_id in number
) return varchar2
is
begin
  get_gnm( p_ivid=> p_id);
  if r_gnm.global_name is null
  then
    get_gnm_for_irid( p_irid=> p_id);
  end if;
  return r_gnm.global_name;
end; -- get_global_name

function get_global_name_ivid
( p_gne_irid in number
) return number
is
  cursor c_gne
         ( b_gne_irid in number
         ) is
    select gne.ivid
    from   sdd_global_names gne
    where  gne.irid = b_gne_irid
    ;
  r_gne c_gne%rowtype;
begin
  open c_gne( b_gne_irid => p_gne_irid);
  fetch c_gne
  into  r_gne;
  close c_gne;
  return r_gne.ivid;
end; -- get_global_name_ivid

function get_global_type_label
( p_id in number
) return varchar2
is
begin
  get_gnm( p_ivid=> p_id);
  if r_gnm.global_name is null
  then
    get_gnm_for_irid( p_irid=> p_id);
  end if;
  return r_gnm.type_label;
end; -- get_global_name

function get_element_type_id
( p_id in number
) return varchar2
is
begin
  get_gnm( p_ivid=> p_id);
  if r_gnm.global_name is null
  then
    get_gnm_for_irid( p_irid=> p_id);
  end if;
  return r_gnm.element_type_id;
end; -- get_element_type_id

function get_irid
( p_ivid in number
) return varchar2
is
begin
  get_gnm( p_ivid=> p_ivid);
  return r_gnm.supplier_irid;
end; -- get_element_type_id

-- returns the irid value of the Dependency Type identified by p_type and p_subtype;
-- returns null if such a dependency type does not exist
function get_dependency_type
( p_type in varchar2
, p_subtype in varchar2
) return number
is
begin
  r_dte.irid := null;
  open c_dte( b_type => p_type, b_subtype=> p_subtype);
  fetch c_dte
  into  r_dte;
  close c_dte;
  return r_dte.irid;
end; -- get_dependency_type  

-- returns the IRID (or ID) of an Element Type in RM_ELEMENT_TYPES
function find_element_type_id
( p_type_label in varchar2
) return number
is
  cursor c_et( b_type_label in varchar2)
  is
  select et.id
  from   rm_element_types et
  where  et.short_name = b_type_label
  UNION
  select et.id
  from   rm_element_types et
  where  et.name = b_type_label
  ;
  r_et c_et%rowtype;
  l_type_label varchar2(500):= upper( replace(p_type_label,' ','_'));
begin
  if l_type_label = 'BR'
  then
    l_type_label:= 'FUN';
  end if;
  if l_type_label = 'BRDD'
  then
    l_type_label:= 'BRDD';
  end if;
  if l_type_label = 'MDE'
  then
    l_type_label:= 'GEM';
  end if;
  open c_et( b_type_label => l_type_label);
  fetch c_et
  into  r_et;
   if c_et%notfound
   then
     r_et.id:= null;
   end if;
   close c_et;
   return r_et.id
   ;
end; -- find_element_type_id

-- returns the IRID (or ID) of an Global_name_domain in SDD_GLOBAL_NAME_DOMAINS
function find_global_name_domain
( p_type_label in varchar2
) return number
is
  cursor c_gnd( b_type_label in varchar2)
  is
  select gnd.irid
  from   sdd_global_name_domains gnd
  where  gnd.name = b_type_label
  ;
  r_gnd c_gnd%rowtype;
  l_type_label varchar2(500):= upper( replace(p_type_label,' ','_'));
begin
  open c_gnd( b_type_label => l_type_label);
  fetch c_gnd
  into  r_gnd;
  close c_gnd;
  dbms_output.put_line( 'Find global name domain '||p_type_label||' result '||to_char(r_gnd.irid));
  return r_gnd.irid
  ;
end; -- find_global_name_domain


-- this function will first check whether there is a Global Name for the p_ivid
-- if there is, it will be returned
-- if not, it will check i$sdd_object_versions and return the Name when found
-- else return the result of a call to jr_name.get_descriptor
-- For specific element types (such as RELENDs) this function may create its own descriptor
function get_descriptor
( p_ivid    in number
, p_irid    in number default null
, p_type_id in number default null -- mandataroy for SACs if they do not have a Global Name
) return varchar2
is
  l_descriptor varchar2(2000);
begin
  l_descriptor:= get_global_name(p_id => nvl(p_ivid,p_irid));
  if l_descriptor is null
  then
    get_ov( p_ivid=> p_ivid);
    l_descriptor:= r_ov.name;
    if l_descriptor is null
    then
      l_descriptor:= jr_name.get_descriptor
                     ( el_id   => nvl(p_ivid,p_irid)
                     , el_type => p_type_id
                     , format  => 'NAME'
                     );
    end if;
  end if;
      l_descriptor:= jr_name.get_descriptor
                     ( el_id   => nvl(p_ivid,p_irid)
                     , el_type => p_type_id
                     , format  => 'NAME'
                     );
  return l_descriptor;
end; -- get_descriptor

-- this procedure will create entries (if those do not already exist) in
-- SDD_GLOBAL_NAMES and SDD_DEPENDENCIES, indicating that Parent Supplier
-- p_pac_ivid supplies through p_detail_ivid a global name p_name
procedure add_detail
( p_pac_ivid       in number
, p_detail_irid    in number
, p_detail_ivid    in number
, p_detail_type_id in number  -- element type of detail provided (refers to RM_ELEMENT_TYPES.ID
, p_global_name    in varchar2 default null -- e.g. HSD_EMPLOYEES.ADDRESS
) is
  l_dpy_irid number(38);
begin
  parse4deps.create_dependency
  ( p_pac_ivid    => p_pac_ivid
  , p_detail_ivid => p_detail_ivid
  , p_detail_irid => p_detail_irid
  , p_detail_type_id => p_detail_type_id
  , p_dependency_type => 'Provides'
  , p_dependency_subtype => ''
  , p_target_name => nvl(p_global_name, get_global_name(p_id => p_detail_ivid))
  , p_target_type => ''
  , p_remark => ''
  , p_irid => l_dpy_irid
  , p_dependency_type_irid => g_deptype_provides
  , p_target_type_id => p_detail_type_id
  , p_target_domain_irid => null
  );
  /*
 jr_dependency_imp.imp
 ( isKnownSupplier    => 1
 , dependencyTypeIrid => g_deptype_provides
 , srcIrid            => p_detail_irid
 , srcIvid            => p_detail_ivid
 , srcParentIvid      => p_pac_ivid
 , srcTypeId          => p_detail_type_id
 , globalName_name    => p_global_name
 , globalName_domain  => null
 , globalName_typeId  => p_detail_type_id
 , stat               => 'C'  -- C(ertain) T(entative), E(xcluded), P(ermanent) see table sdd_depend_statuses
 );
 */
end; -- add_detail

-- this procedure will create entries (if those do not already exist) in
-- SDD_GLOBAL_NAMES and SDD_DEPENDENCIES, indicating that 
-- p_pac_ivid supplies a global name p_name
procedure add_pac
( p_pac_ivid       in number
, p_pac_irid       in number
, p_type_id        in number  -- element type of detail provided (refers to RM_ELEMENT_TYPES.ID
, p_global_name    in varchar2 default null -- e.g. HSD_EMPLOYEES.ADDRESS
) is
  l_dpy_irid number(38);
begin
  parse4deps.create_dependency
  ( p_pac_ivid    => null
  , p_detail_ivid => p_pac_ivid
  , p_detail_irid => p_pac_irid
  , p_detail_type_id => p_type_id
  , p_dependency_type => 'Provides'
  , p_dependency_subtype => 'Principal'
  , p_target_name => nvl( p_global_name, parse4deps.get_global_name(p_id => p_pac_ivid))
  , p_target_type => ''
  , p_remark => ''
  , p_irid => l_dpy_irid
  , p_dependency_type_irid => null -- g_deptype_provides
  , p_target_type_id => p_type_id
  , p_target_domain_irid => null
  );
  /*
 jr_dependency_imp.imp
 ( isKnownSupplier    => 1
 , dependencyTypeIrid => g_deptype_provides
 , srcIrid            => p_pac_irid
 , srcIvid            => p_pac_ivid
 , srcParentIvid      => null
 , srcTypeId          => p_type_id
 , globalName_name    => nvl( p_global_name, parse4deps.get_global_name(p_id => p_pac_ivid))
 , globalName_domain  => null
 , globalName_typeId  => p_type_id
 , stat               => 'C'  -- C(ertain) T(entative), E(xcluded), P(ermanent) see table sdd_depend_statuses
 );
 */
end; -- add_pac


-- this procedure will create entries (if those do not already exist) in
-- SDD_GLOBAL_NAMES and SDD_DEPENDENCIES, indicating that detail p_detail_ivid
-- has a usage of (a dependency on) an element called p_global_name
-- of type p_type_id or of global_name_domain p_domain_irid
-- p_detail_ivid must have been created previously by a call to add_detail
procedure add_usage
( p_pac_ivid          in number
, p_detail_irid       in number  default null
, p_detail_ivid       in number
, p_detail_type_id    in number  default null
, p_global_name_used  in varchar2 
, p_type_id_used      in number   default null
, p_domain_irid_used  in number   default null  -- refers to a row in SDD_GLOBAL_NAME_DOMAINS
, p_dependency_type   in number   default null
, p_dependency_subtype in varchar2   default null
) is
  l_dpy_irid number(38);
begin
  parse4deps.create_dependency
  ( p_pac_ivid => p_pac_ivid
  , p_detail_ivid => p_detail_ivid
  , p_detail_irid => p_detail_irid
  , p_detail_type_id => p_detail_type_id
  , p_dependency_type => 'Uses'
  , p_dependency_subtype => p_dependency_subtype
  , p_target_name => p_global_name_used
  , p_target_type => ''
  , p_remark => ''
  , p_irid => l_dpy_irid
  , p_dependency_type_irid => ifThenElse
                              ( p_dependency_type is not null 
                              , p_dependency_type
                              , ifThenElse
                                ( p_dependency_subtype is not null
                                , null
                                , g_deptype_uses
                                )
                              )
  , p_target_type_id => p_type_id_used
  , p_target_domain_irid => p_domain_irid_used
  );
/*
 jr_dependency_imp.imp
 ( isKnownSupplier    => 0
 , dependencyTypeIrid => nvl( p_dependency_type, g_deptype_uses)
 , srcIrid            => nvl( p_detail_irid, get_irid(p_ivid => p_detail_ivid))
 , srcIvid            => p_detail_ivid
 , srcParentIvid      => p_pac_ivid
 , srcTypeId          => nvl( p_detail_type_id, get_element_type_id(p_id => p_detail_ivid))
 , globalName_name    => p_global_name_used
 , globalName_domain  => p_domain_irid_used
 , globalName_typeId  => p_type_id_used
 , stat               => 'C'  -- C(ertain) T(entative), E(xcluded), P(ermanent) see table sdd_depend_statuses
 );
 */
end; -- add_usage

procedure create_dependency_type
( p_type    in varchar2
, p_subtype in varchar2
, p_irid    out number
)
is
  l_dte_data SDDoDEPENDENCY_TYPE.data;
begin
  -- dbms_output.put_line('Create dependency type '||p_type||' subtype '||p_subtype);
  l_dte_data.v.type:= p_type;
  l_dte_data.v.subtype:= p_subtype;
  l_dte_data.i.type:= true;
  l_dte_data.i.subtype:= true;
  l_dte_data.v.system_flag:='N';
  l_dte_data.i.system_flag:= true;
  SDDoDEPENDENCY_TYPE.Ins(Irid => null,Pl => l_dte_data);
  p_irid:= l_dte_data.v.irid;
end; -- create_dependency_type

procedure create_global_name_domain
( p_name    in varchar2
, p_irid    out number
)
is
  l_gnd_data SDDoGLOBAL_NAME_DOMAIN.data;
  l_ngnd_data SDDoNLS_GLOBAL_NAME_DOMAIN.data;
begin
  --  dbms_output.put_line('Create global name domain '||p_name);
  l_gnd_data.v.name:= upper(replace(p_name,' ','_'));
  l_gnd_data.i.name:= true;
  l_gnd_data.v.system_flag:= 'N';
  l_gnd_data.i.system_flag:= true;
  SDDoGLOBAL_NAME_DOMAIN.Ins(Irid => null,Pl => l_gnd_data);
  l_ngnd_data.v.gnd_irid := l_gnd_data.v.irid;
  l_ngnd_data.v.NLS_LANG := 'US';
  l_ngnd_data.v.NLS_NAME := initcap(p_name);
  l_ngnd_data.v.NLS_PLURAL_NAME:=  initcap(p_name)||'s';
  SDDoNLS_GLOBAL_NAME_DOMAIN.ins(irid => null, pl=> l_ngnd_data);
  p_irid:= l_gnd_data.v.irid;
end; -- create_global_name_domain

-- returns the irid of the Global Name found
function find_global_name
( p_name        in varchar2
, p_type_id     in number
, p_domain_irid in number
) return number
is
  cursor c_gne
         ( b_name        in varchar2
         , b_type_id     in number
         , b_domain_irid in number
         ) is
    select gne.irid
    from   sdd_global_names gne
    where  gne.name    = b_name
    and    gne.type_id = b_type_id
    UNION
    select gne.irid
    from   sdd_global_names gne
    where  gne.name        = b_name
    and    gne.domain_irid = b_domain_irid
    ;
  r_gne c_gne%rowtype;
begin
  open c_gne( b_name => p_name, b_type_id => p_type_id, b_domain_irid => p_domain_irid);
  fetch c_gne
  into  r_gne;
  close c_gne;
  return r_gne.irid;
end; -- find_global_name

function find_or_create_global_name
( p_name in varchar2
, p_domain_irid in number
, p_type_id in number  
) return number
is
  l_irid number(38);
  l_gne_data SDDoGLOBAL_NAME.data;
begin
  l_irid := find_global_name( p_name, p_type_id, p_domain_irid);
  if l_irid is null
  then
    -- dbms_output.put_line('Create global name '||p_name);
    l_gne_data.v.name := p_name;
    l_gne_data.v.domain_irid := p_domain_irid;
    l_gne_data.v.type_id:= p_type_id;
    l_gne_data.i.name := true;
    l_gne_data.i.domain_irid := true;
    l_gne_data.i.type_id:= true;
    SDDoGLOBAL_NAME.ins(irid => null, pl => l_gne_data);
    l_irid:= l_gne_data.v.irid;
  end if; -- l_irid is null
  return l_irid;
end; -- find_or_create_global_name

function find_dependency
( p_dependencyTypeIrid in number
, p_supplierIrid       in number
, p_supplierIvid       in number
, p_supplierTypeId     in number
, p_clientIrid         in number
, p_clientIvid         in number
, p_clientTypeId       in number
) return number
is
  cursor c_dpy
         ( b_dependencyTypeIrid in number
         , b_supplierIrid       in number
         , b_supplierIvid       in number
         , b_supplierTypeId     in number
         , b_clientIrid         in number
         , b_clientIvid         in number
         , b_clientTypeId       in number
         )
  is
    select dpy.irid
    from   sdd_dependencies dpy
    where  dpy.dependency_type_irid= b_dependencyTypeIrid
    and    dpy.supplier_irid= b_supplierIrid
    and    dpy.supplier_ivid= b_supplierIvid
    and    dpy.supplier_type_id= b_supplierTypeId
    and    dpy.client_irid= b_clientIrid
    and    dpy.client_ivid= b_clientIvid
    and    dpy.client_type_id= b_clientTypeId
    ;
  r_dpy c_dpy%rowtype;
begin
  open c_dpy
       (   b_dependencyTypeIrid => p_dependencyTypeIrid
         , b_supplierIrid       => p_supplierIrid
         , b_supplierIvid       => p_supplierIvid
         , b_supplierTypeId     => p_supplierTypeId
         , b_clientIrid         => p_clientIrid
         , b_clientIvid         => p_clientIvid
         , b_clientTypeId       => p_clientTypeId
       );
  fetch c_dpy
  into  r_dpy;
  close c_dpy;
  return r_dpy.irid;
end; -- find_dependency

-- if p_pac_ivid is null, it is assumed that p_detail_... refer to a PAC itself
procedure create_dependency
( p_pac_ivid           in number       -- from which PAC
, p_detail_ivid        in number       -- from which detail (optional)
, p_detail_irid        in number  default null
, p_detail_type_id     in number       -- type of detail
, p_dependency_type    in varchar2     -- Uses or Provides
, p_dependency_subtype in varchar2
, p_target_name        in varchar2
, p_target_type        in varchar2
, p_remark             in varchar2 default null
, p_irid               out number
, p_dependency_type_irid in number default null
, p_target_type_id       in number default null
, p_target_domain_irid   in number default null
, p_target_name_irid     in number default null
) is
  l_dte_irid           number(38):= p_dependency_type_irid;
  l_et_irid            number(38):= p_target_type_id;
  l_gnd_irid           number(38):= p_target_domain_irid;
  l_gne_irid           number(38):= p_target_name_irid;
  l_gne_ivid           number(38):= get_global_name_ivid( p_gne_irid => l_gne_irid);
  l_dpy_data           SDDoDEPENDENCY.data;
begin
  dbms_output.put_line('create dependency on '||p_target_type||' - '||p_target_name
  );
  -- find dependency type (irid) from sdd_dependency_types; when not found, create one
  l_dte_irid := nvl( l_dte_irid, get_dependency_type( p_type => p_dependency_type, p_subtype => p_dependency_subtype));
  if l_dte_irid is null
  then    
    create_dependency_type( p_type => p_dependency_type, p_subtype => initcap(p_dependency_subtype), p_irid => l_dte_irid);
  end if; -- l_dte_irid is null
  
  if l_gne_irid is null -- if Target Global Name already known, no need to continue with type/global name domain determination
  then
    -- find element type; if it does not exist, find global_name_domain; if that does not
    -- exist, then create a new global_name_domain
    l_et_irid:= nvl( l_et_irid, find_element_type_id( p_type_label => p_target_type));
    if l_et_irid is null -- not found in RM_ELEMENT_TYPES
    then
      l_gnd_irid:= nvl( l_gnd_irid, find_global_name_domain(p_type_label => p_target_type));
      if l_gnd_irid is null -- not found in SDD_GLOBAL_NAME_DOMAINS
      then
        create_global_name_domain( p_name => p_target_type, p_irid => l_gnd_irid);
      end if; -- l_gnd_irid is null -- not found in SDD_GLOBAL_NAME_DOMAINS
    end if; -- l_et_irid is null
    l_gne_irid:= nvl( l_gne_irid, find_or_create_global_name( p_target_name, l_gnd_irid, l_et_irid));
    l_gne_ivid:= get_global_name_ivid( p_gne_irid => l_gne_irid);
  end if; -- l_gne_irid is null
  -- now insert the dependency itself
  if p_dependency_type = 'Uses'
  then
    l_dpy_data.v.CLIENT_IRID          := nvl( p_detail_irid, get_irid(p_ivid => p_detail_ivid));
    l_dpy_data.v.CLIENT_IVID          := p_detail_ivid;
    l_dpy_data.v.CLIENT_PARENT_IVID   := p_pac_ivid;
    l_dpy_data.v.CLIENT_TYPE_ID       := nvl( p_detail_type_id, get_element_type_id(p_id => p_detail_ivid));
    l_dpy_data.v.SUPPLIER_IRID        := l_gne_irid; -- the newly created global name
    l_dpy_data.v.SUPPLIER_IVID        := l_gne_ivid;
    l_dpy_data.v.SUPPLIER_PARENT_IVID := null;
    l_dpy_data.v.SUPPLIER_TYPE_ID     := g_typeid_sdd_global_names;
  else -- Provides
    l_dpy_data.v.CLIENT_IRID          := l_gne_irid; -- the newly created global name
    l_dpy_data.v.CLIENT_IVID          := l_gne_ivid;
    l_dpy_data.v.CLIENT_PARENT_IVID   := null;
    l_dpy_data.v.CLIENT_TYPE_ID       := g_typeid_sdd_global_names;
    l_dpy_data.v.SUPPLIER_IRID        := nvl( p_detail_irid, get_irid(p_ivid => p_detail_ivid));
    l_dpy_data.v.SUPPLIER_IVID        := p_detail_ivid;
    l_dpy_data.v.SUPPLIER_PARENT_IVID := p_pac_ivid;
    l_dpy_data.v.SUPPLIER_TYPE_ID     := nvl( p_detail_type_id, get_element_type_id(p_id => p_detail_ivid));
  end if; -- p_dependency_type = 'Uses'
  l_dpy_data.v.DEPENDENCY_TYPE_IRID := l_dte_irid;
  l_dpy_data.v.REMARK               := substr(p_remark,1,70);
  l_dpy_data.v.STATUS               := 'C';
  l_dpy_data.v.irid:= find_dependency
                       (   p_dependencyTypeIrid => l_dpy_data.v.dependency_Type_Irid
                         , p_supplierIrid       => l_dpy_data.v.supplier_Irid
                         , p_supplierIvid       => l_dpy_data.v.supplier_Ivid
                         , p_supplierTypeId     => l_dpy_data.v.supplier_Type_Id
                         , p_clientIrid         => l_dpy_data.v.client_Irid
                         , p_clientIvid         => l_dpy_data.v.client_Ivid
                         , p_clientTypeId       => l_dpy_data.v.client_Type_Id
                       );
  if l_dpy_data.v.irid is not null
  then -- if the dependency already exists, all we do is update the Remark
    l_dpy_data.i.REMARK               := true;
    SDDoDEPENDENCY.upd(irid => l_dpy_data.v.irid, pl => l_dpy_data);
  else  

    l_dpy_data.i.CLIENT_IRID          := true;
    l_dpy_data.i.CLIENT_IVID          := true;
    l_dpy_data.i.CLIENT_PARENT_IVID   := true;
    l_dpy_data.i.CLIENT_TYPE_ID       := true;
    l_dpy_data.i.SUPPLIER_IRID        := true;
    l_dpy_data.i.SUPPLIER_IVID        := true;
    l_dpy_data.i.SUPPLIER_PARENT_IVID := true;
    l_dpy_data.i.SUPPLIER_TYPE_ID     := true;
    l_dpy_data.i.DEPENDENCY_TYPE_IRID := true;
    l_dpy_data.i.REMARK               := true;
    l_dpy_data.i.STATUS               := true;
    SDDoDEPENDENCY.ins(irid => null, pl => l_dpy_data);
  end if;
  p_irid := l_dpy_data.v.irid;
end; -- create_dependency



  
-- function return the value assigned to a certain <TAG attribute
-- written in the following format: <TAG attribute='value' attribute2='value' >
-- or more generic:
-- <open separator> tag = value <close separator>
function get_tag_value
( p_string          in varchar2
, p_tag             in varchar2
, p_attribute       in varchar2
, p_separator_open  in varchar2 default '<'
, p_separator_close in varchar2 default '>'
) return varchar2
is
 l_open_pos    number(10):= instr
                            ( upper(p_string)
                            , p_separator_open||upper(p_tag)
                            );
 l_close_pos   number(10):= nvl
                            ( instr
                              ( p_string
                              , p_separator_close
                              , l_open_pos + 1
                              )
                            ,0);
 l_value_start number(10);
 l_end_pos     number(10);
begin
 if l_open_pos = 0
 then
   return '';
 end if;
 if l_end_pos = 0 -- no end flag
 then
   l_end_pos := length(p_string);
 end if;
 if nvl(l_open_pos,0) > 0
 then
   l_value_start:= instr( p_string , p_attribute||'=''', l_open_pos)
                 + length(p_attribute||'=''')
                 ; -- from the position of the TAG (l_open_pos) look for the first occurrence of p_attribute='
   if nvl(l_value_start,0)  > length(p_attribute||'=''')
      and
      nvl(l_value_start,0)  < l_close_pos
   then
     --dbms_output.put_line( 'l_value_start'||to_char(l_value_start));
     l_end_pos    := instr( p_string, '''', l_value_start+1); -- from the start of attribute=' look for the closing '
     if nvl(l_end_pos, l_close_pos)  < l_close_pos
     then
       return substr( p_string, l_value_start, l_end_pos - l_value_start);
     else
       return '';
     end if;
   else
     return '';
   end if;
 else
   return '';
 end if;
end;  -- get_tag_value




-- this procedure parses the string p_string for any dependencies recorded using the notation
/*  according to enhancement request 1696097
   
   <dependency type='Uses' subtype='Call' remark='Only on Monday'>
   <target name='JR_DEPENDENCY_IMP.IMP' type='PLSQL_MODULE_SUBROUTINE'/>
   </dependency>
   <dependency type='Provides' subtype='Principal'>
   <target name='PARSE4DEPS.PARSE_STRING' type='PLSQL_MODULE_SUBROUTINE'/>
   </dependency>

  if this dependency is from a PAC instead of a detail within a PAC, then p_detail_ivid
  refers to the PAC and p_pac_ivid is ignored
*/
procedure parse_string
( p_string         in varchar2
, p_pac_ivid       in number
, p_detail_ivid    in number
, p_detail_irid    in number  default null
, p_detail_type_id in number  default null
) is
  l_dependency_type    varchar2(40);  -- Uses or Provides
  l_dependency_subtype varchar2(100); -- 
  l_target_name        varchar2(500); -- name of the Detail Provided or Uses; corresponds with SDD_GLOBAL_NAMES.name
  l_target_type        varchar2(500); -- element type of target; corresponds with SDD_GLOBAL_NAME_DOMAINS or RM_ELEMENT_TYPES
  l_remark             varchar2(2000); -- can only be 70 long!
  l_next_tag_pos       number(10);
  l_dpy_irid           number(38);
begin
  l_dependency_type    := get_tag_value( p_string, 'dependency', 'type');
  l_dependency_subtype := get_tag_value( p_string, 'dependency', 'subtype');
  l_remark             := get_tag_value( p_string, 'dependency', 'remark');
  l_target_name        := get_tag_value( p_string, 'target'    , 'name');
  l_target_type        := get_tag_value( p_string, 'target'    , 'type');
  if lower(l_dependency_type) like 'u%'
  then
    l_dependency_type:= 'Uses';
  else
    l_dependency_type:= 'Provides';
  end if; -- lower(l_dependency_type) like 'u%'

  create_dependency
  ( p_pac_ivid => p_pac_ivid
  , p_detail_ivid => p_detail_ivid
  , p_detail_irid => p_detail_irid
  , p_detail_type_id => p_detail_type_id
  , p_dependency_type => l_dependency_type
  , p_dependency_subtype => l_dependency_subtype
  , p_target_name => l_target_name
  , p_target_type => l_target_type
  , p_remark => l_remark
  , p_irid => l_dpy_irid
  );
  
  -- if l_dependency_subtype does not exist in 
  -- if the string contains another <dependency tag, then continue the search with a 
  -- recursive call to parse_string
  l_next_tag_pos:= instr( lower(p_string), '<dependency', 1, 2); -- starting at position 1 look for the second occurrence
  if l_next_tag_pos > 0
  then
    parse_string( substr( p_string, l_next_tag_pos), p_pac_ivid, p_detail_ivid);
  end if;
end; -- parse_string

-- delete all dependency data (PAC+details + all usages) for PAC p_pac_ivid
procedure delete_deps_data
( p_pac_ivid in number
) is
begin
  delete sdd_dependencies
  where  supplier_ivid = p_pac_ivid
  ;
  delete sdd_dependencies
  where  supplier_parent_ivid = p_pac_ivid
  ;
  delete sdd_dependencies
  where  client_ivid = p_pac_ivid
  ;
  delete sdd_dependencies
  where  client_parent_ivid = p_pac_ivid
  ;
  -- now indicate that there is no dependency information (that is not completely reliable)
  update i$sdd_object_versions ov
  set    ov.obj_notm_when_analyzed = null
  where  ov.ivid = p_pac_ivid
  ;
end; -- delete_deps_data

  -- to except certain types of details from being retrieved
  -- such as FUNATT and Diagram Segments
  function skip
  ( p_type_id in number
  , p_tbl_name in varchar2
  ) return boolean
  is
  begin
    if p_tbl_name = 'I$SDD_FUNATT'
       and
       p_type_id <> 4988 -- FUNENT
       and 
       false
    then
      return true;
    end if;
    if p_type_id = 4941  -- DIAGRAM
       and
       p_tbl_name in ('I$SDD_DIAGRAM_SEGMENTS','I$SDD_FILPAC')
    then
      return true;
    end if;
    if p_type_id in (4844,5009)  -- Folder or Application System
       and
       p_tbl_name = 'I$SDD_FOLDER_MEMBERS'
    then
      return true;
    end if;
    if p_type_id = 5039 -- Folder members
    then
      return true;
    end if;
    return false;
  end; -- skip

-- returns the name of the table in which element type p_type_id is stored
function get_table
( p_type_id in number
) return varchar2
is
  cursor c_tbl( b_ete_id in number)
  is
    select 'I$'||tbl.table_name tbl_name
    ,      tbl.table_name       view_name
    ,       tbl.irid            tbl_irid
    from   i$rm_sql_tables    tbl
    ,      i$rm_sql_row_types rt
    ,      i$rm_element_types ete
    where  tbl.irid             = rt.table_mapped
    and    ete.primary_row_type = rt.id
    and    ete.id = b_ete_id
    ;

  r_tbl c_tbl%rowtype;
begin
  open c_tbl( b_ete_id => p_type_id);  
  fetch c_tbl
  into  r_tbl;
  close c_tbl;
  return r_tbl.tbl_name;
end; -- get_table  

-- returns the name of the table in which element type p_type_id is stored
function get_table_irid
( p_type_id in number
) return varchar2
is
  cursor c_tbl( b_ete_id in number)
  is
    select 'I$'||tbl.table_name tbl_name
    ,      tbl.table_name       view_name
    ,       tbl.irid            tbl_irid
    from   i$rm_sql_tables    tbl
    ,      i$rm_sql_row_types rt
    ,      i$rm_element_types ete
    where  tbl.irid             = rt.table_mapped
    and    ete.primary_row_type = rt.id
    and    ete.id = b_ete_id
    ;

  r_tbl c_tbl%rowtype;
begin
  open c_tbl( b_ete_id => p_type_id);  
  fetch c_tbl
  into  r_tbl;
  close c_tbl;
  return r_tbl.tbl_irid;
end; -- get_table_irid

  
function contains_parent_ivid
( p_tbl_irid in number
) return boolean
is
  cursor c_pid
         ( b_tbl_irid in number
         ) is
    select 'x'
    from   dual
    where  exists 
           ( select 'x'
             from   rm_element_types et
              ,      i$rm_sql_row_types srt
              ,      i$RM_SQL_COLUMNS col
              where  col.table_irid= b_tbl_irid
              and    col.column_name = 'PARENT_IVID'
          )
    ;
  l_test varchar2(1);
begin
  open c_pid( b_tbl_irid => p_tbl_irid);
  fetch c_pid
  into  l_test;
  close c_pid;
  return nvl(l_test,'y') = 'x';  
end; -- contains_parent_ivid

  
-- this procedure will create dependencies for all the references of the element
-- identified by p_ivid of type p_type_id  
procedure add_usages_for_element
( p_ivid     in number
, p_irid     in number
, p_pac_ivid in number  
, p_type_id  in number
) is

 l_command varchar2(10000);
 l_ctr     number(3):=0;
 l_first   boolean:= true;
 cursor c_ref
        ( b_type_id in number
        ) is
  select distinct
         tab2.table_name tab_name
  ,      tab2.irid tab_irid
  ,      con2.is_owning_fk
  ,      col.column_name
  from   i$RM_SQL_TABLES tab1
  ,      i$RM_SQL_TABLES tab2
  ,      i$RM_SQL_CONSTRAINTS con1
  ,      i$RM_SQL_CONSTRAINTS con2
  ,      rm_element_types et
  ,      i$rm_sql_row_types srt
  ,      i$RM_SQL_CONS_COLUMNS col
  where  et.id = b_type_id
  and    srt.id  = et.primary_row_type
  and    con1.table_irid  = srt.table_mapped
  and    tab2.irid=con2.table_irid
  and    con1.r_constraint_name=con2.constraint_name
  and    con1.is_owning_fk='N' -- not the 'owning' link
  and    col.constraint_irid=con1.irid
  order
  by     column_name
  ;
begin
 -- select current record using p_ivid and get_table( p_type_id)
 -- loop over all referencing columns in current record
 -- for each referencing column, if value is not null
 -- select types, parent_ivid from reffed_table
 -- create dependency
 l_command:=
 ' declare
     cursor c_detl is
       select det.ivid 
 ';
 for r_ref in c_ref(b_type_id => p_type_id) loop
   l_first:= false;
   l_command:= l_command
   ||' ,     '||r_ref.column_name;
 end loop; -- r_ref
 -- 5018 FUNATT => nullify_flag, retrieve_flag, insert_flag, archive_flag, update_flag
 -- 4988 FUNENT => delete_flag, retrieve_flag, insert_flag,  update_flag
 -- 4920 MCO   =>  delete_flag,   insert_flag,               select_flag, update_flag
 -- 4917 DBI    => display_flag, insert_flag, nullify_flag, select_flag, update_flag
 if p_type_id in (5018,4917) -- FUNATT, DBI
 then
   l_command:= l_command
   ||' ,     nullify_flag';
 end if; 
 if p_type_id = 5018 -- FUNATT
 then
   l_command:= l_command
   ||',     insert_flag ';
 end if; 
 if p_type_id = 4988 -- FUNENT
 then
   l_command:= l_command
   ||'    ,     create_flag ';
 end if; 
 if p_type_id in  (4988, 4920) -- FUNENT, MCO
 then
   l_command:= l_command
   ||' ,     delete_flag';
 end if; 
 if p_type_id = 4917  -- DBI
 then
   l_command:= l_command
   ||' ,     display_flag';
 end if; 
 if p_type_id in  (5018, 4988) -- FUNATT, FUNENT
 then
   l_command:= l_command
   ||' ,     retrieve_flag
       ,     update_flag
       ';
 end if; 
 if p_type_id in  (4920, 4917) -- MCO, DBI
 then
   l_command:= l_command
   ||' ,     select_flag
       ,     update_flag
       ,     insert_flag'
   ;
 end if; 
 l_command:= l_command
 ||' from '||parse4deps.get_table( p_type_id)||' det '
 ||' where det.ivid = '||p_ivid||'
   ;
   r_det c_detl%rowtype;
   ';
 l_first:= true;
 l_ctr:= 0;
 for r_ref in c_ref(b_type_id => p_type_id) loop
   l_ctr:= l_ctr + 1;
   l_command:= l_command
   ||' cursor c_'||r_ref.tab_name||to_char(l_ctr)||'( b_ref_irid in number) is
         select r.types
         ,      r.irid
         from   i$'||r_ref.tab_name||' r
         where  r.irid = b_ref_irid
         ;
      '
   ;
   if l_first
   then
     l_command:= l_command
     ||' r_ref c_'||r_ref.tab_name||to_char(l_ctr)||'%rowtype;';
     l_first:= false;
   end if;
 end loop; -- r_ref
 l_command:= l_command
 ||'   
 begin
   open c_detl;
   fetch c_detl
   into  r_det;
   close c_detl;   
 ';
 l_ctr:=0;
 for r_ref in c_ref(b_type_id => p_type_id) loop
   l_ctr:= l_ctr + 1;
   l_command:= l_command
   ||' if r_det.'||r_ref.column_name||' is not null
       then 
         open c_'||r_ref.tab_name||to_char(l_ctr)||'( b_ref_irid => r_det.'||r_ref.column_name||');
         fetch c_'||r_ref.tab_name||to_char(l_ctr)||'
         into  r_ref;
         close c_'||r_ref.tab_name||to_char(l_ctr)||';
         parse4deps.add_usage
         ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
         , p_detail_ivid       => '||to_char(p_ivid)||'
         , p_detail_irid       => '||to_char(p_irid)||'
         , p_detail_type_id    => '||to_char(p_type_id)||'
         , p_global_name_used  => parse4deps.get_descriptor
                                  ( p_ivid => null
                                  , p_irid=> r_det.'||r_ref.column_name||'
                                  , p_type_id => r_ref.types
                                  )
         , p_type_id_used      => r_ref.types
         , p_domain_irid_used  => null
         , p_dependency_type   => null
         );    
         ';
     -- FUNATT => nullify_flag, retrieve_flag, insert_flag, archive_flag, update_flag
     -- FUNENT => delete_flag, retrieve_flag, insert_flag,  update_flag
     --  MCO   => delete_flag, insert_flag, select_flag, update_flag
     -- DBI    => display_flag, insert_flag, nullify_flag, select_flag, update_flag

   --NULLIFY
   if p_type_id in (5018,4917) -- FUNATT, DBI
      and
      r_ref.column_name in ('ATTRIBUTE_REF','COLUMN_REF')
   then
     l_command:= l_command
     ||'  if r_det.nullify_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => '||to_char(p_ivid)||'
             , p_detail_irid       => '||to_char(p_irid)||'
             , p_detail_type_id    => '||to_char(p_type_id)||'
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Nullify''
             );    
          
          end if;
     ';
     l_command:= l_command
     ||'  if r_det.insert_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => '||to_char(p_ivid)||'
             , p_detail_irid       => '||to_char(p_irid)||'
             , p_detail_type_id    => '||to_char(p_type_id)||'
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Insert''
             );    
          
          end if;
     ';
   end if;  -- FUNATT, DBI and INSERT
   -- CREATE_FLAG
   if p_type_id = 4988 -- FUNENT
      and
      r_ref.column_name = 'ENTITY_REF'
   then
     l_command:= l_command
     ||'  if r_det.create_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => '||to_char(p_ivid)||'
             , p_detail_irid       => '||to_char(p_irid)||'
             , p_detail_type_id    => '||to_char(p_type_id)||'
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Create''
             );    
          
          end if;
     ';
     l_command:= l_command
     ||'  if r_det.delete_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => '||to_char(p_ivid)||'
             , p_detail_irid       => '||to_char(p_irid)||'
             , p_detail_type_id    => '||to_char(p_type_id)||'
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Delete''
             );    
          
          end if;
     ';
   end if; -- FUNENT and ENTITY_REF
   if p_type_id in ( 4988, 5018, 4917) -- FUNENT, FUNATT, DBI
      and
      r_ref.column_name in ( 'ENTITY_REF','ATTRIBUTE_REF','COLUMN_REF')
   then
     l_command:= l_command
     ||'  if r_det.update_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => '||to_char(p_ivid)||'
             , p_detail_irid       => '||to_char(p_irid)||'
             , p_detail_type_id    => '||to_char(p_type_id)||'
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Update''
             );    
          
          end if;
     ';
   end if; -- FUNENT,FUNATT,DBI and ENTITY_REF, ATTRIBUTE_REF,COLUMN_REF
   -- display
   if p_type_id = 4917 -- DBI
      and
      r_ref.column_name = 'COLUMN_REF'
   then
     l_command:= l_command
     ||'  if r_det.display_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => '||to_char(p_ivid)||'
             , p_detail_irid       => '||to_char(p_irid)||'
             , p_detail_type_id    => '||to_char(p_type_id)||'
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Display''
             );    
          
          end if;
     ';
     l_command:= l_command
     ||'  if r_det.select_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => '||to_char(p_ivid)||'
             , p_detail_irid       => '||to_char(p_irid)||'
             , p_detail_type_id    => '||to_char(p_type_id)||'
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Select''
             );    
          
          end if;
     ';
   end if; -- DBI and COLUMN_REF
   -- RETRIEVE
   if p_type_id in ( 4988, 5018) -- FUNENT, FUNATT
      and
      r_ref.column_name in ( 'ENTITY_REF','ATTRIBUTE_REF')
   then
     l_command:= l_command
     ||'  if r_det.retrieve_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => '||to_char(p_ivid)||'
             , p_detail_irid       => '||to_char(p_irid)||'
             , p_detail_type_id    => '||to_char(p_type_id)||'
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Retrieve''
             );    
          
          end if;
     ';
   end if; -- FUNENT,FUNATT and ENTITY_REF, ATTRIBUTE_REF
   l_command:= l_command
   ||' end if;  
     ';
 end loop; -- r_ref
 l_command:= l_command
 ||'  end;'
 ;
 /*
 for i in 1..250 loop
   dbms_output.put_line( substr(l_command,1+(i-1)* 250,250));
   if i*250 > length(l_command)
   then
    exit;
    end if;
 end loop;
 */
 execute immediate l_command;
end; -- add_usages_for_element


-- this procedure will create dependencies for all the references of the element
-- identified by p_ivid of type p_type_id  
procedure add_usages_for_type
( p_pac_ivid in number  
, p_tab_irid in number
, p_tbl_name in varchar2
) is

 l_command varchar2(30000);
 l_ctr     number(3):=0;
 l_first   boolean:= true;
 cursor c_ref
        ( b_tab_irid in number
        ) is
  select distinct
         tab2.table_name tab_name
  ,      tab2.irid tab_irid
  ,      con2.is_owning_fk
  ,      col.column_name
  from   i$RM_SQL_TABLES tab2
  ,      i$RM_SQL_CONSTRAINTS con1
  ,      i$RM_SQL_CONSTRAINTS con2
  ,      i$RM_SQL_CONS_COLUMNS col
  where  con1.table_irid  = b_tab_irid
  and    con1.r_constraint_name=con2.constraint_name
  and    con1.is_owning_fk='N' -- not the 'owning' link
  and    tab2.irid=con2.table_irid
  and    col.constraint_irid=con1.irid
  order
  by     column_name
  ;
begin
 -- select current record using p_tab_irid and p_pac_ivid
 -- loop over all referencing columns in current record
 -- for each referencing column, if value is not null
 -- select types, parent_ivid from reffed_table
 -- create dependency
 dbms_output.put_line('Dependencies from '||p_tbl_name||' ('||to_char(p_tab_irid)||')');
 l_command:=
 ' declare
     cursor c_detl is
       select det.ivid 
       ,      det.irid 
       ,      det.types
 ';
 for r_ref in c_ref(b_tab_irid => p_tab_irid) loop
   l_first:= false;
   l_command:= l_command
   ||' ,     '||r_ref.column_name;
 end loop; -- r_ref
 -- 5018 FUNATT => nullify_flag, retrieve_flag, insert_flag, archive_flag, update_flag
 -- 4988 FUNENT => delete_flag, retrieve_flag, insert_flag,  update_flag
 -- 4920 MCO   =>  delete_flag,   insert_flag,               select_flag, update_flag
 -- 4917 DBI    => display_flag, insert_flag, nullify_flag, select_flag, update_flag
 if p_tbl_name in ('I$SDD_FUNATT','I$SDD_ITE') -- FUNATT, DBI
 then
   l_command:= l_command
   ||' ,     nullify_flag';
 end if; 
 if p_tbl_name ='I$SDD_FUNATT'
 then
   l_command:= l_command
   ||',     insert_flag ';
 end if; 
 if p_tbl_name = 'I$SDD_FUNENT' -- FUNENT
 then
   l_command:= l_command
   ||'    ,     create_flag ';
 end if; 
 if p_tbl_name = 'I$SDD_FUNENT'
 then
   l_command:= l_command
   ||' ,     delete_flag';
 end if; 
 if p_tbl_name ='I$SDD_ITE'
 then
   l_command:= l_command
   ||' ,     display_flag';
 end if; 
 if p_tbl_name in ('I$SDD_FUNATT','I$SDD_FUNENT') -- FUNATT, FUNENT
 then
   l_command:= l_command
   ||' ,     retrieve_flag
       ,     update_flag
       ';
 end if; 
 if p_tbl_name ='I$SDD_ITE'
 then
   l_command:= l_command
   ||' ,     select_flag
       ,     update_flag
       ,     insert_flag'
   ;
 end if; 
 l_command:= l_command
 ||' from '||p_tbl_name||' det '
 ||ifThenElse 
   ( contains_parent_ivid( p_tbl_irid => p_tab_irid)
   , ' where det.parent_ivid = '||to_char(p_pac_ivid)
   , ' where det.ivid = '||to_char(p_pac_ivid)
   )
 ||'
   ;
   r_det c_detl%rowtype;
   ';
 l_first:= true;
 l_ctr:= 0;
 for r_ref in c_ref(b_tab_irid => p_tab_irid) loop
   l_ctr:= l_ctr + 1;
   l_command:= l_command
   ||' cursor c_'||r_ref.tab_name||to_char(l_ctr)||'( b_ref_irid in number) is
         select r.types
         ,      r.irid
         from   i$'||r_ref.tab_name||' r
         where  r.irid = b_ref_irid
         ;
      '
   ;
   if l_first
   then
     l_command:= l_command
     ||' r_ref c_'||r_ref.tab_name||to_char(l_ctr)||'%rowtype;';
     l_first:= false;
   end if;
 end loop; -- r_ref
 l_command:= l_command
 ||'   
 begin
   for r_det in c_detl loop
 ';
 l_ctr:=0;
 for r_ref in c_ref(b_tab_irid => p_tab_irid) loop
   l_ctr:= l_ctr + 1;
   l_command:= l_command
   ||' if r_det.'||r_ref.column_name||' is not null
       then 
         open c_'||r_ref.tab_name||to_char(l_ctr)||'( b_ref_irid => r_det.'||r_ref.column_name||');
         fetch c_'||r_ref.tab_name||to_char(l_ctr)||'
         into  r_ref;
         close c_'||r_ref.tab_name||to_char(l_ctr)||';
         parse4deps.add_usage
         ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
         , p_detail_ivid       => r_det.ivid
         , p_detail_irid       => r_det.irid
         , p_detail_type_id    => r_det.types
         , p_global_name_used  => parse4deps.get_descriptor
                                  ( p_ivid => null
                                  , p_irid=> r_det.'||r_ref.column_name||'
                                  , p_type_id => r_ref.types
                                  )
         , p_type_id_used      => r_ref.types
         , p_domain_irid_used  => null
         , p_dependency_type   => null
         );    
         ';
     -- FUNATT => nullify_flag, retrieve_flag, insert_flag, archive_flag, update_flag
     -- FUNENT => delete_flag, retrieve_flag, insert_flag,  update_flag
     --  MCO   => delete_flag, insert_flag, select_flag, update_flag
     -- DBI    => display_flag, insert_flag, nullify_flag, select_flag, update_flag

   --NULLIFY
   if p_tbl_name in ('I$SDD_FUNATT','I$SDD_ITE')
      and
      r_ref.column_name in ('ATTRIBUTE_REF','COLUMN_REF')
   then
     l_command:= l_command
     ||'  if r_det.nullify_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => r_det.ivid
             , p_detail_irid       => r_det.irid
             , p_detail_type_id    => r_det.types
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Nullify''
             );    
          
          end if;
     ';
     l_command:= l_command
     ||'  if r_det.insert_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => r_det.ivid
             , p_detail_irid       => r_det.irid
             , p_detail_type_id    => r_det.types
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Insert''
             );    
          
          end if;
     ';
   end if;  -- FUNATT, DBI and INSERT
   -- CREATE_FLAG
   if p_tbl_name = 'I$SDD_FUNENT'
      and
      r_ref.column_name = 'ENTITY_REF'
   then
     l_command:= l_command
     ||'  if r_det.create_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => r_det.ivid
             , p_detail_irid       => r_det.irid
             , p_detail_type_id    => r_det.types
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Create''
             );    
          
          end if;
     ';
     l_command:= l_command
     ||'  if r_det.delete_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => r_det.ivid
             , p_detail_irid       => r_det.irid
             , p_detail_type_id    => r_det.types
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Delete''
             );    
          
          end if;
     ';
   end if; -- FUNENT and ENTITY_REF
   if p_tbl_name in ('I$SDD_FUNENT','I$SDD_FUNATT','I$SDD_ITE')
      and
      r_ref.column_name in ( 'ENTITY_REF','ATTRIBUTE_REF','COLUMN_REF')
   then
     l_command:= l_command
     ||'  if r_det.update_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => r_det.ivid
             , p_detail_irid       => r_det.irid
             , p_detail_type_id    => r_det.types
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Update''
             );    
          
          end if;
     ';
   end if; -- FUNENT,FUNATT,DBI and ENTITY_REF, ATTRIBUTE_REF,COLUMN_REF
   -- display
   if p_tbl_name ='I$SDD_ITE'
      and
      r_ref.column_name = 'COLUMN_REF'
   then
     l_command:= l_command
     ||'  if r_det.display_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => r_det.ivid
             , p_detail_irid       => r_det.irid
             , p_detail_type_id    => r_det.types
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Display''
             );    
          
          end if;
     ';
     l_command:= l_command
     ||'  if r_det.select_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => r_det.ivid
             , p_detail_irid       => r_det.irid
             , p_detail_type_id    => r_det.types
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Select''
             );    
          
          end if;
     ';
   end if; -- DBI and COLUMN_REF
   -- RETRIEVE
   if p_tbl_name in ('I$SDD_FUNENT','I$SDD_FUNATT')
      and
      r_ref.column_name in ( 'ENTITY_REF','ATTRIBUTE_REF')
   then
     l_command:= l_command
     ||'  if r_det.retrieve_flag = ''Y''
          then
             parse4deps.add_usage
             ( p_pac_ivid          => '||nvl(to_char(p_pac_ivid),'null')||'
             , p_detail_ivid       => r_det.ivid
             , p_detail_irid       => r_det.irid
             , p_detail_type_id    => r_det.types
             , p_global_name_used  => parse4deps.get_descriptor
                                      ( p_ivid => null
                                      , p_irid=> r_det.'||r_ref.column_name||'
                                      , p_type_id => r_ref.types
                                      )
             , p_type_id_used      => r_ref.types
             , p_dependency_subtype => ''Retrieve''
             );    
          
          end if;
     ';
   end if; -- FUNENT,FUNATT and ENTITY_REF, ATTRIBUTE_REF
   l_command:= l_command
   ||' end if;  
     ';
 end loop; -- r_ref
 l_command:= l_command
 ||'   null; 
     end loop; -- r_det
  end;'
 ;
 /*
 for i in 1..250 loop
   dbms_output.put_line( substr(l_command,1+(i-1)* 250,250));
   if i*250 > length(l_command)
   then
    exit;
    end if;
 end loop;
 */
 execute immediate l_command;
 add_ref_type( p_type_id => p_tab_irid);
end; -- add_usages_for_type

procedure parse_details
( p_ivid        in number  -- father of details
, p_father_type_id in number default null
, p_father_tab_irid in number default null
, p_add_details in boolean default true
, p_level       in number  default 1
) is
  l_command varchar2(30000);
  l_curs    varchar2(30):= '_det'||to_char(p_level);
  cursor c_details
       ( b_father_tab_irid in number
       ) is
  select distinct
         'i$'||tab2.table_name tab_name
  ,      tab2.irid tab_irid
--  ,      con2.is_owning_fk
--  ,      col.column_name
--  ,      et2.irid  detail_type_id
  from   i$RM_SQL_TABLES tab2
  ,      i$RM_SQL_CONSTRAINTS con1
  ,      i$RM_SQL_CONSTRAINTS con2
  ,      rm_element_types et2
  ,      i$rm_sql_row_types srt2
  ,      i$RM_SQL_CONS_COLUMNS col
  where  con1.table_irid  = b_father_tab_irid
  and    tab2.irid=con2.table_irid
  and    con2.r_constraint_name=con1.constraint_name
  and    con2.is_owning_fk!='N'
  and    col.constraint_irid=con2.irid
  and    tab2.irid!= con1.table_irid
  and    srt2.table_mapped = tab2.irid
  and    et2.primary_row_type = srt2.id
  ;

begin
  -- loop over all tables that may contain details of the current node
  dbms_output.put_line('Details for '||to_char(p_father_type_id)||' '
  ||jr_name.get_descriptor(el_id => p_ivid,el_type => p_father_type_id,format=>'NAME'));
  add_det_type( p_type_id => p_father_tab_irid);
  for r_details in c_details( b_father_tab_irid => p_father_tab_irid ) loop
    dbms_output.put_line('Go find details from '||r_details.tab_name);
    if skip( p_type_id => r_ov.logical_type_id, p_tbl_name => upper(r_details.tab_name))
    then
      null; -- skip details from this table
    else
      -- call add detail for all records in upper(r_details.tab_name) with p_parent_ivid = p_ivid
      l_command:= 
      'declare
         cursor c'||l_curs||' is
           select det.ivid
           ,      det.irid
           ,      det.types
           from  '||r_details.tab_name||' det
           where det.parent_ivid = '||to_char(p_ivid)||'
           ;
         begin
           for r'||l_curs||' in c'||l_curs||' loop
             dbms_output.put_line(''- found ''||parse4deps.get_descriptor
                                ( p_ivid => r'||l_curs||'.ivid
                                , p_irid => r'||l_curs||'.irid
                                , p_type_id => r'||l_curs||'.types
                                )
                                );
             parse4deps.add_detail
             ( p_pac_ivid => '||to_char(p_ivid)||'
             , p_detail_irid => r'||l_curs||'.irid
             , p_detail_ivid => r'||l_curs||'.ivid
             , p_detail_type_id => r'||l_curs||'.types
             , p_global_name => parse4deps.get_descriptor
                                ( p_ivid => r'||l_curs||'.ivid
                                , p_irid => r'||l_curs||'.irid
                                , p_type_id => r'||l_curs||'.types
                                )
             );


             -- this is the perfect place to add some dependencies as well
             /*
             parse4deps.add_usages_for_element
             ( p_ivid     => r'||l_curs||'.ivid
             , p_irid     => r'||l_curs||'.irid
             , p_pac_ivid => '||to_char(p_ivid)||'
             , p_type_id => r'||l_curs||'.types
             );
             */             
             parse4deps.parse_mlt
             ( p_ivid     => r'||l_curs||'.ivid
             , p_irid     => r'||l_curs||'.irid
             , p_pac_ivid => '||to_char(p_ivid)||'
             , p_type_id => r'||l_curs||'.types
             );   
             -- look for further details
             if not parse4deps.exists_in_det_table(p_type_id => '||to_char(r_details.tab_irid)||')
             then
               parse4deps.parse_details
               ( p_ivid        => '||to_char(p_ivid)||'
               , p_father_type_id => r'||l_curs||'.types
               , p_father_tab_irid => '||to_char(r_details.tab_irid)||'
               , p_add_details => true
               , p_level       => '||to_char(p_level + 1)||'
               );
             else
               dbms_output.put_line(''can skip ''||to_char(r'||l_curs||'.types));
             end if;

           end loop; -- r'||l_curs||'
           if not parse4deps.exists_in_ref_table(p_type_id => '||to_char(r_details.tab_irid)||')
           then
             parse4deps.add_usages_for_type
             ( p_pac_ivid => '||to_char(p_ivid)||'
             , p_tab_irid => '||to_char(r_details.tab_irid)||'
             , p_tbl_name => '''||upper(r_details.tab_name)||'''
             );
           end if;
         end;
       ';           
/*
 for i in 1..250 loop
   dbms_output.put_line( substr(l_command,1+(i-1)* 250,250));
   if i*250 > length(l_command)
   then
    exit;
    end if;
 end loop;
 */
      execute immediate l_command;      
    end if; -- skip
  end loop; -- r_details
end; -- parse_details

-- browse through the Multi Line Text associated with object p_ivid (a detail of p_pac_ivid)
-- if the <dependency tag is found, then parse that text block for all dependencies indicated
procedure parse_mlt
( p_ivid     in number
, p_irid     in number
, p_pac_ivid in number  
, p_type_id  in number
) is

  l_last_txt_type varchar2(10):='AAA';
  l_first boolean:= true;
  cursor c_txt
         ( b_parent_ivid in number  
         , b_irid in number
         ) is
    select txt.txt_type
    ,      txt.txt_text
    ,      txt.txt_seq
    from   rm_text_lines txt
    where  txt.parent_ivid = b_parent_ivid
    and    txt.txt_ref = b_irid
    UNION
    select 'xxx'  txt_type
    ,      'xxx'  txt_text
    ,      0      txt_seq
    from   dual  
    order
    by     txt_type
    ,      txt_seq
    ;
    l_read_string    varchar2(32760);
    l_length         number(5);
    
    procedure readall
    ( p_ref in number
    , p_parent_ivid in number
    ) is
    begin
        OPEN  c_txt
              ( b_parent_ivid => p_pac_ivid
              , b_irid        => p_irid
              );
        FETCH c_txt
        BULK COLLECT
        INTO  parse4deps.g_txt_type
        ,     parse4deps.g_txt_text
        ,     parse4deps.g_txt_seq
        ;
        close c_txt;
    end; -- readall
    
  begin
    readall
    ( p_parent_ivid => p_pac_ivid
    , p_ref         => p_irid
    );
    for i in 1..g_txt_text.count loop
      if g_txt_type(i) <> l_last_txt_type       
      then
        if not l_first
        then
          if instr( l_read_string, '<dependency') > 0
          then
            parse_string
            ( p_string      => l_read_string
            , p_pac_ivid    => p_pac_ivid
            , p_detail_ivid => p_ivid
            , p_detail_irid => p_irid
            , p_detail_type_id => p_type_id
            );
          end if; -- instr( l_read_string, '<dependency') > 0
       end if; -- not l_first
       l_read_string:='';
       l_last_txt_type:= g_txt_type(i);
     end if; -- g_txt_type(i) <> l_last_txt_type       
     l_read_string:= l_read_string||g_txt_text(i);
     l_first:= false;
  end loop; -- r_txt
end; -- parse_mlt
  
procedure parse_file
( p_ivid in number
) is

  cursor c_fil
         ( b_ivid in number
         ) is
    select contents_blob
    ,      kind -- B(inary) C(haracter)
    ,      irid
    ,      name
    from   i$sdd_files
    where  ivid = b_ivid
    ;
  r_fil c_fil%rowtype;
  l_start_pos number(15):= 1;
  l_end_pos   number(15):= 0;
  l_find_pos   number(15):= 0;
  
  function find_keyword
  ( p_keyword in varchar2
  , p_offset  in number
  ) return number is
    l_find_pos number(15);
   begin
     l_find_pos:= dbms_lob.instr
                  ( lob_loc =>   r_fil.contents_blob
                   ,pattern =>   utl_raw.cast_to_raw(p_keyword)
                   ,offset  =>   p_offset
                  );
      return nvl(l_find_pos,0);
   end; -- find_keyword

begin
    open c_fil( b_ivid => p_ivid);
    fetch c_fil
    into  r_fil;
    close c_fil;
    loop
      -- find next occurrence of <dependency
      l_find_pos:= find_keyword( '<dependency', l_start_pos);
      if l_find_pos = 0
      then
        exit;
      end if;
      l_end_pos:= find_keyword( '</dependency', l_find_pos);
      if l_end_pos = 0
      then
        exit;
      end if;
      parse_string
      ( p_string => utl_raw.cast_to_varchar2
                    ( dbms_lob.substr
                      ( lob_loc =>   r_fil.contents_blob
                       ,amount  =>   l_end_pos - l_find_pos + 13
                       ,offset  =>   l_find_pos
                      )
                    )
      , p_pac_ivid    => null
      , p_detail_ivid => p_ivid
      , p_detail_irid => r_fil.irid
      , p_detail_type_id => 4819
      );
      l_start_pos:= l_end_pos + 14;
    end loop;
end; -- parse_file
  
  
procedure parse_pac
( p_ivid in number
) is
  l_command varchar2(30000);
begin
  reset_type_tbl;
  get_ov( p_ivid => p_ivid);
  parse4deps.add_pac
  ( p_pac_ivid  => p_ivid
  , p_pac_irid  => r_ov.irid
  , p_type_id   => r_ov.logical_type_id
  , p_global_name => r_ov.name
  );
   parse4deps.add_usages_for_element
   ( p_ivid     => p_ivid
   , p_irid     => r_ov.irid
   , p_pac_ivid => null
   , p_type_id  => r_ov.logical_type_id
   );             
   parse_mlt
   ( p_ivid     => p_ivid
   , p_irid     => r_ov.irid
   , p_pac_ivid => p_ivid
   , p_type_id  => r_ov.logical_type_id
   );   
  if r_ov.logical_type_id = 4819 -- PRIMARY_ACCESS_FILE
  then
    parse_file( p_ivid => p_ivid); 
  else
    -- loop over all tables that may contain details of the current node
    parse4deps.parse_details
    ( p_ivid => p_ivid
    , p_father_type_id => r_ov.logical_type_id
    , p_father_tab_irid => get_table_irid(p_type_id => r_ov.logical_type_id)
    , p_add_details => true
    , p_level => 1
    );
  end if; -- r_ov.logical_type_id = 4819 -- PRIMARY_ACCESS_FILE

  -- now indicate that there is dependency information 
  update i$sdd_object_versions ov
  set    ov.obj_notm_when_analyzed = ov.obj_notm 
  where  ov.ivid = p_ivid
  ;
end; -- parse_pac

begin
  select irid
  into   g_deptype_provides
  from   sdd_dependency_types
  where  type = 'Provides'
  and    subtype is null
  ;
  select irid
  into   g_deptype_uses
  from   sdd_dependency_types
  where  type = 'Uses'
  and    subtype is null
  ;
  -- initialisation
  select id 
  into g_typeid_sdd_global_names from rm_element_types where name='GLOBAL_NAME';
end parse4deps;
/