create or replace package body odwadeps is /***************************************************************************************** Purpose Supports displayal of Dependency Information. Usage Called from ODWABROW - Revision history When Who Construct Revision What ------------------------------------------------------------------------------------------ $REVISION_HISTORY$ 03-Dec-2002 Kannan Parthasarathy - B2570312: Do not attempt to parse diagrams and storage definitions. 21-mar-2001 Lucas Jellema 1.10 - try to show Uses only for the Global Name provided as Principal for Dependencies from IVID itself 21-mar-2001 Lucas Jellema 1.9 - Uses node when selected shows too many entries in the property palette; this is still a problem. Some minor changes were made but the over all provblem is stilll there: for every detail provided by the PAC, an entry is shown in the property palette. Instead, only details that actually have a dependency on the global name p_supivid should show up. The problem is that for the PAC the provided Global Name is looked up, which can be more than one if the PAC provides a number of details. 21-mar-2001 Lucas Jellema 1.8 - improve presentation of structured dependencies - allow parsing of dependencies 21-mar-2001 Lucas Jellema 1.7 - improve presentation of structured dependencies 21-mar-2001 Lucas Jellema 1.6 - improve presentation of structured dependencies - intermdeiate check in (Savepoint) 14-mar-2001 Lucas Jellema 1.5 - add support for External Dependencies - support for Details of Structured Elements - improved layout, more use of icons, better type labels 09-mar-2001 Lucas Jellema 1.4 - add browser link in Used_By Property palette - display providers in Used palette 06-mar-2001 Lucas Jellema 1.3 - only show Standalone PL/SQL Objects if there are any - show distinct usages (not one node for each usage type) 06-mar-2001 Lucas Jellema 1.2 - PL/SQL Objects without period (stored functions, stored procedures, rubbish collected by the PL/SQL parser) should all be collected under one node 06-mar-2001 Lucas Jellema 1.1 - collect COLUMNS used under RELATION_DEFINITIONS - group Packages etc. 05-mar-2001 Lucas Jellema 1.0 Checked in after upload from D:\odwa6i\ddl\odwadeps.pkb 28-feb-2001 Lucas Jellema 1.0 Initial Creation *****************************************************************************************/ /* $add_to_revision_history procedure parse4dependencies: add onLoad trigger to refresh the left side tree in order to be able to requery the new dependencies $ */ -- -- private constants -- REVISION_LABEL constant varchar2(30) := '$x.y::1.10 $'; PACKAGE_NAME constant varchar2(30) := 'ODWADEPS'; type t_col_rec is record ( tbl_name varchar2(40) , col_name varchar2(40) , supplier_ivid number(38) , client_ivid number(38) ); type t_col_tbl is table of t_col_rec index by binary_integer ; type t_sa_rec is record ( name varchar2(200) , supplier_ivid number(38) , client_ivid number(38) ); type t_sa_tbl is table of t_sa_rec index by binary_integer ; 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 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(+) ; cursor c_gnm_for_nm_ivid ( b_nm_ivid in number ) is select d.supplier_ivid , d.supplier_irid from sdd_dependencies d , sdd_global_names nm where nm.ivid = b_nm_ivid and nm.irid = d.client_irid ; r_gnm_for_nm_ivid c_gnm_for_nm_ivid%rowtype; r_gnm c_gnm%rowtype; 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_nm_ivid ( p_nm_ivid in number ) is begin open c_gnm_for_nm_ivid( b_nm_ivid=> p_nm_ivid); fetch c_gnm_for_nm_ivid into r_gnm_for_nm_ivid; close c_gnm_for_nm_ivid; end; -- get_gnm function get_global_name ( p_ivid in number ) return varchar2 is begin get_gnm( p_ivid=> p_ivid); return r_gnm.global_name; end; -- get_global_name function get_global_type_label ( p_ivid in number ) return varchar2 is begin get_gnm( p_ivid=> p_ivid); return r_gnm.type_label; end; -- get_global_name function get_global_name_for_nm ( p_nm_ivid in number ) return varchar2 is begin get_gnm_for_nm_ivid( p_nm_ivid=> p_nm_ivid); return get_global_name(p_ivid => r_gnm_FOR_NM_IVID.supplier_ivid); end; -- get_global_name function get_global_type_label_for_nm ( p_nm_ivid in number ) return varchar2 is begin get_gnm_for_nm_ivid( p_nm_ivid=> p_nm_ivid); return get_global_type_label(p_ivid => r_gnm_FOR_NM_IVID.supplier_ivid); end; -- get_global_name procedure uses ( p_ivid in number default odwactxt.get_pac_ivid , p_cliivid in number , p_supivid in number ) is cursor c_use ( b_cliivid in number , b_supivid in number ) is select nm.name global_name , nm.irid nm_irid , nmd.name global_name_domain_name , et.name element_type_name , nvl( nmd.name, et.name) type_label , dt.type , dt.subtype , d.status , d.remark , d.copy_on_version , d.copy_on_copy from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et , sdd_dependency_types dt where nm.irid = d.supplier_irid and d.supplier_ivid = b_supivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and b_cliivid = d.client_ivid and d.dependency_type_irid = dt.irid order by type_label , global_name ; cursor c_pvr ( b_nm_irid in number ) is select distinct ov.ivid , ov.irid , ov.name , ov.vlabel , ov.sequence_in_branch , ov.logical_type_id from sdd_dependencies d , sdd_global_names nm , i$sdd_object_versions ov where nm.irid = d.client_irid and ov.ivid = d.supplier_ivid and nm.irid = b_nm_irid UNION select distinct ov.ivid , ov.irid , ov.name , ov.vlabel , ov.sequence_in_branch , ov.logical_type_id from sdd_dependencies d , sdd_global_names nm , i$sdd_object_versions ov where nm.irid = d.client_irid and ov.ivid = d.supplier_parent_ivid and nm.irid = b_nm_irid order by 3 , 5 ; l_first boolean; l_last_irid number(38):=0; l_providers varchar2(10000); begin l_first:= true; for r_use in c_use ( b_cliivid => nvl( p_cliivid, p_ivid) , b_supivid => p_supivid ) loop if l_first then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_NAME,'','','','') , p_property_value => jr_name.get_path(id => p_ivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => p_ivid)||')' ); if p_ivid <> p_cliivid then -- PAC IVID <> explicit Client, that probably means the Client is a detail odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_DETAIL,'','','','') , p_property_value => get_global_type_label( p_ivid => p_cliivid) ||cdwpbase.nbsp(2) ||get_global_name( p_ivid => p_cliivid) , p_mandatory=> 'Y' ); end if; -- p_ivid <> p_cliivid odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP095_ODWADEPS_USES,'','','','') , p_property_value => r_use.type_label||cdwpbase.nbsp(2)||r_use.global_name , p_mandatory=> 'Y' ); l_last_irid:= 0; -- list providers of this Used Element for r_pvr in c_pvr (b_nm_irid => r_use.nm_irid) loop if r_pvr.irid <> l_last_irid then if l_last_irid <> 0 then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP096_ODWADEPS_PROVIDER,'','','','') , p_property_value => substr( l_providers, 1, length(l_providers)-1) , p_mandatory=> 'N' ); end if; -- r_pvr.irid <> 0 l_last_irid:= r_pvr.irid; l_providers := jr_name.get_path ( id => jr_acc_rights.get_owning_container(obj_irid => r_pvr.irid) , format => 'NAME' ) ||'/' ||cdwp.add_images('{'||cdwpbase.get_icon(p_type_id => r_pvr.logical_type_id)||'}') ||cdwpbase.get_nls_type_name(p_type_id => r_pvr.logical_type_id) ||cdwpbase.nbsp(2) ||get_global_name(r_pvr.ivid); end if; -- r_pvr.irid <> l_last_irid l_providers:= l_providers ||' ('||nvl( r_pvr.vlabel,odwavrsn.get_version_label(p_ivid => r_pvr.ivid))||')' ||odwabrow.browser_link ( p_session_id => odwactxt.get_session_id , p_ivid => r_pvr.ivid , p_text => cdwp.add_images ( '{browsesmall.jpg}' ) ) ||',' ; end loop; -- r_pvr if l_last_irid <> 0 then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP096_ODWADEPS_PROVIDER,'','','','') , p_property_value => substr( l_providers, 1, length(l_providers)-1) , p_mandatory=> 'N' ); else odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP096_ODWADEPS_PROVIDER,'','','','') , p_property_value => Rob_msg.GetMsg(Rob_msg.DSP225_ODWACHCK_NOPROV,'','','','') , p_mandatory=> 'N' ); end if; -- l_last_irid <> 0 l_first:= false; end if; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP097_ODWADEPS_UTYPE,'','','','') , p_property_value => nvl(r_use.subtype, r_use.type) , p_mandatory=> 'N' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP098_ODWADEPS_REM,'','','','') , p_property_value => r_use.remark , p_mandatory=> 'N' ); end loop; -- r_use end; -- uses -- PAC p_ivid uses the global name p_supivid procedure uses ( p_ivid in number default odwactxt.get_pac_ivid , p_supivid in number ) is cursor c_use ( b_ivid in number , b_supivid in number ) is select nm.name global_name , nm.irid nm_irid , nmd.name global_name_domain_name , et.name element_type_name , nvl( nmd.name, et.name) type_label , dt.type , dt.subtype , d.status , d.remark , d.copy_on_version , d.copy_on_copy , nm2.name client_name , nvl( nmd2.name, et2.name) client_type_label from sdd_dependencies d -- the dependencies from PAC b_ivid on b_supivid , sdd_global_names nm -- the global name provided by b_supivid , sdd_dependencies d2 , sdd_global_names nm2 , sdd_global_name_domains nmd , rm_element_types et , sdd_global_name_domains nmd2 , rm_element_types et2 , sdd_dependency_types dt , sdd_dependency_types dt2 where nm.ivid = d.supplier_ivid and d.supplier_ivid = b_supivid and d.dependency_type_irid = dt.irid and b_ivid = d.client_parent_ivid -- Clients that are details of b_ivid and d2.supplier_ivid = d.client_ivid and d2.client_ivid = nm2.ivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and nm2.domain_irid = nmd2.irid(+) and nm2.type_id = et2.id(+) and d2.dependency_type_irid = dt2.irid UNION -- dependencies for b_ivid itself select nm.name global_name , nm.irid nm_irid , nmd.name global_name_domain_name , et.name element_type_name , nvl( nmd.name, et.name) type_label , dt.type , dt.subtype , d.status , d.remark , d.copy_on_version , d.copy_on_copy , nm2.name client_name , nvl( nmd2.name, et2.name) client_type_label from sdd_dependencies d , sdd_global_names nm , sdd_dependencies d2 , sdd_global_names nm2 , sdd_global_name_domains nmd , rm_element_types et , sdd_global_name_domains nmd2 , rm_element_types et2 , sdd_dependency_types dt , sdd_dependency_types dt2 where nm.irid = d.supplier_irid and d.supplier_ivid = b_supivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and nm2.domain_irid = nmd2.irid(+) and nm2.type_id = et2.id(+) and d.dependency_type_irid = dt.irid and b_ivid = d.client_ivid and d2.supplier_ivid = d.client_ivid and d2.client_ivid = nm2.ivid and d2.dependency_type_irid = dt2.irid and dt2.subtype='Principal' UNION -- dependencies for b_ivid itself ni case b_ivid does not provide a Global Name with subtype Principal select nm.name global_name , nm.irid nm_irid , nmd.name global_name_domain_name , et.name element_type_name , nvl( nmd.name, et.name) type_label , dt.type , dt.subtype , d.status , d.remark , d.copy_on_version , d.copy_on_copy , ov.name client_name , et2.name client_type_label from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et , rm_element_types et2 , i$sdd_object_versions ov , sdd_dependency_types dt where nm.irid = d.supplier_irid and d.supplier_ivid = b_supivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and ov.logical_type_id = et2.id and ov.ivid = b_ivid and d.dependency_type_irid = dt.irid and b_ivid = d.client_ivid and not exists ( select 'x' from sdd_dependencies d2 , sdd_dependency_types dt2 where d2.supplier_ivid = d.client_ivid and d2.dependency_type_irid = dt2.irid and dt2.subtype='Principal' ) order by type_label , global_name , client_type_label , client_name ; cursor c_pvr ( b_nm_irid in number ) is select distinct ov.ivid , ov.irid , ov.name , ov.vlabel , ov.sequence_in_branch , ov.logical_type_id from sdd_dependencies d , sdd_global_names nm , i$sdd_object_versions ov where nm.irid = d.client_irid and ov.ivid = d.supplier_ivid and nm.irid = b_nm_irid UNION select distinct ov.ivid , ov.irid , ov.name , ov.vlabel , ov.sequence_in_branch , ov.logical_type_id from sdd_dependencies d , sdd_global_names nm , i$sdd_object_versions ov where nm.irid = d.client_irid and ov.ivid = d.supplier_parent_ivid and nm.irid = b_nm_irid order by 2 , 3 , 5 ; l_first boolean; l_last_irid number(38):=0; l_last_client varchar2(500):='xx'; l_providers varchar2(10000); begin l_first:= true; for r_use in c_use ( b_ivid => p_ivid , b_supivid => p_supivid ) loop if l_first then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_NAME,'','','','') , p_property_value => jr_name.get_path(id => p_ivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => p_ivid)||')' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP095_ODWADEPS_USES,'','','','') , p_property_value => r_use.type_label||cdwpbase.nbsp(2)||r_use.global_name , p_mandatory=> 'Y' ); l_first:= false; -- list providers of this Used Element l_last_irid:= 0; for r_pvr in c_pvr (b_nm_irid => r_use.nm_irid) loop if r_pvr.irid <> l_last_irid then if l_last_irid <> 0 then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP096_ODWADEPS_PROVIDER,'','','','') , p_property_value => substr( l_providers, 1, length(l_providers)-1) , p_mandatory=> 'N' ); end if; -- r_pvr.irid <> 0 l_last_irid:= r_pvr.irid; l_providers := jr_name.get_path ( id => jr_acc_rights.get_owning_container(obj_irid => r_pvr.irid) , format => 'NAME' ) ||'/' ||cdwp.add_images('{'||cdwpbase.get_icon(p_type_id => r_pvr.logical_type_id)||'}') ||cdwpbase.get_nls_type_name(p_type_id => r_pvr.logical_type_id) ||cdwpbase.nbsp(2) ||get_global_name(r_pvr.ivid); end if; -- r_pvr.irid <> l_last_irid l_providers:= l_providers ||' ('||nvl( r_pvr.vlabel,odwavrsn.get_version_label(p_ivid => r_pvr.ivid))||')' ||odwabrow.browser_link ( p_session_id => odwactxt.get_session_id , p_ivid => r_pvr.ivid , p_text => cdwp.add_images ( '{browsesmall.jpg}' ) ) ||',' ; end loop; -- r_pvr if l_last_irid <> 0 then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP096_ODWADEPS_PROVIDER,'','','','') , p_property_value => substr( l_providers, 1, length(l_providers)-1) , p_mandatory=> 'N' ); else odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP096_ODWADEPS_PROVIDER,'','','','') , p_property_value => Rob_msg.GetMsg(Rob_msg.DSP225_ODWACHCK_NOPROV,'','','','') , p_mandatory=> 'N' ); end if; -- l_last_irid <> 0 end if; if r_use.client_type_label||cdwpbase.nbsp(2)||r_use.client_name<> l_last_client then l_last_client:= r_use.client_type_label||cdwpbase.nbsp(2)||r_use.client_name; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP099_ODWADEPS_CLINAME,'','','','') , p_property_value => l_last_client , p_mandatory=> 'Y' ); end if; -- r_use.client_type_label||cdwpbase.nbsp(2)||r_use.client_name<> l_last_client odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP097_ODWADEPS_UTYPE,'','','','') , p_property_value => nvl(r_use.subtype, r_use.type) , p_mandatory=> 'N' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP098_ODWADEPS_REM,'','','','') , p_property_value => r_use.remark , p_mandatory=> 'N' ); end loop; -- r_use end; -- uses procedure used_by ( p_ivid in number default odwactxt.get_pac_ivid , p_cliivid in number , p_supivid in number ) is cursor c_use ( b_ivid in number , b_supivid in number ) is select distinct nm.name global_name , nmd.name global_name_domain_name , et.name element_type_name , nvl( nmd.name, et.name) type_label , dt.type , dt.subtype , d.status , d.remark , d.copy_on_version , d.copy_on_copy , nvl(d.client_parent_ivid, d.client_ivid) client_pac_ivid , nm2.name client_name , nvl( nmd2.name, et2.name) client_type_label from sdd_dependencies d , sdd_dependencies d2 , sdd_global_names nm , sdd_global_names nm2 , sdd_global_name_domains nmd , rm_element_types et , sdd_global_name_domains nmd2 , rm_element_types et2 , sdd_dependency_types dt where nm.irid = d.supplier_irid and d.supplier_ivid = b_supivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and b_ivid = d.client_ivid and d.dependency_type_irid = dt.irid and d.client_ivid = d2.supplier_ivid and d2.client_ivid = nm2.ivid(+) and nm2.type_id = et2.id(+) and nm2.domain_irid= nmd2.irid(+) order by nvl( nmd.name, et.name) , nm.name ; l_first boolean; l_last_supplier varchar2(500):='xx'; l_last_client varchar2(500):='xx'; begin l_first:= true; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_NAME,'','','','') , p_property_value => jr_name.get_path(id => p_ivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => p_ivid)||')' , p_mandatory=> 'Y' ); for r_use in c_use ( b_ivid => p_cliivid , b_supivid => p_supivid ) loop if l_first then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP067_ODWA_USEDBY,'','','','') , p_property_value => jr_name.get_path(id => r_use.client_pac_ivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => r_use.client_pac_ivid)||')' ||odwabrow.browser_link ( p_session_id => odwactxt.get_session_id , p_ivid => r_use.client_pac_ivid , p_text => cdwp.add_images ( '{browsesmall.jpg}' , p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP072_ODWA_OB,'','','','')||'"' ) ) , p_mandatory=> 'Y' ); l_first:= false; end if; if l_last_supplier <> r_use.type_label||cdwpbase.nbsp(2)||r_use.global_name then l_last_supplier := r_use.type_label||cdwpbase.nbsp(2)||r_use.global_name; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_DETAIL,'','','','') , p_property_value => l_last_supplier , p_mandatory=> 'Y' ); end if; if l_last_client <> r_use.client_type_label||cdwpbase.nbsp(2)||r_use.client_name then l_last_client:= r_use.client_type_label||cdwpbase.nbsp(2)||r_use.client_name; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP067_ODWA_USEDBY,'','','','') , p_property_value => l_last_client , p_mandatory=> 'Y' ); end if; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP097_ODWADEPS_UTYPE,'','','','') , p_property_value => nvl(r_use.subtype, r_use.type) , p_mandatory=> 'N' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP098_ODWADEPS_REM,'','','','') , p_property_value => r_use.remark , p_mandatory=> 'N' ); end loop; -- r_use end; -- used_by procedure used_by_cli ( p_ivid in number default odwactxt.get_pac_ivid -- the ivid of the PAC that (or whose details) is used , p_cliivid in number -- ivid of the PAC or Detail that has dependencies on p_ivid and its details ) is cursor c_use ( b_cli_ivid in number , b_ivid in number ) is select nm.name global_name , nmd.name global_name_domain_name , et.name element_type_name , nvl( nmd.name, et.name) type_label , dt.type , dt.subtype , d.status , d.remark , d.copy_on_version , d.copy_on_copy , nvl(d.client_parent_ivid, d.client_ivid) client_pac_ivid , nm2.name client_name , nvl( nmd2.name, et2.name) client_type_label , nvl(d3.supplier_parent_ivid, d3.supplier_ivid) supplier_ivid from sdd_dependencies d -- Dependency from b_cli_ivid on Target Global Name provided by PAC b_ivid , sdd_dependencies d2 -- Global Name provided by b_cli_ivid , sdd_dependencies d3 -- Global Name provided by PAC b_ivid , sdd_global_names nm -- Target Global Name , sdd_global_names nm2 -- Client Global Name , sdd_global_name_domains nmd , rm_element_types et , sdd_global_name_domains nmd2 , rm_element_types et2 , sdd_dependency_types dt where b_cli_ivid = d.client_ivid -- Dependency from b_cli_ivid and nm.ivid = d.supplier_ivid -- Dependency on Global Name and d.dependency_type_irid = dt.irid and d.client_ivid = d2.supplier_ivid -- Global Name provided through d2 and d2.client_ivid = nm2.ivid(+) -- Global Name of b_cli_ivid and nm.ivid = d3.client_ivid -- Global Name provided through d3 and d3.supplier_ivid = b_ivid -- Global Name provided by PAC b_ivid itself and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and nm2.type_id = et2.id(+) and nm2.domain_irid= nmd2.irid(+) UNION select nm.name global_name , nmd.name global_name_domain_name , et.name element_type_name , nvl( nmd.name, et.name) type_label , dt.type , dt.subtype , d.status , d.remark , d.copy_on_version , d.copy_on_copy , nvl(d.client_parent_ivid, d.client_ivid) client_pac_ivid , nm2.name client_name , nvl( nmd2.name, et2.name) client_type_label , nvl(d3.supplier_parent_ivid, d3.supplier_ivid) supplier_ivid from sdd_dependencies d -- Dependency from b_cli_ivid on Target Global Name provided by PAC DETAILS , sdd_dependencies d2 -- Global Name provided by b_cli_ivid , sdd_dependencies d3 -- Global Name provided by PAC Details , sdd_global_names nm -- Target Global Name , sdd_global_names nm2 -- Client Global Name , sdd_global_name_domains nmd , rm_element_types et , sdd_global_name_domains nmd2 , rm_element_types et2 , sdd_dependency_types dt where b_cli_ivid = d.client_ivid -- Dependency from b_cli_ivid and nm.ivid = d.supplier_ivid -- Dependency on Global Name and d.dependency_type_irid = dt.irid and d.client_ivid = d2.supplier_ivid -- Global Name provided through d2 and d2.client_ivid = nm2.ivid(+) -- Global Name of b_cli_ivid and nm.ivid = d3.client_ivid -- Global Name provided through d3 and d3.supplier_parent_ivid = b_ivid -- Global Name provided by PAC's DETAILS itself and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and nm2.type_id = et2.id(+) and nm2.domain_irid= nmd2.irid(+) order by type_label , global_name ; l_first boolean; begin l_first:= true; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_NAME,'','','','') , p_property_value => jr_name.get_path(id => p_ivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => p_ivid)||')' , p_mandatory=> 'Y' ); for r_use in c_use ( b_cli_ivid => p_cliivid , b_ivid => p_ivid ) loop if l_first then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP067_ODWA_USEDBY,'','','','') , p_property_value => get_global_type_label(p_ivid => p_cliivid) ||' ' ||jr_name.get_path(id => r_use.client_pac_ivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => r_use.client_pac_ivid)||')' ||odwabrow.browser_link ( p_session_id => odwactxt.get_session_id , p_ivid => r_use.client_pac_ivid , p_text => cdwp.add_images ( '{browsesmall.jpg}' , p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP072_ODWA_OB,'','','','')||'"' ) ) , p_mandatory=> 'Y' ); odwapred.print_property ( p_property_name => '- '||Rob_msg.GetMsg(Rob_msg.CAP067_ODWA_USEDBY,'','','','') , p_property_value => r_use.client_type_label||cdwpbase.nbsp(2)||r_use.client_name , p_mandatory=> 'Y' ); l_first:= false; end if; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_DETAIL,'','','','') , p_property_value => r_use.type_label||cdwpbase.nbsp(2)||r_use.global_name , p_mandatory=> 'Y' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP097_ODWADEPS_UTYPE,'','','','') , p_property_value => nvl(r_use.subtype, r_use.type) , p_mandatory=> 'N' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP098_ODWADEPS_REM,'','','','') , p_property_value => r_use.remark , p_mandatory=> 'N' ); end loop; -- r_use end; -- used_by_cli procedure pac_used_by_version ( p_ivid in number default odwactxt.get_pac_ivid , p_cli_pac_ivid in number -- PAC ivid of the PAC who (and whose details) have dependencies on p_ivid and its details ) is -- all usages of PAC p_ivid and its details by PAC p_cliivid and its details cursor c_use ( b_supivid in number , b_cliivid in number ) is select d.supplier_ivid , d2.client_ivid , nm2.name global_name_client , nm.name global_name_supplier , nvl( nmd.name, et.name) supplier_type_label , nvl( nmd2.name, et2.name) client_type_label , dt.type , dt.subtype , d2.remark from sdd_dependencies d , sdd_dependencies d2 , sdd_dependencies d3 , sdd_global_names nm , sdd_global_names nm2 , sdd_global_name_domains nmd , rm_element_types et , sdd_dependency_types dt , sdd_global_name_domains nmd2 , rm_element_types et2 where nm.irid(+) = d.supplier_irid and d.supplier_ivid = b_supivid and d2.supplier_ivid = d.client_ivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and ( b_cliivid = d2.client_ivid or b_cliivid = d2.client_parent_ivid ) and d3.client_ivid = nm2.ivid(+) and d3.supplier_ivid = d2.client_ivid and d2.dependency_type_irid = dt.irid and nm2.domain_irid = nmd2.irid(+) and nm2.type_id = et2.id(+) UNION select d.supplier_ivid , d2.client_ivid , nm2.name global_name_client , nm.name global_name_supplier , nvl( nmd.name, et.name) supplier_type_label , nvl( nmd2.name, et2.name) client_type_label , dt.type , dt.subtype , d2.remark from sdd_dependencies d , sdd_dependencies d2 , sdd_dependencies d3 , sdd_dependencies d4 , sdd_global_names nm , sdd_global_names nm2 , sdd_global_name_domains nmd , rm_element_types et , sdd_global_name_domains nmd2 , rm_element_types et2 , sdd_dependency_types dt where d4.client_ivid = nm.ivid(+) and d4.supplier_ivid = d.supplier_ivid and d.supplier_parent_ivid = b_supivid and d2.supplier_ivid = d.client_ivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and ( b_cliivid = d2.client_ivid or b_cliivid = d2.client_parent_ivid ) and d3.client_ivid = nm2.ivid(+) and d3.supplier_ivid = d2.client_ivid and d2.dependency_type_irid = dt.irid and nm2.domain_irid = nmd2.irid(+) and nm2.type_id = et2.id(+) order by supplier_type_label , global_name_supplier ; l_first boolean; begin l_first:= true; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP100_ODWADEPS_REPORT,'','','','') , p_property_value => Rob_msg.GetMsg(Rob_msg.MSG175_ODWA_PACUSAGE,get_global_type_label(p_ivid => p_ivid),get_global_name(p_ivid => p_ivid) ,get_global_type_label(p_ivid => p_cli_pac_ivid),get_global_name(p_ivid => p_cli_pac_ivid)) , p_mandatory=> 'Y' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_NAME,'','','','') , p_property_value => get_global_type_label(p_ivid => p_ivid) ||' ' ||jr_name.get_path(id => p_ivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => p_ivid)||')' , p_mandatory=> 'Y' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP067_ODWA_USEDBY,'','','','') , p_property_value => get_global_type_label(p_ivid => p_cli_pac_ivid) ||' ' ||jr_name.get_path(id => p_cli_pac_ivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => p_cli_pac_ivid)||')' ||odwabrow.browser_link ( p_session_id => odwactxt.get_session_id , p_ivid => p_cli_pac_ivid , p_text => cdwp.add_images ( '{browsesmall.jpg}' , p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP072_ODWA_OB,'','','','')||'"' ) ) , p_mandatory=> 'Y' ); for r_use in c_use ( b_cliivid => p_cli_pac_ivid , b_supivid => p_ivid ) loop if r_use.supplier_type_label is not null then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_DETAIL,'','','','') , p_property_value => r_use.supplier_type_label||cdwpbase.nbsp(2)||r_use.global_name_supplier , p_mandatory=> 'Y' ); end if; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP067_ODWA_USEDBY,'','','','') , p_property_value => r_use.client_type_label||cdwpbase.nbsp(2)||r_use.global_name_client , p_mandatory=> 'Y' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP097_ODWADEPS_UTYPE,'','','','') , p_property_value => nvl(r_use.subtype, r_use.type) , p_mandatory=> 'N' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP098_ODWADEPS_REM,'','','','') , p_property_value => r_use.remark , p_mandatory=> 'N' ); end loop; -- r_use end; -- pac_used_by_version procedure sup_used_by_version ( p_sup_nm_ivid in number -- ivid of detail that is used , p_pac_ivid in number default odwactxt.get_pac_ivid , p_cli_pac_ivid in number -- PAC ivid of the PAC who (and whose details) have dependencies on p_sup_ivid ) is -- all usages of detail p_sup_ivid and its details by PAC p_cliivid and its details cursor c_use ( b_sup_nmivid in number , b_cliivid in number ) is select d2.client_ivid , nm2.name global_name_client , nvl( nmd2.name, et2.name) client_type_label , dt.type , dt.subtype , d2.remark from sdd_dependencies d2 , sdd_dependencies d3 , sdd_global_names nm2 , sdd_dependency_types dt , sdd_global_name_domains nmd2 , rm_element_types et2 where d2.supplier_ivid = b_sup_nmivid -- d.client_ivid => Global Name, d2. has dependency on Global Name and ( b_cliivid = d2.client_ivid or b_cliivid = d2.client_parent_ivid ) and d3.client_ivid = nm2.ivid(+) -- NM2 is globale name provided by Dep Client and d3.supplier_ivid = d2.client_ivid -- dependency client also provides a Global Name and d2.dependency_type_irid = dt.irid and nm2.domain_irid = nmd2.irid(+) and nm2.type_id = et2.id(+) order by client_type_label , global_name_client ; l_first boolean; begin l_first:= true; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP100_ODWADEPS_REPORT,'','','','') , p_property_value => Rob_msg.GetMsg(Rob_msg.MSG175_ODWA_PACUSAGE,get_global_type_label_for_nm(p_nm_ivid => p_sup_nm_ivid),get_global_name_for_nm(p_nm_ivid => p_sup_nm_ivid) ,get_global_type_label(p_ivid => p_cli_pac_ivid),get_global_name(p_ivid => p_cli_pac_ivid)) , p_mandatory=> 'Y' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_NAME,'','','','') , p_property_value => get_global_type_label_for_nm(p_nm_ivid => p_sup_nm_ivid) ||' ' ||jr_name.get_path(id => p_pac_ivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => p_pac_ivid)||')' , p_mandatory=> 'Y' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP067_ODWA_USEDBY,'','','','') , p_property_value => get_global_type_label(p_ivid => p_cli_pac_ivid) ||' ' ||jr_name.get_path(id => p_cli_pac_ivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => p_cli_pac_ivid)||')' ||odwabrow.browser_link ( p_session_id => odwactxt.get_session_id , p_ivid => p_cli_pac_ivid , p_text => cdwp.add_images ( '{browsesmall.jpg}' , p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP072_ODWA_OB,'','','','')||'"' ) ) , p_mandatory=> 'Y' ); for r_use in c_use ( b_cliivid => p_cli_pac_ivid , b_sup_nmivid => p_sup_nm_ivid ) loop odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP067_ODWA_USEDBY,'','','','') , p_property_value => r_use.client_type_label||cdwpbase.nbsp(2)||r_use.global_name_client , p_mandatory=> 'Y' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP097_ODWADEPS_UTYPE,'','','','') , p_property_value => nvl(r_use.subtype, r_use.type) , p_mandatory=> 'N' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP098_ODWADEPS_REM,'','','','') , p_property_value => r_use.remark , p_mandatory=> 'N' ); end loop; -- r_use end; -- sup_used_by_version procedure nm_used_by ( p_ivid in number default odwactxt.get_pac_ivid , p_cliivid in number , p_nm_ivid in number ) is cursor c_use ( b_cliivid in number , b_nm_ivid in number ) is select nm.name global_name , nmd.name global_name_domain_name , et.name element_type_name , nvl( nmd.name, et.name) type_label , dt.type , dt.subtype , d.status , d.remark , d.copy_on_version , d.copy_on_copy from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et , sdd_dependency_types dt where nm.irid = d.supplier_irid and nm.ivid = b_nm_ivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and b_cliivid = d.client_ivid and d.dependency_type_irid = dt.irid order by nvl( nmd.name, et.name) , nm.name ; l_first boolean; begin l_first:= true; for r_use in c_use ( b_cliivid => p_cliivid , b_nm_ivid => p_nm_ivid ) loop if l_first then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_NAME,'','','','') , p_property_value => jr_name.get_path(id => p_ivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => p_ivid)||')' , p_mandatory=> 'Y' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP094_ODWADEPS_DETAIL,'','','','') , p_property_value => r_use.type_label||cdwpbase.nbsp(2)||r_use.global_name , p_mandatory=> 'Y' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP067_ODWA_USEDBY,'','','','') , p_property_value => jr_name.get_path(id => p_cliivid,format => 'NAME',seperator => '/') ||' ('||odwavrsn.get_version_label(p_ivid => p_cliivid)||')' ||odwabrow.browser_link ( p_session_id => odwactxt.get_session_id , p_ivid => p_cliivid , p_text => cdwp.add_images ( '{browsesmall.jpg}' , p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP072_ODWA_OB,'','','','')||'"' ) ) , p_mandatory=> 'Y' ); l_first:= false; end if; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP097_ODWADEPS_UTYPE,'','','','') , p_property_value => nvl(r_use.subtype, r_use.type) , p_mandatory=> 'N' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP098_ODWADEPS_REM,'','','','') , p_property_value => r_use.remark , p_mandatory=> 'N' ); end loop; -- r_use end; -- nm_used_by procedure parse4dependencies ( p_session_id in number , p_ivid in number ) is l_start_time date := sysdate; l_found boolean := false; l_type_id number(38):= cdwpbase.get_ivid_type_id( p_ivid); cursor c_dpy ( b_created_after in date , b_ivid in number ) is select count(dpy.irid) count_dependencies , dt.type , dt.subtype from sdd_dependencies dpy , sdd_dependency_types dt where dpy.date_created > b_created_after - 0.001 and ( dpy.supplier_ivid = b_ivid or dpy.supplier_parent_ivid = b_ivid or dpy.client_ivid = b_ivid or dpy.client_parent_ivid = b_ivid ) and dt.irid = dpy.dependency_type_irid group by dt.type , dt.subtype ; begin odwactxt.update_context ( p_session_id => p_session_id , p_package_name => PACKAGE_NAME , p_procedure_name=> 'palette' ); htp.htmlOpen; htp.headOpen; cdwp.write_about(package_name, revision_label); cdwp.include_stnd_styles; htp.p(''); odwahelp.js_invoke_help; htp.headClose; htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF" onLoad="top.refreshNodeIdx( 1)"' -- $%x.y$ ); htp.p ('' ); odwapred.open_palette(p_title => Rob_msg.GetMsg(Rob_msg.CAP101_ODWADEPS_DPREP,'','','','')); if l_type_id = '4941' OR l_type_id = '5000' then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP0203_CDWP_ERRORMSG,'','','','') , p_property_value => Rob_msg.GetMsg(Rob_msg.MSG246_PARSE_NOT_SUPP,'','','','') , p_mandatory=> 'Y' ); else cdwpbase.start_timer; -- call parse4deps.parse_pac parse4deps.parse_pac(p_ivid => p_ivid); -- display a brief parse report (call dependencies again?) odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP102_ODWADEPS_STIME,'','','','') , p_property_value => to_char(l_start_time,'HH24:MI') , p_mandatory=> 'N' ); odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP103_ODWADEPS_PERF,'','','','') , p_property_value => Rob_msg.GetMsg(Rob_msg.DSP226_ODWADEPS_PTIME,cdwpbase.get_timer/100,'','','') , p_mandatory=> 'N' ); for r_dpy in c_dpy ( b_created_after => l_start_time , b_ivid => p_ivid ) loop l_found:= true; odwapred.print_property ( p_property_name => r_dpy.type||' - '||r_dpy.subtype , p_property_value => to_char(r_dpy.count_dependencies) , p_mandatory=> 'N' ); end loop; -- r_dpy if not l_found then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP104_ODWADEPS_PRES,'','','','') , p_property_value => Rob_msg.GetMsg(Rob_msg.DSP227_ODWADEPS_PRES,'','','','') , p_mandatory=> 'N' ); end if; end if; --l_types -- report on the number of Dependencies (per type, sub type) created during the parse odwapred.close_palette; htp.bodyClose; htp.htmlClose; end; -- parse4dependencies procedure dependencies ( p_ivid in number ) is cursor c_dpy ( b_ivid in number ) is select max( nvl(dpy.date_changed, dpy.date_created)) date_touched from sdd_dependencies dpy where dpy.supplier_ivid = b_ivid or dpy.supplier_parent_ivid = b_ivid or dpy.client_ivid = b_ivid or dpy.client_parent_ivid = b_ivid ; r_dpy c_dpy%rowtype; l_parse_status varchar2(1):= parse_status(p_ivid => p_ivid); begin open c_dpy( b_ivid => p_ivid); fetch c_dpy into r_dpy; close c_dpy; odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP105_ODWADEPS_PSTAT,'','','','') , p_property_value => cdwpbase.ifThenElse ( l_parse_status = 'C' , Rob_msg.GetMsg(Rob_msg.DSP228_ODWADEPS_PSTAT,'','','','') , cdwpbase.ifThenElse ( l_parse_status = 'I' , Rob_msg.GetMsg(Rob_msg.DSP229_ODWADEPS_PSTAT2,'','','','') , Rob_msg.GetMsg(Rob_msg.DSP230_ODWADEPS_PSTAT3,'','','','') ) ) , p_mandatory=> 'N' ); if l_parse_status <> 'N' then odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.DSP231_ODWADEPS_LPDATE,'','','','') , p_property_value => 'on or after '||htf.bold(to_char(r_dpy.date_touched, 'DD-mon-YYYY HH24:MI')) , p_mandatory=> 'N' ); end if; -- l_parse_status <> 'N' odwapred.print_property ( p_property_name => Rob_msg.GetMsg(Rob_msg.CAP106_ODWA_PARSE,'','','','') , p_property_value => '' ||cdwp.add_images ( '{parse_for_dependencies_round.gif}' , p_attributes => 'ALT="Parse this object to find new dependencies"' ) ||'' ||cdwpbase.nbsp(2) ||odwahelp.help_url(p_him_id => 1640,p_icon => true,p_alt_text => 'Help on dependency parsing') -- $%x.y$ changed helpid from 1620 to 1640 , p_mandatory=> 'N' ); end; -- dependencies procedure palette ( p_session_id in number , p_ivid in number , p_type_id in number default null , p_classification in varchar2 default null , p_type_of in varchar2 default null ) is l_ivid varchar2(400):= cdwpbase.get_tag_value( p_string=> p_classification, p_tag => 'IVID'); l_nm_ivid varchar2(400):= cdwpbase.get_tag_value( p_string=> p_classification, p_tag => 'NM_IVID'); l_supivid varchar2(400):= cdwpbase.get_tag_value( p_string=> p_classification, p_tag => 'SUPIVID'); l_cliivid varchar2(400):= cdwpbase.get_tag_value( p_string=> p_classification, p_tag => 'CLIIVID'); l_type varchar2(400):= cdwpbase.get_tag_value( p_string=> p_classification, p_tag => 'TYPE'); begin odwactxt.update_context ( p_session_id => p_session_id , p_package_name => PACKAGE_NAME , p_procedure_name=> 'palette' ); htp.htmlOpen; htp.headOpen; cdwp.write_about(package_name, revision_label); cdwp.include_stnd_styles; htp.p(''); odwahelp.js_invoke_help; htp.headClose; htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"' ); htp.p ('' ); odwapred.open_palette(p_title => Rob_msg.GetMsg(Rob_msg.CAP107_ODWADEPS_DPAL,'','','','')); if l_type = 'U' then if l_cliivid is null then uses ( p_ivid => odwactxt.get_pac_ivid , p_supivid=> to_number(l_supivid) ); else uses ( p_ivid => odwactxt.get_pac_ivid , p_cliivid => to_number(l_cliivid) , p_supivid=> to_number(l_supivid) ); end if; elsif l_type = 'UB' then used_by_cli ( p_ivid => odwactxt.get_pac_ivid , p_cliivid=> to_number(l_cliivid) ); elsif l_type = 'UBV' then pac_used_by_version ( p_ivid => odwactxt.get_pac_ivid , p_cli_pac_ivid=> to_number(l_cliivid) ); elsif l_type = 'DUBP' then sup_used_by_version ( p_sup_nm_ivid => to_number(l_nm_ivid) , p_pac_ivid => odwactxt.get_pac_ivid , p_cli_pac_ivid => to_number(l_cliivid) ); elsif l_type = 'DUB' then if l_nm_ivid is not null then nm_used_by ( p_ivid => odwactxt.get_pac_ivid , p_cliivid=> to_number(l_cliivid) , p_nm_ivid=> to_number(l_nm_ivid) ); else used_by ( p_ivid => odwactxt.get_pac_ivid , p_cliivid=> to_number(l_cliivid) , p_supivid=> to_number(l_nm_ivid) ); end if; elsif l_type = 'DEPENDENCIES' then dependencies( p_ivid => odwactxt.get_pac_ivid); end if; odwapred.close_palette; htp.bodyClose; htp.htmlClose; end; -- palette -- this function informs on the dependency parse status of the object version -- indicated by p_ivid. The result returned is one of the following: -- * C - complete, parsed and up to date -- * I - parsed but changed since then; potentially incomplete set of dependency data -- * N - not parsed at all function parse_status ( p_ivid in number ) return varchar2 is cursor c_ov ( b_ivid in number ) is select ov.obj_notm , ov.obj_notm_when_analyzed from i$sdd_object_versions ov where ov.ivid = b_ivid ; r_ov c_ov%rowtype; begin open c_ov( b_ivid => p_ivid); fetch c_ov into r_ov; close c_ov; if r_ov.obj_notm_when_analyzed is null or r_ov.obj_notm < r_ov.obj_notm_when_analyzed then return 'N'; end if; if r_ov.obj_notm > r_ov.obj_notm_when_analyzed then return 'I'; end if; return 'C'; end; -- parse_status function exist_used_by ( p_nm_ivid in number ) return boolean is cursor c_ub ( b_nm_ivid in number ) is select 'x' from dual where exists ( select 'x' from sdd_dependencies d2 where d2.supplier_ivid = b_nm_ivid -- that object version should not also be the client of a usage ) ; l_test varchar2(1); begin open c_ub( b_nm_ivid => p_nm_ivid); fetch c_ub into l_test; close c_ub; return nvl( l_test,'y') ='x'; end; -- exist_used_by function exist_uses ( p_ivid in number ) return boolean is cursor c_use ( b_ivid in number ) is select 'x' from dual where exists ( select 'x' from sdd_dependencies d2 where d2.client_ivid = b_ivid ) ; l_test varchar2(1); begin open c_use( b_ivid => p_ivid); fetch c_use into l_test; close c_use; return nvl( l_test,'y') ='x'; end; -- exist_uses -- this procedure writes in the Nodes Tree all nodes for the supplier details that have been identified -- within object p_ivid procedure supplier_detail_nodes ( p_ivid in number , p_node_level in number ) is cursor c_sd ( b_ivid in number ) is select nm.name global_name , d.supplier_ivid , et.id element_type_id , nm.ivid nm_ivid , nvl( nmd.name, et.name) type_label from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et where d.supplier_ivid = b_ivid and nm.irid = d.client_irid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) UNION -- 1.5 added to cater for Details of Structured Elements select nm.name global_name , d.supplier_ivid , et.id element_type_id , nm.ivid nm_ivid , nvl( nmd.name, et.name) type_label from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et where d.supplier_parent_ivid = b_ivid and nm.irid = d.client_irid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) order by type_label , global_name ; l_last_type_label varchar2(200):='XX'; l_name varchar2(200); begin for r_sd in c_sd( b_ivid => p_ivid) loop if r_sd.type_label <> l_last_type_label then l_last_type_label := r_sd.type_label; htmltree.add_node ( p_display_label => cdwpbase.ifThenElse ( r_sd.element_type_id is null , cdwp.add_images('{global_name_domain.gif}') , cdwp.add_images('{'||cdwpbase.get_icon(p_type_id => r_sd.element_type_id)||'}') ) ||cdwpbase.nbsp(1) ||initcap(replace(r_sd.type_label,'_',' ')) , p_node_level => p_node_level + 1 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; l_name := r_sd.global_name; if r_sd.type_label ='PLSQL_MODULE_SUBROUTINE' or r_sd.element_type_id is not null then l_name:= substr(l_name, instr(l_name,'.')+ 1); end if; htmltree.add_node ( p_display_label => cdwpbase.ifThenElse ( r_sd.element_type_id is null , cdwp.add_images('{global_name.gif}') , cdwp.add_images('{'||cdwpbase.get_icon(p_type_id => r_sd.element_type_id)||'}') ) ||cdwpbase.nbsp(1) ||l_name , p_node_level => p_node_level + 2 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); -- is the global name provided here (r_sd,nm_ivid) used anywhere in a dependency? if exist_used_by ( p_nm_ivid => r_sd.nm_ivid) then htmltree.add_node ( p_display_label => cdwp.add_images('{usedby.gif}') ||cdwpbase.nbsp(1)||Rob_msg.GetMsg(Rob_msg.CAP067_ODWA_USEDBY,'','','','') , p_node_level => p_node_level + 3 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => true , p_classification => '{TYPE=DEPS:DETUSEDBY}{NM_IVID='||to_char(r_sd.nm_ivid)||'}nolink' ); end if; -- exist_used_by -- does the PAC/detail under discussion here have any usages (dependencies) if r_sd.supplier_ivid <> p_ivid -- probably a detail and exist_uses ( p_ivid => r_sd.supplier_ivid) then htmltree.add_node ( p_display_label => cdwp.add_images('{uses.gif}') ||cdwpbase.nbsp(1)||'Uses' , p_node_level => p_node_level + 3 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => true , p_classification => '{TYPE=DEPS:DETUSES}{IVID='||to_char(r_sd.supplier_ivid)||'}nolink' ); end if; -- exist_uses end loop; -- r_sd end; -- supplier_detail_nodes -- this procedure writes in the Nodes Tree all nodes for the usages that have been identified -- for object p_ivid -- improvements: -- * group PLSQL_MODULE_PACKAGE_VARIABLE and PLSQL_MODULE_SUBROUTINE by Package -- * display Columns under the table they are part of procedure usages_nodes ( p_ivid in number , p_pac_ivid in number , p_node_level in number ) is l_sa_tbl t_sa_tbl; l_col_tbl t_col_tbl; l_col_ctr number(5):= 1; l_first boolean; cursor c_sd ( b_ivid in number ) is select d.supplier_irid , nm.name global_name , to_char(d.supplier_irid) -- refers to OBJECT_VERSIONS or , d.supplier_ivid , d.client_ivid , nmd.name global_name_domain_name , et.name element_type_name , decode ( instr(nvl( nmd.name, et.name),'PLSQL') , 0, nvl( nmd.name, et.name) , 'PL/SQL Object' ) type_label from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et , i$sdd_object_versions ov where nm.irid = d.supplier_irid and ov.ivid = b_ivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and ov.ivid = d.client_ivid UNION select d.supplier_irid , nm.name global_name , to_char(d.supplier_irid) -- refers to OBJECT_VERSIONS or , d.supplier_ivid , d.client_ivid , nmd.name global_name_domain_name , et.name element_type_name , decode ( instr(nvl( nmd.name, et.name),'PLSQL') , 0, nvl( nmd.name, et.name) , 'PL/SQL Object' ) type_label from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et , i$sdd_object_versions ov where nm.irid = d.supplier_irid and ov.ivid = b_ivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and ov.ivid = d.client_parent_ivid UNION select d.supplier_irid , nm.name global_name , to_char(d.supplier_irid) -- refers to OBJECT_VERSIONS or , d.supplier_ivid , d.client_ivid , nmd.name global_name_domain_name , et.name element_type_name , decode ( instr(nvl( nmd.name, et.name),'PLSQL') , 0, nvl( nmd.name, et.name) , 'PL/SQL Object' ) type_label from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et , i$sdd_object_versions ov where nm.irid = d.supplier_irid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and ov.ivid = d.client_parent_ivid and d.client_ivid = b_ivid order by type_label , global_name ; l_last_type_label varchar2(200):='XX'; l_name varchar2(200); l_last_name varchar2(200); l_new_type boolean; l_plsql_unit varchar2(200):='XX'; procedure add_column ( p_name in varchar2 , p_sup_ivid in number , p_client_ivid in number ) is l_found boolean := false; begin -- first check if column already exists for i in 1..l_col_tbl.count loop if l_col_tbl( i).supplier_ivid = p_sup_ivid then l_found:= true; exit; end if; end loop; if not l_found then l_col_tbl( l_col_tbl.count + 1).tbl_name:= substr( p_name, 1, instr( p_name, '.')-1); l_col_tbl( l_col_tbl.count).col_name:= substr( p_name, instr( p_name, '.')+1); l_col_tbl( l_col_tbl.count).supplier_ivid:= p_sup_ivid; l_col_tbl( l_col_tbl.count).client_ivid:= p_client_ivid; end if; end; -- add_column procedure add_so ( p_name in varchar2 , p_sup_ivid in number , p_client_ivid in number ) is begin l_sa_tbl( l_sa_tbl.count + 1).name:= p_name; l_sa_tbl( l_sa_tbl.count).supplier_ivid:= p_sup_ivid; l_sa_tbl( l_sa_tbl.count).client_ivid:= p_client_ivid; end; -- add_so begin for r_sd in c_sd( b_ivid => p_ivid) loop if r_sd.type_label <> 'PL/SQL Object' and l_last_type_label = 'PL/SQL Object' and l_sa_tbl.count > 0 then -- time to display the Stand Alone PL/SQL Objects htmltree.add_node ( p_display_label => cdwp.add_images('{global_name_domain.gif}') ||cdwpbase.nbsp(1)||Rob_msg.GetMsg(Rob_msg.CAP108_ODWADEPS_SAPLSO,'','','','') , p_node_level => p_node_level + 2 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); for i in 1..l_sa_tbl.count loop htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||l_sa_tbl(i).name , p_node_level => p_node_level + 3 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=U}{SUPIVID='||to_char(l_sa_tbl(i).supplier_ivid) ||'}{CLIIVID='||to_char(l_sa_tbl(i).client_ivid)||'}' ); end loop; end if; -- r_sd.type_label <> 'PL/SQL Object' and l_last_type_label = 'PL/SQL Object' if r_sd.type_label = 'COLUMN' then add_column( r_sd.global_name, r_sd.supplier_ivid, r_sd.client_ivid); else if r_sd.type_label <> l_last_type_label then l_last_type_label := r_sd.type_label; htmltree.add_node ( p_display_label => cdwp.add_images('{global_name_domain.gif}') ||cdwpbase.nbsp(1)||replace(initcap(r_sd.type_label),'_',' ') , p_node_level => p_node_level + 1 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); l_new_type:= true; else l_new_type:= false; end if; l_last_name:= l_name; l_name := r_sd.global_name; if not l_new_type and l_name = l_last_name then -- twice the same names of the same type: skip the second one null; else -- now if r_sd.type_label ='PL/SQL Object' -- and -- l_name contains a . (odwaprop.palette_link) -- the supplier is likely to be part of a package or greater unit whose name -- is substr (l_name, 1, instr(l_name,'.')-1) -- if the unit name is not l_plsql_unit, then add a node for the plsql unit -- then add nodes at a lower level for the current supplier, called -- substr (l_name, instr(l_name,'.')+1) if r_sd.type_label ='PL/SQL Object' then if instr(l_name,'.') > 0 then if l_plsql_unit <> substr (l_name, 1, instr(l_name,'.')-1) then l_plsql_unit := substr (l_name, 1, instr(l_name,'.')-1); htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||l_plsql_unit , p_node_level => p_node_level + 2 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; -- l_plsql_unit <> substr (l_name, 1, instr(l_name,'.')-1) htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||substr (l_name, instr(l_name,'.')+1) , p_node_level => p_node_level + 3 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=U}{SUPIVID='||to_char(r_sd.supplier_ivid) ||'}{CLIIVID='||to_char(r_sd.client_ivid)||'}' ); else add_so( r_sd.global_name, r_sd.supplier_ivid, r_sd.client_ivid); end if; -- instr(l_name,'.') > 0 else htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||l_name , p_node_level => p_node_level + 2 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=U}{SUPIVID='||to_char(r_sd.supplier_ivid) ||'}{CLIIVID='||to_char(r_sd.client_ivid)||'}' ); if r_sd.type_label = 'RELATION_DEFINITION' then -- add columns for i in l_col_ctr..l_col_tbl.count loop if l_col_tbl(i).tbl_name <> l_name then exit; end if; htmltree.add_node ( p_display_label => cdwp.add_images('{column.gif}') ||cdwpbase.nbsp(1) ||l_col_tbl(i).col_name , p_node_level => p_node_level + 3 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=U}{SUPIVID='||to_char(l_col_tbl(i).supplier_ivid) ||'}{CLIIVID='||to_char(l_col_tbl(i).client_ivid)||'}' ); l_col_tbl(i).tbl_name:=null; l_col_ctr:= l_col_ctr + 1; end loop; end if; --r_sd.type_label = 'RELATION_DEFINITION' end if; -- r_sd.type_label ='PL/SQL Object' end if; -- not l_new_type and l_name = l_last_name end if; -- r_sd.type_label = 'COLUMN' end loop; -- r_sd -- see if any columns are left l_first:= true; for i in l_col_ctr..l_col_tbl.count loop if l_col_tbl(i).tbl_name is not null -- it has not yet been displayed then if l_first then l_first := false; htmltree.add_node ( p_display_label => cdwp.add_images('{column.gif}') ||cdwpbase.nbsp(1) ||Rob_msg.GetMsg(Rob_msg.CAP109_ODWA_ADDLCOLS,'','','','') , p_node_level => p_node_level + 1 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; htmltree.add_node ( p_display_label => cdwp.add_images('{column.gif}') ||cdwpbase.nbsp(1) ||l_col_tbl(i).tbl_name ||'.' ||l_col_tbl(i).col_name , p_node_level => p_node_level + 2 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=U}{SUPIVID='||to_char(l_col_tbl(i).supplier_ivid) ||'}{CLIIVID='||to_char(l_col_tbl(i).client_ivid)||'}' ); end if; -- l_col_tbl(i).tbl_name is not null end loop; end; -- usages_nodes -- this procedure writes in the Nodes Tree all nodes for the usages that have been identified -- for PAC object p_ivid and its details procedure pac_usages_nodes ( p_ivid in number , p_node_level in number ) is l_sa_tbl t_sa_tbl; l_col_tbl t_col_tbl; l_col_ctr number(5):= 1; l_first boolean; cursor c_sd ( b_ivid in number ) is select d.supplier_irid , nm.name global_name , to_char(d.supplier_irid) -- refers to OBJECT_VERSIONS or , d.supplier_ivid , d.client_ivid , nmd.name global_name_domain_name , et.name element_type_name , decode ( instr(nvl( nmd.name, et.name),'PLSQL') , 0, nvl( nmd.name, et.name) , 'PL/SQL Object' ) type_label from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et , i$sdd_object_versions ov where nm.irid = d.supplier_irid and ov.ivid = b_ivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and ov.ivid = d.client_ivid UNION select d.supplier_irid , nm.name global_name , to_char(d.supplier_irid) -- refers to OBJECT_VERSIONS or , d.supplier_ivid , d.client_ivid , nmd.name global_name_domain_name , et.name element_type_name , decode ( instr(nvl( nmd.name, et.name),'PLSQL') , 0, nvl( nmd.name, et.name) , 'PL/SQL Object' ) type_label from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et , i$sdd_object_versions ov where nm.irid = d.supplier_irid and ov.ivid = b_ivid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and ov.ivid = d.client_parent_ivid UNION select d.supplier_irid , nm.name global_name , to_char(d.supplier_irid) -- refers to OBJECT_VERSIONS or , d.supplier_ivid , d.client_ivid , nmd.name global_name_domain_name , et.name element_type_name , decode ( instr(nvl( nmd.name, et.name),'PLSQL') , 0, nvl( nmd.name, et.name) , 'PL/SQL Object' ) type_label from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et , i$sdd_object_versions ov where nm.irid = d.supplier_irid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and ov.ivid = d.client_parent_ivid and d.client_ivid = b_ivid order by type_label , global_name ; l_last_type_label varchar2(200):='XX'; l_name varchar2(200); l_last_name varchar2(200); l_new_type boolean; l_plsql_unit varchar2(200):='XX'; procedure add_column ( p_name in varchar2 , p_sup_ivid in number , p_client_ivid in number ) is l_found boolean := false; begin -- first check if column already exists for i in 1..l_col_tbl.count loop if l_col_tbl( i).supplier_ivid = p_sup_ivid then l_found:= true; exit; end if; end loop; if not l_found then l_col_tbl( l_col_tbl.count + 1).tbl_name:= substr( p_name, 1, instr( p_name, '.')-1); l_col_tbl( l_col_tbl.count).col_name:= substr( p_name, instr( p_name, '.')+1); l_col_tbl( l_col_tbl.count).supplier_ivid:= p_sup_ivid; l_col_tbl( l_col_tbl.count).client_ivid:= p_client_ivid; end if; end; -- add_column procedure add_so ( p_name in varchar2 , p_sup_ivid in number , p_client_ivid in number ) is begin l_sa_tbl( l_sa_tbl.count + 1).name:= p_name; l_sa_tbl( l_sa_tbl.count).supplier_ivid:= p_sup_ivid; l_sa_tbl( l_sa_tbl.count).client_ivid:= p_client_ivid; end; -- add_so begin for r_sd in c_sd( b_ivid => p_ivid) loop if r_sd.type_label <> 'PL/SQL Object' and l_last_type_label = 'PL/SQL Object' and l_sa_tbl.count > 0 then -- time to display the Stand Alone PL/SQL Objects htmltree.add_node ( p_display_label => cdwp.add_images('{global_name_domain.gif}') ||cdwpbase.nbsp(1)||Rob_msg.GetMsg(Rob_msg.CAP108_ODWADEPS_SAPLSO,'','','','') , p_node_level => p_node_level + 2 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); for i in 1..l_sa_tbl.count loop htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||l_sa_tbl(i).name , p_node_level => p_node_level + 3 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=U}{SUPIVID='||to_char(l_sa_tbl(i).supplier_ivid) ||'}{CLIIVID='||to_char(l_sa_tbl(i).client_ivid)||'}' ); end loop; end if; -- r_sd.type_label <> 'PL/SQL Object' and l_last_type_label = 'PL/SQL Object' if r_sd.type_label = 'COLUMN' then add_column( r_sd.global_name, r_sd.supplier_ivid, r_sd.client_ivid); else if r_sd.type_label <> l_last_type_label then l_last_type_label := r_sd.type_label; htmltree.add_node ( p_display_label => cdwp.add_images('{global_name_domain.gif}') ||cdwpbase.nbsp(1)||replace(initcap(r_sd.type_label),'_',' ') , p_node_level => p_node_level + 1 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); l_new_type:= true; else l_new_type:= false; end if; l_last_name:= l_name; l_name := r_sd.global_name; if not l_new_type and l_name = l_last_name then -- twice the same names of the same type: skip the second one null; else -- now if r_sd.type_label ='PL/SQL Object' -- and -- l_name contains a . (odwaprop.palette_link) -- the supplier is likely to be part of a package or greater unit whose name -- is substr (l_name, 1, instr(l_name,'.')-1) -- if the unit name is not l_plsql_unit, then add a node for the plsql unit -- then add nodes at a lower level for the current supplier, called -- substr (l_name, instr(l_name,'.')+1) if r_sd.type_label ='PL/SQL Object' then if instr(l_name,'.') > 0 then if l_plsql_unit <> substr (l_name, 1, instr(l_name,'.')-1) then l_plsql_unit := substr (l_name, 1, instr(l_name,'.')-1); htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||l_plsql_unit , p_node_level => p_node_level + 2 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; -- l_plsql_unit <> substr (l_name, 1, instr(l_name,'.')-1) htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||substr (l_name, instr(l_name,'.')+1) , p_node_level => p_node_level + 3 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=U}{SUPIVID='||to_char(r_sd.supplier_ivid) ||'}' ); else add_so( r_sd.global_name, r_sd.supplier_ivid, r_sd.client_ivid); end if; -- instr(l_name,'.') > 0 else htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||l_name , p_node_level => p_node_level + 2 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=U}{SUPIVID='||to_char(r_sd.supplier_ivid) ||'}' ); if r_sd.type_label = 'RELATION_DEFINITION' then -- add columns for i in l_col_ctr..l_col_tbl.count loop if l_col_tbl(i).tbl_name <> l_name then exit; end if; htmltree.add_node ( p_display_label => cdwp.add_images('{column.gif}') ||cdwpbase.nbsp(1) ||l_col_tbl(i).col_name , p_node_level => p_node_level + 3 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=U}{SUPIVID='||to_char(l_col_tbl(i).supplier_ivid) ||'}' ); l_col_tbl(i).tbl_name:=null; l_col_ctr:= l_col_ctr + 1; end loop; end if; --r_sd.type_label = 'RELATION_DEFINITION' end if; -- r_sd.type_label ='PL/SQL Object' end if; -- not l_new_type and l_name = l_last_name end if; -- r_sd.type_label = 'COLUMN' end loop; -- r_sd -- see if any columns are left l_first:= true; for i in l_col_ctr..l_col_tbl.count loop if l_col_tbl(i).tbl_name is not null -- it has not yet been displayed then if l_first then l_first := false; htmltree.add_node ( p_display_label => cdwp.add_images('{column.gif}') ||cdwpbase.nbsp(1) ||Rob_msg.GetMsg(Rob_msg.CAP109_ODWA_ADDLCOLS,'','','','') , p_node_level => p_node_level + 1 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; htmltree.add_node ( p_display_label => cdwp.add_images('{column.gif}') ||cdwpbase.nbsp(1) ||l_col_tbl(i).tbl_name ||'.' ||l_col_tbl(i).col_name , p_node_level => p_node_level + 2 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=U}{SUPIVID='||to_char(l_col_tbl(i).supplier_ivid) ||'}' ); end if; -- l_col_tbl(i).tbl_name is not null end loop; end; -- pac_usages_nodes -- this procedure writes in the Nodes Tree all nodes for the used by that have been identified -- for object p_ivid procedure used_by_nodes ( p_ivid in number , p_node_level in number ) is cursor c_ub ( b_ivid in number ) is -- find all distinct PACs that have (themselves or through their details) a dependency -- on PAC b_ivid or its details select /*+ INDEX(nm SDD_GNS_PK) INDEX(et */ distinct nvl( et.name, et3.name) type_label , nvl( et.id, et3.id) logical_type_id , nvl( ov.irid, ov2.irid) pac_irid , nvl( ov.ivid, ov2.ivid) pac_ivid -- client: user of b_nm_ivid , nvl( ov.sequence_in_branch, ov2.sequence_in_branch) seq_in_branch -- client: user of b_nm_ivid , jr_name.get_crn_from_irid( nvl( ov.irid, ov2.irid),'NAME',0) global_name , jr_name.get_path( jr_acc_rights.get_owning_container(nvl( ov.irid, ov2.irid)), 'NAME','/',0) global_path from i$sdd_object_versions ov , i$sdd_object_versions ov2 , sdd_dependencies d -- Global Names provided by b_ivid , sdd_dependencies d2 -- Dependencies on Global Names provided by b_ivid , rm_element_types et , rm_element_types et3 where b_ivid = d.supplier_ivid and d.client_ivid = d2.supplier_ivid -- Global Name = Supplier for Dependency and d2.client_ivid = ov.ivid(+) and d2.client_parent_ivid = ov2.ivid(+) and ov.logical_type_id = et.id(+) and et3.irid(+) = ov2.logical_type_id UNION select /*+ INDEX(nm SDD_GNS_PK) INDEX(et */ distinct nvl( et.name, et3.name) type_label , nvl( et.id, et3.id) logical_type_id , nvl( ov.irid, ov2.irid) pac_irid , nvl( ov.ivid, ov2.ivid) pac_ivid -- client: user of b_nm_ivid , nvl( ov.sequence_in_branch, ov2.sequence_in_branch) seq_in_branch -- client: user of b_nm_ivid , jr_name.get_crn_from_irid( nvl( ov.irid, ov2.irid),'NAME',0) global_name , jr_name.get_path( jr_acc_rights.get_owning_container(nvl( ov.irid, ov2.irid)), 'NAME','/',0) global_path from i$sdd_object_versions ov , i$sdd_object_versions ov2 , sdd_dependencies d -- Global Names provided by b_ivid , sdd_dependencies d2 -- Dependencies on Global Names provided by b_ivid , rm_element_types et , rm_element_types et3 where b_ivid = d.supplier_parent_ivid and d.client_ivid = d2.supplier_ivid -- Global Name = Supplier for Dependency and d2.client_ivid = ov.ivid(+) and d2.client_parent_ivid = ov2.ivid(+) and ov.logical_type_id = et.id(+) and et3.irid(+) = ov2.logical_type_id order by type_label , global_path , global_name , seq_in_branch ; l_last_type_label varchar2(200):='XX'; l_last_irid number(38):=0; l_name varchar2(200); l_icon varchar2(400); l_pac_ivid number(38); begin for r_ub in c_ub( b_ivid => p_ivid) loop if r_ub.type_label <> l_last_type_label then l_last_type_label := r_ub.type_label; l_icon:= cdwp.add_images('{'||cdwpbase.get_icon(p_type_id => r_ub.logical_type_id)||'}'); htmltree.add_node ( p_display_label => l_icon ||cdwpbase.nbsp(1) ||initcap(replace(r_ub.type_label,'_',' ')) , p_node_level => p_node_level + 1 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; l_name := r_ub.global_path ||'/' ||l_icon ||cdwpbase.nbsp(1) ||r_ub.global_name ; if l_last_irid <> r_ub.pac_irid then htmltree.add_node ( p_display_label => l_icon ||cdwpbase.nbsp(1) ||l_name , p_node_level => p_node_level + 2 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); l_last_irid:= r_ub.pac_irid; end if; htmltree.add_node ( p_display_label => l_icon ||cdwpbase.nbsp(1) ||l_name ||' ('||odwavrsn.get_version_label(p_ivid => r_ub.pac_ivid)||')' , p_node_level => p_node_level + 3 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => true , p_classification => '{TYPE=UBV}{CLIIVID='||to_char(r_ub.pac_ivid)||'}' ); end loop; -- r_ub end; -- used_by_nodes -- this procedure writes in the Nodes Tree all nodes for the used by that have been identified -- for object p_ivid procedure used_by_vrsn_nodes ( p_ivid in number , p_node_level in number , p_irid in number ) is begin null; end; -- used_by_vrsn_nodes procedure used_by_detail_nodes ( p_ivid in number , p_node_level in number , p_client_ivid in number -- The PAC who (or whose details) has a depenency on p_ivid (and its details) ) is cursor c_ub ( b_ivid in number , b_client_ivid in number ) is -- usages of the PAC b_ivid by the PAC b_client_ivid (and its details) -- collect all details of b_client_ivid that have one or more dependencies on PAC b_ivid and its details select /*+ INDEX(nm SDD_GNS_PK) INDEX(et */ distinct nvl( nmd.name, et.name) type_label , nm_cli.name global_name , et.id logical_type_id , d2.client_ivid from sdd_dependencies d -- Global Names provided by b_ivid , sdd_dependencies d2 -- Dependencies on Global Names provided by b_ivid , sdd_dependencies d3 -- Global Names provided by client of Dependency , sdd_global_names nm_cli , rm_element_types et , sdd_global_name_domains nmd where b_ivid = d.supplier_ivid and d.client_ivid = d2.supplier_ivid -- Global Name = Supplier for Dependency and d2.client_ivid = b_client_ivid -- PAC is client of dependency and d2.client_ivid = d3.supplier_ivid and d3.client_ivid = nm_cli.ivid and nm_cli.type_id = et.id(+) and nm_cli.domain_irid = nmd.irid(+) UNION select /*+ INDEX(nm SDD_GNS_PK) INDEX(et */ distinct nvl( nmd.name, et.name) type_label , nm_cli.name global_name , et.id logical_type_id , d2.client_ivid from sdd_dependencies d -- Global Names provided by b_ivid , sdd_dependencies d2 -- Dependencies on Global Names provided by b_ivid , sdd_dependencies d3 -- Global Names provided by client of Dependency , sdd_global_names nm_cli , rm_element_types et , sdd_global_name_domains nmd where b_ivid = d.supplier_ivid and d.client_ivid = d2.supplier_ivid -- Global Name = Supplier for Dependency and d2.client_parent_ivid = b_client_ivid -- PAC's Detail is client of dependency and d2.client_ivid = d3.supplier_ivid and d3.client_ivid = nm_cli.ivid and nm_cli.type_id = et.id(+) and nm_cli.domain_irid = nmd.irid(+) order by type_label , global_name ; l_last_type_label varchar2(500):='XX'; l_name varchar2(200); l_icon varchar2(500); begin /* htmltree.add_node ( p_display_label => 'client ivid '||to_char(p_client_ivid)||' supplier ivid '||to_char(p_ivid) , p_node_level => p_node_level + 1 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '' ); */ for r_ub in c_ub( b_ivid => p_ivid, b_client_ivid => p_client_ivid) loop if r_ub.type_label <> l_last_type_label then l_last_type_label := r_ub.type_label; if r_ub.logical_type_id is null then l_icon:= cdwp.add_images('{global_name.gif}'); else l_icon:= cdwp.add_images('{'||cdwpbase.get_icon(p_type_id => r_ub.logical_type_id)||'}'); end if; htmltree.add_node ( p_display_label => l_icon ||cdwpbase.nbsp(1) ||initcap(replace(r_ub.type_label,'_',' ')) , p_node_level => p_node_level + 1 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; l_name := r_ub.global_name; htmltree.add_node ( p_display_label => l_icon ||cdwpbase.nbsp(1) ||l_name , p_node_level => p_node_level + 2 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=UB}' ||'{CLIIVID='||to_char(r_ub.client_ivid)||'}' ); end loop; -- r_ub end; -- used_by_detail_nodes -- this procedure writes in the Nodes Tree all nodes for the used by that have been identified -- for object with Global Name IVID p_nm_ivid -- improvement: -- * strip out used by the (PAC) object itself procedure det_used_by_nodes ( p_nm_ivid in number , p_node_level in number ) is -- find all distinct PACs that have (themselves or through their details) a dependency on b_nm_ivid cursor c_ub ( b_nm_ivid in number ) is select /*+ INDEX(nm SDD_GNS_PK) INDEX(et */ distinct nvl( et.name, et3.name) type_label , nvl( et.id, et3.id) logical_type_id , nvl( ov.irid, ov2.irid) pac_irid , nvl( ov.ivid, ov2.ivid) pac_ivid -- client: user of b_nm_ivid , nvl( ov.sequence_in_branch, ov2.sequence_in_branch) seq_in_branch -- client: user of b_nm_ivid , jr_name.get_crn_from_irid( nvl( ov.irid, ov2.irid),'NAME',0) global_name , jr_name.get_path( jr_acc_rights.get_owning_container(nvl( ov.irid, ov2.irid)), 'NAME','/',0) global_path from i$sdd_object_versions ov , i$sdd_object_versions ov2 , sdd_dependencies d , rm_element_types et , rm_element_types et3 where b_nm_ivid = d.supplier_ivid and ov.logical_type_id = et.id(+) and d.client_ivid = ov.ivid(+) and d.client_parent_ivid = ov2.ivid(+) and et3.irid(+) = ov2.logical_type_id order by type_label , global_path , global_name , seq_in_branch ; l_last_irid number(38):=0; l_last_type_label varchar2(200):='XX'; l_name varchar2(200); l_pac_irid number(38); begin for r_ub in c_ub( b_nm_ivid => p_nm_ivid) loop if r_ub.type_label <> l_last_type_label then l_last_type_label := r_ub.type_label; htmltree.add_node ( p_display_label => cdwp.add_images('{'||cdwpbase.get_icon(p_type_id => r_ub.logical_type_id)||'}') ||cdwpbase.nbsp(1) ||initcap(replace(r_ub.type_label,'_',' ')) , p_node_level => p_node_level + 1 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; l_pac_irid:= r_ub.pac_irid; l_name := r_ub.global_path ||'/' ||cdwp.add_images('{'||cdwpbase.get_icon(p_type_id => r_ub.logical_type_id)||'}') ||cdwpbase.nbsp(1) ||r_ub.global_name ; if l_pac_irid <> l_last_irid then htmltree.add_node ( p_display_label => l_name , p_node_level => p_node_level + 2 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=DUB}{IRID=' || to_char(l_pac_irid) ||'}{NM_IVID='||to_char(p_nm_ivid)||'}nolink' ); l_last_irid := l_pac_irid; end if; -- l_pac_irid <> l_last_irid htmltree.add_node ( p_display_label => l_name||' ('||odwavrsn.get_version_label(p_ivid => r_ub.pac_ivid)||')' , p_node_level => p_node_level + 3 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => true , p_classification => '{TYPE=DUBP}{CLIIVID=' || to_char(r_ub.pac_ivid) ||'}{NM_IVID='||to_char(p_nm_ivid)||'}' ); end loop; -- r_ub end; -- det_used_by_nodes -- this procedure writes in the Nodes Tree nodes for all versions of p_irid for the used by that have been identified -- for object with Global Name IVID p_nm_ivid procedure det_used_by_vrsn_nodes ( p_nm_ivid in number , p_node_level in number , p_cli_pac_ivid in number -- PAC who (or whose details) is using the detail p_nm_ivid ) is -- for all usages made of b_nm_ivid by p_cli_pac_ivid and its details -- find all these details cursor c_ub ( b_nm_ivid in number , b_cli_pac_ivid in number ) is -- usages directly from b_cli_pac_ivid select /*+ INDEX(nm SDD_GNS_PK) INDEX(et */ distinct nvl( nmd.name, et2.name) type_label , nm_cli.name global_name , et2.id logical_type_id , d2.client_ivid from sdd_dependencies d2 -- Usage of b_nm_ivid by Client , sdd_dependencies d3 -- Global Name supplied by Client , rm_element_types et2 , sdd_global_names nm_cli , sdd_global_name_domains nmd where d2.supplier_ivid = b_nm_ivid and d2.client_ivid = b_cli_pac_ivid and d2.client_ivid = d3.supplier_ivid and d3.client_ivid = nm_cli.ivid and nm_cli.type_id = et2.id(+) and nm_cli.domain_irid = nmd.irid(+) UNION -- usages from details of b_cli_pac_ivid select /*+ INDEX(nm SDD_GNS_PK) INDEX(et */ distinct nvl( nmd.name, et2.name) type_label , nm_cli.name global_name , et2.id logical_type_id , d2.client_ivid from sdd_dependencies d2 -- Usage of b_nm_ivid by Client , sdd_dependencies d3 -- Global Name supplied by Client , rm_element_types et2 , sdd_global_names nm_cli , sdd_global_name_domains nmd where d2.supplier_ivid = b_nm_ivid and d2.client_parent_ivid = b_cli_pac_ivid and d2.client_ivid = d3.supplier_ivid and d3.client_ivid = nm_cli.ivid and nm_cli.type_id = et2.id(+) and nm_cli.domain_irid = nmd.irid(+) order by type_label , global_name ; l_name varchar2(200); l_last_type_label varchar2(500):='XX'; begin for r_ub in c_ub( b_nm_ivid => p_nm_ivid, b_cli_pac_ivid => p_cli_pac_ivid) loop if r_ub.type_label <> l_last_type_label then l_last_type_label:= r_ub.type_label; htmltree.add_node ( p_display_label => cdwpbase.ifThenElse ( r_ub.logical_type_id is not null , cdwp.add_images('{'||cdwpbase.get_icon(p_type_id => r_ub.logical_type_id)||'}') , cdwp.add_images('{global_name.gif}') ) ||cdwpbase.nbsp(1) ||r_ub.type_label , p_node_level => p_node_level + 1 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; htmltree.add_node ( p_display_label => cdwpbase.ifThenElse ( r_ub.logical_type_id is not null , cdwp.add_images('{'||cdwpbase.get_icon(p_type_id => r_ub.logical_type_id)||'}') , cdwp.add_images('{global_name.gif}') ) ||cdwpbase.nbsp(1) ||r_ub.global_name , p_node_level => p_node_level + 2 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=DUB}{CLIIVID='||to_char(r_ub.client_ivid)||'}{NM_IVID='||to_char(p_nm_ivid)||'}' ); end loop; -- r_ub end; -- det_used_by_vrsn_nodes procedure unparsed ( p_wa_irid in number default null , p_cfg_ivid in number default null , p_fol_ivid in number default null , p_node_level in number ) is cursor c_unp ( b_wa_irid in number , b_cfg_ivid in number ) is select ov.ivid ov_ivid , jr_name.get_path(ov.ivid,'NAME','/',0) path , et.name type_label , ov.name ov_name , et.irid type_id from i$sdd_object_versions ov , i$sdd_wa_context wac , rm_element_types et where ov.ivid = wac.object_ivid and wac.workarea_irid = b_wa_irid and nvl(ov.obj_notm,-1) <> nvl(ov.obj_notm_when_analyzed ,-2) and et.irid = ov.logical_type_id UNION select ov.ivid ov_ivid , jr_name.get_path(ov.ivid,'NAME','/',0) path , et.name type_label , ov.name ov_name , et.irid type_id from i$sdd_object_versions ov , i$sdd_configuration_members cm , rm_element_types et where cm.object_ivid = ov.ivid and cm.config_ivid = b_cfg_ivid and nvl(ov.obj_notm,-1) <> nvl(ov.obj_notm_when_analyzed ,-2) and et.irid = ov.logical_type_id order by path, type_label, ov_name ; l_last_type_label varchar2(200):='XX'; l_filter_path varchar2(2000):= jr_name.get_path(p_fol_ivid,'NAME','/',0)||'/%'; l_last_path varchar2(2000):=l_filter_path; l_path varchar2(2000); l_path_level number(4):=0; l_ref_level number(3); l_node_level number(3):=0; l_last_node_level number(3); function folder_level ( p_path in varchar2 ) return number is begin return length(p_path) - length( replace(p_path,'/','')); end; -- folder_level function partial_path ( p_path in varchar2 , p_level in number ) return varchar2 is begin return substr( p_path, 1, instr(p_path, '/', 1 , p_level)-1); end; -- partial_path begin -- add nodes for the external dependencies of all objects in the collection bounded by -- the Workarea or Configuration and (optionally) the Folder (and its child folder structure) jr_context.set_workarea( to_number(null)); if p_fol_ivid is not null then l_ref_level:= folder_level(l_filter_path); else l_ref_level:= 0; end if; -- p_fol_ivid is not null for r_unp in c_unp ( b_wa_irid => p_wa_irid , b_cfg_ivid => p_cfg_ivid ) loop l_path:= jr_name.get_path(r_unp.ov_ivid,'NAME','/',0); if p_fol_ivid is null or l_path like l_filter_path then l_node_level:= p_node_level + folder_level(l_path) - l_ref_level; -- l_node_level - l_last_node_level should not be > 1 -- if it is, the steps in between must be filled if l_node_level - l_last_node_level > 1 then for i in 1..(l_node_level - l_last_node_level-1) loop htmltree.add_node ( p_display_label => cdwp.add_images('{folder.gif}') ||cdwpbase.nbsp(1) ||partial_path( l_path, l_last_node_level + i) , p_node_level => l_last_node_level + i , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end loop; end if; htmltree.add_node ( p_display_label => cdwp.add_images('{'||cdwpbase.get_icon(p_type_id => r_unp.type_id)||'}') ||cdwpbase.nbsp(1)||r_unp.path||' ('||r_unp.type_label||')' , p_node_level => p_node_level + 1 + folder_level(l_path) - l_ref_level , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; -- p_fol_ivid or jr_name.get_path(r_edw.ov_ivid,'NAME','/',0) like l_path end loop; -- r_unp end; -- unparsed procedure ext_deps ( p_wa_irid in number default null , p_cfg_ivid in number default null , p_fol_ivid in number default null , p_node_level in number ) is l_command varchar2(10000); l_sa_tbl t_sa_tbl; l_col_tbl t_col_tbl; l_col_ctr number(5):= 1; cursor c_sd is select nm.name global_name , nmd.name global_name_domain_name , et.name element_type_name , nm.ivid nm_ivid , nvl( nmd.name, et.name) type_label from odwa_provided pvd , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et where nm.irid = pvd.nm_irid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) order by nvl( nmd.name, et.name) , nm.name ; cursor c_edw ( b_wa_irid in number , b_cfg_ivid in number ) is select d.supplier_irid , nm.name global_name , ov.ivid ov_ivid , to_char(d.supplier_irid) -- refers to OBJECT_VERSIONS or , d.supplier_ivid , nmd.name global_name_domain_name , et.name element_type_name , decode ( instr(nvl( nmd.name, et.name),'PLSQL') , 0, nvl( nmd.name, et.name) , 'PL/SQL Object' ) type_label from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et , i$sdd_object_versions ov , i$sdd_wa_context wac where nm.irid = d.supplier_irid and ov.ivid = wac.object_ivid and wac.workarea_irid = b_wa_irid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and ov.ivid = d.client_ivid and not exists ( select 'x' from odwa_provided pvd where pvd.nm_irid = nm.irid) UNION select d.supplier_irid , nm.name global_name , ov.ivid ov_ivid , to_char(d.supplier_irid) -- refers to OBJECT_VERSIONS or , d.supplier_ivid , nmd.name global_name_domain_name , et.name element_type_name , decode ( instr(nvl( nmd.name, et.name),'PLSQL') , 0, nvl( nmd.name, et.name) , 'PL/SQL Object' ) type_label from sdd_dependencies d , sdd_global_names nm , sdd_global_name_domains nmd , rm_element_types et , i$sdd_object_versions ov , i$sdd_configuration_members cm where cm.object_ivid = ov.ivid and cm.config_ivid = b_cfg_ivid and nm.irid = d.supplier_irid and nm.domain_irid = nmd.irid(+) and nm.type_id = et.id(+) and ov.ivid = d.client_ivid and not exists ( select 'x' from odwa_provided pvd where pvd.nm_irid = nm.irid) order by type_label , global_name ; l_last_type_label varchar2(200):='XX'; l_name varchar2(200); l_last_name varchar2(200); l_new_type boolean; l_plsql_unit varchar2(200):='XX'; l_path varchar2(2000):= jr_name.get_path(p_fol_ivid,'NAME','/',0)||'/%'; procedure add_column ( p_name in varchar2 , p_sup_ivid in number ) is l_found boolean := false; begin -- first check if column already exists for i in 1..l_col_tbl.count loop if l_col_tbl( i).supplier_ivid = p_sup_ivid then l_found:= true; exit; end if; end loop; if not l_found then l_col_tbl( l_col_tbl.count + 1).tbl_name:= substr( p_name, 1, instr( p_name, '.')-1); l_col_tbl( l_col_tbl.count).col_name:= substr( p_name, instr( p_name, '.')+1); l_col_tbl( l_col_tbl.count).supplier_ivid:= p_sup_ivid; end if; end; -- add_column procedure add_so ( p_name in varchar2 , p_sup_ivid in number ) is begin l_sa_tbl( l_sa_tbl.count + 1).name:= p_name; l_sa_tbl( l_sa_tbl.count).supplier_ivid:= p_sup_ivid; end; -- add_so begin -- add nodes for the external dependencies of all objects in the collection bounded by -- the Workarea or Configuration and (optionally) the Folder (and its child folder structure) /* debug htmltree.add_node ( p_display_label => 'WA='||to_char(p_wa_irid)||'CFG='||to_char(p_cfg_ivid)||'FOL='||to_char(p_fol_ivid) , p_node_level => p_node_level + 1 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); */ jr_context.set_workarea( to_number(null)); l_command:= ' insert into odwa_provided ( nm_irid) select distinct '; if p_wa_irid is not null then l_command:= l_command|| ' d.client_irid from sdd_dependencies d , i$sdd_wa_context wac where wac.object_ivid = d.supplier_ivid and wac.workarea_irid = '||to_char(p_wa_irid) ; if p_fol_ivid is not null then l_command:= l_command|| ' and jr_name.get_path(wac.object_ivid,''NAME'',''/'',0) like ''' ||l_path||''' ' ; end if; -- p_fol_ivid is not null else -- p_cfg_ivid is not null l_command:= l_command|| ' d.client_irid from sdd_dependencies d , i$sdd_configuration_members cm where cm.object_ivid = d.supplier_ivid and cm.config_ivid = '||to_char(p_cfg_ivid) ; if p_fol_ivid is not null then l_command:= l_command|| ' and jr_name.get_path(cm.object_ivid,''NAME'',''/'',0) like ''' ||l_path||''' ' ; end if; -- p_fol_ivid is not null end if; -- p_wa_irid is not null execute immediate l_command; -- now the table odwa_provided contains all Global Names provided/supplied by -- all (parsed) object versions in the chosen context /* for r_sd in c_sd loop if r_sd.type_label <> l_last_type_label then l_last_type_label := r_sd.type_label; htmltree.add_node ( p_display_label => cdwp.add_images('{global_name_domain.gif}') ||cdwpbase.nbsp(1)||r_sd.type_label , p_node_level => p_node_level + 1 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; l_name := r_sd.global_name; if r_sd.type_label ='PLSQL_MODULE_SUBROUTINE' then l_name:= substr(l_name, instr(l_name,'.')+ 1); end if; htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||l_name , p_node_level => p_node_level + 2 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); if exist_used_by ( p_nm_ivid => r_sd.nm_ivid) then htmltree.add_node ( p_display_label => cdwp.add_images('{usedby.gif}') ||cdwpbase.nbsp(1)||'Used By' , p_node_level => p_node_level + 3 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => true , p_classification => '{TYPE=DEPS:DETUSEDBY}{NM_IVID='||to_char(r_sd.nm_ivid)||'}nolink' ); end if; -- exist_used_by end loop; -- r_sd */ -- We can now select all Global Names referenced by the object versions in the -- chosen context that do not occur in odwa_provided; these are the external references for r_edw in c_edw ( b_wa_irid => p_wa_irid , b_cfg_ivid => p_cfg_ivid ) loop if p_fol_ivid is null or jr_name.get_path(r_edw.ov_ivid,'NAME','/',0) like l_path then if r_edw.type_label <> 'PL/SQL Object' and l_last_type_label = 'PL/SQL Object' and l_sa_tbl.count > 0 then -- time to display the Stand Alone PL/SQL Objects htmltree.add_node ( p_display_label => cdwp.add_images('{global_name_domain.gif}') ||cdwpbase.nbsp(1)||Rob_msg.GetMsg(Rob_msg.CAP108_ODWADEPS_SAPLSO,'','','','') , p_node_level => p_node_level + 2 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); for i in 1..l_sa_tbl.count loop htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||l_sa_tbl(i).name , p_node_level => p_node_level + 3 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => '{TYPE=U}{SUPIVID='||to_char(l_sa_tbl(i).supplier_ivid)||'}' ); end loop; end if; -- r_edw.type_label <> 'PL/SQL Object' and l_last_type_label = 'PL/SQL Object' if r_edw.type_label = 'COLUMNS' then add_column( r_edw.global_name, r_edw.supplier_ivid); else if r_edw.type_label <> l_last_type_label then l_last_type_label := r_edw.type_label; htmltree.add_node ( p_display_label => cdwp.add_images('{global_name_domain.gif}') ||cdwpbase.nbsp(1)||replace(initcap(r_edw.type_label),'_',' ') , p_node_level => p_node_level + 1 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); l_new_type:= true; else l_new_type:= false; end if; l_last_name:= l_name; l_name := r_edw.global_name; if not l_new_type and l_name = l_last_name then -- twice the same names of the same type: skip the second one null; else -- now if r_edw.type_label ='PL/SQL Object' -- and -- l_name contains a . (odwaprop.palette_link) -- the supplier is likely to be part of a package or greater unit whose name -- is substr (l_name, 1, instr(l_name,'.')-1) -- if the unit name is not l_plsql_unit, then add a node for the plsql unit -- then add nodes at a lower level for the current supplier, called -- substr (l_name, instr(l_name,'.')+1) if r_edw.type_label ='PL/SQL Object' or r_edw.type_label ='COLUMN' then if instr(l_name,'.') > 0 then if l_plsql_unit <> substr (l_name, 1, instr(l_name,'.')-1) then l_plsql_unit := substr (l_name, 1, instr(l_name,'.')-1); htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||l_plsql_unit , p_node_level => p_node_level + 2 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); end if; -- l_plsql_unit <> substr (l_name, 1, instr(l_name,'.')-1) htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||substr (l_name, instr(l_name,'.')+1) , p_node_level => p_node_level + 3 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); else add_so( r_edw.global_name, r_edw.supplier_ivid); end if; -- instr(l_name,'.') > 0 else htmltree.add_node ( p_display_label => cdwp.add_images('{global_name.gif}') ||cdwpbase.nbsp(1) ||l_name , p_node_level => p_node_level + 2 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); if r_edw.type_label = 'RELATION_DEFINITION' then -- add columns for i in l_col_ctr..l_col_tbl.count loop if l_col_tbl(i).tbl_name <> l_name then exit; end if; htmltree.add_node ( p_display_label => cdwp.add_images('{column.gif}') ||cdwpbase.nbsp(1) ||l_col_tbl(i).col_name , p_node_level => p_node_level + 3 , p_has_children => false , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => false , p_classification => 'nolink' ); l_col_ctr:= l_col_ctr + 1; end loop; end if; --r_edw.type_label = 'RELATION_DEFINITION' end if; -- r_edw.type_label ='PL/SQL Object' end if; -- not l_new_type and l_name = l_last_name end if; -- r_edw.type_label = 'COLUMN' end if; -- p_fol_ivid is null or end loop; -- r_edw delete odwa_provided; htmltree.add_node ( p_display_label => cdwp.add_images('{warning.gif}') ||cdwpbase.nbsp(1)||Rob_msg.GetMsg(Rob_msg.CAP110_ODWADEPS_UPOBJ,'','','','') , p_node_level => p_node_level + 1 , p_has_children => true , p_is_expanded => false , p_value => '' , p_additional_label => '' --l_link , p_is_inflatable => true , p_classification => '{TYPE=UNPARSED}{WA_IRID='||to_char(p_wa_irid) ||'}{IVID='||to_char(p_fol_ivid) ||'}nolink' ); end; -- ext_deps end; -- odwadeps /