Nov 20, 2012

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'



Nov 12, 2012

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;