locked
please review this SP RRS feed

  • Question

  • 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;

    Sunday, January 15, 2012 11:26 PM

All replies

  • User522486851 posted

    helloy you want oracle expert if you can post this on oracle forum you can get more feedback

    Monday, January 16, 2012 12:17 AM
  • User455156504 posted

    Hi,

    additionally you should give some more information on what your database looks like and what you want to achieve with this SP.

    Monday, January 16, 2012 4:18 AM