Dynamic view in PeopleSoft

Views are a useful feature of SQL databases, letting us create virtual tables based on SQL select statements.
PeopleSoft 8 provides the functionality to create dynamic views. These are essentially SQL statements executed on the fly by the PeopleSoft component processor. We can use dynamic views in Peoplesoft pages only because they are PeopleTools objects, not SQL Objects.

Using Dynamic view as Prompt Table

In this article, we will see the use of Dynamic view as a prompt table. Please note that a dynamic view can be used for other purposes such as search records which will not be discussed in the article.

One major question that pops up to many of us the first time we use a dynamic view, why not use a normal view instead of a dynamic view. A dynamic view’s select statement may include Peoplesoft’s meta-SQL, and it may be replaced by a different SQL statement while the user is using the page. And also there might be situations where which you do not save the view in the database.

Using Edit Table for prompt tables

Assume that you have a page in which the user selects a country and depending on the selection you want a different prompt table for the states. For example, if the user selects US show states for US and user selects Canada show states of Canada.

This can be achieved by following the steps below

Step 1: Create two views/dynamic views to show the states of the US and Canada.
Step 2: Go to record field, in our case it is state field (Right click and select View Definition on the state field on the page).
Step 3: Right Click and select Record Field Properties
Step 4: Select Edits tab and then select Table Edit. Select Prompt table with edit( or No Edit) as per your requirement. Write %EDITTABLE against Prompt table.*EDITTABLE is a field in table named DERIVED, provided by PeopleSoft.

Step 5: Place the EDITTABLE field from DERIVED Table on the page.
Step 6: On field change of Country Field, write the below code

if YOURRECORDNAME.COUNTRY = "US";
  DERIVED.EDITTABLE = "STATE_US_DVW"; // dynamic view of list of states of US
Else
  DERIVED.EDITTABLE = "STATE_CAN_DVW"; // dynamic view of list of states of CAN
End-if;

Using SQL Text

Instead of creating two views/dynamic views, you can also create one dynamic view and pass the SQL dynamically to change the prompt table.

Step 1: Create and save A Dynamic view
Step 2: Give the dynamic view as a prompt table to the state field.
Step 3: On field change of your country field you can write.

if YOURRECORDNAME.COUNTRY = "US";
  YOURRECORDNAME.FIELD.SqlText = "Select State from MY_STATE_TBL where country = 'US'";
ELSE
  YOURRECORDNAME.FIELD.SqlText = "Select State from MY_STATE_TBL where country = 'CAN'";
END-IF;

HAPPY LEARNING :)

Custom Run Control Page

Creating a Custom Run Control Page

This step is the same regardless of whether you will ultimately use the Run Control page with an SQR or App Engine program.  Do the following in Application Designer:

Creating the Run Control Record

1. Open Record definition PRCSRUNCNTL and make a copy by using "Save As" and giving the Record a new name (for example, "MY_PRCS_RC").
• When prompted to save a copy of the PeopleCode associated with PRCSRUNCNTL, choose "Yes".
2. Update the following Record PeopleCode definitions on your new Run Control Record, replacing references to PRCSRUNCNTL with your custom Run Control Record name:
• OPRID.RowInit
• RUN_CNTL_ID.SaveEdit
• LANGUAGE_CD.RowInit
• LANGUAGE_OPTION.FieldChange
3. Add any desired additional fields to your custom Run Control Record (for example, "STRM").
4. Build the Record.

Creating the Run Control Page

1. Create a new Page definition and save it with a new name (for example, "MY_PRCS_RC").
2. Insert a Subpage onto your page, and choose "PRCSRUNCNTL_SBP".
• On the Insert Subpage dialog, change the "Subpage Record Name Substitution" value to your custom Run Control Record (i.e., "MY_PRCS_RC").
3. Drag your custom Run Control fields onto the Page (i.e., "STRM").
• Note:  You do not need to add the default Run Control fields onto the page (OPRID, RUN_CNTL_ID, etc).  These values will be populated automatically through PeopleCode.

Creating the Run Control Component

1. Create a new Component definition.
2. Set the Component Search Record to your custom Run Control Record (i.e., "MY_PRCS_RC").
3. Save the Component with a new name (for example, "My PRCS_RC").
4. Register your Component using the Registration Wizard (Tools > Register Component).

Creating the Process Definition

In this step, we create a Process definition entry for our SQR or App Engine program, and associate the process definition with our custom Run Control component.

1. In the PIA, navigate to:  PeopleTools, Process Scheduler, Processes
2. Click the "Add a New Value" tab and enter the Process Type and Process Name.
• Note:  The Process Name must exactly match the name of your App Engine program, or in the case of an SQR, must exactly match the file name of your SQR file (without the ".sqr" extension suffix).
3. On the "Process Definition Options" tab:
1. Add your custom Run Control Component under the "Component" grid area on the bottom left of the page.
2. Add whichever Process Groups are appropriate to grant process security to the appropriate persons.

You should now be able to navigate to your Run Control page in the PIA, fill out the Run Control parameters, and schedule your process to run on the Process Scheduler.

Retrieving Run Control Parameters

Now that we have a way to provide Run Control parameters via a Run Control page, we need to be able retrieve and use those parameters from within a program.  This part of the process is different for App Engine and SQR programs.

Run Control Parameters in App Engine Programs

The standard way to store Run Control parameters in an App Engine program is to use a State Record.  To set up the State Record:

1. A requirement in naming State Records is that they must have "_AET" as their suffix.  Make a copy of your Run Control record, giving it a new name (for example, "MY_PRCS_AET").
1. When prompted to save a copy of the PeopleCode associated with the original Record, choose "No".
2. Change the Record Type from "SQL Table" to "Derived/Work".  A Derived/Work Record doesn't persist any data to the database.  Instead, it acts as an in-memory data structure while the program is running on the Process Scheduler.
3. Open your App Engine program, and navigate to: File, Definition Properties
4. On the "State Records" tab search for your State Record definition, and click the "Add" button to move the definition from the left-hand panel to the right-hand panel.

Now that the State Record is created and associated with the App Engine program, we need to populate it.

1. Add an SQL step as the first step in the App Engine program.  To populate the State Record with values from the Run Control Record, we use the Meta-SQL %Select function.  For example:
   
   %Select(OPRID, RUN_CNTL_ID, LANGUAGE_CD, LANGUAGE_OPTION, STRM)
   FROM PS_MY_PRCS_RC
   WHERE OPRID = %OperatorId
   AND RUN_CNTL_ID = %RunControl

Now that the Run Control parameters have been stored into the State Record, they can be referenced from PeopleCode via the State Record.  For example, the following would write the STRM Run Control parameter value to the message log:

MessageBox(0, "", 0 , 0, "STRM=" | MY_PRCS_AET.STRM);

HAPPY LEARNING :)

History of PeopleSoft ? What is PeopleSoft ?

Dave Duffield and Ken Morris founded PeopleSoft in 1987, when they engineered the company’s first human resources application. Built on a client-server architecture, their solution offered flexibility and ease-of-use to a class of users previously barred from simplified access to the information and capabilities centralized in mainframes.

By putting ease of functionality directly in the hands of users, PeopleSoft quickly assumed industry leadership status in human resources solutions.

This would be the first of many innovations that would transform the way enterprises do business on a global scale. In 2000, PeopleSoft introduced the Pure Internet Architecture®. Even today, it is the only pure-internet architecture with no code on the client.

It has enabled what PeopleSoft calls the Real-Time Enterprise. It allows organizations to connect people directly to business processes, eliminating intermediaries, inside and outside the enterprise. It creates unprecedented efficiency and lowers costs.

It provides businesses with an unmatched competitive advantage. The Real-Time Enterprise sets the standard for business performance in the 21st century, and PeopleSoft is at the forefront of enabling businesses to make this model a reality.

PeopleSoft was the world’s second largest provider of enterprise application software. PeopleSoft’s business solutions are built with the flexibility to fit your business today and the adaptability to change with your business over time.

A broad portfolio of applications with industry-specific functionality gives you unprecedented flexibility to optimize business operations and compete more effectively.

PeopleSoft is a worldwide leader in providing complete solutions for more than 24 industries—from industrial manufacturing and consumer goods to financial services, healthcare, and public sector organizations.

More than 12,200 organizations worldwide—from mid-sized manufacturing companies to the largest service enterprises in both the private and public sectors—are using PeopleSoft solutions to build stronger and more profitable businesses.

PeopleSoft applications are built on innovative and open technology, enabling easy integration with third-party applications and legacy systems. And PeopleSoft’s pioneering Total Ownership Experience initiative ensures the best overall user experience at the lowest cost of ownership.

Founded in 1987 and headquartered in Pleasanton, California, PeopleSoft has 12,000 employees and annual revenues of more than $2.3 billion.

HAPPY LEARNING :)