Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Query to identify Work Orders WHERE Work Centre IS NULL

Accepted answer
33
Views
6
Comments
POD AH
POD AH Rank 3 - Community Apprentice
in Oracle Transactional Business Intelligence

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

Tagged:

Best Answer

  • POD AH
    POD AH Rank 3 - Community Apprentice
    Answer ✓

    @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

Answers

  • Riyaz Ali-Oracle
    Riyaz Ali-Oracle Rank 6 - Analytics Lead

    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
    POD AH Rank 3 - Community Apprentice
    edited Jun 25, 2025 3:55PM
    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?

  • Bhaskar Konar
    Bhaskar Konar Rank 8 - Analytics Strategist
    https://community.oracle.com/products/oracleanalytics/discussion/comment/69204#Comment_69204

    Very helpful information.

    Thanks for sharing @POD AH.

  • Syamantak Saha
    Syamantak Saha Rank 5 - Community Champion

    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
    POD AH Rank 3 - Community Apprentice
    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?