-- SELECT DISTINCT TABELA FROM GLB_CTR_CHAVE_TBL


-- Criacao das tabelas PDV_VD_ID e EST_ORC_ID
IF OBJECT_ID('PDV_VD_ID')				IS NOT NULL drop table PDV_VD_ID
IF OBJECT_ID('EST_ORC_ID')				IS NOT NULL drop table EST_ORC_ID
IF OBJECT_ID('RC_VD_CONV_ID')			IS NOT NULL drop table RC_VD_CONV_ID
IF OBJECT_ID('PDV_MAPA_RESUMO_ID')		IS NOT NULL drop table PDV_MAPA_RESUMO_ID
IF OBJECT_ID('PDV_CUPOM_NAO_FISCAL_ID')	IS NOT NULL drop table PDV_CUPOM_NAO_FISCAL_ID
IF OBJECT_ID('CHQ_EMI_ID')				IS NOT NULL drop table CHQ_EMI_ID
IF OBJECT_ID('BC_LANC_ID')				IS NOT NULL drop table BC_LANC_ID
IF OBJECT_ID('RC_CLI_ID')				IS NOT NULL drop table RC_CLI_ID
IF OBJECT_ID('EST_NF_SAI_ID')			IS NOT NULL drop table EST_NF_SAI_ID
IF OBJECT_ID('EST_PED_VD_ID')			IS NOT NULL drop table EST_PED_VD_ID
IF OBJECT_ID('EST_PROD_TRANSF_ID')		IS NOT NULL drop table EST_PROD_TRANSF_ID
IF OBJECT_ID('EST_PED_ELTR_ID')			IS NOT NULL drop table EST_PED_ELTR_ID
IF OBJECT_ID('EST_PED_CP_ID')			IS NOT NULL drop table EST_PED_CP_ID

go
CREATE TABLE PDV_VD_ID
	(ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
	,FLAG BIT NULL)
GO

CREATE TABLE EST_ORC_ID
	(ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
	,FLAG BIT NULL)
GO

create table RC_VD_CONV_ID
	( ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
		,FLAG BIT NULL)
GO
create table PDV_MAPA_RESUMO_ID
	( ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
		,FLAG BIT NULL)
GO
create table PDV_CUPOM_NAO_FISCAL_ID
	( ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
		,FLAG BIT NULL)
GO
create table CHQ_EMI_ID
	( ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
		,FLAG BIT NULL)
GO
create table BC_LANC_ID
	( ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
		,FLAG BIT NULL)
GO
create table RC_CLI_ID
	( ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
		,FLAG BIT NULL)
GO

create table EST_NF_SAI_ID
	( ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
		,FLAG BIT NULL)
GO

create table EST_PED_VD_ID
	( ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
		,FLAG BIT NULL)
GO

create table EST_PROD_TRANSF_ID
	( ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
		,FLAG BIT NULL)
GO

create table EST_PED_ELTR_ID
	( ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
		,FLAG BIT NULL)
GO

create table EST_PED_CP_ID
	( ID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED
		,FLAG BIT NULL)
GO

-- Inicializar IDENTITY 
DECLARE @ID_MAX INT

-- PDV_VD_ID
SELECT @ID_MAX = MAX(CD_VD)+100 FROM PDV_VD WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_PDV_VD=@ID_MAX --592569
DBCC CHECKIDENT(PDV_VD_ID, RESEED, @ID_MAX) 

-- EST_ORC_ID
SELECT @ID_MAX =  MAX(CD_ORC)+100 FROM EST_ORC WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_EST_ORC=@ID_MAX --282190
DBCC CHECKIDENT(EST_ORC_ID, RESEED, @ID_MAX) 

-- RC_VD_CONV
SELECT @ID_MAX =  MAX(CD_CTR)+100 FROM RC_VD_CONV WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = MAX(CD_CTR)+100 FROM RC_VD_CONV
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_RC_VD_CONV=@ID_MAX --60644
DBCC CHECKIDENT(RC_VD_CONV_ID, RESEED, @ID_MAX) 

--PDV_MAPA_RESUMO
SELECT @ID_MAX =  MAX(CD_CTR)+100 FROM PDV_MAPA_RESUMO WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = MAX(CD_CTR)+100 FROM PDV_MAPA_RESUMO
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_PDV_MAPA_RESUMO=@ID_MAX --60644
DBCC CHECKIDENT(PDV_MAPA_RESUMO_ID, RESEED, @ID_MAX) 


--PDV_CUPOM_NAO_FISCAL
SELECT @ID_MAX =  MAX(CD_CTR)+100 FROM PDV_CUPOM_NAO_FISCAL WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = MAX(CD_CTR)+100 FROM PDV_CUPOM_NAO_FISCAL
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_PDV_CUPOM_NAO_FISCAL=@ID_MAX --60644
DBCC CHECKIDENT(PDV_CUPOM_NAO_FISCAL_ID, RESEED, @ID_MAX) 

--CHQ_EMI
SELECT @ID_MAX =  MAX(CD_EMI)+100 FROM CHQ_EMI WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = MAX(CD_EMI)+100 FROM CHQ_EMI
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_CHQ_EMI=@ID_MAX --60644
DBCC CHECKIDENT(CHQ_EMI_ID, RESEED, @ID_MAX) 

--BC_LANC_ID
SELECT @ID_MAX =  MAX(CD_BC_LANC)+10 FROM BC_LANC WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = MAX(CD_BC_LANC)+100 FROM BC_LANC
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_BC_LANC=@ID_MAX --60644
DBCC CHECKIDENT(BC_LANC_ID, RESEED, @ID_MAX) 


--RC_CLI_ID
SELECT @ID_MAX =  MAX(CD_CLI)+10 FROM RC_CLI WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = MAX(CD_CLI)+10 FROM RC_CLI
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_RC_CLI=@ID_MAX --60644
DBCC CHECKIDENT(RC_CLI_ID, RESEED, @ID_MAX) 

--EST_NF_SAI_ID
SELECT @ID_MAX =  MAX(CD_NF)+10 FROM EST_NF_SAI WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = MAX(CD_NF)+10 FROM EST_NF_SAI
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_EST_NF_SAI=@ID_MAX --60644
DBCC CHECKIDENT(EST_NF_SAI_ID, RESEED, @ID_MAX) 

--EST_PED_VD_ID
SELECT @ID_MAX =  MAX(CD_PED)+10 FROM EST_PED_VD WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = MAX(CD_PED)+10 FROM EST_PED_VD
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_EST_PED_VD=@ID_MAX --60644
DBCC CHECKIDENT(EST_PED_VD_ID, RESEED, @ID_MAX) 

--EST_PROD_TRANSF_ID
SELECT @ID_MAX =  MAX(CD_CTR)+10 FROM EST_PROD_TRANSF WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = MAX(CD_CTR)+10 FROM EST_PROD_TRANSF
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_EST_PROD_TRANSF=@ID_MAX --60644
DBCC CHECKIDENT(EST_PROD_TRANSF_ID, RESEED, @ID_MAX) 

--EST_PED_ELTR_ID
SELECT @ID_MAX =  MAX(CD_PED_SOL) + 10 FROM EST_PED_ELTR WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = MAX(CD_PED_SOL)+10 FROM EST_PED_ELTR
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_EST_PED_ELTR=@ID_MAX --60644
DBCC CHECKIDENT(EST_PED_ELTR_ID, RESEED, @ID_MAX) 

--EST_PED_CP_ID
SELECT @ID_MAX =  MAX(CD_PED) + 10 FROM EST_PED_CP WHERE CD_EMP=1
IF @ID_MAX IS NULL SELECT @ID_MAX = MAX(CD_PED)+10 FROM EST_PED_CP
IF @ID_MAX IS NULL SELECT @ID_MAX = 1;
SELECT ID_MAX_EST_PED_CP=@ID_MAX --60644
DBCC CHECKIDENT(EST_PED_CP_ID, RESEED, @ID_MAX)


/*

EXEC P_GET_NEXT_KEY_TABLE 'PDV_VD',1,1
EXEC P_GET_NEXT_KEY_TABLE 'PDV_VD',1,2
						  
EXEC P_GET_NEXT_KEY_TABLE 'EST_ORC',1,1
EXEC P_GET_NEXT_KEY_TABLE 'EST_ORC',1,7
						  
EXEC P_GET_NEXT_KEY_TABLE 'RC_VD_CONV',1
EXEC P_GET_NEXT_KEY_TABLE 'RC_VD_CONV',1

EXEC P_GET_NEXT_KEY_TABLE 'RC_VD_CONV',1

						  
EXEC P_GET_NEXT_KEY_TABLE 'BC_LANC',1

*/
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
			WHERE ROUTINE_NAME= 'P_GET_NEXT_KEY_TABLE'
				AND ROUTINE_TYPE='PROCEDURE')
BEGIN
	DROP PROCEDURE P_GET_NEXT_KEY_TABLE
END
GO
GO
CREATE PROCEDURE P_GET_NEXT_KEY_TABLE  (@TableName varchar(100)
										  , @Key1 Int  
										  , @Key2 Int=null  
										  , @Key3 Int=null  
										  , @Key4 Int=null  
										  , @Key5 Int=null  
										  , @Atualizar Tinyint = 0)  
as  
begin  
/*   
Quem      Quando       O que foi feito  
========= ===========  ============================================================================================================  
PSR       31-OUT-2018  Alterado geração de IDs para tabelas PDV_VD e EST_ORC. Agora existem duas tabelas (= PDV_VD_ID e EST_ORC_ID)  
                       que efetuam o tratamento do auto-incremento em coluna IDENTITY.  
*/  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 set nocount on;   
 Declare @TableVar  table(	Chave2 Int  
						   ,Chave3 Int  
						   ,Chave4 Int  
						   ,Chave5 Int )  
  
REDOKEY:  
	delete from @TableVar  
  
	-- Geracao de IDs em tabela especifica para PDV_VD e EST_ORC -----------------------------  
	if @TableName IN ('PDV_VD' ,'EST_ORC','RC_VD_CONV', 
						'PDV_MAPA_RESUMO', 'PDV_CUPOM_NAO_FISCAL', 'CHQ_EMI',
						'BC_LANC', 'RC_CLI', 'EST_NF_SAI',
						'EST_PED_VD', 'EST_PROD_TRANSF', 'EST_PED_ELTR', 'EST_PED_CP')
	begin  
		RETRY:  
			if @TableName = 'PDV_VD'				insert into PDV_VD_ID					(Flag) values (1)  
			if @TableName = 'EST_ORC'				insert into EST_ORC_ID					(Flag) values (1)  
			if @TableName = 'RC_VD_CONV'			insert into RC_VD_CONV_ID				(Flag) values (1)  
			if @TableName = 'PDV_MAPA_RESUMO'		insert into PDV_MAPA_RESUMO_ID			(Flag) values (1)    
			if @TableName = 'PDV_CUPOM_NAO_FISCAL'	insert into PDV_CUPOM_NAO_FISCAL_ID		(Flag) values (1)  
			if @TableName = 'CHQ_EMI'				insert into CHQ_EMI_ID					(Flag) values (1)  
			if @TableName = 'BC_LANC'				insert into BC_LANC_ID					(Flag) values (1)  
			if @TableName = 'RC_CLI'				insert into RC_CLI_ID					(Flag) values (1)  
			if @TableName = 'EST_NF_SAI'			insert into EST_NF_SAI_ID				(Flag) values (1)  
			if @TableName = 'EST_PED_VD'			insert into EST_PED_VD_ID				(Flag) values (1)  
			if @TableName = 'EST_PROD_TRANSF'		insert into EST_PROD_TRANSF_ID			(Flag) values (1)  
			if @TableName = 'EST_PED_ELTR'			insert into EST_PED_ELTR_ID				(Flag) values (1)  
			if @TableName = 'EST_PED_CP'			insert into EST_PED_CP_ID				(Flag) values (1)  

			IF @TableName in ('PDV_VD','EST_ORC','PDV_MAPA_RESUMO','PDV_CUPOM_NAO_FISCAL') 
				insert into @TableVar (Chave3) values (@@IDENTITY);

			IF @TableName in ('RC_VD_CONV','CHQ_EMI','BC_LANC','RC_CLI', 'EST_PROD_TRANSF', 'EST_PED_VD', 'EST_NF_SAI', 'EST_PED_ELTR', 'EST_PED_CP')				
				insert into @TableVar (Chave2) values (@@IDENTITY);  
			 
		goto FIM  
	end  
 ------------------------------------------------------------------------------------------  
     
	if @Key2 is null  
	begin  
		update T   
		set  
			CD_CHAVE2 = isnull((Select   
									Max(CD_CHAVE2)+1 Chave  
								from   
									Glb_ctr_chave_tbl  
								where  
									TABELA = T.TABELA  
									AND CD_CHAVE1 = T.CD_CHAVE1 ),1)  
			OUTPUT inserted.CD_CHAVE2  
			Into @TableVar (Chave2)  
		FROM   
			glb_ctr_chave_tbl T  
		where  
			TABELA =  @TableName   
			AND CD_CHAVE1= @Key1;  
	End  
	Else if @Key3 is null  
	begin  
    
		update T   
		set  
			CD_CHAVE3 = isnull((Select   
									Max(CD_CHAVE3)+1 Chave  
								from   
									Glb_ctr_chave_tbl  
								where  
									TABELA = T.TABELA  
									AND CD_CHAVE1 = T.CD_CHAVE1  
									AND CD_CHAVE2 = T.CD_CHAVE2 ),1)  
			OUTPUT inserted.CD_CHAVE3   
			Into @TableVar (Chave3)  
		FROM   
			glb_ctr_chave_tbl T  
		where  
			TABELA =  @TableName   
			AND CD_CHAVE1= @Key1  
			AND CD_CHAVE2= @Key2;   
	End  
	Else if @Key4 is null  
	begin  
		update T   
		set  
			CD_CHAVE4 = isnull((Select   
								Max(CD_CHAVE4)+1 Chave  
							from   
								Glb_ctr_chave_tbl  
							where  
								TABELA = T.TABELA  
								AND CD_CHAVE1 = T.CD_CHAVE1  
								AND CD_CHAVE2 = T.CD_CHAVE2   
								AND CD_CHAVE3 = T.CD_CHAVE3 ),1)  
			OUTPUT inserted.CD_CHAVE4   
			Into @TableVar (Chave4)  
		FROM   
			glb_ctr_chave_tbl T  
		where  
			TABELA =  @TableName   
			AND CD_CHAVE1= @Key1  
			AND CD_CHAVE2= @Key2  
			AND CD_CHAVE3= @Key3;   
	End  
	Else if @Key5 is null  
	begin  
		update T   
		set  
			CD_CHAVE5 = isnull((Select   
								Max(CD_CHAVE5)+1 Chave  
								from   
									Glb_ctr_chave_tbl  
								where  
									TABELA = T.TABELA  
									AND CD_CHAVE1 = T.CD_CHAVE1  
									AND CD_CHAVE2 = T.CD_CHAVE2   
									AND CD_CHAVE3 = T.CD_CHAVE3   
									AND CD_CHAVE4 = T.CD_CHAVE4 ),1)  
			OUTPUT inserted.CD_CHAVE5   
			Into @TableVar (Chave5)  
		FROM   
			glb_ctr_chave_tbl T  
		where  
			TABELA =  @TableName   
			AND CD_CHAVE1= @Key1  
			AND CD_CHAVE2= @Key2  
			AND CD_CHAVE3= @Key3  
			AND CD_CHAVE4= @Key4;   
	End  
  
	if not exists(select * from @TableVar)  
	begin  
  
		INSERT INTO glb_ctr_chave_tbl(TABELA,CD_CHAVE1,CD_CHAVE2,CD_CHAVE3,CD_CHAVE4,CD_CHAVE5)  
		select  
			@TableName Tabela  
			, @Key1 Chave1  
			, coalesce(@Key2,1) AS Chave2  
			, coalesce(@Key3,1) AS Chave3  
			, coalesce(@Key4,1) AS Chave4  
			, coalesce(@Key5,1) AS Chave5  
  
		INSERT INTO @TableVar  
		select  
			coalesce(@Key2,1) AS Chave2  
			,coalesce(@Key3,1) AS Chave3  
			,coalesce(@Key4,1) AS Chave4  
			,coalesce(@Key5,1) AS Chave5  
     
	end  
  
 -------------------------------------------------------------  
 FIM:  
  
	-- Verificar se chave gerada não existe na tabela   
	-- Check necessário pois existem processos que ainda não consomem essa proc   
	-- (utilizam o (MAX+1) diretamente na tabela)  
	if @TableName='RC_VD_CONV'  
	BEGIN  
		if exists (select * from rc_vd_conv where cd_emp=1 and cd_ctr = (select max(Chave2) From @TableVar t)) goto Redokey;  
	END  
	if @TableName='CHQ_EMI'  
	BEGIN  
		if exists (select * from chq_emi where cd_emp=1 and cd_emi = (select max(Chave2) From @TableVar t)) goto RedoKey;  
	END  
	if @TableName='PDV_MAPA_RESUMO'  
	BEGIN  
		if exists (select * from PDV_MAPA_RESUMO where cd_emp=1 and CD_FILIAL=@Key2
						and cd_ctr = (select max(Chave3) From @TableVar t)) 
			goto RedoKey;  
	END
	 
	if @TableName='BC_LANC'  
	BEGIN  
		if (SELECT max(CD_BC_LANC) 
					from BC_LANC with (nolock)
					where 
					cd_emp=1 
					and CD_BC_LANC >= (select max(Chave2) From @TableVar t)) >= (select max(Chave2) From @TableVar t)
		 goto RedoKey;  
	END  

	if @TableName='RC_CLI'  
	BEGIN  
		if (SELECT max(CD_CLI) 
					from RC_CLI with (nolock)
					where 
					cd_emp=1 
					and CD_CLI >= (select max(Chave2) From @TableVar t)) >= (select max(Chave2) From @TableVar t)
		 goto RedoKey;  
	END 

	if @TableName='EST_NF_SAI'  
	BEGIN  
		if (SELECT max(CD_NF) 
					from EST_NF_SAI with (nolock)
					where 
					cd_emp=1 
					and CD_NF >= (select max(Chave2) From @TableVar t)) >= (select max(Chave2) From @TableVar t)
		 goto RedoKey;  
	END  

	if @TableName='EST_PED_VD'  
	BEGIN  
		if (SELECT max(CD_PED) 
					from EST_PED_VD with (nolock)
					where 
					cd_emp=1 
					and CD_PED >= (select max(Chave2) From @TableVar t)) >= (select max(Chave2) From @TableVar t)
		 goto RedoKey;  
	END

	if @TableName='EST_PROD_TRANSF'  
	BEGIN  
		if (SELECT max(CD_CTR) 
					from EST_PROD_TRANSF with (nolock)
					where 
					cd_emp=1 
					and CD_CTR >= (select max(Chave2) From @TableVar t)) >= (select max(Chave2) From @TableVar t)
		 goto RedoKey;  
	END  

	if @TableName='EST_PED_ELTR'  
	BEGIN  
		if (SELECT max(CD_PED_SOL) 
					from EST_PED_ELTR with (nolock)
					where 
					cd_emp=1 
					and CD_PED_SOL >= (select max(Chave2) From @TableVar t)) >= (select max(Chave2) From @TableVar t)
		 goto RedoKey;  
	END
	
	if @TableName='EST_PED_CP'  
	BEGIN  
		if (SELECT max(CD_PED) 
					from EST_PED_CP with (nolock)
					where 
					cd_emp=1 
					and CD_PED >= (select max(Chave2) From @TableVar t)) >= (select max(Chave2) From @TableVar t)
		 goto RedoKey;  
	END 
	  
--------------------------------------------------------------  
	select top 1 
		@TableName Tabela  
		,@Key1 Chave1  
		,coalesce(Chave2, @Key2) AS Chave2  
		,coalesce(Chave3, @Key3) AS Chave3  
		,coalesce(Chave4, @Key4) AS Chave4  
		,coalesce(Chave5, @Key5) AS Chave5  
	from   
		@TableVar  

-------------------------------------------------------------  
  
end     