IF EXISTS( SELECT * FROM SYS.VIEWS WHERE  OBJECT_ID = OBJECT_ID(N'[DBO].[V_PDV_VD_DEV_NCC_QUITACAO]') )
DROP VIEW [DBO].[V_PDV_VD_DEV_NCC_QUITACAO];
GO
CREATE VIEW [DBO].[V_PDV_VD_DEV_NCC_QUITACAO] 
WITH ENCRYPTION
AS
SELECT 
N.CD_EMP,
CASE
    WHEN R.CD_CTR_LANC_NCC IS NOT NULL
    THEN R.CD_FILIAL
    ELSE CASE
             WHEN VD1.NR_ECF IS NOT NULL
             THEN VD1.CD_FILIAL
             ELSE CASE
                      WHEN C.CD_CTR_LANC_NCC IS NOT NULL
                      THEN PDV_VD_CRED_PARC.CD_FILIAL
                      ELSE CASE
                               WHEN D.CD_CTR_LANC_NCC IS NOT NULL
                               THEN VD_CON.CD_FILIAL
                               ELSE CASE
                                        WHEN B.CD_CTR_NCC IS NOT NULL
                                        THEN 0
                                    END
                           END
                  END
         END
END CD_FILIAL, 
N.CD_CLI, 
L.CD_CTR_LANC_NCC LOTE, 
N.CD_CTR_NCC, 
N.VLR_NCC, 
L.DT_CAD DT_QUITACAO,
CASE
    WHEN R.CD_CTR_LANC_NCC IS NOT NULL
    THEN RC.NR_DP
    ELSE CASE
             WHEN C.CD_CTR_LANC_NCC IS NOT NULL
             THEN C.CD_CTR
             ELSE CASE
                      WHEN D.CD_CTR_LANC_NCC IS NOT NULL
                      THEN D.CD_CTR
                      ELSE CASE
                               WHEN B.CD_CTR_NCC IS NOT NULL
                               THEN B.CD_RC_DEB
                           END
                  END
         END
END CONTROLE_DOCUMENTO,
CASE
    WHEN R.CD_CTR_LANC_NCC IS NOT NULL
    THEN CASE
             WHEN VD1.NR_ECF IS NOT NULL 
             THEN VD1.NR_ECF
             ELSE SAI1.NF_NF
         END
    ELSE CASE
             WHEN C.CD_CTR_LANC_NCC IS NOT NULL
             THEN PDV_VD_CRED_PARC.NR_ECF
             ELSE CASE
                      WHEN D.CD_CTR_LANC_NCC IS NOT NULL
                      THEN VD_CON.NR_ECF
                      ELSE CASE
                               WHEN B.CD_CTR_NCC IS NOT NULL
                               THEN 0
                           END
                  END
         END
END DOCUMENTO,
CASE
    WHEN R.CD_CTR_LANC_NCC IS NOT NULL
    THEN 'Crediário - Duplicata'
    ELSE CASE
             WHEN C.CD_CTR_LANC_NCC IS NOT NULL
             THEN 'Crediário - Parcela'
             ELSE CASE
                      WHEN D.CD_CTR_LANC_NCC IS NOT NULL
                      THEN 'Convênio'
                      ELSE CASE
                               WHEN B.CD_CTR_NCC IS NOT NULL
                               THEN 'Duplicata'
                           END
                  END
         END
END TIPO_VENDA_QUITADO, 
VLR_LANC AS VLR_TOTAL_DOCUMENTO_PAGO

-- lote do pagamento
FROM PDV_VD_DEV_NCC_LANC L
     INNER JOIN PDV_VD_DEV_NCC_LANC_CPL P ON L.CD_CTR_LANC_NCC = P.CD_CTR_LANC_NCC
     INNER JOIN PDV_VD_DEV_NCC N ON P.CD_CTR_NCC = N.CD_CTR_NCC

     --1A DE 4 QUITACAO CREDIARIO - QUANDO FATURA
     LEFT JOIN PDV_VD_DEV_NCC_LANC_RC_CRED R ON L.CD_CTR_LANC_NCC = R.CD_CTR_LANC_NCC

     -- DUPLICATA QUITADADA
     LEFT JOIN RC_DEB RC ON R.CD_EMP = RC.CD_EMP
                            AND R.CD_FILIAL = RC.CD_FILIAL
                            AND R.CD_RC_DEB = RC.CD_RC_DEB

     -- O VINCULO ENTRE DUPLICATA E FECHAMENTO
     LEFT JOIN RC_CREDIARIO_FECHA_RC_DEB F ON RC.CD_EMP = F.CD_EMP
                                              AND RC.CD_FILIAL = F.CD_FILIAL
                                              AND RC.CD_RC_DEB = F.CD_RC_DEB
     -- O FECHAMENTO
     LEFT JOIN RC_CREDIARIO_FECHA G ON F.CD_CTR_FECHA = G.CD_CTR_FECHA

     -- A PARCELA QUE PARTICIPOU DO FECHAMENTO
     LEFT JOIN RC_CREDIARIO_PARC_RC_CREDIARIO_FECHA H ON G.CD_CTR_FECHA = H.CD_CTR_FECHA
     LEFT JOIN RC_CREDIARIO_PARC I ON H.CD_CTR = I.CD_CTR
     LEFT JOIN RC_CREDIARIO_PARC_PDV_VD J ON I.CD_CTR = J.CD_CTR
     LEFT JOIN PDV_VD VD1 ON J.CD_EMP = VD1.CD_EMP
                             AND J.CD_FILIAL = VD1.CD_FILIAL
                             AND J.CD_VD = VD1.CD_VD
     LEFT JOIN RC_DEB_DUP_NR_CTR ON RC.CD_EMP = RC_DEB_DUP_NR_CTR.CD_EMP
                                    AND RC.CD_FILIAL = RC_DEB_DUP_NR_CTR.CD_FILIAL
                                    AND RC.NR_DP = RC_DEB_DUP_NR_CTR.NR_DP
                                    AND RC.ANO = RC_DEB_DUP_NR_CTR.ANO
     LEFT JOIN RC_DEB_EST_NF_SAI ON RC_DEB_DUP_NR_CTR.CD_EMP = RC_DEB_EST_NF_SAI.CD_EMP
                                    AND RC_DEB_DUP_NR_CTR.CD_FILIAL = RC_DEB_EST_NF_SAI.CD_FILIAL
                                    AND RC_DEB_DUP_NR_CTR.NR_DP = RC_DEB_EST_NF_SAI.NR_DP
                                    AND RC_DEB_DUP_NR_CTR.ANO = RC_DEB_EST_NF_SAI.ANO
     LEFT JOIN EST_NF_SAI SAI1 ON RC_DEB_EST_NF_SAI.CD_EMP = SAI1.CD_EMP
                                  AND RC_DEB_EST_NF_SAI.CD_FILIAL = SAI1.CD_FILIAL
                                  AND RC_DEB_EST_NF_SAI.CD_NF = SAI1.CD_NF

     --1B DE 4 QUITACAO CREDIARIO - QUANDO PARCELA

     LEFT JOIN PDV_VD_DEV_NCC_LANC_RC_CREDIARIO_PARC_CRED C ON L.CD_CTR_LANC_NCC = C.CD_CTR_LANC_NCC
     LEFT JOIN RC_CREDIARIO_PARC_CRED ED ON C.CD_CTR = ED.CD_CTR
                                            AND C.CD_CTR_CRED = ED.CD_CTR_CRED
     LEFT JOIN RC_CREDIARIO_PARC PARC ON ED.CD_CTR = PARC.CD_CTR
     LEFT JOIN RC_CREDIARIO_PARC_PDV_VD PARC_PDV ON PARC_PDV.CD_CTR = ED.CD_CTR
     LEFT JOIN PDV_VD PDV_VD_CRED_PARC ON PARC_PDV.CD_EMP = PDV_VD_CRED_PARC.CD_EMP
                                          AND PARC_PDV.CD_FILIAL = PDV_VD_CRED_PARC.CD_FILIAL
                                          AND PARC_PDV.CD_VD = PDV_VD_CRED_PARC.CD_VD

     --2 DE 4 QUITACAO PARCELAS CONVENIO     
     LEFT JOIN PDV_VD_DEV_NCC_LANC_RC_VD_CONV_CRED D ON L.CD_CTR_LANC_NCC = D.CD_CTR_LANC_NCC

     -- VENDA DE ORIGEM
     LEFT JOIN RC_VD_CONV V ON D.CD_EMP = V.CD_EMP
                               AND D.CD_CTR = V.CD_CTR

     -- ENCONTRO O CUPOM QUITADO PELA NCC
     LEFT JOIN PDV_VD VD_CON ON V.CD_EMP = VD_CON.CD_EMP
                                AND V.CD_FILIAL = VD_CON.CD_FILIAL
                                AND V.CD_CX = VD_CON.CD_CX
                                AND V.NR_ECF = VD_CON.NR_ECF

     --3 DE 4 QUITACAO CARTAO
     -- QUANDO PDV_VD_DEV_NCC.STS_NCC = 5
     -- 4 - 4 QUITACAO DUPLICATA
     LEFT JOIN PDV_VD_DEV_NCC_RC_DEB B ON N.CD_CTR_NCC = B.CD_CTR_NCC
     LEFT JOIN RC_DEB E ON B.CD_FILIAL = E.CD_FILIAL
                           AND B.CD_RC_DEB = E.CD_RC_DEB