Search Results as_leads_log_n2

Overview

OSM.AS_LEADS_LOG is a transactional history table within the Oracle E-Business Suite 12.1.1 / 12.2.2 Oracle Sales (OSM) module, registered under FND Design Data AS.AS_LEADS_LOG with VALID status. The table stores the opportunity change history — a chronological audit trail of modifications made to sales leads and opportunities over time. Each row captures a snapshot of lead attributes at a point in the lead lifecycle, enabling sales organizations to reconstruct how a prospect progressed from initial qualification to closure.

The metadata resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, while all four indexes live in APPS_TS_TX_IDX. The heuristic Data Vault classification derived from the foreign-key structure is link. In modeling terms, this table is best treated as a link-style structure that connects a lead (via LEAD_ID) to its historical state and to associated master entities such as customers, sales stages, and sales methodologies, rather than as a standalone hub or satellite.

Key Information Stored

The physical schema documents 33 columns. The most business-critical include:

• LOG_ID — surrogate primary key (AS_LEADS_LOG_PK) and the column of the unique index AS_LEADS_LOG_U1, which the user searched for. It is the true unique business-key candidate for the log entry.
• LEAD_ID — foreign key to AS_LEADS_ALL; identifies the lead whose history is being recorded.
• STATUS_CODE — the lead status, validated against AS_STATUSES_VL.
• SALES_STAGE_ID — the sales stage the lead occupied at capture time.
• WIN_PROBABILITY — likelihood of winning, expressed as a percentage.
• TOTAL_AMOUNT and CURRENCY_CODE — expected revenue and its validating currency from FND_CURRENCIES_VL.
• CUSTOMER_ID and ADDRESS_ID — the prospect's party and location references.
• LOG_MODE, LOG_START_DATE, LOG_END_DATE, LOG_ACTIVE_DAYS, and CURRENT_LOG — the temporal and versioning controls that delimit each historical record and flag the active snapshot (supporting index AS_LEADS_LOG_N2).
• OWNER_SALESFORCE_ID and OWNER_SALES_GROUP_ID — sales ownership attribution.
• Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER for concurrency control.

Common Use Cases and Queries

Typical reporting scenarios include opportunity pipeline trending, win-probability evolution, and audit reconstruction of a lead's prior states.

• Retrieve the current snapshot for a lead:
  SELECT * FROM as_leads_log WHERE lead_id = :p_lead AND current_log = 'Y';
• Retrieve full change history ordered chronologically (supported by AS_LEADS_LOG_N3 on LEAD_ID, CREATION_DATE):
  SELECT log_id, status_code, sales_stage_id, win_probability, creation_date FROM as_leads_log WHERE lead_id = :p_lead ORDER BY creation_date;
• Recent updates audit using AS_LEADS_LOG_N1 (LAST_UPDATE_DATE, LEAD_ID):
  SELECT lead_id, last_update_date FROM as_leads_log WHERE last_update_date >= :p_since;
• Pipeline valuation summary by currency:
  SELECT currency_code, SUM(total_amount) FROM as_leads_log WHERE current_log = 'Y' GROUP BY currency_code;

Related Objects

Foreign-key metadata links this table to several master and reference objects, joined on the following columns:

• AS_LEADS_ALL — via LEAD_ID; the parent lead record.
• HZ_PARTIES — via CUSTOMER_ID; the trading partner or prospect.
• AS_SALES_STAGES_ALL_B — via SALES_STAGE_ID; the sales stage definition.
• AS_SALES_METHODOLOGY_B — via SALES_METHODOLOGY_ID; the sales methodology.
• FND_SECURITY_GROUPS — via SECURITY_GROUP_ID; subscriber security context.

Reference validation also draws on AS_STATUSES_VL (status code) and FND_CURRENCIES_VL (currency code), and the CHANNEL_CODE is validated against the order capture LEAD_CHANNEL lookup.