Search Results aso_sup_tmpl_instance_pk

Overview

ASO_SUP_TMPL_INSTANCE is a transaction and configuration table within the ASO (Order Capture) product family of Oracle E-Business Suite. As its name implies, it stores individual instances of supplementary templates — the runtime instantiations created when a template definition (stored separately in the template header table) is applied to a specific owning business entity. Each row represents one template instance and anchors the values, conditions, and rule associations that follow from that instantiation.

The ETRM documentation describes the object only briefly ("This table is to store the instance of the template"), and the mined foreign-key structure classifies it heuristically as standalone — there are no outbound foreign keys to other ASO business entities, only a security-group reference to FND_SECURITY_GROUPS. In Data Vault modeling terms, this object is best treated as a satellite or transactional detail table keyed to its parent template definition, rather than as a hub or link. It does not act as a shared business key integrating multiple sources; it captures instance-level descriptive and contextual payload for a single template.

Key Information Stored

The table is defined with 32 columns in the documented 12.2.2 physical schema. The most significant columns are:

• TEMPLATE_INSTANCE_ID — surrogate primary key (ASO_SUP_TMPL_INSTANCE_PK); also the sole documented unique index (ASO_SUP_TMPL_INSTANCE_U1), making it the de facto business-key candidate.
• TEMPLATE_ID — identifies the template definition that this row instantiates.
• OWNER_TABLE_NAME and OWNER_TABLE_ID — the polymorphic reference to the owning entity (the table name and its row identifier) to which the instance is attached.
• OBJECT_VERSION_NUMBER — optimistic locking / concurrency control.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-org / security-group data isolation.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — standard EBS audit columns.
• CONTEXT and ATTRIBUTE1 through ATTRIBUTE20 — the DFF/descriptive flexfield segment set, providing extensibility without schema changes.

Note that the audit columns and the twenty ATTRIBUTE columns constitute the majority of the 32-column definition; the operational payload is compact and centered on the template-instance/owner relationship.

Common Use Cases and Queries

Typical usage centers on retrieving the instances of a given template, locating the instance attached to a particular owning record, and joining to instance values for reporting.

SELECT i.template_instance_id,
       i.template_id,
       i.owner_table_name,
       i.owner_table_id
FROM   aso.aso_sup_tmpl_instance i
WHERE  i.template_id = :p_template_id;

To find the template instance bound to a specific business record:

SELECT i.template_instance_id
FROM   aso.aso_sup_tmpl_instance i
WHERE  i.owner_table_name = :p_owner_table
AND    i.owner_table_id   = :p_owner_id;

Reporting frequently joins the instance to its stored values via ASO_SUP_INSTANCE_VALUE to present a complete instance view. Because the table is security-group enabled, any custom query should generally filter on SECURITY_GROUP_ID (or run within an initialized org context) to respect data access.

Related Objects

The following related objects are documented and are the most significant for joins and dependency analysis:

• ASO_SUP_INSTANCE_VALUE — references this table via TEMPLATE_INSTANCE_ID; holds the individual values captured for a template instance.
• AMS_QUERY_TEMPLATE_INSTANCE — references this table via TEMPLATE_INSTANCE_ID; associates query templates with an instance.
• AMS_QUERY_TEMP_INST_COND_ASSOC — references this table via TEMPLATE_INSTANCE_ID; stores condition-to-instance associations.
• FND_SECURITY_GROUPS — referenced by this table through SECURITY_GROUP_ID.

Together these relationships place ASO_SUP_TMPL_INSTANCE at the center of a template-instantiation cluster, with dependent detail tables joined on TEMPLATE_INSTANCE_ID.