PeopleSoft Composite Query

What is PeopleSoft Composite Query?

Composite query is a new feature of Peoplesoft reporting tools 8.54 which provides the option to combine data from existing queries and present data as the single and flattened query result. It also allows to use apply filters, aggregates, and so on before presenting the report results.  Composite queries created based on web services requests are not persisted in the database. The based queries can be the existing queries that were created using Query Manager, Query API, or QAS.

Main Highlights:

Composite Query provides option to:

• Select the SQL pruning option to remove tables and fields that are not needed based on users’ attribute selections.
• Set the ORDER BY and GROUP BY clauses.
•   Set the WHERE clause as a filter over the result set of a composite query.
• Prune the based queries at runtime based on the selected output fields, join criteria, filter criteria, and ORDER BY clause.
• View pruned SQL statements for the composite query.
•    Run the composite queries using Application Server.
•  Reuse a composite query definition.
• Specify join criteria, field dependencies within a query, and field or table pruning options at query design time.
•  Set the result limit by selecting the attributes in the based queries.

Creating a Composite Query

The following example illustrates creating a composite query, from two existing queries to get the combined results. In this example we are using the queries Job Data and Personal data to get the Job and personal details of all the active employees.

Step1:

Navigation: Main Menu -> Reporting Tools -> Composite Query -> Composite Query Manager 

Add a new value

Step2: 

Select two of the existing queries, the alias names once selected here cannot be changed.

Step3:

In the next step we specify the joins. In this example as we are querying the employee details, Emplid field is joined in both the queries.

Step4:

Select the output fields from both the queries.

Step 5:

In this step we select the filters, Having clauses etc if any and save the query.

Limitations of Composite Query:

• Composite Query should not be used for returning a large number of rows. The result set returned from a composite query should be 10,000 rows or less at runtime. The time-out limitation to run a composite query is the time-out limitation of the application servers. Need to make sure that construct the Join Criteria, Filter Criteria, and aggregations to reduce the amount of data returned by a composite query.
•  Composite query does not change the SQL statements or the result sets of existing queries
•   Relationships between different based queries are expressed by fields, not by expressions or prompt.
• Users must specify the field dependencies to ensure correctness of SQL pruning.
• If a query has the UNION construct, pruning is based on the main SELECT clause
• A composite query does not parse expressions.
•  If users need to sort the composite query results, they have to use Composite Query Manager to specify the Order By clause for the composite query.     

Author,

Shubha Rohit

Kovaion-Peoplesoft Consulting Practice

Email: shubha.rohit@kovaion.com

PeopleSoft Fluid User Interface

 With the latest release of PeopleTools 8.54  , Oracle has introduced  the Fluid User  Interface experience.

The Fluid UI provides the users with the ability to access PeopleSoft applications across a variety of form factors from smart phones to tablets to desktops/laptops. Fluid also provides a common user experience on variety of devices regardless of the screen size. Through this responsive design, browser will resize itself depending on device size.

Lets have  a look at the home page that is rendered after the latest peopletools has been used.

The important features of home pages are as below:

•  Home Pages
•  Tiles
• PeopleSoft Search widget
•   Notifications widget
• Actions widget
• Navigation Bar widget.

The fundamental feature of the Fluid Model is the Home Page which provides the base for the users or the customers to navigate and carry out the respective works.

In a way Fluid home pages are similar to the current home pages but  fluid model provides the end user with the new User experience that renders well on the dashboard or the home pages.

Tiles/Grouplets are lifeline of Fluid UI page. Tiles help the user to navigate to a fluid page or classic PIA page.

Business Benefits

•  Device independent
• Gives the response based on user device
• Can be personalized based on needs e.g. Tiles can be added or deleted from homepage based on personal needs

      Development Steps 

Let us design a simple fluid UI page for following requirement..

• To create a fluid homepage for Recruiter
• To add a tile for Applicant search

      1) Development of Fluid Home Page

Developing a fluid home page is fairly easy and is done through PIA.

Navigation:  Peopletools> Structure and Content>Fluid Structure and Content>Fluid Homepages.

Add a new homepage. Usage Type should be Landing Page as shown in figure below.

       2)      Development of a Fluid Component

Development of a fluid component goes in the same traditional PeopleSoft way- creation of fields, record, page, component, menu and finally creating the content reference. Fluid properties are defined at page and component level.

Step1: Create a new fluid page

While creating a new fluid page, choose Layout Page. Layout page is the template page for a fluid UI page.

Step2: Select Fluid UI Page Properties

After page is created, fluid UI properties can be set in Page properties.

In Page properties, user should focus more on Form Factor Override and Suppress Form Factor.

Form Factor Override: Overrides the style of page based on device size.

Suppress Form Factor: Suppresses the page from displaying based on device size. E.g.  If Small is checked, page will not appear on most smart phones.

Step 3: Create a new component.

Creating a new fluid component works in the same traditional PIA way. But the search pages are not displayed for fluid applications.

Step 4: Set component fluid properties

As shown in above figure, there are two main checkboxes, Fluid mode and Small Form Factor Optimized

Step6: Add the Tile in home page

Navigation: Main Menu>Fluid Home>Homepage 

1. Click on Action Widget
2. Click on Personalize
3. Click on Add Tile
4. Select the content reference which has to be added as a tile.

  Fluid UI is ready for test.

Clicking on tile 'Applicant List' will open  the designed Fluid UI page.

Best Practices:

 Always check Display on Small form Factor Homepage while creating the content reference. This will show the tile on small devices

.

Limitations:

• Classic PIA search for a component does not work in Fluid. User should create a separate search by selecting the correct layout.
• Currently, only light weighted applications can be deployed in fluid mode e.g. components like add a person, job data which demands heavy data entry are not suited for fluid mode.
• Currently, some features, like spell check, rich text editor, Find In for grids, Download to Excel, modal movement or re-sizing, are not available in fluid.

         Author: Madhur Mohini

  Kovaion-Peoplesoft Consulting Practice

         Email: madhur.mohini@kovaion.com

SQL Update on PSURLDEFN, Application Server and GetURL(URL.XXXX) - All connected

Today, we had an interesting issue at one of our customer site.

When a PeopleSoft environment is refreshed (production / post upgrade refresh), Environment Management team (DBAs) will mask sensitive data using scripts or tools. PSURLDEFN is one of the key table to be masked.

We recently had a environment refresh (Post Upgrade). One issue was reported where the connectivity to the third party system was lost after the refresh (upgrade). On analysis, the PSURLDEFN was blanked out during the refresh process and not reverted with actual values. So, the DBAs repopulated the data using SQL scripts. Post URL data population also, the same connectivity issue was encountered.

On further Analysis, GetURL(URL.XXXX) was used in PeopleCode to get the third party system URL. GetURL() function fetches the information from the App Server Cached details. This was designed for a quicker response on the Application Server. The issue was finally resolved by DBAs clearing the cache and Application Servers bounced.

So, if there are any SQL update on the PSURLDEFN table, its good practice to clear the Application Server Cache.

PeopleSoft demonstrations at UKOUG APPS14

It is our pleasure to share with you some PeopleSoft insights that we presented during the UK Oracle User Group APPS14 conference held from 8th - 10th December. We successfully demonstrated practical solutions and tools that transform enterprise performance. Below is a brief synopsis about our involvement.

If you require more details, please feel free to contact our PeopleSoft Consulting Practice on peoplesoft.team@kovaion.com. We will be in touch shortly to determine if we can assist you in any way with your Oracle, PeopleSoft or Fusion requirements.

UKOUG APPS14

APPS14 is the UK Oracle User Group’s Applications Conference & Exhibition 2014 and is the largest event that UKOUG delivers for this community. The conference took place from 8th - 10th December. Over 800 attendees came together at the ACC Liverpool to expand their Oracle knowledge through an extensive agenda and learn about the latest products on the market from the leading Oracle Applications vendors.

PeopleSoft @ UKOUG APPS14

The PeopleSoft agenda covered four streams (Human Resources, Financials, Global Payroll and Technology) presented over two days. Speakers from Oracle, end users and suppliers focused on product development, real world experiences and innovative ideas.

The agenda for PeopleSoft included:

·         The PeopleSoft Community Keynote was delivered by Gwennan Goriot on an update about the Oracle PeopleSoft product line.

·         Graham Smith demonstrated the PeopleSoft Portal to delegates.

·         Mark Thomas presented a session on the best practices in PeopleSoft 9.2 upgrade.

·         An open Q&A discussion lead by Gwennan Goriot allowed delegates to put their questions to Oracle

·         Roundtables brought together like-minded delegates to discuss challenges particularly focusing on Upgrades.

Kovaion presented 2 sessions on PeopleSoft and also took part in the Round table discussion.

·         PeopleTools 8.5x – The New Experience

o   Oracle-PeopleSoft is determined and bringing new features to PeopleTools. More than 1000 new enhancements have been added by Oracle on PeopleTools 8.5x (including new applications). Kovaion demonstrated the practical usage of PeopleTools Portal Technologies (Related Content), Integration Technologies (REST based service with JSON) and PT 8.54 Fluid MSS page/dashboard.

·         Automate PeopleSoft Testing

o   Application testing is one of the many challenges organizations face as part of a new implementation, upgrade or system changes. Complex as it is, testing involves ensuring that the application delivers expected results in the areas of functional specifications, user interface interactions, security and performance. Kovaion, using practical experience gained in the field, showcased the ROI on testing automation and  test methodologies. We also demonstrated the capabilities of PeopleSoft Test Framework for Regression Testing together with JMeter for Performance testing. Watch this space for the launch of our PeopleSoft Test Factory.

·         9.2 Upgrade Planning & Execution Open Discussion

o   PeopleSoft 9.2 has generated a huge amount of interest with users – with over 1,000 new features including the new fluid mobile approach. This round table focused on bringing together a number of users that have either completed or are currently undertaking an upgrade. They  discussed  their challenges from business case inception to successful deployment. Mark Thomas (Hays) hosted the discussion with Nanda Kumar(Kovaion), Graham Smith (Oxfam), Adrian Nuttall (Oceaneering) and Chris Pannell (Allen & Overy) as panel members.

  

Kovaion has been helping companies transform their IT landscape by providing services around ERP, Analytics, Cloud, Mobility and Testing.  As a trusted partner, we offer services with greater flexibility and are different by virtue of our determination, conviction and creativity in handling customer requirements. We provide niche services on the terms of consulting, implementation, upgrade, training and staffing. Let us know if we can help you or get in touch via info@kovaion.com. 

Wish you merry Christmas and a very Happy New Year ! ! ! 

Regards,

Nanda Kumar

Founder & Director

Kovaion Consulting

m: +44 (0) 75741 64244
e: nanda.kumar@kovaion.com

w: www.kovaion.com

“Had a good show at UKOUG APPS14 Conference. Follow #ukoug_apps14”

Peoplesoft Outlook Integration

What is Outlook Integration?

Outlook Integration enables integration between PeopleSoft and Microsoft Outlook to schedule meetings and appointments. It makes things easy for organizations predominantly engaged with MS Outlook to schedule meetings driven by PeopleSoft data.

Utility Benefit:

·         Keeps communications and schedules in an organised way.

·         Simple and easy to configure & maintain.

·         Increases the productivity by tightly coupling PeopleSoft data and Outlook schedule.

·         Fast, effective and two-way integration keeps processes simple and reduces duplication

Sample Business Scenarios:

·         In Talent Acquisition Manager (TAM) manage interview schedules, interview directly in MS Outlook with updates applied on candidate records. Quickly link emails, schedules interview and adds attachments to candidates

·         MS Outlook can be integrated with PeopleSoft HR Notification to schedule meetings.

·         Absence takes can be requested by entering absence requests through Microsoft Outlook.

Implementation Steps for outlook plug in:

·         Install the PeopleSoft Outlook Plug in

·         Configure outlook menu in PeopleSoft settings and option add on menu. (Refer fig 1 for configuration details)

·         Make Setups based on each module (TAM, Absence Management, ePerformance etc.)

Prerequisites for PeopleSoft outlook Plugin:

To enable the communication and interaction between Microsoft Outlook and the PeopleSoft system, following are required:

·        The Microsoft Add-In—Represented in the PeopleSoft Outlook menu and handles all of the communication and information within Microsoft Outlook.

·        The PeopleSoft Outlook URL Handler—An external Windows application that allows you to drill into Microsoft Outlook directly from the PeopleSoft system.

How to get the PeopleSoft Outlook Plug-in?

The plug-in can be found as compressed files in the PeopleSoft path: "<<PeopleSoft Installation HomeDirectory>>\src\DesktopIntegration". Users can extract the various integration objects (Outlook Add-In, Outlook URL Handler or Word Add-In) and install.

PeopleSoft Outlook Menu:

After installing the PeopleSoft Outlook plug-in, user can see in the outlook, a new menu option PeopleSoft as shown in below screen.

Configuration: User can define the integration options with PeopleSoft and the other required setups like server details, User ID, Email Account, etc.

Based on the features selected, the relevant appointments, tasks, scheduling etc. will be possible from outlook. All the features that can be used from PeopleSoft to Outlook are covered under Enterprise components-Desktop Integration

}  Task- A task is a way for something to be queued up and put into the user’s tasks.

}  Task Queue- You can use the queue to manually add tasks to a user's Microsoft Outlook task list.

}  Appointment- Define the defaults for the user's appointments, invitations, and calendar.

}  Appointment Queue- To review the appointment queue, monitors integration transactions, or manually adds/updates meetings and appointments.

}  File Transfer- Set up file transfer registration.

}  File Transfer Queue- To review what you previously defined as defaults in your file transfers.

}  Address Book- To Register your address book.

}  Dynamic Displays- Dynamic Displays allow the display of information from your PeopleSoft system alongside items in Microsoft Office. You can use Dynamic Displays to show any information contained within the PeopleSoft system and those displays can optionally be shown automatically alongside the Microsoft Office items.

PeopleSoft and Outlook Integration Solutions

Out of Box integrations

   Out of the box, Oracle-PeopleSoft calendar integrations are available for modules like Absence      Management, ePerformance, Talent Acquisition Manager.

Let’s look at the calendar Integration with TAM.

Calendar integration with TAM: PeopleSoft delivered the setup for integrating TAM with    outlook. Three options are provided to user: Full Integration, Partial Integration and No   Integration.

Navigation: Setup HCM > Install>Product Specific>Recruiting Installation

• Full Integration: It is a two way integration between Microsoft Outlook and PeopleSoft which updates data changes in PeopleSoft when changed from Outlook.

• Partial Integration: Sends two notifications to the applicants: .ics attachment and regular notification. Send’s regular notification to Interviewers.Below screen shots show the mails received by the applicant triggered by selecting Partial Integration while scheduling the interview of the applicant.

Below screen shots show the mails received by the applicant triggered by selecting Partial Integration while scheduling the interview of the applicant.

• No Integration: Disables calendar integration with outlook

• Customization: It is possible to integrate Outlook with People code using Icalendar.Icalendar is a file format which allows Internet users to send meeting requests and tasks to other users. Below is the sample code 

import PT_MCF_MAIL:*; /*Declare variables*/ Local string &dateStr = DateTimeToLocalizedString(%Date, "MM/dd/yyyy"); Local string &dateStamp = DateTimeToLocalizedString(%Datetime, "MM/dd/yyyy hh:mm:ss"); /*Receiver and sender email id*/ Local string &mail_to = "susan.silvia@kovaion.com"; Local string &organizer = "Madhur.mohini@kovaion.com"; Local string &desc = "TEST"; &dateStr1 = DateTimeValue(&dateStr | " 2:00 PM"); &dateStr2 = DateTimeValue(&dateStr | " 5:00 PM"); /*Prepare date formats required for .ics format*/ &tempTime = DateTimeToTimeZone(&dateStr1, "Local", "UTC"); &dtstart = DateTimeToLocalizedString(&tempTime, "yyyyMMdd'T'HHmmss'Z'"); &tempTime = DateTimeToTimeZone(&dateStr2, "Local", "UTC"); &dtend = DateTimeToLocalizedString(&tempTime, "yyyyMMdd'T'HHmmss'Z'"); &tempTime = DateTimeToTimeZone(%Datetime, "Local", "UTC"); &dtstamp = DateTimeToLocalizedString(&tempTime, "yyyyMMdd'T'HHmmss'Z'"); Local string &str = Char(13); Local string &icalstr = "BEGIN:VCALENDAR" | &str; &icalstr = &icalstr | "PRODID:-//Prod ID//PeopleCode vCal 1.0//EN" | &str; &icalstr = &icalstr | "VERSION:2.0" | &str; &icalstr = &icalstr | "METHOD:PUBLISH" | &str; &icalstr = &icalstr | "X-MS-OLK-FORCEINSPECTOROPEN:TRUE" | &str; &icalstr = &icalstr | "BEGIN:VEVENT" | &str; &icalstr = &icalstr | "ATTENDEE;ROLE=REQ-PARTICIPANT;RSVP=TRUE:MAILTO:" | &mail_to | &str; &icalstr = &icalstr | "ORGANIZER:MAILTO:" | &organizer | &str; &icalstr = &icalstr | "DTSTART:" | &dtstart | &str; &icalstr = &icalstr | "DTEND:" | &dtend | &str; &icalstr = &icalstr | "LOCATION:Conference Room" | &str; &icalstr = &icalstr | "TRANSP:OPAQUE" | &str; &icalstr = &icalstr | "DTSTAMP:" | &dtstamp | &str; &icalstr = &icalstr | "CATEGORIES:Meeting" | &str; &icalstr = &icalstr | "DESCRIPTION:Meeting Request" | &str; &icalstr = &icalstr | "SUMMARY:" | &desc | &str; &icalstr = &icalstr | "X-MICROSOFT-CDO-BUSYSTATUS:BUSY"| &str; &icalstr = &icalstr |  "X-MICROSOFT-CDO-IMPORTANCE:1"| &str; &icalstr = &icalstr |  "X-MICROSOFT-DISALLOW-COUNTER:FALSE"| &str; &icalstr = &icalstr | "X-MS-OLK-AUTOFILLLOCATION:TRUE"| &str; &icalstr = &icalstr | "X-MS-OLK-CONFTYPE:0"| &str; &icalstr = &icalstr | "PRIORITY:5" | &str; &icalstr = &icalstr | "BEGIN:VALARM" | &str; &icalstr = &icalstr | "TRIGGER:PT15M" | &str; &icalstr = &icalstr | "ACTION:DISPLAY" | &str; &icalstr = &icalstr | "DESCRIPTION:Reminder" | &str; &icalstr = &icalstring | "END:VALARM" | &str; &icalstr = &icalstr | "END:VEVENT" | &str; &icalstr = &icalstr | "END:VCALENDAR"; Local PT_MCF_MAIL:MCFOutboundEmail &email = create PT_MCF_MAIL:MCFOutboundEmail(); &email.From = &organizer; &email.Recipients = &mail_to; &email.Subject = "Task " | &desc; &email.AddHeader("", "method=PUBLISH"); &email.AddHeader("", "charset=UTF-8"); &email.AddHeader("", "component=VEVENT"); &email.ContentType = "multipart/alternative"; rem &email.ContentType = "text/calendar"; Local PT_MCF_MAIL:MCFBodyPart &text = create PT_MCF_MAIL:MCFBodyPart(); &text.Text = " "; &text.ContentType = "text/calendar"; Local PT_MCF_MAIL:MCFBodyPart &iCal = create PT_MCF_MAIL:MCFBodyPart(); &iCal.Text = &icalstr &iCal.AddHeader("content-class", "urn:content-classes:calendarmessage"); &iCal.AddHeader("content-ID", "calendar_message"); &iCal.ContentType = "text/calendar;method=PUBLISH; charset=UTF-8"; Local PT_MCF_MAIL:MCFMultipart &mp = create PT_MCF_MAIL:MCFMultipart(); &mp.SubType = "alternative; differences=Content-type"; rem &mp.AddBodyPart(&text); &mp.AddBodyPart(&iCal); &email.MultiPart = ∓ &resp = &email.Send();

Limitations of PeopleSoft Outlook Integration:

• The integration is compatible with Microsoft Outlook 2007 or higher.

• Not compatible with all type of calendars like lunar calendars.(Icals).

• The debugging options are limited with Icals as the errors messages generated are generic. In-depth Icals knowledge along with PeopleSoft skillset is required to debug. 

Author,

Susan Silvia

Kovaion-Peoplesoft Consulting Practice

Email: susan.silvia@kovaion.com