CSE_TXN_ERRORS

Details

Schema: FUSION
Object owner: CSE
Object type: VIEW

Columns

Name
TRANSACTION_SOURCE TRANSACTION_ID WORKER_ID TRANSACTION_DATE TRANSACTION_SOURCE_ID TRANSACTION_SOURCE_LINE_ID INVENTORY_ITEM_ID INTERFACE_ROW_ID STATUS_CODE ERROR_TEXT SOURCE_SYSTEM_TYPE SOURCE_SYSTEM TRANSACTION_TYPE

Name

TRANSACTION_SOURCE

TRANSACTION_ID

WORKER_ID

TRANSACTION_DATE

TRANSACTION_SOURCE_ID

TRANSACTION_SOURCE_LINE_ID

INVENTORY_ITEM_ID

INTERFACE_ROW_ID

STATUS_CODE

ERROR_TEXT

SOURCE_SYSTEM_TYPE

SOURCE_SYSTEM

TRANSACTION_TYPE

Query

SQL_Statement
SELECT wie_txns.transaction_source, wie_txns.transaction_id, wie_txns.worker_id, wie_txns.transaction_date, to_char(wie_txns.transaction_source_id) transaction_source_id, wie_txns.transaction_source_line_id, wie_txns.inventory_item_id, wie_txns.interface_row_id, wie_txns.status_code, wie_errors.error_text, wie_txns.SOURCE_SYSTEM_TYPE, wie_txns.SOURCE_SYSTEM, wie_txns.TRANSACTION_TYPE FROM (SELECT 'WIE_INV_TXN' transaction_source, wmti.inv_transaction_id transaction_id, wmti.worker_id worker_id, imt.transaction_date transaction_date, wmti.work_order_id transaction_source_id, wmti.wo_operation_id transaction_source_line_id, wmti.inventory_item_id inventory_item_id, wmti.interface_row_id interface_row_id, wmti.interface_status_code status_code, wmti.source_system_type SOURCE_SYSTEM_TYPE, 'Common Work Execution' SOURCE_SYSTEM, (SELECT meaning from fnd_lookups lkps WHERE wmti.TRANSACTION_TYPE_CODE = lkps.lookup_code AND lkps.lookup_type = 'ORA_WIE_MTL_TXN_TYPE') TRANSACTION_TYPE FROM wie_material_transactions_int wmti, inv_material_txns imt WHERE (wmti.interface_status_code = 'GENEALOGY_ERROR' OR wmti.interface_status_code = 'COMPLETED') AND wmti.worker_id is not null AND imt.transaction_id = inv_transaction_id UNION SELECT 'WIE_OPER_TXN' transaction_source, woti.wo_operation_transaction_id transaction_id, woti.worker_id worker_id, wot.transaction_date transaction_date, woti.work_order_id transaction_source_id, woti.wo_operation_id transaction_source_line_id, woti.inventory_item_id inventory_item_id, woti.interface_row_id interface_row_id, woti.interface_status_code status_code, woti.source_system_type SOURCE_SYSTEM_TYPE, 'Common Work Execution' SOURCE_SYSTEM, (SELECT meaning from fnd_lookups lkps WHERE wot.TRANSACTION_TYPE_CODE = lkps.lookup_code AND lkps.lookup_type = 'ORA_WIE_OP_TRANSACTION_TYPE') TRANSACTION_TYPE FROM wie_operation_transactions_int woti, wie_operation_transactions wot WHERE (woti.interface_status_code = 'GENEALOGY_ERROR' OR woti.interface_status_code = 'COMPLETED') AND woti.worker_id is not null AND wot.wo_operation_transaction_id = woti.wo_operation_transaction_id) wie_txns, wie_int_errors wie_errors WHERE wie_errors.interface_row_id(+) = wie_txns.interface_row_id UNION SELECT 'INV_MTL_TXN', imt.transaction_id, null, imt.transaction_date, to_char(imt.transaction_source_id) transaction_source_id, imt.trx_source_line_id, imt.inventory_item_id, null, imt.alm_interface_status, imt.alm_interface_error_code, 'Internal' SOURCE_SYSTEM_TYPE, 'Inventory Management' SOURCE_SYSTEM, (SELECT TRANSACTION_TYPE_NAME FROM INV_TRANSACTION_TYPES_VL WHERE imt.TRANSACTION_TYPE_ID = TRANSACTION_TYPE_ID) TRANSACTION_TYPE FROM inv_material_txns imt, egp_system_items_vl egi WHERE imt.alm_interface_status = 'ORA_P' AND imt.inventory_item_id = egi.inventory_item_id AND imt.organization_id = egi.organization_id AND (egi.asset_tracked_flag in ('Y','C') or egi.enable_genealogy_tracking_flag ='Y') UNION SELECT 'RCV_TXN', rt.transaction_id, rt.parent_transaction_id, rt.transaction_date, to_char(nvl(rt.po_header_id,rt.transfer_order_header_id)) header_id, nvl(rt.po_line_id, rt.transfer_order_line_id) line_id, rsl.item_id, null, rt.alm_interface_status, null, 'Internal' SOURCE_SYSTEM_TYPE, 'Receiving' SOURCE_SYSTEM, (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'RCV_TRANSACTION_TYPE' and lookup_code = rt.transaction_type) TRANSACTION_TYPE from fusion.rcv_transactions rt, rcv_shipment_lines rsl, egp_system_items esi where rt.alm_interface_status = 'ORA_P' and rsl.shipment_line_id = rt.shipment_line_id AND esi.inventory_item_id = rsl.item_id AND esi.organization_id = rt.organization_id AND (esi.asset_tracked_flag in ('Y','C') or esi.enable_genealogy_tracking_flag ='Y') UNION SELECT 'DOO_FULFILLMENT' transaction_source, interface_row_id transaction_id, orchestration_group_id worker_id, transaction_date transaction_date, source_order_number transaction_source_id, header_id transaction_source_line_id, null inventory_item_id, interface_row_id interface_row_id, interface_status_code status_code, error_text error_text, 'Internal' source_system_type, 'Order Management' source_system, transaction_type_code transaction_type from cse_om_orders_int where interface_status_code IN ('READY', 'ERROR')

SQL_Statement

SELECT wie_txns.transaction_source,

wie_txns.transaction_id,

wie_txns.worker_id,

wie_txns.transaction_date,

to_char(wie_txns.transaction_source_id) transaction_source_id,

wie_txns.transaction_source_line_id,

wie_txns.inventory_item_id,

wie_txns.interface_row_id,

wie_txns.status_code,

wie_errors.error_text,

wie_txns.SOURCE_SYSTEM_TYPE,

wie_txns.SOURCE_SYSTEM,

wie_txns.TRANSACTION_TYPE

FROM (SELECT 'WIE_INV_TXN' transaction_source,

wmti.inv_transaction_id transaction_id,

wmti.worker_id worker_id,

imt.transaction_date transaction_date,

wmti.work_order_id transaction_source_id,

wmti.wo_operation_id transaction_source_line_id,

wmti.inventory_item_id inventory_item_id,

wmti.interface_row_id interface_row_id,

wmti.interface_status_code status_code,

wmti.source_system_type SOURCE_SYSTEM_TYPE,

'Common Work Execution' SOURCE_SYSTEM,

(SELECT meaning from fnd_lookups lkps

WHERE wmti.TRANSACTION_TYPE_CODE = lkps.lookup_code

AND lkps.lookup_type = 'ORA_WIE_MTL_TXN_TYPE') TRANSACTION_TYPE

FROM wie_material_transactions_int wmti,

inv_material_txns imt

WHERE (wmti.interface_status_code = 'GENEALOGY_ERROR' OR wmti.interface_status_code = 'COMPLETED')

AND wmti.worker_id is not null

AND imt.transaction_id = inv_transaction_id

UNION

SELECT 'WIE_OPER_TXN' transaction_source,

woti.wo_operation_transaction_id transaction_id,

woti.worker_id worker_id,

wot.transaction_date transaction_date,

woti.work_order_id transaction_source_id,

woti.wo_operation_id transaction_source_line_id,

woti.inventory_item_id inventory_item_id,

woti.interface_row_id interface_row_id,

woti.interface_status_code status_code,

woti.source_system_type SOURCE_SYSTEM_TYPE,

'Common Work Execution' SOURCE_SYSTEM,

(SELECT meaning from fnd_lookups lkps

WHERE wot.TRANSACTION_TYPE_CODE = lkps.lookup_code

AND lkps.lookup_type = 'ORA_WIE_OP_TRANSACTION_TYPE') TRANSACTION_TYPE

FROM wie_operation_transactions_int woti,

wie_operation_transactions wot

WHERE (woti.interface_status_code = 'GENEALOGY_ERROR' OR woti.interface_status_code = 'COMPLETED')

AND woti.worker_id is not null

AND wot.wo_operation_transaction_id = woti.wo_operation_transaction_id) wie_txns,

wie_int_errors wie_errors

WHERE wie_errors.interface_row_id(+) = wie_txns.interface_row_id

UNION

SELECT 'INV_MTL_TXN',

imt.transaction_id,

null,

imt.transaction_date,

to_char(imt.transaction_source_id) transaction_source_id,

imt.trx_source_line_id,

imt.inventory_item_id,

null,

imt.alm_interface_status,

imt.alm_interface_error_code,

'Internal' SOURCE_SYSTEM_TYPE,

'Inventory Management' SOURCE_SYSTEM,

(SELECT TRANSACTION_TYPE_NAME

FROM INV_TRANSACTION_TYPES_VL

WHERE imt.TRANSACTION_TYPE_ID = TRANSACTION_TYPE_ID) TRANSACTION_TYPE

FROM inv_material_txns imt,

egp_system_items_vl egi

WHERE imt.alm_interface_status = 'ORA_P'

AND imt.inventory_item_id = egi.inventory_item_id

AND imt.organization_id = egi.organization_id

AND (egi.asset_tracked_flag in ('Y','C') or egi.enable_genealogy_tracking_flag ='Y')

UNION

SELECT 'RCV_TXN',

rt.transaction_id,

rt.parent_transaction_id,

rt.transaction_date,

to_char(nvl(rt.po_header_id,rt.transfer_order_header_id)) header_id,

nvl(rt.po_line_id, rt.transfer_order_line_id) line_id,

rsl.item_id,

null,

rt.alm_interface_status,

null,

'Internal' SOURCE_SYSTEM_TYPE,

'Receiving' SOURCE_SYSTEM,

(SELECT meaning

FROM fnd_lookups

WHERE lookup_type = 'RCV_TRANSACTION_TYPE' and lookup_code = rt.transaction_type) TRANSACTION_TYPE

from fusion.rcv_transactions rt,

rcv_shipment_lines rsl,

egp_system_items esi

where rt.alm_interface_status = 'ORA_P'

and rsl.shipment_line_id = rt.shipment_line_id

AND esi.inventory_item_id = rsl.item_id

AND esi.organization_id = rt.organization_id

AND (esi.asset_tracked_flag in ('Y','C') or esi.enable_genealogy_tracking_flag ='Y')

UNION

SELECT 'DOO_FULFILLMENT' transaction_source,

interface_row_id transaction_id,

orchestration_group_id worker_id,

transaction_date transaction_date,

source_order_number transaction_source_id,

header_id transaction_source_line_id,

null inventory_item_id,

interface_row_id interface_row_id,

interface_status_code status_code,

error_text error_text,

'Internal' source_system_type,

'Order Management' source_system,

transaction_type_code transaction_type

from cse_om_orders_int

where interface_status_code IN ('READY', 'ERROR')