Hi Lidia,
I've queried much of this but have always stopped short of the BIP data. I've opted to get that by hand when needed and to keep it in a spreadsheet. It's been a while, but I'm pretty sure that was because of security hurdles that I didn't think were worth the effort at the time.
I'll offer up two queries here. The first is to get the comm setup data. The second is especially useful if you're running comms through JobSets and want to see the 3CENGINE details attached to those run controls.
This is the Communications setup. It includes a count of the number of times each letter code has been assigned.
SELECT A.SCC_LETTER_CD, A.DESCR, A.ADMIN_FUNCTION, A.SCC_CG_PARMS, COUNT(*), C.COMM_KEY, C.COMM_CATEGORY, C.COMM_CONTEXT, C.COMM_METHOD, C.COMM_DIRECTION, D.EVENT_3CS_ID
FROM (((PS_SCC_STN_LTR_TBL A LEFT OUTER JOIN PS_COMMUNICATION B ON A.SCC_LETTER_CD = B.SCC_LETTER_CD ) LEFT OUTER JOIN PS_COMM_SPDKEY_TBL C ON A.SCC_LETTER_CD = C.SCC_LETTER_CD AND C.ADMIN_FUNCTION = A.ADMIN_FUNCTION ) LEFT OUTER JOIN PS_EVNT_COMM_TBL D ON A.SCC_LETTER_CD = D.SCC_LETTER_CD )
WHERE ( A.ADMIN_FUNCTION IN ('FINA','FINT','AWRD','LOAN','BDGT','ISIR')
AND ( D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_EVNT_COMM_TBL D_ED
WHERE D.INSTITUTION = D_ED.INSTITUTION
AND D.EVENT_3CS_ID = D_ED.EVENT_3CS_ID
AND D_ED.EFFDT <= SYSDATE)
OR D.EFFDT IS NULL))
GROUP BY A.SCC_LETTER_CD, A.DESCR, A.ADMIN_FUNCTION, A.SCC_CG_PARMS, C.COMM_KEY, C.COMM_CATEGORY, C.COMM_CONTEXT, C.COMM_METHOD, C.COMM_DIRECTION, D.EVENT_3CS_ID
Here's the query that returns JobSet data with 3CENGINE run control details. All those expressions at the beginning are intended to capture how Jobs can be nested inside of other Jobs - this one goes 4 layers deep and produces a meaningful sequence number to keep it all straight.
SELECT D.SCHEDULENAME, D.DESCR, A.JOBNAMESRC, D.OPRID, D.SCHEDULESTATUS, D.RECURNAME, CASE
WHEN A.PRCSITEMLEVEL = 0 THEN '00'
WHEN A.PRCSITEMLEVEL = 1 THEN LPAD(TO_CHAR( A.PRCSJOBSEQ),2,'0')
WHEN A.PRCSITEMLEVEL = 2 THEN LPAD(TO_CHAR( A.PARENTJOBSEQNO),2,'0')
WHEN A.PRCSITEMLEVEL = 3 THEN LPAD(TO_CHAR( B.PARENTJOBSEQNO),2,'0')
WHEN A.PRCSITEMLEVEL = 4 THEN LPAD(TO_CHAR( C.PARENTJOBSEQNO),2,'0')
END || '.' ||
CASE
WHEN A.PRCSITEMLEVEL < 2 THEN '00'
WHEN A.PRCSITEMLEVEL = 2 THEN LPAD(TO_CHAR( A.PRCSJOBSEQ),2,'0')
WHEN A.PRCSITEMLEVEL = 3 THEN LPAD(TO_CHAR( B.PRCSJOBSEQ),2,'0')
ELSE LPAD(TO_CHAR( B.PARENTJOBSEQNO),2,'0')
END || '.' ||
CASE
WHEN A.PRCSITEMLEVEL < 3 THEN '00'
WHEN A.PRCSITEMLEVEL = 3 THEN LPAD(TO_CHAR( A.PRCSJOBSEQ),2,'0')
WHEN A.PRCSITEMLEVEL = 4 THEN LPAD(TO_CHAR( A.PARENTJOBSEQNO),2,'0')
END || '.' ||
CASE
WHEN A.PRCSITEMLEVEL < 4 THEN '00'
WHEN A.PRCSITEMLEVEL = 4 THEN LPAD(TO_CHAR( A.PRCSJOBSEQ),2,'0')
END, A.PRCSTYPE, A.PRCSNAME, A.RUN_CNTL_ID, E.SCCPS_QUERY_NAME, LISTAGG ( F.FIELDNAME, ', '), LISTAGG ( F.BNDVALUE,', '), TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM PS_SCHDLITEM A, (PS_SCHDLITEM B LEFT OUTER JOIN PS_SCHDLITEM C ON B.SCHEDULENAME = C.SCHEDULENAME AND B.PARENT_ITEM_NODE = C.ITEM_NODE ), PS_SCHDLDEFN D, PS_RUN_CNTL_3CENG E, PS_SCCPS_RUN_PARM F
WHERE ( ( A.SCHEDULENAME = B.SCHEDULENAME
AND ( B.ITEM_NODE = A.PARENT_ITEM_NODE
OR B.ITEM_NODE IS NULL)
AND A.SCHEDULENAME = D.SCHEDULENAME
AND A.JOBNAMESRC = D.JOBNAMESRC
AND D.SCHEDULESTATUS = :1
AND A.PRCSNAME = '3CENGINE'
AND E.RUN_CNTL_ID = A.RUN_CNTL_ID
AND E.OPRID = F.OPRID
AND E.RUN_CNTL_ID = F.RUN_CNTL_ID
AND F.SCCPS_CNTXT_ID = E.SCCPS_CNTXT_ID ))
GROUP BY D.SCHEDULENAME, D.DESCR, A.JOBNAMESRC, D.OPRID, D.SCHEDULESTATUS, D.RECURNAME, CASE
WHEN A.PRCSITEMLEVEL = 0 THEN '00'
WHEN A.PRCSITEMLEVEL = 1 THEN LPAD(TO_CHAR( A.PRCSJOBSEQ),2,'0')
WHEN A.PRCSITEMLEVEL = 2 THEN LPAD(TO_CHAR( A.PARENTJOBSEQNO),2,'0')
WHEN A.PRCSITEMLEVEL = 3 THEN LPAD(TO_CHAR( B.PARENTJOBSEQNO),2,'0')
WHEN A.PRCSITEMLEVEL = 4 THEN LPAD(TO_CHAR( C.PARENTJOBSEQNO),2,'0')
END || '.' ||
CASE
WHEN A.PRCSITEMLEVEL < 2 THEN '00'
WHEN A.PRCSITEMLEVEL = 2 THEN LPAD(TO_CHAR( A.PRCSJOBSEQ),2,'0')
WHEN A.PRCSITEMLEVEL = 3 THEN LPAD(TO_CHAR( B.PRCSJOBSEQ),2,'0')
ELSE LPAD(TO_CHAR( B.PARENTJOBSEQNO),2,'0')
END || '.' ||
CASE
WHEN A.PRCSITEMLEVEL < 3 THEN '00'
WHEN A.PRCSITEMLEVEL = 3 THEN LPAD(TO_CHAR( A.PRCSJOBSEQ),2,'0')
WHEN A.PRCSITEMLEVEL = 4 THEN LPAD(TO_CHAR( A.PARENTJOBSEQNO),2,'0')
END || '.' ||
CASE
WHEN A.PRCSITEMLEVEL < 4 THEN '00'
WHEN A.PRCSITEMLEVEL = 4 THEN LPAD(TO_CHAR( A.PRCSJOBSEQ),2,'0')
END, A.PRCSTYPE, A.PRCSNAME, A.RUN_CNTL_ID, E.SCCPS_QUERY_NAME
ORDER BY 1, 7
------------------------------
Timothy Krug
Consulting Manager
Huron Consulting Group
------------------------------
Original Message:
Sent: 04-29-2026 11:17 AM
From: Lidia Anderson
Subject: Query CommGen's - Do you do this?
Hello Friends!
It's me again!! I have another request for query support from our wonderful community. We are developing a process to document all of the communications that we have set up. Our first attempt is a spreadsheet that manually lists out key components; however, as many know, this can be difficult to sustain long-term.
That's where the query comes in. We have some simple communication queries but we are seeking ideas from others who have done this already or has ideas on creating an effective query to capture the key components needed to find communications, how they are set up, and when they are running, including the BI publisher part of the process? The complexity arises when integrating the BI publisher components.
Some key indicators that we think are helpful include:
- Communication category
- Administrative Function
- Communication Context
- PS Query Name
- Data Source ID
- Letter Code
- BI Publisher Report Name
- Event ID
- Event 3C Group
- RUNCTL name
- OPRID name
Thanks!
------------------------------
Lidia Anderson
Manager, Campus Solutions
Central Washington University
lidia.anderson@cwu.edu
------------------------------