Access Log (PSACCESSLOG)

Peoplesoft stores the access log details in PSACCESSLOG table.
This record has following fields:
1. OPRID (Stores OPRID)
2. LOGIPADDRESS (Stores IP Address or System name)
3. LOGINDTTM ( Stores date login time).
4. LOGUOTDTTM (Stores date logout time).
5. PT_SIGNON_TYPE (Stores Signon Type).

Creating MS EXCEL Using CreateObject in PeopleSoft

Here are the few examples how to create EXCEL file and how to read EXCEL file using PeopleCode. This can be used in AppEngine And Online PeopleCode.

This can be used in Excel Reporting and Formatting Excel file (Like changing cell colour etc.… ).

/* Set up the Excel COM objects and open the template file */
Local object &oWorkApp, &oWorkBook;
&oWorkApp = CreateObject("COM", "Excel.Application");
&oWorkApp.DisplayAlerts = "False";
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\some_path_to\template.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkApp.ActiveWorkBook.SaveAs("C:\your_output_file.xls");

&oWorkSheet.Cells(1, 1).Value = "I'm adding stuff to be bolded";
&oWorkSheet.Cells(1, 1).Font.Bold = True; 

&oWorkApp.ActiveWorkBook.Save();
&oWorkApp.ActiveWorkBook.Close();
&oWorkApp.DisplayAlerts = "True";
&oWorkApp.Quit();

/*Add data to cells of the first worksheet in the new workbook*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.add();
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkSheet.Range("A1").Value = "Last Name";
&oWorkSheet.Range("B1").Value = "First Name";
&oWorkSheet.Range("A1:B1").Font.Bold = True;
&oWorkSheet.Range("A2").Value = "Doe";
&oWorkSheet.Range("B2").Value = "John";
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.xls");

/*How to read data from one cell and writes to another*/
&oWorkApp = CreateObject("COM", "Excel.Application"); 
ObjectSetProperty(&oWorkApp, "Visible", True); 
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks"); 
&oWorkBook.Open("E:\Personal\dummy\TEST1.xls"); 
&oWorkSheet = &oWorkApp.Worksheets("Sheet1"); 
&oData = &oWorkSheet.Range("A1").Value; 
&oWorkSheet.Range("A2").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.xls");

/*How to read data from one cell and writes to different sheet*/
&oWorkApp = CreateObject("COM", "Excel.Application"); 
ObjectSetProperty(&oWorkApp, "Visible", True); 
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks"); 
&oWorkBook.Open("E:\Personal\dummy\TEST1.xls"); 
&oWorkSheet = &oWorkApp.Worksheets("Sheet1"); 
&oData = &oWorkSheet.Range("A1").Value; 
&oWorkSheet2 = &oWorkApp.Worksheets(2);
&oWorkSheet2.Range("A1").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.xls");

/*Add data to cells of the first worksheet in the new workbook*/
&oWorkApp = CreateObject("COM", "Excel.Application"); 
ObjectSetProperty(&oWorkApp, "Visible", True); 
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks"); 
&oWorkBook.Open("E:\Personal\dummy\TEST1.xls"); 
&oWorkSheet = &oWorkApp.Worksheets("Sheet1"); 
&oWorkSheet.Range("A1").Value = "Last Name"; 
&oWorkSheet.Range("B1").Value = "First Name"; 
&oWorkSheet.Range("A1:B1").Font.Bold = True; 
&oWorkSheet.Range("A2").Value = "Doe"; 
&oWorkSheet.Range("B2").Value = "John";
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.xls");

/*Transfer the data to Excel from Rowset*/
&oWorkApp = CreateObject("COM", "Excel.Application"); 
ObjectSetProperty(&oWorkApp, "Visible", True); 
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks"); 
&oWorkBook.Open("E:\Personal\dummy\TEST1.xls"); 
&oWorkBook = &oWorkApp.Workbooks.Add(); 
&rs_Awards = CreateRowset(Record.PERTBL); 
&rs_Awards.Fill("WHERE FILL.YEAR = '2008' AND FILL.STATUS = 'C'");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
For &ie = 1 To &rs_Awards.activerowcount 
&oWorkSheet.Cells(&ie, 1).Value = &rs_Awards.getrow(&ie).PERTBL.ID.Value; 
&oWorkSheet.Cells(&ie, 2).Value = &rs_Awards.getrow(&ie).PERTBL.COMP.Value; 
End-For;
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.xls");

/*Save an xls file as a CSV*/
&oWorkApp = CreateObject("COM", "Excel.Application"); 
ObjectSetProperty(&oWorkApp, "Visible", True); 
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks"); 
&oWorkBook.Open("E:\Personal\dummy\TEST1.xls"); 
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.csv", 6);

Person Model Presentation

SQLExec : Return: 8015 - Bind value is too long

You get this error in an online page or while running a Application engine program. This error happens when you try to insert more than 254 characters in a long field using sqlexec and do not use %TextIn meta sql.

Resolution

Use %TextIn meta-sql for the bind variable that is used for inserting into a long field. For e.g. %TextIn(:1)

%TextIn is documented in peoplebooks and is mandatory for all insertions/update of LongChar fields using sqlexec for all database platforms.

Here are some resolutions that discusses this issue in Metalink – Oracle support site.

E-AE Application Engine PeopleCode Step with SQLExec Receives Error; return code 8015 "Bind value is too long" [ID 889806.1]

E-PC:"Bind value is too long" Error When Using SQLExec to Insert into Long Char Field [ID 620874.1]

 

Source: Some Blog.

Grid Row Selection Indicator

PeopleSoft grids have the option of using a single or multi-row selection indicator. A single row selection indicator is a radio button (one option) while a multi-row selection indicator is a check box. You can use this functionality to get the user to select the row(s) they are interested in.

These settings are in the page field properties of the grid (under the Use tab):

This all works really well, but what is the event triggered by user when they use these selection indicators? Well, there isn't one — well not in the sense of a field change anyway. However, there is a Set Component Changed option in the grid properties, which, when set, will trigger a save event when the user sets or changes the selection indicator(s).

So, if a user selects a row in the grid (single selection radio button), when they press save, PeopleSoft will recognise this event and fire off any code in SaveEdit, SavePreChange, or SavePostChange

Here is a snippet of PeopleCode you could use to process the row(s) selected by the user and perform an action:
Local Rowset &rs_YOUR_RECORD;
Local integer &i;
 &rs_YOUR_RECORD = GetLevel0()(1).GetRowset(Scroll.YOUR_RECORD);
 For &i = 1 to &rs_YOUR_RECORD.ActiveRowCount
     If &rs_YOUR_RECORD(&i).Selected = True Then
        /* This is a row selected by the user, do something here */
    End-If;
 End-For;

PeopleCode Bypass Search Page by Role

 

Sometimes you would like to bypass the search page depending on the user's role. For some users, they need the ability to look at other employee’s time sheets, managers for example. However, for others, they should only be restricted to their own data. Below is PeopleCode example that will bypass the search page depending on users' role.

&FLAG_ROLE = "Y";

For &I = 1 To %Roles.Len;

If %Roles [&I] = "CSR" Or

%Roles [&I] = "Operations Service Centre" Then

&FLAG_ROLE = "N";

/*Allow this person to enter the search page*/

SetSearchDialogBehavior(1);

/*Unhide name for user to be able to search */

UnHide(your_search_record_onthe_component.NAME);

End-If;

End-For;

If &FLAG_ROLE = "Y" Then

your_search_record_onthe_component.EMPLID = %EmployeeId;

/*Hide emplid and name so user can not search*/

Hide(your_search_record_onthe_component.EMPLID);

Hide(your_search_record_onthe_component.NAME);

/* skip search page */

SetSearchDialogBehavior(0);

AllowEmplIdChg( True);

End-If;

Delete PeopleSoft Query From the Database

 

The below function can be used to delete a PeopleSoft query from database.

Function DeleteQuery(&sQueryName As string)
   SQLExec("DELETE FROM PSQRYDEFN WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYSELECT WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYRECORD WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYFIELD WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYFIELDLANG WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYCRITERIA WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYEXPR WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYBIND WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYBINDLANG WHERE QRYNAME=:1", &sQueryName);
   /*Below tables are not available in older PS versions*/
   SQLExec("DELETE FROM PSQRYSTATS  WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYEXECLOG WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYFAVORITES WHERE QRYNAME=:1", &sQueryName);
End-Function;

DB Link

A database link (dblink) links databases. It allows you to query against other databases using the syntax TABLENAME@DBLINK.

Use the following code to create a database link:

create database link "database_link_name"       

connect to username

identified by "password"

using 'connection_name';

The values you need to plugin are:

• database_link_name the name you want to give your database link and what you enter after the @ in TABLENAME@DBLINK
• username the username to connect to connect to the database
• password the password for the username to connect to the database
• connect_name the TNS or full domain name to uniquely identify the database connection

Important tips:

• You need to create a new connection to test the database link - it won't work in the same connection used to create the database link.
• If you get a TNS error, ensure that TNSNAMES.ORA is up to date on the database server you are creating the link on (i.e Both the databases should be updated with the latest TNS).

If the table is in your connected schema.

select * from table_name@dbname;

If the table is not in your connected schema. And you have select privileges on other schema table then.

select * from schema.table_name@dbname ;

Query Security

 

Query Security:

 

• PeopleSoft Query uses query access group trees to control the access of the tables in the PeopleSoft database.
• You create and update query access group trees using Query Access Manager.
• You should create query access group trees based on your organization’s needs and on any customizations you’ve made.

 

Relationship between row-level security and Query security record definitions:

 

• PeopleSoft applications implement row-level security by using a SQL view that joins the data table with an authorization table.

 

• To apply row level security:
• Open the record on which you want to apply row-level security. Click the Properties button, and select the Use tab from the Record Properties dialog box.
• Select the security record definition (usually a view) in the Query Security Record list box.

 

• Row-Level (Data Permission) Security Views:

1. Using PeopleSoft row-level security views enables you to restrict users from seeing certain rows of data.
2. You can restrict data by:

1. User, by using the OPRID field.

2. Primary permission list, by using the OPRCLASS field.

3. Row security permission list, by using the ROWSECCLASS field

3.             3.   To implement row-level security through a security view:
1. In Application Designer, insert one of the three row-level security fields(OPRID, OPRCLASS, ROWSECCLASS) into the record definition.
2. Configure the field as a Key, but not a List Box Item.
3. Save the record and build the view.
4. Use the record as the search record or query security record.

• Steps for creating query security record:

          1. Create a view depending on your security need.

 

Example: create a view which has oprid, rowsecclass and the parent key field and in sql editor write the following sql:

 

SELECT DISTINCT opr.oprid,
                opr.rowsecclass,
                dtl.t_cust_id FROM   psoprdefn opr,
       ps_t_sjt_class cls,
       ps_t_sjt_class_dtl dtl WHERE  cls.rowsecclass = dtl.rowsecclass
       AND cls.t_cust_id = dtl.t_cust_id
       AND opr.rowsecclass = cls.rowsecclass 

 

2. Attach the above view to the record in record properties (query security record).

 

3.            3. Grant security to that record to which the query security view is attached.
4.            4. In PIA, traverse to query access manager (Oracle PS Tools ->people tools –> Query  Security -. Query Access Manager). Then CLICK ON CREATE NEW TREE.

 

 

5. 5. If access group is already present, then search for that access group in the prompt.
6. 6. If you want create a new access group then enter the access group name and PRESS ENTER, it will be redirected to the below page where you can create your own access group.       
   
7. 7. Then insert the child records to the access group.

 

step 7.1

Step 7.2   

Step 7.3

 

8. 8. Go to permission list and traverse to query. (People Tools -> Security -> Permission & Roles -> Permission list (Select the permission list)).

 

9. 9. In access group permissions, assign the tree name and access group.

 

 

10. 10. In query manager, add the record to which the query security view is attached.

 

 

NOTE: You should consider adding record definitions to the query trees in a hierarchy that matches the parent/child relationship of records in your database.

 

 

Find Cookie For PeopleSoft Page

Following steps helps to find cookie for PeopleSoft Page.
1. Log in to PeopleSoft using the log in screen or single sign on.
2. In the same browser window which is opened, type JavaScript:document.cookie and press enter. You can see the cookie that is generated for that page.

Reuse in App Engines

One of the easiest tricks to improve performance of some App Engines is the ‘ReUse’ setting. It’s useful when you have a SQL object that is called multiple times, for instance when you have a DoSelect that runs a SQL Step repeatedly, substituting different Bind variables in each time.
 
The default setting for ReUse is ‘No’, which means that each time the SQL Step is called it is recompiled and new execution plan generated, bad news if the SQL has to run a large number of times. If the ReUse setting is ‘Yes’ then any %Binds in the SQL statement are converted into ‘:1’, ‘:2’ etc. This means the statement can just be compiled once and the same plan used each time, resulting in really quite dramatic improvements in processing time.
In addition to the performance gain from the above, some databases also have an area of memory cache where recently used SQL is stored. Oracle definitely has this cache, I’m not sure about other databases. As each SQL statement would contain Binds rather than literals it would be an exact match for the previous SQL Statement enabling the database to use the cached SQL and thus giving a further performance boost.
As always, there are some exceptions. If the %Bind is either part of either a record or column name (using ‘NOQUOTES’) then it shouldn’t be used. If this is the case the record and/or field is changing each time and as a result a new plan would be needed, therefore PeopleSoft converts the Binds to statics.
The performance gains that this gives will vary depending upon your SQL and the data, but a recent opportunity I had to set the ReUse flag to Yes dropped processing time for an AE Section from 15 mins to just under 2. Well worth considering next time you write an App Engine.

Query To See Objects Included In Project

Run the following query to get all the objects included in the Project.

SELECT projectname,
       objecttype,
       DECODE(objecttype, '2', 'Field',
                          '0', 'Record',
                          '5', 'Page',
                          '6', 'Menu',
                          '8', 'Record Peoplecode',
                          '10', 'Query',
                          '17', 'Business Process',
                          '18', 'Activities',
                          '20', 'Process Definition',
                          '25', 'Message Catalog',
                          '30', 'SQL',
                          '33', 'App Engine',
                          '44', 'Page Peoplecode',
                          '49', 'Image',
                          '46', 'Component Peoplecode',
                          '47', 'Component Record Peoplecode',
                          '48', 'Component Rec Fld Peoplecode',
                          '4', 'Translate Values',
                          '7', 'Component',
                          '43', 'App Engine Steps',
                          '11', 'Trees',
                          '13', 'Access Group',
                          '53', 'Permission List',
                          '19', 'Role',
                          '58', 'Application package peoplecode',
                          '55', 'Portal Registry Structure',
                          '50', 'Style Sheet',
                          '57', 'Application class',
                          '51', 'Html',
                          '34', 'App Engine Section',
                          '56', 'URL DEfinition',
                          '32', 'Component Interface',
                          '37', 'Messages',
                          '36', 'Message Channel',
                          '40', 'Subscription PeopleCode',
                          '38', 'Approval RuleSet') object_type,
       ( objectvalue1
          || '.'
          || objectvalue2
          || '.'
          || objectvalue3 )                         objectname
FROM   psprojectitem
WHERE  projectname = 'Project Name'
       AND objectid2 <> 102
       AND objecttype <> 0
        OR ( projectname = 'Project Name'
             AND objectid2 <> 102
             AND objecttype = 0
             AND objectid2 <> 2 )
ORDER  BY object_type;