By | 13 August 2015

A look at the “List of Values” (SCC_GET_LOV) Web Service

When external systems outside of PeopleSoft integrates with any PeopleSoft system, they likely need the setup values from PeopleSoft. For example, the valid list of department codes, the valid list of subject codes, the valid list of business units. This could be for the third party to present those values to some end user in a web page.

One use case for this is where you have an external Admissions Application for your University created in some other non-PeopleSoft technology. For discussion sake, let’s say it is PHP. That PHP application needs to present drop downs for the applicant that coincide with the PeopleSoft values so when the data is pushed back into PeopleSoft, the PHP application is already storing the PeopleSoft field codes like STRM, ACAD_PROG, etc. There are likely situations where your PHP application may not actually want to display all the values for STRM or ACAD_PROG. Perhaps it is a Graduate application only so the values shown should be limited to that functional scope.

There are various ways to extract this information from PeopleSoft using web services. In this article we will describe the PeopleSoft Campus Solutions (CS) Web Service called the “List of Values” that is called by a third parting using the SCC_GET_LOV Service Operation. It provides an interface that solves some of the nuances with a third party integration and allows control over what setup values the third party sees within PeopleSoft.

List of Value Configuration Table

First to enable an external system to use the LOV web service, you have to setup each and every record/field edit table that you want that third party to pull from PeopleSoft. Basically, the SCC_GET_LOV service operation and the accompanying setup table gives a third party visibility into a “prompt lookup table” for a record field. Those all too familiar magnifying glasses on PeopleSoft pages. In the PeopleSoft code, these are all backed by either an “xlat” edit or an edit table pointing to some view on a record field that define the valid codes for that field.

The List of Values setup table allows for many different things to happen which gives the PeopleSoft Administrator control over what the third party sees.

  • You can exclude values from the result set.
  • You can override the view that is used from what is behind the backing table defined in application designer.
  • You can define “bind” values that will limit the list. These can be setup so that the third party has to pass them in the request or they can be hard coded by the administrator.
  • You can setup a “context” which is a user configured string that defines a unique set of values. For example, let’s say you have two different third party applications that need to get the ACAD_PLAN_TBL.ACAD_CAREER list of values. Let’s further assume that you want to return different values to those systems. You can do that with the LOV setup by defining two different “contexts” and defining some different views for the ACAD_PLAN_TBL.ACAD_CAREER under each context. Then each of those applications knows to call the LOV service with the context you have provided for them. It is just an additional parameter in the web service.
    • I would recommend setting up a unique context for each 3rd party using the LOV service.
  • You can also define different results sets if the web service is being called from “Admin Mode”/Super User Mode. We will not go into this in this article. However, one of the use cases is that you may have an admin screen that is allowed to set different PeopleSoft codes than a student or applicant. So you can get the additional admin codes back from the service if needed. See the AAWS documentation on how to submit something in Admin mode.

The Setup table is here: Set Up SACR > System Administration > Utilities > List of Values > List of Values

We will look at some specific examples next.

Simple Example – List of Institution Code

First lets look at a LOV setup from a demo database to pull a list of institution codes.

The LOV setup is defined at the Context and Record level. Under those high level keys you can define what fields are allowed to be returned in the web service.

In the screen-shot above we are looking at the Default context for the ADM_APPL_DATA record and we are at the field name of INSTITUTION. This setup says that the list of INSTITUTION codes returned should come from the INSTITUTION_TBL and the DESCR field will be used as the return. If the “Enable for Web Service” is NOT checked then these values cannot be pulled by the third party. In this example, there are no Prompt Table filters as INSTITUTION is really the highest level setup KEY value in Campus Solution. (We will see some example usage of this later.)

So now that we have that setup let’s look at how to invoke this operation. We will be using standard HTTP notation to stay language agnostic.

I am assuming that you know how to configure all the integration broker steps and setup the service operation. If you need help with that then see the “Additional Reading” section for some related articles that step you though that configuration.

Here is what a POST in the HttpListeningConnector Looks like.

POST /PSIGW/HttpListeningConnector HTTP/1.1
Host: ibtest.cedarhillsgroup.com
OperationName: SCC_GET_LOV.v1
Content-Type: text/xml
From: NU_MALEK
To: DEMO
Password: secret

<?xml version="1.0"?>
<SCC_LOV_REQ>
  <LOVS>
    <LOV name="INSTITUTION_LIST">
      <LOVCONTEXT>DEFAULT</LOVCONTEXT>
      <RECORDNAME>ADM_APPL_DATA</RECORDNAME>
      <FIELDNAME>INSTITUTION</FIELDNAME>
    </LOV>
  </LOVS>
</SCC_LOV_REQ>

The response back from a demo database is.

<?xml version="1.0"?>
<SCC_LOV_RESP xmlns="http://xmlns.oracle.com/Enterprise/HCM/services">
	<LOVS>
		<LOV name="INSTITUTION_LIST">
			<VALUES>
				<VALUE>
					<CODE>GLAKE</CODE>
					<DESC>Great Lakes University</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSAUS</CODE>
					<DESC>PeopleSoft Australia Uni</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSCCS</CODE>
					<DESC>PS Community College System</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSNLD</CODE>
					<DESC>PeopleSoft University -  NLD</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSNZL</CODE>
					<DESC>Silver Fern University</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSSTA</CODE>
					<DESC>PeopleSoft State University</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSUCE</CODE>
					<DESC>PSU Community Education</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSUNV</CODE>
					<DESC>PeopleSoft University</DESC>
				</VALUE>
			</VALUES>
		</LOV>
	</LOVS>
</SCC_LOV_RESP>

There are a few things to discuss here.

  • We added a “name” attribute to the LOV: <LOV name="INSTITUTION_LIST">. This is optional and it will be useful in one of our final examples. However, you will see that this name was echoed back in the response.
  • We are calling the LOV with the default context.
  • The response only returns two field per row. CODE and DESC. There is no way to add additional fields to this output. Therefore, this is more geared toward pulling drop down values for the user. The PeopleSoft prompt table on a PeopleSoft page could have the ability to filter and search for the valid values based on different search keys on the prompt table. This is NOT conveyed in the LOV service. You get only the code and a configurable description field. So you could not use the LOV service to pull something like a list of classes offered in Summer of 2016 if you wanted to also display the campus, start dates, instructor, etc.

Example with Filter and Exclusion

Let’s look at a more complicated example to pull a list of ACAD_CAREERs from the service. In this case we need to pass in an INSTITUTION code for the careers we want. That is configured in the “Prompt Table Filters” grid. In this screenshot we have it setup to say that the third party must submit the institution they want in the XML.

I also setup a few values to exclude in the result set: TECH, MEDS. In this example, we hard coded the exclusion in the LOV lookup. You could have accomplished the same thing by using a different “prompt table” in the LOV setup that had the same exclusion.

The HTTP Payload is slightly different. You should now see that we are passing in some key/value pairs to satisfy the filter.

POST /PSIGW/HttpListeningConnector HTTP/1.1
Host: ibtest.cedarhillsgroup.com
OperationName: SCC_GET_LOV.v1
Content-Type: text/xml
From: NU_MALEK
To: DEMO
Password: secret

<?xml version="1.0"?>
<SCC_LOV_REQ>
  <LOVS>
    <LOV name="CAREER_LIST">
      <LOVCONTEXT>DEFAULT</LOVCONTEXT>
      <RECORDNAME>ACAD_PLAN_TBL</RECORDNAME>
      <FIELDNAME>ACAD_CAREER</FIELDNAME>
     <KEYS>
        <KEY>
          <FIELDNAME>INSTITUTION</FIELDNAME>
          <FIELDVALUE>PSUNV</FIELDVALUE>
        </KEY>
      </KEYS>
    </LOV>
  </LOVS>
</SCC_LOV_REQ>

The results coming out of a demo database are this.

<?xml version="1.0"?>
<SCC_LOV_RESP xmlns="http://xmlns.oracle.com/Enterprise/HCM/services">
	<LOVS>
		<LOV name="CAREER_LIST">
			<VALUES>
				<VALUE>
					<CODE>BUSN</CODE>
					<DESC>Graduate Business</DESC>
				</VALUE>
				<VALUE>
					<CODE>CNED</CODE>
					<DESC>Continuing Education</DESC>
				</VALUE>
				<VALUE>
					<CODE>GRAD</CODE>
					<DESC>Graduate</DESC>
				</VALUE>
				<VALUE>
					<CODE>LAW</CODE>
					<DESC>Law</DESC>
				</VALUE>
				<VALUE>
					<CODE>UENG</CODE>
					<DESC>Undergraduate Engineering</DESC>
				</VALUE>
				<VALUE>
					<CODE>UGRD</CODE>
					<DESC>Undergraduate</DESC>
				</VALUE>
			</VALUES>
		</LOV>
	</LOVS>
</SCC_LOV_RESP>

Combining LOV calls into one HTTP Call

The SCC_GET_LOV web service is actually designed in such a way that you can submit more than one record/field LOV request into a single HTTP call. The XML structure is nicely designed for this. If you do this then the name attribute is needed to parse the result: <LOV name="XXX">

Let’s combine the two requests above into a single combined call and see what happens.

I will leave out all the HTTP information and just show the XML payload submitted.

<?xml version="1.0"?>
<SCC_LOV_REQ>
  <LOVS>
    <LOV name="INSTITUTION_LIST">
      <LOVCONTEXT>DEFAULT</LOVCONTEXT>
      <RECORDNAME>ADM_APPL_DATA</RECORDNAME>
     <FIELDNAME>INSTITUTION</FIELDNAME>
     </LOV>
      
    <LOV name="CAREER_LIST">
      <LOVCONTEXT>DEFAULT</LOVCONTEXT>
      <RECORDNAME>ACAD_PLAN_TBL</RECORDNAME>
      <FIELDNAME>ACAD_CAREER</FIELDNAME>
     <KEYS>
        <KEY>
          <FIELDNAME>INSTITUTION</FIELDNAME>
          <FIELDVALUE>PSUNV</FIELDVALUE>
        </KEY>
      </KEYS>
    </LOV>
  </LOVS>
</SCC_LOV_REQ>

You see that we have two LOV nodes nested inside the LOVS parent node. You will also see that we gave each LOV a unique name.

The response back is one combined result set with our names echoed back.

<?xml version="1.0"?>
<SCC_LOV_RESP xmlns="http://xmlns.oracle.com/Enterprise/HCM/services">
	<LOVS>
		<LOV name="INSTITUTION_LIST">
			<VALUES>
				<VALUE>
					<CODE>GLAKE</CODE>
					<DESC>Great Lakes University</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSAUS</CODE>
					<DESC>PeopleSoft Australia Uni</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSCCS</CODE>
					<DESC>PS Community College System</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSNLD</CODE>
					<DESC>PeopleSoft University -  NLD</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSNZL</CODE>
					<DESC>Silver Fern University</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSSTA</CODE>
					<DESC>PeopleSoft State University</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSUCE</CODE>
					<DESC>PSU Community Education</DESC>
				</VALUE>
				<VALUE>
					<CODE>PSUNV</CODE>
					<DESC>PeopleSoft University</DESC>
				</VALUE>
			</VALUES>
		</LOV>
		<LOV name="CAREER_LIST">
			<VALUES>
				<VALUE>
					<CODE>BUSN</CODE>
					<DESC>Graduate Business</DESC>
				</VALUE>
				<VALUE>
					<CODE>CNED</CODE>
					<DESC>Continuing Education</DESC>
				</VALUE>
				<VALUE>
					<CODE>GRAD</CODE>
					<DESC>Graduate</DESC>
				</VALUE>
				<VALUE>
					<CODE>LAW</CODE>
					<DESC>Law</DESC>
				</VALUE>
				<VALUE>
					<CODE>UENG</CODE>
					<DESC>Undergraduate Engineering</DESC>
				</VALUE>
				<VALUE>
					<CODE>UGRD</CODE>
					<DESC>Undergraduate</DESC>
				</VALUE>
			</VALUES>
		</LOV>
	</LOVS>
</SCC_LOV_RESP>

Additional Reading & Alternative Methods