rem
rem $Header: L:\\\\model\\repman40\\api\\RCS\\cipgd.hpb 1.2 1998/12/14 19:09:08 cvanes Exp $
rem
Rem  Copyright (c) 1994 by Oracle Corporation
Rem    NAME
Rem      cipgd.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     stlyon     01/27/03 -  B2670691
Rem     aheath     07/08/94 -  Creation
REM ***************************************************************************
REM   Hand-carved package body for PROGRAM DATUM created on 14-OCT-94
REM ***************************************************************************
CREATE OR REPLACE PACKAGE BODY cihpgd IS

  -- Generic post-process routines
  PROCEDURE check_delete(id number);

  sac_type constant varchar2(6) := 'PGD';
  pac_type constant varchar2(6) := 'MOD';

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

PROCEDURE pre_process(operation varchar2,id number,
  pl in out NOCOPY cioprogram_datum.data)
IS
  pac_reference number;
  old_col_ref number;
  old_ds_ref number;
  old_ot_ref number;
  old_td_ref number;
  old_dom_ref number;
  old_vdt varchar2(70);
  old_pdf varchar2(8);
BEGIN
  pac_reference := nvl(pl.v.general_module_reference, pl.v.plsql_module_reference);

  -- Access rights for program datum derive from the module
-- Access rights checking performed by triggers in config 4.0.9
--  ciiacc.check_access_rights(pac_reference,pac_type,
--    'UPD','SHR');

  -- 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;
    if pl.v.general_module_reference is not null then
      pl.v.module_type_for := 'GEM';
    else
      pl.v.module_type_for := 'PLM';
    end if;
    pl.i.module_type_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;

--#BEGIN
  if (pl.i.value_datatype or pl.i.value_length or pl.i.value_scale)
     and pl.v.value_datatype is not null
  then
    ciiutl.adjust_datatype_sizes(pl.v.id,'PGD',pl.v.value_datatype,
      pl.v.value_length, pl.v.value_scale, 'PLS_DATATYPE');            -- B2670691 : PLS_DATA_TYPE -> PLS_DATATYPE
  end if;

  if operation = 'INS' then
    if pl.v.value_datatype is not null then
      pl.v.program_datum_for := 'DATATYPE';
    elsif pl.v.datastructure_reference is not null then
      pl.v.program_datum_for := 'DAT';
    elsif pl.v.domain_reference is not null then
      pl.v.program_datum_for := 'DOM';
    elsif pl.v.oracle_type_reference is not null then
      pl.v.program_datum_for := 'ORT';
    elsif pl.v.table_definition_reference is not null then
      pl.v.program_datum_for := 'TBL';
    elsif pl.v.column_reference is not null then
      pl.v.program_datum_for := 'COL';
    else
      pl.v.program_datum_for := null;
    end if;

    if not pl.i.sequence then
      pl.v.sequence := ciiexp.default_seq( pac_reference, sac_type);
      pl.i.sequence := true;
    end if;

  else  -- operation = 'UPD'.

    --Need to set references to null for element types other than the one
    --being changed as cioprogram_datum.upd sets them all to the old value of
    --the element reference (One and only one should ever be not null). (B464359)

    --Also need to set pl.v.vaue_datatype to null for all non-datatype cases (B602209)

    if not pl.i.value_datatype and not pl.i.datastructure_reference and
      not pl.i.domain_reference and not pl.i.oracle_type_reference and
      not pl.i.table_definition_reference and not pl.i.column_reference
    then
      -- The arc has not changed
      null;
    elsif pl.i.value_datatype and pl.v.value_datatype is not null then
      pl.v.program_datum_for := 'DATATYPE';
      pl.v.datastructure_reference:=null;
      pl.v.oracle_type_reference:=null;
      pl.v.table_definition_reference:=null;
      pl.v.column_reference:=null;
      pl.v.domain_reference:=null;
    elsif pl.i.datastructure_reference and
      pl.v.datastructure_reference is not null then
      pl.v.program_datum_for := 'DAT';
      pl.v.oracle_type_reference:=null;
      pl.v.table_definition_reference:=null;
      pl.v.column_reference:=null;
      pl.v.domain_reference:=null;
      pl.v.value_datatype:=null;
    elsif pl.i.oracle_type_reference and
      pl.v.oracle_type_reference is not null then
      pl.v.program_datum_for := 'ORT';
      pl.v.datastructure_reference:=null;
      pl.v.table_definition_reference:=null;
      pl.v.column_reference:=null;
      pl.v.domain_reference:=null;
      pl.v.value_datatype:=null;
    elsif pl.i.table_definition_reference and
      pl.v.table_definition_reference is not null then
      pl.v.program_datum_for := 'TBL';
      pl.v.datastructure_reference:=null;
      pl.v.oracle_type_reference:=null;
      pl.v.column_reference:=null;
      pl.v.domain_reference:=null;
      pl.v.value_datatype:=null;
    elsif pl.i.column_reference and
      pl.v.column_reference is not null then
      pl.v.program_datum_for := 'COL';
      pl.v.datastructure_reference:=null;
      pl.v.oracle_type_reference:=null;
      pl.v.table_definition_reference:=null;
      pl.v.domain_reference:=null;
      pl.v.value_datatype:=null;
    elsif pl.i.domain_reference and pl.v.domain_reference is not null then
      pl.v.program_datum_for := 'DOM';
      pl.v.datastructure_reference:=null;
      pl.v.oracle_type_reference:=null;
      pl.v.table_definition_reference:=null;
      pl.v.column_reference:=null;
      pl.v.value_datatype:=null;
    else
      pl.v.program_datum_for := null;
    end if;

    -- Set the relevant pl.i fields for properties that have changed
    -- 
    select pgd.column_ref, pgd.datastructure_ref, pgd.oracle_type_ref,
           pgd.table_definition_ref, pgd.domain_ref, pgd.value_datatype,
           pgd.program_datum_for
      into old_col_ref, old_ds_ref, old_ot_ref, old_td_ref, old_dom_ref, old_vdt, old_pdf
      from sdd_pgd pgd
     where pgd.irid = pl.v.id;

    if old_col_ref != pl.v.column_reference then 
      pl.i.column_reference := true;
    else
      pl.i.column_reference := false;
    end if;
    if old_ds_ref != pl.v.datastructure_reference then
      pl.i.datastructure_reference := true;
    else
      pl.i.datastructure_reference := false; 
    end if;
    if old_ot_ref != pl.v.oracle_type_reference then
      pl.i.oracle_type_reference := true;
    else 
      pl.i.oracle_type_reference := false;
    end if;
    if old_td_ref != pl.v.table_definition_reference then
      pl.i.table_definition_reference := true;
    else 
      pl.i.table_definition_reference := false;
    end if;
    if old_dom_ref != pl.v.domain_reference then
      pl.i.domain_reference := true;
    else 
      pl.i.domain_reference := false;
    end if;
    if old_vdt != pl.v.value_datatype then
      pl.i.value_datatype := true;
    else 
      pl.i.value_datatype := true;
    end if;
    if old_pdf != pl.v.program_datum_for then
      pl.i.program_datum_for := true;
    else 
      pl.i.program_datum_for := true;
    end if;

  end if;
--#END
END;

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

PROCEDURE pre_process(operation varchar2,id number)
IS
BEGIN
  -- Access rights for program datum derive from the module
-- Access rights checking performed by triggers in config 4.0.9
--  ciiacc.check_access_rights(id,sac_type,operation,'SAC');

  -- if operation is 'DELETE', make sure no non-cascading references exist
  if operation = 'DEL' then
    check_delete(id);
  end if;
END;

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

PROCEDURE post_process(operation varchar2,id number,
  pl cioprogram_datum.data)
IS
BEGIN
  null;
END;

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

PROCEDURE post_process(operation varchar2,id number)
IS
BEGIN
  if operation = 'DEL' then
    -- Delete this and all it's related sub-bits
    ciiutl.delete_element(id,sac_type);
  end if;
END;

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

PROCEDURE check_delete(id number)
IS
  dummy number;
  error_count number := 0;
BEGIN
  -- 4.0.12 Removed all simple delete checking
  -- Check if any datastructure items are connected to me
  -- Check if any module program data, with 'REFERENCED' method of use
  --  are connected to me
  select count(*) into dummy from ci_module_program_data
    where program_datum_reference = check_delete.id
      and method_of_use = 'REFERENCED';
  if dummy > 0 then
    ciierr.post(101,ciiutl.identify(id, 'PGD'),ciiutl.nls_type_plural_name_s('MODPGD'),
      ciiutl.nls_type_name_s('PGD'));
    error_count := 1;
  end if;
  if error_count > 0 then
    raise cdapi.apierror;
  end if;
END;

--============================= CHECK UNIQUE NAME ===========================--
-- Case independent unique name check

FUNCTION unique_name(id number, name varchar2, module_reference number)
RETURN boolean
IS
  cnt number;
BEGIN
  SELECT count(*)
  into cnt
  FROM   ci_program_data pd
  WHERE  nls_upper(pd.name) = nls_upper(unique_name.name)
    AND  nvl(pd.general_module_reference, pd.plsql_module_reference) =
                                         unique_name.module_reference
    and  pd.id != nvl(unique_name.id,0)
  ;

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

--=========================== IS_PGD_UNIQUE =============================--
function is_pgd_unique(i_irid in number, clash_id out number, clash_type out number) return boolean 
is
  cursor other_pgd (pgd_irid in number) is
    select other_pgd.irid, other_pgd.types
      from ci_program_data my_pgd
          ,ci_program_data other_pgd
     where nls_upper(my_pgd.name) = nls_upper(other_pgd.name)
       and (my_pgd.general_module_reference = other_pgd.general_module_reference
        or  my_pgd.plsql_module_reference = other_pgd.plsql_module_reference)
       and my_pgd.irid != other_pgd.irid
       and my_pgd.irid = pgd_irid;
begin
  for other_pgds in other_pgd(i_irid) loop
    clash_id := other_pgds.irid;
    clash_type := other_pgds.types;
    return false;
  end loop;
  clash_id := null;
  clash_type := null;
  return true;
end;
--================================== END ====================================--
--
-- Package instantiation block
--
BEGIN
  is_installed := true;
END;
/