PeopleSoft : SQLEXEC () in RowInit PeopleCode

We cannot perform INSERT,UPDATE statement's through SQLEXEC( ) in Rowinit PeopleCode event.

Below is the alternative to perform insertion and updation in rowinit .

Creating a stroed procedure, that perform's the insertion or updation.
Execute the procedure using SQLEXEC( ) in rowinit.

Example:

1.Creating the stored procedures in Oracle

CREATE OR REPLACE PROCEDURE CH_SAMPLE_PROCEDURE
AS 
BEGIN
    UPDATE PS_CH_TEST_TBL
            SET  COUNT=100;
    COMMIT;
END CH_SAMPLE_PROCEDURE;

2.Execute the procedure
 
EXECUTE CH_SAMPLE_PROCEDURE;

Sample code in ROWINIT:

1.Create a sql object,that contains the sql for creating stored procedure.
    SQLEXEC(SQL.CH_TESTPROCEDURE_SQL);
   Here CH_TESTPROCEDURE_SQL contains a sql for creating stored procedure.
2.Execute procedure.
    SQLEXEC("EXECUTE CH_SAMPLE_PROCEDURE");

Note:
1. To create a stored procedure , you need to have permissions in database level.
2. If you want to check whether the stored procedure is created or not use the below sql.
SELECT * FROM ALL_PROCEDURES WHERE OBJECT_NAME='CH_SAMPLE_PROCEDURE'

PeopleSoft : Query to find the Menu Navigation

SELECT * FROM PSXLATITEM WHERE FIELDNAME = 'PROJECT_STATUS';SELECT DISTINCT RTRIM (REVERSE (SYS_CONNECT_BY_PATH (REVERSE (PORTAL_LABEL), ' > ')), ' > ') "PIA NAVIGATION"

  FROM PSPRSMDEFN

WHERE PORTAL_NAME = 'EMPLOYEE'

   AND PORTAL_PRNTOBJNAME = 'PORTAL_ROOT_OBJECT'

START WITH PORTAL_URI_SEG2 = 'RUN_PCPL1000'  à<Component Name>

CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME;

PeopleSoft : CTRL+J Issue in Google Chrome

CTRL+J is used to see the information about page that is developed in peoplesoft.

In google chrome we got an issue with CTRL+J .

Here is the solution:

First press and hold J then press CTRL and release J.

PeopleSoft : User Id, Symbolic Id, Access Id, Connect Id

	User ID	Symbolic Id	Access Id	Connect Id
Used For	A PeopleSoft user ID is the ID you enter at the PeopleSoft sign-in dialog box. You assign each PeopleSoft user a user ID	Used to map the User Id to Access Id without exposing the Access Id information. All the User Id's are associated with a Symbolic Id (on User Profile Page). A Symbolic Id is associated with Access Id in PSACCESSPRFL record (Access Id is encrypted in this record).	The PeopleSoft access ID is the RDBMS ID with which PeopleSoft applications are ultimately connected to your database after the PeopleSoft system connects using the connect ID and validates the User ID and password. An access ID typically has all the RDBMS privileges necessary to access and manipulate data for an entire PeopleSoft application. The access ID should have Select, Update, and Delete access Users do not know their corresponding access IDs. They just sign in with their user IDs and passwords. Behind the scenes, the system signs them into the database using the access ID.	The connect ID performs the initial connection to the database [RDBMS].Used by 2-tier, App Server, Batch Server to connect the database during Sign-in.
Peoplesoft Application Permissions	Obtains all the rights/permission from Roles/Permission Lists	Does not require to have any PS Roles	Full RDBMS rights	Read-only rights to a) PSDBOWNER (For Oracle/DB2) b) PSSTATUS c) PSOPRDEFN d) PSACCESSPRFL
Type of Account	Peoplesoft Account	Peoplesoft Account	RDBMS Account	RDBMS Account
User exists in Database	No, PeopleSoft no longer creates users at the database level.	No	Yes	Yes
Example	ELLISONLARRY	sa	sysadm [Encrypted in PSACCESSPRFL]. Usually it is schema owner.	people

PeopleSoft : Direct and Indirect reportees in PeopleSoft

 Below is the syntax to get direct and indirect reportees in peoplesoft:

SELECT… CONNECT BY

Select data with a hierarchical (parent/child) relationship

Syntax:

   SELECT…

   [START WITH initial_condition]

   CONNECT BY [nocycle] PRIOR recurse_condition

   [ORDER SIBLINGS BY order_by_clause]

Key:

    START WITH        : The row(s) to be used as the root of the hierarchy

    CONNECT BY        : Condition that identifies the relationship between

                        parent and child rows of the hierarchy

    NOCYCLE           : Do not circle around loops (where the current row has

                        a child which is also its ancestor.)

    ORDER SIBLINGS BY : Preserve ordering of the hierarchical query

                        then apply the order_by_clause to the sibling rows

Example:

Select level-1

,PEOPLE_ID

, SUPERVISOR_ID

FROM PS_JOB_VW

start with SUPERVISOR_ID = 'xxxxx'

CONNECT BY PRIOR PEOPLE_ID = SUPERVISOR_ID;