-- Package jr_dependency_purge
--
prompt Package Body: jr_dependency_purge
create or  replace package body jr_dependency_purge is

  -- -------------------------------------
  -- Private interface
  -- -------------------------------------
  function get_workarea_id(i_workarea in varchar2)
  return number;
  function get_policy_id(i_policy_name in varchar2)
  return number;

  procedure identify_purge_candidates(i_policy_id in number);
  procedure apply_branch_policy(i_policy_id in number);
  procedure apply_config_policy(i_policy_id in number);

  procedure purge_candidates;

  procedure update_purge_control;

  procedure check_system_privilege;

  -- -------------------------------------
  -- Public implementation
  -- -------------------------------------
  -----------------------------------------------------
  -- purge all objects in the  workarea in accordance
  -- with the purging policy identified by policy_name
  -- or policy_id.
  -----------------------------------------------------
  procedure purge_workarea(i_workarea   in varchar2, i_policy_name in varchar2)
  is
    l_workarea number;
  begin
    l_workarea := get_workarea_id(i_workarea);
    purge_workarea(l_workarea, i_policy_name);
  end;

  procedure purge_workarea(i_workarea   in varchar2, i_policy_id   in number)
  is
    l_workarea number;
  begin
    l_workarea := get_workarea_id(i_workarea);
    purge_workarea(l_workarea, i_policy_id);
  end;

  procedure purge_workarea(i_workarea   in number, i_policy_name in varchar2)
  is
    l_policy_id number;
  begin

    l_policy_id := get_policy_id(i_policy_name);
    purge_workarea(i_workarea, l_policy_id);

  end;

  procedure purge_workarea(i_workarea   in number, i_policy_id   in number)
  is
    l_current_workarea number;
  begin

    -- check that the user is permitted to purge dependencies
    check_system_privilege;

    -- save the current workarea, and set context to
    -- workarea to purge
    l_current_workarea :=jr_context.workarea;

    begin
      jr_context.set_workarea(i_workarea);

      -- do the actual purge in two steps, first find the objects
      -- to purge...
      identify_purge_candidates(i_policy_id);
      -- ...then purge dependencies for these objects
      purge_candidates;

      -- update the audit
      update_purge_control;

      -- leave client to do the commit
      -- commit;

    exception
    when others then
      -- need restore workarea
      -- and reraise the exception
      -- leave client to do the rollback
      -- rollback;
      jr_context.set_workarea(l_current_workarea);
      raise;
    end;

    -- and finally restore the workarea context
    jr_context.set_workarea(l_current_workarea);

  end;

  -- -------------------------------------
  -- Private implementation
  -- -------------------------------------

  -- --------------------------------------------------
  -- Identifies candidate dependencies for purging  by
  -- applying purging policy to context workarea.
  -- --------------------------------------------------
  procedure identify_purge_candidates(i_policy_id in number)
  is
  begin

    -- First clear purge_candidate
    update sdd_dependencies d
    set purge_candidate = null;

    -- First tag all possible candidates. A candidate is
    -- a dependency that involves a version of an object
    -- in the workarea. We can't just tag the version which
    -- is in the workarea, because we want to purge all versions
    -- (and apply the branch and config policies as exceptions).

    -- Supplier purge candidates...first tag based on supplier pacs 
    -- which have a version visible in workarea
    update 
    sdd_dependencies d
    set purge_candidate = 'S'
    where d.supplier_irid in
      (select o.irid
       from sdd_object_versions o
       where d.supplier_irid = o.irid);

    -- tag the SACs which are visible in workarea (this will only pick up the
    -- in workarea version)
    update 
    sdd_dependencies d
    set purge_candidate = 'S'
    where d.supplier_parent_ivid is not null
    and d.supplier_parent_ivid in
     (select o.ivid
      from sdd_object_versions o
      where d.supplier_parent_ivid = o.ivid);
   
    -- Now - to try and cover the old versions of in context sacs, tag
    -- SAC supplier dependencies based on tagged PAC supplier dependencies.
    -- This is based on the assumption that the sac's owning pac will have
    -- dependencies stored against it (which is true for Oracle structured 
    -- parser).
    update 
    sdd_dependencies d
    set purge_candidate = 'S'
    where d.supplier_parent_ivid in
      (select d2.supplier_ivid
       from   sdd_dependencies d2
       where  d2.purge_candidate = 'S');

    -- Client purge candidates... see comments above
    update 
    sdd_dependencies d
    set purge_candidate = 'C'
    where d.client_irid in
      (select o.irid
       from sdd_object_versions o
       where d.client_irid = o.irid);

    update 
    sdd_dependencies d
    set purge_candidate = 'C'
    where d.client_parent_ivid is not null
    and d.client_parent_ivid in
     (select o.ivid
      from sdd_object_versions o
      where d.client_parent_ivid = o.ivid);
   
    update 
    sdd_dependencies d
    set purge_candidate = 'C'
    where d.client_parent_ivid in
      (select d2.client_ivid
       from   sdd_dependencies d2
       where  d2.purge_candidate = 'C');

    -- Now set purge candidates according to policies
    apply_branch_policy(i_policy_id);
    apply_config_policy(i_policy_id);

  end;

  -- ----------------------------------------------
  -- Excludes purging candidates based on branch policies
  -- ----------------------------------------------
  procedure apply_branch_policy(i_policy_id in number)
  is
  begin

    -- First exclude tips (always)
    -- and we have to treat client and supplier separately
    -- first suppliers..
    update sdd_dependencies d
    set purge_candidate=null
    where exists
      ( select null
        from  i$sdd_version_associations     aLast
            , sdd_dpnd_purging_br_policies   p

        where

        -- only look at suppliers that we've identified for purging        
        d.purge_candidate = 'S'

        -- and now only look at versions on branches that we have
        -- a save policy for (for any policy we save the tip version)
        and p.branch_id  = aLast.branch_id
        and p.policy_id = i_policy_id

        -- and now see if its the tip
        and aLast.edge_kind = 'T'
        and   ( aLast.to_ivid = d.supplier_ivid
              or (d.supplier_parent_ivid is not null
                  and aLast.to_ivid = d.supplier_parent_ivid)
              )
       );


    -- ...next clients
    update sdd_dependencies d
    set purge_candidate=null
    where exists
      ( select null
        from  i$sdd_version_associations     aLast
            , sdd_dpnd_purging_br_policies   p
        where

        -- only look at clients we've identified for purging
        d.purge_candidate = 'C'

        -- and now only look at versions on branches that we have
        -- a save policy for (for any policy we save the tip version)
        and p.branch_id  = aLast.branch_id
        and p.policy_id = i_policy_id

        -- and now see if its the tip
        and aLast.edge_kind = 'T'
        and   ( aLast.to_ivid = d.client_ivid
              or (d.client_parent_ivid is not null
                  and aLast.to_ivid = d.client_parent_ivid)
              )
      );

    -- Now exclude the version before the tip, if we
    -- have a branch policy that says to keep the last two.
    -- again, have to do suppliers and clients separately

    -- first suppliers...
    update sdd_dependencies d
    set purge_candidate=null
    where exists
    ( select null
      from   i$sdd_version_associations aLast
           , i$sdd_version_associations aLastButOne
           , sdd_dpnd_purging_br_policies   p

      where

      -- only look at suppliers that we still consider for purging
            d.purge_candidate = 'S'

      -- and find possible last but one branches
      and   aLastButOne.edge_kind = 'N'
      and   ((
               d.supplier_parent_ivid is not null
               and aLastButOne.from_ivid  = d.supplier_parent_ivid
              )
              or aLastButOne.from_ivid = d.supplier_ivid
            )

      -- and only look at branches covered by policy
      and   p.policy_id = i_policy_id
      and   aLast.branch_id = p.branch_id
      and   p.number_of_versions = 2

      -- and really make sure we have the last but one branch
      and   aLastButOne.to_ivid    = aLast.from_ivid
      and   aLast.edge_kind  = 'T'
    );

    -- and next clients
    update sdd_dependencies d
    set purge_candidate=null
    where exists
    ( select null
      from   i$sdd_version_associations aLast
           , i$sdd_version_associations aLastButOne
           , sdd_dpnd_purging_br_policies   p

      where

      -- restrict clients we have identified
            d.purge_candidate = 'C'
      -- and find possible last but one branches
      and   aLastButOne.edge_kind = 'N'
      and   ((
               d.client_parent_ivid is not null
               and aLastButOne.from_ivid  = d.client_parent_ivid
              )
              or aLastButOne.from_ivid = d.client_ivid
            )

      -- and only look at branches covered by policy
      and   p.policy_id = i_policy_id
      and   aLast.branch_id = p.branch_id
      and   p.number_of_versions = 2

      -- and really make sure we have the last but one branch
      and   aLastButOne.to_ivid    = aLast.from_ivid
      and   aLast.edge_kind  = 'T'
    );

  end;

  -- ----------------------------------------------
  -- Excludes purging candidates based on config policies
  -- ----------------------------------------------
  procedure apply_config_policy(i_policy_id in number)
  is
  begin
    -- exclude candidates based on membership of configurations
    -- to keep.
    update sdd_dependencies d
    set purge_candidate=null
    where exists
      ( select null
        from  sdd_configuration_members       c
            , sdd_dpnd_purging_cfg_policies   p
        where
              -- only apply relevant config policies
              p.policy_id = i_policy_id
        and   c.config_ivid = p.config_ivid
        and   p.keep_dependencies_for = 'Y'
        and   (c.object_ivid = d.supplier_ivid
               or ( d.supplier_parent_ivid is not null
                   and c.object_ivid = d.supplier_parent_ivid)
               or c.object_ivid = d.client_ivid
               or ( d.client_parent_ivid is not null
                   and c.object_ivid = d.client_parent_ivid)
              )
       );

  end;

  -- ----------------------------------------------
  -- Purge dependencies tagged for purging
  -- ----------------------------------------------
  procedure purge_candidates
  is
  begin

    -- First clear the obj_notm_when_analyzed property
    update i$sdd_object_versions
    set obj_notm_when_analyzed = null
    where 
      ivid in
      (select supplier_ivid
        from sdd_dependencies
        where purge_candidate = 'S');

    update i$sdd_object_versions
    set obj_notm_when_analyzed = null
    where 
      ivid in
      (select supplier_parent_ivid
        from sdd_dependencies
        where purge_candidate = 'S');

    update i$sdd_object_versions
    set obj_notm_when_analyzed = null
    where 
      ivid in
      (select client_ivid
        from sdd_dependencies
        where purge_candidate = 'C');

    update i$sdd_object_versions
    set obj_notm_when_analyzed = null
    where 
      ivid in
      (select client_parent_ivid
        from sdd_dependencies
        where purge_candidate = 'C');
       
    -- And here we must delete from the base table and not the
    -- version resolved view - as we need to delete from
    -- earlier versions than are visisble in the version resolved view.
    delete
    from sdd_dependencies
    where purge_candidate is not null;
  end;

  -- ----------------------------------------------
  -- Check that the user has the system priviledge to manage
  -- dependencies.
  -- Will raise an exception if user doesn't have
  -- manage capability on dependencies.
  -- ----------------------------------------------
  procedure check_system_privilege
  is
    l_username       varchar2(100);
  begin
    -- Raises an exception if user doesn't have manage priviledge.
	--
	-- Changed from dual to sys.dual To fix bug 1311500
	--
    select USER
    into l_username
    from sys.dual;
    jr_sys_privs.CHECK_MANAGE(l_username, 'DEP');
  end;

  -- ----------------------------------------------
  -- Updates purge control table - recording
  -- current data as date of purge last running.
  -- ----------------------------------------------
  procedure update_purge_control
  is
  begin
    update sdd_dpnd_purging_policies
    set date_last_purged = SYSDATE;
  end;

  -- ----------------------------------------------
  -- Get workarea id based on workarea name
  -- will throw a WORKAREA_ERROR if workarea name
  -- is not valid.
  -- ----------------------------------------------
  function get_workarea_id(i_workarea in varchar2)
  return number
  is
    l_workarea number := null;
  begin

    select irid
    into l_workarea
    from sdd_workareas
    where name=i_workarea;

    return l_workarea;

  exception when NO_DATA_FOUND
  then

    rmmes.post('CDR', 6008, i_workarea);
    raise WORKAREA_ERROR;

  end;

  -- ----------------------------------------------
  -- Get policy id based on policy name
  -- will throw a POLICY_NAME_ERROR if policy name
  -- is not valid.
  -- ----------------------------------------------
  function get_policy_id(i_policy_name in varchar2)
  return number
  is
    l_policy_id number := null;
  begin

    select irid
    into l_policy_id
    from sdd_dpnd_purging_policies
    where policy_name=i_policy_name;

    return l_policy_id;

  exception when NO_DATA_FOUND
  then

    rmmes.post('CDR', 6009, i_policy_name);
    raise POLICY_ERROR;

  end;


end jr_dependency_purge;
/