Oracle DB 12.1.0.2
Hello Team,
kindly advise if I can combine these subqueries into 1 subquery?
SELECT
AP.INVOICE_ID,
(SELECT user_name
FROM apps.fnd_user
WHERE user_id = (AP.CREATED_BY))
created_by,
(SELECT user_name
FROM apps.fnd_user
WHERE user_id = AP.LAST_UPDATED_BY)
last_updated_by,
ROUND ((NVL (ap.EXCHANGE_RATE, 1) * ap.invoice_amount), 2)
FUNCTIONAL_INVOICE_AMOUNT,
ROUND ((NVL (ap.EXCHANGE_RATE, 1) * ap.amount_paid), 2)
FUNCTIONAL_AMOUNT_PAID,
(SELECT user_name
FROM apps.fnd_user
WHERE user_id = AP.CANCELLED_BY)
cancelled_by,
from apps.ap_invoices_all AP
--new version
select A.INVOICE_ID,
ROUND ((NVL (ap.EXCHANGE_RATE, 1) * ap.invoice_amount), 2)
FUNCTIONAL_INVOICE_AMOUNT,
ROUND ((NVL (ap.EXCHANGE_RATE, 1) * ap.amount_paid), 2)
FUNCTIONAL_AMOUNT_PAID,
fu.user_name as created_by,
fu.user_name as last_updated_by,
fu.user_name as cancelled_by from
apps.ap_invoices_all A
join apps.fnd_user fu on (fu.user_id = A.CREATED_BY
and fu.user_id = A.LAST_UPDATED_BY)
and fu.user_id = A.CANCELLED_BY)
;