Results for “bom_calendar_weeks_view”

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

Overview

BOM_CALENDAR_WEEKS_VIEW is a reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is part of the Bills of Material (BOM) product family and presents workdays and non-workdays of the month in a pivot-style, month-at-a-glance layout. The view is documented in ETRM as being used by the Calendar window within Oracle Manufacturing, where it renders a graphical or tabular representation of a manufacturing calendar for a selected month.

The view does not store data. It derives its output dynamically from the BOM_CALENDAR_DATES table, applying Oracle date arithmetic and DECODE logic to transpose individual calendar dates into a single row per calendar, exception set, and month. Each row contains summed day numbers and mark indicators for each day of the week, enabling a compact monthly rendering. Because the view is read-only and schema-resident in APPS, it is available to any concurrent program, form, or custom report with appropriate grants.

Underlying Base Objects

The view is defined exclusively over one base object: BOM_CALENDAR_DATES, exposed to APPS through a synonym. BOM_CALENDAR_DATES stores the individual dates that make up a manufacturing calendar, including both workdays and non-workdays, along with the exception set that governs the calendar's workday pattern.

BOM_CALENDAR_DATES holds the granular, row-level calendar detail, while BOM_CALENDAR_WEEKS_VIEW aggregates and pivots that detail into a weekly-by-month presentation. The join is implicit in the view text: the GROUP BY clause consolidates all rows for a given CALENDAR_CODE, EXCEPTION_SET_ID, year, month abbreviation, and month number. No other base tables, external tables, or PL/SQL functions are referenced. The view is dependent solely on BOM_CALENDAR_DATES, so any change to that table's structure or contents directly affects the view's results.

Key Columns

• CALENDAR_CODE — Identifies the manufacturing calendar being reported. This is the primary grouping key for the view.
• EXCEPTION_SET_ID — Identifies the exception set associated with the calendar, used to determine which dates are treated as workdays or non-workdays.
• Year and Month columns — Derived from TO_CHAR(CALENDAR_DATE,'YYYY') and TO_CHAR(CALENDAR_DATE,'MM'), these columns identify the year and month number for the aggregated row. A three-character month abbreviation (e.g., JAN, FEB) is also included.
• Week-of-month column — A derived numeric value calculated as TRUNC((day-of-month + offset)/7), assigning each date to a week position within the month.
• Day-of-week sum columns — Seven SUM(DECODE(...)) columns, one for each day of the week (Sunday through Saturday). Each returns the day-of-month number if the corresponding date falls on that weekday, otherwise NULL. These effectively populate the month grid.
• Day-of-week mark columns — Seven MAX(DECODE(...)) columns that return an asterisk ('*') where SEQ_NUM is blank, or a space otherwise. These mark exception or non-workday entries in the calendar display.

Common Use Cases and Queries

The most common use is rendering the Calendar window month view, showing which dates are workdays, which are non-workdays, and where exceptions apply. Because the view exposes a calendar_date-derived structure, users searching on "calendar_date" typically use it to inspect or export calendar workday patterns for a given month.

A basic query listing all weeks for a specific calendar and month might resemble:

• SELECT CALENDAR_CODE, EXCEPTION_SET_ID, SUBSTR(MONTH_ABBR), WEEK_OF_MONTH FROM BOM_CALENDAR_WEEKS_VIEW WHERE CALENDAR_CODE = :p_calendar AND MONTH_NUMBER = :p_month ORDER BY WEEK_OF_MONTH;
• Reporting all workdays and non-workdays for a plant calendar to validate shop floor scheduling.
• Feeding exception-aware calendar data into custom scheduling or capacity reports that must respect non-workday patterns.
• Exporting monthly calendar grids to external systems for integration with planning or MES applications.

Because the view is defined on a single base table and uses only standard SQL functions, it is portable across both 12.1.1 and 12.2.2 without modification, and it can be safely queried in read-only reporting contexts.