create or replace package PKG_SYS_WEB_UI is
   PROCEDURE SP_GET_NAVIGATION (P_OUT_NUM OUT NUMBER,
                                P_OUT_MSG OUT VARCHAR2, 
                                p_OUT_FEATUREIDLIST OUT VARCHAR2,
                               	P_APP_ID NUMBER,  
                               	P_FEATURE_ID NUMBER,
                               	P_OUT_NAGIVATOR OUT VARCHAR2,
                                P_WEBPORTAL_ROOT IN VARCHAR2);     


end PKG_SYS_WEB_UI;
/
create or replace package body PKG_SYS_WEB_UI is
PROCEDURE SP_GET_NAVIGATION(P_OUT_NUM OUT NUMBER,
                            P_OUT_MSG OUT VARCHAR2,
                            P_OUT_FEATUREIDLIST OUT VARCHAR2,
                            P_APP_ID NUMBER,
                            P_FEATURE_ID NUMBER,
                            P_OUT_NAGIVATOR OUT VARCHAR2,
                            P_WEBPORTAL_ROOT VARCHAR2) IS
    FEATUREID NUMBER; 
    APPID NUMBER;
BEGIN
    APPID := P_APP_ID;
    P_OUT_FEATUREIDLIST := TO_CHAR(P_FEATURE_ID);
        
    IF P_FEATURE_ID >0 THEN
       FEATUREID := P_FEATURE_ID;
       P_OUT_NAGIVATOR := '';
       
       SELECT MAX(app.APP_ID) INTO APPID
       FROM SYS_APP app,
            SYS_APPFEATURE appf
       WHERE app.APP_CODE = appf.APP_CODE
         AND appf.APPFEATURE_ID =P_FEATURE_ID;

       LOOP
           SELECT (CASE WHEN LENGTH(MAX(page.PAGEPATH))>0 THEN '><A HREF="javascript:if(parent.document.all.divMask!=null){WindowBlock(''1'')};location='''||MAX(page.PAGEPATH)
                             ||'?FEATUREID='||MAX(appf.APPFEATURE_ID)||'''">'||MAX(appf.MENUNAME)||'</A>'
                        ELSE (CASE WHEN LENGTH(MAX(appf.MENUNAME)) > 0 THEN '>'||MAX(appf.MENUNAME)
                                   ELSE '' END)
                   END)||P_OUT_NAGIVATOR,
                  MAX(appf1.APPFEATURE_ID)  INTO P_OUT_NAGIVATOR,FEATUREID
           FROM SYS_PAGE page,
                SYS_APPFEATURE appf,
                SYS_APPFEATURE appf1
           WHERE page.PAGE_ID = appf.PAGE_ID
             AND appf.PARENTAPPFEATURE_CODE = appf1.APPFEATURE_CODE
             AND appf.PARENTAPPFEATURE_CODE != appf.APPFEATURE_CODE
             AND appf.APPFEATURE_ID = FEATUREID;
           
           EXIT WHEN (SQL%ROWCOUNT = 0 OR FEATUREID = 1 OR FEATUREID is null);  
           P_OUT_FEATUREIDLIST := TO_CHAR(FEATUREID)||','||P_OUT_FEATUREIDLIST;                       
        
        END LOOP;
    END IF;
    
  IF APPID > 0 THEN        
       SELECT (CASE WHEN LENGTH(MAX(page.PAGEPATH))>0 THEN '<A HREF="javascript:if(parent.document.all.divMask!=null){WindowBlock(''1'');}location='''||
                                                    '/'||P_WEBPORTAL_ROOT||'/'||MAX(PATCH_CODE)||'/ASPXS/'||
                                                    MAX(page.PAGEPATH)||'?APPID='||MAX(app.APP_ID)||'''">'||
                                                    MAX(appf.MENUNAME)||'</A>'
                                              ELSE MAX(appf.MENUNAME)
                                              END)      
             ||P_OUT_NAGIVATOR INTO P_OUT_NAGIVATOR
       FROM SYS_PAGE page,
            SYS_APP app,
            SYS_APPFEATURE appf,
            SYS_PATCH pah
       WHERE page.PAGE_ID = appf.PAGE_ID
         AND app.APP_CODE = appf.App_Code
         and app.APP_CODE = pah.APP_CODE
         AND appf.APPFEATURE_CODE !='WB_PORTAL_HOME'
         AND appf.APPFEATURE_CODE = appf.PARENTAPPFEATURE_CODE
         AND app.APP_ID = APPID;
    END IF;
    dbms_output.put_line(p_out_nagivator);

    SELECT (CASE WHEN LENGTH(page.PAGEPATH)>0 THEN '<A HREF="javascript:if(parent.document.all.divMask!=null){WindowBlock(''1'');}location='''||
                                               '/'||P_WEBPORTAL_ROOT||'/ASPXS/'||page.PAGEPATH||'''">'||appf.MENUNAME||'</A>'
                                          ELSE appf.MENUNAME
                                          END)||
           (CASE WHEN LENGTH(P_OUT_NAGIVATOR)>0 THEN '>' 
                                                ELSE '' 
                                                END)
           ||P_OUT_NAGIVATOR INTO P_OUT_NAGIVATOR
    FROM SYS_APP app,
         SYS_PAGE page,
         SYS_APPFEATURE appf
    WHERE appf.PAGE_ID = page.PAGE_ID
      AND app.APP_CODE = appf.App_Code
      AND app.APP_CODE = 'WB_PORTAL'
      AND appf.APPFEATURE_CODE ='WB_PORTAL_HOME';

    P_OUT_NUM := 0;
    P_OUT_MSG := 'Calculate nagivator success!';
    
    EXCEPTION
    WHEN OTHERS THEN
  	    ROLLBACK;
         p_out_num := SQLCODE;
         p_out_msg := SUBSTR(SQLERRM,1,500);
	      Pkg_SYS_Log.sp_log_activity(P_FEATURE_ID,
						  			  		 			   'WB_PORTAL',
																   'ERROR',
																   'GET_NAGIVATOR',
																   p_out_msg,
																   p_out_num);  	
          
END;

end PKG_SYS_WEB_UI;
/