Search Results xtr_layout_where_clause




Overview
Explore in AI Chat

Overview

XTR_LAYOUT_WHERE_CLAUSE is a Treasury (XTR) module configuration table in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the SQL SELECT statement and WHERE clause fragments that drive the generation of deal confirmation documents and layout views for each template type. In practice, the table acts as the rule repository that determines which deal, confirmation, or counterparty records are retrieved when a confirmation template is rendered by the ETRM (Enterprise Treasury and Risk Management) layout engine.

The ETRM metadata lists the primary key as XTR_LAYOUT_WHERE_CLAUSE_PK, defined on TEMPLATE_TYPE. The physical schema documented for 12.2.2 contains 13 columns. A unique index, XTR_LAYOUT_WHERE_CLAUSE_U1, is defined on (ACTION_TYPE, ZD_EDITION_NAME), which serves as the business-key candidate that identifies a specific clause row within the edition-aware model. Based on the mined foreign-key structure, the object is classified heuristically as hub-leaning in Data Vault terms; the catalogued relationships suggest treating TEMPLATE_TYPE as the natural business key of a hub, with dependent descriptive attributes carried alongside it. This classification is a modeling suggestion, not a physical constraint enforced by the application.

Key Information Stored

The most significant columns in XTR_LAYOUT_WHERE_CLAUSE are as follows:

  • TEMPLATE_TYPE — The primary key column and the core identifier linking each clause to a specific confirmation or layout template type.
  • ACTION_TYPE — Part of the unique business key; references XTR_DEAL_CONFO_TYPES and determines the action or confirmation category the clause applies to.
  • SELECT_CLAUSE_TEXT — The SQL SELECT fragment used to retrieve the data elements for the layout.
  • WHERE_CLAUSE_TEXT — The SQL WHERE fragment used to filter records for the given template type.
  • CLAUSE_TEXT — The combined or composite clause text applied to the layout definition.
  • CONFO_OR_VIEW — Indicates whether the clause governs a confirmation document or a view layout.
  • CLIENT_OR_CPARTY_LETTER — Distinguishes client-facing versus counterparty-facing letter formats.
  • VIEW_SUBSECTION — Identifies the specific subsection of a view to which the clause applies.
  • CURRENT_TEMPLATE_DEFAULT — Flags whether the template type is the active default for its category.
  • TEMPLATE_CREATED — Indicates whether the template has been created for this clause entry.
  • SIGNED_OFF — Tracks approval or completion status of the clause configuration.
  • COMMENTS — Free-text notes maintained by the configuration owner.
  • ZD_EDITION_NAME — The edition identifier supporting the edition-based redefinition model documented in 12.2.2.

Common Use Cases and Queries

Typical use cases include auditing the filtering logic behind confirmation templates, troubleshooting missing or duplicated deal confirmations, and reporting on which template types are currently active defaults. A common query pattern joins the clause table to its parent template definition:

  • SELECT w.TEMPLATE_TYPE, w.ACTION_TYPE, w.SELECT_CLAUSE_TEXT, w.WHERE_CLAUSE_TEXT FROM XTR.XTR_LAYOUT_WHERE_CLAUSE w WHERE w.CURRENT_TEMPLATE_DEFAULT = 'Y' AND w.SIGNED_OFF = 'Y';
  • SELECT l.TEMPLATE_TYPE, l.NAME, w.WHERE_CLAUSE_TEXT FROM XTR.XTR_LAYOUT_TEMPLATE l, XTR.XTR_LAYOUT_WHERE_CLAUSE w WHERE l.TEMPLATE_TYPE = w.TEMPLATE_TYPE AND w.ACTION_TYPE = :action_type;

Reporting extracts frequently target ACTION_TYPE and CONFO_OR_VIEW to group clauses by business function, while CLAUSE_TEXT and COMMENTS are reviewed during configuration migration or post-patch validation.

Related Objects

  • XTR_DEAL_CONFO_TYPES — Referenced by XTR_LAYOUT_WHERE_CLAUSE.ACTION_TYPE; defines the valid action categories for clause rows.
  • XTR_LAYOUT_TEMPLATE — References this table via XTR_LAYOUT_TEMPLATE.TEMPLATE_TYPE; the parent template definition.
  • XTR_LAYOUT_VALUES — References this table via XTR_LAYOUT_VALUES.TEMPLATE_TYPE; holds substituted layout values.
  • XTR_LAYOUT_WHERE_CLAUSE_PK — The primary key constraint enforcing uniqueness on TEMPLATE_TYPE.
  • XTR_LAYOUT_WHERE_CLAUSE_U1 — The unique index on (ACTION_TYPE, ZD_EDITION_NAME) supporting the edition-aware lookup path.