Results for “psb_summary_templates_u1”

5 results




Overview
Explore in AI Chat

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

Overview

PSB.PSB_SUMMARY_TEMPLATES is a table in the Public Sector Budgeting (PSB) schema of Oracle E-Business Suite, documented as VALID in both release 12.1.1 and 12.2.2. It stores the list of General Ledger summary templates for which summary budget totals are maintained in Public Sector Budgeting. In effect, it defines the subset of Oracle Public Sector General Ledger summary template definitions that PSB is permitted to roll up budget data against.

The table resides in the APPS_TS_TX_DATA tablespace, with PCTFREE set to 10. From a Data Vault modeling perspective, the metadata heuristic classifies this object as standalone, suggesting it behaves as a reference or hub-style entity. The template identifier and set of books together form the natural business key, and there are no documented foreign key dependencies outward to other database objects, so it functions as an independent reference list rather than a transactional link or descriptive satellite.

Key Information Stored

The table contains nine documented columns. The most significant are:

  • TEMPLATE_ID (NUMBER, 15) — the General Ledger summary template unique identifier, and the leading column of the composite primary key.
  • SET_OF_BOOKS_ID (NUMBER, 15) — identifies the General Ledger Set of Books for which the summary template is valid; the second column of the primary key.
  • TEMPLATE_NAME (VARCHAR2, 100) — the General Ledger summary template name, providing the human-readable label.
  • EFFECTIVE_START_DATE (DATE) — the date from which the template becomes effective. Per the documented comment, this date is used when defining a subset of General Ledger summary templates for rollups in Public Sector Budgeting, translating the START_PERIOD_NAME in GL_SUMMARY_TEMPLATES by mapping it to a PSB period base.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE — the standard Who columns that audit row creation and modification.

The surrogate primary key is PSB_SUMMARY_TEMPLATES_PK (TEMPLATE_ID, SET_OF_BOOKS_ID). A unique index, PSB_SUMMARY_TEMPLATES_U1, enforces uniqueness on the same two columns (SET_OF_BOOKS_ID, TEMPLATE_ID) in APPS_TS_TX_IDX; this index is the object referenced by the search term psb_summary_templates_u1.

Common Use Cases and Queries

The primary use case is identifying which summary templates are available for budget rollups within a given set of books. Typical queries join the table back to General Ledger summary template definitions or filter by effective date to determine currently active templates.

  • Listing all templates for a set of books: SELECT TEMPLATE_ID, TEMPLATE_NAME FROM PSB.PSB_SUMMARY_TEMPLATES WHERE SET_OF_BOOKS_ID = :sob;
  • Identifying active templates: filter on EFFECTIVE_START_DATE <= SYSDATE.
  • Validating that a specific template is registered before a PSB rollup is run, keyed on TEMPLATE_ID and SET_OF_BOOKS_ID.
  • Audit reporting using the standard Who columns to trace which user added a template.

Because the unique index PSB_SUMMARY_TEMPLATES_U1 covers (SET_OF_BOOKS_ID, TEMPLATE_ID), most lookups can be satisfied as an index range scan without touching the base table.

Related Objects

The documented metadata states that this table does not reference any database object; the relationship is one-directional. Significant related objects include:

  • APPS.PSB_SUMMARY_TEMPLATES — the APPS synonym/view layer over the base PSB table, referenced by the dependency data and used by application code.
  • GL_SUMMARY_TEMPLATES — the General Ledger source of summary template definitions, related by TEMPLATE_ID and SET_OF_BOOKS_ID, whose START_PERIOD_NAME maps to the PSB EFFECTIVE_START_DATE.
  • GL_SETS_OF_BOOKS — joined on SET_OF_BOOKS_ID to resolve the ledger context.
  • FND_ID_FLEX_STRUCTURES / GL period tables — used indirectly when translating summary periods into PSB period bases.
  • PSB budget rollup and summary total tables — maintained against the templates recorded here.

Because the table is classified as standalone, joins should be driven from PSB_SUMMARY_TEMPLATES outward rather than through declared foreign keys.