Index on View (Oracle)

A view is really just a stored SQL statement so if you want to create an index on a view, create the index on the base table instead. 

You can't even add constraints on view. Constraints such as Primary key and unique key do occupy spaces because they create a unique index in turn.  You can create a view with check option.

Note: Using DISTINCT, GROUP BY or UNION clause in a View implies that no join using an index can be done on this View. These keywords should be avoided.

Debug SQR

Command line Flag helpful in performance tuning is –S, requests the status of all cursors, which includes text of each SQL statement, number of times compiled and run and total number of rows selected

Direct URL of a page in PeopleSoft

To directly navigate to a page with portal navigation

Link: http://server/servlet_name/SiteName/PortalName/NodeName/content_type/content_id?content_parm

For components content parameters are : Page=pagename&action=action_value&key_id=key_value

If you do not specify the Page then it goes to the first page in component.

Action = A - Add, U - Update/Display, L - Update/Display All, C - Correction

KeyIDs are actual Field Name of the Search record that has search key attribute set.

To display a page and navigate to that page for e.g URL Maintenance Page the URL will be

https://servername/psp/ps/EMPLOYEE/EMPL/c/UTILITIES.URL_TABLE.GBL?Page=URL_TABLE&Action=U&URL_ID=CMDOCDB

Example:

https://servername:port/psc/instance name/EMPLOYEE/Node/c/ABC_MENU.ABC_COMP_CMP.GBL?page=ABC_PAGE_NM

ORA-00022: invalid session ID; access denied

Here is a quick fix.

Set the following value in psappsrv.cfg (appserver) and psprcs.cfg (batch server) for all the configured appservers and batch servers. You may have to reconfigure the appserver and batch servers and restart them.

DbFlags=8

Default value is DbFlags=0 which means use Persistent Secondary DB Connection.

Setting DbFlags=4 is not recommended by PeopleSoft, which completely disables the secondary database connection.

Setting it to 8 disables Persistent Secondary DB Connection, but it still uses on demand Secondary DB Connection for each request. This is required for using GetNextNumberWithGapsCommit (GNNWGC) function, which is internally used by PeopleSoft for workflow transactions to generate APPR_INSTANCE

If you do not do this, you may get row inserted in PS_APPR_INST_LOG with

APPR_INSTANCE = 0

which may cause, some undesired workflow routings.

The easiest solution I have found is

delete from ps_appr_inst_log where APPR_INSTANCE = 0

Please make sure that you backup the data and test it.

Here is some more information on GetNextNumberWithGapsCommit (GNNWGC) function from peoplebooks.

Use this function instead of the GetNextNumberWithGaps function. The GetNextNumberWithGaps function is very restrictive in its usage. The GetNextNumberWithGapsCommit function can be used in any event. The sequence number (record.field ) is incremented right away and it doesn't hold any database internal row lock beyond the execution of this function.

Note. A secondary database connection is used to increment and retrieve record.field. The default behavior is to keep the secondary database connection persistent in order to improve performance for the next GetNextNumberWithGapsCommit usage. If the database administrator finds the persistent connection too high an overhead for the production environment (which should not be the case since PeopleSoft uses application server to mulitplex the database connection), the database administrator can change the default behavior to use an on-demand connection method. The persistent second connection is disabled using DbFlags bit eight in the application server and process scheduler configuration files. The second connection can be completely disabled using DbFlags bit four in the application server and process scheduler configuration files

In this resolution, PeopleSoft generally recommends to Set DbFlags=8. I hope they deliver this value by default in future peopletools releases.

Retrieve milliseconds from Oracle database and display it on a page.

You can use the following SQL to get milliseconds from Oracle 9i and higher and assign it to a Field of Type Time in PeopleSoft page. May Place this code in FieldDefault event.

SQLExec("select TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF') from dual", XX_TEST_DERIVED.XX_TIME);

Set the Time Formatting to HH:MI:SS:999999 in Field properties.

For Date Time Field, use the following SQL.

SQLExec("select TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD-HH24:MI:SS.FF') from dual", XX_TEST_DERIVED.XX_DATETIME);

Set the Time Formatting to HH:MI:SS:999999 in Field properties. Select Display Century and Display Time Zone in Page Field Properties to display complete date and time.

Page data is inconsistent with database

If you are getting this message, while saving the page, please refer to following resolution on MOS (My Oracle Support).

E-AS: How to Trace "Page data is inconsistent with database" error? [ID 655907.1]

Step 1: Set the following in psappsrv.cfg

TracePPR=1 

TracePPRMask=32767

Step 2: Go to the PIA and before logging on, add &trace=Y (uppercase y) at the end of the web address and press the keyboard Enter key. Check the the first 5 SQL options and for PeopleCode the 'List Program' and 'Each statement' options.

Step 3: Log in to PIA and reproduce the error.

Step 4: Check the trace, especially the portion which contains this information: 

PSAPPSRV.28630    1-167605 11.00.03    0.001 Record RO_LINE_ATTR.ROW_LASTMANT_DTTM database value "2007-06-11-10.58.44.000000" page value "2007-06-11-10.05.30.000000" 

The trace should clearly show the field which is producing the error as well as the values populated both on database side and page side.

One of the most common reason for this happening is:

You are using a view to select data into a Scroll based on a table and No Auto Select option is turned on. The number of columns in view and scroll area base table differs and additional column in base table contain not null values. You use Auto update for Scroll and change the value of the field in a scroll and save.

To resolve this issue, add the same number of  columns to view as the base table or if possible, make the same view as a base record for the scroll that you use for selection.

Other reason is using SQLExec in SavePostChange to update the Base Table Record and therefore changing the value.

How to Use CTRL+J

1. Microsoft Internet Explorer
- CTRL + J

2. Mozilla Firefox
- Hold down the CTRL key and quickly hit "J" TWICE

3. Google Chrome
Use either of below options:
- First press and hold J then press CTRL and release J.

- Press CTRL+SHIFT+J Twice

4. MS Edge or IE 11
- Press the "J" key and hold
- Press the "CTRL" key and hold
- Press 'SHIFT" key and hold
- Release the "J" key

PeopleSoft project migration - tips

1. Always set your PeopleSoft Application Designer Tools Options to:

• Insert object into project when modified and saved.

• Prompt for related objects

The benefit of setting these Tools parameters is that the work the developer just did stands a much better chance of actually being stored in the project.

2. Use the Save All feature early and often in order to reap the benefit of the above recommendation.

3. Insert role, permission lists and portal structure into the project if PeopleSoft online has been used to register

4. Always run Compare Reports between source and target data bases before migrating (copying) projects.

Running compare report set/reset the project upgrade checkbox automatically

5. Make sure the menu definition, component definition and page definition(s) are in the same project as the Permission List.

Export and Import DMS database scripts

Export:
Set output C:\temp\RawData.dat;

Set log C:\temp\RawData.log;

export ABC_RAW_TBL;
export ABC_DATA_TBL;

Import:

set input C:\temp\RawData.dat;
Import *

Check Box Select/Deselect All on Grid

The below function is to be used on a grid with multiple check boxes. Place the code behind a FieldChange event and users will have the option to Select or Deselect grid rows all at once.

Function selectAllRows(&rs As Rowset)

   Local number &i;

   Local Row &row;

   

   For &i = 1 To &rs.ActiveRowCount

      &row = &rs.GetRow(&i);

      /* Make sure we only select visible rows. */

      If &row.Visible = True Then

         &row.Selected = True;

      End-If;

   End-For;

end-function;

/*main line*/

Local Rowset &rs;

&rs = GetLevel0()(1).GetRowset(Scroll.scroll_table);

/*Call Function*/

selectAllRows(&rs);

The same code would work for multiple check boxes "Deselect All", just change the name of the function and line &row.Selected = True; to &row.Selected = False;

Make sure the Multiple Row (Check Box) is checked on the grid properties.

Launch a PSQuery from PeopleSoft webpage

Below delivered function could be used to launch a PSQuery in PS webpage. Place the peoplecode in record rowinit event.

Declare Function CreateQueryURL PeopleCode WEBLIB_QUERY.QRYGENFUNCS FieldFormula;

Your record name.your field name.Value = CreateQueryURL("yuor query name", True);

Example:

Declare Function CreateQueryURL PeopleCode WEBLIB_QUERY.QRYGENFUNCS FieldFormula;

If %Component = Component.ABC_COMPNM_CMP Then
   ABC_COMMON_WRK.URL_1.Value = CreateQueryURL("ABC_PSQRYNM_QRY", True);
End-If;

Custom sort in Grid

Custom sort of the grid can be achieved by using peoplecode. 

Create a Rowset for the grid and use the below code. 

&RowsetName.Sort(Record.Field1, "D", Record.Field2, "A"); 

The grid is sorted by Field1 in Descending and Field2 in Ascending order. 

Search Record and Row Level Security

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

• User, by using the OPRID field.
• Primary permission list, by using the OPRCLASS field.
• Row security permission list, by using the ROWSECCLASS field.

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.

Now, when the user searches, the system dynamically adds a WHERE clause — that incorporates the security field — to the search SELECT statement. The value of the security field is based on the current user.

Oracle SQL Developer problem – enter / backspace / delete button not working – quick fix

To resolve the problem you have to go to:
Tools -> Preferences -> Accelerators -> Load Preset -> Default -> OK

PeopleSoft Trace PeopleCode

One can generate a trace while the subscription PeopleCode get executed by using the following two functions in the subscription PeopleCode:

SetTracePC(0);

SetTraceSQL(0);

Example:

SetTracePC(384);
/* REM PeopleCode Statements to be traced */
Local string &St_temp_l;
/* More PeopleCode statements */
SetTracePC(0);