PS Query & SQL

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
------------------------------

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
------------------------------

------------------------------
Brandon Nadeau
Manager of Data Operations and Campus Relations
University of Maine System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------