Results for “ap_expense_report_lines_all”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AP_EXPENSE_REPORT_LINES_ALL is the core transactional table in the Oracle Payables (AP) module that stores the individual expense line items belonging to employee expense reports. Each row represents a single expense item — an airfare, meal, mileage, hotel charge, or other reimbursable cost — that an employee submits for reimbursement. The table resides in the AP schema and is classified as VALID in the ETRM repository. Its grain is one row per expense line, and it is the detail-level companion to AP_EXPENSE_REPORT_HEADERS_ALL, which holds the report-level header. Together with the distribution table AP_EXP_REPORT_DISTS_ALL, these three objects form the backbone of the Internet Expenses and Payables expense reimbursement flow.

From a Data Vault modeling perspective, the mined foreign-key structure classifies this table as satellite-leaning: it is a wide, descriptive, history-bearing table whose rows depend on upstream hubs (report headers, projects, currencies, code combinations) rather than acting as a pure hub or a pure link. This classification is offered as a heuristic modeling suggestion derived from FK density and the presence of many descriptive attributes.

Key Information Stored

The table contains 163 documented columns. The most significant include:

• REPORT_LINE_ID — the surrogate primary key defined by AP_EXPENSE_REPORT_LINES_PK and enforced by the unique index AP_EXPENSE_REPORT_LINES_U1. This is the true unique identifier for a line.
• REPORT_HEADER_ID — foreign key linking the line to its parent expense report header in AP_EXPENSE_REPORT_HEADERS_ALL; the primary business-key candidate for grouping lines into a report.
• LINE_TYPE_LOOKUP_CODE — identifies the expense category/type.
• ITEM_DESCRIPTION and AMOUNT — the nature of the expense and its entered amount.
• CURRENCY_CODE, EXCHANGE_RATE, EXCHANGE_RATE_TYPE, EXCHANGE_DATE — currency and conversion details for multi-currency reimbursement.
• CODE_COMBINATION_ID — the accounting flexfield (charge account) associated with the line.
• SET_OF_BOOKS_ID — the ledger context for the line.
• PROJECT_ID, TASK_ID, EXPENDITURE_TYPE, EXPENDITURE_ORGANIZATION_ID, EXPENDITURE_ITEM_DATE, PA_QUANTITY — project accounting attributes enabling capitalization and costing.
• ORG_ID — the operating unit (multi-org segregation).
• VAT_CODE and tax-related flags such as AMOUNT_INCLUDES_TAX_FLAG.
• ADJUSTMENT_REASON and POLICY_SHORTPAY_FLAG — audit and policy-compliance fields.
• CREDIT_CARD_TRX_ID — links corporate card charges to the expense line.

Several columns are denormalized convenience fields (FLEX_CONCATENATED, PROJECT_NAME, TASK_NAME, AWARD_NUMBER) that mirror values fetched during entry.

Common Use Cases and Queries

The table is queried primarily for expense report auditing, reimbursement reporting, and project cost analysis. Typical patterns include:

• Listing all lines for a report: SELECT * FROM ap_expense_report_lines_all WHERE report_header_id = :header_id;
• Aggregating reimbursable totals by employee or period, joining headers and employees.
• Identifying lines not yet interfaced to Projects for costing using PA_INTERFACED_FLAG.
• Reconciling credit card transactions by joining CREDIT_CARD_TRX_ID to AP_CREDIT_CARD_TRXNS_ALL.
• Detecting policy exceptions via JUSTIFICATION_REQUIRED_FLAG, RECEIPT_REQUIRED_FLAG, and RECEIPT_MISSING_FLAG.

Because the table is large and ORG_ID-scoped, queries should always filter by org and header to avoid full-table scans, and reporting should be performed against the corresponding _ALL objects rather than any legacy non-multi-org variant.

Related Objects

• AP_EXPENSE_REPORT_HEADERS_ALL — parent header, joined via REPORT_HEADER_ID.
• AP_EXP_REPORT_DISTS_ALL — child distribution lines, joined via REPORT_LINE_ID.
• AP_INVOICES_ALL — linked via COMPANY_PREPAID_INVOICE_ID for prepaid lines.
• AP_CREDIT_CARD_TRXNS_ALL — linked via CREDIT_CARD_TRX_ID.
• GL_CODE_COMBINATIONS — accounting flexfield via CODE_COMBINATION_ID.
• PA_PROJECTS_ALL and PA_TASKS — project/task via PROJECT_ID and TASK_ID.
• FND_CURRENCIES — via CURRENCY_CODE and RECEIPT_CURRENCY_CODE.
• OIE_ATTENDEES_ALL, OIE_PDM_DAILY_BREAKUPS, OIE_PDM_DESTINATIONS — Internet Expenses detail tables referencing REPORT_LINE_ID.
• IGF_AW_AWARD_ALL — grant award linkage via AWARD_ID.

These relationships make AP_EXPENSE_REPORT_LINES_ALL the central detail object connecting Payables, Projects, Grants, and Internet Expenses processes in Oracle EBS 12.1.1 and 12.2.2.