Search Results week_start_end

Overview

The view APPS.GMP_MFG_TIME_WEEKS_V is a database object owned by the APPS schema within the Oracle E-Business Suite environment, specifically associated with the GMP - Process Manufacturing Process Planning product family. Its status is VALID in both Oracle EBS 12.1.1 and 12.2.2. Functionally, the view is a supporting component rather than an end-user reporting object. According to the Oracle ETRM documentation, the query selects all calendars defined in the OPM (Oracle Process Manufacturing) module and prepares weekly data for the GMP_SR_MFG_TIME_V view. In short, GMP_MFG_TIME_WEEKS_V exists to pre-aggregate calendar data into week-level intervals that are subsequently consumed internally by GMP_SR_MFG_TIME_V. Because it is a supporting view, it is not intended for direct customer querying in standard flows, although it remains accessible for diagnostic and custom reporting purposes. Its role in reporting and integration is to standardize time-bucketing across manufacturing calendars so that downstream planning and scheduling views can operate on uniform week boundaries.

Underlying Base Objects

The documented base object referenced by this view is MR_SHCL_DTL, exposed within the APPS schema through a synonym. MR_SHCL_DTL is the OPM shop calendar detail table, holding the individual calendar dates and their associated calendar identifiers that define working and non-working days for each manufacturing calendar. The view's construction depends entirely on this table. The inner query derives a group member by subtracting the day-of-week offset (via TO_NUMBER(TO_CHAR(MSD.CALENDAR_DATE, 'D') - 1)) from each calendar date, thereby aligning dates into week groupings. The outer query then counts the members of each group and captures the minimum and maximum dates to yield the week start and week end dates. This two-stage aggregation transforms daily calendar rows into weekly buckets, one set per CALENDAR_ID. No other documented base objects are referenced; the view is a single-source derivation over MR_SHCL_DTL.

Key Columns

• CALENDAR_ID — Identifies the manufacturing calendar from which the week data is derived. Each row is uniquely associated with a calendar defined in OPM.
• WEEK_NO — The sequential week number within the calendar, generated as a count of aggregated group members. It serves as the ordinal identifier for each week bucket.
• WEEK_START_DATE — The first calendar date belonging to the week grouping, computed as the minimum calendar date within the group.
• WEEK_END_DATE — The last calendar date belonging to the week grouping, computed as the maximum calendar date within the group.

Together these four columns provide a compact representation of each week for each OPM calendar, enabling downstream views such as GMP_SR_MFG_TIME_V to resolve time periods without repeatedly scanning the detailed daily calendar table.

Common Use Cases and Queries

The principal use case is the internal resolution of manufacturing time periods for process planning views. A secondary use case is diagnostic validation of OPM calendar definitions, ensuring that week boundaries are correctly generated before relying on higher-level planning reports.

A sample query to inspect all weeks for a given calendar is:

• SELECT calendar_id, week_no, week_start_date, week_end_date FROM apps.gmp_mfg_time_weeks_v WHERE calendar_id = :p_calendar_id ORDER BY week_no;

To validate week contiguity and detect gaps or overlaps in calendar coverage:

• SELECT calendar_id, COUNT(*) total_weeks, MIN(week_start_date), MAX(week_end_date) FROM apps.gmp_mfg_time_weeks_v GROUP BY calendar_id;

Because the view is documented as internally used by GMP_SR_MFG_TIME_V, customizations should treat it as a read-only reference. Direct updates are impossible, and any logic changes to week bucketing must originate from the underlying calendar definitions in MR_SHCL_DTL.