· 4 years ago · Jul 22, 2021, 04:12 PM
1IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('dbo.UpdateOnlineAlerts'))
2DROP PROCEDURE [dbo].[UpdateOnlineAlerts]
3GO
4
5CREATE PROCEDURE UpdateOnlineAlerts
6AS
7BEGIN
8
9 DECLARE @external_data BIGINT
10 SET @external_data = 1
11
12 IF OBJECT_ID('tempdb..##data_to_close_alert') IS NULL
13 BEGIN
14 CREATE TABLE ##data_to_close_alert
15 (
16 ID INT NOT NULL PRIMARY KEY CLUSTERED,
17 BusinessCaseID VARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL,
18 commentid BIGINT NULL
19 );
20
21 SET @external_data = 0
22 END
23
24
25 insert into [staging_namecheck].[dbo].[alert_id](businesscaseid, online_flag)
26 select t1.businesscaseid, 1 from [staging_namecheck].[dbo].[NAMERESULT] t1
27 join [staging_namecheck].[dbo].[NAMECHECK] nc on t1.businesscaseid = nc.businesscaseid
28 left join [plib_namecheck].[dbo].[NC_THRESHOLDS] thresholds_null on thresholds_null.Config = 'Online' and (thresholds_null.OrgClient is null or thresholds_null.OrgClient = '')
29 left join [plib_namecheck].[dbo].[NC_THRESHOLDS] thresholds_oc on thresholds_oc.Config = 'Online' and thresholds_oc.OrgClient = nc.mandant and thresholds_oc.List = thresholds_null.List
30 where not exists (select businesscaseid from [staging_namecheck].[dbo].[alert_id] t2 where t2.businesscaseid = t1.businesscaseid) and (@external_data = 0 or exists (select BusinessCaseID from ##data_to_close_alert dtca where dtca.businesscaseid = t1.businesscaseid))
31 and hits > 0 and (
32 (CAST(CASE WHEN ISNUMERIC(t1.eff_threshold) = 1 THEN t1.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'Manual_SNC' and nc.TYPE_FLAG = 'man') or
33 (CAST(CASE WHEN ISNUMERIC(t1.eff_threshold) = 1 THEN t1.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'ALL') or
34 (CAST(CASE WHEN ISNUMERIC(t1.eff_threshold) = 1 THEN t1.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'PEP_Desk' and t1.List = 'PEP_Desk') or
35 (CAST(CASE WHEN ISNUMERIC(t1.eff_threshold) = 1 THEN t1.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'NOT_PEP_Desk' and t1.List != 'PEP_Desk'))
36 and not t1.businesscaseid like 'TXM%' and not t1.businesscaseid like 'MULTI%'
37
38 group by t1.businesscaseid
39 order by min(t1.Creation_Date)
40
41 update [staging_namecheck].[dbo].[alert_id] set foreign_id = 'AL-ONLINE-' + RIGHT(100000000 + id, 8), category = 'online' where
42 exists (select businesscaseid from [staging_namecheck].[dbo].[NAMERESULT] nr where nr.businesscaseid = [alert_id].businesscaseid) and (@external_data = 0 or exists (select BusinessCaseID from ##data_to_close_alert dtca where dtca.businesscaseid = [alert_id].businesscaseid))
43
44 MERGE INTO alert_id T
45 USING (
46 SELECT
47 nr.businesscaseid businesscaseid,
48 MIN(CASE WHEN nr.status IS NULL THEN 0 ELSE nr.status END) status,
49 MIN(nr.Creation_Date) Creation_Date,
50 MAX(CAST(CASE WHEN ISNUMERIC(nr.eff_threshold) = 1 THEN nr.eff_threshold ELSE NULL END as float)) similarity
51 FROM nameresult nr
52 join [staging_namecheck].[dbo].[NAMECHECK] nc on nr.businesscaseid = nc.businesscaseid
53 left join [plib_namecheck].[dbo].[NC_THRESHOLDS] thresholds_null on thresholds_null.Config = 'Online' and (thresholds_null.OrgClient is null or thresholds_null.OrgClient = '')
54 left join [plib_namecheck].[dbo].[NC_THRESHOLDS] thresholds_oc on thresholds_oc.Config = 'Online' and thresholds_oc.OrgClient = nc.mandant and thresholds_oc.List = thresholds_null.List
55 WHERE ((CAST(CASE WHEN ISNUMERIC(nr.eff_threshold) = 1 THEN nr.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'Manual_SNC' and nc.TYPE_FLAG = 'man') or
56 (CAST(CASE WHEN ISNUMERIC(nr.eff_threshold) = 1 THEN nr.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'ALL') or
57 (CAST(CASE WHEN ISNUMERIC(nr.eff_threshold) = 1 THEN nr.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'PEP_Desk' and nr.List = 'PEP_Desk') or
58 (CAST(CASE WHEN ISNUMERIC(nr.eff_threshold) = 1 THEN nr.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'NOT_PEP_Desk' and nr.List != 'PEP_Desk'))
59 and (@external_data = 0 or exists (select BusinessCaseID from ##data_to_close_alert dtca where dtca.businesscaseid = nc.businesscaseid))
60 group by nr.businesscaseid
61 ) S
62 ON T.businesscaseid = S.businesscaseid
63 WHEN MATCHED THEN
64 UPDATE
65 SET status = S.status,
66 Creation_Date = S.Creation_Date,
67 similarity = S.similarity;
68
69 MERGE INTO nameresult_calculated T
70 USING (
71 SELECT nr.businesscaseid businesscaseid,
72 nr.list_id listid,
73 MAX(case when nr.RecordType = 'RuleSet' then NULL else slw.list END) type_of_list,
74 MAX(case when nr.RecordType = 'RuleSet' then 'RuleBased' else slw.type_of_list_internal_category END) type_of_list_internal_category,
75 MAX(slw.First_Name) First_Name,
76 MAX(slw.Last_Name) Last_Name,
77 MAX(slw.Full_Name) Full_Name,
78 MAX(CAST(slw.Alias as varchar(2000))) Alias,
79 MAX(slw.DOB) DOB,
80 MAX(slw.Country) Country,
81 MAX(slw.POB) POB,
82 MAX(slw.Country_Of_Origin) Country_Of_Origin,
83 MAX(slw.Country_Of_Activity) Country_Of_Activity,
84 MAX(slw.keywords) keywords
85 FROM nameresult nr
86 join [staging_namecheck].[dbo].[NAMECHECK] nc on nr.businesscaseid = nc.businesscaseid
87 left join [plib_namecheck].[dbo].[NC_THRESHOLDS] thresholds_null on thresholds_null.Config = 'Online' and (thresholds_null.OrgClient is null or thresholds_null.OrgClient = '')
88 left join [plib_namecheck].[dbo].[NC_THRESHOLDS] thresholds_oc on thresholds_oc.Config = 'Online' and thresholds_oc.OrgClient = nc.mandant and thresholds_oc.List = thresholds_null.List
89 left join [plib_namecheck].[dbo].[sanction_lists_internal_category_view] slw on slw.id = nr.list_id
90 WHERE ((CAST(CASE WHEN ISNUMERIC(nr.eff_threshold) = 1 THEN nr.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'Manual_SNC' and nc.TYPE_FLAG = 'man') or
91 (CAST(CASE WHEN ISNUMERIC(nr.eff_threshold) = 1 THEN nr.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'ALL') or
92 (CAST(CASE WHEN ISNUMERIC(nr.eff_threshold) = 1 THEN nr.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'PEP_Desk' and nr.List = 'PEP_Desk') or
93 (CAST(CASE WHEN ISNUMERIC(nr.eff_threshold) = 1 THEN nr.eff_threshold ELSE NULL END as float) > (CASE WHEN thresholds_oc.Value is null THEN thresholds_null.Value ELSE thresholds_oc.Value END) and thresholds_null.List = 'NOT_PEP_Desk' and nr.List != 'PEP_Desk'))
94 and (@external_data = 0 or exists (select BusinessCaseID from ##data_to_close_alert dtca where dtca.businesscaseid = nc.businesscaseid))
95 group by nr.businesscaseid, nr.list_id
96 ) S
97 ON T.businesscaseid = S.businesscaseid and T.listid = S.listid
98 WHEN NOT MATCHED THEN
99 INSERT(businesscaseid, listid, type_of_list, type_of_list_internal_category,
100 First_Name, Last_Name, Full_Name, Alias, DOB, Country, POB, Country_Of_Origin, Country_Of_Activity, keywords)
101 VALUES (S.businesscaseid, S.listid, S.type_of_list, S.type_of_list_internal_category,
102 S.First_Name, S.Last_Name, S.Full_Name, S.Alias, S.DOB, S.Country, S.POB, S.Country_Of_Origin, S.Country_Of_Activity, S.keywords);
103
104END
105
106GO
107