Query Manager Tips and Tricks Rahul Nori People

Query Manager: Tips and Tricks

Rahul Nori People. Soft Application Development Supervisor State of Minnesota rahul. nori@state. mn. us

Work Buddy – Betsy!

My Role State of Minnesota • IT Manager for application development in HCM and ELM • Developer/Technical lead on the HCM 9. 2, FMS 9. 2, HUB 9. 1 and People. Tools 8. 57 upgrade projects • Previously developed People. Soft Campus Solutions (CS), HCM, FSCM and ELM applications at Capella University and North Dakota University System Public Sector User Group • Board Member (2018 – 2021) • Track Chair for Public Sector –> Alliance 2020 • Vice President of Tools/Technology –> 2020 – 2021

State of Minnesota & Oracle HCM 9. 2 (PUM 29, PT 8. 54. 20) – PT 8. 57. 04 (upgrade Ongoing) FMS 9. 2 (PUM 30, PT 8. 57. 04) – Dec 2019 ELM 9. 2 (PUM 15, PT 8. 54. 30) – PT 8. 57. 04 (upgrade Ongoing) HUB 9. 1 (PUM 8, PT 8. 57. 04) EPM 9. 1 (PT 8. 54. 30) Oracle Database 12 c Exalogic and Exadata Infrastructure

Overview 1. 2. 3. 4. 5. Introduction A Tab-by-Tab Overview of Query Manager Tips and Tricks Different ways to Query Manage! 2 -tier Vs 3 -tier Vs 4 -tier Appendix: Lessons learned from People. Soft Upgrades

People. Soft Query Basics 1 - Introduction

What can we use People. Soft Query for? • Explore and understand data • Ad hoc reporting from Query Manager • Scheduled Query based reporting • Population selection • Used as Data Sources for: • Communications • BI Reports • Pivot Grids

List of People. Soft Tables HRMS: http: //hrmspeoplesoft. blogspot. com/p/peopletools-tables. html CS: http: //peoplesoft. wikidot. com/campus-solutions-tables FSCM: http: //finance. fiu. edu/controller/Docs/Commonly_Used_Tables. pdf ELM: https: //peoplesofttutorial. com/complete-list-of-peoplesoft-elm-9 -1 tables/ People. Books: http: //docs. oracle. com/cd/E 29376_01/hrcs 90 r 5/eng/psbooks/index. htm Select RECNAME, RECDESCR From PSRECDEFN Where RECTYPE = 0 and RECNAME not like 'PS%' Order by RECNAME;

Component Navigation https: //mypeoplesoftwebpage. com/psp/hcmdev/EMPLOYEE /HRMS/c/QUERY_MANAGER. GBL

Component Navigation SELECT DISTINCT REVERSE(LTRIM(SYS_CONNECT_BY_PATH (REVERSE(PORTAL_LABEL), ' >- ')) "NAVIGATION" FROM PSPRSMDEFN P WHERE PORTAL_OBJNAME = 'PORTAL_ROOT_OBJECT' -- Add your Component Name Here START WITH PORTAL_URI_SEG 2 = 'QUERY_MANAGER' CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME AND PORTAL_NAME = PRIOR PORTAL_NAME;

Component Navigation Root > Enterprise Components > Find Object Navigation

People. Soft – Components Vs Pages

Records in a component -- List of Records under a component SELECT DISTINCT (recname) FROM psrecdefn WHERE recname IN (SELECT DISTINCT (recname) FROM pspnlfield WHERE pnlname IN (SELECT DISTINCT (b. pnlname) FROM pspnlgroup a, pspnlfield b WHERE (a. pnlname = b. pnlname OR a. pnlname =b. subpnlname) AND a. pnlgrpname = 'Component Name’ -- specify your component name) AND recname <> ' ‘) UNION SELECT DISTINCT (recname) FROM pspnlfield WHERE pnlname IN (SELECT DISTINCT (b. subpnlname) FROM pspnlgroup a, pspnlfield b WHERE (a. pnlname = b. pnlname OR a. pnlname = b. subpnlname ) AND a. pnlgrpname = 'Component Name') -- specify your component name) AND recname <> ' ') AND rectype = '0' -- specify record type order by recname asc;

Records and fields on a Page -- List of Records and Fields used on a Page SELECT recname, fieldname FROM pspnlfield WHERE pnlname = 'Page. Name'; -- List of Pages where a Field from a Record is used SELECT pnlname FROM pspnlfield WHERE recname = 'Record. Name' AND fieldname = 'Field. Name'; -- List of Records Where a Field is used SELECT DISTINCT recname, fieldname FROM psrecfield WHERE fieldname = 'Field. Name';

Queries that use a Record/Field

Google Chrome Extensions • Use Plugins to identify records, manage links and features (Free ones exist for Google Chrome) or right-click fields and use inspect element

People. Soft delivered Security Queries -- List of People. Soft Delivered Security Queries Select * From PSQRYDEFN Where QRYNAME Like 'PT_SEC%' Order by QRYNAME; PT_SEC_ACCESSLOG_DAY PT_SEC_ACCESSLOG_USER PT_SEC_APPDES_OBJECTS_USERS PT_SEC_CURUSER_LIST PT_SEC_MENU_PLIST PT_SEC_MENU_USERS PT_SEC_PAGES_PLIST PT_SEC_PLIST_ALIASES PT_SEC_PLIST_APPDES_ACCESS PT_SEC_PLIST_CREF_MENU_CMP_MKT PT_SEC_PLIST_CREF_PORTAL P PT_SEC_PLIST_CR_PRT_MN_CMP_MKT PT_SEC_PLIST_PAGES PT_SEC_PLIST_PTACCESS PT_SEC_PLIST_ROLES PT_SEC_PLIST_SIGNON_TIME PT_SEC_PLIST_USERS PT_SEC_PLIST_WS_OP PT_SEC_PTLS_PLIST PT_SEC_PTLS_USERS PT_SEC_REN_PLIST Access activity by day User ID's Access Activity PLists with the App Des object Users with the App Des object List of Active Users Perm Lists with this Menu User IDs with access to menu Perm Lists with access to page Permission List Aliases Perm List's App Designer Acces Permission List CRef Access PList CRef Menu Cmp Mkt Access List CRef Portal Access PList CR Prt Mn Cmp Mkt Access Pages the Perm List can Access Perm List's People. Tools Access Roles assigned to Perm List Signon Times Users assigned to Perm List Web Service in Permission List Perm Lists with the PT Tool Users with the PT Tool REN Security by Perm List PT_SEC_REN_USER PT_SEC_ROLE_ALIASES PT_SEC_ROLE_CLASS PT_SEC_ROLE_CREF_MENU_CMP_MKT PT_SEC_ROLE_CREF_PORTAL Role PT_SEC_ROLE_CRF_PRT_MN_CMP_MKT PT_SEC_ROLE_PAGES PT_SEC_ROLE_SIGNONTIME PT_SEC_ROLE_USERS PT_SEC_ROLE_WS_OP PT_SEC_USER_APPDES_ACCESS PT_SEC_USER_CREF_MENU_CMP_MKT PT_SEC_USER_CREF_PORTAL PT_SEC_USER_CRF_PRT_MN_CMP_MKT PT_SEC_USER_PAGE PT_SEC_USER_PERM_LIST PT_SEC_USER_PTACCESS PT_SEC_USER_ROLE_PLIST_PAGE PT_SEC_USER_SIGNON_TIME PT_SEC_USER_WS_OP User REN Access Role Aliases Classes assigned to role. Role Content Reference Access Role CRef Menu Cmp Mkt Access CRef Portal Access Role Crf Prt Mn Cmp Mkt Access Pages that a Role has Access Role Signon Times Users assigned to Role - Web Service Operations User ID App. Des. Access User ID CRef Access User CRef Menu Cmp Mkt Access User ID CRef Portal Access User Crf Prt Mn Cmp Mkt Access User ID page access Users and Permission Lists User ID People. Tools Access User's Roles User ID, Role, P List, Pgs User ID Signon Times User ID-Web Service Operation

A Tab-by-Tab Explanation of Query Manager 2 – PS Query tab-bytab Root -> Reporting Tools -> Query Manager

Records Tab • Use the Search feature to find your records (tables) • Bring in tables one at a time. Know your criteria, and then join • Always go with the most basic tables you can find • We suggest avoiding views – Unless you know what are in them

Records Tab • Click on Show fields to get to this page – examine what is in it • Click on Add record to create a query with that record. • Search and add more to create joins • Keys are marked with a Y in front – Example has a composite key with 6 fields

Query Tab • You can define criteria here using the filter icon, or on the criteria page • Add fields to your query by selecting the checkboxes. Don’t be too eager to select too many fields at first, add them gradually to see how they will impact your query • Always check to make sure your table makes sense before joining it in

Query Feeds • The Feed Publishing Framework provides user interfaces and application programming interfaces (APIs) for RSS feed definition creation and maintenance, feed searching and subscription, as well as feed document generation and delivery. • Two levels of security: • Feed security • Data security • Use the Fields page to publish a query as a feed

Expressions Tab • A means of defining your SQL statement – usually used in Select & Where statements • For now, we will only define expressions on the criteria page • Anything that can be used in an Oracle database can be used in an expression • The internet is a great resource to find expressions

Prompts Tab • Prompts are also known as bind variables • Prompts are completely user configurable – you can decide the length, the format, the edit type*, and specify a default value

Prompts Tab

Fields Tab • You can use the field tab to define the display information for your fields. Based on the tables you choose, these will be defined for you – however (as with everything else) they are configurable • You can re-order and sort here as well

Fields Tab • You can also change the field header and name • Keep an eye out for translate values that can display several different sets of values based on your choice

Criteria Tab • Add, edit, or group criteria • Edit auto join criteria

Criteria Tab Choose your Operand (and, or, >, <, >=, <=, between, in list, like, equal to, not in list, not like)

Criteria Cont’d – Sub Query

Joining tables Inner Join (Standard Join) Full Outer Join Left Outer Join (Exclude B with Where) Left Outer Join Full Outer Join (Exclude B with Where)

Standard Join • You can do standard joins to as many tables as you want • PS Query will suggest Auto Join Criteria based on field name

Left Outer Join • To outer join, change each of your join criteria to [fieldname] • Adds as a ‘belongs to’

Left Outer Join • When adding criteria for Left Outer Joins, make sure you set the criteria as ON clause of outer join • Otherwise, the criteria will cause your join to act as a standard join

Having Tab • Only used with aggregate field functions • If you have an aggregate field that you want to use in criteria, it must be in a having criteria

Dependency Tab • Related to Composite Query • Specify whether a record can be pruned in Composite Query • Specify details of a record or field, depending on other records or fields for pruning

Transformations • Allows a user-defined output format called XFORM • XFORM triggers a transformation (XSLT) • Provide the XSLT to define the desired transformation of the extracted data • Pick an output format: 001, ABA, AET, APC, CSS, CSV, D 01, DAT, DTD, DTL, ERR, GIF, GIR, HTML, IDX, JCD, JS, LIS, LOG, MDL, MT 9, N 01, NVT, NXT, OUT, PS, SQL, STDOUT, SYS, TMP, TRC, TRN, TXT, XLSM, XLSX, XML, XSD, ZIP

Transformations • Can use multiple transformations in the same query • Preview Results in HTML • To generate the transformed output – Schedule the query and select XFORM as the output format.

View SQL Tab • Check your work!! • Always come back to this tab to see how the criteria you add factor into the SQL statement. • Can’t modify SQL here • If you can read it here, you can write it in SQL developer • You can select the ‘XLAT/Related Language’ box to display full SQL, leave unchecked to display SQL without the XLAT and Related Language joins

Run Tab • Once you run the query, you can download the output to excel • Rerun your query to enter new bind variables (prompts values)

Save Your work

Properties In the Properties area, you can select ‘Distinct’ and ‘Security Join Optimizer’ if needed.

Properties • Checking Security Join Optimizer can help optimize some queries by using the same query security record for the first join and then all subsequent joins, rather than using a different security record for each join • Can be very useful if you have a slow running query with many joins • Not recommended for left outer joins

Query Properties

Some Tips for Using Query Manager 3 – Tips and Tricks

Expressions – Manipulating Strings • NVL(A. FIRST_NAME, ‘ ’) • Returns First name if available, if not returns the string ‘ ’ • NVL 2(A. BALANCE_AMOUNT, ‘Paid’, ‘Balance Due’) • Display only the last four of SSN: ‘XXX-XX-’ + SUBSTR(A. SSN, 6) = ‘XXX-XX 1234’ • New People. Tools 8. 58 Feature – Query Masking using People. Soft Data Privacy Framework https: //www. techonthenet. com

Expressions – Formatting • TO_CHAR(sysdate, 'yyyy/mm/dd') • Result: '2003/07/09' • TO_CHAR(sysdate, 'Month DD, YYYY') • Result: 'July 09, 2003’ • TO_CHAR(to_char(‘ 1000’, '$9, 999. 99’ ) • Result: $1, 000. 00 • TO_DATE('2003/07/09', 'yyyy/mm/dd’) • TO_DATE('2015/05/15 8: 30: 25', 'YYYY/MM/DD HH: MI: SS') • Result: 09 -JUL-03 • When comparing Date values with datetime values use TRUNC(‘Date. Value’) • date_occured >= TRUNC(SYSDATE - 30)

Expressions – Date Operations

Expressions - Decode DECODE translates a set of values to another set of values. Provides the ability to assign a default value. DECODE(A. Day, ‘M’, ‘Monday’, ‘Tuesday’, ‘Wednesday’, ‘Thursday’, ‘Friday’, ‘Holiday’)

Expressions – Setting up Criteria in Bulk! • Need to setup criteria with 100 – 1000 EMPLIDs in the Where clause • Data is provided in an Excel Spreadsheet or from a SQL Developer Query • How do we transition that into Query Manager Criteria? • Excel and SQL Developer Demo

Expressions – Setting up Criteria in Bulk!

Expressions - Meta-SQL • Meta-SQL can also be used in expressions. Meta-SQL can be used to retrieve current date, current date time, operator. ID, to get the difference between dates, etc. • Examples: • %Operator. ID • %Current. Date. Time. In • %Current. Date. In • %Date. Diff • See Meta-SQL for more helpful information

Expressions - Regular expressions • Regular expressions are used to identify a pattern within a field, and perform an action on the expression when used in criteria or as a field. • • REGEXP_LIKE (Field, ‘search pattern'); REGEXP_REPLACE(Field, ‘pattern’, ‘value to replace’, position, occurrence, options) REGEXP_INSTR(Field, ‘pattern’, position, occurrence, begin_end, options) REGEXP_SUBSTR(Field, ‘pattern’, start_position, occurrence, ‘match_parameter’, ‘subexpr’) Oracle Database Regular Expressions

Expression - Listagg LISTAGG(A. ACTION, ', ') WITHIN GROUP (ORDER BY A. EMPLID, A. EFFDT Desc, A. EFFSEQ Desc)

Distinct and ‘CLOB’ errors If you are using an Oracle database, checking ‘distinct’ or using aggregate functions may cause the following error if you are trying to display a long description, or other field that can contain a large number of characters:

Distinct and ‘CLOB’ errors Solution: • You can either uncheck ‘distinct’. If you need to have your result be distinct, you can use a substring expression to take a substring of the field. • DBMS_LOB. SUBSTR(TRIM(YOUR_FIELD), 500, 1) • TO_CHAR(YOUR_FIELD) https: //blogs. bgsu. edu/geeks/2015/10/09/query-get-clobed/

Drilling URLs • Drilling URLs are used when you’d like to provide a functioning URL as a column in your query. • They are another type of expression.

Drilling URLs To create a drilling URL in your query, add an expression, with an expression type of Drilling URL, and select your URL type:

Drilling URLS To link to a component in People. Soft, choose component URL, and fill out the component information that you’d like to link to, and the action (update, etc). (you can use Cntrl +J to find component and page info)

Drilling URLS URL search keys pre-populate the search area where you would typically enter data, with the data from your query You must select field and setup columns to be able to display your URL as a column in your query

Drilling URLS Once you click ‘Ok’ you will see the drilling URL code in your expression text.

Drilling URLS In your results, you will now see the column that you selected, and it will now be a URL based on the settings that you chose!

4 – Different ways to Manage Queries • Direct DB - SQL Developer • 2 -tier • 3 -tier • 4 -tier (PIA)

What are they?

What are they? 2 -Tier 3 -Tier 4 -Tier

How to use 2 -tier and 3 -tier access?

How to use 2 -tier and 3 -tier access?

5 – Query Administration and Performance

Query Administration • Query Administration provides users with the ability to manage, monitor and mitigate queries. • The feature is controlled by People. Soft security and is not part of the Report Manager menu structure. • Navigation: • People. Tools > Utilities > Administration > Query Administration • Some of the functions provided by this functionality include: • Assign a private query to other users • Manage queries designed by blocked or terminated users. • Rename or Delete a query • Move a query to a folder • Turn query logging on/off from runs • Monitor execution statistics for queries • Set timeout for query runs • Kill queries that are hung in running state.

Query Administration

Query Administration On Admin Tab

Query Administration On Executing Tab

Query Administration On Settings Tab (Query Column Mask – 8. 58)

Performance Optimization Use Database Hints As Expressions • Optimizer hints can be used with SQL statements to alter execution plans. Make decisions to override the optimizer • Example: /*+ BIND_AWARE */ • Oracle 12 c – Influencing the Optimizer: https: //docs. oracle. com/database/121/TGSQL/tgsql_influence. htm#TGSQL 246 • Work with your DBA

Query Optimization – MOS Resources People. Soft Query Performance Guidelines white paper by Catalin Marinescu - Doc ID 2284809. 1 Oracle MOS Documents: E-QR: Performance Issues With Date Fields in Query Excel Output With People. Tools 8. 55 (Doc ID 2269922. 1) E-QR: Slow Navigation Between Query Manager Tabs (Doc ID 2308062. 1) E-QR: No Results In Query Administration (Doc ID 2319434. 1) E-QR: How Does Left Outer Join Logic Work With Security Tables? (Doc ID 1362716. 1) E-QR: How To Update The Query Security Join Optimizer Automatically? (Doc ID 1362653. 1) E-QR: Performance Issues with Queries containing prompts taking much longer to run than Queries without prompts (Doc ID 621370. 1) https: //support. oracle. com/epmos/faces/Document. Display? id=1395740. 2

Setting Trace for PS Query Set the trace right before the affected query is run. • Navigate to PS_CFG_HOMEappserv<Database Name> and open the psappsrv. cfg file. • Locate the Trace. Sql= value and set it to 31. • Save the file. • Note: If Allow Dynamic Changes=Y, then there is no need to restart the Application Server. If Allow Dynamic Changes=N, then the Application Server will need to be restarted. • Navigate to Query Manager or Viewer and submit the affected query to run. • Once complete, return to the Application Server config file and set the Trace. Sql= value to 0. • Retrieve the *. tracesql file in the <ps_cfg_home>/appserv/<domain>/LOGS/ directory. https: //support. oracle. com/epmos/faces/Document. Display? id=2209232. 1

5 – Lessons learned from People. Soft Upgrades

Issues After Upgrades E-QR: Downloading / Running PS Queries To Excel Adds A Time Component To The Date Field (Doc ID 2140351. 1) E-QR: After Upgrading To 8. 55. 19, Excel Query Output Displays Dates As 1/0/1900 (Doc ID 2354528. 1) Solutions: • Upgrade to People. Tools 8. 56. 02 • Set Timezones for DST to correct query excel output • Create an Expression that treats this output field as a CHAR(10) • INSERT INTO PSVERSION (OBJECTTYPENAME, VERSION) VALUES ('QRYBIFF', 1)

Issues After Upgrades E-PIA: Query Mgr/Query Viewer "Run To Excel" Option Disabled After Upgrading To People. Tools 8. 54 (Doc ID 2143342. 1) Solutions: • Upgrade to People. Tools 8. 54. 22 • Code Fix in QRY_VIEWER_WRK. HTML_FUNCS People. Code • &b. Disable. Excel. XML = False; • Post 8. 55 Upgrade – Clear Cache at Browser and App Server Level

Issues After Upgrades Queries that use a translate value pull no results Solutions: • View the field display on the query and make sure the translate value effective date setting is correct. Usually Current Date is the correct setting, others are more advanced, and need to make sense with the query.

Issues After Upgrades E-QR: When Deleting A Public And A Private Query Together, User Kicked Out Of PIA (Doc ID 2368845. 1) E-QR: PS Query Giving "ORA-00979: Not A GROUP BY Expression" Error When Using Aggregate Function and (+) Join Operator(Doc ID 2118085. 1) E-QR: PS Query With Group Criteria Throws The Error "Illegal usage of parenthesis in criteria!" After People. Tools 8. 55 Upgrade (Doc ID 2278442. 1) E-QR: Composite Query Fails With ORA-01427 When Standard Pruning Is Selected. (Doc ID 2368846. 1) – Solved in 8. 55. 24 E-QRY: Layout Issues In Delivered Query Manager Page. (Doc ID 2325679. 1) E-QR: Performance Issues With Date Fields in Query Excel Output With People. Tools 8. 55 (Doc ID 2269922. 1) – Solved in PT 8. 56

QUESTIONS? Or send email to rahul. nori@state. mn. us

Thank you! This presentation and all Alliance Virtual 2020 presentations will be available for download from the Conference site at www. alliance-conference. com