Results for “bucket_descriptor”

27 results




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

Overview

PO.CHV_HORIZONTAL_SCHEDULES is a denormalized, presentation-oriented table within the Oracle E-Business Suite sourcing and supplier scheduling modules. As documented in the ETRM metadata for release 12.2.2, it stores the horizontal, bucketed representation of schedule item orders and is explicitly "designed for direct selection into the Workbench and reports." Unlike transactional base tables that normalize each bucket into its own row, this table pivots bucket values across a wide set of generic COLUMN1 through COLUMN60 attributes, allowing the scheduling Workbench to fetch an entire horizontal timeline for a schedule item in a single query.

The object resides in the PO schema (physical owner PO, design data FND Design Data CHV.CHV_HORIZONTAL_SCHEDULES) and is stored in the APPS_TS_TX_DATA tablespace with PCT Free 10. Its status is VALID in the documented schema, which contains 73 columns. From a Data Vault modeling perspective, the heuristic classification is standalone. Because the table is a derived, rebuildable projection of upstream schedule and demand data rather than a source-of-truth entity, it is best modeled as a derived satellite or reporting structure rather than a hub or link. There is no modeled hub relationship; the only documented foreign key points SCHEDULE_ITEM_ID to CHV_PURGE_SCHEDULE_LIST, which is a purge-support relationship rather than a business lineage.

Key Information Stored

The table is anchored by two identifier columns. SCHEDULE_ID is the schedule header unique identifier, and SCHEDULE_ITEM_ID is the schedule item unique identifier. Both are indexed non-uniquely. CHV_HORIZONTAL_SCHEDULES_N1 covers SCHEDULE_ID, and CHV_HORIZONTAL_SCHEDULES_N2 covers SCHEDULE_ITEM_ID, both in the APPS_TS_TX_IDX tablespace. Neither index is unique, so no single-column surrogate primary key is enforced by the documented indexes; the effective row identity is the combination of SCHEDULE_ID, SCHEDULE_ITEM_ID, ROW_SELECT_ORDER, and ROW_TYPE.

ROW_TYPE (VARCHAR2, 25) determines the semantic meaning of the row. Documented options are RELEASE_QUANTITY, FORECAST_QUANTITY, CUM_QUANTITY, TOTAL_QUANTITY, BUCKET_DESCRIPTOR, BUCKET_START_DATE, and BUCKET_END_DATE. This column is central to the user's search term: BUCKET_DESCRIPTOR rows carry the human-readable label for each time bucket, while BUCKET_START_DATE and BUCKET_END_DATE rows delimit the bucket period. ROW_SELECT_ORDER is a numeric sequence that fixes display order for the seven row types in the sequence BUCKET_DESCRIPTOR, BUCKET_START_DATE, RELEASE_QUANTITY, FORECAST_QUANTITY, TOTAL_QUANTITY, CUM_QUANTITY, BUCKET_END_DATE.

The wide COLUMN1 through COLUMN60 attributes hold the "Bucket value" for each horizontal period. Their content depends on ROW_TYPE: for quantity row types they carry numeric strings, while for BUCKET_DESCRIPTOR they carry the bucket label. Standard Who columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) are present, along with concurrent program context columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE, indicating the table is populated by a concurrent program that rebuilds the horizontal projection.

Common Use Cases and Queries

The most common access pattern is retrieving the full horizontal timeline for a schedule item, ordered for Workbench display:

  • SELECT ROW_TYPE, ROW_SELECT_ORDER, COLUMN1, COLUMN2, COLUMN3 FROM PO.CHV_HORIZONTAL_SCHEDULES WHERE SCHEDULE_ITEM_ID = :item_id ORDER BY ROW_SELECT_ORDER;
  • Filtering to bucket descriptors only: WHERE ROW_TYPE = 'BUCKET_DESCRIPTOR' ORDER BY ROW_SELECT_ORDER — this returns the bucket labels aligned to the column positions used by the quantity rows.
  • Reporting release versus forecast by bucket: selecting ROW_TYPE IN ('RELEASE_QUANTITY','FORECAST_QUANTITY') and unpivoting COLUMN1..COLUMN60 to compare committed releases against forecast demand per period.
  • Header-level extraction joining SCHEDULE_ID to the schedule header, and item-level extraction filtering on SCHEDULE_ITEM_ID using index N2 or N1 respectively.

Because the table is a rebuildable projection, reporting queries should avoid DML and rely on the owning concurrent program to refresh content. Large horizontal scans should filter on SCHEDULE_ID or SCHEDULE_ITEM_ID to leverage the two documented non-unique indexes.

Related Objects

The documented relationship data identifies the principal dependencies and access paths for this table:

  • PO.CHV_PURGE_SCHEDULE_LIST — referenced by the foreign key CHV_HORIZONTAL_SCHEDULES.SCHEDULE_ITEM_ID → CHV_PURGE_SCHEDULE_LIST.SCHEDULE_ITEM_ID, used to coordinate purge of scheduling data.
  • Schedule header and schedule item base tables in the CHV/PO scheduling family, joined on SCHEDULE_ID and SCHEDULE_ITEM_ID to source the horizontal projection and to resolve descriptive header and item attributes.
  • The Supplier Scheduling Workbench (CHV) UI, which selects directly from this table using SCHEDULE_ID/SCHEDULE_ITEM_ID and orders by ROW_SELECT_ORDER.
  • Concurrent programs (identified by REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID) that populate and refresh CHV_HORIZONTAL_SCHEDULES.
  • CHV_HORIZONTAL_SCHEDULES_N1 and _N2 indexes in APPS_TS_TX_IDX, which are the primary performance access paths for header and item level queries.

Together these objects establish CHV_HORIZONTAL_SCHEDULES as a purpose-built, horizontally pivoted reporting cache whose semantics are governed by ROW_TYPE and whose primary search entry points are SCHEDULE_ID and SCHEDULE_ITEM_ID, with BUCKET_DESCRIPTOR rows supplying the period labels for the bucketed quantities.