/****** OBJECT:  STOREDPROCEDURE [DBO].[P_EST_PROD_EST_ARV_MERCADOLOGICA_PRODUTO_NOVO]    SCRIPT DATE: 01/23/2014 15:19:55 ******/
IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_EST_PROD_EST_ARV_MERCADOLOGICA_PRODUTO_NOVO]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [DBO].[P_EST_PROD_EST_ARV_MERCADOLOGICA_PRODUTO_NOVO]
go
go

CREATE PROCEDURE P_EST_PROD_EST_ARV_MERCADOLOGICA_PRODUTO_NOVO @CD_EMP INT=1, 
												    @CD_PROD INT=0,
												    @CD_ARV_MERC_LINHA int=0,
												    @CD_ARV_MERC_CATEG int=0

AS

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_AREA_ATENDIMENTO WHERE CD_ARV_MERC_AREA_ATEND = 0) 
begin
	INSERT INTO EST_ARV_MERC_AREA_ATENDIMENTO (CD_EMP,CD_ARV_MERC_AREA_ATEND,DS_ARV_MERC_AREA_ATEND)
	SELECT 1,0,'NAO DEFINIDO';
end

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_DEPTO WHERE CD_ARV_MERC_DEPTO = 0) 
begin

	INSERT INTO EST_ARV_MERC_DEPTO (CD_EMP,CD_ARV_MERC_DEPTO,CD_ARV_MERC_AREA_ATEND,DS_ARV_MERC_DEPTO)
	SELECT 1,0,0,'NAO DEFINIDO';
end

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_CATEGORIA WHERE CD_ARV_MERC_CATEG = 0) 
begin
	INSERT INTO EST_ARV_MERC_CATEGORIA (CD_EMP,
											CD_ARV_MERC_CATEG,
											CD_ARV_MERC_DEPTO,
											DS_ARV_MERC_CATEG,
											NR_ITEM_CTB)
	SELECT 1,0,0,'NAO DEFINIDO','';
end

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_SEGMENTO WHERE CD_ARV_MERC_SEG = 0) 
begin
	INSERT INTO EST_ARV_MERC_SEGMENTO(CD_EMP,CD_ARV_MERC_SEG,DS_ARV_MERC_SEG)
	SELECT 1,0,'NAO DEFINIDO';
end

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_PAPEL WHERE CD_ARV_MERC_PAPEL = 0) 
begin
	INSERT INTO EST_ARV_MERC_PAPEL (CD_EMP,CD_ARV_MERC_PAPEL,DS_ARV_MERC_PAPEL,TP_PAPEL)
	SELECT 1,0,'NAO DEFINIDO',0;
end

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_MIX WHERE CD_ARV_MERC_MIX = 0) 
begin
	
	INSERT INTO EST_ARV_MERC_MIX (CD_EMP,CD_ARV_MERC_MIX,DS_ARV_MERC_MIX)
	SELECT 1,0,'NAO DEFINIDO';
end

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_CONSUMO WHERE CD_ARV_MERC_CONSUMO = 0) 
begin
	
	INSERT INTO EST_ARV_MERC_CONSUMO (CD_EMP,CD_ARV_MERC_CONSUMO,DS_ARV_MERC_CONSUMO)
	SELECT 1,0,'NAO DEFINIDO';
end

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_LINHA WHERE CD_ARV_MERC_LINHA = 0) 
begin
	INSERT INTO EST_ARV_MERC_LINHA (CD_EMP,CD_ARV_MERC_LINHA,DS_ARV_MERC_LINHA,DS_SIGLA_LINHA,
									FLAG_LINHA_MIX,FLAG_NAO_PARTIC_INDIC,
									FLAG_PARTIC_PRECIFICACAO,NR_GRP_PROD,CD_ARV_MERC_CLASSIFICACAO,FLAG_LINHA_NAO_FAZ_BALANCO)
	SELECT 1,0,'NAO DEFINIDO','O',0,1,'0101',NULL,0,0;
end

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_MTZ_BCG WHERE CD_ARV_MERC_MTZ_BCG = 0) 
begin
	INSERT INTO EST_ARV_MERC_MTZ_BCG (CD_EMP,CD_ARV_MERC_MTZ_BCG,DS_ARV_MERC_MTZ_BCG)
	SELECT 1,0,'NAO DEFINIDO';
end
IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_FAMILIA WHERE CD_ARV_MERC_FAMILIA = 0) 
begin
	INSERT INTO EST_ARV_MERC_FAMILIA (CD_EMP,CD_ARV_MERC_FAMILIA,DS_ARV_MERC_FAMILIA)
	SELECT 1,0,'NAO DEFINIDO';
end
IF NOT EXISTS (SELECT * FROM EST_MC WHERE CD_MC = 0)
begin
	INSERT INTO EST_MC(CD_EMP,CD_MC,DS_MC,PERC_MIN,PERC_VD)
	SELECT 1,0,'NAO DEFINIDO',0,0;
end

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_EQUIVALENTE WHERE CD_ARV_MERC_EQUI = 0) 
begin
	INSERT INTO EST_ARV_MERC_EQUIVALENTE (CD_EMP,CD_ARV_MERC_EQUI,DS_ARV_MERC_EQUI)
	SELECT 1,0,'NAO DEFINIDO';
end
IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_APRESENTACAO WHERE CD_APRES = 0) 
begin
	INSERT INTO EST_ARV_MERC_APRESENTACAO (CD_EMP,CD_APRES,DS_APRES)
	SELECT 1,0,'NAO DEFINIDO';
end

IF NOT EXISTS (SELECT * FROM EST_GRP_EXPO WHERE CD_GRP_EXPO = 0) 
begin
	INSERT INTO EST_GRP_EXPO (CD_EMP,CD_GRP_EXPO,DS_GRP_EXPO,OBS_GRP_EXPO,CM_FRENTE,CM_PROFUNDIDADE,CD_USU,DT_CAD)
	SELECT 1,0,'NAO DEFINIDO','',0,0,1,GETDATE();
end

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_CLASSIFICACAO WHERE CD_ARV_MERC_CLASSIFICACAO = 0) 
begin
	INSERT INTO EST_ARV_MERC_CLASSIFICACAO (CD_EMP,CD_ARV_MERC_CLASSIFICACAO,DS_ARV_MERC_CLASSIFICACAO,TP_ARV_MERC_CLASSIFICACAO)
	SELECT 1,0,'NAO DEFINIDO',0;
end

IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_FASE WHERE CD_ARV_MERC_FASE = 0) 
begin
	INSERT INTO EST_ARV_MERC_FASE (CD_EMP,CD_ARV_MERC_FASE,DS_FASE)
	SELECT 1,0,'NAO DEFINIDO';
end


IF NOT EXISTS (SELECT * FROM EST_ARV_MERC_GENERO WHERE CD_ARV_MERC_GENERO = 0) 
begin
	INSERT INTO EST_ARV_MERC_GENERO (CD_EMP,CD_ARV_MERC_GENERO,DS_GENERO)
	SELECT 1,0,'NAO DEFINIDO';
end

IF @CD_ARV_MERC_CATEG>0
    IF NOT EXISTS(SELECT CD_EMP FROM EST_ARV_MERC_CATEGORIA 
				WHERE CD_ARV_MERC_CATEG=@CD_ARV_MERC_CATEG)
			 SET @CD_ARV_MERC_CATEG=0;

			 
IF @CD_ARV_MERC_LINHA>0
    IF NOT EXISTS(SELECT CD_EMP FROM EST_ARV_MERC_LINHA
				WHERE CD_ARV_MERC_LINHA=@CD_ARV_MERC_LINHA)
			 SET @CD_ARV_MERC_LINHA=0;


INSERT INTO EST_PROD_EST_ARV_MERCADOLOGICA (CD_EMP, CD_PROD, CD_ARV_MERC_CATEG, CD_ARV_MERC_SEG, CD_ARV_MERC_PAPEL,
											 CD_ARV_MERC_MIX, CD_ARV_MERC_CONSUMO, CD_ARV_MERC_LINHA, 
												CD_ARV_MERC_MTZ_BCG, CD_MC, CD_ARV_MERC_FAMILIA, CD_USU, 
													DT_CAD, DT_ULT_ALT, 
													CD_ARV_MERC_EQUI, CD_APRES, CD_GRP_EXPO, CD_ARV_MERC_CLASSIFICACAO, 
													CD_ARV_MERC_FASE, CD_ARV_MERC_GENERO, GRAMATURA )
SELECT 
	CD_EMP,
	CD_PROD,
	@CD_ARV_MERC_CATEG,
	0 CD_ARV_MERC_SEG,
	0 CD_ARV_MERC_PAPEL,
	0 CD_ARV_MERC_MIX,
	0 CD_ARV_MERC_CONSUMO,
	@CD_ARV_MERC_LINHA,
	0 CD_ARV_MERC_MTZ_BCG,
	0 CD_MC,
	0 CD_ARV_MERC_FAMILIA,
	1 CD_USU,
	GETDATE() DT_CAD,
	NULL DT_ULT_ALT,
	0 CD_ARV_MERC_EQUI,	
	0 CD_APRES,
	0 CD_GRP_EXPO,
     0 CD_ARV_MERC_CLASSIFICACAO,
	0 CD_ARV_MERC_FASE, 
	0 CD_ARV_MERC_GENERO, 
	0 GRAMATURA 
FROM
	EST_PROD
WHERE
	CD_EMP=@CD_EMP
	AND CD_PROD = CASE WHEN @CD_PROD=0 THEN CD_PROD ELSE @CD_PROD END
	AND NOT EXISTS(SELECT 
					*
			   FROM
					EST_PROD_EST_ARV_MERCADOLOGICA
			   WHERE
					EST_PROD.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND
					EST_PROD.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD)
					
					
					
					
				
GO


