Search Results employee_exp

Overview

APPS.AP_DOCUMENTS_PAYABLE is a reporting view in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 that consolidates payment-related document information from the Oracle Payables module. It is designed to present a unified representation of payable documents — invoices, expense reports, and payment transactions — in a format suitable for external reporting, integration, and downstream processing. The view surfaces check, invoice, and payment information, including currency, amount, discount, and withholding details. Its purpose is to decouple consumer queries from the underlying normalized Payables table structures, exposing a denormalized projection with consistent column semantics that can be consumed by reporting tools, custom interfaces, and integration endpoints without requiring direct joins across multiple base tables.

Underlying Base Objects

The view is defined in the APPS schema and draws from a substantial set of base objects. According to ETRM metadata for 12.2.2, these include:

• AP_CHECKS_ALL — source of payment/check header information such as check_id, check_date, checkrun_name, currency_code, payment_method_code, and bank account identifiers.
• AP_INVOICES_ALL — source of invoice attributes including invoice_id, invoice_num, invoice_date, invoice_type_lookup_code, invoice_currency_code, invoice_amount, and payment_reason details.
• AP_INVOICE_PAYMENTS_ALL — links invoices to specific payments via invoice_payment_id and amount.
• AP_PAYMENT_SCHEDULES_ALL — provides due_date and the various discount_date fields evaluated in the view's discount_date DECODE logic.
• AP_INVOICE_DISTRIBUTIONS_ALL — used in the scalar subquery to compute the AMOUNT_WITHHELD (withholding tax) for each invoice/payment combination.
• AP_INV_SELECTION_CRITERIA_ALL, AP_SELECTED_INVOICES_ALL — selection criteria and selected invoice tracking.
• AP_SUPPLIERS, AP_SUPPLIER_SITES_ALL, HZ_PARTY_SITES, CE_BANK_ACCT_USES_ALL — supplier, site, party, and bank account use information.
• AP_INVOICES_PKG and AP_PAYMENT_UTIL_PKG — PL/SQL packages invoked within the view text (AP_INVOICES_PKG.Get_Po_Number for PO_NUMBER and AP_PAYMENT_UTIL_PKG.Get_Interest_Rate for INTEREST_RATE).

Because these are synonyms resolving to the underlying AP and related tables, the view's behavior depends on the data and PL/SQL logic in those source objects.

Key Columns

The SELECT list exposes several important derived and passthrough columns:

• Document type resolution: The first column uses nvl(ai.pay_proc_trxn_type_code, decode(ai.invoice_type_lookup_code,'EXPENSE REPORT','EMPLOYEE_EXP','PAYABLES_DOC')), meaning the view explicitly identifies employee expense reports by emitting the value EMPLOYEE_EXP. This is the column a user searching for "employee_exp" would encounter when filtering on expense-report documents.
• Payment identifiers: check_id, invoice_id, payment_num, invoice_payment_id.
• Amount and currency: invoice_amount, amount (payment amount), invoice_currency_code, currency_code.
• Dates: check_date, invoice_date, anticipated_value_date, due_date, plus a computed discount_date derived from payment schedules and discount flags.
• Withholding: AMOUNT_WITHHELD computes the negative sum of AWT (withholding) distribution amounts associated with the invoice and payment.
• Supplier/party: party_id, party_site_id, vendor_site_id (nulled when negative), legal_entity_id, org_id.
• Remittance and delivery: remittance_message1–3, unique_remittance_identifier, uri_check_digit, delivery_channel_code.
• Miscellaneous: PO_NUMBER (from the package call), description, interest_rate, settlement_priority, payment_profile_id.

Common Use Cases and Queries

The view is typically queried for payable reporting, expense-report extraction, and payment reconciliation. Its fixed document-type tokens make it convenient for filtering by category.

• Retrieving employee expense report payments:
• SELECT invoice_num, invoice_date, amount, currency_code
FROM APPS.AP_DOCUMENTS_PAYABLE
WHERE invoice_type_lookup_code = 'EXPENSE REPORT';
• Reconciling payments by check:
• SELECT check_id, check_date, invoice_num, amount, amount_withheld
FROM APPS.AP_DOCUMENTS_PAYABLE
WHERE check_date >= :start_date;
• Identifying withholding on a payment:
• SELECT invoice_id, invoice_payment_id, amount_withheld
FROM APPS.AP_DOCUMENTS_PAYABLE
WHERE amount_withheld <> 0;

Because the view encapsulates complex joins and PL/SQL calls, query performance may be affected by the underlying package invocations (Get_Po_Number, Get_Interest_Rate) and the correlated subquery for AMOUNT_WITHHELD; appropriate indexes on the base AP tables should be verified. The view is read-only and should not be used for DML. It is intended to provide a stable, denormalized interface for Payables payment document data across the supported EBS 12.1.1 and 12.2.2 releases.