CREATE OR REPLACE FUNCTION BASS_FNGETAccessList
( var_Id VARCHAR2(15),var_uType CHAR(1))

  RETURN VARCHAR2(1000) 

IS

var_ItemList  VARCHAR2(1000);
var_acsFlag   INT;

BEGIN




IF var_uType = 'R' THEN
 SELECT ACCESSFLAG INTO var_acsFlag   from BASSCOMMON.BASS_SEC_ROLE_GRANT 
where ROLEID = var_Id and ACCESSRIGHTID = 'VESSEL' aND ELEMENTID = 'MY_VESSELS';
ELSIF var_uType = 'U' THEN
 SELECT ACCESSFLAG INTO var_acsFlag  from BASSCOMMON.BASS_SEC_USER_GRANT 
where USERID = var_Id and ACCESSRIGHTID = 'VESSEL' aND ELEMENTID = 'MY_VESSELS';
END IF;


IF var_acsFlag = 2 THEN
 var_ItemList := 'ALL';
 
ELSIF var_uType = 'R' THEN
 
  SELECT RTRIM(XMLAGG(XMLELEMENT(e,ITEM || ',')).EXTRACT('//text()'),',') INTO var_ItemList 
from BASSCOMMON.BASS_SEC_ROLE_GRANT_ITEM 
  WHERE ROLEID = var_Id and REC_DELETED = 0;

ELSIF var_uType = 'U' THEN

SELECT RTRIM(XMLAGG(XMLELEMENT(e,ITEM || ',')).EXTRACT('//text()'),',') INTO var_ItemList
 FROM(
   SELECT ITEM  from BASSCOMMON.BASS_SEC_USER_GRANT_ITEM 
  where USERID = var_Id and REC_DELETED = 0
  UNION
   SELECT ITEM  FROM BASS_SEC_ROLE_GRANT_ITEM 
  where ROLEID = var_Id and REC_DELETED = 0
)tmp;
  
END IF;


   return var_ItemList;

END;

CREATE OR REPLACE FUNCTION BASS_FNGETAccessList
  (var_Id       VARCHAR2,
   var_uType    CHAR)
  RETURN        VARCHAR2
IS
...