Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Wednesday, 1 October 2008

PeopleSoft Monitor Message

These queries replicate the Monitor Message Page.

Message Instance

SELECT A.CHNLNAME AS "Channel Name"
     , SUM(C.APMSG_XTB_0) AS "Error"
     , SUM(C.APMSG_XTB_1) AS "New"
     , SUM(C.APMSG_XTB_2) AS "Started"
     , SUM(C.APMSG_XTB_3) AS "Working"
     , SUM(C.APMSG_XTB_4) AS "Done"
     , SUM(C.APMSG_XTB_5) AS "Retry"
     , SUM(C.APMSG_XTB_6) AS "Timeout"
     , SUM(C.APMSG_XTB_7) AS "Edited"
     , SUM(C.APMSG_XTB_8) AS "Canceled"
  FROM PSAPMSGPUBHDR A
     , PSAPMSGXTB C
WHERE A.PUBSTATUS = C.APMSG_XTB_STAT
GROUP BY A.CHNLNAME
ORDER BY A.CHNLNAME;

Publication Contract

SELECT A.CHNLNAME AS "Channel Name"
     , SUM(C.APMSG_XTB_0) AS "Error"
     , SUM(C.APMSG_XTB_1) AS "New"
     , SUM(C.APMSG_XTB_2) AS "Started"
     , SUM(C.APMSG_XTB_3) AS "Working"
     , SUM(C.APMSG_XTB_4) AS "Done"
     , SUM(C.APMSG_XTB_5) AS "Retry"
     , SUM(C.APMSG_XTB_6) AS "Timeout"
     , SUM(C.APMSG_XTB_7) AS "Edited"
     , SUM(C.APMSG_XTB_8) AS "Cancelled"
  FROM PSAPMSGPUBCON A
     , PSAPMSGPUBHDR B
     , PSAPMSGXTB C
 WHERE A.PUBID = B.PUBID
   AND A.PUBNODE = B.PUBNODE
   AND A.CHNLNAME = B.CHNLNAME
   AND A.PUBCONSTATUS = C.APMSG_XTB_STAT
GROUP BY A.CHNLNAME
ORDER BY A.CHNLNAME;

Subscription Contract

SELECT A.CHNLNAME AS "Channel Name"
     , SUM(C.APMSG_XTB_0) AS "Error"
     , SUM(C.APMSG_XTB_1) AS "New"
     , SUM(C.APMSG_XTB_2) AS "Started"
     , SUM(C.APMSG_XTB_3) AS "Working"
     , SUM(C.APMSG_XTB_4) AS "Done"
     , SUM(C.APMSG_XTB_5) AS "Retry"
     , SUM(C.APMSG_XTB_6) AS "Timeout"
     , SUM(C.APMSG_XTB_7) AS "Edited"
     , SUM(C.APMSG_XTB_8) AS "Canceled"
  FROM PSAPMSGSUBCON A
     , PSAPMSGPUBHDR B
     , PSAPMSGXTB C
 WHERE A.PUBID = B.PUBID
   AND A.PUBNODE = B.PUBNODE
   AND A.CHNLNAME = B.CHNLNAME
   AND A.SUBCONSTATUS = C.APMSG_XTB_STAT
GROUP BY A.CHNLNAME
ORDER BY A.CHNLNAME;

Note: You could use the field LASTUPDDTTM to filter by "Days"

No comments: