Description of problem:
Generate applicability can hide an errata to be applicable, in a specific case. The key problem is in `fetch_rpm_content_ids` method https://github.com/Katello/katello/blob/master/app/services/katello/applicability/applicable_content_helper.rb#L82 :

      def fetch_rpm_content_ids
        enabled_module_stream_ids = fetch_enabled_module_stream_ids

        ::Katello::Rpm.
          joins("INNER JOIN katello_repository_rpms ON
            katello_rpms.id = katello_repository_rpms.rpm_id").
          joins("INNER JOIN katello_installed_packages ON
            katello_rpms.name = katello_installed_packages.name AND
            katello_rpms.arch = katello_installed_packages.arch AND
            katello_rpms.evr > katello_installed_packages.evr AND
            katello_installed_packages.id in (#{newest_distinct_installed_packages_query})").
          joins("LEFT JOIN katello_module_stream_rpms ON
            katello_rpms.id = katello_module_stream_rpms.rpm_id").
          joins("INNER JOIN katello_host_installed_packages ON
            katello_installed_packages.id = katello_host_installed_packages.installed_package_id").
          where("katello_repository_rpms.repository_id in (:bound_library_repos)",
            :bound_library_repos => self.bound_library_instance_repos).
          where("katello_host_installed_packages.host_id = :content_facet_id",
            :content_facet_id => self.content_facet.host.id).
          where("(katello_module_stream_rpms.module_stream_id IS NULL AND
            katello_installed_packages.id NOT IN (:locked_modular_installed_packages)) OR
            (katello_module_stream_rpms.module_stream_id IN (:enabled_module_streams)
            AND katello_installed_packages.id IN (:locked_modular_installed_packages))",
            :enabled_module_streams => enabled_module_stream_ids,
            :locked_modular_installed_packages => locked_modular_installed_packages(enabled_module_stream_ids)).pluck(:id).uniq
      end

The latest "where" clause queries for ".. and the package is not in a module stream, OR it is in an enabled module stream".

Two bugs here:
1) the main one: ".. package is not in a module stream" is applied against all repositories, not against `bound_library_instance_repos`. As having `satellite-capsule` module in a Sat/Caps repo which contains `puppet-agent-7.27.0-1.el8sat.x86_64` package, this package is marked as "in a module stream". But it is not in a module stream available to the Host, so the package is ruled out, wrongly. See reproducer for more.

2) Theoretical bug: assume I enable the sat6/caps6 repo on a Host (say, on a Capsule), having older puppet-agent RPM installed there. And I disable the module stream (sic!).

So then "`puppet-agent-7.27.0-1 is in a module stream" is applied properly, as bound repos include the Sat6/Caps6 repo with that `satellite-capsule` module. BUT still, it is not in an enabled module stream, so it will be ruled out. Despite I can still install it from the other repo!

The support case is for the 1) scenario, with trivial below reproducer.

(the reproducer tests errata applicability, but the problem comes from applicable RPMs. Because

::Katello::Applicability::ApplicableContentHelper.new(self, ::Katello::Rpm, bound_repos).calculate_and_import

already fails in finding `puppet-agent-7.27.0-1` as applicable - and this data is used for subsequent errata applicabilty)
 

How reproducible:
100%

Is this issue a regression from an earlier version:
dunno, most probably not

Steps to Reproduce:

1. Have Satellite with synchronized `satellite-capsule-6.14-for-rhel-8-x86_64-rpms` and `satellite-client-6-for-rhel-8-x86_64-rpms` repos (among others)

2. Have a CV with just `satellite-client-6-for-rhel-8-x86_64-rpms` repo.

3. Assign the CV to a Host that has `puppet-agent-7.26.0-3.el8sat` installed.

4. That means, the Host can update the package to either `7.27.0-1.el8sat` or `7.28.0-1.el8sat`. And `dnf updateinfo --list` shows both `RHSA-2024:2011` and `RHSA-2024:2101` errata.

5. Check applicable errata in Satellite WebUI.

Actual behavior:
5. just `RHSA-2024:2101` is shown. `RHSA-2024:2011` is ignored.

Expected behavior:
Also `RHSA-2024:2011` is shown in WebUI as applicable.

Business Impact / Additional info:
???