Search Results as_leads_log




Overview
Explore in AI Chat

Overview

AS_LEADS_LOG is a Sales Foundation (AS) table owned by the OSM schema that stores the opportunity header change history for Oracle E-Business Suite. Each row captures a versioned snapshot of an opportunity (internally a "lead") header as its key commercial attributes evolve over time. The table is a history/audit satellite to the live opportunity record and is distinct from AS_LEADS_ALL, which holds the current state of the opportunity.

The ETRM documentation describes the object simply as "Holds opportunity header change history." Its structure reflects this purpose: business attributes such as status code, sales stage, win probability, decision date, currency, total amount, and owner are replicated with effective-dated columns (LOG_START_DATE, LOG_END_DATE, LOG_ACTIVE_DAYS, ENDDAY_LOG_FLAG, CURRENT_LOG) so that point-in-time and duration-based analysis is possible. The LOG_MODE column indicates the nature of the logged change, and OBJECT_VERSION_NUMBER supports optimistic locking.

The Data Vault classification is link per the heuristic mined from its foreign-key structure. From a modeling perspective, this classification is a suggestion rather than a physical design statement; because the table carries descriptive change attributes alongside its keys, it behaves most like a satellite attached to a link between the opportunity and the party/stage/methodology dimensions.

Key Information Stored

The surrogate primary key is LOG_ID, enforced by AS_LEADS_LOG_PK and mirrored by the unique index AS_LEADS_LOG_U1 (LOG_ID). The table also holds a foreign key from LEAD_ID to AS_LEADS_ALL, identifying the opportunity whose header is being versioned. The most significant columns include:

Common Use Cases and Queries

Typical uses include pipeline aging ("how long did this opportunity sit in each stage?"), win-probability trend reporting, and reconstructing the opportunity header as of a given date. The CURRENT_LOG flag or a LOG_END_DATE test identifies the active version.

Example query: define the current header for an opportunity:

  • SELECT * FROM osm.as_leads_log WHERE lead_id = :p_lead_id AND current_log = 'Y';

Point-in-time reconstruction:

  • SELECT * FROM osm.as_leads_log WHERE lead_id = :p_lead_id AND :p_date BETWEEN log_start_date AND NVL(log_end_date, SYSDATE);

Stage-duration analysis joins AS_SALES_STAGES_ALL_B on SALES_STAGE_ID and aggregates LOG_ACTIVE_DAYS, while revenue trend reporting joins HZ_PARTIES on CUSTOMER_ID to group history by customer. Reporting tools such as Oracle BI Publisher or OBIEE typically source these from a view or extract, filtered on ORG_ID and SECURITY_GROUP_ID for multi-org security.

Related Objects

The table's principal relationships are defined by its foreign keys and should be joined on the columns shown:

  • AS_LEADS_ALL — the current opportunity header; join AS_LEADS_LOG.LEAD_ID = AS_LEADS_ALL.LEAD_ID.
  • HZ_PARTIES — customer master; join AS_LEADS_LOG.CUSTOMER_ID = HZ_PARTIES.PARTY_ID.
  • AS_SALES_STAGES_ALL_B — sales stage definitions; join on SALES_STAGE_ID.
  • AS_SALES_METHODOLOGY_B — sales methodology definitions; join on SALES_METHODOLOGY_ID.
  • FND_SECURITY_GROUPS — security group definition; join on SECURITY_GROUP_ID.

Other AS objects that read or derive from this history include opportunity reporting views and the Sales Foundation history-extraction program logic that populates AS_LEADS_LOG from header changes in AS_LEADS_ALL. When querying for current-state analysis, AS_LEADS_ALL is authoritative; AS_LEADS_LOG is authoritative for historical and duration-based analysis.