IF EXISTS ( SELECT * FROM sys.procedures WHERE name = 'P_PDV_VD_ANALISE' )
BEGIN
	DROP PROCEDURE P_PDV_VD_ANALISE
END
GO

/****** Object:  StoredProcedure [dbo].[P_PDV_VD_ANALISE]    Script Date: 18/07/2023 15:08:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- EXEC P_PDV_VD_ANALISE 1,'6','2023-07-26','2023-07-26'
-- SELECT * FROM PDV_VD WHERE cd_emp=1 and cd_filial=6 and dt_vd = '20230726'
-- SELECT vlr_DESC_VERBA,VLR_DESC,PERC_DESC_MIN,* FROM PDV_VD_IT WHERE cd_emp=1 and cd_filial=6 and cd_vd IN(123946731,123946739)


CREATE PROCEDURE [dbo].[P_PDV_VD_ANALISE] (@CD_EMP INT, 
								    @CD_FILIAL VARCHAR(MAX),
									@DT_INI DATETIME, 
									@DT_FIM DATETIME)
AS
SET NOCOUNT ON;
CREATE TABLE #RSVENDASORCAMENTO (CD_EMP INT,
								 CD_FILIAL INT,
								 CD_ORC INT,
								 CD_CLI INT,
								 DT_EMI DATETIME,
								 NM_CLI VARCHAR(200),
								 NR_ECF INT,
								 CD_CX INT,
								 CD_IT INT,
								 CD_PROD INT,
								 DS_PROD VARCHAR(200),
								 NM_FABRIC VARCHAR(200),
								 DS_ARV_MERC_CATEG VARCHAR(200),
								 DS_ARV_MERC_DEPTO VARCHAR(200),
								 DS_ARV_MERC_LINHA VARCHAR(200),
								 DS_ARV_MERC_FAMILIA VARCHAR(200),
								 CD_VEND INT,
								 NM_FUN_VEND VARCHAR(200),
								 DS_CARGO_VEND VARCHAR(200),
								 CD_VEND_LIB INT,
								 NM_FUN_LIB VARCHAR(200),
								 DS_CARGO_LIB VARCHAR(200),
								 CD_VEND_VERBA INT,
								 NM_FUN_VERBA VARCHAR(200),
								 DS_CARGO_VERBA VARCHAR(200),
								 QT_IT MONEY,
								 VLR_TOT_BRUTO MONEY,
								 VLR_TOT_IT MONEY,
								 VLR_DESC MONEY,
								 PERC_DESC MONEY,
								 VLR_DESC_VERBA MONEY,
								 PERC_DESC_VERBA MONEY,
								 PERC_DESC_MIN MONEY,
								 NM_CONCORRENTE VARCHAR(200),
								 VLR_ITEM_CONCORRENTE MONEY,
								 PERC_DESC_CONCORRENTE MONEY)
DECLARE @SCMD VARCHAR(5000)
CREATE TABLE #RSFILIAL (CD_EMP INT,
						CD_FILIAL INT)
SET @SCMD ='INSERT INTO #RSFILIAL 
			SELECT 
				CD_EMP,
				CD_FILIAL
			FROM 
				PRC_FILIAL 
			WHERE
				CD_EMP= ' + CONVERT(VARCHAR, @CD_EMP) + '
				AND STS_FILIAL =0
				AND CD_FILIAL IN ( ' + @CD_FILIAL + ')'
EXEC (@SCMD);
INSERT INTO #RSVENDASORCAMENTO

SELECT
	DISTINCT
	V.CD_EMP,
	V.CD_FILIAL, 
	ISNULL(EST_ORC_CPL.CD_ORC,0) AS CD_ORC,
	ISNULL(V.CD_CLI,EST_ORC.CD_CLI) AS CD_CLI,
	ISNULL(V.DT_VD,EST_ORC.DT_EMI) AS DT_EMI,
	NM_FANT,
	ISNULL(V.NR_ECF,0) AS NR_ECF,
	ISNULL(V.CD_CX,0) AS CD_CX,
	ISNULL(I.CD_IT,EST_ORC_CPL.CD_IT) AS CD_IT,
	ISNULL(I.CD_PROD,EST_ORC_CPL.CD_PROD) AS CD_PROD,
	V_EST_PROD_ARV_MERCADOLOGICA.DS_PROD,
	V_EST_PROD_ARV_MERCADOLOGICA.NM_FABRIC,
	V_EST_PROD_ARV_MERCADOLOGICA.DS_ARV_MERC_CATEG,
	V_EST_PROD_ARV_MERCADOLOGICA.DS_ARV_MERC_DEPTO,
	V_EST_PROD_ARV_MERCADOLOGICA.DS_ARV_MERC_LINHA,
	V_EST_PROD_ARV_MERCADOLOGICA.DS_ARV_MERC_FAMILIA,
	ISNULL(I.CD_VEND,EST_ORC_CPL.CD_VEND) AS CD_VEND,
	ISNULL(FP_FUNC.NM_FUN,'') AS NM_FUN_VEND,
	ISNULL(FP_CARGO.DS_CARGO,'') AS DS_CARGO,
	ISNULL(I.CD_VEND_LIB,EST_ORC_CPL.CD_VEND_LIB) AS CD_VEND_LIB,
	ISNULL(FUNCLIB.NM_FUN,'') AS NM_FUN_LIB,
	ISNULL(CARGOLIB.DS_CARGO,'') AS DS_CARGO_LIB,
	ISNULL(I.CD_VEND_VERBA,EST_ORC_CPL.CD_VEND_VERBA) AS CD_VEND_VERBA,
	ISNULL(FUNCVERBA.NM_FUN,'') AS NM_FUN_VERBA,
	ISNULL(CARGOVERBA.DS_CARGO,'') AS DS_CARGO_VERBA,
	ISNULL(I.QT_IT,EST_ORC_CPL.QT) AS QT,
	ISNULL((I.VLR_IT*i.qt_it),(EST_ORC_CPL.VLR_IT * EST_ORC_CPL.QT)) AS VLR_TOT_BRUTO, --- VALOR BRUTO
	ISNULL(I.TOT_IT,EST_ORC_CPL.VLR_TOT_IT) AS VLR_TOT_IT,
	ISNULL((ISNULL(I.VLR_DESC,0) - ISNULL(I.VLR_DESC_VERBA,0)),EST_ORC_CPL.VLR_DESC) AS VLR_DESC,
	ISNULL(I.PERC_DESC_VERBA,EST_ORC_CPL.PERC_DESC) AS PERC_DESC,
	ISNULL(I.VLR_DESC_VERBA,EST_ORC_CPL.VLR_DESC_VERBA) AS VLR_DESC_VERBA,
	ISNULL(I.PERC_DESC_VERBA,EST_ORC_CPL.PERC_DESC_VERBA) AS PERC_DESC_VERBA,
	ISNULL(I.PERC_DESC_MIN,EST_ORC_CPL.PERC_DESC_MIN) AS PERC_DESC_MIN,
	ISNULL(CONCOR.DS_CONCOR,'') AS DS_CONCOR,
	ISNULL(VDCONC.VLR_CONCOR,'') AS VLR_CONCOR,
	ISNULL(VDCONC.PERC_DESC,'') AS PERC_DESC
	
FROM PDV_VD V INNER JOIN PDV_VD_IT I ON
	V.CD_EMP=I.CD_EMP AND
	V.CD_FILIAL=I.CD_FILIAL AND
	V.CD_VD=I.CD_VD

	LEFT JOIN EST_ORC_CPL ON
	EST_ORC_CPL.CD_EMP = V.CD_EMP AND
	EST_ORC_CPL.CD_FILIAL=V.CD_FILIAL AND
	EST_ORC_CPL.CD_ORC=V.NR_ORC

	LEFT JOIN EST_ORC ON
	EST_ORC_CPL.CD_EMP = EST_ORC.CD_EMP AND
	EST_ORC_CPL.CD_FILIAL = EST_ORC.CD_FILIAL AND
	EST_ORC_CPL.CD_ORC = EST_ORC.CD_ORC 

	INNER JOIN V_EST_PROD_ARV_MERCADOLOGICA ON
	V_EST_PROD_ARV_MERCADOLOGICA.CD_EMP = i.CD_EMP AND
	V_EST_PROD_ARV_MERCADOLOGICA.CD_PROD = i.CD_PROD
	
	INNER JOIN RC_CLI ON
	V.CD_EMP = RC_CLI.CD_EMP AND
	V.CD_CLI = RC_CLI.CD_CLI
	
	INNER JOIN FP_FUNC ON
	I.CD_EMP = FP_FUNC.CD_EMP AND
	I.CD_VEND = FP_FUNC.CD_FUN 
	
	INNER JOIN FP_CARGO ON 
	FP_FUNC.CD_EMP = FP_CARGO.CD_EMP AND
	FP_FUNC.CD_CARGO = FP_CARGO.CD_CARGO
	
	INNER JOIN #RSFILIAL F ON
	V.CD_EMP = F.CD_EMP AND
	V.CD_FILIAL = F.CD_FILIAL 

	LEFT JOIN FP_FUNC FUNCLIB ON
	I.CD_EMP = FUNCLIB.CD_EMP AND
	I.CD_VEND_LIB = FUNCLIB.CD_FUN

	LEFT JOIN FP_CARGO CARGOLIB ON 
	CARGOLIB.CD_EMP = FUNCLIB.CD_EMP AND
	CARGOLIB.CD_CARGO = FUNCLIB.CD_CARGO

	INNER JOIN FP_FUNC  FUNCVERBA ON
	I.CD_EMP = FUNCVERBA.CD_EMP AND
	I.CD_VEND_VERBA = FUNCVERBA.CD_FUN

	INNER JOIN FP_CARGO CARGOVERBA ON 
	CARGOVERBA.CD_EMP = FUNCVERBA.CD_EMP AND
	CARGOVERBA.CD_CARGO = FUNCVERBA.CD_CARGO

	LEFT JOIN PDV_VD_IT_CONCOR VDCONC ON
	VDCONC.CD_EMP=I.CD_EMP AND
	VDCONC.CD_FILIAL=I.CD_FILIAL AND
	VDCONC.CD_VD=I.CD_VD AND
	VDCONC.CD_PROD=I.CD_PROD AND
	VDCONC.CD_IT=I.CD_IT

	LEFT JOIN GLB_CONCORRENCIA CONCOR ON
	CONCOR.CD_EMP=VDCONC.CD_EMP AND
	CONCOR.CD_CONCOR=VDCONC.CD_CONCOR

WHERE I.CD_EMP=1
	AND I.DT_VD BETWEEN @DT_INI AND @DT_FIM
	AND I.VLR_DESC_VERBA > 0
	AND V.ST_VD=0
	AND I.ST_IT=1

-------- QUANDO NÃO EXISTIR VINCULO COM O CUPOM/DIRETO ORÇAMENTO VERSÃO PDV SEM TRATAMENTO ------------

UPDATE
	O
SET 
	O.PERC_DESC_VERBA = (VLR_DESC_VERBA /(VLR_TOT_BRUTO/QT_IT)) * 100
FROM
	#RSVENDASORCAMENTO O INNER JOIN PDV_VD_EST_ORC ON
	O.CD_EMP = PDV_VD_EST_ORC.CD_EMP AND
	O.CD_FILIAL = PDV_VD_EST_ORC.CD_FILIAL AND 
	O.CD_ORC = PDV_VD_EST_ORC.CD_ORC 

	INNER JOIN EST_ORC ON
	EST_ORC.CD_EMP = PDV_VD_EST_ORC.CD_EMP AND
	EST_ORC.CD_FILIAL = PDV_VD_EST_ORC.CD_FILIAL AND 
	EST_ORC.CD_ORC = PDV_VD_EST_ORC.CD_ORC 
WHERE
	O.VLR_DESC_VERBA > 0
	AND O.PERC_DESC_VERBA = 0
	AND NOT EXISTS(
		SELECT 
			VV.CD_EMP 
		FROM PDV_VD VV INNER JOIN PDV_VD_IT II ON
			VV.CD_EMP=II.CD_EMP AND
			VV.CD_FILIAL=II.CD_FILIAL AND
			VV.CD_VD=II.CD_VD

			INNER JOIN PDV_VD_IT_CONCOR VC ON
			VC.CD_EMP=II.CD_EMP AND
			VC.CD_FILIAL=II.CD_FILIAL AND
			VC.CD_VD=II.CD_VD AND
			VC.CD_PROD=II.CD_PROD AND
			VC.CD_IT=II.CD_IT

		WHERE O.CD_EMP = VV.CD_EMP
			AND O.CD_FILIAL=VV.CD_FILIAL
			AND O.CD_ORC = VV.NR_ORC
			AND VV.ST_VD=0
	)

UPDATE
	O
SET 
	O.VLR_DESC_VERBA = ((O.VLR_TOT_BRUTO/O.QT_IT)*(O.PERC_DESC_VERBA/100)) 
FROM
	#RSVENDASORCAMENTO O INNER JOIN PDV_VD_EST_ORC ON
	O.CD_EMP = PDV_VD_EST_ORC.CD_EMP AND
	O.CD_FILIAL = PDV_VD_EST_ORC.CD_FILIAL AND 
	O.CD_ORC = PDV_VD_EST_ORC.CD_ORC 

	INNER JOIN EST_ORC ON
	EST_ORC.CD_EMP = PDV_VD_EST_ORC.CD_EMP AND
	EST_ORC.CD_FILIAL = PDV_VD_EST_ORC.CD_FILIAL AND 
	EST_ORC.CD_ORC = PDV_VD_EST_ORC.CD_ORC 
WHERE
	O.VLR_DESC_VERBA = 0
	AND O.PERC_DESC_VERBA > 0
	AND NOT EXISTS(
		SELECT 
			VV.CD_EMP 
		FROM PDV_VD VV INNER JOIN PDV_VD_IT II ON
			VV.CD_EMP=II.CD_EMP AND
			VV.CD_FILIAL=II.CD_FILIAL AND
			VV.CD_VD=II.CD_VD

			INNER JOIN PDV_VD_IT_CONCOR VC ON
			VC.CD_EMP=II.CD_EMP AND
			VC.CD_FILIAL=II.CD_FILIAL AND
			VC.CD_VD=II.CD_VD AND
			VC.CD_PROD=II.CD_PROD AND
			VC.CD_IT=II.CD_IT

		WHERE O.CD_EMP = VV.CD_EMP
			AND O.CD_FILIAL=VV.CD_FILIAL
			AND O.CD_ORC = VV.NR_ORC
			AND VV.ST_VD=0
	)

UPDATE
	O
SET 
	O.VLR_DESC = CASE WHEN (O.VLR_DESC - O.VLR_DESC_VERBA) < 0 THEN 0 ELSE (O.VLR_DESC - O.VLR_DESC_VERBA) END
FROM
	#RSVENDASORCAMENTO O INNER JOIN PDV_VD_EST_ORC ON
	O.CD_EMP = PDV_VD_EST_ORC.CD_EMP AND
	O.CD_FILIAL = PDV_VD_EST_ORC.CD_FILIAL AND 
	O.CD_ORC = PDV_VD_EST_ORC.CD_ORC 

	INNER JOIN EST_ORC ON
	EST_ORC.CD_EMP = PDV_VD_EST_ORC.CD_EMP AND
	EST_ORC.CD_FILIAL = PDV_VD_EST_ORC.CD_FILIAL AND 
	EST_ORC.CD_ORC = PDV_VD_EST_ORC.CD_ORC 

WHERE
	O.VLR_DESC_VERBA > 0
	AND NOT EXISTS(
		SELECT 
			VV.CD_EMP 
		FROM PDV_VD VV INNER JOIN PDV_VD_IT II ON
			VV.CD_EMP=II.CD_EMP AND
			VV.CD_FILIAL=II.CD_FILIAL AND
			VV.CD_VD=II.CD_VD

			INNER JOIN PDV_VD_IT_CONCOR VC ON
			VC.CD_EMP=II.CD_EMP AND
			VC.CD_FILIAL=II.CD_FILIAL AND
			VC.CD_VD=II.CD_VD AND
			VC.CD_PROD=II.CD_PROD AND
			VC.CD_IT=II.CD_IT

		WHERE O.CD_EMP = VV.CD_EMP
			AND O.CD_FILIAL=VV.CD_FILIAL
			AND O.CD_ORC = VV.NR_ORC
			AND VV.ST_VD=0
	)

UPDATE
	O
SET 
	O.NM_FUN_LIB = FP_FUNC.NM_FUN,
	O.DS_CARGO_LIB=DS_CARGO
FROM
	#RSVENDASORCAMENTO O INNER JOIN FP_FUNC ON
	O.CD_EMP = FP_FUNC.CD_EMP AND
	O.CD_VEND_LIB = FP_FUNC.CD_FUN

	INNER JOIN FP_CARGO ON 
	FP_FUNC.CD_EMP = FP_CARGO.CD_EMP AND
	FP_FUNC.CD_CARGO = FP_CARGO.CD_CARGO

WHERE
	O.CD_VEND_LIB > 0
	AND NOT EXISTS(
		SELECT 
			VV.CD_EMP 
		FROM PDV_VD VV INNER JOIN PDV_VD_IT II ON
			VV.CD_EMP=II.CD_EMP AND
			VV.CD_FILIAL=II.CD_FILIAL AND
			VV.CD_VD=II.CD_VD

			INNER JOIN PDV_VD_IT_CONCOR VC ON
			VC.CD_EMP=II.CD_EMP AND
			VC.CD_FILIAL=II.CD_FILIAL AND
			VC.CD_VD=II.CD_VD AND
			VC.CD_PROD=II.CD_PROD AND
			VC.CD_IT=II.CD_IT

		WHERE O.CD_EMP = VV.CD_EMP
			AND O.CD_FILIAL=VV.CD_FILIAL
			AND O.CD_ORC = VV.NR_ORC
			AND VV.ST_VD=0
	)

UPDATE
	O
SET 
	O.NM_FUN_VERBA = FP_FUNC.NM_FUN,
	O.DS_CARGO_VERBA=DS_CARGO
FROM
	#RSVENDASORCAMENTO O INNER JOIN FP_FUNC ON
	O.CD_EMP = FP_FUNC.CD_EMP AND
	O.CD_VEND_VERBA = FP_FUNC.CD_FUN
	INNER JOIN FP_CARGO ON 
	FP_FUNC.CD_EMP = FP_CARGO.CD_EMP AND
	FP_FUNC.CD_CARGO = FP_CARGO.CD_CARGO
WHERE
	O.CD_VEND_VERBA > 0
	AND (O.VLR_DESC_VERBA + O.PERC_DESC_VERBA) > 0
	AND NOT EXISTS(
		SELECT 
			VV.CD_EMP 
		FROM PDV_VD VV INNER JOIN PDV_VD_IT II ON
			VV.CD_EMP=II.CD_EMP AND
			VV.CD_FILIAL=II.CD_FILIAL AND
			VV.CD_VD=II.CD_VD

			INNER JOIN PDV_VD_IT_CONCOR VC ON
			VC.CD_EMP=II.CD_EMP AND
			VC.CD_FILIAL=II.CD_FILIAL AND
			VC.CD_VD=II.CD_VD AND
			VC.CD_PROD=II.CD_PROD AND
			VC.CD_IT=II.CD_IT

		WHERE O.CD_EMP = VV.CD_EMP
			AND O.CD_FILIAL=VV.CD_FILIAL
			AND O.CD_ORC = VV.NR_ORC
			AND VV.ST_VD=0
	)
UPDATE
	O
SET 
	O.NR_ECF = PDV_VD.NR_ECF,
	O.CD_CX=PDV_VD.CD_CX
FROM
	#RSVENDASORCAMENTO O INNER JOIN PDV_VD_EST_ORC ON
	O.CD_EMP = PDV_VD_EST_ORC.CD_EMP AND
	O.CD_FILIAL = PDV_VD_EST_ORC.CD_FILIAL AND 
	O.CD_ORC = PDV_VD_EST_ORC.CD_ORC 
	INNER JOIN PDV_VD ON
	PDV_VD_EST_ORC.CD_EMP = PDV_VD.CD_EMP AND
	PDV_VD_EST_ORC.CD_FILIAL = PDV_VD.CD_FILIAL AND
	PDV_VD_EST_ORC.CD_VD = PDV_VD.CD_VD 

WHERE	
	O.CD_VEND_VERBA > 0
	AND NOT EXISTS(
		SELECT 
			VV.CD_EMP 
		FROM PDV_VD VV INNER JOIN PDV_VD_IT II ON
			VV.CD_EMP=II.CD_EMP AND
			VV.CD_FILIAL=II.CD_FILIAL AND
			VV.CD_VD=II.CD_VD

			INNER JOIN PDV_VD_IT_CONCOR VC ON
			VC.CD_EMP=II.CD_EMP AND
			VC.CD_FILIAL=II.CD_FILIAL AND
			VC.CD_VD=II.CD_VD AND
			VC.CD_PROD=II.CD_PROD AND
			VC.CD_IT=II.CD_IT

		WHERE O.CD_EMP = VV.CD_EMP
			AND O.CD_FILIAL=VV.CD_FILIAL
			AND O.CD_ORC = VV.NR_ORC
			AND VV.ST_VD=0
	)

UPDATE
	O
SET 
	O.NM_CONCORRENTE = GLB_CONCORRENCIA.DS_CONCOR,
	O.VLR_ITEM_CONCORRENTE=EST_ORC_CPL_CONCOR.VLR_CONCOR,
	O.PERC_DESC_CONCORRENTE=EST_ORC_CPL_CONCOR.PERC_DESC
FROM
	#RSVENDASORCAMENTO O INNER JOIN EST_ORC_CPL_CONCOR ON
	O.CD_EMP = EST_ORC_CPL_CONCOR.CD_EMP AND
	O.CD_FILIAL = EST_ORC_CPL_CONCOR.CD_FILIAL AND 
	O.CD_ORC = EST_ORC_CPL_CONCOR.NR_ORC AND
	O.CD_IT = EST_ORC_CPL_CONCOR.CD_IT AND
	O.CD_PROD = EST_ORC_CPL_CONCOR.CD_PROD 

	INNER JOIN GLB_CONCORRENCIA ON 
	EST_ORC_CPL_CONCOR.CD_EMP = GLB_CONCORRENCIA.CD_EMP AND
	EST_ORC_CPL_CONCOR.CD_CONCOR = GLB_CONCORRENCIA.CD_CONCOR

WHERE	
	O.CD_VEND_VERBA > 0
	AND NOT EXISTS(
		SELECT 
			VV.CD_EMP 
		FROM PDV_VD VV INNER JOIN PDV_VD_IT II ON
			VV.CD_EMP=II.CD_EMP AND
			VV.CD_FILIAL=II.CD_FILIAL AND
			VV.CD_VD=II.CD_VD

			INNER JOIN PDV_VD_IT_CONCOR VC ON
			VC.CD_EMP=II.CD_EMP AND
			VC.CD_FILIAL=II.CD_FILIAL AND
			VC.CD_VD=II.CD_VD AND
			VC.CD_PROD=II.CD_PROD AND
			VC.CD_IT=II.CD_IT

		WHERE O.CD_EMP = VV.CD_EMP
			AND O.CD_FILIAL=VV.CD_FILIAL
			AND O.CD_ORC = VV.NR_ORC
			AND VV.ST_VD=0
	)

------------- RETONO DA CONSULTA ------------

SELECT
	*
FROM
	#RSVENDASORCAMENTO
ORDER BY
	CD_FILIAL,
	CD_ORC

DROP TABLE #RSVENDASORCAMENTO;
DROP TABLE #RSFILIAL;

GO


