Application Designer

APPLICATION DESIGNER

What is an Application Designer?

Application Designer is an integrated development and upgrade tool that contains collection of related object definitions, these objects are useful for specific purpose means using this objects we can develop projects according to the user requirements.

What are the 8 Development Steps?

8 steps in Developments steps

Design the Application.

Create Field Definition

Create Record

Build the Record

Design the Page

Create Component

Enable Navigation.

Test the Application

Difference between upgrade and development view?

In Development view we will create or modify many types of definitions but in the upgrade view we will have only those definitions that are available for upgrade.

How many types of tables are there in database and what are their differences?

1. System Catalog Tables
2. People Tools Tables
3. Application Data Tables

System Catalog Tables: System Catalog Tables stores physical attributes of the tables and views. Naming convention for these tables is SYS.

PeopleTools Tables: The objects that we develop using the People Tool Objects are saved in PeopleTools Tables. Naming convention for these tables prefixed with PS. Once a field is created it would be stored in PSfield definition table, once a record is created it would be stored in PSrecdef. (Only definition/structure is stored)

Application Data Tables: stores the data, which is entered by the user through PeopleSoft application windows and pages. Naming convention for these tables is PS_.

Application Data Tables are divided into 2 types:

1. Control Tables
2. Transaction Tables

Control Tables: Define the company structure and processing rules.

Transaction Tables: These are tables used to record our day-to-day transactions, such as personal data about employees.

What is a prompt table?

Prompt tables are the most common methods for validating data entry in peoplesoft applications.  This is like application data table the only difference is that it stores values that other tables can validate against. Prompt table is a control table. (Country table and Company table)

What is a Translate Table where are the Translate table values are stored?

It will store values for fields that don't require an individual prompt table, It is also known as XSLT table. The data we store in the translate table does not change often. Each database contains one translate table. (blood types, days of the week)

Structure of the Translate Table (PSXLATITEM)

Field Name	Description
FIELDNAME	Field name, such as ABSENCE_TYPE
LANGUAGE_CD	Language code
FIELDVALUE	Value for the field
EFFDT	Effective date
VERSION	Internal version number (system-maintained)
EFF_STATUS	Status—active or inactive
XLATLONGNAME	30-character description; used as a label on pages and reports
XLATSHORTNAME	10-character description; used as a label on pages and reports
LASTUPDDTTM	Date Time field showing the last time that a field was updated
LASTUPDOPRID	User ID of the user who most recently updated the field

How many table edits are there in Record field properties?

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

Prompt Table No Edit:  Provides List of Suggested values, which users cannot edit.

Prompt Table With Edit: Users can edit the contents of the field against the values that are maintained in the specified prompt Table.

Translate Table Edit: Which is nothing but a translate table created by developer.

Yes/No Table : I n some situations we want the user to enter only Boolean values like yes or no, true or false in this situation we will use yes no table. The Translate Table for ex: PSYESNO. When you select the Yes/No Table Edit, the system automatically points to the PSYESNO field in the Translate Table

Difference between prompt table with edit and No Edit?

Prompt Table No Edit:  Provides List of Suggested values, which users cannot edit.

Prompt Table With Edit: Users can edit the contents of the field against the values that are maintained in the specified prompt Table.

What is effective date?

Date on which a table row becomes effective, the date that an action begins. For example, if you want to close out a ledger on June 30, the effective date for the ledger closing would be July 1. This date also determines when you can view and change the information. Pages or panels and batch processes that use the information use the current row. Effective date enables us to keep historical, current, and future information in tables. There are three types of effective dates: Current, History and Future.

What is effective status?

EFF_STATUS usually accompanies EFFDT. For example in Department table suppose we inactivated one dept from so and so future EFFDT, from that future date onwards this dept is going to be inactivated, after that whenever we click the link prompt button of dept id field we can see only active departments.

What is effective sequence?

Whenever we want to enter more than one transaction on the same day for an employee like promotion and salary hike we will use effective sequence. Effseq number starts with 0

How many Actions are there in the Component Properties dialog (Use Tab)?

Add: Used to add a new high-level key, such as a new employee ID or a new customer. Except in the case of effective dating Add is used to insert new current row or update future rows. If we select only Add action then we can’t modify the existing rows. (Doesn’t have any effect with EFFDT)

Update/Display: Used to update existing rows only.

Update/Display All: Used to update current and future rows in an effective dated record. Only used with effective dated records.

Correction: Used to update any rows (history, current and future) in an effective dated record. Only used with effective dated records.

Data Entry: Not supported in PeopleSoft Internet Architecture

What is the difference between Key and Duplicate Key?

Whenever a field is a key field we cannot have duplicate values in that field. Key is used to identify the field as the search criterion that uniquely identifies each row.

Duplicate values are allowed, for example in composite keys like if we have 4 fields at least one field should be unique ex: in job table emplid, emplrcd, effdt and effseq

What is from search key and through search key in Record properties?

From search key: Gives the values from the specified value to the max values

 For ex:  if we have to search values after emplid of 100 to the max then we should enter 100 in the from search key. (total values to be displayed are 300 values)

Through search key: gives values from the given value – 300 values.

What is the difference between key and search key and also alternate search key?

Keys determine the structure that makes a row of data unique.  A key may also be a search key, which makes the field appear on search pages to help users locate rows of data.  Alternate key are only used for search pages and have nothing to do with the table structure. Alternate search key provides an alternate path into the table data. It will appear to the user whenever he clicks advanced search option.

How many types of tables are there? Define them?

SQL Table: A physical SQL Table present in the database, this is the default.

SQL View:  Not a physical SQL Table but corresponds to a view written in SQL referring one or more fields from different tables. This gives a filtered view of information stored in tables. Stored in the database as a view. (Query view is system generated and SQL views are created by developer) whenever you modify a record you need to take care: check if any views are dependent on this record. 

Dynamic view: A record definition that can be used like a view in pages and People Code but is not stored as a SQL view in the database. Dynamic views provide superior performance. 

Derived/Work: We can use it to calculations. It is not an object, and also we can use it as a funclib. A temporary workspace to use during online page processing. A derived or work record is not stored in the database, so no need to build it.

Sub Record: Group of fields used in multiple records like companies having address data of employees. If you have to use the same fields in different record definitions, then we will store all those fields in a subrecord and add this subrecord as a field to the record definitions. Stored in the database.

Query View: using the PeopleSoft Query we don’t need to write a query it will automatically generate by dragging and dropping. This is a two tier tool and runs through application designer and produces SQL dependent upon the host RDBMS.

Temporary Table: we will do all the manipulations on the data present in the temporary table. Increases the performance, as it is not stored in the database. Used mainly for the AE batch processes.

What is a dynamic view? Where we can use dynamic View?

It can be used like a view in pages and people code but not stored in database as a sql view, instead the system uses the view text as a base for the sql select that is performed at runtime. These views shows superior performance in some situations such as search records and people code selects.

What is difference between SQL View and Dynamic View?

SQL views are written against the RDBMS catalog tables.  Dynamic view is declared only to people tools and not to the system catalog. SQL views are not recommended for performance tuning as they run against the database.  Dynamic views improve performance. SQL views reside in the database dynamic views are not.

Advantages of Using Search Views instead of Search Table

As the search view is created with selected fields there will not be much stress on app server and there by improves the performance.  Search View also gives additional search criteria.

What is the difference between record properties and record field properties?

Record properties apply to the entire record definition. Record field properties are not shared among records, they are specific to a single record definition and are stored with the record. Each record stores a unique set of record field properties while the primary field definition remains the same.

What is Component Buffer?

Area in memory, which stores data for the currently active component. Whenever you open a component the entire data for that component is retrieved upfront and stored in the application server. Component buffer is a temporary buffer allocation.

What is component processor?

It is a people tool runtime engine that controls the processing of the application.

 

What is a data buffer?

Data buffer contains multiple components of data.

What is the difference between component buffer and data buffer?

Component buffer contains active component data i.e. level 0 data. Data buffer contains multiple components of data.

What data comes into Component Buffer?

Primary record data, related display fields, translate values and derived work records. If you keep key and alternate search key on 0 level then only 2 values will appear in Component Buffer the other field values will not available. In Component Buffer if you want to see the other field values then place a non-key field on 0 level and make it invisible. Every level will have its own primary record. A level 0 primary record and a search record can be different but the key field values should match.

How many levels can we have on a page?

Page allows Nested levels up to 3 levels.          

What are Level Based Controls and their Difference?

Level Based controls are Scroll Area, Grid and Scroll Bar.

Scroll areas have their page fields arranged by the developer. Page fields in a gird always appear in a single row. In grid we can view no of rows by giving occurrences. Scroll areas provide a row separator when you select multiple occurrences of data. We can view multiple rows in a grid while in a scroll area you can retrieve only one row at a time. One can have nested scroll areas but not grids. You can insert grid in   a scroll area but not scroll in a grid.

What is the difference between scroll area and Grid?

Scroll areas single row of data will be displayed whereas in grid multiple rows of data will be available.

Difference between standard, secondary and sub page?

Standard page: It is the graphical interface between users and database. The important data is displayed in the standard page. We can place secondary as well as a sub page in standard page.

Secondary Page:  Page that will open on users request but remains as a secondary to the primary page display. We can open it through standard page only. The data within secondary page is at the same level or below the level of the page control that launches the secondary page.

Sub page: sub page controls are used to provide standard look for the commonly used fields.  A sub page is referenced as a page control on multiple standard pages.

Difference between sub page and sub record

All the fields in a sub page must be associated with fields of a sub record in the specified record definition.  Sub record contains all related fields, whereas subpage contains fields from sub record.

What is occur level and occur count?

Occurs Level: Occurs level determines the level of the object that we inserted. By using this we can set levels for example after inserting a level based control in level 2 if you want to change the level based control to level 1 you can change the occurs level.

Occur Count: Occur Count determines how many rows should be displayed.  For ex if you want the scroll to display only 5 rows then we will give occurs count to 5.

Difference between Search Record and Add Search Record

Search Record: The search record controls access of rows of data in a table, and its keys and alternate search keys appear on the search page as criteria. The search record might also contain logic to filter the search to secure rows of data—row-level security is implemented in this way

Add search record: Add search record is specifically used for add actions. If we are using a search view as a search record for performance in this situation we will put the main record as a add search record so that we can enter new values in this main record. The system default is the standard search record if we don't specify an add search record

What are the options available, while building a table?

The Build process uses Data Definition Language (DDL) to construct a physical database component that is based on the associated record and field definitions that you created. With the Build feature, you can create the following.

Create Tables: Creates structure in database.

Create Index:  When you build a table, it also creates index for that table

Create View: It will create a sql view.

Alter Tables: When you don’t want to lose the existing data we build that record with this Create Trigger after the Build process begins, there are no runtime prompts until the process is complete. All events are written to the Build log.

What are the three build options available when building a table from a record?

Build script file: which generates a sql script that can be run against the desired database to build the table.

Execute SQL: which builds the table in the database.

Execute and build script: which both build the table and generates a sql script.

Can you write People Code in Secondary as well in Sub Page?

People Code can be used on the secondary page just like any other page.  We cannot write People Code for Sub page.

Can we have 2 Scroll areas at Level 1?

Yes, we can have 2 scroll areas at level 1.

What are multi level pages where should the field at level 0 be getting their data from?

Level 0 fields must be associated with record fields of search record.

Can we keep 2 records at level 0?

Yes we can have 2 records at level 0 provided should match the key fields.

Audit Table and their fields? Record and Field Level Audits

Records the information of user actions details as well as the changes or additions or even modifications done on the table.  Audit records are prefixed with Audit_.

Audit Field Name	Purpose
AUDIT_OPRID	Identifies the user who caused the system to trigger the audits—either by performing an add, change, or delete to an audited field.
AUDIT_STAMP	Identifies the date and time the audit was triggered.
AUDIT_ACTN	Indicates the type of action that the system audited. Possible actions include: A: Row inserted. D: Row deleted. C: Row changed (updated), but no key fields changed. The system writes old values to the audit table. K: Row changed (updated), and at least one key field changed. The system writes old values to the audit table. N: Row changed (updated), and at least one key field changed. The system writes new values to the audit table.
AUDIT_RECNAME	Identifies the name of the record definition that was audited.

Record Level Audit: for creating a record for record level audit we need to have 3 fields Audit_operid, Audit_stamp, and Audit_action. We can create our own audit records by prefixing Audit_

Field level Audit: By using delivered PS_AUDIT table we can set field level audit.

What are the key disadvantages of auditing?

Auditing adds overhead to transactions that will slow database performance. Audit tables can grow to be quit large, again draining resources.

What is SQL Object? 

People tool object that we can create and use it wherever we want – PC, views, and dynamic views.  %SQL.

Deffered and Interactive Processing

In Interactive mode for every cursor action there will be a trip to the server. In deferred processing mode, trips to the server are reduced. In Interactive whenever a user exits a field there will be a trip to the server.

In Differed processing until a user performs an action, which requires a trip to the server there wont be any trips to the application server.

In how many places we can set Differed processing?

Field

Record

Page

Component

What is the design Considerations while designing a page?

Put the field control on the appropriate derived or work record, rather than on a regular data record definition to derive its value. Use as few record definitions as possible in a component.

Do not remove table edits to improve performance, even though an edit against another table causes a short pause. Eliminating them might compromise data integrity. Apply deferred processing when possible to fields, pages, and components

What is registering a Component?

We will add the component to a Menu and give permissions and security.

What are the considerations that you take while altering a table?

Any time one intends to change an existing object we should always attempt to find out if the changes are effecting on other system objects. Edit> find definition reference: If a definition reference any other definitions we must research the connection and determine with the referenced object will have any effects – then we need more follow-up.

Can we use the same Component in 2 Menus?

Yes , we can use same component in any no of menus

Can we have a subpage at 0 Level?

Yes, we can have a sub page at 0 level.

Can we have a Secondary page at 0 Level?

We cannot have a secondary page in 0 Level.

Can we have a Grid in Level 2 and scroll area in level 3?

Yes we can. But performance issues say that a grid should always reside in the last level

Can we have Nested Grids?

No we cannot have Nested Grids.

What is Parent Child Relationship?

To maintain parent child relationship if the parent table contains one key field the child table should contain the same key field and one additional key.

In parent child relationship we should have a relation between two tables ex: if “A” is the parent table with “x” as a key and “B” is another table then “B” should have “x” as a key with another key “y”, then “B” is the child table of for table “A”.

What is the difference between primary record and the search record?

Each scroll area and grid is associated with a single primary record.  This represents the application data table the page fields are associated with.  All fields in a scroll area or grid must be associated with one (and only one) record, which is the primary record for that level.

The search record builds the search page for a component and populates all fields at level 0.  The search record is determined at the component level.

What is PIA Architecture?

In PIA end users do not have any peoplesoft specific software installed on their machines, they use Internet browser to connect web server, which interacts with application server.

What are components of PIA?

1.    Web browser

2.    Web server

3.    Application server

4.    Batch server

5.    Database server

What are major tasks that the application server performs in PIA?

The application server maintains a persistent connection to the database, and converts peopletools and application table data into HTML.  The server then provides the HTML to the web server for delivery to the browser.

The application server also plays a role in connecting development workstations to the database for three-tier connections.

What are the major tasks performed by the web server in PIA?

Web server acts as the front end of the PIA.

The Web server includes Web services software that displays the HTML to the browser.  It also includes java servlets that manage each session’s connection to the application server.

What is Tuxedo/Jolt? 

Tuxedo manages the multiple connections to the database.  Jolt is a java based communication protocol.  The assembled HTML is sent to the web server.

What is a metadata?

Software that resides on a web server and displays HTML for a Browser.

When you select a row of data from a search page where you are taken to?

The search page takes you to a component the component is made up of one or more pages with each page containing several fields that hold the data.

What is the difference between adding a row of data and inserting a row of data?

When you use the add action a new high level key is created at level 0, when you insert a row of data you are adding information that has a one to many relationship with the key at level 0.  Generally you insert rows within scroll areas are grids.

How do you recognize levels on a page other than level 0?

Levels are bound by either a scroll area or a grid.

When exporting a project to a file in what format is the definition data stored?

Projects are stored in XML files.

Can projects be used to transport application data?

No, People soft offers a different tool data mover to transport application data.  Projects only hold people tools data.

What 2 methods are available for transporting definitions in a project between the databases?

Projects can be exported directly to another database or they can be exported to a file.  When you export to a file you must take the extra step of importing the file from the target database. By using import and export commands we can transfer.

What is Business Unit? What are the major functions?

A business unit is a way of tracking specific business information for reporting and other roll-up data collection. It performs 2major functions

1) It allows you to maintain a tree structure to facilitate customer-specific roll-up reporting.

2) It allows you to distribute and administer certain control tables like the department table.  This is extremely important for large or multinational companies.

What is Setid?

Setid's are key to linking business units to record control groups. It is the key on all control tables in tableset sharing Every row in a control table includes a setid as its key.  Setids partition the data into groups or sets of data that are appropriate for each business unit.

What is Table Sharing?

Tableset Sharing is a method of filtering prompt data so that only appropriate values are available for data entry. With tableset sharing, a single table can offer different values based on underlying business rules.

Table set Sharing is organized around these basic elements:

Business Units and transaction tables

SetIDs and control tables

Business rules.

What is the difference between a business unit and a setid?

A business unit is a way of tracking and reporting specific business information for reporting and other roll up data collection.  It does not have to be a legal entity for HR.  It’s a way of organizing your organization.

A SetID is a link to Table Set Sharing. Table set sharing simply consists of assigning specific SetIDs to specific record groups for individual business units to restrict the view of rows of data within control tables.  It’s a way of organizing your data in the system.