Results for “as_interest_codes_all”

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

Overview

AS_INTEREST_CODES_ALL is a Sales Foundation (AS) table in Oracle E-Business Suite 12.1.1 and 12.2.2, owned by the OSM schema. It defines the interest codes used throughout the Oracle Sales and Telesales modules to classify customer and prospect interest by product, revenue class, and interest type. Interest codes are the fundamental driver of territory assignment logic, lead qualification, and opportunity routing in the EBS Sales applications.

The table is a multi-organization (multi-org) enabled object, evidenced by the "_ALL" suffix and the ORG_ID column that forms part of the composite primary key. This means a single logical interest code can exist once per operating unit, with org-specific attributes such as pricing, currency, and enabled status. The "_B" base table (AS_INTEREST_CODES_B) holds the language-independent definition, while this table stores the operational attributes.

From a Data Vault modeling perspective, the heuristic classification is hub-leaning. INTEREST_CODE_ID functions as the natural business key, and the table carries descriptive attributes (revenue class, interest type, pricing, descriptions) that could be modeled as satellites. In practice, the EBS table combines the hub key, multi-org association, and descriptive attributes into one registration table.

Key Information Stored

The surrogate primary key is AS_INTEREST_CODES_ALL_PK, composed of INTEREST_CODE_ID and ORG_ID. Two unique indexes are documented: AS_INTEREST_CODES_ALL_U2 (also INTEREST_CODE_ID, ORG_ID) and the primary constraint, reinforcing the multi-org uniqueness rule.

The most significant columns are:

• INTEREST_CODE_ID — Surrogate identifier for the interest code; joins to AS_INTEREST_CODES_B.
• ORG_ID — Operating unit identifier; key component enabling multi-org definitions.
• CODE — User-facing interest code value entered on leads, opportunities, and customer records.
• REVENUE_CLASS_ID — Foreign key to CN_REVENUE_CLASSES_ALL, tying the code to a revenue classification.
• INTEREST_TYPE_ID — Foreign key to AS_INTEREST_TYPES_B, grouping codes by interest category.
• ENABLED_FLAG — Controls whether the code is active for use in sales transactions.
• PARENT_INTEREST_CODE_ID — Supports hierarchical interest code structures.
• CATEGORY_ID / CATEGORY_SET_ID — Links the code to a product category within a category set.
• CURRENCY_CODE, PRICE, PF_ITEM_ID, PF_ORGANIZATION_ID — Pricing attributes for the interest code.
• DESCRIPTION — Descriptive text presented to users during selection.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS for row-level security.
• Attribute1–Attribute15, ATTRIBUTE_CATEGORY — DFF columns for client extensions.
• Audit columns — LAST_UPDATE_DATE, CREATED_BY, PROGRAM_ID, REQUEST_ID, etc.

Common Use Cases and Queries

Reporting on active interest codes for a specific operating unit is a common requirement:

SELECT ic.interest_code_id, ic.code, ic.description,
       it.interest_type, rc.revenue_class_name
FROM   as_interest_codes_all ic,
       as_interest_types_b it,
       cn_revenue_classes_all rc
WHERE  ic.org_id = :org_id
AND    ic.enabled_flag = 'Y'
AND    it.interest_type_id = ic.interest_type_id
AND    rc.revenue_class_id = ic.revenue_class_id;

Other typical scenarios include validating that a lead's PRIMARY_INTEREST_CODE_ID or SECONDARY_INTEREST_CODE_ID resolves to an enabled code, analyzing territory assignment coverage by interest code, and auditing pricing attributes (CURRENCY_CODE, PRICE) associated with specific codes. A common join pattern drills from AS_LEAD_LINES_ALL or AS_INTERESTS_ALL back to this table through the primary and secondary interest code foreign keys.

SELECT ll.lead_line_id, ic.code
FROM   as_lead_lines_all ll, as_interest_codes_all ic
WHERE  ll.primary_interest_code_id = ic.interest_code_id
AND    ll.org_id = ic.org_id;

Related Objects

• AS_INTEREST_CODES_B — Base (language-independent) definition table; this table's INTEREST_CODE_ID references it.
• AS_INTEREST_TYPES_B — Referenced via INTEREST_TYPE_ID to classify interest codes by type.
• CN_REVENUE_CLASSES_ALL — Referenced via REVENUE_CLASS_ID to associate revenue classification.
• FND_SECURITY_GROUPS — Referenced via SECURITY_GROUP_ID for security enforcement.
• AS_INTERESTS_ALL — References this table via PRIMARY_INTEREST_CODE_ID and SECONDARY_INTEREST_CODE_ID; stores interest records on customers and prospects.
• AS_LEAD_LINES_ALL — References this table via PRIMARY_INTEREST_CODE_ID and SECONDARY_INTEREST_CODE_ID on lead lines.
• AS_TERRITORY_VALUES_ALL — References this table via PRIMARY_INTEREST_CODE_ID and SECONDARY_INTEREST_CODE_ID for territory assignment rules.