Results for “as_leads_n5”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

OSM.AS_LEADS_ALL is the core transactional table of the Oracle E-Business Suite Sales (Oracle Sales / Oracle Telesales) application, storing information about sales opportunities (leads). In the 12.1.1 and 12.2.2 releases, it is owned by the OSM schema and registered in the application design data as AS.AS_LEADS_ALL. Each row represents a discrete opportunity tracked through the sales pipeline, carrying pipeline stage, win probability, forecast amounts, competitor intelligence, and the owning sales resource or sales group.

The object is status VALID and resides in tablespace APPS_TS_TX_DATA with a PCTFREE of 10. The documented physical schema exposes 80 columns. Because AS_LEADS_ALL holds the natural business entity keyed by a single surrogate identifier and is referenced by an extensive fan-out of dependent tables, the metadata classifies it heuristically as hub-leaning in a Data Vault model. In practice, a Data Vault implementation would treat LEAD_ID as the hub key, with attributes such as TOTAL_AMOUNT, WIN_PROBABILITY, and STATUS modelled as satellites linked to that hub.

Key Information Stored

The surrogate primary key is AS_LEADS_ALL_PK (LEAD_ID), a NUMBER populated from an internal sequence. The unique index AS_LEADS_U1 (LEAD_ID) enforces this uniqueness; the business-facing identifier visible to users is LEAD_NUMBER, which is indexed non-uniquely by AS_LEADS_N12. The most significant columns are:

• LEAD_ID – surrogate unique key; the anchor for all child records.
• LEAD_NUMBER – user-visible opportunity number.
• STATUS – foreign key to AS_STATUSES_B; drives pipeline reporting and is indexed by AS_LEADS_N19 and AS_LEADS_N5.
• SALES_STAGE_ID – FK to AS_SALES_STAGES_ALL_B, the deal stage in the sales cycle.
• WIN_PROBABILITY – FK to AS_FORECAST_PROB_ALL_B; key forecast input, indexed by AS_LEADS_N6.
• CUSTOMER_ID / ADDRESS_ID – FK to the party (HZ_PARTIES, RA_CUSTOMERS) and site (HZ_PARTY_SITES, RA_ADDRESSES_ALL), indexed jointly by AS_LEADS_N1.
• TOTAL_AMOUNT / CURRENCY_CODE – revenue value of the opportunity and its currency (FK to FND_CURRENCIES).
• DECISION_DATE – expected close/decision date; indexed by AS_LEADS_N19 and AS_LEADS_N20.
• OWNER_SALESFORCE_ID / OWNER_SALES_GROUP_ID – FKs to JTF_RS_RESOURCE_EXTNS and JTF_RS_GROUPS_B, defining ownership and routing.
• CLOSE_COMPETITOR_ID – FK to HZ_PARTIES, the winning competitor.
• SOURCE_PROMOTION_ID – FK to AMS_SOURCE_CODES, campaign attribution.
• ORG_ID – multi-organization operating unit discriminator.
• DESCRIPTION – free text, function-based indexed (case-insensitive) via AS_LEADS_N9.
• DELETED_FLAG / OBJECT_VERSION_NUMBER – soft-delete indicator and optimistic locking column.

Standard WHO columns (CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and concurrent program columns) are present and indexed by AS_LEADS_N26 and AS_LEADS_N3. Fifteen ATTRIBUTE1..15 descriptive flexfield columns provide extensibility.

Common Use Cases and Queries

The table is queried heavily for pipeline, forecast, and win/loss analysis. A typical open-pipeline query joins status, stage, and forecast probability:

SELECT l.lead_number, l.total_amount, l.currency_code,
       s.status_name, st.stage_name, l.win_probability
FROM   as_leads_all l,
       as_statuses_b s,
       as_sales_stages_all_b st
WHERE  l.status = s.status_id
AND    l.sales_stage_id = st.sales_stage_id
AND    l.deleted_flag = 'N'
AND    l.org_id = :org_id;

Forecast weighting is derived by multiplying TOTAL_AMOUNT by the probability mapped through WIN_PROBABILITY. Competitor win/loss reports aggregate on CLOSE_COMPETITOR_ID, while source-attribution reports aggregate on SOURCE_PROMOTION_ID. Territory and assignment routines query by OWNER_SALES_GROUP_ID and AUTO_ASSIGNMENT_TYPE. Because DESCRIPTION is function-based indexed, case-insensitive search on UPPER(DESCRIPTION) is supported. Reporting should always filter on DELETED_FLAG = 'N' and constrain ORG_ID for multi-org security.

Related Objects

AS_LEADS_ALL is a hub with a very wide dependency footprint. The most significant dependents and referenced objects include:

• AS_LEAD_LINES_ALL.LEAD_ID – product line details for each opportunity.
• AS_LEAD_CONTACTS_ALL.LEAD_ID – contacts associated with the lead.
• AS_ACCESSES_ALL_ALL.LEAD_ID – team members granted access to the opportunity.
• AS_SALES_CREDITS.LEAD_ID and AS_SALES_CREDITS_DENORM.LEAD_ID – sales credit allocations.
• AS_QUOTES_ALL.LEAD_ID and ASO_QUOTE_RELATED_OBJECTS.OBJECT_ID – quotations generated from the opportunity.
• AS_INTERESTS_ALL.LEAD_ID – products or interests expressed by the customer.
• AS_OPP_WORKSHEET_LINES.LEAD_ID – forecasting worksheet entries.
• AS_PARTNER_LEAD_LINES.LEAD_ID and AS_PARTNER_ACCESSES.LEAD_ID – channel/partner collaboration records.
• AS_OSI_LEADS_ALL.LEAD_ID – integration staging for the opportunity.
• ASF_OPP_TOP_MV.LEAD_ID and related materialized views – pre-built forecast and pipeline summaries.

On the referenced side, the key parents are AS_STATUSES_B, AS_SALES_STAGES_ALL_B, AS_FORECAST_PROB_ALL_B, HZ_PARTIES, HZ_PARTY_SITES, JTF_RS_RESOURCE_EXTNS, JTF_RS_GROUPS_B, and AMS_SOURCE_CODES, joined via the foreign keys described above. These relationships make AS_LEADS_ALL the central fact-like hub of the Oracle Sales data model.