Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
/****** Object: StoredProcedure [dbo].[procedureName] Script Date: 09/24/2011 14:58:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[procedureName] ( @RoutingNumber char(9), @BankAcctNo char(17) ) AS SET NOCOUNT ON; /* Allows for the denial check. IF any check fails, 1 is returned to the front end, the process terminates. IF all checks succeed, 0 is returned to the front end. 1 = Fail, 0 = Pass EXEC dbo.procedureName @RoutingNumber = '072000096',@BankAcctNo = '6820138904' Auth: Me Date: 9/14/2011 THREE CHECKS: 1. Be the same Routing number/Bank Account number associated with a loan in collection status. 2. Be associated with a loan app no that contains the same Routing number/Bank account number with a denial dode of 5 that occurred in the last 45 calendar days. 3. Be associated with a customer who has a loan balance > 0 using the same Routing number/bank account combination. */ BEGIN DECLARE @Status1 TABLE (Status CHAR(1), PRIMARY KEY (Status)) INSERT @Status1 VALUES ('B'),('C'),('R'),('N'),('O'),('G'); DECLARE @Status2 TABLE (Status CHAR(1), PRIMARY KEY (Status)) INSERT @Status2 VALUES ('N'),('O'),('P'),('D'),('B'),('R'),('C'),('G'),('V'); /* Check #1 */ IF EXISTS(SELECT 1 FROM dbo.LoanTable l WITH (NOLOCK) JOIN dbo.CustomerTable c WITH (NOLOCK) ON l.CustID = c.CustID WHERE c.RoutingNo = @RoutingNumber AND c.AcctNo = @BankAcctNo AND EXISTS(SELECT 1 FROM @Status1 s1 WHERE l.Status = s1.Status)) BEGIN SELECT '1'; END /* Check #2 */ IF EXISTS(SELECT 1 FROM dbo.LoanTable l WITH (NOLOCK) JOIN dbo.CustomerTable c WITH (NOLOCK) ON l.CustID = c.CustID WHERE l.DateEntered >= DATEADD(day,-45,GETDATE()) AND l.DenialCode = 5 AND c.RoutingNo = @RoutingNumber AND c.AcctNo = @BankAcctNo) BEGIN SELECT '1'; END /* Check #3 */ IF EXISTS( SELECT 1 FROM ( SELECT CAST(la.LoanAmt AS DECIMAL) + CAST(la.FinCharge AS DECIMAL) - SUM(CAST(COALESCE(p.PmtAmt, 0) AS DECIMAL)) AS Balance FROM dbo.LoanTable la WITH (NOLOCK) LEFT JOIN dbo.Payments p WITH (NOLOCK) ON la.ApplNo = p.ApplNo JOIN dbo.CustomerTable c WITH (NOLOCK) ON la.CustID = c.CustID WHERE la.Originated = 1 AND EXISTS(SELECT 1 FROM @Status2 s2 WHERE s2.Status = la.Status) GROUP BY la.LoanAmt,la.FinCharge) x HAVING SUM(x.Balance) > 0) BEGIN SELECT '1'; END IF @@ERROR <> 0 BEGIN RAISERROR('Failed to perform the denial check.',16,1) RETURN; END ELSE BEGIN SELECT '0'; END END SET NOCOUNT OFF; GO
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.