Oracle Analytics and AI

Kauan_Bohn

Hi Oracle Community,

I’m working on developing a query report in Oracle Cloud Financials and need assistance with linking an expense and an advance. I have an expense and an advance recorded in Oracle Cloud, but I’m unable to find a foreign key (FK) to establish this relationship in my query.

Kauan_Bohn

I found the response of my question:

SELECT
ai.invoice_id,
ai.invoice_num,
ai.reference_key1 AS expense_report_id,
--er.report_name,
er.expense_report_total AS expense_report_total,
ca.cash_advance_id,
ca.amount,
ca.status_code AS cash_advance_status,
cca.amount AS applied_amount,
cca.applied_date
FROM
ap_invoices_all ai
JOIN
exm_expense_reports er
ON ai.reference_key1 = er.expense_report_id
LEFT JOIN
exm_cash_adv_applications cca
ON er.expense_report_id = cca.expense_report_id
LEFT JOIN
exm_cash_advances ca
ON cca.cash_advance_id = ca.cash_advance_id
WHERE
ai.invoice_id IN ('XZY');

Hope that help's someone too.