/****** Object:  View [dbo].[V_PDV_VD_DEV_NCC_CONSULTA]    Script Date: 02/12/2014 12:04:37 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PDV_VD_DEV_NCC_CONSULTA]'))
DROP VIEW [dbo].[V_PDV_VD_DEV_NCC_CONSULTA]
GO

--SELECT * FROM V_PDV_VD_DEV_NCC_CONSULTA WHERE CD_EMP = 1 AND CD_FILIAL = 999 AND CD_PED = 328664
--versao 3297
CREATE VIEW [dbo].[V_PDV_VD_DEV_NCC_CONSULTA]  WITH ENCRYPTION AS 
SELECT 
	CD_EMP,
	CD_FILIAL,
	CD_CX,
	DT_VD,
	NR_ECF,
	VLR_LIQ_VD,
	CD_CLI,
	VLR_DINH,
	VLR_CHQS,
	VLR_CARTAO,
	VLR_TICKETS,
	VLR_DUP,
	VLR_CONV,
	VLR_NCC,
	DT_FECHA_CX,
	CD_CTR_NCC,
	ISNULL(SUM(VLR_NCC_GERADA),0)                       AS VLR_NCC_GERADA                      ,
	ISNULL(SUM(VLR_NCC_ABERTO),0)                       AS VLR_NCC_ABERTO                      ,
	ISNULL(SUM(VLR_NCC_VD_NOVA),0)                      AS VLR_NCC_VD_NOVA                     ,
	ISNULL(SUM(VLR_NCC_VD_ABATIMENTO),0)                AS VLR_NCC_VD_ABATIMENTO               ,
	ISNULL(SUM(VLR_NCC_VD_AMORTIZADO),0)                AS VLR_NCC_VD_AMORTIZADO               ,
	ISNULL(SUM(VLR_NCC_VD_CANCELADA),0)                 AS VLR_NCC_VD_CANCELADA                ,
	ISNULL(SUM(VLR_NCC_OUTRO_DIA),0)                    AS VLR_NCC_OUTRO_DIA                   ,
	ISNULL(SUM(VLR_NCC_OUTRO_DIA_POST),0)               AS VLR_NCC_OUTRO_DIA_POST              ,
	ISNULL(SUM(VLR_NCC_VD_CANCELADA_CARTAO_CONV),0)     AS VLR_NCC_VD_CANCELADA_CARTAO_CONV    ,
	ISNULL(SUM(VLR_NCC_VD_CANCELADA_DINH),0)            AS VLR_NCC_VD_CANCELADA_DINH           ,
	ISNULL(SUM(VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV),0) AS VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV,
	ISNULL(SUM(VLR_NCC_VD_CANCELADA_CHEQUE), 0)         AS VLR_NCC_VD_CANCELADA_CHEQUE
FROM   ( 
SELECT DISTINCT 
	V.CD_EMP,
	V.CD_FILIAL,
	V.CD_CX,
	V.DT_VD,
	V.NR_ECF,
	V.VLR_LIQ_VD,
	V.CD_CLI,
	V.VLR_DINH,
	V.VLR_CHQS,
	V.VLR_CARTAO,
	V.VLR_TICKETS,
	V.VLR_DUP,
	V.VLR_CONV,
	V.VLR_NCC,
	PDV_VD_DEV_NCC.DT_FECHA_CX,
	PDV_VD_DEV_NCC.CD_CTR_NCC,
	PDV_VD_DEV_NCC.VLR_NCC AS VLR_NCC_GERADA                      ,
	0                      AS VLR_NCC_ABERTO                      ,
	0                      AS VLR_NCC_VD_NOVA                     ,
	0                      AS VLR_NCC_VD_ABATIMENTO               ,
	0                      AS VLR_NCC_VD_AMORTIZADO               ,
	0                      AS VLR_NCC_VD_CANCELADA                ,
	0                      AS VLR_NCC_OUTRO_DIA                   ,
	0                      AS VLR_NCC_OUTRO_DIA_POST              ,
	0                      AS VLR_NCC_VD_CANCELADA_CARTAO_CONV    ,
	0                      AS VLR_NCC_VD_CANCELADA_DINH           ,
	0                      AS VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV,
	0                      AS VLR_NCC_VD_CANCELADA_CHEQUE
FROM   PDV_VD_DEV_NCC INNER JOIN PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP ON
	PDV_VD_DEV_NCC.CD_EMP=PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP AND
	PDV_VD_DEV_NCC.CD_FILIAL=PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL AND
	PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_CTR_NCC

	LEFT OUTER JOIN PDV_VD V ON
	PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP    =V.CD_EMP AND 
	PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL =V.CD_FILIAL AND
	PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_VD     = V.CD_VD
		
--WHERE            PDV_VD_DEV_NCC.CD_EMP      = 1
--AND              PDV_VD_DEV_NCC.CD_FILIAL   =505
--AND              PDV_VD_DEV_NCC.DT_FECHA_CX = '11/04/2014'
       
UNION ALL
       
SELECT 
	V.CD_EMP,
	V.CD_FILIAL,
	V.CD_CX,
	V.DT_VD,
	V.NR_ECF,
	V.VLR_LIQ_VD,
	V.CD_CLI,
	V.VLR_DINH,
	V.VLR_CHQS,
	V.VLR_CARTAO,
	V.VLR_TICKETS,
	V.VLR_DUP,
	V.VLR_CONV,
	V.VLR_NCC,
	PDV_VD_DEV_NCC.DT_FECHA_CX,
	PDV_VD_DEV_NCC.CD_CTR_NCC                                     ,
	0                      AS VLR_NCC_GERADA                      ,
	PDV_VD_DEV_NCC.VLR_NCC AS VLR_NCC_ABERTO                      ,
	0                      AS VLR_NCC_VD_NOVA                     ,
	0                      AS VLR_NCC_VD_ABATIMENTO               ,
	0                      AS VLR_NCC_VD_AMORTIZADO               ,
	0                      AS VLR_NCC_VD_CANCELADA                ,
	0                      AS VLR_NCC_OUTRO_DIA                   ,
	0                      AS VLR_NCC_OUTRO_DIA_POST              ,
	0                      AS VLR_NCC_VD_CANCELADA_CARTAO_CONV    ,
	0                      AS VLR_NCC_VD_CANCELADA_DINH           ,
	0                      AS VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV,
	0                      AS VLR_NCC_VD_CANCELADA_CHEQUE
FROM   PDV_VD_DEV_NCC INNER JOIN PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP ON
	PDV_VD_DEV_NCC.CD_EMP=PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP AND
	PDV_VD_DEV_NCC.CD_FILIAL=PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL AND
	PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_CTR_NCC

	LEFT OUTER JOIN PDV_VD V ON
	PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP    =V.CD_EMP AND 
	PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL =V.CD_FILIAL AND
	PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_VD     = V.CD_VD

WHERE  PDV_VD_DEV_NCC.CD_EMP      = 1
--AND    PDV_VD_DEV_NCC.CD_FILIAL   =505
--AND    PDV_VD_DEV_NCC.DT_FECHA_CX = '11/04/2014'
AND    PDV_VD_DEV_NCC.STS_NCC     =0
       
UNION ALL
       
SELECT DISTINCT 
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.CD_CX,
	PDV_VD.DT_VD,
	PDV_VD.NR_ECF,
	PDV_VD.VLR_LIQ_VD,
	PDV_VD.CD_CLI,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CHQS,
	PDV_VD.VLR_CARTAO,
	PDV_VD.VLR_TICKETS,
	PDV_VD.VLR_DUP,
	PDV_VD.VLR_CONV,
	PDV_VD.VLR_NCC,
	PDV_VD_DEV_NCC.DT_FECHA_CX,
	PDV_VD_DEV_NCC.CD_CTR_NCC ,
	0 AS VLR_NCC_GERADA       ,
	0 AS VLR_NCC_ABERTO       ,
	0 AS VLR_NCC_VD_NOVA      ,
	0 AS VLR_NCC_VD_ABATIMENTO,
	(
					CASE
									WHEN PDV_VD.VLR_DINH > 0
									THEN (
																	CASE
																					WHEN PDV_VD.VLR_DINH > PDV_VD_DEV_NCC.VLR_NCC
																					THEN PDV_VD_DEV_NCC.VLR_NCC
																					ELSE PDV_VD.Vlr_Dinh
																	END)
									ELSE 0
					END) AS VLR_NCC_VD_AMORTIZADO               ,
	0                    AS VLR_NCC_VD_CANCELADA                ,
	0                    AS VLR_NCC_OUTRO_DIA                   ,
	0                    AS VLR_NCC_OUTRO_DIA_POST              ,
	0                    AS VLR_NCC_VD_CANCELADA_CARTAO_CONV    ,
	0                    AS VLR_NCC_VD_CANCELADA_DINH           ,
	0                    AS VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV,
	0                    AS VLR_NCC_VD_CANCELADA_CHEQUE
FROM            PDV_VD_DEV_NCC
            LEFT OUTER JOIN PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP
            ON              PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_CTR_NCC
            INNER JOIN PDV_VD  ON              
			PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP    =PDV_VD.CD_EMP
            AND             PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL =PDV_VD.CD_FILIAL
            AND             PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.Cd_Vd     = PDV_VD.Cd_Vd
WHERE           PDV_VD_DEV_NCC.CD_EMP                                            = 1
--AND             PDV_VD_DEV_NCC.CD_FILIAL                                         =505
--AND             PDV_VD_DEV_NCC.DT_FECHA_CX                                       = '11/04/2014'
AND             PDV_VD_DEV_NCC.STS_NCC                                           = 2
       
UNION ALL
       
SELECT 
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.CD_CX,
	PDV_VD.DT_VD,
	PDV_VD.NR_ECF,
	PDV_VD.VLR_LIQ_VD,
	PDV_VD.CD_CLI,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CHQS,
	PDV_VD.VLR_CARTAO,
	PDV_VD.VLR_TICKETS,
	PDV_VD.VLR_DUP,
	PDV_VD.VLR_CONV,
	PDV_VD.VLR_NCC,
	PDV_VD_DEV_NCC.DT_FECHA_CX,
	PDV_VD_DEV_NCC.CD_CTR_NCC                                     ,
	0                      AS VLR_NCC_GERADA                      ,
	0                      AS VLR_NCC_ABERTO                      ,
	PDV_VD_DEV_NCC.VLR_NCC AS VLR_NCC_VD_NOVA                     ,
	0                      AS VLR_NCC_VD_ABATIMENTO               ,
	0                      AS VLR_NCC_VD_AMORTIZADO               ,
	0                      AS VLR_NCC_VD_CANCELADA                ,
	0                      AS VLR_NCC_OUTRO_DIA                   ,
	0                      AS VLR_NCC_OUTRO_DIA_POST              ,
	0                      AS VLR_NCC_VD_CANCELADA_CARTAO_CONV    ,
	0                      AS VLR_NCC_VD_CANCELADA_DINH           ,
	0                      AS VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV,
	0                      AS VLR_NCC_VD_CANCELADA_CHEQUE
FROM   PDV_VD_DEV_NCC
    INNER JOIN PDV_VD_DEV_NCC_PDV_VD
    ON     PDV_VD_DEV_NCC.CD_EMP     = PDV_VD_DEV_NCC_PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC.CD_FILIAL  = PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC
    INNER JOIN PDV_VD
    ON     PDV_VD_DEV_NCC_PDV_VD.CD_EMP    = PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL = PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC_PDV_VD.CD_VD     = PDV_VD.CD_VD
WHERE  PDV_VD.CD_EMP                          = 1
--AND    PDV_VD.CD_FILIAL                       =505
--AND    PDV_VD.DT_VD                           = '11/04/2014'
AND    PDV_VD_DEV_NCC.STS_NCC                 = 1
AND    PDV_VD.ST_VD                           = 0
AND    PDV_VD.DT_VD                           = PDV_VD_DEV_NCC.DT_FECHA_CX
       
UNION ALL
       
SELECT 
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.CD_CX,
	PDV_VD.DT_VD,
	PDV_VD.NR_ECF,
	PDV_VD.VLR_LIQ_VD,
	PDV_VD.CD_CLI,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CHQS,
	PDV_VD.VLR_CARTAO,
	PDV_VD.VLR_TICKETS,
	PDV_VD.VLR_DUP,
	PDV_VD.VLR_CONV,
	PDV_VD.VLR_NCC,
	PDV_VD_DEV_NCC.DT_FECHA_CX,
	PDV_VD_DEV_NCC.CD_CTR_NCC,
	0 AS VLR_NCC_GERADA      ,
	CASE
			WHEN PDV_VD_DEV_NCC.DT_CAD <> PDV_VD_DEV_NCC_LANC.DT_CAD
			THEN PDV_VD_DEV_NCC.VLR_NCC
			ELSE 0
	END AS VLR_NCC_ABERTO ,
	0   AS VLR_NCC_VD_NOVA,
	CASE
			WHEN PDV_VD_DEV_NCC.DT_CAD = PDV_VD_DEV_NCC_LANC.DT_CAD
			THEN PDV_VD_DEV_NCC.VLR_NCC
			ELSE 0
	END AS VLR_NCC_VD_ABATIMENTO               ,
	0   AS VLR_NCC_VD_AMORTIZADO               ,
	0   AS VLR_NCC_VD_CANCELADA                ,
	0   AS VLR_NCC_OUTRO_DIA                   ,
	0   AS VLR_NCC_OUTRO_DIA_POST              ,
	0   AS VLR_NCC_VD_CANCELADA_CARTAO_CONV    ,
	0   AS VLR_NCC_VD_CANCELADA_DINH           ,
	0   AS VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV,
	0   AS VLR_NCC_VD_CANCELADA_CHEQUE
FROM   PDV_VD_DEV_NCC
    LEFT OUTER JOIN PDV_VD_DEV_NCC_PDV_VD
    ON     PDV_VD_DEV_NCC.CD_EMP     = PDV_VD_DEV_NCC_PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC.CD_FILIAL  = PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC
    INNER JOIN PDV_VD_DEV_NCC_LANC_CPL
    ON     PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_LANC_CPL.CD_CTR_NCC
    INNER JOIN PDV_VD_DEV_NCC_LANC
    ON     PDV_VD_DEV_NCC_LANC_CPL.CD_CTR_LANC_NCC = PDV_VD_DEV_NCC_LANC.CD_CTR_LANC_NCC
    INNER JOIN PDV_VD
    ON     PDV_VD_DEV_NCC_PDV_VD.CD_EMP    = PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL = PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC_PDV_VD.CD_VD     = PDV_VD.CD_VD

WHERE  PDV_VD_DEV_NCC.CD_EMP                          = 1
--AND    PDV_VD_DEV_NCC.CD_FILIAL                       =505
--AND    PDV_VD_DEV_NCC.DT_FECHA_CX                     = '11/04/2014'
AND    PDV_VD_DEV_NCC.STS_NCC                         = 1
AND    PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC         IS NULL
       
UNION ALL
       
SELECT 
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.CD_CX,
	PDV_VD.DT_VD,
	PDV_VD.NR_ECF,
	PDV_VD.VLR_LIQ_VD,
	PDV_VD.CD_CLI,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CHQS,
	PDV_VD.VLR_CARTAO,
	PDV_VD.VLR_TICKETS,
	PDV_VD.VLR_DUP,
	PDV_VD.VLR_CONV,
	PDV_VD.VLR_NCC,
	PDV_VD_DEV_NCC.DT_FECHA_CX,
	PDV_VD_DEV_NCC.CD_CTR_NCC                                     ,
	0                      AS VLR_NCC_GERADA                      ,
	0                      AS VLR_NCC_ABERTO                      ,
	0                      AS VLR_NCC_VD_NOVA                     ,
	0                      AS VLR_NCC_VD_ABATIMENTO               ,
	0                      AS VLR_NCC_VD_AMORTIZADO               ,
	0                      AS VLR_NCC_VD_CANCELADA                ,
	PDV_VD_DEV_NCC.VLR_NCC AS VLR_NCC_OUTRO_DIA                   ,
	0                      AS VLR_NCC_OUTRO_DIA_POST              ,
	0                      AS VLR_NCC_VD_CANCELADA_CARTAO_CONV    ,
	0                      AS VLR_NCC_VD_CANCELADA_DINH           ,
	0                      AS VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV,
	0                      AS VLR_NCC_VD_CANCELADA_CHEQUE
FROM   PDV_VD_DEV_NCC
    INNER JOIN PDV_VD_DEV_NCC_PDV_VD
    ON     PDV_VD_DEV_NCC.CD_EMP     = PDV_VD_DEV_NCC_PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC.CD_FILIAL  = PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC
    INNER JOIN PDV_VD
    ON     PDV_VD_DEV_NCC_PDV_VD.CD_EMP    = PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL = PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC_PDV_VD.CD_VD     = PDV_VD.CD_VD
WHERE  PDV_VD.CD_EMP                          = 1
--AND    PDV_VD.CD_FILIAL                       =505
--AND    PDV_VD.DT_VD                           = '11/04/2014'
AND    PDV_VD_DEV_NCC.STS_NCC                 = 1
AND    PDV_VD.ST_VD                           = 0
AND    PDV_VD.DT_VD                          <> PDV_VD_DEV_NCC.DT_FECHA_CX
       
UNION ALL
       
SELECT 
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.CD_CX,
	PDV_VD.DT_VD,
	PDV_VD.NR_ECF,
	PDV_VD.VLR_LIQ_VD,
	PDV_VD.CD_CLI,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CHQS,
	PDV_VD.VLR_CARTAO,
	PDV_VD.VLR_TICKETS,
	PDV_VD.VLR_DUP,
	PDV_VD.VLR_CONV,
	PDV_VD.VLR_NCC,
	PDV_VD_DEV_NCC.DT_FECHA_CX,
	PDV_VD_DEV_NCC.CD_CTR_NCC                                     ,
	0                      AS VLR_NCC_GERADA                      ,
	0                      AS VLR_NCC_ABERTO                      ,
	0                      AS VLR_NCC_VD_NOVA                     ,
	0                      AS VLR_NCC_VD_ABATIMENTO               ,
	0                      AS VLR_NCC_VD_AMORTIZADO               ,
	0                      AS VLR_NCC_VD_CANCELADA                ,
	0                      AS VLR_NCC_OUTRO_DIA                   ,
	PDV_VD_DEV_NCC.VLR_NCC AS VLR_NCC_OUTRO_DIA_POST              ,
	0                      AS VLR_NCC_VD_CANCELADA_CARTAO_CONV    ,
	0                      AS VLR_NCC_VD_CANCELADA_DINH           ,
	0                      AS VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV,
	0                      AS VLR_NCC_VD_CANCELADA_CHEQUE
FROM   PDV_VD_DEV_NCC
    INNER JOIN PDV_VD_DEV_NCC_PDV_VD
    ON     PDV_VD_DEV_NCC.CD_EMP     = PDV_VD_DEV_NCC_PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC.CD_FILIAL  = PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC
    INNER JOIN PDV_VD
    ON     PDV_VD_DEV_NCC_PDV_VD.CD_EMP    = PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL = PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC_PDV_VD.CD_VD     = PDV_VD.CD_VD
WHERE  PDV_VD_DEV_NCC.CD_EMP                  = 1
--AND    PDV_VD_DEV_NCC.CD_FILIAL               = 505
--AND    PDV_VD_DEV_NCC.DT_FECHA_CX             = '11/04/2014'
AND    PDV_VD_DEV_NCC.STS_NCC                 = 1
AND    PDV_VD.DT_VD                          <> DT_FECHA_CX
AND    PDV_VD.ST_VD                           = 0
AND    PDV_VD.DT_VD                          <> PDV_VD_DEV_NCC.DT_FECHA_CX
       
UNION ALL
       
SELECT 
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.CD_CX,
	PDV_VD.DT_VD,
	PDV_VD.NR_ECF,
	PDV_VD.VLR_LIQ_VD,
	PDV_VD.CD_CLI,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CHQS,
	PDV_VD.VLR_CARTAO,
	PDV_VD.VLR_TICKETS,
	PDV_VD.VLR_DUP,
	PDV_VD.VLR_CONV,
	PDV_VD.VLR_NCC,
	PDV_VD_DEV_NCC.DT_FECHA_CX,
	PDV_VD_DEV_NCC.CD_CTR_NCC                                     ,
	0                      AS VLR_NCC_GERADA                      ,
	0                      AS VLR_NCC_ABERTO                      ,
	0                      AS VLR_NCC_VD_NOVA                     ,
	0                      AS VLR_NCC_VD_ABATIMENTO               ,
	0                      AS VLR_NCC_VD_AMORTIZADO               ,
	0                      AS VLR_NCC_VD_CANCELADA                ,
	0                      AS VLR_NCC_OUTRO_DIA                   ,
	PDV_VD_DEV_NCC.VLR_NCC AS VLR_NCC_OUTRO_DIA_POST              ,
	0                      AS VLR_NCC_VD_CANCELADA_CARTAO_CONV    ,
	0                      AS VLR_NCC_VD_CANCELADA_DINH           ,
	0                      AS VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV,
	0                      AS VLR_NCC_VD_CANCELADA_CHEQUE
FROM   PDV_VD_DEV_NCC
    LEFT OUTER JOIN PDV_VD_DEV_NCC_PDV_VD
    ON     PDV_VD_DEV_NCC.CD_EMP     = PDV_VD_DEV_NCC_PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC.CD_FILIAL  =PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC
    INNER JOIN PDV_VD_DEV_NCC_LANC_CPL
    ON     PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_LANC_CPL.CD_CTR_NCC
    INNER JOIN PDV_VD_DEV_NCC_LANC
    ON     PDV_VD_DEV_NCC_LANC_CPL.CD_CTR_LANC_NCC = PDV_VD_DEV_NCC_LANC.CD_CTR_LANC_NCC
    
	INNER JOIN PDV_VD
    ON     PDV_VD_DEV_NCC_PDV_VD.CD_EMP    = PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL = PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC_PDV_VD.CD_VD     = PDV_VD.CD_VD

WHERE  PDV_VD_DEV_NCC.CD_EMP                          = 1
--AND    PDV_VD_DEV_NCC.CD_FILIAL                       = 505
--AND    PDV_VD_DEV_NCC.DT_FECHA_CX                     = '11/04/2014'
AND    PDV_VD_DEV_NCC.STS_NCC                         = 1
AND    PDV_VD_DEV_NCC_LANC.DT_CAD                    <> DT_FECHA_CX
AND    PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC         IS NULL
       
UNION ALL
       
SELECT 
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.CD_CX,
	PDV_VD.DT_VD,
	PDV_VD.NR_ECF,
	PDV_VD.VLR_LIQ_VD,
	PDV_VD.CD_CLI,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CHQS,
	PDV_VD.VLR_CARTAO,
	PDV_VD.VLR_TICKETS,
	PDV_VD.VLR_DUP,
	PDV_VD.VLR_CONV,
	PDV_VD.VLR_NCC,
	PDV_VD_DEV_NCC.DT_FECHA_CX,
	PDV_VD_DEV_NCC.CD_CTR_NCC            ,
	0 AS VLR_NCC_GERADA                  ,
	0 AS VLR_NCC_ABERTO                  ,
	0 AS VLR_NCC_VD_NOVA                 ,
	0 AS VLR_NCC_VD_ABATIMENTO           ,
	0 AS VLR_NCC_VD_AMORTIZADO           ,
	0 AS VLR_NCC_VD_CANCELADA            ,
	0 AS VLR_NCC_OUTRO_DIA               ,
	0 AS VLR_NCC_OUTRO_DIA_POST          ,
	0 AS VLR_NCC_VD_CANCELADA_CARTAO_CONV,
	0 AS VLR_NCC_VD_CANCELADA_DINH       ,
	(
			CASE
				WHEN PDV_VD.VLR_DINH > 0
				THEN (
								CASE
										WHEN PDV_VD.VLR_DINH > PDV_VD_DEV_NCC.VLR_NCC
										THEN 0
										ELSE (PDV_VD_DEV_NCC.VLR_NCC - PDV_VD.VLR_DINH)
								END)
				ELSE (
								CASE
										WHEN PDV_VD.VLR_CHQS > 0
										THEN 0
										ELSE PDV_VD_DEV_NCC.Vlr_NCC
								END)
			END) AS VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV ,
	CASE
			WHEN PDV_VD.VLR_CHQS > 0
			THEN PDV_VD_DEV_NCC.Vlr_NCC
			ELSE 0
	END AS VLR_NCC_VD_CANCELADA_CHEQUE
FROM   PDV_VD_DEV_NCC
    LEFT OUTER JOIN
            ( SELECT DISTINCT PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP  ,
                            PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,
                            PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_VD    ,
                            PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_CTR_NCC
            FROM             PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP
            )
            PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP
    ON     PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_CTR_NCC
    INNER JOIN PDV_VD
    ON     PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP    =PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL =PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.Cd_Vd     = PDV_VD.Cd_Vd
WHERE  PDV_VD_DEV_NCC.CD_EMP                                   = 1
--AND    PDV_VD_DEV_NCC.CD_FILIAL                                =505
--AND    PDV_VD_DEV_NCC.DT_FECHA_CX                              = '11/04/2014'
AND    PDV_VD_DEV_NCC.STS_NCC                                  = 2
AND
    (  PDV_VD.VLR_DUP + PDV_VD.VLR_CONV + PDV_VD.VLR_CARTAO + PDV_VD.VLR_CHQS ) > 0
       
UNION ALL
       
SELECT 
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.CD_CX,
	PDV_VD.DT_VD,
	PDV_VD.NR_ECF,
	PDV_VD.VLR_LIQ_VD,
	PDV_VD.CD_CLI,
	PDV_VD.VLR_DINH,
	PDV_VD.VLR_CHQS,
	PDV_VD.VLR_CARTAO,
	PDV_VD.VLR_TICKETS,
	PDV_VD.VLR_DUP,
	PDV_VD.VLR_CONV,
	PDV_VD.VLR_NCC,
	PDV_VD_DEV_NCC.DT_FECHA_CX,
	PDV_VD_DEV_NCC.CD_CTR_NCC  ,
	0                      AS VLR_NCC_GERADA        ,
	0                      AS VLR_NCC_ABERTO        ,
	0                      AS VLR_NCC_VD_NOVA       ,
	0                      AS VLR_NCC_VD_ABATIMENTO ,
	0                      AS VLR_NCC_VD_AMORTIZADO ,
	PDV_VD_DEV_NCC.VLR_NCC AS VLR_NCC_VD_CANCELADA  ,
	0                      AS VLR_NCC_OUTRO_DIA     ,
	0                      AS VLR_NCC_OUTRO_DIA_POST,
	(
			CASE
				WHEN
						(
								ISNULL(PDV_VD_CANCEL_CONV.CD_VD,0) + ISNULL(PDV_VD_CANCEL_TEF.CD_VD,0) + ISNULL(PDV_VD_CANCEL_DUP.CD_VD,0)
						)
						> 0
				THEN ((
										CASE
											WHEN ISNULL(PDV_VD_CANCEL_CONV.CD_VD,0) > 0
											THEN PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM
											ELSE 0
										END) + (
										CASE
											WHEN ISNULL(PDV_VD_CANCEL_TEF.CD_VD,0) > 0
											THEN PDV_VD.VLR_CARTAO
											ELSE 0
										END) + (
										CASE
											WHEN ISNULL(PDV_VD_CANCEL_DUP.CD_VD,0) > 0
											THEN PDV_VD.VLR_DUP
											ELSE 0
										END))
				ELSE (
								CASE
										WHEN
											(
													PDV_VD.VLR_CARTAO + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_DUP
											)
											> 0
										AND
											(
													PDV_VD_DEV_NCC.VLR_NCC >= PDV_VD.VLR_DINH
											)
										THEN (
													CASE
															WHEN
																	(
																		PDV_VD.VLR_CARTAO + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_DUP
																	)
																					+ (PDV_VD.VLR_DINH + PDV_VD.VLR_CHQS) = PDV_VD_DEV_NCC.VLR_NCC
															THEN (((PDV_VD.VLR_DINH + PDV_VD.VLR_CARTAO + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_DUP) - ((PDV_VD.VLR_DINH + PDV_VD.VLR_CARTAO + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_DUP)- (PDV_VD_DEV_NCC.VLR_NCC )))-(PDV_VD.VLR_DINH + PDV_VD.VLR_CHQS))
															WHEN
																	(
																		PDV_VD.VLR_CARTAO + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_DUP
																	)
																	> PDV_VD_DEV_NCC.VLR_NCC
															THEN (PDV_VD_DEV_NCC.VLR_NCC - (PDV_VD.VLR_DINH + PDV_VD.VLR_CHQS))
															ELSE (PDV_VD.VLR_CARTAO      + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_DUP)
													END)
										WHEN
											(
													PDV_VD.VLR_CARTAO + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_DUP
											)
											> 0
										AND
											(
													PDV_VD_DEV_NCC.VLR_NCC < PDV_VD.VLR_DINH
											)
										THEN ( (
															CASE
																	WHEN ISNULL(PDV_VD_CANCEL_CONV.CD_VD,0) > 0
																	THEN PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM
																	ELSE 0
															END) + (
															CASE
																	WHEN ISNULL(PDV_VD_CANCEL_TEF.CD_VD,0) > 0
																	THEN PDV_VD.VLR_CARTAO
																	ELSE 0
															END) + (
															CASE
																	WHEN ISNULL(PDV_VD_CANCEL_DUP.CD_VD,0) > 0
																	THEN PDV_VD.VLR_DUP
																	ELSE 0
															END))
										ELSE 0
								END)
			END ) AS VLR_NCC_VD_CANCELADA_CARTAO_CONV,
	ISNULL((
				CASE
						WHEN PDV_VD.VLR_DINH   > PDV_VD_DEV_NCC.VLR_NCC
						AND    PDV_VD.VLR_CHQS =0
						THEN PDV_VD_DEV_NCC.Vlr_NCC
						WHEN PDV_VD.VLR_CHQS > PDV_VD_DEV_NCC.VLR_NCC
						THEN (PDV_VD_DEV_NCC.VLR_NCC - (PDV_VD_DEV_NCC.VLR_NCC - (PDV_VD.VLR_DINH + PDV_VD.VLR_CARTAO + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_DUP)))
						ELSE (
										CASE
											WHEN PDV_VD.VLR_CHQS > 0
											THEN (PDV_VD_DEV_NCC.VLR_NCC - PDV_VD.VLR_CHQS - ( (
																	CASE
																		WHEN ISNULL(PDV_VD_CANCEL_CONV.CD_VD,0) > 0
																		THEN PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM
																		ELSE 0
																	END) + (
																	CASE
																		WHEN ISNULL(PDV_VD_CANCEL_TEF.CD_VD,0) > 0
																		THEN PDV_VD.VLR_CARTAO
																		ELSE 0
																	END) + (
																	CASE
																		WHEN ISNULL(PDV_VD_CANCEL_DUP.CD_VD,0) > 0
																		THEN PDV_VD.VLR_DUP
																		ELSE 0
																	END)))
											WHEN
													(
															PDV_VD.VLR_CHQS + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_CARTAO + PDV_VD.VLR_DUP
													)
													< PDV_VD_DEV_NCC.VLR_NCC
											THEN
													CASE
															WHEN PDV_VD.VLR_DINH = PDV_VD_DEV_NCC.VLR_NCC
															THEN PDV_VD_DEV_NCC.Vlr_NCC
															ELSE PDV_VD_DEV_NCC.VLR_NCC -(PDV_VD.VLR_CHQS + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_CARTAO + PDV_VD.VLR_DUP)
													END
											WHEN
													(
															PDV_VD.VLR_CHQS + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_CARTAO + PDV_VD.VLR_DUP
													)
													= PDV_VD_DEV_NCC.VLR_NCC
											THEN 0
											WHEN PDV_VD.VLR_DINH >= PDV_VD_DEV_NCC.VLR_NCC
											THEN PDV_VD_DEV_NCC.Vlr_NCC
											WHEN PDV_VD.VLR_DINH < PDV_VD_DEV_NCC.VLR_NCC
											AND
													(
															PDV_VD.VLR_CHQS + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_CARTAO + PDV_VD.VLR_DUP
													)
													> 0
											THEN PDV_VD.Vlr_Dinh
										END)
				END),0) AS VLR_NCC_VD_CANCELADA_DINH           ,
	0                     AS VLR_NCC_VD_AMORTIZADO_DUP_CARTA_CONV,
	(
			CASE
				WHEN PDV_VD.VLR_CHQS   > PDV_VD_DEV_NCC.VLR_NCC
				AND    PDV_VD.VLR_DINH = 0
				THEN PDV_VD_DEV_NCC.Vlr_NCC
				WHEN PDV_VD.VLR_CHQS   > PDV_VD_DEV_NCC.VLR_NCC
				AND    PDV_VD.VLR_DINH > 0
				THEN (PDV_VD_DEV_NCC.VLR_NCC - (PDV_VD.VLR_DINH + PDV_VD.VLR_CARTAO + PDV_VD.VLR_CONV + PDV_VD.VLR_CONV_PBM + PDV_VD.VLR_DUP))
				ELSE PDV_VD.VLR_CHQS
			END) AS VLR_NCC_VD_CANCELADA_CHEQUE
FROM   PDV_VD_DEV_NCC
    LEFT OUTER JOIN
            ( SELECT DISTINCT PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP  ,
                            PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,
                            PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_VD    ,
                            PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_CTR_NCC
            FROM             PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP
            )
            PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP
    ON     PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_CTR_NCC
    INNER JOIN PDV_VD
    ON     PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP    =PDV_VD.CD_EMP
    AND    PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL =PDV_VD.CD_FILIAL
    AND    PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.Cd_Vd     = PDV_VD.Cd_Vd
    LEFT OUTER JOIN PDV_VD_CANCEL_CONV
    ON     PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP    = PDV_VD_CANCEL_CONV.CD_EMP
    AND    PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD_CANCEL_CONV.CD_FILIAL
    AND    PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_VD     = PDV_VD_CANCEL_CONV.CD_VD
    LEFT OUTER JOIN PDV_VD_CANCEL_TEF
    ON     PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP    = PDV_VD_CANCEL_TEF.CD_EMP
    AND    PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD_CANCEL_TEF.CD_FILIAL
    AND    PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_VD     = PDV_VD_CANCEL_TEF.CD_VD
    LEFT OUTER JOIN PDV_VD_CANCEL_DUP
    ON     PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_EMP    = PDV_VD_CANCEL_DUP.CD_EMP
    AND    PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD_CANCEL_DUP.CD_FILIAL
    AND    PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP.CD_VD     = PDV_VD_CANCEL_DUP.CD_VD
WHERE  PDV_VD_DEV_NCC.CD_EMP                                   = 1
--AND    PDV_VD_DEV_NCC.CD_FILIAL                                =505
--AND    PDV_VD_DEV_NCC.DT_FECHA_CX                              = '11/04/2014'
AND    PDV_VD_DEV_NCC.STS_NCC                                  in(3,5)) AS V_PDV_VD_DEV_NCC

GROUP BY 	
	CD_EMP,
	CD_FILIAL,
	CD_CX,
	DT_VD,
	NR_ECF,
	VLR_LIQ_VD,
	CD_CLI,
	VLR_DINH,
	VLR_CHQS,
	VLR_CARTAO,
	VLR_TICKETS,
	VLR_DUP,
	VLR_CONV,
	VLR_NCC,	
	DT_FECHA_CX,
	CD_CTR_NCC