create or replace package body cdwp_tbl is /***************************************************************************************** Purpose HTML Report of Table Definitions for ODWA Usage Remarks Revision history When Who Construct Revision What ------------------------------------------------------------------------------------------ $REVISION_HISTORY$ 10-Dec-2002 Kannan Parthasarathy - B2701851 : Fixed 07-feb-2001 Lucas Jellema 3.3 - show Table Entity Usages 22-sep-2000 Lucas Jellema 3.2 Correct installation problems indicated by Jan Janssen with regard to Alpha 1 Release (6.5.0.1) 12-sep-2000 Lucas Jellema 3.1 Use parent_ivid in Application Logic routine Tune queries, version resolved, use +RULE hints 26-jul-2000 Lucas Jellema 3.0 - all queries for SACs, Associations, App Logic and Multi Line text look for the Parent_Ivid - no more set_base_url - no parameters in any of the SAC procedures (except where really necessary) - no more use of p_static, p_one_file, p_app_name, p_app_version - exists_trg: use p_tbl_ivid as parameter - use cdwpbase.get_ivid_list - c_tbl only p_ivid as parameter - replace short_list_table_definitions with call to odwarprt.short_list_of_type_instances - make use of odwarprt.show_anchor - make vwtab, tabent public 24-jul-2000 Lucas Jellema 2.8 - update specification of procedure short_list_table_definitions and list_table_definitions - update context right after entering the external procedures - use: jr_acc_rights.get_owning_container(obj_irid) to find the owning container of any object - use CDWP.tableOpen and cdwp.tableClose - use cdwp.tabeRowOpen and cdwp.tableRowClose - call cdwp.include_report_styles - all queries to get references should NOT join (or OUTER join) with other PACs/SACs in other PACs: these elements could be outside the Workarea; a join would result in no element found - cursor c_brdd: outer join with PLM, in case PLM is outside Workarea - remove references to p_app_id, p_app_version, p_static and p_one_file in all procedures and calls; refer to odwactxt if values are really required; gradually remove these parameters from all procedure specifications - allow working outside WA context, i.e. directly to an IVID - for all layouting (font face, font size, font family etc. use a predefined style through a call to cdwp.p - in calls to report_link: pass element type - use odaxist instead of cdwp.exists_detail - no hard coded element_type-GIF names (always call CDWPBASE.get_icon 30-jun-2000 Lucas Jellema procedure list_app_logic, specification 2.7 cater for the fact that this procedure is used by Modules as well as tables 28-jun-2000 Lucas Jellema procedure list_table_definitions 2.6 report_link call needed parameter p_el_id 20-jun-2000 Lucas Jellema procedure print_oky 2.5 l_oky_col wider (15000 instead of 2000) to cater for large number of columns in FCO added space in OKY listing to allow break off in column added space in INDCOL listing to allow break off in column 19-jun-2000 Lucas Jellema procedure summary_modtab_definitions 2.4 reference to Reusable MCO implemented 08-jun-2000 Lucas Jellema list_TBL_definitions and many others 2.3 add summary_column_definitions to specification 07-jun-2000 Lucas Jellema list_TBL_definitions and many others 2.2 + 1 (S) - Comment column in check constraint is not necessary 2 (S) - Column Summary does not include optionality attribute 3 (S) - Foreign Key summary does not include optionality attribute 4 (M) - Share problems :Used by views 5 (M) - Share problems: Module Table Usage 6 (S) - Share problems: PL'SQL Module table usage 7 (M) - The referenced column in the foreign key constraint (summary and details) links to the wrong (current) table 8 (M) - Indication of primary key, mandatory, optional in column summary, use symbols #,*,o or gifs 9 (S) - Take domain and default out of column summary and replace it with hinttekst. 29-may-2000 Lucas Jellema list_TBL_definitions 2.1 + write menu_bar (status bar) for one_file = 'Y' 26-may-2000 Lucas Jellema function has_app_logic, cursor c_al, procedure list_app_logic 2.0 add support for application logic package specification 25-may-2000 Lucas Jellema list_table_definitions 1.9 call cdwpbase.app_summary 24-may-2000 Lucas Jellema short_list_table_definitions 1.8 use cdwpbase.set_base_url and local_link procedure summary_ind, print_indcol list_table_definitions support indexes and index-entries 23-may-2000 Lucas Jellema short_list_table_definitions 1.7 use cdwpbase.get_app_label to properly show application system for shared tables write_no_found added 23-may-2000 Sandra Muller summary_modtab_definitions 1.6 link to module did not work; changed 'mde' to 'gem' 22-may-2000 Sandra Muller several procedures, list_column_definitions 1.5 replaced cursor_var.app_name by p_app_name and cursorvar.app_version by p_appversion list_column_definitions display_all_mlt had wrong element type: TBL instead of COL 16-may-2000 Sandra Muller summary_modtab_definitions 1.4 Module usages: split off module component name to new column and included module short name in module name and made it a public procedure that can also be used in cdwp_vw package exists_brdd_definition, exists_trg new functions to decide if business rules / triggers link on top must be shown list_column_definitions Prompt 'Hint' instead of 'Description' (also in summary_column_definitions) + convert Y/N to check for uppercase, display_flag, server_derived, suggestion list and dynamic (+ show correct dynamic flag) + added optional property + only show domain / source attribute / denormalization prompt if there is one + repaired denormalized (From Column and Via Foreign Key) + added base column ref / made procedure public so it can be used for views list_column/pco/uco/fco/cco_definitions Multiline text starts in new table, better alignment 12-may-2000 Sandra Muller print_oky 1.3 removed last remaining outer join from c_oky: column reference is mandatory so no outer join needed 11-may-2000 Sandra Muller print_oky 1.2 eliminated full table scan: split c_oky in 2 cursors, so no outer join needed + made it public for use in view package (short_)list_table_definitions added write_about list_table_definitions removed heading bar at multi line text 09-may-2000 Sandra Muller list_table_definitions 1.1 Fixed alignment of transferable property 23-03-2000 Lucas Jellema Added support for incoming FK, VWTAB, AppLogic 06-03-2000 Lucas Jellema Added support for MODTAB/MODCOL,Business Rules 03-mar-2000 Lucas Jellema 1.0 Creation after the Table Doc Specification prepared for Solutron, Publisher/2000 2.8 - update specification of procedure short_list_table_definitions and list_table_definitions - update context right after entering the external procedures - use: jr_acc_rights.get_owning_container(obj_irid) to find the owning container of any object - use CDWP.tableOpen and cdwp.tableClose - use cdwp.tabeRowOpen and cdwp.tableRowClose - call cdwp.include_report_styles - all queries to get references should NOT join (or OUTER join) with other PACs/SACs in other PACs: these elements could be outside the Workarea; a join would result in no element found - cursor c_brdd: outer join with PLM, in case PLM is outside Workarea - remove references to p_app_id, p_app_version, p_static and p_one_file in all procedures and calls; refer to odwactxt if values are really required; gradually remove these parameters from all procedure specifications - allow working outside WA context, i.e. directly to an IVID - for all layouting (font face, font size, font family etc. use a predefined style through a call to cdwp.p - in calls to report_link: pass element type - use odaxist instead of cdwp.exists_detail - no hard coded element_type-GIF names (always call CDWPBASE.get_icon 3.0 - all queries for SACs, Associations, App Logic and Multi Line text look for the Parent_Ivid - no more set_base_url - no parameters in any of the SAC procedures (except where really necessary) - no more use of p_static, p_one_file, p_app_name, p_app_version 3.0 - exists_trg: use p_tbl_ivid as parameter - use cdwpbase.get_ivid_list - c_tbl only p_ivid as parameter - replace short_list_table_definitions with call to odwarprt.short_list_of_type_instances - make use of odwarprt.show_anchor *****************************************************************************************/ -- -- private constants -- REVISION_LABEL constant varchar2(30) := '$x.y::3.3 $'; PACKAGE_NAME constant varchar2(30) := 'CDWP_TBL'; cursor c_tbl ( b_tbl_ivid in number ) is select tbl.id tbl_id , tbl.irid tbl_irid , tbl.ivid tbl_ivid , tbl.name tbl_name , tbl.alias tbl_alias , tbl.CLUSTER_REFERENCE tbl_CLUSTER_REFERENCE , tbl.COLUMN_PREFIX tbl_COLUMN_PREFIX , tbl.DATAWAREHOUSE_TYPE tbl_DATAWAREHOUSE_TYPE , tbl.DISPLAY_TITLE tbl_DISPLAY_TITLE , tbl.INDEX_ONLY_FLAG tbl_INDEX_ONLY_FLAG , tbl.INITIAL_NUMBER_OF_ROWs tbl_INITIAL_NUMBER_OF_ROWS , tbl.journal_location tbl_JOURNAL_LOCATION , tbl.MAXIMUM_NUMBER_OF_ROWs tbl_MAXIMUM_NUMBER_OF_ROWS from ci_table_definitions tbl where tbl.ivid = b_tbl_ivid ; r_tbl c_tbl%rowtype; -- cursor to select the Business Rule Design Definitions of a table cursor c_brdd ( b_tbl_ivid in number ) is select /*+ RULE */ plm.CANDIDATE_FLAG , plm.COMPLETION_STATUS , plm.Irid id , plm.ivid ivid , plm.IMPLEMENTATION_NAME , plm.NAME , plm.PURPOSE , plm.SHORT_NAME , trg.id trg_id , trg.enabled_flag trg_enabled_flag , trg.COMPLETE_FLAG trg_complete_flag , trg.PURPOSE trg_purpose , trg.name trg_name from i$sdd_mod plm , ci_database_triggers trg where trg.parent_ivid = b_tbl_ivid -- 3.0 and trg.plsql_module_reference = plm.irid and plm.ivid = cdwpbase.get_best_ivid( plm.irid) -- this goes wrong if is split over multiple lines of cdi_text and exists (select 1 from cdi_text where parent_ivid = plm.ivid -- $x.y3.3 $ and txt_ref = plm.irid and txt_type = 'CDINOT' and txt_text like '%%' ) order by trg.name , plm.name ; cursor c_al ( b_el_id in number , b_el_ivid in number default odwactxt.get_pac_ivid , b_al_type in varchar2 default '%' ) is select alc.al_id , decode ( alc.al_type ,'N', 'NRA' , decode ( alc.al_gen_product_id , 1, 'EVA' , 7, 'API' , 5, 'EVA' ,'EVA' ) ) al_type , decode ( alc.al_type , 'N', alc.al_nr_routine_name , alc.al_evt_short_description ) al_name , al_evt_name , al_evt_seq_in_event , decode ( alc.al_type , 'N', alc.al_nr_routine_name , alc.al_evt_short_description||' ('||to_char(alc.al_evt_seq_in_event)||')' ) al_display_label from ck_application_logic alc where alc.parent_ivid = b_el_ivid and alc.al_repos_element = b_el_id and decode ( alc.al_type ,'N', 'NRA' , decode ( alc.al_gen_product_id , 1, 'EVA' , 7, 'API' , 5, 'EVA' ,'EVA' ) ) like b_al_type order by alc.al_evt_name , alc.al_evt_seq_in_event , al_name ; -- -- cursor to select properties of non business rule triggers of a table_definition -- cursor c_trg ( b_tbl_id in number , b_tbl_ivid in number default null ) is select /*+ RULE */ trg.id id , trg.COMPLETE_FLAG , trg.ENABLED_FLAG , trg.EXECUTE_TIME , trg.FIRE_WHEN_PROPAGATED_FLAG , trg.NEW_ALIAS , trg.OLD_ALIAS , trg.PURPOSE , trg.TRIGGER_LEVEL , trg.TRIGGER_ON_DELETE , trg.TRIGGER_ON_INSERT , trg.TRIGGER_ON_UPDATE , trg.name name , trg.plsql_module_reference plm_id , plm.ivid plm_ivid , plm.name plm_name , plm.short_name plm_short_name from i$sdd_mod plm , ci_database_triggers trg where trg.parent_ivid = b_tbl_ivid -- $x.y3.3 $ reference ivids for SACs table_definition_reference = b_tbl_id and trg.plsql_module_reference = plm.irid and plm.ivid = cdwpbase.get_best_ivid( plm.irid) -- this goes wrong if is split over multiple lines of cdi_text and not exists (select 1 from cdi_text where parent_ivid = plm.ivid -- $x.y3.3 $ reference ivids for SACs and MLT and txt_ref = plm.irid and txt_type = 'CDINOT' and txt_text like '%%' ) order by trg.name ; procedure short_list_table_definitions ( p_session_id in number ) is begin odwactxt.update_context ( p_session_id => p_session_id , p_package_name => PACKAGE_NAME , p_procedure_name=> 'short_list_table_definitions' ); odwarprt.short_list_of_type_instances ( p_session_id => p_session_id , p_el_type_of => 'TBL' , p_app_irid => odwactxt.get_folder_irid , p_view_name => 'CI_TABLE_DEFINITIONS' , p_plural_name => 'Table Definitions' , p_order_by => 'NAME' , p_descriptor_column => 'NAME' ); end; -- short_list_table_definitions function exists_brdd_definition ( p_tbl_ivid in number ) return boolean is r_brdd c_brdd%rowtype; begin open c_brdd( b_tbl_ivid => p_tbl_ivid); fetch c_brdd into r_brdd; if c_brdd%found then close c_brdd; return true; else close c_brdd; return false; end if; -- c_brdd%found end exists_brdd_definition; procedure summary_brdd_definitions is l_first boolean; l_tbl_ivid number(38):= odwactxt.get_pac_ivid; begin cdwp.bookmark('BRDD'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK cdwp.tableOpen('BORDER="0"'); -- l_first := true; for r_brdd in c_brdd(l_tbl_ivid) loop if l_first then cdwp.tableRowOpen; cdwp.TableDataHeading ( cdwp.add_images('{brdd.gif}') ||ROB_msg.getMsg(Rob_msg.DSP309_CDWP_BRS, '', '', '', '') ); htp.print(''); cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0186_CDWP_PURPOSE, '', '', '', ''), 1); cdwp.tableRowClose; l_first := false; end if; cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.tableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue ( cdwp.report_link ( p_el_id => r_brdd.id , p_ivid => r_brdd.ivid , p_type_of => 'BRDD' , p_el_name => r_brdd.name ) , 1 ); cdwp.TableDataValue(r_brdd.trg_purpose, 1); cdwp.tableRowClose; cdwp.tableRowClose; end loop; -- r_brdd cdwp.tableClose; htp.print(''); cdwp.tableRowClose; end; -- summary_BRDD_definitions function exists_trg -- check if real database triggers exist, that is, not brdd triggers ( p_tbl_ivid in number -- 3.0 ) return boolean is r_trg c_trg%rowtype; begin open c_trg(p_tbl_ivid); fetch c_trg into r_trg; if c_trg%found then close c_trg; return true; else close c_trg; return false; end if; -- c_trg%found end exists_trg; procedure summary_modtab_definitions -- Purpose List the Module (Detail) Table Usages -- (together bookmarked as MODTAB) -- is -- -- cursor c_mti ( b_tbl_id in number ) is select /*+ rule */ mti.alias , mti.usage_type , mti.Irid , decode( mti.usage_type, 'BASE', mco.DELETE_FLAG,'N') delete_flag , decode( mti.usage_type, 'BASE', mco.insert_FLAG,'N') insert_flag , 'Y' select_flag , decode( mti.usage_type, 'BASE', mco.update_FLAG,'N') update_flag , mco.irid mco_id , mco.ivid mco_ivid , mco.name mco_name , mde.name mde_name -- make distinction between MTI in Reusable MCO (mde_name is null) -- and MTI in Module (mde_name is not null) , decode ( mde.name , null, ROB_msg.getMsg(Rob_msg.DSP310_CDWP_RMC, '', '', '', '') , mde.short_name||' - '||mde.name ) mde_display_name , mde.irid mde_id , mde.ivid mde_ivid from i$sdd_mod mde , i$sdd_mun mco , i$sdd_mti mti where mti.table_ref = b_tbl_id and mti.module_unit_ref = mco.irid -- Reusable Module Components do not have an owning General Module and mco.general_module_ref = mde.irid and mde.ivid = cdwpbase.get_best_ivid( mde.irid) and mco.parent_ivid = mde.ivid and mti.parent_ivid = mde.ivid order by mde.short_name, mco.name, mti.alias ; -- l_first boolean; begin cdwp.bookmark('MODTAB'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK -- Detailed Module Table Usages cdwp.tableOpen('BORDER="0"'); l_first := true; for r_mti in c_mti(odwactxt.get_pac_irid) loop if l_first then cdwp.tableRowOpen; cdwp.TableDataHeading ( cdwp.add_images('{'||cdwpbase.get_icon('GEM')||'}') ||ROB_msg.getMsg(Rob_msg.DSP311_CDWP_MTU, '', '', '', '') ); htp.print(''); cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0205_CDWP_MODULE, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0214_CDWP_MCTU, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0207_CDWP_USAGETYPE, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0206_CDWP_SELECT, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP035_CDWP_INSERT, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP013_CDWP_UPDATE, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP014_CDWP_DELETE, '', '', '', ''), 1); cdwp.TableRowClose; l_first:= false; end if; cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue -- perhaps better cater for Reusable Module Components using the table ( cdwp.report_link ( p_el_id => nvl( r_mti.mde_id, r_mti.mco_id) -- 2.4 , p_ivid => nvl( r_mti.mde_ivid, r_mti.mco_ivid) -- 2.4 , p_type_of => cdwpbase.ifThenElse ( r_mti.mde_id is not null , 'GEM' , 'MCO' ) , p_text => cdwpbase.ifThenElse ( r_mti.mde_id is not null -- a MTi in a MCO in MDE, not a Reusable MCO , '{gif}'||r_mti.mde_display_name , '{gif}{name}' ) ) ); cdwp.TableDataValue ( cdwpbase.ifThenElse ( r_mti.mde_id is not null -- a MTi in a MCO in MDE, not a Reusable MCO , cdwp.report_link ( p_el_id => r_mti.mde_id , p_ivid => r_mti.mde_ivid , p_text => cdwp.add_images ( '{forms_private_mco.gif}' -- may need to cater for Reusable MCO as well! ) ||r_mti.mco_name||'.'||r_mti.alias , p_bookmark => 'MCO'||to_char( r_mti.mco_id) ) , cdwp.report_link ( p_el_id => r_mti.mco_id , p_text => '{gif}' ||r_mti.mco_name||'.'||r_mti.alias ) ) , 1 ); --cdwp.TableDataValue(r_mti.mco_name||'.'||r_mti.alias, 1); -- might make this one into the hyperlink to the MCO? cdwp.TableDataValue(r_mti.usage_type, 1); cdwp.TableDataValue(cdwp.convertYnToCheck(r_mti.select_flag), 1); cdwp.TableDataValue(cdwp.convertYnToCheck(r_mti.insert_flag), 1); cdwp.TableDataValue(cdwp.convertYnToCheck(r_mti.update_flag), 1); cdwp.TableDataValue(cdwp.convertYnToCheck(r_mti.delete_flag), 1); cdwp.TableRowClose; end loop; -- r_mti cdwp.tableClose; htp.print(''); cdwp.tableRowClose; end; -- summary_modtab_definitions procedure summary_trg_definitions is -- -- l_first boolean; l_tbl_ivid number(38):= odwactxt.get_pac_ivid; begin cdwp.bookmark('TRG'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK cdwp.tableOpen('BORDER="0"'); -- l_first := true; for r_trg in c_trg(l_tbl_ivid) loop if l_first then cdwp.tableRowOpen; cdwp.TableDataHeading ( cdwp.add_images('{'||cdwpbase.get_icon('TRG')||'}') ||ROB_msg.getMsg(Rob_msg.DSP312_CDWP_DBTRIGSUM, '', '', '', '') ); htp.print(''); cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0191_CDWP_TIME, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP035_CDWP_INSERT, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP013_CDWP_UPDATE, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP014_CDWP_DELETE, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0189_CDWP_LEVEL, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0205_CDWP_MODULE, '', '', '', ''), 1); cdwp.tableRowClose; cdwp.tableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue(null); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0186_CDWP_PURPOSE, '', '', '', ''), 1, null, 3); cdwp.tableRowClose; l_first := false; end if; -- l_first cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.tableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue(r_trg.name); cdwp.TableDataValue(r_trg.execute_time, 1); cdwp.TableDataValue(cdwp.convertYnToCheck(r_trg.trigger_on_insert), 1); cdwp.TableDataValue(cdwp.convertYnToCheck(r_trg.trigger_on_update), 1); cdwp.TableDataValue(cdwp.convertYnToCheck(r_trg.trigger_on_delete), 1); cdwp.TableDataValue(r_trg.trigger_level, 1); if r_trg.plm_id is not null then cdwp.TableDataValue ( cdwp.report_link ( p_el_id => r_trg.plm_id , p_type_of => 'PLM' , p_text => '{gif}'||r_trg.plm_short_name||' - '||r_trg.plm_name ) ); end if; -- r_trg.plm_id is not null cdwp.tableRowClose; cdwp.tableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue(null); cdwp.TableDataValue(nvl(r_trg.purpose,'.'), 1, null, 3); cdwp.tableRowClose; end loop; -- r_trg cdwp.tableClose; htp.print(''); cdwp.tableRowClose; end; -- summary_trg_definitions procedure print_indcol -- this procedure prints the columns in the index entries -- for the indicated index -- it prints them all in one row, -- assuming the row has been opened and will be closed by the calling routine ( p_ind_reference in number ) is -- cursor to select properties of the key components of the given constraint cursor c_indcol ( b_ind_id in number , b_tbl_ivid in number ) is select /*+ RULE */ indcol.sequence_number indcol_sequence_number , col.name col_name , col.id col_id from ci_index_entries indcol , ci_columns col where indcol.relation_index_reference = b_ind_id and indcol.column_reference = col.id and indcol.parent_ivid = b_tbl_ivid and col.parent_ivid = b_tbl_ivid order by indcol.sequence_number ; l_indcolcol varchar2(15000); l_first boolean :=true; l_tbl_ivid number(38):= odwactxt.get_pac_ivid; begin for r_indcol in c_indcol ( b_ind_id => p_ind_reference , b_tbl_ivid => l_tbl_ivid ) loop if not l_first then l_indcolcol:= l_indcolcol||', '; -- 2.5 added space else l_first:= false; end if; l_indcolcol := l_indcolcol ||cdwpbase.local_link ( p_bookmark => 'col'||to_char(r_indcol.col_id) , p_text => cdwp.add_images ( '{'||cdwpbase.get_icon('INDCOL') ||'}' ) ||r_indcol.col_name ); end loop; -- r_indcol cdwp.TableDataValue(l_indcolcol, 1); end; -- print_indcol procedure summary_ind_definitions is -- -- cursor to select properties of indexesof a specific table_definition -- cursor c_ind ( b_tbl_ivid in number ) is select /*+ RULE */ ind.id , ind.INDEX_TYPE , ind.NAME , ind.REMARK , ind.FOREIGN_KEY_REFERENCE , fco.name fco_name , fco.id fco_id from ci_foreign_key_constraints fco , ci_relation_indexes ind where ind.parent_ivid = b_tbl_ivid -- table_definition_reference = b_tbl_id and fco.irid = ind.FOREIGN_KEY_REFERENCE and fco.parent_ivid = b_tbl_ivid ; -- l_first boolean; l_tbl_ivid number(38):= odwactxt.get_pac_ivid; procedure write_first ( p_title in varchar2 ) is begin if l_first then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.DSP302_CDWP_COLUMNS, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0208_CDWP_FRNKEY, '', '', '', ''), 1); -- cdwp.TableDataValue('Comment', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP008_CDWP_DESC, '', '', '', ''), 1); cdwp.tableRowClose; l_first := false; end if; -- l_first = true end; begin cdwp.bookmark('IND'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK cdwp.tableOpen('BORDER="0"'); cdwp.tableRowOpen; cdwp.TableDataHeading ( cdwp.add_images('{'||cdwpbase.get_icon('IND')||'}') ||Rob_msg.Getmsg(Rob_msg.DSP299_CDWP_INDEXES,'','','','') ); htp.print(''); -- l_first := true; for r_ind in c_ind(l_tbl_ivid) loop write_first('Index'); cdwp.tableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue ( cdwp.add_images('{'||cdwpbase.get_icon('IND')||'}') ||r_ind.name ); print_indcol ( p_ind_reference => r_ind.id ); cdwp.TableDataValue ( cdwpbase.ifThenElse ( r_ind.foreign_key_reference is not null , cdwpbase.local_link ( p_bookmark => 'FCO'||to_char(r_ind.foreign_key_reference) , p_text => cdwp.add_images('{'||cdwpbase.get_icon('FCO')||'}') ||r_ind.fco_name ) ) ); -- cdwp.TableDataValue(r_ind.remark); cdwp_txt.display_mlt ( p_el_id => r_ind.id , p_txt_type => 'CDIDSC' , p_mlt_prompt_name => ROB_msg.getMsg(Rob_msg.CAP008_CDWP_DESC, '', '', '', '') , p_max_lines => 0 , p_el_type => 'IND' , p_no_row => true ); cdwp.tableRowClose; end loop; -- r_ind l_first := true; cdwp.tableClose; htp.print(''); cdwp.tableRowClose; end; -- summary_ind_definitions function column_in_primary_key ( p_column_id in number ) return boolean is cursor c_tabuie ( b_column_id in number , b_tbl_ivid in number ) is select /*+ rule */ 1 from ci_key_components tabuie , ci_primary_key_constraints uidx where tabuie.column_reference = b_column_id and tabuie.constraint_reference = uidx.id and tabuie.parent_ivid = b_tbl_ivid and uidx.parent_ivid = b_tbl_ivid ; l_dummy number(1); begin open c_tabuie ( b_column_id => p_column_id , b_tbl_ivid => odwactxt.get_pac_ivid ); fetch c_tabuie into l_dummy; if c_tabuie%notfound then close c_tabuie; return false; else close c_tabuie; return true; end if; end; -- column_in_primary_key procedure summary_column_definitions is -- -- cursor to select properties of columns of a specific table_definition -- cursor c_col ( b_tbl_ivid in number ) is select col.id col_id , col.name col_name , col.maximum_length col_maximum_length , col.default_value col_default_value , col.help_text col_help_text , col.null_indicator col_null_indicator , col.domain_reference dom_id , col.datatype|| decode(col.maximum_length , null , null , '('|| to_char(col.maximum_length)|| decode(col.decimal_places , null , ')' , ', '|| col.decimal_places|| ')' ) ) col_datatype_full from ci_columns col where col.parent_ivid = b_tbl_ivid order by col.sequence_number; l_first boolean; l_col_gif varchar2(30); l_tbl_ivid number(38) := odwactxt.get_pac_ivid; begin -- -- Columns cdwp.bookmark('COL'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK -- cdwp.tableOpen('BORDER="0"'); -- -- l_first := true; for r_col in c_col(l_tbl_ivid) loop if l_first then cdwp.tableRowOpen; cdwp.TableDataHeading(cdwp.add_images('{column.gif}')||Rob_msg.Getmsg(Rob_msg.CAP322_CDWP_COLSUMM,'','','','')); htp.print(''); cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP020_CDWP_DATATYPE, '', '', '', ''), 1); -- 2.2 Take domain and default out of column summary and replace it with hinttekst. cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0218_CDWP_HINT , '', '', '', ''), 1); cdwp.tableRowClose; l_first := false; end if; cdwp.tableRowOpen; cdwp.TableDataValue('', 1); if column_in_primary_key ( p_column_id => r_col.col_id ) then l_col_gif:= 'primary_key.gif'; else if r_col.col_null_indicator ='NOT NULL' then l_col_gif:= 'mandatory_column.gif'; else l_col_gif:= 'optional_column.gif'; end if; -- column is 'NOT NULL' end if; -- column_in_primary_key cdwp.TableDataValue ( cdwpbase.local_link ( p_bookmark => 'COL'||to_char(r_col.col_id) , p_text => cdwp.add_images('{'||l_col_gif||'}') -- 2.2 ||r_col.col_name ) ); cdwp.TableDataValue(r_col.col_datatype_full, 1); -- 2.2 cdwp.TableDataValue(nvl(r_col.col_help_text,'.'), 1, null, 3); cdwp.tableRowClose; end loop; -- r_col cdwp.tableClose; htp.print(''); cdwp.tableRowClose; end; -- summary_column_definitions procedure print_oky -- this procedure prints the columns in the key components -- for the indicated constraint -- it prints them all in one row, -- assuming the row has been opened and will be closed by the calling routine ( p_oco_reference in number , p_pac_ivid in number default null ) is -- cursor to select properties of the key components of the given constraint cursor c_oky ( b_oco_id in number , b_tbl_ivid in number ) is select /*+ rule */ col.table_reference tab_id , oky.sequence_number oky_sequence_number , col.name col_name , col.id col_id , oky.foreign_column_reference fgn_col_id , col.table_reference col_table_reference , oky.parent_ivid , decode( col.relation_type , 'TABLE', 'TBL' , 'VIEW', 'VW' ) col_table_type from ci_columns col , ci_key_components oky where oky.constraint_reference = b_oco_id and oky.column_reference = col.id and oky.parent_ivid = nvl( b_tbl_ivid, oky.parent_ivid) and oky.parent_ivid = col.parent_ivid order by oky.sequence_number , col.name ; -- cursor to select the properties of a foreign column of a key component cursor c_col_fgn ( b_fgn_col_id in number ) is select fgn_col.name fgn_col_name , fgn_col.table_ref fgn_tab_id , fgn_col.parent_ivid fgn_tab_ivid , decode( fgn_col.relation_type , 'TABLE', 'TBL' , 'VIEW', 'VW' ) fgn_table_type from i$sdd_col fgn_col where fgn_col.irid = b_fgn_col_id and fgn_col.parent_ivid = cdwpbase.get_best_ivid( fgn_col.table_ref) ; r_col_fgn c_col_fgn%rowtype; l_okycol varchar2(15000); -- 2.5 from 2000 to 15000 l_tbl_ivid number(38) := odwactxt.get_pac_ivid; l_first boolean :=true; begin for r_oky in c_oky ( b_oco_id => p_oco_reference , b_tbl_ivid => p_pac_ivid ) loop if not l_first then l_okycol:= l_okycol||', '; -- 2.5 space added else l_first:= false; end if; l_okycol := l_okycol || cdwpbase.ifThenElse ( r_oky.parent_ivid = l_tbl_ivid -- foreign table is the current table , cdwpbase.local_link ( p_bookmark => 'COL'||to_char(r_oky.col_id) , p_text => cdwp.add_images('{'||cdwpbase.get_icon('COL')||'}') ||r_oky.col_name ) , cdwp.report_link ( p_el_id => r_oky.tab_id , p_type_of => r_oky.col_table_type , p_text => cdwp.add_images('{'||cdwpbase.get_icon('COL')||'}') ||''||r_oky.col_name , p_bookmark => 'COL'||to_char(r_oky.col_id) ) ) ; if r_oky.fgn_col_id is not null then -- fetch the foreign column properties open c_col_fgn(b_fgn_col_id => r_oky.fgn_col_id); fetch c_col_fgn into r_col_fgn; close c_col_fgn; l_okycol := l_okycol ||' => ' || cdwpbase.ifThenElse ( r_col_fgn.fgn_tab_id = g_tbl_id -- foreign table is the current table , cdwpbase.local_link ( p_bookmark => 'COL'||to_char(r_oky.fgn_col_id) , p_text => r_col_fgn.fgn_col_name ) , cdwp.report_link ( p_el_id => r_col_fgn.fgn_tab_id , p_ivid => r_col_fgn.fgn_tab_ivid , p_type_of => r_col_fgn.fgn_table_type , p_text => r_col_fgn.fgn_col_name , p_bookmark => 'COL'||to_char(r_oky.fgn_col_id) ) ) ; end if; -- r_oky.fgn_col_id is not null end loop; -- r_oky cdwp.TableDataValue(l_okycol, 1); end; -- print_oky procedure incoming_fco is cursor c_fco ( b_tbl_id in number ) is select /*+ rule */ fco.irid fco_id , fco.IMPLEMENTATION_LEVEL fco_IMPLEMENTATION_LEVEL , fco.NAME fco_NAME , fco.TABLE_REF fco_TABLE_REFERENCE , fco.parent_ivid fco_tab_ivid , tab.name tab_name , tab.irid tab_id , tab.ivid tab_ivid , decode( tab.table_type , 'TABLE', 'TBL' , 'VIEW', 'VW' ) tab_table_type from i$sdd_tab tab , i$sdd_oco fco where fco.foreign_table_ref = b_tbl_id and fco.table_ref = tab.irid and tab.ivid = cdwpbase.get_best_ivid( tab.irid) and fco.parent_ivid = tab.ivid ; -- l_first boolean; procedure write_first ( p_title in varchar2 ) is begin if l_first then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(htf.bold(p_title), 1); cdwp.tableRowClose; cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.DSP302_CDWP_COLUMNS, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0204_CDWP_VALIDATEIN, '', '', '', ''), 1); cdwp.tableRowClose; l_first := false; end if; -- l_first = true end; begin cdwp.bookmark('FCO_IN'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK cdwp.tableOpen('BORDER="0"'); l_first := true; for r_fco in c_fco( b_tbl_id => odwactxt.get_pac_irid) loop if l_first then cdwp.tableRowOpen; cdwp.TableDataHeading ( cdwp.add_images('{'||cdwpbase.get_icon('FCO')||'}') ||'Referenced by '); htp.print(''); cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0215_CDWP_REFTABLE, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0216_CDWP_FKCONSNAME, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.DSP302_CDWP_COLUMNS, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0204_CDWP_VALIDATEIN, '', '', '', ''), 1); cdwp.tableRowClose; l_first := false; end if; -- l_first = true cdwp.tableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue ( cdwp.report_link ( p_el_id => r_fco.fco_table_reference , p_ivid => r_fco.fco_tab_ivid , p_type_of => r_fco.tab_table_type , p_el_name => r_fco.tab_name ) ); cdwp.TableDataValue ( cdwp.report_link ( p_el_id => r_fco.fco_table_reference , p_ivid => r_fco.fco_tab_ivid , p_type_of => r_fco.tab_table_type , p_el_name => r_fco.tab_name , p_bookmark => 'fco'||to_char(r_fco.fco_id) , p_text => cdwp.add_images('{'||cdwpbase.get_icon('FCO')||'}') ||r_fco.fco_name ) ); print_oky ( p_oco_reference => r_fco.fco_id , p_pac_ivid => r_fco.fco_tab_ivid ); cdwp.TableDataValue(r_fco.fco_implementation_level); cdwp.tableRowClose; end loop; -- r_fco cdwp.tableClose; htp.print(''); cdwp.tableRowClose; if not l_first then htp.nl; htp.nl; end if; -- not l_first end; -- incoming_fco procedure summary_oco_definitions is -- -- cursor to select properties of primary key constraints of a specific table_definition -- cursor c_pco ( b_tbl_id in number , b_tbl_ivid in number ) is select pco.id pco_id , pco.IMPLEMENTATION_LEVEL pco_IMPLEMENTATION_LEVEL , pco.NAME pco_NAME , pco.TABLE_REFERENCE pco_TABLE_REFERENCE from ci_primary_key_constraints pco where pco.table_reference = b_tbl_id and pco.parent_ivid = b_tbl_ivid ; cursor c_uco ( b_tbl_id in number , b_tbl_ivid in number ) is select uco.id uco_id , uco.IMPLEMENTATION_LEVEL uco_IMPLEMENTATION_LEVEL , uco.NAME uco_NAME , uco.TABLE_REFERENCE uco_TABLE_REFERENCE from ci_unique_key_constraints uco where uco.table_reference = b_tbl_id and uco.parent_ivid = b_tbl_ivid ; cursor c_fco ( b_tbl_id in number , b_tbl_ivid in number ) is select fco.id fco_id , fco.IMPLEMENTATION_LEVEL fco_IMPLEMENTATION_LEVEL , fco.NAME fco_NAME , fco.TABLE_REFERENCE fco_TABLE_REFERENCE , fco.foreign_table_reference tab_id , tab.ivid tab_ivid , fco.mandatory_flag fco_mandatory_flag , decode( tab.table_type , 'TABLE', 'TBL' , 'VIEW', 'VW' ) tab_table_type from i$sdd_tab tab , ci_foreign_key_constraints fco where fco.table_reference = b_tbl_id and fco.foreign_table_reference = tab.irid and fco.parent_ivid = b_tbl_ivid and tab.ivid = cdwpbase.get_best_ivid( tab.irid) ; cursor c_cco ( b_tbl_id in number , b_tbl_ivid in number ) is select cco.id cco_id , cco.IMPLEMENTATION_LEVEL cco_IMPLEMENTATION_LEVEL , cco.NAME cco_NAME , cco.TABLE_REFERENCE cco_TABLE_REFERENCE from ci_check_constraints cco where cco.table_reference = b_tbl_id and cco.parent_ivid = b_tbl_ivid ; -- l_first boolean; l_tbl_ivid number(38):= odwactxt.get_pac_ivid; procedure write_first ( p_title in varchar2 ) is begin if l_first then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(htf.bold(p_title), 1); cdwp.tableRowClose; cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', ''), 1); -- 2.2 No Columns for Check Constraints if instr( upper(p_title), 'CHECK') = 0 then cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.DSP302_CDWP_COLUMNS, '', '', '', ''), 1); end if; cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0204_CDWP_VALIDATEIN, '', '', '', ''), 1); cdwp.tableRowClose; l_first := false; end if; -- l_first = true end; begin -- Constraints -- cdwp.bookmark('OCO'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK cdwp.tableOpen('BORDER="0"'); cdwp.tableRowOpen; cdwp.TableDataHeading ( cdwp.add_images('{'||cdwpbase.get_icon('OCO')||'}') ||ROB_msg.getMsg(Rob_msg.DSP318_CDWP_SUMCONS, '', '', '', '') ); htp.print(''); l_first := true; for r_pco in c_pco(odwactxt.get_pac_irid, l_tbl_ivid) loop write_first(ROB_msg.getMsg(Rob_msg.DSP316_CDWP_FKC, '', '', '', '')); cdwp.tableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue ( cdwpbase.local_link ( p_bookmark => 'PCO'||to_char(r_pco.pco_id) , p_text => cdwp.add_images('{'||cdwpbase.get_icon('PCO')||'}') ||r_pco.pco_name ) ); print_oky ( p_oco_reference => r_pco.pco_id , p_pac_ivid => l_tbl_ivid ); cdwp.TableDataValue(r_pco.pco_implementation_level); cdwp.tableRowClose; end loop; -- r_pco l_first := true; for r_uco in c_uco(odwactxt.get_pac_irid, l_tbl_ivid) loop write_first(ROB_msg.getMsg(Rob_msg.DSP314_CDWP_UKC, '', '', '', '')); cdwp.tableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue ( cdwpbase.local_link ( p_bookmark => 'UCO'||to_char(r_uco.uco_id) , p_text => cdwp.add_images('{'||cdwpbase.get_icon('UCO')||'}') ||r_uco.uco_name ) ); print_oky ( p_oco_reference => r_uco.uco_id , p_pac_ivid => l_tbl_ivid ); cdwp.TableDataValue(r_uco.uco_implementation_level); cdwp.tableRowClose; end loop; -- r_uco l_first := true; for r_fco in c_fco(odwactxt.get_pac_irid, l_tbl_ivid) loop if l_first then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(htf.bold(ROB_msg.getMsg(Rob_msg.DSP316_CDWP_FKC, '', '', '', '')), 1); cdwp.tableRowClose; cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.DSP315_CDWP_REFTBL, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.DSP302_CDWP_COLUMNS, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0209_CDWP_MANDATE, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0204_CDWP_VALIDATEIN, '', '', '', ''), 1); cdwp.tableRowClose; l_first := false; end if; -- l_first = true cdwp.tableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue ( cdwpbase.local_link ( p_bookmark => 'FCO'||to_char(r_fco.fco_id) , p_text => cdwp.add_images('{'||cdwpbase.get_icon('FCO')||'}') ||r_fco.fco_name ) ); cdwp.TableDataValue ( cdwp.report_link ( p_el_id => r_fco.tab_id , p_ivid => r_fco.tab_ivid , p_type_of => r_fco.tab_table_type ) ); print_oky ( p_oco_reference => r_fco.fco_id , p_pac_ivid => l_tbl_ivid ); cdwp.TableDataValue(cdwp.convertYnToCheck(r_fco.fco_mandatory_flag),1); cdwp.TableDataValue(r_fco.fco_implementation_level); cdwp.tableRowClose; end loop; -- r_fco l_first := true; for r_cco in c_cco(odwactxt.get_pac_irid, l_tbl_ivid) loop write_first(ROB_msg.getMsg(Rob_msg.DSP317_CDWP_CHKCONSTRAINT, '', '', '', '')); cdwp.tableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue ( cdwpbase.local_link ( p_bookmark => 'CCO'||to_char(r_CCO.CCO_id) , p_text => cdwp.add_images('{'||cdwpbase.get_icon('CCO')||'}') ||r_CCO.CCO_name ) ); cdwp.TableDataValue(r_cco.cco_implementation_level); cdwp.tableRowClose; end loop; -- r_cco cdwp.tableClose; htp.print(''); cdwp.tableRowClose; end; -- summary_oco_definitions procedure list_column_definitions -- can be used for both table columns and view columns is -- -- cursor to select properties of columns of a specific table_definition -- cursor c_col ( b_tbl_ivid in number ) is select col.id col_id , col.name col_name , col.datatype col_datatype , col.maximum_length col_maximum_length , col.default_value col_default_value , col.remark col_remark , col.domain_reference dom_id , col.datatype|| decode(col.maximum_length , null , null , '('|| to_char(col.maximum_length)|| decode(col.decimal_places , null , ')' , ', '|| col.decimal_places|| ')' ) ) col_datatype_full , col.AUTO_GENERATED , col.AVERAGE_LENGTH , col.BASE_COLUMN_REFERENCE , col.CLUSTER_COLUMN_REFERENCE , col.COLUMN_DENORMALISED_FROM , col.COMPLETE_FLAG , col.CONSTRAINT_DENORMALISED_VIA , col.DATATYPE , col.DECIMAL_PLACES , col.DEFAULT_DISPLAY_TYPE , col.DEFAULT_IMPLEMENTATION_ITEM , col.DENORMALIZED_SUMMARY_FUNCTION , col.DERIVATION_EXPRESSION_TYPE , col.DESCRIPTOR_COLUMN , col.DISPLAY_FLAG , col.DISPLAY_HEIGHT , col.DISPLAY_LENGTH , col.DISPLAY_SEQUENCE , col.DOMAIN_REFERENCE , col.ELEMENT_TYPE_NAME , col.EXPRESSION_TEXT , col.FIELD_INITIAL_VALUE , col.FIELD_LEVEL , col.FIELD_NULL_VALUE , col.FINAL_VOLUME , col.FORMAT_MODIFIER , col.HELP_TEXT , col.HIGHLIGHTING , col.INITIAL_VOLUME , col.JUSTIFICATION , decode ( col.NULL_INDICATOR , 'NOT NULL', 'N' , 'NULL' , 'Y' ) optional_flag , col.OCCURS , col.ORDER_SEQUENCE , col.PROMPT , col.REDEFINES , col.RELATION_SELECTION_REFERENCE , col.RELATION_TYPE , col.SCOPE_TABLE_REFERENCE , col.SEQUENCE_NUMBER , col.SEQUENCE_REFERENCE , col.SERVER_DEFAULTED_FLAG , col.SERVER_DERIVED_FLAG , col.SIGNED , col.SOFT_LOV , col.SORTING_ORDER , col.SOURCE_ATTRIBUTE_REFERENCE , col.SOURCE_ENTITY_DERIVED_FROM , col.SOURCE_REL_ENTITY , col.SOURCE_REL_ENTITY_ATTRIBUTE , col.SOURCE_RELATIONSHIP_END , col.STORAGE_FORMAT , col.STORE_REF_WITH_ROWID_FLAG , col.SUGGESTION_LIST_FLAG , col.UPPERCASE from ci_columns col where col.parent_ivid = b_tbl_ivid order by col.sequence_number; cursor c_osq ( b_osq_id number ) is select osq.name osq_name , osq.purpose osq_purpose , osq.remark osq_remark from i$sdd_osq osq where osq.irid = b_osq_id and osq.ivid = cdwpbase.get_best_ivid( b_osq_id) ; r_osq c_osq%rowtype; cursor c_att ( b_att_id number ) is select /*+ RULE */ att.name att_name , att.irid att_id , ent.name ent_name , ent.ivid ent_ivid , att.entity_ref ent_id from i$sdd_ent ent , i$sdd_att att where att.irid = b_att_id and att.entity_ref = ent.irid and ent.ivid = cdwpbase.get_best_ivid( att.entity_ref) ; r_att c_att%rowtype; -- select properties needed to make link to this column cursor c_col2 ( b_col_id number ) is select /*+ RULE */ col.name col_name , col.irid col_id , tab.name tab_name , decode( tab.table_type , 'TABLE', 'TBL' , 'VIEW', 'VW' ) tab_table_type , decode( tab.table_type , 'TABLE', 'cdwp_tbl.list_table_definitions' , 'VIEW', 'cdwp_vw.list_vw_definitions' ) procedure_call , col.table_ref tab_id , tab.ivid tab_ivid from i$sdd_tab tab , i$sdd_col col where col.irid = b_col_id and col.table_ref = tab.irid and tab.ivid = cdwpbase.get_best_ivid( tab.irid) and col.parent_ivid = tab.ivid ; r_col2 c_col2%rowtype; cursor c_fco2 ( b_fco_id number ) is select /*+ RULE */ fco.name fco_name , fco.id fco_id , tab.name tab_name , decode( tab.table_type , 'TABLE', 'TBL' , 'VIEW', 'VW' ) tab_table_type , fco.table_reference tab_id , tab.ivid tab_ivid from i$sdd_tab tab , ci_foreign_key_constraints fco where fco.id = b_fco_id and tab.ivid = cdwpbase.get_best_ivid( fco.table_reference) ; r_fco2 c_fco2%rowtype; -- l_first boolean; l_tbl_ivid number(38):= odwactxt.get_pac_ivid; procedure print_prop ( p_label in varchar2 ,p_value in varchar2 ) is begin if p_value is not null then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(p_label, 1); cdwp.TableDataValue(p_value, 1, null, 3); cdwp.tableRowClose; end if; -- p_value is not null end; begin cdwp.bookmark('col_detailed'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK cdwp.heading(3); cdwp.p ( cdwp.add_images('{column.gif}') ||Rob_msg.Getmsg(Rob_msg.CAP323_CDWP_COLSIN,cdwpbase.get_el_name( odwactxt.get_pac_irid),'','','') , p_style => 'partitle' ); htp.nl; htp.nl; l_first := true; for r_col in c_col(l_tbl_ivid) loop cdwp.bookmark('COL'||to_char(r_col.col_id)); -- BOOKMARK cdwp.tableOpen('BORDER="0"'); cdwp.tableRowOpen; cdwp.TableDataHeading('', 1); cdwp.TableDataValue ( cdwp.add_images('{'||cdwpbase.get_icon('COL')||'}') ||r_col.col_name ); cdwp.tableRowClose; cdwp.tableRowOpen; cdwp.TableDataHeading('', 1); cdwp.TableDataValue(cdwp.add_images('{heading4.gif}')); cdwp.tableRowClose; cdwp.tableClose; htp.print(''); cdwp.tableRowClose; cdwp.tableOpen('BORDER="0"'); cdwp.tableRowOpen; cdwp.TableDataHeading('', 1); print_prop( ROB_msg.getMsg(Rob_msg.CAP0218_CDWP_HINT, '', '', '', '') , r_col.help_text); print_prop( ROB_msg.getMsg(Rob_msg.CAP020_CDWP_DATATYPE, '', '', '', '') , r_col.col_datatype_full); print_prop( ROB_msg.getMsg(Rob_msg.CAP0150_CDWP_OPT, '', '', '', '')||'?', cdwp.convertYnToCheck(r_col.optional_flag)); -- following is necessary, because this procedure -- is also used for view columns if r_col.base_column_reference is not null then open c_col2(r_col.base_column_reference ); fetch c_col2 into r_col2; if c_col2%found then print_prop ( 'Base Column Reference' , cdwpbase.ifThenElse ( r_col2.col_id is not null , cdwp.report_link ( p_el_id => r_col2.tab_id , p_ivid => r_col2.tab_ivid , p_type_of => r_col2.tab_table_type , p_text => cdwp.add_images('{'||cdwpbase.get_icon('COL')||'}') ||r_col2.tab_name||'.'||r_col2.col_name , p_bookmark => 'COL'||to_char(r_col2.col_id) ) ) ); end if; -- c_col2%found close c_col2; end if; -- r_col.base_column_reference is not null if r_col.domain_reference is not null then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP021_CDWP_DOMAIN, '', '', '', ''), 1); cdwp.TableDataValue ( cdwp.report_link ( p_el_id => r_col.domain_reference , p_type_of => 'DOM' ) , 1 ); cdwp.tableRowClose; end if; -- r_col.domain_reference is not null -- sequence reference if r_col.sequence_reference is not null then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0219_CDWP_SEQ, '', '', '', ''), 1); open c_osq(r_col.sequence_reference); fetch c_osq into r_osq; if c_osq%found then cdwp.TableDataValue(r_osq.osq_name, 1); end if; close c_osq; cdwp.tableRowClose; end if; -- r_col.sequence_reference is not null print_prop( ROB_msg.getMsg(Rob_msg.CAP022_CDWP_DEFAULT, '', '', '', ''), r_col.col_default_value); print_prop( ROB_msg.getMsg(Rob_msg.CAP0220_CDWP_UPCASE, '', '', '', ''), cdwp.convertYnToCheck(r_col.uppercase)); print_prop( htf.bold(htf.underline(ROB_msg.getMsg(Rob_msg.CAP0221_CDWP_DISPLAY, '', '', '', ''))), ':'); print_prop( '- '||ROB_msg.getMsg(Rob_msg.CAP0221_CDWP_DISPLAY, '', '', '', '')||'?', cdwp.convertYnToCheck( r_col.display_flag)); print_prop('- '|| ROB_msg.getMsg(Rob_msg.CAP0222_CDWP_DISPDATATYPE, '', '', '', ''), r_col.default_display_type); print_prop( '- '||ROB_msg.getMsg(Rob_msg.CAP0223_CDWP_JUSTIFY, '', '', '', '') , r_col.justification); print_prop( '- '||ROB_msg.getMsg(Rob_msg.CAP0224_CDWP_DISPLEN, '', '', '', '') , r_col.display_length); print_prop( '- '||ROB_msg.getMsg(Rob_msg.CAP0225_CDWP_DISPHGT, '', '', '', '') , r_col.display_height); print_prop( '- '||ROB_msg.getMsg(Rob_msg.CAP0226_CDWP_DISPSEQ, '', '', '', ''), r_col.display_sequence); print_prop('- '||ROB_msg.getMsg(Rob_msg.CAP0227_CDWP_FORMAT, '', '', '', '') , r_col.format_modifier); print_prop( '- '||ROB_msg.getMsg(Rob_msg.CAP0228_CDWP_HLIGHT, '', '', '', '') , r_col.highlighting); print_prop( '- '|| ROB_msg.getMsg(Rob_msg.CAP0229_CDWP_PROMPT, '', '', '', '') , r_col.prompt); print_prop( ROB_msg.getMsg(Rob_msg.CAP0230_CDWP_ORDSEQ, '', '', '', '') , r_col.order_sequence); print_prop( ROB_msg.getMsg(Rob_msg.CAP0230_CDWP_SORTORDER, '', '', '', ''), r_col.sorting_order); print_prop( ROB_msg.getMsg(Rob_msg.CAP0231_CDWP_DESSEQ, '', '', '', '') , r_col.descriptor_column); print_prop( ROB_msg.getMsg(Rob_msg.CAP0232_CDWP_AUTOGENTYPE, '', '', '', '') , r_col.auto_generated); -- denormalized from if r_col.COLUMN_DENORMALISED_FROM is not null or r_col.CONSTRAINT_DENORMALISED_VIA is not null or r_col.denormalized_summary_function is not null then print_prop( htf.bold(htf.underline(ROB_msg.getMsg(Rob_msg.CAP0233_CDWP_DENORM, '', '', '', ''))) , ':'); end if; if r_col.column_denormalised_from is not null then open c_col2(r_col.COLUMN_DENORMALISED_FROM ); fetch c_col2 into r_col2; if c_col2%found then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue('- '||ROB_msg.getMsg(Rob_msg.CAP0234_CDWP_FRMCOL, '', '', '', ''), 1); cdwp.TableDataValue ( cdwpbase.ifThenElse ( r_col2.col_id is not null , cdwp.report_link ( p_el_id => r_col2.tab_id , p_ivid => r_col2.tab_ivid , p_type_of => 'TAB' , p_text => cdwp.add_images('{column.gif}') ||r_col2.tab_name||'.'||r_col2.col_name , p_bookmark => 'COL'||to_char(r_col2.col_id) ) ) , 1 ); cdwp.tableRowClose; end if; close c_col2; end if; -- r_col.COLUMN_DENORMALISED_FROM is not null if r_col.CONSTRAINT_DENORMALISED_VIA is not null then open c_fco2(r_col.CONSTRAINT_DENORMALISED_VIA ); fetch c_fco2 into r_fco2; if c_fco2%found then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue('-'||ROB_msg.getMsg(Rob_msg.CAP0208_CDWP_FRNKEY, '', '', '', ''), 1); cdwp.TableDataValue ( cdwp.report_link ( p_el_id => r_fco2.tab_id , p_ivid => r_fco2.tab_ivid , p_type_of => r_fco2.tab_table_type , p_bookmark => 'fco'||to_char(r_fco2.fco_id) ) , 1 ); cdwp.tableRowClose; end if; close c_fco2; end if; -- r_col.CONSTRAINT_DENORMALISED_VIA is not null print_prop( '- Using Operator' , r_col.denormalized_summary_function); -- source attribute reference if r_col.source_attribute_reference is not null then open c_att(r_col.source_attribute_reference ); fetch c_att into r_att; if c_att%found then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0235_CDWP_SRCATTRIB, '', '', '', ''), 1); -- until Attributes are independently recognisable, -- we will have to link to Entities cdwp.TableDataValue ( cdwp.report_link ( p_el_id => r_att.ent_id , p_ivid => r_att.ent_ivid , p_type_of => 'ENT' , p_text => r_att.ent_name||'.'||r_att.att_name , p_bookmark => 'ATT'||to_char(r_att.ent_id) ) , 1 ); cdwp.tableRowClose; end if; close c_att; end if; -- r_col.source_attribute_reference is not null print_prop( ROB_msg.getMsg(Rob_msg.CAP0236_CDWP_SERVERDEF, '', '', '', '') , cdwp.convertYnToCheck(r_col.server_derived_flag)); print_prop( ROB_msg.getMsg(Rob_msg.CAP0237_CDWP_SUGLIST, '', '', '', ''), cdwp.convertYnToCheck(r_col.suggestion_list_flag)); print_prop(ROB_msg.getMsg(Rob_msg.CAP0238_CDWP_DYNLIST, '', '', '', '') , cdwp.convertYnToCheck(r_col.soft_lov)); cdwp.tableClose; -- new table for multiline text cdwp.tableOpen('BORDER="0"'); cdwp_txt.display_all_mlt (p_el_id => r_col.col_id ,p_max_lines => null ,p_el_type => 'COL' ); cdwp.tableClose; htp.print(''); cdwp.tableRowClose; htp.nl; end loop; -- r_col end; -- list_column_definitions procedure list_pco_definitions is -- -- cursor to select properties of pco of a specific table_definition -- cursor c_pco ( b_tbl_id in number , b_tbl_ivid in number ) is select pco.id , pco.COMPLETE_FLAG , pco.DEFER_STATUS , pco.ELEMENT_TYPE_NAME , pco.ENABLED_FLAG , pco.ERROR_MESSAGE , pco.EXCEPTION_TABLE , pco.IMPLEMENTATION_LEVEL , pco.KEY_UPDATEABLE , pco.NAME , pco.UNIQUE_IDENTIFIER_REFERENCE from ci_primary_key_constraints pco where pco.table_reference = b_tbl_id and pco.parent_ivid = b_tbl_ivid ; l_first boolean; l_tbl_ivid number(38):= odwactxt.get_pac_ivid; procedure print_prop ( p_label in varchar2 ,p_value in varchar2 ) is begin if p_value is not null then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(p_label, 1); cdwp.TableDataValue(p_value, 1, null, 3); cdwp.tableRowClose; end if; -- p_value is not null end; begin cdwp.bookmark( 'pco_detailed'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK l_first := true; for r_pco in c_pco(odwactxt.get_pac_irid, l_tbl_ivid) loop if l_first then hwsp.fontOpen('Book Antiqua'); htp.bold(cdwp.add_images('{primary_key.gif}') ||ROB_msg.getMsg(Rob_msg.DSP313_CDWP_PKC, '', '', '', '')||' '||g_tbl_name); hwsp.fontClose; htp.nl; htp.nl; l_first:= false; end if; -- l_first = true cdwp.bookmark('PCO'||to_char(r_pco.id)); -- BOOKMARK cdwp.tableOpen('BORDER="0"'); cdwp.tableRowOpen; cdwp.TableDataHeading('', 1); cdwp.TableDataValue ( cdwp.add_images('{primary_key.gif}')||htf.underline(r_pco.name) ); cdwp.tableRowClose; print_prop( ROB_msg.getMsg(Rob_msg.CAP0204_CDWP_VALIDATEIN, '', '', '', '') , r_pco.implementation_level); print_prop( ROB_msg.getMsg(Rob_msg.CAP0203_CDWP_ERRORMSG, '', '', '', '') , r_pco.error_message); print_prop( ROB_msg.getMsg(Rob_msg.CAP0217_CDWP_UPDATEABLE, '', '', '', '') , r_pco.key_updateable); cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP033_CDWP_COL, '', '', '', '')||'(s)', 1); print_oky ( p_oco_reference => r_pco.id , p_pac_ivid => l_tbl_ivid ); cdwp.tableRowClose; print_prop(ROB_msg.getMsg(Rob_msg.CAP0202_CDWP_EXCEPTABLE, '', '', '', '') , r_pco.exception_table); print_prop( ROB_msg.getMsg(Rob_msg.CAP0201_CDWP_DEFERSTATUS, '', '', '', '') , r_pco.defer_status); print_prop( ROB_msg.getMsg(Rob_msg.CAP0200_CDWP_ENABLEFLAG, '', '', '', '') , r_pco.enabled_flag); print_prop( ROB_msg.getMsg(Rob_msg.CAP0199_CDWP_COMPFLAG, '', '', '', '') , r_pco.complete_flag); cdwp.tableClose; -- new table for multiline text cdwp.tableOpen('BORDER="0"'); cdwp_txt.display_all_mlt ( p_el_id => r_pco.id , p_max_lines => null , p_el_type => 'TBL' ); cdwp.tableClose; htp.print(''); cdwp.tableRowClose; htp.nl; end loop; -- r_pco end; -- list_pco_definitions procedure list_uco_definitions is -- -- cursor to select properties of uco of a specific table_definition -- cursor c_uco ( b_tbl_id in number , b_tbl_ivid in number ) is select uco.id , uco.COMPLETE_FLAG , uco.DEFER_STATUS , uco.ELEMENT_TYPE_NAME , uco.ENABLED_FLAG , uco.ERROR_MESSAGE , uco.EXCEPTION_TABLE , uco.IMPLEMENTATION_LEVEL , uco.KEY_UPDATEABLE , uco.NAME , uco.MANDATORY_FLAG , uco.UNIQUE_IDENTIFIER_REFERENCE from ci_unique_key_constraints uco where uco.table_reference = b_tbl_id and uco.parent_ivid = b_tbl_ivid ; l_first boolean; l_tbl_ivid number(38):= odwactxt.get_pac_ivid; procedure print_prop ( p_label in varchar2 ,p_value in varchar2 ) is begin if p_value is not null then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(p_label, 1); cdwp.TableDataValue(p_value, 1, null, 3); cdwp.tableRowClose; end if; -- p_value is not null end; begin cdwp.bookmark('uco_detailed'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK l_first := true; for r_uco in c_uco(odwactxt.get_pac_irid, l_tbl_ivid) loop if l_first then hwsp.fontOpen('Book Antiqua'); htp.bold(cdwp.add_images('{unique_constraint.gif}') ||ROB_msg.getMsg(Rob_msg.DSP314_CDWP_UKC, '', '', '', '')||g_tbl_name); hwsp.fontClose; htp.nl; htp.nl; l_first:= false; end if; -- l_first = true cdwp.bookmark('UCO'||to_char(r_uco.id)); -- BOOKMARK cdwp.tableOpen('BORDER="0"'); cdwp.tableRowOpen; cdwp.TableDataHeading('', 1); cdwp.TableDataValue(cdwp.add_images('{unique_constraint.gif}')||htf.underline(r_uco.name) ); cdwp.tableRowClose; print_prop( ROB_msg.getMsg(Rob_msg.CAP0204_CDWP_VALIDATEIN, '', '', '', '') , r_uco.implementation_level); print_prop( ROB_msg.getMsg(Rob_msg.CAP0209_CDWP_MANDATE, '', '', '', '') , r_uco.mandatory_flag); print_prop( ROB_msg.getMsg(Rob_msg.CAP0203_CDWP_ERRORMSG, '', '', '', '') , r_uco.error_message); print_prop(ROB_msg.getMsg(Rob_msg.CAP0217_CDWP_UPDATEABLE, '', '', '', '') , r_uco.key_updateable); cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue('Column(s)', 1); print_oky ( p_oco_reference => r_uco.id , p_pac_ivid => odwactxt.get_pac_ivid ); cdwp.tableRowClose; print_prop(ROB_msg.getMsg(Rob_msg.CAP0202_CDWP_EXCEPTABLE, '', '', '', '') , r_uco.exception_table); print_prop( ROB_msg.getMsg(Rob_msg.CAP0201_CDWP_DEFERSTATUS, '', '', '', '') , r_uco.defer_status); print_prop( ROB_msg.getMsg(Rob_msg.CAP0200_CDWP_ENABLEFLAG, '', '', '', '') , r_uco.enabled_flag); print_prop( ROB_msg.getMsg(Rob_msg.CAP0199_CDWP_COMPFLAG, '', '', '', '') , r_uco.complete_flag); cdwp.tableClose; -- new table for multiline text cdwp.tableOpen('BORDER="0"'); cdwp_txt.display_all_mlt ( p_el_id => r_uco.id , p_max_lines => null , p_el_type => 'TBL' ); cdwp.tableClose; htp.print(''); cdwp.tableRowClose; htp.nl; end loop; -- r_uco end; -- list_uco_definitions procedure list_fco_definitions is -- -- cursor to select properties of fco of a specific table_definition -- cursor c_fco ( b_tbl_id in number , b_tbl_ivid in number ) is select /*+ RULE */ fco.id , fco.COMPLETE_FLAG , fco.DEFER_STATUS , fco.ELEMENT_TYPE_NAME , fco.ENABLED_FLAG , fco.ERROR_MESSAGE , fco.EXCEPTION_TABLE , fco.IMPLEMENTATION_LEVEL , fco.NAME , fco.MANDATORY_FLAG , fco.ARC_MANDATORY , fco.ARC_NUMBER , decode(fco.FK_CASCADE_DELETE , 'X', 'Restricted' , 'C', 'Cascades' , 'N', 'Nullifies' , 'D', 'Defaults' ) fk_cascade_delete , decode(fco.FK_CASCADE_UPDATE , 'X', 'Restricted' , 'C', 'Cascades' , 'N', 'Nullifies' , 'D', 'Defaults' ) FK_CASCADE_UPDATE , fco.FK_TRANSFERABLE , fco.FOREIGN_TABLE_REFERENCE , fco.PRIMARY_KEY_REFERENCE , fco.RELATIONSHIP_END_REFERENCE , tab.name tab_name , tab.irid tab_id , tab.ivid tab_ivid , decode( tab.table_type , 'TABLE', 'TBL' , 'VIEW', 'VW' ) tab_table_type from i$sdd_tab tab , ci_foreign_key_constraints fco where fco.table_reference = b_tbl_id and fco.foreign_table_reference = tab.irid and fco.parent_ivid = b_tbl_ivid and tab.ivid = cdwpbase.get_best_ivid( tab.irid) ; l_first boolean; l_tbl_ivid number(38):= odwactxt.get_pac_ivid; procedure print_prop ( p_label in varchar2 ,p_value in varchar2 ) is begin if p_value is not null then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(p_label, 1); cdwp.TableDataValue(p_value, 1, null, 3); cdwp.tableRowClose; end if; -- p_value is not null end; begin -- fco cdwp.bookmark('fco_detailed'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK -- l_first := true; for r_fco in c_fco(odwactxt.get_pac_irid, l_tbl_ivid) loop if l_first then hwsp.fontOpen('Book Antiqua'); htp.bold(cdwp.add_images('{fk.gif}') ||ROB_msg.getMsg(Rob_msg.DSP316_CDWP_FKC, '', '', '', '')||g_tbl_name); hwsp.fontClose; htp.nl; htp.nl; l_first:= false; end if; -- l_first = true cdwp.bookmark('FCO'||to_char(r_fco.id)); -- BOOKMARK cdwp.tableOpen('BORDER="0"'); cdwp.tableRowOpen; cdwp.TableDataHeading('', 1); cdwp.TableDataValue(cdwp.add_images('{fk.gif}')||htf.underline(r_fco.name) ); cdwp.tableRowClose; print_prop ( ROB_msg.getMsg(Rob_msg.DSP315_CDWP_REFTBL, '', '', '', '') , cdwp.report_link ( p_el_id => r_fco.foreign_table_reference , p_ivid => r_fco.tab_ivid , p_type_of => r_fco.tab_table_type ) ); print_prop(ROB_msg.getMsg(Rob_msg.CAP0204_CDWP_VALIDATEIN, '', '', '', '') , r_fco.implementation_level); print_prop(ROB_msg.getMsg(Rob_msg.CAP0209_CDWP_MANDATE, '', '', '', '') , r_fco.mandatory_flag); print_prop( ROB_msg.getMsg(Rob_msg.CAP0203_CDWP_ERRORMSG, '', '', '', '') , r_fco.error_message); cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP033_CDWP_COL, '', '', '', '')||'(s)', 1); print_oky ( p_oco_reference => r_fco.id , p_pac_ivid => odwactxt.get_pac_ivid ); cdwp.tableRowClose; print_prop( ROB_msg.getMsg(Rob_msg.CAP0202_CDWP_EXCEPTABLE, '', '', '', ''), r_fco.exception_table); print_prop( ROB_msg.getMsg(Rob_msg.CAP0201_CDWP_DEFERSTATUS, '', '', '', '') , r_fco.defer_status); cdwp.tableRowOpen; -- 1.1 switched next two lines cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP042_CDWP_TRANSFERABLE, '', '', '', '')||'?', 1); cdwp.TableDataValue(cdwp.convertYnToCheck( r_fco.fk_transferable), 1); cdwp.tableRowClose; print_prop( ROB_msg.getMsg(Rob_msg.CAP0210_CDWP_DELRULE, '', '', '', '') , r_fco.fk_cascade_delete); print_prop( ROB_msg.getMsg(Rob_msg.CAP0211_CDWP_UPDATERULE, '', '', '', '') , r_fco.fk_cascade_update); print_prop( ROB_msg.getMsg(Rob_msg.CAP0212_CDWP_ARCNUM, '', '', '', '') , r_fco.arc_number); print_prop( ROB_msg.getMsg(Rob_msg.CAP0213_CDWP_ARCMANDATE, '', '', '', '') , r_fco.arc_mandatory); print_prop( ROB_msg.getMsg(Rob_msg.CAP0200_CDWP_ENABLEFLAG, '', '', '', '') , r_fco.enabled_flag); print_prop( ROB_msg.getMsg(Rob_msg.CAP0199_CDWP_COMPFLAG, '', '', '', '') , r_fco.complete_flag); cdwp.tableClose; -- new table for multiline text cdwp.tableOpen('BORDER="0"'); cdwp_txt.display_all_mlt ( p_el_id => r_fco.id , p_max_lines => null , p_el_type => 'TBL' ); cdwp.tableClose; htp.nl; end loop; -- r_fco end; -- list_fco_definitions procedure list_cco_definitions is -- -- cursor to select properties of cco of a specific table_definition -- cursor c_cco ( b_tbl_id in number , b_tbl_ivid in number ) is select cco.id , cco.COMPLETE_FLAG , cco.DEFER_STATUS , cco.ELEMENT_TYPE_NAME , cco.ENABLED_FLAG , cco.ERROR_MESSAGE , cco.EXCEPTION_TABLE , cco.IMPLEMENTATION_LEVEL , cco.NAME , cco.remark from ci_check_constraints cco where cco.table_reference = b_tbl_id and cco.parent_ivid = b_tbl_ivid ; l_first boolean; l_tbl_ivid number(38):= odwactxt.get_pac_ivid; procedure print_prop ( p_label in varchar2 ,p_value in varchar2 ) is begin if p_value is not null then cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(p_label, 1); cdwp.TableDataValue(p_value, 1, null, 3); cdwp.tableRowClose; end if; -- p_value is not null end; begin -- -- cco -- cdwp.bookmark( 'cco_detailed'||to_char(odwactxt.get_pac_irid)); -- BOOKMARK l_first := true; for r_cco in c_cco(odwactxt.get_pac_irid , l_tbl_ivid) loop if l_first then hwsp.fontOpen('Book Antiqua'); htp.bold(cdwp.add_images('{check_constraint.gif}') ||ROB_msg.getMsg(Rob_msg.DSP308_CDWP_CHKCONSTRAINT, g_tbl_name , '', '', '')); hwsp.fontClose; htp.nl; htp.nl; l_first:= false; end if; -- l_first = true cdwp.bookmark('CCO'||to_char(r_cco.id)); -- BOOKMARK cdwp.tableOpen('BORDER="0"'); cdwp.tableRowOpen; cdwp.TableDataHeading('', 1); cdwp.TableDataValue ( cdwp.add_images('{check_constraint.gif}') ||htf.underline(r_cco.name) ); cdwp.tableRowClose; print_prop( ROB_msg.getMsg(Rob_msg.CAP023_CDWP_COMMENT, '', '', '', '') , r_cco.remark); print_prop( ROB_msg.getMsg(Rob_msg.CAP0204_CDWP_VALIDATEIN, '', '', '', '') , r_cco.implementation_level); print_prop( ROB_msg.getMsg(Rob_msg.CAP0203_CDWP_ERRORMSG, '', '', '', '') , r_cco.error_message); print_prop(ROB_msg.getMsg(Rob_msg.CAP0202_CDWP_EXCEPTABLE, '', '', '', '') , r_cco.exception_table); print_prop( ROB_msg.getMsg(Rob_msg.CAP0201_CDWP_DEFERSTATUS, '', '', '', '') , r_cco.defer_status); print_prop( ROB_msg.getMsg(Rob_msg.CAP0200_CDWP_ENABLEFLAG, '', '', '', '') , r_cco.enabled_flag); print_prop( ROB_msg.getMsg(Rob_msg.CAP0199_CDWP_COMPFLAG, '', '', '', '') , r_cco.complete_flag); cdwp.tableClose; -- new table for multiline text cdwp.tableOpen('BORDER="0"'); cdwp_txt.display_all_mlt ( p_el_id => r_cco.id , p_max_lines => null , p_el_type => 'TBL' ); cdwp.tableClose; htp.print(''); cdwp.tableRowClose; htp.nl; end loop; -- r_cco end; -- list_cco_definitions procedure list_tabent is -- cursor to select table-entity mappings for a specific entity -- cursor c_tabent ( b_tbl_id in number , b_tbl_ivid in number ) is select /*+ RULE */ ent.irid ent_id , ent.ivid ent_ivid , ent.short_name ent_alias , ent.name ent_name , tabent.column_ref column_reference , col.name col_name , tabent.discriminator_value discriminator_value , tabent.mapping_type mapping_type , tabent.remark remark from i$sdd_ent ent , i$sdd_col col , i$sdd_tabent tabent where tabent.entity_ref = ent.irid and tabent.table_ref = b_tbl_id and tabent.column_ref = col.irid(+) and tabent.parent_ivid = b_tbl_ivid and col.parent_ivid(+) = b_tbl_ivid and ent.ivid = cdwpbase.get_best_ivid( tabent.entity_ref) order by ent.name ; r_tabent c_tabent%rowtype; -- l_first boolean; begin cdwp.bookmark('tabent'||to_char(odwactxt.get_pac_irid)); cdwp.tableOpen('BORDER="0"'); l_first := true; for r_tabent in c_tabent ( b_tbl_id => odwactxt.get_pac_irid , b_tbl_ivid => odwactxt.get_pac_ivid ) loop if l_first then cdwp.tableRowOpen; cdwp.TableDataHeading ( cdwp.add_images('{'||cdwpbase.get_icon('ENT')||'}') ||ROB_msg.getMsg(Rob_msg.DSP307_CDWP_IMPLENTITY, '', '', '', '') ); htp.print(''); cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.DSP304_CDWP_DISCRIMCOL, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.DSP305_CDWP_DISCRIMVAL, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.DSP306_CDWP_MAPTYPE, '', '', '', ''), 1); cdwp.tableRowClose; cdwp.tableRowOpen; cdwp.TableDataValue(null); cdwp.TableDataValue(null); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP023_CDWP_COMMENT, '', '', '', ''), 1, null, 3); cdwp.tableRowClose; l_first := false; end if; cdwp.tableRowOpen; cdwp.TableDataValue(''); -- 1.7 report_link should ensure that the correct application system context is called cdwp.TableDataValue ( cdwp.report_link ( p_el_id => r_tabent.ent_id , p_ivid => r_tabent.ent_ivid , p_type_of => 'ENT' ) ); cdwp.TableDataValue(r_tabent.col_name, 1); cdwp.TableDataValue(r_tabent.discriminator_value,1); cdwp.TableDataValue(r_tabent.mapping_type,1); cdwp.tableRowClose; cdwp.tableRowOpen; cdwp.TableDataValue(''); cdwp.TableDataValue(''); cdwp.TableDataValue(r_tabent.remark); cdwp.tableRowClose; end loop; -- r_tabent cdwp.tableClose; htp.print(''); cdwp.tableRowClose; end; -- list_tabent procedure list_vwtab is -- cursor to select view tables usages -- cursor c_vwtab ( b_tbl_id in number ) is select /*+ RULE */ vw.irid vw_id , vw.ivid vw_ivid , vw.alias vw_alias , vw.name vw_name , vwtab.alias vwtab_alias , vwtab.sequence_number vwtab_sequence , vwtab.subquery_usage_flag vwtab_subquery_usage_flag from i$sdd_tab vw , i$sdd_vwtab vwtab where vwtab.table_ref = b_tbl_id and vwtab.parent_ivid = cdwpbase.get_best_ivid( vwtab.view_ref) and vw.ivid = vwtab.parent_ivid order by vw.name; r_vwtab c_vwtab%rowtype; -- l_first boolean; begin cdwp.bookmark('vwtab'||to_char(odwactxt.get_pac_irid)); cdwp.tableOpen('BORDER="0"'); l_first := true; for r_vwtab in c_vwtab(odwactxt.get_pac_irid) loop if l_first then cdwp.tableRowOpen; cdwp.TableDataHeading ( cdwp.add_images('{view.gif}') ||ROB_msg.getMsg(Rob_msg.DSP303_CDWP_USEDBYVIEWS, '', '', '', '') ); htp.print(''); cdwp.tableRowOpen; cdwp.TableDataValue('', 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', ''), 1); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP029_CDWP_ALIAS, '', '', '', ''), 1); cdwp.tableRowClose; l_first := false; end if; cdwp.tableRowOpen; cdwp.TableDataValue(''); cdwp.TableDataValue ( cdwp.report_link -- 2.2 ( p_el_id => r_vwtab.vw_id , p_ivid => r_vwtab.vw_ivid , p_type_of => 'VW' , p_el_name => r_vwtab.vw_name ) ); cdwp.TableDataValue(r_vwtab.vw_alias, 1); cdwp.tableRowClose; end loop; -- r_vwtab cdwp.tableClose; htp.print(''); cdwp.tableRowClose; end; -- list_vwtab function display_name_al_type ( p_al_type in varchar2 ) return varchar2 is begin if p_al_type ='EVA' then return 'Events'; elsif p_al_type = 'NRA' then return ROB_msg.getMsg(Rob_msg.CAP0155_CDWPBASE_NRA, '', '', '', ''); elsif p_al_type = 'API' then return ROB_msg.getMsg(Rob_msg.CAP0157_CDWPBASE_API, '', '', '', ''); else return ROB_msg.getMsg(Rob_msg.CAP0198_CDWP_APPLOGIC, '', '', '', ''); end if; end; -- display_name_al_type procedure list_app_logic ( p_el_id in number default null , p_el_ivid in number default null , p_al_type in varchar2 default '%' , p_el_type in varchar2 default 'TBL' -- 2.7 ) is l_first boolean; begin cdwp.bookmark ( cdwpbase.ifThenElse ( p_al_type = '%' , 'applogic' , p_al_type ) ||to_char(p_el_id) ); cdwp.tableOpen('BORDER="0"'); l_first := true; for r_al in c_al(p_el_id, p_el_ivid , p_al_type) loop if l_first then cdwp.tableRowOpen; cdwp.TableDataHeading ( cdwp.add_images('{app_logic_indicator.gif}') ||display_name_al_type( p_al_type) ); cdwp.tableRowOpen; cdwp.TableDataValue(''); if r_al.al_type in ('API', 'EVA') then cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0197_CDWP_EVENT, '', '', '', '')); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', '')); else cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', ''), p_colspan => 2); end if; cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP023_CDWP_COMMENT, '', '', '', '')); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0196_CDWP_CODE, '', '', '', '')); cdwp.tableRowClose; l_first := false; end if; cdwp.tableRowOpen; cdwp.TableDataValue(''); if r_al.al_type in ('API', 'EVA') then cdwp.TableDataValue(r_al.al_evt_name); cdwp.TableDataValue(r_al.al_display_label); else cdwp.TableDataValue(r_al.al_display_label, p_colspan => 2); end if; -- r_al.al_type in ('API', 'EVA') cdwp_txt.display_mlt ( p_el_id => r_al.al_id , p_txt_type => 'ALCMNT' , p_mlt_prompt_name => ROB_msg.getMsg(Rob_msg.CAP023_CDWP_COMMENT, '', '', '', '') , p_max_lines => 0 , p_el_type => p_el_type -- 2.7 , p_no_row => true ); cdwp_txt.display_mlt ( p_el_id => r_al.al_id , p_txt_type => 'ALCODE' , p_mlt_prompt_name => ROB_msg.getMsg(Rob_msg.CAP0196_CDWP_CODE, '', '', '', '') , p_max_lines => 0 , p_el_type => p_el_type -- 2.7 , p_no_row => true ); cdwp.tableRowClose; end loop; -- r_al cdwp.tableClose; cdwp.tableRowClose; end; -- list_app_logic -- 2.2 function has_app_logic ( p_el_id in number default null , p_el_ivid in number default null , p_al_type in varchar2 default '%' -- any type; other values: NRA, API and EVA ) return boolean is r_al c_al%ROWTYPE; begin open c_al ( b_el_id => p_el_id , b_el_ivid => p_el_ivid , b_al_type => p_al_type ); fetch c_al into r_al; if c_al%found then close c_al; return true; else close c_al; return false; end if; end; -- has_app_logic procedure list_table_definitions ( p_session_id in number , p_irid in number default null , p_ivid in number default null ) is p_app_name varchar2(10):='APP'; p_app_version number(10) := 1; p_static boolean := odwactxt.get_static; p_one_file varchar2(1):= odwactxt.get_one_file; -- local variables -- l_first boolean; procedure icon_bar ( p_tbl_irid in number , p_tbl_ivid in number ) is begin -- begin icon bar odwarprt.init_short_cut_bar; cdwp.tableOpen('BORDER="0"'); cdwp.tableRowOpen; if odwaxist.exists_detail ( p_pac_irid => p_tbl_irid , p_pac_ivid => p_tbl_ivid , p_el_type_of => 'COL' ) then odwarprt.show_anchor( p_anchor_name => 'COL'||to_char(p_tbl_irid), p_gif => 'column.gif', p_title => ' '||ROB_msg.getMsg(Rob_msg.DSP302_CDWP_COLUMNS, '', '', '', '')); end if; if odwaxist.exists_detail ( p_pac_irid => p_tbl_irid , p_pac_ivid => p_tbl_ivid , p_el_type_of => 'TABENT' ) then odwarprt.show_anchor( p_anchor_name => 'TABENT'||to_char(p_tbl_irid), p_gif => 'entity.gif', p_title => ' '||ROB_msg.getMsg(Rob_msg.DSP301_CDWP_MAPTABENTITY, '', '', '', '')); end if; if odwaxist.exists_detail ( p_pac_irid => p_tbl_irid , p_pac_ivid => p_tbl_ivid , p_el_type_of => 'OCO' ) then odwarprt.show_anchor( p_anchor_name => 'OCO'||to_char(p_tbl_irid), p_gif => 'constraint.gif', p_title => ' '||ROB_msg.getMsg(Rob_msg.DSP300_CDWP_CONSTRAINT, '', '', '', '')); end if; if odwaxist.exists_detail ( p_pac_irid => p_tbl_irid , p_pac_ivid => p_tbl_ivid , p_el_type_of => 'IND' ) then odwarprt.show_anchor( p_anchor_name => 'IND'||to_char(p_tbl_irid), p_gif => 'index.gif', p_title =>' '||ROB_msg.getMsg(Rob_msg.DSP299_CDWP_INDEXES, '', '', '', '')); end if; if exists_brdd_definition( p_tbl_ivid => p_tbl_ivid) then odwarprt.show_anchor( p_anchor_name => 'BRDD'||to_char(p_tbl_irid), p_gif => 'brdd.gif', p_title => ' '||ROB_msg.getMsg(Rob_msg.DSP260_CDWP_BRULE, '', '', '', '')); end if; if exists_trg(p_tbl_ivid => p_tbl_ivid) then odwarprt.show_anchor( p_anchor_name => 'TRG'||to_char(p_tbl_irid), p_gif => 'db_trigger.gif', p_title =>' '||ROB_msg.getMsg(Rob_msg.DSP298_CDWP_DBTRIGGERS, '', '', '', '')); end if; if odwaxist.exists_detail ( p_pac_irid => p_tbl_irid , p_el_type_of => 'MTI' , p_ref_column => 'TABLE_REFERENCE' ) then odwarprt.show_anchor( p_anchor_name => 'MODTAB'||to_char(p_tbl_irid), p_gif => 'module_detail_tab_usage.gif', p_title =>ROB_msg.getMsg(Rob_msg.DSP251_CDWP_USEDINMODULE, '', '', '', '')); end if; if odwaxist.exists_detail ( p_pac_irid => p_tbl_irid , p_el_type_of => 'VWTAB' , p_ref_column => 'TABLE_REFERENCE' ) then odwarprt.show_anchor( p_anchor_name => 'VWTAB'||to_char(p_tbl_irid), p_gif => 'view.gif', p_title =>ROB_msg.getMsg(Rob_msg.DSP297_CDWP_USEDINVIEWS, '', '', '', '')); end if; if odwaxist.exists_detail ( p_pac_irid => p_tbl_irid , p_el_type_of => 'FCO' , p_ref_column => 'FOREIGN_TABLE_REFERENCE' ) then odwarprt.show_anchor( p_anchor_name => 'FCO_IN'||to_char(p_tbl_irid), p_gif => 'fk.gif', p_title =>ROB_msg.getMsg(Rob_msg.DSP296_CDWP_REFBYFK, '', '', '', '')); end if; if odwaxist.exists_detail ( p_pac_irid => p_tbl_irid , p_el_type_of => 'DEU' ) then odwarprt.show_anchor( p_anchor_name => 'DEU'||to_char(p_tbl_irid), p_gif => 'data_diagram.gif', p_title =>ROB_msg.getMsg(Rob_msg.DSP271_CDWP_INDIAGRAM, '', '', '', '')); end if; if has_app_logic ( p_el_ivid => p_tbl_ivid , p_al_type => '%' ) then odwarprt.show_anchor ( p_anchor_name => 'API'||to_char(p_tbl_irid) , p_gif => 'app_logic_indicator.gif' , p_title => display_name_al_type( p_al_type => 'API') ); end if; if cdwp.exists_mlt( p_id => p_tbl_ivid, p_not_include1 => 'CDIDSC') then odwarprt.show_anchor( p_anchor_name => 'TAB_MLT'||to_char(p_tbl_ivid), p_gif => 'mlt.gif', p_title =>ROB_msg.getMsg(Rob_msg.DSP131_CDWP_MLT, '', '', '', '')); end if; cdwp.tableRowClose; cdwp.tableClose; -- end icon bar end; -- icon_bar begin -- list_table_definitions -- if an ivid is passed in, that suggests we are going outside the workarea -- so in updating the context, the workarea id should be reset odwactxt.update_context ( p_session_id => p_session_id , p_package_name => PACKAGE_NAME , p_procedure_name=> 'list_table_definitions' , P_PAC_IRID => p_irid , P_PAC_IvID => p_ivid , P_PAC_TYPE_ID => 4974 ); cdwpbase.start_timer; htp.htmlOpen; htp.headOpen; cdwp.include_report_styles; if odwactxt.get_one_file = 'Y' then htp.title ( cdwpbase.get_el_name ( p_el_id => odwactxt.get_folder_irid , p_el_type_of => 'APP' ) ||' -'||ROB_msg.getMsg(Rob_msg.CAP0188_CDWP_TABLEDEF, '', '', '', '') ); else htp.title ( cdwpbase.get_el_name ( p_el_id => odwactxt.get_folder_irid , p_el_type_of => 'APP' ) ||' - '||ROB_msg.getMsg(Rob_msg.CAP0188_CDWP_TABLEDEF, '', '', '', '')||' - ' ||cdwpbase.get_el_name( p_el_id => nvl(p_irid, cdwpbase.get_irid( p_ivid)), p_el_type_of => 'TBL') ); end if; htp.headClose; htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"'); cdwpbase.open_timer_form; if odwactxt.get_one_file = 'Y' then -- 2.1 cdwp.menu_bar ( p_level => 2 , p_el_short_name=> 'TBL' , p_el_id => null ); cdwp.heading(2); cdwp.p ( cdwp.add_images('{'||cdwpbase.get_icon('TBL')||'}') ||ROB_msg.getMsg(Rob_msg.CAP0188_CDWP_TABLEDEF, '', '', '', '') , p_style => 'reptitle' ); htp.nl; htp.nl; cdwp.tableOpen; cdwpbase.get_ivid_list ( p_app_id => odwactxt.get_folder_irid , p_ci_view_name => 'CI_TABLE_DEFINITIONS' , p_order_by => 'NAME' , p_descriptor_column => 'NAME' ); for i in 1..cdwpbase.g_ividTab.count loop cdwp.tableRowOpen; cdwp.TableDataHeading(null); cdwp.TableDataValue ( cdwpbase.local_link ( p_bookmark => cdwpbase.g_nameTab(i) , p_text => '{gif}'||cdwpbase.g_nameTab(i) , p_el_id => cdwpbase.g_iridTab(i) , p_type_of => 'TBL' ) ); cdwp.tableRowClose; end loop; -- r_tbl cdwp.tableClose; htp.nl; htp.nl; else cdwpbase.get_ivid_list ( p_ivid => nvl( p_ivid, cdwpbase.get_ivid( p_irid)) ); end if; -- odwactxt.get_one_file = 'Y' for i in 1..cdwpbase.g_ividTab.count loop open c_tbl( b_tbl_ivid => cdwpbase.g_ividTab(i)); fetch c_tbl into r_tbl; close c_tbl; odwactxt.update_context ( P_PAC_IRID => r_tbl.tbl_irid , P_PAC_IvID => r_tbl.tbl_ivid , p_new_request => false ); g_tbl_name:=r_tbl.tbl_name; g_tbl_id :=r_tbl.tbl_id; cdwp.bookmark(r_tbl.tbl_name); cdwp.menu_bar ( p_level => 2 , p_el_short_name => 'TBL' , p_el_id => r_tbl.tbl_id ); cdwp.heading(3); cdwp.p ( cdwp.add_images('{'||cdwpbase.get_icon('TBL')||'}') ||r_tbl.tbl_name , p_style => 'partitle' ); htp.nl; icon_bar ( p_tbl_irid => r_tbl.tbl_irid , p_tbl_ivid => r_tbl.tbl_ivid ); htp.nl; cdwp.tableOpen('BORDER="0"'); odwavrsn.version_status; cdwpbase.app_summary ( p_el_id => r_tbl.tbl_id ); cdwp.print_property( p_property_name => ROB_msg.getMsg(Rob_msg.CAP029_CDWP_ALIAS, '', '', '', ''),p_property_value => r_tbl.tbl_alias); cdwp.print_property( p_property_name => ROB_msg.getMsg(Rob_msg.CAP025_CDWP_DISPTITLE, '', '', '', ''),p_property_value => r_tbl.tbl_display_title); cdwp.print_property( p_property_name => ROB_msg.getMsg(Rob_msg.CAP0195_CDWP_JTABLE, '', '', '', ''),p_property_value => r_tbl.tbl_journal_location); cdwp_txt.display_mlt ( p_el_id => r_tbl.tbl_id ,p_txt_type => 'CDIDSC' ,p_mlt_prompt_name => null ,p_replace_entities => true ,p_replace_tables => true ,p_max_lines => null ,p_el_type => 'TBL' ); cdwp.tableClose; htp.nl; htp.nl; list_tabent; -- VOLUMES if nvl( r_tbl.tbl_initial_number_of_rows, r_tbl.tbl_maximum_number_of_rows) is not null then cdwp.tableOpen('BORDER="0"'); cdwp.tableRowOpen; cdwp.TableDataHeading(ROB_msg.getMsg(Rob_msg.CAP0194_CDWP_VOLUME, '', '', '', '')); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0193_CDWP_STARTROWS, '', '', '', ''), 1); cdwp.TableDataValue(r_tbl.tbl_initial_number_of_rows, 1); cdwp.tableRowClose; cdwp.tableRowOpen; cdwp.TableDataHeading(''); cdwp.TableDataValue(ROB_msg.getMsg(Rob_msg.CAP0192_CDWP_ENDROWS, '', '', '', ''), 1); cdwp.TableDataValue(r_tbl.tbl_maximum_number_of_rows, 1); cdwp.tableRowClose; cdwp.tableClose; htp.nl; end if; -- one of the volume properties is not null summary_column_definitions; summary_oco_definitions; summary_ind_definitions; summary_brdd_definitions; summary_trg_definitions; list_vwtab; incoming_fco; summary_modtab_definitions; list_pco_definitions; list_uco_definitions; list_fco_definitions; list_cco_definitions; list_column_definitions; /* cdwp_dia.list_in_diagrams ( p_app_name => p_app_name , p_app_version => p_app_version , p_el_id => r_tbl.tbl_id , p_one_file => p_one_file , p_static => p_static ); */ list_app_logic ( p_el_id => r_tbl.tbl_id , p_el_ivid => r_tbl.tbl_ivid , p_al_type => 'API' ); cdwp.bookmark('TAB_MLT'||to_char(r_tbl.tbl_id)); -- BOOKMARK if cdwp.exists_mlt( p_id => r_tbl.tbl_id, p_not_include1 => 'CDIDSC') then hwsp.fontOpen('Book Antiqua'); htp.bold(cdwp.add_images('{mlt.gif}') ||ROB_msg.getMsg(Rob_msg.DSP259_CDWP_MLTF, g_tbl_name, '', '', '')); hwsp.fontClose; htp.nl; htp.nl; cdwp.tableOpen('BORDER="0"'); cdwp.tableRowOpen; cdwp_txt.display_all_mlt (p_el_id => r_tbl.tbl_id ,p_max_lines => 3 ,p_do_not_display1 => 'CDIDSC' ,p_el_type => 'TBL' ); cdwp.tableRowClose; cdwp.tableClose; end if; -- cdwp.exists_mlt end loop; -- i in 1..cdwpbase.g_irdTab.count cdwpbase.write_timer; htp.formClose; htp.bodyClose; htp.htmlClose; end list_table_definitions; begin null; end; /