Skip to content

sp73341_external_client_lookup

Overview

Field Value
Schema WCIS_DBA
Type Standalone function
Database Oracle
Calling libraries SAIF.Business.View.Policy
Calling classes clsTeamAssignment

Purpose

sp73341_external_client_lookup is the Oracle replacement for the older SQL Server external-client lookup. It returns the matched contractors email address from SYSTEM_USER_CONTRACTOR when both mail ID and last name match.

Procedures / Methods

sp73341_external_client_lookup

Field Value
Returns VARCHAR2
Called by clsTeamAssignment.InitializeEmailAddressUsingSystemUser
Description Returns the matched contractor email address or null when no contractor row qualifies.

Parameters

Parameter Direction Type Default Description
in_mailid IN VARCHAR2 — System user ID or mail ID to match.
in_last_name IN VARCHAR2 — Last name to match.

Business Rules

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

Rule ID Summary
BR-VP-035 The function matches SYSTEM_USER_CONTRACTOR rows only when both system_user_id and last_name match the trimmed uppercase inputs.
BR-VP-036 If the cursor fetch finds no row, the function explicitly returns null.

BR-VP-035 External-client email lookup requires trimmed case-insensitive matches on mail ID and last name

Field Value
Statement The function selects an email address only from SYSTEM_USER_CONTRACTOR rows whose trimmed LAST_NAME and SYSTEM_USER_ID both match the trimmed uppercase input values.
Trigger sp73341_external_client_lookup executes.
Enforcement The cursor query filters with upper(trim(last_name)) = upper(trim(in_last_name)) and upper(trim(system_user_id)) = upper(trim(in_mailid)).
Outcome Only contractor rows matching both supplied identity values qualify.
Source Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json

BR-VP-036 External-client lookup returns null when no contractor row is found

Field Value
Statement When the contractor cursor fetch does not find a row, the function assigns v_email_address := null before returning.
Trigger sp73341_external_client_lookup executes and the cursor fetch is not found.
Enforcement The body checks if su_contractor_cur%notfound then v_email_address := null; end if;.
Outcome Callers receive null instead of an exception or stale email value when no contractor row matches.
Source Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json

Operations

The retrieved database source implements the following internal operations.

Operation Description Trigger
Resolve contractor email address Look up and return the trimmed contractor email address that matches the supplied mail ID and last name. Caller invokes sp73341_external_client_lookup.

Resolve contractor email address

Field Value
Description Look up and return the trimmed contractor email address that matches the supplied mail ID and last name.
Trigger Caller invokes sp73341_external_client_lookup.
Source Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\sp73341_external_client_lookup.json

Calling code references

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

Source Code

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

Sources

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