Search Results xtr_layout_where_clause_u1

Overview

The XTR.XTR_LAYOUT_WHERE_CLAUSE table is a seed data object within the Oracle E-Business Suite Treasury (ETRM) module, owned by the XTR schema and registered under FND Design Data as XTR.XTR_LAYOUT_WHERE_CLAUSE. Its purpose is to store the SQL fragments that drive the dynamic generation of confirmation letter templates. Specifically, each row holds a SQL select statement used to construct a database view for a given template type, together with a corresponding where clause used to filter confirmation letters by deal number, transaction number, or interest rate swap reference. The object resides in the APPS_TS_SEED tablespace, reflecting its role as seeded configuration data rather than transactional data.

Under the heuristic Data Vault classification derived from its foreign key structure, this table leans toward a hub. This classification is offered as a modeling suggestion: the table anchors a small set of descriptive attributes keyed by a stable business identifier, with lightweight references outward. It is not a transactional ledger, and its low rate of change is consistent with a reference or configuration hub used by downstream template and value objects.

Key Information Stored

The table contains 13 documented columns. The most significant are the columns that define template behavior and the filtering logic applied when confirmation letters are produced.

• TEMPLATE_TYPE (VARCHAR2, 50) — The template type identifier. It is the column named by the documented primary key XTR_LAYOUT_WHERE_CLAUSE_PK and is the principal join key referenced by dependent tables.
• ACTION_TYPE (VARCHAR2, 50) — The deal action type. Along with ZD_EDITION_NAME, it forms the unique business-key candidate index XTR_LAYOUT_WHERE_CLAUSE_U1.
• ZD_EDITION_NAME (VARCHAR2, 30) — The edition name, part of the unique index XTR_LAYOUT_WHERE_CLAUSE_U1 and therefore a business-key candidate paired with ACTION_TYPE.
• WHERE_CLAUSE_TEXT (VARCHAR2, 2000) — The SQL where clause used to filter confirmation letters.
• CLAUSE_TEXT (VARCHAR2, 2000) — The SQL select statement used when creating the view for a template type.
• CLIENT_OR_CPARTY_LETTER (VARCHAR2) — A flag indicating whether the confirmation is a client letter (C) or counterparty letter (CP).
• CONFO_OR_VIEW (VARCHAR2) — A flag distinguishing a confirmation template (C) from a view template (F); Treasury currently supports only confirmation templates.
• SELECT_CLAUSE_TEXT (VARCHAR2, 2000) — Documented as not currently used.
• COMMENTS (VARCHAR2, 500) — Documented as not currently used.
• SIGNED_OFF, CURRENT_TEMPLATE_DEFAULT, TEMPLATE_CREATED, and VIEW_SUBSECTION — Documented placeholder attributes, not currently used.

The surrogate-style primary key is TEMPLATE_TYPE via XTR_LAYOUT_WHERE_CLAUSE_PK, while ACTION_TYPE plus ZD_EDITION_NAME constitute the documented unique business-key candidate. A foreign key maps ACTION_TYPE to XTR_DEAL_CONFO_TYPES.

Common Use Cases and Queries

Typical usage centers on retrieving the filtering and select logic for a confirmation letter template, and on reconciling which template types exist for a given action type and edition.

• Look up the select and where clause for a specific template type:

  SELECT template_type, clause_text, where_clause_text
  FROM   xtr.xtr_layout_where_clause
  WHERE  template_type = :template_type;

• Identify the unique business key for a template:

  SELECT action_type, zd_edition_name, template_type
  FROM   xtr.xtr_layout_where_clause
  WHERE  action_type = :action_type
  AND    zd_edition_name = :edition_name;

• Report which confirmation letters are client-facing versus counterparty-facing:

  SELECT template_type, client_or_cparty_letter, confo_or_view
  FROM   xtr.xtr_layout_where_clause;

Because the clause columns store dynamic SQL, extraction queries should treat CLAUSE_TEXT and WHERE_CLAUSE_TEXT as text payloads for review rather than execution in ad hoc reports.

Related Objects

The following documented relationships define the object's dependency graph.

• XTR.XTR_DEAL_CONFO_TYPES — Referenced by this table through the foreign key on ACTION_TYPE; join on action_type to resolve deal action type descriptions.
• XTR.XTR_LAYOUT_TEMPLATE — References this table via TEMPLATE_TYPE, joining template_type to the primary key TEMPLATE_TYPE.
• XTR.XTR_LAYOUT_VALUES — Also references this table via TEMPLATE_TYPE, linking template values to their parent template definition.

Together these objects form the confirmation letter template framework: this table supplies the query logic, while the dependent template and value tables consume it to render the final output.