SQL to retrieve the difference between DATES

Method 1: SELECT t1_id, EXTRACT(Day FROM(mod_date_time – create_date_time) DAY TO SECOND) as Day, EXTRACT(HOUR FROM(mod_date_time – create_date_time) DAY TO SECOND) as Hour, EXTRACT(Minute FROM(mod_date_time – create_date_time) DAY TO SECOND) as Minute, EXTRACT(SECOND FROM(mod_date_time – create_date_time) DAY TO SECOND) as second FROM t1; Method 2: SELECT floor((date1-date2)*24) || ' HOURS ' || mod(floor((date1-date2)*24*60),60) || ' MINUTES ' || mod(floor((date1-date2)*24*60*60),60) || ' SECS ' time_difference FROM dates;

Summary of requested processes by process status

The following query will give you a summary of the requested processes by process status. SELECT RQST.RUNSTATUS, RQST.PRCSTYPE, ( SELECT XLAT.XLATLONGNAME FROM PSXLATITEM XLAT WHERE XLAT.EFFDT = ( SELECT MAX(XLAT_ED.EFFDT) FROM PSXLATITEM XLAT_ED WHERE XLAT_ED.FIELDNAME = XLAT.FIELDNAME AND XLAT_ED.FIELDVALUE = XLAT.FIELDVALUE ) AND XLAT.FIELDNAME = 'RUNSTATUS' AND XLAT.FIELDVALUE = RQST.RUNSTATUS ) AS RUNSTATUS_XLAT, COUNT(RQST.PRCSINSTANCE) AS TOTAL_PROCESSES, MIN(RUNDTTM) AS FIRST_OCCURRED, MAX(RUNDTTM) AS LAST_OCCURRED FROM PSPRCSRQST RQST GROUP BY RQST.RUNSTATUS, RQST.PRCSTYPE ORDER BY RUNSTATUS_XLAT, RQST.PRCSTYPE

Common Process failures

The App Engine program has run to No Cuccess and we dont see any error in the log. But as per the trace file that is being generated we can see that the following errors have occurred: 28 01.00.04 340.792628 CMgrUpdateTransaction::OnPreCommit : trying to commit a failed transaction 29 01.00.04 0.000823 CMgrUpdateTransaction::OnPreCommit : trying to commit a failed transaction 30 01.00.04 0.008131 CMgrUpdateTransaction::OnAbort 31 01.00.04 0.000033 CMgrUpdateTransaction::OnAbort 32 01.00.04 0.000738 CMgrUpdateTransaction::OnPreCommit : trying to commit a failed transaction Cause: The cause of this issue is corruption of the Process Scheduler cache on DB Platform. This is sporadic failure, some App Engine programs will fail and some of them will run successfully. Steps to be taken: Try restarting/ rerunning the processes after deleting the failed instances. If the problem persists, Bounce the Process scheduler server and Clear Cache. SOLUTION INSTRUCTIONS --------------- Implement the following instruction steps: 1. On Process Scheduler Windows box, run psadmin to stop the Process Scheduler. 2. Navigate to PS_HOME\appserv\prcs\DBNAME directory. 3. Delete the CACHE directory. 4. Retest the issue. 5. Migrate the solution as appropriate to other environments.

Query to find the Special Characters in PS_NAMES table

SELECT N.EMPLID,N.NAME_TYPE,N.EFFDT,N.NAME,TRANSLATE(UPPER(N.NAME) ,'`/()-,.ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"''',' ') AS "SPECIAL" FROM PS_NAMES N WHERE N.EFFDT=(SELECT MAX(N1.EFFDT) FROM PS_NAMES N1 WHERE N1.EMPLID=N.EMPLID AND N1.NAME_TYPE='PRI' AND N1.EFFDT<=SYSDATE) AND N.NAME_TYPE='PRI' AND RTRIM(LTRIM((TRANSLATE(UPPER(N.NAME),'`/()-,.ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"''',' '))))<>' ' ORDER BY N.EMPLID

PeopleSoft Security related Queries (Permission List, Roles etc)

Component Permission List Query: This query identifies the permission lists and its description associated with component. SELECT MENU.MENUNAME, COMPDFN.PNLGRPNAME, AUTH.CLASSID PERMISSION_LIST, CLASS.CLASSDEFNDESC PERMISSION_DESC FROM PSAUTHITEM AUTH, PSMENUDEFN MENU, PSMENUITEM MENUITM, PSPNLGROUP COMP, PSPNLGRPDEFN COMPDFN, PSCLASSDEFN CLASS WHERE MENU.MENUNAME = MENUITM.MENUNAME AND MENUITM.PNLGRPNAME = COMP.PNLGRPNAME AND COMPDFN.PNLGRPNAME = COMP.PNLGRPNAME AND COMPDFN.PNLGRPNAME LIKE UPPER (:COMPONENT_NAME) AND AUTH.MENUNAME = MENU.MENUNAME AND AUTH.BARNAME = MENUITM.BARNAME AND AUTH.BARITEMNAME = MENUITM.ITEMNAME AND AUTH.PNLITEMNAME = COMP.ITEMNAME AND AUTH.CLASSID = CLASS.CLASSID GROUP BY MENU.MENUNAME, COMPDFN.PNLGRPNAME, AUTH.CLASSID, CLASS.CLASSDEFNDESC ORDER BY MENU.MENUNAME, COMPDFN.PNLGRPNAME, PERMISSION_LIST; Content Reference accessed by a permission list: This query identifies Content references accessed by Permission List. SELECT a.portal_label AS PORTAL_LINK_NAME, a.portal_objname, a.portal_name, a.portal_reftype FROM psprsmdefn a, psprsmperm b, psclassdefn c WHERE a.portal_reftype = 'C' AND a.portal_cref_usgt = 'TARG' AND a.portal_name = b.portal_name AND a.portal_reftype = b.portal_reftype AND a.portal_objname = b.portal_objname AND c.classid = b.portal_permname AND a.portal_uri_seg1 <> ' ' AND a.portal_uri_seg2 <> ' ' AND a.portal_uri_seg3 <> ' ' AND c.classid = :permissionlist AND a.portal_name = :portalname ORDER BY portal_label; Page Access By Permission List: SELECT B.MENUNAME, B.BARNAME, B.BARITEMNAME, B.PNLITEMNAME AS PAGENAME, C.PAGEACCESSDESCR, DECODE (B.DISPLAYONLY, 0, 'NO', 1, 'YES') AS DISPLAYONLY FROM PSCLASSDEFN A, PSAUTHITEM B, PSPGEACCESSDESC C WHERE A.CLASSID = B.CLASSID AND A.CLASSID = :1 AND B.BARITEMNAME > ' ' AND B.AUTHORIZEDACTIONS = C.AUTHORIZEDACTIONS; PeopleTools Accessed By a Permission List: SELECT DISTINCT B.MENUNAME FROM PSCLASSDEFN A, PSAUTHITEM B WHERE A.CLASSID = B.CLASSID AND ( B.MENUNAME = 'CLIENTPROCESS' OR B.MENUNAME = 'DATA_MOVER' OR B.MENUNAME = 'IMPORT_MANAGER' OR B.MENUNAME = 'APPLICATION_DESIGNER' OR B.MENUNAME = 'OBJECT_SECURITY' OR B.MENUNAME = 'QUERY' ) AND A.CLASSID = :PERMISSIONLIST; Roles Assigned to a Permission List: SELECT B.ROLENAME, B.CLASSID AS PERMISSION_LIST FROM PSCLASSDEFN A, PSROLECLASS B WHERE A.CLASSID = B.CLASSID AND A.CLASSID = :PERMISSIONLIST; User IDs assigned to a Permission List: SELECT C.ROLEUSER AS USER_IDS FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C WHERE A.CLASSID = B.CLASSID AND B.ROLENAME = C.ROLENAME AND A.CLASSID = :PERMISSIONLIST GROUP BY C.ROLEUSER;

PORTAL_CSS process error out

PeopleTools 8.49.27 - Application Engine Server Copyright (c) 1988-2013 PeopleSoft, Inc. All Rights Reserved PSAESRV started service request at 23.37.09 2013-03-25 Not authorized CRef: HC_AWARD_SUMMARY_GBL (95,5032) Not authorized CRef: EO_EO9030R_GBL (95,5032) Not authorized CRef: HC_AV_P_GPROFILE_GBL3 (95,5032) Not authorized CRef: HC_AV_VOL_HIST_GBL2 (95,5032) Not authorized CRef: EOEI_INCR_LOAD_GBL (95,5032) Not authorized CRef: EOEC_MP_ROLE_GBL (95,5032) Not authorized CRef: HC_SS_CC_TODOS_GBL (95,5032) Not authorized CRef: HC_FPMCONVTABL_PNL_GBL5 (95,5032) Not authorized CRef: HC_AV_INVLV_SMRY_E_GBL1 (95,5032) Not authorized CRef: HC_SSR_SSENRL_EXAM_L_GBL3 (95,5032) Not authorized CRef: HC_LN_EDIT_DFLTS_GBL1 (95,5032) Not authorized CRef: HC_RUNCTL_DISBURSE_GBL (95,5032) Not authorized CRef: HC_FA_SS_BATCH_INQ_GBL (95,5032) Not authorized CRef: HC_GPIN_RC_ITCS_GBL (95,5032) Not authorized CRef: HC_GPIN_YE_F6_GBL (95,5032) Not authorized CRef: HC_GPMX_INF_BR_GBL (95,5032) Not authorized CRef: HC_GPMX_MAINT_SUB_EE_GBL (95,5032) Not authorized CRef: HCSS_TRCR_RPT_GBL (95,5032) Not authorized CRef: HC_SFA_ASG_ELIG_GBL (95,5032) Not authorized CRef: HC_SFA_ASG_HS_PROGRAM_GBL (95,5032) Not authorized CRef: HC_SFA_ASG_MRR_EC_GBL (95,5032) Not authorized CRef: HC_GPAR_LAYOUT_GBL (95,5032) Not authorized CRef: HC_HONORS_AWARDS_GBL4 (95,5032) Not authorized CRef: HC_AV_GCLUB_PRG_GBL2 (95,5032) Not authorized CRef: HC_INAS_IM_SMRY_05_GBL (95,5032) Not authorized CRef: HC_SCC_SM_SEARCH_GBL14 (95,5032) Not Authorized. (96,1) Not Authorized. (96,1) First operand of . is NULL, so cannot access member ItemByName. (180,236) PORTAL_CSS.CREFPERM.GBL.default.1900-01-01.Step01.OnExecute Name:CheckParentFolder PCPC:2339 Statement:40 Called from:PORTAL_CSS.CREFPERM.GBL.default.1900-01-01.Step01.OnExecute Statement:202 Process 209291 ABENDED at Step PORTAL_CSS.CREFPERM.Step01 (PeopleCode) -- RC = 24 (108,524) Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s PSAESRV completed service request at 23.37.21 2013-03-25 Solution: As per oracle support,the below is the information provided: please provide roles to the user and run the portal security sync process. To run portal security sync, you need access to every single folder and content reference in the portal. The two specially delivered roles that will give you this is: PeopleSoft Administrator Portal Administrator It is seen that if you don't have a role that gives you access to the entire portal structure then, the portal security sync application engine program (PORTAL_CSS) will run to No Success (abend) with a message like this in the standard output (.stdout) file: First operand of . is NULL, so cannot access member ItemByName. (180,236) PORTAL_CSS.CREFPERM.GBL.default.1900-01-01.Step01.OnExecute Name:CheckParentFolder PCPC:2339 Statement:40 Called from:PORTAL_CSS.CREFPERM.GBL.default.1900-01-01.Step01.OnExecute Statement:202

SQL for finding the navigation of a Component

Query 1: SELECT LEVEL0.PORTAL_LABEL || ' > ' || LEVEL1.PORTAL_LABEL || ' > ' || LEVEL2.PORTAL_LABEL || ' > ' || level3.PORTAL_LABEL PATH_TO_COMPONENT FROM PSPRSMDEFN level3 , PSPRSMDEFN level2 , PSPRSMDEFN level1 , PSPRSMDEFN LEVEL0 WHERE level3.PORTAL_URI_SEG2 = 'Component Name here' AND level3.PORTAL_PRNTOBJNAME = level2.PORTAL_OBJNAME AND level2.PORTAL_PRNTOBJNAME = level1.PORTAL_OBJNAME AND level1.PORTAL_PRNTOBJNAME = LEVEL0.PORTAL_OBJNAME AND level3.PORTAL_NAME = level2.PORTAL_NAME AND level2.PORTAL_NAME = level1.PORTAL_NAME AND level1.PORTAL_NAME = LEVEL0.PORTAL_NAME Query 2: SELECT DISTINCT LTRIM (SYS_CONNECT_BY_PATH (PORTAL_LABEL, ' > '), ' > ') PATH FROM ( SELECT * FROM (SELECT * FROM PSPRSMDEFN WHERE PORTAL_NAME = 'EMPLOYEE') CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME START WITH (PORTAL_URI_SEG2 = UPPER ('Component Name here') AND PORTAL_REFTYPE = 'C' AND PORTAL_CREF_USGT = 'TARG') ) WHERE PORTAL_REFTYPE = 'C' CONNECT BY PORTAL_PRNTOBJNAME = PRIOR PORTAL_OBJNAME START WITH (PORTAL_OBJNAME = 'PORTAL_ROOT_OBJECT' AND PORTAL_REFTYPE = 'F') Check if the Component is registered SELECT LPAD('–',2*(LEVEL-1)) || PORTAL_LABEL "NAVIGATION” FROM (SELECT PORTAL_LABEL, PORTAL_PRNTOBJNAME, PORTAL_OBJNAME, PORTAL_URI_SEG2 FROM PSPRSMDEFN A WHERE PORTAL_NAME = ‘EMPLOYEE' ) B WHERE B.PORTAL_PRNTOBJNAME != ' ' START WITH (B.PORTAL_URI_SEG2 IN (SELECT D.PNLGRPNAME FROM PSMENUITEM A, PSMENUDEFN B, PSPNLGROUP D WHERE A.MENUNAME=B.MENUNAME AND A.PNLGRPNAME = D.PNLGRPNAME AND A.PNLGRPNAME LIKE UPPER('Enter Component Name')) ) CONNECT BY PRIOR B.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME

Peoplesoft Basics

Explain briefly about record properties? KEY: the record knows a field, which uniquely identifies each row, as a key. According, to the key field, we will search and retrieve data from the database. This will not allow duplicate and not null values. It is a primary key It allows multiple keys Maximum keys allowed in a record is 15 It should be placed at the top of the record definition. Duplicate Order Key: It provides a way of ordering data in the table when the duplicate values are allowed. It will create index for the column. However the index can be disabled. Alternate Search key: It identifies the field as a key that provides an alternative path into table data. Due to alternate search key the system becomes slow, because database index will be created when SQL creates the tables, so it will consume the disk space. It allows duplicate values. Alternate search will be seen in the search dialog box when update/display mode is selected. Descending order key: It is used to retrieve data in the form of 3-2-1 or C-B-A and normally it is used in Effective date. What is ordering key? The following way we have to arrange the fields Field with primary key and search box. Field with primary key Field with duplicate order key. Define Scroll? How many types of scrolls are there? A Scroll is used to enter multiple rows and retrieve multiple row data. There are two types of scrolls: Nested scrolls – For each repeating data and for each entry with other set of repeating set of data is called a nested scroll. Maximum 3 nested levels are allowed. The nested scrolls share same high-level keys. Independent scrolls – At each scroll level a maximum of 15 screen be included. Define Occurs Level? Occurs level specifies the level of a scroll bar relative to any other scroll bars on a panel. The following are the occurs level at each nested scroll bar. The primary scroll bar in a set of nested scroll bars has an occur level 1 A Secondary scroll bar has an occurs level of 2, which means it is subordinate to scroll bar 1.The third scroll bar has an occurs level of 3, which means it is subordinate to scroll bar 2. What are the types of controls that can be placed on the panel? There are three types of controls: Aesthetic Control: It is used to organize the information on the panel and it is not associated with the database. There are 4 types of aesthetic contents. Text Frame Group box Static image. Data entry control: It is used to offer different way to enter and maintain the information. There are 7 types of Data entry controls. Edit box Drop down list box Long edit box Check box Radio button Sub-panel Image. Function and data processing control: It adds command and maintains level of information on the command. There are 6 types of data processing controls Command push button Process push button Secondary panel Scroll bar Grid control Tree. Define Sub-panel and Secondary panel? Sub-panel : It is a predefined, preside group control, which will appear in more than one panel. (Utility: if you have group of controls, such as address coasts, which you use on multiple panel definition, you can save those panel control groups on sub- panel) Secondary Panel: An invisible control that associates a secondary panel with a primary panel. (Utility: Secondary panels are used to gather or display supplemental information related to the object that appears in a primary panel) Explain about display control field and related display control field? Display control field: It is controlling the field in some other record. Related control field: display control field controls it. Micromax Canvas 4 (White) What is panel group? Explain in brief about panel group? Panel group is the bridge between panel and menu. A panel group is a set of panels That should be processed as if it is one panel. The panel group represents a complete business transaction because all the Information goes together. The group must be stored in the database as a single Unit of work. At runtime, we are opening and saving panel group not individual Panels. Define a Menu? How many types of Menus exist? Menus are used to navigate the application (Menu is a window to the application) There are two types of menus: Standard menu: It appears in the menu bar of a Peoplesoft application. Popup menu: Allows the user to navigate How many types of securities are available in People soft? There are 6 types of securities: RDBMS Security Network security Operator security Object security Tree level security Query security (row level security)

Blank Menu after user logs into PepleSoft

You can check existing Doc ID 1301566.1 from Oracle Customer Connect. The document mentions a Workaround. Below is the Workaround. WORKAROUND ---------------------------- 0) "PeopleTools -> Web Profile -> Web Profile Configuration", search for your webprofile and make sure "Cache Menu" = UNCHECKED. Save and bounce PIA. 1) Open App Designer 2) Open stylesheet: PSHOMEPAGE 3) Locate the .ptpgltdroppable and li.pthpli style classes, and have them apply the IE zoom property like so: /* pagelet column */ .ptpgltdroppable { margin:0; padding:0; zoom:1; /* add this property */ } /* pagelet, sortable and fixed position */ li.pthpli { position:relative; list-style-type:none; margin:0 0 4px 0; padding:0; zoom:1; /* add this property } 4) Save the stylesheet 5) Open the PSNAV2 stylesheet 6) locate the following comment: /* end left navigation CSS */ 7) Add the following before the end CSS comment: * html #ptnav2pglt, * html #ptnav2pgltbody, * html #ptnav2tree { zoom:1; } /* end left navigation CSS */ 8) Save the style sheet 9) Shutdown the web server 10) Delete webserver cache 11) Start the web server 12) The user should clear browser cache 13) Test

Employees are not visible in job data / Personal data through peoplesoft front-end

Check if you have enabled the future effective dated rows for the Security Access Type. Set Up HRMS >> Security >> Core Row Level Security >> Security Access Type >> Security Type Table Also check the HRMS Security Install Settings where you configure the actions that trigger future effective dated security rows. Set Up HRMS >> Security >> Core Row Level Security >> Security Installation Settings >> Security Install Settings Note: This is based on HRMS 8.9.

Record Types in PeopleSoft

There are 7 types of records in PeopleSoft.

• SQL Table
• SQL View
• Derived/Work
• SubRecord
• Dynamic View
• Query View
• Temporary Table.

SQL Table: A record that is actually built on database.

SQL View: PeopleSoft View is a query written specially to retrieve/modify data on the records and also view's are used to show transaction data on reports and much more.

Derived/Work: This record does not store any data. It is not a database object an hence cannot be built.

SubRecord: They are used to group fields together and inserted in to records. These are reusable objects. A Subrecord too is not a database object and hence does not store any data.

Dynamic View: These are essentially SQL statements executed on the fly by the PeopleSoft component processor. This is different to a regular PeopleSoft view which is a database view that needs to be built and exists in the PeopleSoft database.

Query View: This is selected to define the record definition as a view that is constructed using the PeopleSoft Query tool. Before you can create the view, PeopleSoft Application Designer prompts you to save the definition. 

Temporary Table: They are used in Parallel processing and are used for improving performance of a process like Application Engine.

           

Types of Tables in PeopleSoft

 

What is a Setup Manager?

• A Setup Manager is delivered by PeopleSoft and it helps in identifying, managing, and executing setup tasks based on business processes or the PeopleSoft products you select. 
• Setup Manager reduces the time and cost required to implement your PeopleSoft
  applications, ensuring the implementation is completed accurately and in the
  correct sequence.
• The PeopleSoft Setup Manager is designed to assist you with your implementation of PeopleSoft products.
• It enables an Organization to quickly identify the products an organization would implement and setup required to support the implementation.

PeopleSoft Setup Manager Setup

• PeopleSoft Setup Manager is delivered as part of the Enterprise Components that are standard with all PeopleSoft products.
• Several activities must be completed before you use the setup task list that will guide you through the implementation of your PeopleSoft applications.
•  First, the implementation team must make business decisions regarding what functionality you need to configure within your PeopleSoft applications.
• Next, you must install your PeopleSoft database and define security access.
• The EOLT Implementer role contains the permissions lists necessary to access the relevant Setup Manager pages contained within a PeopleSoft database.
• In addition to the other roles you associate with your user profiles, you must ensure the EOLT Implementer role is assigned to any user who requires access to Setup Manager.

• PeopleSoft Setup Manager produces a setup task list that identifies the setup tasks required to support your specific implementation and presents those tasks in the sequence in which they must be completed.
• The list contains the related navigation path that shows where a setup component is located and provides a direct link to that setup component.
• For each task, a suggested load method is identified.

Note - Setup Manager system data must never be altered or deleted as this will impair data integrity and Setup Manager results.

Security: Roles Required for a User Profile

• EOLT Implementer - LifeCycle Tools Implementer has access to all Implementer specific components
• PTLT_PROJECT_MGR - This role should be assigned to Project Managers who use the Setup Manager.
• PTLT_IMPLEMENTER - This role should be assigned to Implementers who use the Setup Manager.  Operators who have this Role (and not the PTLT_PROJECT_MGR Role) are not allowed to Generate or Delete Task Lists, or perform any other potentially destructive operations. 

 

Understanding Setup Task List

• The Manage Setup Tasks component identifies the setup tasks required to complete your implementation based on the feature selections you made in your configuration set.
• Setup tasks are displayed in sequence showing the order in which they must be completed and how each setup task relates to specific PeopleSoft setup component.

Testing the Implementation

• Testing individual online transactions and batch processes
• Verifying that you can access any existing data and enter new data successfully.
• Running through the steps of business processes end-to-end.

 

Setting Trace for an Application Engine

Application Engine Trace

• Navigate to PeopleTools >> Process Scheduler >> Processes.

• Click "Override Options" tab. Select "Parameter List" drop down and select "Append". We can select the Trace values based on requirement. Few trace options are as below.
• -TRACE 7
• -TOOLSTRACEPC 4044
• -TOOLSTRACESQL 31
• -TRACE 135

Save the Definition and this program will be traced each time it is run, until trace setting is removed and set parameter list back to None.  For normal program tracing use following trace settings:

-TRACE 7 -TOOLSTRACEPC 4044 
 
The above settings have equivelent settings in psprcs.cfg:

TraceAE=    -TRACE  output goes into Application Engine Trace (.AET)
TracePC=    -TOOLSTRACEPC  output goes into the PeopleTools trace file (.trc)
TraceSQL=  -TOOLSTRACESQL output ALSO goes into the PeopleTools trace file (.trc)
Again, depending on what the program is doing, most commonly we'd use the -TRACE & -TOOLSTRACEPC together. The only time I use the -TOOLSTRACESQL is when we're looking for a problem with the psae program itself.

NOTE: Using Both the -TOOLSTRACEPC & -TOOLSTRACESQL together should only be done when required, as the output from both go into the same file, making it very large and difficult to read.