BIP – Extract environment url dynamically

For many BIP reports, it is required to publish the environment url. One can easily hardcode it in SQL query, however, when P2T or T2T happens, the same url gets copied to target instance which is incorrect. The better way of doing it is to use below piece of code in custom SQL to extract environment url dynamically:

SELECT 'https://'||EXTERNAL_VIRTUAL_HOST INSTANCE_NAME
  FROM FUSION.ASK_DEPLOYED_DOMAINS
 WHERE DEPLOYED_DOMAIN_NAME = 'FADomain'