Results for “calendar_code”

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

Overview

MSC_PHUB_MFG_CAL_DATES_MV is a materialized-view-backed table owned by the APPS schema and registered in Oracle EBS under the FND — Application Object Library product. In Oracle EBS 12.1.1 and 12.2.2 it functions as the pre-aggregated manufacturing calendar date dimension used by the Advanced Supply Chain Planning (ASCP) and planning hub (MSC) components, translating raw shop-floor calendar definitions into ready-to-join rows that planning engines and reports can consume without recomputing calendar arithmetic at runtime. The "MV" suffix confirms it is populated from an underlying materialized view refresh rather than maintained transactionally, so its contents should be treated as read-only and periodically refreshed by the concurrent program that owns the refresh group. The ETRM metadata lists eight documented columns, a single unique index (MSC_PHUB_MFG_CAL_DATES_MV_U1 on CALENDAR_DATE), and one foreign key to BOM_EXCEPTION_SETS. The heuristic Data Vault classification mined from the FK structure marks this object as standalone. As a modeling suggestion, this is consistent with its role as a derived calendar dimension: it carries no dependent child relationships of its own, and the single outward reference to BOM_EXCEPTION_SETS behaves more like a link to a lookup than a true hub-parent dependency. It should therefore be modeled as a reference/date dimension rather than a transactional hub or satellite.

Key Information Stored

The table's business purpose is to provide one row per manufacturing calendar date, with all commonly needed period-boundary offsets precomputed. The most important columns are:

• CALENDAR_DATE — the business-key candidate and the only documented unique index (MSC_PHUB_MFG_CAL_DATES_MV_U1). It is the grain of the table and the primary join key for any date-based query.
• CALENDAR_CODE — identifies which manufacturing calendar the row belongs to, allowing multiple calendars to coexist within the same materialized view.
• MFG_CAL_WEEK_START_DATE — the first day of the manufacturing week containing CALENDAR_DATE, used for weekly bucket aggregation in planning buckets.
• MONTH_START_DATE — the first day of the calendar month, supporting monthly rollups.
• FISCAL_PERIOD_START_DATE — the first day of the fiscal period, used when planning or costing must align to the accounting calendar rather than the Gregorian month.
• PERIOD_SET_NAME — the accounting period set associated with the fiscal period, linking calendar dates to GL period definitions.
• EXCEPTION_SET_ID — the foreign key to BOM_EXCEPTION_SETS, indicating which exception set governs workday/holiday treatment for the date.
• DAY_COUNT — a numeric day counter used for interval arithmetic and bucket offset calculations.

No dedicated surrogate primary key column is documented; the unique index on CALENDAR_DATE serves as the business-key candidate. Where multiple calendar codes exist, uniqueness is effectively (CALENDAR_CODE, CALENDAR_DATE), so queries should qualify both columns.

Common Use Cases and Queries

Typical uses include converting planning bucket boundaries, validating that a proposed schedule date falls on a working day, and supplying a fiscal-period bridge to supply chain reports. A common pattern groups demand or supply by fiscal period:

• Join to transactional tables on CALENDAR_DATE to obtain FISCAL_PERIOD_START_DATE and PERIOD_SET_NAME for GL-aligned reporting.
• Filter by CALENDAR_CODE and a date range to build planning buckets using MFG_CAL_WEEK_START_DATE.
• Resolve EXCEPTION_SET_ID against BOM_EXCEPTION_SETS to determine holidays or shutdown days.
• Use DAY_COUNT differences to measure elapsed manufacturing days between two events.

A representative query: SELECT calendar_date, fiscal_period_start_date, period_set_name FROM msc_phub_mfg_cal_dates_mv WHERE calendar_code = :code AND calendar_date BETWEEN :start_date AND :end_date;. Because the object is a materialized view, users should avoid DML and confirm the refresh schedule before relying on near-real-time calendar changes. The searched artifact "zmch 30.05.2026.xls" is an external spreadsheet and is not a database object; it does not appear in the ETRM metadata for this table.

Related Objects

The documented relationship data identifies one explicit foreign key. The most significant related objects are:

• BOM_EXCEPTION_SETS — referenced via EXCEPTION_SET_ID; supplies the exception-set definition governing workday and holiday rules.
• MSC_PHUB_MFG_CAL_DATES_MV_U1 — the unique index on CALENDAR_DATE that enforces the business key and drives lookup performance.
• MSC calendar and planning bucket tables — consume CALENDAR_DATE and the week/month start columns when building planning time fences.
• GL period definitions (PERIOD_SET_NAME) — the fiscal columns bridge this table to the accounting calendar.
• ASCP planning engine reports and concurrent programs — depend on the materialized view being freshly refreshed for accurate bucket boundaries.