Advertisement
Guest User

Untitled

a guest
May 23rd, 2019
65
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 3.57 KB | None | 0 0
  1. USE [BSIIDPOSITIVA]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[IdPositiva_ObtemResposta_AgenciaCliente]    Script Date: 23/05/2019 17:00:09 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER PROCEDURE [dbo].[IdPositiva_ObtemResposta_AgenciaCliente] @cpfCli VARCHAR(14)
  9. AS
  10. BEGIN
  11.  
  12.     DECLARE @AUX_CPF VARCHAR(14);
  13.     SET @AUX_CPF = REPLICATE('0', 11 - LEN(@cpfCli)) + @cpfCli;
  14.     SET @AUX_CPF = SUBSTRING(@AUX_CPF, 1, 3) + '.' + SUBSTRING(@AUX_CPF, 4, 3) + '.' + SUBSTRING(@AUX_CPF, 7, 3) + '-' + SUBSTRING(@AUX_CPF, 10, 2);
  15.  
  16.     DECLARE @AUX_CPFCRK VARCHAR(15);
  17.     SET @AUX_CPFCRK = REPLICATE('0', 15 - LEN(@cpfCli)) + @cpfCli;
  18.  
  19.     DECLARE @CPF_NUM NUMERIC(15);
  20.     SET @CPF_NUM = CONVERT(NUMERIC, REPLACE(REPLACE(@AUX_CPF, '.', ''), '-', ''));
  21.  
  22.     DECLARE @AGENCIA VARCHAR(250);
  23.  
  24.     SELECT @AGENCIA = SUBSTRING(CODAGEFVC, 1, 4)
  25.     FROM SCCDEM..Pagto_CRK PCRK WITH (NOLOCK)
  26.         LEFT JOIN SCCDEM..TED01 TED WITH (NOLOCK)           ON TED.CNPJ_CPF = PCRK.CODCGCCPFFVC AND TED.CONTRATO_ID = PCRK.NUMCTT
  27.         INNER JOIN DM01.dbo.EMPRESTIMO AS E WITH (NOLOCK)   ON E.CNPJ_CPF = PCRK.CODCGCCPFCLI  AND NUMCTT = NRO_CONTRATO
  28.     WHERE CODCGCCPFFVC = @AUX_CPFCRK
  29.           AND CODFRMLQD = 30
  30.           AND PCRK.DATLQDOPE =
  31.           (
  32.               SELECT MAX(ICRK.DATLQDOPE)
  33.               FROM SCCDEM..Pagto_CRK ICRK WITH (NOLOCK)
  34.               WHERE ICRK.CODCGCCPFFVC = PCRK.CODCGCCPFFVC
  35.                     AND CODFRMLQD = 30
  36.           )
  37.           AND TED.CNPJ_CPF IS NULL
  38.           AND E.STATUS = 'A'
  39.           AND CHARINDEX('-', CODCNTFVC) > 0
  40.           AND (ISNULL(PCRK.NUMBCOFVC, 0) <> 0)
  41.           AND (ISNULL(NULLIF(LTRIM(RTRIM(PCRK.CODAGEFVC)), ''), '0') <> '0')
  42.           AND (ISNULL(NULLIF(LTRIM(RTRIM(PCRK.CODCNTFVC)), ''), '0') <> '0')
  43.     ORDER BY NUMOPECKR DESC;
  44.  
  45.     IF (@AGENCIA IS NULL OR LTRIM(RTRIM(@AGENCIA)) = '')
  46.     BEGIN
  47.         SELECT @AGENCIA = SUBSTRING(CONVERT(VARCHAR, AGECRED), 1, 4)
  48.         FROM SCCDEM..FT01 FT01 WITH (NOLOCK)
  49.             LEFT JOIN SCCDEM..TED01 TED WITH (NOLOCK)          ON TED.CNPJ_CPF = FT01.CNPJ_CPF AND TED.CONTRATO_ID = FT01.CONTRATO_ID
  50.             INNER JOIN DM01.dbo.EMPRESTIMO AS E WITH (NOLOCK)  ON E.CNPJ_CPF = FT01.CNPJ_CPF AND E.NRO_CONTRATO = FT01.CONTRATO_ID
  51.         WHERE FT01.CNPJ_CPF = @CPF_NUM
  52.               AND TED.CNPJ_CPF IS NULL
  53.               AND E.STATUS = 'A'
  54.               AND (ISNULL(FT01.BCOCRED, 0) <> 0)
  55.               AND (ISNULL(FT01.AGECRED, 0) <> 0)
  56.               AND (ISNULL(NULLIF(LTRIM(RTRIM(FT01.CTACRED)), ''), '0') <> '0')
  57.         ORDER BY FT01.DATA_EXPORT_CPAGAR DESC;
  58.     END;
  59.     IF (@AGENCIA IS NULL OR LTRIM(RTRIM(@AGENCIA)) = '')
  60.     BEGIN
  61.         SELECT @AGENCIA = SUBSTRING(DCDSTAGE, 1, 4)
  62.          FROM BSOAUTORIZ..CCLIP WITH (NOLOCK)
  63.              INNER JOIN BSOAUTORIZ..CPROP WITH (NOLOCK)                 ON PPCODCLI = CLCODCLI
  64.              INNER JOIN BSOAUTORIZ..TPARA WITH (NOLOCK)                 ON PMCODPRD = PPCODCONV
  65.              INNER JOIN BSOAUTORIZ..CMOVP WITH (NOLOCK)                 ON MPNRPROP = PPNRPROP
  66.              INNER JOIN BSOAUTORIZ..CDOCP WITH (NOLOCK)                 ON DCNRPROP = PPNRPROP
  67.          WHERE (PPDTBASE > GETDATE() - 1080)
  68.                AND CLCGC = @AUX_CPF
  69.                AND PMTPOPER <> '08' --CARTÃO
  70.                AND PMTPOPER <> '13' -- PORTABILIDADE
  71.                AND MPSIT <> 'REP'
  72.                AND (ISNULL(NULLIF(LTRIM(RTRIM(DCDSTBCO)), ''), '0') <> '0')
  73.                AND (ISNULL(NULLIF(LTRIM(RTRIM(DCDSTAGE)), ''), '0') <> '0')
  74.                AND (ISNULL(NULLIF(LTRIM(RTRIM(DCDSTCC)), ''), '0') <> '0')
  75.         ORDER BY PPDTBASE DESC;
  76.     END;   
  77.     SELECT LTRIM(RTRIM(@AGENCIA)) AS RESPOSTA;
  78.  
  79. END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement