Search Results mr_shcl_dtl_pk

Overview

The MR_SHCL_DTL table is a core data object within the Process Manufacturing Process Planning (GMP) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It functions as the detail table for a shop calendar, which is a critical scheduling tool in manufacturing. This table stores the specific dates that constitute a defined calendar, enabling the system to model working days, non-working days, and specific shop day patterns for production planning, scheduling, and capacity management. Its role is to provide the granular date-level foundation against which manufacturing operations and resource availability are planned.

Key Information Stored

The table's primary purpose is to map calendar dates to a specific shop calendar definition and associated attributes. Its structure is defined by a composite primary key and several foreign key relationships. The most critical columns include:

• CALENDAR_ID: Links the detail record to its parent calendar definition in the MR_SHCL_HDR table.
• CALENDAR_DATE: The specific date entry within the calendar. Together with CALENDAR_ID, this forms the table's primary key (MR_SHCL_DTL_PK).
• SHOPDAY_NO: A reference to a predefined shop day pattern or type, linked to the MR_SHDY_HDR table, which may define attributes like shift patterns or capacity for that type of day.
• TEXT_CODE: An optional reference to descriptive text stored in the MR_TEXT_HDR table, allowing for notes or descriptions associated with a particular calendar date.

Common Use Cases and Queries

This table is primarily queried to determine operational schedules and validate production dates. Common use cases include generating production schedules, checking if a specific date is a valid working day for a given plant or production line, and calculating lead times based on calendar working days. A typical query would join MR_SHCL_DTL to its header table to retrieve all dates for a specific calendar, often filtering by a date range and SHOPDAY_NO to identify working days.

Sample Query: Retrieving all working dates for a specific calendar in a given month.

SELECT d.calendar_date, d.shopday_no, h.calendar_code
FROM gmp.mr_shcl_dtl d,
     gmp.mr_shcl_hdr h
WHERE d.calendar_id = h.calendar_id
  AND h.calendar_code = 'MAIN_PLANT_CAL'
  AND d.calendar_date BETWEEN TRUNC(SYSDATE, 'MM') AND LAST_DAY(SYSDATE)
ORDER BY d.calendar_date;

This data is essential for reports analyzing plant utilization, schedule adherence, and available production days.

Related Objects

MR_SHCL_DTL is centrally connected to several key GMP tables, as indicated by its foreign keys:

• MR_SHCL_HDR: The parent table containing the calendar definition (code, description). The relationship is on CALENDAR_ID.
• MR_SHDY_HDR: The table defining shop day types or patterns, linked via the SHOPDAY_NO column.
• MR_TEXT_HDR: The table for storing descriptive text, linked via the TEXT_CODE column.

This table is a foundational component for scheduling engines and capacity planning functions within Oracle Process Manufacturing (OPM).