rem
rem $Header: L:\\\\model\\repman40\\api\\RCS\\ciexp.ipb 1.5 1998/11/25 16:26:20 cvanes Exp $
rem
Rem  Copyright (c) 1994 by Oracle Corporation
Rem    NAME
Rem      ciexp.ipb - <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     aheath     03/23/95 -  Change datatypes of exp8 arguments
Rem     jwetherb   10/14/94 -  Enhance exp3
Rem     jwetherb   10/13/94 -  Add exp8
Rem     jwetherb   10/06/94 -  Add exp1
Rem     jwetherb   09/03/94 -  Changed entity -> external in exp4/exp5
Rem     aheath     07/11/94 -  Creation
CREATE OR REPLACE PACKAGE BODY ciiexp IS

FUNCTION exp1(denormalized_summary_function varchar2,datatype varchar2,
  denorm_datatype varchar2,maximum_length number,denorm_maximum_length number,
  decimal_places number,denorm_decimal_places number)
RETURN boolean
IS
BEGIN
  if denormalized_summary_function in ('MAX','MIN') then
    if maximum_length < denorm_maximum_length or
       decimal_places < denorm_decimal_places then
      return(false);
    elsif datatype in ('NUMBER','INTEGER','FLOAT','SMALLINT','REAL',
        'DOUBLE PRECISION','DECIMAL') then
      return(denorm_datatype in ('NUMBER','INTEGER','FLOAT','SMALLINT','REAL',
        'DOUBLE PRECISION','DECIMAL'));
    elsif datatype in ('RAW','LONG_RAW') then
      return(denorm_datatype in ('RAW','LONG_RAW'));
    elsif datatype in ('DATE','TIME','TIMESTAMP') then
      return(denorm_datatype in ('DATE','TIME','TIMESTAMP'));
    elsif datatype in ('CHAR','VARCHAR','LONG','LONG VARCHAR','VARCHAR2') then
      return(denorm_datatype in ('CHAR','VARCHAR','LONG','LONG VARCHAR',
  'VARCHAR2'));
    else
      return(true);
    end if;
  elsif denormalized_summary_function in ('SUM','AVG','STDDEV','VARIANCE') then
    return(datatype in ('NUMBER','INTEGER','FLOAT','SMALLINT','REAL',
      'DOUBLE PRECISION','DECIMAL') and
           denorm_datatype in ('NUMBER','INTEGER','FLOAT','SMALLINT','REAL',
                               'DOUBLE PRECISION','DECIMAL'));
  else
    return(true);
  end if;
END;
FUNCTION exp2(triggered_function_reference number,
  triggering_event_reference number,
  triggered_event_reference number,
  triggering_function_reference number)
RETURN boolean
IS
BEGIN
  -- This is enforced by registered foreign key triggers
  return(true);
END;

FUNCTION exp3(source_type varchar2,dest_type varchar2,
  func_source_parent number,func_dest_parent number)
RETURN boolean
IS
BEGIN
  return(source_type is null or dest_type is null or
   source_type != 'FUNCTION' or dest_type != 'FUNCTION' or
         (func_source_parent is null and func_dest_parent is null) or
   func_source_parent = func_dest_parent);
END;

FUNCTION exp4(id number,dummy varchar2)
RETURN boolean
IS
  s_ref number;
  s_type varchar2(3);
BEGIN
  select nvl(nvl(external_source_reference,function_source_reference),
    datastore_source_reference),
  decode(source_type,
    'EXTERNAL','EXT',
    'FUNCTION','FUN',
    'DATASTORE','DST')
  into s_ref,s_type
  from ci_dataflows
  where id = exp4.id;

  return(ciiutl.in_context_appsys(s_ref,s_type));
EXCEPTION
  when no_data_found then
    return(true);
END;

FUNCTION exp5(id number,dummy varchar2)
RETURN boolean
IS
  d_ref number;
  d_type varchar2(3);
BEGIN
  select nvl(nvl(external_dest_reference,function_dest_reference),
          datastore_dest_reference),
        decode(destination_type,
    'EXTERNAL','EXT',
    'FUNCTION','FUN',
    'DATASTORE','DST')
  into d_ref,d_type
  from ci_dataflows
  where id = exp5.id;

  return(ciiutl.in_context_appsys(d_ref,d_type));
EXCEPTION
  when no_data_found then
    return(true);
END;

FUNCTION exp6(increment_value number,maximum_value number,minimum_value number)
RETURN boolean
IS
  dummy number;
BEGIN
  select count(*) into dummy from sys.dual
    where nvl(increment_value,1)
    <= (nvl(maximum_value,
     decode(sign(nvl(increment_value,1)),1,1.0E+27,-1))
          - nvl(minimum_value,
     decode(sign(nvl(increment_value,1)),1,1,-1.0E+26)));
  if dummy = 1 then
    return(true);
  else
    return(false);
  end if;
END;

FUNCTION exp7(increment_value number,maximum_value number,minimum_value number,
  cache_value number)
RETURN boolean
IS
  dummy number;
BEGIN
  select count(*) into dummy from sys.dual
    where to_number(nvl(cache_value,20))
    <= ceil( ( nvl(maximum_value,
             decode(sign(nvl(increment_value,1)),1,1.0E+27,-1))
                   - nvl(minimum_value,
             decode(sign(nvl(increment_value,1)),1,1,-1.0E+26)))
                 / abs(nvl(increment_value,1)));
  if dummy = 1 then
    return(true);
  else
    return(false);
  end if;
END;

FUNCTION exp8(cluster_column_datatype varchar2,cluster_column_length number,
  cluster_column_scale number,column_datatype varchar2,
  column_maximum_length number,column_decimal_places number)
RETURN boolean
IS
BEGIN
  return((nvl(cluster_column_datatype,column_datatype) is null or
    cluster_column_datatype = column_datatype) and
         (nvl(cluster_column_length,column_maximum_length) is null or
    cluster_column_length = column_maximum_length) and
   (nvl(cluster_column_scale,column_decimal_places) is null or
    cluster_column_scale = column_decimal_places));
END;

-- Note only useful for SACs who have a SEQ based on el_seq and are
--  'within' el_within_id
FUNCTION default_seq(pac_id number, sac_type_of varchar2,
                     increment number default 5,
                     sac_occur_type varchar2 default 'XYZ')
RETURN number
IS
  new_seq number;
  pac_irid number;
  ret_val integer;
  big_str3 varchar2(1000);
  cur_id3 integer;
  seq_property_name varchar2(40);
  tab_name varchar2(40);
BEGIN
  -- Used to lock parent sdd_elements here - probably still should

  select min(pr.name)
    into seq_property_name
    from rm_properties pr
        ,rm_element_types et
   where pr.defined_against = et.id
     and et.short_name = sac_type_of
     and pr.name like 'SEQUENCE%';

  if seq_property_name is null then
    return null;
  end if;

  tab_name := ciiexp.get_table_name(sac_type_of);

  -- Sadly must ignore occur_type as we can't tell what property
  --  it is. This is OK at 404 (25 Aug 98) as only GIP calls here
  --  and LIP does not have a sequence.
  big_str3 := 'select nvl(max(' || seq_property_name || '),0) ' ||
    ' from ' || tab_name || ' sac ' ||
    'where sac.pac_ref = :pac_irid';
--  dbms_output.put_line(big_str3);
--  dbms_output.put_line('pac_id'||to_char(pac_id));
  cur_id3 := dbms_sql.open_cursor;
  dbms_sql.parse(cur_id3, big_str3, dbms_sql.native);
  dbms_sql.bind_variable(cur_id3, ':pac_irid', pac_id);
  dbms_sql.define_column(cur_id3, 1, new_seq);
  ret_val := dbms_sql.execute(cur_id3);
  if dbms_sql.fetch_rows(cur_id3) > 0 then
    dbms_sql.column_value(cur_id3, 1, new_seq);
  end if;
  dbms_sql.close_cursor(cur_id3);

  return(new_seq + increment);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    return null;
END;

FUNCTION default_str_seq(str_part_of number, str_use_of number, str_type_of varchar2,
                         increment number default 5, str_use_of_col varchar2 default NULL)
RETURN number
IS
  new_seq number;
  increment1 number;
  pac_irid number;
  ele_use_of number;
  ret_val integer;
  big_str3 varchar2(1000);
  cur_id3 integer;
  seq_property_name varchar2(40);
  tab_name varchar2(40);
BEGIN
  -- nasty hack to get round limitations of PL/SQL
  if (increment IS NULL) then
    increment1 := 5;
  else
    increment1 := increment;
  end if;


  -- Used to lock parent sdd_elements here - probably still should

  -- Unfortunate exception for MODMOD, see Bug 772888 for further details
  if (str_type_of = 'MODMOD') then
    seq_property_name := 'CALLED_SEQUENCE';
  else
    select min(pr.name)
      into seq_property_name
      from rm_properties pr
          ,rm_element_types et
     where pr.defined_against = et.id
       and et.short_name = str_type_of
       and pr.name like 'SEQUENCE%';
  end if;

  if seq_property_name is null then
    return null;
  end if;

  tab_name := ciiexp.get_table_name(str_type_of);

  big_str3 := 'select nvl(max(' || seq_property_name || '),0) ' ||
    ' from ' || tab_name || ' str ' ||
    'where str.pac_ref = NVL(:pac_irid, str.pac_ref)';
  --  Bug 930883 - need to continue to use str_use_of
  if (str_use_of_col IS NOT NULL) then
    big_str3 := big_str3 || ' and str.'||str_use_of_col||' = :ele_use_of';
  end if;
  rmdbg.trace('str_part_of: '||to_char(str_part_of));
  rmdbg.trace('str_use_of: '||to_char(str_use_of));
  rmdbg.trace('str_use_of_col: '||str_use_of_col);
  rmdbg.trace(big_str3);
  cur_id3 := dbms_sql.open_cursor;
  dbms_sql.parse(cur_id3, big_str3, dbms_sql.native);
  dbms_sql.bind_variable(cur_id3, ':pac_irid', str_part_of);
  if (str_use_of_col IS NOT NULL) then
    dbms_sql.bind_variable(cur_id3, ':ele_use_of', str_use_of);
  end if;
  dbms_sql.define_column(cur_id3, 1, new_seq);
  ret_val := dbms_sql.execute(cur_id3);
  rmdbg.trace('Ret Val : ' ||to_char(ret_val));
  if dbms_sql.fetch_rows(cur_id3) > 0 then
    rmdbg.trace('Fetching rows');
    dbms_sql.column_value(cur_id3, 1, new_seq);
    rmdbg.trace('New Seq: ' ||to_char(new_seq));
  end if;
  rmdbg.trace('Closing Cursor');
  dbms_sql.close_cursor(cur_id3);
  rmdbg.trace('New Seq: ' ||to_char(new_seq));
  rmdbg.trace('Increment: ' ||to_char(increment1));
  rmdbg.trace('Seq: '||to_char(new_seq + increment1));
  return(new_seq + increment1);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    return null;
END;


PROCEDURE insert_sequence_gap(element_type_plural_name varchar2,
                              sequence_property_name varchar2,
                              within_property_name varchar2,
                              before_after varchar2,
                              base_element_id number,
                              within_element_id number,
                              gap_size number)
IS
  string varchar2(2000);
  seq_operator varchar2(10); -- to find the sequence
  grp_operator1 varchar2(10); -- to find the sequence
  grp_operator2 varchar2(10); -- to find the sequence
  mod_operator varchar2(10); -- to modify the sequence
  ret integer;
  cur integer;
BEGIN

  -- if base_element_id is null then it acts as if the gap should
  --  be before the first element
  if before_after = 'BEFORE' or base_element_id is null then
    seq_operator := ' < ';
    mod_operator := ' >= ';
    grp_operator2 := ' max '; -- used to find the next element before me
  else
    seq_operator := ' > ';
    mod_operator := ' > ';
    grp_operator2 := ' min '; -- used to find the next element after me
  end if;

  -- This looks horrid!!, but when called with :
  -- ciiexp.insert_sequence_gap('ATTRIBUTES', 'SEQUENCE_NUMBER', 'ENTITY_REFERENCE',
  --  'AFTER', 1002, 1234, 35);
  --      will produce a string like :
  --
  --  declare  
  --    current_seq number;
  --    current_gap number;
  --  begin  
  --    select el1.SEQUENCE_NUMBER
  --      into current_seq
  --      from ci_ATTRIBUTES el1
  --     where el1.id = 1002;
  --     
  --    if current_seq is null then 
  --      return; 
  --    end if;   
  --    
  --    select abs(nvl( min (el1.SEQUENCE_NUMBER), 0)  - current_seq) -1
  --      into current_gap
  --      from ci_ATTRIBUTES el1
  --     where el1.ENTITY_REFERENCE = 1234
  --       and el1.SEQUENCE_NUMBER >  current_seq;  
  --       
  --    if current_gap >= 35 then 
  --      return; 
  --    end if;   
  --    
  --    update ci_ATTRIBUTES
  --       set SEQUENCE_NUMBER = SEQUENCE_NUMBER + 35 - current_gap
  --     where ENTITY_REFERENCE = 1234
  --       and SEQUENCE_NUMBER > current_seq;
  --  end;

  
  -- and when called with :
  -- ciiexp.insert_sequence_gap('ATTRIBUTES', 'SEQUENCE_NUMBER', 'ENTITY_REFERENCE',
  --  'AFTER', null, 1234, 35);
  --      the first query will be this instead:
  -- 
  -- select min(el1.SEQUENCE_NUMBER)
  --   into current_seq
  --   from ci_ATTRIBUTES el1
  --  where el1.ENTITY_REFERENCE = 1234
  --    and el1.SEQUENCE_NUMBER =
  --        (select min(el2.SEQUENCE_NUMBER)
  --           from ci_ATTRIBUTES el2
  --          where el2.ENTITY_REFERENCE = 1234);


  string :=
  'declare' ||
  '  current_seq number;' ||
  '  current_gap number;' ||
  'begin';
  if base_element_id is not null then
    string := string ||
  '  select el1.' || sequence_property_name ||
  '    into current_seq' ||
  '    from ci_' || element_type_plural_name || ' el1'  ||
  '   where el1.id = ' || base_element_id || ';';
  else
    string := string ||
  '  select min(el1.' || sequence_property_name || ')' ||
  '    into current_seq' ||
  '    from ci_' || element_type_plural_name || ' el1'  ||
  '   where el1.' || within_property_name || ' = ' || within_element_id ||
  '     and el1.' || sequence_property_name || ' = ' ||
  '    (select min(el2.' || sequence_property_name || ')' ||
  '       from ci_' || element_type_plural_name || ' el2'  ||
  '   where el2.' || within_property_name || ' = ' || within_element_id || ');';
  end if;
  string := string ||
  '  if current_seq is null then return; end if; ' ||
  '  select abs(nvl('|| grp_operator2 || '(el1.' || sequence_property_name ||
  '                                 ), 0)  - current_seq) -1 ' ||
  '    into current_gap' ||
  '    from ci_' || element_type_plural_name || ' el1'  ||
  '   where el1.' || within_property_name || ' = ' || within_element_id ||
  '     and el1.' || sequence_property_name || seq_operator ||
                                                        ' current_seq;' ||
  '  if current_gap >= ' || gap_size || ' then return; end if; ' ||
  '  update ci_' || element_type_plural_name ||
  '     set ' || sequence_property_name || ' = ' ||
          sequence_property_name || ' + ' || gap_size || ' - current_gap' ||
  '   where ' || within_property_name || ' = ' || within_element_id ||
  '     and ' || sequence_property_name || mod_operator || ' current_seq;' ||
  'end;';


  --dbms_output.put_line(length(string));
  --dbms_output.put_line(substr(string,1,80));
  --dbms_output.put_line(substr(string,81,80));
  --dbms_output.put_line(substr(string,161,80));
  --dbms_output.put_line(substr(string,241,80));
  --dbms_output.put_line(substr(string,321,80));
  --dbms_output.put_line(substr(string,401,80));
  --dbms_output.put_line(substr(string,481,80));
  --dbms_output.put_line(substr(string,561,80));
  --dbms_output.put_line(substr(string,641,80));

  cur := dbms_sql.open_cursor;
  dbms_sql.parse(cur, string, dbms_sql.v7);
  ret := dbms_sql.execute(cur);
  dbms_sql.close_cursor(cur);

END;

--============================ GET_TABLE_NAME ==============================--

-- Return the table name that supports the element type specified by name
--   returns null if not found
FUNCTION get_table_name(type_short_name varchar2) return varchar2
IS
  table_name varchar2(40);
BEGIN
  select max(tab.name)
    into table_name
    from rm_sql_tables tab
        ,rm_sql_row_types rt
        ,rm_element_types et
   where tab.id = rt.table_mapped
     and rt.id = et.primary_row_type
     and et.short_name = type_short_name;
  return(table_name);
end;
-- Return the table name that supports the element type specified by ty id
--   returns null if not found
FUNCTION get_table_name(type_id number) return varchar2
IS
  table_name varchar2(40);
BEGIN
  select max(tab.name)
    into table_name
    from rm_sql_tables tab
        ,rm_sql_row_types rt
        ,rm_element_types et
   where tab.id = rt.table_mapped
     and rt.id = et.primary_row_type
     and et.id = type_id;
  return(table_name);
end;

--============================ GET_REPOS_TABLE_NAME =========================--

-- Return the 'base' (I$...) table name that supports the element type
--  specified by ty id
--   returns null if not found
FUNCTION get_repos_table_name(type_id number) return varchar2
IS
  repos_table_name varchar2(40);
BEGIN
  select max(tab.repos_table_name)
    into repos_table_name
    from rm_sql_tables tab
        ,rm_sql_row_types rt
        ,rm_element_types et
   where tab.id = rt.table_mapped
     and rt.id = et.primary_row_type
     and et.id = type_id;
  return(repos_table_name);
end;
--=================================== END ===================================--
--
-- Package instantiation block
--
BEGIN
  is_installed := true;
END;
/