Results for “original_system_code”

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

Overview

AMS_SOURCE_CODES is a reference and master data table in the Oracle Marketing (AMS) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It records the source codes that qualify and categorize all objects tracked in the system, providing a centralized vocabulary for identifying where a lead, campaign result, interaction, or activity originated. Because source attribution is fundamental to marketing analytics — measuring promotion effectiveness, lead quality, channel performance, and campaign ROI — this table serves as a controlling lookup for a wide range of transactional and analytical flows across both the AMS schema and the interaction history (JTF_IH) tables.

Under the Data Vault classification heuristic mined from its foreign key structure, AMS_SOURCE_CODES behaves as a hub. This is a modeling suggestion: the table anchors a stable, uniquely identified business concept (a source code), and surrounding tables attach descriptive or transactional attributes to it. Practically, this makes AMS_SOURCE_CODES a natural integration point for reporting that must consolidate marketing source information across lead, campaign, and interaction subject areas.

Key Information Stored

The documented physical schema for release 12.2.2 shows 16 columns in the AMS schema. The most significant columns are:

• SOURCE_CODE_ID — the surrogate primary key, enforced by AMS_SOURCE_CODES_PK and also backed by the unique index AMS_SOURCE_CODES_U1. It is the identifier referenced by the many foreign keys that point at this table.
• SOURCE_CODE — the business-key candidate, enforced by the unique index AMS_SOURCE_CODES_U2. This is the human-recognizable source code value used in searches and joins at the business level.
• SOURCE_CODE_FOR_ID — a reference that associates the source code with the object it describes, supporting the "source codes for all objects" design stated in the object description.
• ARC_SOURCE_CODE_FOR — an archival or alternate designation of the source code's target, preserved for historical continuity.
• ACTIVE_FLAG — indicates whether the source code is currently active and therefore available for use in new leads, activities, and campaign results.
• SECURITY_GROUP_ID — the security group that governs visibility and access, with a documented foreign key to FND_SECURITY_GROUPS.
• RELATED_SOURCE_CODE, RELATED_SOURCE_OBJECT, and RELATED_SOURCE_ID — a triple that links this source code to a related source entity, enabling hierarchical or cross-referenced source relationships.
• ORIGINAL_SYSTEM_CODE — identifies the originating system, which is central to multi-source data integration and migration mapping.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — the standard EBS audit and concurrency-control columns, used for change tracking and stale-data detection.

Common Use Cases and Queries

Typical usage centers on resolving a source code ID to its descriptive value, validating active codes before assignment, and reporting source distribution across lead and campaign activity. A common join retrieves lead attribution:

• SELECT l.lead_id, s.SOURCE_CODE FROM AS_LEADS_ALL l JOIN AMS.AMS_SOURCE_CODES s ON l.SOURCE_PROMOTION_ID = s.SOURCE_CODE_ID;
• SELECT SOURCE_CODE_ID, SOURCE_CODE, ACTIVE_FLAG FROM AMS.AMS_SOURCE_CODES WHERE ACTIVE_FLAG = 'Y'; — to populate a valid-code list.
• SELECT h.INTERACTION_ID, s.SOURCE_CODE FROM JTF_IH_INTERACTIONS h JOIN AMS.AMS_SOURCE_CODES s ON h.SOURCE_CODE_ID = s.SOURCE_CODE_ID; — to attribute interactions to a source channel.

Reporting scenarios include source performance analysis (leads generated by source), campaign result attribution, and interaction volume by source. Change-audit queries using LAST_UPDATE_DATE and ORIGINAL_SYSTEM_CODE support data-governance and reconciliation tasks following a migration.

Related Objects

AMS_SOURCE_CODES is referenced by numerous tables through documented foreign keys. The most significant are:

• AS_LEADS_ALL — joins on SOURCE_PROMOTION_ID; the primary consumer for lead source attribution.
• AS_LEAD_LINES_ALL — joins on SOURCE_PROMOTION_ID, capturing source at the lead line level.
• AS_SALES_LEAD_LINES — joins on SOURCE_PROMOTION_ID for sales-lead sourcing.
• JTF_IH_INTERACTIONS — joins on SOURCE_CODE_ID; interaction-level source tracking.
• JTF_IH_ACTIVITIES — joins on SOURCE_CODE_ID; links activities to their initiating source.
• JTF_IH_CAMPAIGNS_RESULTS and JTF_IH_CAMPAIGN_RESULT_REASONS — join on SOURCE_CODE_ID to attribute campaign outcomes to a source.
• JTF_IH_INTERACTIONS_CAMPAIGNS and JTF_IH_OUTCOMES_CAMPAIGNS — join on SOURCE_CODE_ID for campaign-interaction sourcing.
• JTF_IH_WRAP_UPS — uniquely joins on both SOURCE_CODE_ID and SOURCE_CODE, supporting wrap-up source capture.
• FND_SECURITY_GROUPS — the parent for SECURITY_GROUP_ID, governing row-level visibility.

Together these relationships confirm AMS_SOURCE_CODES as the central source-attribution hub for Oracle Marketing and interaction history data.