Creating Records in PeopleSoft: SQL views

A SQL view is not a Physical SQL table in the database. It represents the data that is present in different physical SQL tables. Thus no DML operations can be performed on a view.
If you try to create a View with DML operation say INSERT statement and try to build the SQL View, it will generate an Error message "ORA-00928: missing SELECT keyword".

To create a View, click on File --> New and select the Record from the definition list.
Insert the fields that you want to see into the record definition.
Click on record Type Tab and select Record Type as 'SQL View'.
Now when you try to click to open SQL Editor, it will ask you to first save the record definition.
Now its important to make sure whether the view you are creating is a Non-Standard SQL table name or not. Generally, all the records created in the App Designer will be prefixed by default with "PS_". So If you create a record with name 'TEST_VW', it will be created as PS_TEST_VW. If you want the record to deviate from the normal peoplesoft record naming convention , you should specify that name in the 'Non-Standard SQL Table Name:". In simple terms, your own convention.
Also you can observe that the build sequence number is '1' by default. This will show the number of instances that are available for the view.

After saving the record , open SQL editor and type the Select SQL statement.
Note:
1. See that the select columns match the same order and type of the fields mentioned in the record definition.
2.Donot put Semicolon[;] at the end of the SQL statement. It will be treated as an invalid character .

Now build the SQL View with appropriate settings. Below the build editor, shows the status of the build action.
If there are 0 errors and 0 warnings, the view is successfully build.
Hope the info is helpful.
Will share you about 'Dynamic View' in next post. In short, Dynamic view is a SQL that is executed in runtime.

Effective Dates usage

Action Type : Update/Display
View : Current, Future
Change : Future only
Insert New Rows : Effective Date Greater Than the Current Row

Action Type : Update/Display All
View : History, Current, Future
Change : Future only
Insert New Rows : Effective Date Greater Than the Current Row

Action Type : Correction
View : History, Current, Future
Change : All Existing Rows
Insert New Rows : Add New Rows with No EffectiveDate Restrictions.

Note. For records that do not contain EFFDT, all actions (Update/Display, Update/Display All,and Correction) operate the same way—they retrieve all existing rows for the specified keys.

Save Processing

A user can direct the system to save a component by clicking Save or by pressing Alt+1.

An application can prompt the user to save a component when the Next or List button is clicked, or when a new action or component is selected. If the user clicks Save after being prompted, save processing begins.

The following actions occur in save processing:

1) The Component Processor initiates the SaveEdit PeopleCode event, which triggers any SaveEdit PeopleCode associated with a record field or a component record.

This enables you to cross-validate page fields before saving, checking consistency among the page field values. An Error statement in SaveEdit PeopleCode displays a message and then redisplays the page, stopping the save. A Warning statement enables the user to cancel save processing by clicking Cancel, or to continue with save processing by clicking OK.

2) The Component Processor initiates the SavePreChange event, which triggers any SavePreChange PeopleCode associated with a record field, a component record, or a component.

SavePreChange PeopleCode enables you to process data after validation and before the database is updated.

3) The Component Processor initiates the Workflow event, which triggers any Workflow PeopleCode associated with a record field or a component.

Workflow PeopleCode should be used only for workflow-related processing (TriggerBusinessEvent and related functions).

4) The Component Processor updates the database with the changed component data, performing any necessary SQL Insert, Update, and Delete statements.

5) The Component Processor initiates the SavePostChange PeopleCode event, which triggers any SavePostChange PeopleCode associated with a record field, a component record, or a component.

6) You can use SavePostChange PeopleCode for processing that must occur after the database update, such as updates to other database tables not in the component buffer.

7) The Component Processor issues a SQL Commit statement to the database server.

8) The Component Processor redisplays the component

Important! Never use an Error or Warning statement in any save processing event other than SaveEdit. Perform all component data validation in SaveEdit.

Reference peoplebooks

flowchart of save processing

Component Build Processing in Update Mode -flowchart

reference: peoplebooks

Component Build Processing in Add Modes

Component Build Processing in Add Modes

After search processing in add or data-entry modes, the Component Processor:

1) Initiates the PreBuild event.

2) Runs default processing on all page fields.
This enables you to set default fields programmatically using FieldDefault PeopleCode.

3) Initiates the RowInit event on all fields in the component, which triggers any RowInit PeopleCode associated with the record field or component record.

This enables you to initialize the state of page controls, using RowInit PeopleCode, before the controls are displayed. (RowInit enables you to set the values of non-blank fields programmatically, whereas default processing is used to set blank fields to their default values.)

4) Initiates the PostBuild event, which triggers any PostBuild PeopleCode associated with the component record, enabling you to set global or component scope variables that can be used later by PeopleCode located in other events.

5) Initiates the Activate event, which triggers any Activate PeopleCode associated with the page about to be displayed, enabling you to programmatically control the display of that page.

6) Displays a new component, using the search keys obtained from the Add or Data Entry dialog box, with other fields set to their default values.

Event flow

prebuild --> field default ( record field default, component record field default) --> rowinit( record row init, component record row init) --> post build --> page Activate

some of the content taken from peopletools 8.49

peoplecode event: field default

A field default can be set at 'record field' properties of the record definition. Apart from this, If we talk about setting default value through code for a particular field, we use 'field default' peoplcode event.

Field Default peoplecode can be written at record field and component record field.

This event triggers peoplecode, only If the following conditions are all true.

1) The page field is still blank after applying any default value specified in the
record field properties.This is true if there is no default specified, if a null value is specified,
or if a 0 is specified for a numeric field.

2) The field has a FieldDefault PeopleCode program.

peoplecode rowset

In PeopleSoft, the coding is done in peoplecode using peoplecode editor. Peoplecode is an object oriented language. Just like C++, Java, there is a concept of classes in peoplecode.
Data types can be divided into 2 types based on their nature.
1. Conventional data types like number ,date, string.
2. Object data types which are instantiated from Peopletools classes.
There are classes which are already defined and can be used right away in the code. If you want to create your own classes, it can be done with the help of Application classes.
Of the different classes availabe in peoplecode, following are the data buffer access types
1. Field class
2. Record class
3. Row class
4. Rowset class
The relationship between these 4 classes can be better understood as follows.
Field -is a subset of--> Record -is a subset of--> Row -is a subset of--> Rowset

Thus rowset object can be defined as data structure used to describe the hierarchial data.
Rowset Example: A component scroll area.
Using normal rowset object, we refer the data from the current acitve data structure in the component buffer.
Meaning, if a particular page , say Employee Personal Details page is opened, the current active data structure will be the Employee Personal Details component. Using the method getrowset(scroll.PRIMARY_RECORD_OF_THAT_SCROLL) will give us the entire structure of the particular scrolllevel.
Now using the notation row.record.field.value , we can use the data in that scroll.

standalone rowset:
Standalone rowsets are not associated with a component or page. Use them to work on data that is not associated with a component or page buffer. In earlier releases, this was done using derived work records.
Rowsets created using CreateRowset are not automatically tied to the database. This means if you insert or delete rows, the rows will not be inserted or deleted in the database when you save the page.
Note: All notations used are from 8.49 peopletools version.