Search Results orig_system_code




Overview
Explore in AI Chat

Overview

AST_SALES_LEADS_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite, defined within the AST (TeleSales) product family. It presents a denormalized, business-friendly projection of sales lead records held in the underlying AS_SALES_LEADS table, joining in descriptive meaning columns so that consumers do not have to resolve codes against multiple lookup and reference tables. The view is documented as VALID in both ETRM 12.1.1 and 12.2.2 metadata, and it is the primary read interface used by TeleSales dashboards, lead management reports, and external integrations that must extract lead data without navigating the transactional base tables directly.

The view exposes the full lead lifecycle: identification attributes, customer and address context, assignment information, channel and source detail, budget and currency, scoring and ranking, partner relationship management (PRM) attributes, descriptive flexfield segments, and audit columns. Because the ORIG_SYSTEM_CODE column is present, the view is frequently used to trace imported or interfaced leads back to their originating system, which is a common requirement in multi-source lead ingestion scenarios.

Underlying Base Objects

The view is defined over a combination of base tables, synonyms, and supporting views. The principal table is AS_SALES_LEADS (referenced through a synonym), which supplies all lead-level columns including SALES_LEAD_ID, LEAD_NUMBER, STATUS_CODE, CUSTOMER_ID, ASSIGN_TO_PERSON_ID, QUALIFIED_FLAG, and ORIG_SYSTEM_CODE. Descriptive meaning columns are joined from lookup sources including AS_LOOKUPS, OE_LOOKUPS, AS_STATUSES_VL and AS_STATUSES_TL, and AS_SALES_LEAD_RANKS_TL.

Each join enriches the base lead row with a human-readable value, which is why the view is preferred over direct queries against AS_SALES_LEADS in reporting contexts.

Key Columns

Several columns warrant specific attention. SALES_LEAD_ID is the primary key and should be used for joins back to the base table. LEAD_NUMBER is the user-visible lead identifier. ORIG_SYSTEM_CODE and ORIG_SYSTEM_REFERENCE identify the source system and the source record, which together support reconciliation of inbound interfaces. STATUS_CODE and its associated meaning column reflect lead state. CUSTOMER_ID links to HZ_PARTIES, with PARTY_NAME, PARTY_NUMBER, and PARTY_TYPE exposed directly. Assignment is captured by ASSIGN_TO_PERSON_ID, ASSIGN_TO_SALESFORCE_ID, ASSIGN_DATE, and ASSIGN_SALES_GROUP_ID. Qualification is indicated by QUALIFIED_FLAG, TOTAL_SCORE, SCORECARD_ID, and LEAD_RANK_CODE. Audit columns such as CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, and REQUEST_ID support lineage tracking. The descriptive flexfield columns ATTRIBUTE1 through ATTRIBUTE15, plus ATTRIBUTE_CATEGORY, allow client-specific extensions to be surfaced.

Common Use Cases and Queries

Typical uses include lead conversion analysis, assignment workload reports, scoring and ranking extracts, and inbound interface reconciliation using ORIG_SYSTEM_CODE. A simple listing of active leads with their assigned resource and source system is shown below.

  • SELECT lead_number, status_code, party_name, source_name, orig_system_code FROM apps.ast_sales_leads_v WHERE deleted_flag = 'N';
  • SELECT orig_system_code, COUNT(*) FROM apps.ast_sales_leads_v GROUP BY orig_system_code;
  • SELECT lead_number, assign_to_person_id, assign_date FROM apps.ast_sales_leads_v WHERE qualified_flag = 'Y' ORDER BY assign_date DESC;

Because the view is read-only and pre-joins lookup meanings, it is safe for high-volume reporting workloads, though queries should always filter on deleted_flag and qualified_flag as appropriate to avoid unnecessary full scans.