CST_ALL_INTF_TRANSACTIONS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
EXTERNAL_SYSTEM_REFERENCE EXTERNAL_SYSTEM_REF_ID SOURCE_TABLE INVENTORY_ITEM_ID RESOURCE_ID INVENTORY_ORG_ID ITEM_ORGANIZATION_ID BASE_TXN_TYPE_ID BASE_TXN_SOURCE_TYPE_ID BASE_TXN_ACTION_ID TRANSACTION_DATE QUANTITY UOM_CODE TXN_SOURCE_DOC_TYPE TXN_SOURCE_DOC_NUMBER TXN_SOURCE_REF_DOC_TYPE TXN_SOURCE_REF_DOC_NUMBER CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN

Name

EXTERNAL_SYSTEM_REFERENCE

EXTERNAL_SYSTEM_REF_ID

SOURCE_TABLE

INVENTORY_ITEM_ID

RESOURCE_ID

INVENTORY_ORG_ID

ITEM_ORGANIZATION_ID

BASE_TXN_TYPE_ID

BASE_TXN_SOURCE_TYPE_ID

BASE_TXN_ACTION_ID

TRANSACTION_DATE

QUANTITY

UOM_CODE

TXN_SOURCE_DOC_TYPE

TXN_SOURCE_DOC_NUMBER

TXN_SOURCE_REF_DOC_TYPE

TXN_SOURCE_REF_DOC_NUMBER

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

Query

SQL_Statement
SELECT EXTERNAL_SYSTEM_REFERENCE, EXTERNAL_SYSTEM_REF_ID, 'CST_I_INV_TRANSACTIONS' SOURCE_TABLE, INVENTORY_ITEM_ID, NULL RESOURCE_ID, INVENTORY_ORG_ID, INVENTORY_ORG_ID ITEM_ORGANIZATION_ID, TRANSACTION_TYPE_ID BASE_TXN_TYPE_ID, TRANSACTION_SOURCE_TYPE_ID BASE_TXN_SOURCE_TYPE_ID, TRANSACTION_ACTION_ID BASE_TXN_ACTION_ID, TRANSACTION_DATE, TRANSACTION_QTY QUANTITY, TRANSACTION_UOM_CODE UOM_CODE, TXN_SOURCE_DOC_TYPE, TXN_SOURCE_DOC_NUMBER, TXN_SOURCE_REF_DOC_TYPE, TXN_SOURCE_REF_DOC_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN FROM CST_I_INV_TRANSACTIONS UNION ALL SELECT EXTERNAL_SYSTEM_REFERENCE, EXTERNAL_SYSTEM_REF_ID, 'CST_INV_TRANSACTIONS' SOURCE_TABLE, INVENTORY_ITEM_ID, NULL RESOURCE_ID, INVENTORY_ORG_ID, INVENTORY_ORG_ID ITEM_ORGANIZATION_ID, BASE_TXN_TYPE_ID BASE_TXN_TYPE_ID, BASE_TXN_SOURCE_TYPE_ID BASE_TXN_SOURCE_TYPE_ID, BASE_TXN_ACTION_ID BASE_TXN_ACTION_ID, TRANSACTION_DATE, TRANSACTION_QTY QUANTITY, TRANSACTION_UOM_CODE UOM_CODE, TXN_SOURCE_DOC_TYPE, TXN_SOURCE_DOC_NUMBER, TXN_SOURCE_REF_DOC_TYPE, TXN_SOURCE_REF_DOC_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN FROM CST_INV_TRANSACTIONS CIT WHERE EXISTS ( SELECT 1 FROM CST_INV_TRANSACTION_STATUS CITS WHERE CITS.CST_INV_TRANSACTION_ID = CIT.CST_INV_TRANSACTION_ID AND CITS.POSTED_FLAG IS NOT NULL) UNION ALL SELECT CWRT.EXTERNAL_SYSTEM_REFERENCE, CWRT.EXTERNAL_SYSTEM_REF_ID, 'CST_WO_RESOURCE_TXNS_INT' SOURCE_TABLE, NVL(CWO.INVENTORY_ITEM_ID, CWOI.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID, CWRT.RESOURCE_ID, NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) INVENTORY_ORG_ID, NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) ITEM_ORGANIZATION_ID, 21 BASE_TXN_TYPE_ID, 0 BASE_TXN_SOURCE_TYPE_ID, 21 BASE_TXN_ACTION_ID, CWRT.TRANSACTION_DATE, CWRT.TRANSACTION_QUANTITY QUANTITY, CWRT.TRANSACTION_UOM_CODE UOM_CODE, 'WO' TXN_SOURCE_DOC_TYPE, NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_DOC_NUMBER, 'WO' TXN_SOURCE_REF_DOC_TYPE, NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_REF_DOC_NUMBER, CWRT.CREATED_BY, CWRT.CREATION_DATE, CWRT.LAST_UPDATED_BY, CWRT.LAST_UPDATE_DATE, CWRT.LAST_UPDATE_LOGIN FROM FUSION.CST_WO_RESOURCE_TXNS_INT CWRT, FUSION.CST_WORK_ORDERS CWO, FUSION.CST_WORK_ORDERS_INT CWOI WHERE CWO.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWRT.WORK_ORDER_ID) AND CWOI.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWRT.WORK_ORDER_ID) UNION ALL SELECT CWRT.EXTERNAL_SYSTEM_REFERENCE, CWRT.EXTERNAL_SYSTEM_REF_ID, 'CST_WO_RESOURCE_TXNS' SOURCE_TABLE, NVL(CWO.INVENTORY_ITEM_ID, CWOI.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID, CWRT.RESOURCE_ID, NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) INVENTORY_ORG_ID, NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) ITEM_ORGANIZATION_ID, 21 BASE_TXN_TYPE_ID, 0 BASE_TXN_SOURCE_TYPE_ID, 21 BASE_TXN_ACTION_ID, CWRT.TRANSACTION_DATE, CWRT.TRANSACTION_QUANTITY QUANTITY, CWRT.TRANSACTION_UOM_CODE UOM_CODE, 'WO' TXN_SOURCE_DOC_TYPE, NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_DOC_NUMBER, 'WO' TXN_SOURCE_REF_DOC_TYPE, NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_REF_DOC_NUMBER, CWRT.CREATED_BY, CWRT.CREATION_DATE, CWRT.LAST_UPDATED_BY, CWRT.LAST_UPDATE_DATE, CWRT.LAST_UPDATE_LOGIN FROM FUSION.CST_WO_RESOURCE_TXNS CWRT, FUSION.CST_WORK_ORDERS CWO, FUSION.CST_WORK_ORDERS_INT CWOI WHERE CWO.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWRT.WORK_ORDER_ID) AND CWOI.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWRT.WORK_ORDER_ID) AND CWRT.PROCESSED_BY_CA_FLAG IS NOT NULL UNION ALL SELECT CWOT.EXTERNAL_SYSTEM_REFERENCE, CWOT.EXTERNAL_SYSTEM_REF_ID, 'CST_WO_OPERATION_TXNS_INT' SOURCE_TABLE, NVL(CWO.INVENTORY_ITEM_ID, CWOI.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID, NULL RESOURCE_ID, NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) INVENTORY_ORG_ID, NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) ITEM_ORGANIZATION_ID, 231 BASE_TXN_TYPE_ID, 0 BASE_TXN_SOURCE_TYPE_ID, 23 BASE_TXN_ACTION_ID, CWOT.TRANSACTION_DATE, CWOT.TRANSACTION_QUANTITY QUANTITY, CWOT.TRANSACTION_UOM_CODE UOM_CODE, 'WO' TXN_SOURCE_DOC_TYPE, NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_DOC_NUMBER, 'WO' TXN_SOURCE_REF_DOC_TYPE, NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_REF_DOC_NUMBER, CWOT.CREATED_BY, CWOT.CREATION_DATE, CWOT.LAST_UPDATED_BY, CWOT.LAST_UPDATE_DATE, CWOT.LAST_UPDATE_LOGIN FROM FUSION.CST_WO_OPERATION_TXNS_INT CWOT, FUSION.CST_WORK_ORDERS CWO, FUSION.CST_WORK_ORDERS_INT CWOI WHERE CWO.EXTERNAL_SYSTEM_REF_ID (+) = TO_CHAR(CWOT.WORK_ORDER_ID) AND CWOI.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWOT.WORK_ORDER_ID) UNION ALL SELECT CWOT.EXTERNAL_SYSTEM_REFERENCE, CWOT.EXTERNAL_SYSTEM_REF_ID, 'CST_WO_OPERATION_TXNS' SOURCE_TABLE, NVL(CWO.INVENTORY_ITEM_ID, CWOI.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID, NULL RESOURCE_ID, NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) INVENTORY_ORG_ID, NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) ITEM_ORGANIZATION_ID, 231 BASE_TXN_TYPE_ID, 0 BASE_TXN_SOURCE_TYPE_ID, 23 BASE_TXN_ACTION_ID, CWOT.TRANSACTION_DATE, CWOT.TRANSACTION_QUANTITY QUANTITY, CWOT.TRANSACTION_UOM_CODE UOM_CODE, 'WO' TXN_SOURCE_DOC_TYPE, NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_DOC_NUMBER, 'WO' TXN_SOURCE_REF_DOC_TYPE, NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_REF_DOC_NUMBER, CWOT.CREATED_BY, CWOT.CREATION_DATE, CWOT.LAST_UPDATED_BY, CWOT.LAST_UPDATE_DATE, CWOT.LAST_UPDATE_LOGIN FROM FUSION.CST_WO_OPERATION_TXNS CWOT, FUSION.CST_WORK_ORDERS CWO, FUSION.CST_WORK_ORDERS_INT CWOI WHERE CWO.EXTERNAL_SYSTEM_REF_ID (+) = TO_CHAR(CWOT.WORK_ORDER_ID) AND CWOI.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWOT.WORK_ORDER_ID) AND CWOT.PROCESSED_BY_CA_FLAG IS NOT NULL UNION ALL SELECT CWUT.EXTERNAL_SYSTEM_REFERENCE, CWUT.EXTERNAL_SYSTEM_REF_ID, 'CST_WO_UPDATE_EVENTS_INT' SOURCE_TABLE, NVL(CWO.INVENTORY_ITEM_ID, CWOI.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID, NULL RESOURCE_ID, NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) INVENTORY_ORG_ID, NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) ITEM_ORGANIZATION_ID, 31 BASE_TXN_TYPE_ID, 0 BASE_TXN_SOURCE_TYPE_ID, 31 BASE_TXN_ACTION_ID, CWUT.TRANSACTION_DATE TRANSACTION_DATE, NULL QUANTITY, NULL UOM_CODE, 'WO' TXN_SOURCE_DOC_TYPE, CWO.WORK_ORDER_NUMBER TXN_SOURCE_DOC_NUMBER, 'WO' TXN_SOURCE_REF_DOC_TYPE, CWO.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER, CWUT.CREATED_BY, CWUT.CREATION_DATE, CWUT.LAST_UPDATED_BY, CWUT.LAST_UPDATE_DATE, CWUT.LAST_UPDATE_LOGIN FROM FUSION.CST_WO_UPDATE_EVENTS_INT CWUT, FUSION.CST_WORK_ORDERS CWO, FUSION.CST_WORK_ORDERS_INT CWOI WHERE CWO.EXTERNAL_SYSTEM_REF_ID (+) = CWUT.EXTERNAL_SYSTEM_REF_ID AND CWOI.EXTERNAL_SYSTEM_REF_ID (+) = CWUT.EXTERNAL_SYSTEM_REF_ID UNION ALL SELECT CWO.EXTERNAL_SYSTEM_REFERENCE, CWO.EXTERNAL_SYSTEM_REF_ID, 'CST_WORK_ORDERS' SOURCE_TABLE, CWO.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, NULL RESOURCE_ID, CWO.INVENTORY_ORGANIZATION_ID INVENTORY_ORG_ID, CWO.INVENTORY_ORGANIZATION_ID ITEM_ORGANIZATION_ID, 31 BASE_TXN_TYPE_ID, 0 BASE_TXN_SOURCE_TYPE_ID, 31 BASE_TXN_ACTION_ID, NULL TRANSACTION_DATE, NULL QUANTITY, NULL UOM_CODE, 'WO' TXN_SOURCE_DOC_TYPE, CWO.WORK_ORDER_NUMBER TXN_SOURCE_DOC_NUMBER, 'WO' TXN_SOURCE_REF_DOC_TYPE, CWO.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER, CWO.CREATED_BY, CWO.CREATION_DATE, CWO.LAST_UPDATED_BY, CWO.LAST_UPDATE_DATE, CWO.LAST_UPDATE_LOGIN FROM FUSION.CST_WORK_ORDERS CWO WHERE CWO.CST_WORK_ORDER_ID NOT IN( SELECT CWUT.CST_WORK_ORDER_ID FROM FUSION.CST_WO_UPDATE_EVENT_TXNS CWUT) UNION ALL SELECT CITE.EXTERNAL_SYSTEM_REFERENCE, CITE.EXTERNAL_SYSTEM_REF_ID, 'CST_I_TRADE_EVENTS' SOURCE_TABLE, CITE.INVENTORY_ITEM_ID, NULL RESOURCE_ID, CITE.INVENTORY_ORG_ID, CITE.INVENTORY_ORG_ID ITEM_ORGANIZATION_ID, CSA.BASE_TXN_ACTION_ID BASE_TXN_TYPE_ID, CSA.BASE_TXN_SOURCE_TYPE_ID, CSA.BASE_TXN_ACTION_ID, CITE.TRANSACTION_DATE, CITE.TRANSACTION_QTY QUANTITY, CITE.TRANSACTION_UOM_CODE UOM_CODE, CITE.OWNERSHIP_CHANGE_DOC_TYPE, CITE.OWNERSHIP_CHANGE_DOC_NUMBER, CITE.TXN_SOURCE_DOC_TYPE, CITE.TXN_SOURCE_DOC_NUMBER, CITE.CREATED_BY, CITE.CREATION_DATE, CITE.LAST_UPDATED_BY, CITE.LAST_UPDATE_DATE, CITE.LAST_UPDATE_LOGIN FROM fusion.CST_I_TRADE_EVENTS CITE, fusion.CST_TXN_SOURCE_ACTIONS CSA where CITE.TRANSACTION_TYPE = CSA.EXTERNAL_TXN_TYPE UNION ALL SELECT CTE.EXTERNAL_SYSTEM_REFERENCE, CTE.EXTERNAL_SYSTEM_REF_ID, 'CST_TRADE_EVENTS' SOURCE_TABLE, CTE.INVENTORY_ITEM_ID, NULL RESOURCE_ID, CTE.INVENTORY_ORG_ID, CTE.INVENTORY_ORG_ID ITEM_ORGANIZATION_ID, CSA.BASE_TXN_ACTION_ID BASE_TXN_TYPE_ID, CSA.BASE_TXN_SOURCE_TYPE_ID, CSA.BASE_TXN_ACTION_ID, CTE.TRANSACTION_DATE, CTE.TRANSACTION_QTY QUANTITY, CTE.TRANSACTION_UOM_CODE UOM_CODE, CTE.OWNERSHIP_CHANGE_DOC_TYPE, CTE.OWNERSHIP_CHANGE_DOC_NUMBER, CTE.TXN_SOURCE_DOC_TYPE, CTE.TXN_SOURCE_DOC_NUMBER, CTE.CREATED_BY, CTE.CREATION_DATE, CTE.LAST_UPDATED_BY, CTE.LAST_UPDATE_DATE, CTE.LAST_UPDATE_LOGIN FROM CST_TRADE_EVENTS CTE, CST_TXN_SOURCE_ACTIONS CSA WHERE CTE.TRANSACTION_TYPE NOT IN ('RECEIPT_ACCRUAL','RETURN_ACCRUAL') AND CTE.INVENTORY_ITEM_ID IS NOT NULL AND CTE.PROCESS_BY_CA_FLAG IS NOT NULL AND CTE.TRANSACTION_TYPE = CSA.EXTERNAL_TXN_TYPE

SQL_Statement

SELECT EXTERNAL_SYSTEM_REFERENCE,

EXTERNAL_SYSTEM_REF_ID,

'CST_I_INV_TRANSACTIONS' SOURCE_TABLE,

INVENTORY_ITEM_ID,

NULL RESOURCE_ID,

INVENTORY_ORG_ID,

INVENTORY_ORG_ID ITEM_ORGANIZATION_ID,

TRANSACTION_TYPE_ID BASE_TXN_TYPE_ID,

TRANSACTION_SOURCE_TYPE_ID BASE_TXN_SOURCE_TYPE_ID,

TRANSACTION_ACTION_ID BASE_TXN_ACTION_ID,

TRANSACTION_DATE,

TRANSACTION_QTY QUANTITY,

TRANSACTION_UOM_CODE UOM_CODE,

TXN_SOURCE_DOC_TYPE,

TXN_SOURCE_DOC_NUMBER,

TXN_SOURCE_REF_DOC_TYPE,

TXN_SOURCE_REF_DOC_NUMBER,

CREATED_BY,

CREATION_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATE_LOGIN

FROM CST_I_INV_TRANSACTIONS

UNION ALL

SELECT EXTERNAL_SYSTEM_REFERENCE,

EXTERNAL_SYSTEM_REF_ID,

'CST_INV_TRANSACTIONS' SOURCE_TABLE,

INVENTORY_ITEM_ID,

NULL RESOURCE_ID,

INVENTORY_ORG_ID,

INVENTORY_ORG_ID ITEM_ORGANIZATION_ID,

BASE_TXN_TYPE_ID BASE_TXN_TYPE_ID,

BASE_TXN_SOURCE_TYPE_ID BASE_TXN_SOURCE_TYPE_ID,

BASE_TXN_ACTION_ID BASE_TXN_ACTION_ID,

TRANSACTION_DATE,

TRANSACTION_QTY QUANTITY,

TRANSACTION_UOM_CODE UOM_CODE,

TXN_SOURCE_DOC_TYPE,

TXN_SOURCE_DOC_NUMBER,

TXN_SOURCE_REF_DOC_TYPE,

TXN_SOURCE_REF_DOC_NUMBER,

CREATED_BY,

CREATION_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATE_LOGIN

FROM CST_INV_TRANSACTIONS CIT

WHERE

EXISTS ( SELECT 1

FROM CST_INV_TRANSACTION_STATUS CITS

WHERE CITS.CST_INV_TRANSACTION_ID = CIT.CST_INV_TRANSACTION_ID

AND CITS.POSTED_FLAG IS NOT NULL)

UNION ALL

SELECT CWRT.EXTERNAL_SYSTEM_REFERENCE,

CWRT.EXTERNAL_SYSTEM_REF_ID,

'CST_WO_RESOURCE_TXNS_INT' SOURCE_TABLE,

NVL(CWO.INVENTORY_ITEM_ID, CWOI.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,

CWRT.RESOURCE_ID,

NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) INVENTORY_ORG_ID,

NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) ITEM_ORGANIZATION_ID,

21 BASE_TXN_TYPE_ID,

0 BASE_TXN_SOURCE_TYPE_ID,

21 BASE_TXN_ACTION_ID,

CWRT.TRANSACTION_DATE,

CWRT.TRANSACTION_QUANTITY QUANTITY,

CWRT.TRANSACTION_UOM_CODE UOM_CODE,

'WO' TXN_SOURCE_DOC_TYPE,

NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_DOC_NUMBER,

'WO' TXN_SOURCE_REF_DOC_TYPE,

NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_REF_DOC_NUMBER,

CWRT.CREATED_BY,

CWRT.CREATION_DATE,

CWRT.LAST_UPDATED_BY,

CWRT.LAST_UPDATE_DATE,

CWRT.LAST_UPDATE_LOGIN

FROM FUSION.CST_WO_RESOURCE_TXNS_INT CWRT,

FUSION.CST_WORK_ORDERS CWO,

FUSION.CST_WORK_ORDERS_INT CWOI

WHERE

CWO.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWRT.WORK_ORDER_ID) AND

CWOI.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWRT.WORK_ORDER_ID)

UNION ALL

SELECT CWRT.EXTERNAL_SYSTEM_REFERENCE,

CWRT.EXTERNAL_SYSTEM_REF_ID,

'CST_WO_RESOURCE_TXNS' SOURCE_TABLE,

NVL(CWO.INVENTORY_ITEM_ID, CWOI.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,

CWRT.RESOURCE_ID,

NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) INVENTORY_ORG_ID,

NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) ITEM_ORGANIZATION_ID,

21 BASE_TXN_TYPE_ID,

0 BASE_TXN_SOURCE_TYPE_ID,

21 BASE_TXN_ACTION_ID,

CWRT.TRANSACTION_DATE,

CWRT.TRANSACTION_QUANTITY QUANTITY,

CWRT.TRANSACTION_UOM_CODE UOM_CODE,

'WO' TXN_SOURCE_DOC_TYPE,

NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_DOC_NUMBER,

'WO' TXN_SOURCE_REF_DOC_TYPE,

NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_REF_DOC_NUMBER,

CWRT.CREATED_BY,

CWRT.CREATION_DATE,

CWRT.LAST_UPDATED_BY,

CWRT.LAST_UPDATE_DATE,

CWRT.LAST_UPDATE_LOGIN

FROM FUSION.CST_WO_RESOURCE_TXNS CWRT,

FUSION.CST_WORK_ORDERS CWO,

FUSION.CST_WORK_ORDERS_INT CWOI

WHERE

CWO.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWRT.WORK_ORDER_ID) AND

CWOI.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWRT.WORK_ORDER_ID) AND

CWRT.PROCESSED_BY_CA_FLAG IS NOT NULL

UNION ALL

SELECT CWOT.EXTERNAL_SYSTEM_REFERENCE,

CWOT.EXTERNAL_SYSTEM_REF_ID,

'CST_WO_OPERATION_TXNS_INT' SOURCE_TABLE,

NVL(CWO.INVENTORY_ITEM_ID, CWOI.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,

NULL RESOURCE_ID,

NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) INVENTORY_ORG_ID,

NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) ITEM_ORGANIZATION_ID,

231 BASE_TXN_TYPE_ID,

0 BASE_TXN_SOURCE_TYPE_ID,

23 BASE_TXN_ACTION_ID,

CWOT.TRANSACTION_DATE,

CWOT.TRANSACTION_QUANTITY QUANTITY,

CWOT.TRANSACTION_UOM_CODE UOM_CODE,

'WO' TXN_SOURCE_DOC_TYPE,

NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_DOC_NUMBER,

'WO' TXN_SOURCE_REF_DOC_TYPE,

NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_REF_DOC_NUMBER,

CWOT.CREATED_BY,

CWOT.CREATION_DATE,

CWOT.LAST_UPDATED_BY,

CWOT.LAST_UPDATE_DATE,

CWOT.LAST_UPDATE_LOGIN

FROM FUSION.CST_WO_OPERATION_TXNS_INT CWOT,

FUSION.CST_WORK_ORDERS CWO,

FUSION.CST_WORK_ORDERS_INT CWOI

WHERE

CWO.EXTERNAL_SYSTEM_REF_ID (+) = TO_CHAR(CWOT.WORK_ORDER_ID) AND

CWOI.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWOT.WORK_ORDER_ID)

UNION ALL

SELECT CWOT.EXTERNAL_SYSTEM_REFERENCE,

CWOT.EXTERNAL_SYSTEM_REF_ID,

'CST_WO_OPERATION_TXNS' SOURCE_TABLE,

NVL(CWO.INVENTORY_ITEM_ID, CWOI.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,

NULL RESOURCE_ID,

NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) INVENTORY_ORG_ID,

NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) ITEM_ORGANIZATION_ID,

231 BASE_TXN_TYPE_ID,

0 BASE_TXN_SOURCE_TYPE_ID,

23 BASE_TXN_ACTION_ID,

CWOT.TRANSACTION_DATE,

CWOT.TRANSACTION_QUANTITY QUANTITY,

CWOT.TRANSACTION_UOM_CODE UOM_CODE,

'WO' TXN_SOURCE_DOC_TYPE,

NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_DOC_NUMBER,

'WO' TXN_SOURCE_REF_DOC_TYPE,

NVL(CWO.WORK_ORDER_NUMBER, CWOI.WORK_ORDER_NUMBER) TXN_SOURCE_REF_DOC_NUMBER,

CWOT.CREATED_BY,

CWOT.CREATION_DATE,

CWOT.LAST_UPDATED_BY,

CWOT.LAST_UPDATE_DATE,

CWOT.LAST_UPDATE_LOGIN

FROM FUSION.CST_WO_OPERATION_TXNS CWOT,

FUSION.CST_WORK_ORDERS CWO,

FUSION.CST_WORK_ORDERS_INT CWOI

WHERE

CWO.EXTERNAL_SYSTEM_REF_ID (+) = TO_CHAR(CWOT.WORK_ORDER_ID) AND

CWOI.EXTERNAL_SYSTEM_REF_ID(+) = TO_CHAR(CWOT.WORK_ORDER_ID) AND

CWOT.PROCESSED_BY_CA_FLAG IS NOT NULL

UNION ALL

SELECT CWUT.EXTERNAL_SYSTEM_REFERENCE,

CWUT.EXTERNAL_SYSTEM_REF_ID,

'CST_WO_UPDATE_EVENTS_INT' SOURCE_TABLE,

NVL(CWO.INVENTORY_ITEM_ID, CWOI.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,

NULL RESOURCE_ID,

NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) INVENTORY_ORG_ID,

NVL(CWO.INVENTORY_ORGANIZATION_ID, CWOI.INVENTORY_ORGANIZATION_ID) ITEM_ORGANIZATION_ID,

31 BASE_TXN_TYPE_ID,

0 BASE_TXN_SOURCE_TYPE_ID,

31 BASE_TXN_ACTION_ID,

CWUT.TRANSACTION_DATE TRANSACTION_DATE,

NULL QUANTITY,

NULL UOM_CODE,

'WO' TXN_SOURCE_DOC_TYPE,

CWO.WORK_ORDER_NUMBER TXN_SOURCE_DOC_NUMBER,

'WO' TXN_SOURCE_REF_DOC_TYPE,

CWO.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER,

CWUT.CREATED_BY,

CWUT.CREATION_DATE,

CWUT.LAST_UPDATED_BY,

CWUT.LAST_UPDATE_DATE,

CWUT.LAST_UPDATE_LOGIN

FROM FUSION.CST_WO_UPDATE_EVENTS_INT CWUT,

FUSION.CST_WORK_ORDERS CWO,

FUSION.CST_WORK_ORDERS_INT CWOI

WHERE

CWO.EXTERNAL_SYSTEM_REF_ID (+) = CWUT.EXTERNAL_SYSTEM_REF_ID AND

CWOI.EXTERNAL_SYSTEM_REF_ID (+) = CWUT.EXTERNAL_SYSTEM_REF_ID

UNION ALL

SELECT CWO.EXTERNAL_SYSTEM_REFERENCE,

CWO.EXTERNAL_SYSTEM_REF_ID,

'CST_WORK_ORDERS' SOURCE_TABLE,

CWO.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,

NULL RESOURCE_ID,

CWO.INVENTORY_ORGANIZATION_ID INVENTORY_ORG_ID,

CWO.INVENTORY_ORGANIZATION_ID ITEM_ORGANIZATION_ID,

31 BASE_TXN_TYPE_ID,

0 BASE_TXN_SOURCE_TYPE_ID,

31 BASE_TXN_ACTION_ID,

NULL TRANSACTION_DATE,

NULL QUANTITY,

NULL UOM_CODE,

'WO' TXN_SOURCE_DOC_TYPE,

CWO.WORK_ORDER_NUMBER TXN_SOURCE_DOC_NUMBER,

'WO' TXN_SOURCE_REF_DOC_TYPE,

CWO.WORK_ORDER_NUMBER TXN_SOURCE_REF_DOC_NUMBER,

CWO.CREATED_BY,

CWO.CREATION_DATE,

CWO.LAST_UPDATED_BY,

CWO.LAST_UPDATE_DATE,

CWO.LAST_UPDATE_LOGIN

FROM

FUSION.CST_WORK_ORDERS CWO

WHERE

CWO.CST_WORK_ORDER_ID NOT IN(

SELECT CWUT.CST_WORK_ORDER_ID FROM FUSION.CST_WO_UPDATE_EVENT_TXNS CWUT)

UNION ALL

SELECT CITE.EXTERNAL_SYSTEM_REFERENCE,

CITE.EXTERNAL_SYSTEM_REF_ID,

'CST_I_TRADE_EVENTS' SOURCE_TABLE,

CITE.INVENTORY_ITEM_ID,

NULL RESOURCE_ID,

CITE.INVENTORY_ORG_ID,

CITE.INVENTORY_ORG_ID ITEM_ORGANIZATION_ID,

CSA.BASE_TXN_ACTION_ID BASE_TXN_TYPE_ID,

CSA.BASE_TXN_SOURCE_TYPE_ID,

CSA.BASE_TXN_ACTION_ID,

CITE.TRANSACTION_DATE,

CITE.TRANSACTION_QTY QUANTITY,

CITE.TRANSACTION_UOM_CODE UOM_CODE,

CITE.OWNERSHIP_CHANGE_DOC_TYPE,

CITE.OWNERSHIP_CHANGE_DOC_NUMBER,

CITE.TXN_SOURCE_DOC_TYPE,

CITE.TXN_SOURCE_DOC_NUMBER,

CITE.CREATED_BY,

CITE.CREATION_DATE,

CITE.LAST_UPDATED_BY,

CITE.LAST_UPDATE_DATE,

CITE.LAST_UPDATE_LOGIN

FROM fusion.CST_I_TRADE_EVENTS CITE,

fusion.CST_TXN_SOURCE_ACTIONS CSA

where CITE.TRANSACTION_TYPE = CSA.EXTERNAL_TXN_TYPE

UNION ALL

SELECT CTE.EXTERNAL_SYSTEM_REFERENCE,

CTE.EXTERNAL_SYSTEM_REF_ID,

'CST_TRADE_EVENTS' SOURCE_TABLE,

CTE.INVENTORY_ITEM_ID,

NULL RESOURCE_ID,

CTE.INVENTORY_ORG_ID,

CTE.INVENTORY_ORG_ID ITEM_ORGANIZATION_ID,

CSA.BASE_TXN_ACTION_ID BASE_TXN_TYPE_ID,

CSA.BASE_TXN_SOURCE_TYPE_ID,

CSA.BASE_TXN_ACTION_ID,

CTE.TRANSACTION_DATE,

CTE.TRANSACTION_QTY QUANTITY,

CTE.TRANSACTION_UOM_CODE UOM_CODE,

CTE.OWNERSHIP_CHANGE_DOC_TYPE,

CTE.OWNERSHIP_CHANGE_DOC_NUMBER,

CTE.TXN_SOURCE_DOC_TYPE,

CTE.TXN_SOURCE_DOC_NUMBER,

CTE.CREATED_BY,

CTE.CREATION_DATE,

CTE.LAST_UPDATED_BY,

CTE.LAST_UPDATE_DATE,

CTE.LAST_UPDATE_LOGIN

FROM CST_TRADE_EVENTS CTE,

CST_TXN_SOURCE_ACTIONS CSA

WHERE

CTE.TRANSACTION_TYPE NOT IN ('RECEIPT_ACCRUAL','RETURN_ACCRUAL')

AND CTE.INVENTORY_ITEM_ID IS NOT NULL

AND CTE.PROCESS_BY_CA_FLAG IS NOT NULL

AND CTE.TRANSACTION_TYPE = CSA.EXTERNAL_TXN_TYPE