-- created by Oraschemadoc Fri Jun 13 14:06:12 2008
-- visit http://www.yarpen.cz/oraschemadoc/ for more info

  CREATE OR REPLACE PACKAGE "RHNSAT"."RHN_ENTITLEMENTS" 
is
	body_version varchar2(100) := '$Id: universe.satellite.sql,v 1.2 2008/06/09 08:37:56 mmraka Exp $';
   type ents_array is varray(10) of rhnServerGroupType.label%TYPE;
    procedure remove_org_entitlements (
        org_id_in number
    );
    function entitlement_grants_service (
	    entitlement_in in varchar2,
		service_level_in in varchar2
	) return number;
	function lookup_entitlement_group (
		org_id_in in number,
		type_label_in in varchar2 := 'sw_mgr_entitled'
	) return number;
	function create_entitlement_group (
		org_id_in in number,
		type_label_in in varchar2 := 'sw_mgr_entitled'
	) return number;
   function can_entitle_server (
      server_id_in   in number,
      type_label_in  in varchar2
   )
   return number;
   function can_switch_base (
      server_id_in   in    integer,
      type_label_in  in    varchar2
   )
   return number;
   function find_compatible_sg (
      server_id_in in number,
      type_label_in in varchar2,
      sgid_out out number
   )
   return boolean;
	procedure entitle_server (
		server_id_in in number,
		type_label_in in varchar2 := 'sw_mgr_entitled'
	);
	procedure remove_server_entitlement (
		server_id_in in number,
		type_label_in in varchar2 := 'sw_mgr_entitled',
        repoll_virt_guests in number := 1
	);
	procedure unentitle_server (
		server_id_in in number
	);
    procedure repoll_virt_guest_entitlements(
        server_id_in in number
    );
	function get_server_entitlement (
		server_id_in in number
	) return ents_array;
	procedure modify_org_service (
		org_id_in in number,
		service_label_in in varchar2,
		enable_in in char
	);
    procedure set_customer_enterprise (
		customer_id_in in number
	);
	procedure set_customer_provisioning (
		customer_id_in in number
	);
	procedure set_customer_nonlinux (
		customer_id_in in number
	);
    procedure unset_customer_enterprise (
		customer_id_in in number
	);
	procedure unset_customer_provisioning (
		customer_id_in in number
	);
	procedure unset_customer_nonlinux (
		customer_id_in in number
	);
    procedure assign_system_entitlement(
        group_label_in in varchar2,
        from_org_id_in in number,
        to_org_id_in in number,
        quantity_in in number
    );
    procedure assign_channel_entitlement(
        channel_family_label_in in varchar2,
        from_org_id_in in number,
        to_org_id_in in number,
        quantity_in in number
    );
    procedure activate_system_entitlement(
        org_id_in in number,
        group_label_in in varchar2,
        quantity_in in number
    );
    procedure activate_channel_entitlement(
        org_id_in in number,
        channel_family_label_in in varchar2,
        quantity_in in number
    );
    procedure set_group_count (
		customer_id_in in number,	-- customer_id
		type_in in char,			-- 'U' or 'S'
		group_type_in in number,	-- rhn[User|Server]GroupType.id
		quantity_in in number		-- quantity
    );
    procedure set_family_count (
		customer_id_in in number,		-- customer_id
		channel_family_id_in in number,	-- 246
		quantity_in in number			-- 3
    );
    procedure entitle_last_modified_servers (
		customer_id_in in number,	-- customer_id
		type_label_in in varchar2,	-- 'enterprise_entitled'
		quantity_in in number		-- 3
    );
	procedure prune_everything (
		customer_id_in in number
	);
	procedure subscribe_newest_servers (
		customer_id_in in number
	);
end rhn_entitlements;
CREATE OR REPLACE PACKAGE BODY "RHNSAT"."RHN_ENTITLEMENTS" 
is
	body_version varchar2(100) := '$Id: universe.satellite.sql,v 1.2 2008/06/09 08:37:56 mmraka Exp $';
    procedure remove_org_entitlements(
        org_id_in in number
    )
    is
        cursor system_ents is
        select sg.id, sg.max_members, sg.group_type
        from rhnServerGroup sg
        where group_type is not null
          and org_id = org_id_in;
        cursor channel_subs is
        select pcf.channel_family_id, pcf.max_members
        from rhnChannelFamily cf,
             rhnPrivateChannelFamily pcf
        where pcf.org_id = org_id_in
          and pcf.channel_family_id = cf.id
          and cf.org_id is null;
    begin
        for system_ent in system_ents loop
            update rhnServerGroup
            set max_members = max_members + system_ent.max_members
            where org_id = 1
              and group_type = system_ent.group_type;
        end loop;
        update rhnServerGroup
        set max_members = 0
        where org_id = org_id_in;
        for channel_sub in channel_subs loop
            update rhnPrivateChannelFamily
            set max_members = max_members + channel_sub.max_members
            where org_id = 1
              and channel_family_id = channel_sub.channel_family_id;
        end loop;
        update rhnPrivateChannelFamily
        set max_members = 0
        where org_id = org_id_in;
    end remove_org_entitlements;
	function entitlement_grants_service (
	    entitlement_in in varchar2,
	    service_level_in in varchar2
	) return number	is
	begin
		if service_level_in = 'provisioning' then
			if entitlement_in = 'provisioning_entitled' then
				return 1;
			else
				return 0;
			end if;
		elsif service_level_in = 'management' then
			if entitlement_in = 'enterprise_entitled' then
				return 1;
			else
				return 0;
			end if;
		elsif service_level_in = 'monitoring' then
			if entitlement_in = 'monitoring_entitled' then
				return 1;
			end if;
		elsif service_level_in = 'updates' then
			return 1;
		else
			return 0;
		end if;
	end entitlement_grants_service;
	function lookup_entitlement_group (
		org_id_in in number,
		type_label_in in varchar2 := 'sw_mgr_entitled'
	) return number is
		cursor server_groups is
			select	sg.id				server_group_id
			from	rhnServerGroup		sg,
					rhnServerGroupType	sgt
			where	sgt.label = type_label_in
				and sgt.id = sg.group_type
				and sg.org_id = org_id_in;
	begin
		for sg in server_groups loop
			return sg.server_group_id;
		end loop;
		return rhn_entitlements.create_entitlement_group(
				org_id_in,
				type_label_in
			);
	end lookup_entitlement_group;
	function create_entitlement_group (
		org_id_in in number,
		type_label_in in varchar2 := 'sw_mgr_entitled'
	) return number is
		sg_id_val number;
	begin
		select	rhn_server_group_id_seq.nextval
		into	sg_id_val
		from	dual;
		insert into rhnServerGroup (
				id, name, description, max_members, current_members,
				group_type, org_id
			) (
				select	sg_id_val, sgt.label, sgt.label,
						0, 0, sgt.id, org_id_in
				from	rhnServerGroupType sgt
				where	sgt.label = type_label_in
			);
		return sg_id_val;
	end create_entitlement_group;
   function can_entitle_server (
        server_id_in in number,
        type_label_in in varchar2 )
   return number is
      cursor addon_servergroups (base_label_in in varchar2,
                                 addon_label_in in varchar2) is
         select
            addon_id
         from
            rhnSGTypeBaseAddonCompat
         where base_id = lookup_sg_type (base_label_in)
           and addon_id = lookup_sg_type (addon_label_in);
      previous_ent        rhn_entitlements.ents_array;
      is_base_in          char   := 'N';
      is_base_current     char   := 'N';
      i                   number := 0;
      sgid                number := 0;
   begin
      previous_ent := rhn_entitlements.ents_array();
      previous_ent := rhn_entitlements.get_server_entitlement(server_id_in);
      select distinct is_base
      into is_base_in
      from rhnServerGroupType
      where label = type_label_in;
      if previous_ent.count = 0 then
         if (is_base_in = 'Y' and rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid)) then
            return 1;
         else
            return 0;
         end if;
      elsif is_base_in = 'Y' then
         return 0;
      else
         is_base_current := 'N';
         i := 0;
         while is_base_current = 'N' and i <= previous_ent.count
         loop
            i := i + 1;
            select is_base
            into is_base_current
            from rhnServerGroupType
            where label = previous_ent(i);
         end loop;
         if is_base_current  = 'N' then
            return 0;
         end if;
         for addon_servergroup in addon_servergroups  (previous_ent(i), type_label_in) loop
            if rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid) then
               return 1;
            else
               return 0;
            end if;
         end loop;
      end if;
      return 0;
   end can_entitle_server;
   function can_switch_base (
      server_id_in   in    integer,
      type_label_in  in    varchar2
   ) return number is
      type_label_in_is_base   char(1);
      sgid                    number;
   begin
      begin
         select is_base into type_label_in_is_base
         from rhnServerGroupType
         where label = type_label_in;
      exception
         when no_data_found then
            rhn_exception.raise_exception ( 'invalid_entitlement' );
      end;
      if type_label_in_is_base = 'N' then
         rhn_exception.raise_exception ( 'invalid_entitlement' );
      elsif rhn_entitlements.find_compatible_sg ( server_id_in,
                                                  type_label_in, sgid ) then
         return 1;
      else
         return 0;
      end if;
   end can_switch_base;
   function find_compatible_sg (
      server_id_in    in   number,
      type_label_in   in   varchar2,
      sgid_out        out  number
   ) return boolean is
      cursor servergroups is
         select sg.id            id
           from rhnServerGroupType             sgt,
                rhnServerGroup                 sg,
                rhnServer                     s,
                rhnServerServerGroupArchCompat ssgac
          where s.id = server_id_in
            and s.org_id = sg.org_id
            and sgt.label = type_label_in
            and sg.group_type = sgt.id
            and ssgac.server_group_type = sgt.id
            and ssgac.server_arch_id = s.server_arch_id
            and not exists (
                     select 1
                      from rhnServerGroupMembers sgm
                     where sgm.server_group_id = sg.id
                       and sgm.server_id = s.id);
   begin
      for servergroup in servergroups loop
         sgid_out := servergroup.id;
         return true;
      end loop;
      sgid_out := 0;
      return false;
   end find_compatible_sg;
	procedure entitle_server (
		server_id_in in number,
		type_label_in in varchar2 := 'sw_mgr_entitled'
	) is
      sgid  number := 0;
      is_virt number := 0;
	begin
          begin
          select 1 into is_virt
            from rhnServerEntitlementView
           where server_id = server_id_in
             and label in ('virtualization_host', 'virtualization_host_platform');
	  exception
            when no_data_found then
              is_virt := 0;
          end;
      if is_virt = 0 and (type_label_in = 'virtualization_host' or
                          type_label_in = 'virtualization_host_platform') then
        is_virt := 1;
      end if;
      if rhn_entitlements.can_entitle_server(server_id_in,
                                             type_label_in) = 1 then
         if rhn_entitlements.find_compatible_sg (server_id_in,
                                                 type_label_in, sgid) then
            insert into rhnServerHistory ( id, server_id, summary, details )
            values ( rhn_event_id_seq.nextval, server_id_in,
                     'added system entitlement ',
                      case type_label_in
                       when 'enterprise_entitled' then 'Management'
                       when 'sw_mgr_entitled' then 'Update'
                       when 'provisioning_entitled' then 'Provisioning'
                       when 'monitoring_entitled' then 'Monitoring'
                       when 'virtualization_host' then 'Virtualization'
                       when 'virtualization_host_platform' then
                            'Virtualization Platform' end  );
            rhn_server.insert_into_servergroup (server_id_in, sgid);
            if is_virt = 1 then
              rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
            end if;
         else
            rhn_exception.raise_exception ('no_available_server_group');
         end if;
      else
         rhn_exception.raise_exception ('invalid_entitlement');
      end if;
   end entitle_server;
	procedure remove_server_entitlement (
		server_id_in in number,
		type_label_in in varchar2 := 'sw_mgr_entitled',
        repoll_virt_guests in number := 1
	) is
		group_id number;
      type_is_base char;
      is_virt number := 0;
	begin
      begin
        begin
          select 1 into is_virt
            from rhnServerEntitlementView
           where server_id = server_id_in
             and label in ('virtualization_host', 'virtualization_host_platform');
        exception
          when no_data_found then
            is_virt := 0;
        end;
		select	sg.id, sgt.is_base
  		into	group_id, type_is_base
  		from	rhnServerGroupType sgt,
   			rhnServerGroup sg,
  				rhnServerGroupMembers sgm,
  				rhnServer s
  		where	s.id = server_id_in
  			and s.id = sgm.server_id
  			and sgm.server_group_id = sg.id
  			and sg.org_id = s.org_id
  			and sgt.label = type_label_in
  			and sgt.id = sg.group_type;
      if ( type_is_base = 'Y' ) then
         unentitle_server ( server_id_in );
      else
         insert into rhnServerHistory ( id, server_id, summary, details )
         values ( rhn_event_id_seq.nextval, server_id_in,
                  'removed system entitlement ',
                   case type_label_in
                    when 'enterprise_entitled' then 'Management'
                    when 'sw_mgr_entitled' then 'Update'
                    when 'provisioning_entitled' then 'Provisioning'
                    when 'monitoring_entitled' then 'Monitoring'
                    when 'virtualization_host' then 'Virtualization'
                    when 'virtualization_host_platform' then
                         'Virtualization Platforrm' end  );
         rhn_server.delete_from_servergroup(server_id_in, group_id);
         if type_label_in = 'monitoring_entitled' then
           DELETE
             FROM state_change
            WHERE o_id IN (SELECT probe_id
                             FROM rhn_check_probe
                            WHERE host_id = server_id_in);
           DELETE
             FROM time_series
            WHERE SUBSTR(o_id, INSTR(o_id, '-') + 1,
                        (INSTR(o_id, '-', INSTR(o_id, '-') + 1) - INSTR(o_id, '-')) - 1)
              IN (SELECT probe_id
                    FROM rhn_check_probe
                   WHERE host_id = server_id_in);
           DELETE
             FROM rhn_probe
            WHERE recid IN (SELECT probe_id
                              FROM rhn_check_probe
                             WHERE host_id = server_id_in);
         end if;
         if is_virt = 1 and repoll_virt_guests = 1 then
           rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
         end if;
      end if;
  		exception
  		when no_data_found then
  				rhn_exception.raise_exception('invalid_server_group_member');
      end;
 	end remove_server_entitlement;
   procedure unentitle_server (server_id_in in number) is
      cursor servergroups is
         select distinct sgt.label, sg.id server_group_id
         from  rhnServerGroupType sgt,
               rhnServerGroup sg,
               rhnServer s,
               rhnServerGroupMembers sgm
         where s.id = server_id_in
            and s.org_id = sg.org_id
            and sg.group_type = sgt.id
            and sgm.server_group_id = sg.id
            and sgm.server_id = s.id;
     is_virt number := 0;
   begin
      begin
        select 1 into is_virt
          from rhnServerEntitlementView
         where server_id = server_id_in
           and label in ('virtualization_host', 'virtualization_host_platform');
      exception
        when no_data_found then
          is_virt := 0;
      end;
      for servergroup in servergroups loop
         insert into rhnServerHistory ( id, server_id, summary, details )
         values ( rhn_event_id_seq.nextval, server_id_in,
                  'removed system entitlement ',
                   case servergroup.label
                    when 'enterprise_entitled' then 'Management'
                    when 'sw_mgr_entitled' then 'Update'
                    when 'provisioning_entitled' then 'Provisioning'
                    when 'monitoring_entitled' then 'Monitoring'
                    when 'virtualization_host' then 'Virtualization'
                    when 'virtualization_host_platform' then
                         'Virtualization Platform' end  );
         rhn_server.delete_from_servergroup(server_id_in,
                                            servergroup.server_group_id );
      end loop;
      if is_virt = 1 then
        rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
      end if;
   end unentitle_server;
    procedure repoll_virt_guest_entitlements(server_id_in in number)
    is
        cursor families is
            select distinct cfs.channel_family_id
            from
                rhnChannelFamilyServers cfs,
                rhnVirtualInstance vi
            where
                vi.host_system_id = server_id_in
                and vi.virtual_system_id = cfs.server_id;
        cursor group_types is
            select distinct sg.group_type, sgt.label
            from
                rhnServerGroupType sgt,
                rhnServerGroup sg,
                rhnServerGroupMembers sgm,
                rhnVirtualInstance vi
            where
                vi.host_system_id = server_id_in
                and vi.virtual_system_id = sgm.server_id
                and sgm.server_group_id = sg.id
                and sg.group_type = sgt.id;
        cursor virt_servers_cfam(family_id_in in number, quantity_in in number)  is
            select virtual_system_id
            from (
                select rownum, vi.virtual_system_id
                from
                    rhnChannelFamilyMembers cfm,
                    rhnServerChannel sc,
                    rhnVirtualInstance vi
                where
                    vi.host_system_id = server_id_in
                    and vi.virtual_system_id = sc.server_id
                    and sc.channel_id = cfm.channel_id
                    and cfm.channel_family_id = family_id_in
                order by sc.modified desc
                )
            where rownum <= quantity_in;
        cursor virt_servers_sgt(group_type_in in number, quantity_in in number)  is
            select virtual_system_id
            from (
                select rownum, vi.virtual_system_id
                from
                    rhnServerGroup sg,
                    rhnServerGroupMembers sgm,
                    rhnVirtualInstance vi
                where
                    vi.host_system_id = server_id_in
                    and vi.virtual_system_id = sgm.server_id
                    and sgm.server_group_id = sg.id
                    and sg.group_type = group_type_in
                order by sgm.modified desc
                )
            where rownum <= quantity_in;
        org_id_val number;
        max_members_val number;
        current_members_calc number;
        sg_id number;
    begin
        select org_id
        into org_id_val
        from rhnServer
        where id = server_id_in;
        for family in families loop
            current_members_calc :=
                rhn_channel.channel_family_current_members(family.channel_family_id,
                                                           org_id_val); -- fixed transposed args
            select max_members
            into max_members_val
            from rhnPrivateChannelFamily
            where channel_family_id = family.channel_family_id
            and org_id = org_id_val;
            if current_members_calc > max_members_val then
                for virt_server in virt_servers_cfam(family.channel_family_id,
                                current_members_calc - max_members_val) loop
                    rhn_channel.unsubscribe_server_from_family(
                                virt_server.virtual_system_id,
                                family.channel_family_id);
                end loop;
                if current_members_calc > max_members_val then
                    set_family_count(org_id_val,
                                     family.channel_family_id,
                                     max_members_val);
                end if;
           end if;
            rhn_channel.update_family_counts(family.channel_family_id,
                                             org_id_val);
        end loop;
        for a_group_type in group_types loop
          select max_members, id
            into max_members_val, sg_id
            from rhnServerGroup
            where group_type = a_group_type.group_type
            and org_id = org_id_val;
	  select count(sep.server_id) into current_members_calc
            from rhnServerEntitlementPhysical sep
           where sep.server_group_id = sg_id
             and sep.server_group_type_id = a_group_type.group_type;
          if current_members_calc > max_members_val then
            for virt_server in virt_servers_sgt(a_group_type.group_type,
                                                current_members_calc - max_members_val) loop
              rhn_entitlements.remove_server_entitlement(virt_server.virtual_system_id, a_group_type.label);
              current_members_calc := current_members_calc - 1;
            end loop;
          end if;
          update rhnServerGroup set current_members = current_members_calc
           where org_id = org_id_val
             and group_type = a_group_type.group_type;
        end loop;
    end repoll_virt_guest_entitlements;
	function get_server_entitlement (
		server_id_in in number
	) return ents_array is
		cursor server_groups is
			select	sgt.label
			from	rhnServerGroupType		sgt,
					rhnServerGroup			sg,
					rhnServerGroupMembers	sgm
			where	1=1
				and sgm.server_id = server_id_in
				and sg.id = sgm.server_group_id
				and sgt.id = sg.group_type
				and sgt.label in (
					'sw_mgr_entitled','enterprise_entitled',
					'provisioning_entitled', 'nonlinux_entitled',
					'monitoring_entitled', 'virtualization_host',
                                        'virtualization_host_platform'
					);
         ent_array ents_array;
	begin
      ent_array := ents_array();
		for sg in server_groups loop
         ent_array.extend;
         ent_array(ent_array.count) := sg.label;
		end loop;
		return ent_array;
	end get_server_entitlement;
	procedure modify_org_service (
		org_id_in in number,
		service_label_in in varchar2,
		enable_in in char
	) is
		type roles_v is varray(10) of rhnUserGroupType.label%TYPE;
		roles_to_process roles_v;
		cursor roles(role_label_in in varchar2) is
			select	label, id
			from	rhnUserGroupType
			where	label = role_label_in;
		cursor org_roles(role_label_in in varchar2) is
			select	1
			from	rhnUserGroup ug,
					rhnUserGroupType ugt
			where	ugt.label = role_label_in
				and ug.org_id = org_id_in
				and ugt.id = ug.group_type;
		type ents_v is varray(10) of rhnOrgEntitlementType.label%TYPE;
		ents_to_process ents_v;
		cursor ents(ent_label_in in varchar2) is
			select	label, id
			from	rhnOrgEntitlementType
			where	label = ent_label_in;
		cursor org_ents(ent_label_in in varchar2) is
			select	1
			from	rhnOrgEntitlements oe,
					rhnOrgEntitlementType oet
			where	oet.label = ent_label_in
				and oe.org_id = org_id_in
				and oet.id = oe.entitlement_id;
		create_row char(1);
	begin
		ents_to_process := ents_v();
		roles_to_process := roles_v();
		if service_label_in = 'enterprise' or
           service_label_in = 'management' then
			ents_to_process.extend;
			ents_to_process(ents_to_process.count) := 'sw_mgr_enterprise';
			roles_to_process.extend;
			roles_to_process(roles_to_process.count) := 'org_admin';
			roles_to_process.extend;
			roles_to_process(roles_to_process.count) := 'system_group_admin';
			roles_to_process.extend;
			roles_to_process(roles_to_process.count) := 'activation_key_admin';
			roles_to_process.extend;
			roles_to_process(roles_to_process.count) := 'org_applicant';
		elsif service_label_in = 'provisioning' then
			ents_to_process.extend;
			ents_to_process(ents_to_process.count) := 'rhn_provisioning';
			roles_to_process.extend;
			roles_to_process(roles_to_process.count) := 'system_group_admin';
			roles_to_process.extend;
			roles_to_process(roles_to_process.count) := 'activation_key_admin';
			roles_to_process.extend;
			roles_to_process(roles_to_process.count) := 'config_admin';
			if enable_in = 'Y' then
				ents_to_process.extend;
				ents_to_process(ents_to_process.count) := 'sw_mgr_enterprise';
			end if;
		elsif service_label_in = 'monitoring' then
			ents_to_process.extend;
			ents_to_process(ents_to_process.count) := 'rhn_monitor';
			roles_to_process.extend;
			roles_to_process(roles_to_process.count) := 'monitoring_admin';
		elsif service_label_in = 'virtualization' then
			ents_to_process.extend;
			ents_to_process(ents_to_process.count) := 'rhn_virtualization';
			roles_to_process.extend;
			roles_to_process(roles_to_process.count) := 'config_admin';
        elsif service_label_in = 'virtualization_platform' then
			ents_to_process.extend;
			ents_to_process(ents_to_process.count) := 'rhn_virtualization_platform';
			roles_to_process.extend;
			roles_to_process(roles_to_process.count) := 'config_admin';
	elsif service_label_in = 'nonlinux' then
			ents_to_process.extend;
			ents_to_process(ents_to_process.count) := 'rhn_nonlinux';
			roles_to_process.extend;
			roles_to_process(roles_to_process.count) := 'config_admin';
		end if;
		if enable_in = 'Y' then
			for i in 1..ents_to_process.count loop
				for ent in ents(ents_to_process(i)) loop
					create_row := 'Y';
					for oe in org_ents(ent.label) loop
						create_row := 'N';
					end loop;
					if create_row = 'Y' then
						insert into rhnOrgEntitlements(org_id, entitlement_id)
							values (org_id_in, ent.id);
					end if;
				end loop;
			end loop;
			for i in 1..roles_to_process.count loop
				for role in roles(roles_to_process(i)) loop
					create_row := 'Y';
					for o_r in org_roles(role.label) loop
						create_row := 'N';
					end loop;
					if create_row = 'Y' then
						insert into rhnUserGroup(
								id, name, description, current_members,
								group_type, org_id
							) (
								select	rhn_user_group_id_seq.nextval,
										ugt.name || 's',
										ugt.name || 's for Org ' ||
											o.name || ' ('|| o.id ||')',
										0, ugt.id, o.id
								from	rhnUserGroupType ugt,
										web_customer o
								where	o.id = org_id_in
									and ugt.id = role.id
							);
					end if;
				end loop;
			end loop;
		else
			for i in 1..ents_to_process.count loop
				for ent in ents(ents_to_process(i)) loop
					delete from rhnOrgEntitlements
					 where org_id = org_id_in
					   and entitlement_id = ent.id;
				end loop;
			end loop;
		end if;
	end modify_org_service;
	procedure set_customer_enterprise (
		customer_id_in in number
	) is
	begin
		modify_org_service(customer_id_in, 'enterprise', 'Y');
	end set_customer_enterprise;
	procedure set_customer_provisioning (
		customer_id_in in number
	) is
	begin
		modify_org_service(customer_id_in, 'provisioning', 'Y');
	end set_customer_provisioning;
	procedure set_customer_monitoring (
		customer_id_in in number
	) is
	begin
		modify_org_service(customer_id_in, 'monitoring', 'Y');
	end set_customer_monitoring;
	procedure set_customer_nonlinux (
		customer_id_in in number
	) is
	begin
		modify_org_service(customer_id_in, 'nonlinux', 'Y');
	end set_customer_nonlinux;
	procedure unset_customer_enterprise (
		customer_id_in in number
	) is
	begin
		modify_org_service(customer_id_in, 'enterprise', 'N');
	end unset_customer_enterprise;
	procedure unset_customer_provisioning (
		customer_id_in in number
	) is
	begin
		modify_org_service(customer_id_in, 'provisioning', 'N');
	end unset_customer_provisioning;
	procedure unset_customer_monitoring (
		customer_id_in in number
	) is
	begin
		modify_org_service(customer_id_in, 'monitoring', 'N');
	end unset_customer_monitoring;
	procedure unset_customer_nonlinux (
		customer_id_in in number
	) is
	begin
		modify_org_service(customer_id_in, 'nonlinux', 'N');
	end unset_customer_nonlinux;
	procedure prune_group (
		group_id_in in number,
		type_in in char,
		quantity_in in number
	) is
		cursor usergroups is
			select	user_id, user_group_id, ugt.label
			from	rhnUserGroupType	ugt,
					rhnUserGroup		ug,
					rhnUserGroupMembers	ugm
			where	1=1
				and ugm.user_group_id = group_id_in
				and ugm.user_id in (
					select	user_id
					from	(
						select	rownum row_number,
								user_id,
								time
						from	(
							select	user_id,
									modified time
							from	rhnUserGroupMembers
							where	user_group_id = group_id_in
							order by time asc
						)
					)
					where	row_number > quantity_in
				)
				and ugm.user_group_id = ug.id
				and ug.group_type = ugt.id;
        cursor servergroups is
           select  server_id, server_group_id, sgt.id group_type_id, sgt.label
            from    rhnServerGroupType              sgt,
                            rhnServerGroup                  sg,
                            rhnServerGroupMembers   sgm
            where   1=1
                    and sgm.server_group_id = group_id_in
                    and sgm.server_id in (
                            select  server_id
                            from    (
                                    select  rownum row_number,
                                                    server_id,
                                                    time
                                    from    (
                                            select  sep.server_id,
                                                    sep.modified time
                                            from
                                                rhnServerEntitlementPhysical sep
                                            where
                                                sep.server_group_id = group_id_in
                                            order by time asc
                                    )
                            )
                            where   row_number > quantity_in
                    )
                    and sgm.server_group_id = sg.id
                    and sg.group_type = sgt.id;
      type_is_base char;
	begin
		if type_in = 'U' then
			update		rhnUserGroup
				set		max_members = quantity_in
				where	id = group_id_in;
			for ug in usergroups loop
				rhn_user.remove_from_usergroup(ug.user_id, ug.user_group_id);
			end loop;
		elsif type_in = 'S' then
			update		rhnServerGroup
				set		max_members = quantity_in
				where	id = group_id_in;
			for sg in servergroups loop
				remove_server_entitlement(sg.server_id, sg.label);
            select is_base
            into type_is_base
            from rhnServerGroupType sgt
            where sgt.id = sg.group_type_id;
            if ( type_is_base = 'Y' ) then
				   rhn_channel.clear_subscriptions(sg.server_id);
            end if;
			end loop;
		end if;
	end prune_group;
    procedure assign_system_entitlement(
        group_label_in in varchar2,
        from_org_id_in in number,
        to_org_id_in in number,
        quantity_in in number
    )
    is
        prev_ent_count number;
	to_org_prev_ent_count number;
        new_ent_count number;
	new_quantity number;
        group_type number;
    begin
        begin
            select max_members
            into prev_ent_count
            from rhnServerGroupType sgt,
                 rhnServerGroup sg
            where sg.org_id = from_org_id_in
              and sg.group_type = sgt.id
              and sgt.label = group_label_in;
        exception
            when NO_DATA_FOUND then
                rhn_exception.raise_exception(
                              'not_enough_entitlements_in_base_org');
        end;
        begin
            select max_members
            into to_org_prev_ent_count
            from rhnServerGroupType sgt,
                 rhnServerGroup sg
            where sg.org_id = to_org_id_in
              and sg.group_type = sgt.id
              and sgt.label = group_label_in;
        exception
            when NO_DATA_FOUND then
                to_org_prev_ent_count := 0;
        end;
        begin
            select id
            into group_type
            from rhnServerGroupType
            where label = group_label_in;
        exception
            when NO_DATA_FOUND then
                rhn_exception.raise_exception(
                              'invalid_server_group');
        end;
        new_ent_count := prev_ent_count - quantity_in;
        if prev_ent_count > new_ent_count then
            new_quantity := to_org_prev_ent_count + quantity_in;
        end if;
        if new_ent_count < 0 then
            rhn_exception.raise_exception(
                          'not_enough_entitlements_in_base_org');
        end if;
        rhn_entitlements.set_group_count(from_org_id_in,
                                         'S',
                                         group_type,
                                         new_ent_count);
        rhn_entitlements.set_group_count(to_org_id_in,
                                         'S',
                                         group_type,
                                         new_quantity);
        if group_label_in = 'enterprise_entitled' then
            if new_quantity > 0 then
                set_customer_enterprise(to_org_id_in);
            else
                unset_customer_enterprise(to_org_id_in);
            end if;
        end if;
        if group_label_in = 'provisioning_entitled' then
            if new_quantity > 0 then
                set_customer_provisioning(to_org_id_in);
            else
                unset_customer_provisioning(to_org_id_in);
            end if;
        end if;
        if group_label_in = 'monitoring_entitled' then
            if new_quantity > 0 then
                set_customer_monitoring(to_org_id_in);
            else
                unset_customer_monitoring(to_org_id_in);
            end if;
        end if;
    end assign_system_entitlement;
    procedure assign_channel_entitlement(
        channel_family_label_in in varchar2,
        from_org_id_in in number,
        to_org_id_in in number,
        quantity_in in number
    )
    is
        prev_ent_count number;
        new_ent_count number;
	to_org_prev_ent_count number;
	new_quantity number;
        cfam_id       number;
    begin
        begin
            select max_members
            into prev_ent_count
            from rhnChannelFamily cf,
                 rhnPrivateChannelFamily pcf
            where pcf.org_id = from_org_id_in
              and pcf.channel_family_id = cf.id
              and cf.label = channel_family_label_in;
        exception
            when NO_DATA_FOUND then
                rhn_exception.raise_exception(
                              'not_enough_entitlements_in_base_org');
        end;
        begin
            select max_members
            into to_org_prev_ent_count
            from rhnChannelFamily cf,
                 rhnPrivateChannelFamily pcf
            where pcf.org_id = to_org_id_in
              and pcf.channel_family_id = cf.id
              and cf.label = channel_family_label_in;
        exception
            when NO_DATA_FOUND then
                to_org_prev_ent_count := 0;
        end;
        begin
            select id
            into cfam_id
            from rhnChannelFamily
            where label = channel_family_label_in;
        exception
            when NO_DATA_FOUND then
                rhn_exception.raise_exception(
                              'invalid_channel_family');
        end;
        new_ent_count := prev_ent_count - quantity_in;
	if prev_ent_count > new_ent_count then
            new_quantity := to_org_prev_ent_count + quantity_in;
	end if;
        if new_ent_count < 0 then
            rhn_exception.raise_exception(
                          'not_enough_entitlements_in_base_org');
        end if;
        rhn_entitlements.set_family_count(from_org_id_in,
                                          cfam_id,
                                          new_ent_count);
        rhn_entitlements.set_family_count(to_org_id_in,
                                          cfam_id,
                                          new_quantity);
    end assign_channel_entitlement;
    procedure activate_system_entitlement(
        org_id_in in number,
        group_label_in in varchar2,
        quantity_in in number
    )
    is
        prev_ent_count number;
        prev_ent_count_sum number;
        group_type number;
    begin
        begin
            select current_members
            into prev_ent_count
            from rhnServerGroupType sgt,
                 rhnServerGroup sg
            where sg.group_type = sgt.id
              and sgt.label = group_label_in
              and sg.org_id = org_id_in;
        exception
            when NO_DATA_FOUND then
                prev_ent_count := 0;
        end;
        begin
            select id
            into group_type
            from rhnServerGroupType
            where label = group_label_in;
        exception
            when NO_DATA_FOUND then
                rhn_exception.raise_exception(
                              'invalid_server_group');
        end;
        if quantity_in < prev_ent_count then
            rhn_exception.raise_exception(
                          'not_enough_entitlements_in_base_org');
        else
            rhn_entitlements.set_group_count(org_id_in,
                                             'S',
                                             group_type,
                                             quantity_in);
        end if;
    end activate_system_entitlement;
    procedure activate_channel_entitlement(
        org_id_in in number,
        channel_family_label_in in varchar2,
        quantity_in in number
    )
    is
        prev_ent_count number;
        prev_ent_count_sum number;
        cfam_id number;
    begin
        begin
            select current_members
            into prev_ent_count
            from rhnChannelFamily cf,
                 rhnPrivateChannelFamily pcf
            where pcf.org_id = org_id_in
              and pcf.channel_family_id = cf.id
              and cf.label = channel_family_label_in;
        exception
            when NO_DATA_FOUND then
                prev_ent_count := 0;
        end;
        begin
            select id
            into cfam_id
            from rhnChannelFamily
            where label = channel_family_label_in;
        exception
            when NO_DATA_FOUND then
                rhn_exception.raise_exception(
                              'invalid_channel_family');
        end;
        if quantity_in < prev_ent_count then
            rhn_exception.raise_exception(
                          'not_enough_entitlements_in_base_org');
        else
            rhn_entitlements.set_family_count(org_id_in,
                                              cfam_id,
                                              quantity_in);
        end if;
    end activate_channel_entitlement;
	procedure set_group_count (
		customer_id_in in number,
		type_in in char,
		group_type_in in number,
		quantity_in in number
	) is
		group_id number;
		quantity number;
	begin
		quantity := quantity_in;
		if quantity is not null and quantity < 0 then
			quantity := 0;
		end if;
		if type_in = 'U' then
			select	rug.id
			into	group_id
			from	rhnUserGroup rug
			where	1=1
				and rug.org_id = customer_id_in
				and rug.group_type = group_type_in;
		elsif type_in = 'S' then
			select	rsg.id
			into	group_id
			from	rhnServerGroup rsg
			where	1=1
				and rsg.org_id = customer_id_in
				and rsg.group_type = group_type_in;
		end if;
		rhn_entitlements.prune_group(
			group_id,
			type_in,
			quantity
		);
	exception
		when no_data_found then
			if type_in = 'U' then
				insert into rhnUserGroup (
						id, name, description, max_members, current_members,
						group_type, org_id, created, modified
					) (
						select	rhn_user_group_id_seq.nextval, name, name,
								quantity, 0, id, customer_id_in,
								sysdate, sysdate
						from	rhnUserGroupType
						where	id = group_type_in
				);
			elsif type_in = 'S' then
				insert into rhnServerGroup (
						id, name, description, max_members, current_members,
						group_type, org_id, created, modified
					) (
						select	rhn_server_group_id_seq.nextval, name, name,
								quantity, 0, id, customer_id_in,
								sysdate, sysdate
						from	rhnServerGroupType
						where	id = group_type_in
				);
			end if;
	end set_group_count;
	procedure prune_family (
		customer_id_in in number,
		channel_family_id_in in number,
		quantity_in in number
	) is
		cursor serverchannels is
			select	sc.server_id,
					sc.channel_id
			from	rhnServerChannel sc,
					rhnChannelFamilyMembers cfm
			where	1=1
				and cfm.channel_family_id = channel_family_id_in
				and cfm.channel_id = sc.channel_id
				and server_id in (
					select	server_id
					from	(
						select	server_id,
								time,
								rownum row_number
						from	(
							select	rs.id					server_id,
									rcfm.modified			time
							from
									rhnServerChannel		rsc,
									rhnChannelFamilyMembers	rcfm,
                                    rhnServer				rs
							where	1=1
								and rs.org_id = customer_id_in
								and rs.id = rsc.server_id
								and rsc.channel_id = rcfm.channel_id
								and rcfm.channel_family_id =
									channel_family_id_in
                                and exists (
                                    select 1
                                    from rhnChannelFamilyServerPhysical cfsp
                                    where cfsp.server_id = rs.id
                                    and cfsp.channel_family_id =
                                        channel_family_id_in
                                    )
							order by time asc
						)
					)
					where row_number > quantity_in
				);
	begin
		if customer_id_in is null then
			return;
		end if;
		update		rhnPrivateChannelFamily
			set		max_members = quantity_in
			where	1=1
				and org_id = customer_id_in
				and channel_family_id = channel_family_id_in;
		for sc in serverchannels loop
			rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1);
		end loop;
	end prune_family;
	procedure set_family_count (
		customer_id_in in number,
		channel_family_id_in in number,
		quantity_in in number
	) is
		cursor privperms is
			select	1
			from	rhnPrivateChannelFamily
			where	org_id = customer_id_in
				and channel_family_id = channel_family_id_in;
		cursor pubperms is
			select	o.id org_id
			from	web_customer o,
					rhnPublicChannelFamily pcf
			where	pcf.channel_family_id = channel_family_id_in;
		quantity number;
		done number := 0;
	begin
		quantity := quantity_in;
		if quantity is not null and quantity < 0 then
			quantity := 0;
		end if;
		if customer_id_in is not null then
			for perm in privperms loop
				rhn_entitlements.prune_family(
					customer_id_in,
					channel_family_id_in,
					quantity
				);
				update rhnPrivateChannelFamily
					set max_members = quantity
					where org_id = customer_id_in
						and channel_family_id = channel_family_id_in;
				return;
			end loop;
			insert into rhnPrivateChannelFamily (
					channel_family_id, org_id, max_members, current_members
				) values (
					channel_family_id_in, customer_id_in, quantity, 0
				);
			return;
		end if;
		for perm in pubperms loop
			if quantity = 0 then
				rhn_entitlements.prune_family(
					perm.org_id,
					channel_family_id_in,
					quantity
				);
				if done = 0 then
					delete from rhnPublicChannelFamily
						where channel_family_id = channel_family_id_in;
				end if;
			end if;
			done := 1;
		end loop;
		if done != 1 then
			insert into rhnPublicChannelFamily (
					channel_family_id
				) values (
					channel_family_id_in
				);
		end if;
	end set_family_count;
	procedure entitle_last_modified_servers (
		customer_id_in in number,
		type_label_in in varchar2,
		quantity_in in number
	) is
		cursor servers(cid_in in number, quant_in in number) is
			select	server_id
			from	(
						select	rownum row_number,
								server_id
						from	(
									select	rs.id server_iD
									from	rhnServer rs
									where	1=1
										and rs.org_id = cid_in
										and not exists (
											select	1
											from	rhnServerGroup sg,
													rhnServerGroupMembers rsgm
											where	rsgm.server_id = rs.id
												and rsgm.server_group_id = sg.id
												and sg.group_type is not null
										)
                                        and not exists (
                                            select 1
                                            from rhnVirtualInstance vi
                                            where vi.virtual_system_id =
                                                  rs.id
                                        )                                                                           order by modified desc
								)
					)
			where	row_number <= quant_in;
	begin
		for server in servers(customer_id_in, quantity_in) loop
			rhn_entitlements.entitle_server(server.server_id, type_label_in);
		end loop;
	end entitle_last_modified_servers;
	procedure prune_everything (
		customer_id_in in number
	) is
		cursor everything is
			select	sg.id					id,
					'S'						type,
					sg.max_members			quantity
			from	rhnServerGroup			sg
			where	sg.org_id = customer_id_in
			union
			select	ug.id					id,
					'U'						type,
					ug.max_members 			quantity
			from	rhnUserGroup			ug
			where	ug.org_id = customer_id_in
			union (
			select	cfp.channel_family_id	id,
					'C'						type,
					cfp.max_members			quantity
			from	rhnOrgChannelFamilyPermissions cfp
			where	cfp.org_id = customer_id_in
			union
			select	cfm.channel_family_id	id,
					'C'						type,
					0						quantity
			from	rhnChannelFamily		cf,
					rhnChannelFamilyMembers	cfm,
					rhnServerChannel		sc,
					rhnServer				s
			where	s.org_id = customer_id_in
				and s.id = sc.server_id
				and sc.channel_id = cfm.channel_id
				and cfm.channel_family_id = cf.id
				and cf.org_id is not null
				and cf.org_id != customer_id_in
				and not exists (
					select	1
					from	rhnOrgChannelFamilyPermissions cfp
					where	cfp.org_id = customer_id_in
						and cfp.channel_family_id = cfm.channel_family_id
					)
			);
	begin
		for one in everything loop
			if one.type in ('U','S') then
				prune_group(one.id, one.type, one.quantity);
			else
				prune_family(customer_id_in, one.id, one.quantity);
			end if;
		end loop;
	end prune_everything;
	procedure subscribe_newest_servers (
		customer_id_in in number
	) is
		cursor servers(cid_in in number) is
			select	s.id
			from	rhnServer			s
			where	1=1
				and s.org_id = cid_in
				and not exists (
						select 1
						from	rhnChannel			c,
								rhnServerChannel	sc
						where	sc.server_id = s.id
							and sc.channel_id = c.id
							and c.parent_channel is null
					)
                and not exists (
                        select 1
                        from rhnVirtualInstance vi
                        where vi.virtual_system_id = s.id
                    )
			order by s.modified desc;
		channel_id number;
	begin
		for server in servers(customer_id_in) loop
			channel_id := rhn_channel.guess_server_base(server.id);
			if channel_id is not null then
				begin
					rhn_channel.subscribe_server(server.id, channel_id);
					commit;
				exception
					when others then
						null;
				end;
			end if;
		end loop;
	end subscribe_newest_servers;
end rhn_entitlements;
 
/
