---
title: "sp73341_external_client_lookup stored procedure documentation"
summary: "Analysis of the Oracle function called by SAIF.Business.View.Policy, including business rules and operations."
status: draft
owner: "SAIF Doc Gen"
last_reviewed: "2026-07-31"
source_schema: "WCIS_DBA"
source_name: "sp73341_external_client_lookup"
source_type: "FUNCTION"
oracle_status: "found"
library: SAIF.Business.View.Policy
reference_type: package
---

# sp73341_external_client_lookup

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Function (standalone) |
| **Database** | Oracle (TNS alias not declared in this library's source)** |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsTeamAssignment` |

## Purpose

Resolves a system user's e-mail address from the contractor directory by mail identifier and last name.

## Procedures / Methods

### sp73341_external_client_lookup

| Field | Value |
| --- | --- |
| **Returns** | VARCHAR2 — e-mail address or null |
| **Called by** | `clsTeamAssignment.InitializeEmailAddressUsingSystemUser` |
| **SQL document** | [SQL-025](../sql/SQL-025-system-user-email-lookup.md) |

**Description:** Returns a system user's e-mail address from the contractor directory, matched case-insensitively on the trimmed mail identifier and last name.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_mailid` | IN | `varchar2` | — | System-user mail identifier to match. |
| `in_last_name` | IN | `varchar2` | — | Last name to match alongside the mail identifier. |

## Business Rules

Rules extracted from this function's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [PROC-RULE-021](../business-rules.md#stored-procedure-business-rules) | External client e-mail lookup is a case-insensitive, trimmed exact match on both mail identifier and last name | `UPPER(TRIM(system_user_id)) = UPPER(TRIM(in_mailid))` and `UPPER(TRIM(last_name)) = UPPER(TRIM(in_last_name))` | Returns the matched `system_user_contractor.email_address`, or null when no row matches |

> These rules are also listed under `## Stored procedure business rules` in [business-rules.md](../business-rules.md).

## Operations

Operations implemented in this function:

| Operation | Description | Trigger |
| --- | --- | --- |
| Look up external client e-mail | Resolves a system user's e-mail address by mail identifier and last name | `clsTeamAssignment.InitializeEmailAddressUsingSystemUser` |

> These operations are also listed under `## Stored procedure operations` in [operations-processes.md](../operations-processes.md).

## Source Code

<details>
<summary>PL/SQL source</summary>

```sql
-- Retrieved from the Oracle data dictionary for WCIS_DBA.sp73341_external_client_lookup
FUNCTION sp73341_external_client_lookup
/********************************************************
* Procedure Title      : sp73341_external_client_lookup
* Name                  : sp73341_external_client_lookup
* Prepared By        : valrey
* Date Created      : 08/29/2013
*       This is an oracle replacement of sp_external_client_lookup.  The source is now going to be
*       system_user_contractor vs. net_registrant.
*/
(
   in_mailid in varchar2,
   in_last_name in varchar2)

   return varchar2
  is

    v_email_address     varchar2(60);

    cursor su_contractor_cur is
        select trim(email_address)
          from system_user_contractor
        where upper(trim(last_name)) = upper(trim(in_last_name))
            and upper(trim(system_user_id)) = upper(trim(in_mailid));


begin

    open su_contractor_cur;
    fetch su_contractor_cur into v_email_address;
    if su_contractor_cur%notfound then
        v_email_address := null;
    end if;
    close su_contractor_cur;

   return  v_email_address;


end sp73341_external_client_lookup;
```

</details>

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts. Statements marked with `**` could not be confirmed; treat
> as unknown until verified.

## Related documentation

- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

## Sources

- `Business/SAIF.Business.View.Policy/clsTeamAssignment.vb`
- Oracle data dictionary source for `WCIS_DBA.sp73341_external_client_lookup`