How to Select Schema of Record or Table of Peoplesoft in SQL
You can view fields or all detail of table of peoplesoft in database by use sql statement
SELECT * FROM ALL_TAB_COLUMNS
where owner = ‘owner name’ AND TABLE_NAME =’RecordName’;
If you want to show all field of record then you can use this sql
SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
where owner = ‘owner name’ AND TABLE_NAME =’RecordName;
You can use Peoplecode in page activate or action field change for hide all row in grid record level 1 or Unhide all row in grid record level 1.
For Example. Define Page and a grid has record in level 1 on page. User has to hide grid when effdt not in this year and show or unhide grid when effdt in this area.
If you want to Hide Record in grid of Peoplecode on level 1
For &ROW = ActiveRowCount(Record.Record_Name) To 1 Step – 1
HideRow(Record.Record_Name, &ROW);
End-For;
If you want to Unhide Record in grid of Peoplecode on level 1
For &ROW = ActiveRowCount(Record.Record_Name) To 1 Step – 1
HideRow(Record.Record_Name, &ROW);
End-For;
You can use queries of sql for fine permission list of each user ID
SELECT a.ROLEUSER, a.ROLENAME, a.DYNAMIC_SW, b.CLASSID
, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME, DISPLAYONLY, AUTHORIZEDACTIONS
FROM PSROLEUSER a , PSROLECLASS b , PSAUTHITEM c
WHERE a.ROLEUSER=’1111′ AND
a.ROLENAME = b.ROLENAME
AND b.CLASSID = c.CLASSIDORDER BY ROLENAME , CLASSID , MENUNAME
You can use queries of sql for fine permission list of page of peoplesoft
SELECT a.ROLEUSER, a.ROLENAME, a.DYNAMIC_SW, b.CLASSID
, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME, DISPLAYONLY, AUTHORIZEDACTIONS
FROM PSROLEUSER a , PSROLECLASS b , PSAUTHITEM c
WHERE a.ROLENAME = b.ROLENAME
AND b.CLASSID = c.CLASSID AND PNLITEMNAME=’JPM_CAT_TYPES’ORDER BY ROLENAME , CLASSID , MENUNAME
You can use queries of sql for fine permission list of component of peoplesoft
SELECT a.ROLEUSER, a.ROLENAME, a.DYNAMIC_SW, b.CLASSID
, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME, DISPLAYONLY, AUTHORIZEDACTIONS
FROM PSROLEUSER a , PSROLECLASS b , PSAUTHITEM c
WHERE a.ROLENAME = b.ROLENAME
AND b.CLASSID = c.CLASSID AND BARITEMNAME=’JPM_CAT_TYPES’ORDER BY ROLENAME , CLASSID , MENUNAME
/*– Use ; –*/
SQL Statment 1
%Execute()
Update TABLE1 Set Status=’A’;
Update TABLE1 Set Status=’B’;
Update TABLE2 Set Status=’C’;
SQL Statment 2
%Execute()
Update TABLE1 Set Status=’A’;
Delete TABLE1 where Status=’B’;
insert into TABLE2 values(‘1′,’C’);
/*– Use / –*/
SQL Statment 3
%Execute(/)
Update TABLE1 Set Status=’A’/
Update TABLE1 Set Status=’B’/
Update TABLE2 Set Status=’C’/
SQL Statment 4
%Execute(/)
Update TABLE1 Set Status=’A’/
Delete TABLE1 where Status=’B’/
insert into TABLE2 values(‘1′,’C’)/