Your First SWS Service

Let’s create your first SWS service to see how easy it is to extract information from PeopleSoft.

Categories:

Introduction

SWS combines two products. One product is advanced and caters to a specific group of users. The other has a broader application. In our first example, we’ll concentrate on the simplest method to create a web service. This involves crafting a SQL statement that can be exposed as a web service. This documentation will focus on the other areas of SWS. In this first example, we will focus on a quick win to expose a list of users and their emails.

Define Our Use Case

In this use case, the objective is to provide a list of users with their names, employee IDs, and primary email addresses to an external system.
Let’s further assume that the external system is going to have several other data requests that will be custom to this system/vendor. Therefore, we will “scope” our APIs to the vendor system in the URL path which will make more sense as we progress through this example. We will use “acmecorp” as the vendor name in our URL path.
We also know from the vendor that they need specific field names in the output as they cannot handle data translation. Therefore, we will need to rename the fields in our output.

Create a SQL Statement

The first step is to create a SQL statement that will return the data we need. In this case, we will use the following SQL statement. This was generated by PS Query and copied out but you can do what magic SQL you want.

SELECT B.EMPLID, B.NAME, A.EMAIL_ADDR
  FROM PS_EMAIL_ADDRESSES A, PS_PERSON_NAME B
  WHERE  A.PREF_EMAIL_FLAG = 'Y'
     AND A.EMPLID = B.EMPLID

Create the SWS Service

Navigate to: CHG Custom -> Simple Web Service Setup
Use “Add Mode” to add a new value.
Unique Identifier: This is auto-generated by SAVE.
Description & Notes:** Any Metadata you want to add to the service.
URL Path: This is the URL path that will be used to access the service. In this case, we will use acmecorp as the vendor name in our URL path. We further added /people/email to have a full path of /acmecorp/people/email. This will be in the URL that the vendor will use to access the service.
Request Type: SQL
SQL Statement: The SQL statement we created above.
Output Fields: This is where we will rename the fields to match the vendor’s requirements. We will rename the fields to UserID, LegalName, and PrimaryEmail. These fields were communicated by the vendor.

The second half of the page contains some miscellaneous options that we will not cover in this example. We will cover these in other examples. Leave the defaults.
Parameters: The parameters section does not need any values as the vendor will NOT pass in any parameters to filter or limit the data for this simple example.
Allowed Permission Lists: This is important and defines how users can access the system. We cover this in detail in the Security section. We granted the web service to our admin permission list and a new permission list we created just for ACME. We created a user to represent the ACME Corp server and the security structure looks like this:
- OPRID: ACMECORP_SWS_USER
  - Password: phoebe-PROBABLE-dallas
  - Role: ACMECORP_SWS_USER
    - Permission List: ACMECORP_SWS_USER
      - Web Service: CHG_SWS.CHG_SWG_GET

We can go to the last table and get the URL to test the service. We will use the URL in the next section. We give you HTTP and curl syntax.

I like HTTP Syntax as I think it is very clear. Let’s create our base64 encoded user name and password (Postman can do this for you) and ask for JSON accept: application/json. We will see if a response comes back. I am removing some of the data because we did not have a row limit specified and we have not implemented pagination which is possible but those are more advanced topics to cover later.


GET http://psft-cs-858.c.peoplesoftdemo-1470578694381.internal:8000/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/acmecorp/people/email
accept: application/json
authorization: Basic QUNNRUNPUlBfU1dTX1VTRVI6cGhvZWJlLVBST0JBQkxFLWRhbGxhcw==


HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-type: application/json; encoding=UTF-8
date: Thu, 21 Sep 2023 01:03:27 GMT
transfer-encoding: chunked
x-oracle-dms-ecid: 8458013e-3913-40bb-9698-190771ec7d9a-0000001f
x-oracle-dms-rid: 0
x-peoplesoftrequestid: ad31d07e-581a-11ee-a26c-7f8dcd811d6d
x-success: True

{
  "data": [
    {
      "UserID": "FAI036",
      "LegalName": "Shard Yahq",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FAEQT0001",
      "LegalName": "Sandra Falix",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FAEQT0003",
      "LegalName": "Ortega Pariara",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FAEQT0008",
      "LegalName": "Rinda Marnar",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FAEQT0012",
      "LegalName": "Judith Alkins",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FASS0150",
      "LegalName": "Timothy Rond",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FASS0153",
      "LegalName": "Tim Rihela",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FASS0154",
      "LegalName": "Tona Ricado",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FASSFAN001",
      "LegalName": "Apple Fan",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    }
  ],
  "errors": "",
  "meta": {
    "rowCount": "4011",
    "sqlIDExecuted": "d7fb7404-b1dc-4acd-b062-6d9f529f9b00",
    "success": "True",
    "debugMessages": "",
    "QueryString": "NULL",
    "URLPath": "acmecorp/people/email",
    "finalSQL": "SELECT B.EMPLID, B.NAME, A.EMAIL_ADDR\n  FROM PS_EMAIL_ADDRESSES A, PS_PERSON_NAME B\n  WHERE  A.PREF_EMAIL_FLAG = 'Y'\n     AND A.EMPLID = B.EMPLID",
    "productVersion": "2023-06-07",
    "toolsVer": "8.58.07",
    "currentUser": "ACMECORP_SWS_USER",
    "responseDTTM": "2023-09-21 01:03:29.000000",
    "psftTransactionId": "ad31d07e-581a-11ee-a26c-7f8dcd811d6d",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "ServerDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessage": ""
  }
}

If you go back and look at the setup in the miscellaneous section we asked for metadata to be generated and we get back this JSON object which provides very helpful information for debugging.

"meta": {
    "rowCount": "4011",
    "sqlIDExecuted": "d7fb7404-b1dc-4acd-b062-6d9f529f9b00",
    "success": "True",
    "debugMessages": "",
    "QueryString": "NULL",
    "URLPath": "acmecorp/people/email",
    "finalSQL": "SELECT B.EMPLID, B.NAME, A.EMAIL_ADDR\n  FROM PS_EMAIL_ADDRESSES A, PS_PERSON_NAME B\n  WHERE  A.PREF_EMAIL_FLAG = 'Y'\n     AND A.EMPLID = B.EMPLID",
    "productVersion": "2023-06-07",
    "toolsVer": "8.58.07",
    "currentUser": "ACMECORP_SWS_USER",
    "responseDTTM": "2023-09-21 01:03:29.000000",
    "psftTransactionId": "ad31d07e-581a-11ee-a26c-7f8dcd811d6d",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "ServerDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessage": ""
  }

Do you want XML instead? Easy-peasy, just change the accept header.


GET http://psft-cs-858.c.peoplesoftdemo-1470578694381.internal:8000/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/acmecorp/people/email
accept-encoding: gzip, deflate, br
accept: application/xml
authorization: Basic QUNNRUNPUlBfU1dTX1VTRVI6cGhvZWJlLVBST0JBQkxFLWRhbGxhcw==
user-agent: httpyac

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-type: application/xml; encoding=UTF-8
date: Thu, 21 Sep 2023 01:16:11 GMT
transfer-encoding: chunked
x-oracle-dms-ecid: 8458013e-3913-40bb-9698-190771ec7d9a-00000020
x-oracle-dms-rid: 0
x-peoplesoftrequestid: 7343166f-581c-11ee-a26c-7f8dcd811d6d
x-success: True
<?xml version="1.0"?>
<response>
  <data>
    <row>
      <UserID><![CDATA[FAI036]]></UserID>
      <LegalName><![CDATA[Shard Yahq]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>
    <row>
      <UserID><![CDATA[FAEQT0001]]></UserID>
      <LegalName><![CDATA[Sandra Falix]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>
    <row>
      <UserID><![CDATA[FAEQT0003]]></UserID>
      <LegalName><![CDATA[Ortega Pariara]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>
    <row>
      <UserID><![CDATA[FAEQT0008]]></UserID>
      <LegalName><![CDATA[Rinda Marnar]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>
    <row>
      <UserID><![CDATA[FAEQT0012]]></UserID>
      <LegalName><![CDATA[Judith Alkins]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>
    <row>
      <UserID><![CDATA[FASS0150]]></UserID>
      <LegalName><![CDATA[Timothy Rond]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>
  
  </data>
  <errors></errors>
  <meta>
    <rowCount>4011</rowCount>
    <sqlIDExecuted>d7fb7404-b1dc-4acd-b062-6d9f529f9b00</sqlIDExecuted>
    <peopleSoftRequestID>7343166f-581c-11ee-a26c-7f8dcd811d6d</peopleSoftRequestID>
    <success>True</success>
    <dbName>CS92U020</dbName>
    <QueryString><![CDATA[NULL]]></QueryString>
    <URLPath><![CDATA[acmecorp/people/email]]></URLPath>
    <finalSQL><![CDATA[SELECT B.EMPLID, B.NAME, A.EMAIL_ADDR
  FROM PS_EMAIL_ADDRESSES A, PS_PERSON_NAME B
  WHERE  A.PREF_EMAIL_FLAG = 'Y'
     AND A.EMPLID = B.EMPLID]]></finalSQL>
    <productVersion>2023-06-07</productVersion>
    <peopleSoftUser>ACMECORP_SWS_USER</peopleSoftUser>
    <peopleToolsVersion>8.58.07</peopleToolsVersion>
    <requestTime>2023-09-21-01.16.11.000000</requestTime>
    <debugMessage><![CDATA[]]></debugMessage>
  </meta>
</response>

Or how about CSV? Just change the accept header. You will notice with CSV any metadata is in the header and the data is in the body. This is a common pattern with CSV.

GET http://psft-cs-858.c.peoplesoftdemo-1470578694381.internal:8000/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/acmecorp/people/email
accept-encoding: gzip, deflate, br
accept: text/csv
authorization: Basic QUNNRUNPUlBfU1dTX1VTRVI6cGhvZWJlLVBST0JBQkxFLWRhbGxhcw==
user-agent: httpyac

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-type: text/csv; encoding=UTF-8
date: Thu, 21 Sep 2023 01:18:36 GMT
transfer-encoding: chunked
x-dbname: CS92U020
x-errors: 
x-peoplesoftrequestid: ca315226-581c-11ee-a26c-7f8dcd811d6d
x-peoplesoftuser: ACMECORP_SWS_USER
x-peopletoolsversion: 8.58.07
x-productversion: 2023-06-07
x-requesttime: 2023-09-21-01.18.37.000000
x-rowcount: 4011
x-sqlidexecuted: d7fb7404-b1dc-4acd-b062-6d9f529f9b00
x-success: True
x-urlpath: acmecorp/people/email

"UserID","LegalName","PrimaryEmail"
"FAI036","Shard Yahq","HCMGENUser1@ap6023fems.us.oracle.com"
"FAEQT0001","Sandra Falix","HCMGENUser1@ap6023fems.us.oracle.com"
"FAEQT0003","Ortega Pariara","HCMGENUser1@ap6023fems.us.oracle.com"
"FAEQT0008","Rinda Marnar","HCMGENUser1@ap6023fems.us.oracle.com"
"FAEQT0012","Judith Alkins","HCMGENUser1@ap6023fems.us.oracle.com"
"FASS0150","Timothy Rond","HCMGENUser1@ap6023fems.us.oracle.com"
"FASS0153","Tim Rihela","HCMGENUser1@ap6023fems.us.oracle.com"
"FASS0154","Tona Ricado","HCMGENUser1@ap6023fems.us.oracle.com"
"FASSFAN001","Apple Fan","HCMGENUser1@ap6023fems.us.oracle.com"
"FASSFAN002","Honey Fan","HCMGENUser1@ap6023fems.us.oracle.com"
"FASSFAN003","Cookie Fan","HCMGENUser1@ap6023fems.us.oracle.com"
"FASSFAN004","Gavin Kravitz","HCMGENUser1@ap6023fems.us.oracle.com"
"FASSFAN005","KATHERINE HURADO","HCMGENUser1@ap6023fems.us.oracle.com

Summary

In this example, we created a simple web service that returned a list of users and their email addresses. We used a SQL statement to return the data and we renamed the fields to match the vendor’s requirements. We also showed how to test the service using HTTP and curl. We also showed how to get metadata back from the service to help with debugging. We also showed how to get the service to return XML and CSV.

This is a production-ready web service that was created in about 3 minutes!!!!! (not counting the security setup)