CREATE OR REPLACE VIEW USER_MENU_V AS
SELECT usr.USER_ID,
       usr.USER_CODE,
       usr.FIRSTNAME,
       appf.APPFEATURE_ID,
       appf.APPFEATURE_CODE,
       appf.APPFEATUREDESC,
       app.APP_ID,
       appf.APP_CODE,
       app.APPDESC,
       app.VERSION,
       app.VERSIONDATE,
       pag1.PAGEPATH AS APPPAGEPATH,
       pag1.PAGE_CODE AS APPPAGECODE,
       appf.HIERARCHYLEVEL,
       appf.MENUNAME,
       appf.ISMENUITEM,
       appf.ISMENULINK,
       NVL(appf1.APPFEATURE_ID,0) AS PARENTAPPFEATURE_ID,
       appf.PARENTAPPFEATURE_CODE,
       page.PAGEPATH,
       page.PAGE_CODE
FROM SYS_USER usr,
     SYS_APPGROUP apg,
     SYS_APPGROUP_FEATURE rtgf,
     SYS_APPFEATURE appf,
     SYS_APPFEATURE appf1,
     SYS_APP app,
     SYS_PAGE page,
     SYS_PAGE pag1
WHERE usr.APPGROUP_ID = apg.APPGROUP_ID
  AND rtgf.APP_CODE = app.APP_CODE
  AND apg.APPGROUP_ID = rtgf.APPGROUP_ID
  AND rtgf.APP_CODE = appf.APP_CODE
  AND rtgf.APPFEATURE_CODE = appf.APPFEATURE_CODE
  AND appf.PAGE_ID = page.PAGE_ID
  AND app.HOMEAPPFEATURE_ID = pag1.PAGE_ID
  AND appf.PARENTAPPFEATURE_CODE = appf1.APPFEATURE_CODE (+)
  --AND a.USER_CODE='FIRBIRD'
  AND apg.ISVALID = 'Y'
  AND rtgf.ISVALID = 'Y'
  AND appf.ISVALID = 'Y'
  AND appf.STATUS_CODE = 'ACTIVE'
  AND app.APPSTATUS_CODE = 'ACTIVE'
ORDER BY USER_ID,APP_CODE