1 2 Previous Next 15 Replies Latest reply on Mar 2, 2015 12:07 PM by Vedant

    Branch not working properly

    Vedant

      Hi,

      While login to application control jump based on condition. When i click on login branch should be conditional . If user mapped to one company then control  goes to page no 44.

      if user mapped to multiple companies then control goes to page no 26. I have created two branches for this in login page. one for page no 44

      and one for page 26. it is only going to page 26. My code is

       

      this code is for page no --44

       

      DECLARE

      VCNT NUMBER;

      BEGIN

      BEGIN

      SELECT COUNT(1) INTO VCNT FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

      EXCEPTION WHEN OTHERS THEN

      VCNT :=0;

      RETURN FALSE;

      END;

      IF VCNT =1 THEN

      SELECT COMP_CODE,DIV_CODE INTO :P0_SBU_CODE,:P0_DIVISION_CODE

      FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

      RETURN TRUE;

      END IF;

      EXCEPTION WHEN OTHERS THEN

      RETURN FALSE;

      END;

       

      ------------------------------------------------

      code for page 26-----------

       

       

      DECLARE

      VCNT NUMBER;

      BEGIN

      BEGIN

      SELECT COUNT(1) INTO VCNT FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

      EXCEPTION WHEN OTHERS THEN

      VCNT :=0;

      RETURN FALSE;

      END;

      IF VCNT >1 THEN

      RETURN TRUE;

      END IF;

      EXCEPTION WHEN OTHERS THEN

      RETURN FALSE;

      END;

       

       

      Where am i doing wrong please help.

        • 1. Re: Branch not working properly
          Sunil Bhatia

          What is the home page for your application?

           

          It will be in Shared Components -> User Interface section. So you can create a dummy page say (1) and give link in home page as 1. Then on page 1 you can create page load function to redirect on 2 different pages based on ur PL SQL code.

          • 2. Re: Branch not working properly
            Huzaifa_Apex

            Hi Vedant,

             

            You can directly create a New Process After submit in LOGIN Page(101) which will help you to redirect to specific page based on the user. Something like below. I have consider page 1 as a default HOME page.

             

            DECLARE

            VCNT_44 NUMBER;

            VCNT_26 NUMBER;

            v_start_page NUMBER;

             

            BEGIN

            VCNT_44 := 0;

            VCNT_26 := 0;

             

             

            SELECT COUNT(1) INTO VCNT_44 FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

            SELECT COUNT(1) INTO VCNT_26 FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

             

             

            IF VCNT_44 =1 THEN

            v_start_page := 44;

            wwv_flow_custom_auth_std.login(

            P_UNAME => :p101_USERNAME,

            P_PASSWORD => :p101_PASSWORD ,

            P_SESSION_ID => v('APP_SESSION'),

            P_FLOW_PAGE => :APP_ID||':'||nvl(v_start_page,1)

            );

             

            IF VCNT_26 = 1 THEN

            v_start_page := 44;

            wwv_flow_custom_auth_std.login(

            P_UNAME => :p101_USERNAME,

            P_PASSWORD => :p101_PASSWORD ,

            P_SESSION_ID => v('APP_SESSION'),

            P_FLOW_PAGE => :APP_ID||':'||nvl(v_start_page,1)

            );

             

            END;

             

            Code might have some syntax error.

             

            Br,

            Zaif

            1 person found this helpful
            • 3. Re: Branch not working properly
              Vedant

              Can you help me to change the code accordint to condition?

              • 4. Re: Branch not working properly
                Huzaifa_Apex

                I have used your code only. Please review it .

                 

                Br,

                Zaif

                • 5. Re: Branch not working properly
                  Vedant

                  Hi,

                   

                  My Home url  is -- f?p=&APP_ID.:150:&SESSION.

                   

                  login url is --f?p=&APP_ID.:LOGIN_DESKTOP:&SESSION.  // page NO 111

                   

                  I have applied your code in page 111  same as login page.

                   

                  DECLARE

                  VCNT_44 NUMBER;

                  VCNT_26 NUMBER;

                  v_start_page NUMBER;

                   

                  BEGIN

                  VCNT_44 := 0;

                  VCNT_26 := 0;

                   

                   

                  SELECT COUNT(1) INTO VCNT_44 FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

                  SELECT COUNT(1) INTO VCNT_26 FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

                   

                   

                  IF VCNT_44 =1 THEN

                  v_start_page := 44;

                  wwv_flow_custom_auth_std.login(

                  P_UNAME => :P111_USERNAME,

                  P_PASSWORD => :P111_PASSWORD ,

                  P_SESSION_ID => v('APP_SESSION'),

                  P_FLOW_PAGE => :APP_ID||':'||nvl(v_start_page,150)

                  );

                  END IF;

                   

                  IF VCNT_26 = 1 THEN

                  v_start_page := 44;

                  wwv_flow_custom_auth_std.login(

                  P_UNAME => :P111_USERNAME,

                  P_PASSWORD => :P111_PASSWORD ,

                  P_SESSION_ID => v('APP_SESSION'),

                  P_FLOW_PAGE => :APP_ID||':'||nvl(v_start_page,150)

                  );

                  END IF;

                   

                  END;

                   

                  still it is not working as i want. Control directly going to page 150. when i click on login button.

                  • 6. Re: Branch not working properly
                    Huzaifa_Apex

                    Are you sure your condition is proper with the user you are using ? I guess that condtion is having some problem

                     

                    Ok, May be its issue of case

                     

                    try

                    v_user := :APP_USER

                     

                    SELECT COUNT(1) INTO VCNT_44 FROM AC_COMPANY_ACCESS_DTL WHERE upper(USER_ID) = upper(v_user) AND NVL(ACTIVE_FLG,'N')='Y';

                    SELECT COUNT(1) INTO VCNT_26 FROM AC_COMPANY_ACCESS_DTL WHERE upper(USER_ID) = upper(:APP_USER) AND NVL(ACTIVE_FLG,'N')='Y';

                    Br,

                    Zaif

                    • 7. Re: Branch not working properly
                      Kiran Pawar

                      Hi Vedant,

                       

                      Vedant wrote:

                       

                      Hi,

                       

                      My Home url  is -- f?p=&APP_ID.:150:&SESSION.

                       

                      login url is --f?p=&APP_ID.:LOGIN_DESKTOP:&SESSION.  // page NO 111

                       

                      I have applied your code in page 111  same as login page.

                       

                      DECLARE

                      VCNT_44 NUMBER;

                      VCNT_26 NUMBER;

                      v_start_page NUMBER;

                       

                      BEGIN

                      VCNT_44 := 0;

                      VCNT_26 := 0;

                       

                       

                      SELECT COUNT(1) INTO VCNT_44 FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

                      SELECT COUNT(1) INTO VCNT_26 FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

                       

                       

                      IF VCNT_44 =1 THEN

                      v_start_page := 44;

                      wwv_flow_custom_auth_std.login(

                      P_UNAME => :P111_USERNAME,

                      P_PASSWORD => :P111_PASSWORD ,

                      P_SESSION_ID => v('APP_SESSION'),

                      P_FLOW_PAGE => :APP_ID||':'||nvl(v_start_page,150)

                      );

                      END IF;

                       

                      IF VCNT_26 = 1 THEN

                      v_start_page := 44;

                      wwv_flow_custom_auth_std.login(

                      P_UNAME => :P111_USERNAME,

                      P_PASSWORD => :P111_PASSWORD ,

                      P_SESSION_ID => v('APP_SESSION'),

                      P_FLOW_PAGE => :APP_ID||':'||nvl(v_start_page,150)

                      );

                      END IF;

                       

                      END;

                       

                      still it is not working as i want. Control directly going to page 150. when i click on login button.

                           It is not wise to play with the authentication procedure, it will give you weird results. An elegant way of doing this will be to let user authenticate and login into the application home page and write a before header PLSQL process on application home page that redirects the appropriate user to his/her landing page using APEX_UTIL.REDIRECT_URL.

                           Refer : Change default (first) page in Apex

                       

                           Hope this helps!

                       

                      Regards,

                      Kiran

                      • 8. Re: Branch not working properly
                        Vedant

                        Hi,

                         

                        I have check the code above it is ok.

                        i have change into the code even

                         

                        DECLARE

                        VCNT_44 NUMBER;

                        VCNT_26 NUMBER;

                        v_start_page NUMBER;

                         

                        BEGIN

                        VCNT_44 := 0;

                        VCNT_26 := 0;

                         

                         

                        SELECT COUNT(1) INTO VCNT_44 FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

                         

                         

                         

                         

                        IF VCNT_44 =1 THEN

                        v_start_page := 44;

                        wwv_flow_custom_auth_std.login(

                        P_UNAME => :P111_USERNAME,

                        P_PASSWORD => :P111_PASSWORD ,

                        P_SESSION_ID => v('APP_SESSION'),

                        P_FLOW_PAGE => :APP_ID||':'||nvl(v_start_page,150)

                        );

                        END IF;

                         

                        IF VCNT_44 > 1 THEN

                        v_start_page := 26;

                        wwv_flow_custom_auth_std.login(

                        P_UNAME => :P111_USERNAME,

                        P_PASSWORD => :P111_PASSWORD ,

                        P_SESSION_ID => v('APP_SESSION'),

                        P_FLOW_PAGE => :APP_ID||':'||nvl(v_start_page,150)

                        );

                        END IF;

                         

                        END;

                         

                        still it is going to page 150.

                        • 9. Re: Branch not working properly
                          Sunil Bhatia

                          Exactly true Kiran. I agree with it too.

                          • 10. Re: Re: Branch not working properly
                            Kiran Pawar

                            Hi Vedant,

                             

                                So, according to my suggestion above, the before header PL/SQL process on home page would be like:

                            DECLARE
                            
                              VCNT_44 NUMBER := 0;
                              VCNT_26 NUMBER := 0;
                              V_START_PAGE NUMBER;
                            
                            BEGIN
                            
                              SELECT COUNT(*)
                                INTO VCNT_44
                                FROM AC_COMPANY_ACCESS_DTL
                              WHERE UPPER(USER_ID) = UPPER(:APP_USER)
                                AND NVL(ACTIVE_FLG,'N') = 'Y';
                            
                              -- this is redundant as you are selecting from same table and same where clause
                              SELECT COUNT(*)
                                INTO VCNT_26
                                FROM AC_COMPANY_ACCESS_DTL
                              WHERE UPPER(USER_ID) = UPPER(:APP_USER)
                                AND NVL(ACTIVE_FLG,'N') = 'Y';
                            
                              IF VCNT_44 > 0 THEN
                              V_START_PAGE := 44;
                              ELSIF VCNT_26 > 0 THEN
                              V_START_PAGE := 26;
                              ELSE
                              V_START_PAGE := 150;
                              END IF;
                            
                              -- redirect the user to appropriate landing page
                              APEX_UTIL.REDIRECT_URL (
                                  P_URL => 'f?p=' || :APP_ID || ':' || V_START_PAGE || ':'|| :APP_SESSION ||'::NO:::' );
                            
                            END;
                            
                            

                             

                                 Moreover, the problem regarding branch not working properly is due to your query:

                            SELECT COUNT(*)
                              FROM AC_COMPANY_ACCESS_DTL
                            WHERE UPPER(USER_ID) = UPPER(:APP_USER)
                               AND NVL(ACTIVE_FLG,'N') = 'Y';
                            
                            

                                 Check this query for:

                            • Run this query in SQL Commands in oracle apex providing appropriate app_user and check whether it is returning proper results.
                            • Check whether the column USER_ID and :APP_USER hold same value. If not change the query accordingly for example:
                            SELECT COUNT(*)
                              FROM AC_COMPANY_ACCESS_DTL
                            WHERE USER_ID = ( SELECT USER_ID
                                                 FROM AC_COMPANY_ACCESS_DTL
                                                WHERE UPPER(TRIM(USERNAME)) = UPPER(TRIM(:APP_USER)) )
                               AND NVL(ACTIVE_FLG,'N') = 'Y';
                            
                            
                            • For page 26 you are not doing a different check, it is same as for page 44, hence redundant.

                             

                                 Hope this helps!

                             

                            Regards,

                            Kiran

                            • 11. Re: Branch not working properly
                              Vedant

                              Hi,

                              Now control is moving according to my requirement.

                               

                              As in starting of this forum. In case of If count is 1 then the  value directly initialize into

                               

                              SELECT COMP_CODE,DIV_CODE INTO :P0_SBU_CODE,:P0_DIVISION_CODE

                              FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

                              This step is missing . I try to put in case of 1. :P0_SBU_CODE,:P0_DIVISION_CODE items are null.

                              is i am doing wrong here ?

                               

                               

                              DECLARE 

                               

                                VCNT_44 NUMBER := 0; 

                                VCNT_26 NUMBER := 0; 

                                V_START_PAGE NUMBER; 

                               

                              BEGIN 

                               

                                SELECT COUNT(*) 

                                  INTO VCNT_44 

                                  FROM AC_COMPANY_ACCESS_DTL 

                                WHERE UPPER(USER_ID) = UPPER(:APP_USER) 

                                  AND NVL(ACTIVE_FLG,'N') = 'Y'; 

                               

                                -- this is redundant as you are selecting from same table and same where clause 

                                SELECT COUNT(*) 

                                  INTO VCNT_26 

                                  FROM AC_COMPANY_ACCESS_DTL 

                                WHERE UPPER(USER_ID) = UPPER(:APP_USER) 

                                  AND NVL(ACTIVE_FLG,'N') = 'Y'; 

                               

                                IF VCNT_44=1 THEN 

                              SELECT COMP_CODE,DIV_CODE INTO :P0_SBU_CODE,:P0_DIVISION_CODE

                              FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

                                V_START_PAGE := 44; 

                                ELSIF VCNT_26 > 1 THEN 

                                V_START_PAGE := 26; 

                                ELSE 

                                V_START_PAGE := 150; 

                                END IF; 

                               

                                -- redirect the user to appropriate landing page 

                                APEX_UTIL.REDIRECT_URL ( 

                                    P_URL => 'f?p=' || :APP_ID || ':' || V_START_PAGE || ':'|| :APP_SESSION ||'::NO:::' ); 

                               

                              END;

                              • 12. Re: Branch not working properly
                                Huzaifa_Apex

                                Thanks kiran for a detailed analysis.

                                I will also follow the process mentioned by you

                                 

                                Br,

                                Zaif

                                • 13. Re: Branch not working properly
                                  Vedant

                                  Hi,

                                  If count is 1 then  below statement should fetch value into :P0_SBU_CODE,:P0_DIVISION_CODE items . I try it . it  is not working.

                                  SELECT COMP_CODE,DIV_CODE INTO :P0_SBU_CODE,:P0_DIVISION_CODE

                                  FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

                                  • 14. Re: Re: Branch not working properly
                                    Kiran Pawar

                                    Hi Vedant,

                                     

                                    Vedant wrote:

                                     

                                    Hi,

                                    If count is 1 then  below statement should fetch value into :P0_SBU_CODE,:P0_DIVISION_CODE items . I try it . it  is not working.

                                    SELECT COMP_CODE,DIV_CODE INTO :P0_SBU_CODE,:P0_DIVISION_CODE

                                    FROM AC_COMPANY_ACCESS_DTL WHERE USER_ID=:APP_USER AND NVL(ACTIVE_FLG,'N')='Y';

                                        Is branching to page 44 working when count is 1?

                                        Try to avoid SELECT ... INTO as it tend to raise NO_DATA_FOUND. Use explicit cursor/cursor for loop. In case of count, it is okay to use SELECT ... INTO as count returns 0 when no data matching the where clause is found.

                                        Try the following code:

                                    DECLARE
                                    
                                      VCNT_44 NUMBER := 0;
                                      VCNT_26 NUMBER := 0;
                                      V_START_PAGE NUMBER;
                                    
                                    BEGIN
                                    
                                      SELECT COUNT(*)
                                        INTO VCNT_44
                                        FROM AC_COMPANY_ACCESS_DTL
                                      WHERE UPPER(USER_ID) = UPPER(:APP_USER)
                                        AND NVL(ACTIVE_FLG,'N') = 'Y';
                                    
                                      -- this is redundant as you are selecting from same table and same where clause
                                      SELECT COUNT(*)
                                        INTO VCNT_26
                                        FROM AC_COMPANY_ACCESS_DTL
                                      WHERE UPPER(USER_ID) = UPPER(:APP_USER)
                                        AND NVL(ACTIVE_FLG,'N') = 'Y';
                                    
                                      IF VCNT_44 = 1 THEN
                                        FOR REC IN ( SELECT COMP_CODE
                                                          , DIV_CODE
                                                      FROM AC_COMPANY_ACCESS_DTL
                                                      WHERE UPPER(USER_ID) = UPPER(:APP_USER)
                                                        AND NVL(ACTIVE_FLG,'N')='Y' )
                                        LOOP
                                          APEX_UTIL.SET_SESSION_STATE('P0_SBU_CODE',REC.COMP_CODE);
                                          APEX_UTIL.SET_SESSION_STATE('P0_DIVISION_CODE',REC.DIV_CODE);
                                        END LOOP;
                                        V_START_PAGE := 44;
                                      ELSIF VCNT_26 > 1 THEN
                                        V_START_PAGE := 26;
                                      ELSE
                                        V_START_PAGE := 150;
                                      END IF;
                                    
                                      -- redirect the user to appropriate landing page
                                      APEX_UTIL.REDIRECT_URL (
                                          P_URL => 'f?p=' || :APP_ID || ':' || V_START_PAGE || ':'|| :APP_SESSION ||'::NO:::' );
                                    
                                    END;
                                    
                                    
                                    

                                     

                                         Try to instrument your PL/SQL code, starting with APEX_DEBUG.MESSAGE. Log the intermediate results into debug, so that it's easier to find out what is going wrong behind the scenes. Moreover, try to using Debugging Mode and viewing the Session State.

                                     

                                         Hope this helps!

                                     

                                    Regards,

                                    Kiran

                                    1 2 Previous Next