Search Results cc_required

Overview

APPS.OIE_MGR_RPT_V is a reporting view in Oracle E-Business Suite (validated against 12.1.1 and 12.2.2) that consolidates manager-facing expense report data with policy violation detail. The view name anticipates the Oracle Internet Expenses (OIE) manager reporting layer: it returns one aggregated row per employee, expense report, report header, and distribution line, combining the functional-currency line amount, the allowable amount, the exceeded (violation) amount, and a count of policy violations. Its principal role is to feed manager review, approval, and audit reporting that must reconcile submitted expense amounts against corporate expense policy limits.

The view is defined in the APPS schema and is therefore accessible to standard EBS responsibilities and to custom concurrent programs, BI Publisher data templates, and OAF pages that reference APPS-owned objects. Because policy violations are resolved with an outer join on the distribution line, reports that have no violations still appear, with zero-valued violation metrics. This makes the view suitable both for exception reporting (violations only) and for complete manager summary reporting.

Underlying Base Objects

The documented referenced objects are AP_EXPENSE_REPORT_HEADERS, AP_EXPENSE_REPORT_LINES, AP_EXPENSE_REPORT_PARAMS, AP_POL_VIOLATIONS, AP_SYSTEM_PARAMETERS, and AP_EXPENSE_REPORTS (all accessed through APPS synonyms), joined to PER_PEOPLE_X and PER_ASSIGNMENTS_F for employee and supervisor identity. AP_WEB_OA_ACTIVE_PKG supplies the derived report status code, GL_CURRENCY_API provides currency conversion through CONVERT_AMOUNT_SQL, and HR_GENERAL, HR_PERSON_NAME, and HR_SECURITY support name formatting and row-level security. The header-to-line relationship is driven by REPORT_HEADER_ID, while the violation relationship is driven by the pairing of REPORT_HEADER_ID and DISTRIBUTION_LINE_NUMBER, joined with the (+) outer-join operator so that non-violating lines are preserved.

Key Columns

• EMPLOYEE_ID / FULL_NAME — the expense-reporting employee and display name from PER_PEOPLE_X.
• EXPENSE_REPORT_DATE — derived from WEEK_END_DATE on the report header.
• REPORT_HEADER_ID / DISTRIBUTION_LINE_NUMBER — the report and line identifiers that uniquely key the aggregation.
• CATEGORY_CODE — the expense category from AP_EXPENSE_REPORT_PARAMS.
• VIOLATION_TYPE — the policy violation classification, including the literal values DAILY_SUM_LIMIT and CC_REQUIRED.
• LINE_AMOUNT — the functional-currency line amount, converted where necessary using GL_CURRENCY_API.
• ALLOWABLE_AMOUNT — the sum of allowable amounts, explicitly zeroed for DAILY_SUM_LIMIT and CC_REQUIRED violations.
• VIOLATION_AMOUNT — the converted exceeded amount, zeroed for NULL violation types and CC_REQUIRED.
• NUMBER_OF_VIOLATIONS — a count of populated violation types.
• SUPERVISOR_ID — the supervisor from PER_ASSIGNMENTS_F, filtered to primary assignments of type C or E.

Common Use Cases and Queries

The view is typically queried to reproduce the manager approval report, to isolate violations by type, or to reconcile allowable versus submitted amounts. The CC_REQUIRED violation type is of particular interest to auditors, since the view deliberately excludes its amount from both the allowable and violation totals while still counting the violation.

A representative query listing violations by supervisor:

• SELECT full_name, expense_report_date, violation_type, line_amount, violation_amount FROM apps.oie_mgr_rpt_v WHERE supervisor_id = :p_supervisor_id AND violation_type IS NOT NULL ORDER BY expense_report_date, full_name;
• SELECT violation_type, SUM(violation_amount) total_exceeded, SUM(number_of_violations) violation_count FROM apps.oie_mgr_rpt_v GROUP BY violation_type;

Results reflect only reports whose derived status resolves to MGRPAYAPPR, INVOICED, or PAID, and that are not paid on behalf of another employee, so downstream reporting naturally aligns with the manager approval and payment lifecycle.