Expense Detail Report

Expenses

Employee expense report detail — each expense line by type, amount, project, and policy status — so finance can review spend by employee, organization, and category and spot policy violations before reimbursement.

Sample build of the Expense Detail Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

Bu Name

Globex Holdings

Currency

USD

Emname

Globex Holdings

Expense Type

Standard

Gl End Date

2026-02-28

Gl Start Date

2026-02-28

6,420

Expense lines

$2.31M

Total claimed

146

Policy flags

Employee	Expense Report	Expense Type	Date	Amount	Project	Policy Status
Sample	Sample	Standard	2026-04-30	$1,240,500.00	Sample	Open
—	—	Corporate	2026-03-31	$842,150.75	—	Posted
Sample	Sample	Standard	2026-02-28	$96,400.00	Sample	Validated
—	—	Default	2026-01-31	$1,005,233.10	—	Open
Sample	Sample	Standard	2025-12-31	$58,720.40	Sample	Paid
Sample	Sample	Standard	2026-04-30	$1,240,500.00	Sample	Open

AI Analyst · active

reading

The report reads EXM_EXPENSES with expense types and the submitting organization, rolling claimed amounts up by employee and category.

flag

146 lines tripped a policy rule — most are missing-receipt flags above the receipt threshold, concentrated in one category.

root cause & next step

When one category drives most flags, the receipt threshold or the rule is likely miscalibrated; fix it and the genuine violations stand out.

Illustrative data. The live interactive version — drill-through, filters, export, and the AI Analyst — runs on your warehouse. See it live →

This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.

Data sources

How it interconnects: this data set reads the physical tables above. Those same tables surface in OTBI as subject areas and in BICC as PVOs — three lenses on one source. Open any table to trace its subject areas and View Objects.

SQL data set · genericized · parameterized

Show / hide SQL

SELECT
    INVOICE_NUM,
    RECEIPT_DATE, 
    EXPENSE_TYPE,
    receipt_amount,
    JUSTIFICATION,
    MERCHANT_NAME,
	TAXPAYER_ID,
	RECEIPT_NUMBER,
    ITEMIZATION_EXPENSE_TYPE,
    ITEMIZATION_EXPENSE_AMOUNT,
    EMPLOYEE_NAME,
    LOCATION,
    ATTENDEE_TYPE,
    ATTENDEE_NAME,
    TITLE,
    INVOICE_DATE,
    ACCOUNTING_DATE,
    STATUS,
    EMPLOYEE_ID
FROM
(
    SELECT
        EER.expense_report_num AS INVOICE_NUM,
        EER.expense_report_date AS RECEIPT_DATE, 
        EET.name AS EXPENSE_TYPE,
        EE.receipt_amount,
        EE.JUSTIFICATION,
        EE.MERCHANT_NAME,
		EE.MERCHANT_TAXPAYER_ID TAXPAYER_ID,
		EE.MERCHANT_DOCUMENT_NUMBER RECEIPT_NUMBER,
        NULL AS ITEMIZATION_EXPENSE_TYPE,
        NULL AS ITEMIZATION_EXPENSE_AMOUNT,
        PPNFV.FIRST_NAME || ' ' || PPNFV.LAST_NAME AS EMPLOYEE_NAME,
        --PLDFV.location_name AS LOCATION,
		EE.location AS LOCATION,
        EEA.ATTENDEE_TYPE,
        EEA.name AS ATTENDEE_NAME,
        EEA.TITLE,
        AIA.INVOICE_DATE AS INVOICE_DATE,
        AIA.GL_DATE AS ACCOUNTING_DATE,
        FLV.meaning AS STATUS,
        PAPF.person_number AS EMPLOYEE_ID
    FROM 
        exm_expenses EE
        INNER JOIN exm_expense_types EET ON EE.expense_type_id = EET.expense_type_id
        INNER JOIN exm_expense_reports EER ON EE.expense_report_id = EER.expense_report_id
        INNER JOIN per_person_names_f_v PPNFV ON EE.person_id = PPNFV.person_id
        INNER JOIN hr_all_organization_units_f HAOUF ON EER.org_id = HAOUF.organization_id
        INNER JOIN hr_organization_units_f_tl HOUFTL ON HAOUF.organization_id = HOUFTL.organization_id
        LEFT JOIN per_location_details_f_vl PLDFV ON HAOUF.location_id = PLDFV.location_id
        LEFT JOIN exm_expense_attendees EEA ON EE.expense_id = EEA.expense_id
        INNER JOIN ap_invoices_all AIA ON EER.expense_report_num = AIA.invoice_num
        INNER JOIN fnd_lookup_values FLV ON AIA.PAYMENT_STATUS_FLAG = FLV.lookup_code 
            AND FLV.lookup_type = 'INVOICE PAYMENT STATUS'
        INNER JOIN per_all_people_f PAPF ON EE.person_id = PAPF.person_id
        CROSS JOIN (SELECT SYSDATE AS cur_date FROM dual) dt
    WHERE
        dt.cur_date BETWEEN PPNFV.effective_start_date AND PPNFV.effective_end_date
        AND dt.cur_date BETWEEN HAOUF.effective_start_date AND HAOUF.effective_end_date
        AND dt.cur_date BETWEEN HOUFTL.effective_start_date AND HOUFTL.effective_end_date
        AND dt.cur_date BETWEEN PLDFV.effective_start_date AND PLDFV.effective_end_date
        AND dt.cur_date BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date
        AND HOUFTL.LANGUAGE = USERENV('LANG')
        AND FLV.language = USERENV('LANG')
        AND EE.ITEMIZATION_PARENT_EXPENSE_ID IS NULL 
        AND AIA.APPROVAL_STATUS = 'APPROVED'
		AND ap_invoices_pkg.get_posting_status(AIA.invoice_id) ='Y'
        AND (HOUFTL.name IN (:P_BU_NAME) OR 'All' IN (:P_BU_NAME || 'All'))
        AND (EET.name IN (:P_EXPENSE_TYPE) OR 'All' IN (:P_EXPENSE_TYPE || 'All'))
        AND (EE.MERCHANT_NAME IN (:P_MERCHANT_NAME) OR 'All' IN (:P_MERCHANT_NAME || 'All'))
        AND (PPNFV.PERSON_ID IN (:P_EMP_NAME) OR 'All' IN (:P_EMP_NAME || 'All'))
        AND (EE.receipt_currency_code IN (:P_CURRENCY) OR 'All' IN (:P_CURRENCY || 'All'))
        AND TRUNC(AIA.GL_DATE) BETWEEN trunc(:P_GL_START_DATE) AND trunc(:P_GL_END_DATE)

    UNION

    SELECT
        EER.expense_report_num AS INVOICE_NUM,
        EER.expense_report_date AS RECEIPT_DATE, 
        CASE 
            WHEN EE.ITEMIZATION_PARENT_EXPENSE_ID IS NOT NULL THEN 
                (SELECT EETT.name 
                 FROM exm_expense_types EETT
                 INNER JOIN exm_expenses EEE ON EEE.expense_type_id = EETT.expense_type_id
                 INNER JOIN exm_expense_reports EERR ON EERR.expense_report_id = EER.expense_report_id
                 WHERE EEE.ITEMIZATION_PARENT_EXPENSE_ID = -1 
                   AND EEE.expense_report_id = EERR.expense_report_id)
            ELSE EET.name 
        END AS EXPENSE_TYPE,
        EEER.receipt_amount,
        EE.JUSTIFICATION,
        EE.MERCHANT_NAME,
		EE.MERCHANT_TAXPAYER_ID TAXPAYER_ID,
		EE.MERCHANT_DOCUMENT_NUMBER RECEIPT_NUMBER,
        CASE 
            WHEN EE.ITEMIZATION_PARENT_EXPENSE_ID <> -1 THEN EET.name
        END AS ITEMIZATION_EXPENSE_TYPE,
        CASE 
            WHEN EE.ITEMIZATION_PARENT_EXPENSE_ID <> -1 THEN EE.receipt_amount
        END AS ITEMIZATION_EXPENSE_AMOUNT,
        PPNFV.FIRST_NAME || ' ' || PPNFV.LAST_NAME AS EMPLOYEE_NAME,
        --PLDFV.location_name AS LOCATION,
        EE.location AS LOCATION,
        EEA.ATTENDEE_TYPE,
        EEA.name AS ATTENDEE_NAME,
        EEA.TITLE,
        AIA.INVOICE_DATE AS INVOICE_DATE,
        AIA.GL_DATE AS ACCOUNTING_DATE,
        FLV.meaning AS STATUS,
        PAPF.person_number AS EMPLOYEE_ID
    FROM 
        exm_expenses EE
        INNER JOIN exm_expense_types EET ON EE.expense_type_id = EET.expense_type_id
        INNER JOIN exm_expense_reports EER ON EE.expense_report_id = EER.expense_report_id
        INNER JOIN per_person_names_f_v PPNFV ON EE.person_id = PPNFV.person_id
        INNER JOIN hr_all_organization_units_f HAOUF ON EER.org_id = HAOUF.organization_id
        INNER JOIN hr_organization_units_f_tl HOUFTL ON HAOUF.organization_id = HOUFTL.organization_id
        LEFT JOIN per_location_details_f_vl PLDFV ON HAOUF.location_id = PLDFV.location_id
        LEFT JOIN exm_expense_attendees EEA ON EE.expense_id = EEA.expense_id
        INNER JOIN ap_invoices_all AIA ON EER.expense_report_num = AIA.invoice_num
        INNER JOIN fnd_lookup_values FLV ON AIA.PAYMENT_STATUS_FLAG = FLV.lookup_code 
            AND FLV.lookup_type = 'INVOICE PAYMENT STATUS'
        INNER JOIN per_all_people_f PAPF ON EE.person_id = PAPF.person_id
        CROSS JOIN (SELECT SYSDATE AS cur_date FROM dual) dt
        INNER JOIN (
            SELECT
                EER1.expense_report_num,
                EET1.name AS EXPENSE_TYPE,
                SUM(EE1.receipt_amount) AS receipt_amount
            FROM 
                exm_expenses EE1
                INNER JOIN exm_expense_types EET1 ON EE1.expense_type_id = EET1.expense_type_id
                INNER JOIN exm_expense_reports EER1 ON EE1.expense_report_id = EER1.expense_report_id
            WHERE
                EE1.ITEMIZATION_PARENT_EXPENSE_ID = -1
            GROUP BY
                EER1.expense_report_num,
                EET1.name
        ) EEER ON EER.expense_report_num = EEER.expense_report_num
    WHERE
        EE.ITEMIZATION_PARENT_EXPENSE_ID IS NOT NULL 
        AND EE.ITEMIZATION_PARENT_EXPENSE_ID <> -1
        AND FLV.language = USERENV('LANG')
        AND dt.cur_date BETWEEN PPNFV.effective_start_date AND PPNFV.effective_end_date
        AND dt.cur_date BETWEEN HAOUF.effective_start_date AND HAOUF.effective_end_date
        AND dt.cur_date BETWEEN HOUFTL.effective_start_date AND HOUFTL.effective_end_date
        AND dt.cur_date BETWEEN PLDFV.effective_start_date AND PLDFV.effective_end_date
        AND dt.cur_date BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date
        AND HOUFTL.LANGUAGE = USERENV('LANG')
        AND AIA.APPROVAL_STATUS = 'APPROVED'
		AND ap_invoices_pkg.get_posting_status(AIA.invoice_id) ='Y'
        AND (HOUFTL.name IN (:P_BU_NAME) OR 'All' IN (:P_BU_NAME || 'All'))
        AND (EET.name IN (:P_EXPENSE_TYPE) OR 'All' IN (:P_EXPENSE_TYPE || 'All'))
        AND (EE.MERCHANT_NAME IN (:P_MERCHANT_NAME) OR 'All' IN (:P_MERCHANT_NAME || 'All'))
        AND (PPNFV.PERSON_ID IN (:P_EMP_NAME) OR 'All' IN (:P_EMP_NAME || 'All'))
        AND (EE.receipt_currency_code IN (:P_CURRENCY) OR 'All' IN (:P_CURRENCY || 'All'))
        AND AIA.GL_DATE BETWEEN trunc(:P_GL_START_DATE) AND trunc(:P_GL_END_DATE)
)
ORDER BY
    EMPLOYEE_NAME,
    INVOICE_NUM,
    EXPENSE_TYPE,
    INVOICE_DATE

:P_BU_NAME :P_CURRENCY :P_EMP_NAME :P_EXPENSE_TYPE :P_GL_END_DATE :P_GL_START_DATE :P_MERCHANT_NAME

The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.

●— fact → dimension join

Element	Type	Definition
EXM_EXPENSE_TYPES	dimension	dimension
EXM_EXPENSE_REPORTS	dimension	dimension
PER_PERSON_NAMES_F_V	dimension	dimension
HR_ALL_ORGANIZATION_UNITS_F	dimension	dimension
Amount	measure	measure

Runs on your cloud warehouse — Snowflake, BigQuery, Redshift, or Synapse on AWS, Google Cloud, Azure, or any provider. Reconciled to the source control total — 0% variance by design. You own the code, the model, and the data.

How the data gets here: a BICC bulk extract of the source tables above, on the same pattern for every report. See the extraction pattern & data flow →

See the complete model

How this report's fact and dimensions fit the full picture, via conformed keys.

Expenses data model →Enterprise model →

Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.

Table	Reporting columns	Subject areas
EXM_EXPENSES	36	1
EXM_EXPENSE_TYPES	1	2
EXM_EXPENSE_REPORTS	16	1
PER_PERSON_NAMES_F_V	124	169
HR_ALL_ORGANIZATION_UNITS_F	18	156
HR_ORGANIZATION_UNITS_F_TL	13	172
PER_LOCATION_DETAILS_F_VL	47	71
EXM_EXPENSE_ATTENDEES	No page in this catalog’s data dictionary yet
AP_INVOICES_ALL	63	15
FND_LOOKUP_VALUES	No page in this catalog’s data dictionary yet
PER_ALL_PEOPLE_F	105	26

Reporting columns = fields the report selects that are exposed as analytics attributes; subject areas = the OTBI subject areas the table appears in. Setup and configuration tables (master data, ledger and book setup, lookups) are referenced by the report's joins but aren't exposed as analytics columns or subject areas — that's expected, not a gap.