Results for “budget_code_suffix”

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

Overview

AMS_CATEGORIES_B is the base (non-translated) definition table for categories within the Oracle E-Business Suite 12.1.1 and 12.2.2 Marketing (AMS) module. It stores the master definition of category records that are consumed by the Events, Metrics, and Deliverables areas of the application. The table physically resides in the AMS schema and is documented with 32 columns in the ETRM 12.2.2 physical schema. Its purpose is to provide a centralized, hierarchical classification mechanism: category records can reference a parent category through PARENT_CATEGORY_ID, enabling multi-level category trees rather than flat lists. Translated, user-facing category names are stored separately in AMS_CATEGORIES_TL, which is the standard Oracle EBS multilingual pattern of pairing a "_B" base table with a "_TL" translation table.

From a modeling perspective, the Data Vault classification supplied in the metadata is heuristic and hub-leaning. This is consistent with the table's role as a master definition entity whose primary key, CATEGORY_ID, is referenced by numerous dependent transactional and configuration tables. In practice, the object may be best treated as a hub-style candidate in a Data Vault model, with the descriptive foreign-key relationships to parent categories and security groups handled via link structures.

Key Information Stored

The table is anchored by its surrogate primary key CATEGORY_ID, enforced through the AMS_CATEGORIES_B_PK constraint. A second unique index, AMS_CATEGORIES_B_U1 on (CATEGORY_ID, ZD_EDITION_NAME), reflects the edition-aware business-key candidate used in 12.2.2.

• CATEGORY_ID — surrogate primary key and the column referenced by every child table.
• PARENT_CATEGORY_ID — self-referencing foreign key enabling hierarchical category structures.
• ENABLED_FLAG — controls whether a category is active and selectable in Events, Metrics, and Deliverables.
• ARC_CATEGORY_CREATED_FOR — indicates the functional area or context for which the category was created.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting data-level security partitioning.
• LEDGER_ID — associates the category with a specific ledger for financial or accrual-related categorization.
• ACCRUED_LIABILITY_ACCOUNT — accounting flexfield reference for accrued liability postings.
• DED_ADJUSTMENT_ACCOUNT — accounting flexfield reference for deduction adjustment postings.
• BUDGET_CODE_SUFFIX — suffix used when deriving budget codes from the category.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield columns for extensible, customer-defined attributes.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Application Framework and concurrent APIs.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns capturing change history.
• ZD_EDITION_NAME — editioning column supporting the 12.2 online patching architecture.

Common Use Cases and Queries

The primary use case is retrieving active categories for use in Events, Metrics, and Deliverables. A typical query joins the base table to its translation table to return user-facing names:

• Active category lookup: SELECT b.category_id, t.name FROM ams.ams_categories_b b, ams.ams_categories_tl t WHERE b.category_id = t.category_id AND b.enabled_flag = 'Y'.
• Hierarchy traversal: A self-join on PARENT_CATEGORY_ID resolves parent-child relationships for reporting category trees.
• Metrics categorization: Joining to AMS_METRICS_ALL_B on METRIC_CATEGORY or METRIC_SUB_CATEGORY supports metric reporting and grouping.
• Deliverables classification: Joining to AMS_DELIVERABLES_ALL_B on CATEGORY_TYPE_ID or CATEGORY_SUB_TYPE_ID supports deliverables analysis.
• Accounting derivation: Reporting against ACCRUED_LIABILITY_ACCOUNT, DED_ADJUSTMENT_ACCOUNT, and BUDGET_CODE_SUFFIX supports budget and accrual reconciliation.
• Security-scoped reporting: Filtering by SECURITY_GROUP_ID aligns results with the user's security profile.

Related Objects

The table sits at the center of a well-defined foreign-key network. The most significant related objects are:

• AMS_CATEGORIES_TL — holds translated names and descriptions, joined on CATEGORY_ID.
• AMS_METRICS_ALL_B — references this table via METRIC_CATEGORY and METRIC_SUB_CATEGORY.
• AMS_DELIVERABLES_ALL_B — references this table via CATEGORY_TYPE_ID and CATEGORY_SUB_TYPE_ID.
• AMS_ACT_CATEGORIES — links activities to categories via CATEGORY_ID.
• AMS_ACT_PRODUCTS — references CATEGORY_ID to associate products with categories.
• AMS_CAT_ACTIVITIES — references CATEGORY_ID to associate activities with categories.
• AMS_METRIC_FORMULAS — references this table through SOURCE_ID.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for security partitioning.
• AMS_CATEGORIES_B itself — self-referenced through PARENT_CATEGORY_ID for hierarchical structure.

Because AMS_CATEGORIES_B is a hub-leaning master definition table with widespread downstream dependencies, changes to its records can propagate across Events, Metrics, Deliverables, activity, and formula functionality; impact analysis prior to modification is therefore essential.