Search Results as_lead_orders_u2

Overview

OSM.AS_LEAD_ORDERS is a transactional join table in the Oracle E-Business Suite 12.1.1 and 12.2.2 data model, owned by the OSM schema and registered under FND Design Data as AS.AS_LEAD_ORDERS. The table exists to record the association between a lead or opportunity captured in Oracle Sales and Marketing and the Oracle Order Entry sales order that resulted from it. As the documentation excerpt states, "AS_LEAD_ORDERS is a join table between as_leads_all and OE order_headers. It contains a link to determine what orders in Oracle Order Entry resulted from the lead/opportunity in Oracle Sales and Marketing." This makes the table a bridge object straddling two product families: the Oracle Sales (AS) schema, which owns leads and opportunities, and the Oracle Order Management (OE) schema, which owns order headers.

The ETRM metadata's heuristic Data Vault classification of this object is satellite-leaning. That classification is a modeling suggestion rather than a declarative Oracle attribute, and it reflects the table's dependency profile: the design carries descriptive attributes around a core foreign key relationship. A reasonable interpretation is that AS_LEAD_ORDERS functions as a link table that also carries peripheral descriptive content, principally the descriptive flexfield segment columns. Analysts building a dimensional or Data Vault-style layer over EBS should treat LEAD_ORDER_ID as the stable integration key and LEAD_ID plus ORDER_HEADER_ID as the natural business key of the relationship.

Key Information Stored

The table contains 29 documented columns. The most significant are summarized below.

• LEAD_ORDER_ID — Numeric surrogate primary key, enforced by the AS_LEAD_ORDERS_PK constraint. This is the single-column unique identifier for each association row and is the target of the AS_LEAD_ORDERS_U1 unique index.
• LEAD_ID — Foreign key to AS_LEADS_ALL, identifying the originating lead or opportunity.
• ORDER_HEADER_ID — Foreign key to the OE order headers entity, identifying the resulting sales order header.
• LEAD_ID and ORDER_HEADER_ID together — Form the AS_LEAD_ORDERS_U2 unique index, and therefore constitute the documented business-key candidate. The U2 index is the object the user searched for, and it guarantees that a given lead can be linked to a given order header only once.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, supporting multi-organization and security-group access control.
• Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide the standard Oracle audit trail.
• Concurrent program columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE capture the last concurrent request that touched the row.
• Descriptive flexfield columns — ATTRIBUTE_CATEGORY plus ATTRIBUTE1 through ATTRIBUTE15 (VARCHAR2(150) each) provide the DFF structure definition and segment values, and account for the satellite-leaning classification.

Common Use Cases and Queries

The primary reporting use case is lead-to-order attribution: determining which sales orders originated from marketing or telesales leads, and measuring conversion rates by lead source, campaign, or sales representative.

• Listing orders generated from a specific lead: SELECT order_header_id FROM as_lead_orders WHERE lead_id = :lead_id;
• Finding the lead behind a given order: SELECT lead_id FROM as_lead_orders WHERE order_header_id = :order_header_id;
• Joining to OE headers for order detail: SELECT o.order_number, l.lead_id FROM as_lead_orders lo, oe_order_headers_all o, as_leads_all l WHERE lo.order_header_id = o.header_id AND lo.lead_id = l.lead_id;
• Duplicate prevention and data-quality checks using the U2 business key: SELECT lead_id, order_header_id, COUNT(*) FROM as_lead_orders GROUP BY lead_id, order_header_id HAVING COUNT(*) > 1;
• Security-group filtering in multi-org deployments by constraining on SECURITY_GROUP_ID.
• DFF reporting, where ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 are exposed through the registered flexfield definition.

Related Objects

The following objects are the most significant dependencies and join targets for AS_LEAD_ORDERS.

• OSM.AS_LEADS_ALL — Joined on AS_LEAD_ORDERS.LEAD_ID = AS_LEADS_ALL.LEAD_ID; the source lead entity.
• OE_ORDER_HEADERS_ALL — Joined on AS_LEAD_ORDERS.ORDER_HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID; the resulting order header. This is referenced by the non-unique index AS_LEAD_ORDERS_N1 on ORDER_HEADER_ID.
• OSM.AS_LEAD_ORDERS_U1 — Unique index on LEAD_ORDER_ID, supporting primary key access.
• OSM.AS_LEAD_ORDERS_U2 — Unique index on (LEAD_ID, ORDER_HEADER_ID), enforcing the business key.
• OSM.AS_LEAD_ORDERS_N1 — Non-unique index on ORDER_HEADER_ID, supporting order-centric lookups.
• FND_SECURITY_GROUPS — Referenced through AS_LEAD_ORDERS.SECURITY_GROUP_ID for access control.

All indexes reside in the APPS_TS_TX_IDX tablespace, while table data is stored in APPS_TS_TX_DATA with PCT Free 10.