/****** Object:  View [dbo].[V_ANALISE_VENDAS_VERSUS_CONTAS_PAGAR]    Script Date: 07/04/2013 16:57:47 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_ANALISE_VENDAS_VERSUS_CONTAS_PAGAR]'))
DROP VIEW [dbo].[V_ANALISE_VENDAS_VERSUS_CONTAS_PAGAR]
GO
--SELECT * FROM V_ANALISE_VENDAS_VERSUS_CONTAS_PAGAR
CREATE VIEW V_ANALISE_VENDAS_VERSUS_CONTAS_PAGAR AS
SELECT
	CD_FILIAL,
	 ANO,
	 MES,
	 SUM(VLR_META_VENDA) AS VLR_META_VENDA,
	 SUM(VLR_REALIZADO) AS VLR_REALIZADO,
	 SUM(VLR_DP_CMV) AS VLR_DP_CMV,
	 SUM(VLR_PEDIDO_PENDENTE) AS VLR_PEDIDO_PENDENTE,
	 SUM(VLR_DP_CMV_NAO) AS VLR_DP_CMV_NAO,
	 SUM(VLR_DP_CMV_NAO_PREVISAO) AS VLR_DP_CMV_NAO_PREVISAO
FROM
(SELECT
	 PG_CRED.CD_FILIAL,
	 YEAR(DT_VENCTO) AS ANO,
	 MONTH(DT_VENCTO) AS MES,
	 0 AS VLR_META_VENDA,
	 0 AS VLR_REALIZADO,
	 ((VLR_DP+VLR_JUROS+VLR_DESP_OUTR)-(VLR_DESC+VLR_DEVOLUCAO+ISNULL(V_PG_CRED_IMPOSTO.VLR_IMP,0))) AS VLR_DP_CMV,
	 0 AS VLR_DP_CMV_NAO,
	 0 AS VLR_DP_CMV_NAO_PREVISAO,
	 0 AS VLR_PEDIDO_PENDENTE
FROM
	PG_CRED INNER JOIN PG_FORN ON
	PG_CRED.CD_EMP = PG_FORN.CD_EMP AND
	PG_CRED.CD_FORN = PG_FORN.CD_FORN
	INNER JOIN PG_TP_FORN ON
	PG_FORN.CD_TP_FORN = PG_TP_FORN.CD_TP_FORN
    LEFT OUTER JOIN V_PG_CRED_IMPOSTO ON
    PG_CRED.CD_EMP = V_PG_CRED_IMPOSTO.CD_EMP AND 
    PG_CRED.CD_FILIAL = V_PG_CRED_IMPOSTO.CD_FILIAL AND 
    PG_CRED.CD_PG_CRED = V_PG_CRED_IMPOSTO.CD_PG_CRED 
WHERE
	PG_TP_FORN.TP_FORN=0
	AND PG_CRED.STS_DP<>3
	AND PG_CRED.TP_DP <> 5
UNION ALL
SELECT	
	 PG_CRED.CD_FILIAL,
	 YEAR(DT_VENCTO) AS ANO,
	 MONTH(DT_VENCTO) AS MES,
	 0 AS VLR_META_VENDA,
	 0 AS VLR_REALIZADO,
	 0 AS VLR_DP_CMV, 
	 ((VLR_DP+VLR_JUROS+VLR_DESP_OUTR)-(VLR_DESC+VLR_DEVOLUCAO+ISNULL(V_PG_CRED_IMPOSTO.VLR_IMP,0))) AS VLR_DP_CMV_NAO,
	 0 AS VLR_DP_CMV_NAO_PREVISAO,
	 0 AS VLR_PEDIDO_PENDENTE
FROM
	PG_CRED INNER JOIN PG_FORN ON
	PG_CRED.CD_EMP = PG_FORN.CD_EMP AND
	PG_CRED.CD_FORN = PG_FORN.CD_FORN
	INNER JOIN PG_TP_FORN ON
	PG_FORN.CD_TP_FORN = PG_TP_FORN.CD_TP_FORN
    LEFT OUTER JOIN V_PG_CRED_IMPOSTO ON
    PG_CRED.CD_EMP = V_PG_CRED_IMPOSTO.CD_EMP AND 
    PG_CRED.CD_FILIAL = V_PG_CRED_IMPOSTO.CD_FILIAL AND 
    PG_CRED.CD_PG_CRED = V_PG_CRED_IMPOSTO.CD_PG_CRED 
WHERE
	PG_TP_FORN.TP_FORN<>0
	AND PG_CRED.STS_DP<>3
UNION ALL
SELECT	
	 PG_CRED.CD_FILIAL,
	 YEAR(DT_VENCTO) AS ANO,
	 MONTH(DT_VENCTO) AS MES,
	 0 AS VLR_META_VENDA,
	 0 AS VLR_REALIZADO,
	 0 AS VLR_DP_CMV, 
	 0 AS VLR_DP_CMV_NAO,
	((VLR_DP+VLR_JUROS+VLR_DESP_OUTR)-(VLR_DESC+VLR_DEVOLUCAO+ISNULL(V_PG_CRED_IMPOSTO.VLR_IMP,0))) AS VLR_DP_CMV_NAO_PREVISAO,
	 0 AS VLR_PEDIDO_PENDENTE
FROM
	PG_CRED INNER JOIN PG_FORN ON
	PG_CRED.CD_EMP = PG_FORN.CD_EMP AND
	PG_CRED.CD_FORN = PG_FORN.CD_FORN
	INNER JOIN PG_TP_FORN ON
	PG_FORN.CD_TP_FORN = PG_TP_FORN.CD_TP_FORN
    LEFT OUTER JOIN V_PG_CRED_IMPOSTO ON
    PG_CRED.CD_EMP = V_PG_CRED_IMPOSTO.CD_EMP AND 
    PG_CRED.CD_FILIAL = V_PG_CRED_IMPOSTO.CD_FILIAL AND 
    PG_CRED.CD_PG_CRED = V_PG_CRED_IMPOSTO.CD_PG_CRED 
WHERE
	PG_CRED.STS_DP<>3
	AND PG_CRED.TP_DP=5
UNION ALL
SELECT
	P.CD_FILIAL,
	YEAR(DATEADD(DAY,ISNULL(C.DIAS_VENCTO,20), ISNULL(P.DT_FATURAMENTO,P.DT_EMI) )) ANO,
	MONTH(DATEADD(DAY,ISNULL(C.DIAS_VENCTO,20), ISNULL(P.DT_FATURAMENTO,P.DT_EMI))) MES,
	0 AS VLR_META_VENDA,
	0 AS VLR_REALIZADO,
	0 AS VLR_DP_CMV,
	0 AS VLR_DP_CMV_NAO,
	0 AS VLR_DP_CMV_NAO_PREVISAO,
	P.VLR_PED / CASE WHEN ISNULL(c.QT_PARC,1)=0 THEN 1 ELSE ISNULL(c.QT_PARC,1) END  VLR_PEDIDO_PENDENTE
FROM EST_PED_CP P LEFT OUTER JOIN V_GLB_COND_PGTO_CPL C ON  
P.CD_COND_PGTO = C.CD_COND_PGTO
INNER JOIN EST_PED_CP_PENDENCIA pend ON  
P.CD_EMP  = pend.CD_EMP AND 
P.CD_FILIAL  = pend.CD_FILIAL AND 
P.CD_PED  = pend.CD_PED
UNION ALL
SELECT
	CD_FILIAL,
	ANO_META,
	MES_META,
	VLR_VD AS VLR_META_VENDA,
	0 AS VLR_REALIZADO,
	0 AS VLR_DP_CMV,
	0 AS VLR_DP_CMV_NAO,
	0 AS VLR_DP_CMV_NAO_PREVISAO,
	0 AS VLR_PEDIDO_PENDENTE
FROM
	V_EST_META_FILIAL_DIARIO_TOTAL
UNION ALL
SELECT
	Filial,
	AnoMovimento,
	MesMovimento ,
	0 AS VLR_META_VENDA,
	VlrVdLiquida AS VLR_REALIZADO,
	0 AS VLR_DP_CMV,
	0 AS VLR_DP_CMV_NAO,
	0 AS VLR_DP_CMV_NAO_PREVISAO,
	0 AS VLR_PEDIDO_PENDENTE
FROM
	V_RELATORIO_MOVIMENTO_VENDAS
) RS
GROUP BY
	CD_FILIAL,
	ANO,
	MES
	