%P(1) in SQL object

The %P(1) refers to the first parameter of SQL definition.

Example: Below view retrieves EMPLID,NAME and EMAIL_ADDRESSES from two tables PS_NAMES and PS_EMAIL_ADDRESSES.

SQL Definiton VC_EMP_NM => %P(1).EMPLID,%P(1).NAME,%P(2).EMAIL_ADDRESS

VC_EMP_NM is the SQL definition which is referenced in SQL Editor as
select %SQL(VC_EMP_NM,NM,EML) from PS_NAMES NM, PS_EMAIL_ADDRESS EML
where NM.EMPLID=EML.EMPLID

NM -- 1st Param nothing but alias of PS_NAMES record
EML -- 2nd Param nothing but alisa of PS_EMAIL_ADDRESS record

which is resolved into
select NM.EMPLID,NM.NAME,EML.EMAIL_ADDRESS from PS_NAMES NM, PS_EMAIL_ADDRESS EML
where NM.EMPLID=EML.EMPLID

Below is the snapshot.

 

PeopleSoft Edit tables

In PeopleSoft , we have different ways to edit  (Dictionary meaning ,To modify or adapt so as to make data we enter suitable or acceptable).

Edits helps user to select valid data from a list of values. Below are the types of Edit available in PeopleSoft

1. Prompt Table With NoEdit
2. Prompt Table With Edit
3. Yes/No Table Edit
4. Translate Table Edit

1. Prompt Table With NoEdit
    Displays list of values for the user to select. If user enters some other value which is not present in the list
    then the new value is accepted by the system.

2. Prompt Table With Edit
    Displays the list of values for the user to select. User had to select only those values. If some other value is
    entered the system throws an error saying, "Invalid Value".

3. Yes/No Table Edit
    This Edit can be used for checkbox. By Default it will be checked (Y). For Example, In any of the
    application pages, if there is an Address details which needs to be provided we can use this option.
    Address same as Permenant Address? 

4. Translate Table Edit
    This will list the users to select data from dropdown. The length of the field should not exceed 4 char.
    The translate values for this type of field are stored in Peopletools table PSXLATITEM.
    Example: This is used to display Marital Status of an employee etc.

PeopleSoft Signon Process

Peoplesoft adopted the n-tier architecture from 8.x versions onwards.

There are different ways to connect to database.

1. Direct login to database with database userID/Password. (2-Tier)

  
     Below are the ways we connect to database directly.

     Login through Toad/SQL Plus using SYSADM as userID/pwd.    

•      This the the powerful userID.
•      Using this we can do DML/DDL from backend. Usually PSAdmin/Database Admin use these.
•      Sometimes Application Developers do require access in Dev environments.
•      We can also create other Database Userids which have only select access to database.

     Login through App Designer. 

1.      In the Login Window, user has the option to select Type of  login.
2.      There if we select Database say Oracle then we are connecting directly to database.
3.      User enters the credentials,
4.      App designer first connects to database using the connectID provided in the Configuration  Manager.Usually, ConnectID will be people. 
5.     Once connection is successful, the user 'people' issues a couple of select statements on peopletools  Security tables (namely, PSDBOWNER/PSSTATUS/PSACCESSPRFL/PSOPRDEFN) to validate the credentials provided.
6.     Gets the owner name from PSDBOWNER, usually SYSADM
7.     Then checks the tools release.
8.     Retrieves the SYMBOLICID (usually SYSADM1)  for the given UserID from PSOPRDEFN. 
9.     Now gets the ACCESSID/PWD (usually SYSADM) from the PSACCESSPRFL which is linked  to the SYMBOLICID.
10.     Disconnects from Database
11.    Connects using AccessID namely SYSADM/pwd. This UserID is powerful as it had full access to  the database.
12.    Now App Designer opens and is awaiting user action.

      Login through Application Server.

   

•       This happens when we boot the server using PSADMIN utility.
•       The Appserver Config file has the details of UserID/Pwd and also the ConnectID/Pwd. 
•       So initial connection happens through the ConnectID  namely 'people' , and then validates the credentials against the Peopletools Security tables as mentioned above. 
•       Once validated, it disconnects and connects again using UserID/Pwd present in the file.
•       This way a persitent connection is made to the database by the Application server. 

Component Properties: Difference between Search Record and Add Search Record

Below are the Search records we can add through Component properties.


Search Record Name
 Displays the component's default search record when you access the component in update or display mode.

Note. You can assign an override search record to a component at the menu level. If the component uses an override search record, the search record displayed in the Override Search Record will be different from this one and you should search it instead.

Add Search
 Displays the component's search record when you access the component in add mode.


Source of above information: Peoplebooks

When to use Add Search Record?
 In a case where we want to control the data that should be added or for a requirement where we add a counter for the ID.

Lets see a simple example where the Search record and Add Search record is used.

The below Events page helps us to create different types of  Campus Events.

Let's first see how this page works.

Search shows us the list of Campus Events available.
Lets see the data for Event ID 000010005.

 Now, Lets Add a New Event ID. In this case , we use a different Search record because we need to setup a new Event ID.

  Lets see how we can do this in App Designer.

  Below is strucuture of these search records.
 The Standard Search record is a view which retrieves data from primary record of the component.
 For Add Search record, Primary record of the component is used. The EventID is set to '0'  at fielddefault peoplecode. Once the page is saved, the peoplecode in the SavePrechange event will get the most recent Event ID and then adds plus 1. (i.e if recent EventID is 121 ,then new Event ID will be 121 +1 = 122)

Difference between key , duplicate order key , Alternate Search key

Record Field Properties:

Difference between key , duplicate order key , Alternate Search key

Key:

•     Helps us in uniquely identifying a row of data.
•     The primary key must be the first field in the record, followed by the next most important key, and   so on.
•     Key fields are followed by duplicate order keys, in order of importance, then by fields that are not keys.
•     Can be used as Search key or listbox item or both.

Duplicate order key

• Allows duplicate values.
              Example. Same Employee having multiple degrees at same date.
• Do not create a related language record with a duplicate order key.
• Can be used as listbox item.

Alternate Search key 

•  This displays the field in the Advance Search and builds indexes on this field. 
•  This will help in fast retrieval of data.
•  Also keep in mind that, as these database indexes consume disk space, mark the field alernate search  key only if it is really required.
•  Alternate key fields can be placed anywhere among regular fields (non-key fields), in any order. They do not need to be grouped.
•  It is displayed in the Advanced search and can be used as Listbox item.

All these 3 keys are mutually exclusive.