Results for “ap_xml_paymentlevel_v”

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

Overview

AP_XML_PAYMENTLEVEL_V is a Payables (AP) module reporting view in Oracle E-Business Suite 12.1.1 and 12.2.2. It is designed to expose payment-level detail in a denormalized form suitable for XML payment file generation, remittance advice extraction, and downstream reporting integrations. The view joins selected invoice check data with payment batch selection criteria and bank/branch reference information to present a flat, consumable result set. Its role is primarily integration-oriented: XML payment processing routines and external bank communication modules reference payment-level information through this structure to obtain check amounts, payee details, bank account attributes, and currency codes in a single query. The view is not documented as a base or seeded transactional entity; ETRM metadata notes it as "Not implemented in this database," indicating it is a query-only object whose definition is supplied rather than physically stored in all environments.

Underlying Base Objects

The view is defined over four Payables base tables:

• AP_SELECTED_INVOICE_CHECKS (ASIC) — Holds the payment-level records for selected invoices, including check number, vendor, amount, dates, and the OK_TO_PAY flag.
• AP_INVOICE_SELECTION_CRITERIA (AISC) — Stores payment batch selection criteria keyed by checkrun name, supplying the CHECKRUN_ID.
• AP_BANK_ACCOUNTS (ABA) — Provides internal bank account attributes such as account type, number, and branch identifier.
• AP_BANK_BRANCHES (ABB) — Supplies bank branch details including branch name, bank name, and address information.

Joins are established on EXTERNAL_BANK_ACCOUNT_ID = BANK_ACCOUNT_ID, matching CHECKRUN_NAME across the check and selection-criteria tables, and BANK_BRANCH_ID between the account and branch tables. A restrictive filter limits results to rows where OK_TO_PAY_FLAG is 'Y' or 'F', ensuring only approved or flagged payments are returned.

Key Columns

• CHECK_NUMBER, SELECTED_CHECK_ID, CHECKRUN_ID, CHECKRUN_NAME — Identify the payment and its batch context.
• VENDOR_ID, VENDOR_NAME — Payee identification.
• CHECK_AMOUNT, DUE_DATE, PAYMENT_DATE, CURRENCY_CODE — Financial and timing attributes.
• BANK_NUM, BANK_ACCOUNT_NUM, BANK_ACCOUNT_TYPE, BANK_NUMBER — Bank account identifiers and classification.
• BANK_BRANCH_ID, BANK_BRANCH_NAME, BANK_NAME, BANK_BRANCH_TYPE — Branch and institution detail.
• CUSTOMER_NUM — The searched attribute; a customer/supplier reference number carried on the selected check record, useful for matching remittance data to trading partners.
• ADDRESS_LINE1, CITY, STATE, ZIP, COUNTRY — Bank branch address used in payment formatting.

Common Use Cases and Queries

Typical usage includes generating payment-level XML extracts, auditing disbursements by bank account, and reconciling remittance advice against supplier records. A representative query filtering on the searched attribute would be:

• SELECT check_number, vendor_name, check_amount, customer_num, currency_code FROM ap_xml_paymentlevel_v WHERE customer_num = :p_customer_num;
• SELECT checkrun_name, bank_name, SUM(check_amount) FROM ap_xml_paymentlevel_v WHERE payment_date BETWEEN :p_from AND :p_to GROUP BY checkrun_name, bank_name;
• SELECT vendor_id, vendor_name, bank_account_num, bank_branch_name FROM ap_xml_paymentlevel_v WHERE check_number = :p_check_number;

Because the view restricts output to payable records and joins across batch, bank, and branch data, it eliminates the need for multi-table joins in payment reporting. Where the object is not materialized, DBAs should confirm availability of the referenced base tables and grants before deploying dependent reports.