/****** Object:  View [dbo].[V_ANALISE_CONTAS_PAGAR_COMPRADOR ]    Script Date: 07/04/2013 16:57:47 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_ANALISE_CONTAS_PAGAR_COMPRADOR ]'))
DROP VIEW [dbo].[V_ANALISE_CONTAS_PAGAR_COMPRADOR ]
GO
/*
SELECT 
	CD_EMP,
	CD_FILIAL, 
	ANO, 
	MES, 
	CD_COMPRADOR, 
	NM_COMPRADOR,
	DS_TP,
	VLR_DUPLICATA,
	VLR_DESC,
	VLR_JUROS,
	VLR_DESP_OUTR,
	VLR_DEVOLUCAO,
	VLR_DUPLICATA_LIQUIDO,
	TP_FORN,
	CLASSIFICACAO,
	RZ_FORN
FROM 
	V_ANALISE_CONTAS_PAGAR_COMPRADOR  
WHERE 
	CD_EMP =1 
	AND CD_FILIAL = 12 
	AND ANO=2019 
	AND MES = 12
*/
CREATE VIEW V_ANALISE_CONTAS_PAGAR_COMPRADOR 
WITH ENCRYPTION
AS
SELECT
	CPL.CD_EMP,
	CPL.CD_FILIAL,
	YEAR(cpl.DT_VENCTO) AS ANO,
	MONTH(cpl.DT_VENCTO) AS MES,
	e.CD_COMPRADOR,
	EST_COMPRADOR.NM_COMPRADOR,
	PG_FORN.RZ_FORN,
	PG_FORN.NM_FANT,
	PG_TP_FORN.DS_TP, 
	sum(cpl.VLR_DP) AS VLR_DUPLICATA,
	sum(cpl.VLR_DESC) AS VLR_DESC,
	sum(cpl.VLR_JUROS) AS VLR_JUROS,
	sum(cpl.VLR_DESP_OUTR) AS VLR_DESP_OUTR,
	sum(cpl.VLR_DEVOLUCAO) AS VLR_DEVOLUCAO,
	sum((cpl.VLR_DP + cpl.VLR_JUROS + cpl.VLR_DESP_OUTR)-(cpl.VLR_DESC+cpl.VLR_DEVOLUCAO)) AS VLR_DUPLICATA_LIQUIDO,
	PG_TP_FORN.TP_FORN,
	V_TP_FORN_CLASSIFICACAO.CLASSIFICACAO,
	CPL.CD_PROD
FROM
	EST_COMPRADOR_MOVIMENTACAO e INNER JOIN EST_COMPRADOR_MOVIMENTACAO_CPL cpl ON
	e.CD_CTR = cpl.CD_CTR
	INNER JOIN EST_COMPRADOR ON
	e.CD_EMP  = EST_COMPRADOR.CD_EMP AND 
	e.CD_COMPRADOR = EST_COMPRADOR.CD_COMPRADOR 
	INNER JOIN EST_PROD ON 
	cpl.CD_EMP = EST_PROD.CD_EMP AND
	cpl.CD_PROD = EST_PROD.CD_PROD 
	INNER JOIN PG_TP_FORN ON
	cpl.CD_TP_FORN = PG_TP_FORN.CD_TP_FORN
	INNER JOIN V_TP_FORN_CLASSIFICACAO ON
	PG_TP_FORN.TP_FORN = V_TP_FORN_CLASSIFICACAO.TP_FORN
	INNER JOIN PG_FORN ON
	cpl.CD_EMP = PG_FORN.CD_EMP AND
	cpl.CD_FORN = PG_FORN.CD_FORN
group by
	CPL.CD_EMP,
	CPL.CD_FILIAL,
	YEAR(cpl.DT_VENCTO),
	MONTH(cpl.DT_VENCTO),
	e.CD_COMPRADOR,
	EST_COMPRADOR.NM_COMPRADOR,
	PG_TP_FORN.TP_FORN,
	PG_TP_FORN.DS_TP,
	V_TP_FORN_CLASSIFICACAO.CLASSIFICACAO,
	PG_FORN.RZ_FORN,
	CPL.CD_PROD,
	PG_FORN.NM_FANT