Search Results msd_time_v




Overview
Explore in AI Chat

Overview

MSD_TIME_V is a VALID database view owned by the APPS schema, delivered as part of the MSD – Demand Planning product module in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to expose the definition of time hierarchies for every hierarchy type supported by Demand Planning: the Gregorian, Manufacturing, and fiscal calendars. Rather than storing data itself, the view projects a denormalized, reporting-friendly representation of one or more calendars, so that each row corresponds to a single day and carries its full set of parent time-period attributes (year, quarter, month, week, and day) plus the descriptive text for each level.

Because it flattens year-to-day attributes into one structure, MSD_TIME_V is a foundational reference object for planning and reporting logic. It allows SQL, concurrent programs, and analytics to resolve a transaction or demand date into the correct planning bucket without repeatedly joining separate hierarchy levels. The deterministic row ordering and the NVL on WORKING_DAY make the view suitable for consistent, repeatable extracts and integrations.

Underlying Base Objects

The documented referenced base object is MSD_TIME, accessed through a SYNONYM. The view is defined as an inline query over MSD_TIME wrapped in an outer SELECT, with two notable behaviors derived from the view text:

  • The inner query selects the calendar and period columns from MSD_TIME and applies ORDER BY CALENDAR_CODE, DAY, guaranteeing a chronological sequence within each calendar.
  • The outer query assigns ROWNUM as the SEQ_NUM column (listed in the metadata as SEQ_NUM) and applies NVL(WORKING_DAY, 'YES'), so any NULL working-day flag defaults to 'YES'.

Consequently, MSD_TIME_V is a pure projection and re-sequencing layer over MSD_TIME; no aggregation or filtering of dates occurs, so any calendar maintained in the base table appears here in full.

Key Columns

The view exposes one row per day, with repeating parent-period values. The critical columns include:

Common Use Cases and Queries

The primary use case is resolving a date to its planning period boundaries, particularly the quarter. A typical query that answers the "quarter_start_date" search is:

  • SELECT CALENDAR_CODE, DAY, QUARTER, QUARTER_START_DATE, QUARTER_END_DATE FROM APPS.MSD_TIME_V WHERE CALENDAR_CODE = :code AND DAY BETWEEN :from_date AND :to_date ORDER BY DAY;
  • Ranking or comparing period definitions: SELECT DISTINCT CALENDAR_CODE, YEAR, QUARTER, QUARTER_START_DATE, QUARTER_END_DATE FROM APPS.MSD_TIME_V ORDER BY CALENDAR_CODE, QUARTER_START_DATE;
  • Working-day filtering: SELECT DAY, QUARTER_START_DATE FROM APPS.MSD_TIME_V WHERE WORKING_DAY = 'YES';

Typical scenarios include loading period start/end dates into planning extracts, validating that demand transactions fall within expected quarters, and driving date-dimension hierarchies in Demand Planning reports and integrations.