Skip to content

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

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 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.

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.

Source Code

PL/SQL source 
-- 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;

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.

Sources

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