Skip to content

cp_external_client_lookup

Overview

Field Value
Schema dbo
Type Standalone SQL Server stored procedure
Database SQL Server — BDS database eisD0P11, reading the EIS_PROD.dbo.Net_Registrant table via a three-part name
Status ✅ Found in SQL Server (not found in Oracle — confirmed not an Oracle object)
Calling libraries SAIF.Business.View.Policy
Calling classes clsTeamAssignment

This procedure was previously undocumented because Oracle was the only stored-procedure source retrievable during the original analysis. The @-prefixed parameter syntax and the separate data-portal instance (inEmailAddressDPI) correctly signalled a SQL Server target*; source retrieval via legends sqlserver query --name cp_external_client_lookup --database Unknown confirms the procedure and resolves the database as eisD0P11.

Procedures / Methods

cp_external_client_lookup

Field Value
Returns Result set (top 1 row) from Net_Registrant
Called by clsTeamAssignment.InitializeEmailAddress
SQL document SQL-024

Description: Looks up an external client's e-mail (mail ID) based on the client's user number and last name.

Parameters

Parameter Direction Type Max length Description
@idNum Input char(100) 100 User/mail identifier used to look up an external client's e-mail address.
@clientLastName Input char(255) 255 Last name derived from the assignment's full name, used as a secondary lookup key.

Business Rules

Rule ID Rule Condition Action
PROC-RULE-022 Match is a trimmed exact match on both mail identifier and last name RTRIM(reg.ID_NUM) = RTRIM(@idNum) AND RTRIM(reg.USER_LAST_NAM) = RTRIM(@clientLastName) Include the row as a candidate match
PROC-RULE-023 Only active, non-test registrant rows are eligible reg.ACTIVE_FL = 'Y' AND reg.TEST_USER_FL = 'N' Exclude inactive or test-user rows from the result
PROC-RULE-024 When multiple registrant rows match, the most recently updated row wins ORDER BY reg.UPDATE_DATE DESC with TOP 1 Return only the single most-recently-updated matching row

These rules are also listed under ## Stored procedure business rules in business-rules.md.

Operations

Operation Description Trigger
PROC-OP-01 Query EIS_PROD.dbo.Net_Registrant for a matching, active, non-test registrant by ID number and last name Procedure invoked with @idNum and @clientLastName
PROC-OP-02 Return the most recently updated matching row (or none) to the .NET caller Zero or more candidate rows found

These operations are also listed under ## Stored procedure operations in operations-processes.md.

Source Code

T-SQL stored procedure source 
-- Actual T-SQL source retrieved from SQL Server (database eisD0P11) via Legends CLI
Create Procedure dbo.cp_external_client_lookup
--  LIST PARAMETERS
    @idNum char(100),
    @clientLastName char(255)
AS

/*--------------------------------------------------------------------------------
Procedure Name: cp_external_client_lookup
Date Written:   01/08/2007
Author:     Jerry Todd (JERTOD)
Installation:   SAIF Corporation
Source File:    cp_external_client_lookup.sql
Description:    This procedure looks up an external client's mailid based on the client's
        user number

        PARMS: client ID number and client Last Name
Modifications:
--------------------------------------------------------------------------------*/
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


select top 1 *
from EIS_PROD.dbo.Net_Registrant reg
where
rtrim(reg.ID_NUM)=rtrim(@idNum)
and rtrim(reg.USER_LAST_NAM)=rtrim(@clientLastName)
and reg.ACTIVE_FL='Y'
AND reg.TEST_USER_FL='N'

order by reg.UPDATE_DATE desc


/* END THE PROCEDURE */

Return

Notes

  • The .NET caller reads the EMAIL_ADDR column from the returned row (Reader.FirstOrDefault.GetString("EMAIL_ADDR")); this column is confirmed to exist on Net_Registrant by the retrieved source.
  • This is the legacy lookup path. The current replacement path is InitializeEmailAddressUsingSystemUser, which calls the confirmed Oracle function sp73341_external_client_lookup. Both paths remain live in source; this procedure is not dead code — it is reachable whenever InitializeEmailAddress is invoked directly.

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts.

Sources