CST_UPG_INCOMPLETE_IOT_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
CST_INV_TRANSACTION_ID EXTERNAL_SYSTEM_REFERENCE EXTERNAL_SYSTEM_REF_ID SHIPPED RECEIVED REMAINING

Query

SQL_Statement

SELECT A.CST_INV_TRANSACTION_ID, A.EXTERNAL_SYSTEM_REFERENCE, A.EXTERNAL_SYSTEM_REF_ID, A.TRANSACTION_QTY SHIPPED, NVL(SUM(B.TRANSACTION_QTY),0) RECEIVED, NVL(A.TRANSACTION_QTY + SUM(B.TRANSACTION_QTY),A.TRANSACTION_QTY) REMAINING FROM CST_INV_TRANSACTIONS A, CST_INV_TRANSACTIONS B WHERE (A.BASE_TXN_SOURCE_TYPE_ID, A.BASE_TXN_ACTION_ID ) IN ((13,21)) AND (B.BASE_TXN_SOURCE_TYPE_ID(+) ,B.BASE_TXN_ACTION_ID(+)) IN ((13,12)) AND A.EXTERNAL_SYSTEM_REF_ID = B.TRANSFER_TRANSACTION_ID(+) AND A.CREATION_DATE < (SELECT NVL(MIN(CTE.CREATION_DATE),SYSDATE) FROM CST_TRADE_EVENTS CTE) AND NOT EXISTS (SELECT 'X' FROM CST_UPG_INTERCO_TXNS CUIT WHERE CUIT.CST_INV_TRANSACTION_ID = A.CST_INV_TRANSACTION_ID) GROUP BY A.CST_INV_TRANSACTION_ID, A.EXTERNAL_SYSTEM_REFERENCE, A.EXTERNAL_SYSTEM_REF_ID, A.TRANSACTION_QTY HAVING NVL(A.TRANSACTION_QTY + SUM(B.TRANSACTION_QTY),A.TRANSACTION_QTY) < 0