Skip to content

cp_external_client_lookup

Overview

Field Value
Schema dbo
Type Standalone procedure
Database SQL Server (eisD0P11)
Calling libraries SAIF.Business.View.Policy
Calling classes clsTeamAssignment

Purpose

cp_external_client_lookup looks up an external-client registrant row from EIS_PROD.dbo.Net_Registrant. The retrieved body filters on trimmed ID and last-name equality, excludes inactive and test users, and returns the most recently updated match.

Procedures / Methods

cp_external_client_lookup

Field Value
Returns Result set containing the matching Net_Registrant row
Called by clsTeamAssignment.InitializeEmailAddress
Description Returns the latest active non-test registrant row that matches the supplied client ID and last name.

Parameters

Parameter Direction Type Default Description
@idNum IN char(100) — Client or user ID number to match.
@clientLastName IN char(255) — Client last name to match.

Business Rules

The retrieved database source embeds the following server-side rules.

Rule ID Summary
BR-VP-041 The procedure returns TOP 1 from Net_Registrant after trimmed ID and last-name matching, restricted to active non-test rows and ordered by latest update date.

BR-VP-041 External-client lookup returns the latest active non-test registrant that matches the supplied ID and last name

Field Value
Statement The external-client lookup returns only rows whose trimmed ID_NUM and USER_LAST_NAM match the input values, whose ACTIVE_FL = 'Y', whose TEST_USER_FL = 'N', and whose UPDATE_DATE is greatest.
Trigger cp_external_client_lookup executes.
Enforcement The query filters on trimmed ID and last-name equality, active and non-test flags, orders by reg.UPDATE_DATE DESC, and selects TOP 1 *.
Outcome Callers receive the most recently updated active production registrant row for the supplied identity values.
Source SQL Server retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_external_client_lookup.json

Operations

The retrieved database source implements the following internal operations.

Operation Description Trigger
Return the latest matching external-client registrant row Select the most recently updated active non-test Net_Registrant row that matches the supplied ID and last name. Caller invokes cp_external_client_lookup.

Return the latest matching external-client registrant row

Field Value
Description Select the most recently updated active non-test Net_Registrant row that matches the supplied ID and last name.
Trigger Caller invokes cp_external_client_lookup.
Source SQL Server retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_external_client_lookup.json

Calling code references

Class Method Source File
clsTeamAssignment InitializeEmailAddress SAIF.Business.View.Policy\clsTeamAssignment.vb

Source Code

SQL Server procedure source 
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

Sources

  • SQL Server retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_external_client_lookup.json
  • Calling code: SAIF.Business.View.Policy\clsTeamAssignment.vb