Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to LinkExpense and Advance (Financials Query Report Development)

Accepted answer
25
Views
2
Comments
Kauan_Bohn
Kauan_Bohn Rank 5 - Community Champion
edited Jul 23, 2025 2:07PM in Oracle Transactional Business Intelligence

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.

image.png
Tagged:

Best Answer

  • Kauan_Bohn
    Kauan_Bohn Rank 5 - Community Champion
    Answer ✓

    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.

Answers