Configuration 🌟

At the heart of SWS is a single PeopleSoft Service Operation called CHG_SWS_GET. This single service operation can serve infinite use cases. A PeopleSoft super-user who has read this documentation configures SWS to create new web services using a configuration page delivered as part of SWS. This configuration page lives inside your PeopleSoft database. It is the job of the CHG_SWS_GET service operation to interpret the configuration, enforce security, run SQL and export the results to the client.

This section discusses configuring this service in detail in this section as well as the security model and how to configure API client users.

Planning a new web service

Before configuring a new web service there are a few things to think through. We will cover them here at a high level and then go into detail in the subsequent sections. New web services are trivial to set up with SWS. However, without a little upfront thought, you can end up with some confusion for the users of your APIs.

URLs and Paths

In REST web services, the URL paths have an implied meaning and provide a hierarchy that should map to some logical structure of the underlying data it exposes. This paradigm is carried forward into SWS. The URL path is used to find the logic to run to export data over the web services. So you need to think through the URL path structure and how it will map to the SQL or PsoftQL statements you want to expose.

Thinking about the path hierarchy gives the API clients a good structure to reason about. When reading the paths from left to right, they start out broad and narrow down the request. A URL Path structure could be something like this:

• {prefix}/person/{emplid}
  • This output information about a person and could possibly include some child data like phones, addresses, etc.
• {prefix}/person/{emplid}/phone
  • This would output phones for a person. You might give this out to a team that needs to know phone numbers for a person and are not interested in other data or have security to see that other data.
• {prefix}/person/{emplid}/address/
  • This would output addresses for a person.
• {prefix}/person/{emplid}/address/{address_type}}
  • This would output a specific address type for a person.
• {prefix}/security/users/{oprid}/
  • This would output general information on a specific OPRID.
• {prefix}/security/users/{oprid}/roles/
  • This would output roles a user was a member of.

In those examples above, they are fairly generic paths. You can also have more specific paths that are more targeted to a specific use case. Let’s imagine that you have an integration with your SSO system. You need to expose some data that they need. Their requests do NOT seem to fit into any other categories that you have configured before. Perhaps those other SWS configurations expose too much data or sensitive data the SSO team should not see. They have very limited information and that team cannot use the generic paths above because the data is too broad. So you can create a more specific path for them. Let’s imagine that they need to know information about a person as well as to know if a person is active or not. You could create a path like this:

• {prefix}/sso/person/{emplid}/
  • This may return general information about a person like name, email, etc.
• {prefix}/sso/person/{emplid}/status
  • This may return a simple yes/no if the person is active or not.

In the case above, we “scoped” the SSO end points to the /sso/* path. If you had some other integration partners with very specific requirements then you could create a scoped path as well like /acmecorp/*. You can create as many paths as you need. You can also create paths that are not scoped to a specific integration partner. You can create paths that are scoped to a specific business process. For example, you could create a path for all the data needed for a specific business process like expense reports or invoices.

SWS is flexible. It provides no guidance on how to structure your paths. You can create as many paths as you need.

In SWS, the Path string is used to look up a configuration that ends up running some SQL and exporting the results to the client. So you need to think through the path structure and how it will map to the SQL or PsoftQL statements you want to run. You also configure security to these paths. As we will see shortly, a path maps to a specific SWS configuration row in the database. That configuration row has a security grid that allows you to specify what permission lists are allowed to run that configuration.

Syntax Types

There are two syntax types that SWS supports. When you configure a new web service you must choose one.

• SQL - You can configure a SQL statement to to run.
• PsoftQL - You can configure a PsoftQL statement to run.

When an HTTP client is calling a specific SWS path, the code in the web service handler looks up the configuration for that path. The configuration has a syntax type. The handler will use that syntax type to determine how to export data to the client. There are pros and cons to each syntax type. Here is a quick summary:

Security

Security - Security is very important with PeopleSoft data as the database holds sensitive information. SWS is based on the PeopleSoft REST services. The only viable authentication mechanism for REST based services is “Basic Authentication” which is tied to a PeopleSoft OPRID and password stored in PSOPRDEFN. There is a very thorough document on REST Security in our Integration broker book. That should be your reference on how REST authentication works and the best practices. We assume you have read it and are following the best practices.

There is more explanation of security in the Security Page

Configuring a New Web Service

Configuring a new web service in SWS is easy and fast. You can deploy a new SQL or PsoftQL statement as a new web service in a few minutes. We will walk through all the configuration sections in this section. In later sections, we will show how to create different web services in order to give you some ideas of how this can be used.

Here is a screenshot of the SWS configuration page for a simple person example. Let’s go through each configuration at a high level. Then in later sections, we will drill into the detailed functionality offered.

In the example above we have configured the following PsoftQL statement to run at this path: /doc-example/person/{{emplid}}

{
  "isDebugMode": true,
    "records": [
        {
            "recordName": "PERSON",
            "sqlWhereClause": "  EMPLID = {{emplid}}",
            "excludeFields": [
                "BIRTHDATE",
                "BIRTHPLACE",
                "BIRTHCOUNTRY",
                "BIRTHSTATE",
                "DT_OF_DEATH"
            ]
        },
        {
            "recordName": "NAMES",
            "parentRecordName": "PERSON",
            "includeDescriptionsFor": [
                "NAME_TYPE"
            ],
            "criteriaFields": [
                {
                    "fieldName": "NAME_TYPE",
                    "fieldValue": "PRI"
                }
            ] 
        },
        {
            "recordName": "EMAIL_ADDRESSES",
            "parentRecordName": "PERSON",
            "includeDescriptionsFor": [
                "E_ADDR_TYPE"
            ]
        },
        {
            "recordName": "PERSONAL_PHONE",
            "parentRecordName": "PERSON",
            "includeDescriptionsFor": [
                "PHONE_TYPE"
            ]
        },
        {
            "recordName": "ADDRESSES",
            "parentRecordName": "PERSON",
            "includeDescriptionsFor": [
                "ADDRESS_TYPE"
            ]
        }
    ]
}

Let’s decode this configuration. We are configuring a PsoftQL statement to run. It is asking for this structure.

• PERSON

  • NAMES
  • EMAIL_ADDRESSES
    • SWS will return all email address types since there are no filters
  • PERSONAL_PHONE
    • SWS will return all phone types since there are no filters
  • ADDRESSES
    • SWS will return all effective dated and active addresses since there are no filters. This is SWS auto-magic handling of effective dates and EFF_STATUS

• Additionally, we are asking that we exclude some fields from the PERSON record. We do not want to expose these fields to the client.

  • BIRTHDATE
  • BIRTHPLACE
  • BIRTHCOUNTRY
  • BIRTHSTATE
  • DT_OF_DEATH

• We are also making this a single response for a single EMPLID. We have the {{emplid}} variable in the path. That will be substituted into the PsoftQL statement at run time.

• For NAMES, we are limiting it to only return the PRI name type.

• We are also asking for several description fields to be included in the output. This is a feature of PsoftQL.

• This SWS configuration is limited to a single permission list SWS_DOC_ACCOUNT, which is a service account we setup for this example.

We can then call this web service using an HTTP Request like this:

GET https://127.0.0.1:8000/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/doc-example/person/FA0003
accept: application/json
authorization: Basic U1dTX0RPQ19BQ0NPVU5UOkRJUkdFLXNwZWxsaW5nLWphZGUtdml0aWF0ZQ==

The response will look like this:

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-length: 1271
content-type: application/json; encoding=UTF-8
date: Mon, 25 Sep 2023 19:00:34 GMT
x-oracle-dms-ecid: 786dc482-39b0-4314-8f25-9a91bc734fdb-0000002a
x-oracle-dms-rid: 0
x-peoplesoftrequestid: ce514ec3-5bd5-11ee-adcb-d5409ab5d8fe
x-success: True

{
  "data": {
    "PERSON": {
      "objectType": "record",
      "objectName": "PERSON",
      "fields": [
        {
          "rowNumber": 1,
          "EMPLID": "FA0003",
          "LAST_CHILD_UPDDTM": "",
          "NAMES": {
            "objectType": "record",
            "objectName": "NAMES",
            "fields": [
              {
                "EMPLID": "FA0003",
                "NAME_TYPE": "PRF",
                "EFFDT": "1998-07-09",
                "EFF_STATUS": "A",
                "COUNTRY_NM_FORMAT": "001",
                "NAME": "Abban,Ali",
                "NAME_INITIALS": "",
                "NAME_PREFIX": "",
                "NAME_SUFFIX": "",
                "NAME_ROYAL_PREFIX": "",
                "NAME_ROYAL_SUFFIX": "",
                "NAME_TITLE": "",
                "LAST_NAME_SRCH": "ABBAN",
                "FIRST_NAME_SRCH": "ALI",
                "LAST_NAME": "Abban",
                "FIRST_NAME": "Ali",
                "MIDDLE_NAME": "",
                "SECOND_LAST_NAME": "",
                "SECOND_LAST_SRCH": "",
                "NAME_AC": "",
                "PREF_FIRST_NAME": "",
                "PARTNER_LAST_NAME": "",
                "PARTNER_ROY_PREFIX": "",
                "LAST_NAME_PREF_NLD": "1",
                "NAME_DISPLAY": "Ali Abban",
                "NAME_FORMAL": "Ali Abban",
                "NAME_DISPLAY_SRCH": "ALIABBAN",
                "LASTUPDDTTM": "",
                "LASTUPDOPRID": ""
              },
              {
                "EMPLID": "FA0003",
                "NAME_TYPE": "PRI",
                "EFFDT": "1998-07-09",
                "EFF_STATUS": "A",
                "COUNTRY_NM_FORMAT": "001",
                "NAME": "Abban,Ali",
                "NAME_INITIALS": "",
                "NAME_PREFIX": "Ms",
                "NAME_SUFFIX": "",
                "NAME_ROYAL_PREFIX": "",
                "NAME_ROYAL_SUFFIX": "",
                "NAME_TITLE": "",
                "LAST_NAME_SRCH": "ABBAN",
                "FIRST_NAME_SRCH": "ALI",
                "LAST_NAME": "Abban",
                "FIRST_NAME": "Ali",
                "MIDDLE_NAME": "",
                "SECOND_LAST_NAME": "",
                "SECOND_LAST_SRCH": "",
                "NAME_AC": "",
                "PREF_FIRST_NAME": "",
                "PARTNER_LAST_NAME": "",
                "PARTNER_ROY_PREFIX": "",
                "LAST_NAME_PREF_NLD": "1",
                "NAME_DISPLAY": "Ali Abban",
                "NAME_FORMAL": "Ms Ali Abban",
                "NAME_DISPLAY_SRCH": "ALIABBAN",
                "LASTUPDDTTM": "",
                "LASTUPDOPRID": ""
              }
            ]
          },
          "EMAIL_ADDRESSES": {
            "objectType": "record",
            "objectName": "EMAIL_ADDRESSES",
            "fields": []
          },
          "PERSONAL_PHONE": {
            "objectType": "record",
            "objectName": "PERSONAL_PHONE",
            "fields": [
              {
                "EMPLID": "FA0003",
                "PHONE_TYPE": "MAIN",
                "COUNTRY_CODE": "",
                "PHONE": "",
                "EXTENSION": "",
                "PREF_PHONE_FLAG": "Y"
              }
            ]
          },
          "ADDRESSES": {
            "objectType": "record",
            "objectName": "ADDRESSES",
            "fields": [
              {
                "EMPLID": "FA0003",
                "ADDRESS_TYPE": "PERM",
                "EFFDT": "1998-01-14",
                "EFF_STATUS": "A",
                "COUNTRY": "USA",
                "ADDRESS1": "10299 Placid Place",
                "ADDRESS2": "",
                "ADDRESS3": "",
                "ADDRESS4": "",
                "CITY": "New Market",
                "NUM1": "",
                "NUM2": "",
                "HOUSE_TYPE": "",
                "ADDR_FIELD1": "",
                "ADDR_FIELD2": "",
                "ADDR_FIELD3": "",
                "COUNTY": "",
                "STATE": "MD",
                "POSTAL": "21774",
                "GEO_CODE": "",
                "IN_CITY_LIMIT": "",
                "ADDRESS1_AC": "",
                "ADDRESS2_AC": "",
                "ADDRESS3_AC": "",
                "CITY_AC": "",
                "REG_REGION": "",
                "LASTUPDDTTM": "",
                "LASTUPDOPRID": ""
              }
            ]
          }
        }
      ]
    }
  },
  "errorMessages": "",
  "errors": "",
  "meta": {
    "rowCount": "1",
    "sqlIDExecuted": "71906a11-57b0-4831-b227-f5238ed1be58",
    "success": "True",
    "debugMessages": "",
    "QueryString": "NULL",
    "URLPath": "doc-example/person/FA0003",
    "finalSQL": "{\n    \"records\": [\n        {\n            \"recordName\": \"PERSON\",\n            \"sqlWhereClause\": \"  EMPLID = 'FA0003'\",\n            \"excludeFields\": [\"BIRTHDATE\",\"BIRTHPLACE\", \"BIRTHCOUNTRY\", \"BIRTHSTATE\", \"DT_OF_DEATH\"]\n        },\n        {\n            \"recordName\": \"NAMES\",\n            \"parentRecordName\": \"PERSON\"\n        },\n        {\n            \"recordName\": \"EMAIL_ADDRESSES\",\n            \"parentRecordName\": \"PERSON\"\n        },\n        {\n            \"recordName\": \"PERSONAL_PHONE\",\n            \"parentRecordName\": \"PERSON\"\n        },\n        {\n            \"recordName\": \"ADDRESSES\",\n            \"parentRecordName\": \"PERSON\"\n        }\n    ]\n}",
    "productVersion": "2023-06-07",
    "toolsVer": "8.58.07",
    "currentUser": "SWS_DOC_ACCOUNT",
    "responseDTTM": "2023-09-25 19:00:34.000000",
    "psftTransactionId": "ce514ec3-5bd5-11ee-adcb-d5409ab5d8fe",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "ServerDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessage": ""
  }
}

Configuration Options

Let’s cover each field on the SWS configuration page.

• Unique Identifier: This is a system generated GUID that will be generated at save time. It is used to uniquely identify this configuration. You can not change this value and it should be safe to export to another database unlike a integer counter.
• Description: This is the description of the configuration. This is for admin use and is used in PeopleSoft search records.
• URL Path: This field is very important and will map to the full URL that a client will use to target your web service. This has to be unique across the database.
• Active: Allows you to easily turn on or off the configuration.
• Notes: This is a section for you to add notes about this web service. This might be internal notes or links to documentation or development tickets.
• Request Format Type: PSOFT-QL or SQL - This defines what syntax SWS uses to query and return data.
  • If “Psoft-QL request format type was chosen then a long text box will show up called. Psoft-QL Text (JSON)
    • You enter the Psoft-QL that you want to run in response to a client request. This has a very particular format.
  • If “SQL” request format type was chosen then a long text box will show up called. SQL Statement Text
    • You enter the SQL that you want to run in response to a client request.
  • SQL Statement Text: This is the section where you enter the SQL statement that will run.
  • There is some special syntax for binding variables from the client request to the SQL statement. We will cover that detail in the next section.
• Row Limit: Optional row limit. You can specify a non-zero number here and the handler will stop the output after that number of output rows has been encoded.
• Default Encoding: Used to set a default encoding scheme if the client does not include one in the request. All SWS web services automatically handle JSON, CSV and XML encoding.
• Include metadata in Response: If checked then some additional information about the SWS request and response will be encoded. We will show some examples of this shortly.
• (danger) Minutes to Cache Response: You can optionally configure to have the integration broker cache the response. This can be done for performance reasons if the SQL is very “expensive”. Please read the Caching article before enabling this. There are some hidden dangers.
• Parameters Grid: This grid allows you to configure how the client passes parameters that are substituted into the SQL. If your SQL does not have any variables provided by the client, there will be no rows here. We will cover this in depth shortly.
• Output Fields Grid: This will only show for a “SQL” Request Format Type. This grid is where you name the SQL columns that show up in the encoding section. For JSON and XML encoding, the values here will be the names of the properties and nodes. For CSV, these will be the header values.
• Allowed Permission Lists Grid: This grid is where you configure SWS to tell what permission lists are authorized to run this SQL statement. There is a detailed security section below that documents how to use this and setup API client users.
• Copy & Delete - Inside this group box are buttons to clone/copy the current configuration or to delete it.
• DMS Export - Inside this group box is a generated DMS script template that can be used to export your configuration between databases.
• HTTP Info Page - This page gives you both HTTP and curl examples on how to invoke the service operation. This removes the guesswork and speeds up the testing cycle.