Saturday, June 18, 2011

Send Mail Function

REM ****************************************;
REM SEND MAIL MESSAGE;
REM ****************************************;
&FLAGS = 0;
&BCC = "";
&TO = test@abc.com
&CC = "";
&SUBJECT = "Image Test";
&TEXT = "This is a sample mail message.";
&FILES = "/home/psoft/PT846/DVLP/appserv/prcs/DVLP/log_output/Sample.jpg";
REM &FILES = "";
&RETURN_CODE = SendMail(&FLAGS, &TO, &CC, &BCC, &SUBJECT, &TEXT, &FILES, &TITLES);
MessageBox(0, "Debug", 0, 0, "Return Code " | &RETURN_CODE);

Sunday, May 22, 2011

Meta tables for Integration Broker

****************
IB Tables
******************
-- Service
PSOPERATION; --all services
PSOPERATIONLANG; -- language specific descriptions

-- Service Operation Versions
PSOPRVERDFN; -- all the service operations
PSOPRVERDFNLANG; -- language specific descriptions
PSOPRVERDFNPARM; -- holds request / response message information , queue

-- Service Operation Handlers
PSOPRHDLR; -- all the handlers
PSOPRHDLRLANG; -- language specific descriptions
PSOPERATIONAC; -- handler details referring to DMS application class
PSOPERATIONDMS; -- handler details referring to DMS script
PSOPERATIONCI; -- handler details referring to CI's

-- Service Operation Routings
PSIBRTNGDEFN; -- all routings
PSRTNGDFNLANG; -- language specific descriptions
PSIBRTNGSUBDEFN; -- routing alias information
PSRTNGDFNPARM; -- routing transformation information
PSRTNGDFNCONPRP; -- routings specific connector override information
PSOPRGENRTGPARM; -- holds list of auto generated routings

Secondry pages and do modal


A small point about DoModal fucntion and Secondary page.
You know that Secondary page can be called from a hyperlink/Push Button either by 'PeopleCode Command' or by 'Secondary page directly'

1. When we use Secondary page option in hyperlink, it suppreses the FieldChange/FieldEdit events and if we need to do any changes we cannot do. When we are using the Secondary page option, we dont need to insert the secondary page on the main page at the corresponding level it called from. As the Modal page knows where it has to comeback, we dont need to specify it.

2. When we use PeopleCode Command option in hyperlink, as we will be using DoModal PeopleCode and assume that the secondary page is not inserted on the page, the modal page doesnt know where to return its results. So, its mandatory that we need to include the secondary page on the main page where we have this hyperlink (on which the PeopleCode is written). Since, its based on PeopleCode, we can make use of FieldChange and FieldEdits of the hyperlink.

Friday, March 25, 2011

SQL to check Table lock

select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine,
b.program,
b.type,
b.client_info
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;

Find Defintion References

Impact analysis of the existing object plays a major role during development or customization of the PeopleTools objects. Developers make use of the “Find Definition Reference” utility in Application designer to identify the list of impacted / dependent objects. However, this Application designer utility has its own limitations. For E.g. When a record is referenced inside a SQLExec function will not be traced by this utility.

1.There are two records (HEX_TEST & HEX_TEST_REC) in the project (HEX_TEST_PRJ). The HEX_TEST record is referenced inside the SQLExec function in the HEX_TEST_REC.EMPLID.FieldDefault PeopleCode. In such cases when the ‘Find Definition Reference’ for HEX_TEST runs, the HEX_TEST_REC will not be listed as impacted or dependency objects. This may lead to incomplete impact analysis of the record reference. (It can be taken cared by the ‘Find In’ utility but it ends in huge volume of time for Impact analysis)

In such cases, the PeopleCode can be rewritten to pass the record name as parameters to the SQLExec function. This helps the developers to list the record in SQLExec function as impacted object when “Find Definition Reference” runs through the application designer

Who Modified the code last

SELECT A.OBJECTVALUE1 RECORD, A.OBJECTVALUE2 FIELD, A.OBJECTVALUE3 EVENT, TO_CHAR(A.LASTUPDDTTM,’YYYY-MM-DD-
HH24.MI.SS.”000000?‘),A.LASTUPDOPRID FROM PSPCMPROG A
WHERE A.OBJECTVALUE1 = ‘&RECNAME’
AND OBJECTVALUE2 = ‘&FIELDNAME’
AND UPPER(OBJECTVALUE3) = UPPER(‘&EVENTNAME’)

Monitor Process Scheduler

There are many approaches to monitor the PeopleSoft process scheduler. One of the options that come to mind is to have a script running on the OS hosting the process scheduler to monitor the OS process. But having process schedulers running on multiple OS (PSUNX and PSNT) will need development of scripts running on both servers.

My preferred method is to use the below SQL to monitor all the process schedulers running on the database.

SELECT A.SERVERNAME, B.XLATSHORTNAME,B.FIELDNAME,B.FIELDVALUE,TO_CHAR(B.EFFDT,’YYYY-MM-DD’)
  FROM PSSERVERSTAT A, PSXLATITEM B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PSXLATITEM B_ED
        WHERE B.FIELDNAME = B_ED.FIELDNAME
          AND B.FIELDVALUE = B_ED.FIELDVALUE
          AND B_ED.EFFDT <= SYSDATE)
     AND B.FIELDNAME = ‘SERVERSTATUS’
    AND B.FIELDVALUE = A.SERVERSTATUS

DEBUG statement in SQRs

Consider a scenario where you write a code which needs to be executed only when you are trying to debug the program. For e.g. you may want to create a log file in SQR about the rows processed to check the validity of the Program. You may not want this log file to be generated in normal processing. When you want to debug the data you need to be executed.
In such cases SQR provides‘#DEBUG’ commands to perform the action. The Syntax for ‘#DEBUG’ command is:
#DEBUG[x...] SQR_Command

Where ‘x’ represents any letter or digit.

You can write different debug sets in a single SQR program. For E.g. you want only certain part of code to be executed in certain situation. You want some other set of codes need to be executed in other conditions. In such case group the related set of SQR commands with the debug command
Example
In a SQR, Consider the following set of commands. If my SQR errors out and I want to generate the list of Employee Id, Employee Records and Effective Date in log file, I will run the program again with the debug mode of ‘A’. In this case the SQR commands starting with ‘DEBUGA’ will get executed. If I want to trap all the Employee Id, Employee Records and Effective Date in Temp Log table, I will run the program in debug mode of ‘B’. In this case all the errors will be loaded in the temp file and log file will not be generated. If I want to run the SQR program to generate log file and load into Temp table, I can run the program in both mode at same time. #DEBUGA show ‘Emplid :’ $Emplid
#DEBUGA show ‘Empl Rcd :’ $Empl_Rcd
#DEBUGA show ‘Effdt :’ $Effdt



#DEBUGB Begin-Sql

#DEBUGB Insert into PS_Temp_log values (‘Error’,$Emplid,$Empl_Rcd,$Effdt);

#DEBUGB End-Sql



How to run the program I Debug Mode?

In the Processes navigation (‘Home > PeopleTools > Process Scheduler > Processes’) for the specified process, under ‘Override Options’ tab set the Parameter list value as ‘-DEBUG[x]’ with ‘Append’ mode.



Friday, March 18, 2011

Dynamic section calling in App engines

The execution of a PeopleSoft Application Engine starts with the Main section and flows down to other sections which are called from the main section.

For calling a section from within one, we use the call section action. To do this, in the call-section action, we specify the name of the App engine and the section we wish to call. If the section is in the same App engine, then, providing the name of the App engine is optional.

How to use Dynamic Call-Section in App engine?

Often, business logic requires us to call different sections based on occurrence of certain conditions in different scenarios and that too from the same call-section. To enable this kind of logic, we will have to make the call-section dynamic. This is how we can do it.

Dynamic Call-Section State Record

To enable dynamic call-section, we need to have a state record that can support it. The state record, in this case, should have two extra fields – AE_APPLID and AE_SECTION. If you intent to make a dynamic call to sections that are in the same App engine as the calling section, then the state record would be good to go with just AE_SECTION.

PeopleCode for Dynamic Call-Section

Once you have the state record in place, it’s time to set the values for the extra fields – AE_APPLID and AE_SECTION. This should be done before the dynamic call section. The simplest way to implement this is to use a if-else as shown below:
if condition then
AE_APPLID = "AE_ABC_TEST";
AE_SECTION = "SEC_STATE";
else
AE_APPLID = "AE_ABC_TEST";
AE_SECTION = "SEC_CITY";
end-if;

Call-Section Action

This is the final step. Insert a Call-section action into the App engine. Check the Dynamic check-box that states that the call-section is dynamic. On finding the dynamic check-box checked, the processor looks for the values of AE_APPLID and AE_SECTION in the state record and calls the section mentioned in AE_SECTION from the app engine that is mentioned in the AE_APPLID.

If the AE_APPLID is blank, the processor calls the section mentioned in AE_SECTION from the current application engine.


Multiple Reports in SQR


Generating multiple reports in SQR is common these days. Writing SQRs that produce multiple reports have many advantages over the other approach of having multiple SQRs do this job. Here are some of them.

Advantages

Multiple reports in one SQR approach reduces database trips thereby making the reports faster. This is especially true when all the reports are based on the same set of data. However, be cautious not to get totally different SQR reports into one – this can complicate things.
Easily possible to direct multiple reports to multiple printers. Since we are free to have different layouts and printers for different reports, we can easily direct some reports to one printer while some other reports to a different printer.
This results in fewer SQRs which in turn reduces maintenance costs. Well, this one needs no further explanation.
Having seen the advantages, you would be curious to see how we can generate multiple reports in SQRs. This can essentially be achieved in a three step approach.

Declare-Report
When generating multiple reports, SQR mandates us to declare all the reports that we wish to generate. This is done within the Setup section of the SQR. We can use different printers / layouts for different reports. If you are happy with the default printer and layout, just ignore these in your report definition.

In the sample code below, we have declared two reports, both of which use the default layout and printer for simplicity sake.

Begin-Setup

declare-report TEST1
end-declare

declare-report TEST2
end-declare

End-Setup

For-Reports
Standard SQRs that generate just one report would have only one Heading / Footing. However, we need to have a mechanism to print different headings / footings on different reports. This can be achieved using the For-Reports parameter. This is how we use it.

Begin-Heading 1 for-reports=(TEST1)
print ’Test Report One’ (1) center
End-Heading

Begin-Footing 1 for-reports=(TEST1)
page-number (1,1) ’Page ’
last-page () ’ of ’
End-Footing

Begin-Heading 1 for-reports=(TEST2)
print ’Test Report Two’ (1) center
End-Heading

Begin-Footing 1 for-reports=(TEST2)
page-number (1,1) ’Page ’
last-page () ’ of ’
End-Footing

Use-Report
We have reached the final stage – printing the actual report. Before we can print anything, we would need to inform the processor about the report to which we are printing. We use the Use-Report command to set the printing context. This is how we do it.

Begin-Program

use-report TEST1
print 'This text goes into report TEST1' (,1)

use-report TEST2
print 'This text goes into report TEST2' (,1)

End-Program


Load Look up In SQR

It’s common to join tables within SQRs to retrieve data from normalized tables. As SQL statements consume significant computing resources, such joins may be a hindrance to performance of the SQR. Further, as the number of tables that are used in the join increases, the performance decreases.

This rational makes us look for ways to reduce the number of tables used in the join as a means to tune the SQR. This is when Load-Lookup in SQR comes into picture. Using Load-Lookup is a two step process – here’s how to make use of it in your SQR programs.

Load-Lookup
You start by loading the Load-Lookup. This can either be done within the setup section or within a procedure. While done within the setup section, it is only executed once. When within procedures, the execution happens each time the code is encountered.

The code snippet shows how this is used within the setup section. On execution of the below Load-Lookup, SQR creates an array containing a set of return values against keys.

Begin-Setup
Load-Lookup
Name = Product_Names
Table = PRODUCTS
Key = PRODUCT_CODE
Return_value = DESCRIPTION
End-Setup

Lookup
Once we have the first step in place, it’s time to utilize the lookup. The below code will essentially look up for a key (PRODUCT_CODE) in the array and return the return value (DESCRIPTION).

Begin-Select
ORDER_NUM (+1,1)
PRODUCT_CODE
Lookup Product_Names &PRODUCT_CODE $DESC
print $DESC (,15)
from ORDERLINES
End-Select

Multiple Keys / Return_values
Although Load-Lookup doesn’t support multiple keys or return_values, we can do this by concatenating the values using database specific concatenation operators. So if you are on Oracle DB, this would be how you can do it. The return values can later be separated using the unstring command.

Load-Lookup
Name = Product_Names
Table = PRODUCTS1
Key = 'PRODUCT_CODE||','||KEY2'
Return_value = 'DESCRIPTION||','||COLUMN2'

Using where clause in Load-Lookup
To limit the values that are populated in the Load-Lookup array, we can use a where clause as shown below.

Load-Lookup
Name = Product_Names
Table = PRODUCTS
Key = PRODUCT_CODE
Return_value = DESCRIPTION
Where = PRODUCT_CODE > 1000

calling unix scripts from Unix

It often requires us to invoke OS commands from within an SQR. Today we will see how to use the call system command from within the SQR to invoke a UNIX script.

Call System Command
SQR provides the Call System command to issue commands to the underlying OS. The OS then returns a status indicating if the execution of the command that was issued was successful or not. This is how the Call System command is used.

Call System using $cmd_string #status

In the above statement, $cmd_string is contains the command that would be issued to the OS. It’s up to you to decide what needs to be written in the script, the choices are unlimited! The status that the OS returns will be received in #status. On successful execution, the status returned would be 0. This can be used to confirm if the command issued was successful or not.

Another way of doing this would be to hard code the value of the command directly within the Call System command. This would be less flexible though.

Call System using 'rm abcd.lis' #status


Standalone rowsets

When any page in a component is opened, the system retrieves all of the data records for the entire component and stores them in one set of record buffers called the component buffer which is organized by scroll level and then by page level. However, if we need to access data in records that are outside of the component buffer, we need to use Standalone Rowsets. This post will take you through the steps involved in creating and manipulating data using standalone rowsets.

So what are Standalone Rowsets?
It’s a rowset that is outside of the component buffer and not related to the component presently being processed. Since it lies outside the data buffer, we will have to write PeopleCode to perform data manipulations like insert / update / delete.

Creating Standalone Rowset
We use the below PeopleCode to create a standalone rowset. With this step, the rowset is just created with similar structure to that of the record SAMPLE_RECORD. However, the rowset would be empty at this point..

Local Rowset &rsSAlone;
&rsSAlone = CreateRowset(Record.SAMPLE_RECORD);

Populating a Standalone Rowset
Now that we have created a standalone rowset, we need to populate it with date that we need to work on. We can use the below methods to populate data into a standalone rowset.

Fill Method
The simplest way to populate data into a standalone rowset is to use the Fill method. What the below code essentially does is, populate the standalone rowset with all rows from the SAMPLE_RECORD where the TRAINING_ID = ’12345'.

&TRG_ID = '12345';
&rsSAlone.Fill("where TRAINING_ID = :1", &TRG_ID);

CopyTo Method
Another way to populate a standalone rowset it by using the CopyTo method. This method copies like-named fields from a source rowset to a destination rowset. To perform the copy, it uses like-named records for matching, unless specified. The below code copies the content of the above rowset &rsSAlone into &rsSAlone2.

Local Rowset &rsSAlone2;
&rsSAlone2 = CreateRowset(Record.SAMPLE_RECORD);
&rsSAlone.CopyTo(&rsSAlone2);

In case if we had NOT created both the above rowsets from the same record, we would have mentioned the complete record names in the fill method as shown below. The below code would copy the contents of similar fields in &rsSAlone into those in &rsSAlone2.

&rsSAlone.CopyTo(rsSAlone2, RECORD.SAMPLE_RECORD, RECORD.SAMPLE_RECORD_2);

Child Rowsets
We have seen a standalone rowset being created usign a single record. However, we can also create one using another rowset. This would be handy to setup parent-child relations. This is how this can be achieved.

Local Rowset &Lvl1, &Lvl2, &Lvl3;
&Lvl3 = CreateRowset(Record.SAMPLE_LVL3_REC);
&Lvl2 = CreateRowset(Record.SAMPLE_LVL2_REC, &Lvl3);
&Lvl1 = CreateRowset(Record.SAMPLE_LVL1_REC, &Lvl2);

The above can also be written as shown below.

Local Rowset &Lvl1;
&Lvl1 = CreateRowset(Record.SAMPLE_LVL1_REC,
CreateRowset(Record.SAMPLE_LVL2_REC,
CreateRowset(Record.SAMPLE_LVL3_REC)));

Related Language Records

As we all know, PeopleSoft is capable of maintaining application data in multiple languages within the same database. This feature is driven by special records called Related Language Records that store language sensitive information in all required languages other than the base language of the system.

Structure of a Related Language Record
Data in Related Language Record has a one-to-one relation with the data in the base record to which it is tagged. So it’s natural that it will have all the key fields that the base record have. Further, it will also have LANGUAGE_CD as a key field.

Creating a Related Language Record
The easiest way to setup a Related Language Record for your base record would be to follow the below steps.

Clone the base record and and save it as <base_record_name>_LANG
Include LANGUAGE_CD as a key
Remove all non-key, non-language-sensitive fields
Associate it with the base record
Associating Related Language Record to Base Record
To associate the Related Language Record to your base record, open the base record in Application Designer and open the record properties. On the use tab, within the Related Language Record field, enter the name of the Related Language Record that you have just created. Save the record!

How Related Language Records Work
Say you have a search record that has a Related Language Record associated with it. When you login to the system in the base language and try searching, the text is retrieved from the base record. However, when you are logged in to the system in a language other than the base language, the system checks for a translation in the related language record. If a translation is found, it is displayed; otherwise, it displays the text from the base record. This logic enables us to selectively translate portions of data in the system while keeping the system functional at all times even if all rows are not translated.

By default, the base language for all PeopleSoft systems is English. However, an admin can change the base language to the desired one via SWAP_BASE_LANGUAGE Data Mover script.

Friday, March 11, 2011

callsection and SQL actions are mutually exclusive

It is due to Commit in the database. It is mutually
exclusive so that there is no inconsistency in the database.
In Brief.....
Usually a commit takes place after the STEP, not after the
ACTION. Both SQL as well as CallSection are Actions. If they
were not mutually exclusive you could execute an SQL (update,
insert or so) and before comitting go to an entirely different
programm where you would work on the same data, change that data
and then come back to the originating program to issue the
commit on a dataset that has changed. I think you can see the
potential errors that can come up.

SQL for finding component navigation

SELECT P3.PORTAL_LABEL L3
, P2.PORTAL_LABEL L2
, P1.PORTAL_LABEL L1
, P.PORTAL_LABEL L0
, P.*
FROM PSPRSMDEFN P
, PSPRSMDEFN P1
, PSPRSMDEFN P2
, PSPRSMDEFN P3
WHERE P.PORTAL_URI_SEG2 = 'component'
AND P.PORTAL_PRNTOBJNAME = P1.PORTAL_OBJNAME
AND P1.PORTAL_PRNTOBJNAME = P2.PORTAL_OBJNAME
AND P2.PORTAL_PRNTOBJNAME = P3.PORTAL_OBJNAME
AND P.PORTAL_NAME = P1.PORTAL_NAME
AND P1.PORTAL_NAME = P2.PORTAL_NAME
AND P2.PORTAL_NAME = P3.PORTAL_NAME

Trace Options in Peoplecode

Tracing online People Code and SQL


1. Select Trace at the login screen or add “&trace=Y” to the login URL OR - To set trace flags, click here (use this link.
2. Choose the options in the tracing screen and login.
3. Perform the actions you wish to record.
4. Once complete logout and retrieve the file from the /appserv//LOGS directory. 
The trace file name will include your login name.


Tracing online PeopleCode and SQL (Alternative)

1. Login to PeopleSoft and navigate to PeopleTools > Debug.
2. Choose the options and save.
3. Perform the actions you wish to record.
4. Once complete logout and retrieve the file from the
/appserv//LOGS directory. The trace
file name will include your login name.


Application Engine Tracing
1. Open the process definition
2. Select the overrides tab.
3. Select Append on the parameters field.
4. Enter -TRACE 131 -TOOLSTRACEPC 3596 -TOOLSTRACESQL
143
5. Save the record.


SQR Tracing
1. Open the process definition
2. Select the overrides tab.
3. Select Append on the parameters field.
4. Enter –DEBUG[ABC]… (replace ABC with the debug letters
provided in the sqr, sqc source).
5. Save the record.


Cobol Tracing
1. Open the Cobol SQL process type for the platform you are running on.
2. Enter 255 in the command line field between two forward slashes “//”.
3. Save the record.
%%PRCSNAME%% %%DBTYPE%%/%%DBNAME%%/%%OPRID%%/%%OPRPSWD%%/%%RUNCNTLID%%/%%INSTANCE%%/255/%%DBFLAG%%
4. Run the process and retrieve the COBOL sqltrace from the process monitor.


Tracing Settings
1. Both the process scheduler (psprcs.cfg) and application server (psappsrv.cfg) config files allow for permanent tracing settings as well as setting up trace Masks. psprcs.cfg - /appserv/prcs// psappsrv.cfg - /appserv//

2. Trace Masks are setup to limit the amount of tracing allowed when a user wants to trace through the front end package (see all methods
above).

3. LogFence allows for lower and higher detail tracing with regards to application server standard logging. This is ideal for identifying problems on the application server.

4. To enable the trace link in the Sign on page.
    People-tools > Web-profile.
 
   Web profile > Debugging Tab -> Check the Option - Show Trace Link at Signon
  

Application Engine Steps

A few things about Actions.

1. There is a specific order in with Actions get executed. The order in which you create them does not matter.
1. DO when ( like a if statement.) If the sql returns 1 or more rows, subsequent actions are executed just once.

2. Do While ( Loop. Will exceute all subsequent actions once for every row returned by query.

3. Do SELECT ( Like a for loop. Will execute once for every row returned. )

4. Peoplecode

5. Call Section:

6. SQL

7. Log Message

8. Do Until: Will execute subsequent actions till no rows are returned by the query. Will executed at least one. If if no rows returned the first time, it will executed actions at least one time

Aborting an App Engine

How do you get an AE program to abort?

In certain situation, you want to stop processing and fail the program ?

There are certain places where you can accomplish this:

1. SQL Action with On Error set to Abort.
Other values are
Continue: Continue processing. It will continue processing the next action within the step.

Skip step: Skip this step and go to the next.
Section break: Stop processing the current section and pass control back to calling section.


2. Peoplecode Action with OnReturn set to abort and exit(1) fired within the peoplecode on encuntering the error condition.
3. SQL Action which returns no rows can be flagged to Abort.


Other possible value for Peoplecode On Return are Skip Step, Break.
With break, subsequent steps in section are not processed and control is returned to calling section.
With skip step, the existing step and ( subsequent actions ) are skipped and control is passed on to the subsequent step.

Tuesday, March 8, 2011

Peoplesoft Meta Tables

I have compiled a list of  important and handy Meta tables in Peoplesoft. I found the list very useful and would refer it often. So here is the compilation..

 PeopleSoft Projects:

1) PSPROJECTDEFN table stores information about projects created in Application Designer.
2) PSPROJECTITEM table stores objects inserted into your Application Designer project.

Portal Structure
1) PSPRSMDEFN is a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component.

2) 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 Tables1)PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values.
2) PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).


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


2) PSRECFIELD: Stores records with all their fields (sub-records are not expanded)

3) PSRECFIELDALL: Stores records with all their fields (sub-records are expanded)

4) PSINDEXDEFN: Contains 1 row per index defined for a table.

5) PSKEYDEFN: Containes 1 row per key field defined for an index.

6) PSDBFIELD: You got it, stores information about fields.

7) PSDBFLDLABL: Stores field label information.

Process Definition Table(s):
1) PS_PRCSDEFNPNL: Stores the process definition name, process type(sqr report, application engine...), and the component name associated with the process definition.

2) 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
1)PSMSGCATDEFN: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.

2) PSMSGCATLANG: language table.

Menu Tables1) PSMENUDEFN: Store Menu related information. No related component info on this table.

2) PSMENUITEM: List the menu with all components attached to it.

Component Tables1) PSPNLGRPDEFN: Stores component related information only.

2) PSPNLGROUP: This table will give you information regarding a specific component along with the names of pages attached to it.

Pages1) PSPNLDEFN: Stores pages definitions.

2) PSPNLFIELD: Stores all items used by each page definition.

Security1) PSPRSMPERM: Portal Structure Permissions.

2) PSAUTHITEM: Page Permissions. This table stores the information about the page level access for a permission list.

3) PSROLECLASS: Role Classes table. A many to many relationship table between Roles and Permission Lists.

4) PSROLEDEFN: This table stores information about Peoplesoft Role definitions. Users get permissions to PeopleSoft objects through Roles, which are assigned Permission Lists.

5) PSROLEUSER: This table stores information about the Users in Peoplesoft and the roles assigned to them.

6) PSCLASSDEFN: Permissions List definitions table. Permission list name can be found under Field Name CLASSID.

7) PSOPRDEFN: Users/Operator definition table. This table stores information about PeopleSoft users. This is the core table for User Profile Manager.

8) 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

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

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

PeopleSoft Query Tables1) PSQRYDEFN: Stores query related info.

2) PSQRYFIELD: Stores all fields used in a query (both the fields in the Select and Where clause).

3) PSQRYCRITERIA: Stores criteria query fields. You can get the name of the fields by joining the PSQRYFIELD table.

4) PSQRYEXPR: Stores query expressions.

5) PSQRYBIND: Stores query bind variables.

6) PSQRYRECORD: Stores all records used in all aspects of query creation

7) PSQRYSELECT: Stores all SELECT requirements by select type. Example would be sub select, join, ect.

8) PSQRYLINK: Stores the relationships to child queries.

9) PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher)

10) 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 Objects1)PSSQLDEFN: Stores SQL object definitions.

2) PSSQLDESCR: Stores SQL objects descriptions, and description long.

3) 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.

Application Engines1) PSAEAPPLDEFN: Table that stores Application Engine program definitions.

2) PSAEAPPLSTATE: Stores application engine STATE records and a flag to indicate if the record is the default STATE record.

3) PSAESECTDEFN: Application engine section information and also stores last user id to update a specific section.

4) PSAESECTDTLDEFN: AE section along with descriptions and wither the section is active or not.

5) PSAEAPPLTEMPTBL: If your application engine uses Temp tables it will show on this record.

6) PSAESTEPDEFN: Steps in application engines are stored in this table.

7) PSAESTMTDEFN: Stores your application engine actions and along with their types, such as "Do Select" and so on.

8) PSAESTEPMSGDEFN: Application engine message action definition table.

9) AEREQUESTTBL: Application Engine request table behind the AE run control page.

10) AEREQUESTPARM: Application Engine request parameters table behind the AE run control page.

PeopleCode Tables1) PSPCMNAME: PeopleCode Reference table.

2) PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).

Process Request Tables1) PSPRCSQUE: This record contains the process request information to run a process request.

2) PSPRCSRQST: This record contains the process request information to run a process request.

3) PS_PMN_PRCSLIST: A view to list all process requests in the Process Monitor except for "Delete" (runstatus = 2) process requests.

Other Useful Tables1) 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.

2) 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.

3) PSMAPFIELD: Stores Field mapping of Activity
4) PS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online.














Pre build Vs Post build event


It's very common to see people have doubts over the difference b/w these 2 events. Both of them seem to be used to either hide pages or declare component variable.
The main difference really is that PreBuild fires before any peoplecode code event on all the rows and fields in the component such as FieldDefault and RowInit. During the PreBuild event there will not be any data in the component buffer structure other than the search record as it's executing prior to the component build process.That's why it's called PreBuild - Pre means "before".

PostBuild runs after that.
During the PostBuild event you will have access to the data read from the database into the component buffer structure.
That's why it's called PostBuild - Post means "after".
Pre build and post build are most commonly used to hide or unhide pages.Don't get hung up on the fact the both hide/unhide pages. Many PeopleCode functions can be used in more than one event.