Search Results ap_dbi_log




Overview
Explore in AI Chat

Overview

AP_DBI_LOG is a transaction log table owned by the AP (Payables) schema in Oracle E-Business Suite, present and valid in both release 12.1.1 and 12.2.2. Its documented purpose is to track operations performed against Payables transaction tables that are relevant to the Daily Business Intelligence (DBI) product. In practice, AP_DBI_LOG functions as a change-capture staging area: whenever rows are inserted, updated, or deleted in monitored Payables entities, a corresponding log record is written so that the DBI collection and refresh processes can identify which transactions require re-aggregation. This decouples the high-volume transactional workload from the analytical summarization performed by DBI, allowing incremental refresh rather than full reprocessing.

The ETRM metadata classifies AP_DBI_LOG heuristically as standalone under the Data Vault model. This should be read as a modeling suggestion rather than a normative definition: the table carries no dependent child tables within its documented FK structure, and its single foreign key points outward to an external partition/request mechanism. A standalone classification is consistent with the table's role as an append-oriented event log whose rows are consumed by downstream processes rather than participating in a normalized parent-child hierarchy. The table exposes 12 documented columns in the 12.2.2 physical schema.

Key Information Stored

The most significant columns in AP_DBI_LOG, drawn from the documented schema, are:

  • TABLE_NAME — identifies the Payables transaction table on which the logged operation occurred (for example, a source table whose change must propagate to DBI aggregates). This is the primary business-key candidate, since it scopes the event to a specific entity.
  • OPERATION_FLAG — encodes the nature of the captured change (typically insert, update, or delete), allowing the DBI refresh logic to apply the correct incremental action.
  • KEY_VALUE1 and KEY_VALUE2 — hold the primary or composite key values of the affected transaction row, providing the pointer back to the source record.
  • EXP_PROCESSED_FLAG — indicates whether the log entry has been consumed by the expense-related processing path.
  • PS_PROCESSED_FLAG — indicates whether the entry has been consumed by the Payables/payment-schedule processing path. Together with EXP_PROCESSED_FLAG, these flags drive work-queue semantics so entries are not reprocessed.
  • PARTITION_ID — the sole documented foreign key, referencing JTF_FM_PARTITION_X_REQUEST; it links the log entry to a partition or request context.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS WHO columns recording audit and concurrency context.

The metadata does not document a dedicated surrogate primary-key column; the WHO audit columns are conventional rather than identifying. In the absence of a documented unique index, TABLE_NAME combined with KEY_VALUE1/KEY_VALUE2 and the timestamp columns serves as the practical business-key candidate for tracing an event.

Common Use Cases and Queries

Typical usage centers on monitoring DBI backlog and diagnosing refresh gaps. A common pattern is to identify unprocessed entries by table:

  • SELECT table_name, operation_flag, COUNT(*) FROM ap.ap_dbi_log WHERE exp_processed_flag = 'N' OR ps_processed_flag = 'N' GROUP BY table_name, operation_flag;
  • Reconciling a specific transaction via WHERE key_value1 = :id to confirm that a change was captured and later consumed.
  • Measuring collection latency by comparing creation_date against the current time for entries whose processed flags remain unset.
  • Auditing operation distribution (operation_flag) to understand workload mix and identify excessive deletes on monitored tables.

These queries support DBI administrators validating that daily aggregation keeps pace with Payables activity.

Related Objects

The documented foreign key establishes the principal dependency:

  • JTF_FM_PARTITION_X_REQUEST — referenced by AP_DBI_LOG.PARTITION_ID; supplies partition/request context for logged entries.

Beyond the documented FK, AP_DBI_LOG logically relates to the Payables transaction tables named at runtime in TABLE_NAME (for example invoice, payment, and payment-schedule tables), to the DBI summary and fact objects that consume processed entries, and to the DBI collection programs scheduled to read unprocessed rows. These relationships are operational rather than enforced by declared constraints.