User + Role + Dynamic Menu setup

 

Table
====

1)

CREATE TABLE  "UMS_MENU_MAIN" 

   ( "ID"                          NUMBER , 
"MENU_NAME"           VARCHAR2(100) NOT NULL ENABLE, 
"MENU_TYPE"            VARCHAR2(10), 
"RUN_FILE_NAME"     VARCHAR2(100), 
"MENU_SL"                NUMBER, 
"PARENT_ID"             NUMBER, 
"STATUS"                  VARCHAR2(2) DEFAULT 1, 
"HOME_DIR"              VARCHAR2(200), 
"REMARKS"                VARCHAR2(400), 
"MENU_LEVEL"           NUMBER, 
"PRV_ICON_IMG"       VARCHAR2(50), 
"PARENT"                   NUMBER, 
"CARD_ICON"             VARCHAR2(50), 
"APP_ID"                    NUMBER NOT NULL ENABLE, 
"CREATE_DATE"          DATE
   )

/

--> CREATE SEQUENCE UMS_MENU_MAIN_SEQ

CREATE OR REPLACE EDITIONABLE TRIGGER  "UMS_MENU_MAIN_TRIG" 
before insert
on UMS_MENU_MAIN
for each row
begin
:new.id:=UMS_MENU_MAIN_SEQ.NEXTVAL;
end; 

2)

CREATE TABLE  "UMS_ROLE_MST" 
  ( "ID" NUMBER , 
"NAME" VARCHAR2(50) NOT NULL ENABLE, 
"REMARKS" VARCHAR2(200), 
"STATUS" NUMBER(1,0) DEFAULT 1, 
"CREATE_BY" NUMBER, 
"CREATE_DATE" DATE
   );

---> CREATE SEQUENCE UMS_ROLE_MST_SEQ

CREATE OR REPLACE EDITIONABLE TRIGGER  "ROLE_MST_ID_TRIG" 
before insert
on ums_role_mst
for each row
begin
:new.id:=ums_role_mst_seq.NEXTVAL;
end; 

3)

CREATE TABLE  "UMS_ROLE_DTL" 
 ( "ID" NUMBER DEFAULT  "UMS_ROLE_DTL_SEQ"."NEXTVAL", 
"ROLE_MST_ID" NUMBER NOT NULL ENABLE, 
"MENU_ID" NUMBER NOT NULL ENABLE, 
"STATUS" NUMBER DEFAULT 1, 
"CREATE_DATE" DATE
   )

---> CREATE SEQUENCE UMS_ROLE_DTL_SEQ

CREATE OR REPLACE EDITIONABLE TRIGGER  "UMS_ROLE_DTL_TRIG" 
before insert
on UMS_ROLE_DTL
for each row
begin
:new.id:=UMS_ROLE_DTL_seq.NEXTVAL;
end; 

4)

CREATE TABLE  "UMS_USER_LOGING" 
   ( "ID" NUMBER DEFAULT  "UMS_USER_LOGING_SEQ"."NEXTVAL", 
"LOGIN" VARCHAR2(100) NOT NULL ENABLE, 
"PASSWORD" VARCHAR2(60), 
"NAME" VARCHAR2(50), 
"EMAIL" VARCHAR2(100), 
"ROLE_ID" NUMBER DEFAULT 0 NOT NULL ENABLE, 
"PHONE_NUMBER" VARCHAR2(20), 
"PICTURE" BLOB, 
"EMP_ID" NUMBER, 
"FILENAME" VARCHAR2(400), 
"IMAGE_LAST_UPDATE" TIMESTAMP (6) WITH LOCAL TIME ZONE, 
"CHARSET" VARCHAR2(512), 
"CREATE_BY" NUMBER, 
"CREATE_DATE" DATE, 
"STATUS" NUMBER DEFAULT 0 NOT NULL ENABLE, 
"COM_ID" NUMBER, 
"UPDATE_DATE" DATE DEFAULT sysdate, 
"UPDATE_BY" NUMBER, 
"MIMETYPE" VARCHAR2(255), 
"DESIGNATION" VARCHAR2(50), 
"APP_ID" NUMBER,
"ASSIGN_COMPANY" NUMBER, 
"COMPANY_TYPE" VARCHAR2(25)
);

5)

CREATE TABLE  "SETUP_COMPANY" 

   ( "ID" NUMBER, 

"NAME" VARCHAR2(255), 

"ADDRESS" VARCHAR2(255), 

"COUNTRY" NUMBER, 

        "DIVISIONS" NUMBER, 

"DISTRICTS" NUMBER, 

"UPAZILAS" NUMBER, 

"PHONE" VARCHAR2(25), 

"MOBILE" VARCHAR2(25), 

"EMAIL" VARCHAR2(50), 

"STATUS" NUMBER(1,0) DEFAULT 1, 

"CREATE_BY" NUMBER, 

"CREATE_DATE" DATE DEFAULT SYSDATE, 

"UPDATE_BY" NUMBER, 

"UPDATE_DATE" DATE, 

"LOGO" BLOB, 

"MIMETYPE" VARCHAR2(255), 

"FILENAME" VARCHAR2(400), 

"IMAGE_LAST_UPDATE" TIMESTAMP (4) WITH LOCAL TIME ZONE, 

"IMAGE_CHARACTER_SET" VARCHAR2(100), 

CONSTRAINT "PK_COMPANY_COM_ID" PRIMARY KEY ("ID")

);

==============================================

Form Design
---------------

1. Role Setup

Table Name: UMS_ROLE_MST
Type : Intaractive Grid

2. User Registration From

Table Name: UMS_USER_LOGING

-->Create Report with From

Column Name 

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

Name -- Full Name

Login -- User Name

ROLE_ID -- Role Select --- LOV (select NAME, id from UMS_ROLE_MST)

Default Value set
============

Select your items (flowing Below items) 

P24_STATUS   (must LOV set)
Default --> Type --> Static 
Static Value: 1

P24_CREATE_BY
Default --> Type --> Item
Item --> Application --> :GUSER_ID

Create  Process for "UMS_USER_LOGING" 
=============================

1. Create  Process "PK_SAVE" and Select Button "CREATE"

SELECT NVL(MAX(ID),1000)+1 INTO :P24_ID FROM UMS_USER_LOGING;

:P24_CREATE_DATE:=SYSDATE;

:P24_CREATE_BY:=:GUSER_ID;

apex_application.g_print_success_message:='<span> '||:P24_LOGIN||' '||

                                          ' User Successfully Create !'||' </span>';

2. Create  Process "Update" and Select Button "Save/Apply Update"

:P24_UPDATE_DATE:=SYSDATE;

:P24_UPDATE_BY:=:GUSER_ID;

-------Note :- Another Tables Must default set com_id Item -------------

P50_COM_ID
Default --> Type --> Static 
Static Value: GCOM_ID

2. Menu Setup

Table Name: UMS_MENU_MAIN

PRV_ICON_IMG ---> Menu Icon

APP_ID :

       Default : Type : Static

        Static Value : Your Application_id (138)

P5_RUN_FILE_NAME  --- LOV

SQL Query : 

select PAGE_NAME||' -'||PAGE_ID  d, PAGE_ID r

from APEX_APPLICATION_PAGES

where APPLICATION_ID=138

order by 1

Create Dynamic Action on P5_RUN_FILE_NAME

Set Value :
SQL Statement:

select PAGE_TITLE

from APEX_APPLICATION_PAGES

where PAGE_ID=:P5_RUN_FILE_NAME

and APPLICATION_ID=138

Item to submit

Create LOV on P5_PARENT_ID

select MENU_NAME as dis, id as ret from UMS_MENU_MAIN

where RUN_FILE_NAME is null

order by MENU_NAME

3. Role Wise Menu permission

Table Name : UMS_ROLE_DTL
Type : Interactive Grid 

Create LOV on 
Column Name  : ROLE_MST_ID

select name d, id r from ums_role_mst

where status=1

Create LOV on 
Column Name  : MENU_ID

select menu_name  d ,id  r

from UMS_MENU_MAIN

where status =1

order by menu_name

======================================================================

Identify User & Company
================

Go to Shared Components --> Application Items

      Create :

     GUSER_ID

     GCOM_ID

Application Processes
===============

Go TO Shared Components --> Application Processes--> Create

  1. GET_COM_ID

PL/SQL -->

BEGIN

select com_id into :GCOM_ID from UMS_USER_LOGING

where upper(LOGIN) = upper(:app_user);

END;

 --> Condition Type : User is Authenticated (Not Public) 

2. GET_USER_NAME

PL/SQL -->

BEGIN

select id into :GUSER_ID from UMS_USER_LOGING

where upper(LOGIN) = upper(:app_user);

END;

 --> Condition Type : User is Authenticated (Not Public) 

3. GET_GROLE_ID

PL/SQL -->

BEGIN

select ROLE_id into :GROLE_ID from UMS_USER_LOGING

where upper(LOGIN) = upper(:app_user);

END;

 --> Condition Type : User is Authenticated (Not Public) 

4. GET_GUSER_COM

PL/SQL -->

BEGIN

select ASSIGN_COMPANY into :GUSER_COM from UMS_USER_LOGING

where upper(LOGIN) = upper(:app_user);

END;

 --> Condition Type : User is Authenticated (Not Public) 

5. GET_GUSER_TYPE

PL/SQL -->

BEGIN

select COMPANY_TYPE into :GUSER_TYPE from UMS_USER_LOGING

where upper(LOGIN) = upper(:app_user);

END;

 --> Condition Type : User is Authenticated (Not Public) 

Function :
======

create or replace function validate_user_access_db (p_username in varchar2, p_password in varchar2)

return boolean

as

    v_pw_check varchar2(1);

begin

    select 'x'

    into v_pw_check

    from UMS_USER_LOGING        

   where upper(LOGIN) = upper(p_username)

    and  PASSWORD = p_password

    and status = 1;

    apex_util.set_authentication_result(0);

    return true;

exception when no_data_found then

    apex_util.set_authentication_result(4);

    return false;

end validate_user_access_db;

--> Go to Shared Components --> Authentication Schemes

Click Create  Button --

Name: Application Express Accounts
Authentication Function Name: validate_user_access_db

--> Go to Shared Components --> User Interface Attributes-->Security

Dynamic Menu
==========

--> Go to Shared Components --> Navigation Menu --> create --> Name : Dynamic_menu  --> Type : Dynamic --> SQL Query

select LEVEL "level",

MENU_NAME  "label",

decode(connect_by_isleaf,0, '','f?p=&APP_ID.:'||TO_CHAR(RUN_FILE_NAME)||':&SESSION.::NO:::')  "target" ,

MENU_NAME  "is_current",

decode(nvl(PRV_ICON_IMG,'A'),'A','',PRV_ICON_IMG)  "image"

from UMS_MENU_MAIN

WHERE STATUS = 1

and app_id=138

and ID IN (SELECT menu_id

FROM ums_role_dtl

WHERE UMS_ROLE_DTL.STATUS =1

and role_mst_id IN (SELECT role_id

FROM ums_user_loging

WHERE STATUS = 1

and UPPER (login) = upper(:APP_USER)))

CONNECT BY PRIOR ID = parent_id

START WITH parent_id IS NULL

connect by

prior id = PARENT_ID 

order siblings by MENU_LEVEL

Navigation Menu
===========

Go TO Shared Components --> User Interfaces Attributes

Navigation Menu List --> Dynamic_menu

Company Name Show Navigation :
========================

Go to Shared Components  -- > Application Items --> Create

Name: G_COM_NAME
Session State Protection: Unrestricted

Go to Shared Components  -- > Application Processes --> Create

Name: GET_COM_NAME

PLSQL : 

SELECT NAME 
INTO :G_COM_NAME
FROM SETUP_COMPANY
WHERE ID=:GCOM_ID;

 --> Condition Type: User is Authenticated (Not Public) 

Go to Shared Components  -- > Navigation Bar List --> Navigation Bar--> Crete Entry

List Entry Label: &G_COM_NAME.
Page: 1

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

IR REPORT CONDITION
===============

1.  Shipper Wise IR Report (Log in : Shipper)

  WHERE COM_ID=:GCOM_ID

   AND SETUP_PARTY_COM_ID = :GUSER_COM  

------or

   AND SETUP_PARTY_COM_ID= CASE WHEN :GUSER_TYPE = 'Shipper' THEN :GUSER_COM ELSE nvl(:GUSER_COM,SETUP_PARTY_COM_ID) end

2. All Data Show for Admin And User wise Data Show

WHERE COM_ID=:GCOM_ID

AND CREATE_BY= case when USER_WISE_ROLE ('a',:GUSER_ID) in (1)  then CREATE_BY else to_number(:GUSER_ID) end 

-------------- Function

create or replace function  USER_WISE_ROLE (in_type in varchar2,in_id in number)

return varchar2 is

v_name varchar2(256);

begin 

if in_type  = 'a' then

select ROLE_ID into v_name from UMS_USER_LOGING where ID = in_id ;

else null;

end if ;

return v_name;

exception when others then return null ;

end;