/****** OBJECT:  VIEW [DBO].[V_ANALISE_CONTAS_PAGAR_COMPRADOR_ANALITICO]    SCRIPT DATE: 03/11/2022 16:57:47 ******/
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_ANALISE_CONTAS_PAGAR_COMPRADOR_ANALITICO]'))
	DROP VIEW [DBO].[V_ANALISE_CONTAS_PAGAR_COMPRADOR_ANALITICO]
GO
/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 20221123
'Job.................:  
'Nome................:  V_ANALISE_CONTAS_PAGAR_COMPRADOR_ANALITICO
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: 
'Dt Ult Alteracão....: 2022-11-23
'Alterado por........:  Eduardo Nunes - Marcos Barros
'Criado por..........: 
'=======================================================================================
FARMAGOI-33878 - SELECT	* FROM 	V_ANALISE_CONTAS_PAGAR_COMPRADOR_ANALITICO WHERE CD_EMP =1 	AND ANO=2019 AND MES = 12
FARMAGOI-33177 - Inclusão de coluna mês data de pagamento
*/
CREATE VIEW V_ANALISE_CONTAS_PAGAR_COMPRADOR_ANALITICO
WITH ENCRYPTION
AS
	SELECT
    	 CPL.CD_EMP
    	,CPL.CD_FILIAL
    	,YEAR(CPL.DT_VENCTO) AS ANO
    	,MONTH(CPL.DT_VENCTO) AS MES
    	,CPL.DT_EMIS
    	,CPL.DT_VENCTO
    	,E.CD_COMPRADOR
    	,EST_COMPRADOR.NM_COMPRADOR
    	,CPL.VLR_DP AS VLR_DUPLICATA
    	,CPL.VLR_DESC AS VLR_DESC
    	,CPL.VLR_JUROS AS VLR_JUROS
    	,CPL.VLR_DESP_OUTR AS VLR_DESP_OUTR
    	,CPL.VLR_DEVOLUCAO AS VLR_DEVOLUCAO
    	,(CPL.VLR_DP + CPL.VLR_JUROS + CPL.VLR_DESP_OUTR)-(CPL.VLR_DESC+CPL.VLR_DEVOLUCAO) AS VLR_DUPLICATA_LIQUIDO
    	,CPL.CD_PROD
    	,FAB.CD_FABRIC
    	,FAB.NM_FABRIC
    	,L.CD_ARV_MERC_LINHA
    	,L.DS_ARV_MERC_LINHA
    	,PG_CRED.NR_DP
    	,PG_CRED.CD_PG_CRED
    	,PG_CRED.STS_DP
    	,PG_CRED.PARC
		,PG_CRED.CD_FORN
    	,EST_PROD.DS_PROD
    	,CPL.CD_TP_FORN
    	,CPL.VLR_IMP
    	,0 AS VLR_VENDA
    	,'CONTAS A PAGAR' AS TIPO_TRANSACAO
    	,CPL.VLR_DUP_PGTO
    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 DBO.EST_PROD_EST_ARV_MERCADOLOGICA AS ARV ON	CPL.CD_EMP = ARV.CD_EMP
    				AND CPL.CD_PROD = ARV.CD_PROD
    	INNER JOIN DBO.EST_PROD_FABRIC AS FAB ON	EST_PROD.CD_FABRIC = FAB.CD_FABRIC
    	INNER JOIN DBO.EST_ARV_MERC_LINHA AS L ON	ARV.CD_EMP = L.CD_EMP
    				AND ARV.CD_ARV_MERC_LINHA = L.CD_ARV_MERC_LINHA
    	INNER JOIN PG_CRED ON	CPL.CD_EMP = PG_CRED.CD_EMP
    				AND CPL.CD_FILIAL = PG_CRED.CD_FILIAL
    				AND CPL.CD_PG_CRED = PG_CRED.CD_PG_CRED
    WHERE
    	CPL.TIPO_TRANSACAO=0
	UNION ALL
		SELECT
    	 P.CD_EMP
    	,P.CD_FILIAL
    	,YEAR(DATEADD(DAY,ISNULL(C.DIAS_VENCTO,20), ISNULL(P.DT_FATURAMENTO,P.DT_EMI))) AS ANO
    	,MONTH(DATEADD(DAY,ISNULL(C.DIAS_VENCTO,20), ISNULL(P.DT_FATURAMENTO,P.DT_EMI))) AS MES
    	,P.DT_EMI AS DT_EMIS
    	,DATEADD(DAY,ISNULL(C.DIAS_VENCTO,20), ISNULL(P.DT_FATURAMENTO,P.DT_EMI)) AS DT_VENCTO
    	,ITCP.CD_COMPRADOR
    	,EST_COMPRADOR.NM_COMPRADOR
    	,(CONVERT (MONEY, ((IT.VLR_IT - (IT.VLR_IT * (IT.PERC_DESC/100.0000000))))+IT.VLR_ICMS_SUBS) * IT.QT_IT) / CASE
    		WHEN ISNULL(C.QT_PARC,1)=0
    		THEN 1
    		ELSE ISNULL(C.QT_PARC,1)
    	END VLR_PEDIDO_PENDENTE
    	,0 AS VLR_DESC
    	,0 AS VLR_JUROS
    	,0 AS VLR_DESP_OUTR
    	,0 AS VLR_DEVOLUCAO
    	,(CONVERT (MONEY, ((IT.VLR_IT - (IT.VLR_IT * (IT.PERC_DESC/100.0000000))))+IT.VLR_ICMS_SUBS) * IT.QT_IT) / CASE
    		WHEN ISNULL(C.QT_PARC,1)=0
    		THEN 1
    		ELSE ISNULL(C.QT_PARC,1)
    	END VLR_PEDIDO_PENDENTE
    	,IT.CD_PROD
    	,FAB.CD_FABRIC
    	,FAB.NM_FABRIC
    	,L.CD_ARV_MERC_LINHA
    	,L.DS_ARV_MERC_LINHA
    	,P.CD_PED
    	,0
    	,0
    	,C.NR_VENCTO AS PARC
		,P.CD_FORN
    	,EST_PROD.DS_PROD
		,0 AS CD_TP_FORN
    	,0 AS VLR_IMP
    	,0 AS VLR_VENDA
    	,'PEDIDO COMPRAS PENDENTE' AS TRANSACAO
    	,0 AS VLR_DUP_PGTO
    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
    	INNER JOIN EST_PED_CP_IT IT ON	P.CD_EMP = IT.CD_EMP
    				AND P.CD_FILIAL = IT.CD_FILIAL
    				AND P.CD_PED = IT.CD_PED
    	INNER JOIN EST_PED_CP_IT_COMPRADOR ITCP ON	IT.CD_EMP = ITCP.CD_EMP
    				AND IT.CD_FILIAL = ITCP.CD_FILIAL
    				AND IT.CD_PED = ITCP.CD_PED
    				AND IT.CD_PROD = ITCP.CD_PROD
    	INNER JOIN EST_PROD ON	IT.CD_EMP = EST_PROD.CD_EMP
    				AND IT.CD_PROD = EST_PROD.CD_PROD
    	INNER JOIN DBO.EST_PROD_EST_ARV_MERCADOLOGICA AS ARV ON	IT.CD_EMP = ARV.CD_EMP
    				AND IT.CD_PROD = ARV.CD_PROD
    	INNER JOIN DBO.EST_PROD_FABRIC AS FAB ON	EST_PROD.CD_FABRIC = FAB.CD_FABRIC
    	INNER JOIN DBO.EST_ARV_MERC_LINHA AS L ON	ARV.CD_EMP = L.CD_EMP
    				AND ARV.CD_ARV_MERC_LINHA = L.CD_ARV_MERC_LINHA
    	INNER JOIN EST_COMPRADOR ON	ITCP.CD_EMP = EST_COMPRADOR.CD_EMP
    				AND ITCP.CD_COMPRADOR = EST_COMPRADOR.CD_COMPRADOR