Search Results cancelled_date




Overview
Explore in AI Chat

Overview

APPS.FIIBV_AP_IVTY_INV_LCV is a reporting and integration view that exposes a flat, denormalized projection of Oracle Payables invoice header data. The name follows Oracle's FII (Financial Intelligence/Informatica integration) naming convention, where the trailing LCV denotes a logical canonical view used by the Oracle Financial Services data extraction layer, and IVTY refers to the invoice entity. The view is scoped to the ETRM/Business Intelligence publication model and is generally not intended for direct use by application forms or concurrent programs; rather, it feeds downstream data warehouse loads, extract-transform-load (ETL) mappings, and cross-source reconciliation reports operating on the AP_INVOICES_ALL table.

The object is delivered for Oracle EBS 12.1.1 and 12.2.2 and is owned by the APPS schema. Because it is a view rather than a table, it performs no physical storage of its own; all data is materialized at query time from the referenced base table, with a small number of synthetic columns appended to satisfy the integration contract.

Underlying Base Objects

The ETRM metadata lists no explicitly documented base objects besides those visible in the view text. The view is defined over a single base table, AP_INVOICES_ALL, aliased as ai. This is consistent with the standard Payables data model, in which AP_INVOICES_ALL holds both active and cancelled invoice rows (as opposed to AP_INVOICES, which historically filtered cancelled records).

The view also references the PL/SQL function EDW_INSTANCE.GET_CODE, which returns the E-Business Suite instance identifier. This function is called once, and the result is truncated to 40 bytes via SUBSTRB before being exposed as both the INSTANCE column and part of the composite INV_PK primary-key surrogate. No other lookup tables, partitions, or joined objects are documented.

Key Columns

  • INV_PK — A concatenated surrogate key composed of INVOICE_ID, ORG_ID, and the truncated instance code, formatted as invoice_id-org_id-instance. This provides globally unique identification across multiple EBS instances consolidated into a single warehouse.
  • INV_TYPE_FK — The invoice type lookup code (for example, STANDARD, PREPAYMENT, CREDIT, DEBIT), serving as a foreign key into the invoice type dimension.
  • INV_ID — The numeric INVOICE_ID rendered as a character string through TO_CHAR.
  • NAME / INV_NAME — Both map to INVOICE_NUM, the human-readable supplier invoice number as entered by the user; the duplication supports different naming expectations in downstream targets.
  • INSTANCE — The truncated EBS instance code from EDW_INSTANCE.GET_CODE.
  • INV_SOURCE — The SOURCE column of AP_INVOICES_ALL, indicating how the invoice was created (for example, manual entry or an automated import).
  • CANCELLED_DATE — The date on which the invoice was cancelled; NULL for invoices still active. This is the column referenced by the search term cancelled_date and is the primary filter used to segregate active from cancelled documents in the warehouse.
  • LAST_UPDATE_DATE / CREATION_DATE — Standard audit columns supporting incremental ("delta") extract logic.
  • DELETION_DATE / INV_DP — Placeholder NULL columns reserved for downstream conventions. INV_DP is typically used to carry a "deleted" indicator payload, left empty here.
  • The four string literals appended at the end of the select list identify the source mapping lineage (AP_INVOICES, AP_GOV_DETAIL_INVOICES, JG_AP_INVOICES).

Common Use Cases and Queries

The principal use case is feeding AP invoice header facts into a financial data mart. Because CANCELLED_DATE is surfaced explicitly, the view supports straightforward lifecycle analysis of invoice cancellation patterns and exclusion of reversed documents from active-payables reporting.

SELECT inv_pk, inv_id, name, cancelled_date
FROM   apps.fiibv_ap_ivty_inv_lcv
WHERE  cancelled_date IS NOT NULL;

SELECT inv_type_fk,
       COUNT(*) AS cancelled_count
FROM   apps.fiibv_ap_ivty_inv_lcv
WHERE  cancelled_date BETWEEN :from_date AND :to_date
GROUP  BY inv_type_fk;

Incremental extract queries should constrain on LAST_UPDATE_DATE to minimize full-table scans against AP_INVOICES_ALL. Because the view is an integration artifact, direct DML is impossible; consumers must treat it as read-only and route any write-back through the base Payables APIs.