CREATE OR REPLACE package APPS.cux_hr_validatelogin_pkg as
/******************************************************************************
   name:             emrsn_hr_ValidateLogin_pkg
   purpose:          ValidateLogin
   revisions:      0
   ver              1
   date              JUL-03-2009
   author         Steven.Cheng
   description

******************************************************************************/
    TYPE cur_p IS REF CURSOR;
  procedure validateLogin(p_user_name IN  varchar2 default null,p_password IN  varchar2 default null,o_validate_result out number) ;
 
  procedure get_user_info(p_user_name IN varchar2 default null,o_result out varchar2);   
 
  procedure get_valid_user(p_user_name IN  varchar2 default null,p_password  IN varchar2 default null,o_result out varchar2) ;
 
end ;
/
CREATE OR REPLACE package body APPS.cux_hr_validatelogin_pkg as
procedure get_valid_user(p_user_name in varchar2 default null,p_password in varchar2 default null,o_result out varchar2)
is
o_validate_result  number;
begin
    ValidateLogin(p_user_name,p_password,o_validate_result);
    if o_validate_result=1 then
        get_user_info(p_user_name,o_result);
    else
        o_result:='';
    end if;
end ;
procedure ValidateLogin(p_user_name in varchar2 default null,p_password varchar2 default null,o_validate_result out number)
is
tf boolean;
begin
    tf:=apps.fnd_user_pkg.ValidateLogin(p_user_name,p_password);
    if (tf=true) then
        o_validate_result:=1;
    else
        o_validate_result:=0;
    end if;
end ;

procedure get_user_info(p_user_name IN varchar2 default null,o_result out varchar2)
is
cursor user_info is
select FU.USER_NAME,
        --FU.DESCRIPTION,
        PPF.EMPLOYEE_NUMBER,
        PPF.EMAIL_ADDRESS,       
        ppf.FIRST_NAME,
        ppf.LAST_NAME,
        PPF.FULL_NAME,
        fu.USER_ID
    FROM apps.FND_USER FU
    ,apps.per_people_f ppf
    where FU.EMPLOYEE_ID=PPF.PERSON_ID(+)
    AND FU.user_name= upper(p_user_name)
    group by FU.USER_NAME,
        --FU.DESCRIPTION,
        PPF.EMPLOYEE_NUMBER,
        PPF.EMAIL_ADDRESS,       
        ppf.FIRST_NAME,
        ppf.LAST_NAME,
        PPF.FULL_NAME,
        fu.USER_ID;
begin
    o_result:='';
    for uinfo in user_info loop
        o_result:=o_result || uinfo.USER_NAME || ';' || uinfo.EMPLOYEE_NUMBER || ';' || uinfo.EMAIL_ADDRESS || ';'|| uinfo.FIRST_NAME || ';'|| uinfo.LAST_NAME || ';'|| uinfo.FULL_NAME || ';'|| uinfo.USER_ID ;
    end loop;
 EXCEPTION
        WHEN OTHERS THEN
             null;
end ;
end ;
/