Table Based Menu :
To create menu based on table you have to create the following table :
CREATE TABLE MENUTREE
(
CODE VARCHAR2(10 BYTE),
PARENT VARCHAR2(10 BYTE),
TYP CHAR(1 BYTE), ----- TOP MENU,PARENT MENU, SUB MENU
NAME VARCHAR2(50 BYTE),
ACTIVE CHAR(1 BYTE),
ADMIN CHAR(1 BYTE),
MENUTYP CHAR(1 BYTE) DEFAULT '1',
PERMISSION NUMBER(1) DEFAULT 1
);
Create an Interactive Grid with Enabled Attribute and enter your menu entry like :
No go to : Shared Component > Navigation Menu >
Now go to :
Apply Changes.
Now run the Application and you will see the menu:
Menu SQL :
select level,
NAME label,
decode(pageID, null, null, 'f?p=&APP_ID.:'||PAGEID||':&APP_SESSION.') as target,
'NO' is_current,
'>>' image
from MENUTREE
where ACTIVE='Y'
start with PARENT is null
connect by prior LTRIM(RTRIM(CODE))=LTRIM(RTRIM(PARENT))
order siblings by CODE;
MENU SQL with Permission :
select level,
NAME label,
decode(pageID, null, null, 'f?p=&APP_ID.:'||PAGEID||':&APP_SESSION.') as target,
'NO' is_current,
'>>' image
from MENUTREE
where ACTIVE='Y'
AND to_number(PERMISSION)>=
(SELECT to_number(USERTYP) FROM APPSUSERS WHERE
UPPER(LTRIM(RTRIM(USERNAME)))=UPPER(LTRIM(RTRIM(:APP_USER))))
start with PARENT is null
connect by prior LTRIM(RTRIM(CODE))=LTRIM(RTRIM(PARENT))
order siblings by CODE;
CREATE TABLE USERTYP
(
ID VARCHAR2(1),
NAME VARCHAR2(50)
);
INSERT INTO USERTYP (ID,NAME) VALUES ('1','ADMINISTRATOR');
INSERT INTO USERTYP (ID,NAME) VALUES ('2','CONTRIBUTOR');
INSERT INTO USERTYP (ID,NAME) VALUES ('3','OPERATOR');
INSERT INTO USERTYP (ID,NAME) VALUES ('4','CUSTOMER');
COMMIT;