July 13, 2009

PeopleSoft Technical -Part I

Application Designer
Application Designer is an interactive and easy-to-use rapid application development tool. It provides an integrated development environment that allows you to work with several definitions stored in a Project container in one single work area. Application Designer is the main development environment for PeopleSoft.
Any object created is defined both in metadata and the database. The Object Definition and Object will not respond if PeopleSoft metadata repository and physical database object do not match. When building objects in PeopleSoft, you must always keep in mind that they have to be defined and removed from two places: the physical database level and the metadata repository.

PeopleCode is the programming language delivered by PeopleSoft for the customization of the PeopleSoft Application.

There are two types of PeopleCode available to you as a Developer: PeopleSoft Component Interface PeopleCode, fired off by an object like a button on an event, and PeopleSoft Application Engine PeopleCode. It is important to understand that PeopleCode is the language that is written via the Application Designer interface. PeopleCode is a PeopleSoft proprietary language that is object-oriented.

Structured Query Report Writer is a PeopleSoft-delivered reporting tool(3rd Party Tool). SQR has a robust report-writing environment with complex procedural logic built in.
You can combine database retrieval and row processing. You can even embed PL/SQL in your reports and do complex database manipulation.

Application Engine
Application Engine is a batch processing tool.
Application Engine is different from regular batch programs in that it is not procedural. It focuses on set-based SQL logic and goes to a much deeper level of the underlying application definitions.
1) Why would Application Engine show up as part of Oracle Fusion?

a) Oracle doesn’t have a good tool for batch development.
b) Application Engine fits well within Oracle as it is a set-based tool.
2) SQR also has the advantage of being a little bit more independent of the applications.
3) SQR has zero understanding of the underlying applications that it’s working with. Trees, effective dating, setids, row-level security, related language records, etc. all have to be coded by hand when you’re writing SQR code
4) Aside from being a very PeopleSoft-specific tool, App Engine lacks any kind of built-in output capability.

“Meta-SQL” is a PeopleSoft extension of SQL that converts to correct SQL code.
Meta-SQL expands to platform-specific SQL substrings and then causes another function to be called, or substitutes a value. Meta-SQL constructs are used in functions that pass SQL strings, such as SQLExec.

Fusion Middleware Tools
Oracle Fusion Middleware is a comprehensive middleware family composed of SOA and middleware products. When used in conjunction with Oracle Fusion Middleware Enterprise Portal, PeopleTools is designed to serve as a point of access for all enterprise applications.

XML Publisher
XML Publisher, called BI Publisher in the Oracle Application world, handles the data formatting and output and has recently been integrated with PS/Query and PeopleCode, which makes it accessible from Application Engine. It’s not a big stretch to see how Application Engine could be enhanced to use BI Publisher natively as an output tool and effectively get rid of the need to use SQR at all.

Oracle Business Intelligence Publisher (BI Publisher, formerly XML Publisher) is an enterprise reporting solution to author, manage, and deliver all types of highly formatted documents eliminating the need for costly point solutions. Built on open standards, IT staff and developers can create data models against practically any data source and use BI Publisher APIs to build custom applications leveraging existing data sources and infrastructure. End users can easily design report layouts using familiar desktop tools, dramatically reducing the time and cost needed to develop and maintain reports. Extremely efficient and highly scalable, BI Publisher can generate tens of thousands of documents per hour with minimal impact to transactional systems. Reports can be viewed online or scheduled for delivery to a wide range of destinations.

Definitions in PeopleSoft Application Designer
In the Application Designer you will define the metadata definition as well as allow the tool to build the SQL that can be submitted to your DBA to build the physical database object. Always
be cognizant that the two objects must match in every aspect: size, type, decimal places, and format. If they do not match in every aspect, you will receive an unknown object definition error via the PeopleSoft PIA.

Fields are the lowest-level definition in PeopleSoft. Fields have the following attributes:
  • Type
  • Length
  • Decimal places (if a number type)
  • Translation values (optional)
  • Long name
  • Short name
  • Format
Available field types include
  • Character
  • Long Character
  • Number
  • Signed Number
  • Date
  • Time
  • Datetime
  • Image
  • ImageReference
  • Attachment
    1) The attributes for fields are stored in the database table PSDBFIELD, which is a metadata catalog table that is populated when a field is created or changed in the PeopleSoft System
    2) The catalog table PSDBFIELDLANG stores the long and short descriptions in multiple languages.
    3) To see fields that are used in Record definitions, look at the catalog table PSRECFIELD.

    • A collection of fields is a record definition.
    • A record can be a table (SQL table or Temporary table), view, subrecord, derived work record, query view, or dynamic view.
    • Tables and views would exist in both the PeopleSoft metadata and the physical database; the other types of records are only available via the PeopleSoft metadata.
    • Record definitions are stored in the metadata catalog table PSRECDEFN which stores the attributes for the records. It holds the properties of the record definition.These properties are of three types: General Properties, Type Properties, and Use Properties.
    • General Properties of a Record have the following attributes: Description, Record Definition, Owner ID, Last Updated Date/Time and Last Updated By User.
    • Type Properties of a Record have the following attributes: Record Type and Non-Standard SQL Table Name.
    • Use Properties of a Record have the following attributes: Set Control Field, Record Relationships(Parent Record, Related Language Record, Query Security Record, Optimization Delete Record), Record Audit, Audit Options(Add, Change, Selective, Delete) and Record-Level Auto-Update(System ID Field, Timestamp Field)
    • PSRECDEFN holds table space name in the SQL Table definition. Each record that is a SQL table and was delivered by PeopleSoft is stored with a prefix of PS_; a table that is created in PeopleTools will be named with the PS_ convention except when the name is defined in the Non-Standard SQL Table Name field in the configuration.
    • Set Control Field is the property that is used in a multi-company environment to share records across company databases. In order to do this, PeopleSoft delivered SETID field needs to be defined as the first field in the record definition as a Search Key.
    • Parent Record: Parent/Child definitions and relationships help create automatic joins for use in the PeopleSoft Query Tool,
    • Related Language Record setting is used in the case of multilingual application, and this setting brings that functionality down to the record level.
    • Query Security Record setting is available for PeopleSoft Query. This setting will provide row-level security for the record definition.
    • Record Audit: The audit flag if turned on in the record definition will record all inserts, updates, and deletes in an audit table. PeopleSoft will create a trigger at the database level that will write these transactions off to a table. This will allow you as the developer to know who updated, deleted, and/or added records as well as when (time and date) that was done.
    • Record Field Properties: PSRECFIELD stores the fields that are contained in the record definition. Within the PSRECFIELD table is a column USEEDIT, a 32-bit integer field. The following shows the basic settings:
    • Translate Values: Translate tables allow you to create prompt tables or list boxes that have predefined values for your users to choose from. This will make data entry quicker and more accurate. The rules for the data that you need to follow when creating a translate table are as follows:follows:
      a) Field type is Character.
      b) Field length is 1 to 4 characters.
      c) Field values consist of a relatively small, static set of values that are not maintained by the user.
      d) No other fields relate to this field.
    • A Derived Work Record is primarily used to store temporary data, data that
      do not need to be reused. One of the main functionality of a Derived Work Record is to store totals.
      Another common use is for buttons, messages, and temporary fields. Derived Work records are only available during your session with the page and are reset anytime the page is re-entered.
    • Subrecord Is used for SubPages and creates the one-to-many relationship.
    • Dynamic View Are views that are recreated dynamically with each use.
    • Query View Are used to define the record as a view using the PeopleSoft Query Tool.
    • Temporary tables Are used for running PeopleSoft Application Engine batch processes.
    The main graphical representation of the data on the web via the PeopleSoft application is through a page. Pages represent the data in your tables; they display the fields, columns, and rows in the database. They are used to view, update, and enter data in the application database.
    • One of the important elements in your page creation is to determine the mode in which you wish to handle the processing of the data back to the server(App Server -> DB Server). There are two choices for processing modes:Deferred mode and Interactive mode.
    • Most of the PeopleSoft-delivered components run in deferred processing mode; this mode reduces the need for the application to have increased interaction with the server and reduces the number of calls, leading to increased performance. Each trip to the server will refresh the browser page, and too much interaction can lead to screen flicker and slowing down the application.
    • If a component is using deferred-processing mode, pages and fields within that component can be made interactive on an individual basis (by unticking the "Allow Deferred processing" box). However, if a component is interactive, then all pages and fields on that component are interactive as well.
    • The page action options are directly related to the actions you can perform on data. The available options are limited to the type of data row that you are trying to retrieve, modify, insert, or update. The rules that are directly linked to effective date are Include History and Correct History actions.
    • PeopleSoft pages have three essential types of elements that you can use to design your pages: basic aesthetic elements, for example, lines and pictures; functional elements, which include buttons and links; and data-entry elements, including check boxes and radio buttons. The following are the common elements used in a PeopleSoft page.
      Frames Allow you as a Developer to draw a frame around a set of elements and create a group from them. These are display-only boxes that create a grouping of fields on a page.
      Group boxes Similar to frames, but they allow for text labels. They group and identify related fields.
      Horizontal rules Horizontal lines that are display only.
      Images Unchanging graphics on your page; an example would be your Company logo.
      Text Static text that is unchanging and can give a set of instructions on a page.
      Check boxes These directly correspond to the capturing of data entry for an on/off, yes/no field. A check box has only two options.
      Radio buttons Similar to check boxes but allow for a set of options to be chosen in the collection of data entry.
      Buttons A button can perform a number of actions, calculate a value, and refresh the fields on the page, open a new page, and help with navigation in the application. Buttons are very dynamic and can be coded to do a variety of actions and navigations.
      Links Can redirect the navigation to a new site outside or inside of PeopleSoft.
      Edit boxes Allow for data entry. They directly correlate to the field in the database; any limits on type and size are defined by the field. Edit boxes with spell check enabled will display a spell check button.
      Long edit boxes Are the same as edit boxes, but they allow for the data entry of longer character strings.
      Drop-down list boxes Allow for the selection of a data item for entry from a predefined list.
      Grids Grids are spreadsheets on the PeopleSoft page; they allow for the display of one to many relationships on PeopleSoft pages.
      Scroll areas These are similar to grids, but the data is displayed in a free-form manner—not in a spreadsheet-type display. Both grids and scroll areas allow for the sharing for key data into each new row that is created. These keys become important in the creation of scroll levels in the component and can cause problems with sharing of data between multiple levels.
    A component is derived from several pages and defines the way that the page will behave in the Internet browser. It is also where you define the pages that display as tabs across the top of the page. The component is based on the page and the properties that define the way that the page will be displayed in the browser.

    Menu Name needs to be created in Application Designer and PeopleTools->Portal->Structure and Content are used to create a Menu.

    Roles and Permission Lists
    Permission and security are tied to the page through the component and menu. Menu
    under the Page tab is defined in the Permission List object. A role is defined as one or more permission lists, and a permission list is defined as access to one or more pages via the component.

    1 comment:

    1. Hi,

      I found this site when i was googling.This is more informative for the people soft beginners.Many thanks.