You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
70 lines
2.5 KiB
70 lines
2.5 KiB
ALTER PROC proc_riskBasedAssessementRpt(
|
|
@flag VARCHAR(50) = NULL
|
|
,@user VARCHAR(50) = NULL
|
|
,@country VARCHAR(50) = NULL
|
|
,@idNumber VARCHAR(50) = NULL
|
|
,@risk VARCHAR(50) = NULL
|
|
,@customerId VARCHAR(50) = NULL
|
|
)AS
|
|
BEGIN
|
|
IF @flag = 'sumRpt'
|
|
BEGIN
|
|
|
|
DECLARE @MinVal INT = 0, @MaxVal INT = 10
|
|
|
|
DECLARE @tblRisk TABLE (minValue INT, maxValue INT, risklevel VARCHAR(4))
|
|
INSERT INTO @tblRisk(minValue,maxValue,risklevel)
|
|
SELECT 1,4, 'L'
|
|
UNION ALL
|
|
SELECT 5,6, 'M'
|
|
UNION ALL
|
|
SELECT 7,8, 'H'
|
|
UNION ALL
|
|
SELECT 9,10, 'VH'
|
|
|
|
|
|
SELECT @MinVal = minValue, @MaxVal = maxValue FROM @tblRisk WHERE risklevel = @risk
|
|
|
|
|
|
|
|
SELECT
|
|
ROW_NUMBER() OVER (ORDER BY customerId ASC) AS [No]
|
|
,customerId
|
|
,firstName AS [Name]
|
|
,idType
|
|
,idNumber
|
|
,ISNULL(RiskScore,0) AS WeightedRisk
|
|
FROM dbo.customerMaster(NOLOCK) WHERE nativeCountry = ISNULL(@country,nativeCountry) AND idNumber = ISNULL(@idNumber,idNumber)
|
|
AND ISNULL(RiskScore,0) BETWEEN @MinVal AND @MaxVal
|
|
END
|
|
|
|
|
|
IF @flag = 'detailRpt'
|
|
BEGIN
|
|
DECLARE @customerRisk TABLE (RiskType VARCHAR(100), Item VARCHAR(100), RiskScore DECIMAL(10,2), Weightage DECIMAL(10,2), WeightedScore DECIMAL(10,2))
|
|
DECLARE @nativeCountry BIGINT, @prodServiceId BIGINT, @customerType BIGINT, @channel BIGINT
|
|
|
|
SELECT
|
|
@nativeCountry = nativeCountry
|
|
,@prodServiceId = 11058
|
|
,@customerType = occupation
|
|
,@channel = CASE WHEN customerType = '11068' THEN '11067' ELSE '11066' END
|
|
FROM dbo.customerMaster(NOLOCK) WHERE customerId = @customerId --AND approvedDate IS NOT NULL
|
|
|
|
|
|
--151 11058 8081 4700
|
|
|
|
SELECT rh.RiskType,x.ItemName,ISNULL(rb.RiskScore,0.00) RiskScore,ISNULL(rh.Weightage,0.00) Weightage,ISNULL(rb.rbaScore,0.00) rbaScore FROM dbo.RiskTypeWeightage(NOLOCK) rh
|
|
LEFT JOIN (
|
|
SELECT countryId AS ItemId,countryName ItemName, 1 AS RiskType FROM dbo.countryMaster(NOLOCK) WHERE countryId = @nativeCountry
|
|
UNION ALL
|
|
SELECT valueId,detailTitle, 2 AS RiskType FROM dbo.staticDataValue(NOLOCK) WHERE TypeID = 7008 AND valueId = @prodServiceId
|
|
UNION ALL
|
|
SELECT valueId,detailTitle, 3 AS RiskType FROM dbo.staticDataValue(NOLOCK) WHERE TypeID = 2000 AND valueId = @customerType
|
|
UNION ALL
|
|
SELECT valueId,detailTitle, 4 AS RiskType FROM dbo.staticDataValue(NOLOCK) WHERE TypeID = 7009 AND valueId = @channel
|
|
)x ON x.RiskType = rh.RowId
|
|
LEFT JOIN dbo.RbaSetup(NOLOCK) rb ON rb.RiskType = rh.RowId AND x.RiskType = rb.RiskType AND x.ItemId = rb.Item
|
|
|
|
END
|
|
END
|