Search Results rev_flag

Overview

BIL.BIL_BI_OPDTL_DENLOG_TMP is an interface table owned by the BIL (Billing) schema within Oracle E-Business Suite 12.1.1 and 12.2.2. The object is classified as VALID, resides in the APPS_TS_INTERFACE tablespace, and is marked "Oracle Internal Use Only," meaning Oracle Corporation does not support direct access except through standard Oracle Applications programs. Its name and structure indicate that it functions as a temporary staging area for denormalized opportunity, lead, and sales credit log data consumed by Billing's business intelligence and processing routines.

The table carries standard Oracle Applications interface-table characteristics: an APPS_TS_INTERFACE storage location, a PCT Free of 10, a SECURITY_GROUP_ID who-column, and LAST_UPDATE_DATE indexing that supports incremental processing. Two nonunique indexes exist: BIL_BI_OPDTL_DENLOG_TMP_N1 on (LEAD_ID, LEAD_LINE_ID, SALES_CREDIT_ID) and BIL_BI_OPDTL_DENLOG_TMP_N2 on (LAST_UPDATE_DATE).

From a Data Vault modeling perspective, the heuristic classification is standalone. Because the table combines lead, lead line, and sales credit identifiers plus their corresponding log identifiers, it could be modeled as a link connecting these business entities; however, no explicit hub or link enforcing structure is present, so the classification should be treated as a modeling suggestion rather than a documented constraint.

Key Information Stored

The twelve documented columns capture the denormalized operational context. The most significant are:

• LEAD_ID — Lead identifier; foreign key to AS_LEADS_ALL and the first column of the N1 index.
• LEAD_LOG_ID — Lead log identifier sourced from AS_LEADS_LOG, tying the row to a specific audited version.
• LEAD_LINE_ID — Lead line identifier; foreign key to AS_LEAD_LINES_ALL.
• LEAD_LINE_LOG_ID — Lead line log identifier from AS_LEAD_LINES_LOG.
• SALES_CREDIT_ID — Sales credit identifier associated with the lead or line.
• SALES_CREDIT_LOG_ID — Sales credit log identifier from AS_SALES_CREDITS_LOG.
• REV_FLAG — VARCHAR2(240) indicating whether the row is a reversal row. This is the column referenced by the "rev_flag" search.
• LAST_UPDATE_DATE — Last update date; indexed by N2 to drive incremental selection.
• SEQ_NO — Stores the row number, useful for ordering staged records.
• SECURITY_GROUP_ID — Standard who-column; foreign key to FND_SECURITY_GROUPS for data security partitioning.
• CREATION_DATE and CONVERSION_DATE — Documented as obsolete; retained for historical compatibility.

No unique index or single-column surrogate primary key is documented. Business-key candidates, based on the log-centric design, are the combination of LEAD_ID, LEAD_LINE_ID, SALES_CREDIT_ID together with their respective log identifiers.

Common Use Cases and Queries

Because this is an internal interface table, typical usage involves staging and reconciliation of lead/sales-credit log data for Billing processing. A common pattern filters reversal rows using REV_FLAG, or selects only rows updated after a prior high-water mark:

• Identify reversal records: SELECT LEAD_ID, LEAD_LINE_ID, SALES_CREDIT_ID, REV_FLAG FROM BIL.BIL_BI_OPDTL_DENLOG_TMP WHERE REV_FLAG IS NOT NULL;
• Incremental extraction: SELECT * FROM BIL.BIL_BI_OPDTL_DENLOG_TMP WHERE LAST_UPDATE_DATE >= :p_since;
• Security-scoped reporting: filter on SECURITY_GROUP_ID to honor FND security group isolation.
• Row sequencing during load reconciliation: order by SEQ_NO to reconstruct the staged order.

Reporting use cases center on auditing which lead and sales credit versions were processed and whether any were reversed, making REV_FLAG a frequent filter predicate in reconciliation queries.

Related Objects

The documentation lists foreign key relationships that define the table's integration points:

• AS_LEADS_ALL — joined on LEAD_ID = AS_LEADS_ALL.LEAD_ID.
• AS_LEAD_LINES_ALL — joined on LEAD_LINE_ID = AS_LEAD_LINES_ALL.LEAD_LINE_ID.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID.
• AS_LEADS_LOG, AS_LEAD_LINES_LOG, AS_SALES_CREDITS_LOG — source log tables referenced by the corresponding *_LOG_ID columns.
• APPS.BIL_BI_OPDTL_DENLOG_TMP — the APPS synonym/cover for this table, which is the only documented referencing object; BIL_BI_OPDTL_DENLOG_TMP does not reference any database object through the dependency report.