Search Results withheld_amount_currency_code




Overview
Explore in AI Chat

Overview

POS_AP_INVOICES_V is a Purchasing (PO) module view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a consolidated, presentation-ready read of Oracle Payables invoice header and payment schedule information as it relates to purchase order activity, joining invoice data to vendor, vendor site, payment schedule, and operating unit organization records. The view is not a base transactional object; it is a reporting and integration construct intended for use in Oracle Forms-based Purchasing inquiry screens, OAF pages, and custom reports that need invoice data expressed in a user-facing format. A defining characteristic is that monetary columns are returned as formatted character strings rather than native numbers, produced through FND_CURRENCY.SAFE_GET_FORMAT_MASK using the invoice currency code with a 30-character mask. This makes the view convenient for display but unsuitable for arithmetic or aggregation without conversion.

Underlying Base Objects

The view is defined over five directly joined objects: AP_INVOICES_ALL, AP_PAYMENT_SCHEDULES_ALL, PO_VENDORS, PO_VENDOR_SITES_ALL, and HR_ALL_ORGANIZATION_UNITS_TL. The join path links invoice headers to payment schedules on INVOICE_ID, to vendor sites on VENDOR_SITE_ID, to vendors on VENDOR_ID, and to the operating unit organization on ORG_ID, filtered by the session language through USERENV('LANG'). Documented ETRM references also list AP_LOOKUP_CODES, FND_CURRENCY_CACHE, FND_GLOBAL, and the custom package POS_AP_INVOICES_PKG as dependencies. PO_VENDORS and PO_VENDOR_SITES_ALL are themselves views, so the lineage resolves ultimately to the Payables and Purchasing base tables beneath them. Two derived columns depend on PL/SQL: WITHHELD_AMOUNT_CURRENCY_CODE calls POS_AP_INVOICES_PKG.GET_AMOUNT_WITHHELD, PO_NUMBER calls POS_AP_INVOICES_PKG.GET_PO_NUMBER_LIST, and PAYMENT_NUMBER calls POS_AP_INVOICE_PAYMENTS_PKG.GET_PAID_BY_LIST. These package calls execute per row, which materially affects performance on large result sets.

Key Columns

  • INVOICE_ID, INVOICE_NUM, INVOICE_DATE — Invoice identifier, number, and invoice date from AP_INVOICES_ALL.
  • INVOICE_AMOUNT, GROSS_AMOUNT, AMOUNT_REMAINING — Formatted character representations of the invoice amount, schedule gross amount, and open balance.
  • DISCOUNT_AMOUNT_AVAILABLE, DISCOUNT_DATE — The early-payment discount still available on the schedule and its expiry date. DISCOUNT_AMOUNT_AVAILABLE is returned via TO_CHAR with the currency format mask.
  • WITHHELD_AMOUNT_CURRENCY_CODE — Withheld amount concatenated with the invoice currency code, derived from the custom package.
  • DUE_DATE — Payment schedule due date from AP_PAYMENT_SCHEDULES_ALL.
  • PO_NUMBER, PAYMENT_NUMBER — Package-derived listing of related purchase orders and the payment reference.
  • VENDOR_ID, VENDOR_SITE_ID, VENDOR_SITE_CODE — Supplier and supplier site identifiers.
  • INVOICE_TYPE, PAYMENT_STATUS — Present in the column list but populated as NULL in the view text.
  • ORG_ID, ORG_NAME, EMPLOYEE_ID — Operating unit and employee context.

Common Use Cases and Queries

Typical uses include supplier invoice inquiry from Purchasing, reconciliation of PO-linked invoices against payment schedules, and discount monitoring. A search on discount_amount_available is normally intended to surface invoices with a live early-payment discount. Because the column is character-formatted, filtering on it requires care; use the underlying AP_PAYMENT_SCHEDULES_ALL column for numeric predicates:

SELECT invoice_num, invoice_currency_code, discount_amount_available, discount_date
FROM apps.pos_ap_invoices_v
WHERE discount_amount_available IS NOT NULL
AND org_id = :p_org_id
ORDER BY discount_date;

To evaluate actual discount exposure, query the base schedule instead:

SELECT ai.invoice_num, aps.discount_amount_available, aps.discount_date
FROM ap_invoices_all ai, ap_payment_schedules_all aps
WHERE ai.invoice_id = aps.invoice_id
AND aps.discount_amount_available > 0;

Because of the per-row package calls and the language-restricted organization join, the view should be filtered by ORG_ID and constrained by invoice identifiers or dates wherever possible. Oracle Proprietary, Confidential Information.