Skip to main content
Query Sample

SQL Query to find all active errors reported on Orders in Oracle SCM Cloud

By September 14, 2020April 24th, 2024No Comments

SQL Query to find all active errors reported on Orders. This is the query to see errors reported for Orders entered from UI –

SELECT DISTINCT dha.creation_date,
        dha.source_order_number  ,
        DMT.message_text         ,
        DMB.message_type
FROM    fusion.doo_headers_all dha     ,
        FUSION.DOO_MESSAGE_ENTITIES DME,
        fusion.DOO_MESSAGES_B DMB      ,
        fusion.DOO_MESSAGES_TL DMT     ,
        fusion.DOO_MESSAGE_requests dmr
WHERE   dha.header_id          = DME.entity_id
        AND DME.entity_name    = 'ORDER'
        AND DMB.msg_request_id = DME.msg_request_id
        AND DMT.message_id     = DMB.message_id
        AND dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
        AND dmr.ACTIVE_FLAG    = 'Y'
ORDER BY dha.creation_date DESC

And this is for Errors reported for imported orders:

SELECT DISTINCT dohai.creation_date    ,
        dohai.source_transaction_number,
        DMT.message_text               ,
        DMB.message_type
FROM    fusion.DOO_ORDER_HEADERS_ALL_INT dohai,
        FUSION.DOO_MESSAGE_ENTITIES DME       ,
        fusion.DOO_MESSAGES_B DMB             ,
        fusion.DOO_MESSAGES_TL DMT            ,
        fusion.DOO_MESSAGE_requests dmr
WHERE   dohai.source_transaction_number = DME.entity_id
        AND DME.entity_name            IN ('SRC_ORDER_NUM','SRC_ORDER','RECORD_NUMBER')
        AND DMB.msg_request_id          = DME.msg_request_id
        AND DMT.message_id              = DMB.message_id
        AND dmr.MSG_REQUEST_ID          = DMB.MSG_REQUEST_ID
        AND dmr.ACTIVE_FLAG             = 'Y'
ORDER BY dohai.creation_date DESC