rem 
rem $Header: L:\\\\model\\repman40\\api\\RCS\\ciappsac.mpb 1.2 1998/09/23 14:49:16 cvanes Exp $ 
rem 
Rem  Copyright (c) 1994 by Oracle Corporation 
Rem    NAME
Rem      ciappsac.mpb - UID check for SAC's, name unique in application sys
Rem    DESCRIPTION
Rem      This is only used for a few Secondary Access Controlled elements (SAC) 
Rem      which need their name to be unique in an application system.
Rem      Currently this is being used for database triggers and foreign key
Rem      constraints. This is complicated by the possibility that the 
Rem      PAC being refered to by the SAC may be shared into more than one
Rem      application system. So the actual code checks whether another SAC 
Rem      has the same name as the SAC being checked in all the application 
Rem      systems that the SAC is shared into.
Rem       Now used by OCO, TRG, RIN and CIN CvE 13 Aug 98
Rem    RETURNS
Rem 
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    MODIFIED   (MM/DD/YY)
Rem     cvanes     08/01/96 -  Creation 
Rem     aheath     03/28/95 -  Gave the error message the missing parameter
Rem     jwetherb   01/11/95 -  Removed maxact variable
Rem     aheath     11/09/94 -  Creation 
CREATE OR REPLACE PACKAGE BODY cimappsac IS

--=========================== CHECK_CIHAPPSACU1 =============================--
procedure check_ciappsacu1(conid in number) is

  cursor elems(constraint_id number, activity_id number) is
    select dc.element
      from rm_deferred_checks dc
    where dc.assertion = constraint_id
      and dc.activity = activity_id;

  clash_id number;
  clash_type number;
  clash_app number;
  my_type number;      
begin
  rmman.record_check(conid,null,null,null,true);
  -- For each application system and sac element pair
  for elem in elems(conid, cdapi.activity) loop
    if not cimappsac.is_appsac_unique(elem.element, my_type, clash_id, clash_type, clash_app) then
      rmman.record_check(conid,elem.element,null,cdapi.activity,false,'Y',
            ciiutl.identify(elem.element, my_type),
            ciiutl.identify(clash_id, clash_type),
            ciiutl.identify(clash_app, 'CEL'));
    end if;
  end loop;
end check_ciappsacu1;



--=========================== IS_APPSAC_UNIQUE =============================--
function is_appsac_unique(i_irid in number
                         ,my_type out number
                         ,clash_id out number
                         ,clash_type out number
                         ,clash_app out number) return boolean 
is
  cursor appsac_share(elem_id number, context_cel in number) is
    select appxxx.folder_reference app_id
          ,sac.name sac_name
          ,sac.irid sac_id
          ,sac.element_type_name sac_type
          ,sac.types sac_types
          ,decode(appxxx.folder_reference, context_cel, 1, 2) ord
      from sdd_folder_members appxxx
          ,ci_named_database_sacs sac
     where appxxx.member_object= sac.parent_reference
       and sac.irid = elem_id
     order by ord;
   -- we guarantee that the error is reported in the context of the current
   --  container by the 'order by' in appsac_share.
       
  cursor get_dup_sac(app_id number, sac_id number,
      sac_name varchar2, sac_type varchar2) is
    select sac.irid  id
          ,sac.types types
      from sdd_folder_members appxxx
          ,ci_named_database_sacs sac
     where sac.parent_reference = appxxx.member_object
       and appxxx.folder_reference = app_id
       and sac.name = sac_name
       and sac.irid != sac_id
       and sac.element_type_name = sac_type;
begin
  for appsac in appsac_share(i_irid, cdapi.app_sys_ref) loop
    for el in get_dup_sac(appsac.app_id, appsac.sac_id, appsac.sac_name, appsac.sac_type) loop
      my_type := appsac.sac_types;
      clash_id := el.id;
      clash_type := el.types;
      clash_app := appsac.app_id;
      return false;
    end loop;
  end loop appsac1;
  my_type := null;
  clash_id := null;
  clash_type := null;
  clash_app := null;
  return true;

end is_appsac_unique;

END;
/