Search Results bom_org_cal_weeks_view




Overview
Explore in AI Chat

Overview

BOM_ORG_CAL_WEEKS_VIEW is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Bills of Material (BOM) product. The view is documented in the E-Business Suite Technical Reference Manual (ETRM) for releases 12.1.1 and 12.2.2 with the description "Week start dates." Its purpose is to expose the week start dates defined within a manufacturing organization's workday calendar, resolved per organization rather than per calendar code. Because calendar definitions in Oracle Manufacturing are stored at the calendar level and then associated to organizations through MTL_PARAMETERS, this view performs the join that maps calendar-level week definitions to the specific organization that uses them.

The view is read-only and is not used by the BOM scheduling engine directly for transactional processing; instead it serves reporting, integration, and custom extension scenarios. Consultants typically encounter it when building queries or interfaces that must enumerate the working weeks of a plant, engineering organization, or any other inventory organization that references a manufacturing calendar.

Underlying Base Objects

The view text documents a two-table join over synonyms that resolve to the following base objects:

  • BOM_CAL_WEEK_START_DATES — the BOM table that stores the sequence of week start dates generated for a given calendar and exception set. Each row represents one week within the calendar's valid horizon.
  • MTL_PARAMETERS — the inventory organization parameter table, which stores the CALENDAR_CODE and CALENDAR_EXCEPTION_SET_ID assigned to each organization.

The join condition links the two sources on calendar identity: MTL.CALENDAR_CODE = BOM.CALENDAR_CODE and MTL.CALENDAR_EXCEPTION_SET_ID = BOM.EXCEPTION_SET_ID. The effect is that every organization inheriting a given calendar receives its own row set from the shared week definitions. This design avoids duplicating weekly rows per organization in the base table while still allowing organization-scoped querying.

Key Columns

  • ORGANIZATION_ID — the inventory organization identifier from MTL_PARAMETERS. This is the primary filter column for most queries and joins to HR_ALL_ORGANIZATION_UNITS or ORG_ORGANIZATION_DEFINITIONS.
  • WEEK_START_DATE — the date on which the week begins for that organization's calendar, adjusted for the calendar's exception set.
  • SEQ_NUM — the sequence number of the week within the calendar's generated horizon, enabling ordered traversal from earliest to latest week.
  • NEXT_DATE — the start date of the following week; useful for interval-based calculations without a self-join.
  • PRIOR_DATE — the start date of the preceding week, providing the same benefit in the reverse direction.

Together, SEQ_NUM, PRIOR_DATE, and NEXT_DATE allow forward and backward navigation across the weekly time fence without additional lookups.

Common Use Cases and Queries

Typical scenarios include capacity or workload reporting by week, validating that a calendar's exception set has been applied correctly, and populating custom planning tables with plant-specific week buckets. A representative query listing the weeks for a specific organization is:

  • SELECT organization_id, seq_num, week_start_date, next_date, prior_date FROM apps.bom_org_cal_weeks_view WHERE organization_id = :org_id ORDER BY seq_num;

To find the current week for every organization, a filter on the date range is applied:

  • SELECT organization_id, week_start_date FROM apps.bom_org_cal_weeks_view WHERE SYSDATE BETWEEN week_start_date AND next_date - 1;

Because the view is defined in the APPS schema, custom code should reference it as APPS.BOM_ORG_CAL_WEEKS_VIEW and grant SELECT explicitly where cross-schema access is required. Note that since the view derives from MTL_PARAMETERS, only organizations with an assigned calendar will appear in the results.