# Creating Views and Adding New People Code in Component Interface

This section describes the procedure for creating views and adding new people code in component interface on a managed system.

The Roles attribute for the PeopleSoft source aggregates the Roles and Roles along with Route Controls assigned to a user. To improve the performance of PeopleSoft, creating or modifying a view can be used to reduce the population of accounts returned by the PeopleSoft system.

> [!NOTE]
>
> The following script is for PeopleSoft with Oracle as the backend server. For database other than Oracle, the script must be modified accordingly.
>
> (Database tables might be created with schema prefix (for example, sysadm))

## Creating Views

Login to a database with admin permissions and execute the following commands:

1. This script is for the creation of View for getting User IDs:

   ```sql
   CREATE VIEW SP_PS_USERID_VIEW AS (SELECT OPRID FROM PSOPRDEFN);
   GRANT SELECT ON SP_PS_USERID_VIEW TO people;
   commit;
   ```
2. This script is for the creation of View for getting Role Names:

   ```sql
   CREATE VIEW SP_PS_ROLE_VIEW AS (SELECT ROLENAME FROM PSROLEDEFN);
   GRANT SELECT ON SP_PS_ROLE_VIEW TO people;
   commit;
   ```
3. This script is for the creation of View for getting Route controls and its description:

   ```sql
   CREATE VIEW SP_PS_RTE_CNTL_PROFILE_VIEW AS (select RTE_CNTL_PROFILE,NVL(DESCRLONG,'NA') as DESCRLONG FROM PS_RTE_CNTL_PROF);
   GRANT SELECT ON SP_PS_RTE_CNTL_PROFILE_VIEW TO people;
   commit;
   ```

## Adding New People Code

To add new people code, complete the following:

1. Add new function `getIds` in the user component interface:

   > **Note**
   >
   > The SAILPOINT\_USERS component interface must be available. To create SAILPOINT\_USERS or any other user, see [PeopleSoft Component Interface Utility](<https://community.sailpoint.com/t5/Common-Connectors/PeopleSoft-Component-Interface-Utility/ta-p/147442>).

   1. Open the **SAILPOINT\_USERS** component interface.
   2. Right-click on the **methods** section and then select **View Peoplecode**.
   3. Copy and paste the following script in the blank space.

      ```text
      REM This is an example of commenting PeopleCode;
      /* ----- Logic for getting userIds from the view SP_PS_USERID_VIEW ----- */

      Function getIds(&delimiter As string) Returns string;
         &finalString = "";
         &sql_text = "SELECT OPRID FROM SP_PS_USERID_VIEW ORDER BY OPRID";
         &userSql = CreateSQL(&sql_text);
         While &userSql.Fetch(&userId);
            &finalString = &finalString | &userId | &delimiter;
         End-While;
         &userSql.Close();
         Return &finalString;
      End-Function;
      ```
   4. Save the script and component interface.
   5. Close the component interface and reopen to verify that a new method is available with name `getIds`.
2. Add new function `getIds` in the role component interface:

   > **Note**
   >
   > The SAILPOINT\_ROLES component interface must be available.

   1. Open the **SAILPOINT\_Roles** component interface.
   2. Right-click on the **methods** section and then select **View Peoplecode**.
   3. Copy and paste the following script in the blank space.

      ```text
      REM This is an example of commenting PeopleCode;
      /* ----- Logic for getting roleIds from the view SP_PS_ROLE_VIEW ----- */

      Function getIds(&delimiter As string) Returns string;
         &finalString = "";
         &sql_text = "SELECT ROLENAME FROM SP_PS_ROLE_VIEW ORDER BY ROLENAME";
         &userSql = CreateSQL(&sql_text);
         While &userSql.Fetch(&roleId);
            &finalString = &finalString | &roleId | &delimiter;
         End-While;
         &userSql.Close();
         Return &finalString;
      End-Function;
      ```
   4. Save the script and component interface.
   5. Close the component interface and reopen to verify that a new method is available with new name `getIds`.
3. (*Only applicable if the Route Control Component Interface is configured*) Add new function `getIds` in the route control component interface:

   > **Note**
   >
   > The SAILPOINT\_ROUTECONTROL component interface must be available.

   1. Open the **SAILPOINT\_ROUTECONTROL** component interface.
   2. Right-click on the **methods** section and then select **View Peoplecode**.
   3. Copy and paste the following script in the blank space.

      ```text
      REM This is an example of commenting PeopleCode;
      /* ----- Logic for getting route control name and description from the view SP_PS_RTE_CNTL_PROFILE_VIEW ----- */

      Function getIds(&delimiter As string) Returns string;
         Local Record &rtCntlId;
         &finalString = "";
         &rtCntlId = CreateRecord(Record.RTE_CNTL_PROF);
         &sql_text = "SELECT RTE_CNTL_PROFILE,DESCRLONG FROM SP_PS_RTE_CNTL_PROFILE_VIEW ORDER BY RTE_CNTL_PROFILE";
         &rtCntrlSql = CreateSQL(&sql_text, &rtCntlId);
         While &rtCntrlSql.Fetch(&rtCntlId);
            &finalString = &finalString | &rtCntlId.RTE_CNTL_PROFILE.Value | &delimiter | &rtCntlId.DESCRLONG.Value | &delimiter | &delimiter;
         End-While;
         &rtCntrlSql.Close();
         Return &finalString;
      End-Function;
      ```
   4. Save the script and component interface.
   5. Close the component interface and reopen to verify that a new method is available with new name `getIds`.