Search Results week_start_end

Overview

APPS.GMP_MFG_TIME_WEEKS_V is a reporting view in the Oracle E-Business Suite manufacturing and process manufacturing (OPM/GMP) schema. It presents a derived, week-level interpretation of a manufacturing calendar by aggregating individual calendar dates stored in the underlying schedule detail table into discrete weekly buckets. Each row produced by the view represents a single week within a given calendar, identified by a calendar_id and a sequential week_no.

The view exists to bridge the granularity gap between the daily calendar records maintained by the manufacturing scheduling engine and the weekly reporting horizons commonly required by planning, capacity, and shop-floor reporting. Rather than consuming and aggregating raw daily dates in every report or interface, consumers can query this view to obtain pre-computed week boundaries. Its role is therefore primarily that of a reporting and integration conformed dimension: any process needing to group production activity, resources, or demand by manufacturing week can join to this view on its week start and end columns.

Underlying Base Objects

The view is defined over a single documented base object: the synonym MR_SHCL_DTL. This table holds the schedule detail — that is, the individual calendar dates that make up a manufacturing calendar. The synonym resolves to the underlying OPM schedule calendar detail table owned by the manufacturing application schema.

The view text operates in two nested aggregation stages over this one table. In the inner inline view (aliased WEEK_START_END), each calendar date is mapped to the first day of its week using the expression calendar_date - (TO_NUMBER(TO_CHAR(calendar_date,'D') - 1)), which relies on the D day-of-week format element to compute the offset to the start of the week. The inner query groups by calendar_id and this computed group member, taking the MIN and MAX calendar date to derive the week start and week end respectively. The outer query then re-joins back to MR_SHCL_DTL and derives a running week_no via a COUNT aggregation ordered by the derived dates. The result is a deduplicated set of week windows per calendar.

Key Columns

• calendar_id — Identifier of the manufacturing calendar to which the week belongs. This is the primary grouping key and is used to join back to calendar header definitions and to other scheduling objects.
• week_no — A sequence number for the week within the calendar, calculated as a count of contributing session detail rows. It provides an ordinal position rather than a calendar-year week number, so interpretation should always be scoped to a specific calendar_id.
• week_start_date — The first calendar date in the computed seven-day window. This is the column most frequently searched for and is used for range filtering, grouping, and range joins.
• week_end_date — The last calendar date in the computed window. Together with week_start_date, it defines an inclusive date range for the reporting bucket.

Common Use Cases and Queries

Typical scenarios include building weekly capacity reports, bucketing scheduled production or shop-floor transactions into manufacturing weeks, and supplying a week dimension for planning extracts. A common filter is to constrain to a single calendar and a date window:

• Retrieve all weeks for a calendar: 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_start_date;
• Find the week containing a given date: SELECT week_no, week_start_date, week_end_date FROM apps.gmp_mfg_time_weeks_v WHERE :p_date BETWEEN week_start_date AND week_end_date;
• Join production or transaction data to weeks using an inclusive range predicate on week_start_date and week_end_date to aggregate measures by manufacturing week.

Because week_no is derived by counting and is not a calendar-year attribute, reports should always carry calendar_id alongside it and prefer ordering and filtering by week_start_date for determinism.