1. Tables and Views for SCM
  2. CMR_TRANSACTION_ERRORS_V

CMR_TRANSACTION_ERRORS_V

Details

  • Schema: FUSION

  • Object owner: CMR

  • Object type: VIEW

Columns

Name

TRANSACTION_ERROR_ID

TRANSACTION_TYPE

SOURCE_DOCUMENT_CODE

RECEIPT_NUMBER

PO_NUMBER

TRANSACTION_DATE

TRANSACTION_QUANTITY

REQUEST_ID

ERROR_CODE

ERROR_CATEGORY

EXCEPTION_SOURCE

PRICE

INVENTORY_ITEM_ID

BUSINESS_UNIT_ID

TABLE_NAME

TOKEN1_NAME

TOKEN1_VALUE

TOKEN2_NAME

TOKEN2_VALUE

Query

SQL_Statement

SELECT distinct CTE.TRANSACTION_ERROR_ID,

CIRT.TRANSACTION_TYPE,

DECODE(CIRT.SOURCE_DOCUMENT_CODE, 'TRANSFER ORDER', 'TO', CIRT.SOURCE_DOCUMENT_CODE) SOURCE_DOCUMENT_CODE,

CIRT.RECEIPT_NUMBER,

(select CPOD.PO_NUMBER from cmr_purchase_order_dtls cpod where cpod.po_line_location_id = cirt.po_line_location_id and rownum<=1) PO_NUMBER,

CIRT.TRANSACTION_DATE,

CIRT.TRANSACTION_QUANTITY,

CPE.REQUEST_ID,

CPE.ERROR_CODE,

CPE.ERROR_CATEGORY,

CPE.EXCEPTION_SOURCE,

(select CPOD.price from cmr_purchase_order_dtls cpod where cpod.po_line_location_id = cirt.po_line_location_id and rownum<=1) PRICE,

CIRT.INVENTORY_ITEM_ID,

CPE.BUSINESS_UNIT_ID,

CPE.TABLE_NAME,

CPE.TOKEN1_NAME,

CPE.TOKEN1_VALUE,

CPE.TOKEN2_NAME,

CPE.TOKEN2_VALUE

FROM CMR_PROCESS_ERRORS CPE,

CMR_TRANSACTION_ERRORS CTE,

CMR_I_RCV_TRANSACTIONS CIRT

WHERE cpe.table_name ='CMR_I_RCV_TRANSACTIONS'

AND cte.cmr_process_error_id = cpe.cmr_process_error_id

AND cte.request_id = cpe.request_id

AND CTE.EXTERNAL_SYSTEM_REF_ID = CIRT.EXTERNAL_SYSTEM_REF_ID

AND CTE.EXTERNAL_SYSTEM_REFERENCE = CIRT.EXTERNAL_SYSTEM_REFERENCE

UNION ALL

/* CMR_RCV_TRANSACTIONS */

SELECT distinct CTE.TRANSACTION_ERROR_ID,

crt.transaction_type,

DECODE(CRT.SOURCE_DOCUMENT_CODE, 'TRANSFER ORDER', 'TO', CRT.SOURCE_DOCUMENT_CODE) SOURCE_DOCUMENT_CODE,

crt.receipt_number,

(select CPOD.PO_NUMBER from cmr_purchase_order_dtls cpod where cpod.po_line_location_id = crt.po_line_location_id and rownum<=1) PO_NUMBER,

crt.transaction_date,

crt.transaction_quantity,

cpe.request_id,

CPE.ERROR_CODE,

CPE.ERROR_CATEGORY,

Cpe.EXCEPTION_SOURCE,

(select CPOD.price from cmr_purchase_order_dtls cpod where cpod.po_line_location_id = crt.po_line_location_id and rownum<=1) PRICE,

CRT.INVENTORY_ITEM_ID,

cpe. business_unit_id,

cpe.table_name,

CPE.TOKEN1_NAME,

CPE.TOKEN1_VALUE,

CPE.TOKEN2_NAME,

CPE.TOKEN2_VALUE

FROM CMR_PROCESS_ERRORS CPE,

CMR_TRANSACTION_ERRORS CTE,

CMR_RCV_TRANSACTIONS CRT

WHERE cpe.table_name ='CMR_RCV_TRANSACTIONS'

AND cte.cmr_process_error_id = cpe.cmr_process_error_id

AND cte.request_id = cpe.request_id

AND cte.cmr_rcv_transaction_id = crt.cmr_rcv_transaction_id

UNION ALL

/* CMR_TRANSACTIONS */

SELECT distinct CTE.TRANSACTION_ERROR_ID,

CT.TRANSACTION_TYPE,

DECODE(CT.SOURCE_DOCUMENT_CODE, 'TRANSFER ORDER', 'TO', CT.SOURCE_DOCUMENT_CODE) SOURCE_DOCUMENT_CODE,

CT.RECEIPT_NUMBER,

NULL PO_NUMBER,

CT.TRANSACTION_DATE,

CT.TRANSACTION_QTY,

CPE.REQUEST_ID,

CPE.ERROR_CODE,

CPE.ERROR_CATEGORY,

CPE.EXCEPTION_SOURCE,

NULL PRICE,

ct.INVENTORY_ITEM_ID,

CPE.BUSINESS_UNIT_ID,

CPE.TABLE_NAME,

CPE.TOKEN1_NAME,

CPE.TOKEN1_VALUE,

CPE.TOKEN2_NAME,

CPE.TOKEN2_VALUE

FROM cmr_process_errors cpe,

cmr_transaction_errors cte,

cmr_transactions ct

WHERE cpe.table_name = 'CMR_TRANSACTIONS'

AND cte.cmr_process_error_id = cpe.cmr_process_error_id

AND cte.request_id = cpe.request_id

AND CTE.CMR_RCV_TRANSACTION_ID = ct.CMR_RCV_TRANSACTION_ID

UNION ALL

/* CMR_RCV_EVENTS */

SELECT distinct CTE.TRANSACTION_ERROR_ID,

cre.TRANSACTION_TYPE_CODE,

Cre.EVENT_SOURCE,

cre.sla_transaction_number RECEIPT_NUMBER,

cre.source_doc_number PO_NUMBER,

Cre.TRANSACTION_DATE,

CRe.TRANSACTION_QTY,

CPE.REQUEST_ID,

CPE.ERROR_CODE,

CPE.ERROR_CATEGORY,

CPE.EXCEPTION_SOURCE,

NULL PRICE,

cre.INVENTORY_ITEM_ID,

CPE.BUSINESS_UNIT_ID,

CPE.TABLE_NAME,

CPE.TOKEN1_NAME,

CPE.TOKEN1_VALUE,

CPE.TOKEN2_NAME,

CPE.TOKEN2_VALUE

FROM cmr_process_errors cpe,

cmr_transaction_errors cte,

CMR_RCV_EVENTS cre

WHERE cpe.table_name = 'CMR_RCV_EVENTS'

AND cte.cmr_process_error_id = cpe.cmr_process_error_id

AND cte.request_id = cpe.request_id

AND CTE.accounting_event_id = cre.accounting_event_id

UNION ALL

/* CMR_I_PURCHASE_ORDER_DTLS */

SELECT distinct CTE.TRANSACTION_ERROR_ID,

NULL TRANSACTION_TYPE,

'PO' SOURCE_DOCUMENT_CODE,

NULL RECEIPT_NUMBER,

cipod.PO_NUMBER,

NULL TRANSACTION_DATE,

NULL TRANSACTION_QUANTITY,

CPE.REQUEST_ID,

CPE.ERROR_CODE,

CPE.ERROR_CATEGORY,

CPE.EXCEPTION_SOURCE,

cipod.PRICE PRICE,

cipod.INVENTORY_ITEM_ID,

CPE.BUSINESS_UNIT_ID,

CPE.TABLE_NAME,

CPE.TOKEN1_NAME,

CPE.TOKEN1_VALUE,

CPE.TOKEN2_NAME,

CPE.TOKEN2_VALUE

FROM cmr_process_errors cpe,

cmr_transaction_errors cte,

CMR_I_PURCHASE_ORDER_DTLS cipod

WHERE cpe.table_name = 'CMR_I_PURCHASE_ORDER_DTLS'

AND cte.cmr_process_error_id = cpe.cmr_process_error_id

AND cte.request_id = cpe.request_id

and ( cte.external_system_ref_id = cipod.external_system_ref_id and cte.external_system_ref_id = cipod.external_system_ref_id )

UNION ALL

/*CST_TRADE_EVENTS */

SELECT distinct cte.transaction_error_id,

ctre.transaction_type,

ctre.txn_source_doc_type,

NULL,

CTRE.TXN_SOURCE_DOC_NUMBER,

CTRE.TRANSACTION_DATE,

CTRE.TRANSACTION_QTY,

CPE.REQUEST_ID,

cpe.error_code,

cpe.error_category,

cpe.exception_source,

NULL,

cpe.inventory_item_id,

cpe. business_unit_id,

cpe.table_name,

CPE.TOKEN1_NAME,

CPE.TOKEN1_VALUE,

CPE.TOKEN2_NAME,

CPE.TOKEN2_VALUE

FROM CMR_PROCESS_ERRORS CPE,

CMR_TRANSACTION_ERRORS CTE,

CST_TRADE_EVENTS CTRE

WHERE cpe.table_name ='CST_TRADE_EVENTS'

AND cte.request_id = cpe.request_id

AND cte.cmr_process_error_id = cpe.cmr_process_error_id

AND ctre.trade_event_id = cte.trade_event_id

UNION ALL

SELECT distinct cte.transaction_error_id,

ct.transaction_type,

'PO',

ct.receipt_number ||': Charge: ' || (select cr.name from cml_charges_vl cr where cr.charge_id = crce.charge_id) receipt_number,

cpod.po_number po_number,

ct.transaction_date,

ct.source_doc_qty,

cpe.request_id,

cpe.error_code,

cpe.error_category,

cpe.exception_source,

cpod.price PRICE,

cpod.inventory_item_id,

ct.profit_center_business_unit_id,

cpe.table_name,

CPE.TOKEN1_NAME,

CPE.TOKEN1_VALUE,

CPE.TOKEN2_NAME,

CPE.TOKEN2_VALUE

FROM cmr_process_errors cpe,

cmr_transaction_errors cte,

cml_rcv_charge_estimates crce,

cmr_purchase_order_dtls cpod,

cmr_transactions ct

WHERE cpe.table_name in ('CML_RCV_CHARGE_ESTIMATES')

AND cte.request_id = cpe.request_id

AND cte.cmr_process_error_id = cpe.cmr_process_error_id

AND crce.rcv_charge_estimate_id = cte.rcv_charge_estimate_id

and crce.cmr_po_line_location_id = cpod.cmr_po_line_location_id

and cpod.active_flag = 'Y'

and crce.cmr_po_line_location_id = ct.cmr_po_line_location_id

and crce.cmr_rcv_transaction_id = ct.cmr_rcv_transaction_id

and ct.cmr_po_distribution_id = cpod.cmr_po_distribution_id

UNION ALL

SELECT distinct cte.transaction_error_id,

ct.transaction_type,

'PO',

ct.receipt_number ||': Charge: ' || (select cr.name from cml_charges_vl cr where cr.charge_id = crce.charge_id) receipt_number,

cpod.po_number po_number,

ct.transaction_date,

ct.source_doc_qty,

cpe.request_id,

cpe.error_code,

cpe.error_category,

cpe.exception_source,

cpod.price PRICE,

cpod.inventory_item_id,

ct.profit_center_business_unit_id,

cpe.table_name,

CPE.TOKEN1_NAME,

CPE.TOKEN1_VALUE,

CPE.TOKEN2_NAME,

CPE.TOKEN2_VALUE

FROM cmr_process_errors cpe,

cmr_transaction_errors cte,

CML_RCV_CHARGE_ACTUALS crce,

cmr_purchase_order_dtls cpod,

cmr_transactions ct

WHERE cpe.table_name in ('CML_RCV_CHARGE_ACTUALS')

AND cte.request_id = cpe.request_id

AND cte.cmr_process_error_id = cpe.cmr_process_error_id

AND crce.rcv_charge_actual_id = cte.rcv_charge_actual_id

and crce.cmr_po_line_location_id = cpod.cmr_po_line_location_id

and cpod.active_flag = 'Y'

and crce.cmr_po_line_location_id = ct.cmr_po_line_location_id

and crce.cmr_rcv_transaction_id = ct.cmr_rcv_transaction_id

and ct.cmr_po_distribution_id = cpod.cmr_po_distribution_id

UNION ALL

SELECT distinct cte.transaction_error_id,

crt.transaction_type || ': Invoice Type - '|| ciaid.invoice_type,

crt.source_document_code,

crt.receipt_number || ': Invoice - '|| ciaid.invoice_number,

CPOD.PO_NUMBER,

ciaid.accounting_DATE,

crt.transaction_quantity,

CPE.REQUEST_ID,

cpe.error_code,

cpe.error_category,

cpe.exception_source,

cpod.price,

cpod.inventory_item_id,

cpe. business_unit_id,

cpe.table_name,

CPE.TOKEN1_NAME,

CPE.TOKEN1_VALUE,

CPE.TOKEN2_NAME,

CPE.TOKEN2_VALUE

FROM CMR_PROCESS_ERRORS CPE,

CMR_TRANSACTION_ERRORS CTE,

CMR_I_AP_INVOICE_DTLS CIAID,

cmr_purchase_order_dtls cpod,

cmr_rcv_transactions crt

WHERE

cpe.table_name = 'CMR_I_AP_INVOICE_DTLS'

AND cte.request_id = cpe.request_id

AND cte.cmr_process_error_id = cpe.cmr_process_error_id

and CTE.EXTERNAL_SYSTEM_REFERENCE = CIAID.EXTERNAL_SYSTEM_REFERENCE

and CTE.EXTERNAL_SYSTEM_REF_ID = CIAID.EXTERNAL_SYSTEM_REF_ID

AND ciaid.external_system_reference = cpod.external_system_reference(+)

AND ciaid.po_distribution_id = cpod.external_system_ref_id (+)

AND ciaid.external_system_reference = crt.external_system_reference(+)

AND ciaid.rcv_transaction_id = crt.external_system_ref_id(+)

UNION ALL

SELECT distinct cte.transaction_error_id,

crt.transaction_type,

crt.source_document_code,

crt.receipt_number,

CPOD.PO_NUMBER,

crt.TRANSACTION_DATE,

crt.transaction_quantity,

CPE.REQUEST_ID,

cpe.error_code,

cpe.error_category,

cpe.exception_source,

cpod.price,

cpod.inventory_item_id,

cpe. business_unit_id,

cpe.table_name,

CPE.TOKEN1_NAME,

CPE.TOKEN1_VALUE,

CPE.TOKEN2_NAME,

CPE.TOKEN2_VALUE

FROM CMR_PROCESS_ERRORS CPE,

fusion.CMR_TRANSACTION_ERRORS CTE,

cmr_purchase_order_dtls cpod,

cmr_rcv_transactions crt

WHERE cpe.table_name = 'CMR_PURCHASE_ORDER_DTLS'

AND cte.request_id = cpe.request_id

AND cte.cmr_process_error_id = cpe.cmr_process_error_id

and cte.cmr_po_distribution_id = cpod.cmr_po_distribution_id

and cte.cmr_rcv_transaction_id = crt.cmr_rcv_transaction_id