create or replace package CKoAPPLICATION_LOGIC is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Properties
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
type rm_values is record (
  AL_CODE_LANGUAGE VARCHAR2(2)
, AL_CREATE_FLAG VARCHAR2(1)
, AL_EVT_CONTEXT_REPOS_ELEMENT NUMBER(38)
, AL_EVT_EXECUTION_STYLE VARCHAR2(1)
, AL_EVT_NAME VARCHAR2(40)
, AL_EVT_SEQ_IN_EVENT NUMBER(6)
, AL_EVT_SHORT_DESCRIPTION VARCHAR2(240)
, AL_GEN_OWN_REF VARCHAR2(50)
, AL_GEN_PRODUCT_ID NUMBER(6)
, AL_GEN_UNIQUE_CONTEXT VARCHAR2(240)
, AL_ID NUMBER(38)
, AL_NR_PUBLIC_SCOPE VARCHAR2(1)
, AL_NR_ROUTINE_NAME VARCHAR2(40)
, AL_NR_TYPE VARCHAR2(2)
, AL_REPOS_ELEMENT NUMBER(38)
, AL_SOURCE VARCHAR2(1)
, AL_TARGET_LOCATION VARCHAR2(6)
, AL_TYPE VARCHAR2(1)
, ELEMENT_TYPE_FOR VARCHAR2(6)
, IRID NUMBER(38)
, IVID NUMBER(38)
, PAC_REFERENCE NUMBER(38)
, PARENT_IVID NUMBER(38)
);
type rm_indicators is record (
  AL_CODE_LANGUAGE boolean not null default false
, AL_CREATE_FLAG boolean not null default false
, AL_EVT_CONTEXT_REPOS_ELEMENT boolean not null default false
, AL_EVT_EXECUTION_STYLE boolean not null default false
, AL_EVT_NAME boolean not null default false
, AL_EVT_SEQ_IN_EVENT boolean not null default false
, AL_EVT_SHORT_DESCRIPTION boolean not null default false
, AL_GEN_OWN_REF boolean not null default false
, AL_GEN_PRODUCT_ID boolean not null default false
, AL_GEN_UNIQUE_CONTEXT boolean not null default false
, AL_ID boolean not null default false
, AL_NR_PUBLIC_SCOPE boolean not null default false
, AL_NR_ROUTINE_NAME boolean not null default false
, AL_NR_TYPE boolean not null default false
, AL_REPOS_ELEMENT boolean not null default false
, AL_SOURCE boolean not null default false
, AL_TARGET_LOCATION boolean not null default false
, AL_TYPE boolean not null default false
, ELEMENT_TYPE_FOR boolean not null default false
, IRID boolean not null default false
, IVID boolean not null default false
, PAC_REFERENCE boolean not null default false
, PARENT_IVID boolean not null default false
);
type data is record (
  v rm_values
, i rm_indicators
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Operations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
);
--
-- del
--
procedure del
(
  irid in number
);
--
-- sel
--
procedure sel
(
  irid in     number
, pl   in out data
);
--
-- upd
--
procedure upd
(
  irid in     number
, pl   in out data
);
--
-- lck
--
procedure lck
(
  irid in number
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- copy
--
procedure copy_sac( i_irid              in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  );
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Exceptions
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
element_locked exception;
  element_locked_fac  constant varchar2(3) := 'RME';
  element_locked_code constant number      := 10;
invalid_ref exception;
  invalid_ref_fac  constant varchar2(3) := 'RME';
  invalid_ref_code constant number      := 9;
operation_failed exception;
  operation_failed_fac  constant varchar2(3) := 'RME';
  operation_failed_code constant number      := 11;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Globals
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
is_installed boolean := false;
end CKoAPPLICATION_LOGIC;
/
create or replace package body CKoAPPLICATION_LOGIC is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Cursors
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Retrieve Element Type property values
cursor GetP(irid in rm.reference) is
  select
    el.AL_CODE_LANGUAGE
  , el.AL_CREATE_FLAG
  , el.AL_EVT_CONTEXT_REPOS_ELEMENT
  , el.AL_EVT_EXECUTION_STYLE
  , el.AL_EVT_NAME
  , el.AL_EVT_SEQ_IN_EVENT
  , el.AL_EVT_SHORT_DESCRIPTION
  , el.AL_GEN_OWN_REF
  , el.AL_GEN_PRODUCT_ID
  , el.AL_GEN_UNIQUE_CONTEXT
  , el.AL_ID
  , el.AL_NR_PUBLIC_SCOPE
  , el.AL_NR_ROUTINE_NAME
  , el.AL_NR_TYPE
  , el.AL_REPOS_ELEMENT
  , el.AL_SOURCE
  , el.AL_TARGET_LOCATION
  , el.AL_TYPE
  , el.ELEMENT_TYPE_FOR
  , el.IRID
  , el.IVID
  , el.PAC_REF
  , el.PARENT_IVID
  from CK_APPLICATION_LOGIC el
  where el.irid = GetP.irid
  ;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Procedure and Function forward Declarations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Public Procedures
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
) is
begin
  -- If irid is not null copy it into the property list
  if irid is not NULL then
    pl.v.irid := ins.irid;
    pl.i.irid := true;
  end if;
  -- Property Maintenance
  maintain(pl,false);
  if not pl.i.irid then
    pl.v.irid := jr_util.get_new_irid;
    pl.i.irid := true;
  end if;
  if not pl.i.ivid then
    pl.v.ivid := jr_system_util.get_new_ivid;
    pl.i.ivid := true;
  end if;
  -- Insert
  insert into CK_APPLICATION_LOGIC
  (
    AL_CODE_LANGUAGE
  , AL_CREATE_FLAG
  , AL_EVT_CONTEXT_REPOS_ELEMENT
  , AL_EVT_EXECUTION_STYLE
  , AL_EVT_NAME
  , AL_EVT_SEQ_IN_EVENT
  , AL_EVT_SHORT_DESCRIPTION
  , AL_GEN_OWN_REF
  , AL_GEN_PRODUCT_ID
  , AL_GEN_UNIQUE_CONTEXT
  , AL_ID
  , AL_NR_PUBLIC_SCOPE
  , AL_NR_ROUTINE_NAME
  , AL_NR_TYPE
  , AL_REPOS_ELEMENT
  , AL_SOURCE
  , AL_TARGET_LOCATION
  , AL_TYPE
  , ELEMENT_TYPE_FOR
  , IRID
  , IVID
  , PAC_REF
  , PARENT_IVID
  )
  values
  (
    pl.v.AL_CODE_LANGUAGE
  , pl.v.AL_CREATE_FLAG
  , pl.v.AL_EVT_CONTEXT_REPOS_ELEMENT
  , pl.v.AL_EVT_EXECUTION_STYLE
  , pl.v.AL_EVT_NAME
  , pl.v.AL_EVT_SEQ_IN_EVENT
  , pl.v.AL_EVT_SHORT_DESCRIPTION
  , pl.v.AL_GEN_OWN_REF
  , pl.v.AL_GEN_PRODUCT_ID
  , pl.v.AL_GEN_UNIQUE_CONTEXT
  , pl.v.AL_ID
  , pl.v.AL_NR_PUBLIC_SCOPE
  , pl.v.AL_NR_ROUTINE_NAME
  , pl.v.AL_NR_TYPE
  , pl.v.AL_REPOS_ELEMENT
  , pl.v.AL_SOURCE
  , pl.v.AL_TARGET_LOCATION
  , pl.v.AL_TYPE
  , pl.v.ELEMENT_TYPE_FOR
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.PAC_REFERENCE
  , pl.v.PARENT_IVID
  );
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'ins','APPLICATION_LOGIC');
    raise;
end ins;
--
-- del
--
procedure del
(
  irid in number
) is
begin
  -- Delete the element itself
  delete from CK_APPLICATION_LOGIC
  where CK_APPLICATION_LOGIC.irid = del.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'del','APPLICATION_LOGIC');
    raise;
end del;
--
-- sel
--
procedure sel
(
  irid in number
, pl   in out data
) is
begin
  -- Select
  open GetP(sel.irid);
  fetch GetP into
    pl.v.AL_CODE_LANGUAGE
  , pl.v.AL_CREATE_FLAG
  , pl.v.AL_EVT_CONTEXT_REPOS_ELEMENT
  , pl.v.AL_EVT_EXECUTION_STYLE
  , pl.v.AL_EVT_NAME
  , pl.v.AL_EVT_SEQ_IN_EVENT
  , pl.v.AL_EVT_SHORT_DESCRIPTION
  , pl.v.AL_GEN_OWN_REF
  , pl.v.AL_GEN_PRODUCT_ID
  , pl.v.AL_GEN_UNIQUE_CONTEXT
  , pl.v.AL_ID
  , pl.v.AL_NR_PUBLIC_SCOPE
  , pl.v.AL_NR_ROUTINE_NAME
  , pl.v.AL_NR_TYPE
  , pl.v.AL_REPOS_ELEMENT
  , pl.v.AL_SOURCE
  , pl.v.AL_TARGET_LOCATION
  , pl.v.AL_TYPE
  , pl.v.ELEMENT_TYPE_FOR
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.PAC_REFERENCE
  , pl.v.PARENT_IVID
  ;
  if GetP%NOTFOUND then
    close GetP;
    rmmes.post(invalid_ref_fac,invalid_ref_code,
               'APPLICATION_LOGIC',sel.irid);
    raise invalid_ref;
  end if;
  close GetP;
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'sel','APPLICATION_LOGIC');
    raise;
end sel;
--
-- upd
--
procedure upd
(
  irid in number
, pl   in out data
) is
begin
  -- Property Maintenance
    pl.v.irid := irid;
    maintain(pl,true);
  -- Update
  update CK_APPLICATION_LOGIC set
    AL_CODE_LANGUAGE = pl.v.AL_CODE_LANGUAGE
  , AL_CREATE_FLAG = pl.v.AL_CREATE_FLAG
  , AL_EVT_CONTEXT_REPOS_ELEMENT = pl.v.AL_EVT_CONTEXT_REPOS_ELEMENT
  , AL_EVT_EXECUTION_STYLE = pl.v.AL_EVT_EXECUTION_STYLE
  , AL_EVT_NAME = pl.v.AL_EVT_NAME
  , AL_EVT_SEQ_IN_EVENT = pl.v.AL_EVT_SEQ_IN_EVENT
  , AL_EVT_SHORT_DESCRIPTION = pl.v.AL_EVT_SHORT_DESCRIPTION
  , AL_GEN_OWN_REF = pl.v.AL_GEN_OWN_REF
  , AL_GEN_PRODUCT_ID = pl.v.AL_GEN_PRODUCT_ID
  , AL_GEN_UNIQUE_CONTEXT = pl.v.AL_GEN_UNIQUE_CONTEXT
  , AL_ID = pl.v.AL_ID
  , AL_NR_PUBLIC_SCOPE = pl.v.AL_NR_PUBLIC_SCOPE
  , AL_NR_ROUTINE_NAME = pl.v.AL_NR_ROUTINE_NAME
  , AL_NR_TYPE = pl.v.AL_NR_TYPE
  , AL_REPOS_ELEMENT = pl.v.AL_REPOS_ELEMENT
  , AL_SOURCE = pl.v.AL_SOURCE
  , AL_TARGET_LOCATION = pl.v.AL_TARGET_LOCATION
  , AL_TYPE = pl.v.AL_TYPE
  , ELEMENT_TYPE_FOR = pl.v.ELEMENT_TYPE_FOR
  , IRID = pl.v.IRID
  , IVID = pl.v.IVID
  , PAC_REF = pl.v.PAC_REFERENCE
  , PARENT_IVID = pl.v.PARENT_IVID
  where CK_APPLICATION_LOGIC.irid = upd.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'upd','APPLICATION_LOGIC');
    raise;
end upd;
--
-- lck
--
procedure lck
(
  irid in number
) is
  dummy rm.reference;
begin
  -- Lock the Element
    select null into dummy
    from CK_APPLICATION_LOGIC
    where CK_APPLICATION_LOGIC.irid = lck.irid for update nowait
    ;
exception
  when no_data_found then
    rmmes.post(invalid_ref_fac, invalid_ref_code,
              'APPLICATION_LOGIC',lck.irid);
    raise invalid_ref;
  when others then
    -- Oracle error of 'Resource interested is busy'
    if sqlcode = -54 then
      rmmes.post(element_locked_fac,element_locked_code,
                 lck.irid,'APPLICATION_LOGIC');
      raise element_locked;
    end if;
    rmmes.post(operation_failed_fac,operation_failed_code,
              'lck','APPLICATION_LOGIC');
    raise;
end lck;
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
) is
  pl   data;           -- Existing Property Values
begin
  -- Retrieve Existing Properties, if for update
  if for_update then
    open GetP(np.v.irid);
    fetch GetP into
      pl.v.AL_CODE_LANGUAGE
    , pl.v.AL_CREATE_FLAG
    , pl.v.AL_EVT_CONTEXT_REPOS_ELEMENT
    , pl.v.AL_EVT_EXECUTION_STYLE
    , pl.v.AL_EVT_NAME
    , pl.v.AL_EVT_SEQ_IN_EVENT
    , pl.v.AL_EVT_SHORT_DESCRIPTION
    , pl.v.AL_GEN_OWN_REF
    , pl.v.AL_GEN_PRODUCT_ID
    , pl.v.AL_GEN_UNIQUE_CONTEXT
    , pl.v.AL_ID
    , pl.v.AL_NR_PUBLIC_SCOPE
    , pl.v.AL_NR_ROUTINE_NAME
    , pl.v.AL_NR_TYPE
    , pl.v.AL_REPOS_ELEMENT
    , pl.v.AL_SOURCE
    , pl.v.AL_TARGET_LOCATION
    , pl.v.AL_TYPE
    , pl.v.ELEMENT_TYPE_FOR
    , pl.v.IRID
    , pl.v.IVID
    , pl.v.PAC_REFERENCE
    , pl.v.PARENT_IVID
    ;
    if GetP%NOTFOUND then
      close GetP;
      rmmes.post(invalid_ref_fac,invalid_ref_code,
                 'APPLICATION_LOGIC',np.v.irid);
      raise invalid_ref;
    end if;
    close GetP;
  end if;
  -- Not updateable checks
  -- Not applicable to registered types
  -- Not updateable (if existing value is not null) checks
  -- Not applicable to registered types
  -- Old to New Transfer
  if for_update then null;
    if not np.i.AL_CODE_LANGUAGE then
      np.v.AL_CODE_LANGUAGE := pl.v.AL_CODE_LANGUAGE;
    elsif nvl(np.v.AL_CODE_LANGUAGE,pl.v.AL_CODE_LANGUAGE) is null or
          np.v.AL_CODE_LANGUAGE = pl.v.AL_CODE_LANGUAGE then
      np.i.AL_CODE_LANGUAGE := false;
    end if;
    if not np.i.AL_CREATE_FLAG then
      np.v.AL_CREATE_FLAG := pl.v.AL_CREATE_FLAG;
    elsif nvl(np.v.AL_CREATE_FLAG,pl.v.AL_CREATE_FLAG) is null or
          np.v.AL_CREATE_FLAG = pl.v.AL_CREATE_FLAG then
      np.i.AL_CREATE_FLAG := false;
    end if;
    if not np.i.AL_EVT_CONTEXT_REPOS_ELEMENT then
      np.v.AL_EVT_CONTEXT_REPOS_ELEMENT := pl.v.AL_EVT_CONTEXT_REPOS_ELEMENT;
    elsif nvl(np.v.AL_EVT_CONTEXT_REPOS_ELEMENT,pl.v.AL_EVT_CONTEXT_REPOS_ELEMENT) is null or
          np.v.AL_EVT_CONTEXT_REPOS_ELEMENT = pl.v.AL_EVT_CONTEXT_REPOS_ELEMENT then
      np.i.AL_EVT_CONTEXT_REPOS_ELEMENT := false;
    end if;
    if not np.i.AL_EVT_EXECUTION_STYLE then
      np.v.AL_EVT_EXECUTION_STYLE := pl.v.AL_EVT_EXECUTION_STYLE;
    elsif nvl(np.v.AL_EVT_EXECUTION_STYLE,pl.v.AL_EVT_EXECUTION_STYLE) is null or
          np.v.AL_EVT_EXECUTION_STYLE = pl.v.AL_EVT_EXECUTION_STYLE then
      np.i.AL_EVT_EXECUTION_STYLE := false;
    end if;
    if not np.i.AL_EVT_NAME then
      np.v.AL_EVT_NAME := pl.v.AL_EVT_NAME;
    elsif nvl(np.v.AL_EVT_NAME,pl.v.AL_EVT_NAME) is null or
          np.v.AL_EVT_NAME = pl.v.AL_EVT_NAME then
      np.i.AL_EVT_NAME := false;
    end if;
    if not np.i.AL_EVT_SEQ_IN_EVENT then
      np.v.AL_EVT_SEQ_IN_EVENT := pl.v.AL_EVT_SEQ_IN_EVENT;
    elsif nvl(np.v.AL_EVT_SEQ_IN_EVENT,pl.v.AL_EVT_SEQ_IN_EVENT) is null or
          np.v.AL_EVT_SEQ_IN_EVENT = pl.v.AL_EVT_SEQ_IN_EVENT then
      np.i.AL_EVT_SEQ_IN_EVENT := false;
    end if;
    if not np.i.AL_EVT_SHORT_DESCRIPTION then
      np.v.AL_EVT_SHORT_DESCRIPTION := pl.v.AL_EVT_SHORT_DESCRIPTION;
    elsif nvl(np.v.AL_EVT_SHORT_DESCRIPTION,pl.v.AL_EVT_SHORT_DESCRIPTION) is null or
          np.v.AL_EVT_SHORT_DESCRIPTION = pl.v.AL_EVT_SHORT_DESCRIPTION then
      np.i.AL_EVT_SHORT_DESCRIPTION := false;
    end if;
    if not np.i.AL_GEN_OWN_REF then
      np.v.AL_GEN_OWN_REF := pl.v.AL_GEN_OWN_REF;
    elsif nvl(np.v.AL_GEN_OWN_REF,pl.v.AL_GEN_OWN_REF) is null or
          np.v.AL_GEN_OWN_REF = pl.v.AL_GEN_OWN_REF then
      np.i.AL_GEN_OWN_REF := false;
    end if;
    if not np.i.AL_GEN_PRODUCT_ID then
      np.v.AL_GEN_PRODUCT_ID := pl.v.AL_GEN_PRODUCT_ID;
    elsif nvl(np.v.AL_GEN_PRODUCT_ID,pl.v.AL_GEN_PRODUCT_ID) is null or
          np.v.AL_GEN_PRODUCT_ID = pl.v.AL_GEN_PRODUCT_ID then
      np.i.AL_GEN_PRODUCT_ID := false;
    end if;
    if not np.i.AL_GEN_UNIQUE_CONTEXT then
      np.v.AL_GEN_UNIQUE_CONTEXT := pl.v.AL_GEN_UNIQUE_CONTEXT;
    elsif nvl(np.v.AL_GEN_UNIQUE_CONTEXT,pl.v.AL_GEN_UNIQUE_CONTEXT) is null or
          np.v.AL_GEN_UNIQUE_CONTEXT = pl.v.AL_GEN_UNIQUE_CONTEXT then
      np.i.AL_GEN_UNIQUE_CONTEXT := false;
    end if;
    if not np.i.AL_ID then
      np.v.AL_ID := pl.v.AL_ID;
    elsif nvl(np.v.AL_ID,pl.v.AL_ID) is null or
          np.v.AL_ID = pl.v.AL_ID then
      np.i.AL_ID := false;
    end if;
    if not np.i.AL_NR_PUBLIC_SCOPE then
      np.v.AL_NR_PUBLIC_SCOPE := pl.v.AL_NR_PUBLIC_SCOPE;
    elsif nvl(np.v.AL_NR_PUBLIC_SCOPE,pl.v.AL_NR_PUBLIC_SCOPE) is null or
          np.v.AL_NR_PUBLIC_SCOPE = pl.v.AL_NR_PUBLIC_SCOPE then
      np.i.AL_NR_PUBLIC_SCOPE := false;
    end if;
    if not np.i.AL_NR_ROUTINE_NAME then
      np.v.AL_NR_ROUTINE_NAME := pl.v.AL_NR_ROUTINE_NAME;
    elsif nvl(np.v.AL_NR_ROUTINE_NAME,pl.v.AL_NR_ROUTINE_NAME) is null or
          np.v.AL_NR_ROUTINE_NAME = pl.v.AL_NR_ROUTINE_NAME then
      np.i.AL_NR_ROUTINE_NAME := false;
    end if;
    if not np.i.AL_NR_TYPE then
      np.v.AL_NR_TYPE := pl.v.AL_NR_TYPE;
    elsif nvl(np.v.AL_NR_TYPE,pl.v.AL_NR_TYPE) is null or
          np.v.AL_NR_TYPE = pl.v.AL_NR_TYPE then
      np.i.AL_NR_TYPE := false;
    end if;
    if not np.i.AL_REPOS_ELEMENT then
      np.v.AL_REPOS_ELEMENT := pl.v.AL_REPOS_ELEMENT;
    elsif nvl(np.v.AL_REPOS_ELEMENT,pl.v.AL_REPOS_ELEMENT) is null or
          np.v.AL_REPOS_ELEMENT = pl.v.AL_REPOS_ELEMENT then
      np.i.AL_REPOS_ELEMENT := false;
    end if;
    if not np.i.AL_SOURCE then
      np.v.AL_SOURCE := pl.v.AL_SOURCE;
    elsif nvl(np.v.AL_SOURCE,pl.v.AL_SOURCE) is null or
          np.v.AL_SOURCE = pl.v.AL_SOURCE then
      np.i.AL_SOURCE := false;
    end if;
    if not np.i.AL_TARGET_LOCATION then
      np.v.AL_TARGET_LOCATION := pl.v.AL_TARGET_LOCATION;
    elsif nvl(np.v.AL_TARGET_LOCATION,pl.v.AL_TARGET_LOCATION) is null or
          np.v.AL_TARGET_LOCATION = pl.v.AL_TARGET_LOCATION then
      np.i.AL_TARGET_LOCATION := false;
    end if;
    if not np.i.AL_TYPE then
      np.v.AL_TYPE := pl.v.AL_TYPE;
    elsif nvl(np.v.AL_TYPE,pl.v.AL_TYPE) is null or
          np.v.AL_TYPE = pl.v.AL_TYPE then
      np.i.AL_TYPE := false;
    end if;
    if not np.i.ELEMENT_TYPE_FOR then
      np.v.ELEMENT_TYPE_FOR := pl.v.ELEMENT_TYPE_FOR;
    elsif nvl(np.v.ELEMENT_TYPE_FOR,pl.v.ELEMENT_TYPE_FOR) is null or
          np.v.ELEMENT_TYPE_FOR = pl.v.ELEMENT_TYPE_FOR then
      np.i.ELEMENT_TYPE_FOR := false;
    end if;
    if not np.i.IRID then
      np.v.IRID := pl.v.IRID;
    elsif nvl(np.v.IRID,pl.v.IRID) is null or
          np.v.IRID = pl.v.IRID then
      np.i.IRID := false;
    end if;
    if not np.i.IVID then
      np.v.IVID := pl.v.IVID;
    elsif nvl(np.v.IVID,pl.v.IVID) is null or
          np.v.IVID = pl.v.IVID then
      np.i.IVID := false;
    end if;
    if not np.i.PAC_REFERENCE then
      np.v.PAC_REFERENCE := pl.v.PAC_REFERENCE;
    elsif nvl(np.v.PAC_REFERENCE,pl.v.PAC_REFERENCE) is null or
          np.v.PAC_REFERENCE = pl.v.PAC_REFERENCE then
      np.i.PAC_REFERENCE := false;
    end if;
    if not np.i.PARENT_IVID then
      np.v.PARENT_IVID := pl.v.PARENT_IVID;
    elsif nvl(np.v.PARENT_IVID,pl.v.PARENT_IVID) is null or
          np.v.PARENT_IVID = pl.v.PARENT_IVID then
      np.i.PARENT_IVID := false;
    end if;
  end if;
  -- Initialisation (default values)
  if not for_update then
    null;
    if not np.i.AL_CREATE_FLAG then
      np.v.AL_CREATE_FLAG := 'y'  ;
      np.i.AL_CREATE_FLAG := true;
    end if;
  end if; -- Initialisation
  -- Maintenance
  if for_update then
    null;
  end if; -- Maintenance
  -- String Manipulation (case,trim)
  -- Not applicable to registered types
end maintain;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- copy
--
procedure copy_sac( i_irid              in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  )
is
  save_curr_ivid  number;
  save_new_irid   number;
  save_new_ivid   number;
  copy_mode       boolean;
begin
  -- Caller may want to pre-specifiy copied irid
  save_new_irid := pl.v.irid;
  save_new_ivid := pl.v.ivid;
  pl.v.irid := i_irid;
  copy_mode := true;
  maintain(pl, true);
  copy_mode := false;
  pl.v.irid := save_new_irid;
  if save_new_irid is not null then
    pl.i.irid := true;
  end if;
  save_curr_ivid := pl.v.ivid;
  pl.v.ivid := save_new_ivid;
  if save_new_ivid is not null then
    pl.i.ivid := true;
  end if;
  pl.v.pac_reference := null;
  pl.i.pac_reference := true;
  pl.v.parent_ivid   := null;
  pl.i.parent_ivid   := true;
  ins(pl.v.irid, pl);
  if deep = 'Y' then
    jr_copy.copy( i_irid
                , save_curr_ivid
                , pl.v.irid
                , pl.v.ivid
                , null
                , true
                , false
                );
  end if;
  exception
    when others then
      copy_mode := false;
      rmmes.post(operation_failed_fac,operation_failed_code,
                'copy','APPLICATION_LOGIC');
      raise;
end copy_sac;
--
-- Package instantiation block
--
begin
  is_installed := true;
end CKoAPPLICATION_LOGIC;
/
create or replace package SDDoDEPENDENCY is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Properties
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
type rm_values is record (
  CHANGED_BY VARCHAR2(30)
, CLIENT_IRID NUMBER(38)
, CLIENT_IVID NUMBER
, CLIENT_PARENT_IVID NUMBER
, CLIENT_TYPE_ID NUMBER(38)
, COPY_ON_COPY VARCHAR2(1)
, COPY_ON_VERSION VARCHAR2(1)
, CREATED_BY VARCHAR2(30)
, DATE_CHANGED DATE
, DATE_CREATED DATE
, DEPENDENCY_TYPE_IRID NUMBER(38)
, IRID NUMBER(38)
, IVID NUMBER(38)
, NUMBER_OF_TIMES_MODIFIED NUMBER(38)
, PURGE_CANDIDATE VARCHAR2(1)
, REMARK VARCHAR2(70)
, STATUS VARCHAR2(1)
, SUPPLIER_IRID NUMBER(38)
, SUPPLIER_IVID NUMBER
, SUPPLIER_PARENT_IVID NUMBER
, SUPPLIER_TYPE_ID NUMBER(38)
);
type rm_indicators is record (
  CHANGED_BY boolean not null default false
, CLIENT_IRID boolean not null default false
, CLIENT_IVID boolean not null default false
, CLIENT_PARENT_IVID boolean not null default false
, CLIENT_TYPE_ID boolean not null default false
, COPY_ON_COPY boolean not null default false
, COPY_ON_VERSION boolean not null default false
, CREATED_BY boolean not null default false
, DATE_CHANGED boolean not null default false
, DATE_CREATED boolean not null default false
, DEPENDENCY_TYPE_IRID boolean not null default false
, IRID boolean not null default false
, IVID boolean not null default false
, NUMBER_OF_TIMES_MODIFIED boolean not null default false
, PURGE_CANDIDATE boolean not null default false
, REMARK boolean not null default false
, STATUS boolean not null default false
, SUPPLIER_IRID boolean not null default false
, SUPPLIER_IVID boolean not null default false
, SUPPLIER_PARENT_IVID boolean not null default false
, SUPPLIER_TYPE_ID boolean not null default false
);
type data is record (
  v rm_values
, i rm_indicators
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Operations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
);
--
-- del
--
procedure del
(
  irid in number
);
--
-- sel
--
procedure sel
(
  irid in     number
, pl   in out data
);
--
-- upd
--
procedure upd
(
  irid in     number
, pl   in out data
);
--
-- lck
--
procedure lck
(
  irid in number
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   );
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  );
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  );
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Exceptions
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
element_locked exception;
  element_locked_fac  constant varchar2(3) := 'RME';
  element_locked_code constant number      := 10;
invalid_ref exception;
  invalid_ref_fac  constant varchar2(3) := 'RME';
  invalid_ref_code constant number      := 9;
operation_failed exception;
  operation_failed_fac  constant varchar2(3) := 'RME';
  operation_failed_code constant number      := 11;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Globals
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
is_installed boolean := false;
end SDDoDEPENDENCY;
/
create or replace package body SDDoDEPENDENCY is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Cursors
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Retrieve Element Type property values
cursor GetP(irid in rm.reference) is
  select
    el.CHANGED_BY
  , el.CLIENT_IRID
  , el.CLIENT_IVID
  , el.CLIENT_PARENT_IVID
  , el.CLIENT_TYPE_ID
  , el.COPY_ON_COPY
  , el.COPY_ON_VERSION
  , el.CREATED_BY
  , el.DATE_CHANGED
  , el.DATE_CREATED
  , el.DEPENDENCY_TYPE_IRID
  , el.IRID
  , el.IVID
  , el.NUMBER_OF_TIMES_MODIFIED
  , el.PURGE_CANDIDATE
  , el.REMARK
  , el.STATUS
  , el.SUPPLIER_IRID
  , el.SUPPLIER_IVID
  , el.SUPPLIER_PARENT_IVID
  , el.SUPPLIER_TYPE_ID
  from SDD_DEPENDENCIES el
  where el.irid = GetP.irid
  ;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Procedure and Function forward Declarations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Public Procedures
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
) is
begin
  -- If irid is not null copy it into the property list
  if irid is not NULL then
    pl.v.irid := ins.irid;
    pl.i.irid := true;
  end if;
  -- Property Maintenance
  maintain(pl,false);
  if not pl.i.irid then
    pl.v.irid := jr_util.get_new_irid;
    pl.i.irid := true;
  end if;
  if not pl.i.ivid then
    pl.v.ivid := jr_system_util.get_new_ivid;
    pl.i.ivid := true;
  end if;
  -- Insert
  insert into SDD_DEPENDENCIES
  (
    CHANGED_BY
  , CLIENT_IRID
  , CLIENT_IVID
  , CLIENT_PARENT_IVID
  , CLIENT_TYPE_ID
  , COPY_ON_COPY
  , COPY_ON_VERSION
  , CREATED_BY
  , DATE_CHANGED
  , DATE_CREATED
  , DEPENDENCY_TYPE_IRID
  , IRID
  , IVID
  , NUMBER_OF_TIMES_MODIFIED
  , PURGE_CANDIDATE
  , REMARK
  , STATUS
  , SUPPLIER_IRID
  , SUPPLIER_IVID
  , SUPPLIER_PARENT_IVID
  , SUPPLIER_TYPE_ID
  )
  values
  (
    pl.v.CHANGED_BY
  , pl.v.CLIENT_IRID
  , pl.v.CLIENT_IVID
  , pl.v.CLIENT_PARENT_IVID
  , pl.v.CLIENT_TYPE_ID
  , pl.v.COPY_ON_COPY
  , pl.v.COPY_ON_VERSION
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.DEPENDENCY_TYPE_IRID
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.PURGE_CANDIDATE
  , pl.v.REMARK
  , pl.v.STATUS
  , pl.v.SUPPLIER_IRID
  , pl.v.SUPPLIER_IVID
  , pl.v.SUPPLIER_PARENT_IVID
  , pl.v.SUPPLIER_TYPE_ID
  );
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'ins','DEPENDENCY');
    raise;
end ins;
--
-- del
--
procedure del
(
  irid in number
) is
begin
  -- Delete the element itself
  delete from SDD_DEPENDENCIES
  where SDD_DEPENDENCIES.irid = del.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'del','DEPENDENCY');
    raise;
end del;
--
-- sel
--
procedure sel
(
  irid in number
, pl   in out data
) is
begin
  -- Select
  open GetP(sel.irid);
  fetch GetP into
    pl.v.CHANGED_BY
  , pl.v.CLIENT_IRID
  , pl.v.CLIENT_IVID
  , pl.v.CLIENT_PARENT_IVID
  , pl.v.CLIENT_TYPE_ID
  , pl.v.COPY_ON_COPY
  , pl.v.COPY_ON_VERSION
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.DEPENDENCY_TYPE_IRID
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.PURGE_CANDIDATE
  , pl.v.REMARK
  , pl.v.STATUS
  , pl.v.SUPPLIER_IRID
  , pl.v.SUPPLIER_IVID
  , pl.v.SUPPLIER_PARENT_IVID
  , pl.v.SUPPLIER_TYPE_ID
  ;
  if GetP%NOTFOUND then
    close GetP;
    rmmes.post(invalid_ref_fac,invalid_ref_code,
               'DEPENDENCY',sel.irid);
    raise invalid_ref;
  end if;
  close GetP;
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'sel','DEPENDENCY');
    raise;
end sel;
--
-- upd
--
procedure upd
(
  irid in number
, pl   in out data
) is
begin
  -- Property Maintenance
    pl.v.irid := irid;
    maintain(pl,true);
  -- Update
  update SDD_DEPENDENCIES set
    CHANGED_BY = pl.v.CHANGED_BY
  , CLIENT_IRID = pl.v.CLIENT_IRID
  , CLIENT_IVID = pl.v.CLIENT_IVID
  , CLIENT_PARENT_IVID = pl.v.CLIENT_PARENT_IVID
  , CLIENT_TYPE_ID = pl.v.CLIENT_TYPE_ID
  , COPY_ON_COPY = pl.v.COPY_ON_COPY
  , COPY_ON_VERSION = pl.v.COPY_ON_VERSION
  , CREATED_BY = pl.v.CREATED_BY
  , DATE_CHANGED = pl.v.DATE_CHANGED
  , DATE_CREATED = pl.v.DATE_CREATED
  , DEPENDENCY_TYPE_IRID = pl.v.DEPENDENCY_TYPE_IRID
  , IRID = pl.v.IRID
  , IVID = pl.v.IVID
  , NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED
  , PURGE_CANDIDATE = pl.v.PURGE_CANDIDATE
  , REMARK = pl.v.REMARK
  , STATUS = pl.v.STATUS
  , SUPPLIER_IRID = pl.v.SUPPLIER_IRID
  , SUPPLIER_IVID = pl.v.SUPPLIER_IVID
  , SUPPLIER_PARENT_IVID = pl.v.SUPPLIER_PARENT_IVID
  , SUPPLIER_TYPE_ID = pl.v.SUPPLIER_TYPE_ID
  where SDD_DEPENDENCIES.irid = upd.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'upd','DEPENDENCY');
    raise;
end upd;
--
-- lck
--
procedure lck
(
  irid in number
) is
  dummy rm.reference;
begin
  -- Lock the Element
    select null into dummy
    from SDD_DEPENDENCIES
    where SDD_DEPENDENCIES.irid = lck.irid for update nowait
    ;
exception
  when no_data_found then
    rmmes.post(invalid_ref_fac, invalid_ref_code,
              'DEPENDENCY',lck.irid);
    raise invalid_ref;
  when others then
    -- Oracle error of 'Resource interested is busy'
    if sqlcode = -54 then
      rmmes.post(element_locked_fac,element_locked_code,
                 lck.irid,'DEPENDENCY');
      raise element_locked;
    end if;
    rmmes.post(operation_failed_fac,operation_failed_code,
              'lck','DEPENDENCY');
    raise;
end lck;
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
) is
  pl   data;           -- Existing Property Values
begin
  -- Retrieve Existing Properties, if for update
  if for_update then
    open GetP(np.v.irid);
    fetch GetP into
      pl.v.CHANGED_BY
    , pl.v.CLIENT_IRID
    , pl.v.CLIENT_IVID
    , pl.v.CLIENT_PARENT_IVID
    , pl.v.CLIENT_TYPE_ID
    , pl.v.COPY_ON_COPY
    , pl.v.COPY_ON_VERSION
    , pl.v.CREATED_BY
    , pl.v.DATE_CHANGED
    , pl.v.DATE_CREATED
    , pl.v.DEPENDENCY_TYPE_IRID
    , pl.v.IRID
    , pl.v.IVID
    , pl.v.NUMBER_OF_TIMES_MODIFIED
    , pl.v.PURGE_CANDIDATE
    , pl.v.REMARK
    , pl.v.STATUS
    , pl.v.SUPPLIER_IRID
    , pl.v.SUPPLIER_IVID
    , pl.v.SUPPLIER_PARENT_IVID
    , pl.v.SUPPLIER_TYPE_ID
    ;
    if GetP%NOTFOUND then
      close GetP;
      rmmes.post(invalid_ref_fac,invalid_ref_code,
                 'DEPENDENCY',np.v.irid);
      raise invalid_ref;
    end if;
    close GetP;
  end if;
  -- Not updateable checks
  -- Not applicable to registered types
  -- Not updateable (if existing value is not null) checks
  -- Not applicable to registered types
  -- Old to New Transfer
  if for_update then null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := pl.v.CHANGED_BY;
    elsif nvl(np.v.CHANGED_BY,pl.v.CHANGED_BY) is null or
          np.v.CHANGED_BY = pl.v.CHANGED_BY then
      np.i.CHANGED_BY := false;
    end if;
    if not np.i.CLIENT_IRID then
      np.v.CLIENT_IRID := pl.v.CLIENT_IRID;
    elsif nvl(np.v.CLIENT_IRID,pl.v.CLIENT_IRID) is null or
          np.v.CLIENT_IRID = pl.v.CLIENT_IRID then
      np.i.CLIENT_IRID := false;
    end if;
    if not np.i.CLIENT_IVID then
      np.v.CLIENT_IVID := pl.v.CLIENT_IVID;
    elsif nvl(np.v.CLIENT_IVID,pl.v.CLIENT_IVID) is null or
          np.v.CLIENT_IVID = pl.v.CLIENT_IVID then
      np.i.CLIENT_IVID := false;
    end if;
    if not np.i.CLIENT_PARENT_IVID then
      np.v.CLIENT_PARENT_IVID := pl.v.CLIENT_PARENT_IVID;
    elsif nvl(np.v.CLIENT_PARENT_IVID,pl.v.CLIENT_PARENT_IVID) is null or
          np.v.CLIENT_PARENT_IVID = pl.v.CLIENT_PARENT_IVID then
      np.i.CLIENT_PARENT_IVID := false;
    end if;
    if not np.i.CLIENT_TYPE_ID then
      np.v.CLIENT_TYPE_ID := pl.v.CLIENT_TYPE_ID;
    elsif nvl(np.v.CLIENT_TYPE_ID,pl.v.CLIENT_TYPE_ID) is null or
          np.v.CLIENT_TYPE_ID = pl.v.CLIENT_TYPE_ID then
      np.i.CLIENT_TYPE_ID := false;
    end if;
    if not np.i.COPY_ON_COPY then
      np.v.COPY_ON_COPY := pl.v.COPY_ON_COPY;
    elsif nvl(np.v.COPY_ON_COPY,pl.v.COPY_ON_COPY) is null or
          np.v.COPY_ON_COPY = pl.v.COPY_ON_COPY then
      np.i.COPY_ON_COPY := false;
    end if;
    if not np.i.COPY_ON_VERSION then
      np.v.COPY_ON_VERSION := pl.v.COPY_ON_VERSION;
    elsif nvl(np.v.COPY_ON_VERSION,pl.v.COPY_ON_VERSION) is null or
          np.v.COPY_ON_VERSION = pl.v.COPY_ON_VERSION then
      np.i.COPY_ON_VERSION := false;
    end if;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := pl.v.CREATED_BY;
    elsif nvl(np.v.CREATED_BY,pl.v.CREATED_BY) is null or
          np.v.CREATED_BY = pl.v.CREATED_BY then
      np.i.CREATED_BY := false;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := pl.v.DATE_CHANGED;
    elsif nvl(np.v.DATE_CHANGED,pl.v.DATE_CHANGED) is null or
          np.v.DATE_CHANGED = pl.v.DATE_CHANGED then
      np.i.DATE_CHANGED := false;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := pl.v.DATE_CREATED;
    elsif nvl(np.v.DATE_CREATED,pl.v.DATE_CREATED) is null or
          np.v.DATE_CREATED = pl.v.DATE_CREATED then
      np.i.DATE_CREATED := false;
    end if;
    if not np.i.DEPENDENCY_TYPE_IRID then
      np.v.DEPENDENCY_TYPE_IRID := pl.v.DEPENDENCY_TYPE_IRID;
    elsif nvl(np.v.DEPENDENCY_TYPE_IRID,pl.v.DEPENDENCY_TYPE_IRID) is null or
          np.v.DEPENDENCY_TYPE_IRID = pl.v.DEPENDENCY_TYPE_IRID then
      np.i.DEPENDENCY_TYPE_IRID := false;
    end if;
    if not np.i.IRID then
      np.v.IRID := pl.v.IRID;
    elsif nvl(np.v.IRID,pl.v.IRID) is null or
          np.v.IRID = pl.v.IRID then
      np.i.IRID := false;
    end if;
    if not np.i.IVID then
      np.v.IVID := pl.v.IVID;
    elsif nvl(np.v.IVID,pl.v.IVID) is null or
          np.v.IVID = pl.v.IVID then
      np.i.IVID := false;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := pl.v.NUMBER_OF_TIMES_MODIFIED;
    elsif nvl(np.v.NUMBER_OF_TIMES_MODIFIED,pl.v.NUMBER_OF_TIMES_MODIFIED) is null or
          np.v.NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED then
      np.i.NUMBER_OF_TIMES_MODIFIED := false;
    end if;
    if not np.i.PURGE_CANDIDATE then
      np.v.PURGE_CANDIDATE := pl.v.PURGE_CANDIDATE;
    elsif nvl(np.v.PURGE_CANDIDATE,pl.v.PURGE_CANDIDATE) is null or
          np.v.PURGE_CANDIDATE = pl.v.PURGE_CANDIDATE then
      np.i.PURGE_CANDIDATE := false;
    end if;
    if not np.i.REMARK then
      np.v.REMARK := pl.v.REMARK;
    elsif nvl(np.v.REMARK,pl.v.REMARK) is null or
          np.v.REMARK = pl.v.REMARK then
      np.i.REMARK := false;
    end if;
    if not np.i.STATUS then
      np.v.STATUS := pl.v.STATUS;
    elsif nvl(np.v.STATUS,pl.v.STATUS) is null or
          np.v.STATUS = pl.v.STATUS then
      np.i.STATUS := false;
    end if;
    if not np.i.SUPPLIER_IRID then
      np.v.SUPPLIER_IRID := pl.v.SUPPLIER_IRID;
    elsif nvl(np.v.SUPPLIER_IRID,pl.v.SUPPLIER_IRID) is null or
          np.v.SUPPLIER_IRID = pl.v.SUPPLIER_IRID then
      np.i.SUPPLIER_IRID := false;
    end if;
    if not np.i.SUPPLIER_IVID then
      np.v.SUPPLIER_IVID := pl.v.SUPPLIER_IVID;
    elsif nvl(np.v.SUPPLIER_IVID,pl.v.SUPPLIER_IVID) is null or
          np.v.SUPPLIER_IVID = pl.v.SUPPLIER_IVID then
      np.i.SUPPLIER_IVID := false;
    end if;
    if not np.i.SUPPLIER_PARENT_IVID then
      np.v.SUPPLIER_PARENT_IVID := pl.v.SUPPLIER_PARENT_IVID;
    elsif nvl(np.v.SUPPLIER_PARENT_IVID,pl.v.SUPPLIER_PARENT_IVID) is null or
          np.v.SUPPLIER_PARENT_IVID = pl.v.SUPPLIER_PARENT_IVID then
      np.i.SUPPLIER_PARENT_IVID := false;
    end if;
    if not np.i.SUPPLIER_TYPE_ID then
      np.v.SUPPLIER_TYPE_ID := pl.v.SUPPLIER_TYPE_ID;
    elsif nvl(np.v.SUPPLIER_TYPE_ID,pl.v.SUPPLIER_TYPE_ID) is null or
          np.v.SUPPLIER_TYPE_ID = pl.v.SUPPLIER_TYPE_ID then
      np.i.SUPPLIER_TYPE_ID := false;
    end if;
  end if;
  -- Initialisation (default values)
  if not for_update then
    null;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := user;
      np.i.CREATED_BY := true;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := sysdate;
      np.i.DATE_CREATED := true;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := 0;
      np.i.NUMBER_OF_TIMES_MODIFIED := true;
    end if;
    if not np.i.STATUS then
      np.v.STATUS := 'C'     ;
      np.i.STATUS := true;
    end if;
  end if; -- Initialisation
  -- Maintenance
  if for_update then
    null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := user;
      np.i.CHANGED_BY := true;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := sysdate;
      np.i.DATE_CHANGED := true;
    end if;
  end if; -- Maintenance
  -- String Manipulation (case,trim)
  -- Not applicable to registered types
end maintain;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   )
is
  lck boolean;
begin
  if i_lock = 'Y' then
    lck := true;
  else
    lck := false;
  end if;
  o_ivid := jr_version.check_out(i_irid, i_ivid, lck, i_notes, i_vlabel);
end check_out;
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  )
is
begin
  o_ivid := jr_version.check_in(i_irid, i_ivid, i_notes, i_vlabel);
end check_in;
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  )
is
  save_curr_ivid  number;
  save_new_irid   number;
  save_new_ivid   number;
  copy_mode       boolean;
begin
  -- Caller may want to pre-specifiy copied irid
  save_new_irid := pl.v.irid;
  save_new_ivid := pl.v.ivid;
  pl.v.irid := i_irid;
  copy_mode := true;
  maintain(pl, true);
  copy_mode := false;
  pl.v.irid := save_new_irid;
  if save_new_irid is not null then
    pl.i.irid := true;
  end if;
  save_curr_ivid := pl.v.ivid;
  pl.v.ivid := save_new_ivid;
  if save_new_ivid is not null then
    pl.i.ivid := true;
  end if;
  ins(pl.v.irid, pl);
  if deep = 'Y' then
    jr_copy.copy( i_irid
                , save_curr_ivid
                , pl.v.irid
                , pl.v.ivid
                , null
                , true
                , false
                );
  end if;
  exception
    when others then
      copy_mode := false;
      rmmes.post(operation_failed_fac,operation_failed_code,
                'copy','DEPENDENCY');
      raise;
end copy_pac;
--
-- Package instantiation block
--
begin
  is_installed := true;
end SDDoDEPENDENCY;
/
create or replace package SDDoDEPENDENCY_TYPE is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Properties
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
type rm_values is record (
  CHANGED_BY VARCHAR2(30)
, CREATED_BY VARCHAR2(30)
, DATE_CHANGED DATE
, DATE_CREATED DATE
, IRID NUMBER(38)
, IVID NUMBER(38)
, NUMBER_OF_TIMES_MODIFIED NUMBER(38)
, SUBTYPE VARCHAR2(256)
, SYSTEM_FLAG VARCHAR2(1)
, TRANSITIVE VARCHAR2(1)
, TYPE VARCHAR2(256)
);
type rm_indicators is record (
  CHANGED_BY boolean not null default false
, CREATED_BY boolean not null default false
, DATE_CHANGED boolean not null default false
, DATE_CREATED boolean not null default false
, IRID boolean not null default false
, IVID boolean not null default false
, NUMBER_OF_TIMES_MODIFIED boolean not null default false
, SUBTYPE boolean not null default false
, SYSTEM_FLAG boolean not null default false
, TRANSITIVE boolean not null default false
, TYPE boolean not null default false
);
type data is record (
  v rm_values
, i rm_indicators
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Operations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
);
--
-- del
--
procedure del
(
  irid in number
);
--
-- sel
--
procedure sel
(
  irid in     number
, pl   in out data
);
--
-- upd
--
procedure upd
(
  irid in     number
, pl   in out data
);
--
-- lck
--
procedure lck
(
  irid in number
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   );
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  );
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  );
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Exceptions
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
element_locked exception;
  element_locked_fac  constant varchar2(3) := 'RME';
  element_locked_code constant number      := 10;
invalid_ref exception;
  invalid_ref_fac  constant varchar2(3) := 'RME';
  invalid_ref_code constant number      := 9;
operation_failed exception;
  operation_failed_fac  constant varchar2(3) := 'RME';
  operation_failed_code constant number      := 11;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Globals
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
is_installed boolean := false;
end SDDoDEPENDENCY_TYPE;
/
create or replace package body SDDoDEPENDENCY_TYPE is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Cursors
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Retrieve Element Type property values
cursor GetP(irid in rm.reference) is
  select
    el.CHANGED_BY
  , el.CREATED_BY
  , el.DATE_CHANGED
  , el.DATE_CREATED
  , el.IRID
  , el.IVID
  , el.NUMBER_OF_TIMES_MODIFIED
  , el.SUBTYPE
  , el.SYSTEM_FLAG
  , el.TRANSITIVE
  , el.TYPE
  from SDD_DEPENDENCY_TYPES el
  where el.irid = GetP.irid
  ;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Procedure and Function forward Declarations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Public Procedures
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
) is
begin
  -- If irid is not null copy it into the property list
  if irid is not NULL then
    pl.v.irid := ins.irid;
    pl.i.irid := true;
  end if;
  -- Property Maintenance
  maintain(pl,false);
  if not pl.i.irid then
    pl.v.irid := jr_util.get_new_irid;
    pl.i.irid := true;
  end if;
  if not pl.i.ivid then
    pl.v.ivid := jr_system_util.get_new_ivid;
    pl.i.ivid := true;
  end if;
  -- Insert
  insert into SDD_DEPENDENCY_TYPES
  (
    CHANGED_BY
  , CREATED_BY
  , DATE_CHANGED
  , DATE_CREATED
  , IRID
  , IVID
  , NUMBER_OF_TIMES_MODIFIED
  , SUBTYPE
  , SYSTEM_FLAG
  , TRANSITIVE
  , TYPE
  )
  values
  (
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.SUBTYPE
  , pl.v.SYSTEM_FLAG
  , pl.v.TRANSITIVE
  , pl.v.TYPE
  );
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'ins','DEPENDENCY_TYPE');
    raise;
end ins;
--
-- del
--
procedure del
(
  irid in number
) is
begin
  -- Delete the element itself
  delete from SDD_DEPENDENCY_TYPES
  where SDD_DEPENDENCY_TYPES.irid = del.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'del','DEPENDENCY_TYPE');
    raise;
end del;
--
-- sel
--
procedure sel
(
  irid in number
, pl   in out data
) is
begin
  -- Select
  open GetP(sel.irid);
  fetch GetP into
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.SUBTYPE
  , pl.v.SYSTEM_FLAG
  , pl.v.TRANSITIVE
  , pl.v.TYPE
  ;
  if GetP%NOTFOUND then
    close GetP;
    rmmes.post(invalid_ref_fac,invalid_ref_code,
               'DEPENDENCY_TYPE',sel.irid);
    raise invalid_ref;
  end if;
  close GetP;
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'sel','DEPENDENCY_TYPE');
    raise;
end sel;
--
-- upd
--
procedure upd
(
  irid in number
, pl   in out data
) is
begin
  -- Property Maintenance
    pl.v.irid := irid;
    maintain(pl,true);
  -- Update
  update SDD_DEPENDENCY_TYPES set
    CHANGED_BY = pl.v.CHANGED_BY
  , CREATED_BY = pl.v.CREATED_BY
  , DATE_CHANGED = pl.v.DATE_CHANGED
  , DATE_CREATED = pl.v.DATE_CREATED
  , IRID = pl.v.IRID
  , IVID = pl.v.IVID
  , NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED
  , SUBTYPE = pl.v.SUBTYPE
  , SYSTEM_FLAG = pl.v.SYSTEM_FLAG
  , TRANSITIVE = pl.v.TRANSITIVE
  , TYPE = pl.v.TYPE
  where SDD_DEPENDENCY_TYPES.irid = upd.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'upd','DEPENDENCY_TYPE');
    raise;
end upd;
--
-- lck
--
procedure lck
(
  irid in number
) is
  dummy rm.reference;
begin
  -- Lock the Element
    select null into dummy
    from SDD_DEPENDENCY_TYPES
    where SDD_DEPENDENCY_TYPES.irid = lck.irid for update nowait
    ;
exception
  when no_data_found then
    rmmes.post(invalid_ref_fac, invalid_ref_code,
              'DEPENDENCY_TYPE',lck.irid);
    raise invalid_ref;
  when others then
    -- Oracle error of 'Resource interested is busy'
    if sqlcode = -54 then
      rmmes.post(element_locked_fac,element_locked_code,
                 lck.irid,'DEPENDENCY_TYPE');
      raise element_locked;
    end if;
    rmmes.post(operation_failed_fac,operation_failed_code,
              'lck','DEPENDENCY_TYPE');
    raise;
end lck;
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
) is
  pl   data;           -- Existing Property Values
begin
  -- Retrieve Existing Properties, if for update
  if for_update then
    open GetP(np.v.irid);
    fetch GetP into
      pl.v.CHANGED_BY
    , pl.v.CREATED_BY
    , pl.v.DATE_CHANGED
    , pl.v.DATE_CREATED
    , pl.v.IRID
    , pl.v.IVID
    , pl.v.NUMBER_OF_TIMES_MODIFIED
    , pl.v.SUBTYPE
    , pl.v.SYSTEM_FLAG
    , pl.v.TRANSITIVE
    , pl.v.TYPE
    ;
    if GetP%NOTFOUND then
      close GetP;
      rmmes.post(invalid_ref_fac,invalid_ref_code,
                 'DEPENDENCY_TYPE',np.v.irid);
      raise invalid_ref;
    end if;
    close GetP;
  end if;
  -- Not updateable checks
  -- Not applicable to registered types
  -- Not updateable (if existing value is not null) checks
  -- Not applicable to registered types
  -- Old to New Transfer
  if for_update then null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := pl.v.CHANGED_BY;
    elsif nvl(np.v.CHANGED_BY,pl.v.CHANGED_BY) is null or
          np.v.CHANGED_BY = pl.v.CHANGED_BY then
      np.i.CHANGED_BY := false;
    end if;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := pl.v.CREATED_BY;
    elsif nvl(np.v.CREATED_BY,pl.v.CREATED_BY) is null or
          np.v.CREATED_BY = pl.v.CREATED_BY then
      np.i.CREATED_BY := false;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := pl.v.DATE_CHANGED;
    elsif nvl(np.v.DATE_CHANGED,pl.v.DATE_CHANGED) is null or
          np.v.DATE_CHANGED = pl.v.DATE_CHANGED then
      np.i.DATE_CHANGED := false;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := pl.v.DATE_CREATED;
    elsif nvl(np.v.DATE_CREATED,pl.v.DATE_CREATED) is null or
          np.v.DATE_CREATED = pl.v.DATE_CREATED then
      np.i.DATE_CREATED := false;
    end if;
    if not np.i.IRID then
      np.v.IRID := pl.v.IRID;
    elsif nvl(np.v.IRID,pl.v.IRID) is null or
          np.v.IRID = pl.v.IRID then
      np.i.IRID := false;
    end if;
    if not np.i.IVID then
      np.v.IVID := pl.v.IVID;
    elsif nvl(np.v.IVID,pl.v.IVID) is null or
          np.v.IVID = pl.v.IVID then
      np.i.IVID := false;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := pl.v.NUMBER_OF_TIMES_MODIFIED;
    elsif nvl(np.v.NUMBER_OF_TIMES_MODIFIED,pl.v.NUMBER_OF_TIMES_MODIFIED) is null or
          np.v.NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED then
      np.i.NUMBER_OF_TIMES_MODIFIED := false;
    end if;
    if not np.i.SUBTYPE then
      np.v.SUBTYPE := pl.v.SUBTYPE;
    elsif nvl(np.v.SUBTYPE,pl.v.SUBTYPE) is null or
          np.v.SUBTYPE = pl.v.SUBTYPE then
      np.i.SUBTYPE := false;
    end if;
    if not np.i.SYSTEM_FLAG then
      np.v.SYSTEM_FLAG := pl.v.SYSTEM_FLAG;
    elsif nvl(np.v.SYSTEM_FLAG,pl.v.SYSTEM_FLAG) is null or
          np.v.SYSTEM_FLAG = pl.v.SYSTEM_FLAG then
      np.i.SYSTEM_FLAG := false;
    end if;
    if not np.i.TRANSITIVE then
      np.v.TRANSITIVE := pl.v.TRANSITIVE;
    elsif nvl(np.v.TRANSITIVE,pl.v.TRANSITIVE) is null or
          np.v.TRANSITIVE = pl.v.TRANSITIVE then
      np.i.TRANSITIVE := false;
    end if;
    if not np.i.TYPE then
      np.v.TYPE := pl.v.TYPE;
    elsif nvl(np.v.TYPE,pl.v.TYPE) is null or
          np.v.TYPE = pl.v.TYPE then
      np.i.TYPE := false;
    end if;
  end if;
  -- Initialisation (default values)
  if not for_update then
    null;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := user;
      np.i.CREATED_BY := true;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := sysdate;
      np.i.DATE_CREATED := true;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := 0;
      np.i.NUMBER_OF_TIMES_MODIFIED := true;
    end if;
    if not np.i.TRANSITIVE then
      np.v.TRANSITIVE := 'N'     ;
      np.i.TRANSITIVE := true;
    end if;
  end if; -- Initialisation
  -- Maintenance
  if for_update then
    null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := user;
      np.i.CHANGED_BY := true;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := sysdate;
      np.i.DATE_CHANGED := true;
    end if;
  end if; -- Maintenance
  -- String Manipulation (case,trim)
  -- Not applicable to registered types
end maintain;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   )
is
  lck boolean;
begin
  if i_lock = 'Y' then
    lck := true;
  else
    lck := false;
  end if;
  o_ivid := jr_version.check_out(i_irid, i_ivid, lck, i_notes, i_vlabel);
end check_out;
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  )
is
begin
  o_ivid := jr_version.check_in(i_irid, i_ivid, i_notes, i_vlabel);
end check_in;
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  )
is
  save_curr_ivid  number;
  save_new_irid   number;
  save_new_ivid   number;
  copy_mode       boolean;
begin
  -- Caller may want to pre-specifiy copied irid
  save_new_irid := pl.v.irid;
  save_new_ivid := pl.v.ivid;
  pl.v.irid := i_irid;
  copy_mode := true;
  maintain(pl, true);
  copy_mode := false;
  pl.v.irid := save_new_irid;
  if save_new_irid is not null then
    pl.i.irid := true;
  end if;
  save_curr_ivid := pl.v.ivid;
  pl.v.ivid := save_new_ivid;
  if save_new_ivid is not null then
    pl.i.ivid := true;
  end if;
  ins(pl.v.irid, pl);
  if deep = 'Y' then
    jr_copy.copy( i_irid
                , save_curr_ivid
                , pl.v.irid
                , pl.v.ivid
                , null
                , true
                , false
                );
  end if;
  exception
    when others then
      copy_mode := false;
      rmmes.post(operation_failed_fac,operation_failed_code,
                'copy','DEPENDENCY_TYPE');
      raise;
end copy_pac;
--
-- Package instantiation block
--
begin
  is_installed := true;
end SDDoDEPENDENCY_TYPE;
/
create or replace package SDDoDEPEND_STATUS is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Properties
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
type rm_values is record (
  CHANGED_BY VARCHAR2(30)
, CREATED_BY VARCHAR2(30)
, DATE_CHANGED DATE
, DATE_CREATED DATE
, DESCRIPTION VARCHAR2(256)
, NUMBER_OF_TIMES_MODIFIED NUMBER(38)
, STATUS VARCHAR2(1)
, SYSTEM_FLAG VARCHAR2(1)
);
type rm_indicators is record (
  CHANGED_BY boolean not null default false
, CREATED_BY boolean not null default false
, DATE_CHANGED boolean not null default false
, DATE_CREATED boolean not null default false
, DESCRIPTION boolean not null default false
, NUMBER_OF_TIMES_MODIFIED boolean not null default false
, STATUS boolean not null default false
, SYSTEM_FLAG boolean not null default false
);
type data is record (
  v rm_values
, i rm_indicators
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Operations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
);
--
-- del
--
procedure del
(
  irid in number
);
--
-- sel
--
procedure sel
(
  irid in     number
, pl   in out data
);
--
-- upd
--
procedure upd
(
  irid in     number
, pl   in out data
);
--
-- lck
--
procedure lck
(
  irid in number
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   );
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  );
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  );
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Exceptions
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
element_locked exception;
  element_locked_fac  constant varchar2(3) := 'RME';
  element_locked_code constant number      := 10;
invalid_ref exception;
  invalid_ref_fac  constant varchar2(3) := 'RME';
  invalid_ref_code constant number      := 9;
operation_failed exception;
  operation_failed_fac  constant varchar2(3) := 'RME';
  operation_failed_code constant number      := 11;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Globals
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
is_installed boolean := false;
end SDDoDEPEND_STATUS;
/
create or replace package body SDDoDEPEND_STATUS is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Cursors
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Retrieve Element Type property values
cursor GetP(irid in rm.reference) is
  select
    el.CHANGED_BY
  , el.CREATED_BY
  , el.DATE_CHANGED
  , el.DATE_CREATED
  , el.DESCRIPTION
  , el.NUMBER_OF_TIMES_MODIFIED
  , el.STATUS
  , el.SYSTEM_FLAG
  from SDD_DEPEND_STATUSES el
  where el.irid = GetP.irid
  ;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Procedure and Function forward Declarations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Public Procedures
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
) is
begin
  -- If irid is not null copy it into the property list
  if irid is not NULL then
    pl.v.irid := ins.irid;
    pl.i.irid := true;
  end if;
  -- Property Maintenance
  maintain(pl,false);
  if not pl.i.irid then
    pl.v.irid := jr_util.get_new_irid;
    pl.i.irid := true;
  end if;
  if not pl.i.ivid then
    pl.v.ivid := jr_system_util.get_new_ivid;
    pl.i.ivid := true;
  end if;
  -- Insert
  insert into SDD_DEPEND_STATUSES
  (
    CHANGED_BY
  , CREATED_BY
  , DATE_CHANGED
  , DATE_CREATED
  , DESCRIPTION
  , NUMBER_OF_TIMES_MODIFIED
  , STATUS
  , SYSTEM_FLAG
  )
  values
  (
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.DESCRIPTION
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.STATUS
  , pl.v.SYSTEM_FLAG
  );
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'ins','DEPEND_STATUS');
    raise;
end ins;
--
-- del
--
procedure del
(
  irid in number
) is
begin
  -- Delete the element itself
  delete from SDD_DEPEND_STATUSES
  where SDD_DEPEND_STATUSES.irid = del.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'del','DEPEND_STATUS');
    raise;
end del;
--
-- sel
--
procedure sel
(
  irid in number
, pl   in out data
) is
begin
  -- Select
  open GetP(sel.irid);
  fetch GetP into
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.DESCRIPTION
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.STATUS
  , pl.v.SYSTEM_FLAG
  ;
  if GetP%NOTFOUND then
    close GetP;
    rmmes.post(invalid_ref_fac,invalid_ref_code,
               'DEPEND_STATUS',sel.irid);
    raise invalid_ref;
  end if;
  close GetP;
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'sel','DEPEND_STATUS');
    raise;
end sel;
--
-- upd
--
procedure upd
(
  irid in number
, pl   in out data
) is
begin
  -- Property Maintenance
    pl.v.irid := irid;
    maintain(pl,true);
  -- Update
  update SDD_DEPEND_STATUSES set
    CHANGED_BY = pl.v.CHANGED_BY
  , CREATED_BY = pl.v.CREATED_BY
  , DATE_CHANGED = pl.v.DATE_CHANGED
  , DATE_CREATED = pl.v.DATE_CREATED
  , DESCRIPTION = pl.v.DESCRIPTION
  , NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED
  , STATUS = pl.v.STATUS
  , SYSTEM_FLAG = pl.v.SYSTEM_FLAG
  where SDD_DEPEND_STATUSES.irid = upd.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'upd','DEPEND_STATUS');
    raise;
end upd;
--
-- lck
--
procedure lck
(
  irid in number
) is
  dummy rm.reference;
begin
  -- Lock the Element
    select null into dummy
    from SDD_DEPEND_STATUSES
    where SDD_DEPEND_STATUSES.irid = lck.irid for update nowait
    ;
exception
  when no_data_found then
    rmmes.post(invalid_ref_fac, invalid_ref_code,
              'DEPEND_STATUS',lck.irid);
    raise invalid_ref;
  when others then
    -- Oracle error of 'Resource interested is busy'
    if sqlcode = -54 then
      rmmes.post(element_locked_fac,element_locked_code,
                 lck.irid,'DEPEND_STATUS');
      raise element_locked;
    end if;
    rmmes.post(operation_failed_fac,operation_failed_code,
              'lck','DEPEND_STATUS');
    raise;
end lck;
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
) is
  pl   data;           -- Existing Property Values
begin
  -- Retrieve Existing Properties, if for update
  if for_update then
    open GetP(np.v.irid);
    fetch GetP into
      pl.v.CHANGED_BY
    , pl.v.CREATED_BY
    , pl.v.DATE_CHANGED
    , pl.v.DATE_CREATED
    , pl.v.DESCRIPTION
    , pl.v.NUMBER_OF_TIMES_MODIFIED
    , pl.v.STATUS
    , pl.v.SYSTEM_FLAG
    ;
    if GetP%NOTFOUND then
      close GetP;
      rmmes.post(invalid_ref_fac,invalid_ref_code,
                 'DEPEND_STATUS',np.v.irid);
      raise invalid_ref;
    end if;
    close GetP;
  end if;
  -- Not updateable checks
  -- Not applicable to registered types
  -- Not updateable (if existing value is not null) checks
  -- Not applicable to registered types
  -- Old to New Transfer
  if for_update then null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := pl.v.CHANGED_BY;
    elsif nvl(np.v.CHANGED_BY,pl.v.CHANGED_BY) is null or
          np.v.CHANGED_BY = pl.v.CHANGED_BY then
      np.i.CHANGED_BY := false;
    end if;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := pl.v.CREATED_BY;
    elsif nvl(np.v.CREATED_BY,pl.v.CREATED_BY) is null or
          np.v.CREATED_BY = pl.v.CREATED_BY then
      np.i.CREATED_BY := false;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := pl.v.DATE_CHANGED;
    elsif nvl(np.v.DATE_CHANGED,pl.v.DATE_CHANGED) is null or
          np.v.DATE_CHANGED = pl.v.DATE_CHANGED then
      np.i.DATE_CHANGED := false;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := pl.v.DATE_CREATED;
    elsif nvl(np.v.DATE_CREATED,pl.v.DATE_CREATED) is null or
          np.v.DATE_CREATED = pl.v.DATE_CREATED then
      np.i.DATE_CREATED := false;
    end if;
    if not np.i.DESCRIPTION then
      np.v.DESCRIPTION := pl.v.DESCRIPTION;
    elsif nvl(np.v.DESCRIPTION,pl.v.DESCRIPTION) is null or
          np.v.DESCRIPTION = pl.v.DESCRIPTION then
      np.i.DESCRIPTION := false;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := pl.v.NUMBER_OF_TIMES_MODIFIED;
    elsif nvl(np.v.NUMBER_OF_TIMES_MODIFIED,pl.v.NUMBER_OF_TIMES_MODIFIED) is null or
          np.v.NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED then
      np.i.NUMBER_OF_TIMES_MODIFIED := false;
    end if;
    if not np.i.STATUS then
      np.v.STATUS := pl.v.STATUS;
    elsif nvl(np.v.STATUS,pl.v.STATUS) is null or
          np.v.STATUS = pl.v.STATUS then
      np.i.STATUS := false;
    end if;
    if not np.i.SYSTEM_FLAG then
      np.v.SYSTEM_FLAG := pl.v.SYSTEM_FLAG;
    elsif nvl(np.v.SYSTEM_FLAG,pl.v.SYSTEM_FLAG) is null or
          np.v.SYSTEM_FLAG = pl.v.SYSTEM_FLAG then
      np.i.SYSTEM_FLAG := false;
    end if;
  end if;
  -- Initialisation (default values)
  if not for_update then
    null;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := user;
      np.i.CREATED_BY := true;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := sysdate;
      np.i.DATE_CREATED := true;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := 0;
      np.i.NUMBER_OF_TIMES_MODIFIED := true;
    end if;
  end if; -- Initialisation
  -- Maintenance
  if for_update then
    null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := user;
      np.i.CHANGED_BY := true;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := sysdate;
      np.i.DATE_CHANGED := true;
    end if;
  end if; -- Maintenance
  -- String Manipulation (case,trim)
  -- Not applicable to registered types
end maintain;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   )
is
  lck boolean;
begin
  if i_lock = 'Y' then
    lck := true;
  else
    lck := false;
  end if;
  o_ivid := jr_version.check_out(i_irid, i_ivid, lck, i_notes, i_vlabel);
end check_out;
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  )
is
begin
  o_ivid := jr_version.check_in(i_irid, i_ivid, i_notes, i_vlabel);
end check_in;
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  )
is
  save_curr_ivid  number;
  save_new_irid   number;
  save_new_ivid   number;
  copy_mode       boolean;
begin
  -- Caller may want to pre-specifiy copied irid
  save_new_irid := pl.v.irid;
  save_new_ivid := pl.v.ivid;
  pl.v.irid := i_irid;
  copy_mode := true;
  maintain(pl, true);
  copy_mode := false;
  pl.v.irid := save_new_irid;
  if save_new_irid is not null then
    pl.i.irid := true;
  end if;
  save_curr_ivid := pl.v.ivid;
  pl.v.ivid := save_new_ivid;
  if save_new_ivid is not null then
    pl.i.ivid := true;
  end if;
  ins(pl.v.irid, pl);
  if deep = 'Y' then
    jr_copy.copy( i_irid
                , save_curr_ivid
                , pl.v.irid
                , pl.v.ivid
                , null
                , true
                , false
                );
  end if;
  exception
    when others then
      copy_mode := false;
      rmmes.post(operation_failed_fac,operation_failed_code,
                'copy','DEPEND_STATUS');
      raise;
end copy_pac;
--
-- Package instantiation block
--
begin
  is_installed := true;
end SDDoDEPEND_STATUS;
/
create or replace package SDDoDPND_PURGING_BR_POLICY is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Properties
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
type rm_values is record (
  BRANCH_ID NUMBER(38)
, CHANGED_BY VARCHAR2(30)
, CREATED_BY VARCHAR2(30)
, DATE_CHANGED DATE
, DATE_CREATED DATE
, IRID NUMBER(38)
, IVID NUMBER(38)
, NUMBER_OF_TIMES_MODIFIED NUMBER(38)
, NUMBER_OF_VERSIONS NUMBER
, POLICY_ID NUMBER(38)
);
type rm_indicators is record (
  BRANCH_ID boolean not null default false
, CHANGED_BY boolean not null default false
, CREATED_BY boolean not null default false
, DATE_CHANGED boolean not null default false
, DATE_CREATED boolean not null default false
, IRID boolean not null default false
, IVID boolean not null default false
, NUMBER_OF_TIMES_MODIFIED boolean not null default false
, NUMBER_OF_VERSIONS boolean not null default false
, POLICY_ID boolean not null default false
);
type data is record (
  v rm_values
, i rm_indicators
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Operations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
);
--
-- del
--
procedure del
(
  irid in number
);
--
-- sel
--
procedure sel
(
  irid in     number
, pl   in out data
);
--
-- upd
--
procedure upd
(
  irid in     number
, pl   in out data
);
--
-- lck
--
procedure lck
(
  irid in number
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   );
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  );
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  );
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Exceptions
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
element_locked exception;
  element_locked_fac  constant varchar2(3) := 'RME';
  element_locked_code constant number      := 10;
invalid_ref exception;
  invalid_ref_fac  constant varchar2(3) := 'RME';
  invalid_ref_code constant number      := 9;
operation_failed exception;
  operation_failed_fac  constant varchar2(3) := 'RME';
  operation_failed_code constant number      := 11;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Globals
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
is_installed boolean := false;
end SDDoDPND_PURGING_BR_POLICY;
/
create or replace package body SDDoDPND_PURGING_BR_POLICY is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Cursors
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Retrieve Element Type property values
cursor GetP(irid in rm.reference) is
  select
    el.BRANCH_ID
  , el.CHANGED_BY
  , el.CREATED_BY
  , el.DATE_CHANGED
  , el.DATE_CREATED
  , el.IRID
  , el.IVID
  , el.NUMBER_OF_TIMES_MODIFIED
  , el.NUMBER_OF_VERSIONS
  , el.POLICY_ID
  from SDD_DPND_PURGING_BR_POLICIES el
  where el.irid = GetP.irid
  ;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Procedure and Function forward Declarations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Public Procedures
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
) is
  l_branch_name i$sdd_branches.name%TYPE;
begin
  -- If irid is not null copy it into the property list
  if irid is not NULL then
    pl.v.irid := ins.irid;
    pl.i.irid := true;
  end if;
  -- Property Maintenance
  maintain(pl,false);
  if not pl.i.irid then
    pl.v.irid := jr_util.get_new_irid;
    pl.i.irid := true;
  end if;
  if not pl.i.ivid then
    pl.v.ivid := jr_system_util.get_new_ivid;
    pl.i.ivid := true;
  end if;
  -- Insert
  insert into SDD_DPND_PURGING_BR_POLICIES
  (
    BRANCH_ID
  , CHANGED_BY
  , CREATED_BY
  , DATE_CHANGED
  , DATE_CREATED
  , IRID
  , IVID
  , NUMBER_OF_TIMES_MODIFIED
  , NUMBER_OF_VERSIONS
  , POLICY_ID
  )
  values
  (
    pl.v.BRANCH_ID
  , pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.NUMBER_OF_VERSIONS
  , pl.v.POLICY_ID
  );
  -- Constraints
  -- Not applicable to registered types
exception
  when DUP_VAL_ON_INDEX then 
  
    -- get the branch name for 
    -- our error msg
    select name 
    into l_branch_name
    from i$sdd_branches
    where branch_id = pl.v.BRANCH_ID;

    -- branch name conflicts 
    -- with already entered branch name <name>
    rmmes.post('CDR', 6074, l_branch_name);
    raise;

  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'ins','DPND_PURGING_BR_POLICY');
    raise;
end ins;
--
-- del
--
procedure del
(
  irid in number
) is
begin
  -- Delete the element itself
  delete from SDD_DPND_PURGING_BR_POLICIES
  where SDD_DPND_PURGING_BR_POLICIES.irid = del.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'del','DPND_PURGING_BR_POLICY');
    raise;
end del;
--
-- sel
--
procedure sel
(
  irid in number
, pl   in out data
) is
begin
  -- Select
  open GetP(sel.irid);
  fetch GetP into
    pl.v.BRANCH_ID
  , pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.NUMBER_OF_VERSIONS
  , pl.v.POLICY_ID
  ;
  if GetP%NOTFOUND then
    close GetP;
    rmmes.post(invalid_ref_fac,invalid_ref_code,
               'DPND_PURGING_BR_POLICY',sel.irid);
    raise invalid_ref;
  end if;
  close GetP;
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'sel','DPND_PURGING_BR_POLICY');
    raise;
end sel;
--
-- upd
--
procedure upd
(
  irid in number
, pl   in out data
) is
begin
  -- Property Maintenance
    pl.v.irid := irid;
    maintain(pl,true);
  -- Update
  update SDD_DPND_PURGING_BR_POLICIES set
    BRANCH_ID = pl.v.BRANCH_ID
  , CHANGED_BY = pl.v.CHANGED_BY
  , CREATED_BY = pl.v.CREATED_BY
  , DATE_CHANGED = pl.v.DATE_CHANGED
  , DATE_CREATED = pl.v.DATE_CREATED
  , IRID = pl.v.IRID
  , IVID = pl.v.IVID
  , NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED
  , NUMBER_OF_VERSIONS = pl.v.NUMBER_OF_VERSIONS
  , POLICY_ID = pl.v.POLICY_ID
  where SDD_DPND_PURGING_BR_POLICIES.irid = upd.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'upd','DPND_PURGING_BR_POLICY');
    raise;
end upd;
--
-- lck
--
procedure lck
(
  irid in number
) is
  dummy rm.reference;
begin
  -- Lock the Element
    select null into dummy
    from SDD_DPND_PURGING_BR_POLICIES
    where SDD_DPND_PURGING_BR_POLICIES.irid = lck.irid for update nowait
    ;
exception
  when no_data_found then
    rmmes.post(invalid_ref_fac, invalid_ref_code,
              'DPND_PURGING_BR_POLICY',lck.irid);
    raise invalid_ref;
  when others then
    -- Oracle error of 'Resource interested is busy'
    if sqlcode = -54 then
      rmmes.post(element_locked_fac,element_locked_code,
                 lck.irid,'DPND_PURGING_BR_POLICY');
      raise element_locked;
    end if;
    rmmes.post(operation_failed_fac,operation_failed_code,
              'lck','DPND_PURGING_BR_POLICY');
    raise;
end lck;
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
) is
  pl   data;           -- Existing Property Values
begin
  -- Retrieve Existing Properties, if for update
  if for_update then
    open GetP(np.v.irid);
    fetch GetP into
      pl.v.BRANCH_ID
    , pl.v.CHANGED_BY
    , pl.v.CREATED_BY
    , pl.v.DATE_CHANGED
    , pl.v.DATE_CREATED
    , pl.v.IRID
    , pl.v.IVID
    , pl.v.NUMBER_OF_TIMES_MODIFIED
    , pl.v.NUMBER_OF_VERSIONS
    , pl.v.POLICY_ID
    ;
    if GetP%NOTFOUND then
      close GetP;
      rmmes.post(invalid_ref_fac,invalid_ref_code,
                 'DPND_PURGING_BR_POLICY',np.v.irid);
      raise invalid_ref;
    end if;
    close GetP;
  end if;
  -- Not updateable checks
  -- Not applicable to registered types
  -- Not updateable (if existing value is not null) checks
  -- Not applicable to registered types
  -- Old to New Transfer
  if for_update then null;
    if not np.i.BRANCH_ID then
      np.v.BRANCH_ID := pl.v.BRANCH_ID;
    elsif nvl(np.v.BRANCH_ID,pl.v.BRANCH_ID) is null or
          np.v.BRANCH_ID = pl.v.BRANCH_ID then
      np.i.BRANCH_ID := false;
    end if;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := pl.v.CHANGED_BY;
    elsif nvl(np.v.CHANGED_BY,pl.v.CHANGED_BY) is null or
          np.v.CHANGED_BY = pl.v.CHANGED_BY then
      np.i.CHANGED_BY := false;
    end if;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := pl.v.CREATED_BY;
    elsif nvl(np.v.CREATED_BY,pl.v.CREATED_BY) is null or
          np.v.CREATED_BY = pl.v.CREATED_BY then
      np.i.CREATED_BY := false;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := pl.v.DATE_CHANGED;
    elsif nvl(np.v.DATE_CHANGED,pl.v.DATE_CHANGED) is null or
          np.v.DATE_CHANGED = pl.v.DATE_CHANGED then
      np.i.DATE_CHANGED := false;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := pl.v.DATE_CREATED;
    elsif nvl(np.v.DATE_CREATED,pl.v.DATE_CREATED) is null or
          np.v.DATE_CREATED = pl.v.DATE_CREATED then
      np.i.DATE_CREATED := false;
    end if;
    if not np.i.IRID then
      np.v.IRID := pl.v.IRID;
    elsif nvl(np.v.IRID,pl.v.IRID) is null or
          np.v.IRID = pl.v.IRID then
      np.i.IRID := false;
    end if;
    if not np.i.IVID then
      np.v.IVID := pl.v.IVID;
    elsif nvl(np.v.IVID,pl.v.IVID) is null or
          np.v.IVID = pl.v.IVID then
      np.i.IVID := false;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := pl.v.NUMBER_OF_TIMES_MODIFIED;
    elsif nvl(np.v.NUMBER_OF_TIMES_MODIFIED,pl.v.NUMBER_OF_TIMES_MODIFIED) is null or
          np.v.NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED then
      np.i.NUMBER_OF_TIMES_MODIFIED := false;
    end if;
    if not np.i.NUMBER_OF_VERSIONS then
      np.v.NUMBER_OF_VERSIONS := pl.v.NUMBER_OF_VERSIONS;
    elsif nvl(np.v.NUMBER_OF_VERSIONS,pl.v.NUMBER_OF_VERSIONS) is null or
          np.v.NUMBER_OF_VERSIONS = pl.v.NUMBER_OF_VERSIONS then
      np.i.NUMBER_OF_VERSIONS := false;
    end if;
    if not np.i.POLICY_ID then
      np.v.POLICY_ID := pl.v.POLICY_ID;
    elsif nvl(np.v.POLICY_ID,pl.v.POLICY_ID) is null or
          np.v.POLICY_ID = pl.v.POLICY_ID then
      np.i.POLICY_ID := false;
    end if;
  end if;
  -- Initialisation (default values)
  if not for_update then
    null;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := user;
      np.i.CREATED_BY := true;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := sysdate;
      np.i.DATE_CREATED := true;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := 0;
      np.i.NUMBER_OF_TIMES_MODIFIED := true;
    end if;
    if not np.i.NUMBER_OF_VERSIONS then
      np.v.NUMBER_OF_VERSIONS := 1       ;
      np.i.NUMBER_OF_VERSIONS := true;
    end if;
  end if; -- Initialisation
  -- Maintenance
  if for_update then
    null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := user;
      np.i.CHANGED_BY := true;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := sysdate;
      np.i.DATE_CHANGED := true;
    end if;
  end if; -- Maintenance
  -- String Manipulation (case,trim)
  -- Not applicable to registered types
end maintain;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   )
is
  lck boolean;
begin
  if i_lock = 'Y' then
    lck := true;
  else
    lck := false;
  end if;
  o_ivid := jr_version.check_out(i_irid, i_ivid, lck, i_notes, i_vlabel);
end check_out;
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  )
is
begin
  o_ivid := jr_version.check_in(i_irid, i_ivid, i_notes, i_vlabel);
end check_in;
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  )
is
  save_curr_ivid  number;
  save_new_irid   number;
  save_new_ivid   number;
  copy_mode       boolean;
begin
  -- Caller may want to pre-specifiy copied irid
  save_new_irid := pl.v.irid;
  save_new_ivid := pl.v.ivid;
  pl.v.irid := i_irid;
  copy_mode := true;
  maintain(pl, true);
  copy_mode := false;
  pl.v.irid := save_new_irid;
  if save_new_irid is not null then
    pl.i.irid := true;
  end if;
  save_curr_ivid := pl.v.ivid;
  pl.v.ivid := save_new_ivid;
  if save_new_ivid is not null then
    pl.i.ivid := true;
  end if;
  ins(pl.v.irid, pl);
  if deep = 'Y' then
    jr_copy.copy( i_irid
                , save_curr_ivid
                , pl.v.irid
                , pl.v.ivid
                , null
                , true
                , false
                );
  end if;
  exception
    when others then
      copy_mode := false;
      rmmes.post(operation_failed_fac,operation_failed_code,
                'copy','DPND_PURGING_BR_POLICY');
      raise;
end copy_pac;
--
-- Package instantiation block
--
begin
  is_installed := true;
end SDDoDPND_PURGING_BR_POLICY;
/
create or replace package SDDoDPND_PURGING_CFG_POLICY is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Properties
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
type rm_values is record (
  CHANGED_BY VARCHAR2(30)
, CONFIG_IRID NUMBER
, CONFIG_IVID NUMBER(38)
, CREATED_BY VARCHAR2(30)
, DATE_CHANGED DATE
, DATE_CREATED DATE
, IRID NUMBER(38)
, IVID NUMBER(38)
, KEEP_DEPENDENCIES_FOR VARCHAR2(1)
, NUMBER_OF_TIMES_MODIFIED NUMBER(38)
, POLICY_ID NUMBER(38)
, PURGE_DEPENDENCIES_FOR VARCHAR2(1)
);
type rm_indicators is record (
  CHANGED_BY boolean not null default false
, CONFIG_IRID boolean not null default false
, CONFIG_IVID boolean not null default false
, CREATED_BY boolean not null default false
, DATE_CHANGED boolean not null default false
, DATE_CREATED boolean not null default false
, IRID boolean not null default false
, IVID boolean not null default false
, KEEP_DEPENDENCIES_FOR boolean not null default false
, NUMBER_OF_TIMES_MODIFIED boolean not null default false
, POLICY_ID boolean not null default false
, PURGE_DEPENDENCIES_FOR boolean not null default false
);
type data is record (
  v rm_values
, i rm_indicators
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Operations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
);
--
-- del
--
procedure del
(
  irid in number
);
--
-- sel
--
procedure sel
(
  irid in     number
, pl   in out data
);
--
-- upd
--
procedure upd
(
  irid in     number
, pl   in out data
);
--
-- lck
--
procedure lck
(
  irid in number
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   );
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  );
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  );
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Exceptions
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
element_locked exception;
  element_locked_fac  constant varchar2(3) := 'RME';
  element_locked_code constant number      := 10;
invalid_ref exception;
  invalid_ref_fac  constant varchar2(3) := 'RME';
  invalid_ref_code constant number      := 9;
operation_failed exception;
  operation_failed_fac  constant varchar2(3) := 'RME';
  operation_failed_code constant number      := 11;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Globals
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
is_installed boolean := false;
end SDDoDPND_PURGING_CFG_POLICY;
/
create or replace package body SDDoDPND_PURGING_CFG_POLICY is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Cursors
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Retrieve Element Type property values
cursor GetP(irid in rm.reference) is
  select
    el.CHANGED_BY
  , el.CONFIG_IRID
  , el.CONFIG_IVID
  , el.CREATED_BY
  , el.DATE_CHANGED
  , el.DATE_CREATED
  , el.IRID
  , el.IVID
  , el.KEEP_DEPENDENCIES_FOR
  , el.NUMBER_OF_TIMES_MODIFIED
  , el.POLICY_ID
  , el.PURGE_DEPENDENCIES_FOR
  from SDD_DPND_PURGING_CFG_POLICIES el
  where el.irid = GetP.irid
  ;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Procedure and Function forward Declarations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Public Procedures
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
) is
begin
  -- If irid is not null copy it into the property list
  if irid is not NULL then
    pl.v.irid := ins.irid;
    pl.i.irid := true;
  end if;
  -- Property Maintenance
  maintain(pl,false);
  if not pl.i.irid then
    pl.v.irid := jr_util.get_new_irid;
    pl.i.irid := true;
  end if;
  if not pl.i.ivid then
    pl.v.ivid := jr_system_util.get_new_ivid;
    pl.i.ivid := true;
  end if;
  -- Insert
  insert into SDD_DPND_PURGING_CFG_POLICIES
  (
    CHANGED_BY
  , CONFIG_IRID
  , CONFIG_IVID
  , CREATED_BY
  , DATE_CHANGED
  , DATE_CREATED
  , IRID
  , IVID
  , KEEP_DEPENDENCIES_FOR
  , NUMBER_OF_TIMES_MODIFIED
  , POLICY_ID
  , PURGE_DEPENDENCIES_FOR
  )
  values
  (
    pl.v.CHANGED_BY
  , pl.v.CONFIG_IRID
  , pl.v.CONFIG_IVID
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.KEEP_DEPENDENCIES_FOR
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.POLICY_ID
  , pl.v.PURGE_DEPENDENCIES_FOR
  );
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'ins','DPND_PURGING_CFG_POLICY');
    raise;
end ins;
--
-- del
--
procedure del
(
  irid in number
) is
begin
  -- Delete the element itself
  delete from SDD_DPND_PURGING_CFG_POLICIES
  where SDD_DPND_PURGING_CFG_POLICIES.irid = del.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'del','DPND_PURGING_CFG_POLICY');
    raise;
end del;
--
-- sel
--
procedure sel
(
  irid in number
, pl   in out data
) is
begin
  -- Select
  open GetP(sel.irid);
  fetch GetP into
    pl.v.CHANGED_BY
  , pl.v.CONFIG_IRID
  , pl.v.CONFIG_IVID
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.KEEP_DEPENDENCIES_FOR
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.POLICY_ID
  , pl.v.PURGE_DEPENDENCIES_FOR
  ;
  if GetP%NOTFOUND then
    close GetP;
    rmmes.post(invalid_ref_fac,invalid_ref_code,
               'DPND_PURGING_CFG_POLICY',sel.irid);
    raise invalid_ref;
  end if;
  close GetP;
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'sel','DPND_PURGING_CFG_POLICY');
    raise;
end sel;
--
-- upd
--
procedure upd
(
  irid in number
, pl   in out data
) is
begin
  -- Property Maintenance
    pl.v.irid := irid;
    maintain(pl,true);
  -- Update
  update SDD_DPND_PURGING_CFG_POLICIES set
    CHANGED_BY = pl.v.CHANGED_BY
  , CONFIG_IRID = pl.v.CONFIG_IRID
  , CONFIG_IVID = pl.v.CONFIG_IVID
  , CREATED_BY = pl.v.CREATED_BY
  , DATE_CHANGED = pl.v.DATE_CHANGED
  , DATE_CREATED = pl.v.DATE_CREATED
  , IRID = pl.v.IRID
  , IVID = pl.v.IVID
  , KEEP_DEPENDENCIES_FOR = pl.v.KEEP_DEPENDENCIES_FOR
  , NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED
  , POLICY_ID = pl.v.POLICY_ID
  , PURGE_DEPENDENCIES_FOR = pl.v.PURGE_DEPENDENCIES_FOR
  where SDD_DPND_PURGING_CFG_POLICIES.irid = upd.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'upd','DPND_PURGING_CFG_POLICY');
    raise;
end upd;
--
-- lck
--
procedure lck
(
  irid in number
) is
  dummy rm.reference;
begin
  -- Lock the Element
    select null into dummy
    from SDD_DPND_PURGING_CFG_POLICIES
    where SDD_DPND_PURGING_CFG_POLICIES.irid = lck.irid for update nowait
    ;
exception
  when no_data_found then
    rmmes.post(invalid_ref_fac, invalid_ref_code,
              'DPND_PURGING_CFG_POLICY',lck.irid);
    raise invalid_ref;
  when others then
    -- Oracle error of 'Resource interested is busy'
    if sqlcode = -54 then
      rmmes.post(element_locked_fac,element_locked_code,
                 lck.irid,'DPND_PURGING_CFG_POLICY');
      raise element_locked;
    end if;
    rmmes.post(operation_failed_fac,operation_failed_code,
              'lck','DPND_PURGING_CFG_POLICY');
    raise;
end lck;
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
) is
  pl   data;           -- Existing Property Values
begin
  -- Retrieve Existing Properties, if for update
  if for_update then
    open GetP(np.v.irid);
    fetch GetP into
      pl.v.CHANGED_BY
    , pl.v.CONFIG_IRID
    , pl.v.CONFIG_IVID
    , pl.v.CREATED_BY
    , pl.v.DATE_CHANGED
    , pl.v.DATE_CREATED
    , pl.v.IRID
    , pl.v.IVID
    , pl.v.KEEP_DEPENDENCIES_FOR
    , pl.v.NUMBER_OF_TIMES_MODIFIED
    , pl.v.POLICY_ID
    , pl.v.PURGE_DEPENDENCIES_FOR
    ;
    if GetP%NOTFOUND then
      close GetP;
      rmmes.post(invalid_ref_fac,invalid_ref_code,
                 'DPND_PURGING_CFG_POLICY',np.v.irid);
      raise invalid_ref;
    end if;
    close GetP;
  end if;
  -- Not updateable checks
  -- Not applicable to registered types
  -- Not updateable (if existing value is not null) checks
  -- Not applicable to registered types
  -- Old to New Transfer
  if for_update then null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := pl.v.CHANGED_BY;
    elsif nvl(np.v.CHANGED_BY,pl.v.CHANGED_BY) is null or
          np.v.CHANGED_BY = pl.v.CHANGED_BY then
      np.i.CHANGED_BY := false;
    end if;
    if not np.i.CONFIG_IRID then
      np.v.CONFIG_IRID := pl.v.CONFIG_IRID;
    elsif nvl(np.v.CONFIG_IRID,pl.v.CONFIG_IRID) is null or
          np.v.CONFIG_IRID = pl.v.CONFIG_IRID then
      np.i.CONFIG_IRID := false;
    end if;
    if not np.i.CONFIG_IVID then
      np.v.CONFIG_IVID := pl.v.CONFIG_IVID;
    elsif nvl(np.v.CONFIG_IVID,pl.v.CONFIG_IVID) is null or
          np.v.CONFIG_IVID = pl.v.CONFIG_IVID then
      np.i.CONFIG_IVID := false;
    end if;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := pl.v.CREATED_BY;
    elsif nvl(np.v.CREATED_BY,pl.v.CREATED_BY) is null or
          np.v.CREATED_BY = pl.v.CREATED_BY then
      np.i.CREATED_BY := false;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := pl.v.DATE_CHANGED;
    elsif nvl(np.v.DATE_CHANGED,pl.v.DATE_CHANGED) is null or
          np.v.DATE_CHANGED = pl.v.DATE_CHANGED then
      np.i.DATE_CHANGED := false;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := pl.v.DATE_CREATED;
    elsif nvl(np.v.DATE_CREATED,pl.v.DATE_CREATED) is null or
          np.v.DATE_CREATED = pl.v.DATE_CREATED then
      np.i.DATE_CREATED := false;
    end if;
    if not np.i.IRID then
      np.v.IRID := pl.v.IRID;
    elsif nvl(np.v.IRID,pl.v.IRID) is null or
          np.v.IRID = pl.v.IRID then
      np.i.IRID := false;
    end if;
    if not np.i.IVID then
      np.v.IVID := pl.v.IVID;
    elsif nvl(np.v.IVID,pl.v.IVID) is null or
          np.v.IVID = pl.v.IVID then
      np.i.IVID := false;
    end if;
    if not np.i.KEEP_DEPENDENCIES_FOR then
      np.v.KEEP_DEPENDENCIES_FOR := pl.v.KEEP_DEPENDENCIES_FOR;
    elsif nvl(np.v.KEEP_DEPENDENCIES_FOR,pl.v.KEEP_DEPENDENCIES_FOR) is null or
          np.v.KEEP_DEPENDENCIES_FOR = pl.v.KEEP_DEPENDENCIES_FOR then
      np.i.KEEP_DEPENDENCIES_FOR := false;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := pl.v.NUMBER_OF_TIMES_MODIFIED;
    elsif nvl(np.v.NUMBER_OF_TIMES_MODIFIED,pl.v.NUMBER_OF_TIMES_MODIFIED) is null or
          np.v.NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED then
      np.i.NUMBER_OF_TIMES_MODIFIED := false;
    end if;
    if not np.i.POLICY_ID then
      np.v.POLICY_ID := pl.v.POLICY_ID;
    elsif nvl(np.v.POLICY_ID,pl.v.POLICY_ID) is null or
          np.v.POLICY_ID = pl.v.POLICY_ID then
      np.i.POLICY_ID := false;
    end if;
    if not np.i.PURGE_DEPENDENCIES_FOR then
      np.v.PURGE_DEPENDENCIES_FOR := pl.v.PURGE_DEPENDENCIES_FOR;
    elsif nvl(np.v.PURGE_DEPENDENCIES_FOR,pl.v.PURGE_DEPENDENCIES_FOR) is null or
          np.v.PURGE_DEPENDENCIES_FOR = pl.v.PURGE_DEPENDENCIES_FOR then
      np.i.PURGE_DEPENDENCIES_FOR := false;
    end if;
  end if;
  -- Initialisation (default values)
  if not for_update then
    null;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := user;
      np.i.CREATED_BY := true;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := sysdate;
      np.i.DATE_CREATED := true;
    end if;
    if not np.i.KEEP_DEPENDENCIES_FOR then
      np.v.KEEP_DEPENDENCIES_FOR := 'Y'     ;
      np.i.KEEP_DEPENDENCIES_FOR := true;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := 0;
      np.i.NUMBER_OF_TIMES_MODIFIED := true;
    end if;
    if not np.i.PURGE_DEPENDENCIES_FOR then
      np.v.PURGE_DEPENDENCIES_FOR := 'N'     ;
      np.i.PURGE_DEPENDENCIES_FOR := true;
    end if;
  end if; -- Initialisation
  -- Maintenance
  if for_update then
    null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := user;
      np.i.CHANGED_BY := true;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := sysdate;
      np.i.DATE_CHANGED := true;
    end if;
  end if; -- Maintenance
  -- String Manipulation (case,trim)
  -- Not applicable to registered types
end maintain;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   )
is
  lck boolean;
begin
  if i_lock = 'Y' then
    lck := true;
  else
    lck := false;
  end if;
  o_ivid := jr_version.check_out(i_irid, i_ivid, lck, i_notes, i_vlabel);
end check_out;
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  )
is
begin
  o_ivid := jr_version.check_in(i_irid, i_ivid, i_notes, i_vlabel);
end check_in;
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  )
is
  save_curr_ivid  number;
  save_new_irid   number;
  save_new_ivid   number;
  copy_mode       boolean;
begin
  -- Caller may want to pre-specifiy copied irid
  save_new_irid := pl.v.irid;
  save_new_ivid := pl.v.ivid;
  pl.v.irid := i_irid;
  copy_mode := true;
  maintain(pl, true);
  copy_mode := false;
  pl.v.irid := save_new_irid;
  if save_new_irid is not null then
    pl.i.irid := true;
  end if;
  save_curr_ivid := pl.v.ivid;
  pl.v.ivid := save_new_ivid;
  if save_new_ivid is not null then
    pl.i.ivid := true;
  end if;
  ins(pl.v.irid, pl);
  if deep = 'Y' then
    jr_copy.copy( i_irid
                , save_curr_ivid
                , pl.v.irid
                , pl.v.ivid
                , null
                , true
                , false
                );
  end if;
  exception
    when others then
      copy_mode := false;
      rmmes.post(operation_failed_fac,operation_failed_code,
                'copy','DPND_PURGING_CFG_POLICY');
      raise;
end copy_pac;
--
-- Package instantiation block
--
begin
  is_installed := true;
end SDDoDPND_PURGING_CFG_POLICY;
/
create or replace package SDDoDPND_PURGING_POLICY is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Properties
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
type rm_values is record (
  CHANGED_BY VARCHAR2(30)
, CREATED_BY VARCHAR2(30)
, DATE_CHANGED DATE
, DATE_CREATED DATE
, DATE_LAST_PURGED DATE
, IRID NUMBER(38)
, IVID NUMBER(38)
, NUMBER_OF_TIMES_MODIFIED NUMBER(38)
, POLICY_NAME VARCHAR2(256)
, SCHEDULED_DATE DATE
);
type rm_indicators is record (
  CHANGED_BY boolean not null default false
, CREATED_BY boolean not null default false
, DATE_CHANGED boolean not null default false
, DATE_CREATED boolean not null default false
, DATE_LAST_PURGED boolean not null default false
, IRID boolean not null default false
, IVID boolean not null default false
, NUMBER_OF_TIMES_MODIFIED boolean not null default false
, POLICY_NAME boolean not null default false
, SCHEDULED_DATE boolean not null default false
);
type data is record (
  v rm_values
, i rm_indicators
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Operations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
);
--
-- del
--
procedure del
(
  irid in number
);
--
-- sel
--
procedure sel
(
  irid in     number
, pl   in out data
);
--
-- upd
--
procedure upd
(
  irid in     number
, pl   in out data
);
--
-- lck
--
procedure lck
(
  irid in number
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   );
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  );
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  );
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Exceptions
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
element_locked exception;
  element_locked_fac  constant varchar2(3) := 'RME';
  element_locked_code constant number      := 10;
invalid_ref exception;
  invalid_ref_fac  constant varchar2(3) := 'RME';
  invalid_ref_code constant number      := 9;
operation_failed exception;
  operation_failed_fac  constant varchar2(3) := 'RME';
  operation_failed_code constant number      := 11;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Globals
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
is_installed boolean := false;
end SDDoDPND_PURGING_POLICY;
/
create or replace package body SDDoDPND_PURGING_POLICY is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Cursors
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Retrieve Element Type property values
cursor GetP(irid in rm.reference) is
  select
    el.CHANGED_BY
  , el.CREATED_BY
  , el.DATE_CHANGED
  , el.DATE_CREATED
  , el.DATE_LAST_PURGED
  , el.IRID
  , el.IVID
  , el.NUMBER_OF_TIMES_MODIFIED
  , el.POLICY_NAME
  , el.SCHEDULED_DATE
  from SDD_DPND_PURGING_POLICIES el
  where el.irid = GetP.irid
  ;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Procedure and Function forward Declarations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Public Procedures
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
) is
begin
  -- If irid is not null copy it into the property list
  if irid is not NULL then
    pl.v.irid := ins.irid;
    pl.i.irid := true;
  end if;
  -- Property Maintenance
  maintain(pl,false);
  if not pl.i.irid then
    pl.v.irid := jr_util.get_new_irid;
    pl.i.irid := true;
  end if;
  if not pl.i.ivid then
    pl.v.ivid := jr_system_util.get_new_ivid;
    pl.i.ivid := true;
  end if;
  -- Insert
  insert into SDD_DPND_PURGING_POLICIES
  (
    CHANGED_BY
  , CREATED_BY
  , DATE_CHANGED
  , DATE_CREATED
  , DATE_LAST_PURGED
  , IRID
  , IVID
  , NUMBER_OF_TIMES_MODIFIED
  , POLICY_NAME
  , SCHEDULED_DATE
  )
  values
  (
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.DATE_LAST_PURGED
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.POLICY_NAME
  , pl.v.SCHEDULED_DATE
  );
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'ins','DPND_PURGING_POLICY');
    raise;
end ins;
--
-- del
--
procedure del
(
  irid in number
) is
begin
  -- Delete the element itself
  delete from SDD_DPND_PURGING_POLICIES
  where SDD_DPND_PURGING_POLICIES.irid = del.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'del','DPND_PURGING_POLICY');
    raise;
end del;
--
-- sel
--
procedure sel
(
  irid in number
, pl   in out data
) is
begin
  -- Select
  open GetP(sel.irid);
  fetch GetP into
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.DATE_LAST_PURGED
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.POLICY_NAME
  , pl.v.SCHEDULED_DATE
  ;
  if GetP%NOTFOUND then
    close GetP;
    rmmes.post(invalid_ref_fac,invalid_ref_code,
               'DPND_PURGING_POLICY',sel.irid);
    raise invalid_ref;
  end if;
  close GetP;
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'sel','DPND_PURGING_POLICY');
    raise;
end sel;
--
-- upd
--
procedure upd
(
  irid in number
, pl   in out data
) is
begin
  -- Property Maintenance
    pl.v.irid := irid;
    maintain(pl,true);
  -- Update
  update SDD_DPND_PURGING_POLICIES set
    CHANGED_BY = pl.v.CHANGED_BY
  , CREATED_BY = pl.v.CREATED_BY
  , DATE_CHANGED = pl.v.DATE_CHANGED
  , DATE_CREATED = pl.v.DATE_CREATED
  , DATE_LAST_PURGED = pl.v.DATE_LAST_PURGED
  , IRID = pl.v.IRID
  , IVID = pl.v.IVID
  , NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED
  , POLICY_NAME = pl.v.POLICY_NAME
  , SCHEDULED_DATE = pl.v.SCHEDULED_DATE
  where SDD_DPND_PURGING_POLICIES.irid = upd.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'upd','DPND_PURGING_POLICY');
    raise;
end upd;
--
-- lck
--
procedure lck
(
  irid in number
) is
  dummy rm.reference;
begin
  -- Lock the Element
    select null into dummy
    from SDD_DPND_PURGING_POLICIES
    where SDD_DPND_PURGING_POLICIES.irid = lck.irid for update nowait
    ;
exception
  when no_data_found then
    rmmes.post(invalid_ref_fac, invalid_ref_code,
              'DPND_PURGING_POLICY',lck.irid);
    raise invalid_ref;
  when others then
    -- Oracle error of 'Resource interested is busy'
    if sqlcode = -54 then
      rmmes.post(element_locked_fac,element_locked_code,
                 lck.irid,'DPND_PURGING_POLICY');
      raise element_locked;
    end if;
    rmmes.post(operation_failed_fac,operation_failed_code,
              'lck','DPND_PURGING_POLICY');
    raise;
end lck;
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
) is
  pl   data;           -- Existing Property Values
begin
  -- Retrieve Existing Properties, if for update
  if for_update then
    open GetP(np.v.irid);
    fetch GetP into
      pl.v.CHANGED_BY
    , pl.v.CREATED_BY
    , pl.v.DATE_CHANGED
    , pl.v.DATE_CREATED
    , pl.v.DATE_LAST_PURGED
    , pl.v.IRID
    , pl.v.IVID
    , pl.v.NUMBER_OF_TIMES_MODIFIED
    , pl.v.POLICY_NAME
    , pl.v.SCHEDULED_DATE
    ;
    if GetP%NOTFOUND then
      close GetP;
      rmmes.post(invalid_ref_fac,invalid_ref_code,
                 'DPND_PURGING_POLICY',np.v.irid);
      raise invalid_ref;
    end if;
    close GetP;
  end if;
  -- Not updateable checks
  -- Not applicable to registered types
  -- Not updateable (if existing value is not null) checks
  -- Not applicable to registered types
  -- Old to New Transfer
  if for_update then null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := pl.v.CHANGED_BY;
    elsif nvl(np.v.CHANGED_BY,pl.v.CHANGED_BY) is null or
          np.v.CHANGED_BY = pl.v.CHANGED_BY then
      np.i.CHANGED_BY := false;
    end if;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := pl.v.CREATED_BY;
    elsif nvl(np.v.CREATED_BY,pl.v.CREATED_BY) is null or
          np.v.CREATED_BY = pl.v.CREATED_BY then
      np.i.CREATED_BY := false;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := pl.v.DATE_CHANGED;
    elsif nvl(np.v.DATE_CHANGED,pl.v.DATE_CHANGED) is null or
          np.v.DATE_CHANGED = pl.v.DATE_CHANGED then
      np.i.DATE_CHANGED := false;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := pl.v.DATE_CREATED;
    elsif nvl(np.v.DATE_CREATED,pl.v.DATE_CREATED) is null or
          np.v.DATE_CREATED = pl.v.DATE_CREATED then
      np.i.DATE_CREATED := false;
    end if;
    if not np.i.DATE_LAST_PURGED then
      np.v.DATE_LAST_PURGED := pl.v.DATE_LAST_PURGED;
    elsif nvl(np.v.DATE_LAST_PURGED,pl.v.DATE_LAST_PURGED) is null or
          np.v.DATE_LAST_PURGED = pl.v.DATE_LAST_PURGED then
      np.i.DATE_LAST_PURGED := false;
    end if;
    if not np.i.IRID then
      np.v.IRID := pl.v.IRID;
    elsif nvl(np.v.IRID,pl.v.IRID) is null or
          np.v.IRID = pl.v.IRID then
      np.i.IRID := false;
    end if;
    if not np.i.IVID then
      np.v.IVID := pl.v.IVID;
    elsif nvl(np.v.IVID,pl.v.IVID) is null or
          np.v.IVID = pl.v.IVID then
      np.i.IVID := false;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := pl.v.NUMBER_OF_TIMES_MODIFIED;
    elsif nvl(np.v.NUMBER_OF_TIMES_MODIFIED,pl.v.NUMBER_OF_TIMES_MODIFIED) is null or
          np.v.NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED then
      np.i.NUMBER_OF_TIMES_MODIFIED := false;
    end if;
    if not np.i.POLICY_NAME then
      np.v.POLICY_NAME := pl.v.POLICY_NAME;
    elsif nvl(np.v.POLICY_NAME,pl.v.POLICY_NAME) is null or
          np.v.POLICY_NAME = pl.v.POLICY_NAME then
      np.i.POLICY_NAME := false;
    end if;
    if not np.i.SCHEDULED_DATE then
      np.v.SCHEDULED_DATE := pl.v.SCHEDULED_DATE;
    elsif nvl(np.v.SCHEDULED_DATE,pl.v.SCHEDULED_DATE) is null or
          np.v.SCHEDULED_DATE = pl.v.SCHEDULED_DATE then
      np.i.SCHEDULED_DATE := false;
    end if;
  end if;
  -- Initialisation (default values)
  if not for_update then
    null;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := user;
      np.i.CREATED_BY := true;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := sysdate;
      np.i.DATE_CREATED := true;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := 0;
      np.i.NUMBER_OF_TIMES_MODIFIED := true;
    end if;
  end if; -- Initialisation
  -- Maintenance
  if for_update then
    null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := user;
      np.i.CHANGED_BY := true;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := sysdate;
      np.i.DATE_CHANGED := true;
    end if;
  end if; -- Maintenance
  -- String Manipulation (case,trim)
  -- Not applicable to registered types
end maintain;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   )
is
  lck boolean;
begin
  if i_lock = 'Y' then
    lck := true;
  else
    lck := false;
  end if;
  o_ivid := jr_version.check_out(i_irid, i_ivid, lck, i_notes, i_vlabel);
end check_out;
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  )
is
begin
  o_ivid := jr_version.check_in(i_irid, i_ivid, i_notes, i_vlabel);
end check_in;
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  )
is
  save_curr_ivid  number;
  save_new_irid   number;
  save_new_ivid   number;
  copy_mode       boolean;
begin
  -- Caller may want to pre-specifiy copied irid
  save_new_irid := pl.v.irid;
  save_new_ivid := pl.v.ivid;
  pl.v.irid := i_irid;
  copy_mode := true;
  maintain(pl, true);
  copy_mode := false;
  pl.v.irid := save_new_irid;
  if save_new_irid is not null then
    pl.i.irid := true;
  end if;
  save_curr_ivid := pl.v.ivid;
  pl.v.ivid := save_new_ivid;
  if save_new_ivid is not null then
    pl.i.ivid := true;
  end if;
  ins(pl.v.irid, pl);
  if deep = 'Y' then
    jr_copy.copy( i_irid
                , save_curr_ivid
                , pl.v.irid
                , pl.v.ivid
                , null
                , true
                , false
                );
  end if;
  exception
    when others then
      copy_mode := false;
      rmmes.post(operation_failed_fac,operation_failed_code,
                'copy','DPND_PURGING_POLICY');
      raise;
end copy_pac;
--
-- Package instantiation block
--
begin
  is_installed := true;
end SDDoDPND_PURGING_POLICY;
/
create or replace package SDDoGLOBAL_NAME is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Properties
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
type rm_values is record (
  CHANGED_BY VARCHAR2(30)
, CREATED_BY VARCHAR2(30)
, DATE_CHANGED DATE
, DATE_CREATED DATE
, DOMAIN_IRID NUMBER(38)
, IRID NUMBER(38)
, IVID NUMBER(38)
, NAME VARCHAR2(256)
, NUMBER_OF_TIMES_MODIFIED NUMBER(38)
, TYPE_ID NUMBER(38)
);
type rm_indicators is record (
  CHANGED_BY boolean not null default false
, CREATED_BY boolean not null default false
, DATE_CHANGED boolean not null default false
, DATE_CREATED boolean not null default false
, DOMAIN_IRID boolean not null default false
, IRID boolean not null default false
, IVID boolean not null default false
, NAME boolean not null default false
, NUMBER_OF_TIMES_MODIFIED boolean not null default false
, TYPE_ID boolean not null default false
);
type data is record (
  v rm_values
, i rm_indicators
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Operations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
);
--
-- del
--
procedure del
(
  irid in number
);
--
-- sel
--
procedure sel
(
  irid in     number
, pl   in out data
);
--
-- upd
--
procedure upd
(
  irid in     number
, pl   in out data
);
--
-- lck
--
procedure lck
(
  irid in number
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   );
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  );
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  );
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Exceptions
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
element_locked exception;
  element_locked_fac  constant varchar2(3) := 'RME';
  element_locked_code constant number      := 10;
invalid_ref exception;
  invalid_ref_fac  constant varchar2(3) := 'RME';
  invalid_ref_code constant number      := 9;
operation_failed exception;
  operation_failed_fac  constant varchar2(3) := 'RME';
  operation_failed_code constant number      := 11;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Globals
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
is_installed boolean := false;
end SDDoGLOBAL_NAME;
/
create or replace package body SDDoGLOBAL_NAME is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Cursors
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Retrieve Element Type property values
cursor GetP(irid in rm.reference) is
  select
    el.CHANGED_BY
  , el.CREATED_BY
  , el.DATE_CHANGED
  , el.DATE_CREATED
  , el.DOMAIN_IRID
  , el.IRID
  , el.IVID
  , el.NAME
  , el.NUMBER_OF_TIMES_MODIFIED
  , el.TYPE_ID
  from SDD_GLOBAL_NAMES el
  where el.irid = GetP.irid
  ;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Procedure and Function forward Declarations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Public Procedures
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
) is
begin
  -- If irid is not null copy it into the property list
  if irid is not NULL then
    pl.v.irid := ins.irid;
    pl.i.irid := true;
  end if;
  -- Property Maintenance
  maintain(pl,false);
  if not pl.i.irid then
    pl.v.irid := jr_util.get_new_irid;
    pl.i.irid := true;
  end if;
  if not pl.i.ivid then
    pl.v.ivid := jr_system_util.get_new_ivid;
    pl.i.ivid := true;
  end if;
  -- Insert
  insert into SDD_GLOBAL_NAMES
  (
    CHANGED_BY
  , CREATED_BY
  , DATE_CHANGED
  , DATE_CREATED
  , DOMAIN_IRID
  , IRID
  , IVID
  , NAME
  , NUMBER_OF_TIMES_MODIFIED
  , TYPE_ID
  )
  values
  (
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.DOMAIN_IRID
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NAME
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.TYPE_ID
  );
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'ins','GLOBAL_NAME');
    raise;
end ins;
--
-- del
--
procedure del
(
  irid in number
) is
begin
  -- Delete the element itself
  delete from SDD_GLOBAL_NAMES
  where SDD_GLOBAL_NAMES.irid = del.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'del','GLOBAL_NAME');
    raise;
end del;
--
-- sel
--
procedure sel
(
  irid in number
, pl   in out data
) is
begin
  -- Select
  open GetP(sel.irid);
  fetch GetP into
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.DOMAIN_IRID
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NAME
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.TYPE_ID
  ;
  if GetP%NOTFOUND then
    close GetP;
    rmmes.post(invalid_ref_fac,invalid_ref_code,
               'GLOBAL_NAME',sel.irid);
    raise invalid_ref;
  end if;
  close GetP;
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'sel','GLOBAL_NAME');
    raise;
end sel;
--
-- upd
--
procedure upd
(
  irid in number
, pl   in out data
) is
begin
  -- Property Maintenance
    pl.v.irid := irid;
    maintain(pl,true);
  -- Update
  update SDD_GLOBAL_NAMES set
    CHANGED_BY = pl.v.CHANGED_BY
  , CREATED_BY = pl.v.CREATED_BY
  , DATE_CHANGED = pl.v.DATE_CHANGED
  , DATE_CREATED = pl.v.DATE_CREATED
  , DOMAIN_IRID = pl.v.DOMAIN_IRID
  , IRID = pl.v.IRID
  , IVID = pl.v.IVID
  , NAME = pl.v.NAME
  , NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED
  , TYPE_ID = pl.v.TYPE_ID
  where SDD_GLOBAL_NAMES.irid = upd.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'upd','GLOBAL_NAME');
    raise;
end upd;
--
-- lck
--
procedure lck
(
  irid in number
) is
  dummy rm.reference;
begin
  -- Lock the Element
    select null into dummy
    from SDD_GLOBAL_NAMES
    where SDD_GLOBAL_NAMES.irid = lck.irid for update nowait
    ;
exception
  when no_data_found then
    rmmes.post(invalid_ref_fac, invalid_ref_code,
              'GLOBAL_NAME',lck.irid);
    raise invalid_ref;
  when others then
    -- Oracle error of 'Resource interested is busy'
    if sqlcode = -54 then
      rmmes.post(element_locked_fac,element_locked_code,
                 lck.irid,'GLOBAL_NAME');
      raise element_locked;
    end if;
    rmmes.post(operation_failed_fac,operation_failed_code,
              'lck','GLOBAL_NAME');
    raise;
end lck;
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
) is
  pl   data;           -- Existing Property Values
begin
  -- Retrieve Existing Properties, if for update
  if for_update then
    open GetP(np.v.irid);
    fetch GetP into
      pl.v.CHANGED_BY
    , pl.v.CREATED_BY
    , pl.v.DATE_CHANGED
    , pl.v.DATE_CREATED
    , pl.v.DOMAIN_IRID
    , pl.v.IRID
    , pl.v.IVID
    , pl.v.NAME
    , pl.v.NUMBER_OF_TIMES_MODIFIED
    , pl.v.TYPE_ID
    ;
    if GetP%NOTFOUND then
      close GetP;
      rmmes.post(invalid_ref_fac,invalid_ref_code,
                 'GLOBAL_NAME',np.v.irid);
      raise invalid_ref;
    end if;
    close GetP;
  end if;
  -- Not updateable checks
  -- Not applicable to registered types
  -- Not updateable (if existing value is not null) checks
  -- Not applicable to registered types
  -- Old to New Transfer
  if for_update then null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := pl.v.CHANGED_BY;
    elsif nvl(np.v.CHANGED_BY,pl.v.CHANGED_BY) is null or
          np.v.CHANGED_BY = pl.v.CHANGED_BY then
      np.i.CHANGED_BY := false;
    end if;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := pl.v.CREATED_BY;
    elsif nvl(np.v.CREATED_BY,pl.v.CREATED_BY) is null or
          np.v.CREATED_BY = pl.v.CREATED_BY then
      np.i.CREATED_BY := false;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := pl.v.DATE_CHANGED;
    elsif nvl(np.v.DATE_CHANGED,pl.v.DATE_CHANGED) is null or
          np.v.DATE_CHANGED = pl.v.DATE_CHANGED then
      np.i.DATE_CHANGED := false;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := pl.v.DATE_CREATED;
    elsif nvl(np.v.DATE_CREATED,pl.v.DATE_CREATED) is null or
          np.v.DATE_CREATED = pl.v.DATE_CREATED then
      np.i.DATE_CREATED := false;
    end if;
    if not np.i.DOMAIN_IRID then
      np.v.DOMAIN_IRID := pl.v.DOMAIN_IRID;
    elsif nvl(np.v.DOMAIN_IRID,pl.v.DOMAIN_IRID) is null or
          np.v.DOMAIN_IRID = pl.v.DOMAIN_IRID then
      np.i.DOMAIN_IRID := false;
    end if;
    if not np.i.IRID then
      np.v.IRID := pl.v.IRID;
    elsif nvl(np.v.IRID,pl.v.IRID) is null or
          np.v.IRID = pl.v.IRID then
      np.i.IRID := false;
    end if;
    if not np.i.IVID then
      np.v.IVID := pl.v.IVID;
    elsif nvl(np.v.IVID,pl.v.IVID) is null or
          np.v.IVID = pl.v.IVID then
      np.i.IVID := false;
    end if;
    if not np.i.NAME then
      np.v.NAME := pl.v.NAME;
    elsif nvl(np.v.NAME,pl.v.NAME) is null or
          np.v.NAME = pl.v.NAME then
      np.i.NAME := false;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := pl.v.NUMBER_OF_TIMES_MODIFIED;
    elsif nvl(np.v.NUMBER_OF_TIMES_MODIFIED,pl.v.NUMBER_OF_TIMES_MODIFIED) is null or
          np.v.NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED then
      np.i.NUMBER_OF_TIMES_MODIFIED := false;
    end if;
    if not np.i.TYPE_ID then
      np.v.TYPE_ID := pl.v.TYPE_ID;
    elsif nvl(np.v.TYPE_ID,pl.v.TYPE_ID) is null or
          np.v.TYPE_ID = pl.v.TYPE_ID then
      np.i.TYPE_ID := false;
    end if;
  end if;
  -- Initialisation (default values)
  if not for_update then
    null;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := user;
      np.i.CREATED_BY := true;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := sysdate;
      np.i.DATE_CREATED := true;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := 0;
      np.i.NUMBER_OF_TIMES_MODIFIED := true;
    end if;
  end if; -- Initialisation
  -- Maintenance
  if for_update then
    null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := user;
      np.i.CHANGED_BY := true;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := sysdate;
      np.i.DATE_CHANGED := true;
    end if;
  end if; -- Maintenance
  -- String Manipulation (case,trim)
  -- Not applicable to registered types
end maintain;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   )
is
  lck boolean;
begin
  if i_lock = 'Y' then
    lck := true;
  else
    lck := false;
  end if;
  o_ivid := jr_version.check_out(i_irid, i_ivid, lck, i_notes, i_vlabel);
end check_out;
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  )
is
begin
  o_ivid := jr_version.check_in(i_irid, i_ivid, i_notes, i_vlabel);
end check_in;
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  )
is
  save_curr_ivid  number;
  save_new_irid   number;
  save_new_ivid   number;
  copy_mode       boolean;
begin
  -- Caller may want to pre-specifiy copied irid
  save_new_irid := pl.v.irid;
  save_new_ivid := pl.v.ivid;
  pl.v.irid := i_irid;
  copy_mode := true;
  maintain(pl, true);
  copy_mode := false;
  pl.v.irid := save_new_irid;
  if save_new_irid is not null then
    pl.i.irid := true;
  end if;
  save_curr_ivid := pl.v.ivid;
  pl.v.ivid := save_new_ivid;
  if save_new_ivid is not null then
    pl.i.ivid := true;
  end if;
  ins(pl.v.irid, pl);
  if deep = 'Y' then
    jr_copy.copy( i_irid
                , save_curr_ivid
                , pl.v.irid
                , pl.v.ivid
                , null
                , true
                , false
                );
  end if;
  exception
    when others then
      copy_mode := false;
      rmmes.post(operation_failed_fac,operation_failed_code,
                'copy','GLOBAL_NAME');
      raise;
end copy_pac;
--
-- Package instantiation block
--
begin
  is_installed := true;
end SDDoGLOBAL_NAME;
/
create or replace package SDDoGLOBAL_NAME_DOMAIN is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Properties
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
type rm_values is record (
  CHANGED_BY VARCHAR2(30)
, CREATED_BY VARCHAR2(30)
, DATE_CHANGED DATE
, DATE_CREATED DATE
, IRID NUMBER(38)
, IVID NUMBER(38)
, NAME VARCHAR2(256)
, NUMBER_OF_TIMES_MODIFIED NUMBER(38)
, SYSTEM_FLAG VARCHAR2(1)
);
type rm_indicators is record (
  CHANGED_BY boolean not null default false
, CREATED_BY boolean not null default false
, DATE_CHANGED boolean not null default false
, DATE_CREATED boolean not null default false
, IRID boolean not null default false
, IVID boolean not null default false
, NAME boolean not null default false
, NUMBER_OF_TIMES_MODIFIED boolean not null default false
, SYSTEM_FLAG boolean not null default false
);
type data is record (
  v rm_values
, i rm_indicators
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Operations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
);
--
-- del
--
procedure del
(
  irid in number
);
--
-- sel
--
procedure sel
(
  irid in     number
, pl   in out data
);
--
-- upd
--
procedure upd
(
  irid in     number
, pl   in out data
);
--
-- lck
--
procedure lck
(
  irid in number
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   );
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  );
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  );
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Exceptions
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
element_locked exception;
  element_locked_fac  constant varchar2(3) := 'RME';
  element_locked_code constant number      := 10;
invalid_ref exception;
  invalid_ref_fac  constant varchar2(3) := 'RME';
  invalid_ref_code constant number      := 9;
operation_failed exception;
  operation_failed_fac  constant varchar2(3) := 'RME';
  operation_failed_code constant number      := 11;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Globals
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
is_installed boolean := false;
end SDDoGLOBAL_NAME_DOMAIN;
/
create or replace package body SDDoGLOBAL_NAME_DOMAIN is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Cursors
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Retrieve Element Type property values
cursor GetP(irid in rm.reference) is
  select
    el.CHANGED_BY
  , el.CREATED_BY
  , el.DATE_CHANGED
  , el.DATE_CREATED
  , el.IRID
  , el.IVID
  , el.NAME
  , el.NUMBER_OF_TIMES_MODIFIED
  , el.SYSTEM_FLAG
  from SDD_GLOBAL_NAME_DOMAINS el
  where el.irid = GetP.irid
  ;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Procedure and Function forward Declarations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Public Procedures
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
) is
begin
  -- If irid is not null copy it into the property list
  if irid is not NULL then
    pl.v.irid := ins.irid;
    pl.i.irid := true;
  end if;
  -- Property Maintenance
  maintain(pl,false);
  if not pl.i.irid then
    pl.v.irid := jr_util.get_new_irid;
    pl.i.irid := true;
  end if;
  if not pl.i.ivid then
    pl.v.ivid := jr_system_util.get_new_ivid;
    pl.i.ivid := true;
  end if;
  -- Insert
  insert into SDD_GLOBAL_NAME_DOMAINS
  (
    CHANGED_BY
  , CREATED_BY
  , DATE_CHANGED
  , DATE_CREATED
  , IRID
  , IVID
  , NAME
  , NUMBER_OF_TIMES_MODIFIED
  , SYSTEM_FLAG
  )
  values
  (
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NAME
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.SYSTEM_FLAG
  );
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'ins','GLOBAL_NAME_DOMAIN');
    raise;
end ins;
--
-- del
--
procedure del
(
  irid in number
) is
begin
  -- Delete the element itself
  delete from SDD_GLOBAL_NAME_DOMAINS
  where SDD_GLOBAL_NAME_DOMAINS.irid = del.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'del','GLOBAL_NAME_DOMAIN');
    raise;
end del;
--
-- sel
--
procedure sel
(
  irid in number
, pl   in out data
) is
begin
  -- Select
  open GetP(sel.irid);
  fetch GetP into
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NAME
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  , pl.v.SYSTEM_FLAG
  ;
  if GetP%NOTFOUND then
    close GetP;
    rmmes.post(invalid_ref_fac,invalid_ref_code,
               'GLOBAL_NAME_DOMAIN',sel.irid);
    raise invalid_ref;
  end if;
  close GetP;
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'sel','GLOBAL_NAME_DOMAIN');
    raise;
end sel;
--
-- upd
--
procedure upd
(
  irid in number
, pl   in out data
) is
begin
  -- Property Maintenance
    pl.v.irid := irid;
    maintain(pl,true);
  -- Update
  update SDD_GLOBAL_NAME_DOMAINS set
    CHANGED_BY = pl.v.CHANGED_BY
  , CREATED_BY = pl.v.CREATED_BY
  , DATE_CHANGED = pl.v.DATE_CHANGED
  , DATE_CREATED = pl.v.DATE_CREATED
  , IRID = pl.v.IRID
  , IVID = pl.v.IVID
  , NAME = pl.v.NAME
  , NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED
  , SYSTEM_FLAG = pl.v.SYSTEM_FLAG
  where SDD_GLOBAL_NAME_DOMAINS.irid = upd.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'upd','GLOBAL_NAME_DOMAIN');
    raise;
end upd;
--
-- lck
--
procedure lck
(
  irid in number
) is
  dummy rm.reference;
begin
  -- Lock the Element
    select null into dummy
    from SDD_GLOBAL_NAME_DOMAINS
    where SDD_GLOBAL_NAME_DOMAINS.irid = lck.irid for update nowait
    ;
exception
  when no_data_found then
    rmmes.post(invalid_ref_fac, invalid_ref_code,
              'GLOBAL_NAME_DOMAIN',lck.irid);
    raise invalid_ref;
  when others then
    -- Oracle error of 'Resource interested is busy'
    if sqlcode = -54 then
      rmmes.post(element_locked_fac,element_locked_code,
                 lck.irid,'GLOBAL_NAME_DOMAIN');
      raise element_locked;
    end if;
    rmmes.post(operation_failed_fac,operation_failed_code,
              'lck','GLOBAL_NAME_DOMAIN');
    raise;
end lck;
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
) is
  pl   data;           -- Existing Property Values
begin
  -- Retrieve Existing Properties, if for update
  if for_update then
    open GetP(np.v.irid);
    fetch GetP into
      pl.v.CHANGED_BY
    , pl.v.CREATED_BY
    , pl.v.DATE_CHANGED
    , pl.v.DATE_CREATED
    , pl.v.IRID
    , pl.v.IVID
    , pl.v.NAME
    , pl.v.NUMBER_OF_TIMES_MODIFIED
    , pl.v.SYSTEM_FLAG
    ;
    if GetP%NOTFOUND then
      close GetP;
      rmmes.post(invalid_ref_fac,invalid_ref_code,
                 'GLOBAL_NAME_DOMAIN',np.v.irid);
      raise invalid_ref;
    end if;
    close GetP;
  end if;
  -- Not updateable checks
  -- Not applicable to registered types
  -- Not updateable (if existing value is not null) checks
  -- Not applicable to registered types
  -- Old to New Transfer
  if for_update then null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := pl.v.CHANGED_BY;
    elsif nvl(np.v.CHANGED_BY,pl.v.CHANGED_BY) is null or
          np.v.CHANGED_BY = pl.v.CHANGED_BY then
      np.i.CHANGED_BY := false;
    end if;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := pl.v.CREATED_BY;
    elsif nvl(np.v.CREATED_BY,pl.v.CREATED_BY) is null or
          np.v.CREATED_BY = pl.v.CREATED_BY then
      np.i.CREATED_BY := false;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := pl.v.DATE_CHANGED;
    elsif nvl(np.v.DATE_CHANGED,pl.v.DATE_CHANGED) is null or
          np.v.DATE_CHANGED = pl.v.DATE_CHANGED then
      np.i.DATE_CHANGED := false;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := pl.v.DATE_CREATED;
    elsif nvl(np.v.DATE_CREATED,pl.v.DATE_CREATED) is null or
          np.v.DATE_CREATED = pl.v.DATE_CREATED then
      np.i.DATE_CREATED := false;
    end if;
    if not np.i.IRID then
      np.v.IRID := pl.v.IRID;
    elsif nvl(np.v.IRID,pl.v.IRID) is null or
          np.v.IRID = pl.v.IRID then
      np.i.IRID := false;
    end if;
    if not np.i.IVID then
      np.v.IVID := pl.v.IVID;
    elsif nvl(np.v.IVID,pl.v.IVID) is null or
          np.v.IVID = pl.v.IVID then
      np.i.IVID := false;
    end if;
    if not np.i.NAME then
      np.v.NAME := pl.v.NAME;
    elsif nvl(np.v.NAME,pl.v.NAME) is null or
          np.v.NAME = pl.v.NAME then
      np.i.NAME := false;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := pl.v.NUMBER_OF_TIMES_MODIFIED;
    elsif nvl(np.v.NUMBER_OF_TIMES_MODIFIED,pl.v.NUMBER_OF_TIMES_MODIFIED) is null or
          np.v.NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED then
      np.i.NUMBER_OF_TIMES_MODIFIED := false;
    end if;
    if not np.i.SYSTEM_FLAG then
      np.v.SYSTEM_FLAG := pl.v.SYSTEM_FLAG;
    elsif nvl(np.v.SYSTEM_FLAG,pl.v.SYSTEM_FLAG) is null or
          np.v.SYSTEM_FLAG = pl.v.SYSTEM_FLAG then
      np.i.SYSTEM_FLAG := false;
    end if;
  end if;
  -- Initialisation (default values)
  if not for_update then
    null;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := user;
      np.i.CREATED_BY := true;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := sysdate;
      np.i.DATE_CREATED := true;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := 0;
      np.i.NUMBER_OF_TIMES_MODIFIED := true;
    end if;
  end if; -- Initialisation
  -- Maintenance
  if for_update then
    null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := user;
      np.i.CHANGED_BY := true;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := sysdate;
      np.i.DATE_CHANGED := true;
    end if;
  end if; -- Maintenance
  -- String Manipulation (case,trim)
  -- Not applicable to registered types
end maintain;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   )
is
  lck boolean;
begin
  if i_lock = 'Y' then
    lck := true;
  else
    lck := false;
  end if;
  o_ivid := jr_version.check_out(i_irid, i_ivid, lck, i_notes, i_vlabel);
end check_out;
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  )
is
begin
  o_ivid := jr_version.check_in(i_irid, i_ivid, i_notes, i_vlabel);
end check_in;
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  )
is
  save_curr_ivid  number;
  save_new_irid   number;
  save_new_ivid   number;
  copy_mode       boolean;
begin
  -- Caller may want to pre-specifiy copied irid
  save_new_irid := pl.v.irid;
  save_new_ivid := pl.v.ivid;
  pl.v.irid := i_irid;
  copy_mode := true;
  maintain(pl, true);
  copy_mode := false;
  pl.v.irid := save_new_irid;
  if save_new_irid is not null then
    pl.i.irid := true;
  end if;
  save_curr_ivid := pl.v.ivid;
  pl.v.ivid := save_new_ivid;
  if save_new_ivid is not null then
    pl.i.ivid := true;
  end if;
  ins(pl.v.irid, pl);
  if deep = 'Y' then
    jr_copy.copy( i_irid
                , save_curr_ivid
                , pl.v.irid
                , pl.v.ivid
                , null
                , true
                , false
                );
  end if;
  exception
    when others then
      copy_mode := false;
      rmmes.post(operation_failed_fac,operation_failed_code,
                'copy','GLOBAL_NAME_DOMAIN');
      raise;
end copy_pac;
--
-- Package instantiation block
--
begin
  is_installed := true;
end SDDoGLOBAL_NAME_DOMAIN;
/
create or replace package SDDoNLS_GLOBAL_NAME_DOMAIN is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Properties
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
type rm_values is record (
  CHANGED_BY VARCHAR2(30)
, CREATED_BY VARCHAR2(30)
, DATE_CHANGED DATE
, DATE_CREATED DATE
, GND_IRID NUMBER(38)
, IRID NUMBER(38)
, IVID NUMBER(38)
, NLS_LANG VARCHAR2(3)
, NLS_NAME VARCHAR2(256)
, NLS_PLURAL_NAME VARCHAR2(256)
, NUMBER_OF_TIMES_MODIFIED NUMBER(38)
);
type rm_indicators is record (
  CHANGED_BY boolean not null default false
, CREATED_BY boolean not null default false
, DATE_CHANGED boolean not null default false
, DATE_CREATED boolean not null default false
, GND_IRID boolean not null default false
, IRID boolean not null default false
, IVID boolean not null default false
, NLS_LANG boolean not null default false
, NLS_NAME boolean not null default false
, NLS_PLURAL_NAME boolean not null default false
, NUMBER_OF_TIMES_MODIFIED boolean not null default false
);
type data is record (
  v rm_values
, i rm_indicators
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Operations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
);
--
-- del
--
procedure del
(
  irid in number
);
--
-- sel
--
procedure sel
(
  irid in     number
, pl   in out data
);
--
-- upd
--
procedure upd
(
  irid in     number
, pl   in out data
);
--
-- lck
--
procedure lck
(
  irid in number
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   );
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  );
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  );
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Exceptions
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
element_locked exception;
  element_locked_fac  constant varchar2(3) := 'RME';
  element_locked_code constant number      := 10;
invalid_ref exception;
  invalid_ref_fac  constant varchar2(3) := 'RME';
  invalid_ref_code constant number      := 9;
operation_failed exception;
  operation_failed_fac  constant varchar2(3) := 'RME';
  operation_failed_code constant number      := 11;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Globals
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
is_installed boolean := false;
end SDDoNLS_GLOBAL_NAME_DOMAIN;
/
create or replace package body SDDoNLS_GLOBAL_NAME_DOMAIN is
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Cursors
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Retrieve Element Type property values
cursor GetP(irid in rm.reference) is
  select
    el.CHANGED_BY
  , el.CREATED_BY
  , el.DATE_CHANGED
  , el.DATE_CREATED
  , el.GND_IRID
  , el.IRID
  , el.IVID
  , el.NLS_LANG
  , el.NLS_NAME
  , el.NLS_PLURAL_NAME
  , el.NUMBER_OF_TIMES_MODIFIED
  from SDD_NLS_GLOBAL_NAME_DOMAINS el
  where el.irid = GetP.irid
  ;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Private Procedure and Function forward Declarations
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
);
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Public Procedures
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- ins
--
procedure ins
(
  irid in     number
, pl   in out data
) is
begin
  -- If irid is not null copy it into the property list
  if irid is not NULL then
    pl.v.irid := ins.irid;
    pl.i.irid := true;
  end if;
  -- Property Maintenance
  maintain(pl,false);
  if not pl.i.irid then
    pl.v.irid := jr_util.get_new_irid;
    pl.i.irid := true;
  end if;
  if not pl.i.ivid then
    pl.v.ivid := jr_system_util.get_new_ivid;
    pl.i.ivid := true;
  end if;
  -- Insert
  insert into SDD_NLS_GLOBAL_NAME_DOMAINS
  (
    CHANGED_BY
  , CREATED_BY
  , DATE_CHANGED
  , DATE_CREATED
  , GND_IRID
  , IRID
  , IVID
  , NLS_LANG
  , NLS_NAME
  , NLS_PLURAL_NAME
  , NUMBER_OF_TIMES_MODIFIED
  )
  values
  (
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.GND_IRID
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NLS_LANG
  , pl.v.NLS_NAME
  , pl.v.NLS_PLURAL_NAME
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  );
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'ins','NLS_GLOBAL_NAME_DOMAIN');
    raise;
end ins;
--
-- del
--
procedure del
(
  irid in number
) is
begin
  -- Delete the element itself
  delete from SDD_NLS_GLOBAL_NAME_DOMAINS
  where SDD_NLS_GLOBAL_NAME_DOMAINS.irid = del.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'del','NLS_GLOBAL_NAME_DOMAIN');
    raise;
end del;
--
-- sel
--
procedure sel
(
  irid in number
, pl   in out data
) is
begin
  -- Select
  open GetP(sel.irid);
  fetch GetP into
    pl.v.CHANGED_BY
  , pl.v.CREATED_BY
  , pl.v.DATE_CHANGED
  , pl.v.DATE_CREATED
  , pl.v.GND_IRID
  , pl.v.IRID
  , pl.v.IVID
  , pl.v.NLS_LANG
  , pl.v.NLS_NAME
  , pl.v.NLS_PLURAL_NAME
  , pl.v.NUMBER_OF_TIMES_MODIFIED
  ;
  if GetP%NOTFOUND then
    close GetP;
    rmmes.post(invalid_ref_fac,invalid_ref_code,
               'NLS_GLOBAL_NAME_DOMAIN',sel.irid);
    raise invalid_ref;
  end if;
  close GetP;
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'sel','NLS_GLOBAL_NAME_DOMAIN');
    raise;
end sel;
--
-- upd
--
procedure upd
(
  irid in number
, pl   in out data
) is
begin
  -- Property Maintenance
    pl.v.irid := irid;
    maintain(pl,true);
  -- Update
  update SDD_NLS_GLOBAL_NAME_DOMAINS set
    CHANGED_BY = pl.v.CHANGED_BY
  , CREATED_BY = pl.v.CREATED_BY
  , DATE_CHANGED = pl.v.DATE_CHANGED
  , DATE_CREATED = pl.v.DATE_CREATED
  , GND_IRID = pl.v.GND_IRID
  , IRID = pl.v.IRID
  , IVID = pl.v.IVID
  , NLS_LANG = pl.v.NLS_LANG
  , NLS_NAME = pl.v.NLS_NAME
  , NLS_PLURAL_NAME = pl.v.NLS_PLURAL_NAME
  , NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED
  where SDD_NLS_GLOBAL_NAME_DOMAINS.irid = upd.irid
  ;
  -- Constraints
  -- Not applicable to registered types
exception
  when others then
    rmmes.post(operation_failed_fac,operation_failed_code,
              'upd','NLS_GLOBAL_NAME_DOMAIN');
    raise;
end upd;
--
-- lck
--
procedure lck
(
  irid in number
) is
  dummy rm.reference;
begin
  -- Lock the Element
    select null into dummy
    from SDD_NLS_GLOBAL_NAME_DOMAINS
    where SDD_NLS_GLOBAL_NAME_DOMAINS.irid = lck.irid for update nowait
    ;
exception
  when no_data_found then
    rmmes.post(invalid_ref_fac, invalid_ref_code,
              'NLS_GLOBAL_NAME_DOMAIN',lck.irid);
    raise invalid_ref;
  when others then
    -- Oracle error of 'Resource interested is busy'
    if sqlcode = -54 then
      rmmes.post(element_locked_fac,element_locked_code,
                 lck.irid,'NLS_GLOBAL_NAME_DOMAIN');
      raise element_locked;
    end if;
    rmmes.post(operation_failed_fac,operation_failed_code,
              'lck','NLS_GLOBAL_NAME_DOMAIN');
    raise;
end lck;
--
-- maintain
--
procedure maintain
(
  np         in out data
, for_update in     boolean
) is
  pl   data;           -- Existing Property Values
begin
  -- Retrieve Existing Properties, if for update
  if for_update then
    open GetP(np.v.irid);
    fetch GetP into
      pl.v.CHANGED_BY
    , pl.v.CREATED_BY
    , pl.v.DATE_CHANGED
    , pl.v.DATE_CREATED
    , pl.v.GND_IRID
    , pl.v.IRID
    , pl.v.IVID
    , pl.v.NLS_LANG
    , pl.v.NLS_NAME
    , pl.v.NLS_PLURAL_NAME
    , pl.v.NUMBER_OF_TIMES_MODIFIED
    ;
    if GetP%NOTFOUND then
      close GetP;
      rmmes.post(invalid_ref_fac,invalid_ref_code,
                 'NLS_GLOBAL_NAME_DOMAIN',np.v.irid);
      raise invalid_ref;
    end if;
    close GetP;
  end if;
  -- Not updateable checks
  -- Not applicable to registered types
  -- Not updateable (if existing value is not null) checks
  -- Not applicable to registered types
  -- Old to New Transfer
  if for_update then null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := pl.v.CHANGED_BY;
    elsif nvl(np.v.CHANGED_BY,pl.v.CHANGED_BY) is null or
          np.v.CHANGED_BY = pl.v.CHANGED_BY then
      np.i.CHANGED_BY := false;
    end if;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := pl.v.CREATED_BY;
    elsif nvl(np.v.CREATED_BY,pl.v.CREATED_BY) is null or
          np.v.CREATED_BY = pl.v.CREATED_BY then
      np.i.CREATED_BY := false;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := pl.v.DATE_CHANGED;
    elsif nvl(np.v.DATE_CHANGED,pl.v.DATE_CHANGED) is null or
          np.v.DATE_CHANGED = pl.v.DATE_CHANGED then
      np.i.DATE_CHANGED := false;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := pl.v.DATE_CREATED;
    elsif nvl(np.v.DATE_CREATED,pl.v.DATE_CREATED) is null or
          np.v.DATE_CREATED = pl.v.DATE_CREATED then
      np.i.DATE_CREATED := false;
    end if;
    if not np.i.GND_IRID then
      np.v.GND_IRID := pl.v.GND_IRID;
    elsif nvl(np.v.GND_IRID,pl.v.GND_IRID) is null or
          np.v.GND_IRID = pl.v.GND_IRID then
      np.i.GND_IRID := false;
    end if;
    if not np.i.IRID then
      np.v.IRID := pl.v.IRID;
    elsif nvl(np.v.IRID,pl.v.IRID) is null or
          np.v.IRID = pl.v.IRID then
      np.i.IRID := false;
    end if;
    if not np.i.IVID then
      np.v.IVID := pl.v.IVID;
    elsif nvl(np.v.IVID,pl.v.IVID) is null or
          np.v.IVID = pl.v.IVID then
      np.i.IVID := false;
    end if;
    if not np.i.NLS_LANG then
      np.v.NLS_LANG := pl.v.NLS_LANG;
    elsif nvl(np.v.NLS_LANG,pl.v.NLS_LANG) is null or
          np.v.NLS_LANG = pl.v.NLS_LANG then
      np.i.NLS_LANG := false;
    end if;
    if not np.i.NLS_NAME then
      np.v.NLS_NAME := pl.v.NLS_NAME;
    elsif nvl(np.v.NLS_NAME,pl.v.NLS_NAME) is null or
          np.v.NLS_NAME = pl.v.NLS_NAME then
      np.i.NLS_NAME := false;
    end if;
    if not np.i.NLS_PLURAL_NAME then
      np.v.NLS_PLURAL_NAME := pl.v.NLS_PLURAL_NAME;
    elsif nvl(np.v.NLS_PLURAL_NAME,pl.v.NLS_PLURAL_NAME) is null or
          np.v.NLS_PLURAL_NAME = pl.v.NLS_PLURAL_NAME then
      np.i.NLS_PLURAL_NAME := false;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := pl.v.NUMBER_OF_TIMES_MODIFIED;
    elsif nvl(np.v.NUMBER_OF_TIMES_MODIFIED,pl.v.NUMBER_OF_TIMES_MODIFIED) is null or
          np.v.NUMBER_OF_TIMES_MODIFIED = pl.v.NUMBER_OF_TIMES_MODIFIED then
      np.i.NUMBER_OF_TIMES_MODIFIED := false;
    end if;
  end if;
  -- Initialisation (default values)
  if not for_update then
    null;
    if not np.i.CREATED_BY then
      np.v.CREATED_BY := user;
      np.i.CREATED_BY := true;
    end if;
    if not np.i.DATE_CREATED then
      np.v.DATE_CREATED := sysdate;
      np.i.DATE_CREATED := true;
    end if;
    if not np.i.NUMBER_OF_TIMES_MODIFIED then
      np.v.NUMBER_OF_TIMES_MODIFIED := 0;
      np.i.NUMBER_OF_TIMES_MODIFIED := true;
    end if;
  end if; -- Initialisation
  -- Maintenance
  if for_update then
    null;
    if not np.i.CHANGED_BY then
      np.v.CHANGED_BY := user;
      np.i.CHANGED_BY := true;
    end if;
    if not np.i.DATE_CHANGED then
      np.v.DATE_CHANGED := sysdate;
      np.i.DATE_CHANGED := true;
    end if;
  end if; -- Maintenance
  -- String Manipulation (case,trim)
  -- Not applicable to registered types
end maintain;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Constraints
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Not applicable to registered types
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- Versioning
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- check_out
--
procedure check_out( i_irid       number
                   , i_ivid       number
                   , i_lock       varchar2 default 'Y'
                   , i_notes      varchar2 default null
                   , i_vlabel     varchar2 default null
                   , o_ivid   out number
                   )
is
  lck boolean;
begin
  if i_lock = 'Y' then
    lck := true;
  else
    lck := false;
  end if;
  o_ivid := jr_version.check_out(i_irid, i_ivid, lck, i_notes, i_vlabel);
end check_out;
--
-- check_in
--
procedure check_in( i_irid        number
                  , i_ivid        number
                  , i_notes       varchar2 default null
                  , i_vlabel      varchar2 default null
                  , o_ivid    out number
                  )
is
begin
  o_ivid := jr_version.check_in(i_irid, i_ivid, i_notes, i_vlabel);
end check_in;
--
-- copy
--
procedure copy_pac( i_irid              in      number
                  , container_reference in      number
                  , pl                  in out  data
                  , ensure_unique       in      varchar2 default 'Y'
                  , deep                in      varchar2 default 'Y'
                  )
is
  save_curr_ivid  number;
  save_new_irid   number;
  save_new_ivid   number;
  copy_mode       boolean;
begin
  -- Caller may want to pre-specifiy copied irid
  save_new_irid := pl.v.irid;
  save_new_ivid := pl.v.ivid;
  pl.v.irid := i_irid;
  copy_mode := true;
  maintain(pl, true);
  copy_mode := false;
  pl.v.irid := save_new_irid;
  if save_new_irid is not null then
    pl.i.irid := true;
  end if;
  save_curr_ivid := pl.v.ivid;
  pl.v.ivid := save_new_ivid;
  if save_new_ivid is not null then
    pl.i.ivid := true;
  end if;
  ins(pl.v.irid, pl);
  if deep = 'Y' then
    jr_copy.copy( i_irid
                , save_curr_ivid
                , pl.v.irid
                , pl.v.ivid
                , null
                , true
                , false
                );
  end if;
  exception
    when others then
      copy_mode := false;
      rmmes.post(operation_failed_fac,operation_failed_code,
                'copy','NLS_GLOBAL_NAME_DOMAIN');
      raise;
end copy_pac;
--
-- Package instantiation block
--
begin
  is_installed := true;
end SDDoNLS_GLOBAL_NAME_DOMAIN;
/
