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

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

SET @pId_Menu				= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJson,'Id_Menu')), 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,O.Item
	INTO #Menu
	FROM Seg_Menu M,Seg_Menu_Mod O
	WHERE M.Id_Menu = O.Id_Menu
	AND O.Id_Modulo = 1
	AND O.Id_Menu_Padre = @pId_Menu
	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,O.Item
		FROM Seg_Menu M,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)
		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,O.Item
		FROM Seg_Menu M,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)
		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,4,O.Item
		FROM Seg_Menu M,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 = 3)
		ORDER BY O.Orden



SELECT * INTO #Menu2 FROM #Menu  ORDER BY Orden

ALTER TABLE #Menu2 ADD Orden_Nuevo INT IDENTITY(100,10)



UPDATE Seg_Menu_Mod  SET Orden = N.Orden_Nuevo FROM Seg_Menu_Mod V, #Menu2 N WHERE N.Item = V.Item AND N.Orden_Nuevo <> V.Orden


DROP TABLE #Menu
DROP TABLE #Menu2
/*

EXEC CPJ_Menu_Ordenar '{"Id_Menu":"39"}'

*/
