Search Results as_leads_log_pk

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:

• LOG_ID — surrogate primary key; unique identifier for each logged version.
• LEAD_ID — foreign key to AS_LEADS_ALL, the opportunity being tracked.
• CUSTOMER_ID — foreign key to HZ_PARTIES, the customer on the opportunity header at log time.
• SALES_STAGE_ID — foreign key to AS_SALES_STAGES_ALL_B; stage in the sales cycle.
• SALES_METHODOLOGY_ID — foreign key to AS_SALES_METHODOLOGY_B; methodology applied.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS; controls record visibility by operating unit.
• STATUS_CODE — opportunity status as logged.
• WIN_PROBABILITY — probability of winning at log time.
• DECISION_DATE — expected customer decision date.
• CURRENCY_CODE and TOTAL_AMOUNT — opportunity value snapshot.
• OWNER_SALESFORCE_ID and OWNER_SALES_GROUP_ID — sales ownership snapshot.
• LOG_START_DATE, LOG_END_DATE, LOG_ACTIVE_DAYS, ENDDAY_LOG_FLAG, CURRENT_LOG — effective-dating and duration columns.
• LOG_MODE and DESCRIPTION — nature of and commentary on the change.
• ORG_ID, OBJECT_VERSION_NUMBER, and standard WHO audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN).

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.