Creating Views of External Database Tables in Peoplesoft

Here are the steps to creating a view in PeopleSoft of data that exists in a database table that is external to your PeopleSoft database.

1. Create a view in the external database of the data you need access to in PeopleSoft.
2. Create a view in the PeopleSoft database of the view that you created in step 1.
3. Create a view record in App Designer of the view you created in step 2.
4. Build your view record in App Designer
5. Save your scripts from steps 1 and 2 so that you can use them to create the same views in the other database environments in your development path when you migrate your new project forward to production.

Using Oracle SQL notation, and using the view Y_ACAD_PROG_VW for my example, I'll explain steps 1 thru 3 below. (To clarify: this process works just as well if the external database happens to be a PeopleSoft database itself.)



 Step 1. Create a view of the data in the external database.

The  SQL for creating a view looks like this:

CREATE OR REPLACE VIEW link.PS_ACAD_PROG_LNK

AS

  SELECT DISTINCT(LEVEL_2_UNIT_ID),

    LEVEL_2_NAME

  FROM ACAD_PROGRAM_LIST

  order by 2;

You may want to prefix your view in the external database with "PS_" as a quick visual reminder that it is intended to provide data for your PeopleSoft application.

Step 2. Create a PeopleSoft view of the external view.

Assuming you have a database link to your external database called "extlink", here's what the SQL would look like in your PeopleSoft:
 

create or replace view link.ACAD_PROG_LNK

as select LEVEL_2_UNIT_ID,

    LEVEL_2_NAME from link.PS_ACAD_PROG_LNK@extlink;

Step 3. Create a view record in PeopleSoft App Designer.

Once the link view is created successfully in your PeopleSoft database, you can create the view record in App Designer.  First drop fields into your record definition that match the number and datatypes of the fields in the view created in step 2.  The fields should be in the same order in your PeopleSoft view record as they are in the select statement of the view you created in step 2. Here's what that looks like in our example:

Then, on the Record Type tab, click the button labeled "Click to Open SQL Editor" and type in the SQL that selects data from the view that you created in step 2, which will look something like this:

Don't forget to build your view record, and then you're off and running with displaying data from an external database in your PeopleSoft page.