Wednesday, April 30, 2014

Using HTML Areas

This section provides an overview of HTML area controls and discusses how to:

Insert HTML areas.
Populate HTML areas.
Change HTML area labels.
Include HTML areas in the page tab order.

Understanding HTML Area Controls

You can insert an HTML area control on any PeopleSoft page. You can insert it at any level on a page, and you can place it in a grid control. This control is rectangular and is easy to resize.

Populate the HTML area control in one of these ways:

Statically, in the page field property sheet.
Dynamically, by associating the control with a record field or HTML definition.

If the control is linked to a record field, the value of the record field appears in the HTML area. Use PeopleCode to associate the HTML area control with a predefined HTML definition.

When using HTML areas, consider:

HTML areas are downloaded to Microsoft Excel as a nonbreaking space (&nbsp).
HTML areas are not searched with the Find feature.

Generating Trees in HTML Areas

You can use the GenerateTree PeopleCode function with HTML areas. This example shows the tree that is created by the GenerateTree PeopleCode function on the Select New Parent Folder page:

Image: Example of a tree in an HTML area

This example illustrates the fields and controls on the Example of a tree in an HTML area. You can find definitions for the fields and controls later on this page.

Populating HTML Areas

You can populate an HTML area either statically, using the HTML Area Properties dialog box, or dynamically, by associating the control with a record field. Because the HTML that you write is included in the HTML that is dynamically generated by the system at runtime, consider the following:

The HTML that you include can affect the page layout.

Complying with the design-time sizing of the HTML area is the best way to ensure that you do not affect the layout of the other page field controls. Adding an invisible frame around the HTML area control can help ensure that you do not affect other page fields.
You can use only certain types of HTML tags. These tags are not supported by the HTML area control:
- <body>
- <frame>
- <frameset>
- <form>
- <head>
- <html>
- <meta>
- <title>

Using JavaScript in HTML Areas

If an HTML area contains a reference to third-party JavaScript, you need to include the absolutized attribute in the script tag and set it to"false". For example:

<script type="text/javascript" language="javascript" absolutized="false"
   src="../../../../../ps/spellcheck/lf/spch.js">
</script>

Using Rich Text Editor-Generated HTML in an HTML Area

When you use the rich text editor (RTE) to generate HTML that appears in an HTML area page control, you must call the ProcessRTEHTML function to process the RTE-generated HTML before you assign it to the HTML area. The ProcessRTEHTML function wraps the HTML inside a <div> element, sets the style class to PT_RTE_DISPLAYONLY, and attaches the image processing JavaScript. Use the following code as an example:

Declare Function ProcessRTEHTML PeopleCode WEBLIB_PTRTE.ISCRIPT1 FieldFormula;

&HTMLAAREA.value = ProcessRTEHTML ("URL ID of the image target", "HTML Data To be Processed");

Your code might look like this:

&HTMLAAREA.value = ProcessRTEHTML (URL.PT_RTE_IMG_DB_LOC, "<p>example</p>");

The &HTMLAAREA.valueshould be similar to this:

<div id="RTEDiv188" class="PT_RTE_DISPLAYONLY">
  <p>example</p>
     <script type="text/javascript" language="JavaScript">
          PTRTE_CheckImages("", "PT_RTE_IMG_DB_LOC", "RTEDiv188");
     </script>
</div>

Using HTML Areas in AJAX Mode

Note the following code restrictions in HTML areas when running the application in AJAX mode:

Do not include XML tags, such as CDATA.
Do not include empty or commented out <script> elements.
Do not include document.write as part of the HTML in an HTML area.

Inserting HTML Areas

To insert an HTML area on a page:

Select select Insert, then select HTML Area.
Draw the HTML area on your page.
Move the HTML area control by dragging it with the mouse or by pressing the arrow keys.

Populating HTML Areas

This section discusses how to:

Populate an HTML area statically.
Populate an HTML area dynamically.

Populating an HTML Area Statically

Use the HTML Area Properties dialog box to populate an HTML area:

Image: HTML Area Properties dialog box with constant text

This example illustrates the fields and controls on the HTML Area Properties dialog box with constant text. You can find definitions for the fields and controls later on this page.

To populate an HTML area statically:

Access the HTML Area Properties dialog box.
On the HTML tab, select Constant as the value type.
In the long edit box, enter the HTML code that you want to appear in the HTML area.

Populating an HTML Area Dynamically

To populate an HTML area dynamically:

Access the HTML Area Properties dialog box.
On the HTML tab, select Field as the value type.
Specify the record and field to which you want to associate the HTML area control.

The value of the record field generates the HTML code that is included at runtime in the HTML area.

Note: When you associate an HTML area control with a field, make sure that the field is long enough to contain the data that you want to pass to it. For example, if you associate an HTML area control with a field that is only 10 characters long, only the first 10 characters of your text will appear. You should use long character fields for record fields that are associated with an HTML area control.

Changing HTML Area Labels

Assign a distinct label to your HTML area to distinguish it from other HTML area controls on the Order tab of the page.

To change an HTML area label:

Access the HTML Area Properties dialog box.
Select the Label tab.
Enter a brief text description of the HTML area.

This label does not display at runtime; however, it appears on the Order tab of the page definition.

Including HTML Areas in the Page Tab Order

When the PeopleSoft Pure Internet Architecture generates HTML for a page, it creates a tab index, or order, including every control or widget on the page, based on the field order for that page in Application Designer. However, with HTML areas, the system skips any control defined within an HTML area, by default, when building the tab index. In some situations, this may be confusing to an end user, such as in the case when screen readers are used.

To include the controls in an HTML area within the page tabbing order, add the %tabindex metavariable to the HTML, similar to:

<a href=http://espn.go.com/mlb/clubhouses/bos.html tabindex=%tabindex>Red Sox</a></small></td>
<td><small>94</small></td>

When %tabindex appears within the HTML in an HTML area, the controls within the HTML area assume the tab order given to the HTML area itself.

This metavariable is documented in more detail in the PeopleCode documentation, see %tabindex.

PeopleSoft PeopleTools Meta-Tables

This is an attempt to list PeopleSoft meta-tables along with some kind of description for every table. The list will be broken into categories (pages, records, components, and so forth). Please feel free to add to or correct the list, this is a wiki page, so jump in!

PeopleSoft Projects

PSPROJECTDEFN table stores information about projects created in Application Designer.
Try it out:

SELECT * FROM PSPROJECTDEFN

WHERE PROJECTNAME = 'Your_Project_name'; 

PSPROJECTITEM table stores objects inserted into your Application Designer project.
Try it out:

SELECT * FROM PSPROJECTITEM

WHERE PROJECTNAME = 'Your_Project_name';

Portal Structure

PSPRSMDEFN is a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component. Take a closer look on how this is done!.
PSPRSMPERM: Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.

XLAT Tables

XLATTABLE: Stores translate values (PeopleSoft version prior to 8.4).
PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values.
PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).

Record & Field Tables

PSRECDEFN: Stores informations about tables. One row for each table. Field count and record type are two fields that are stored on this table.

CASE RECTYPE

        WHEN 0 THEN 'Table'

        WHEN 1 THEN 'View'

        WHEN 2 THEN 'Derived'

        WHEN 3 THEN 'Sub Record'

        WHEN 5 THEN 'Dynamic View'

        WHEN 6 THEN 'Query View'

        WHEN 7 THEN 'Temporary Table'

        ELSE TO_CHAR(RECTYPE)

END CASE

PSRECFIELD: Stores records with all their fields (sub-records are not expanded)
PSRECFIELDALL: Stores records with all their fields (sub-records are expanded)
PSINDEXDEFN: Contains 1 row per index defined for a table.
PSKEYDEFN: Containes 1 row per key field defined for an index.
PSDBFIELD: You got it, stores information about fields.

CASE FIELDTYPE

                WHEN 0 THEN 'Character'

                WHEN 1 THEN 'Long Character'

                WHEN 2 THEN 'Number'

                WHEN 3 THEN 'Signed Number'

                WHEN 4 THEN 'Date'

                WHEN 5 THEN 'Time'

                WHEN 6 THEN 'DateTime'

                WHEN 8 THEN 'Image'

                WHEN 9 THEN 'Image Reference'

                ELSE TO_CHAR(FIELDTYPE)

        END CASE

PSDBFLDLABL: Stores field label information.

Process Definition Table(s)

PS_PRCSDEFNPNL: Stores the process definition name, process type(sqr report, application engine...), and the component name associated with the process definition.
PS_PRCSDEFN: Process definitions table. The record stores processes that can run within the Process Scheduler. Security information such as components and process groups are also stored on this table.

Message Catalog Tables

PSMSGSETDEFN: Stores information about PeopleSoft message catalog message sets (descriptions, version).
PSMSGSETLANG: Message sets language table.
PSMSGCATDEFN: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
PSMSGCATLANG: Message catalogs language table.

-- Example

SELECT * FROM PSMSGCATDEFN

WHERE LAST_UPDATE_DTTM  > TO_DATE('03-DEC-07', 'DD-MON-YY')

AND LAST_UPDATE_DTTM  < TO_DATE('05-DEC-07', 'DD-MON-YY')

ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;

-- This will return messages that has been last update/added between 2 specific dates.

Previous PeopleSoft message catalog tables:
PS_MESSAGE_CATALOG: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
MESSAGE_SET_TBL: Message set description table.

-- Example

SELECT * FROM PS_MESSAGE_CATALOG

WHERE LAST_UPDATE_DTTM  > TO_DATE('03-DEC-07', 'DD-MON-YY')

AND LAST_UPDATE_DTTM  < TO_DATE('05-DEC-07', 'DD-MON-YY')

ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;

-- This will return messages that has been last update/added between 2 specific dates.

Menu Tables

PSMENUDEFN: Store Menu related information. No related component info on this table.
PSMENUITEM: List the menu with all components attached to it.

Component Tables

PSPNLGRPDEFN: Stores component related information only.
PSPNLGROUP: This table will give you information regarding a specific component along with the names of pages attached to it.

Pages

PSPNLDEFN: Stores pages definitions.

CASE PNLTYPE

        WHEN 0 THEN 'Page'

        WHEN 1 THEN 'Sub page'

        WHEN 2 THEN 'Secondary page'

        ELSE TO_CHAR(PNLTYPE)

    END CASE

PSPNLFIELD: Stores all items used by each page definition.

CASE FIELDTYPE

        WHEN 0 THEN 'Static Text'

        WHEN 1 THEN 'Frame'

        WHEN 2 THEN 'Group Box'

        WHEN 3 THEN 'Statis Image'

        WHEN 4 THEN 'Edit Box'

        WHEN 5 THEN 'Dropdown List'

        WHEN 6 THEN 'Long Edit Box'

        WHEN 7 THEN 'Check Box'

        WHEN 8 THEN 'Radio Button'

        WHEN 9 THEN 'Image'

        WHEN 10 THEN 'Scroll Bar'

        WHEN 11 THEN 'Subpage'

        WHEN 12 THEN 'Peoplecode Command - (Button/Hyperlink Destination)'

        WHEN 13 THEN 'Scroll Action - (Button/Hyperlink Destination)'

        WHEN 14 THEN 'Toolbar Action - (Button/Hyperlink Destination)'

        WHEN 15 THEN 'External Link - (Button/Hyperlink Destination)'

        WHEN 16 THEN 'Internal Link - (Button/Hyperlink Destination)'

        WHEN 17 THEN 'Process - (Button/Hyperlink Destination)'

        WHEN 18 THEN 'Secondary Page'

        WHEN 19 THEN 'Grid'

        WHEN 20 THEN 'Tree'

        WHEN 21 THEN 'Secondary Page - (Button/Hyperlink Destination)'

        WHEN 23 THEN 'Horizontal Rule'

        WHEN 24 THEN 'Tab Separator'

        WHEN 25 THEN 'Html Area'

        WHEN 26 THEN 'Prompt Action - (Button/Hyperlink Destination)'

        WHEN 27 THEN 'Scroll Area' 

        WHEN 29 THEN 'Page Anchor'

        WHEN 30 THEN 'Chart'

        WHEN 31 THEN 'Push Button/Link'

        WHEN 32 THEN 'Analytic Grid'

        ELSE TO_CHAR(FIELDTYPE)

    END CASE

Security

PSPRSMPERM: Portal Structure Permissions.
PSAUTHITEM: Page Permissions. This table stores the information about the page level access for a permission list.
PSAUTHPRCS Process Group Permissions. A many to many relationship table between Permission Lists and Process Groups. Setup can be found at PeopleTools > Security > Permissions & Roles > Process.
PSROLECLASS: Role Classes table. A many to many relationship table between Roles and Permission Lists.
PSROLEDEFN: This table stores information about Peoplesoft Role definitions. Users get permissions to PeopleSoft objects through Roles, which are assigned Permission Lists.
PSROLEUSER: This table stores information about the Users in Peoplesoft and the roles assigned to them.
PSCLASSDEFN: Permissions List definitions table. Permission list name can be found under Field Name CLASSID.
PSOPRDEFN: Users/Operator definition table. This table stores information about PeopleSoft users. This is the core table for User Profile Manager.
PSOPRCLS: Users/Operator and Perm list mapping Table. This table stores information about PeopleSoft users and the permission lists attached to those users.
A User gets these permission lists indirectly through the roles which are attached to the user
Here is an example query post that uses all of the above security tables!

URL Definitions

PSURLDEFN: Stores URL definitions. Here is the path to create URL definitions in PeopleSoft Root >> PeopleTools >> Utilities >> Administration >> URLs

Application Classes

PSAPPCLASSDEFN: Application Class Definitions table. You can use field PACKAGEROOT to search for a specific Application Package.

PeopleSoft Query Tables

PSQRYDEFN: Stores query related info.
PSQRYFIELD: Stores all fields used in a query (both the fields in the Select and Where clause).
PSQRYCRITERIA: Stores criteria query fields. You can get the name of the fields by joining the PSQRYFIELD table.
PSQRYEXPR: Stores query expressions.
PSQRYBIND: Stores query bind variables.
PSQRYRECORD: Stores all records used in all aspects of query creation
PSQRYSELECT: Stores all SELECT requirements by select type. Example would be sub select, join, ect.
PSQRYLINK: Stores the relationships to child queries.
PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher)
PSQRYSTATS: Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).

SQL Objects

PSSQLDEFN: Stores SQL object definitions.
PSSQLDESCR: Stores SQL objects descriptions, and description long.
PSSQLTEXTDEFN: Stores actual SQL text. You can filter by SQLTYPE field to get SQL objects of interest such as Views SQLs and Application Engine SQLs.

-- When SQL type is:

= Stand alone SQL objects

= Application engine SQL

= Views SQLs

Application Engines

PSAEAPPLDEFN: Table that stores Application Engine program definitions.
PSAEAPPLSTATE: Stores application engine STATE records and a flag to indicate if the record is the default STATE record.
PSAESECTDEFN: Application engine section information and also stores last user id to update a specific section.
PSAESECTDTLDEFN: AE section along with descriptions and wither the section is active or not.
PSAEAPPLTEMPTBL: If your application engine uses Temp tables it will show on this record.
PSAESTEPDEFN: Steps in application engines are stored in this table.
PSAESTMTDEFN: Stores your application engine actions and along with their types, such as "Do Select" and so on.
PSAESTEPMSGDEFN: Application engine message action definition table.
AEREQUESTTBL: Application Engine request table behind the AE run control page.
AEREQUESTPARM: Application Engine request parameters table behind the AE run control page.

PeopleCode Tables

PSPCMNAME: PeopleCode Reference table.
PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).

Process Request Tables

PSPRCSQUE: This record contains the process request information to run a process request.
PSPRCSRQST: This record contains the process request information to run a process request.
PS_PMN_PRCSLIST: A view to list all process requests in the Process Monitor except for "Delete" (runstatus = 2) process requests.

Other Useful Tables

PSSTATUS: Stores PeopleSoft information such as PS Tools release version and the UNICODE_ENABLED boolean flag where a value of 1 indicates the DB is to be treated by Tools as a UNICODE DB.
PSCHGCTLLOCK: Description as explained by PeopleSoft "This table contains a a row for every object that is currently locked by any user. When the user requests to lock an object in the Application Designer, first this table is searched to see if the object is locked by another user. If it is not found, a row is inserted into the table. When the user requests to unlock an object, the row in this table is deleted."
Visit this post to see how could you make use of this table.
PSMAPFIELD: Stores Field mapping of Activity
PS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online.

Unlock PeopleSoft Objects All at Once (Change Control Locking)

Change control is used in Application Designer to lock definitions and track history changes for each object. Change control can be activated through Application Designer using Tools > Change Control > Administrator.

After few good size projects, you can easily have hundreds if not thousands of locked objects. Attempting to unlock the objects one by one is very time consuming, so here is a way to speed up the process.

Locked objects are stored on PSCHGCTLLOCK table. Here is how PeopleSoft describes the table:

This table contains a a row for every object that is currently locked by any user. When the user requests to lock an object in the Application Designer, first this table is searched to see if the object is locked by another user. If it is not found, a row is inserted into the table. When the user requests to unlock an object, the row in this table is deleted.

From the description above, I would feel safe enough to assume that if you want to unlock objects, just delete them from this table! Here is an example to delete by operator ID, you can also delete by object name or object type.

-- deletes locked objects by user id.

DELETE FROM pschgctllock

      WHERE oprid = :userid;

Is this the only way we can "batch" unlock objects? No, it was brought to my attention by a college of mine that Application Designer has a feature to accomplish the same result as deleting from pschgctllock table.

control locking

Select an object to unlock it (Ctrl+click) to select more than one. Right click and chose unlock object.

ORA-28000: the account is locked – PeopleSoft

Just a small PeopleSoft issue I ran into while running an SQR:

(SQR 5528) ORACLE OCISessionBegin(RDBMS) error 28000 in cursor 0:
   ORA-28000: the account is locked
(SQR 4701) Cannot logon to the database.

SQR for PeopleSoft: Program Aborting.

For some reason the sysadm account had been locked, thankfully there’s a fairly easy fix:

For some reason the sysadm account had been locked, thankfully there’s a fairly easy fix:

1	ALTER USER sysadm ACCOUNT UNLOCK;

If you don’t have access you may need to get a DBA to run it.

Reserved Words – Oracle

A quick post on how to check if a word is reserved using the v$reserved_words:

SELECT *
FROM v$reserved_words
WHERE reserved = 'Y'

Alernatively you can specify the word, i.e. to check if online is a reserved word, run the following:

SELECT *
FROM v$reserved_words
WHERE UPPER(keyword) LIKE '%ONLINE%'

Sleep Function Within PeopleCode – PeopleSoft

I discovered that there doesn’t appear to be any built in functionality to allow for a delay to be implemented within PeopleCode. Thankfully there are a couple of roundabout way you can go about doing this:

#1: Works if you are sitting on an Oracle DB

/* The following code creates a 5 second delay using DBMS_LOCK.SLEEP */
&s = 5;
SQLExec("exec DBMS_LOCK.SLEEP(:1)", &s);

#2: Note that this method is not as efficient as DMBS_LOCK.SLEEP()

/* The following code creates a 5 second delay using the java sleep method */
&s = 5;
GetJavaClass("java.lang.Thread").sleep(1000 * &s);

Batch Scheduling

The PeopleTools process scheduler allows you to run jobs on a regular schedule. This wiki page goes through the two ways to set up batch scheduling using recurrences.

Batch Scheduling Terminology

However, before setting up batch scheduling, there's some terminology that you'll need to be aware of:

A job is a collection of one or more processes that run together.
A recurrence defines a regular event. For example a daily recurrence would be for every day of the week. A recurrence includes a start request date and time, an end request date and time, and repeat information (e.g. repeat every 1 hour for 24 hours).
A schedule jobset definition is a job that has been scheduled to run on a regular basis (with a recurrence) using specifc run control(s) and parameters.

The navigations you'll need to know are:

PeopleTools > Process Scheduler > Jobs
PeopleTools > Process Scheduler > Recurrences
PeopleTools > Process Scheduler > Schedule Jobset Definitions

Recurrences through the Process Scheduler

The first (and simplest) way to schedule something to run on a regular basis is to select a recurrence when you schedule a process through the process scheduler. For example, if you wanted to run the system audit process through system process requests each day, navigate to PeopleTools > Process Scheduler > System Process Requests then select the system audit process to be run. At the process scheduler request page, you would then select a recurrence. Here's an example screenshot.

You can use this technique for any process you can run through the process scheduler and gives you access to this page first.
Now if you go to the process monitor you'll see your SYSAUDIT process running as per normal, but it now includes a green recurrence icon after the process name that looks like this:

Note that you will probably end up with two items in the process monitor. The first item is your request running today and the second will be the queued request for tomorrow (or sometime in the future depending on your recurrence). You can see this in the screenshot above. Process instance 2173557 was initiated on 05/01/2009 and has run to success and posted. Process instance 2173558 will run on 06/01/2009 at 12:02:00 AM and is currently set to queued.
You can also click on the green recurrence icon to manage the process further. This gives you:

The ability to hold (suspend) the recurrence. This will change the run status to Hold.
The ability to stop the request recurring
The ability resume the recurrence if it has been stopped or set to hold.
A request list with a history of the process requests that have already run through this recurrence and if/when the process will recur next

Using this method works well for simple recurrences for a single process or jobs. If you want more advanced options such running a job with difference run controls for each process then you'll need to use a schedule jobset definition.

Schedule Jobset Definitions

A schedule jobset definition needs to run on a job. So the first step might be to create a job with one or more processes in it. For this example, i'm using the delivered All Process Types ALLTYPES PSJob. This runs a COBOL, a Crystal Report and a SQR report and is available through system process requests.
Once you have a job, navigate to the Schedule JobSet Definitions page (PeopleTools > Process Scheduler > Schedule Jobset Definitions) and select Add a New Value. For this example:

The schedule name will be All Types Example
The job name is ALLTYPES (and needs to exist first)

By default a new jobset definition has a status of inactive. You'll want to leave it inactive until you know it is working correctly - especially for more volatile processes. You'll need to also specify a run control ID. Note that this is user specific - the user that creates the schedule jobset is the one that the processes will be running under. For this reason, a lot of organisations create their own batch scheduling user (e.g. PSBATCH) for the specific purpose of running batch processes and jobs. All run controls, and schedule jobset definitions are then configured using that user. In this example, I'm just using the default run control ID which is my operator ID as this will always exist.
The next step is to set a begin date and time. This will be the date and time from which the job set will begin. You'll also need select your recurrence (daily for this example). You can also specify a process scheduler server if specifically required. However, it might be better to let PeopleTools decide what server to use. In this example, the ALLTYPES job includes a crystal report that will need to run on an NT server, however, the other processes should run on a Unix server.

Next, you'll need to specify schedule jobset items. This is effectively where you choose the run controls to be used by your job/processes. Use the left hand side tree view to expand your job (click on the icons) and then for each of the three processes, specify a run control ID (i'm using my operator ID). Note that these run control IDs need to already exist and be configured before you set them up in the job set items.

Also, for the ALLTYPES job, the crystal process (XRFAPFL) needs to have its server settings changed so that th server option is Specific Server and the server name is PSNT. This is because crystal reports can only run on Windows process schedulers servers.

The final tab, schedule jobset requests, is for informational purposes. It tells which requests have already been processed and when the next one is queued to run.
Before you set the jobset definition status to active, use the Run Now button to run the job set definition once to check it runs to success. Also note that when you save the schedule jobset definition, it may clear the recurrence name - this appears to be a bug?
Go to the process monitor and you should see the ALLTYPES job running, and hopefully it runs to success for you. Also, you should see the jobset definition calendar icon. Clicking on this icon tells gives you all the schedule jobset requests.
Note that your jobset definition may change status to completed. This indicates that the jobset has finished running and there are no more recurrences for it to process. The jobset will not run again with a status of completed. Change this back to active and ensure that your recurrence settings are correct.

Recurrences

In both examples, I've been using the Daily recurrence, which I believe is delivered with PeopleTools. However you might want to create your own recurrence with a different recurrence pattern, and request settings.
To do this, navigate to PeopleTools > Process Scheduler > Recurrences, add a new value and give it an informative name.
You'll need to choose between the options, schedule next recurrence when current request is initiated OR prior recurrence has completed. This essentially specifies whether to schedule the future recurrence before or after the current recurrence process request has completed. You can choose a regular recurrrence pattern, or use the customize dates option to specify one or more values in the run on specific dates grid. For example if you have a process that runs on two specific dates in a given calendar year, you can set them up this way.
The start and end request options are straightforward. The repeat is used to repeat a process within the recurrence pattern. For example if you might want your process to run every day, however, on each day you may also want it to run every 6 hours for the 24 hours of the day. That is, run 4 times each day (24 hours in day / repeat every 6 hours = 4 repeats).
The do not schedule processes missed from the recurrence pattern prevents the process scheduler from playing catch up if the start request date/time is before the current date/time. If you want processes to play catch up, then leave this unchecked, otherwise, set it to checked.
This article explains how to check for users who have set up their own recurrences. This can cause quite a problem if the user's profile is deleted but their recurrences are left in the system.

Tuesday, April 29, 2014

Access Log – PeopleSoft

I came across a requirement where I needed to find out when a user had last logged into PeopleSoft. The record PSACCESSLOG was useful, it stores all of the following:

PSACCESSLOG
OPRID: The users operator ID
LOGIPADDRESS: The users IP address
LOGINDTTM: A timestamp showing when the user logged in
LOGOUTDTTM: A timestamp showing when the user logged out
PT_SIGNON_TYPE: The signon type

Find Last Login Date for User:

SELECT *
FROM PSACCESSLOG
WHERE oprid = '<OPERATOR ID>'
ORDER BY effdt DESC;

Find all logins since 1st March 2014:

SELECT *
FROM PSACCESSLOG
WHERE TO_DATE(CAST(logindttm AS DATE), 'dd/mm/yy') >= TO_DATE('01/03/2014', 'dd/mm/yy');

PSACCESSLOG - Login History in PeopleSoft

View Last Query Executed – Oracle

Just a quick post on how to view recent queries run into an Oracle database:

 
SELECT sql_text, sql_fulltext
  FROM v$sql
ORDER BY last_load_time DESC

How to Edit a Message Catalog Definition – PeopleSoft

Editing a message catalog definition in PeopleSoft is pretty straight forward, simply browse to the following menu path:

PeopleTools > Utilities > Administration > Message Catalog

Enter your message set number into the prompt. Note that if you do not know your message set number you can use the following to find it:

SELECT *
FROM PSMSGCATDEFN
WHERE MESSAGE_SET_NBR = '12345'
      AND MESSAGE_NBR = '12345';
 
SELECT *
FROM PSMSGCATDEFN
WHERE MESSAGE_TEXT LIKE '%what your message is%';

Once you have your message simply edit the text and description appropriately then save!

Message Catalog Entries Missing From Your Project

When you're working on a large project in Application Designer, you can end up creating or updating a number of message catalog entries. Because this is done outside of Application Designer, there is a good chance you might end up leaving some of them out of your project.
The following SQL is my attempt to find message catalog entries that were modified in the last 14 days for a particular message set and to check whether or not they exist in your project definition. Obviously this isn't going to be accurate in all cases, but scanning through the results of the query may help you identify message catalog entries that were missed.
To use this SQL:

Specify your message set number (you might want to check more than one message set, or even the whole message catalog?)
Specify how far back you want to check for changes (default is 14 days)
Specify the name of your application designer project

select
    MESSAGE_SET_NBR,
    MESSAGE_NBR,
    MESSAGE_TEXT,
    LAST_UPDATE_DTTM
from 
    PSMSGCATDEFN MCD
where 
    MESSAGE_SET_NBR = 99999
    and trunc(LAST_UPDATE_DTTM) >= sysdate - 14
    and not exists (
        select  1
        from    PSPROJECTITEM
        where   PROJECTNAME = 'YOUR_APP_DESIGNER_PROJECT'
        and     OBJECTTYPE = '25'
        and     OBJECTVALUE1 = MCD.MESSAGE_SET_NBR
        and     OBJECTVALUE2 = MCD.MESSAGE_NBR
    );

Thursday, April 24, 2014

Drill Down Queries

PS Query in PT 8.51 now supports a new expression type called as Drilling URL. This expression type allows a user to drill down on values displayed in the result. Drill down can be done to a child query, external URL, component or attachment.
Following steps illustrate the drilling url feature using parent - child queries.

Create two queries, a parent query (MENU) and a child query (PANEL), so as to speak. My parent query, queries the PSMENUDEFN and PSMENUITEM tables to list the menuname, component etc and then child query lists the pages in a component (from PSPNLGROUP).
I will be using the drilling url expression and join Panelgroup between the two queries.
My child query (PANEL) takes a prompt value for the field PNLGRPNAME
In my parent query (MENU) I add a new expression of the type Drilling URL and choose the "Query URL" link, which takes me to the Query URL Widget
Specify the child query name which is PANEL, and then assign the prompt key, which is
PNLGRPNAME.
Under "Map URL to Query Columns" grid I selected the PNLGRPNAME column as I want this column to be hyperlinked.
The expression text will look like below.

'/q/?ICAction=ICQryNameURL=PUBLIC.PANEL&BIND1=%B.PNLGRPNAME%:B.PNLGRPNAME'

Now save and run the parent query and the results will be displayed like so.

Clicking on the component hyperlink will open the child query results like so.

Drilling URLs are supported only in RTF XMLP templates.

To use a drilling URL in a XML Publisher report:

Create the query with the drilling URL defined as a field. When you run the query the URL to the child query should be displayed in the results like so.

2. Create an RTF template. In the RTF template map the field that contain drilling URL.

Highlight the field where you want to place the drilling URL.
Select Insert (from the Word menu), Hyperlink or use Ctrl+K.
In the Insert Hyperlink dialog box, enter the URL link in Address field.
Each URL link should be defined as {URL_FIELD}, where URL_FIELD is the unique field name for the expression that contains a specific drilling URL.

Use the Target Frame push button to select how this URL link will be opened, either in the same window or in the new window

Note. The URL value does not need to be added to the report, as long as the unique field name (data file that contains the URL) is mapped to a field in the report.

How to use SQLExec with a criteria using IN or NOT IN

SQLExec is one of the most powerful peoplecode command to execute your SQL statement. Of course, there are several other ways to accomplish the same thing, such as CreateSQL, GetSQL, etc...If you ask me what I prefer, it really comes down to whether I need to loop through multiple rows vs single row from table. If multiple rows, CreateSQL and GETSQL will do a fantastic job, otherwise SQLExec is very efficient in fetching single row data.

Now and then, you will use IN or NOT IN SQL criteria to fetch a row from table in peoplecode. This example will use SQLExec technique.

Let say you have a string of values like this: 'A','B','C'. You want to use insert this string as a parameter in the SQLExec.

Bad Example:

Local string &in_values = "'A','B','C'";
Local string &out_val;

SQLExec("Select min(field1) from ps_table_1 Where field1 NOT IN (:1)", &in_values, &out_val);

Result: Bad

Good Example:

1 Local string &in_values = "'A','B','C'";
2 Local string &sql_cmd;
3 Local string &out_val;
4 
5 &sql_cmd = ("Select min(field1) from ps_table_1 Where field1 NOT IN (" | &in_values | ")"; 
6 
7 SQLExec(&sql_cmd, &out_val);

Result: Good

Component Error "You Are Not Authorized..."

Here is a common peoplesoft component error "You are not authorized to access this component. (40,20)"

In order to try to resolve this, Check the following:
1. Component in the menu
2. Access to component in the permission list
3. permission list in the role
4. the role assigned to the user profile
5. clear browser cache, close the browser, reopen browser
6. clear and reboot app server cache
Ya..Ya..all checked, but why it still errors out??? I know your frustration, and don't bang your head against the table yet.

This is what I found today that I want to add it into the list:

7. non-existing permission list assigned to the role.

What!! How could it be possible?

The only way that non-existing permission list in the role is by doing a project migration from database to database. You may think the permission list in the project, but apparently not.

If you were like me, I do not add roles, permission lists, menu in my project. Unless you are the sole developer for the system, I would encourage to stay away from it.

Good Luck!

Code Tip:
Here is a SQL to check if there is non-existed permission list which is still attached to role:


Select R.rolename
, RC.classidFrom psroleuser R
      , psroleclass RCWhere RC.Rolename = R.Rolename
    And not exists
          (Select 'X' from psclassdefn C
           Where C.classid = RC.classid)
    And R.roleuser = '[--OPRID--]'

Note: Replace [--OPRID--] to the actual user oprid.

How To Customize the PeopleSoft Header Logo in PeopleTools 8.53

To enable your establishment’s branding and help users identify each instance – you may want to customize the branding after you do PeopleTools 8.53 upgrade. Easiest way to customize the PeopleSoft header logo in PT 8.53 is to save your company’s logo/image with the same name that is being referred in PIA by the current PeopleSoft Stylesheet.

The current/default Stylesheet can be identified in the following places,
- PeopleTools->Utilities->Administration->PeopleTools Options
- PeopleTools->Portal->Portal Utilities->System options.
- PeopleTools > Portal > Portal Utlities > Registry Options
There are different StyleSheets for PeopleSoft Application and each style sheet refers to a different image. This is controlled by ‘BrandingBase‘ Application Class under ‘PT_BRANDING’ App Package.
Below are the details of style sheets with default image being referred.

If your Application version is 9 then the default stylesheet would be PSSTYLEDEF, NEW_PS_LOGO is the image name.
If your Application Version is 9.1 then the default stylesheet would be PSSTYLEDEF_SWAN, PT_ORACLELOGO_SWAN is the image name.
If your Application Version is 9.2 then the default stylesheet would be PSSTYLEDEF_TANGERINE, PT_ORACLELOGO_CSS is the image name.

Steps to customize the PeopleSoft header logo

Option #1
1) Launch app designer(ENG) and create a copy of the delivered image
2) Save your new image with the same name that is being referred in PIA by the current PeopleSoft Stylesheet.
3) Stop your App and Web servers.
4) Clear the cache for App and Web servers. Restart them both.
5) Log into PIA again with clean browser cache.

Alternate Option #2

1) Log into PIA(ENG) (8.53.xx and your current stylesheet is PSSTYLEDEF_TANGERINE)
2) Go to PeopleTools > Portal > Branding > Branding objects. Go to Image tab and click ‘Upload Image object’.
3) In the Modal window,
Name: PT_ORACLELOGO_CSS
Description: Oracle header logo
Image Type: JPG files
Click button “Save”
3) Stop your App and Web servers.
4) Clear the cache for App and Web servers. Restart them both.
5) Log into PIA again with clean browser cache.
Note that for Multilingual setup, you will have to repeat the above steps for each installed language.

PeopleSoft Query Security - Explaination

If a PeopleSoft record built through Application designer is not listed in PS-Query tool for reporting, it means that the particular PeopleSoft record is not available for the user to access through PS-Query.

For example, one has built a custom record PS_Z_ABC through Application Designer, then the record - Z_ABC won't be available under for PS-Query for any user. The record has to be added to a query tree.

Why to add a record to a Query Tree?How to add a record to Query Tree?
Well, PeopleSoft Queries are used to build SQL queries and retrieve information from application tables. For each PeopleSoft Query user, you can specify the records the user is allowed to access when building and running queries. This is done by creating query access groups in PeopleSoft Tree Manager and then assigning users to these query access groups with PeopleSoft Query security. PeopleSoft Query security is enforced only when using PeopleSoft Query; it does not control runtime page access to table data.

Once a decision is made on what kind of users(based on Permission lists) must have access to the record, then the record has to be attached to the query tree associated with the permission list. This can be done under:
PeopleTools > Security > Query Security > Query Access Manager
Most often, it is better to run Query Access Cache process immediately after making changes to any query tree.

How to associate a Query Tree with a user/OPRID?
This is done based upon the Permission lists attached to the users' roles. To associate a Permission List with a particular Query Tree, at first place the query tree with required access groups must be created. Once a query tree with required access groups is available then associate this query tree with a permission list under: PeopleTools > Security > Permissions and Roles > Permission Lists > Query

In this navigation, click on "Access Group Permission"; then chose the Tree name and the access group in the tree for which you want to give access to.

How to identify which user/permission list has access to which tree?

We can verify this by clicking "Access Group Permission" in the navigation: PeopleTools > Security > Permissions and Roles > Permission Lists > Query

Alternatively, we can check this through database as well. PeopleSoft has a delivered record named - SCRTY_ACC_GRP which maintains the permission lists and its associated query trees along with the access group and access properties. The structure of this record PS_SCRTY_ACC_GRP is as below:

1. CLASSID Key field
2. TREE_NAME Key field
3. ACCESS_GROUP Key field
4. ACCESSIBLE Non-Key field with default value as 'Y'

Sample People Code for getting the DEPT ID based on the ORG ID

Below is the 3 level rowset peoplecode for getting the Department ID prompt values when we select the Organization ID from prompt table.(i'e when we select the Organization ID from the prompt table then the departments under the particular Organization should only display)

Local Rowset &RS0, &RS1, &RS2;
& RS0 = GetLevel0();
& RS1 = &RS0(1).GetRowset(Scroll.SRI_ORG1_TBL);
For &I = 1 To &RS1.ActiveRowCount
& ID = &RS1(&I).GetRecord(Record.SRI_ORG1_TBL).GetField(Field.SRI_ORG_ID).Value;
& SQL = CreateSQL("SELECT DESCR FROM PS_EXT_ORG_TBL WHERE EXT_ORG_ID=:1", &ID);
While &SQL.FETCH(&DESCR)
& RS1(&I).GetRecord(Record.SRI_ORG1_TBL).GetField(Field.SRI_ORG1_NAME).Value = &DESCR;
REM WinMessage(&DESCR, 0);

& RS2 = &RS1(&I).GetRowset(Scroll.SRI_DEPT1_TBL);

For &J = 1 To &RS2.ActiveRowCount;
& DI = &RS2(&J).GetRecord(Record.SRI_DEPT1_TBL).GetField(Field.SRI_ORG_ID).Value;
& SQL1 = CreateSQL("SELECT ORG_DEPARTMENT FROM PS_ORG_DEPT WHERE EXT_ORG_ID=:1", &DI);
While &SQL1.FETCH(&ORG_DEPARTMENT)
REM SQLEXEC("SELECT COUNT(EMPLID) FROM ");
If &ORG_DEPARTMENT = "" Then
& RS2(&J).GetRecord(Record.SRI_DEPT1_TBL).GetField(Field.SRI_DEPT_ID).Value = "";
WinMessage("There are no Departments under this Organisation ID", 0);
Else
& RS2(&J).GetRecord(Record.SRI_DEPT1_TBL).GetField(Field.SRI_DEPT_ID).Value = "";

rem WinMessage(&ORG_DEPARTMENT, 0);

End-If;
End-While;
End-For;
End-While;
End-For;

Disabling Peoplesoft radio button using JavaScript

From a development standpoint, I need to disable one of three radio buttons based on a condition. The dilemma here is that a radio button is using the same field, so using field property displayonly = True or enabled = False won't work in this sense.

How do I do this:

To something like this (note: catch up below is grayed out):

Here is a step by step work around to disable one radio button using a simple javascript.

We need to set a page field name from the radio button properties. I named this one "ADJ_CATCHUP".
Place HTML area to the bottom of the page to make sure that the page had been fully rendered before the javascript being called)
Assign Record and Field into the HTML area properties
Create a new HTML definition
Note: (I use bind variable so I can reuse this HTML code with a different field name or property(true/false)
Place the peoplecode below where you need it. In my case, I put it in the page activate.
If &_has_paid_amounts is True, then it will disable catch up radio button. Otherwise, enable it.
That should be it.

Kudos to Mr. Putra for providing insite into how this is accomplished. (http://eliandokp.blogspot.com/2013/01/disable-peopesoft-radio-button-using.html)