I am not sure how these API endpoints work (never used them myself) but you might want to take a look at: POST /api/hosts/bulk/applicable_errata POST /api/hosts/bulk/installable_errata
According to the API docs, these should do what you are looking for on a per-organization basis.
As with all content-related things, afaik there is no way to do this cross-organization, so you will have to iterate over all your organizations with these API calls.
API doc says you can use included[search] with a search value. If you use a search that matches all hosts (like organization = ) I would assume it should work.
btw, just in case you didn’t know already: You can find the API doc for your Foreman version and all your installed plugins at https://yourforeman.example.com/apidoc
There is for sure a way, though you will probably have to write that SQL statement from hand. All the information should be in the database somewhere, but I don’t know if some parts might be only stored in the pulp database.