PS Query & SQL

 View Only

  • 1.  Record for Prompts for Queries in Run Control tables

    Posted 05-21-2025 11:50 AM

    Does anyone know what the record is if I wanted to query the prompts that are used for a query on a pop select run control.  I think the answer is going to be "it depends on the run control", but I'm optimistically hoping that it is a single record for every one :). 

    Specifically, I'm looking for the User Edit Message Process ( Financial Aid > Disbursement >  Process User Edit Messages).  I have this thing running 20+ times to add/update values based on the same query, but different prompt values and I want a query to make sure I've entered all the fields correctly.  I know the table names for the run control  (SFA_RUN_EDITMSG and SFA_RUN_EDMSOVR), but all the traditional ways that I use to find the table name for query prompts are failing me.

    Thanks!



    ------------------------------
    Scott Cho
    ------------------------------
    Alliance 2026 Registration is Open!


  • 2.  RE: Record for Prompts for Queries in Run Control tables

    Posted 05-21-2025 12:15 PM

    SCCPS_RUN_PARM 

     

    I have many, many, many QA queries to check this stuff.  😊

     




    Original Message
    Alliance 2026 Registration is Open!


  • 3.  RE: Record for Prompts for Queries in Run Control tables

    Posted 05-21-2025 12:23 PM

    Thanks Dana!

     

     

    Scott Cho

    PeopleSoft Campus Solutions Consultant

    630/384-9711

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 4.  RE: Record for Prompts for Queries in Run Control tables

    Posted 05-21-2025 02:06 PM
    Hi Scott,

    Here is a SQL I wrote a few years back to find query prompts based on a prompted OPRID. It returns information for any of the binds, but gives more information for 3C or Pop Update run controls. It will still return the prompts for other processes, but won't give all of the additional information because I didn't get around to finding all of the records and putting them in the SQL:

    Select distinct A.oprid, A.run_cntl_id, A.BNDNUM, A.FIELDNAME, TO_CHAR(SUBSTR(a.bndvalue,1,100)) as BindValue, NVL(b.sccps_query_name,c.sccps_query_name) as Query,
    b.EVENT_3CS_ID, NVL(d.heading,e.heading) as BindName, TO_CHAR(MAX(F.RUNDTTM),'YYYY-MM-DD HHMM.SS') AS LastRun

    From ps_SCCPS_RUN_PARM A
        LEFT OUTER JOIN PS_RUN_CNTL_3CENG B
            ON a.oprid = b.oprid
            AND a.run_cntl_id = b.run_cntl_id
        LEFT OUTER JOIN PS_SCC_RUN_POP_UPD C
            ON a.oprid = c.oprid
            AND a.run_cntl_id = c.run_cntl_id
        LEFT OUTER JOIN PSQRYBIND D
            ON b.sccps_query_name = d.qryname
            AND a.bndnum = d.bndnum
        LEFT OUTER JOIN PSQRYBIND E
            ON c.sccps_query_name = e.qryname
            AND a.bndnum = e.bndnum
        LEFT OUTER JOIN PS_PMN_PRCSLIST F
            ON a.oprid = f.oprid
            AND a.run_cntl_id = f.runcntlid
           
    Where  A.oprid = :1
    group by A.oprid, A.run_cntl_id, A.BNDNUM, A.FIELDNAME, TO_CHAR(SUBSTR(a.bndvalue,1,100)), NVL(b.sccps_query_name,c.sccps_query_name), b.EVENT_3CS_ID, NVL(d.heading,e.heading)
    order by A.run_cntl_id, A.BNDNUM

    Best,

    Jeffrie
    --
    Jeffrie Brooks | BUSINESS SYSTEM ANALYST
    UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES
    734-647-8763 | jedobr@umich.edu



    Original Message
    Alliance 2026 Registration is Open!


  • 5.  RE: Record for Prompts for Queries in Run Control tables

    Posted 05-21-2025 02:43 PM

    Thanks Jeffrie!

     

     

    Scott Cho

    PeopleSoft Campus Solutions Consultant

    630/384-9711

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 6.  RE: Record for Prompts for Queries in Run Control tables

    Posted 05-21-2025 12:18 PM

    I have one for UEMs:

     

    SELECT A.run_cntl_id,
           A.sccps_query_name,
           A.attachuserfile,
           A.sfa_process_type,
           B.aid_year,
           B.edit_msg_type,
           B.edit_msg_cd,
           B.strm,
           B.fa_msg_action,
           B.sfa_msg_action_to,
           B.edit_process,
           B.comments,
           To_char(SYSDATE, 'YYYY-MM-DD'),
           To_char(SYSDATE, 'YYYY-MM-DD'),
           To_char(SYSDATE, 'YYYY-MM-DD'),
           To_char(SYSDATE, 'YYYY-MM-DD')
    FROM   ps_sfa_run_editmsg A,
           ps_sfa_run_edmsovr B
    WHERE  ( A.oprid = 'M00493730'
             AND B.oprid = 'M00493730'
             AND ( A.oprid = B.oprid
                   AND A.run_cntl_id = B.run_cntl_id
                   AND B.aid_year = :1
                   AND B.strm = :2 ) )
    ORDER  BY 1 

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 7.  RE: Record for Prompts for Queries in Run Control tables

    Posted 05-21-2025 12:20 PM

    Sorry, Scott.  I don't know why SYSDATE is in there 4 times.  I don't even have that as a field I'm displaying.  Comments is my last field.

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 8.  RE: Record for Prompts for Queries in Run Control tables

    Posted 05-22-2025 11:01 AM

    Hi Dana,

    FYI, the SYSDATE is there 4 times because every XLAT used on a field adds a SYSDATE to the select clause of the SQL, but it is not part of that actual output.  It is just an oddity of PS query as far as I can tell.



    ------------------------------
    Ross Nolan
    Data and Reporting Analysis
    University of California, Berkeley
    rvnolan@berkeley.edu
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


  • 9.  RE: Record for Prompts for Queries in Run Control tables

    Posted 05-22-2025 11:04 AM

    Thanks, Ross!  I guess I never noticed that before.  I love this group.  I'm always learning new things!

     




    Original Message
    Alliance 2026 Registration is Open!


Related Content