prompt Package Body : jr_ues_chk

create or replace package body jr_ues_chk is
  --------------------------------------------------------------
  -- New package to implement functions required to fix Bug 2078916
  -- All of these new functions are called from the element delete
  -- triggers and are used to check if there are any blocking
  -- user defined associations
  --
  -- Initially, all blocking functions were generated as database functions
  -- but as the functions contained in this package for user extended associations
  -- were overlooked it was decided to place all the additional required 
  -- functions in one package to minimise the impact on the installer
  --------------------------------------------------------------

  --------------------------------------------------------------
  -- check_ues_blocks_delete
  -- Checks whether a user defined association blocks the deletion of an object.
  --------------------------------------------------------------
  function check_ues_blocks_delete (object_ivid number
                                   ,object_irid number
                                   ,object_type number
                                   ,action VARCHAR2
                                   ) return boolean as
    cursor bad_cur is
    select ues.irid, ues.types
    from   sdd_ues ues
    where  use_of_ref = object_irid;

    is_ok boolean := TRUE;
  begin
    for ref in bad_cur loop
      is_ok := FALSE;
      jr_error.post_ref_error (object_ivid,object_type,ref.irid,ref.types,action);
    end loop;

    return is_ok;
  end check_ues_blocks_delete;

function FK_UES_TO_ATT_cmd (irid number, ivid number) return boolean as

  cursor bad_cur is
  select child_tab.irid child_irid, child_tab.types child_type
        ,parent_tab.ivid par_ivid, parent_tab.types par_type
  from   SDD_UES child_tab, i$SDD_ATT parent_tab
  where  parent_tab.parent_ivid=FK_UES_TO_ATT_cmd.ivid
  and    child_tab.USE_OF_REF=parent_tab.IRID
  ;
  is_ok boolean:=TRUE;
begin
  for ref in bad_cur loop
   is_ok:=FALSE;
   jr_error.post_ref_error(ref.par_ivid,ref.par_type,ref.child_irid,ref.child_type,'DEL');
  end loop;
  return is_ok;
end FK_UES_TO_ATT_cmd;

function FK_UES_TO_COL_cmd (irid number, ivid number) return boolean as

  cursor bad_cur is
  select child_tab.irid child_irid, child_tab.types child_type
        ,parent_tab.ivid par_ivid, parent_tab.types par_type
  from   SDD_UES child_tab, i$SDD_COL parent_tab
  where  parent_tab.parent_ivid=FK_UES_TO_COL_cmd.ivid
  and    child_tab.USE_OF_REF=parent_tab.IRID
  ;
  is_ok boolean:=TRUE;
begin
  for ref in bad_cur loop
   is_ok:=FALSE;
   jr_error.post_ref_error(ref.par_ivid,ref.par_type,ref.child_irid,ref.child_type,'DEL');
  end loop;
  return is_ok;
end FK_UES_TO_COL_cmd;

function FK_UES_TO_DAT_cmd (irid number, ivid number) return boolean as

  cursor bad_cur is
  select child_tab.irid child_irid, child_tab.types child_type
        ,parent_tab.ivid par_ivid, parent_tab.types par_type
  from   SDD_UES child_tab, i$SDD_DAT parent_tab
  where  parent_tab.parent_ivid=FK_UES_TO_DAT_cmd.ivid
  and    child_tab.USE_OF_REF=parent_tab.IRID
  ;
  is_ok boolean:=TRUE;
begin
  for ref in bad_cur loop
   is_ok:=FALSE;
   jr_error.post_ref_error(ref.par_ivid,ref.par_type,ref.child_irid,ref.child_type,'DEL');
  end loop;
  return is_ok;
end FK_UES_TO_DAT_cmd;

function FK_UES_TO_DBU_cmd (irid number, ivid number) return boolean as

  cursor bad_cur is
  select child_tab.irid child_irid, child_tab.types child_type
        ,parent_tab.ivid par_ivid, parent_tab.types par_type
  from   SDD_UES child_tab, i$SDD_DBU parent_tab
  where  parent_tab.parent_ivid=FK_UES_TO_DBU_cmd.ivid
  and    child_tab.USE_OF_REF=parent_tab.IRID
  ;
  is_ok boolean:=TRUE;
begin
  for ref in bad_cur loop
   is_ok:=FALSE;
   jr_error.post_ref_error(ref.par_ivid,ref.par_type,ref.child_irid,ref.child_type,'DEL');
  end loop;
  return is_ok;
end FK_UES_TO_DBU_cmd;

function FK_UES_TO_GRP_cmd (irid number, ivid number) return boolean as

  cursor bad_cur is
  select child_tab.irid child_irid, child_tab.types child_type
        ,parent_tab.ivid par_ivid, parent_tab.types par_type
  from   SDD_UES child_tab, i$SDD_GRP parent_tab
  where  parent_tab.parent_ivid=FK_UES_TO_GRP_cmd.ivid
  and    child_tab.USE_OF_REF=parent_tab.IRID
  ;
  is_ok boolean:=TRUE;
begin
  for ref in bad_cur loop
   is_ok:=FALSE;
   jr_error.post_ref_error(ref.par_ivid,ref.par_type,ref.child_irid,ref.child_type,'DEL');
  end loop;
  return is_ok;
end FK_UES_TO_GRP_cmd;

function FK_UES_TO_ORS_cmd (irid number, ivid number) return boolean as

  cursor bad_cur is
  select child_tab.irid child_irid, child_tab.types child_type
        ,parent_tab.ivid par_ivid, parent_tab.types par_type
  from   SDD_UES child_tab, i$SDD_ORS parent_tab
  where  parent_tab.parent_ivid=FK_UES_TO_ORS_cmd.ivid
  and    child_tab.USE_OF_REF=parent_tab.IRID
  ;
  is_ok boolean:=TRUE;
begin
  for ref in bad_cur loop
   is_ok:=FALSE;
   jr_error.post_ref_error(ref.par_ivid,ref.par_type,ref.child_irid,ref.child_type,'DEL');
  end loop;
  return is_ok;
end FK_UES_TO_ORS_cmd;

function FK_UES_TO_OTB_cmd (irid number, ivid number) return boolean as

  cursor bad_cur is
  select child_tab.irid child_irid, child_tab.types child_type
        ,parent_tab.ivid par_ivid, parent_tab.types par_type
  from   SDD_UES child_tab, i$SDD_OTB parent_tab
  where  parent_tab.parent_ivid=FK_UES_TO_OTB_cmd.ivid
  and    child_tab.USE_OF_REF=parent_tab.IRID
  ;
  is_ok boolean:=TRUE;
begin
  for ref in bad_cur loop
   is_ok:=FALSE;
   jr_error.post_ref_error(ref.par_ivid,ref.par_type,ref.child_irid,ref.child_type,'DEL');
  end loop;
  return is_ok;
end FK_UES_TO_OTB_cmd;

function FK_UES_TO_DFI_cmd (irid number, ivid number) return boolean as

  cursor bad_cur is
  select child_tab.irid child_irid, child_tab.types child_type
        ,parent_tab.ivid par_ivid, parent_tab.types par_type
  from   SDD_UES child_tab, i$SDD_DFI parent_tab
  where  parent_tab.parent_ivid=FK_UES_TO_DFI_cmd.ivid
  and    child_tab.USE_OF_REF=parent_tab.IRID
  ;
  is_ok boolean:=TRUE;
begin
  for ref in bad_cur loop
   is_ok:=FALSE;
   jr_error.post_ref_error(ref.par_ivid,ref.par_type,ref.child_irid,ref.child_type,'DEL');
  end loop;
  return is_ok;
end FK_UES_TO_DFI_cmd;

function FK_UES_TO_MUN_cmd (irid number, ivid number) return boolean as

  cursor bad_cur is
  select child_tab.irid child_irid, child_tab.types child_type
        ,parent_tab.ivid par_ivid, parent_tab.types par_type
  from   SDD_UES child_tab, i$SDD_MUN parent_tab
  where  parent_tab.parent_ivid=FK_UES_TO_MUN_cmd.ivid
  and    child_tab.USE_OF_REF=parent_tab.IRID
  ;
  is_ok boolean:=TRUE;
begin
  for ref in bad_cur loop
   is_ok:=FALSE;
   jr_error.post_ref_error(ref.par_ivid,ref.par_type,ref.child_irid,ref.child_type,'DEL');
  end loop;
  return is_ok;
end FK_UES_TO_MUN_cmd;

end jr_ues_chk;
/