1. 1
2. 2
3. 3
4. 4
5. 5
6. 6
7. 7
8. 8
9. 9
10. 10
11. 11
12. 12
13. 13
14. 14
15. 15
16. 16
17. 17
18. 18
19. 19
20. 20
21. 21
22. 22

CREATE OR REPLACE VIEW PR_CCC_QP_TRADES AS
select "INT_MSG_ID","PRODUCT_TYPE","ICE_TRANSACTION_ID","SUMMIT_TRADE_ID","MPP_ID","BLOCK_MSG_ID","BUY_SELL","SUMMIT_BOOK","ICELINK_BOOK","SUMMIT_COUNTERPARTY","ICELINK_COUNTERPARTY","NOTIONAL","CURRENCY","DB_PAY_REC","FIXED_RATE","SPREAD","TRADE_DATE","EFFECTIVE_DATE","END_DATE","FEATURE_CODES","STRUCTURE_ID","CURVE_NAME","CDX_IDX_RED_CODE","CDX_IDX_NAME","RESTRUCT_TYPE","EVENT_TRANSACTION","EVENT_SUBTYPE","EVENT_STATUS","REJECT_CODE","REJECT_REASON","STATUS","CLEARING_STATUS","NOTIF_ORIGINATOR","NOTIF_VERSIONNO","NOTIF_TIMESTAMP","ORIG_SYSTEM_NAME","ORIG_SYSTEM_LOC","CATALOGUE_CODE","MSG_COMMENTS","MSG_SENDER","RETRY_NUMBER","SOURCE_MSG","MODIFICATION_TIME","CREATION_TIME","CLEARING_TYPE" from (
	  SELECT ICE.* FROM TPADMIN_OWNER.CCC_QP_TRADES ICE,
		TPADMIN_OWNER.CCC_QP_TRADE_STATUS S
	  WHERE ICE.STATUS = S.STATUS
	  AND S.PRIORITY =
		(SELECT MIN(s.priority)
		FROM TPADMIN_OWNER.CCC_QP_TRADES CM
		JOIN TPADMIN_OWNER.CCC_QP_TRADE_STATUS S
		ON CM.STATUS    = S.STATUS
		WHERE CM.MPP_ID = ICE.MPP_ID)
  ) T 
  WHERE T.NOTIF_TIMESTAMP = (SELECT MAX(trade.NOTIF_TIMESTAMP)
    FROM TPADMIN_OWNER.CCC_QP_TRADES trade
    WHERE T.STATUS = trade.STATUS
    and t.MPP_ID = trade.MPP_ID)
    AND T.INT_MSG_ID =  (SELECT MAX(trade.INT_MSG_ID)
    FROM TPADMIN_OWNER.CCC_QP_TRADES trade
    WHERE T.STATUS       = TRADE.STATUS
    AND T.MPP_ID         = TRADE.MPP_ID
    AND T.NOTIF_TIMESTAMP=TRADE.NOTIF_TIMESTAMP
    );