Search Results ap_expense_reports_pk

Overview

AP_EXPENSE_REPORTS_ALL is a Payables (AP) module table that stores descriptive information about expense report templates. In Oracle EBS 12.1.1 and 12.2.2, it functions as the defining entity for expense report configurations used by the Internet Expenses (OIE) and Payables expense functionality. Each row represents a logical expense report template, identified by a surrogate primary key EXPENSE_REPORT_ID, and is qualified by ORG_ID for multi-org security and by REPORT_TYPE for classification.

Per the ETRM metadata, the table is owned by the AP schema, is marked VALID, and contains 13 documented columns. The heuristic Data Vault classification is hub-leaning: EXPENSE_REPORT_ID behaves as a durable business key that links to dependent satellites, while much of the descriptive context and parameters reside in child tables such as AP_EXPENSE_REPORT_PARAMS_ALL. This classification should be treated as a modeling suggestion rather than a documented assertion.

Key Information Stored

The following are the most significant columns documented for this table:

• EXPENSE_REPORT_ID — the surrogate primary key, defined by the AP_EXPENSE_REPORTS_PK constraint; also part of the unique index AP_EXPENSE_REPORTS_U1.
• REPORT_TYPE — the classification of the expense report template; a business-key candidate appearing in the unique index AP_EXPENSE_REPORTS_U2.
• ORG_ID — the operating unit identifier supporting multi-org access; also part of AP_EXPENSE_REPORTS_U2.
• DEFAULT_PARAMETER_ID — foreign key to AP_EXPENSE_REPORT_PARAMS_ALL, identifying the default parameter configuration for the template.
• DESCRIPTION — textual description of the expense report template.
• INACTIVE_DATE — date on which the template is deactivated.
• WEB_ENABLED_FLAG — indicates whether the template is available for web/Internet Expenses entry.
• ZD_EDITION_NAME — editioning column used by EBS 12.2.2 online patching; included in both unique indexes (AP_EXPENSE_REPORTS_U1 and U2).
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard Who columns for audit and change tracking.

Common Use Cases and Queries

Typical usage includes validating active expense templates, resolving the parameters governing a template, and joining to headers for reporting on submitted expense reports.

• List active templates for an operating unit:

  SELECT expense_report_id, report_type, description
  FROM   ap_expense_reports_all
  WHERE  org_id = :org_id
  AND    inactive_date IS NULL;

• Retrieve the default parameters for a template:

  SELECT p.*
  FROM   ap_expense_report_params_all p,
         ap_expense_reports_all      r
  WHERE  r.default_parameter_id = p.default_parameter_id
  AND    r.expense_report_id    = :id;

• Audit expense reports by template:

  SELECT r.report_type, COUNT(*)
  FROM   ap_expense_report_headers_all h,
         ap_expense_reports_all       r
  WHERE  h.expense_report_id = r.expense_report_id
  GROUP BY r.report_type;

Related Objects

Relationship data documents the following significant dependencies:

• AP_EXPENSE_REPORT_PARAMS_ALL — child table joined on EXPENSE_REPORT_ID; also referenced by DEFAULT_PARAMETER_ID.
• AP_EXPENSE_REPORT_HEADERS_ALL — transaction headers joined on EXPENSE_REPORT_ID.
• AP_AUD_QUEUES — audit queue records linked by EXPENSE_REPORT_ID.
• AP_SYSTEM_PARAMETERS_ALL — system parameters referencing the default expense report ID.

Together these objects form the template, parameter, and transaction structure underpinning Payables expense reporting.