rem 
rem $Header: L:\\\\model\\repman40\\api\\RCS\\cioky.hpb 1.4 1998/12/18 16:58:52 cvanes Exp $ 
rem 
Rem  Copyright (c) 1994 by Oracle Corporation 
Rem    NAME
Rem      cioky.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     aheath     07/08/94 -  Creation 
REM ***************************************************************************
REM   Hand-carved package body for KEY COMPONENT created on 06-SEP-94
REM ***************************************************************************
CREATE OR REPLACE PACKAGE BODY cihoky IS

  -- Type-specific post-process routines
  PROCEDURE check_delete(id rm.reference);

  sac_type constant varchar2(6) := 'OKY';
  pac_type constant varchar2(6) := 'OCO';

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

PROCEDURE pre_process(operation varchar2,id rm.reference,
  pl in out NOCOPY ciokey_component.data)
IS
mandatory_flag varchar2(1);
BEGIN
  -- Access rights for key component derive from the constraint
-- Access rights checking performed by triggers in config 4.0.9
--  ciiacc.check_access_rights(pl.v.constraint_reference,pac_type,
--    'UPD','SAC');

  -- Initialize unassigned properties with default values on INSERT
  if operation = 'INS' then
    pl.v.element_type_name := sac_type;
    pl.i.element_type_name := true;
 
    -- maintain denormalised preoperties constraint_type 
    -- SELECT decode(oco.element_type_name, 'PCO', 'PRIMARY'
    --                                    , 'UCO', 'UNIQUE'
    --                                    , 'FCO', 'FOREIGN')
    SELECT decode(oco.constraint_type, 'JOIN', 'FOREIGN', oco.constraint_type)
      INTO pl.v.constraint_type
      FROM ci_constraints oco
     WHERE oco.id = pl.v.constraint_reference;

    pl.i.constraint_type := true;

    if pl.v.constraint_type = 'PRIMARY' then
      update ci_columns col
         set null_indicator = 'NOT NULL'
       where col.id = pl.v.column_reference;
    end if;


    -- Update null_indicator for column if making column part of
    -- a madatory unique or foreign key (B459429)    
    if pl.v.constraint_type in ('UNIQUE','FOREIGN') then
      select max(mandatory_flag) 
        into mandatory_flag
        from sdd_oco oco
       where oco.irid = pl.v.constraint_reference;
      if mandatory_flag = 'Y' then
       update ci_columns col
          set null_indicator = 'NOT NULL'
        where col.id = pl.v.column_reference;
      end if;
    end if;

    if pl.v.cqa_reference is not null then
      pl.v.oky_arc1_for := 'CQA';
    else
      pl.v.oky_arc1_for := 'COL';
    end if;
    pl.i.oky_arc1_for := true;

    if pl.v.foreign_cqa_reference is not null then
      pl.v.oky_arc2_for := 'CQA';
    else
      pl.v.oky_arc2_for := 'COL';
    end if;
    pl.i.oky_arc2_for := true;

    if pl.v.foreign_cqa_reference2 is not null then
      pl.v.oky_arc3_for := 'CQA';
    else
      pl.v.oky_arc3_for := 'COL';
    end if;
    pl.i.oky_arc3_for := true;
  end if;

  -- 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;

END;

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

PROCEDURE pre_process(operation varchar2,id rm.reference)
IS
  oco_id number;
  indcol_id number;
BEGIN
  -- Access rights for key component derive from the constraint
--  select constraint_reference 
--    into oco_id 
--    from ci_key_components oky
--   where oky.id = pre_process.id;
   
-- Access rights checking performed by triggers in config 4.0.9
--  ciiacc.check_access_rights(oco_id,pac_type,operation,'SAC');

  /* COMMENTED OUT WHILST COSULTING USERS AS TO IMPLICATIONS 
  -- Delete any index entries associated with the same col/cqa as this component
  -- for any relation index based on same foriegn key as this component. (B394845)
  if operation = 'DEL' then
    
    select max(indcol.id)
      into indcol_id
      from ci_index_entries indcol
          ,ci_key_components oky
          ,ci_relation_indexes rin
     where (indcol.cqa_reference=oky.cqa_reference 
            or indcol.column_reference = oky.column_reference)
       and indcol.relation_index_reference=rin.id
       and rin.foreign_key_reference=oky.constraint_reference
       and oky.id = pre_process.id;

    if indcol_id is not null then
      delete ci_index_entries indcol
       where indcol.id = indcol_id;
    end if;
  end if; 
*/
  null;
END;

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

PROCEDURE post_process(operation varchar2,id rm.reference,
  pl ciokey_component.data)
IS
  dummy number;
  rin_id number;
  indcol cioindex_entry.data;
BEGIN
  -- If there is a relation_index based on the same foreign key as this compenent
  -- then generate a new index entry if inserting a new component.
  -- (not in load_mode)
  /* COMMENTED OUT WHILST COSULTING USERS AS TO IMPLICATIONS 
  if operation = 'INS' and not cdapi.load_mode then
    select max(rin.id) 
      into rin_id
      from ci_relation_indexes rin
     where rin.foreign_key_reference=pl.v.constraint_reference;

      -- Don't create an index entry if it already exists
    select count(*) 
      into dummy
      from ci_index_entries indcol
     where (indcol.column_reference=pl.v.column_reference
             or indcol.cqa_reference=pl.v.cqa_reference)
       and indcol.relation_index_reference=rin_id;
 
    if rin_id is not null and dummy = 0 then
    
      if pl.v.cqa_reference is not null then
        indcol.v.cqa_reference := pl.v.cqa_reference;
        indcol.i.cqa_reference := true;
      else
        indcol.v.column_reference := pl.v.column_reference;
        indcol.i.column_reference := true;
      end if;
      indcol.v.sequence_number := pl.v.sequence_number;
      indcol.i.sequence_number := true;
      indcol.v.relation_index_reference := rin_id;
      indcol.i.relation_index_reference := true;
      cioindex_entry.ins(null,indcol);
    end if;
  end if;
  */
null; 
   
END;

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

PROCEDURE post_process(operation varchar2,id rm.reference)
IS
BEGIN
  null;
END;

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

PROCEDURE check_delete(id rm.reference)
IS
BEGIN
  null;
END;

--============================= CHECK_SAME ==================================--
/* returns false if P constraint same is now same as one U constraint */
FUNCTION check_same(key_ref rm.reference)
RETURN boolean
IS
  pk_cnt number := 0;
  uk_cnt number := 0;
  pk_key number;
  uk_key number;
  tab_ref rm.reference;
  result boolean := true;
  cursor key_cursor(tab_ref rm.reference) is
         select con.id uk_ref
         from ci_unique_key_constraints con
         where con.table_reference = tab_ref;
  cursor pk_cursor(tab_ref rm.reference) is
         select oky.column_reference
         from  ci_primary_key_constraints pco,
               ci_key_components oky
         where oky.constraint_reference = pco.id
         and   pco.table_reference = tab_ref
         order by oky.sequence_number;
  cursor uk_cursor (par1 number) is
         select oky.column_reference
         from   ci_key_components oky
         where  oky.constraint_reference = par1
         order by oky.sequence_number;
BEGIN
  begin
    select oco.table_reference
    into   tab_ref
    from   ci_constraints    oco
          ,ci_key_components oky
    where  oco.id = oky.constraint_reference
      and  oky.id = check_same.key_ref;
  end;
  begin
    select count(oky.column_reference)
    into  pk_cnt
    from  ci_primary_key_constraints pco,
          ci_key_components oky
    where oky.constraint_reference = pco.id
    and   pco.table_reference = tab_ref;
  end;
  begin
    for uk in key_cursor(tab_ref)
    loop
      begin
        select count(oky.column_reference)
        into uk_cnt
        from ci_key_components oky
        where oky.constraint_reference = uk.uk_ref;
      end;
      if uk_cnt = pk_cnt then
        begin
          open pk_cursor (tab_ref);
          open uk_cursor (uk.uk_ref);
          for i in 1 .. pk_cnt
          loop
             fetch pk_cursor into pk_key;
             fetch uk_cursor into uk_key;
             if pk_key != uk_key then
                exit;
             end if;
          end loop;
          close pk_cursor;
          close uk_cursor;
          if pk_key = uk_key then
             result := false;
             exit;
          end if;
        end;
      end if;
    end loop;
  end;
return(result);
END;

--============================= CHECK_UNIQUE ================================--
/* returns false if FOREIGN_COLUMN_REFERENCE is not unique in constraint */
FUNCTION check_unique(id rm.reference, fcolref rm.reference, 
                      con_ref rm.reference)
RETURN boolean
IS
  cnt number;
BEGIN
  select count(*) into cnt
  from ci_key_components oky
  where oky.foreign_column_reference = check_unique.fcolref
  and   oky.constraint_reference     = check_unique.con_ref
  and   oky.id                      != check_unique.id;

  if cnt > 0 then
    return(false);
  else
    return(true);
  end if;

END;

--============================= CHECK_JOIN_COL ==============================--
/* returns false if FOREIGN_COLUMN_REFERENCE is not in a primary key */
FUNCTION check_join_col(fcolref rm.reference)
RETURN boolean
IS
  cnt number;
BEGIN
select count(*) into cnt
  from ci_key_components oky
      ,ci_primary_key_constraints pco
  where oky.column_reference = check_join_col.fcolref
  and   oky.constraint_reference  = pco.id
  ;
  
  if cnt > 0
  then
    return(true);
  else
    return(false);
  end if;
END;

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