nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Query to identify Work Orders WHERE Work Centre IS NULL

POD AH

Is it possible to create an analysis in OTBI to retrieve work orders that are not associated to any work centre? Our organisation has multiple departments (work centres) who are responsible for triaging work orders in their ques. Unfortunately, some work orders are getting "Lost" and since they are not assigned to any particular work centre nobody is looking at them. I need a report I can run on an adhoc basis that will give me the following:

Work Order Number, Scheduled Start Date, Creation Date, Work Order Description (and a number of the DFF fields in the work order subject area). I only need where the work centre is Null and status is not in Completed, Closed, Cancelled

Find more posts tagged with

SQL

OTBI (Oracle Transactional Business Intelligence)

Accepted answers

POD AH

@Riyaz Ali-Oracle - I used the below in the end. This returns all work orders without an operation therefore no work centre

SELECT
WWOB.WORK_ORDER_ID,
WWOB.ORGANIZATION_ID,
WWOB.WORK_ORDER_NUMBER,
WWOT.WORK_ORDER_DESCRIPTION,
WWOB.WORK_ORDER_SUB_TYPE,
WWOB.WORK_ORDER_STATUS_ID,
WWOB.PLANNED_START_DATE,
WWOB.CREATION_DATE,
WWOB.CREATED_BY,
WWOB.LAST_UPDATED_BY,
WWST.WO_STATUS_NAME,
WWOB.ATTRIBUTE_CHAR1,
WWOB.ATTRIBUTE_CHAR2,
WWOB.ATTRIBUTE_CHAR3,
WWOB.ATTRIBUTE_CHAR4,
WWOB.ATTRIBUTE_CHAR5,
WWOB.ATTRIBUTE_CHAR6,
WWOB.ATTRIBUTE_CHAR7,
WWOB.ATTRIBUTE_CHAR8,
WWOB.ATTRIBUTE_CHAR9,
WWOB.ATTRIBUTE_CHAR10,
WWOP.OPERATION_SEQ_NUMBER
FROM
FUSION.WIE_WORK_ORDERS_B WWOB
LEFT OUTER JOIN FUSION.WIE_WO_OPERATIONS_B WWOP ON WWOB.WORK_ORDER_ID = WWOP.WORK_ORDER_ID
LEFT OUTER JOIN FUSION.WIE_WORK_ORDERS_TL WWOT ON WWOT.WORK_ORDER_ID = WWOB.WORK_ORDER_ID
LEFT OUTER JOIN FUSION.WIE_WO_STATUSES_TL WWST ON WWOB.WORK_ORDER_STATUS_ID = WWST.WO_STATUS_ID
WHERE WWOP.OPERATION_SEQ_NUMBER IS NULL

All comments

Riyaz Ali-Oracle

Hi ,

You can try creating a analysis in OTBI using the Manufacturing – Work Order Performance Real Time subject area.

Columns to Include:

Work Order Number
Work Order Description
Work Order Creation Date
Scheduled Start Date
Work Center Name (this is the key column to filter for NULL)

Filters to Apply
Work Center Name → is null This will isolate work orders not assigned to any work center.
Work Order Status → is not equal to

Completed
Closed
Cancelled

You can use a filter like: Work Order Status Code not in (COMPLETED, CLOSED, CANCELLED) (Exact values may vary depending on your setup—check the LOV.)

Thanks,
Riyaz Ali

POD AH

https://community.oracle.com/products/oracleanalytics/discussion/comment/68776#Comment_68776

@Riyaz Ali-Oracle

Thank you for your response. To confirm, its the maintenance subject area we are using. I have tried something similar to the above but it appears the way OTBI behaves is that it defaults to an inner join and excludes the null values. When I apply a null filter to the work centre name the results are zero (i know this not be to true). I was wondering if there is a way to achieve my requirements using the advanced tab or if a new data model would have to be created?

POD AH

@Riyaz Ali-Oracle - I used the below in the end. This returns all work orders without an operation therefore no work centre

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/69204#Comment_69204

Very helpful information.

Thanks for sharing @POD AH.

Syamantak Saha

Hi,

You should really use one of the standard reports to collect this information. Writing ad-hoc queries is not suitable, as further patches etc., would corrupt your reported data. From the symptoms, it seems like a Data Quality issue, where the current state is significantly different to any assumed state during installation or major patching.

POD AH

https://community.oracle.com/products/oracleanalytics/discussion/comment/69419#Comment_69419

@Syamantak Saha - I don't believe there is a standard report to retrieve this information unfortunately. The issue is functionality in Oracle allows for a work order to be saved without an associated work centre but then no means to identify where null. Id really appreciate any advice on how I might better tackle this issue?