-- All Orders In Error
-- Errors reported on ALL Orders.
SELECT
dha.source_order_number
, dha.order_number
, dha.header_id
, (
SELECT DISTINCT
service_name
FROM
fusion.DOO_SERVICE_DEFINITIONS_tl
WHERE
service_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'SVC'
AND language = userenv('LANG')
)
)
AS Service
, (
SELECT DISTINCT
display_name
FROM
fusion.DOO_TASK_INSTANCES sdti
, fusion.DOO_TASK_DEFINITIONS_TL sdtt
WHERE
task_instance_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'TASK'
AND language = userenv('LANG')
)
AND sdti.task_id = sdtt.task_id
AND sdtt.language = userenv('LANG')
)
AS Task
, dmr.REQUEST_FUNCTION
, dmr.REQ_ENTITY_TYPE
, dmr.REQUEST_RESULT
, dmb.msg_entity_type
, dmb.msg_entity_id1
, dmt.message_text
, dmt.creation_Date
FROM
fusion.DOO_MESSAGE_requests dmr
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.doo_headers_all dha
, fusion.doo_lines_all dla
, fusion.doo_fulfill_lines_all dfla
, FUSION.doo_ORCHESTRATION_GROUPS DOG
WHERE
dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
AND dmr.ACTIVE_FLAG = 'Y'
AND DMB.msg_request_id = DME.msg_request_id
AND DMB.message_id = DME.message_id
AND DMT.message_id = DMB.message_id
AND dmt.language = userenv('LANG')
AND dmb.MSG_ENTITY_TYPE IN ('FLINE')
AND dmb.msg_entity_type = dme.ENTITY_NAME
AND TO_CHAR(dfla.fulfill_line_id) = dmb.msg_entity_id1
AND dha.header_id = dfla.header_id
AND dfla.line_id = dla.line_id
AND dfla.fulfill_line_id = dog.fulfillment_line_id
AND dog.status = 'ACTIVE'
AND EXISTS
(
SELECT
1
FROM
fusion.doo_process_step_instances dpsi
WHERE
dog.group_id = dpsi.group_id
AND dpsi.step_status IN( 'IMPLICIT_WAIT','STARTED')
AND dpsi.ACTUAL_COMPLETION_DATE IS NULL
)
UNION ALL
SELECT
dha.source_order_number
, dha.order_number
, dha.header_id
, (
SELECT DISTINCT
service_name
FROM
fusion.DOO_SERVICE_DEFINITIONS_tl
WHERE
service_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'SVC'
AND language = userenv('LANG')
)
)
AS Service
, (
SELECT DISTINCT
display_name
FROM
fusion.DOO_TASK_INSTANCES sdti
, fusion.DOO_TASK_DEFINITIONS_TL sdtt
WHERE
task_instance_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'TASK'
AND language = userenv('LANG')
)
AND sdti.task_id = sdtt.task_id
AND sdtt.language = userenv('LANG')
)
AS Task
, dmr.REQUEST_FUNCTION
, dmr.REQ_ENTITY_TYPE
, dmr.REQUEST_RESULT
, dmb.msg_entity_type
, dmb.msg_entity_id1
, dmt.message_text
, dmt.creation_Date
FROM
fusion.DOO_MESSAGE_requests dmr
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.doo_headers_all dha
, fusion.doo_lines_all dla
WHERE
dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
AND dmr.ACTIVE_FLAG = 'Y'
AND DMB.msg_request_id = DME.msg_request_id
AND DMB.message_id = DME.message_id
AND DMT.message_id = DMB.message_id
AND dmt.language = userenv('LANG')
AND dmb.MSG_ENTITY_TYPE IN ('SRC_LINE')
AND dmb.msg_entity_type = dme.ENTITY_NAME
AND TO_CHAR(dla.line_id) = dmb.msg_entity_id1
AND dha.header_id = dla.header_id
UNION ALL
SELECT
dha.source_order_number
, dha.order_number
, dha.header_id
, (
SELECT DISTINCT
service_name
FROM
fusion.DOO_SERVICE_DEFINITIONS_tl
WHERE
service_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'SVC'
AND language = userenv('LANG')
)
)
AS Service
, (
SELECT DISTINCT
display_name
FROM
fusion.DOO_TASK_INSTANCES sdti
, fusion.DOO_TASK_DEFINITIONS_TL sdtt
WHERE
task_instance_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'TASK'
AND language = userenv('LANG')
)
AND sdti.task_id = sdtt.task_id
AND sdtt.language = userenv('LANG')
)
AS Task
, dmr.REQUEST_FUNCTION
, dmr.REQ_ENTITY_TYPE
, dmr.REQUEST_RESULT
, dmb.msg_entity_type
, dmb.msg_entity_id1
, dmt.message_text
, dmt.creation_Date
FROM
fusion.DOO_MESSAGE_requests dmr
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.doo_headers_all dha
, fusion.doo_lines_all dla
, fusion.doo_fulfill_lines_all dfla
, FUSION.doo_ORCHESTRATION_GROUPS DOG
, fusion.DOO_TASK_INSTANCES DTI
WHERE
dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
AND dmr.ACTIVE_FLAG = 'Y'
AND DMB.msg_request_id = DME.msg_request_id
AND DMB.message_id = DME.message_id
AND DMT.message_id = DMB.message_id
AND dmt.language = userenv('LANG')
AND dmb.MSG_ENTITY_TYPE IN ('TASK')
AND dmb.msg_entity_type = dme.ENTITY_NAME
AND TO_CHAR(dti.TASK_INSTANCE_ID) = dmb.msg_entity_id1
AND dha.header_id = dfla.header_id
AND dfla.line_id = dla.line_id
AND dfla.fulfill_line_id = dog.fulfillment_line_id
AND dog.status = 'ACTIVE'
AND dog.DOO_PROCESS_INSTANCE_ID = dti.DOO_PROCESS_INSTANCE_ID
UNION ALL
SELECT
dha.source_order_number
, dha.order_number
, dha.header_id
, (
SELECT DISTINCT
service_name
FROM
fusion.DOO_SERVICE_DEFINITIONS_tl
WHERE
service_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'SVC'
AND language = userenv('LANG')
)
)
AS Service
, (
SELECT DISTINCT
display_name
FROM
fusion.DOO_TASK_INSTANCES sdti
, fusion.DOO_TASK_DEFINITIONS_TL sdtt
WHERE
task_instance_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'TASK'
AND language = userenv('LANG')
)
AND sdti.task_id = sdtt.task_id
AND sdtt.language = userenv('LANG')
)
AS Task
, dmr.REQUEST_FUNCTION
, dmr.REQ_ENTITY_TYPE
, dmr.REQUEST_RESULT
, dmb.msg_entity_type
, dmb.msg_entity_id1
, dmt.message_text
, dmt.creation_Date
FROM
fusion.DOO_MESSAGE_requests dmr
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.doo_headers_all dha
WHERE
dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
AND dmr.ACTIVE_FLAG = 'Y'
AND DMB.msg_request_id = DME.msg_request_id
AND DMB.message_id = DME.message_id
AND DMT.message_id = DMB.message_id
AND dmt.language = userenv('LANG')
AND dmb.MSG_ENTITY_TYPE IN ('SRC_ORDER','ORDER')
AND dmb.msg_entity_type = dme.ENTITY_NAME
AND TO_CHAR(dha.HEADER_ID) = dmb.msg_entity_id1
UNION ALL
SELECT
dha.source_order_number
, dha.order_number
, dha.header_id
, (
SELECT DISTINCT
service_name
FROM
fusion.DOO_SERVICE_DEFINITIONS_tl
WHERE
service_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'SVC'
AND language = userenv('LANG')
)
)
AS Service
, (
SELECT DISTINCT
display_name
FROM
fusion.DOO_TASK_INSTANCES sdti
, fusion.DOO_TASK_DEFINITIONS_TL sdtt
WHERE
task_instance_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'TASK'
AND language = userenv('LANG')
)
AND sdti.task_id = sdtt.task_id
AND sdtt.language = userenv('LANG')
)
AS Task
, dmr.REQUEST_FUNCTION
, dmr.REQ_ENTITY_TYPE
, dmr.REQUEST_RESULT
, dmb.msg_entity_type
, dmb.msg_entity_id1
, dmt.message_text
, dmt.creation_Date
FROM
fusion.DOO_MESSAGE_requests dmr
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.doo_headers_all dha
, fusion.doo_lines_all dla
, fusion.doo_fulfill_lines_all dfla
, FUSION.doo_ORCHESTRATION_GROUPS DOG
WHERE
dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
AND dmr.ACTIVE_FLAG = 'Y'
AND DMB.msg_request_id = DME.msg_request_id
AND DMB.message_id = DME.message_id
AND DMT.message_id = DMB.message_id
AND dmt.language = userenv('LANG')
AND dmb.MSG_ENTITY_TYPE IN ('PROCESS')
AND dmb.msg_entity_type = dme.ENTITY_NAME
AND TO_CHAR(dog.DOO_PROCESS_INSTANCE_ID) = dmb.msg_entity_id1
AND dha.header_id = dfla.header_id
AND dfla.line_id = dla.line_id
AND dfla.fulfill_line_id = dog.fulfillment_line_id
AND dog.status = 'ACTIVE'
order by
1
, 3
, 12 asc /
-- Errors Reported on a specific order.
-- Note: - Replace &ORDER_NUMBER and &SOURCE_ORDER_NUMBER.
SELECT
dha.source_order_number
, dha.order_number
, dha.header_id
, (
SELECT DISTINCT
service_name
FROM
fusion.DOO_SERVICE_DEFINITIONS_tl
WHERE
service_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'SVC'
AND language = userenv('LANG')
)
)
AS Service
, (
SELECT DISTINCT
display_name
FROM
fusion.DOO_TASK_INSTANCES sdti
, fusion.DOO_TASK_DEFINITIONS_TL sdtt
WHERE
task_instance_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'TASK'
AND language = userenv('LANG')
)
AND sdti.task_id = sdtt.task_id
AND sdtt.language = userenv('LANG')
)
AS Task
, dmr.REQUEST_FUNCTION
, dmr.REQ_ENTITY_TYPE
, dmr.REQUEST_RESULT
, dmb.msg_entity_type
, dmb.msg_entity_id1
, dmt.message_text
, dmt.creation_Date
FROM
fusion.DOO_MESSAGE_requests dmr
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.doo_headers_all dha
, fusion.doo_lines_all dla
, fusion.doo_fulfill_lines_all dfla
, FUSION.doo_ORCHESTRATION_GROUPS DOG
WHERE
dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
AND dmr.ACTIVE_FLAG = 'Y'
AND DMB.msg_request_id = DME.msg_request_id
AND DMB.message_id = DME.message_id
AND DMT.message_id = DMB.message_id
AND dmt.language = userenv('LANG')
AND dmb.MSG_ENTITY_TYPE IN ('FLINE')
AND dmb.msg_entity_type = dme.ENTITY_NAME
AND TO_CHAR(dfla.fulfill_line_id) = dmb.msg_entity_id1
AND dha.header_id = dfla.header_id
AND dfla.line_id = dla.line_id
AND dfla.fulfill_line_id = dog.fulfillment_line_id
AND dog.status = 'ACTIVE'
AND EXISTS
(
SELECT
1
FROM
fusion.doo_process_step_instances dpsi
WHERE
dog.group_id = dpsi.group_id
AND dpsi.step_status IN( 'IMPLICIT_WAIT','STARTED')
AND dpsi.ACTUAL_COMPLETION_DATE IS NULL
)
AND
(
dha.source_order_number = '&SOURCE_ORDER_NUMBER'
OR dha.order_number = '&ORDER_NUMBER'
)
UNION ALL
SELECT
dha.source_order_number
, dha.order_number
, dha.header_id
, (
SELECT DISTINCT
service_name
FROM
fusion.DOO_SERVICE_DEFINITIONS_tl
WHERE
service_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'SVC'
AND language = userenv('LANG')
)
)
AS Service
, (
SELECT DISTINCT
display_name
FROM
fusion.DOO_TASK_INSTANCES sdti
, fusion.DOO_TASK_DEFINITIONS_TL sdtt
WHERE
task_instance_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'TASK'
AND language = userenv('LANG')
)
AND sdti.task_id = sdtt.task_id
AND sdtt.language = userenv('LANG')
)
AS Task
, dmr.REQUEST_FUNCTION
, dmr.REQ_ENTITY_TYPE
, dmr.REQUEST_RESULT
, dmb.msg_entity_type
, dmb.msg_entity_id1
, dmt.message_text
, dmt.creation_Date
FROM
fusion.DOO_MESSAGE_requests dmr
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.doo_headers_all dha
, fusion.doo_lines_all dla
WHERE
dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
AND dmr.ACTIVE_FLAG = 'Y'
AND DMB.msg_request_id = DME.msg_request_id
AND DMB.message_id = DME.message_id
AND DMT.message_id = DMB.message_id
AND dmt.language = userenv('LANG')
AND dmb.MSG_ENTITY_TYPE IN ('SRC_LINE')
AND dmb.msg_entity_type = dme.ENTITY_NAME
AND TO_CHAR(dla.line_id) = dmb.msg_entity_id1
AND dha.header_id = dla.header_id
AND
(
dha.source_order_number = '&SOURCE_ORDER_NUMBER'
OR dha.order_number = '&ORDER_NUMBER'
)
UNION ALL
SELECT
dha.source_order_number
, dha.order_number
, dha.header_id
, (
SELECT DISTINCT
service_name
FROM
fusion.DOO_SERVICE_DEFINITIONS_tl
WHERE
service_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'SVC'
AND language = userenv('LANG')
)
)
AS Service
, (
SELECT DISTINCT
display_name
FROM
fusion.DOO_TASK_INSTANCES sdti
, fusion.DOO_TASK_DEFINITIONS_TL sdtt
WHERE
task_instance_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'TASK'
AND language = userenv('LANG')
)
AND sdti.task_id = sdtt.task_id
AND sdtt.language = userenv('LANG')
)
AS Task
, dmr.REQUEST_FUNCTION
, dmr.REQ_ENTITY_TYPE
, dmr.REQUEST_RESULT
, dmb.msg_entity_type
, dmb.msg_entity_id1
, dmt.message_text
, dmt.creation_Date
FROM
fusion.DOO_MESSAGE_requests dmr
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.doo_headers_all dha
, fusion.doo_lines_all dla
, fusion.doo_fulfill_lines_all dfla
, FUSION.doo_ORCHESTRATION_GROUPS DOG
, fusion.DOO_TASK_INSTANCES DTI
WHERE
dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
AND dmr.ACTIVE_FLAG = 'Y'
AND DMB.msg_request_id = DME.msg_request_id
AND DMB.message_id = DME.message_id
AND DMT.message_id = DMB.message_id
AND dmt.language = userenv('LANG')
AND dmb.MSG_ENTITY_TYPE IN ('TASK')
AND dmb.msg_entity_type = dme.ENTITY_NAME
AND TO_CHAR(dti.TASK_INSTANCE_ID) = dmb.msg_entity_id1
AND dha.header_id = dfla.header_id
AND dfla.line_id = dla.line_id
AND dfla.fulfill_line_id = dog.fulfillment_line_id
AND dog.status = 'ACTIVE'
AND dog.DOO_PROCESS_INSTANCE_ID = dti.DOO_PROCESS_INSTANCE_ID
AND
(
dha.source_order_number = '&SOURCE_ORDER_NUMBER'
OR dha.order_number = '&ORDER_NUMBER'
)
UNION ALL
SELECT
dha.source_order_number
, dha.order_number
, dha.header_id
, (
SELECT DISTINCT
service_name
FROM
fusion.DOO_SERVICE_DEFINITIONS_tl
WHERE
service_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'SVC'
AND language = userenv('LANG')
)
)
AS Service
, (
SELECT DISTINCT
display_name
FROM
fusion.DOO_TASK_INSTANCES sdti
, fusion.DOO_TASK_DEFINITIONS_TL sdtt
WHERE
task_instance_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'TASK'
AND language = userenv('LANG')
)
AND sdti.task_id = sdtt.task_id
AND sdtt.language = userenv('LANG')
)
AS Task
, dmr.REQUEST_FUNCTION
, dmr.REQ_ENTITY_TYPE
, dmr.REQUEST_RESULT
, dmb.msg_entity_type
, dmb.msg_entity_id1
, dmt.message_text
, dmt.creation_Date
FROM
fusion.DOO_MESSAGE_requests dmr
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.doo_headers_all dha
WHERE
dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
AND dmr.ACTIVE_FLAG = 'Y'
AND DMB.msg_request_id = DME.msg_request_id
AND DMB.message_id = DME.message_id
AND DMT.message_id = DMB.message_id
AND dmt.language = userenv('LANG')
AND dmb.MSG_ENTITY_TYPE IN ('SRC_ORDER','ORDER')
AND dmb.msg_entity_type = dme.ENTITY_NAME
AND TO_CHAR(dha.HEADER_ID) = dmb.msg_entity_id1
AND
(
dha.source_order_number = '&SOURCE_ORDER_NUMBER'
OR dha.order_number = '&ORDER_NUMBER'
)
UNION ALL
SELECT
dha.source_order_number
, dha.order_number
, dha.header_id
, (
SELECT DISTINCT
service_name
FROM
fusion.DOO_SERVICE_DEFINITIONS_tl
WHERE
service_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'SVC'
AND language = userenv('LANG')
)
)
AS Service
, (
SELECT DISTINCT
display_name
FROM
fusion.DOO_TASK_INSTANCES sdti
, fusion.DOO_TASK_DEFINITIONS_TL sdtt
WHERE
task_instance_id IN
(
SELECT
entity_id
FROM
Fusion.doo_message_entities
WHERE
message_id = dme.message_id
AND entity_name = 'TASK'
AND language = userenv('LANG')
)
AND sdti.task_id = sdtt.task_id
AND sdtt.language = userenv('LANG')
)
AS Task
, dmr.REQUEST_FUNCTION
, dmr.REQ_ENTITY_TYPE
, dmr.REQUEST_RESULT
, dmb.msg_entity_type
, dmb.msg_entity_id1
, dmt.message_text
, dmt.creation_Date
FROM
fusion.DOO_MESSAGE_requests dmr
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.doo_headers_all dha
, fusion.doo_lines_all dla
, fusion.doo_fulfill_lines_all dfla
, FUSION.doo_ORCHESTRATION_GROUPS DOG
WHERE
dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
AND dmr.ACTIVE_FLAG = 'Y'
AND DMB.msg_request_id = DME.msg_request_id
AND DMB.message_id = DME.message_id
AND DMT.message_id = DMB.message_id
AND dmt.language = userenv('LANG')
AND dmb.MSG_ENTITY_TYPE IN ('PROCESS')
AND dmb.msg_entity_type = dme.ENTITY_NAME
AND TO_CHAR(dog.DOO_PROCESS_INSTANCE_ID) = dmb.msg_entity_id1
AND dha.header_id = dfla.header_id
AND dfla.line_id = dla.line_id
AND dfla.fulfill_line_id = dog.fulfillment_line_id
AND dog.status = 'ACTIVE'
AND
(
dha.source_order_number = '&SOURCE_ORDER_NUMBER'
OR dha.order_number = '&ORDER_NUMBER'
)
ORDER BY
1
, 3
, 12 ASC /
-- Errors reported on ALL imported orders.
SELECT DISTINCT
DME.entity_name
, dohai.BATCH_NAME
, dohai.SUB_BATCH_NAME
, dohai.source_transaction_number
, DME.entity_id
, dmb.MSG_ENTITY_ID2
, dohai.creation_date
, DMT.message_text
, DMB.message_type
, dmt.creation_Date
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'
AND dmt.language = userenv('LANG')
UNION
SELECT DISTINCT
DME.entity_name
, dohai.BATCH_NAME
, dohai.SUB_BATCH_NAME
, dohai.source_transaction_number
, DME.entity_id
, dmb.MSG_ENTITY_ID2
, dohai.creation_date
, DMT.message_text
, DMB.message_type
, dmt.creation_Date
FROM
fusion.DOO_ORDER_HEADERS_ALL_INT dohai
, fusion.DOO_ORDER_LINES_ALL_INT doLai
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.DOO_MESSAGE_requests dmr
WHERE
dohai.SOURCE_TRANSACTION_ID = dolai.SOURCE_TRANSACTION_ID
AND dohai.source_transaction_number = DME.entity_id
AND dolai.SOURCE_TRANSACTION_LINE_ID = dmb.MSG_ENTITY_ID2
AND DME.entity_name IN ('SRC_LINE')
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'
AND dmt.language = userenv('LANG')
ORDER BY
2
, 3
, 4
, 10 ASC /
-- Errors reported on a specific imported order.
-- Note: - Replace &SOURCE_ORDER_NUMBER.
SELECT DISTINCT
DME.entity_name
, dohai.BATCH_NAME
, dohai.SUB_BATCH_NAME
, dohai.source_transaction_number
, DME.entity_id
, dmb.MSG_ENTITY_ID2
, dohai.creation_date
, DMT.message_text
, DMB.message_type
, dmt.creation_Date
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'
AND dmt.language = userenv('LANG')
and dohai.SOURCE_TRANSACTION_NUMBER = '&SOURCE_ORDER_NUMBER'
UNION
SELECT DISTINCT
DME.entity_name
, dohai.BATCH_NAME
, dohai.SUB_BATCH_NAME
, dohai.source_transaction_number
, DME.entity_id
, dmb.MSG_ENTITY_ID2
, dohai.creation_date
, DMT.message_text
, DMB.message_type
, dmt.creation_Date
FROM
fusion.DOO_ORDER_HEADERS_ALL_INT dohai
, fusion.DOO_ORDER_LINES_ALL_INT doLai
, FUSION.DOO_MESSAGE_ENTITIES DME
, fusion.DOO_MESSAGES_B DMB
, fusion.DOO_MESSAGES_TL DMT
, fusion.DOO_MESSAGE_requests dmr
WHERE
dohai.SOURCE_TRANSACTION_ID = dolai.SOURCE_TRANSACTION_ID
AND dohai.source_transaction_number = DME.entity_id
AND dolai.SOURCE_TRANSACTION_LINE_ID = dmb.MSG_ENTITY_ID2
AND DME.entity_name IN ('SRC_LINE')
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'
AND dmt.language = userenv('LANG')
and dohai.SOURCE_TRANSACTION_NUMBER = '&SOURCE_ORDER_NUMBER'
ORDER BY
2
, 3
, 4
, 10 ASC