IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[CPJ_Menu]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].CPJ_Menu 
END
GO

CREATE PROCEDURE [dbo].CPJ_Menu @pJson VARCHAR(8000)
AS
DECLARE @pId_Menu			INT
DECLARE @pId_User			INT

SET @pId_Menu				= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJson,'Id_Menu')), 0)
SET @pId_User				= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJson,'Id_User')), 0)

SELECT  M.Id_Menu,M.Descripcion_Ori, M.Descripcion, M.Tipo, M.Funcion,M.Ubicacion,M.Icono,M.Dll,M.DLL_Proc,O.Orden,1 AS Nivel
INTO #Menu
                                 FROM clever.dbo.Seg_Menu M,clever.dbo.Seg_Menu_Mod O
                                WHERE M.Id_Menu = O.Id_Menu
                                 AND O.Id_Modulo = 1
                                 AND O.Id_Menu_Padre = @pId_Menu
			                     AND ((dbo.CF_Seg_User_Validar(@pId_User, M.Equate_Door)=1 ) or M.Tipo='G') 
                                 ORDER BY O.Orden

INSERT INTO #Menu
SELECT  M.Id_Menu,M.Descripcion_Ori, M.Descripcion, M.Tipo, M.Funcion,M.Ubicacion,M.Icono,M.Dll,M.DLL_Proc,O.Orden,2

                                 FROM clever.dbo.Seg_Menu M,clever.dbo.Seg_Menu_Mod O
                                WHERE M.Id_Menu = O.Id_Menu
                                 AND O.Id_Modulo = 1
                                 AND O.Id_Menu_Padre IN (SELECT Id_Menu FROM #Menu WHERE Tipo ='G' AND Nivel = 1)
			                     AND ((dbo.CF_Seg_User_Validar(@pId_User, M.Equate_Door)=1 ) or M.Tipo='G') 
                                 ORDER BY O.Orden


INSERT INTO #Menu
SELECT  M.Id_Menu,M.Descripcion_Ori, M.Descripcion, M.Tipo, M.Funcion,M.Ubicacion,M.Icono,M.Dll,M.DLL_Proc,O.Orden,3

                                 FROM clever.dbo.Seg_Menu M,clever.dbo.Seg_Menu_Mod O
                                WHERE M.Id_Menu = O.Id_Menu
                                 AND O.Id_Modulo = 1
                                 AND O.Id_Menu_Padre IN (SELECT Id_Menu FROM #Menu WHERE Tipo ='G' AND Nivel = 2)
			                     AND ((dbo.CF_Seg_User_Validar(@pId_User, M.Equate_Door)=1 ) or M.Tipo='G') 
                                 ORDER BY O.Orden

SELECT * FROM #Menu ORDER BY Orden
DROP TABLE #Menu

/*

EXEC CPJ_Menu '{"Id_Menu":"39","Id_User":"-1"}'

*/
