
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]
AS
     SELECT N.CD_EMP,
            CASE
                WHEN R.CD_CTR_LANC_NCC IS NOT NULL
                THEN CONVERT(VARCHAR(50), R.CD_FILIAL)
                ELSE CASE
                         WHEN VD1.NR_ECF IS NULL
                         THEN CONVERT(VARCHAR(50), VD1.CD_FILIAL)
                         ELSE CASE
                                  WHEN C.CD_CTR_LANC_NCC IS NOT NULL
                                  THEN CONVERT(VARCHAR(50), PDV_VD_CRED_PARC.CD_FILIAL)
                                  ELSE CASE
                                           WHEN D.CD_CTR_LANC_NCC IS NOT NULL
                                           THEN CONVERT(VARCHAR(50), VD_CON.CD_FILIAL)
                                           ELSE CASE
                                                    WHEN B.CD_CTR_NCC IS NOT NULL
                                                    THEN '0'
                                                END
                                       END
                              END
                     END
            END 'CD_FILIAL', 
            L.CD_CTR_LANC_NCC LOTE, 
            P.CD_CTR_NCC, 
            N.VLR_NCC, 
            L.DT_CAD DT_QUITACAO,
            CASE
                WHEN R.CD_CTR_LANC_NCC IS NOT NULL
                THEN CONVERT(VARCHAR(50), R.CD_RC_DEB)
                ELSE CASE
                         WHEN C.CD_CTR_LANC_NCC IS NOT NULL
                         THEN CONVERT(VARCHAR(50), C.CD_CTR)
                         ELSE CASE
                                  WHEN D.CD_CTR_LANC_NCC IS NOT NULL
                                  THEN CONVERT(VARCHAR(50), D.CD_CTR)
                                  ELSE CASE
                                           WHEN B.CD_CTR_NCC IS NOT NULL
                                           THEN CONVERT(VARCHAR(50), 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 NULL
                     --THEN 'ASD'
                         THEN CONVERT(VARCHAR(50), EST_NF_SAI.NF_NF)--  EST_NF_SAI.NF_NF)))
                         ELSE CASE
                                  WHEN C.CD_CTR_LANC_NCC IS NOT NULL
                                  THEN CONVERT(VARCHAR(50), PDV_VD_CRED_PARC.NR_ECF)
                                  ELSE CASE
                                           WHEN D.CD_CTR_LANC_NCC IS NOT NULL
                                           THEN CONVERT(VARCHAR(50), VD_CON.NR_ECF)
                                           ELSE CASE
                                                    WHEN B.CD_CTR_NCC IS NOT NULL
                                                    THEN '0'
                                                END
                                       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 DUPLICATA
          LEFT JOIN PDV_VD_DEV_NCC_LANC_RC_CRED R ON L.CD_CTR_LANC_NCC = R.CD_CTR_LANC_NCC
          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
          LEFT JOIN RC_DEB_PDV_VD DEB ON RC.CD_EMP = DEB.CD_EMP
                                         AND RC.CD_FILIAL = DEB.CD_FILIAL
                                         AND RC.NR_DP = DEB.NR_DP
                                         AND RC.ANO = DEB.ANO
          LEFT JOIN PDV_VD VD1 ON DEB.CD_EMP = VD1.CD_EMP
                                  AND DEB.CD_FILIAL = VD1.CD_FILIAL
                                  AND DEB.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 ON RC_DEB_EST_NF_SAI.CD_EMP = EST_NF_SAI.CD_EMP
                                  AND RC_DEB_EST_NF_SAI.CD_FILIAL = EST_NF_SAI.CD_FILIAL
                                  AND RC_DEB_EST_NF_SAI.CD_NF = EST_NF_SAI.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 = P.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.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
          LEFT JOIN RC_VD_CONV V ON D.CD_CTR = V.CD_CTR

          -- ENCONTRO O CUPOM QUITADO PELA NCC
          LEFT JOIN PDV_VD_RC_VD_CONV O ON V.CD_EMP = O.CD_EMP
                                           AND V.CD_CTR = O.CD_CTR
          LEFT JOIN PDV_VD VD_CON ON O.CD_EMP = VD_CON.CD_EMP
                                     AND O.CD_FILIAL = VD_CON.CD_FILIAL
                                     AND O.CD_VD = VD_CON.CD_VD

          --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;