Results for “week_end_date”

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

Overview

AP_EXPENSE_REPORT_VIOLATIONS_V is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It is part of the Oracle Payables (AP) product family and belongs to the Oracle Internet Expenses / Payables expense reporting subsystem. The view presents all expense report policy violations together with the corresponding expense report lines that are either ready for payment or have already been paid. Its status is VALID and it is documented in the ETRM repository for 12.2.2.

The view is intended for reporting and integration scenarios where organizations need visibility into employee expense reports that breach corporate expense policies — for example, exceeding daily spending limits or missing a required cost center — while simultaneously exposing the payable or already-paid financial context of the affected expense line. Because it filters to reports in statuses that are manager-approved-and-payable, invoiced, or paid, it is useful for audit, reconciliation, and downstream payment analysis rather than for reviewing reports still in submission or approval.

Underlying Base Objects

The view is defined over the following documented base objects:

• AP_EXPENSE_REPORT_HEADERS_ALL — the expense report header, providing the employee, report header identifier, week end date, invoice number, source, workflow approval flag, and expense status.
• AP_EXPENSE_REPORT_LINES_ALL — the expense report distribution lines, supplying the line amount, currency, category, submitted amount, receipt amounts, and itemization parent relationship.
• AP_POL_VIOLATIONS_ALL — the policy violation repository, supplying violation number, violation type, violation date, allowable amount, and exceeded amount.
• AP_EXPENSE_REPORT_PARAMS_ALL and AP_EXPENSE_REPORTS_ALL — referenced in a NOT EXISTS subquery that excludes personal (non-payable) expense types from the result set.
• AP_WEB_OA_ACTIVE_PKG — a package invoked through AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE to derive the expense status when EXPENSE_STATUS_CODE is null.

Headers, lines, and violations are joined on REPORT_HEADER_ID, DISTRIBUTION_LINE_NUMBER, and ORG_ID, ensuring that a violation row is aligned with its exact expense distribution line for the same operating unit.

Key Columns

• EMPLOYEE_ID / PAID_ON_BEHALF_EMPLOYEE_ID — the expense submitter and any other employee the expenses were incurred on behalf of.
• WEEK_END_DATE / INVOICE_NUM / REPORT_HEADER_ID — report identification and the associated invoice when the report has been invoiced.
• AMOUNT / CURRENCY_CODE / SUBMITTED_AMOUNT / DAILY_AMOUNT / RECEIPT_CURRENCY_AMOUNT — expense line monetary values in both report and receipt currencies.
• DISTRIBUTION_LINE_NUMBER / REPORT_LINE_ID / ITEMIZATION_PARENT_ID / CATEGORY_CODE — line-level identifiers and expense category.
• VIOLATION_NUMBER / VIOLATION_TYPE / VIOLATION_DATE — the policy violation identity, with type such as DAILY_SUM_LIMIT or CC_REQUIRED.
• ALLOWABLE_AMOUNT — derived via DECODE; it returns 0 for DAILY_SUM_LIMIT and CC_REQUIRED, otherwise the stored allowable amount.
• VIOLATION_AMOUNT — derived via DECODE; it returns 0 when the violation type is null or CC_REQUIRED, otherwise the exceeded amount.

Common Use Cases and Queries

Typical uses include auditing expense policy breaches on paid or payable reports, feeding exception reports to internal audit, and reconciling violation amounts against expense amounts. Reports flagged as NONVALIDATEDWEBEXPENSE with a null workflow approval flag, reports sourced as BOTH PAY, personal expense types, and itemization parents equal to -1 are excluded by the view's predicates.

Example query listing violations for an employee:

• SELECT report_header_id, invoice_num, violation_number, violation_type, violation_amount, allowable_amount, amount, currency_code FROM ap_expense_report_violations_v WHERE employee_id = :p_employee_id ORDER BY week_end_date, report_header_id;

Example query aggregating exceeded amounts by violation type:

• SELECT violation_type, COUNT(*) violations, SUM(violation_amount) total_exceeded FROM ap_expense_report_violations_v GROUP BY violation_type ORDER BY 3 DESC;

These patterns make the view a convenient, single-source structure for expense policy compliance analysis without querying the underlying header, line, and violation tables separately.