Search Results as_lead_lines_log_n2

Overview

The OSM.AS_LEAD_LINES_LOG table is a transactional audit table within the Oracle E-Business Suite 12.1.1 / 12.2.2 environment, owned by the OSM schema and registered in FND Design Data as AS.AS_LEAD_LINES_LOG. Its documented purpose is to hold the change history of opportunity lead lines — that is, each row captures a historical snapshot or delta of an opportunity line at a point in time, allowing users to trace how forecasts, products, offers, and interest assignments evolved. The table is stored in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, while all associated indexes reside in APPS_TS_TX_IDX.

From a heuristic Data Vault modeling perspective, this table is classified as standalone, meaning it does not act as a pure hub, link, or satellite but should be treated most naturally as a satellite attached to the lead line hub (via LEAD_LINE_ID) and, optionally, to the lead hub (via LEAD_ID). Its surrogate LOG_ID provides the grain of a single logged change event.

Key Information Stored

The table contains 23 documented columns. The most operationally significant are:

• LOG_ID – surrogate primary key, uniquely identified by the unique index AS_LEAD_LINES_LOG_U1. This is the business-key candidate that guarantees one row per logged event.
• LEAD_LINE_ID – foreign key to AS_LEAD_LINES_ALL, tying the log entry to the originating opportunity line.
• LEAD_ID – foreign key to AS_LEADS_ALL, linking the log to the parent opportunity.
• INTEREST_TYPE_ID – documented FK to AS_INTEREST_TYPES_B; though flagged obsolete in the comments, the referential constraint remains.
• FORECAST_DATE – the forecast date of the purchase line at the time of the change.
• ROLLING_FORECAST_FLAG – indicates the forecast date was defaulted from the close date on AS_LEADS_ALL.
• SOURCE_PROMOTION_ID and OFFER_ID – identifiers for the promotion and the Marketing offer that generated or influenced the line.
• PRODUCT_CATEGORY_ID and PRODUCT_CAT_SET_ID – the mapped product category and the catalog category set.
• ORGANIZATION_ID and INVENTORY_ITEM_ID – inventory organization and item references.
• LOG_MODE and ENDDAY_LOG_FLAG – control columns identifying the type of logging action (for example insert/update) and whether the record represents an end-of-day snapshot.
• ORG_ID – the operating unit that performed the transaction, supporting multi-org security.
• OBJECT_VERSION_NUMBER – optimistic locking / version attribute for the logged row.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – standard WHO audit columns.

Common Use Cases and Queries

The table is principally queried for opportunity pipeline change tracking, forecast accuracy analysis, and audit/compliance reporting. Typical patterns include:

• Retrieving the full change history of a single opportunity line:
  SELECT log_id, lead_line_id, forecast_date, log_mode, last_update_date FROM as_lead_lines_log WHERE lead_line_id = :p_line_id ORDER BY last_update_date;
• Reporting all logged activity for a parent lead across its lines:
  SELECT l.lead_id, l.lead_line_id, l.forecast_date, l.probability FROM as_lead_lines_log l WHERE l.lead_id = :p_lead_id;
• Identifying end-of-day snapshots to measure rolling forecast trends:
  SELECT * FROM as_lead_lines_log WHERE endday_log_flag = 'Y' AND org_id = :p_org;
• Tracking promotional impact by joining SOURCE_PROMOTION_ID and OFFER_ID back to Marketing campaign tables.

Efficient queries generally filter on LOG_ID (unique), LAST_UPDATE_DATE (index AS_LEAD_LINES_LOG_N1), or the LEAD_ID/LEAD_LINE_ID combination (index AS_LEAD_LINES_LOG_N2).

Related Objects

• AS_LEAD_LINES_ALL – referenced via LEAD_LINE_ID; the current master record for opportunity lines that this log history describes.
• AS_LEADS_ALL – referenced via LEAD_ID; the parent opportunity header.
• AS_INTEREST_TYPES_B – referenced via INTEREST_TYPE_ID; the interest type base table.
• AS_LEAD_LINES_LOG_U1 – the unique index on LOG_ID; the primary access path for point lookups.
• AS_LEAD_LINES_LOG_N1 and AS_LEAD_LINES_LOG_N2 – non-unique indexes supporting date-based and lead-based reporting.
• Oracle Marketing promotion/offer entities referenced through SOURCE_PROMOTION_ID and OFFER_ID, commonly joined for campaign attribution analysis.