Have you made any progress on this?

From inside the database, do you know where I can retrieve the middleware’s hostname?

For an opportunity, you should be able to write a query string as a URL:

'/main/ifsapplications/web/page/BusinessOpportunity/OpportunityDetailPage;$filter=%28OpportunityNo%20eq%20%27' || opportunity_no_ || '%27%29'

so for opportunity ID 123:

/main/ifsapplications/web/page/BusinessOpportunity/OpportunityDetailPage;$filter=%28OpportunityNo%20eq%20%27123%27%29

From inside the application, pointing back to itself, it would be a best practice to drop off the front of the URL and let the user’s web browser resolve the hostname and protocol implicitly.

For my current use, on the other hand, I want to write a whole URL and include it in the body of an email. I want it to dynamically pick the correct hostname based on whether it’s PROD or TEST.

Here's an example URL for customer order A123456:

https://ifs.example.com/main/ifsapplications/web/page/CustomerOrder/Form;$filter=%28OrderNo%20eq%20%27A123456%27%29

I found this very hacky workaround to retrieve the application URL from the database. (I tested this in 22R1.)

   SELECT REGEXP_REPLACE(value, '^(.*)(/redirect)$', '\1') AS url_base
     FROM iam_login_event_detail_tab
    WHERE parameter = 'redirect_uri'
      AND rowversion > (SELECT created FROM v$database)
 GROUP BY value
 ORDER BY COUNT(*) DESC
    FETCH FIRST 1 ROWS ONLY;