CST_TRANSACTION_ERRORS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
CP_ERROR_ID EXTERNAL_SYSTEM_REFERENCE EXTERNAL_SYSTEM_REF_ID TRANSACTION_ID WO_RES_EXT_SYSTEM_REF_ID WO_OP_EXT_SYSTEM_REF_ID WO_EXT_SYSTEM_REF_ID INVENTORY_ORG_ID INVENTORY_ITEM_ID TRANSACTION_DATE BASE_TXN_TYPE_ID BASE_TXN_SOURCE_TYPE_ID BASE_TXN_ACTION_ID TRANSACTION_QTY TRANSACTION_UOM_CODE TXN_SOURCE_DOC_TYPE TXN_SOURCE_DOC_NUMBER TXN_SOURCE_REF_DOC_TYPE TXN_SOURCE_REF_DOC_NUMBER COST_ORG_ID COST_BOOK_ID VAL_UNIT_ID REQUEST_ID PROCESSOR_NAME ERROR_CODE ROW_COUNT RUN_CONTROL MESSAGE_TYPE MESSAGE TABLE_NAME SUBPROCESSOR_NAME CREATION_DATE TABLE_ROW_ID LOT_NUMBER SERIAL_NUMBER ACCOUNTED_DATE PO_ITEM_DESCRIPTION TRANSACTION_CURRENCY_CODE

Name

CP_ERROR_ID

EXTERNAL_SYSTEM_REFERENCE

EXTERNAL_SYSTEM_REF_ID

TRANSACTION_ID

WO_RES_EXT_SYSTEM_REF_ID

WO_OP_EXT_SYSTEM_REF_ID

WO_EXT_SYSTEM_REF_ID

INVENTORY_ORG_ID

INVENTORY_ITEM_ID

TRANSACTION_DATE

BASE_TXN_TYPE_ID

BASE_TXN_SOURCE_TYPE_ID

BASE_TXN_ACTION_ID

TRANSACTION_QTY

TRANSACTION_UOM_CODE

TXN_SOURCE_DOC_TYPE

TXN_SOURCE_DOC_NUMBER

TXN_SOURCE_REF_DOC_TYPE

TXN_SOURCE_REF_DOC_NUMBER

COST_ORG_ID

COST_BOOK_ID

VAL_UNIT_ID

REQUEST_ID

PROCESSOR_NAME

ERROR_CODE

ROW_COUNT

RUN_CONTROL

MESSAGE_TYPE

MESSAGE

TABLE_NAME

SUBPROCESSOR_NAME

CREATION_DATE

TABLE_ROW_ID

LOT_NUMBER

SERIAL_NUMBER

ACCOUNTED_DATE

PO_ITEM_DESCRIPTION

TRANSACTION_CURRENCY_CODE

Query

SQL_Statement
SELECT /+ cardinality(a 2) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, B.INVENTORY_ORG_ID, B.INVENTORY_ITEM_ID, B.TRANSACTION_DATE, B.BASE_TXN_TYPE_ID, B.BASE_TXN_SOURCE_TYPE_ID, B.BASE_TXN_ACTION_ID, NVL2(E.CST_INV_TRANSACTION_DTL_ID, E.TRANSACTION_QTY, B.TRANSACTION_QTY) TRANSACTION_QTY, B.TRANSACTION_UOM_CODE, B.TXN_SOURCE_DOC_TYPE, B.TXN_SOURCE_DOC_NUMBER, B.TXN_SOURCE_REF_DOC_TYPE, B.TXN_SOURCE_REF_DOC_NUMBER, C.COST_ORG_ID , C.COST_BOOK_ID , C.VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , E.LOT_NUMBER, E.SERIAL_NUMBER, D.ACCOUNTED_DATE, C.PO_ITEM_DESCRIPTION, C.TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, CST_INV_TRANSACTIONS B, CST_TRANSACTIONS C, CST_CP_ERRORS D, CST_INV_TRANSACTION_DTLS E WHERE A.EXTERNAL_SYSTEM_REFERENCE = B.EXTERNAL_SYSTEM_REFERENCE AND A.EXTERNAL_SYSTEM_REF_ID = B.EXTERNAL_SYSTEM_REF_ID AND A.TRANSACTION_ID = C.TRANSACTION_ID(+) AND A.CP_ERROR_ID = D.CP_ERROR_ID AND C.CST_INV_TRANSACTION_DTL_ID = E.CST_INV_TRANSACTION_DTL_ID(+) AND NVL(D.TABLE_NAME, 'TBL_BLANK') NOT IN ('CST_TRADE_EVENTS','CST_I_TRADE_EVENTS', 'CMR_I_RCV_TRANSACTIONS') UNION ALL SELECT /+ cardinality(a 2) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, B.INVENTORY_ORG_ID , B.INVENTORY_ITEM_ID , B.TRANSACTION_DATE , B.TRANSACTION_TYPE_ID AS BASE_TXN_TYPE_ID, B.TRANSACTION_SOURCE_TYPE_ID AS BASE_TXN_SOURCE_TYPE_ID, B.TRANSACTION_ACTION_ID AS BASE_TXN_ACTION_ID, NVL2(E.CST_INV_TRANSACTION_DTLS_ID, E.TRANSACTION_QTY, B.TRANSACTION_QTY) TRANSACTION_QTY, B.TRANSACTION_UOM_CODE, B.TXN_SOURCE_DOC_TYPE, B.TXN_SOURCE_DOC_NUMBER, B.TXN_SOURCE_REF_DOC_TYPE, B.TXN_SOURCE_REF_DOC_NUMBER, NULL AS COST_ORG_ID , NULL AS COST_BOOK_ID , NULL AS VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , E.LOT_NUMBER, E.SERIAL_NUMBER, D.ACCOUNTED_DATE, B.PO_ITEM_DESCRIPTION, B.TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, CST_I_INV_TRANSACTIONS B, CST_CP_ERRORS D, CST_I_INV_TRANSACTION_DTLS E WHERE A.EXTERNAL_SYSTEM_REFERENCE = B.EXTERNAL_SYSTEM_REFERENCE AND A.EXTERNAL_SYSTEM_REF_ID = B.EXTERNAL_SYSTEM_REF_ID AND A.CP_ERROR_ID = D.CP_ERROR_ID AND A.EXTERNAL_SYSTEM_REFERENCE = E.EXTERNAL_SYSTEM_REFERENCE(+) AND A.EXTERNAL_SYSTEM_REF_ID = E.EXTERNAL_SYSTEM_REF_ID(+) AND NVL(D.TABLE_NAME, 'TBL_BLANK') NOT IN ('CST_TRADE_EVENTS','CST_I_TRADE_EVENTS', 'CMR_I_RCV_TRANSACTIONS') UNION ALL SELECT /+ cardinality(a 2) leading(d a b c) use_hash(c) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, B.INVENTORY_ORG_ID , B.INVENTORY_ITEM_ID , B.TRANSACTION_DATE , C.BASE_TXN_ACTION_ID AS BASE_TXN_TYPE_ID, C.BASE_TXN_SOURCE_TYPE_ID AS BASE_TXN_SOURCE_TYPE_ID, C.BASE_TXN_ACTION_ID AS BASE_TXN_ACTION_ID, B.TRANSACTION_QTY TRANSACTION_QTY, B.TRANSACTION_UOM_CODE, B.TXN_SOURCE_DOC_TYPE, B.TXN_SOURCE_DOC_NUMBER, B.TXN_SOURCE_REF_DOC_TYPE, B.TXN_SOURCE_REF_DOC_NUMBER, NULL AS COST_ORG_ID , NULL AS COST_BOOK_ID , NULL AS VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , NULL AS LOT_NUMBER, NULL AS SERIAL_NUMBER, D.ACCOUNTED_DATE, NULL PO_ITEM_DESCRIPTION, NULL TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, CST_I_TRADE_EVENTS B, CST_TXN_SOURCE_ACTIONS C, CST_CP_ERRORS D WHERE A.EXTERNAL_SYSTEM_REFERENCE = B.EXTERNAL_SYSTEM_REFERENCE AND A.EXTERNAL_SYSTEM_REF_ID = B.EXTERNAL_SYSTEM_REF_ID AND A.CP_ERROR_ID = D.CP_ERROR_ID AND B.TRANSACTION_TYPE = C.EXTERNAL_TXN_TYPE AND D.TABLE_NAME = 'CST_I_TRADE_EVENTS' UNION ALL SELECT /+ cardinality(a 2) leading(d a b c) use_hash(c) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, B.INVENTORY_ORG_ID , B.INVENTORY_ITEM_ID , B.TRANSACTION_DATE , C.BASE_TXN_ACTION_ID AS BASE_TXN_TYPE_ID, C.BASE_TXN_SOURCE_TYPE_ID AS BASE_TXN_SOURCE_TYPE_ID, C.BASE_TXN_ACTION_ID AS BASE_TXN_ACTION_ID, B.TRANSACTION_QTY TRANSACTION_QTY, B.TRANSACTION_UOM_CODE, B.TXN_SOURCE_DOC_TYPE, B.TXN_SOURCE_DOC_NUMBER, B.TXN_SOURCE_REF_DOC_TYPE, B.TXN_SOURCE_REF_DOC_NUMBER, NULL AS COST_ORG_ID , NULL AS COST_BOOK_ID , NULL AS VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , NULL AS LOT_NUMBER, NULL AS SERIAL_NUMBER, D.ACCOUNTED_DATE, NULL PO_ITEM_DESCRIPTION, NULL TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, CST_TRADE_EVENTS B, CST_TXN_SOURCE_ACTIONS C, CST_CP_ERRORS D WHERE A.EXTERNAL_SYSTEM_REFERENCE = B.EXTERNAL_SYSTEM_REFERENCE AND A.EXTERNAL_SYSTEM_REF_ID = B.EXTERNAL_SYSTEM_REF_ID AND A.CP_ERROR_ID = D.CP_ERROR_ID AND B.TRANSACTION_TYPE = C.EXTERNAL_TXN_TYPE AND D.TABLE_NAME = 'CST_TRADE_EVENTS' UNION ALL SELECT /+ cardinality(a 2) PUSH_PRED(C 2 3 ) NO_PUSH_PRED(C 4 5 6 7 8) PUSH_PRED(E) leading(d a g c e f) USE_HASH(f) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, E.INVENTORY_ORGANIZATION_ID , E.INVENTORY_ITEM_ID , C.TRANSACTION_DATE , F.BASE_TXN_ACTION_ID BASE_TXN_TYPE_ID , F.BASE_TXN_SOURCE_TYPE_ID , F.BASE_TXN_ACTION_ID , C.TRANSACTION_QUANTITY TRANSACTION_QTY , C.TRANSACTION_UOM_CODE , NVL(G.TXN_SOURCE_DOC_TYPE , 'WO') TXN_SOURCE_DOC_TYPE, C.EXTERNAL_SYSTEM_REF_ID TXN_SOURCE_DOC_NUMBER, NVL(G.TXN_SOURCE_REF_DOC_TYPE, 'WO') TXN_SOURCE_REF_DOC_TYPE, E.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER, G.COST_ORG_ID , G.COST_BOOK_ID , null VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , null LOT_NUMBER , null SERIAL_NUMBER , D.ACCOUNTED_DATE, NULL PO_ITEM_DESCRIPTION, NULL TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, (SELECT EXTERNAL_SYSTEM_REFERENCE , EXTERNAL_SYSTEM_REF_ID , WORK_ORDER_ID , TRANSACTION_TYPE_CODE , null INVENTORY_ORGANIZATION_ID , null INVENTORY_ITEM_ID , TRANSACTION_DATE , TRANSACTION_QUANTITY , TRANSACTION_UOM_CODE FROM CST_WO_OPERATION_TXNS_INT UNION ALL SELECT EXTERNAL_SYSTEM_REFERENCE , EXTERNAL_SYSTEM_REF_ID , WORK_ORDER_ID , TRANSACTION_TYPE_CODE , INVENTORY_ORGANIZATION_ID , INVENTORY_ITEM_ID , TRANSACTION_DATE , TRANSACTION_QUANTITY , TRANSACTION_UOM_CODE FROM CST_WO_OPERATION_TXNS) C, CST_OPERATION_TRANSACTIONS G, CST_CP_ERRORS D, (SELECT EXTERNAL_SYSTEM_REFERENCE , EXTERNAL_SYSTEM_REF_ID , WORK_ORDER_NUMBER , INVENTORY_ORGANIZATION_ID , INVENTORY_ITEM_ID FROM CST_WORK_ORDERS_INT UNION ALL SELECT EXTERNAL_SYSTEM_REFERENCE , EXTERNAL_SYSTEM_REF_ID , WORK_ORDER_NUMBER , INVENTORY_ORGANIZATION_ID , INVENTORY_ITEM_ID FROM CST_WORK_ORDERS) E, CST_TXN_SOURCE_ACTIONS F WHERE A.EXTERNAL_SYSTEM_REFERENCE = C.EXTERNAL_SYSTEM_REFERENCE AND A.WO_OP_EXT_SYSTEM_REF_ID = C.EXTERNAL_SYSTEM_REF_ID AND A.TRANSACTION_ID = G.OPERATION_TRANSACTION_ID(+) AND C.EXTERNAL_SYSTEM_REFERENCE = E.EXTERNAL_SYSTEM_REFERENCE AND TO_CHAR(C.WORK_ORDER_ID) = E.EXTERNAL_SYSTEM_REF_ID AND F.EXTERNAL_TXN_TYPE = C.TRANSACTION_TYPE_CODE AND A.CP_ERROR_ID = D.CP_ERROR_ID AND NVL(D.TABLE_NAME, 'TBL_BLANK') NOT IN ('CMR_I_RCV_TRANSACTIONS') UNION ALL SELECT /+ cardinality(a 2) PUSH_PRED(C 2 3 ) NO_PUSH_PRED(C 4 5 6 7 8) PUSH_PRED(E) leading(d a g c e f) USE_HASH(f) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, E.INVENTORY_ORGANIZATION_ID , E.INVENTORY_ITEM_ID , C.TRANSACTION_DATE , F.BASE_TXN_ACTION_ID BASE_TXN_TYPE_ID , F.BASE_TXN_SOURCE_TYPE_ID , F.BASE_TXN_ACTION_ID , C.TRANSACTION_QUANTITY TRANSACTION_QTY, C.TRANSACTION_UOM_CODE , NVL(G.TXN_SOURCE_DOC_TYPE, 'WO') TXN_SOURCE_DOC_TYPE, C.EXTERNAL_SYSTEM_REF_ID TXN_SOURCE_DOC_NUMBER, NVL(G.TXN_SOURCE_REF_DOC_TYPE, 'WO') TXN_SOURCE_REF_DOC_TYPE, E.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER, G.COST_ORG_ID , G.COST_BOOK_ID , null VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , null LOT_NUMBER , null SERIAL_NUMBER , D.ACCOUNTED_DATE , NULL PO_ITEM_DESCRIPTION, NULL TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, (SELECT EXTERNAL_SYSTEM_REFERENCE , EXTERNAL_SYSTEM_REF_ID , TRANSACTION_TYPE_CODE , WORK_ORDER_ID , null INVENTORY_ORGANIZATION_ID , null INVENTORY_ITEM_ID , TRANSACTION_DATE , TRANSACTION_QUANTITY , TRANSACTION_UOM_CODE FROM CST_WO_RESOURCE_TXNS_INT UNION ALL SELECT EXTERNAL_SYSTEM_REFERENCE , EXTERNAL_SYSTEM_REF_ID , TRANSACTION_TYPE_CODE , WORK_ORDER_ID , INVENTORY_ORGANIZATION_ID , INVENTORY_ITEM_ID , TRANSACTION_DATE , TRANSACTION_QUANTITY , TRANSACTION_UOM_CODE FROM CST_WO_RESOURCE_TXNS) C, CST_RESOURCE_TRANSACTIONS G, CST_CP_ERRORS D, (SELECT EXTERNAL_SYSTEM_REFERENCE , EXTERNAL_SYSTEM_REF_ID , WORK_ORDER_NUMBER , INVENTORY_ORGANIZATION_ID , INVENTORY_ITEM_ID FROM CST_WORK_ORDERS_INT UNION ALL SELECT EXTERNAL_SYSTEM_REFERENCE , EXTERNAL_SYSTEM_REF_ID , WORK_ORDER_NUMBER , INVENTORY_ORGANIZATION_ID , INVENTORY_ITEM_ID FROM CST_WORK_ORDERS) E, CST_TXN_SOURCE_ACTIONS F WHERE A.EXTERNAL_SYSTEM_REFERENCE = C.EXTERNAL_SYSTEM_REFERENCE AND A.WO_RES_EXT_SYSTEM_REF_ID = C.EXTERNAL_SYSTEM_REF_ID AND A.TRANSACTION_ID = G.RESOURCE_TRANSACTION_ID(+) AND C.EXTERNAL_SYSTEM_REFERENCE = E.EXTERNAL_SYSTEM_REFERENCE AND to_char(C.WORK_ORDER_ID) = E.EXTERNAL_SYSTEM_REF_ID AND F.EXTERNAL_TXN_TYPE = C.TRANSACTION_TYPE_CODE AND A.CP_ERROR_ID = D.CP_ERROR_ID AND NVL(D.TABLE_NAME, 'TBL_BLANK') NOT IN ('CMR_I_RCV_TRANSACTIONS') UNION ALL SELECT /+ cardinality(a 2) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, C.INVENTORY_ORGANIZATION_ID , C.INVENTORY_ITEM_ID , C.TRANSACTION_DATE , C.BASE_TXN_TYPE_ID , C.BASE_TXN_SOURCE_TYPE_ID , C.BASE_TXN_ACTION_ID , null TRANSACTION_QUANTITY, null TRANSACTION_UOM_CODE, C.TXN_SOURCE_DOC_TYPE, C.TXN_SOURCE_DOC_NUMBER, C.TXN_SOURCE_REF_DOC_TYPE, C.TXN_SOURCE_REF_DOC_NUMBER, C.COST_ORG_ID , C.COST_BOOK_ID , null VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , null LOT_NUMBER , null SERIAL_NUMBER , D.ACCOUNTED_DATE, NULL PO_ITEM_DESCRIPTION, NULL TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, CST_WO_UPDATE_EVENT_TXNS C, CST_CP_ERRORS D WHERE A.TRANSACTION_ID = C.WO_UPDATE_EVENT_TXN_ID AND A.CP_ERROR_ID = D.CP_ERROR_ID UNION ALL SELECT /+ cardinality(a 2) PUSH_PRED(E) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, E.INVENTORY_ORGANIZATION_ID , E.INVENTORY_ITEM_ID , case when d.ERROR_CODE <> 'CST_COMPL_WO_NOT_CLOSED' THEN (select max(i.transaction_date) from cst_wo_update_events_int i where i.external_system_reference = a.external_system_reference and i.external_system_ref_id = a.WO_EXT_SYSTEM_REF_ID and i.WO_SYSTEM_STATUS_CODE = 'CLOSED' ) else null end TRANSACTION_DATE , 31 BASE_TXN_TYPE_ID , 0 BASE_TXN_SOURCE_TYPE_ID , 31 BASE_TXN_ACTION_ID , null TRANSACTION_QTY, null TRANSACTION_UOM_CODE, 'WO' TXN_SOURCE_DOC_TYPE, E.EXTERNAL_SYSTEM_REF_ID TXN_SOURCE_DOC_NUMBER, 'WO' TXN_SOURCE_REF_DOC_TYPE, E.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER, D.COST_ORG_ID , D.COST_BOOK_ID , null VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , null LOT_NUMBER , null SERIAL_NUMBER , D.ACCOUNTED_DATE, NULL PO_ITEM_DESCRIPTION, NULL TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, /* CST_WO_UPDATE_EVENTS_INT C, bug 32768334/ CST_CP_ERRORS D, (SELECT EXTERNAL_SYSTEM_REFERENCE , EXTERNAL_SYSTEM_REF_ID , WORK_ORDER_NUMBER , INVENTORY_ORGANIZATION_ID , INVENTORY_ITEM_ID FROM CST_WORK_ORDERS_INT UNION ALL SELECT EXTERNAL_SYSTEM_REFERENCE , EXTERNAL_SYSTEM_REF_ID , WORK_ORDER_NUMBER , INVENTORY_ORGANIZATION_ID , INVENTORY_ITEM_ID FROM CST_WORK_ORDERS) E WHERE /A.EXTERNAL_SYSTEM_REFERENCE = C.EXTERNAL_SYSTEM_REFERENCE AND A.WO_EXT_SYSTEM_REF_ID = C.EXTERNAL_SYSTEM_REF_ID AND C.WO_SYSTEM_STATUS_CODE = 'CLOSED' AND bug 32768334/ A.CP_ERROR_ID = D.CP_ERROR_ID AND A.EXTERNAL_SYSTEM_REFERENCE = E.EXTERNAL_SYSTEM_REFERENCE AND A.WO_EXT_SYSTEM_REF_ID = E.EXTERNAL_SYSTEM_REF_ID AND A.TRANSACTION_ID = 0 UNION ALL SELECT /+ cardinality(a 2) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, C.INVENTORY_ORG_ID, C.INVENTORY_ITEM_ID, C.TRANSACTION_DATE, C.BASE_TXN_TYPE_ID, C.BASE_TXN_SOURCE_TYPE_ID, C.BASE_TXN_ACTION_ID, NVL2(E.CST_INV_TRANSACTION_DTL_ID, E.TRANSACTION_QTY, C.TRANSACTION_QTY) TRANSACTION_QTY, C.TRANSACTION_UOM_CODE, C.TXN_SOURCE_DOC_TYPE, C.TXN_SOURCE_DOC_NUMBER, C.TXN_SOURCE_REF_DOC_TYPE, C.TXN_SOURCE_REF_DOC_NUMBER, C.COST_ORG_ID , C.COST_BOOK_ID , C.VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , E.LOT_NUMBER, E.SERIAL_NUMBER, D.ACCOUNTED_DATE, C.PO_ITEM_DESCRIPTION, C.TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, CST_TRANSACTIONS C, CST_CP_ERRORS D, CST_INV_TRANSACTION_DTLS E WHERE A.TRANSACTION_ID = C.TRANSACTION_ID(+) AND A.CP_ERROR_ID = D.CP_ERROR_ID AND C.CST_INV_TRANSACTION_DTL_ID = E.CST_INV_TRANSACTION_DTL_ID(+) AND NVL(D.TABLE_NAME, 'TBL_BLANK') NOT IN ('CST_TRADE_EVENTS','CST_I_TRADE_EVENTS', 'CMR_I_RCV_TRANSACTIONS') AND C.cost_transaction_type = 'ADJUST' AND C.ADDITIONAL_PROCESSING_CODE in ('STANDARD', 'WIP_STANDARD_PROD', 'WIP_STANDARD_COMP', 'ITEM', 'WIP_STD_OSP_COMP', 'WIP_STD_NEG_COMP', 'VALUE', 'OPENING_BALANCE') UNION ALL SELECT /+ cardinality(a 2) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, C.INVENTORY_ORG_ID, c.INVENTORY_ITEM_ID, b.TRANSACTION_DATE, b.transaction_type_id, 0, b.transaction_type_id, c.TRANSACTION_QTY TRANSACTION_QTY, c.TRANSACTION_UOM_CODE, 'SO', c.TXN_SOURCE_REF_DOC_NUMBER transaction_number , 'SO', c.TXN_SOURCE_REF_DOC_NUMBER, C.COST_ORG_ID , C.COST_BOOK_ID , C.VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , NULL, NULL, NULL, NULL PO_ITEM_DESCRIPTION, NULL TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, cst_cogs_transactions B, CST_TRANSACTIONS C, cst_revenue_lines crl , CST_CP_ERRORS D WHERE crl.revenue_line_id(+) = b.revenue_line_id AND A.TRANSACTION_ID = B.TRANSACTION_ID AND A.CP_ERROR_ID = D.CP_ERROR_ID AND b.cst_transaction_id = c.transaction_id UNION ALL SELECT /+ cardinality(a 2) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, b.inventory_org_id, b.INVENTORY_ITEM_ID, b.txn_account_date , 10, 0, 10, NULL TRANSACTION_QTY, NULL, 'ACCT', TO_CHAR(ACCTG_ONLY_TXNS_ID ), 'PO', cpod.po_number, b.COST_ORG_ID , b.COST_BOOK_ID , NULL , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , NULL, NULL, NULL, NULL PO_ITEM_DESCRIPTION, NULL TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, cst_acctg_only_txns B, CST_CP_ERRORS D, cmr_purchase_order_dtls cpod, cst_invoice_to_exp_txns citet WHERE A.TRANSACTION_ID = B.ACCTG_ONLY_TXNS_ID AND A.CP_ERROR_ID = D.CP_ERROR_ID AND citet.invoice_to_exp_txn_id = b.invoice_to_exp_txn_id AND cpod.cmr_po_distribution_id = citet.cmr_po_distribution_id AND cpod.active_flag = 'Y' UNION ALL SELECT /+ cardinality(a 2) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, E.INVENTORY_ORGANIZATION_ID , E.INVENTORY_ITEM_ID , g.TRANSACTION_DATE , g.BASE_TXN_ACTION_ID BASE_TXN_TYPE_ID , g.BASE_TXN_SOURCE_TYPE_ID , g.BASE_TXN_ACTION_ID , g.TRANSACTION_QUANTITY TRANSACTION_QTY , g.TRANSACTION_UOM_CODE , 'WO' TXN_SOURCE_DOC_TYPE, e.work_order_number TXN_SOURCE_DOC_NUMBER, 'WO' TXN_SOURCE_REF_DOC_TYPE, E.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER, G.COST_ORG_ID , G.COST_BOOK_ID , NULL VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , NULL LOT_NUMBER , NULL SERIAL_NUMBER , D.ACCOUNTED_DATE, NULL PO_ITEM_DESCRIPTION, NULL TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, CST_RESOURCE_TRANSACTIONS G, CST_CP_ERRORS D, cst_work_orders E WHERE A.TRANSACTION_ID = G.RESOURCE_TRANSACTION_ID AND g.cst_work_order_id =e.cst_work_order_id AND A.CP_ERROR_ID = D.CP_ERROR_ID AND g.cost_transaction_type ='ADJUST' AND g.additional_processing_code='WIP_STD_RESOURCE' UNION ALL SELECT /+ cardinality(a 2) leading(a d) index(f CMR_AP_INVOICE_DTLS_PK) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, P.DELIVER_TO_INVENTORY_ORG_ID, P.INVENTORY_ITEM_ID, F.ACCOUNTING_DATE TRANSACTION_DATE, NULL BASE_TXN_TYPE_ID, NULL BASE_TXN_SOURCE_TYPE_ID, NULL BASE_TXN_ACTION_ID, F.INVOICE_AMT TRANSACTION_QTY, NULL TRANSACTION_UOM_CODE, 'INV' AS TXN_SOURCE_DOC_TYPE, F.INVOICE_NUMBER \|\| '->' \|\| F.INVOICE_LINE_NUMBER \|\| '->' \|\| F.INVOICE_DISTRIBUTION_TYPE AS TXN_SOURCE_DOC_NUMBER, 'PO' TXN_SOURCE_REF_DOC_TYPE, P.PO_NUMBER \|\| '->' \|\| P.LINE_NUMBER \|\| '-' \|\| P.SHIPMENT_NUMBER \|\| '-' \|\| P.DISTRIBUTION_NUMBER TXN_SOURCE_REF_DOC_NUMBER, NULL COST_ORG_ID , NULL COST_BOOK_ID , NULL VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , NULL LOT_NUMBER, NULL SERIAL_NUMBER, NULL ACCOUNTED_DATE, NULL PO_ITEM_DESCRIPTION, F.CURRENCY_CODE TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, CST_CP_ERRORS D, CMR_AP_INVOICE_DTLS F, CMR_PURCHASE_ORDER_DTLS P WHERE A.EXTERNAL_SYSTEM_REFERENCE = F.EXTERNAL_SYSTEM_REFERENCE AND A.EXTERNAL_SYSTEM_REF_ID = F.EXTERNAL_SYSTEM_REF_ID AND to_char(A.TRANSACTION_ID) = F.EXTERNAL_SYSTEM_REF_ID AND F.PROCESSED_BY_CA_FLAG = 'F' AND A.CP_ERROR_ID = D.CP_ERROR_ID AND D.TABLE_NAME = 'CMR_AP_INVOICE_DTLS' AND F.CMR_PO_DISTRIBUTION_ID = P.CMR_PO_DISTRIBUTION_ID AND P.ACTIVE_FLAG = 'Y' UNION ALL SELECT /+ cardinality(a 2) / A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, B.SHIP_TO_ORGANIZATION_ID, B.INVENTORY_ITEM_ID, B.TRANSACTION_DATE, NULL BASE_TXN_TYPE_ID, NULL BASE_TXN_SOURCE_TYPE_ID, NULL BASE_TXN_ACTION_ID, B.TRANSACTION_QUANTITY TRANSACTION_QTY, B.TRANSACTION_UOM_CODE TRANSACTION_UOM_CODE, 'RCV' TXN_SOURCE_DOC_TYPE, B.RECEIPT_NUMBER TXN_SOURCE_DOC_NUMBER, 'PO' TXN_SOURCE_REF_DOC_TYPE, NULL TXN_SOURCE_REF_DOC_NUMBER, NULL COST_ORG_ID , NULL COST_BOOK_ID , NULL VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID AS TABLE_ROW_ID , NULL LOT_NUMBER, NULL SERIAL_NUMBER, B.TRANSACTION_DATE ACCOUNTED_DATE, NULL PO_ITEM_DESCRIPTION, NULL TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, CMR_I_RCV_TRANSACTIONS B, CST_CP_ERRORS D WHERE A.EXTERNAL_SYSTEM_REFERENCE = B.EXTERNAL_SYSTEM_REFERENCE AND A.EXTERNAL_SYSTEM_REF_ID = B.EXTERNAL_SYSTEM_REF_ID AND A.CP_ERROR_ID = D.CP_ERROR_ID AND B.TRANSACTION_STATUS = 'E' AND B.DESTINATION_TYPE_CODE <> 'EXPENSE' AND D.TABLE_NAME = 'CMR_I_RCV_TRANSACTIONS' UNION ALL SELECT /+ cardinality(a 2) */ A.CP_ERROR_ID, A.EXTERNAL_SYSTEM_REFERENCE , A.EXTERNAL_SYSTEM_REF_ID , A.TRANSACTION_ID, A.WO_RES_EXT_SYSTEM_REF_ID, A.WO_OP_EXT_SYSTEM_REF_ID, A.WO_EXT_SYSTEM_REF_ID, C.VALIDATION_ORGANIZATION_ID INVENTORY_ORG_ID, B.INVENTORY_ITEM_ID, B.ADJUSTMENT_DATE TRANSACTION_DATE, B.BASE_TXN_TYPE_ID, B.BASE_TXN_SOURCE_TYPE_ID, B.BASE_TXN_ACTION_ID, B.TOTAL_UNIT_COST TRANSACTION_QTY, B.UOM_CODE TRANSACTION_UOM_CODE, B.ADJUSTMENT_TYPE TXN_SOURCE_DOC_TYPE, B.ADJUSTMENT_NUMBER TXN_SOURCE_DOC_NUMBER, NULL TXN_SOURCE_REF_DOC_TYPE, NULL TXN_SOURCE_REF_DOC_NUMBER, B.COST_ORG_ID , B.COST_BOOK_ID , B.VAL_UNIT_ID , D.REQUEST_ID , D.PROCESSOR_NAME , D.ERROR_CODE , D.ROW_COUNT, D.RUN_CONTROL, D.MESSAGE_TYPE, D.MESSAGE , D.TABLE_NAME , D.SUBPROCESSOR_NAME , D.CREATION_DATE, A.ROWID TABLE_ROW_ID , NULL LOT_NUMBER, NULL SERIAL_NUMBER, NULL ACCOUNTED_DATE, NULL PO_ITEM_DESCRIPTION, B.CURRENCY_CODE TRANSACTION_CURRENCY_CODE FROM CST_TRANSACTION_ERRORS A, CST_PAC_USER_ADJ_HEADERS B, CST_COST_ORG_PARAMETERS C, CST_CP_ERRORS D WHERE A.EXTERNAL_SYSTEM_REFERENCE = 'CST_PAC_ADJUSTMENTS' AND A.EXTERNAL_SYSTEM_REF_ID = to_char(B.PAC_USER_ADJUSTMENT_ID) AND A.TRANSACTION_ID = B.TRANSACTION_ID AND B.COST_ORG_ID = C.COST_ORG_ID AND A.CP_ERROR_ID = D.CP_ERROR_ID AND D.ERROR_CODE = 'CST_UNIMPORTED_INV_TXNS'

SQL_Statement

SELECT /*+ cardinality(a 2) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

B.INVENTORY_ORG_ID,

B.INVENTORY_ITEM_ID,

B.TRANSACTION_DATE,

B.BASE_TXN_TYPE_ID,

B.BASE_TXN_SOURCE_TYPE_ID,

B.BASE_TXN_ACTION_ID,

NVL2(E.CST_INV_TRANSACTION_DTL_ID, E.TRANSACTION_QTY, B.TRANSACTION_QTY) TRANSACTION_QTY,

B.TRANSACTION_UOM_CODE,

B.TXN_SOURCE_DOC_TYPE,

B.TXN_SOURCE_DOC_NUMBER,

B.TXN_SOURCE_REF_DOC_TYPE,

B.TXN_SOURCE_REF_DOC_NUMBER,

C.COST_ORG_ID ,

C.COST_BOOK_ID ,

C.VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

E.LOT_NUMBER,

E.SERIAL_NUMBER,

D.ACCOUNTED_DATE,

C.PO_ITEM_DESCRIPTION,

C.TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

CST_INV_TRANSACTIONS B,

CST_TRANSACTIONS C,

CST_CP_ERRORS D,

CST_INV_TRANSACTION_DTLS E

WHERE A.EXTERNAL_SYSTEM_REFERENCE = B.EXTERNAL_SYSTEM_REFERENCE

AND A.EXTERNAL_SYSTEM_REF_ID = B.EXTERNAL_SYSTEM_REF_ID

AND A.TRANSACTION_ID = C.TRANSACTION_ID(+)

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND C.CST_INV_TRANSACTION_DTL_ID = E.CST_INV_TRANSACTION_DTL_ID(+)

AND NVL(D.TABLE_NAME, 'TBL_BLANK') NOT IN ('CST_TRADE_EVENTS','CST_I_TRADE_EVENTS', 'CMR_I_RCV_TRANSACTIONS')

UNION ALL

SELECT /*+ cardinality(a 2) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

B.INVENTORY_ORG_ID ,

B.INVENTORY_ITEM_ID ,

B.TRANSACTION_DATE ,

B.TRANSACTION_TYPE_ID AS BASE_TXN_TYPE_ID,

B.TRANSACTION_SOURCE_TYPE_ID AS BASE_TXN_SOURCE_TYPE_ID,

B.TRANSACTION_ACTION_ID AS BASE_TXN_ACTION_ID,

NVL2(E.CST_INV_TRANSACTION_DTLS_ID, E.TRANSACTION_QTY, B.TRANSACTION_QTY) TRANSACTION_QTY,

B.TRANSACTION_UOM_CODE,

B.TXN_SOURCE_DOC_TYPE,

B.TXN_SOURCE_DOC_NUMBER,

B.TXN_SOURCE_REF_DOC_TYPE,

B.TXN_SOURCE_REF_DOC_NUMBER,

NULL AS COST_ORG_ID ,

NULL AS COST_BOOK_ID ,

NULL AS VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

E.LOT_NUMBER,

E.SERIAL_NUMBER,

D.ACCOUNTED_DATE,

B.PO_ITEM_DESCRIPTION,

B.TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

CST_I_INV_TRANSACTIONS B,

CST_CP_ERRORS D,

CST_I_INV_TRANSACTION_DTLS E

WHERE A.EXTERNAL_SYSTEM_REFERENCE = B.EXTERNAL_SYSTEM_REFERENCE

AND A.EXTERNAL_SYSTEM_REF_ID = B.EXTERNAL_SYSTEM_REF_ID

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND A.EXTERNAL_SYSTEM_REFERENCE = E.EXTERNAL_SYSTEM_REFERENCE(+)

AND A.EXTERNAL_SYSTEM_REF_ID = E.EXTERNAL_SYSTEM_REF_ID(+)

AND NVL(D.TABLE_NAME, 'TBL_BLANK') NOT IN ('CST_TRADE_EVENTS','CST_I_TRADE_EVENTS', 'CMR_I_RCV_TRANSACTIONS')

UNION ALL

SELECT /*+ cardinality(a 2) leading(d a b c) use_hash(c) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

B.INVENTORY_ORG_ID ,

B.INVENTORY_ITEM_ID ,

B.TRANSACTION_DATE ,

C.BASE_TXN_ACTION_ID AS BASE_TXN_TYPE_ID,

C.BASE_TXN_SOURCE_TYPE_ID AS BASE_TXN_SOURCE_TYPE_ID,

C.BASE_TXN_ACTION_ID AS BASE_TXN_ACTION_ID,

B.TRANSACTION_QTY TRANSACTION_QTY,

B.TRANSACTION_UOM_CODE,

B.TXN_SOURCE_DOC_TYPE,

B.TXN_SOURCE_DOC_NUMBER,

B.TXN_SOURCE_REF_DOC_TYPE,

B.TXN_SOURCE_REF_DOC_NUMBER,

NULL AS COST_ORG_ID ,

NULL AS COST_BOOK_ID ,

NULL AS VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

NULL AS LOT_NUMBER,

NULL AS SERIAL_NUMBER,

D.ACCOUNTED_DATE,

NULL PO_ITEM_DESCRIPTION,

NULL TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

CST_I_TRADE_EVENTS B,

CST_TXN_SOURCE_ACTIONS C,

CST_CP_ERRORS D

WHERE A.EXTERNAL_SYSTEM_REFERENCE = B.EXTERNAL_SYSTEM_REFERENCE

AND A.EXTERNAL_SYSTEM_REF_ID = B.EXTERNAL_SYSTEM_REF_ID

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND B.TRANSACTION_TYPE = C.EXTERNAL_TXN_TYPE

AND D.TABLE_NAME = 'CST_I_TRADE_EVENTS'

UNION ALL

SELECT /*+ cardinality(a 2) leading(d a b c) use_hash(c) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

B.INVENTORY_ORG_ID ,

B.INVENTORY_ITEM_ID ,

B.TRANSACTION_DATE ,

C.BASE_TXN_ACTION_ID AS BASE_TXN_TYPE_ID,

C.BASE_TXN_SOURCE_TYPE_ID AS BASE_TXN_SOURCE_TYPE_ID,

C.BASE_TXN_ACTION_ID AS BASE_TXN_ACTION_ID,

B.TRANSACTION_QTY TRANSACTION_QTY,

B.TRANSACTION_UOM_CODE,

B.TXN_SOURCE_DOC_TYPE,

B.TXN_SOURCE_DOC_NUMBER,

B.TXN_SOURCE_REF_DOC_TYPE,

B.TXN_SOURCE_REF_DOC_NUMBER,

NULL AS COST_ORG_ID ,

NULL AS COST_BOOK_ID ,

NULL AS VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

NULL AS LOT_NUMBER,

NULL AS SERIAL_NUMBER,

D.ACCOUNTED_DATE,

NULL PO_ITEM_DESCRIPTION,

NULL TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

CST_TRADE_EVENTS B,

CST_TXN_SOURCE_ACTIONS C,

CST_CP_ERRORS D

WHERE A.EXTERNAL_SYSTEM_REFERENCE = B.EXTERNAL_SYSTEM_REFERENCE

AND A.EXTERNAL_SYSTEM_REF_ID = B.EXTERNAL_SYSTEM_REF_ID

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND B.TRANSACTION_TYPE = C.EXTERNAL_TXN_TYPE

AND D.TABLE_NAME = 'CST_TRADE_EVENTS'

UNION ALL

SELECT /*+ cardinality(a 2) PUSH_PRED(C 2 3 ) NO_PUSH_PRED(C 4 5 6 7 8) PUSH_PRED(E) leading(d a g c e f) USE_HASH(f) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

E.INVENTORY_ORGANIZATION_ID ,

E.INVENTORY_ITEM_ID ,

C.TRANSACTION_DATE ,

F.BASE_TXN_ACTION_ID BASE_TXN_TYPE_ID ,

F.BASE_TXN_SOURCE_TYPE_ID ,

F.BASE_TXN_ACTION_ID ,

C.TRANSACTION_QUANTITY TRANSACTION_QTY ,

C.TRANSACTION_UOM_CODE ,

NVL(G.TXN_SOURCE_DOC_TYPE , 'WO') TXN_SOURCE_DOC_TYPE,

C.EXTERNAL_SYSTEM_REF_ID TXN_SOURCE_DOC_NUMBER,

NVL(G.TXN_SOURCE_REF_DOC_TYPE, 'WO') TXN_SOURCE_REF_DOC_TYPE,

E.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER,

G.COST_ORG_ID ,

G.COST_BOOK_ID ,

null VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

null LOT_NUMBER ,

null SERIAL_NUMBER ,

D.ACCOUNTED_DATE,

NULL PO_ITEM_DESCRIPTION,

NULL TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

(SELECT EXTERNAL_SYSTEM_REFERENCE ,

EXTERNAL_SYSTEM_REF_ID ,

WORK_ORDER_ID ,

TRANSACTION_TYPE_CODE ,

null INVENTORY_ORGANIZATION_ID ,

null INVENTORY_ITEM_ID ,

TRANSACTION_DATE ,

TRANSACTION_QUANTITY ,

TRANSACTION_UOM_CODE

FROM CST_WO_OPERATION_TXNS_INT

UNION ALL

SELECT EXTERNAL_SYSTEM_REFERENCE ,

EXTERNAL_SYSTEM_REF_ID ,

WORK_ORDER_ID ,

TRANSACTION_TYPE_CODE ,

INVENTORY_ORGANIZATION_ID ,

INVENTORY_ITEM_ID ,

TRANSACTION_DATE ,

TRANSACTION_QUANTITY ,

TRANSACTION_UOM_CODE

FROM CST_WO_OPERATION_TXNS) C,

CST_OPERATION_TRANSACTIONS G,

CST_CP_ERRORS D,

(SELECT EXTERNAL_SYSTEM_REFERENCE ,

EXTERNAL_SYSTEM_REF_ID ,

WORK_ORDER_NUMBER ,

INVENTORY_ORGANIZATION_ID ,

INVENTORY_ITEM_ID

FROM CST_WORK_ORDERS_INT

UNION ALL

SELECT EXTERNAL_SYSTEM_REFERENCE ,

EXTERNAL_SYSTEM_REF_ID ,

WORK_ORDER_NUMBER ,

INVENTORY_ORGANIZATION_ID ,

INVENTORY_ITEM_ID

FROM CST_WORK_ORDERS) E,

CST_TXN_SOURCE_ACTIONS F

WHERE

A.EXTERNAL_SYSTEM_REFERENCE = C.EXTERNAL_SYSTEM_REFERENCE

AND A.WO_OP_EXT_SYSTEM_REF_ID = C.EXTERNAL_SYSTEM_REF_ID

AND A.TRANSACTION_ID = G.OPERATION_TRANSACTION_ID(+)

AND C.EXTERNAL_SYSTEM_REFERENCE = E.EXTERNAL_SYSTEM_REFERENCE

AND TO_CHAR(C.WORK_ORDER_ID) = E.EXTERNAL_SYSTEM_REF_ID

AND F.EXTERNAL_TXN_TYPE = C.TRANSACTION_TYPE_CODE

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND NVL(D.TABLE_NAME, 'TBL_BLANK') NOT IN ('CMR_I_RCV_TRANSACTIONS')

UNION ALL

SELECT /*+ cardinality(a 2) PUSH_PRED(C 2 3 ) NO_PUSH_PRED(C 4 5 6 7 8) PUSH_PRED(E) leading(d a g c e f) USE_HASH(f) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

E.INVENTORY_ORGANIZATION_ID ,

E.INVENTORY_ITEM_ID ,

C.TRANSACTION_DATE ,

F.BASE_TXN_ACTION_ID BASE_TXN_TYPE_ID ,

F.BASE_TXN_SOURCE_TYPE_ID ,

F.BASE_TXN_ACTION_ID ,

C.TRANSACTION_QUANTITY TRANSACTION_QTY,

C.TRANSACTION_UOM_CODE ,

NVL(G.TXN_SOURCE_DOC_TYPE, 'WO') TXN_SOURCE_DOC_TYPE,

C.EXTERNAL_SYSTEM_REF_ID TXN_SOURCE_DOC_NUMBER,

NVL(G.TXN_SOURCE_REF_DOC_TYPE, 'WO') TXN_SOURCE_REF_DOC_TYPE,

E.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER,

G.COST_ORG_ID ,

G.COST_BOOK_ID ,

null VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

null LOT_NUMBER ,

null SERIAL_NUMBER ,

D.ACCOUNTED_DATE ,

NULL PO_ITEM_DESCRIPTION,

NULL TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

(SELECT EXTERNAL_SYSTEM_REFERENCE ,

EXTERNAL_SYSTEM_REF_ID ,

TRANSACTION_TYPE_CODE ,

WORK_ORDER_ID ,

null INVENTORY_ORGANIZATION_ID ,

null INVENTORY_ITEM_ID ,

TRANSACTION_DATE ,

TRANSACTION_QUANTITY ,

TRANSACTION_UOM_CODE

FROM CST_WO_RESOURCE_TXNS_INT

UNION ALL

SELECT EXTERNAL_SYSTEM_REFERENCE ,

EXTERNAL_SYSTEM_REF_ID ,

TRANSACTION_TYPE_CODE ,

WORK_ORDER_ID ,

INVENTORY_ORGANIZATION_ID ,

INVENTORY_ITEM_ID ,

TRANSACTION_DATE ,

TRANSACTION_QUANTITY ,

TRANSACTION_UOM_CODE

FROM CST_WO_RESOURCE_TXNS) C,

CST_RESOURCE_TRANSACTIONS G,

CST_CP_ERRORS D,

(SELECT EXTERNAL_SYSTEM_REFERENCE ,

EXTERNAL_SYSTEM_REF_ID ,

WORK_ORDER_NUMBER ,

INVENTORY_ORGANIZATION_ID ,

INVENTORY_ITEM_ID

FROM CST_WORK_ORDERS_INT

UNION ALL

SELECT EXTERNAL_SYSTEM_REFERENCE ,

EXTERNAL_SYSTEM_REF_ID ,

WORK_ORDER_NUMBER ,

INVENTORY_ORGANIZATION_ID ,

INVENTORY_ITEM_ID

FROM CST_WORK_ORDERS) E,

CST_TXN_SOURCE_ACTIONS F

WHERE A.EXTERNAL_SYSTEM_REFERENCE = C.EXTERNAL_SYSTEM_REFERENCE

AND A.WO_RES_EXT_SYSTEM_REF_ID = C.EXTERNAL_SYSTEM_REF_ID

AND A.TRANSACTION_ID = G.RESOURCE_TRANSACTION_ID(+)

AND C.EXTERNAL_SYSTEM_REFERENCE = E.EXTERNAL_SYSTEM_REFERENCE

AND to_char(C.WORK_ORDER_ID) = E.EXTERNAL_SYSTEM_REF_ID

AND F.EXTERNAL_TXN_TYPE = C.TRANSACTION_TYPE_CODE

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND NVL(D.TABLE_NAME, 'TBL_BLANK') NOT IN ('CMR_I_RCV_TRANSACTIONS')

UNION ALL

SELECT /*+ cardinality(a 2) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

C.INVENTORY_ORGANIZATION_ID ,

C.INVENTORY_ITEM_ID ,

C.TRANSACTION_DATE ,

C.BASE_TXN_TYPE_ID ,

C.BASE_TXN_SOURCE_TYPE_ID ,

C.BASE_TXN_ACTION_ID ,

null TRANSACTION_QUANTITY,

null TRANSACTION_UOM_CODE,

C.TXN_SOURCE_DOC_TYPE,

C.TXN_SOURCE_DOC_NUMBER,

C.TXN_SOURCE_REF_DOC_TYPE,

C.TXN_SOURCE_REF_DOC_NUMBER,

C.COST_ORG_ID ,

C.COST_BOOK_ID ,

null VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

null LOT_NUMBER ,

null SERIAL_NUMBER ,

D.ACCOUNTED_DATE,

NULL PO_ITEM_DESCRIPTION,

NULL TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

CST_WO_UPDATE_EVENT_TXNS C,

CST_CP_ERRORS D

WHERE A.TRANSACTION_ID = C.WO_UPDATE_EVENT_TXN_ID

AND A.CP_ERROR_ID = D.CP_ERROR_ID

UNION ALL

SELECT /*+ cardinality(a 2) PUSH_PRED(E) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

E.INVENTORY_ORGANIZATION_ID ,

E.INVENTORY_ITEM_ID ,

case when d.ERROR_CODE <> 'CST_COMPL_WO_NOT_CLOSED' THEN

(select max(i.transaction_date)

from cst_wo_update_events_int i

where i.external_system_reference = a.external_system_reference

and i.external_system_ref_id = a.WO_EXT_SYSTEM_REF_ID

and i.WO_SYSTEM_STATUS_CODE = 'CLOSED' )

else null

end TRANSACTION_DATE ,

31 BASE_TXN_TYPE_ID ,

0 BASE_TXN_SOURCE_TYPE_ID ,

31 BASE_TXN_ACTION_ID ,

null TRANSACTION_QTY,

null TRANSACTION_UOM_CODE,

'WO' TXN_SOURCE_DOC_TYPE,

E.EXTERNAL_SYSTEM_REF_ID TXN_SOURCE_DOC_NUMBER,

'WO' TXN_SOURCE_REF_DOC_TYPE,

E.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER,

D.COST_ORG_ID ,

D.COST_BOOK_ID ,

null VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

null LOT_NUMBER ,

null SERIAL_NUMBER ,

D.ACCOUNTED_DATE,

NULL PO_ITEM_DESCRIPTION,

NULL TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

/* CST_WO_UPDATE_EVENTS_INT C, bug 32768334*/

CST_CP_ERRORS D,

(SELECT EXTERNAL_SYSTEM_REFERENCE ,

EXTERNAL_SYSTEM_REF_ID ,

WORK_ORDER_NUMBER ,

INVENTORY_ORGANIZATION_ID ,

INVENTORY_ITEM_ID

FROM CST_WORK_ORDERS_INT

UNION ALL

SELECT EXTERNAL_SYSTEM_REFERENCE ,

EXTERNAL_SYSTEM_REF_ID ,

WORK_ORDER_NUMBER ,

INVENTORY_ORGANIZATION_ID ,

INVENTORY_ITEM_ID

FROM CST_WORK_ORDERS) E

WHERE /*A.EXTERNAL_SYSTEM_REFERENCE = C.EXTERNAL_SYSTEM_REFERENCE

AND A.WO_EXT_SYSTEM_REF_ID = C.EXTERNAL_SYSTEM_REF_ID

AND C.WO_SYSTEM_STATUS_CODE = 'CLOSED'

AND bug 32768334*/

A.CP_ERROR_ID = D.CP_ERROR_ID

AND A.EXTERNAL_SYSTEM_REFERENCE = E.EXTERNAL_SYSTEM_REFERENCE

AND A.WO_EXT_SYSTEM_REF_ID = E.EXTERNAL_SYSTEM_REF_ID

AND A.TRANSACTION_ID = 0

UNION ALL

SELECT /*+ cardinality(a 2) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

C.INVENTORY_ORG_ID,

C.INVENTORY_ITEM_ID,

C.TRANSACTION_DATE,

C.BASE_TXN_TYPE_ID,

C.BASE_TXN_SOURCE_TYPE_ID,

C.BASE_TXN_ACTION_ID,

NVL2(E.CST_INV_TRANSACTION_DTL_ID, E.TRANSACTION_QTY, C.TRANSACTION_QTY) TRANSACTION_QTY,

C.TRANSACTION_UOM_CODE,

C.TXN_SOURCE_DOC_TYPE,

C.TXN_SOURCE_DOC_NUMBER,

C.TXN_SOURCE_REF_DOC_TYPE,

C.TXN_SOURCE_REF_DOC_NUMBER,

C.COST_ORG_ID ,

C.COST_BOOK_ID ,

C.VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

E.LOT_NUMBER,

E.SERIAL_NUMBER,

D.ACCOUNTED_DATE,

C.PO_ITEM_DESCRIPTION,

C.TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

CST_TRANSACTIONS C,

CST_CP_ERRORS D,

CST_INV_TRANSACTION_DTLS E

WHERE

A.TRANSACTION_ID = C.TRANSACTION_ID(+)

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND C.CST_INV_TRANSACTION_DTL_ID = E.CST_INV_TRANSACTION_DTL_ID(+)

AND NVL(D.TABLE_NAME, 'TBL_BLANK') NOT IN ('CST_TRADE_EVENTS','CST_I_TRADE_EVENTS', 'CMR_I_RCV_TRANSACTIONS')

AND C.cost_transaction_type = 'ADJUST'

AND C.ADDITIONAL_PROCESSING_CODE in ('STANDARD', 'WIP_STANDARD_PROD', 'WIP_STANDARD_COMP', 'ITEM', 'WIP_STD_OSP_COMP', 'WIP_STD_NEG_COMP', 'VALUE', 'OPENING_BALANCE')

UNION ALL

SELECT /*+ cardinality(a 2) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

C.INVENTORY_ORG_ID,

c.INVENTORY_ITEM_ID,

b.TRANSACTION_DATE,

b.transaction_type_id,

0,

b.transaction_type_id,

c.TRANSACTION_QTY TRANSACTION_QTY,

c.TRANSACTION_UOM_CODE,

'SO',

c.TXN_SOURCE_REF_DOC_NUMBER transaction_number ,

'SO',

c.TXN_SOURCE_REF_DOC_NUMBER,

C.COST_ORG_ID ,

C.COST_BOOK_ID ,

C.VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

NULL,

NULL PO_ITEM_DESCRIPTION,

NULL TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

cst_cogs_transactions B,

CST_TRANSACTIONS C,

cst_revenue_lines crl ,

CST_CP_ERRORS D

WHERE crl.revenue_line_id(+) = b.revenue_line_id

AND A.TRANSACTION_ID = B.TRANSACTION_ID

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND b.cst_transaction_id = c.transaction_id

UNION ALL

SELECT /*+ cardinality(a 2) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

b.inventory_org_id,

b.INVENTORY_ITEM_ID,

b.txn_account_date ,

10,

0,

10,

NULL TRANSACTION_QTY,

NULL,

'ACCT',

TO_CHAR(ACCTG_ONLY_TXNS_ID ),

'PO',

cpod.po_number,

b.COST_ORG_ID ,

b.COST_BOOK_ID ,

NULL ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

NULL,

NULL PO_ITEM_DESCRIPTION,

NULL TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

cst_acctg_only_txns B,

CST_CP_ERRORS D,

cmr_purchase_order_dtls cpod,

cst_invoice_to_exp_txns citet

WHERE A.TRANSACTION_ID = B.ACCTG_ONLY_TXNS_ID

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND citet.invoice_to_exp_txn_id = b.invoice_to_exp_txn_id

AND cpod.cmr_po_distribution_id = citet.cmr_po_distribution_id

AND cpod.active_flag = 'Y'

UNION ALL

SELECT /*+ cardinality(a 2) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

E.INVENTORY_ORGANIZATION_ID ,

E.INVENTORY_ITEM_ID ,

g.TRANSACTION_DATE ,

g.BASE_TXN_ACTION_ID BASE_TXN_TYPE_ID ,

g.BASE_TXN_SOURCE_TYPE_ID ,

g.BASE_TXN_ACTION_ID ,

g.TRANSACTION_QUANTITY TRANSACTION_QTY ,

g.TRANSACTION_UOM_CODE ,

'WO' TXN_SOURCE_DOC_TYPE,

e.work_order_number TXN_SOURCE_DOC_NUMBER,

'WO' TXN_SOURCE_REF_DOC_TYPE,

E.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER,

G.COST_ORG_ID ,

G.COST_BOOK_ID ,

NULL VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

NULL LOT_NUMBER ,

NULL SERIAL_NUMBER ,

D.ACCOUNTED_DATE,

NULL PO_ITEM_DESCRIPTION,

NULL TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

CST_RESOURCE_TRANSACTIONS G,

CST_CP_ERRORS D,

cst_work_orders E

WHERE A.TRANSACTION_ID = G.RESOURCE_TRANSACTION_ID

AND g.cst_work_order_id =e.cst_work_order_id

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND g.cost_transaction_type ='ADJUST'

AND g.additional_processing_code='WIP_STD_RESOURCE'

UNION ALL

SELECT /*+ cardinality(a 2) leading(a d) index(f CMR_AP_INVOICE_DTLS_PK) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

P.DELIVER_TO_INVENTORY_ORG_ID,

P.INVENTORY_ITEM_ID,

F.ACCOUNTING_DATE TRANSACTION_DATE,

NULL BASE_TXN_TYPE_ID,

NULL BASE_TXN_SOURCE_TYPE_ID,

NULL BASE_TXN_ACTION_ID,

F.INVOICE_AMT TRANSACTION_QTY,

NULL TRANSACTION_UOM_CODE,

'INV' AS TXN_SOURCE_DOC_TYPE,

F.INVOICE_NUMBER || '->' || F.INVOICE_LINE_NUMBER || '->' || F.INVOICE_DISTRIBUTION_TYPE AS TXN_SOURCE_DOC_NUMBER,

'PO' TXN_SOURCE_REF_DOC_TYPE,

P.PO_NUMBER || '->' || P.LINE_NUMBER || '-' || P.SHIPMENT_NUMBER || '-' || P.DISTRIBUTION_NUMBER TXN_SOURCE_REF_DOC_NUMBER,

NULL COST_ORG_ID ,

NULL COST_BOOK_ID ,

NULL VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

NULL LOT_NUMBER,

NULL SERIAL_NUMBER,

NULL ACCOUNTED_DATE,

NULL PO_ITEM_DESCRIPTION,

F.CURRENCY_CODE TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

CST_CP_ERRORS D,

CMR_AP_INVOICE_DTLS F,

CMR_PURCHASE_ORDER_DTLS P

WHERE A.EXTERNAL_SYSTEM_REFERENCE = F.EXTERNAL_SYSTEM_REFERENCE

AND A.EXTERNAL_SYSTEM_REF_ID = F.EXTERNAL_SYSTEM_REF_ID

AND to_char(A.TRANSACTION_ID) = F.EXTERNAL_SYSTEM_REF_ID

AND F.PROCESSED_BY_CA_FLAG = 'F'

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND D.TABLE_NAME = 'CMR_AP_INVOICE_DTLS'

AND F.CMR_PO_DISTRIBUTION_ID = P.CMR_PO_DISTRIBUTION_ID

AND P.ACTIVE_FLAG = 'Y'

UNION ALL

SELECT /*+ cardinality(a 2) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

B.SHIP_TO_ORGANIZATION_ID,

B.INVENTORY_ITEM_ID,

B.TRANSACTION_DATE,

NULL BASE_TXN_TYPE_ID,

NULL BASE_TXN_SOURCE_TYPE_ID,

NULL BASE_TXN_ACTION_ID,

B.TRANSACTION_QUANTITY TRANSACTION_QTY,

B.TRANSACTION_UOM_CODE TRANSACTION_UOM_CODE,

'RCV' TXN_SOURCE_DOC_TYPE,

B.RECEIPT_NUMBER TXN_SOURCE_DOC_NUMBER,

'PO' TXN_SOURCE_REF_DOC_TYPE,

NULL TXN_SOURCE_REF_DOC_NUMBER,

NULL COST_ORG_ID ,

NULL COST_BOOK_ID ,

NULL VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID AS TABLE_ROW_ID ,

NULL LOT_NUMBER,

NULL SERIAL_NUMBER,

B.TRANSACTION_DATE ACCOUNTED_DATE,

NULL PO_ITEM_DESCRIPTION,

NULL TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

CMR_I_RCV_TRANSACTIONS B,

CST_CP_ERRORS D

WHERE A.EXTERNAL_SYSTEM_REFERENCE = B.EXTERNAL_SYSTEM_REFERENCE

AND A.EXTERNAL_SYSTEM_REF_ID = B.EXTERNAL_SYSTEM_REF_ID

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND B.TRANSACTION_STATUS = 'E'

AND B.DESTINATION_TYPE_CODE <> 'EXPENSE'

AND D.TABLE_NAME = 'CMR_I_RCV_TRANSACTIONS'

UNION ALL

SELECT /*+ cardinality(a 2) */ A.CP_ERROR_ID,

A.EXTERNAL_SYSTEM_REFERENCE ,

A.EXTERNAL_SYSTEM_REF_ID ,

A.TRANSACTION_ID,

A.WO_RES_EXT_SYSTEM_REF_ID,

A.WO_OP_EXT_SYSTEM_REF_ID,

A.WO_EXT_SYSTEM_REF_ID,

C.VALIDATION_ORGANIZATION_ID INVENTORY_ORG_ID,

B.INVENTORY_ITEM_ID,

B.ADJUSTMENT_DATE TRANSACTION_DATE,

B.BASE_TXN_TYPE_ID,

B.BASE_TXN_SOURCE_TYPE_ID,

B.BASE_TXN_ACTION_ID,

B.TOTAL_UNIT_COST TRANSACTION_QTY,

B.UOM_CODE TRANSACTION_UOM_CODE,

B.ADJUSTMENT_TYPE TXN_SOURCE_DOC_TYPE,

B.ADJUSTMENT_NUMBER TXN_SOURCE_DOC_NUMBER,

NULL TXN_SOURCE_REF_DOC_TYPE,

NULL TXN_SOURCE_REF_DOC_NUMBER,

B.COST_ORG_ID ,

B.COST_BOOK_ID ,

B.VAL_UNIT_ID ,

D.REQUEST_ID ,

D.PROCESSOR_NAME ,

D.ERROR_CODE ,

D.ROW_COUNT,

D.RUN_CONTROL,

D.MESSAGE_TYPE,

D.MESSAGE ,

D.TABLE_NAME ,

D.SUBPROCESSOR_NAME ,

D.CREATION_DATE,

A.ROWID TABLE_ROW_ID ,

NULL LOT_NUMBER,

NULL SERIAL_NUMBER,

NULL ACCOUNTED_DATE,

NULL PO_ITEM_DESCRIPTION,

B.CURRENCY_CODE TRANSACTION_CURRENCY_CODE

FROM CST_TRANSACTION_ERRORS A,

CST_PAC_USER_ADJ_HEADERS B,

CST_COST_ORG_PARAMETERS C,

CST_CP_ERRORS D

WHERE A.EXTERNAL_SYSTEM_REFERENCE = 'CST_PAC_ADJUSTMENTS'

AND A.EXTERNAL_SYSTEM_REF_ID = to_char(B.PAC_USER_ADJUSTMENT_ID)

AND A.TRANSACTION_ID = B.TRANSACTION_ID

AND B.COST_ORG_ID = C.COST_ORG_ID

AND A.CP_ERROR_ID = D.CP_ERROR_ID

AND D.ERROR_CODE = 'CST_UNIMPORTED_INV_TXNS'