set define '^'
set verify off
prompt ...wwv_flow_hint


Rem    NAME
Rem      wwv_flow_hint.sql
Rem    Arguments:
Rem     1:  
Rem     2:
Rem     3:  Flow user
Rem    MODIFIED (MM/DD/YYYY)
Rem     cbcho    04/02/2003 - Bug 2882452: Created
Rem     cbcho    04/09/2003 - Bug 2882452: Added get_display_seq_report
Rem     cbcho    04/10/2003 - Bug 2882452: Added create_table_hint, create_column_hint, remove_table_hint
Rem     cbcho    04/11/2003 - Bug 2882452: Added more functions to get friendly names for forms
Rem     cbcho    04/11/2003 - Bug 2882452: Removed obsolete column and added new column reference in create_column_hint
Rem     cbcho    04/24/2003 - Added Sharon's default_hints procedure
Rem     sbk      05/06/2003 - Changed default_hints to synch_hints
Rem     cbcho    05/06/2003 - Added table_hint_exists and column_hint_exists
Rem     sbk      06/02/2003 - Added mask_form column to create column, renamed column mask to mask_report
Rem                         - renamed get_mask to get_report_mask, added get_form_mask
Rem     cbcho    06/03/2003 - Added procedure create_normalize_hint

Rem
Rem     sbk      05/25/2004 - Changes to support version 1.6
Rem                         - removed get_region_title, replace with get_report_region_title and get_form_region_title
Rem                         - added get_display_as_tab_form, get_display_as_report
Rem                         - changed create_table_hint to remove region_title, 
Rem                                   added report_region_title and form_region_title
Rem                         - changed create_column_hint to add display_as_tab_form and display_as_report
Rem                         - changed synch_hints to default group_by to N (rather than Y) - requested by CBCho
Rem                         - changed synch_hints to reflect updated create_table_hint procedure
Rem                         - added create_lov_data
Rem     sbk      05/28/2004 - put back in get_region_title for backward compatibility
Rem     sbk      06/02/2004 - added get_lov to retrieve either query or column_id if static lov
Rem     sbk      06/04/2004 - added get_aggregate_by, modified create_column_hint to add aggregate_by
Rem     sbk      06/07/2004 - added display_cons_columns
Rem                         - added gen_lov_name
Rem     sbk      06/10/2004 - added get_table_ui_defaults and get_col_ui_defaults
Rem     sbk      06/11/2004 - defaulted globals to null, added global to allow users to not use defaults
Rem                         - added remove_hint
Rem                         - added check_table function, returns N if no UI Defaults, S if need to synch table, F if table fine
Rem     sbk      06/15/2004 - added get_static_lov_string
Rem     sbk      06/22/2004 - added check_schema_privs
Rem                         - added create_table_hint_priv, create_column_hint_priv, create_lov_data_priv, remove_hint_priv
Rem                                 These ensure that the user has privilge on the schema they are creating for
Rem     sbk      06/28/2004 - added check_lov_issues to display warning when display as requires lov but no lov provided
Rem     sbk      09/13/2004 - added back in create_normalize_hint - it was somehow dropped
Rem     sbk      09/19/2007 - added update_table_hint, update_column_hint, update_lov_data, remove_column_hint, remove_lov_data

create or replace package wwv_flow_hint
as
--  Copyright (c) Oracle Corporation 1999 - 2004. All Rights Reserved.
--
--
--    DESCRIPTION
--      This package is used to get friendly name attributes from following tables:
--          wwv_flow_hnt_table_info
--          wwv_flow_hnt_column_info
--      This package can be used to better create meaningful forms and reports and other flows contructs.
--
--    NOTES
--      
--      
--    SECURITY
--      No grants, must be run as FLOW schema owner.
--
--    NOTES
--
--    INTERNATIONALIZATION
--      unknown
--
--    MULTI-CUSTOMER
--      unknown
--
--    CUSTOMER MAY CUSTOMIZE
--      NO
--
--    RUNTIME DEPLOYMENT: YES
--

-- Used in get_table_ui_defaults and get_col_ui_defaults

    g_form_region_title    varchar2(255)  := null;
    g_report_region_title  varchar2(255)  := null;

    g_column_id            number         := null;
    g_label                varchar2(255)  := null;
    g_help_text            varchar2(4000) := null;
    g_mask_form            varchar2(255)  := null;
    g_display_seq_form     number         := null;
    g_display_in_form      varchar2(1)    := null;
    g_display_as_form      varchar2(50)   := null;
    g_display_as_tab_form  varchar2(255)  := null;
    g_display_seq_report   number         := null;
    g_display_in_report    varchar2(1)    := null;
    g_display_as_report    varchar2(255)  := null;
    g_mask_report          varchar2(255)  := null;
    g_aggregate_by         varchar2(1)    := null;
    g_lov_query            varchar2(4000) := null;
    g_default_value        varchar2(255)  := null;
    g_required             varchar2(1)    := null;
    g_alignment            varchar2(1)    := null;
    g_display_width        number         := null;
    g_max_width            number         := null;
    g_height               number         := null;
    g_group_by             varchar2(1)    := null;
    g_searchable           varchar2(1)    := null;
    g_lov_type             varchar2(1)    := null;

-- Setting the following global to true makes all the functions and procedures pull
--   the UI Default values.  If the global is set to false (the user running the wizard
--   decided to override and not use UI Defaults), null is returned for the values.

    g_use_ui_defaults      boolean        := true;

-- The following global is used in check_schema_privs to ensure that, on import,
--    the user has privilege on the schema they are importing.

    g_schema               varchar2(30)   := null;


function table_hint_exists (
    p_schema      in varchar2,
    p_table_name  in varchar2
    ) return boolean;
    
function column_hint_exists (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return boolean; 

-- sbk, 06/11/04: This column is no longer used but function left in for now
function get_region_title (
    p_schema      in varchar2,
    p_table_name  in varchar2
    ) return varchar2;

function get_report_region_title (
    p_schema      in varchar2,
    p_table_name  in varchar2
    ) return varchar2;

function get_form_region_title (
    p_schema      in varchar2,
    p_table_name  in varchar2
    ) return varchar2;
            
function get_item_help (
    p_schema      in varchar2,
    p_table_name  in varchar2,
    p_column_name in varchar2
    ) return varchar2;
    
function get_report_mask (
    p_schema      in varchar2,
    p_table_name  in varchar2,
    p_column_name in varchar2
    ) return varchar2;

function get_form_mask (
    p_schema      in varchar2,
    p_table_name  in varchar2,
    p_column_name in varchar2
    ) return varchar2;

function get_alignment (
    p_schema      in varchar2,
    p_table_name  in varchar2,
    p_column_name in varchar2
    ) return varchar2;
    
function get_searchable (
    p_schema      in varchar2,
    p_table_name  in varchar2,
    p_column_name in varchar2
    ) return varchar2;        
    
function get_required (
    p_schema      in varchar2,
    p_table_name  in varchar2,
    p_column_name in varchar2
    ) return varchar2;

function get_label (
    p_schema      in varchar2,
    p_table_name  in varchar2,
    p_column_name in varchar2
    ) return varchar2;

function get_display_in_report (
    p_schema      in varchar2,
    p_table_name  in varchar2,
    p_column_name in varchar2
    ) return varchar2;

function get_display_seq_report (
    p_schema      in varchar2,
    p_table_name  in varchar2,
    p_column_name in varchar2
    ) return number;
        
function get_group_by (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return varchar2;

-- sbk, 06/11/04 - column not used for 1.5 or 1.6
function get_order_by_seq (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return number;

-- sbk, 06/11/04 - column not used for 1.5 or 1.6
function get_order_by_asc_desc (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return number;                    

function get_display_in_form (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return varchar2;
    
function get_display_seq_form (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return number;
    
function get_display_as_form (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return varchar2;
    
function get_lov_query (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return varchar2;

procedure get_lov (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2,
    p_lov_type     out varchar2,
    p_lov_query    out varchar2,
    p_column_id    out number
    );

function get_static_lov_string (
    p_column_id   in number
    ) return varchar2;
    
function get_default_value (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return varchar2;
    
function get_display_width (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return number;
    
function get_max_width (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return number;
    
function get_height (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return number;

function get_display_as_tab_form (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return varchar2;

function get_display_as_report (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return varchar2;

function get_aggregate_by (
    p_schema       in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2
    ) return varchar2;


procedure create_table_hint (   
    p_table_id             in number   default null,
    p_schema               in varchar2 default null,
    p_table_name           in varchar2 default null,
    p_form_region_title    in varchar2 default null,
    p_report_region_title  in varchar2 default null
    );
    
procedure create_column_hint ( 
    p_column_id           in number   default null,
    p_table_id            in number   default null,
    p_column_name         in varchar2 default null,
    p_label               in varchar2 default null,
    p_help_text           in varchar2 default null,
    p_mask_form           in varchar2 default null,
    p_display_seq_form    in number   default null,
    p_display_in_form     in varchar2 default null,
    p_display_as_form     in varchar2 default null,
    p_display_as_tab_form in varchar2 default null,
    p_display_seq_report  in number   default null,
    p_display_in_report   in varchar2 default null,
    p_display_as_report   in varchar2 default null,
    p_mask_report         in varchar2 default null,
    p_aggregate_by        in varchar2 default null,
    p_lov_query           in varchar2 default null,
    p_default_value       in varchar2 default null,
    p_required            in varchar2 default null,
    p_alignment           in varchar2 default null,
    p_display_width       in number   default null,
    p_max_width           in number   default null,
    p_height              in number   default null,
    p_group_by            in varchar2 default null,
    p_order_by_seq        in number   default null,
    p_order_by_asc_desc   in varchar2 default null,
    p_searchable          in varchar2 default null  
    );

procedure create_lov_data (
    p_id                 in number      default null,
    p_column_id          in number      default null,
    p_lov_disp_sequence  in number      default null,
    p_lov_disp_value     in varchar2    default null,
    p_lov_return_value   in varchar2    default null
    );
    
procedure remove_table_hint (
    p_table_id    in number   default null
    ); 

procedure remove_hint (
    p_schema          in varchar2 default null,
    p_table_name      in varchar2 default null
    );  
    
procedure synch_hints (
    p_schema          in varchar2 default null,
    p_table_name      in varchar2 default null
    );

function display_cons_columns (
    p_owner           in varchar2,
    p_table_name      in varchar2,
    p_constraint_name in varchar2
    ) return varchar2;

function gen_lov_name (
    p_owner        in varchar2,
    p_table_name   in varchar2,
    p_column_name  in varchar2,
    p_sgid         in number,
    p_app_number   in number,
    p_page_number  in number
    ) return varchar2;


procedure get_table_ui_defaults (   
    p_schema               in  varchar2,
    p_table_name           in  varchar2
    );
    
procedure get_col_ui_defaults ( 
    p_schema              in  varchar2,
    p_table_name          in  varchar2,
    p_column_name         in  varchar2
    );

function check_table (
    p_schema      in varchar2,
    p_table_name  in varchar2
    ) return varchar2;

procedure check_schema_privs;

procedure create_table_hint_priv (   
    p_table_id             in number   default null,
    p_schema               in varchar2 default null,
    p_table_name           in varchar2 default null,
    p_form_region_title    in varchar2 default null,
    p_report_region_title  in varchar2 default null
    );
    
procedure create_column_hint_priv ( 
    p_column_id           in number   default null,
    p_table_id            in number   default null,
    p_column_name         in varchar2 default null,
    p_label               in varchar2 default null,
    p_help_text           in varchar2 default null,
    p_mask_form           in varchar2 default null,
    p_display_seq_form    in number   default null,
    p_display_in_form     in varchar2 default null,
    p_display_as_form     in varchar2 default null,
    p_display_as_tab_form in varchar2 default null,
    p_display_seq_report  in number   default null,
    p_display_in_report   in varchar2 default null,
    p_display_as_report   in varchar2 default null,
    p_mask_report         in varchar2 default null,
    p_aggregate_by        in varchar2 default null,
    p_lov_query           in varchar2 default null,
    p_default_value       in varchar2 default null,
    p_required            in varchar2 default null,
    p_alignment           in varchar2 default null,
    p_display_width       in number   default null,
    p_max_width           in number   default null,
    p_height              in number   default null,
    p_group_by            in varchar2 default null,
    p_order_by_seq        in number   default null,
    p_order_by_asc_desc   in varchar2 default null,
    p_searchable          in varchar2 default null  
    );

procedure create_lov_data_priv (
    p_id                 in number      default null,
    p_column_id          in number      default null,
    p_lov_disp_sequence  in number      default null,
    p_lov_disp_value     in varchar2    default null,
    p_lov_return_value   in varchar2    default null
    );
    

procedure remove_hint_priv (
    p_schema          in varchar2 default null,
    p_table_name      in varchar2 default null
    );

function check_lov_issues (
    p_table_id in number
    ) return varchar2;  

procedure create_normalize_hint (
    p_schema      in varchar2,
    p_table_name  in varchar2,
    p_column_name in varchar2,
    p_lov_query   in varchar2
    ); 

procedure update_table_hint (   
    p_table_id             in number   default null,
    p_schema               in varchar2 default null,
    p_table_name           in varchar2 default null,
    p_form_region_title    in varchar2 default null,
    p_report_region_title  in varchar2 default null
    );
    
procedure update_column_hint ( 
    p_column_id           in number   default null,
    p_table_id            in number   default null,
    p_column_name         in varchar2 default null,
    p_label               in varchar2 default null,
    p_help_text           in varchar2 default null,
    p_mask_form           in varchar2 default null,
    p_display_seq_form    in number   default null,
    p_display_in_form     in varchar2 default null,
    p_display_as_form     in varchar2 default null,
    p_display_as_tab_form in varchar2 default null,
    p_display_seq_report  in number   default null,
    p_display_in_report   in varchar2 default null,
    p_display_as_report   in varchar2 default null,
    p_mask_report         in varchar2 default null,
    p_aggregate_by        in varchar2 default null,
    p_lov_query           in varchar2 default null,
    p_default_value       in varchar2 default null,
    p_required            in varchar2 default null,
    p_alignment           in varchar2 default null,
    p_display_width       in number   default null,
    p_max_width           in number   default null,
    p_height              in number   default null,
    p_group_by            in varchar2 default null,
    p_order_by_seq        in number   default null,
    p_order_by_asc_desc   in varchar2 default null,
    p_searchable          in varchar2 default null  
    );

procedure update_lov_data (
    p_id                 in number      default null,
    p_column_id          in number      default null,
    p_lov_disp_sequence  in number      default null,
    p_lov_disp_value     in varchar2    default null,
    p_lov_return_value   in varchar2    default null
    );

procedure remove_column_hint (
    p_column_id  in number   default null
    );

procedure remove_lov_data (
    p_id  in number   default null
    );
 
end wwv_flow_hint;
/
show errors;