Your Production Planning Data Template
Your Production Planning Data Template
- Recommended attributes to collect
- Key activities to track
- Practical extraction guidance
Production Planning Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Name ActivityName | The name of the specific event or task that occurred within the production planning process. | ||
| Description This attribute describes a specific step or milestone in the production order's lifecycle, such as 'Work Order Created', 'Materials Issued', or 'Production Started'. Each activity represents a distinct point in the process. Process mining uses this information to construct the process map, showing the sequence and frequency of all activities. Analyzing activities is fundamental to understanding process flows, identifying bottlenecks between steps, and measuring the time spent in different stages. Why it matters The Activity Name is a core requirement for process mining, as it defines the steps in the process map and allows for the visualization and analysis of the process flow. Where to get This is typically derived from event logs, transaction statuses, or specific event tables within Oracle Manufacturing Cloud that track the lifecycle of a work order. Examples Work Order CreatedWork Order ReleasedMaterials IssuedProduction StartedWork Order Completed | |||
| Event Timestamp EventTimestamp | The precise date and time when a specific activity or event occurred. | ||
| Description This attribute records the timestamp for each activity in the process. It provides the chronological context necessary to understand the sequence of events and measure the duration between them. In process analysis, this timestamp is critical for calculating cycle times, waiting times, and overall process duration. It enables performance monitoring, bottleneck detection, and analysis of how process timing impacts outcomes like schedule adherence. Why it matters This timestamp is essential for all time-based process mining analysis, including calculating durations, discovering bottlenecks, and building a chronologically accurate process map. Where to get This information is usually found alongside the event or status data in transaction or log tables within Oracle Manufacturing Cloud, often as a creation date or last update date. Examples 2023-10-26T08:00:00Z2023-10-26T14:30:15Z2023-11-05T10:11:45Z | |||
| Production Order ProductionOrderNumber | The unique identifier for a production order, which serves as the primary case identifier for the production planning process. | ||
| Description The Production Order number uniquely identifies a request to produce a specific quantity of a product. It acts as the central case identifier, linking all related planning, scheduling, execution, and closing activities. In process mining, analyzing events grouped by this number allows for a complete end-to-end view of the production lifecycle. This helps in tracking the journey of each order, measuring lead times, identifying common pathways, and pinpointing deviations from the standard process. Why it matters This is the essential case identifier that connects all related production events, enabling a comprehensive analysis of the entire production planning and execution flow for each order. Where to get This identifier is a core element in Oracle Manufacturing Cloud, typically found in work order headers and transaction tables. Consult the WIP_ENTITIES or similar work order tables. Examples WO-2024-00123PROD-58974M450001852 | |||
| Last Data Update LastDataUpdateTimestamp | The timestamp indicating when the data for this event was last refreshed or extracted from the source system. | ||
| Description This attribute records the date and time of the most recent data pull from the source system. It is a metadata field that reflects the freshness of the dataset being analyzed. This information is crucial for understanding the timeliness of the insights generated. It informs users about how current the process data is, ensuring that decisions are based on data of a known age. Why it matters Provides transparency about data freshness, ensuring that users understand how current the analysis is and when the next data update might be expected. Where to get This value is typically generated and stamped onto the dataset during the data extraction, transformation, and loading (ETL) process. Examples 2024-01-15T02:00:00Z2024-01-16T02:00:00Z2024-01-17T02:00:00Z | |||
| Source System SourceSystemIdentifier | The system from which the production planning data was extracted. | ||
| Description This attribute identifies the source information system, for instance, Oracle Manufacturing Cloud. It is particularly useful in environments where data from multiple systems is combined for a holistic process view. In analysis, it allows for filtering the process data based on its origin. This can help in comparing processes across different systems or isolating data to a specific source for targeted analysis. Why it matters It provides crucial context about the data's origin, which is important for data governance, validation, and analysis in multi-system environments. Where to get This is typically a static value ('Oracle Manufacturing Cloud') added during the data extraction, transformation, and loading (ETL) process. Examples Oracle Manufacturing CloudOracle Fusion Cloud SCMOMC_PROD_INSTANCE_1 | |||
| Planned End Date PlannedEndDate | The scheduled date on which production for the order is planned to be completed. | ||
| Description This attribute contains the target completion date for the production order. It is the deadline that the production team aims to meet. This date is essential for calculating the Production Schedule Adherence Rate KPI, which compares this planned date against the actual completion timestamp. It helps quantify how well production meets its targets and is a primary metric on the Production Schedule Adherence dashboard. Why it matters Serves as the primary benchmark for measuring on-time completion and overall production schedule adherence. Where to get Consult Oracle Manufacturing Cloud documentation. Likely available in work order scheduling tables or the main work order header table, such as WIP_DISCRETE_JOBS. Examples 2023-11-10T17:00:00Z2023-11-20T17:00:00Z2023-12-10T17:00:00Z | |||
| Planned Start Date PlannedStartDate | The scheduled date on which production for the order is planned to begin. | ||
| Description This attribute contains the target start date for the production order as defined during the planning phase. It serves as a baseline against which actual performance is measured. This date is crucial for the Production Order Release On-Time Rate KPI, where it is compared to the actual release timestamp. It helps in assessing the effectiveness of the planning and scheduling process and in identifying premature or delayed starts. Why it matters This is a critical baseline for measuring schedule adherence and on-time performance for the start of production. Where to get Consult Oracle Manufacturing Cloud documentation. Likely available in work order scheduling tables or the main work order header table. Examples 2023-11-01T08:00:00Z2023-11-15T09:00:00Z2023-12-01T00:00:00Z | |||
| Planner Planner | The user or planner responsible for managing the production order. | ||
| Description This attribute identifies the employee or role assigned to oversee the production order's planning and execution. It can be a person's name, ID, or a planning group. Analyzing the process by planner helps to understand workload distribution and identify if certain planners or teams are associated with process delays or efficiencies. It is a key attribute for the Delay Root Cause & Activity Impact dashboard to find human factors correlated with performance. Why it matters Helps identify the impact of individual planners or teams on process performance, enabling targeted training and workload balancing. Where to get Consult Oracle Manufacturing Cloud documentation. This may be stored on the work order header or in related assignment tables. Examples John SmithPlannerGroup_AUSER12345 | |||
| Product Code ProductCode | The unique identifier for the product being manufactured. | ||
| Description This attribute specifies the item or material that is the subject of the production order. It is a key dimension for segmenting and analyzing the production process. Using the product code, analysts can compare production processes for different items. This helps answer questions like 'Which products have the longest lead times?' or 'Are there process deviations specific to certain product families?'. It is essential for the Production Performance Throughput dashboard. Why it matters Allows for process analysis to be segmented by product, revealing product-specific bottlenecks, delays, or inefficiencies. Where to get Consult Oracle Manufacturing Cloud documentation. This is typically stored in the work order details, linked from tables like WIP_DISCRETE_JOBS to an item master table. Examples FG-1001-AAS-5432-BLUERAW-987-C | |||
| Production Plant ProductionPlant | The manufacturing facility or plant where the production order is being executed. | ||
| Description This attribute indicates the physical location or organizational unit responsible for the production. It's a critical dimension for comparing process performance across different sites. Analyzing by production plant allows for benchmarking and identifying best practices or systemic issues at specific locations. It is used in dashboards like Resource Utilization Bottlenecks and Production Performance Throughput to understand site-specific performance. Why it matters Enables comparison of process efficiency and performance across different manufacturing locations, highlighting regional bottlenecks or best practices. Where to get Consult Oracle Manufacturing Cloud documentation. This information is typically part of the work order definition, often referred to as the organization or inventory organization. Examples Site A - ShanghaiPlant 001 - AustinGermany Works | |||
| Work Order Status WorkOrderStatus | The current status of the production order at the time of the event. | ||
| Description This attribute reflects the state of the production order, such as 'Unreleased', 'Released', 'On Hold', 'Completed', or 'Canceled'. The status provides a snapshot of where the order is in its lifecycle. Analyzing the process by work order status helps to understand the distribution of orders across different states. It is also valuable for identifying orders that are stuck in a particular state for too long or for filtering analysis to focus only on completed or in-progress orders. Why it matters It provides a clear view of the order's progress and outcome, enabling analysis of process bottlenecks, delays, and completion rates. Where to get Consult Oracle Manufacturing Cloud documentation. Likely found in work order header tables like WIP_DISCRETE_JOBS, associated with the status column. Examples ReleasedCompletedOn HoldCanceled | |||
| Activity Duration ActivityDuration | The time taken to complete a single activity. This is also known as processing time. | ||
| Description This metric represents the processing time of an activity, calculated as the difference between its end and start timestamps. If an end time is not available, it can be estimated, but having both provides the most accurate measure. Analyzing activity duration is key to identifying which specific steps in the process are the most time-consuming. This helps focus improvement efforts on the activities that contribute most to the overall cycle time. Why it matters Pinpoints exactly which process steps are taking the most time, allowing for targeted optimization efforts. Where to get This is calculated during data transformation. It requires both a start (EventTimestamp) and end (EventEndTime) timestamp for each activity to be accurate. Examples 5 minutes8 hours1.5 days | |||
| Completed Quantity CompletedQuantity | The actual quantity of the product that was successfully produced and completed. | ||
| Description This attribute captures the final output quantity of a production order after all operations are finished. This may differ from the planned quantity due to factors like scrap, yield loss, or overproduction. This is the 'actual' value used in the 'Planned vs Actual Quantity Deviation' KPI. It is fundamental for analyzing production efficiency, yield analysis, and understanding the reasons for variance between planned and actual output. Why it matters This measures the actual output of the production process, enabling crucial analysis of yield, efficiency, and planning accuracy. Where to get Consult Oracle Manufacturing Cloud documentation. This is often stored in work order tables like WIP_DISCRETE_JOBS as 'QUANTITY_COMPLETED'. Examples 9849501255 | |||
| Event End Time EventEndTime | The precise date and time when a specific activity or event was completed. | ||
| Description This attribute records the completion timestamp for activities that have a duration. It complements the StartTime by defining a specific time window for an activity. When both start and end times are available, the exact processing time for each activity can be calculated accurately. This is more precise than inferring duration from the start time of the next event, as it accounts for waiting time. Why it matters Enables the precise calculation of activity processing times, distinguishing them from the waiting times between activities. Where to get This information may be available in event logs or transaction tables that record both the start and end of an operation. Examples 2023-10-26T08:05:10Z2023-10-26T15:00:00Z2023-11-05T10:11:55Z | |||
| Material Availability MaterialAvailabilityStatus | Indicates whether the required materials for the production order are available. | ||
| Description This attribute shows the status of material availability, typically with values like 'Available', 'Shortage', or 'Partially Available'. This status is determined by checking inventory levels against the order's bill of materials. It is the key attribute for the Material Shortage Impact Analysis dashboard and the associated KPI. Analyzing this helps to quantify the impact of material shortages on production start times and overall lead times. Why it matters Directly measures the impact of material shortages on production schedules, which is a common cause of significant delays. Where to get Consult Oracle Manufacturing Cloud documentation. This might not be a direct field but could be derived by checking component availability for a work order. Examples AvailableShortagePartially Available | |||
| Planned Quantity PlannedQuantity | The quantity of the product that is scheduled to be produced in the order. | ||
| Description This attribute represents the target production volume for a given production order. It is set during the planning phase based on demand and capacity. This value is a key input for the 'Planned vs Actual Quantity Deviation' KPI, where it is compared against the actual quantity produced. Analyzing this helps to assess planning accuracy, yield rates, and scrap levels. Why it matters Provides the baseline quantity for evaluating production output accuracy, yield, and scrap, which is critical for capacity and material planning. Where to get Consult Oracle Manufacturing Cloud documentation. This is a standard field in work order tables like WIP_DISCRETE_JOBS, often named 'START_QUANTITY' or similar. Examples 10050001250 | |||
| Production Line ProductionLine | The specific assembly or production line within the plant assigned to the work order. | ||
| Description This attribute identifies the specific equipment or work center group responsible for executing the production order. It provides a more granular level of detail than the production plant. In the Resource Utilization Bottlenecks dashboard, this attribute is used to pinpoint delays associated with specific lines or work centers. It helps in identifying capacity constraints and optimizing resource allocation at a detailed level. Why it matters Allows for granular analysis of resource utilization and bottleneck detection at the specific production line or work center level. Where to get Consult Oracle Manufacturing Cloud documentation. This information is typically found in the work order routing or operations details. Examples LINE_01_PACKAGINGASSEMBLY_CELL_3BCNC_MACHINE_5 | |||
| Production Priority ProductionPriority | A code or value indicating the urgency or priority of the production order. | ||
| Description This attribute assigns a priority level, such as 'High', 'Medium', or 'Low', to a production order. This helps schedulers and production teams prioritize work when resources are constrained. In the Delay Root Cause & Activity Impact dashboard, priority can be used as a dimension to see if high-priority orders are actually processed faster or if they also suffer from delays. It helps evaluate the effectiveness of the prioritization system. Why it matters Helps analyze whether the prioritization system is effective and if high-priority orders are processed faster than low-priority ones. Where to get Consult Oracle Manufacturing Cloud documentation. This may be a standard or custom field on the work order header. Examples HighMediumLow19 | |||
| Release On Time Status ReleaseOnTimeStatus | A calculated status indicating if the order was released on time, early, or late. | ||
| Description This attribute is derived by comparing the 'Planned Start Date' with the actual timestamp of the 'Work Order Released' activity. This focuses specifically on the adherence of the initial release step of the process. This status directly supports the Production Order Release On-Time Rate KPI and the associated dashboard. It helps isolate delays that happen at the very beginning of the production execution cycle. Why it matters Helps to specifically identify and analyze delays in the critical step of releasing work orders to the production floor. Where to get This attribute is calculated during data transformation by comparing the 'PlannedStartDate' attribute to the timestamp of the release event. Examples On TimeLateEarly | |||
| Schedule Adherence Status ScheduleAdherenceStatus | A calculated status indicating if the order was completed on time, early, or late. | ||
| Description This attribute is derived by comparing the 'Planned End Date' with the actual timestamp of the 'Work Order Completed' activity. It provides a clear, categorical outcome for schedule performance. This status is the foundation of the Production Schedule Adherence dashboard and KPI. It simplifies analysis by allowing users to quickly filter for all late orders and investigate the root causes for the delays. Why it matters Provides a clear, simple indicator of on-time performance, making it easy to identify and analyze orders that failed to meet their deadlines. Where to get This attribute is calculated during data transformation by comparing the 'PlannedEndDate' attribute to the timestamp of the completion event. Examples On TimeLateEarly | |||
| Total Cycle Time TotalCycleTime | The total duration of the production order from creation to completion. | ||
| Description This attribute is a calculated metric representing the total time elapsed from the first event (e.g., 'Work Order Created') to the last event (e.g., 'Work Order Completed') for each production order. This is a primary KPI for measuring overall process performance and is central to the End-to-End Production Lead Time dashboard. It provides a high-level view of efficiency and helps track improvements over time. Why it matters This is a key performance indicator that measures the end-to-end efficiency of the production process for each order. Where to get This attribute is not available in the source system. It is calculated during data transformation by subtracting the minimum timestamp from the maximum timestamp for each case. Examples 10 days 4 hours3 weeks 2 days15.5 days | |||
Production Planning Activities
| Activity | Description | ||
|---|---|---|---|
| Production Started | This activity marks the beginning of the first manufacturing operation for the production order. It is the point where physical work on the product commences on the shop floor. | ||
| Why it matters This milestone is crucial for measuring actual production cycle times and adherence to the planned start date. It helps distinguish between pre-production delays and delays during manufacturing execution. Where to get This can be an explicit event from a shop floor transaction, like an operator clocking into the first operation. It can also be inferred from the timestamp of the first labor transaction or material consumption after the order is released. Capture Captured from the first 'move' or 'completion' transaction recorded against a work order operation. Event type explicit | |||
| Work Order Closed | This is the final administrative step after production is complete, where all costs are finalized and variances are calculated and posted. This formally closes the order from a financial and logistical perspective. | ||
| Why it matters The time between 'Completed' and 'Closed' can reveal inefficiencies in the financial closing or costing process. It provides a definitive end point for the entire work order lifecycle. Where to get This is an explicit event captured when the period-end closing process is run or a user manually changes the work order status to 'Closed'. A closed date timestamp is typically stored on the work order. Capture Captured from the timestamp when the work order status is set to 'Closed'. Event type explicit | |||
| Work Order Completed | This activity signifies that the final quantity of the finished product has been manufactured and reported against the production order. This event marks the end of all production activities for the order. | ||
| Why it matters As the primary success end-point, this activity is essential for calculating end-to-end production lead times and measuring schedule adherence against the planned completion date. It is a cornerstone for performance analysis. Where to get This is an explicit event triggered by a completion transaction that moves the finished goods into inventory. A work order completion date or status change to 'Completed' is recorded. Capture Captured from the final completion transaction timestamp or the date the work order status changes to 'Complete'. Event type explicit | |||
| Work Order Created | This activity marks the creation of a production order, also known as a work order, in Oracle Manufacturing Cloud. This is the formal instruction to manufacture a specific quantity of an item and is typically generated from a master production schedule or a sales order. | ||
| Why it matters As the starting point for the production process, this activity is crucial for measuring the overall lead time and analyzing the demand-to-production signal latency. It helps identify how long it takes for a production need to be formally actioned. Where to get This is an explicit event captured from the work order creation transaction. It corresponds to the creation timestamp of the work order record in the manufacturing execution tables, such as WIP_DISCRETE_JOBS or similar tables. Capture Captured from the creation date of the Work Order entity. Event type explicit | |||
| Work Order Released | Marks the official release of the production order to the shop floor, authorizing the start of manufacturing activities. This event makes the order and its documentation, like pick lists and routing sheets, available to production staff. | ||
| Why it matters This is a critical milestone that separates the planning phase from the execution phase. Delays in releasing orders are a common cause of production schedule slippage and are essential to monitor. Where to get This is an explicit event captured when a user or system process changes the work order status to 'Released'. A specific release date timestamp is typically recorded on the work order header. Capture Captured from the 'Released Date' timestamp on the work order record. Event type explicit | |||
| Material Availability Checked | Represents the point where the system has checked the availability of all required components for the production order. This is often an automated step following order creation or can be triggered manually by a planner. | ||
| Why it matters Tracking this activity helps analyze delays caused by material shortages. It isolates planning issues from execution issues and is key to understanding the impact of supply chain performance on production start times. Where to get This is likely an inferred event derived from a status change on the work order or its component requirements list. A status like 'Awaiting Components' changing to 'Components Available' or the population of a material status field would signify this. Capture Inferred from a change in the material availability status field associated with the work order. Event type inferred | |||
| Materials Issued | This activity represents the transaction of issuing component materials from inventory to the specific production order. It signifies that the necessary raw materials or sub-assemblies have been picked and moved to the production line. | ||
| Why it matters Tracking material issuance helps analyze the efficiency of warehouse and logistics operations. It also confirms material availability just before production starts, providing a more accurate picture than the initial check. Where to get This is an explicit event recorded as a material transaction in the inventory module, linked to the work order. Each transaction has a timestamp and details of the component issued. Capture Captured from material transaction records (e.g., WIP component issue) linked to the work order ID. Event type explicit | |||
| Operation Completed | Represents the completion of a single step or operation in the production routing for the work order. A production order will typically have multiple 'Operation Completed' events, one for each step in its manufacturing process. | ||
| Why it matters Analyzing the time between consecutive operation completions provides granular insight into the production flow. It helps identify bottleneck operations and calculate work-in-process times for specific stages. Where to get This is an explicit event captured through shop floor move or completion transactions. Operators record the quantity completed for an operation, which generates a timestamped transaction record. Capture Captured from completion transactions against specific operations of the work order. Event type explicit | |||
| Production Performance Analyzed | Represents the post-completion analysis of the production order's performance, comparing actual results against planned targets. This could involve reviewing costs, material usage, and schedule adherence. | ||
| Why it matters This activity is key to understanding the effectiveness of the continuous improvement cycle. Measuring the time to analyze performance indicates how quickly the organization learns from and responds to production variances. Where to get This is a calculated event, as it is a business process rather than a system transaction. It could be inferred from the generation date of a variance report or the update of a performance notes field, but this is highly dependent on local procedures. Capture Derived by detecting the creation of related variance reports or status updates in a performance management system. Event type calculated | |||
| Production Plan Adjusted | This activity occurs when a key parameter of a released production order, such as the required quantity or the planned completion date, is changed. This signifies a deviation from the original plan that was committed to production. | ||
| Why it matters Frequent adjustments indicate instability in the planning or execution process. Tracking these events helps quantify the level of churn and identify root causes, such as demand volatility or production issues. Where to get This is an explicit event that can be captured from audit trails or change logs on the work order record. Oracle Cloud often tracks changes to key fields, including who made the change and when. Capture Captured from audit logs or change history tables for the work order entity. Event type explicit | |||
| Quality Inspection Performed | Represents a quality control check performed during or after a manufacturing operation. This can include measurements, visual inspections, or other tests to ensure the product meets specifications. | ||
| Why it matters This activity provides insight into the quality management process. Analyzing the frequency and duration of inspections, as well as their outcomes (pass/fail), helps identify quality-related bottlenecks or recurring issues. Where to get This is an explicit event captured in the Oracle Quality Management module. Inspection results are recorded in quality collection plans, which are linked to the production order or specific operation. Capture Captured from records in the quality collection results table linked to the work order. Event type explicit | |||
| Work Order Approved | This activity signifies that the production order has been reviewed and approved by a planner or manager. Approval may be required before the order can be released to the shop floor, especially for high-value or complex products. | ||
| Why it matters Analyzing the time between creation and approval helps identify bottlenecks in the pre-production administrative process. Long approval cycles can significantly delay the start of manufacturing. Where to get This is typically an inferred event based on a status change on the work order, for example, moving from 'Unapproved' to 'Approved'. Oracle often uses status fields to manage the lifecycle of transactional objects like work orders. Capture Inferred from a work order status change from an unapproved state to an approved or releasable state. Event type inferred | |||
| Work Order Canceled | This activity represents the cancellation of a production order that was previously created and possibly released. This is an exception path that stops all further work on the order. | ||
| Why it matters Tracking cancellations is crucial for understanding process exceptions. Analyzing the reasons for cancellations can highlight issues with demand forecasting, planning accuracy, or engineering changes. Where to get This is an explicit event triggered by a user action to change the work order status to 'Canceled'. A timestamp for this status change is recorded. Capture Captured from the timestamp of the status change to 'Canceled' on the work order. Event type explicit | |||
Extraction Guides
Steps
- Navigate to Oracle BI Publisher: Log in to your Oracle Fusion Cloud instance with a user that has BI Author or Administrator privileges. Use the Navigator menu to go to Tools > Reports and Analytics. Then, click the 'Browse Catalog' button to open the BI Publisher catalog.
- Create a New Data Model: In the BI Publisher catalog, navigate to a suitable folder (e.g., Shared Folders > Custom). Click on the 'New' dropdown menu and select 'Data Model'.
- Define a New Data Set: In the Data Model editor, click the '+' icon next to 'Data Sets' and select 'SQL Query'.
- Configure the Data Set: In the 'New Data Set' dialog box, enter a descriptive name, for example, 'Production_Planning_Event_Log'. For 'Data Source', select your Oracle Fusion application database, which is typically named 'ApplicationDB_FSCM' or a similar variant.
- Enter the SQL Query: Copy the complete SQL query provided in the 'query' section of this document and paste it into the 'SQL Query' text area.
- Define Query Parameters: The query uses parameters
:p_start_dateand:p_end_dateto filter the date range. After pasting the query, these parameters will appear in the parameters section. For each parameter, set the 'Data Type' to 'Date' and provide a user friendly 'Display Label', such as 'Start Date'. You can also set default values if desired. - View and Verify Data: Click the 'Data' tab within the Data Model editor. Enter values for the start and end date parameters, then click 'View' to execute the query and see a sample of the extracted data. Verify that the columns and rows appear as expected.
- Save the Data Model: Once you are satisfied with the data preview, click the 'Save' icon. Choose a name for your data model, for example, 'ProductionPlanningExtraction.xdm', and save it in your designated custom folder.
- Create a Report for Export: To export the full data set, you must link this data model to a report. Click the 'Create Report' button from within the saved data model. Follow the wizard to create a simple table-based report layout. No complex formatting is needed.
- Run and Export the Report: Save the newly created report. Open the report and run it with the desired date parameters. Once the report is generated, use the 'Export' functionality to save the output as a CSV file. This CSV file is the event log ready for upload.
Configuration
- Data Source: The query must be run against the Oracle Fusion application database, typically named
ApplicationDB_FSCM. Ensure the configured user has read access to all manufacturing, inventory, and personnel tables referenced in the query. - Date Range Parameters: The query includes two mandatory parameters,
:p_start_dateand:p_end_date, to filter the data. It is recommended to extract data in batches of 3 to 6 months to maintain good performance and manage file sizes. - Key Filters: To further limit the data scope and improve performance, consider adding
WHEREclauses to thebase_datacommon table expression. Common filters include:IODV.ORGANIZATION_CODE IN ('[Your Plant Code 1]', '[Your Plant Code 2]')to select specific production plants.WDJ.WORK_ORDER_TYPE IN ('Standard', '[Your Custom Type]')to select specific types of production orders.
- Performance Considerations: This is a complex query with multiple
UNION ALLbranches. For large manufacturing operations, it can be resource intensive. Schedule the extraction during off-peak hours to minimize impact on system performance. - Prerequisites: The user creating and running the data model needs Oracle BI Publisher roles, such as
BI Author. The underlying data source user needs explicit read access toWIP_DISCRETE_JOBS,WIP_OPERATIONS,INV_MATERIAL_TXNS,QA_RESULTS,INV_ORGANIZATION_DEFINITIONS_V,EGP_SYSTEM_ITEMS_B,PER_USERS, andPER_PERSON_NAMES_Ftables.
a Sample Query sql
WITH base_data AS (
SELECT
WDJ.WORK_ORDER_NUMBER,
WDJ.STATUS_TYPE,
(SELECT STATUS_NAME FROM WIP_WORK_ORDER_STATUSES_VL WHERE STATUS_TYPE = WDJ.STATUS_TYPE) AS WORK_ORDER_STATUS_NAME,
ESI.ITEM_NUMBER AS PRODUCT_CODE,
IODV.ORGANIZATION_CODE AS PRODUCTION_PLANT,
PPNF.DISPLAY_NAME AS PLANNER,
WDJ.SCHEDULED_START_DATE,
WDJ.SCHEDULED_COMPLETION_DATE,
WDJ.CREATION_DATE,
WDJ.DATE_RELEASED,
WDJ.ACTUAL_COMPLETION_DATE,
WDJ.DATE_CLOSED,
WDJ.LAST_UPDATE_DATE,
WDJ.WIP_ENTITY_ID
FROM
WIP_DISCRETE_JOBS WDJ
JOIN INV_ORGANIZATION_DEFINITIONS_V IODV ON WDJ.ORGANIZATION_ID = IODV.ORGANIZATION_ID
JOIN EGP_SYSTEM_ITEMS_B ESI ON WDJ.PRIMARY_ITEM_ID = ESI.INVENTORY_ITEM_ID AND WDJ.ORGANIZATION_ID = ESI.ORGANIZATION_ID
LEFT JOIN PER_USERS PU ON WDJ.CREATED_BY = PU.USERNAME
LEFT JOIN PER_PERSON_NAMES_F PPNF ON PU.PERSON_ID = PPNF.PERSON_ID AND PPNF.NAME_TYPE = 'GLOBAL' AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
WHERE
WDJ.CREATION_DATE BETWEEN TO_DATE(:p_start_date, 'YYYY-MM-DD') AND TO_DATE(:p_end_date, 'YYYY-MM-DD')
)
-- 1. Work Order Created
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Created' AS "ActivityName",
BD.CREATION_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
UNION ALL
-- 2. Material Availability Checked (Inferred)
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Material Availability Checked' AS "ActivityName",
BD.CREATION_DATE + interval '1' minute AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
UNION ALL
-- 3. Work Order Approved (Inferred)
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Approved' AS "ActivityName",
BD.DATE_RELEASED - interval '1' minute AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.DATE_RELEASED IS NOT NULL
UNION ALL
-- 4. Work Order Released
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Released' AS "ActivityName",
BD.DATE_RELEASED AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.DATE_RELEASED IS NOT NULL
UNION ALL
-- 5. Materials Issued
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Materials Issued' AS "ActivityName",
IMT.TRANSACTION_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
INV_MATERIAL_TXNS IMT
JOIN base_data BD ON IMT.TRANSACTION_SOURCE_ID = BD.WIP_ENTITY_ID
WHERE
IMT.TRANSACTION_SOURCE_TYPE_ID = 5 -- WIP Transaction Source Type
AND IMT.TRANSACTION_ACTION_ID IN (1, 27, 33, 34) -- Issue, Backflush, Sub-assy Return
AND IMT.TRANSACTION_TYPE_ID IN (35, 43) -- WIP Issue, Assembly Completion
UNION ALL
-- 6. Production Started
SELECT
OPS.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Production Started' AS "ActivityName",
OPS.FIRST_OP_START_DATE AS "EventTimestamp",
OPS.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
OPS.PRODUCT_CODE AS "ProductCode",
OPS.PRODUCTION_PLANT AS "ProductionPlant",
OPS.PLANNER AS "Planner",
OPS.SCHEDULED_START_DATE AS "PlannedStartDate",
OPS.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM (
SELECT
BD.WORK_ORDER_NUMBER,
MIN(WO.ACTUAL_START_DATE) AS FIRST_OP_START_DATE,
BD.WORK_ORDER_STATUS_NAME,
BD.PRODUCT_CODE,
BD.PRODUCTION_PLANT,
BD.PLANNER,
BD.SCHEDULED_START_DATE,
BD.SCHEDULED_COMPLETION_DATE
FROM
WIP_OPERATIONS WO
JOIN base_data BD ON WO.WIP_ENTITY_ID = BD.WIP_ENTITY_ID
WHERE
WO.ACTUAL_START_DATE IS NOT NULL
GROUP BY
BD.WORK_ORDER_NUMBER, BD.WORK_ORDER_STATUS_NAME, BD.PRODUCT_CODE, BD.PRODUCTION_PLANT, BD.PLANNER, BD.SCHEDULED_START_DATE, BD.SCHEDULED_COMPLETION_DATE
) OPS
UNION ALL
-- 7. Operation Completed
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Operation Completed' AS "ActivityName",
WO.ACTUAL_COMPLETION_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
WIP_OPERATIONS WO
JOIN base_data BD ON WO.WIP_ENTITY_ID = BD.WIP_ENTITY_ID
WHERE
WO.ACTUAL_COMPLETION_DATE IS NOT NULL
UNION ALL
-- 8. Production Plan Adjusted
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Production Plan Adjusted' AS "ActivityName",
BD.LAST_UPDATE_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.DATE_RELEASED IS NOT NULL
AND BD.STATUS_TYPE NOT IN (7, 12) -- Not Canceled or Closed
AND BD.LAST_UPDATE_DATE > (BD.DATE_RELEASED + interval '1' hour)
AND BD.LAST_UPDATE_DATE < NVL(BD.ACTUAL_COMPLETION_DATE, SYSDATE)
UNION ALL
-- 9. Quality Inspection Performed
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Quality Inspection Performed' AS "ActivityName",
QR.CREATION_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
QA_RESULTS QR
JOIN base_data BD ON QR.WIP_ENTITY_ID = BD.WIP_ENTITY_ID
WHERE
QR.WIP_ENTITY_ID IS NOT NULL
UNION ALL
-- 10. Work Order Completed
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Completed' AS "ActivityName",
BD.ACTUAL_COMPLETION_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.ACTUAL_COMPLETION_DATE IS NOT NULL
UNION ALL
-- 11. Production Performance Analyzed (Inferred)
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Production Performance Analyzed' AS "ActivityName",
BD.DATE_CLOSED + interval '1' day AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.DATE_CLOSED IS NOT NULL
UNION ALL
-- 12. Work Order Closed
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Closed' AS "ActivityName",
BD.DATE_CLOSED AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.DATE_CLOSED IS NOT NULL
UNION ALL
-- 13. Work Order Canceled
SELECT
BD.WORK_ORDER_NUMBER AS "ProductionOrderNumber",
'Work Order Canceled' AS "ActivityName",
BD.LAST_UPDATE_DATE AS "EventTimestamp",
BD.WORK_ORDER_STATUS_NAME AS "WorkOrderStatus",
BD.PRODUCT_CODE AS "ProductCode",
BD.PRODUCTION_PLANT AS "ProductionPlant",
BD.PLANNER AS "Planner",
BD.SCHEDULED_START_DATE AS "PlannedStartDate",
BD.SCHEDULED_COMPLETION_DATE AS "PlannedEndDate",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp"
FROM
base_data BD
WHERE
BD.STATUS_TYPE = 7Steps
- Navigate to Oracle Transactional Business Intelligence (OTBI): Log in to your Oracle Manufacturing Cloud instance. Use the Navigator menu to go to Tools, then select Reports and Analytics. Click on the 'Browse Catalog' button to open the Business Intelligence catalog.
- Create a New Analysis: In the OTBI catalog, click on 'New' in the toolbar and select 'Analysis'. You will be prompted to select a Subject Area.
- Select the Subject Area: From the list of available subject areas, choose 'Manufacturing - Work Order Performance Real Time'. This subject area contains the core information about work orders and their statuses.
- Define Analysis Criteria: The analysis editor has two main tabs: 'Criteria' and 'Results'. In the 'Criteria' tab, you will build the logic for the event log.
- Add Columns for the First Activity: From the 'Subject Areas' pane on the left, drag and drop the necessary columns into the 'Selected Columns' area. Start with the 'Work Order Created' activity. For example, add 'Work Order Details'.'Work Order Number', a formula for the Activity Name, 'Work Order Details'.'Creation Date' for the timestamp, and other required attributes.
- Create a Custom Formula for Activity Name: Click the 'Add a new calculated measure' icon next to the 'Selected Columns' header. In the formula box, enter the text for the activity, for example,
'Work Order Created'. Give the column a name like 'ActivityName'. - Combine All Activities using UNION ALL: To create a single event log with all required activities, you must combine multiple queries. In the 'Criteria' tab, find the 'Combine results based on a set operation' icon (often looks like two overlapping circles) at the bottom right of the 'Selected Columns' section. Select 'Union All' and add a new criteria block for the next activity, such as 'Work Order Released'.
- Repeat for All Activities: Repeat steps 5-7 for all 13 required activities. For each activity, you will create a new criteria block combined with 'Union All'. Ensure you select the correct date field for each activity's timestamp (e.g., 'Release Date', 'Actual Completion Date', 'Closed Date'). Some activities will require filtering on 'Work Order Status Name'.
- Apply Global Filters: After setting up all the UNION blocks, go to the 'Filters' section. Add filters to limit the data scope. It is critical to add a filter for the 'Work Order Creation Date' or 'Last Update Date' to specify a date range, for example, the last 6 months. You should also filter by 'Organization Name' to select the correct production plant.
- Review and Format Results: Switch to the 'Results' tab to preview the data. Ensure all columns are present and correctly named. You can drag and drop columns to reorder them if necessary.
- Export the Event Log: Once the analysis is complete and the results look correct, click the 'Export' link at the bottom of the page. Select 'CSV File' as the format.
- Prepare for Upload: Open the downloaded CSV file. Verify the column headers match the required attributes:
ProductionOrderNumber,ActivityName,EventTimestamp, etc. Ensure the timestamp format is consistent. The file is now ready for upload to your process mining tool.
Configuration
- Subject Area: 'Manufacturing - Work Order Performance Real Time' is the primary subject area used for this extraction. For detailed material or quality data, you may need to create a separate analysis using 'Manufacturing - Material Usage Real Time' or 'Quality Management - Inspection Results Real Time' and join the results.
- Date Range Filter: It is crucial to apply a date range filter to manage the volume of data. Create a filter on 'Work Order Details'.'Creation Date' or 'Work Order Details'.'Last Update Date' to extract a recent period, such as the last 3 to 6 months.
- Organizational Filter: Always filter by 'Organization Details'.'Organization Name' or 'Organization Code' to limit the data to the specific production plant or business unit you are analyzing.
- Work Order Status Filter: To reduce noise, you might want to exclude certain work order statuses from the entire analysis, such as draft or simulation orders. This can be done with a global filter on 'Work Order Status'.'Work Order Status Name'.
- Prerequisites: The user running the analysis must have the appropriate roles and permissions, typically a role like 'Manufacturing Engineer' or a custom BI role, to access the 'Manufacturing - Work Order Performance Real Time' subject area.
a Sample Query config
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Created' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Created By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Material Availability Checked' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Update Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" = 'Unreleased' AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Approved' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Release Date" - INTERVAL '1' MINUTE AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Release Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Released' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Release Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Release Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Materials Issued' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Start Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Start Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Production Started' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Start Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Start Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Operation Completed' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Operation Details"."Actual Completion Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Operation Details"."Actual Completion Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Production Plan Adjusted' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Update Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Update Date" > "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Release Date" AND "Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" IN ('Released', 'On Hold') AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Quality Inspection Performed' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Operation Details"."Actual Completion Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Operation Details"."Actual Completion Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Completed' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Completion Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Actual Completion Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Production Performance Analyzed' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Closed Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Closed Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Closed' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Closed Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Closed Date" IS NOT NULL AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'
UNION ALL
SELECT
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Work Order Number" AS "ProductionOrderNumber",
'Work Order Canceled' AS "ActivityName",
CAST("Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Update Date" AS TIMESTAMP) AS "EventTimestamp",
'Oracle Manufacturing Cloud' AS "SourceSystemIdentifier",
CURRENT_TIMESTAMP AS "LastDataUpdateTimestamp",
"Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" AS "WorkOrderStatus",
"Manufacturing - Work Order Performance Real Time"."Item"."Item Number" AS "ProductCode",
"Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Name" AS "ProductionPlant",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Last Updated By" AS "Planner",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Start Date" AS "PlannedStartDate",
"Manufacturing - Work Order Performance Real Time"."Work Order Details"."Planned Completion Date" AS "PlannedEndDate"
FROM "Manufacturing - Work Order Performance Real Time"
WHERE "Manufacturing - Work Order Performance Real Time"."Work Order Status"."Work Order Status Name" = 'Canceled' AND "Manufacturing - Work Order Performance Real Time"."Work Order Details"."Creation Date" >= TIMESTAMP '[start_date]' AND "Manufacturing - Work Order Performance Real Time"."Organization Details"."Organization Code" = '[Your Organization Code]'