Integration broker debug with SQL

​​PeopleSoft delivers an application engine workflow solution to send notifications to a user if a message is in an error status. Simply schedule the delivered app engine program PT_AMM_WF as a recurring job.

Check meesages in error state:

select * from PSAPMSGPUBHDR HDR, PSAPMSGPUBCON CON where HDR.IBTRANSACTIONID=CON.IBPUBTRANSACTID and CON.STATUSSTRING not in ('DONE', 'CNCLD')

0 - Error, 1 - New , 2 - Started, 3 - Working,
4 - Done, 5 - Retry, 6 - Timeout, 7 - Edited, 8 - Canceled

select * FROM PSIBERRP WHERE MESSAGE_PARM LIKE '%1000076%'
select * from PSAPMSGSUBCON
select * from PSAPMSGPUBHDR
select * from PSAPMSGPUBDATA
select * from PSAPMSGPUBCON

select * from PSAPMSGPUBCON where STATUSSTRING='ERROR'

select * from PSAPMSGPUBHDR HDR, PSAPMSGPUBCON CON where HDR.IBTRANSACTIONID=CON.IBPUBTRANSACTID and CON.STATUSSTRING not in ('DONE', 'CNCLD')

select distinct STATUSSTRING from PSAPMSGPUBCON

select *

from PSAPMSGPUBHDR

where PUBSTATUS in (0, 5, 6)

Here's a simplified example of the where clauses. You'll need 3 of them. I added status 5, as I recall it's the timeout status. I took these directly from the app engine pt_amm_wf.

To get errored message instances before a contract is created:
select field list
from psapmsgpubhdr
where pubstatus in (0, 5, 6)
and your date criteria

To get errored pub contracts:
select field list
from psapmsgpubcon a,
psapmmsgpubhdr b
where a.pubnode = b.pubnode
and a.chnlname = b.chnlname
and a.pubid = b.pubid
and a.pubconstatus in (0, 5, 6)
and your date criteria

To get errored sub contracts:
select field list
from psapmsgsubcon
where subconstatus in (0, 5, 6)
and your date criteria​​