Skip to content

sp73341_external_client_lookup

Overview

Field Value
Schema WCIS_DBA
Type Standalone function
Database Oracle
Status ✅ found — retrieved via Legends CLI
Query executed legends oracle query --name sp73341_external_client_lookup
Calling libraries SAIF.Business.View.Policy
Calling classes clsTeamAssignment

Purpose

sp73341_external_client_lookup is the Oracle replacement for sp_external_client_lookup, returning the external contractor email address for a system user id and last name. Per an in-source comment (valrey, 08/29/2013), the source was changed from net_registrant to system_user_contractor.

Procedures / Methods

sp73341_external_client_lookup

Field Value
Returns VARCHAR2 (function return value)
Called by clsTeamAssignment.GetExternalEmailAddress (clsTeamAssignment.vb:412-440, SQL at 279)

Description: Sources external client identity from system_user_contractor, replacing the legacy net_registrant source, and returns NULL rather than raising an error on a lookup miss (see rules PR-BVP-015, PR-BVP-016 in Business Rules below).

Parameters

Parameter Direction Type Default Description
in_mailid IN VARCHAR2(30) — System user id
in_last_name IN VARCHAR2(65) — Last name
(return) RETURN VARCHAR2(100) — Email address or NULL

Business Rules

Rules extracted from this function's logic:

Rule ID Rule Condition Action
PR-BVP-015 External client identity is sourced from system_user_contractor, replacing the legacy net_registrant source always Cursor matches on UPPER(TRIM(last_name)) and UPPER(TRIM(system_user_id))
PR-BVP-016 A lookup miss yields NULL rather than an error cursor %notfound Return NULL

These rules are documented in business-rules.md.

Operations

Operations and processes implemented in this function:

Operation Description Trigger
Look up external client email (Oracle) Returns the external contractor email for a system user id + last name clsTeamAssignment.GetExternalEmailAddress (clsTeamAssignment.vb:412-440, SQL at 279)

These operations are also listed in operations-processes.md with source reference back to this document.

Source Code

PL/SQL function (specification and body) 
-- Oracle replacement for sp_external_client_lookup.
-- Source changed from net_registrant to system_user_contractor.
-- valrey 08/29/2013
FUNCTION sp73341_external_client_lookup (in_mailid    IN VARCHAR2,
                                         in_last_name IN VARCHAR2)
   RETURN VARCHAR2
IS
   CURSOR c_user IS
      SELECT 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 c_user;
   FETCH c_user INTO v_email;
   IF c_user%NOTFOUND THEN
      v_email := NULL;
   END IF;
   CLOSE c_user;
   RETURN v_email;
END;

Sources

  • Oracle DBA_SOURCE: WCIS_DBA.sp73341_external_client_lookup
  • Legends CLI query executed 2026-08-31: legends oracle query --name sp73341_external_client_lookup
  • Calling code: SAIF.Business.View.Policy/clsTeamAssignment.vb:279, 412-440