Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [BSIIDPOSITIVA]
- GO
- /****** Object: StoredProcedure [dbo].[IdPositiva_ObtemResposta_AgenciaCliente] Script Date: 23/05/2019 17:00:09 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER PROCEDURE [dbo].[IdPositiva_ObtemResposta_AgenciaCliente] @cpfCli VARCHAR(14)
- AS
- BEGIN
- DECLARE @AUX_CPF VARCHAR(14);
- SET @AUX_CPF = REPLICATE('0', 11 - LEN(@cpfCli)) + @cpfCli;
- SET @AUX_CPF = SUBSTRING(@AUX_CPF, 1, 3) + '.' + SUBSTRING(@AUX_CPF, 4, 3) + '.' + SUBSTRING(@AUX_CPF, 7, 3) + '-' + SUBSTRING(@AUX_CPF, 10, 2);
- DECLARE @AUX_CPFCRK VARCHAR(15);
- SET @AUX_CPFCRK = REPLICATE('0', 15 - LEN(@cpfCli)) + @cpfCli;
- DECLARE @CPF_NUM NUMERIC(15);
- SET @CPF_NUM = CONVERT(NUMERIC, REPLACE(REPLACE(@AUX_CPF, '.', ''), '-', ''));
- DECLARE @AGENCIA VARCHAR(250);
- SELECT @AGENCIA = SUBSTRING(CODAGEFVC, 1, 4)
- FROM SCCDEM..Pagto_CRK PCRK WITH (NOLOCK)
- LEFT JOIN SCCDEM..TED01 TED WITH (NOLOCK) ON TED.CNPJ_CPF = PCRK.CODCGCCPFFVC AND TED.CONTRATO_ID = PCRK.NUMCTT
- INNER JOIN DM01.dbo.EMPRESTIMO AS E WITH (NOLOCK) ON E.CNPJ_CPF = PCRK.CODCGCCPFCLI AND NUMCTT = NRO_CONTRATO
- WHERE CODCGCCPFFVC = @AUX_CPFCRK
- AND CODFRMLQD = 30
- AND PCRK.DATLQDOPE =
- (
- SELECT MAX(ICRK.DATLQDOPE)
- FROM SCCDEM..Pagto_CRK ICRK WITH (NOLOCK)
- WHERE ICRK.CODCGCCPFFVC = PCRK.CODCGCCPFFVC
- AND CODFRMLQD = 30
- )
- AND TED.CNPJ_CPF IS NULL
- AND E.STATUS = 'A'
- AND CHARINDEX('-', CODCNTFVC) > 0
- AND (ISNULL(PCRK.NUMBCOFVC, 0) <> 0)
- AND (ISNULL(NULLIF(LTRIM(RTRIM(PCRK.CODAGEFVC)), ''), '0') <> '0')
- AND (ISNULL(NULLIF(LTRIM(RTRIM(PCRK.CODCNTFVC)), ''), '0') <> '0')
- ORDER BY NUMOPECKR DESC;
- IF (@AGENCIA IS NULL OR LTRIM(RTRIM(@AGENCIA)) = '')
- BEGIN
- SELECT @AGENCIA = SUBSTRING(CONVERT(VARCHAR, AGECRED), 1, 4)
- FROM SCCDEM..FT01 FT01 WITH (NOLOCK)
- LEFT JOIN SCCDEM..TED01 TED WITH (NOLOCK) ON TED.CNPJ_CPF = FT01.CNPJ_CPF AND TED.CONTRATO_ID = FT01.CONTRATO_ID
- INNER JOIN DM01.dbo.EMPRESTIMO AS E WITH (NOLOCK) ON E.CNPJ_CPF = FT01.CNPJ_CPF AND E.NRO_CONTRATO = FT01.CONTRATO_ID
- WHERE FT01.CNPJ_CPF = @CPF_NUM
- AND TED.CNPJ_CPF IS NULL
- AND E.STATUS = 'A'
- AND (ISNULL(FT01.BCOCRED, 0) <> 0)
- AND (ISNULL(FT01.AGECRED, 0) <> 0)
- AND (ISNULL(NULLIF(LTRIM(RTRIM(FT01.CTACRED)), ''), '0') <> '0')
- ORDER BY FT01.DATA_EXPORT_CPAGAR DESC;
- END;
- IF (@AGENCIA IS NULL OR LTRIM(RTRIM(@AGENCIA)) = '')
- BEGIN
- SELECT @AGENCIA = SUBSTRING(DCDSTAGE, 1, 4)
- FROM BSOAUTORIZ..CCLIP WITH (NOLOCK)
- INNER JOIN BSOAUTORIZ..CPROP WITH (NOLOCK) ON PPCODCLI = CLCODCLI
- INNER JOIN BSOAUTORIZ..TPARA WITH (NOLOCK) ON PMCODPRD = PPCODCONV
- INNER JOIN BSOAUTORIZ..CMOVP WITH (NOLOCK) ON MPNRPROP = PPNRPROP
- INNER JOIN BSOAUTORIZ..CDOCP WITH (NOLOCK) ON DCNRPROP = PPNRPROP
- WHERE (PPDTBASE > GETDATE() - 1080)
- AND CLCGC = @AUX_CPF
- AND PMTPOPER <> '08' --CARTÃO
- AND PMTPOPER <> '13' -- PORTABILIDADE
- AND MPSIT <> 'REP'
- AND (ISNULL(NULLIF(LTRIM(RTRIM(DCDSTBCO)), ''), '0') <> '0')
- AND (ISNULL(NULLIF(LTRIM(RTRIM(DCDSTAGE)), ''), '0') <> '0')
- AND (ISNULL(NULLIF(LTRIM(RTRIM(DCDSTCC)), ''), '0') <> '0')
- ORDER BY PPDTBASE DESC;
- END;
- SELECT LTRIM(RTRIM(@AGENCIA)) AS RESPOSTA;
- END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement