本帖最后由 yingrihong 于 2012-1-19 16:50 编辑
FUNCTION get_email_alias
( agent_name_in IN STG_ED_AGENT.agent_name%TYPE
)
RETURN VARCHAR
IS
v_err_code ERROR_LOG.err_code%TYPE;
v_err_message ERROR_LOG.err_message%TYPE := NULL;
v_err_remark ERROR_LOG.err_remark%TYPE := NULL;
v_name ED_ASSIGN_EMAIL_ALIAS.agent_name%TYPE;
v_lastname VARCHAR2(30);
v_firstname VARCHAR2(30);
v_midname VARCHAR2(30);
v_firstnametrim VARCHAR2(30);
v_midnametrim VARCHAR2(30);
v_len NUMBER;
v_idx NUMBER;
v_idx2 NUMBER;
v_execute_ok BOOLEAN := TRUE;
v_result_name ED_ASSIGN_EMAIL_ALIAS.email_alias%TYPE;
v_return BOOLEAN := TRUE;
e_trim_space_exception EXCEPTION;
v_dup_count NUMBER;
v_alias_out STG_ED_AGENT.EMAIL_ALIAS%TYPE;
v_dmn_firstname STG_ED_AGENT.DMN_FirstName%TYPE;
v_dmn_lastname STG_ED_AGENT.DMN_LastName%TYPE;
v_temp VARCHAR2(60);
BEGIN
v_name := TRIM(agent_name_in); v_len := LENGTH(v_name);
IF v_name LIKE '% %,%' THEN
-- Case have comma and space; Chan Tai Man, Peter -> Peter.Chan
v_idx := INSTR(v_name, ' ', 1, 1);
v_lastname := SUBSTR(v_name, 1, v_idx-1);
v_idx := INSTR(v_name, ',', 1, 1);
v_midname := SUBSTR(v_name, v_idx+1, v_len);
v_midnametrim := REPLACE(v_midname, ' ', '');
v_alias_out := v_midnametrim || '.' || v_lastname ;
v_dmn_firstname := trim(v_midname);
v_dmn_lastname := trim(v_lastname);
--add by kobe wang 2012-01-09 start
IF if_in_gmail_agent_list(v_alias_out) = 1 THEN
v_alias_out := v_midnametrim || '.tm.' || v_lastname;
v_temp := v_alias_out;
--if Peter.tm.Chan exists then Peter.tm.Chan2
--if Peter.tm.Chan2 exists then Peter.tm.Chan3 ...
v_idx := 1;
WHILE if_in_gmail_agent_list(v_alias_out) = 1
LOOP
v_idx := v_idx + 1;
v_alias_out := v_temp || v_idx;
END LOOP;
END IF;
--add by kobe wang 2012-01-09 end
END IF;
RETURN v_alias_out;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_email_alias;