|
|
Posted by Driesen via SQLMonster.com on 10/02/15 11:28
Hi Hugo
Thanks for the reply. I will try IF ISNUMERIC(@sellername) = 1. I have also
posted the complete procedure as well.
CREATE PROCEDURE dbo.UTL_CompletenessCheckLoan (@ApplicationId INT)
AS
DECLARE @ClientID INT,
@sellername varchar(40),
@BuilderName varchar(40)
SET NOCOUNT ON
CREATE TABLE #ERRORS(
RuleNumber INT,
ApplicationID INT,
ClientId INT,
SuccessInd CHAR(1))
--1
-- Loan Amount Not Captured
IF (SELECT LoanAmount FROM LoanDetail WHERE ApplicationId = @ApplicationId)
IS NULL OR
(SELECT LoanAmount FROM LoanDetail WHERE ApplicationId = @ApplicationId) =
0
INSERT INTO #ERRORS VALUES (1301,@ApplicationId,0,'N')
ELSE
INSERT INTO #ERRORS VALUES (1301,@ApplicationId,0,'Y')
--2
/*
IF (SELECT LoanAmount FROM LoanDetail WHERE ApplicationId = @ApplicationId) >
(SELECT PurchasePrice FROM LoanDetail WHERE ApplicationId = @ApplicationId)
INSERT INTO #ERRORS VALUES (1302,@ApplicationId,0,'N')
ELSE
INSERT INTO #ERRORS VALUES (1302,@ApplicationId,0,'Y')
*/
--3
-- Registered Amount must be the same or higher than the Loan Amount
IF (SELECT LoanAmount FROM LoanDetail WHERE ApplicationId = @ApplicationId)
<=
(SELECT RegistrationAmount FROM LoanDetail WHERE ApplicationId =
@ApplicationId)
INSERT INTO #ERRORS VALUES (1303,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1303,@ApplicationId,0,'N')
--4 Purchase date before today and after 2000
IF (SELECT PurchaseDate FROM LoanDetail WHERE ApplicationId = @ApplicationId)
BETWEEN
(SELECT '20000101') AND (select dateadd(day,-1,GETDATE()))
INSERT INTO #ERRORS VALUES (1304,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1304,@ApplicationId,0,'N')
--5
-- Deposit Details not Captured
IF (SELECT DepositInd FROM LoanDetail WHERE ApplicationId = @ApplicationId) =
'Y'
IF (SELECT (isnull(CashAmount,0) + isnull(SecurityAmount,0) + isnull
(SecurityBondsAmount,0) + isnull(SuretyshipAmount,0) + isnull(OtherAmount,0))
FROM LoanDetail WHERE ApplicationId = @ApplicationId) = 0
INSERT INTO #ERRORS VALUES (1305,@ApplicationId,0,'N')
ELSE
INSERT INTO #ERRORS VALUES (1305,@ApplicationId,0,'Y')
--6
-- Cost Details not captured
IF (SELECT AdditionalCostInd FROM LoanDetail WHERE ApplicationId =
@ApplicationId) = 'Y'
BEGIN
IF (SELECT (isnull(FurnishingsAmount,0) + isnull(TransferCosts,0) + isnull
(DepositCost,0) + isnull(OtherCost,0)) FROM LoanDetail WHERE ApplicationId =
@ApplicationId) > 0
INSERT INTO #ERRORS VALUES (1306,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1306,@ApplicationId,0,'N')
END
--7
-- The Repayment Day of the month must be between 1 and 31
if (SELECT RepaymentDayOfMonth FROM LoanDetail WHERE ApplicationId =
@ApplicationId) is not null
Begin
IF (SELECT RepaymentDayOfMonth FROM LoanDetail WHERE ApplicationId =
@ApplicationId) BETWEEN 1 AND 31
INSERT INTO #ERRORS VALUES (1307,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1307,@ApplicationId,0,'N')
End
--8
-- No Builder Details Captured
IF (SELECT LoanPurposeCode FROM LoanDetail WHERE ApplicationId =
@ApplicationId) = 2
BEGIN
IF (SELECT COUNT(*) FROM Builder WHERE ApplicationId = @ApplicationId) = 0
OR (SELECT CompletionDate FROM Builder WHERE ApplicationId = @ApplicationId)
is NULL
OR (SELECT BuilderPhoneCode FROM Builder WHERE ApplicationId =
@ApplicationId) is NULL
OR (SELECT BuilderPhone FROM Builder WHERE ApplicationId = @ApplicationId)
is NULL
OR (SELECT BuilderName FROM Builder WHERE ApplicationId = @ApplicationId)
is NULL
OR (SELECT BuildingContractAmount FROM Builder WHERE ApplicationId =
@ApplicationId) is NULL
INSERT INTO #ERRORS VALUES (1308,@ApplicationId,0,'N')
ELSE
INSERT INTO #ERRORS VALUES (1308,@ApplicationId,0,'Y')
END
--9
/*
IF (SELECT MarketValue FROM Property WHERE ApplicationId = @ApplicationId) <
(SELECT PurchasePrice FROM LoanDetail WHERE ApplicationId = @ApplicationId)
INSERT INTO #ERRORS VALUES (1309,@ApplicationId,0,'N')
ELSE
INSERT INTO #ERRORS VALUES (1309,@ApplicationId,0,'Y')
*/
--10
-- Client has an existing Homeloan but no Home Loan captured
SET @ClientId = 0
IF (SELECT ExistingHomeLoanInd FROM LoanDetail WHERE ApplicationId =
@ApplicationId) = 'Y'
BEGIN
SELECT @ClientId = ClientId FROM ApplicationClient WHERE ApplicationID =
@ApplicationId and ApplicationRoleCode = 1
IF (SELECT COUNT(*) FROM BankAccount BA JOIN ClientBankAccount CBA ON BA.
BankAccountId = CBA.BankAccountId
WHERE ClientId = @ClientId and AccountTypeId = 7) >= 1
INSERT INTO #ERRORS VALUES (1310,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1310,@ApplicationId,0,'N')
END
--11
-- If it is a Building Loan the Contract Amount must be captured
/*If (SELECT LoanPurposeCode FROM LoanDetail WHERE ApplicationID =
@ApplicationId) = 2
BEGIN
IF (SELECT BuildingContractAmount FROM Builder WHERE ApplicationID =
@ApplicationId) > 0
INSERT INTO #ERRORS VALUES (1311,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1311,@ApplicationId,0,'N')
END
*/
--12
-- If Property Type is Sectional Title then the Loan must not be a Building
Loan
/*
If (SELECT PropertyTypeCode FROM Property WHERE ApplicationID =
@ApplicationId) = 3
BEGIN
IF (SELECT LoanPurposeCode FROM LoanDetail WHERE ApplicationID =
@ApplicationId) <> 2
INSERT INTO #ERRORS VALUES (1312,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1312,@ApplicationId,0,'N')
END*/
--14
-- Complex Name not captured for a Sectional Title development
IF (SELECT PropertyTypeCode FROM Property WHERE ApplicationID =
@ApplicationId) = 3
BEGIN
IF (SELECT ComplexName FROM SectionalTitle WHERE ApplicationID =
@ApplicationId) IS NOT NULL
INSERT INTO #ERRORS VALUES (1314,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1314,@ApplicationId,0,'N')
END
--15
-- Surety Name and Surety Amount must be captured
IF (SELECT SuretyType FROM LoanDetail WHERE ApplicationID = @ApplicationId)
in (1,2)
BEGIN
IF ((SELECT SuretyshipDetails FROM LoanDetail WHERE ApplicationID =
@ApplicationId) IS NOT NULL
and (SELECT SuretyshipAmount FROM LoanDetail WHERE ApplicationID =
@ApplicationId) IS NOT NULL)
INSERT INTO #ERRORS VALUES (1315,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1315,@ApplicationId,0,'N')
END
--16
-- If repayment method is Debit Order then All Bank Details must be captured
IF (SELECT PaymentMethodCode FROM LoanDetail WHERE ApplicationID =
@ApplicationId) = 1
BEGIN
IF (SELECT PaymentBankAccountId FROM LoanDetail WHERE ApplicationID =
@ApplicationId) IS NOT NULL
INSERT INTO #ERRORS VALUES (1316,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1316,@ApplicationId,0,'N')
END
--17
--Check payment account is Current or Savings
if (select distinct PaymentBankAccountID from LoanDetail where ApplicationId
= @ApplicationId) is not null
Begin
if (select AccountTypeId from BankAccount BA join LoanDetail LD on BA.
BankAccountId = LD.PaymentBankAccountID where ApplicationId = @ApplicationId)
in (1,2)
INSERT INTO #ERRORS VALUES (1317, @ApplicationID, 0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1317, @ApplicationID, 0,'N')
End
--18
--if payment method is Debit Order then Payment freq must be captured
if (select PaymentMethodCode from LoanDetail where ApplicationId =
@ApplicationId) = 1
Begin
if (select isnumeric(InstalmentFrequency) from LoanDetail where ApplicationId
= @ApplicationId) = 1
INSERT INTO #ERRORS VALUES (1318,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1318,@ApplicationId,0,'N')
End
--19
--Sellers IdNo and DOB must Match
if (select len(SellerIdNo) from Seller where Applicationid = @ApplicationId)
> 0
Begin
if (select substring(convert(char(8),SellerDateOfBirth,112),3,6) from seller
where Applicationid = @ApplicationId) =
(select substring(convert(char(15),SellerIdNo),1,6) from seller where
Applicationid = @ApplicationId )
INSERT INTO #ERRORS VALUES (1319,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1319,@ApplicationId,0,'N')
End
--no special characters and numerics in builders/ contracters name - Driesen
--1411
select @BuilderName = buildername from builder where ApplicationId =
@ApplicationId
If @BuilderName is not null or @BuilderName <> ''
begin
if (select isnumeric(buildername) from builder where ApplicationId =
@ApplicationId) = 1
INSERT INTO #ERRORS VALUES (1411,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1411,@ApplicationId,0,'N')
end
--no special characters and numerics in sellers name - Driesen
--1412
select @sellername = sellername from Seller where ApplicationId =
@ApplicationId
--select applicationid = 1000054939
If @sellername is not null or @sellername <> ''
begin
if (select isnumeric(Sellername) from seller where ApplicationId =
@ApplicationId) = 1
INSERT INTO #ERRORS VALUES (1412,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1412,@ApplicationId,0,'N')
end
SELECT E.RuleNumber, E.ApplicationId, E.ClientId, CC.RuleDescription, CC.
ScreenName, Er.ErrorDescription, CC.BankID
FROM #Errors E
JOIN CompletenessCheck CC on E.RuleNumber = CC.RuleNumber
JOIN Error Er on CC.ErrorNumber = Er.ErrorNumber
WHERE SuccessInd = 'N'
RETURN 0
DROP TABLE #Errors
GO
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-general/200510/1
Navigation:
[Reply to this message]
|