REM ***************************************************************************
REM   Hand-carved package body for LOV INCLUSION created on 07-DEC-98
REM ***************************************************************************
CREATE OR REPLACE PACKAGE BODY cihlvi IS

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

  sac_type constant varchar2(6) := 'LVI';
  pac_type constant varchar2(6) := 'MOD';
  slv_to_delete number := NULL;

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

PROCEDURE pre_process(operation varchar2,id number,
  pl in out NOCOPY ciolov_inclusion.data)
IS
BEGIN
  -- Access rights for lov inclusion derive from the general module
-- Access rights checking performed by triggers in config 4.0.9
--  ciiacc.check_access_rights(pl.v.general_module_reference,pac_type,
--    'UPD','SHR');
  -- Validate user-defined properties
  if cieval.is_extended then
    cieval.check_element(id,pl.v.types,(operation='UPD')
      ,pl.v.user_defined_property_0,pl.i.user_defined_property_0
      ,pl.v.user_defined_property_1,pl.i.user_defined_property_1
      ,pl.v.user_defined_property_2,pl.i.user_defined_property_2
      ,pl.v.user_defined_property_3,pl.i.user_defined_property_3
      ,pl.v.user_defined_property_4,pl.i.user_defined_property_4
      ,pl.v.user_defined_property_5,pl.i.user_defined_property_5
      ,pl.v.user_defined_property_6,pl.i.user_defined_property_6
      ,pl.v.user_defined_property_7,pl.i.user_defined_property_7
      ,pl.v.user_defined_property_8,pl.i.user_defined_property_8
      ,pl.v.user_defined_property_9,pl.i.user_defined_property_9
      ,pl.v.user_defined_property_10,pl.i.user_defined_property_10
      ,pl.v.user_defined_property_11,pl.i.user_defined_property_11
      ,pl.v.user_defined_property_12,pl.i.user_defined_property_12
      ,pl.v.user_defined_property_13,pl.i.user_defined_property_13
      ,pl.v.user_defined_property_14,pl.i.user_defined_property_14
      ,pl.v.user_defined_property_15,pl.i.user_defined_property_15
      ,pl.v.user_defined_property_16,pl.i.user_defined_property_16
      ,pl.v.user_defined_property_17,pl.i.user_defined_property_17
      ,pl.v.user_defined_property_18,pl.i.user_defined_property_18
      ,pl.v.user_defined_property_19,pl.i.user_defined_property_19
      );
  end if;
  -- Initialize unassigned properties with default values on INSERT
  if operation = 'INS' then
    pl.v.element_type_name := sac_type;
    pl.i.element_type_name := true;
    if not pl.i.usage_sequence then
      select nvl(max(usage_sequence),0) + 100
        into pl.v.usage_sequence
        from ci_lov_inclusions lvi
       where lvi.general_module_reference = pl.v.general_module_reference;
       pl.i.usage_sequence := true;
    end if;
  end if;
  
END;

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

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

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

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

PROCEDURE post_process(operation varchar2,id number,
  pl ciolov_inclusion.data)
IS
BEGIN
  -- You cannot have two LVIs within the same GEM where the MUN name is the same
  if operation = 'INS' then
    rmdbg.trace('In CIHMCN.post_process');
    rmdbg.trace('Operation is : ' ||operation);
    rmman.log_change('CIHMUNE_UNIQUE',pl.v.list_of_values_reference);
    if not cdapi.load_mode then -- Added for 1208172
      begin
        update sdd_mun slv
           set slv.general_module_ref = pl.v.general_module_reference
         where slv.irid = pl.v.list_of_values_reference
         and   slv.module_unit_subtype = 'SLV';
      exception
        WHEN NO_DATA_FOUND THEN
          null;  -- It's perfectly fine if the LOV is not SPECIFIC
      end;
    end if;
  end if;

END;

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

PROCEDURE post_process(operation varchar2,id number)
IS
BEGIN
  if slv_to_delete IS NOT NULL then
    ciospecific_lov.del(slv_to_delete);
    slv_to_delete := NULL;
  end if;
END;

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

PROCEDURE check_delete(id number)
IS
  found_others boolean := FALSE;
  error_count number := 0;

  CURSOR sel_lvi IS
    SELECT  lvi2.id
    ,       lvi1.list_of_values_reference
    FROM    ci_lov_inclusions   lvi1
    ,       ci_lov_inclusions   lvi2
    ,       ci_module_units     mun
    WHERE   lvi1.id = check_delete.id
    AND     lvi1.general_module_reference = lvi2.general_module_reference
    AND     lvi1.list_of_values_reference = lvi2.list_of_values_reference
    AND     mun.id = lvi1.list_of_values_reference
    AND     mun.module_unit_subtype = 'SLV';

BEGIN
  --Bug 900482 If this is the only inclusion for a SLV then cascade delete the 
  -- SLV too.
  -- Code added for 6.5 Limited Production, do not remove
  FOR clvi IN sel_lvi LOOP
    if clvi.id != check_delete.id then
       found_others := TRUE;
    else
       slv_to_delete := clvi.list_of_values_reference;
    end if;
  END LOOP;

  if found_others = TRUE then
    slv_to_delete := NULL;
  end if;

  EXCEPTION
    -- This may happen if the LOV inclusion is for a reusable LOV, we want to ignore this
    WHEN NO_DATA_FOUND then
      NULL;    
    WHEN OTHERS then
      RAISE;
END;

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