Tuesday, July 31, 2007

PeopleSoft Hot keys

We all probably know that “CTRL J” hot key gives us the system information. But did you know that there are a lot more available. But you do not have to trouble your brain to remember them, simply press navigate to a search or transaction page and invoke hot key “CTRL K” to get all the information. Below are the results from PeopleSoftCTRL K“.
Accessing your application using the keyboard
Keyboard navigation is controlled by Hot keys and Access keys .
List of Hot Keys
Alt 1– Executes different buttons depending on the page type
> Save button on the Toolbar in a page
> OK button on a secondary page
> Search or Add button on a Search or Lookup page
Alt 2 — Return to Search
Alt 3 — Next in List
Alt 4 — Previous in List
Alt 5 — Valid Lookup Values
Alt 6 — Related Links
Alt 7 — Insert Row in grid or scroll area
Alt 8 — Delete Row in grid or scroll area
Alt 0 — Refreshes the page by invoking the Refresh button on the Toolbar
Alt . — Next set of rows in grid or scroll area [e.g., Alt period]
Alt , — Previous set of rows in grid or scroll area [e.g., Alt comma]
Alt /– Find in grid or scroll area [e.g., Alt forward slash]
Alt ‘ — View All in grid or scroll area [e.g., Alt prime]
Alt — Toggle between Add and Update on the Search page [e.g., Alt backslash]
Ctrl J– System Information
Ctrl K> — Keyboard Information
Ctrl Y>– Toggle menu between collapse and expand.
Ctrl Tab> — Toggles focus through the frame set
Enter >– Invokes the following buttons where present: OK, Search, Lookup
Esc >– Cancel
List of Access Keys
Alt 9> — Takes you to the Help line
Alt >– Takes you to the Toolbar [e.g., Alt backslash Enter]
Ctrl Z> — Takes you to the Search box of the Menu
Menu Access KeysThe Ctrl Z combination will focus your cursor onto the menuing system. From there, you can use your tab key (or shift-tab to reverse direction) to navigate through the menu hierarchy.
About Access keys and Hot keys
>   An Access Key is an Alt key combination that moves focus to a specified field on the current page.
For example, Alt moves focus to first button on the Toolbar. Then pressing the Enter key would invoke that action. Or, you may use the Tab key to move you to the next Toolbar button.
>   A Hot Key performs an immediate action. For example, when focus is in a field that has lookup processing, Alt 5 invokes the Lookup page without having to press the Enter key.
Read More about  Hot keys

Thursday, July 26, 2007

How to make a PeopleSoft PRIVATE query PUBLIC?

Sometime back I had a ticket asking me to make a PRIVATE query PUBLIC. The query was created by a superuser who had quit the company and whose OPRID was deleted. Here are the SQL’s you need to execute if you need to make a PRIVATE query PUBLIC using a data fix. The other straightforward alternative is to log on as the owner of the PRIVATE query and save as PUBLIC (which is not applicable in this case).
Ps_query_private_to_public1
 
UPDATE PSQRYBIND SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;
UPDATE PSQRYBINDLANG SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;
UPDATE PSQRYCRITERIA SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;
UPDATE PSQRYDEFN SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;
UPDATE PSQRYDEFNLANG SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;
UPDATE PSQRYEXPR SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;
UPDATE PSQRYFIELD SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;
UPDATE PSQRYFIELDLANG SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;
UPDATE PSQRYRECORD SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;
UPDATE PSQRYSELECT SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;
Ps_query_private_to_public
Note:
Understanding how PeopleSoft differentiates PRIVATE versus PUBLIC query should help you resolve various similar requests like a few mentioned below.
- Create a PUBLIC query clone of the PRIVATE query
- Rename a PRIVATE or PUBLIC query
- Delete a query
Read More about  PeopleSoft

Monday, July 23, 2007

PeopleSoft Process Monitor Run Status


PeopleSoft Process Monitor Run Status
Ever had trouble identifying the runstatus in PSPRCSRQST table with what it meant. Run the following SQL to determine what it means.
SELECT C.FIELDVALUE, C.XLATLONGNAME, C.XLATSHORTNAME,C.FIELDNAME,TO_CHAR(C.EFFDT,’YYYY-MM-DD’)
FROM PSXLATITEM C
WHERE C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PSXLATITEM C_ED
WHERE C.FIELDNAME = C_ED.FIELDNAME
AND C.FIELDVALUE = C_ED.FIELDVALUE
AND C_ED.EFFDT <= SYSDATE)
AND C.FIELDNAME = ‘RUNSTATUS’
AND C.EFF_STATUS = ‘A’
Process_mon_run_status

you can read it More about PeopleSoft

Tuesday, July 17, 2007

LifeSaver of the Week – 4


UNIX command for the PS DBA – find
In several of my previous projects, we used to get 4-5 tickets per week because of space related issues. Finally we implemented automated scripts to take care of this issue. The PS_HOME and psreports are the two main directories which have to be monitored for space growth. Below I explain the relevant examples of the UNIX command “find” that you can use to build your shell script. of Peoplesof
find $PS_HOME -name ‘*.AET’
The above command will recursively find all files with .AET extension residing in the PS_HOME directory.
find $PS_HOME -name ‘*.AET’ -mtime +1
The above command will recursively find all files with .AET extension and older than 1day in the PS_HOME directory.
find $PS_HOME -name ‘*.AET’ -mtime +1 -exec gzip {} ;
The above command will recursively find all files with .AET extension, older than 1day and will gzip the file in the PS_HOME directory.
find $PS_HOME -name ‘*.AET’ -mtime +1 -exec rm {} ;
The above command will recursively find all files with .AET extension, older than 1day and will remove the file in the PS_HOME directory.
find $PS_HOME -name ‘*.AET’ -size +10000000c -exec gzip {} ;
The above command will recursively find all files with .AET extension, greater than 10MB in sizeand will gzip the file in the PS_HOME directory.
You can replace AET with trc or out or log as appropriate
To add more variety to your thoughts on Operational Data, you can read it More about Peoplesoft 

Friday, July 13, 2007

Data Integration Challenge – Capturing Changes


When we receive the data from source systems, the data file will not carry a flag indicating whether the record provided is new or has it changed. We would need to build process to determine the changes and then push them to the target table.

There are two steps to it
  1. Pull the incremental data from the source file or table

  2. Process the pulled incremental data and determine the impact of it on the target table as Insert or Update or Delete

Step 1: Pull the incremental data from the source file or table
If source system has audit columns like date then we can find the new records else we will not be able to find the new records and have to consider the complete data
For source system’s file or table that has audit columns, we would follow the below steps
  1. While reading the source records for a day (session), find the maximum value of date(audit filed) and store in a persistent variable or a temporary table
  2. Use this persistent variable value as a filter in the next day to pull the incremental data from the source table

Step 2: Determine the impact of the record on target table as Insert/Update/ Delete 
Following are the scenarios that we would face and the suggested approach
  1. Data file has only incremental data from Step 1 or the source itself provide only incremental data

    • do a lookup on the target table and determine whether it’s a new record or an existing record
    • if an existing record then compare the required fields to determine whether it’s an updated record
    • have a process to find the aged records in the target table and do a clean up for ‘deletes’

  2. Data file has full complete data because no audit columns are present

    • The data is of higher

      • have a back up of the previously received file
      • perform a comparison of the current file and prior file; create a ‘change file’ by determining the inserts, updates and deletes. Ensure both the ‘current’ and ‘prior’ file are sorted by key fields
      • have a process that reads the ‘change file’ and loads the data into the target table
      • based on the ‘change file’ volume, we could decide whether to do a ‘truncate & load’
    • The data is of lower volume

      • do a lookup on the target table and determine whether it’s a new record or an existing record
      • if an existing record then compare the required fields to determine whether it’s an updated record
      • have a process to find the aged records in the target table and do a clean up or delete


Thursday, July 12, 2007

LifeSaver of the Week -3

Trace SQR
The most common approach to tracing SQR is to use the–DEBUG or –S flag. In this post, I will share the techniques I use to generate the SQL Trace that I can use to troubleshoot and determine the bad SQL.
If I need to trace a SQR in a development environment, I choose to modify the SQR and include the following procedure which will be called at the start of the program.
begin-procedure SetSQLTrace ! Set SQL Trace
begin-SQL
ALTER SESSION SET SQL_TRACE = TRUE;
end-SQL
end-procedure
However, in a production or any other environment which is under change control, I need to co-ordinate with the functional analyst to execute the SQR. Here are the steps I follow.
1.Determine the session id as soon as the SQR program starts processing.
Tip – Use the CLIENT_INFO and PROGRAM in V$SESSION to determine the user session.
2.Execute below SQL’s.
exec dbms_system.set_bool_param_in_session(sid, serial#, ‘TIMED_STATISTICS’, TRUE);
exec dbms_system.set_int_param_in_session(sid, serial#, ‘MAX_DUMP_FILE_SIZE’, 2147483647);
– Turn on trace
exec dbms_system.set_ev(sid, serial#, 10046, 8, ”)
– or use below
exec dbms_system.set_sql_trace_in_session(sid,serial#,Y)
– Turn off trace
exec dbms_system.set_ev(sid, serial#, 10046, 0, ”)
– Run TKPROF on trace file
I have found the above approaches very useful to identify the problem SQL whenever I receive a ticket from the user complaining about a long running SQR report or process.

Friday, July 6, 2007

Business Intelligence Utopia – Dream to Reality: Key Enablers


In the last post, I discussed my view of BI Utopia in which information is available to all stakeholders at the right time, in the right format enabling them to make actionable decisions at both strategic & operational levels. Having said that, the BI street is not paved with gold.

I consider the following key enablers as pre-requisites to achieve true ‘Information Democracy’ in an enterprise. The “Power of Ten” includes:
  1. Proliferation of agile, modular & robust transaction processing systems.

  2. Real Time Data Integration Components

  3. Strong Data Governance structure

  4. Service Oriented Architecture

  5. Extensible Business centric Data Models

  6. Flexible business rules repositories surrounded by clean metadata/reference data environments

  7. Ability to integrate unstructured information into the BI architectural landscape

  8. Guided context-sensitive, user-oriented analytics

  9. Analytical models powered by Simulations

  10. Closed loop Business Intelligence Utopia

Each of these units comprising the “Power of Ten” is a fascinating topic on its own. We will drill-down and focus on some of the salient features of each of these areas in the coming weeks.