TRAINING (PS-12) - PeopleSoft Application Development - Part9 (Step-4: Build the SQL Table - Creating Online Views)

Step-4: Build the SQL Table - Creating Online Views

When you create views to use online, you must keep the views synchronized with the database.  Like SQL tables, you must have a record definition for online views because the system looks to the record definitions for online processing rules.  And you must build the view before you can use or reference it online.

If you use a view as the basis for a panel, you select existing information to display on the panel.  So, when you create the record definition for your view, you can clone an existing record definition, delete any fields not used in the view, and proceed to define the view.  Query views are processed the same way as view text, which is defined as part of the record.

To create online views

1.) Clone a record definition.
Open an existing record definition, and then select File, Save As.  Specify the name of the view you want to create.

2.) Delete and add the appropriate fields, so that all columns selected in your view have a corresponding field in the record definition.

3.) Select File, Object Properties,  and select the Type tab on the Record Properties dialog

4.) Select SQL View in the Record Type group box.

5.) Enter your view text in the SQL View Select Statement: edit box.
Make sure the order you list the columns in your SQL SELECT statement matches the order specified in the record definition.

6.) Save the record definition.

7.) Select Build, Current Object to create the view in the database.

8.) Select Create Views checkbox under Build Options, and press Build.

TRAINING (PS-11) - PeopleSoft Application Development - Part8 (Step-4: Build the SQL Table)

Step-4: Build the SQL Table

Using Application Designer, you create several kinds of object definitions that represent database components.  For instance, field definitions represent table columns, and record definitions represent tables, indexes, and views.  The important concept to remember is that the definitions are just the blueprints for the actual, physical database components with which they are associated.  In the context of the whole application development cycle, using the Build process (creating SQL tables, views, and indexes) comes after you've defined new fields and created the record definitions.  After you build the SQL tables you would then begin creating Panel definitions.

After you create and define your field and record definitions, you will need to create the underlying SQL tables that will actually house the application data that your users will enter online in your production environment.

Build uses Data Definition Language (DDL) to construct a physical database component based on the associated record and field definitions you've created.

•  Tables
•  Views
•  Indexes
•  Triggers

Build Menu
You use the Build menu in Application Designer to access the Build dialog by selecting either Current Object or Project.  You can also select the Settings menu option to review the Build Settings dialog to make sure that you have selected all of the appropriate options for your build process.



You use the Build dialog to create and/or run the SQL scripts that define the underlying database components.  Use it to build tables, indexes, views, and to alter existing tables.

Build Scope
There are three different scopes of the Build feature that determine the size of your build project. Depending on what you actually want to build—one record or an entire project—you can determine this by selecting one of the scopes described in the following sections.

• Current Object
  Select Build, Current Object when you want to build or alter the active record definition.
• Build Project
  Select Build, Project when you want to build or alter all the records, indexes, and views in the current project—all the records that appear in the project workspace.  As shown in the following example, you will typically find numerous record definitions in the Build Scope list box.
• Build Selected Objects
  If you do not want to build an entire project or even the current project, you can opt to build just the objects you select in the project workspace.  In some situations you may just want to build a subset of the records in the project that is currently open.  You can select the objects you want to build by pressing the Ctrl button, and selecting multiple records in the project workspace.  After you have selected the appropriate records, then, with a right-mouse click, invoke the popup menu, which offers the Build option. 

To Build all objects in a database, you would create a new project and insert all the appropriate records; you can just insert records of a particular type, such as views.  If  you create another record after the "all records" project is built, you need to manually add the new record to the project.

Build Dialog

You use the Build dialog to create and/or run the SQL scripts that define the underlying database components.  Use it to build tables, indexes, views, and to alter existing tables.

Build Options

The Build Options group allows you to specify what action you want to occur:

• Create Tables
• Create Indexes
• Create Views
• Alter Tables
• Create Triggers

If you select Create Tables or Alter Tables, then, by default, Create Indexes will automatically be selected.

Build Execute Options

The Build Execute Options group offers the following options:

• Build script file: The advantage to using this option is that you can review and update the SQL prior to executing the script.  
• Execute SQL now: The advantage in using this option is it's not necessary to invoke another program to run the SQL; the SQL runs as part of the Build process.  The disadvantage with running the SQL immediately is that you have no opportunity to review the SQL prior to it being committed to the database. 
• Execute and build script: The advantage of this option is that you can review the SQL that the Build process just ran.

Build Settings

Pressing the Settings button from the Build dialog opens the Build Settings tab dialog where you can view or change your options.

The Build Settings dialog offers the following tabs:

• CREATE: Specify your Create Table and Create Views options.
  When you select Create Tables or Create Views, you'll want to make sure that you also specify the desired options on the Create tab on the Build Settings dialog. The following sections describe what the options on the Create tab mean to your build. The following sections describe the options that the Alter tab offers.
  
  
  Table Creation OptionsThese options determine when a table should and should not be created.
  
  (i) Recreate table if it already exists:This options will always drop and recreate a table if it already exists.  Use this option with extreme care because if there is any data in the table that already exists, it will also be dropped. If you select this option, Build prompts you to confirm your specified intention prior to performing any destructive action.  If you don't care about losing the data that resides in the table, then this option is much faster compared to doing an alter.
  
  (ii) Skip table if it already exists:This option will only create those tables that are newly defined. If you want to preserve the data already residing in existing tables or you're just interested in creating the tables that do not already exist, this is the appropriate option.
  
  View Creation OptionsSimilar settings are available for creating views that you use for creating tables.
  
  (i) Recreate view if it already exists:Since views are just logical views of data, it is safe to use this option;  you don't run the risk of losing physical data.  Using this option ensures that all views in the project are rebuilt, whether they need to be or not.
  
  (ii) Skip view if it already exists:If you're only concerned with creating views that do not already exist in the database, this is the appropriate option.  This option is useful if you want to run Build Project on a large project and only a subset of the views in the project need to be created.  This consumes less time than recreating all the views in the project.
  

• ALTER: Specify your options when running Alter Tables.
  If you make any changes to your record definitions, you'll need to make sure the tables in your production database reflect the current definitions to maintain your data integrity.  When you perform an alter, make sure you select the appropriate options on the Alter tab. The following sections describe the options that the Alter tab offers.
  
  
  
  
  Drop Column OptionsThe Drop Column Options are referenced whenever a field is deleted from a PeopleTools record definition and data exists in the database for that column.
  
  (i) Drop column if data present:If you have this selected then the column and data will be dropped and a warning will be written to the build log.
  
  (ii) Skip record if data present:If you have this selected, then the alter for that record is aborted and an error message will be written to the log.  Processing continues with the next record.
  
  Change Column Length OptionsThe Change Column length options are used whenever the length of a character column is reduced in PeopleTools and an alter could result in data truncation.
  
  (i) Truncate data if field too short:If you have this selected and the alter could result in data truncation then the record is altered and a warning message is written to the build log.
  
  (ii) Skip record if field too short:If you have this selected in this case then the alter for that record is aborted and an error message is written to the build log.  Processing continues with the next record.
  
  Alter Any
  PeopleTools assumes you want to perform alters for any modifications made to tables, so, by default, all the check boxes in the Alter Any group are selected.  Alter Any allows for custom alter processing regarding:
  
  > Adds
  > Changes
  > Renames
  > Deletes.  
  
  For example, you have the flexibility to add, change, and rename fields, but not delete any removed columns.  When you complete any other custom external conversion processes, you can then enable the delete processing to drop columns that are no longer defined.
  
  The Change Column length options are used whenever the length of a character column is reduced in PeopleTools and an alter could result in data truncation.
  
  Normally you would have all of these options selected, but during upgrades or operations requiring data conversion steps you may choose a subset of actions.
  
  For Example
  - Perform alter with Adds and Changes selected.
  - Perform data conversion routines to populate the new and changed columns (perhaps from columns that are ultimately to be dropped).
  - Perform alter with Rename and Delete selected.
  
  Alter even if no changes

Select this check box to force an alter, even if no changes were made to the tables. This check box is deselected by default. If selected, the Alter by Table Rename option in the Alter Table Options group box is automatically selected and the Alter in Place option is disabled.

Alter Table Option
When altering tables, the alter process takes care of the indexes, regardless of the Alter Table Options you select on the Alter tab of the Build Settings dialog box.

(i) Alter in Place:
> Only get enabled when Alter even if no changes check box is deselected.
> For the Alter in Place option, the index creation process goes through the Recreated index only if modified option on the Create tab.

(ii) Alter by Table Rename:
> Select to create a temporary table (with changes made to the original table or its fields); the data from the original table is imported into the temporary table. The original table is then dropped and the temporary table is renamed to the original table.
> In the case of Alter by Table Rename, the indexes are dropped automatically when the temporary table is dropped.
> If a table is renamed from the old name to a new name, the indexes that were created for the old table are moved to the new table, but the index names remain in the name of the old table. With Alter by Table Rename selected, the indexes of the old table are dropped before being renamed to the new table name, and the indexes are recreated after the table is renamed to the new name. This way, the index is created in the name of the new table.

• LOGGING: Specify the desired logging level and where you would like the output log file the build process generates.
  
  One way that you can monitor the build process is to review the log files that the build process automatically generates.  Keep in mind that the log file is entirely separate from the script file; do not confuse the two.  How much information that the log file contains is up to you.  You can set up your logging so that all status (both good and bad) appears in the log, or you can specify that just the errors or warnings appear in the log.  This section describes the options you can specify in regards to the Build log file.
  
  You control the settings for the Build log file on the Logging tab on the Build Settings dialog.  Here your specify the desired Logging Level and how you would like to view the log information—from a file or in the output window.
  
  
  
  Logging LevelSelect one of the options in the Logging Level group to specify the detail of information that you want in your Build log output.
  
  (i) Fatal errors only: If you're only interested in seeing the operations that failed.
  (ii) Fatal errors and warnings: To add warnings to that mix by selecting.
  (iii) Fatal errors, warnings and informational: To see everything that processed successfully and all of the errors and warnings.
  
  Logging Output
  The Logging Output group allows you to specify where and in what form you would like to view your log output.  You have the following options:
  
  (i) Log to output window: Writes the same information that would appear in the log file to the Application Designer output window.  Always be aware of the level that you've selected. If there is a lot of detail (high logging level plus many record to process) it may be easier on the eyes to print the logging information to a file, and print it later.
  
  (ii) Log to file: Writes the log to a file.  If you select this option, you have the option to specify a custom location and name for your log file in the Log file name edit box.  If you do not select this option, the edit box will be disabled.
  
  (iii) Log settings: The option writes all of the runtime options to the log.  If you select this option it will not be necessary to rerun a test when you have a problem. Should it be necessary to report a problem with the Build process or troubleshoot, it is important to know the settings that were active.
  

• SCRIPTS: You can choose the format of your script file and the name and location of the file that the build process generates.
  
  If you've elected to build a script file then the build process will generate a script file that contains all of the CREATE and/or ALTER SQL statements so that you can review them prior to running the SQL through another SQL command processor.  If the generated script file meets your requirements a DBA can run the script at a later date.  Build can produce multiple scripts during a single run—one for each build option—depending on the script settings that you specify at run time.  For example, you can specify that the Build process generate a separate script for your tables, views, indexes, and alters, or you can opt to have all the SQL for each action contained in one script.
  
  You use the Scripts tab on the Build Settings dialog to specify where the SQL script is written, whether or not you want multiple scripts generated for each object type, and whether you want previous scripts overwritten.
  
  The following sections describe the options you can specify regarding the build scripts you will generate.
  
  
  
  CommentsThe Write Alter comments to script check box is simply a switch allowing you to either include or suppress alter comments.
  
  Script File Options(i) Output to single file:If you prefer to have all of your statements—CREATE, ALTER, and so on—contained in a single file, then select the Output to single file option.
  
  (ii) Output to separate files:
  If you would like all of your CREATE TABLE statements to be written to one SQL script file and your CREATE INDEX statements to another, then select the Output to separate files option.
  
  File Overwrite OptionsThe File Overwrite Options will indicate whether you want to automatically overwrite or be prompted before script files get overwritten.  Your personal preference determines which technique you employ.
  
  (i) Always Overwrite:
  If you don't mind that your previous build scripts get overwritten, then select Always overwrite.
  
  (ii) Prompt for each overwrite:
  If you are more comfortable being alerted when a script is about to be overwritten, then select Prompt for each overwrite.
  
  The following example shows the type of prompt that appears to alert you of an impending script file overwrite.
  
  
  
  Script File Names
  
  The options available to select in the Script File Names group box depends on your selection in the Script File Options group.
  Output to Single File
  
  When you have Output to single file selected in the Script File Options group, just one edit box will appear in the Script File Names group box:  All Output File Name. The default name for the generated script will be PSBUILD.SQL
  
  Output to Separate Files
  
  If you have Output to separate files selected in the Script File Options group, the following four edit boxes appear—one for each build option—in the Script File Names group box.
  
  Create Table File Name.  The default name for this script is PSTABLE.SQL.
  Create Index File Name. The default name for this script is PSINDEX.SQL.
  Create View File Name. The default name for this script is PSVIEW.SQL.
  Alter Table File Name. The default name for this script is PSALTER.SQL.

The options most likely to change each time you run a build process appear on the Build dialog, and the options that are most likely to remain the same from run-to-run are defined in the Build Settings dialog.  Since most of the settings you choose, due to your preferences, will remain similar from one build to the next, all of the options defined on the Build Settings dialog are retained between sessions, including the Build Execute Options selections from the Build dialog.  The attributes that PeopleTools does not retain are the Build Options selections from the Build dialog.


After execution of Build Script:
The Create Table process creates a new application table based on parameters defined in the record definition.  When a new table is created, the DBMS updates the System Catalog tables to reflect the attributes of the new table.

After you build an updated SQL build script and execute it, the PeopleTools tables and the System Catalog tables are synchronized; the record definition and the Application Data table are synchronized.

Tables:
The Build process generates the appropriate SQL Create statements to build tables based on both your record definition and your platform.  It prefaces each new application SQL table with a PS_ to identify it as an application built using PeopleTools.
For example: PS_ABSENCE_HIST

Inxex:
Build also creates the appropriate indexes.  And, because Long Character fields operate more efficiently in some database environments when they're located at the end of the table, Build automatically positions Long Character fields at the end of the table for each database environment.

View:
When you create views to use online, you must keep the views synchronized with the database.  Like SQL tables, you must have a record definition for online views because the system looks to the record definitions for online processing rules.  And you must build the view before you can use or reference it online.

If you use a view as the basis for a panel, you select existing information to display on the panel.  So, when you create the record definition for your view, you can clone an existing record definition, delete any fields not used in the view, and proceed to define the view.  Query views are processed the same way as view text, which is defined as part of the record.

TRAINING (PS-10) - PeopleSoft Application Development - Part7 (Step-3: Create Record Definition)

Step-2: Create Record Definition

To create Record Definition
1.) Select File, New or click the New button on the Application Designer toolbar.



2.) Select Record and click OK.
A record definition window is displayed in the object workspace.
Your workspace is designed to build a list of fields in a record definition.




To open an existing record definition
1.) Select File, Open or click the Open toolbar button to open an existing record definition.

2.) Select Record from the Object Type list and then click Select.
The Open Object dialog is displayed.



3.) In the Open Object dialog enter the first letter of the record you are searching for and click Select.
PeopleTools will search for all existing records starting with that letter.
For example, if you press P, you will see a display all of the record definitions that begin with that letter.

4.) You can further narrow the search criteria by specifying Type and/or Project.



5.) You can also open an existing record from the project tree view by double clicking each node.

TRAINING (PS-9) - PeopleSoft Application Development - Part6 (Step-2: Create Field Definition - Effective Dates)

Step-1: Create Field Definition - Effective Dates

• The EFFDT (Effective Date) field has special properties related to the processing of effective dates on rows and should only be used when needed.
• EFFDT only on record definitions where you want to maintain data history—future, current, and past - to store rows of data in sequence.
• For effective-dated rows, you can only have one current row of data, but multiple occurrences of future and history.

Understanding Effective Dates
Effective dates allow you to keep historical, current, and future information in tables.  You can use the information to look at what's happened up to now and plan for the future.

Future - Data rows that have effective dates greater than the system date—usually today's date.
Current -  The data row with the most recent effective date closest to today's (system) date, but not a future date.  Only one row is the current row.
History - Data rows that have effective dates less than the current data row.

To enable you to track an accurate history of all your effective-dated information, the system invokes special logic when you access a panel associated with a record definition that contains EFFDT.  The menu action type that you select will dictate whether you can access the row type and what you can do with each type of row.

Action Type	View	Change	Insert New Rows
Update/Display	Current, Future	Future only	Effective Date Greater Than the Current Row
Update/Display All	History, Current, Future	Future only	Effective Date Greater Than the Current Row
Correction	History,Current, Future	All Existing Rows	Add New Rows with No Effective Date Restrictions

Update/Display Mode, you can view only current and future rows.  You can't access history.
Update/Display All Mode, you can view all History, Current and Future rows.

Correction Mode, you can do it all.  View, change, and insert rows of data regardless of the effective date.

TRAINING (PS-8) - PeopleSoft Application Development - Part5 (Step-2: Create Field Definition - What is Translate Table)

Step-1: Create Field Definition - What is Translate Table

The Translate Table is a prompt table that serves as an all purpose data dictionary to store values for fields.

Field Type Character.
Field Length 1 to 4 characters.
Field Values Consist of a relatively small, static set of values not maintained by the user.
Other Fields No other fields are related to this field.

When to Use the Translate Table
To better understand how the Translate Table works, consider the relative attributes of a Department ID field and a Sex field in a company database.  Your company may have hundreds of Department IDs and the field may need to be longer than three characters.  Other fields, such as department manager, may be associated with the department.

The Sex field, on the other hand, has two values—M (Male) and F (Female).  Because it has a relatively low number of values and the field is less than four characters in length, the values for this field should be included on the Translate Table.

To add Translate Values
1.) From a Field Definition window, click the Properties button or go to File, Object Properties.
2.) When the Field Properties dialog is displayed, select the Translate Values tab.



The Translate Values dialog box displays any existing values for the field and enables you to Add, Change, or Delete values.
In the Last Updated box, you'll see information—date, time, and operator ID—about the last update for the selected translate value.
3.) Click Add to define a new value.



4.) In the Add Translate Table Value dialog box, enter the Field Value that your users will enter on the panel.

The Field Value is the translate value for the field.

The system automatically sorts values in ascending order as you enter them.  So if you enter three translate values with Field Values A, T, and C, they will appear in the Translate Values dialog box as A, C, T.

The Effective Date defaults to today's date.  You should change it to the date when you want this value to take effect.  If you want the effective date to predate all rows on your database, enter 01012002 (January 1, 2002).

5.) To describe the value, enter a Long Name of up to 30 characters and Short Name of up to 10 characters as you would like it to appear on panels and reports.

If you leave Short Name blank, the system automatically copies the first 10 characters of Long Name into this field.

TRAINING (PS-7) - PeopleSoft Application Development - Part4 (Step-2: Create Field Definition)

Step-1: Creating the Field Definitions

Fields are where you actually enter and store individual pieces of data such as names, dates, salaries, and so forth.  When you define a field, you give it a set of basic characteristics depending on how the field is used, what kind of data it contains, and how it is edited.  Should it be a character field or a number field?  Should automatic formatting be used?  How large should it be?

To create a new field definition:
1.) Click the New button on the Application Designer toolbar or select File, New.
The New dialog box is displayed.



2.) Select Field and click OK.
The New Field dialog box is displayed.

3.) Select a field type.
Once you make a choice, the object workspace displays a field definition window for you to complete your new field definition.



4.) Once you are done, click the Save button or select File, Save.

Field Types
We can create several different types of fields using Application Designer.

Character - An alphanumeric field of fixed length.
System Shorthand: CHAR

Long Character - An alphanumeric field of variable length; used for textual entries such as comments or descriptions.
System Shorthand: LONG

Number - A positive numeric field of fixed length for which decimals are allowed.
System Shorthand: NBR

Signed Number - A positive or negative numeric field of fixed length for which decimals are allowed.
System Shorthand: SIGN

Date - A date field, the length of which is system-maintained.  Built-in edits prevent the entry of illegal dates such as day 42 or month 20.  Date fields always store a four-digit year, including century, to ensure proper functionality after the year 2000.
System Shorthand:  DATE

Time - A Time field, the length of which is system-maintained.  Built-in edits prevent the entry of illegal times such as hour 26, minute 70, or second 94.
System Shorthand: TIME

DateTime - A date and time field, the length of which is system-maintained.  Built-in edits prevent the entry of illegal date/times as defined in the individual Date and Time fields.  DateTime fields always store a four-digit year, including century, to ensure proper functionality after the year 2000.
System Shorthand: DTTM

SubRecord - A SubRecord field that represents a group of fields as defined on a SubRecord definition.
System Shorthand:  SREC

Image - An image field you can use to store images in a user defined format, such as bitmap (BMP) or Postscript (EPS).
System Shorthand: IMG

Field Properties
To see a field's properties from a Field Definition window, click the Properties button or go to File, Object Properties.
OR
If you are in a Record Definition window, first open the Field Definition window by highlighting the field, right-clicking the field name, and selecting View Definition from the Pop-up menu.  From here, you can view the field's properties.



NOTE: In the above screenshot you could see Translate Values. Its a special feature for fields in PeopleSoft application development. We will discuss about Translate Values in details in my next post.

TRAINING (PS-6) - PeopleSoft Application Development - Part3 (Step-1: Design The Application)

Step-1: Design The Application

Design the application is the first step of any application development in PeopleSoft. It needs proepr planning to analyze the business requirement and prepare a plan as how & what needs to be created to achieve it. Therefore before start any PeopleSoft application development we must have a requirement or design document which will contain the tables affected and the relationships between them, list of object definitions to be created or modified, GUI & Look/feel preparation document.

TRAINING (PS-5) - PeopleSoft Application Development - Part2 (Application Designer - Brief Introduction)

APPLICATION DESIGNER - Brief Introduction:

Application Designer is PeopleSoft main and an integrated development environment (IDE). Therefore before we go to nine steps of PeopleSoft Application Development its always good to understand a brief about Application Designer.

• Application Designer is PeopleSoft main and an integrated development environment that enables you to work with the numerous object definitions of a business application in a single work area. 
• The main advantage of having access to all of the development definitions in one tool is that it is easy to understand and exploit the relationships between the different components when building, maintaining, and upgrading your application.

Navigating the Application Designer:
Application Designer has been designed so that you can create, maintain, and customize PeopleSoft business applications in a simple and straight forward manner.

• To Start Application Designer
  Select Start > Programs > PeopleTools > Application Designer.
  
  
  
  The Application Designer window is divided into several component
  A.) Title bar:  Displays the name of the open project and active object definition.
  B.) Menu:  Provides access to Application Designer commands and features.
  C.) Toolbar:  Displays buttons useful in editing the active object.
  D.) Project workspace:  A graphical representation of the objects contained in a project.  The project workspace window has two folder tabs representing the Development View and the Upgrade View.
  
  
  D.1.) Development ViewUse the Development View to create and manipulate    development object definitions during application development, customization, or maintenance.
  D.2.) Upgrade View
  The Upgrade View shows folders that represent the various object definitions that are part of an upgrade project.  These can include development objects, as well as upgrade-only definitions. Use this view during the process of upgrading an application or individual application objects.
  
  E.) Object workspace:  Displays individual objects you open.
  F.) Output window:  Contains the output text from Application Designer operations, such as Build (SQL Create and Alter), Validate, and Find in PeopleCode.  

Output Window Tabs
F.1.) Build
The build process is used to create tables, indexes, and views—as well as alter tables.  If you specify the Log to Output Window option in the Build settings, then errors, warnings, and informational messages are displayed here.  You can control the level of detail that is displayed.
F.2.) Find Object References
The Find Object Reference feature is used to identify object definitions that are used by or referenced by other object definitions.  The output from the Find Object References operation is sent to Application Designer's output window.  Once in the output window, you can double-click on the lines there, and it will open the named object definition.
F.3.) Upgrade
Any upgrade actions initiated from Application Designer are displayed here.
F.4.) Results
Any messages related to project operations, like opening a project or inserting objects into the project, are displayed here.
F.5.) Validate
The validation process is used to validate both projects and panel groups.  The results of the validation are displayed here.
F.6.) Find in PeopleCode
The Find in PeopleCode dialog output is displayed here.  Once in the output window, you can double-click on the lines there, and it will open the named object definition.

APPLICATION DESIGNER MENUS

Application Designer makes a number of tools available to you when you're creating or modifying object definitions.  The menus are dynamic, changing depending on what type of object definition is active.  However, some menu items are "generic," so they always, or almost always, appear in Application Designer menus.

Following is a brief description of the generic menu items and their purpose.

The File Menu

FILE Menu Item	Purpose
Export Project	Copy a project from your current database to another database.
Project Properties	Enables you to edit properties for the open project.
Object Properties	Gives you the active object definition Properties dialog enabling you to make general comments regarding the object definition, select Use characteristics and Type characteristics.

The Edit Menu

EDIT Menu Item	Purpose
Find in PeopleCode	Searches through all PeopleCode programs for a text string you describe in a dialog box.

The View Menu

VIEW Menu Item	Purpose
View Definition	View definition that is referenced in the active object definition.
View PeopleCode	View PeopleCode characteristics of the active object definition.
Filtering	Options for filtering your view of the Upgrade View.  The options are: No Filtering, Selected for Upgrade Action, Not Selected for Upgrade Action, and Custom Filtering.
Toolbar	Turns the toolbar on and off.
Status Bar	Turns the status bar on and off.
Project Workspace	Turns the display of the project workspace on and off.
Output Window	Turns the display of the output window on and off.

The Insert Menu

INSERT Menu Item	Purpose
Current Object into Project	Inserts the active object definition into the project.
Objects into Project	Displays a dialog to insert objects into the project.
Projects into Project	Inserts another project into the active project.

The Build Menu

BUILD  Menu Item	Purpose
Project	Displays a dialog to build (SQL Create and SQL Alter) all records in the project.
Settings	Displays a dialog where parameters are set that apply to build operations.

The Tools Menu

TOOLS Menu Item	Purpose
Validate Project	Validates attributes of the project.
Data Administration	Provides dialogs that describe Space allocation on the database.
Change Control	Provides dialogs that enable you to view history or set system wide change control options as the administrator.
Upgrade	Provides functions for upgrading an application project.
Miscellaneous Objects	Provides options for setting custom field formats, toolbars, colors, and styles.
Options	Enables you to select commands specifying Insertion commands, object language preference, and validating options

The Go Menu

GO Menu Item	Purpose
GO	The Go menu gives you access to all of the different PeopleTools, including Application Designer.  From within an application, selecting Go, Recent provides a list of the last 10 panel groups you've opened.

The Favorites Menu

FAVORITES Menu Item	Purpose
Favorites	The Favorites menu retains panel group destinations you've specified using the Favorites, Add to Favorites menu option, including the key list.

Note: The Purpose of the Rest of the Menu Items are same as in other Applications.

TRAINING (PS-4) - PeopleSoft Application Development - Part1 (Nine Steps for PS Application Development)

PEOPLESOFT APPLICATION DEVELOPMENT

Every PeopleSoft application is a collection of related objects that have been designed to work together for a specific purpose.  These objects include fields, records, panels, and other objects that all interact with one another.  Developing and customizing PeopleSoft applications is a step-by-step process in which such objects are defined and built, relationships among objects are established, security is implemented, and every aspect is tested thoroughly.  The majority of these activities are completed using one interactive tool—Application Designer.

The "Nine Steps" of Application Development
The Nine Steps of Application Development utilize multiple PeopleTools and impact databases System Catalog tables, PeopleTools tables, and Application Data tables.

1. Design the Application
2. Define New Fields
3. Create the Record Definition
4. Build the SQL Table
5. Create the Panel Definition
6. Define the Panel Group
7. Create the Menu Definition
8. Enable Security
9. Test the Application

TRAINING (PS-3) - Creating PeopleSoft Database on Windows & Oracle

For PeopleSoft DB creation PeopleTools installation guide is the best resource to follow. However below are the high level steps for creating PeopleSoft Database on Oracle & windows.
1. Installing the Oracle RDBMS Software
Install Oracle S/W CDs. This will create This will create the home directory (Referred as ORACLE_HOME) and required files and binary.

2. Installing the PeopleSoft Database Server Components on the Database Server
Install PeopleSoft People Tools and Application CDs. This will create the home directory (Referred as PS_HOME) and required files and binaries.

3. Obtaining Windows Administrator Authority
Make sure the user creating the database should be part of local admin group

4. Creating an INIT<SID>.ORA File 
Add or modify the following init.ora parameters:

DB_NAME = <SID>
DB_FILES = specify max allowed per your OS, typically 1021
OPEN_CURSORS = 255 <This is a minimum value. You may choose to set this higher>
db_block_size = 8192

Below is a sample INIT.ORA file names initHR88DEMO.ora.

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=""
db_name=HR88DEMO

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:\oracle\product\10.1.0\admin\HR88DEMO\bdump
core_dump_dest=C:\oracle\product\10.1.0\admin\HR88DEMO\cdump
user_dump_dest=C:\oracle\product\10.1.0\admin\HR88DEMO\udump

###########################################
# File Configuration
###########################################
control_files=("C:\oracle\product\10.1.0\oradata\HR88DEMO\control01.ctl", "C:\oracle\product\10.1.0\oradata\HR88DEMO\control02.ctl", "C:\oracle\product\10.1.0\oradata\HR88DEMO\control03.ctl")
db_recovery_file_dest=C:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size=2147483648

###########################################
# Job Queues
###########################################
job_queue_processes=10

###########################################
# Miscellaneous
###########################################
compatible=10.1.0.2.0

###########################################
# Pools
###########################################
java_pool_size=50331648
large_pool_size=8388608
shared_pool_size=83886080

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=HR88DEMOXDB)"

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=65536

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=PSUNDOTS


 

5. Creating Target Directory Paths
Modify the PeopleSoft scripts with appropriate oracle path

6. Setting the ORACLE_SID Environment Variable

7. Create Password File
orapwd file=C:\oracle\product\10.1.0\Db_1\database\pwdtemp2.ora password=manager entries=5



8. Create Oracle Service in Windows
oradim -new -sid HR88DEMO -intpwd manager -startmode auto -pfile C:\oracle\product\10.1.0\admin\HR88DEMO\pfile\initHR88DEMO.ora



9. Perform steps for executing SQL scripts

To create an Oracle database:
1. Invoke SQL*PLUS (sqlplus), connecting as sysdba.
$sqlplus ‘/as sysdba’
2. Run the CREATEDB.SQL or CREATEDB10.SQL script, using the following example as a guide:
sqlplus>@<PS_HOME>\scripts\nt\createdb10.sql

Creating Catalog Views and Utility Tablespaces
Run the UTLSPACE.SQL script from SQL*Plus to create catalog views and utility tablespaces.
To create catalog views and utility tablespaces:
1. Invoke SQL*Plus (sqlplus), connecting as sysdba.
2. Run the UTLSPACE.SQL script:
sqlplus>@<PS_HOME>\scripts\nt\utlspace.sql

Creating PS.PSDBOWNER Table
Run the DBOWNER.SQL script from SQL*Plus to create the PS.PSDBOWNER table.
To create the PS.PSDBOWNER table:
1. Invoke SQL*Plus (sqlplus), connecting as sysdba.
2. Run the DBOWNER.SQL script, using the following example as a guide:
sqlplus>@<PS_HOME>\scripts\nt\dbowner.sql

To create application-specific tablespaces:
Run the HCDDL.sql to create the application specifica tablespaces.
1. Invoke SQL*Plus (sqlplus), connecting as sysdba.
2. Run the appropriate DDL scripts.
For example:
sqlplus>@E:\PT8.48_ORA\scripts\nt\HCDDL.sql

To create the roles for your PeopleSoft database:
1. ToLog on to SQL*Plus to as the System user.
2. Run the PSROLES.SQL script:
SQLPLUS>@<PS_HOME>\scripts\nt\psroles.sql

To create the PeopleSoft database owner ID:
1. Log on to SQL*Plus as the System user.
2. Run the PSADMIN.SQL script.
SQLPLUS>@<PS_HOME>\scripts\nt\psadmin.sql

To create connect ID:
1. Log on to SQL*Plus as the System user.
2. Run the connect.sql script.
SQLPLUS>@<PS_HOME>\scripts\nt\connect.sql
3. The script will then create the connect ID and grant it CREATE Session privileges only.

10. Running Data Mover Script to create PeopleSoft table, view etc
- Once we reach to this state tnsping <dbname> must be successful.
- Open the PeopleSoft Configuration Manager from  <PS_HOME>\bin\client\winx86\pscfg.exe and enter the database name and connect id/pwd created in earlier step.



- Run Data Mover ( <PS_HOME>\bin\client\winx86\psdmt.exe) in bootstrap mode, using as a user ID the access ID, which should be the user that creates the database.



- To invoke the Database Setup wizard, choose File, Database Setup.

Select your database platform.
Select your character set and click Next.
Select your PeopleSoft Application and click Next.
Select the Demo or System radio button, depending on which type of PeopleSoft database you are installing.
Set the database parameters and click Next.



Enter the Database Name, Symbolic ID, Access ID, Access Password, Connect ID.



After Click on Finish, there should have a file named HR88DEMOORA.dms will be created in data mover. (File name could be different on the basis of DB name you entered. When you run the script, Data Mover typically does the following:

IMPORT *
Create all the PeopleTools and application tables with their indexes.

ENCRYPT_PASSWORD *
Encrypt security information for the database.

CREATE_TRIGGER *
Create application required triggers.

REPLACE_VIEW *
Create PeopleSoft views.

CREATE_TEMP_TABLE *
Create PeopleSoft temporary tables.

11. Connect the database using Application Designer
Every PeopleSoft Application (HCM, CRM, Portal, ELM etc) is delivered with a minimum People tools version.
If your application version is compatible with People Tools for which you are using binary files like Data Mover,
App Designer etc you should be able to login using Application designer (<PS_HOME>\bin\client\winx86\pscfg.exe)

12. Updating Database to Latest PeopleTools Release

In case your PeopleSoft application database may be on a PeopleTools release prior to the version that you are currently running.
For you to be able to sign on to your database after running the Data Mover script to load your database, the PeopleTools versions
for your database and your file server must match. The steps in this task ensure that your PeopleSoft database is in sync with the
PeopleTools version that you are running. Refer below table for version compatibility between People Tools & Application.



Below table decide what and how many rel script (rel<PeopleToolsVersion>.sql or rel<PeopleToolsVersion><u>.sql) needs to be executed.



13. Updating PeopleTools Database Objects

To update PeopleTools database objects to the current release you must be in Application Designer. The Copy from File functionality lets you 
update your PeopleTools database objects from a file. You must perform this step to bring the database objects in sync with the PeopleTools release. 
Failure to run this step will introduce problems to your environment.

To update PeopleTools database objects:
- Launch Application Designer and sign on to your database with a valid PeopleSoft user ID.
- Select Tools, Copy Project, From File.
In the resulting dialog box, change the import directory to - <PS_HOME>\projects, select PPLTLS84CUR from the list of projects 
and click the Select button and copy the project.



14. Deleting Obsolete PeopleTools Database Objects

This process removes obsolete PeopleTools objects from your database. To update PeopleTools database objects to the current release you must be in Application Designer. You will use the Copy from File functionality to delete the obsolete objects from the database.

To delete obsolete PeopleTools database objects:
- Launch Application Designer and sign on to your database with a valid PeopleSoft user ID.
- Select Tools, Copy Project, From File.
- In the resulting dialog box, change the import directory to <PS_HOME>\projects, select PPLTLS84CURDEL from the list of projects and click Select and copy.



15. Altering PeopleTools Tables

ALTER AUDIT is an online utility used to check whether the PeopleTools tables are synchronized with the underlying SQL data tables in your database. This process compares the data structures of your database tables with the PeopleTools tables to uncover inconsistencies. ALTER AUDIT then reports its findings. In this release, we expect to see differences between the database structure and the tools tables. You will generate and run a SQL script to synchronize the PeopleTools table definitions with the underlying tables in your database.

To alter PeopleTools tables:

- Launch PeopleTools and sign on to the installed database.
- From the Application Designer, select File, Open.
- Select Project, enter PPLTLS84CUR in the name dialog box, and click OK.
- Select Build, Project.

16. Migrating Records to New Tablespaces
PeopleSoft has moved some delivered tables to different Tablespaces. You must run this step to move the tables. To copy the Tablespace Record project: - Launch Application Designer and sign on to your database with a valid PeopleSoft user ID. - Select Tools, Copy Project, From File. - In the resulting dialog box, change the import directory to <PS_HOME>\projects, select PT84TBLSPC from the list of projects, and click Select. Note. If the project already exists on the database, a confirmation dialog box appears asking if you want to overwrite the existing project.
Select the File radio button and click OK to overwrite the existing project.



Once the project copied build and execute the Alter Audit Script as per settings mentioned in PeopleTools install document.

17. Updating PeopleTools System Data Data Mover scripts that update PeopleTools system data are run to enable new features and load new messages for the PeopleTools 8.48 release.
Several of the scripts that you need to run are dependent upon the version of the application you are running. To update PeopleTools system data: - Invoke Data Mover by running <PS_HOME>\bin\client\winx86\psdmt.exe. The PeopleSoft Logon window appears. - Log on using the access ID you specified when you created your Data Mover scripts with the Database Setup program. This will start Data Mover in bootstrap mode. 3. Run the appropriate Data Mover scripts for your application database version. The application database version refers to the version before you started this step. Be sure to run the scripts in the order listed.
The scripts are found in the <PS_HOME>\scripts directory: Below is the list to determine what script (<PeopleToolVersion>TLS.dms) needs to be executed.


18. Running PeopleTools Conversions
Refer the PeopleTools install document to understand what are different Application Engine nneds to run, as the number and name of AE may vary on the PeopleTools version you are installing.
Some of the AE which needs to run for PT 8.48 are given below: - UPG844PORTAL - UPGQRYDUPHED - UPGPTSMDAT - UPGPT846PP - UPGPT848PP - UPGPT848IBUG - PTUPGIBCLONE 19. Running Version Application Engine & SQRs