The PeopleSoft Blog

Connected Query - PeopleCode Implementation

2012-01-25T22:18:00.001+05:30

The link below the PeopleCode implementation of the connected query.

http://mfinchps.blogspot.com/2011/05/how-to-launch-connected-query-xml.html

Connected Query Feature

2012-01-25T22:02:00.006+05:30

Forgot to post this for a long time. The below description I took it from PeopleBooks. Why to destroy its beauty :)

Starts here

Connected Query enables you to create a single XML file based on a set of queries with parent-child relationships.

A connected query is a hierarchical object built with existing PeopleSoft queries. A parent query can nest n levels of child queries and any child query can have m sibling queries within a hierarchy. No artificial limits exist for n and m, but typical use involves a single parent-child relationship or a few levels of nesting. Fields in a child query are mapped to related fields in the immediate parent query.

A connected query returns a hierarchical data set in which data returned by child queries is filtered by the results of its immediate parent query.

Connected queries are used to analyze data, supply to other systems with PeopleSoft data using Web Services, and create XML Publisher reports that use connected query as a Data Source.

Connected queries are significantly useful:

• To simplify task for a business user.

• User create a set of linked simple queries to collect data from multiple tables instead of creating a complicated query with multiple equal, outer joins, and grouping conditions.

• When two or more SQL statements are linked with left outer join to the same query.

• Connected Query enables you to collect data similar to query performing left outer joins. In contrast to query, Connected Query enables you to link multiple queries to a single parent query.

• As a tool in the process of replacing Crystal reports (that use subreports) with XML Publisher reports.

Crystal reports can include the main reports and a set of subreports that could be combined in the same template. Using Connected Query, you can replace a Crystal reports having main reports and sub-reports with XML Publisher reports.

• When outputs need to be in structured XML.

A simple query (including a query with multiple joins) creates a tabular plain layout. To have a hierarchical output, you need to use multiple grouping conditions that involve complicated logic and is not always straight forward. Connected Query processing creates a hierarchical output where a single row of data from parent query results in a set of rows in a child query. This data is processed row by row and produces structured data. It has similarity with SQR and Application Engine nested loops processing.

Instead of using Application Engine or SQR to collect data for file processing by XML Publisher, use Connected Query as the data collection tool because no conditional logic involves in this process. However, use SQR for processes with high volume of data or when high performance is required. You can quickly create a single parent-child relationship using the Connected Query Quick Start component (CQ_WIZARD), and create complex connected queries using the Connected Query Manager (PSCONQRS).

PSQuery drilling feature

2012-01-20T23:08:00.006+05:30

For those who havn't tried this feature:
After creating the query, go to Expressions tab and click on the Add Expression.

Search keys when clicked on it will give you the URL keys scroll which you selects as the parameters passed to the target component.

Map URl to Query coloumns: to hyperlink the field of your wish in the query result

In the above query result you can see the emplid KU0007 is hyperlinked.

Auto Increment: Best practice

2012-01-20T22:16:00.001+05:30

I came across this function recently and found it quite useful and a best coding practice.

If you have a counter setup and wanted to increment the counter manually in the code, rather than writing a sqlexec increment step you can use this delivered functions: GetNextNumber,GetNextNumberWithGaps and GetNextNumberWithGapsCommit.

GetNextNumber:
Use the GetNextNumber function to increment the value in a record for the field you specify by one and returns that value. You might use this function to increment an employee ID field by one when you are adding a new employee. If the new value generated exceeds max_number, a negative value is returned and the field value isn't incremented.

GetNextNumberWithGaps:
Use the GetNextNumberWithGaps function to determine the highest value in a table for the field you specify, and return that value plus increment

GetNextNumberWithGapsCommit:
Use the GetNextNumberWithGapsCommit function to return the sequence number value plus increment for the given field residing in the given record. This function also enables you to specify a SQL Where clause as part of the function for maintaining multiple sequence numbers in a single record.

This function is typically used for obtaining a new sequence number for the application, for example, getting a new Purchase Order number to be used in the application transaction.

Use this function instead of the GetNextNumberWithGaps function. The GetNextNumberWithGaps function is very restrictive in its usage. The GetNextNumberWithGapsCommit function can be used in any event. The sequence number (record.field ) is incremented right away and it doesn't hold any database internal row lock beyond the execution of this function.

AAWS Response error

2012-01-04T16:19:00.002+05:30

Has anyone outhere experience this issue. You were able to save/submit the application using delivered AAWS SAD_ADMISSION web service. But in response we should get the application number for the tracking purpose. This is the bug currently we are facing. Any thoughts...

The Macro Issue while installing XML Publisher Plug in

2011-12-12T15:50:00.003+05:30

This issue never happened before. I tried installing the lpugin several times and nothing was working. Finally thought of searching in Google and tada...Got the solution from the blog below. I must admit that it was a tricky solution in place.

http://damir-vadas.blogspot.com/2011/02/macro-cannot-be-found-or-has-been.html

Good Job Damir :)

PeopleTools 8.52 is Generally Available

2011-12-03T21:53:00.002+05:30

PeopleSoft is getting better version by version. So whats new!!!

http://blogs.oracle.com/peopletools/entry/peopletools_8_52_is_generally
http://blogs.oracle.com/peopletools/entry/peoplesoft_8_52_ipad_certification

Check out Oracle's PeopleTools 8.52 PeopleBooks:
http://download.oracle.com/docs/cd/E25688_01/pt852pbr0/eng/psbooks/index.htm

Development Tools
- PeopleTools 8.52: PeopleSoft Documents Technology
- PeopleTools 8.52: PeopleSoft Search Technology
- PeopleTools 8.52: PeopleTools Portal Technologies

Reporting and Analysis Tools
- PeopleTools 8.52: PeopleSoft Pivot Grid
- PeopleTools 8.52: Reporting Web Services

Integration Tools
- PeopleTools 8.52: Feed Publishing Framework

Administration Tools
- PeopleTools 8.52: Performance Monitor
- PeopleTools 8.52: Security Administration
- PeopleTools 8.52: System and Server Administration

Lifecycle Management Tools
- PeopleTools 8.52: PeopleSoft Application Designer Lifecycle Management Guide
- PeopleTools 8.52: PeopleSoft Change Impact Analyzer
- PeopleTools 8.52: PeopleSoft Test Frameworkv

Long time

2011-12-02T23:18:00.001+05:30

It's been long time...Got heldup with some urgent milestones..Will be back!

JQuery Cycle Plugin

2011-10-28T21:09:00.002+05:30

This is a photo effect script for intuitive effects. Just tried out and it is a good one.

Please download the attachment below to get the sample code. Copy paste this code to an HTML area for the DEMO.

Context Sensitive Help

2011-10-24T10:28:00.003+05:30

If you are not able to view the document in the embed form above, download it by clicking on the PDF icon below

Ajax Vertical Slider.

2011-10-20T21:56:00.011+05:30

Just tried out the vertical slider jQuery and worked perfectly.

http://pupunzi.open-lab.com/mb-jquery-components/jquery-mb-verticalslider/ is the site where the source code resides.

Try the code below in an HTML area on the page. There are two .js script files that you need to place in the webserver. You can download the same from the above source site.

From the text file link given below, download the same and find ".js" to find the script file references.

Below is the implementation screen shot on my local instance.

Once clicked on the link, my site will open inside the instance.

Try it out!!!

jQuery Accordion Menu

2011-10-20T11:03:00.005+05:30

If you are bored of the classic menu, then try beautifying it up with some jQuery. I just tried some script to create an accordion menu.

Visit http://jqueryui.com/demos/accordion/ for the implementation steps.

For Demo follow this link: http://jqueryui.com/demos/accordion/sortable.html

For a quick implementation:
1. Create a pagelet wizard and place the code in the HTML source.
2. Hide the classic menu and select the menu position to the left side.

Please contact if you need further assistance to try this out or if you have a better idea to implement the same.

I had the demo accordion menu which I did in my local instance. unfortunately before taking the backup, it got crashed. Excuse me this time.

comment allez-vous les gars!!!

2011-10-19T13:46:00.002+05:30

Hope everyone out there is having fantastic time with your work. Same here. Lot of learnings and not getting time to post new items.

However in between tried some jQuery stuff within PeopleSoft along with some Ajax. Will be posting those here.

Embedding Pagelet to a Page

2011-09-23T12:23:00.003+05:30

Never tried embedding the Pagelet to a page before. I have tried it on the home page or under a component. For those who have not tried this; below are the steps to do it.

Create a pagelet and publish it on the home page tab.

And make sure on the 6th step; that is on the publish pagelet step; to check the embedd pagelet option.

Now use the following code and place it under the event you like.

import PTPPB_PAGELET:*;
Local object &PWAPI, &Pagelet;
&PWAPI = create PTPPB_PAGELET:PageletWizard();
&Pagelet = &PWAPI.getPageletByID("ASDASD", False);
AMM_DERIVED.HTMLAREA = "
" | &Pagelet.Title |
"
" | &Pagelet.Execute() | "
";

AMM_DERIVED.HTMLAREA is the HTML area that you place on the page to display the embedded pagelet.

And the pagelet on the page will look like the one below.

Interesting right? Try it out!

Implementing Related Content

2011-09-22T14:09:00.004+05:30

Just tried out implementing the same and it worked out well. Below are the steps to do the same.

Go to PeopleTools > Portal > Related Content Service > Define Related Content Service to create a new Related Content Service.

You can select the URL type you like. I selected the Pagelet type and provide the pagelet name in the URL information scroll. I wanted that Pagelet to appear as the realted content data.

Security option you can select either the public access or the available one. In case of Related Content Security check box, please refer to Peoplebook on how to do the same.

Now go to PeopleTools > Portal > Related Content Service > Manage Related Content Service.

Here you will assign the Related Content to your desired component/page. In the screen shot below, I mapped the related content to the page not to the component. To ensure security go to the Map Fields link and save.

Now you can navigate to the component where you mapped the Related Content. On landing to that component you can see a related content drop down on the upper right portion of the page. Click on the same to view the Related Content you mapped.

You can expand and collapse the Related Content section as you wish.

Pop up feature

2011-09-21T18:28:00.008+05:30

You might have seen the above pop up in the JOB data page. Wanted to look into this long time back; just today I thought of the same and looked into it.

It is fairly simple thing. Go to the employee field on the job_data1 page and navigate to the use tab in the definition property; you can see the popup page assigned.

I just created a pop up page and assigned to the emplid field to test it and it worked perfectly well.

As you see the feature is easy to implement. However its effectiveness depends on how we use it.

New PIA Installation

2011-09-15T18:53:00.009+05:30

Requirement can happen for creating additional PIA's for the exisitng PS HOME. How to do it. Below are the steps to create the same.

1.Log on to the app server box
2.Shut down PIA
3.Go to C:\PSOFT\HRSA90\setup\mpinternet and run Setup.exe (If you are on windows) and follow the screenshots below:

Be sure to select "Existing Weblogic Domain" when you get to the "Select domain type" step.

Be sure to select "Install additional PeopleSoft site" when you get to that point.

The website name should be _pwd, for example:Please specify a name for the PeopleSoft web site:Website name:[ps] ps_pwd

Enter the same app server and port number as the primary website. To get this use PeopleSoft trick [CTRL+J]

For web profile name, enter the same web profile that is created for this PIA.
To create it go to PeopleTools > WebProfile > Copy webprofile. You can clone any web profile and provide that name here. In this case 'NEW' is the name of the new Web Profile created.

Once you reach the above step, click Install button to create the site.

You would not be having a seperate bat file for Start and Stop PIA.

Now to check the new PIA, Start the AppServer and modify the old PIA login link.

Old PIA will be looking like this: http://localhost/psp/ps/?cmd=login

New PIA: http://localhost/psp/ps_new/?cmd=login

To view the Login page (Signin.html) for the old and new PIA's; navigate to \webserv\peoplesoft\applications\peoplesoft\PORTAL\WEB-INF\psftdocs

Tino Simon

The Strings Table

2011-08-27T21:01:00.005+05:30

I have seen this in a couple of SQR reports and this will be a good tip for the developers.

What is this Strings Table?

The PeopleTools Strings table (STRINGS_TBL) stores textual strings used for language sensitive labels and other text in PS/nVision and SQRs to avoid hard-coding labels into the report files themselves. The use of strings rather than hard-coded text in reports enables translators to translate the report layout in the database without editing the report’s code itself.This enables you to run a single copy of a report in multiple languages, while avoiding the duplication of code and report logic.Each row in the Strings table keys each string to a STRING_ID, which is associated with one of two different string types:

• The short (RFT Short) or long (RFT Long) field description of the field.

The STRING_ID must equal the field name.

• A free-form text string.

In either case, when a report requests a string in a particular language, the system returns the translated string if a translation is available. If no translation exists, the base language string is returned.

The Strings table is also keyed by a PROGRAM_ID field, which enables you to classify strings into groups that are used in similar reports. PROGRAM_ID can refer to a specific SQR or PS/nVision report name, or it can be a mnemonic for a group of common strings that are shared between reports

Click the picture to expand

Find Object Navigation

2011-08-27T00:30:00.004+05:30

Might be a good handy tip. Those who knows this feature already please excuse. Searching the navigation is easy with this feature. try it out.

Click the picture to expand

PeopleSoft Ping

2011-08-27T00:02:00.002+05:30

Just another good weekend. Expected some more work today, however it did not happen. Then I thought of just playing around the instance and saw this utility. Never seen or heard before. However it seemed interesting and gave a search in PeopleBooks. The details are given below. Hope this might be a new information for you too. For those who have worked with this utility; please feel free to comment.

The PeopleSoft Ping utility collects timestamps by sending a specific page to different tiers of the PeopleSoft system, starting at the browser, then going to the web server, the application server, the database and back. The timestamps that are collected are total time elapsed for the round trip, and arrival and departure time at each of the tiers.

To use the PeopleSoft Ping feature, select PeopleTools, Utilities, PeopleSoft Ping.

Nice weekend!

Testing Web Service with SendMaster utility

2011-08-24T19:17:00.004+05:30

It was in version 9.1 in which first I tried creating a Web Service to GET and POST the data. This was fairly an easy job coz in 9.1 we have delivered SOAP tester. We can use it right after Generating the SOAP template, which is under the Service Utilities.

Click to expand the picture

However while trying to help a colleague I found that in 9.0 the Invoke Service Operation button is not available and we brought up the Send Master Utility.

Now I want to write about how to test a WSDL in Send Master. See the screenshot below.

Click to expand the picture

The Service URL: It is the gateway URL.

In the Header box if it is not populating by itself; you have to type it out. But be sure that the SOAP action is correct. How to get that?

For getting the SOAP action, hit the wsdl on the browser and search for the soapAction.

Dynamic Page Title

2011-08-22T16:35:00.004+05:30

One of my colleagues had a small requirement to change the page title dynamically. The example is the RUNCTL_ASOFDATE page in HRMS.

Activating Verity Search Engine

2011-07-24T22:15:00.003+05:30

Frankly speaking, I never did this before. When there happened a need of activating the Verity Search engine, I Google'd it and found the below article from Shyam's PeopleSoft Blog. I am re-posting that article here for reference.

------------------------------

VERITY Search Engine, PORTAL_INDEX process, Building a Search Index and Searching the Portal

What does Verity Search engine do?

How to build a Search Index?

What PORTAL_INDEX application engine program does?

How to retrieve content references path by searching the portal?

This post is an attempt to answer these questions.

Verity Search Engine:

PeopleSoft Portal technology provides a tremendous search feature using the Verity search engine. PeopleSoft Portal and Verity Search technologies combine and give portal users easy and efficient search on content references registered in the portal registry.

Registry Search Index:

The portal registry collections are generated by the portal administrator and stored on the application server. This can be either done manually or by a scheduled process. The application engine used to build the registry search index is PORTAL_INDEX.

This process can be run manually from the navigation:

PeopleTools -> Portal -> Build Registry Search Index

Verity Collection files are stored on the application server in the location:

%PS_HOME%\data\search\< index_name >\< db_name >\ < lang_cd >

%PS_HOME% - the home directory in which PeopleSoft application server is installed

< index_name > - name of the application or the portal name for portal registry that the collection is serving (PORTAL)

< db_name > - as the name signifies name of the database

< lang_cd > - PeopleSoft local language code. A single portal will have only one collection per locale. Each holds the text files used to build the collection - input.bif and input.dat

Several numbers of subdirectories exist beneath the language code directory. These hold the various elements of actual collection used by Verity to conduct the search.

Running the PORTAL_INDEX application engine process builds a search collection which includes few elements from the content references in the registry and get included in the index.

We know that Content References can be accessed from

PeopleTools -> Portal -> Structure & Content -> <(nested) folder_name> -> ContentReference

PeopleSoft Component Content References have the following information:

ICType

Market

PanelGroupName

iScript Component Content References have the following information:

ICType

The following information is collected for all Content References:

Label

Description

Author

Product

Valid from Date

Valid to Date

Creation Date

Content Provider

URL

Path

Attributes

In the Content Reference Attributes, a set of keywords are entered for PeopleSoft delivered content references. These keywords are specified in Attribute Value and not in Name or Label. To add keywords to a content reference the NAME for the content reference attribute must be KEYWORD and add the search words or phrases separated by commas.

To have the Label and Attribute values to be a translate table click the Translate checkbox. Two distinct tables are used for attributes. One is translatable into other languages while the other is not. For example, PORTAL_HIDE_FROM_NAV = False is used internally by the system and should not be translated however keywords should be translated.

For accurate and high performance searches through the portal, the search engine must reference a comprehensive, up-to-date search index. The search index must be easy to maintain, as content is likely to change frequently within the portal registry.

How the Search Index is built?

The Application Engine (AE) program - PORTAL_INDEX has to be launched through the process scheduler.

This AE process launches a PeopleSoft C++ program. The C++ program queries the tables in the 'Portal Registry' for search content and it builds two text files. These files are created as .BIF and .DAT which are used by Verity to build its collection.

The AE process then launches the Verity program - MKVDK. The MKVDK program builds the Search index (Verity collection) based on the content in .BIF and .DAT files

Running the PORTAL_INDEX process:

In a busy portal where the content gets frequently changed it is absolutely important to refresh the search index often. Every time the search index is built, the existing search index is overridden. So, generally it is better to schedule this process in batch environment. Alternatively, the process can be also run manually.

Go to: PeopleTools -> Portal -> Build Registry Search Index

After creating or reusing an existing Run Control ID, Run the process. Before running the process, under Language Options, check the 'All Installed Languages' check box as required.

Searching the Portal after building Search Index:

Type any valid keyword in Search and click 'Go'. The search performs the following steps:

The case of the entered text gets changed to uppercase automatically. This string enables the Verity search engine to search for the text irrespective of case type.

The query string is formatted and passed to the Search API. The formatting includes filtering out hidden content references, expired content references and invalid content references based upon from and to dates.

Calls the Search API which returns the query results

Calls the Portal Registry API. This is done to apply security filtering to the results. Security is applied in PeopleCode by checking the 'Authorized' property.

Formats and displays the search results.

For every content reference returned by the search results page, the following fields are displayed:

Content reference label - a hyperlink which on a click takes directly to the content reference URL

Long Description of the content reference

Path - breadcrumbs to the content reference

Glossary of PeopleSoft Enterprise Terms

2011-07-01T11:19:00.000+05:30

Setup BPEL with PT 8.X

2011-06-24T18:27:00.002+05:30