rem
rem $Header: L:\\\\model\\repman40\\api\\RCS\\citbl.hpb 1.4 1998/10/14 14:09:29 jkchow Exp $
rem
Rem  Copyright (c) 1994 by Oracle Corporation
Rem    NAME
Rem      citbl.hpb - <one-line expansion of the name>
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem    RETURNS
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    MODIFIED   (MM/DD/YY)
Rem     kparthas   08/28/03 -  B3114688:Updated the assertion Number
Rem     kparthas   08/05/03 -  Add Assertion for Global Temporary Tables feature
Rem     jwetherb   10/07/94 -  Add log_change for UID constraint
Rem     aheath     07/08/94 -  Creation
REM ***************************************************************************
REM   Hand-carved package body for Table Definition created on 14-FEB-94
REM ***************************************************************************
CREATE OR REPLACE PACKAGE BODY cihtbl IS

  -- Generic pre-process routines
  PROCEDURE check_delete(id rm.reference);

  pac_type constant varchar2(6) := 'TAB';

--======================== PRE-PROCESS (INS,UPD) ============================--

PROCEDURE pre_process(operation varchar2,id rm.reference,
        pl in out NOCOPY ciotable_definition.data)
IS
dummy number;
old_obj_type_ref number;
BEGIN
  -- Access control checks
-- Access rights checking performed by triggers in config 4.0.9
--  ciiacc.check_pac(pl.v.id,pac_type,operation);

  -- Validate user-defined properties
  if cieval.is_extended then
    cieval.check_element(id,pl.v.types,(operation='UPD')
      ,pl.v.user_defined_property_0,pl.i.user_defined_property_0
      ,pl.v.user_defined_property_1,pl.i.user_defined_property_1
      ,pl.v.user_defined_property_2,pl.i.user_defined_property_2
      ,pl.v.user_defined_property_3,pl.i.user_defined_property_3
      ,pl.v.user_defined_property_4,pl.i.user_defined_property_4
      ,pl.v.user_defined_property_5,pl.i.user_defined_property_5
      ,pl.v.user_defined_property_6,pl.i.user_defined_property_6
      ,pl.v.user_defined_property_7,pl.i.user_defined_property_7
      ,pl.v.user_defined_property_8,pl.i.user_defined_property_8
      ,pl.v.user_defined_property_9,pl.i.user_defined_property_9
      ,pl.v.user_defined_property_10,pl.i.user_defined_property_10
      ,pl.v.user_defined_property_11,pl.i.user_defined_property_11
      ,pl.v.user_defined_property_12,pl.i.user_defined_property_12
      ,pl.v.user_defined_property_13,pl.i.user_defined_property_13
      ,pl.v.user_defined_property_14,pl.i.user_defined_property_14
      ,pl.v.user_defined_property_15,pl.i.user_defined_property_15
      ,pl.v.user_defined_property_16,pl.i.user_defined_property_16
      ,pl.v.user_defined_property_17,pl.i.user_defined_property_17
      ,pl.v.user_defined_property_18,pl.i.user_defined_property_18
      ,pl.v.user_defined_property_19,pl.i.user_defined_property_19
      );
  end if;

  -- Initialize unassigned properties with default values on INSERT
  if operation = 'INS' then
    -- Assign miscellaneous initial default values
    pl.v.element_type_name := pac_type;
    pl.i.element_type_name := true;
    pl.v.table_type := 'TABLE';
    pl.i.table_type := true;
    if pl.v.data_persistence is null then
       pl.v.data_persistence := 'PERMANENT';
       pl.i.data_persistence := true;
    end if;
  end if;

  -- Assign denormalized and derived property values
--#BEGIN
  if pl.i.name then
    -- replace spaces with underscores
    remove_spaces(pl.v.name); --B631806
  end if;
  if pl.i.column_prefix then
    -- replace spaces with underscores
    pl.v.column_prefix := translate(pl.v.column_prefix,' ','_');
  end if;
  if pl.i.alias then
    -- replace spaces with underscores
    remove_spaces(pl.v.alias); --B631806
    -- Check that the alias is unique across Table, View and Snapshot alias
    -- for the application system
    -- Check now done in appcon.mpb
  end if;
--#END

  if operation = 'UPD' and pl.i.oracle_object_type_reference then

    -- Get the existing oracle object type reference
    -- This tells us if the table is object or relational
    select tab.oracle_object_type_ref
      into old_obj_type_ref
      from sdd_tab tab
     where tab.irid = pl.v.id;

    -- Trying to change table from object to relational - not allowed
    if old_obj_type_ref is not null and pl.v.oracle_object_type_reference is null then
      ciierr.fatal(1194,ciiutl.identify(pl.v.id, pl.v.types));
    end if;

    -- Trying to change table from relational to object - not allowed
    if old_obj_type_ref is null and pl.v.oracle_object_type_reference is not null then
      ciierr.fatal(1194,ciiutl.identify(pl.v.id, pl.v.types));
    end if;

  end if;
END;

--======================== PRE-PROCESS (DEL,SEL) ============================--

PROCEDURE pre_process(operation varchar2,id rm.reference)
IS
BEGIN
  -- access control checks
-- Access rights checking performed by triggers in config 4.0.9
--  ciiacc.check_pac(id,pac_type,operation);

  -- if operation is 'DELETE', check that non-cascade references don't exist
  if operation = 'DEL' then
    check_delete(id);
  end if;
END;

--===================== POST-PROCESS (INS,UPD,SEL) ==========================--

PROCEDURE post_process(operation varchar2,id rm.reference,
  pl ciotable_definition.data)
IS
  dummy number;
BEGIN
  if operation = 'INS' then
    ciiutl.create_appxxx(pac_type,pl.v.id,pl.v.name);
  end if;
--#BEGIN
  if pl.i.name or pl.i.alias then
    if operation = 'UPD' then
      cioapp_con_interface.traverse('CIHAPPCONU1',id,'APPTAB');
    end if;
    if pl.i.name then 
      -- check name is a valid ORACLE name
      if is_name_valid(pl.v.name) then
        ciierr.fatal(1003,ciiutl.identify(pl.v.id, pl.v.types),pl.v.name);
      end if;
    end if;
    if pl.i.alias then
      --Check that alias is a valid ORACLE name
      if is_name_valid(pl.v.alias) then
        ciierr.fatal(1003,ciiutl.identify(pl.v.id, pl.v.types),pl.v.alias);
      end if;
    end if;
  end if;
  -- Added for Global Temporary Table feature.
  if operation = 'UPD' then
    if pl.i.data_persistence and pl.v.data_persistence != 'PERMANENT' then
       select count(*) into dummy from ci_foreign_key_constraints fk where fk.table_reference = pl.v.id or fk.foreign_table_reference = pl.v.id;
       if dummy > 0 then
         ciierr.fatal(1439,ciiutl.identify(pl.v.id, pl.v.types),pl.v.alias);
       end if;
     end if;
  end if;
--#END
END;

--========================= POST PROCESS (DEL) ==============================--

PROCEDURE post_process(operation varchar2,id rm.reference)
IS
BEGIN
  if operation = 'DEL' then
    -- Delete the Table Definition and all it's related sub-bits
    ciiutl.delete_element(id,pac_type);
  end if;
END;



--============================= CHECK DELETE ================================--

PROCEDURE check_delete(id rm.reference)
IS
  dummy number;
  error_count number := 0;
BEGIN
  -- 4.0.12 Removed all simple delete checking
  -- Removed check if table definition is connected to other application systems
  -- as this is performed by the registration triggers
  -- Check if any MTIs are connected to me
  -- Check if any ITMs are connected to me
  -- Check if any VWTABs are connected to me
  -- Check if any FCOs are connected to me
  --                             (that I don't own - you can delete pig's ears)
  select count(*) into dummy from ci_foreign_key_constraints
    where foreign_table_reference = check_delete.id
      and table_reference != check_delete.id;
  if dummy > 0 then
    ciierr.post(101,ciiutl.identify(id, 'TBL'), ciiutl.nls_type_plural_name_s('FCO'),
                                         ciiutl.nls_type_name_s('TBL'));
    error_count := 1;
  end if;
  --
  -- Check if any COLs are connected to me that are the source of a
  --          denormailsed column in another table.
  select count(*) into dummy from ci_columns c1
    where c1.table_reference = check_delete.id
      and exists (select null from ci_columns c2
                   where c2.column_denormalised_from = c1.id
                     and c2.table_reference != check_delete.id);
  if dummy > 0 then
    ciierr.post(101,ciiutl.identify(id, 'TBL'), ciiutl.nls_type_plural_name_s('COL'),
                                         ciiutl.nls_type_name_s('TBL'));
    error_count := 1;
  end if;

  if error_count > 0 then
    raise cdapi.apierror;
  end if;
END;

--======================== CHECK FOR INVALID ENTRY FUNCTION ===============--

FUNCTION is_name_valid (name varchar2, is_plsql_mod boolean default false)
return boolean
IS
  dummy number;
  language varchar2(30);
BEGIN
  -- Check that name is enclosed in quotes
  if substr(name,1,1) = '"' and substr(name,(length(name)),1) = '"' then
    return false;
  end if;
  --Check that name is not a reserved word
  --First check if the object is a plsql module (no longer used)
  --if is_plsql_mod then
  --  select count(*) into dummy from ref_values
  --    where ref_domain like '%RESERVED%'
  --      and ref_code = upper(name);
  --  if dummy > 0 then
  --    return true;
  --  end if;
  --else
    select count(*) into dummy from ref_values
      where ref_domain = 'RESERVED_NAMES'
        and ref_code = upper(name);
    if dummy > 0 then
      return true;
    end if;
  --end if;
  --The following checks can only be made when the National Language is American


  -- Removed due to Bug 1308557 NLS: MULTIBYTE CHAR IS REFUSED WHILE COMMITING IN CREATING TABLE DEFINITIONS
  -- select value into language from nls_session_parameters
  --   where parameter = 'NLS_LANGUAGE';
  -- if language = 'AMERICAN' then
  --   --Check that name does not contain illegal characters
  --   if (length(translate (upper(name), '*ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789$#_','*'))) > 0 then
  --     return true;
  --   end if;
  --   -- Check name begins with a letter
  --   if ((substr(upper(name),1,1)) not in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')) then
  --     return true;
  --   end if;
  -- end if;
  return false;
end;


--==============================================================================B631806
 PROCEDURE remove_spaces(p_name IN OUT varchar2) IS
 BEGIN
    if substr(p_name,1,1) = '"' and substr(p_name,(length(p_name)),1) = '"'
  then
       null;
  else
     p_name := translate(p_name,' ','_');
  end if;
 END;

--==============================================================================
 FUNCTION index_only_flag(tab_ref in number)
 return varchar2
 IS
   index_only_flag varchar2(10);
 BEGIN
   select nvl(tab.index_only_flag, 'N')
     into index_only_flag
     from sdd_tab tab
    where tab.irid = tab_ref;

   return(index_only_flag);
 END;

--================================== END ====================================--
--
-- Package instantiation block
--
BEGIN
  is_installed := true;
END;
/