The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Saturday, 1 June 2013

8 different methods to run a PSQuery


PS Query might appear as simple and less extensible tool provided by PeopleSoft. But in real world, some business users cannot even live without a PS Query. They prioritize their daily task based on some query output. What all simple information required by the user can be made available with this simple tool. Further even business users can have the ability to create a query of their own.

I will discuss here the different methods to utilize this simple tool, so as to extend the power of simplicity.

 1.       Query Viewer

This is the simplest and straight forward method to run a PS Query and view the output. To try this method, go to: Reporting Tools > Query > Query Viewer and give the query name. You will have the option to view the output online (HTML), as excel or xml file.

 2.       Schedule Query
If you are a regular user of PS Query and you run the query daily to get a list of transactions that you need to work on, then this is the best option you can try out. Schedule your query for a specific time frame and set the destination as email. So that you will get regular emails in your inbox and you don’t need to open the PeopleSoft page to see the list of actionable items.

To schedule your query, go to: Reporting Tools > Query > Schedule Query. Add a new runcontrol, select your query and click on run. On the run page makesure that you set the correct Recurrence option so that it is run daily/weekly/monthly etc…

 3.       Register as a content

Another prominent mechanism to reach out to your query is to register it as a content reference in the portal. PeopleSoft users are used to the culture of opening pages and components by the menu navigational structure. So registering the query as menu navigation link will be the best method for the traditional PS user community. Like opening any PeopleSoft component, you will be able to open the PS Query by going through the navigation and clicking on the link.

To register the PS Query as a menu link create a new content reference in the desired folder with the below options.
a.       In the URL Information group select the URL Type as PeopleSoft Generic URL
b.      In the Portal URL box, give the URL as q/?ICAction=ICQryNameURL=MY_QUERY. Replace the term MY_QUERY with your actual query name.

 4.       Running from a Push Button
This method is most suited when you want to run a PS Query from a push button or a hyperlink on your transaction page. You can think of this if your main truncation has some child transaction for which some action should be performed. In such scenario’s you can create a hyperlink/push button on the main transaction page, which will fetch all the child transactions for which actions are required.

You can implement it in two ways.

 a.       Create a push button/ hyperlink and set the destination as process. Now you can give the process name as PSQUERY and type as Application Engine. If your query contains prompts, it is your responsibility to pass the run parameters and destination types.

 b.      You can set the target of push button/hyperlink as PeopleCode and generate the query URL using any of the built in functions such as GenerateQueryContentURL, GenerateQueryPortalURL and GenerateQueryRelativeURL. Use these functions in conjunction with ViewURL() function to launch the query. This method have the advantage of opening the query results directly on the page or a new window.

 5.       Accessing query as a related content to a component or page

From tools 8.52 onwards you have this pretty stable option of linking related contents to a component or page. You can attach your query as a related content to your transaction component so that when you select it the query will appear on a separate frame on the right or bottom side of the existing component. This method has the advantage of (a) it will not disturb the real estate and UI of the existing page and (b) this method is a zero customization method of linking a query to a transaction component. For further details on creating related content you can refer my previous blogs on Related Contents and Related Content Services.

 6.       Query access from Feeds
From Tools 8.50 onwards you have the option of subscribing to a PS Query with RSS Feeds. To subscribe to a PS Query, you need to publish it as a Feed. To do that go to: Reporting Tools > Query > Query Manager and open the query. On the bottom, you have a hyperlink called Publish as Feed. Click on that give your options. Now on top of your query manager page you will get the feed option enabled. Clicking on it will take you to the feed URL. Use the URL to subscribe to the query using any standard subscriber tools. You can also retrieve the feed by going to: Main Menu > My Feeds link and search for the feed.

 7.       Query As a Service

This is another interesting option provided by PeopleSoft to invoke a PS Query from a third party application or external system. PeopleSoft has delivered Service and Service Operations to execute your query from an external source. You can utilize the power by enabling the service QAS_QRY_SERVICE service and its related service operations. To use this feature you need to add the service operations to the necessary permission lists.

 8.       Query from WorkCenters

With the release of PeopleSoft 9.2, PeopleSoft has introduced a one stop place to do all your works. Yes, your WorkCenters will also support PS Query’s. You can access the PS Query by going to the Report/Queries tab of the WorkCenter.



To add your own query to the WorkCenter, navigate to: Enterprise Components > WorkCenter/Dashboards > Configure Pagelets, open your WorkCenter and go to the tab Queries and add your query there. Remember to click on Activate Query Settings button to activate the changes you have made to the WorkCenter.


4 comments:

  1. How do we download a query using android tablet, like Google Pixel C? We cannot download query using iPAd.

    ReplyDelete
  2. how do we identify where the query is being used using SQL ?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Hi Manuel,

      Good question. The only thing that comes to my mind is querying the meta-tables associated with all the eight methods. There won't be a central place where you can query and get the results. Same PS Query can be run using one or more methods mentioned in the post. So you have to query the meta-tables associated with all the methods to get the desired result.

      Probably you could create a view with union of 8 SQLs and then query the view, if this is going to be a frequent exercise.

      Hope this helps. Feel free to share any other techniques that might have worked out for you.

      Thanks,
      Tony

      Delete

Followers