User-1925567390 posted
CREATE OR REPLACE
PROCEDURE TRB_PS_GET_USER
--************************************************************************************************************************************
--** <name>TRB_PS_GET_USER</name>
--** <description>G2R7 RFC 2011.117719 This SP is used to get the user details
--** </description>
--** <actor>Anurag Rawat</actor>
--** <modification_date> 14/01/12 </modification_date>
--************************************************************************************************************************************
(
IN_USER_ID TRB_UTILISATEUR.UTL_ID%type,
IN_LAST_NAME TRB_UTILISATEUR.LASTNAME%type,
IN_FIRST_NAME TRB_UTILISATEUR.FIRSTNAME%type,
OUT_COUNT_USER OUT number,
OUT_USER OUT SYS_REFCURSOR
)
IS
BEGIN
if IN_LAST_NAME is not null and IN_FIRST_NAME is not null then
select UTL_ID
INTO OUT_COUNT_USER, COUNT(UTL_ID)
from TRB_UTILISATEUR
where TRB_UTILISATEUR.LASTNAME = IN_LAST_NAME
and TRB_UTILISATEUR.FIRSTNAME = IN_FIRST_NAME group by UTL_ID;
else
OUT_COUNT_USER := 0;
END IF;
if IN_USER_ID is not null OR OUT_COUNT_USER = 1 then
OPEN OUT_USER FOR SELECT trb_utilisateur.utl_id,
utl_nom,
trb_utilisateur.eds_id,
trb_utilisateur.utl_actif ,
lng_id,
utl_aff_heure,
utl_telephone,
utl_portable,
utl_fax,
utl_mail,
med_id,
utl_horaire,
trb_utilisateur.gea_id,
trb_utilisateur.eds_id_ref,
trb_utilisateur.oce_profile,
fct_libelleANG
FROM trb_utilisateur,
trb_eds,
trb_fonction
WHERE trb_utilisateur.eds_id=trb_eds.eds_id
and UTL_ID =IN_USER_ID
and TRB_UTILISATEUR.FCT_ID =TRB_FONCTION.FCT_ID(+);
else OUT_USER := null;
OUT_COUNT_USER := 0;
END IF;
END TRB_PS_GET_USER;
