Results for “lead_description”

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

Overview

EDW_BIM_LEAD_LEADS_LTC is a table owned by the BIM schema in Oracle E-Business Suite, associated with the Marketing Intelligence product family. The object carries an Obsolete status in the ETRM documentation, meaning it is retained primarily for historical, archival, or data-warehouse reconciliation purposes rather than active transactional processing. Within the BIM marketing analytics model, it functions as the lead-level fact table — the finest grain of the leads star schema — storing one row per sales lead together with its scoring, budget, assignment, and source-promotion attributes. Its name prefix EDW_ indicates it originates from the Enterprise Data Warehouse extract pipeline that feeds marketing intelligence reporting.

The metadata heuristic classifies this object as standalone within a Data Vault model. As a modeling suggestion, this implies the table does not sit at the center of an inferred hub-and-link cluster driven solely by its foreign-key topology. It instead bears the characteristics of a denormalized satellite-style lead-level snapshot, since it records descriptive, mutable attributes (scores, flags, timestamps) rather than pure key relationships. The absence of an inbound referenced-by signature reinforces that it is best treated as a leaf-level reporting object in any downstream warehouse design.

Key Information Stored

The table defines 41 columns. Its documented surrogate primary key is LEAD_PK, while the unique index EDW_BIM_LEAD_LEADS_LTC_U1 exposes LEAD_PK_KEY as the business-key candidate suitable for cross-system reconciliation. The most significant columns include:

• SALES_LEAD_ID — the operational lead identifier, foreign-keyed to AS_SALES_LEADS; the principal linkage back to the transactional source.
• LEAD_NUMBER — the human-readable lead reference presented to users.
• NAME and LEAD_DESCRIPTION — descriptive identity of the lead.
• TOTAL_SCORE and SCORECARD_ID — the lead-scoring outcome and the scorecard definition applied, central to marketing qualification analysis.
• BUDGET_AMOUNT, CURRENCY_CODE, and BUDGET_STATUS_CODE — financial qualification of the opportunity.
• ASSIGN_TO_PERSON_ID and ASSIGN_DATE — sales-resource ownership and routing history.
• SOURCE_PROMOTION_ID and VEHICLE_RESPONSE_CODE — campaign attribution and response channel.
• CUSTOMER_ID and ADDRESS_ID — the party and location against which the lead was raised.
• LEAD_RANK_CODE, ACCEPT_FLAG, KEEP_FLAG, DELETED_FLAG, and URGENT_FLAG — status and disposition flags.
• SECURITY_GROUP_ID — the operational unit controlling row-level access, foreign-keyed to FND_SECURITY_GROUPS.
• CREATION_DATE and LAST_UPDATE_DATE — audit timestamps supporting incremental extraction.

USER_ATTRIBUTE1 through USER_ATTRIBUTE5, ORIG_SYSTEM_REFERENCE, and INSTANCE provide extensibility and multi-source origin tracking.

Common Use Cases and Queries

The dominant use case is lead conversion and pipeline reporting. Analysts join the warehouse table back to the transactional lead table to measure funnel performance by promotion, score, or owning resource.

• Win/loss and acceptance analysis — aggregate ACCEPT_FLAG by SOURCE_PROMOTION_ID to evaluate campaign yield.
• Scoring effectiveness — correlate TOTAL_SCORE against ACCEPT_FLAG to validate scorecard thresholds.
• Assignment load balancing — count and sum BUDGET_AMOUNT grouped by ASSIGN_TO_PERSON_ID.
• Incremental ETL — filter on LAST_UPDATE_DATE to drive delta loads.

A representative query joining the source system:

SELECT l.LEAD_NUMBER, l.TOTAL_SCORE, l.BUDGET_AMOUNT, s.SALES_LEAD_ID
FROM BIM.EDW_BIM_LEAD_LEADS_LTC l
JOIN AS_SALES_LEADS s ON s.SALES_LEAD_ID = l.SALES_LEAD_ID
WHERE l.DELETED_FLAG = 'N' AND l.LAST_UPDATE_DATE >= :p_since;

Related Objects

• AS_SALES_LEADS — joined via EDW_BIM_LEAD_LEADS_LTC.SALES_LEAD_ID = AS_SALES_LEADS.SALES_LEAD_ID; the authoritative transactional lead record.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; enforces row-level security and organizational partitioning.
• Scorecard definition tables referenced by SCORECARD_ID for score interpretation.
• Promotion/campaign tables referenced by SOURCE_PROMOTION_ID for attribution roll-ups.
• Customer and address master tables referenced by CUSTOMER_ID and ADDRESS_ID.
• HR/per-resource tables referenced by ASSIGN_TO_PERSON_ID and INITIATING_CONTACT_ID.

Because the object is marked Obsolete, integrators should treat it as read-only and validate its continued population before depending on it for new deliverables.