PS Query & SQL

 View Only

  • 1.  Reporting out Run Control Values for a User

    Posted 17 hours ago

    Good Morning All.  

    I am attempting to create a method to review Run Control values for a person who is now on LOA.  She has been with our institution for over 30 years, she has 100's of RC Values configured, and we are trying to document some of what she has created. 

    I know that there are certain dedicated tables that store RC data (like PSPRCSRUNCNTL), and there are a lot of one-off tables.  I also know that HCM, FIN and CS handle this differently.  

    Does anyone have a document which describes how you approached this problem? (Or approached a similar situation?) I'm not looking for a single all encompassing solution, but it would be helpful to have a plan of attack that has been previously vetted.  

    We have other options to get at this data, plus documentation this person left, but we would prefer to also have a reporting based solution to this issue. 

    thank you!  



    ------------------------------
    Tom Johnson
    Sr Business Systems Analyst
    Duke University
    tom.johnson@duke.edu
    "None of us is as smart as all of us"
    ------------------------------
    Alliance 2026 Registration is Open!


  • 2.  RE: Reporting out Run Control Values for a User

    Posted 17 hours ago
    Hi Tom,

    I think I would try to find all tables that contain both fields, OPRID and RUN_CNTL_ID. From there I would see about creating a large SQL SELECT statement in SQL Developer with a ton of unions and have the criterion just be OPRID = "The person's OPRID" for each record that contains both of those fields.

    It's not sophisticated, but I think it would work! You could pretty easily write the SQL in Excel or by employing help from GPT.

    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!


  • 3.  RE: Reporting out Run Control Values for a User

    Posted 17 hours ago

    Tom, I'd start with this, but you have to save it as a process query to not get the extra security added to it.  Or have a DBA run it for you.

     


    SELECT A.OPRID, A.RUN_CNTL_ID, A.SCCPS_CNTXT_ID, A.BNDNUM, A.FIELDNAME, A.BNDNAME, A.BNDVALUE
      FROM PS_SCCPS_RUN_PARM A
     WHERE A.OPRID = :1
      ORDER BY 2, 5

     

     

    Thanks!

    Dana Pawlowicz

    Business Systems Analyst Sr - ERP

    Business Enterprise Systems and Technologies
    Digital Technology Solutions

    University of Cincinnati

    51 Goodman Dr.

    Cincinnati, Oh 45221

     

     




    Original Message
    Alliance 2026 Registration is Open!


Related Content