一级菜单获取2,3级菜单

----新首页根据一级菜单获取2,3级菜单

SELECT DISTINCT CASE
                  WHEN P.GOTO_TARGET IS NULL THEN
                   0
                  WHEN P.GOTO_TARGET = 2 THEN
                   0
                  ELSE
                   P.GOTO_TARGET
                END NEW_WINDOW,
                P.PRIVILEGE_ID MENU_ID,
                P.MENU_RANK,
                P.PRIVILEGE_NAME MENU_NAME,
                CASE
                  WHEN A.DOMAIN = 0 THEN
                   DECODE(P.REPORT_ID,
                          NULL,
                          P.URL || DECODE(INSTR(P.URL, '?', -1, 1),
                                          0,
                                          '?menuId=',
                                          '&' || 'menuId=') || P.PRIVILEGE_ID,
                          (SELECT S.SERVER_URL || R.REPORT_URL ||
                                  DECODE(INSTR(R.REPORT_URL, '?', -1, 1),
                                         0,
                                         '?menuId=',
                                         '&' || 'menuId=') || P.PRIVILEGE_ID
                             FROM DBP_REPORT        R,
                                  DBP_REPORT_SERVER RS,
                                  DBP_SERVER        S
                            WHERE R.REPORT_ID = RS.REPORT_ID
                              AND RS.SERVER_ID = S.SERVER_ID
                              AND R.REPORT_ID = P.REPORT_ID
                              AND ROWNUM = 1))
                  WHEN A.DOMAIN = 1 THEN
                   DECODE(P.REPORT_ID,
                          NULL,
                          P.URL || DECODE(INSTR(P.URL, '?', -1, 1),
                                          0,
                                          '?menuId=',
                                          '&' || 'menuId=') || P.PRIVILEGE_ID,
                          (SELECT R.REPORT_URL ||
                                  DECODE(INSTR(R.REPORT_URL, '?', -1, 1),
                                         0,
                                         '?menuId=',
                                         '&' || 'menuId=') || P.PRIVILEGE_ID
                             FROM DBP_REPORT R
                            WHERE R.REPORT_ID = P.REPORT_ID
                              AND ROWNUM = 1))
                END MENU_ADDR,
                DECODE(P.REPORT_ID,
                       NULL,
                       NULL,
                       (SELECT DECODE(T.CUR_STATE,
                                      3,
                                      -1,
                                      TO_NUMBER(SUBSTR((CAST(SYSDATE AS
                                                             TIMESTAMP) -
                                                       T.RECENT_TIME),
                                                       1,
                                                       INSTR(CAST(SYSDATE AS
                                                                  TIMESTAMP) -
                                                             T.RECENT_TIME,
                                                             ' '))) + 1) DAYS
                          FROM DBP_REPORT T
                         WHERE T.REPORT_ID = P.REPORT_ID)) REPORT_STATE,
                P.MENU_TYPE MENU_TYPE_ID,
                DECODE(P.PARENT_PRIVILEGE_ID, 0, -1, P.PARENT_PRIVILEGE_ID) PAR_MENU_ID,
                P.STATE STATE,
                P.DESCRIPTION MENU_DESC,
                P.REQUIREMENT_ID REQUIREMENT_ID,
                P.LAYER MENU_LEVEL,
                DECODE(P.PRIVILEGE_ID, #NOWID#, -1, P.POSITION) MENUORDER,
                P.APP_ID BELONG_SYS,
                CASE
                  WHEN P.IS_HIDE = 1 THEN
                   P.IS_HIDE
                  ELSE
                   0
                END IS_SHOW,
                P.MENU_OTHERNAME MENU_NICKNAME,
                P.IS_SUBSCRIBE IS_SUBSCRIBE,
                P.REPORT_ID,
                P.IS_CODE,
                A.DOMAIN DOMAIN,
                DECODE(P.REPORT_ID,
                       NULL,
                       NULL,
                       (SELECT TO_CHAR(EXIT_TIME, 'yyyy-mm-dd')
                          FROM DBP_REPORT T
                         WHERE T.REPORT_ID = P.REPORT_ID)) EXIT_TIME,
                DECODE(P.REPORT_ID,
                       NULL,
                       NULL,
                       (SELECT REQ_ASKER
                          FROM DBP_REPORT T
                         WHERE T.REPORT_ID = P.REPORT_ID)) REQ_ASKER,
                P.IMAGE
  FROM (SELECT X.LAYER,
               X.PRIVILEGE_ID,
               X.PRIVILEGE_CODE,
               X.PRIVILEGE_NAME,
               X.DESCRIPTION,
               X.SUPER_CODE,
               X.PRIVILEGE_TYPE,
               X.URL,
               X.STATE,
               X.POSITION,
               X.MENU_TARGET,
               X.APP_ID,
               X.PARENT_PRIVILEGE_ID,
               X.IMAGE,
               X.IMAGE_DIS,
               X.CHECKED,
               X.HOT_KEY,
               X.TYPE,
               X.GROUPS,
               X.IS_LEAF,
               X.MENU_TYPE,
               X.GOTO_TARGET,
               X.SYS_CODE,
               X.CREATER_ID,
               X.CREATE_DATE,
               X.IS_HIDE,
               X.REPORT_ID,
               X.MENU_OTHERNAME,
               X.CREATER_LATN_ID,
               X.IS_GENERAL,
               X.IS_SUBSCRIBE,
               X.MENU_RANK,
               X.REQUIREMENT_ID,
               X.EXTEND1,
               X.IS_CODE,
               X.EXTEND_URL,
               X.PUSH_TYPE
          FROM TB_SYS_PRIVILEGE X
         WHERE X.IS_HIDE = #ISHIDE#
         START WITH X.PARENT_PRIVILEGE_ID = #PMENUID#
        CONNECT BY PRIOR X.PRIVILEGE_ID = X.PARENT_PRIVILEGE_ID
        UNION
        SELECT LAYER,
               PRIVILEGE_ID,
               PRIVILEGE_CODE,
               PRIVILEGE_NAME,
               DESCRIPTION,
               SUPER_CODE,
               PRIVILEGE_TYPE,
               URL,
               STATE,
               POSITION,
               MENU_TARGET,
               APP_ID,
               PARENT_PRIVILEGE_ID,
               IMAGE,
               IMAGE_DIS,
               CHECKED,
               HOT_KEY,
               TYPE,
               GROUPS,
               IS_LEAF,
               MENU_TYPE,
               GOTO_TARGET,
               SYS_CODE,
               CREATER_ID,
               CREATE_DATE,
               IS_HIDE,
               REPORT_ID,
               MENU_OTHERNAME,
               CREATER_LATN_ID,
               IS_GENERAL,
               IS_SUBSCRIBE,
               MENU_RANK,
               REQUIREMENT_ID,
               EXTEND1,
               IS_CODE,
               EXTEND_URL,
               PUSH_TYPE
          FROM TB_SYS_PRIVILEGE
         WHERE PRIVILEGE_ID = #PMENUID#) P,
       TB_SYS_EMPEE_ROLE ER,
       TB_SYS_ROLE_PRIVILEGE RP,
       TB_SYS_APPLICATION A,
       TB_SYS_ROLE R
WHERE P.STATE = 1
   AND P.PUSH_TYPE = 3
   AND P.APP_ID = A.APP_ID
   AND P.PRIVILEGE_ID = RP.PRIVILEGE_ID
   AND RP.ROLE_ID = ER.ROLE_ID
   AND R.ROLE_ID = ER.ROLE_ID
   AND ER.EMPEE_ID = #USERID#
   AND P.LAYER IS NOT NULL
   AND P.MENU_TYPE <> 4
   AND R.STATE = '1'
ORDER BY MENU_LEVEL, MENUORDER