IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[P_ESTORNO_RECEBIMENTO]') AND type IN(N'P', N'PC'))
    DROP PROCEDURE P_ESTORNO_RECEBIMENTO
GO

CREATE PROCEDURE P_ESTORNO_RECEBIMENTO(
                 @CD_CTR_PGTO INT
               , @CD_USU      INT
               , @HISTORICO   VARCHAR(255)
               , @OFFLINE     INT          = 0
)
AS
	-- EXEC P_ESTORNO_RECEBIMENTO 486, 1, 'TESTE SIMPLES'

    SET NOCOUNT ON;

    DECLARE @NCC_UTILIZADA INT;
    DECLARE @CD_CLI INT;

    -- TABELA TEMP RECEBIMETNO DE PARCELAS
    CREATE TABLE #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED
      (
         CD_EMP      INT,
         ANO         INT,
         CD_CTR      INT,
         CD_CTR_CRED INT,
         CD_BC_LANC  INT,
         CD_CHQ_LANC INT,
         CD_CHQ      INT
      )

    -- TABELA TEMP RECEBIMETNO DE PARCELAS
    CREATE TABLE #RS_PDV_VD_DEV_NCC
      (
         CD_CTR_NCC INT
      )

    -- TABELA TEMP RECEBIMETNO DE FATURA
    CREATE TABLE #RS_RC_PGTO_PDV_RC_CRED
      (
         CD_EMP      INT,
         CD_FILIAL   INT,
         CD_RC_DEB   INT,
         CD_DP_LANC  INT,
         CD_BC_LANC  INT,
         CD_CHQ_LANC INT,
         ANO         INT,
         CD_CHQ      INT
      )

    SET @NCC_UTILIZADA = (SELECT Count(1)
                          FROM   RC_CREDIARIO_PARC_PDV_VD_DEV_NCC A
                                 INNER JOIN PDV_VD_DEV_NCC B
                                         ON A.CD_CTR_NCC = B.CD_CTR_NCC
                          WHERE  A.CD_CTR_PGTO = @CD_CTR_PGTO
                                 AND B.STS_NCC > 0)
    SET @NCC_UTILIZADA = @NCC_UTILIZADA
                         + (SELECT Count(1)
                            FROM   RC_DEB_PDV_VD_DEV_NCC A
                                   INNER JOIN PDV_VD_DEV_NCC B
                                           ON A.CD_CTR_NCC = B.CD_CTR_NCC
                            WHERE  A.CD_CTR_PGTO = @CD_CTR_PGTO
                                   AND B.STS_NCC > 0)

    IF @NCC_UTILIZADA > 0
      BEGIN
          RAISERROR(N'Extorno não permitido pois NCC para o controle de pgamento %s já utilizado',18,-- Severity.
                    1,-- State.
                    @CD_CTR_PGTO)

          RETURN
      END
    ELSE
      BEGIN
          SET @CD_CLI = COALESCE((SELECT TOP 1 B.CD_CLI
                                  FROM   RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED A
                                         INNER JOIN RC_CREDIARIO_PARC B
                                                 ON A.CD_CTR = B.CD_CTR
                                  WHERE  A.CD_CTR_PGTO = @CD_CTR_PGTO), 0)

          IF @CD_CLI = 0
            BEGIN
                SET @CD_CLI = COALESCE((SELECT TOP 1 B.CD_CLI
                                        FROM   RC_PGTO_PDV_RC_CRED A
                                               INNER JOIN RC_DEB B
                                                       ON A.CD_EMP = B.CD_EMP
                                                          AND A.CD_FILIAL = B.CD_FILIAL
                                                          AND A.CD_RC_DEB = B.CD_RC_DEB
                                        WHERE  A.CD_CTR_PGTO = @CD_CTR_PGTO), 0)
            END

          -- EXECUANTO BAIXA
          INSERT INTO #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED
          SELECT D.CD_EMP,
                 E.ANO,
                 A.CD_CTR,
                 A.CD_CTR_CRED,
                 C.CD_BC_LANC,
                 E.CD_CHQ_LANC,
                 G.CD_CHQ
          FROM   RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED A
                 INNER JOIN RC_CREDIARIO_PARC_CRED B
                         ON A.CD_CTR = B.CD_CTR
                            AND A.CD_CTR_CRED = B.CD_CTR_CRED
                 INNER JOIN RC_CREDIARIO_PARC_CRED_BC_LANC C
                         ON B.CD_CTR = C.CD_CTR
                            AND B.CD_CTR_CRED = C.CD_CTR_CRED
                 INNER JOIN BC_LANC D
                         ON C.CD_EMP = D.CD_EMP
                            AND C.CD_BC_LANC = D.CD_BC_LANC
                 LEFT JOIN BC_LANC_CHQ_LANC E
                        ON D.CD_EMP = E.CD_EMP
                           AND D.CD_BC_LANC = E.CD_BC_LANC
                 LEFT JOIN CHQ_LANC F
                        ON E.CD_EMP = F.CD_EMP
                           AND E.ANO = F.ANO
                           AND E.CD_CHQ_LANC = F.CD_CHQ_LANC
                 LEFT JOIN CHQ_LANC_CHQ_LANC_CPL G
                        ON F.CD_EMP = G.CD_EMP
                           AND F.ANO = G.ANO
                           AND F.CD_CHQ_LANC = G.CD_CHQ_LANC
                 LEFT JOIN CHQ_LANC_CPL H
                        ON G.CD_EMP = H.CD_EMP
                           AND G.CD_CHQ = H.CD_CHQ
                 LEFT JOIN CHQ_LANC_CPL_RC_CARTAO_PROPRIO I
                        ON H.CD_EMP = I.CD_EMP
                           AND H.CD_CHQ = I.CD_CHQ
          WHERE  A.CD_CTR_PGTO = @CD_CTR_PGTO

          INSERT INTO #RS_RC_PGTO_PDV_RC_CRED
          SELECT A.CD_EMP,
                 A.CD_FILIAL,
                 A.CD_RC_DEB,
                 A.CD_DP_LANC,
                 C.CD_BC_LANC,
                 E.CD_CHQ_LANC,
                 F.ANO,
                 G.CD_CHQ
          FROM   RC_PGTO_PDV_RC_CRED A
                 INNER JOIN RC_CRED B
                         ON A.CD_EMP = B.CD_EMP
                            AND A.CD_FILIAL = B.CD_FILIAL
                            AND A.CD_RC_DEB = B.CD_RC_DEB
                            AND A.CD_DP_LANC = B.CD_DP_LANC
                 INNER JOIN RC_CRED_BC_LANC C
                         ON B.CD_EMP = C.CD_EMP
                            AND B.CD_FILIAL = C.CD_FILIAL
                            AND B.CD_RC_DEB = C.CD_RC_DEB
                            AND B.CD_DP_LANC = C.CD_DP_LANC
                 INNER JOIN BC_LANC D
                         ON C.CD_EMP = D.CD_EMP
                            AND C.CD_BC_LANC = D.CD_BC_LANC
                 LEFT JOIN BC_LANC_CHQ_LANC E
                        ON D.CD_EMP = E.CD_EMP
                           AND D.CD_BC_LANC = E.CD_BC_LANC
                 LEFT JOIN CHQ_LANC F
                        ON E.CD_EMP = F.CD_EMP
                           AND E.ANO = F.ANO
                           AND E.CD_CHQ_LANC = F.CD_CHQ_LANC
                 LEFT JOIN CHQ_LANC_CHQ_LANC_CPL G
                        ON F.CD_EMP = G.CD_EMP
                           AND F.ANO = G.ANO
                           AND F.CD_CHQ_LANC = G.CD_CHQ_LANC
                 LEFT JOIN CHQ_LANC_CPL H
                        ON G.CD_EMP = H.CD_EMP
                           AND G.CD_CHQ = H.CD_CHQ
                 LEFT JOIN CHQ_LANC_CPL_RC_CARTAO_PROPRIO I
                        ON H.CD_EMP = I.CD_EMP
                           AND H.CD_CHQ = I.CD_CHQ
          WHERE  A.CD_CTR_PGTO = @CD_CTR_PGTO

          INSERT INTO #RS_PDV_VD_DEV_NCC
          SELECT B.CD_CTR_NCC
          FROM   RC_DEB_PDV_VD_DEV_NCC A
                 INNER JOIN PDV_VD_DEV_NCC B
                         ON A.CD_CTR_NCC = B.CD_CTR_NCC
          WHERE  A.CD_CTR_PGTO = @CD_CTR_PGTO
                 AND NOT A.CD_CTR_NCC IS NULL;

          INSERT INTO #RS_PDV_VD_DEV_NCC
          SELECT B.CD_CTR_NCC
          FROM   RC_CREDIARIO_PARC_PDV_VD_DEV_NCC A
                 INNER JOIN PDV_VD_DEV_NCC B
                         ON A.CD_CTR_NCC = B.CD_CTR_NCC
          WHERE  A.CD_CTR_PGTO = @CD_CTR_PGTO
                 AND NOT A.CD_CTR_NCC IS NULL;

          INSERT INTO #RS_PDV_VD_DEV_NCC
          SELECT B.CD_CTR_NCC
          FROM   RC_DEB_PDV_VD_DEV_NCC A
                 INNER JOIN PDV_VD_DEV_NCC B
                         ON A.CD_CTR_NCC = B.CD_CTR_NCC
          WHERE  A.CD_CTR_PGTO = @CD_CTR_PGTO
                 AND NOT A.CD_CTR_NCC IS NULL;

          -- EXCLUINDO NCC
          DELETE A
          FROM   RC_DEB_PDV_VD_DEV_NCC A
          WHERE  A.CD_CTR_PGTO = @CD_CTR_PGTO;

          DELETE B
          FROM   #RS_PDV_VD_DEV_NCC A
                 INNER JOIN PDV_VD_DEV_NCC B
                         ON A.CD_CTR_NCC = B.CD_CTR_NCC;

          DELETE A
          FROM   RC_CREDIARIO_PARC_PDV_VD_DEV_NCC A
          WHERE  A.CD_CTR_PGTO = @CD_CTR_PGTO;

          DELETE B
          FROM   #RS_PDV_VD_DEV_NCC A
                 INNER JOIN PDV_VD_DEV_NCC B
                         ON A.CD_CTR_NCC = B.CD_CTR_NCC;

          -- EXCLUINDO PARCELAS 
          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED TMP
                 INNER JOIN CHQ_LANC_CHQ_LANC_CPL DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.ANO = DELETED.ANO
                            AND TMP.CD_CHQ_LANC = DELETED.CD_CHQ_LANC;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED TMP
                 INNER JOIN CHQ_LANC_CPL_RC_CARTAO_PROPRIO DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_CHQ = DELETED.CD_CHQ;

		  DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED TMP
                 INNER JOIN PDV_VD_CHQS_CHQ_LANC DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_CHQ = DELETED.CD_CHQ;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED TMP
                 INNER JOIN CHQ_LANC_CPL DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_CHQ = DELETED.CD_CHQ;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED TMP
                 INNER JOIN BC_LANC_CHQ_LANC DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_BC_LANC = DELETED.CD_BC_LANC;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED TMP
                 INNER JOIN CHQ_LANC DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.ANO = DELETED.ANO
                            AND TMP.CD_CHQ_LANC = DELETED.CD_CHQ_LANC;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED TMP
                 INNER JOIN RC_CREDIARIO_PARC_CRED_BC_LANC DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_BC_LANC = DELETED.CD_BC_LANC
                            AND TMP.CD_CTR_CRED = DELETED.CD_CTR_CRED;


          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED TMP
                 INNER JOIN BC_LANC DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_BC_LANC = DELETED.CD_BC_LANC;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED TMP
                 INNER JOIN RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED DELETED -- DELETANDO AQUI
                         ON TMP.CD_CTR_CRED = DELETED.CD_CTR_CRED AND TMP.CD_CTR = DELETED.CD_CTR;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CREDIARIO_PARC_CRED TMP
                 INNER JOIN RC_CREDIARIO_PARC_CRED DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_CTR_CRED = DELETED.CD_CTR_CRED
                            AND TMP.CD_CTR = DELETED.CD_CTR;

          -- EXCLUINDO FATURAS 
          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CRED TMP
                 INNER JOIN CHQ_LANC_CHQ_LANC_CPL DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.ANO = DELETED.ANO
                            AND TMP.CD_CHQ_LANC = DELETED.CD_CHQ_LANC;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CRED TMP
                 INNER JOIN CHQ_LANC_CPL_RC_CARTAO_PROPRIO DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_CHQ = DELETED.CD_CHQ;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CRED TMP
                 INNER JOIN CHQ_LANC_CPL DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_CHQ = DELETED.CD_CHQ;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CRED TMP
                 INNER JOIN BC_LANC_CHQ_LANC DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_BC_LANC = DELETED.CD_BC_LANC;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CRED TMP
                 INNER JOIN CHQ_LANC DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.ANO = DELETED.ANO
                            AND TMP.CD_CHQ_LANC = DELETED.CD_CHQ_LANC;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CRED TMP
                 INNER JOIN RC_CRED_BC_LANC DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_FILIAL = DELETED.CD_FILIAL
                            AND TMP.CD_RC_DEB = DELETED.CD_RC_DEB
                            AND TMP.CD_DP_LANC = DELETED.CD_DP_LANC;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CRED TMP
                 INNER JOIN BC_LANC DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_BC_LANC = DELETED.CD_BC_LANC;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CRED TMP
                 INNER JOIN RC_PGTO_PDV_RC_CRED DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_FILIAL = DELETED.CD_FILIAL
                            AND TMP.CD_RC_DEB = DELETED.CD_RC_DEB
                            AND TMP.CD_DP_LANC = DELETED.CD_DP_LANC;

          DELETE DELETED
          FROM   #RS_RC_PGTO_PDV_RC_CRED TMP
                 INNER JOIN RC_CRED DELETED -- DELETANDO AQUI
                         ON TMP.CD_EMP = DELETED.CD_EMP
                            AND TMP.CD_FILIAL = DELETED.CD_FILIAL
                            AND TMP.CD_RC_DEB = DELETED.CD_RC_DEB
                            AND TMP.CD_DP_LANC = DELETED.CD_DP_LANC;

							
		  IF @OFFLINE = 0
		      BEGIN
			  
			  UPDATE RC_PGTO_PDV
				  SET    STS_PGTO = 1,
						 CD_USU = @CD_USU,
						 HIST_PGTO = @HISTORICO,
						 DT_ULT_ALT = CONVERT(CHAR, Getdate(), 112),
						 HR_ULT_ALT = CONVERT(CHAR, Getdate(), 108)
				  WHERE  CD_CTR_PGTO = @CD_CTR_PGTO	;		  
			  
			  END
		  ELSE
			  BEGIN			  
			  
			    DELETE RC_PGTO_PDV_PENDENTE WHERE  CD_CTR_PGTO = @CD_CTR_PGTO;
				
				DELETE PDV_CUPOM_NAO_FISCAL_RC_PGTO_PDV WHERE  CD_CTR_PGTO = @CD_CTR_PGTO;
				
				DELETE RC_PGTO_PDV_ITENS_PAGOS WHERE  CD_CTR_PGTO = @CD_CTR_PGTO;
				
				DELETE RC_PGTO_PDV WHERE  CD_CTR_PGTO = @CD_CTR_PGTO;	
			  
			  END;

      END;


