PeopleTools Reference: Meta-SQL %Delete

generates a delete statement using the values from a record object

This command will pull the record name, the key fields, and the values for those key fields to generate the delete statement. This will result in the statement deleting one row from the database since the statement includes all of the keys for the record.

Example

Local Record &recJob;
&recJob.EMPLID.value = &emplid;
&recJob.EMPL_RCD.value = &empl_rcd;
&recJob.EFFDT.value = &effdt;
&recJob.EFFSEQ.value = 0;
SqlExec("%Delete(:1)", &recJob);

This post has moved to Wordpress

PeopleTools Reference: Meta-SQL %Update

generates an update statement to change the database to match the values in the record object.


Example

Local Record &recJob;
&recJob.EMPLID.value = &emplid;
&recJob.EMPL_RCD.value = &empl_rcd;
SQLExec("%SelectByKeyEffdt(:1)", &recJob, %Date, &recJob);
&recJob.DEPTID.value = &deptid;
SQLExec("%Update(:1)", &recJob);


This post has moved to Wordpress.

PeopleTools Reference: Meta-SQL %Insert

generates an Insert statement to insert the data from a record object into the database.

Remember that a record object (not a record definition) stores both the record structure (table name and field name) and the field values. Therefore, the system has everything it needs to write an insert statement: insert( ... field list ... ) values ( ... options ...).

Examples

Local Record &myRecord;
&myRecord.KEY1.value = "Key Value";
&myRecord.FIELD1.value = "Field Value";
SQLExec("%Insert(:1)", &myRecord);

This post has moved to Wordpress/

PeopleTools Reference: Meta-SQL %SelectByKey

generates a select statement based on the given record definition.

Remember that a record object (not a record definition) stores both the record structure (table name and field name) and the field values. Therefore, the system has everything it needs to write a select statement: select ... list of fields ... from ... table name ... where ... key field name ... = ... value for that field ... and so on.

Related: %SelectByKeyEffdt

does the same thing except it adds the effective dating criteria to limit it to the most recent rows.

Examples

Local Record &recJob;
&recJob = CreateRecord(RECORD.JOB);
&recJob.EMPLID.value = &emplid;
&recJob.EMPL_RCD.value = &empl_rcd;
&recJob.EFFDT.value = &effdt;
&recJob.EFFSEQ.value = &effseq;
SqlExec("%SelectByKey(:1)", &recJob, &recJob);
WinMessage("Job Code: " | &recJob.JOBCODE.value);


Local Record &recNames, &recOutput;
&recNames = CreateRecord(RECORD.NAMES);
&recOutput = CreateRecord(RECORD.NAMES);
&recNames.EMPLID.value = &emplid;
&recNames.NAME_TYPE.value = "PRI";
SqlExec("%SelectByKeyEffdt(:1)", &recNames, %Date, &recOutput);

This post has moved to Wordpress

PeopleTools Reference: Meta-SQL %SelectAll

Generates a select statement that selects all the fields from a chosen record.

Typically, when you use the %SelectAll, you would use a record object to store the results. That way you don't have to know how many fields your result will return.

Examples

%SelectAll(JOB)

Local Record &myRecord;
&myRecord = CreateRecord(RECORD.MY_RECORD);
SQLExec("%SelectAll(:1) WHERE MY_KEY = :2", &myRecord, "My Key", &myRecord);

Local Record &myInstallation;
&myRecord = CreateRecord(RECORD.INSTALLATION);
SQLExec("%SelectAll(:1)", &RECORD.INSTALLATION, &myInstallation);

This post has moved to Wordpress.

PeopleTools Reference: Meta-SQL %Table

%Table

Replaces with the actual table name of the given record.

Generally, this just means adding a "PS_" to the front of the record name. But, PeopleTools actually checks the alternate table name from the Record Type tab to see if a value is there first.

Two Uses:

• Access a table with a reference or record object rather than embedding a table name in the SQL; PeopleTools will not index the table/record in a string literal.
• Reference the Temporary table in an App Engine; %Table is the only way to reference the table because the table name is assign dynamically at run time.

Examples:

SELECT * FROM %Table(JOB) WHERE EMPLID = :1

SqlExec("SELECT NAME FROM %Table(:1) WHERE EMPLID = :2", Record.NAMES, &emplid, &name);

&sql = CreateSql("SELECT * FROM %Table(:1) WHERE EMPLID = :2", &MyRecord, &emplid);

INSERT INTO %Table(MYTEMP_TAO)
SELECT * FROM PS_MYDATATABLE

This post has moved to Wordpress.

PeopleTools Reference: Meta-SQL DateIn/DateOut

DateIn

Description

Converts a date into the form the database uses.
Used for:

• Where clauses
• Insert/Update

Remember DateIn passes a date into the database.

DateOut

Description

Converts a date into the form that PeopleTools uses.
Used for:

• Select clause

Examples

SELECT %DateOut(MAX(EFFDT))
FROM PS_JOB
WHERE EMPLID = :1
AND EMPL_RCD = :2
AND EFFDT <= %DateIn(:3)

INSERT INTO PS_MY_RNCTL(OPRID, RUN_CONTROL_ID, AS_OF_DATE)
VALUES(:1, :2, %DateIn(:3))

UPDATE PS_MY_TABLE
SET MY_DATE = %DateIn(:1)
WHERE MY_DATE <= %DateIn(:2)

This post has been moved to Wordpress.

PeopleTools Tip -- Cloning a Component Interface

Have you ever tried to recreate a component interface and allow PeopleSoft to default the properties and collections? The hard part is that you have to make sure that it uses the same names as your old one or you will have to change your code.

The following SQL helped me figure how to change my code to refer to the correct fields (ex. effdt or effdt0). I was cloning the delivered CI_JOB_DATA component interface with a brand new customized component interface.

SELECT A.BCNAME, A.BCTYPE || ':' || A.BCSCROLL || ':' || A.BCSCROLLNUM || '->' || A.BCSCROLLNAME LOC, A.BCITEMPARENT, A.BCITEMNAME,
A.RECNAME, A.FIELDNAME,
B.BCNAME, B.BCTYPE || ':' || B.BCSCROLL || ':' || B.BCSCROLLNUM || '->' || B.BCSCROLLNAME LOC, B.BCITEMPARENT, B.BCITEMNAME
FROM PSBCITEM A, PSBCITEM B, PSBCITEM AP, PSBCITEM BP
WHERE A.BCNAME = '<Old component interface name>'
AND A.BCNAME = AP.BCNAME(+)
AND B.BCNAME = '<New component interface name>'
AND B.BCNAME = BP.BCNAME(+)
AND A.BCITEMPARENT = AP.BCITEMNAME(+)
AND B.BCITEMPARENT = BP.BCITEMNAME(+)
AND A.RECNAME = B.RECNAME
AND A.FIELDNAME = B.FIELDNAME
AND (BP.RECNAME = AP.RECNAME OR (BP.RECNAME IS NULL AND AP.RECNAME IS NULL))
AND A.BCITEMPARENT = '<Parent collection name>'
AND A.BCITEMNAME = '<Item name>'

Examples:
<Old component interface name> -- CI_JOB_DATA
<Parent collection name> -- COLL_JOB_EARNS_DIST
<Item name> -- KEYPROP_ERNCD

This post has moved to Wordpress.

PeopleTools Tip: Searching for Records

You can find records that contain a specific field with the find definition references tool in App Designer, but what if you want to know what record contains field1 and field2?

You can go to the database to file that answer:

SELECT A.RECNAME
FROM PSRECFIELDALL A, PSRECFIELDALL B
WHERE A.RECNAME = B.RECNAME
AND A.FIELDNAME = 'FIELD1'
AND B.FIELDNAME = 'FIELD2'

This post has moved to Wordpress.

PeopleTools Reference: Meta-Data -- Object Type

The PeopleTools project-related tables contain a field called OBJECTTYPE. The field is a number field, which means that it cannot have translate values. This list shows what the numbers mean.

You can build the list yourself by running a compare report in Application Designer. The report files contain both the number and the description of the object type. But, this list should save you the time.

00 Records
01 Indexes
02 Fields
03 Field Formats
04 Translate Values
05 Pages
06 Menus
07 Components
08 Record People Code
09 Menu People Code
10 Queries
11 Tree Structures
14 Colors
15 Styles
17 Business Processes
18 Activities
20 Process Definitions
21 Server Definitions
22 Process Type Definitions
23 Job Definitions
24 Recurrence Definitions
29 Business Interlink
30 SQL
31 File Layout Definitions
32 Component Interfaces
33 Application Engine Programs
34 Application Engine Sections
35 Message Nodes
36 Message Channels
37 Messages
38 Approval Rule Sets
39 Message People Code
40 Subscription People Code
42 Comp Interface People Code
43 Application Engine People Co
44 Page People Code
45 Page Field People Code
46 Component People Code
47 Component Record People Code
48 Component Record Field People Code
49 Images
50 Style Sheets
51 HTML
53 Permission Lists
54 Portal Registry Definitions
55 Portal Registry Structures
56 URL Definitions
57 Application Packages
58 Application Package People Code
59 Portal Registry User Homepag
60 Analytic Types
61 Archive Templates
62 XS LT
63 Portal Registry User Favorit
64 Mobile Pages
65 Relationships
66 CI Property People Code
67 Optimization Models
68 File References
69 File Type Codes
70 Archive Object Definitions
71 Archive Templates( Type2)
72 Diagnostic Plug- Ins
73 Analytic Models
75 Java Portlet User Preference
76 WSRP Remote Producers
77 WSRP Remote Portlets
78 WSRP Cloned Portlet Handles

This post has moved to Wordpress

References -- Meta-Data Tables

PeopleSoft stores all of its information/code in database tables. Each of the definitions that you can open in Application Designer can be found in one or more tables in the database. Knowing these tables can be helpful:

SQL Definitions
Object Type (11/15/2006)

Work in Progress...

This post has moved to Wordpress.

Reference -- SQL Definitions Meta-Data Tables

The following lists the PeopleSoft tables that store SQL Definitions:

PSSQLDEFN

Noteworthy Fields
SQLID -- Name of SQL Definition
SQLType -- 0, 1, or 2
SQLText -- Clob field that stores the text of the SQL Definition

This post has moved to Wordpress.

PeopleSoft Information on the Internet

There is more information on the Internet than you might expect. Here are some links that you might find handy.

PeopleBooks

• PeopleTools 8.46
• HRMS 8.9 + PeopleTools 8.46 link from here
• HRMS 8.3 + PeopleTools 8.14 link from here
• Financials 8 SP1 + PeopleTools
• Financials 8.4 + PeopleTools 8.45
• Financials 8.8 + PeopleTools 8.44
  
• Oracle offers their PeopleBooks in PDF form

Instructions

• UF Instruction Guides
• List of How To Guids

WebSites

• ITtoolbox: PeopleSoft Knowledge Base
• PeopleSoft Fans

Blogs (updated 2/19/2007)

• Grey Sparling
• PeopleSoft Corner
• Jim's PeopleSoft Journal
• Driven by PeopleSoft motivated by MicroSoft

Miscellaneous Info(updated 2/19/2007)

• Wikipedia
• "PeopleSoft Enterprise Customers: 10 Things You Can Do Now to Prepare for Oracle Fusion Applications"
• "PeopleTools New Features and Road Map"
• "PeopleTools 8.48 in Detail"

This post has moved to Wordpress.

PeopleTools Tip: Bypassing App Designer Login

Application Designer has command line options that allow you to specify the login information. This would allow you to create a shortcut on the desktop or in the Start Menu that automatically logs into an instance of PeopleSoft.

Just copy your shortcut to App Designer, and change the command line to look something like this:

<PS Home>\pside.exe -CT <Database Type> -CS <Server/Database Name> -CD <Database Name> -CO <Username> -CP <Password>

Options:
PS Home -- you shouldn't have to change this if you copy a shortcut that already works
Database Type -- ORACLE for an Oracle database
Server/Database Name -- The name of the database or instance of PeopleSoft
Database Name -- Same as the Server/Database Name
Username -- The username you want to login with
Password -- The password for that username

Security Warning --
You may want to limit this to Demo instances where no sensitive data exists. You are leaving your password available in plain text and bypassing security. If someone gains access to your computer they have your password and the ability to log into the PeopleSoft system with your security.

This post has moved to Wordpress.

PeopleTools Tip -- App Engine Restart

If you get an error message like this, you either need to restart the specific instance that abended, or remove the restart row from the table.

PeopleTools 8.46 - Application Engine Server
Copyright (c) 1988-2006 PeopleSoft, Inc.
All Rights Reserved
PSAESRV started service request at 14.19.05 2006-09-19
All Processing Suspended: Restart OPRID=PS, RUNID=AppEngName, PI=499 (108,503)
PSAESRV completed service request at 14.19.05 2006-09-19

Disable Restart:
You can disable the restart on the properties of the application engine, but that will not help when the Application Engine has already abended. Remember, the disable restart prevents the app engine from writing to the restart table when the program abends; it does not prevent it from checking the table to see if another instance is waiting to restart.

In other words, once an application has abended, disable restart will not allow you to rerun the program without restarting.

AERUNCONTROL Record:
The restart status is stored in the record AERUNCONTROL. If you receive the message requiring you to restart instead of rerun, then there is a row in this table for the program you are running and the run control that you are using.

Fields:
PROCESS_INSTANCE -- instance that abended
OPRID -- your user name
RUN_CNTL_ID -- run control used when it abended
AE_APPLID -- Application Engine program name

Fixing the Error:
To fix the error, you either have to delete the row from the table or restart the original process instance that abended.
SELECT * FROM PS_AERUNCONTROL WHERE AE_APPLID = '<App Engine Name>'
Update:
Ray pointed out that PeopleSoft has provided an online way to fix the problem. You can do it from the page: PeopleTools > Application Engine > Manage Abends

Deleting the Row:
If you are the only one with an abended program, you can just truncate the whole table:

TRUNCATE TABLE PS_AERUNCONTROL

Or, if you want to just affect your program, you can do something more like this:
DELETE FROM PS_AERUNCONTROL WHERE OPIRD = '<username>' AND RUN_CNTL_ID = '<run control>'




This post has moved to Wordpress.

Tip -- PeopleTools: Tablespaces
What happens when you decide to create a new record and PeopleSoft asks you which tablespace to use, but the tablespace you want is not in the list?

Solution: Insert a Row into the PeopleTools System Catalog table.

As far as I know, PeopleSoft offers no other way of adding tablespaces as options.

Insert Statement:
INSERT INTO PSTBLSPCCAT (DDLSPACENAME, DBNAME, TSTYPE, DBXTSTYPE, COMMENTS) VALUES ('TABLESPACE_NAME', ' ', 'R', 'DMS', 'Your Comments');
More Information on Tablespaces
PeopleTools tables: PSTBLSPCCAT
http://peoplesoft.ittoolbox.com/groups/technical-functional/peoplesoft-fi-l/creating-new-tablespace-in-ps84-489136#


This post has moved to Wordpress.

Tip -- Other Tools: Toad Installation Issue

Just recently, we installed the free version of Toad on a development VMWare server. After logging in, we received two error messages:

Access violation at address 00000000. Write of address 00000000.

and

Error loading SPServer.

I found the fix for these error messages at:
http://www.nabble.com/Error-loading-SPServer-t995120.html

Basically, you must turn off Data Execution Prevention for Toad.

• Go to the System Properties:
  Control Panel, System or Start Key+Pause/Break Key
• On the Advanced tab, click the Settings under Performance
• On the Performance Options, go to the Data Execution Prevention tab
• You should see selected: "Turn on DEP for all Programs and Services except those I select"
• Click the Add button, and browse to the Toad executable.
  Probably: c:\Program Files\Quest Software\Toad for Oracle FREEWARE\Toad.exe
• Choose Ok/Apply for all the Windows and try to run Toad again.

This post has moved to Wordpress.

Tip -- PeopleTools: Application Engine Logging

Application Engines have the ability to write to the "Redirected Terminal Output". But, everything that is written into it must be written with a MessageBox command. That results in a blank line between messages and the infamous message set ids at the end.

If you want any formatting at all you should open a file and use the File object to create the file.

First, declare the variable in every PeopleCode program in your App Engine:

Component File &logFile;
Declare the variable as a component or global variable so that it stays around for the life of the App Engine program. Otherwise, you will have to keep reopening the file to write to it in each PeopleCode step.

Second, create an Init step in the Main section that will open the file:

&logFile = GetFile(GetEnv("PSPRCSLOGDIR") "\logFile.txt", "W", %FilePath_Absolute);

PSPRCSLOGDIR is the environment variable that is set to the path where the files should go. Every file that is put in this path will be copied to the web server so that the user can see it.

You can use %Filepath_Relative in many cases, but sometimes the relative path does not point to the proper folder, particullarly on older PeopleSoft systems. I have had more success with PSPRCSLOGDIR.

Third, close the file in a step at the end of the Main section:

&logFile.close();

Finally, write what you want to the file with:

&logFile.writeline("This is a log statement!");

You have complete control of this file and can make it say what you want it to.


This post has moved to Wordpress.