Oracle EBS R12 – Query to View Consolidated Invoice Details with Individual Transactions

SELECT

    hp.party_name                  AS customer_name,

    aca.account_number             AS customer_account,

    hci.cons_billing_number        AS consolidated_bill_number,

    hci.issue_date                 AS bill_issue_date,

    rct.trx_number                 AS invoice_number,

    cit.transaction_type,

    cit.amount_original,

    cit.tax_original

FROM

    ar_cons_inv_all        hci,

    ar_cons_inv_trx_all    cit,

    hz_cust_accounts       aca,

    hz_parties             hp,

    ra_customer_trx_all    rct

WHERE

    hci.cons_inv_id          = cit.cons_inv_id

    AND hci.customer_id      = aca.cust_account_id

    AND aca.party_id         = hp.party_id

    AND rct.customer_trx_id  = cit.customer_trx_id

    -- AND hci.status        = 'ACCEPTED'        -- Uncomment if needed

    AND rct.trx_number       = '31380885'       -- Change as required

    AND hci.issue_date      >= TRUNC(SYSDATE) - 30

ORDER BY

    hci.issue_date DESC;