Results for “iby_g_msg_templates_b”

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

Overview

IBY_G_MSG_TEMPLATES_B is the base (master) table that stores message template definitions within the Oracle Payments (IBY) module of Oracle E-Business Suite. In the EBS architecture, message templates provide the reusable, versioned text and formatting framework that the Payments engine and its notification infrastructure use when generating outbound communications such as payment process request notifications, remittance advices, and other payment-related correspondence. The "_B" suffix denotes the base table, holding language-independent template attributes, while translatable text is stored in the companion "_TL" table, IBY_G_MSG_TEMPLATES_TL.

From a Data Vault modeling perspective, the metadata classifies this object as hub-leaning. This heuristic reflects the fact that the table acts as a durable registry of message template identities, keyed by a surrogate identifier and referenced by downstream transactional or descriptive tables. In a Data Vault design this would typically be modeled as a hub capturing the unique template business key, with dependent descriptive attributes handled separately.

Key Information Stored

The table contains 32 documented columns. The most significant are the following:

• MSG_TEMPLATE_ID — the surrogate primary key (constraint IBY_G_MSG_TEMPLATES_B_PK) uniquely identifying each template record.
• MSG_TEMPLATE_NAME and MSG_TEMPLATE_VERSION — together forming the business-key candidate defined by unique index IBY_G_MSG_TEMPLATES_B_U2, ensuring each named template version is unique.
• MSG_TEMPLATE_NUMBER — a human-readable reference for the template.
• MSG_TYPE_CODE — classifies the message type the template applies to.
• FROZEN_FLAG and SEEDED_FLAG — control whether a template is locked from edit and whether it was delivered as seeded Oracle data.
• START_DATE_ACTIVE and END_DATE_ACTIVE — define the effective date range during which the template is active.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-org/security partitioning.
• OBJECT_VERSION_NUMBER — supports optimistic locking for concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard Oracle descriptive flexfield (DFF) columns for extensibility.

Common Use Cases and Queries

Typical usage involves resolving active, current-version templates for a given message type, and tracing which notification records used which template. Common patterns include:

• Identifying the latest active template for a message type:

SELECT msg_template_id, msg_template_name, msg_template_version
FROM   iby_g_msg_templates_b
WHERE  msg_type_code = :code
AND    TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active, SYSDATE)
ORDER BY msg_template_version DESC;

• Reporting audit history of template changes using LAST_UPDATED_BY and LAST_UPDATE_DATE.
• Restricting listings to seeded or custom templates via SEEDED_FLAG.
• Joining to IBY_G_MSG_TEMPLATES_TL to retrieve the translatable name or text for display.

Related Objects

The following objects depend on or reference IBY_G_MSG_TEMPLATES_B, using the documented join columns:

• IBY_G_MSG_TEMPLATES_TL — join on MSG_TEMPLATE_ID; holds translated template text.
• IBY_F_T_EMAIL_NOTIF_LOG — join on MSG_TEMPLATE_ID; logs email notifications generated from a template.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for row-level security.