EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@NR	char	IN
@PREST	int	IN
@COMPRADOR	int	IN
@TIPO	char	IN
@CODCOLIGADA	int	IN

Definition

/*NATHAN DE PAULA RAMOS AMORIM - SA.2020.02_09 - OC.:DSAUGHFATF-6375 - 02/04/2020 VERSAO.: 12.1.28 - EQUIPE:INOV  */
CREATE PROCEDURE [dbo].[INITRESUMOGLOSA]
@NR CHAR(4),
@PREST INTEGER,
@COMPRADOR INTEGER,
@TIPO CHAR(1),
@CODCOLIGADA INTEGER
AS
DECLARE
@NCONTA CHAR(14),
@SCONTA INTEGER,
@SPARCIAL INTEGER,
@CONTA_TIPO CHAR(1)
BEGIN
  DECLARE CONTASREMESSA CURSOR FOR
   SELECT A.NUMEROCONTA, A.SEQUENCIALCONTA, A.SEQPARCIAL,
    CASE WHEN B.CODCOLIGADA IS NULL THEN 'R'
    ELSE 'N'
     END CONTA_TIPO
	FROM SZPARCIALATEND A (NOLOCK) LEFT JOIN SZATENDIMENTO B
	ON A.CODCOLIGADA = B.CODCOLIGADA AND
	   A.NUMEROCONTA = B.NUMEROCONTA AND
	   A.SEQUENCIALCONTA = B.SEQUENCIALCONTA
    WHERE A.CODCOLIGADA = @CODCOLIGADA AND A.CODCONVENIO = @COMPRADOR
    AND A.CODPRESTADOR = @PREST AND A.NUMEROREMESSA = @NR
    AND NOT EXISTS (SELECT * FROM SZATENDRESUMOGLOSA C
                    WHERE C.CODCOLIGADA = A.CODCOLIGADA AND C.NUMEROCONTA = A.NUMEROCONTA
                    AND C.SEQUENCIALCONTA = A.SEQUENCIALCONTA AND C.SEQPARCIAL = A.SEQPARCIAL)
  OPEN CONTASREMESSA
  FETCH NEXT FROM CONTASREMESSA INTO @NCONTA, @SCONTA, @SPARCIAL, @CONTA_TIPO
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    IF ( SELECT COUNT(*) FROM SZATENDRESUMO
         WHERE CODCOLIGADA = @CODCOLIGADA AND NUMEROCONTA = @NCONTA
         AND SEQUENCIALCONTA = @SCONTA AND SEQPARCIAL = @SPARCIAL ) <= 0
    BEGIN
      EXECUTE ATUALIZARESUMO @NCONTA, @SCONTA, @CONTA_TIPO, @CODCOLIGADA, @SPARCIAL
    END
    INSERT INTO SZATENDRESUMOGLOSA
    SELECT * FROM SZATENDRESUMO
    WHERE CODCOLIGADA = @CODCOLIGADA AND NUMEROCONTA = @NCONTA
    AND SEQUENCIALCONTA = @SCONTA AND SEQPARCIAL = @SPARCIAL
    INSERT INTO SZRESUMOCONTAPCTEG
    SELECT * FROM SZRESUMOCONTAPCTE
    WHERE CODCOLIGADA = @CODCOLIGADA AND NUMEROCONTA = @NCONTA
    AND SEQUENCIALCONTA = @SCONTA AND SEQPARCIAL = @SPARCIAL
    FETCH NEXT FROM CONTASREMESSA INTO @NCONTA, @SCONTA, @SPARCIAL, @CONTA_TIPO
  END
  CLOSE CONTASREMESSA
  DEALLOCATE CONTASREMESSA
END