3 Replies Latest reply: Apr 1, 2014 4:51 AM by BluShadow RSS

    how to avoid null values for diaplay order in procedure ?

    913349

      Hi All ,

      i am using oracle 11g.

      see below procedure is working for a single user means when i am giving user_id  suppose 112 .  infocenter order is coming properly but  after that when i  am login by other user  in display order column

      it is showing null values for all other users.  so how to handle for othher users it should not come null it will show what ever orders for them.

      CREATE OR REPLACE

      PROCEDURE MS_SRA_UPDATE_USER_INFO_ORDER(p_user_id           NUMBER,

                                              o_error_code    OUT NUMBER,

                                              o_error_message OUT VARCHAR2)

       

      AS

      PRAGMA AUTONOMOUS_TRANSACTION;

      BEGIN

      UPDATE SI_USER_INFOCENTER_T outer_qry

      SET (default_infocenter_flag,display_order) =

      (SELECT CASE WHEN myrn=1 THEN 'Y'

                   ELSE 'N'

              END flag,

              myrn FROM(SELECT user_id,

                                 infocenter_name,

                               --default_infocenter_flag,

                               ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY

                                                                      CASE INFOCENTER_NAME

                                                                      WHEN 'A' THEN 1

                                                                      WHEN 'B' THEN 2

                                                                      WHEN 'C' THEN 3

                                                                      WHEN 'D' THEN 4

                                                                      WHEN 'E' THEN 5

                                                                      WHEN 'F' THEN 6

                                                                      WHEN 'G' THEN 7

                                                                      ELSE display_order

                                                                      END) myrn

                      FROM SI_USER_INFOCENTER_T

                      WHERE USER_ID = p_user_id) inner_qry

      WHERE inner_qry.USER_ID=outer_qry.USER_ID

      AND inner_qry.INFOCENTER_NAME=outer_qry.INFOCENTER_NAME

      )

      WHERE USER_ID = p_user_id

      AND NOT EXISTS (SELECT 1

                      FROM MS_SRA_USER_ORG_ROLE_ACT_V msu

                      WHERE ORG_ENTITY_ID = (SELECT ORG_ENTITY_ID FROM SI_ORG_ENTITIES_T WHERE ORG_ENTITY_NAME='F')

                      AND msu.user_id = outer_qry.USER_ID

                      AND msu.user_id = p_user_id);

       

        O_ERROR_CODE   := 0;

        O_ERROR_MESSAGE:=NULL;

        COMMIT;

      EXCEPTION

      WHEN OTHERS THEN

        O_ERROR_CODE   := SQLCODE;

        O_Error_Message:= SQLERRM;

      END MS_SRA_UPDATE_USER_INFO_ORDER;

       

      Regards

      Damby