/****** Object:  StoredProcedure [dbo].[P_RC_CLI_CONV_PBM_CARGA_PRODUTOS]    Script Date: 12/02/2015 15:35:56 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_RC_CLI_CONV_PBM_CARGA_PRODUTOS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_RC_CLI_CONV_PBM_CARGA_PRODUTOS]
GO
GO
GO
--EXEC P_RC_CLI_CONV_PBM_CARGA_PRODUTOS
CREATE PROCEDURE [dbo].[P_RC_CLI_CONV_PBM_CARGA_PRODUTOS] AS
--Criando cabecalho do relacionado convenio
INSERT INTO RC_CLI_CONV_PBM 
		(CD_EMP,CD_CONV,TX_ADM,PERC_PROD_FR_LISTA,PERC_PROD_FR_LISTA_FORN,UTILIZA_DESC_IGUAL,PERC_DESC_PROD,UTILIZA_DESC_IGUAL_REPO,
			PERC_DESC_PROD_REPO,UTILIZA_DESC_SOBRE_EMP,VLR_SDO_CRED,VLR_SDO_DEB,PERC_REPASSE_FR_LISTA,TP_DIA_PGTO,NR_DIA_PGTO,
			VENDAFORALISTA,NR_DIA_PERM_FAT,UTILIZA_VALOR_REPASSE_ONLINE,PERC_DESCONTO_MINIMO,
			FLAG_POSSUI_REPOSICAO_PRODUTOS,TP_DESC_PRECO) 
select
	DISTINCT RC_CLI_CONV_VINC_RC_CLI.CD_EMP,
	RC_CLI_CONV_VINC_RC_CLI.CD_CONV,
	0 TX_ADM,PERC_PROD_FR_LISTA,
	0 PERC_PROD_FR_LISTA_FORN,
	'N' UTILIZA_DESC_IGUAL,
	0 PERC_DESC_PROD,
	'N' UTILIZA_DESC_IGUAL_REPO,
	0 PERC_DESC_PROD_REPO,
	'N' UTILIZA_DESC_SOBRE_EMP,
	0 VLR_SDO_CRED,
	0 VLR_SDO_DEB,
	0 PERC_REPASSE_FR_LISTA,
	'A' TP_DIA_PGTO,
	0 NR_DIA_PGTO,
	'N' VENDAFORALISTA,
	0 NR_DIA_PERM_FAT,
	'N' UTILIZA_VALOR_REPASSE_ONLINE,
	0 PERC_DESCONTO_MINIMO,
	1 FLAG_POSSUI_REPOSICAO_PRODUTOS,
	0 TP_DESC_PRECO
from
	PDV_VD_IT_VD_PBM 
	INNER JOIN PDV_VD ON
	PDV_VD_IT_VD_PBM.CD_EMP = PDV_VD.CD_EMP AND
	PDV_VD_IT_VD_PBM.CD_FILIAL = PDV_VD.CD_FILIAL AND
	PDV_VD_IT_VD_PBM.CD_VD = PDV_VD.CD_VD 
	INNER JOIN RC_CLI_CONV_VINC_RC_CLI ON
	PDV_VD_IT_VD_PBM.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP AND
	PDV_VD_IT_VD_PBM.CD_CLI_PBM = RC_CLI_CONV_VINC_RC_CLI.CD_CLI 
	LEFT OUTER JOIN RC_CLI_CONV_PBM ON
	RC_CLI_CONV_VINC_RC_CLI.CD_EMP = RC_CLI_CONV_PBM.CD_EMP AND
	RC_CLI_CONV_VINC_RC_CLI.CD_CONV = RC_CLI_CONV_PBM.CD_CONV 
WHERE
	PDV_VD.DT_VD >= CONVERT(CHAR(10),GETDATE() -200,112)
	AND RC_CLI_CONV_PBM.CD_CONV  IS NULL
--Inserindo produtos
INSERT INTO EST_PROD_RC_CLI_CONV (CD_EMP,CD_CONV,CD_PROD,PERC_DESC_PROD,REPOSICAO,PERC_DESC_REPO,PERC_REPASSE,QT_SDO)
select
	DISTINCT Rs.CD_EMP,
	Rs.CD_CONV,
	Rs.CD_PROD,
	0	PERC_DESC_PROD,
	'N' REPOSICAO,
	0	PERC_DESC_REPO,
	0	PERC_REPASSE,
	0 QT_SDO
from
	(
	SELECT
		RC_CLI_CONV_VINC_RC_CLI.CD_EMP,
		RC_CLI_CONV_VINC_RC_CLI.CD_CONV,	
		PDV_VD_IT.CD_PROD 
	 FROM
		PDV_VD_IT_VD_PBM INNER JOIN PDV_VD ON
		PDV_VD_IT_VD_PBM.CD_EMP = PDV_VD.CD_EMP AND
		PDV_VD_IT_VD_PBM.CD_FILIAL = PDV_VD.CD_FILIAL AND
		PDV_VD_IT_VD_PBM.CD_VD = PDV_VD.CD_VD 
		INNER JOIN PDV_VD_IT ON
		PDV_VD_IT_VD_PBM.CD_EMP = PDV_VD_IT.CD_EMP AND
		PDV_VD_IT_VD_PBM.CD_FILIAL = PDV_VD_IT.CD_FILIAL AND
		PDV_VD_IT_VD_PBM.CD_VD = PDV_VD_IT.CD_VD AND
		PDV_VD_IT_VD_PBM.CD_IT = PDV_VD_IT.CD_IT
		INNER JOIN RC_CLI_CONV_VINC_RC_CLI ON
		PDV_VD_IT_VD_PBM.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP AND
		PDV_VD_IT_VD_PBM.CD_CLI_PBM = RC_CLI_CONV_VINC_RC_CLI.CD_CLI
	WHERE
		PDV_VD.DT_VD >= CONVERT(CHAR(10),GETDATE() -200 ,112)) Rs
	LEFT OUTER JOIN EST_PROD_RC_CLI_CONV ON
	Rs.CD_EMP = EST_PROD_RC_CLI_CONV.CD_EMP AND
	Rs.CD_PROD = EST_PROD_RC_CLI_CONV.CD_PROD AND
	Rs.CD_CONV = EST_PROD_RC_CLI_CONV.CD_CONV
	INNER JOIN EST_PROD ON
	Rs.CD_EMP = EST_PROD.CD_EMP AND
	Rs.CD_PROD = EST_PROD.CD_PROD 
WHERE
	EST_PROD_RC_CLI_CONV.CD_CONV  IS NULL
	AND EST_PROD.CTR_PMC =1

UPDATE
	EST_PROD_RC_CLI_CONV
SET 	
	REPOSICAO =	'N'
WHERE
	REPOSICAO='0'

GO


