Set Dynamic Menu According To User Group. Oracle Apex.

How To Set Dynamic Menu According To User Group. Oracle Apex.

 

We Often Have Different Types Of Users In The Oracle Apex Application. And Not All Users Can See All The Pages And All The Menus, Or Have To Set The Page According To The User. In Today's Video / Post, I Will Try To Show In Detail How To Set The Menu According To The User Group. Hopefully, After Watching The Full Video / Post, There Will Be No Problem In Setting The Menu According To The User Group In Your Application.

If You Have Not Seen How To Use Custom User Authorization Scheme In Oracle Apex Application, Then You Can Watch The Video Tutorial And Post By Clicking On The Link Below.

Requirements To Set The Menu According To The User Group.

You Must Use Custom User Authorization To Set The Menu According To The User Group. Some Users Need To Be Created According To User Group Using Custom User Authorization.

🔗 Custom User Authorization Scheme In Oracle Apex. Part-1 Video
            URL-https://youtu.be/z2uEawNglJU  
🔗 Custom User Authorization Scheme In Oracle Apex. Part-1 Post
            URL-https://cutt.ly/iPHIfxy
 

🔗 Custom User Authorization Scheme In Oracle Apex. Part-2 Video
            URL-https://youtu.be/Y48bJ-Gh0WI
;🔗 Custom User Authorization Scheme In Oracle Apex. Part-2 Post
            URL-https://cutt.ly/ePHG4ID
 

The Whole Process Is Quite Large To Use The Menu According To The User Group. So I Will Try To Explain The Whole Process In A Few Steps.

First Step :-

Steps To How To Set Dynamic Menu According To User Group

1. First Create A Table To Create The Menu, You Can Create Tables And Sequences Using The SQL Code Below. 

CREATE TABLE  "MENUS" 
   (	"OID" NUMBER NOT NULL ENABLE, 
	"PARENT_OID" NUMBER, 
	"MENU_NAME" NVARCHAR2(100), 
	"MENU_LINK" NVARCHAR2(200), 
	"STATUS" NVARCHAR2(1), 
	"DESCRIPTION" NVARCHAR2(200), 
	"SORT_BY" NUMBER, 
	"PID_EMPLOYEE_INSERT_BY" NUMBER, 
	"PID_EMPLOYEE_UPDATE_BY" NUMBER, 
	"UPDATE_DATE" DATE, 
	"INSERT_DATE" DATE DEFAULT sysdate, 
	"ICON_IMG" VARCHAR2(250), 
	"MENU_NAME_BANGLA" VARCHAR2(250), 
	 CONSTRAINT "MENUS_C01" CHECK (parent_oid <> oid) ENABLE, 
	 CONSTRAINT "MENUS_CON_PK" PRIMARY KEY ("OID")
  USING INDEX  ENABLE
   )  ENABLE ROW MOVEMENT
/
ALTER TABLE  "MENUS" ADD CONSTRAINT "MENUS_CON" FOREIGN KEY ("PARENT_OID")
	  REFERENCES  "MENUS" ("OID") ENABLE
/

 CREATE SEQUENCE   "MENUS_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1527 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL
/

2. Once The Table And Sequence Have Been Created, Create A Report And Form Page On The Menu Table. 

       If We Create A Menu Submenu, Then We Must First, Select The Name Of The Parent Menu.
      Parent OID Type - Select List
      SQL Code-

SELECT LPAD (' ', 3 * (LEVEL - 1), '-') || menu_name, oID
    FROM MENUS
       START WITH parent_oid IS NULL
       CONNECT BY PRIOR oid = parent_oid
ORDER SIBLINGS BY menu_name

Menu Link Page Type - Select List
       SQL Code-

 SELECT PAGE_NAME || ' (P-' || PAGE_ID || ')' AS d, PAGE_ID AS r
  FROM apex_application_pages
 WHERE application_id = :APP_ID;

Status Type - Select List
       SQL Code-

SELECT STATUS, OID 
    FROM IS_ACTIVATE

Create A Process For Menu OID
       Name - Get OID
      PL/SQL Code-

SELECT MENUS_SEQ.NEXTVAL 
    INTO :P5_OID
   FROM DUAL;

3. Once The Menu Creation Form Is Completed, Our Will Create Some New Menus.

Our First Step Is Over. Now We Will Do The Second Step.

The 2nd Step :-

1. In The Second Step We Will Create A Dynamic Menu List. 

2. To Create A Dynaki Menu List, We Will Select Lists From The Shared Components Of The Application.
 I Will Click On The Create Button To Create A New Lists

      Name - Dynamic Menu List 

Query Source Type- SQL Query
       Query-

SELECT LEVEL,
           MENU_NAME
                      label,
                  'f?p=' || :APP_ID || ':' || MENU_LINK || ':' || :APP_SESSION
                      target,
                  'NO:'
                      is_current,
                  NVL (icon_img, 'fa-apex-square')
                      image,
                  PARENT_OID
             FROM MENUS
       START WITH PARENT_OID IS NULL
       CONNECT BY PRIOR OID = PARENT_OID
ORDER SIBLINGS BY SORT_BY;

3. Once The Dynamic Menu List Is Created, We Will Click On User Interface Attributes From The Shared Component Again.

I Will Click On Navigation Menu From User Interfaces.

Navigation Menu List- Dynamic Menu List
       Position- Top
      List Template- Top Navigation Menu

4. At The End Of All I Will Click On The Apply Chain Button. 

5. At This Stage We Will See If All The Menus Of The Application Can Be Seen 

Our Second Phase Is Over. Our Main Objective Today Was How To Set The Menu According To The User Role. In The Third Step, We Will See How To Set The Menu According To The User Role.

The 3rd Step:-

1. To Grant Group Wise Page Access / Menu Access, We First Need To Create A Table. 

CREATE TABLE "USER_GROUP_PAGE_ACCESS"
(
    "OID"           NUMBER NOT NULL ENABLE,
    "PID_GROUP"     NUMBER NOT NULL ENABLE,
    "PAGE_ID"       NUMBER,
    "PERMISSION"    NUMBER (1, 0) DEFAULT 1,
    CONSTRAINT "ACL_GROUP_PAGE_ACCESS_PK" PRIMARY KEY ("OID")
        USING INDEX ENABLE,
    CONSTRAINT "ACL_GROUP_PAGE_ACCESS_U01" UNIQUE ("PID_GROUP", "PAGE_ID")
        USING INDEX ENABLE
)
ENABLE ROW MOVEMENT
/

2. After Creating The Table, I Will Create A New Page To Give Group Page Permission.
       Name - Group Page Permission

3. I Will First Create A Region For The Filtered Report I Can See,
       Name - Filter Option

4. I Will Take A Page Item.
       Name - USER_GROUP
      Type- Select List
      SQL Code-

SELECT GROUP_TITLE, OID
    FROM USER_GROUP
ORDER BY 1

5. I Will Create A New Region To View The Group Page Permission Report.
       Name - Group Page Permission
      SQL Code-

SELECT b.OID                                    AS OID,
       a.GROUP_TITLE                            AS GROUP_TITLE,
       (c.MENU_LINK || ' - ' || c.MENU_NAME)    AS page_name,
       c.MENU_LINK,
       c.DESCRIPTION,
       b.PERMISSION                             AS PERMISSION,
       (SELECT g.GROUP_TITLE
          FROM USER_GROUP g
         WHERE g.oid = b.PID_GROUP)             AS PID_GROUP
  FROM USER_GROUP a, USER_GROUP_PAGE_ACCESS b, MENUS c
 WHERE     b.PID_GROUP = a.OID
       AND b.PAGE_ID = c.MENU_LINK(+)
       AND ( :P8_USER_GROUP IS NULL OR b.PID_GROUP = :P8_USER_GROUP)

6. Group Page Permission I Will Create A New Region For Granting Permission.
       Name - Add Panel

7. I Will Create 2 Items In The Add Panel Region.
       Name - Group, Page
      Item Name Group Type-Select List
      SQL Code-

SELECT GROUP_TITLE, OID
    FROM USER_GROUP
ORDER BY 1

Item Name Page Type-Select List
       SQL Code-

SELECT MENU_LINK || ' - ' || MENU_NAME d, OID r
    FROM MENUS
   WHERE status = '1'
ORDER BY MENU_LINK

8. I Will Create A Button
       Name- SAVE_PAGE
      Set Static ID= SAVE_PAGE

9. Create Dynamic Action
       Name - Save Page Access
      Action- Execute Sercer-side Code
      PL/SQL code-

DECLARE
    l_selected   APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
    l_selected := APEX_UTIL.STRING_TO_TABLE ( :P8_PAGE);

    FOR i IN 1 .. l_selected.COUNT
    LOOP
        INSERT INTO USER_GROUP_PAGE_ACCESS (OID,
                                            PID_GROUP,
                                            PAGE_ID,
                                            PERMISSION)
                 VALUES (
                            (SELECT NVL (MAX (oid), 0) + 1
                               FROM USER_GROUP_PAGE_ACCESS),
                            :P8_GROUP,
                            l_selected (i),
                            1);
    END LOOP;
END;

10. If All Our Work So Far Is Correct, I Will Give Access To A Few Pages According To The Group And Check The Group Wise Report.

 

We Gave Group Wise Access, Checked The Report, But Still The Menu Is Not Showing According To User Group, To View Menu According To User Group We Need To Make Some Changes In The SQL Code Of Dynamic Menu List Already.

      Before Changing The SQL Code Of The Dynamic Menu List,

11. I Will Create An Item Called User Group On The Global Page Or 0 Page Of The Application.       Name- P0_USER_GROUP
      Type- Hidden

12. Once An Item Named User Group Has Been Created On The Global Page Or 0 Page Of The Application, We Will Go To The Login Page Of The Application And Store The User Group Data Between Our Previously Taken Global P0_user_group Items In The Login Process Of The Login Page.
       Go To The Login Page Of The Application
      Go To The Login Process PL/SQL Code
      I Will Add The Following SQL Code In The Login Process Pl/ SQL Code.

 SELECT USER_TYPE
  INTO :P0_USER_GROUP
  FROM MY_USERS
 WHERE UPPER (USERNAME) = UPPER ( :P9999_USERNAME);

13. I Will Text The Item For A While To Check If The User Group Is Correctly In The Middle Of The P0_USER_GROUP Item.
       P0_USER_GROUP I Will Hide The Item Again.

14. I Will Change Some PL/SQL Code In The SQL Query Of Dynamic Menu List.
       To Change The SQL Code Of The Dynamic Menu List, We Will Again Go To The Shared Components Option Of The Application And Click On The List Button.
      When The List Is Open, I Will Select The Dynamic Menu List.
      In The SQL Query Of Dynamic Menu List, I Will Use A Condition After The Form.
      Add SQL Query-

WHERE OID IN (SELECT PAGE_ID
                            FROM USER_GROUP_PAGE_ACCESS
                           WHERE PID_GROUP = :P0_USER_GROUP)

15. I Will Save The SQL Query By Clicking On The Apply Chain Button.
       Setting Up Dynamic Menus According To Our User Group Is Almost Finished. Now We Are Not Showing The Menu Correctly According To The User? I'll See. Do You See The Dynamic Menu Showing Correctly According To The Group Logged In To The Application With A Few Users Including Admin User, Normal User?

Displaying Dynamic Menus Correctly According To User Group. If You Have Any Problems Or Questions Regarding This Video Or Post, You Must Comment In The Comment Box. Oracle Apex Related More Videos And Posts So That We Can Bring Among You, For That Everyone's Cooperation Is Desirable. Please Support And Encourage Me By Subscribing To My Youtube Channel.

 

🔗 Demo Application-
            URL- Demo Application
            Username - demo, Pass- demo
 

I hope everyone will like it. Please watch the full video,
Comment on any of your problems, I will try my best to solve the problem, In-Shah Allah. Everyone's cooperation is desirable. Visit my blog site, new technology related videos, you will get different types of tutorials of Oracle Apex, and hopefully, you can use them in your daily work.
Please stay tuned by subscribing to the YouTube channel, and encourages new videos to be uploaded.
=================
Visit my site to get more collaborative posts about Oracle Apex and subscribe to my YouTube channel. Thanks.
Comment on any of your issues, I will try my best to solve the problem, In-Shah Allah. Everyone's cooperation is desirable.
Visit my blog site, new technology-related videos, you will get different types of tutorials of Oracle Apex, and hopefully, you can use them in your daily work.
==============================

🙍🏾‍ Md jABER HOSSEN
📲 Mobile- +8801760688286
📨 Email- jaberit786@gmail.com
🌐 FB- facebook.com/mdjaber.hossen1
Please Subscribe to My Channel

Many thanks for visiting the site.

Then Enjoy.........................