Results for “period_category_id”

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

Overview

CAC_SR_PERIOD_CATS_B is a CRM Foundation (JTF) base table that stores non-translated data for period categories within the Oracle E-Business Suite Service (SR) scheduling framework. Period categories define logical groupings of time periods used in service resource scheduling, such as availability windows, capacity buckets, and exception handling rules. The "_B" suffix identifies this as the base-language table in a translated pair; the corresponding translations reside in CAC_SR_PERIOD_CATS_TL, which follows the standard EBS multi-language table pattern where language-independent attributes live in the _B table and translated descriptive text lives in the _TL table.

Based on the documented foreign key structure, the table exhibits hub-leaning characteristics under a Data Vault classification heuristic. It is referenced by multiple downstream tables (CAC_SR_EXCEPTIONS_B, CAC_SR_PERIODS_B, CAC_SR_PERIOD_CATS_TL) through the PERIOD_CATEGORY_ID column, which is consistent with a hub entity whose surrogate key is broadcast to dependent satellites and links. This classification should be treated as a modeling suggestion rather than a formal designation.

Key Information Stored

The table is documented with ten physical columns in the ETRM 12.2.2 schema. The most significant include:

• PERIOD_CATEGORY_ID — The surrogate primary key (CAC_SR_PERIOD_CATS_B_PK) that uniquely identifies each period category and serves as the join column to all dependent tables.
• FREE_BUSY_TYPE — Indicates whether the associated period represents free or busy time, driving availability logic in scheduling engines.
• DISPLAY_COLOR — Stores the UI color assigned to the category for visual rendering in scheduling calendar interfaces.
• SHOW_PERIOD_DETAILS — A flag controlling whether detailed period information is exposed to end users.
• ZD_EDITION_NAME — The editioning column introduced in EBS 12.2 to support online patching and multi-edition data isolation.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS audit columns capturing who created and last modified each row and when.

The unique index CAC_SR_PERIOD_CATS_B_U1 on (PERIOD_CATEGORY_ID, ZD_EDITION_NAME) is the business-key candidate and reflects the editioning architecture. Notably, the table contains no descriptive name column; all user-facing text is deferred to the _TL translation table.

Common Use Cases and Queries

Period categories are queried most often during scheduling engine execution, calendar UI rendering, and availability reporting. A typical lookup joins the base table to its translation to retrieve the display name:

• Category lookup with translation: SELECT b.period_category_id, t.period_category_name, b.free_busy_type, b.display_color FROM jtf.cac_sr_period_cats_b b, jtf.cac_sr_period_cats_tl t WHERE b.period_category_id = t.period_category_id AND t.language = USERENV('LANG');
• Availability configuration review: Query FREE_BUSY_TYPE to list categories flagged as free or busy for a given resource schedule.
• Calendar rendering: Retrieve DISPLAY_COLOR and SHOW_PERIOD_DETAILS to configure visual output in custom scheduling dashboards.
• Edition-aware queries (12.2): Include ZD_EDITION_NAME filtering where editioned data access is required during online patching cycles.

Related Objects

• CAC_SR_PERIODS_B — References CAC_SR_PERIOD_CATS_B.PERIOD_CATEGORY_ID; holds the actual period definitions belonging to each category.
• CAC_SR_EXCEPTIONS_B — References CAC_SR_PERIOD_CATS_B.PERIOD_CATEGORY_ID; stores exception records scoped to a period category.
• CAC_SR_PERIOD_CATS_TL — References CAC_SR_PERIOD_CATS_B.PERIOD_CATEGORY_ID; supplies translated names and descriptions.
• CAC_SR_PERIOD_CATS_B_PK — Primary key constraint on PERIOD_CATEGORY_ID.
• CAC_SR_PERIOD_CATS_B_U1 — Unique index on (PERIOD_CATEGORY_ID, ZD_EDITION_NAME).

These dependencies confirm the table's central role in the Service scheduling period configuration model.