eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPLSYS.AQ$FND_CP_TM_AQTBL

Source

View Text - Preformatted


SELECT  q_name QUEUE, qt.msgid MSG_ID, corrid CORR_ID,  priority MSG_PRIORITY,  decode(s.subscriber_type,2,'UNDELIVERABLE',
                                             decode(h.dequeue_time, NULL,decode(l.dequeue_time, NULL, decode(state, 0,   'READY',	     
                              		                                          1,   'WAIT',	     
                                                                                  2,   'PROCESSED',	     
                                                                                  3,   'EXPIRED',
                                                                                  8,   'DEFERRED',
                                                                                 10,  'BUFFERED_EXPIRED'),(decode(l.transaction_id, NULL, 'UNDELIVERABLE', 'PROCESSED'))), (decode(h.transaction_id, NULL, 'UNDELIVERABLE', 'PROCESSED')))
                          )                         MSG_STATE,  cast(FROM_TZ(qt.delay, '-08:00')
                 at time zone sessiontimezone as date) delay,  cast(FROM_TZ(qt.delay, '-08:00')
                 at time zone sessiontimezone as timestamp) DELAY_TIMESTAMP, expiration,  cast(FROM_TZ(qt.enq_time, '-08:00')
                 at time zone sessiontimezone as date) enq_time,  cast(FROM_TZ(qt.enq_time, '-08:00')
                 at time zone sessiontimezone as timestamp)
                 enq_timestamp,   enq_uid ENQ_USER_ID,  qt.enq_tid ENQ_TXN_ID,  decode(h.transaction_id, NULL, 
                 decode(l.transaction_id, NULL, TO_DATE(NULL), l.dequeue_time),
                        cast(FROM_TZ(h.dequeue_time, '-08:00')
                 at time zone sessiontimezone as date)) DEQ_TIME,  decode(h.transaction_id, NULL, 
            decode(l.transaction_id, NULL, TO_TIMESTAMP(NULL), l.dequeue_time),
                        cast(FROM_TZ(h.dequeue_time, '-08:00')
                 at time zone sessiontimezone as timestamp))
                 DEQ_TIMESTAMP,  decode(h.dequeue_user, NULL, l.dequeue_user, h.dequeue_user) 
	         DEQ_USER_ID,  decode(h.transaction_id, NULL, l.transaction_id, 
                 h.transaction_id) DEQ_TXN_ID,  h.retry_count retry_count,  decode (state, 0, exception_qschema, 
                                1, exception_qschema, 
                                2, exception_qschema,  
                                NULL) EXCEPTION_QUEUE_OWNER,  decode (state, 0, exception_queue, 
                                1, exception_queue, 
                                2, exception_queue,  
                                NULL) EXCEPTION_QUEUE,  user_data,  h.propagated_msgid PROPAGATED_MSGID,  sender_name  SENDER_NAME, sender_address  SENDER_ADDRESS, sender_protocol  SENDER_PROTOCOL, dequeue_msgid ORIGINAL_MSGID,  decode (h.dequeue_time, NULL, decode (l.dequeue_time, NULL,
                   decode (state, 3, exception_queue, NULL), 
                   decode (l.transaction_id, NULL, NULL, exception_queue)),
                   decode (h.transaction_id, NULL, NULL, exception_queue)) 
                                ORIGINAL_QUEUE_NAME,  decode (h.dequeue_time, NULL, decode (l.dequeue_time, NULL,
                   decode (state, 3, exception_qschema, NULL), 
                   decode (l.transaction_id, NULL, NULL, exception_qschema)),
                   decode (h.transaction_id, NULL, NULL, exception_qschema)) 
                                ORIGINAL_QUEUE_OWNER,  decode(s.subscriber_type,2,
                                         'Messages to be cleaned up later',
                        decode(h.dequeue_time, NULL, 
                               decode(state, 3, 
                                           decode(h.transaction_id, NULL, 
                                           decode (expiration , NULL , 
                                                   'MAX_RETRY_EXCEEDED',
                                                   'TIME_EXPIRATION'),
                                                   'INVALID_TRANSACTION', NULL,
                                                   'MAX_RETRY_EXCEEDED'), NULL),
                                           decode(h.transaction_id, NULL, 
                                                 'PROPAGATION_FAILURE', NULL)))
                 EXPIRATION_REASON,  decode(h.subscriber#, 0, decode(h.name, '0', NULL,
							        h.name),
					  s.name) CONSUMER_NAME,  s.address ADDRESS,  s.protocol PROTOCOL  FROM "FND_CP_TM_AQTBL" qt, "AQ$_FND_CP_TM_AQTBL_H" h LEFT OUTER JOIN "AQ$_FND_CP_TM_AQTBL_L" l  ON h.msgid = l.msgid AND h.subscriber# = l.subscriber# AND h.name = l.name AND h.address# = l.address#, "AQ$_FND_CP_TM_AQTBL_S" s  WHERE  qt.msgid = h.msgid AND  ((h.subscriber# != 0 AND h.subscriber# = s.subscriber_id)  OR (h.subscriber# = 0 AND h.address# = s.subscriber_id)) AND (qt.state != 7 OR   qt.state != 9 )     WITH READ ONLY

View Text - HTML Formatted

SELECT Q_NAME QUEUE
, QT.MSGID MSG_ID
, CORRID CORR_ID
, PRIORITY MSG_PRIORITY
, DECODE(S.SUBSCRIBER_TYPE
, 2
, 'UNDELIVERABLE'
, DECODE(H.DEQUEUE_TIME
, NULL
, DECODE(L.DEQUEUE_TIME
, NULL
, DECODE(STATE
, 0
, 'READY'
, 1
, 'WAIT'
, 2
, 'PROCESSED'
, 3
, 'EXPIRED'
, 8
, 'DEFERRED'
, 10
, 'BUFFERED_EXPIRED')
, (DECODE(L.TRANSACTION_ID
, NULL
, 'UNDELIVERABLE'
, 'PROCESSED')))
, (DECODE(H.TRANSACTION_ID
, NULL
, 'UNDELIVERABLE'
, 'PROCESSED'))) ) MSG_STATE
, CAST(FROM_TZ(QT.DELAY
, '-08:00') AT TIME ZONE SESSIONTIMEZONE AS DATE) DELAY
, CAST(FROM_TZ(QT.DELAY
, '-08:00') AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP) DELAY_TIMESTAMP
, EXPIRATION
, CAST(FROM_TZ(QT.ENQ_TIME
, '-08:00') AT TIME ZONE SESSIONTIMEZONE AS DATE) ENQ_TIME
, CAST(FROM_TZ(QT.ENQ_TIME
, '-08:00') AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP) ENQ_TIMESTAMP
, ENQ_UID ENQ_USER_ID
, QT.ENQ_TID ENQ_TXN_ID
, DECODE(H.TRANSACTION_ID
, NULL
, DECODE(L.TRANSACTION_ID
, NULL
, TO_DATE(NULL)
, L.DEQUEUE_TIME)
, CAST(FROM_TZ(H.DEQUEUE_TIME
, '-08:00') AT TIME ZONE SESSIONTIMEZONE AS DATE)) DEQ_TIME
, DECODE(H.TRANSACTION_ID
, NULL
, DECODE(L.TRANSACTION_ID
, NULL
, TO_TIMESTAMP(NULL)
, L.DEQUEUE_TIME)
, CAST(FROM_TZ(H.DEQUEUE_TIME
, '-08:00') AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP)) DEQ_TIMESTAMP
, DECODE(H.DEQUEUE_USER
, NULL
, L.DEQUEUE_USER
, H.DEQUEUE_USER) DEQ_USER_ID
, DECODE(H.TRANSACTION_ID
, NULL
, L.TRANSACTION_ID
, H.TRANSACTION_ID) DEQ_TXN_ID
, H.RETRY_COUNT RETRY_COUNT
, DECODE (STATE
, 0
, EXCEPTION_QSCHEMA
, 1
, EXCEPTION_QSCHEMA
, 2
, EXCEPTION_QSCHEMA
, NULL) EXCEPTION_QUEUE_OWNER
, DECODE (STATE
, 0
, EXCEPTION_QUEUE
, 1
, EXCEPTION_QUEUE
, 2
, EXCEPTION_QUEUE
, NULL) EXCEPTION_QUEUE
, USER_DATA
, H.PROPAGATED_MSGID PROPAGATED_MSGID
, SENDER_NAME SENDER_NAME
, SENDER_ADDRESS SENDER_ADDRESS
, SENDER_PROTOCOL SENDER_PROTOCOL
, DEQUEUE_MSGID ORIGINAL_MSGID
, DECODE (H.DEQUEUE_TIME
, NULL
, DECODE (L.DEQUEUE_TIME
, NULL
, DECODE (STATE
, 3
, EXCEPTION_QUEUE
, NULL)
, DECODE (L.TRANSACTION_ID
, NULL
, NULL
, EXCEPTION_QUEUE))
, DECODE (H.TRANSACTION_ID
, NULL
, NULL
, EXCEPTION_QUEUE)) ORIGINAL_QUEUE_NAME
, DECODE (H.DEQUEUE_TIME
, NULL
, DECODE (L.DEQUEUE_TIME
, NULL
, DECODE (STATE
, 3
, EXCEPTION_QSCHEMA
, NULL)
, DECODE (L.TRANSACTION_ID
, NULL
, NULL
, EXCEPTION_QSCHEMA))
, DECODE (H.TRANSACTION_ID
, NULL
, NULL
, EXCEPTION_QSCHEMA)) ORIGINAL_QUEUE_OWNER
, DECODE(S.SUBSCRIBER_TYPE
, 2
, 'MESSAGES TO BE CLEANED UP LATER'
, DECODE(H.DEQUEUE_TIME
, NULL
, DECODE(STATE
, 3
, DECODE(H.TRANSACTION_ID
, NULL
, DECODE (EXPIRATION
, NULL
, 'MAX_RETRY_EXCEEDED'
, 'TIME_EXPIRATION')
, 'INVALID_TRANSACTION'
, NULL
, 'MAX_RETRY_EXCEEDED')
, NULL)
, DECODE(H.TRANSACTION_ID
, NULL
, 'PROPAGATION_FAILURE'
, NULL))) EXPIRATION_REASON
, DECODE(H.SUBSCRIBER#
, 0
, DECODE(H.NAME
, '0'
, NULL
, H.NAME)
, S.NAME) CONSUMER_NAME
, S.ADDRESS ADDRESS
, S.PROTOCOL PROTOCOL
FROM "FND_CP_TM_AQTBL" QT
, "AQ$_FND_CP_TM_AQTBL_H" H LEFT OUTER JOIN "AQ$_FND_CP_TM_AQTBL_L" L ON H.MSGID = L.MSGID
AND H.SUBSCRIBER# = L.SUBSCRIBER#
AND H.NAME = L.NAME
AND H.ADDRESS# = L.ADDRESS#
, "AQ$_FND_CP_TM_AQTBL_S" S
WHERE QT.MSGID = H.MSGID
AND ((H.SUBSCRIBER# != 0
AND H.SUBSCRIBER# = S.SUBSCRIBER_ID) OR (H.SUBSCRIBER# = 0
AND H.ADDRESS# = S.SUBSCRIBER_ID))
AND (QT.STATE != 7 OR QT.STATE != 9 ) WITH READ ONLY