Delivered Web Services - Query Access Service and Process Scheduler Service

    Before we deep dive in to PeopleSoft delivered Web-Services, let's talk about the basics of PeopleSoft Web Service. What is it.? "It provides a way other (3rd party) applications communicate with PeopleSoft Application, using Integration Broker".

    Why Web-Service and why not other methods of integrations? like file-based if you ask, then my response would be simple. It is built on-top of existing PeopleTools Technology and provides controls for the owner system (PeopleSoft) to have necessary security (Authentication and Authorization) for data communication and standards (like SOAP and RESTful APIs) to support almost all the integrating applications. In the recent Tools version you can even create Open API consumer services.

    Now, since the basics are clear, lets focus on web services that are provided by Oracle PeopleSoft out-of-the-box in the application. These services are common for any application you own, be it a HCM, FSCM, CS or CRM etc., Which can be grouped in to following two, (as per PeopleTools 8.62 PeopleBook)

1. Query Access Services (aka QAS)
• Query Creation Services (QCS)
• Query Security Services (QSS)
• Query Metadata Services (QMS)
• Query Execution Services (QES)
2. Process Scheduler Services
• Schedule Request Services
• Monitor Request Services

Query Access Services (aka QAS)

    These services provides a way to Create, Access and Execute Queries simply by using necessary APIs. Think about the scenario where a Consultant just sits and do all the hard job of creating and providing access to the query or even get the report and share it via email/SharePoint every time when someone needs to see what is inside the PeopleSoft DB. Instead if you can able to provide these API access (obviously with all the controls) and 3rd Party application can able to do all these works themselves without any dependency of the PeopleSoft Consultants.

Query Creation Services

    Provides 20+ SOAP and RESTful services to access record information, record relationships, field information, tree details. And with Save/Delete the query that the user is having access to.

Query Security Services

    Serves 10 APIs to get Roles, Users and their relationships in the application.

Query Metadata Services

    Lists the query details, fields, field translates and prompt details for better understanding of the query and planning the execution.

Query Execution Services

    Serves APIs for the query execution, status fetching and results download.

Process Scheduler Services

    In this we have about 9 Schedule Request Services and another 9 Monitor Request Services. Using this any system can able to initiate the process and monitor the status. Consider a scenario that you need to provide some processed data to a 3rd party on demand basis, e.g., Leave Balance of each staff, and for that you need to initiate the Absence Calculation in HRMS as and when they need the data.

This increase the dependency of Support Consultant or Application Admin and availability. By using these delivered services those dependencies can be avoided.

    Both set of services utilizes one or many of the Query Security, Service Operation Security, WS-Security and Process Profile while invoking any APIs. And uses SSL and/or Login Credentials for Authentication.

    I know "SEEING IS BELIEVING", let's have a walkthrough of using Query Execution Service (Service Operation: QAS_EXECUTEQRY_REST_GET) API using Postman tool step by step.

You first need a query to execute, it can be created using the QCS or existing queries can be used.

Query Details

Then update the WS-Security according to the need in this example I'm setting only Basic Authentication (as I'm planning to call consume the service from Postman and I don't want to bother my PS Admin to install the Postman SSL Certificate on this server).

Web Service Security

Prepare the URI parameters to be filled for our need, in this query we don't have any Prompt and Criteria so the URI templates can be build by using following parameters.

1. OwnerType
2. QueryName
3. OutResultType
4. OutResultFormat
5. isconnectedquery
6. maxrows
7. json_resp

One URI Template I took it from the service to give an idea how you use these parameters on the URL End-Point to access the service.

Template:{OwnerType}/{QueryName}/{OutResultType}/{OutResultFormat}?isconnectedquery={isConnectedQuery}&maxrows={MaxRow}&prompt_psqueryname={Prompt_PSQueryName*}&prompt_uniquepromptname={Prompt_UniquePromptName*}&prompt_fieldvalue={Prompt_FieldValue*}&json_resp={json_response}

Sample URI: PUBLIC/BD_PERS_BASIC_QRY/JSON/NONFILE?isconnectedquery=N&maxrows=100&json_resp=true

Setup the Postman to call the service and see the results,

Parameters:

URI Parameters

Authorization Details:

Authorization Details

Note: Ensure the given User ID has access to execute the PS Query specified.

Hit "Send" and get the response, since we have kept json_resp parameter true we are getting the response in JSON or else the response will be on XML

API Response

Here is the formatted output using "Visualization" feature available in Postman,

Table formatted response

Pretty easy right? If you think from business angle the use-cases are plenty.

Let's give it a try and comment the use case of yours.

Happy Learning.

Composite Query - A clever way to filter and prune data from existing queries

As the name suggests the composite query can simply combine the existing queries and can add filters, aggregates, sorts the data for presenting.

All you just have to do is, 

1. Create PS Queries
2. Combine two or more of them
3. Join them based on relationship
4. Apply additional filters/prompts needed
5. Provide Group By or Order By clauses
6. View and schedule to get the results

You might ask why would we create composite query when we already have so many PS Queries and Connected Queries which we can use to get the data we want, right?. To you the answer would be you can RE-USE the existing queries and add controls on top of it.

Consider an example of having separate queries for fetching Employee Details, Salary Information and Login Details, to fetch the high paid employees login details you don't have to create a new query. Simple combine these three and create it as a composite query. Or you have Customer Information and Aging Report and wanted to get the summary report to give a follow-up call for each customer.  

For better understanding I have taken a simple use case of fetching the recently logged employee details along with their Component Interface accesses to use in my Audit Analysis.

To do that I have created 3 PS Queries with simple SQL (no fancy filters or GROUP BY or ORDER BY clauses),

Then combine the first two base queries, Reporting Tools > Composite Query > Composite Query Manager

Added the 3 queries with necessary joins (here I used left out join as I wanted to skip those who don't have CI access. And I used Row Security Class for simplicity, instead I could have used PSROLECLASS and PSROLEUSER to fetch the actual security.

Select the needed fields only, and then added some filters.

Also added some order by clause to get the recent rows on top.

Note: GROUP BY can be used in reference to the aggregate functions used in other selected fields.

Following aggregates are available for you Average, Sum, Count, Count Distinct, Min and Max.

There, your composite query is ready and change the status as "Active" to use it. 

You can directly download results via Reporting Tools > Composite Query > Composite Query Viewer or schedule it via Composite Query Scheduler.

When your query is not correctly build like having incorrect expression or prompt, you can see the errors in process log.

And of course there are some limitations in using the Composite Query, but I think those will be ok for the intended purpose. Which you can refer on latest PeopleBook link.

Follow my blog for more tutorials and insights on PeopleSoft applications and PeopleTools.

Thanks

Universal Data Loader

    After a decade of time, I'm back to blogging, And it's time to give back the community that helped me grow in my career and skills with some of works.

    As a first step, let me present my efforts on the vision to create a common upload utility I wanted since I started as a developer. Many customers have their own modules (bolt-on or customized) developed on their application and using delivered systems only when necessary.

    And once in a while they load bulk data in those modules from 3rd party, or processed with offline tools (like Excel). With this base all those customers can benefit by simply having a CI - Component Interface for the same.

The process is simple as follows,

1. You create and perfect your CI based on the data you want to load
2. Build your own upload templates with defaults, formats, etc., 
3. Complete the setup to create the File Layout (CSV or Fixed format)
• Which can provide you the sample file in FIXED as well as XLSX format
4. Perfect your data using Excel or Text pad
5. Use the universal uploader process to load your content

 Few screens I have provided here to give a glimpse,

Benefits of this utility,

1. No need of new process (or AE) to create every time you have different set or component to load
2. Utility is capable of creating the File Layout automatically, so reduced technical work
3. Sophisticated logs and information so that the technical or functional user aware what to do
4. Multiple Templates for a single Component Interface to suite the business needs

You can find the project in my GitHub, (Better-Designs)

At present the process will load the data in FIXED file format only., I'm planning to improve the same to work for CSV and XML and also with CI logic based (so that you will not loose business validations). Any suggestion to improve the design and quality are welcomed.