· 6 years ago · May 31, 2019, 04:34 AM
1----CREATE TABLES
2 PRINT('Creating Tables...' + CHAR(10))
3
4 IF OBJECT_ID('dbo.Member','U') IS NOT NULL
5 DROP TABLE dbo.Member;
6
7 CREATE TABLE [dbo].[Member]
8 ( [ContactId] NVARCHAR(255) NULL
9 ,[ava_UIN] NVARCHAR(255) NULL
10 ,[FullName] NVARCHAR(255) NULL
11 ,[BirthDate] DATETIME NULL
12 ,[CreatedOn] DATETIME NULL
13 )
14 GO
15
16 IF OBJECT_ID('dbo.Memberships','U') IS NOT NULL
17 DROP TABLE dbo.Memberships;
18
19 CREATE TABLE [dbo].[Memberships]
20 ( [ava_membershipId] NVARCHAR(255) NULL
21 ,[ava_ContactId] NVARCHAR(255) NULL
22 ,[ava_UIN] NVARCHAR(255) NULL
23 ,[FullName] NVARCHAR(255) NULL
24 ,[ava_DateofBirth] DATETIME NULL
25 ,[ava_JoinedUnionDate] DATETIME NULL
26 )
27 GO
28
29 IF OBJECT_ID('dbo.PME','U') IS NOT NULL
30 DROP TABLE dbo.PME;
31
32 CREATE TABLE [dbo].[PME]
33 ( [ava_pmemembershipId] NVARCHAR(255) NULL
34 ,[ava_NRICFIN] NVARCHAR(255) NULL
35 ,[ava_name] NVARCHAR(255) NULL
36 ,[ava_DateofBirth] DATETIME NULL
37 ,[ava_joindate] DATETIME NULL
38 )
39 GO
40
41 IF OBJECT_ID('dbo.SME','U') IS NOT NULL
42 DROP TABLE dbo.SME;
43
44 CREATE TABLE [dbo].[SME]
45 ( [ava_smemembershipId] NVARCHAR(255) NULL
46 ,[ava_NRICFIN] NVARCHAR(255) NULL
47 ,[ava_name] NVARCHAR(255) NULL
48 ,[ava_DateofBirth] DATETIME NULL
49 ,[ava_joindate] DATETIME NULL
50 )
51 GO
52
53 IF OBJECT_ID('dbo.FSE','U') IS NOT NULL
54 DROP TABLE dbo.FSE;
55
56 CREATE TABLE [dbo].[FSE]
57 ( [ava_fsemembershipId] NVARCHAR(255) NULL
58 ,[ava_NRICFIN] NVARCHAR(255) NULL
59 ,[ava_name] NVARCHAR(255) NULL
60 ,[ava_DateofBirth] DATETIME NULL
61 ,[ava_joindate] DATETIME NULL
62 )
63 GO
64
65 IF OBJECT_ID('dbo.CDE','U') IS NOT NULL
66 DROP TABLE dbo.CDE;
67
68 CREATE TABLE [dbo].[CDE]
69 ( [dxc_cdemembershipId] NVARCHAR(255) NULL
70 ,[ava_NRICFIN] NVARCHAR(255) NULL
71 ,[ava_name] NVARCHAR(255) NULL
72 ,[ava_DateofBirth] DATETIME NULL
73 ,[CreatedOn] DATETIME NULL
74 )
75 GO
76
77 IF OBJECT_ID('dbo.MWC','U') IS NOT NULL
78 DROP TABLE dbo.MWC;
79
80 CREATE TABLE [dbo].[MWC]
81 ( [dxc_mwcmembershipId] NVARCHAR(255) NULL
82 ,[ava_NRICFIN] NVARCHAR(255) NULL
83 ,[ava_name] NVARCHAR(255) NULL
84 ,[ava_DateofBirth] DATETIME NULL
85 ,[CreatedOn] DATETIME NULL
86 )
87 GO
88
89 IF OBJECT_ID('dbo.GeneratedID','U') IS NOT NULL
90 DROP TABLE dbo.GeneratedID;
91 GO
92
93 CREATE TABLE [dbo].[GeneratedID]
94 ( [NRIC] VARCHAR(100)
95 ,[FullName] VARCHAR(100)
96 ,[BirthDate] DATETIME
97 ,[JoinDate] DATETIME
98 ,[ExchangeID] VARCHAR(100)
99 ,[SearchID] VARCHAR(100)
100 )
101 GO
102
103 IF OBJECT_ID('dbo.SequenceCount','U') IS NOT NULL
104 DROP TABLE dbo.SequenceCount;
105 GO
106
107 CREATE TABLE [dbo].[SequenceCount]
108 ( [SequenceYear] SMALLINT NOT NULL PRIMARY KEY
109 ,[TotalSequence] INT
110 )
111 GO
112
113 IF OBJECT_ID('dbo.ErrorLogs','U') IS NOT NULL
114 DROP TABLE dbo.ErrorLogs;
115 GO
116
117 CREATE TABLE [dbo].[ErrorLogs]
118 ( [EncounteredOn] NVARCHAR(100)
119 ,[BatchProcessed] NVARCHAR(100)
120 ,[ErrorEncountered] NVARCHAR(MAX)
121 ,[DateEncountered] DATETIME
122 )
123 GO
124
125----ADD EXCHANGEID AND SEARCHID COLUMNS IN RECORD TABLES
126 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Member' AND COLUMN_NAME = 'ExchangeID')
127 ALTER TABLE Member ADD ExchangeID VARCHAR(50)
128 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Member' AND COLUMN_NAME = 'SearchID')
129 ALTER TABLE Member ADD SearchID VARCHAR(50)
130 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Memberships' AND COLUMN_NAME = 'ExchangeID')
131 ALTER TABLE Memberships ADD ExchangeID VARCHAR(50)
132 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Memberships' AND COLUMN_NAME = 'SearchID')
133 ALTER TABLE Memberships ADD SearchID VARCHAR(50)
134 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'PME' AND COLUMN_NAME = 'ExchangeID')
135 ALTER TABLE PME ADD ExchangeID VARCHAR(50)
136 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'PME' AND COLUMN_NAME = 'SearchID')
137 ALTER TABLE PME ADD SearchID VARCHAR(50)
138 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'SME' AND COLUMN_NAME = 'ExchangeID')
139 ALTER TABLE SME ADD ExchangeID VARCHAR(50)
140 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'SME' AND COLUMN_NAME = 'SearchID')
141 ALTER TABLE SME ADD SearchID VARCHAR(50)
142 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'FSE' AND COLUMN_NAME = 'ExchangeID')
143 ALTER TABLE FSE ADD ExchangeID VARCHAR(50)
144 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'FSE' AND COLUMN_NAME = 'SearchID')
145 ALTER TABLE FSE ADD SearchID VARCHAR(50)
146 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'CDE' AND COLUMN_NAME = 'ExchangeID')
147 ALTER TABLE CDE ADD ExchangeID VARCHAR(50)
148 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'CDE' AND COLUMN_NAME = 'SearchID')
149 ALTER TABLE CDE ADD SearchID VARCHAR(50)
150 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'MWC' AND COLUMN_NAME = 'ExchangeID')
151 ALTER TABLE MWC ADD ExchangeID VARCHAR(50)
152 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'MWC' AND COLUMN_NAME = 'SearchID')
153 ALTER TABLE MWC ADD SearchID VARCHAR(50)
154
155----ADD IDENTITY COLUMNS IN RECORD TABLES
156 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ControlID' AND TABLE_NAME = 'Member')
157 ALTER TABLE Member ADD ControlID INT IDENTITY(1,1)
158 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ControlID' AND TABLE_NAME = 'Memberships')
159 ALTER TABLE Memberships ADD ControlID INT IDENTITY(1,1)
160 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ControlID' AND TABLE_NAME = 'PME')
161 ALTER TABLE PME ADD ControlID INT IDENTITY(1,1)
162 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ControlID' AND TABLE_NAME = 'SME')
163 ALTER TABLE SME ADD ControlID INT IDENTITY(1,1)
164 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ControlID' AND TABLE_NAME = 'FSE')
165 ALTER TABLE FSE ADD ControlID INT IDENTITY(1,1)
166 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ControlID' AND TABLE_NAME = 'CDE')
167 ALTER TABLE CDE ADD ControlID INT IDENTITY(1,1)
168 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'ControlID' AND TABLE_NAME = 'MWC')
169 ALTER TABLE MWC ADD ControlID INT IDENTITY(1,1)
170
171----CREATE USER-DEFINED FUNCTIONS
172 PRINT('Creating User-Defined Functions...' + CHAR(10))
173
174 IF OBJECT_ID('dbo.GetCheckSum','FN') IS NOT NULL
175 DROP FUNCTION dbo.GetCheckSum;
176 GO
177
178 CREATE FUNCTION [dbo].[GetCheckSum](@ExchangeID VARCHAR(10))
179 RETURNS VARCHAR(2)
180 AS BEGIN
181 DECLARE @CheckSum INT
182
183 SET @CheckSum = ((CAST(SUBSTRING(@ExchangeID, 1,1) AS INT) * 9) +
184 (CAST(SUBSTRING(@ExchangeID, 2,1) AS INT) * 6) +
185 (CAST(SUBSTRING(@ExchangeID, 3,1) AS INT) * 3) +
186 (CAST(SUBSTRING(@ExchangeID, 4,1) AS INT) * 2) +
187 (CAST(SUBSTRING(@ExchangeID, 5,1) AS INT) * 1) +
188 (CAST(SUBSTRING(@ExchangeID, 6,1) AS INT) * 4) +
189 (CAST(SUBSTRING(@ExchangeID, 7,1) AS INT) * 7) +
190 (CAST(SUBSTRING(@ExchangeID, 8,1) AS INT) * 8) +
191 (CAST(SUBSTRING(@ExchangeID, 9,1) AS INT) * 5) +
192 (CAST(SUBSTRING(@ExchangeID,10,1) AS INT) * 9)
193 ) % 19 + 1
194
195 RETURN RIGHT('00' + CAST(@CheckSum AS VARCHAR(2)),2)
196 END
197 GO
198
199 IF OBJECT_ID('dbo.GenerateDummyNRIC','FN') IS NOT NULL
200 DROP FUNCTION dbo.GenerateDummyNRIC;
201 GO
202
203 CREATE FUNCTION [dbo].[GenerateDummyNRIC](@CTR UNIQUEIDENTIFIER, @BirthDate DATETIME, @Serial INT)
204 RETURNS VARCHAR(9)
205 AS BEGIN
206 DECLARE
207 @CheckDigit INT = 0
208 ,@NRIC VARCHAR(10) = RIGHT('000000' + CAST(@Serial AS VARCHAR(10)),7)
209
210 SET @CheckDigit = ABS(((CAST(SUBSTRING(@NRIC, 1,1) AS INT) * 2) + (CAST(SUBSTRING(@NRIC, 2,1) AS INT) * 7) + (CAST(SUBSTRING(@NRIC, 3,1) AS INT) * 6) + (CAST(SUBSTRING(@NRIC, 4,1) AS INT) * 5) + (CAST(SUBSTRING(@NRIC, 5,1) AS INT) * 4) + (CAST(SUBSTRING(@NRIC, 6,1) AS INT) * 3) + (CAST(SUBSTRING(@NRIC, 7,1) AS INT) * 2)) % 11 - 11)
211
212 RETURN (CASE WHEN ABS(CHECKSUM(@CTR)) % 2 = 1 THEN (CASE WHEN @BirthDate < '1991-04-01' THEN 'S' ELSE 'T' END) ELSE (CASE WHEN @BirthDate < '2000-01-01' THEN 'F' ELSE 'G' END) END) + @NRIC + (CASE WHEN @CheckDigit = 1 THEN 'A' WHEN @CheckDigit = 2 THEN 'B' WHEN @CheckDigit = 3 THEN 'C' WHEN @CheckDigit = 4 THEN 'D' WHEN @CheckDigit = 5 THEN 'E' WHEN @CheckDigit = 6 THEN 'F' WHEN @CheckDigit = 7 THEN 'G' WHEN @CheckDigit = 8 THEN 'H' WHEN @CheckDigit = 9 THEN 'I' WHEN @CheckDigit = 10 THEN 'Z' WHEN @CheckDigit = 11 THEN 'J' ELSE '0' END)
213 END
214 GO
215
216----CREATE VIEWS
217 PRINT('Creating Views...' + CHAR(10))
218
219 IF OBJECT_ID('dbo.vwGenerateUnionID','V') IS NOT NULL
220 DROP VIEW dbo.vwGenerateUnionID;
221 GO
222
223 CREATE VIEW [dbo].[vwGenerateUnionID]
224 AS
225 SELECT NRIC, FullName, BirthDate, JoinDate
226 ,ControlNo + dbo.getCheckSum(ControlNo) AS ExchangeID
227 ,RIGHT(NRIC,4) + REPLACE(CONVERT(CHAR(10),BirthDate,103),'/','') AS SearchID
228 FROM (
229 SELECT NRIC, FullName, BirthDate, JoinDate
230 ,CAST(YEAR([JoinDate]) AS VARCHAR) + RIGHT('000000' + CAST((CASE WHEN TotalSequence IS NULL THEN 0 ELSE TotalSequence END) + ROW_NUMBER() OVER (PARTITION BY DATEPART(yyyy,JoinDate) ORDER BY FullName) AS VARCHAR),6) AS ControlNo
231 FROM (
232 SELECT NRIC, MIN(FullName) AS [FullName], MIN(BirthDate) AS [BirthDate], MIN(JoinDate) AS [JoinDate], DATEPART(yyyy,MIN(JoinDate)) AS [JoinYear]
233 FROM (
234 SELECT
235 (CASE WHEN A.ava_uin IS NOT NULL THEN A.ava_uin ELSE B.ava_uin END) AS [NRIC]
236 ,(CASE WHEN A.FullName IS NOT NULL THEN A.FullName ELSE B.FullName END) AS [FullName]
237 ,(CASE WHEN A.BirthDate IS NOT NULL THEN A.BirthDate ELSE B.ava_dateofbirth END) AS [BirthDate]
238 ,(CASE WHEN B.ava_JoinedUnionDate IS NOT NULL THEN B.ava_JoinedUnionDate ELSE A.Createdon END) AS [JoinDate]
239 FROM Member AS A LEFT JOIN Memberships AS B ON A.ava_uin = B.ava_uin
240 ) aTable GROUP BY NRIC
241 ) bTable LEFT JOIN SequenceCount ON bTable.JoinYear = SequenceCount.SequenceYear
242 ) cTable
243 GO
244
245 IF OBJECT_ID('dbo.vwGenerateUNetworkID','V') IS NOT NULL
246 DROP VIEW dbo.vwGenerateUNetworkID;
247 GO
248
249 CREATE VIEW [dbo].[vwGenerateUNetworkID]
250 AS
251 SELECT NRIC, FullName, BirthDate, JoinDate
252 ,ControlNo + dbo.getCheckSum(ControlNo) AS ExchangeID
253 ,RIGHT(NRIC,4) + REPLACE(CONVERT(CHAR(10),BirthDate,103),'/','') AS SearchID
254 FROM (
255 SELECT NRIC, FullName, BirthDate, JoinDate
256 ,CAST(YEAR([JoinDate]) AS VARCHAR) + RIGHT('000000' + CAST((CASE WHEN TotalSequence IS NULL THEN 0 ELSE TotalSequence END) + ROW_NUMBER() OVER (PARTITION BY DATEPART(yyyy,JoinDate) ORDER BY FullName) AS VARCHAR),6) AS ControlNo
257 FROM (
258 SELECT A.NRIC, MIN(A.FullName) AS [FullName], MIN(A.BirthDate) AS [BirthDate], MIN(A.JoinDate) AS [JoinDate], DATEPART(yyyy,MIN(A.JoinDate)) AS [JoinYear]
259 FROM (
260 SELECT ava_NRICFIN AS [NRIC], ava_Name AS [FullName], ava_DateofBirth AS [BirthDate], ava_joindate AS [JoinDate] FROM PME UNION
261 SELECT ava_NRICFIN AS [NRIC], ava_Name AS [FullName], ava_DateofBirth AS [BirthDate], ava_joindate AS [JoinDate] FROM SME UNION
262 SELECT ava_NRICFIN AS [NRIC], ava_Name AS [FullName], ava_DateofBirth AS [BirthDate], ava_joindate AS [JoinDate] FROM FSE
263 ) A LEFT JOIN GeneratedID AS B ON A.NRIC = B.NRIC WHERE B.NRIC IS NULL GROUP BY A.NRIC
264 ) aTable LEFT JOIN SequenceCount ON aTable.JoinYear = SequenceCount.SequenceYear
265 ) bTable
266 GO
267
268 IF OBJECT_ID('dbo.vwGenerateCDEMWCID','V') IS NOT NULL
269 DROP VIEW dbo.vwGenerateCDEMWCID;
270 GO
271
272 CREATE VIEW [dbo].[vwGenerateCDEMWCID]
273 AS
274 SELECT NRIC, FullName, BirthDate, JoinDate, ControlNo + dbo.getCheckSum(ControlNo) AS [ExchangeID], SearchID
275 FROM (
276 SELECT NRIC, FullName, BirthDate, JoinDate, MembershipType
277 ,(CASE WHEN MembershipType = 'CDE' THEN '44' WHEN MembershipType = 'MWC' THEN '45' ELSE '00' END) + RIGHT('00000000' + CAST(ROW_NUMBER() OVER (PARTITION BY MembershipType ORDER BY DATEPART(yyyy,JoinDate), FullName) AS VARCHAR),8) AS [ControlNo]
278 ,RIGHT(NRIC,4) + REPLACE(CONVERT(CHAR(10),BirthDate,103),'/','') AS SearchID
279 FROM (
280 SELECT NRIC, FullName, BirthDate, MembershipType, MIN(JoinDate) AS [JoinDate]
281 FROM (
282 SELECT ava_NRICFIN AS [NRIC], ava_name AS [FullName], ava_DateofBirth AS [BirthDate], CreatedOn AS [JoinDate], 'CDE' AS [MembershipType] FROM CDE UNION
283 SELECT ava_NRICFIN AS [NRIC], ava_name AS [FullName], ava_DateofBirth AS [BirthDate], CreatedOn AS [JoinDate], 'MWC' AS [MembershipType] FROM MWC
284 ) aTable GROUP BY NRIC, FullName, BirthDate, MembershipType
285 ) bTable
286 ) cTable
287 GO
288
289----CREATE PROCEDURES
290 PRINT('Creating Stored Procedures...' + CHAR(10))
291
292 IF OBJECT_ID('dbo.spClearGeneratedID','P') IS NOT NULL
293 DROP PROCEDURE spClearGeneratedID
294 GO
295
296 CREATE PROCEDURE [dbo].[spClearGeneratedID]
297 AS
298 BEGIN
299 UPDATE Member SET ExchangeID = NULL, SearchID = NULL
300 UPDATE Memberships SET ExchangeID = NULL, SearchID = NULL
301 UPDATE PME SET ExchangeID = NULL, SearchID = NULL
302 UPDATE SME SET ExchangeID = NULL, SearchID = NULL
303 UPDATE FSE SET ExchangeID = NULL, SearchID = NULL
304 UPDATE CDE SET ExchangeID = NULL, SearchID = NULL
305 UPDATE MWC SET ExchangeID = NULL, SearchID = NULL
306 END
307 GO
308
309 IF OBJECT_ID('dbo.spGenerateID','P') IS NOT NULL
310 DROP PROCEDURE spGenerateID
311 GO
312
313 CREATE PROCEDURE [dbo].[spGenerateID]
314 AS
315 BEGIN
316 SET NOCOUNT ON
317 PRINT('----Generate ExchangeID and SearchID' + CHAR(10))
318 BEGIN TRY
319 BEGIN TRANSACTION
320 --------DECLARE VARIABLES
321 DECLARE @Process NVARCHAR(100)
322
323 --------CLEAR EXCHANGEID AND SEQUENCE TABLES
324 DELETE FROM ErrorLogs
325 DELETE FROM GeneratedID
326 DELETE FROM SequenceCount
327
328 --------GENERATE EXCHANGE ID FOR UNION TABLES
329 SET @Process = 'Generating ExchangeID and SearchID for Union Tables'; PRINT(@Process + '...')
330 INSERT INTO GeneratedID ([NRIC],[FullName],[BirthDate],[JoinDate],[ExchangeID],[SearchID])
331 SELECT [NRIC], [FullName], [BirthDate], [JoinDate], [ExchangeID], [SearchID] FROM vwGenerateUnionID
332 PRINT(' Process Successful.')
333
334 --------UPDATE SEQUENCE TABLE TO GET THE LATEST SEQUENCE USED PER YEAR
335 DELETE FROM SequenceCount
336 INSERT INTO SequenceCount (SequenceYear,TotalSequence)
337 SELECT DATEPART(yyyy,JoinDate), COUNT(*) FROM GeneratedID GROUP BY DATEPART(yyyy,JoinDate)
338
339 --------GENERATE EXCHANGE ID FOR UNETWORK TABLES
340 SET @Process = 'Generating ExchangeID and SearchID for UNetwork Tables'; PRINT(@Process + '...')
341 INSERT INTO GeneratedID ([NRIC],[FullName],[BirthDate],[JoinDate],[ExchangeID],[SearchID])
342 SELECT [NRIC], [FullName], [BirthDate], [JoinDate], [ExchangeID], [SearchID] FROM vwGenerateUNetworkID
343 PRINT(' Process Successful.')
344
345 --------UPDATE SEQUENCE TABLE TO GET THE LATEST SEQUENCE USED PER YEAR
346 DELETE FROM SequenceCount
347 INSERT INTO SequenceCount (SequenceYear,TotalSequence)
348 SELECT DATEPART(yyyy,JoinDate), COUNT(*) FROM GeneratedID GROUP BY DATEPART(yyyy,JoinDate)
349
350 --------GENERATE EXCHANGE ID FOR CDE/MWC TABLES
351 SET @Process = 'Generating ExchangeID and SearchID for CDE/MWC Tables'; PRINT(@Process + '...')
352 INSERT INTO GeneratedID ([NRIC],[FullName],[BirthDate],[JoinDate],[ExchangeID],[SearchID])
353 SELECT [NRIC], [FullName], [BirthDate], [JoinDate], [ExchangeID], [SearchID] FROM vwGenerateCDEMWCID
354 PRINT(' Process Successful.')
355
356 PRINT(CHAR(10) + '----Update ExchangeID and SearchID of Tables in Staging Database' + CHAR(10))
357 --------DECLARE VARIABLES
358 DECLARE
359 @ActualRecordCount BIGINT
360 ,@DuplicateRecordCount BIGINT
361 ,@GeneratedExchangeID BIGINT
362 ,@NullExchangeID BIGINT
363 ,@GeneratedSearchID BIGINT
364 ,@NullSearchID BIGINT
365
366 SET @ActualRecordCount = 0; SET @DuplicateRecordCount = 0; SET @GeneratedExchangeID = 0; SET @NullExchangeID = 0; SET @GeneratedSearchID = 0; SET @NullSearchID = 0;
367 SET @Process = 'Numbers in Members Table'; PRINT(@Process)
368 UPDATE B SET B.ExchangeID = A.ExchangeID, B.SearchID = A.SearchID FROM GeneratedID AS A INNER JOIN Member AS B ON A.NRIC = B.ava_UIN
369 SELECT
370 @ActualRecordCount = COUNT(*)
371 ,@DuplicateRecordCount = COUNT(*) - COUNT(DISTINCT(ava_uin))
372 ,@GeneratedExchangeID = COUNT(DISTINCT(ExchangeID))
373 ,@NullExchangeID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
374 ,@GeneratedSearchID = COUNT(DISTINCT(SearchID))
375 ,@NullSearchID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
376 FROM Member
377 PRINT(' Actual Record Count : ' + CAST(@ActualRecordCount AS VARCHAR))
378 PRINT(' Duplicate Record Count : ' + CAST(@DuplicateRecordCount AS VARCHAR))
379 PRINT(' Generated ExchangeID : ' + CAST(@GeneratedExchangeID AS VARCHAR))
380 PRINT(' Null ExchangeID : ' + CAST(@NullExchangeID AS VARCHAR))
381 PRINT(' Generated SearchID : ' + CAST(@GeneratedSearchID AS VARCHAR))
382 PRINT(' Null SearchID : ' + CAST(@NullSearchID AS VARCHAR))
383 PRINT('')
384 SET @ActualRecordCount = 0; SET @DuplicateRecordCount = 0; SET @GeneratedExchangeID = 0; SET @NullExchangeID = 0; SET @GeneratedSearchID = 0; SET @NullSearchID = 0;
385 SET @Process = 'Numbers in Memberships Table'; PRINT(@Process)
386 UPDATE B SET B.ExchangeID = A.ExchangeID, B.SearchID = A.SearchID FROM GeneratedID AS A INNER JOIN Memberships AS B ON A.NRIC = B.ava_UIN
387 SELECT
388 @ActualRecordCount = COUNT(*)
389 ,@DuplicateRecordCount = COUNT(*) - COUNT(DISTINCT(ava_uin))
390 ,@GeneratedExchangeID = COUNT(DISTINCT(ExchangeID))
391 ,@NullExchangeID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
392 ,@GeneratedSearchID = COUNT(DISTINCT(SearchID))
393 ,@NullSearchID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
394 FROM Memberships
395 PRINT(' Actual Record Count : ' + CAST(@ActualRecordCount AS VARCHAR))
396 PRINT(' Duplicate Record Count : ' + CAST(@DuplicateRecordCount AS VARCHAR))
397 PRINT(' Generated ExchangeID : ' + CAST(@GeneratedExchangeID AS VARCHAR))
398 PRINT(' Null ExchangeID : ' + CAST(@NullExchangeID AS VARCHAR))
399 PRINT(' Generated SearchID : ' + CAST(@GeneratedSearchID AS VARCHAR))
400 PRINT(' Null SearchID : ' + CAST(@NullSearchID AS VARCHAR))
401 PRINT('')
402 SET @ActualRecordCount = 0; SET @DuplicateRecordCount = 0; SET @GeneratedExchangeID = 0; SET @NullExchangeID = 0; SET @GeneratedSearchID = 0; SET @NullSearchID = 0;
403 SET @Process = 'Numbers in PME Table'; PRINT(@Process)
404 UPDATE B SET B.ExchangeID = A.ExchangeID, B.SearchID = A.SearchID FROM GeneratedID AS A INNER JOIN PME AS B ON A.NRIC = B.ava_NRICFIN
405 SELECT
406 @ActualRecordCount = COUNT(*)
407 ,@DuplicateRecordCount = COUNT(*) - COUNT(DISTINCT(ava_NRICFIN))
408 ,@GeneratedExchangeID = COUNT(DISTINCT(ExchangeID))
409 ,@NullExchangeID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
410 ,@GeneratedSearchID = COUNT(DISTINCT(SearchID))
411 ,@NullSearchID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
412 FROM PME
413 PRINT(' Actual Record Count : ' + CAST(@ActualRecordCount AS VARCHAR))
414 PRINT(' Duplicate Record Count : ' + CAST(@DuplicateRecordCount AS VARCHAR))
415 PRINT(' Generated ExchangeID : ' + CAST(@GeneratedExchangeID AS VARCHAR))
416 PRINT(' Null ExchangeID : ' + CAST(@NullExchangeID AS VARCHAR))
417 PRINT(' Generated SearchID : ' + CAST(@GeneratedSearchID AS VARCHAR))
418 PRINT(' Null SearchID : ' + CAST(@NullSearchID AS VARCHAR))
419 PRINT('')
420 SET @ActualRecordCount = 0; SET @DuplicateRecordCount = 0; SET @GeneratedExchangeID = 0; SET @NullExchangeID = 0; SET @GeneratedSearchID = 0; SET @NullSearchID = 0;
421 SET @Process = 'Numbers in SME Table'; PRINT(@Process)
422 UPDATE B SET B.ExchangeID = A.ExchangeID, B.SearchID = A.SearchID FROM GeneratedID AS A INNER JOIN SME AS B ON A.NRIC = B.ava_NRICFIN
423 SELECT
424 @ActualRecordCount = COUNT(*)
425 ,@DuplicateRecordCount = COUNT(*) - COUNT(DISTINCT(ava_NRICFIN))
426 ,@GeneratedExchangeID = COUNT(DISTINCT(ExchangeID))
427 ,@NullExchangeID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
428 ,@GeneratedSearchID = COUNT(DISTINCT(SearchID))
429 ,@NullSearchID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
430 FROM SME
431 PRINT(' Actual Record Count : ' + CAST(@ActualRecordCount AS VARCHAR))
432 PRINT(' Duplicate Record Count : ' + CAST(@DuplicateRecordCount AS VARCHAR))
433 PRINT(' Generated ExchangeID : ' + CAST(@GeneratedExchangeID AS VARCHAR))
434 PRINT(' Null ExchangeID : ' + CAST(@NullExchangeID AS VARCHAR))
435 PRINT(' Generated SearchID : ' + CAST(@GeneratedSearchID AS VARCHAR))
436 PRINT(' Null SearchID : ' + CAST(@NullSearchID AS VARCHAR))
437 PRINT('')
438 SET @ActualRecordCount = 0; SET @DuplicateRecordCount = 0; SET @GeneratedExchangeID = 0; SET @NullExchangeID = 0; SET @GeneratedSearchID = 0; SET @NullSearchID = 0;
439 SET @Process = 'Numbers in FSE Table'; PRINT(@Process)
440 UPDATE B SET B.ExchangeID = A.ExchangeID, B.SearchID = A.SearchID FROM GeneratedID AS A INNER JOIN FSE AS B ON A.NRIC = B.ava_NRICFIN
441 SELECT
442 @ActualRecordCount = COUNT(*)
443 ,@DuplicateRecordCount = COUNT(*) - COUNT(DISTINCT(ava_NRICFIN))
444 ,@GeneratedExchangeID = COUNT(DISTINCT(ExchangeID))
445 ,@NullExchangeID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
446 ,@GeneratedSearchID = COUNT(DISTINCT(SearchID))
447 ,@NullSearchID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
448 FROM FSE
449 PRINT(' Actual Record Count : ' + CAST(@ActualRecordCount AS VARCHAR))
450 PRINT(' Duplicate Record Count : ' + CAST(@DuplicateRecordCount AS VARCHAR))
451 PRINT(' Generated ExchangeID : ' + CAST(@GeneratedExchangeID AS VARCHAR))
452 PRINT(' Null ExchangeID : ' + CAST(@NullExchangeID AS VARCHAR))
453 PRINT(' Generated SearchID : ' + CAST(@GeneratedSearchID AS VARCHAR))
454 PRINT(' Null SearchID : ' + CAST(@NullSearchID AS VARCHAR))
455 PRINT('')
456 SET @ActualRecordCount = 0; SET @DuplicateRecordCount = 0; SET @GeneratedExchangeID = 0; SET @NullExchangeID = 0; SET @GeneratedSearchID = 0; SET @NullSearchID = 0;
457 SET @Process = 'Numbers in CDE Table'; PRINT(@Process)
458 UPDATE B SET B.ExchangeID = A.ExchangeID, B.SearchID = A.SearchID FROM GeneratedID AS A INNER JOIN CDE AS B ON A.NRIC = B.ava_NRICFIN
459 SELECT
460 @ActualRecordCount = COUNT(*)
461 ,@DuplicateRecordCount = COUNT(*) - COUNT(DISTINCT(ava_NRICFIN))
462 ,@GeneratedExchangeID = COUNT(DISTINCT(ExchangeID))
463 ,@NullExchangeID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
464 ,@GeneratedSearchID = COUNT(DISTINCT(SearchID))
465 ,@NullSearchID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
466 FROM CDE
467 PRINT(' Actual Record Count : ' + CAST(@ActualRecordCount AS VARCHAR))
468 PRINT(' Duplicate Record Count : ' + CAST(@DuplicateRecordCount AS VARCHAR))
469 PRINT(' Generated ExchangeID : ' + CAST(@GeneratedExchangeID AS VARCHAR))
470 PRINT(' Null ExchangeID : ' + CAST(@NullExchangeID AS VARCHAR))
471 PRINT(' Generated SearchID : ' + CAST(@GeneratedSearchID AS VARCHAR))
472 PRINT(' Null SearchID : ' + CAST(@NullSearchID AS VARCHAR))
473 PRINT('')
474 SET @ActualRecordCount = 0; SET @DuplicateRecordCount = 0; SET @GeneratedExchangeID = 0; SET @NullExchangeID = 0; SET @GeneratedSearchID = 0; SET @NullSearchID = 0;
475 SET @Process = 'Numbers in MWC Table'; PRINT(@Process)
476 UPDATE B SET B.ExchangeID = A.ExchangeID, B.SearchID = A.SearchID FROM GeneratedID AS A INNER JOIN MWC AS B ON A.NRIC = B.ava_NRICFIN
477 SELECT
478 @ActualRecordCount = COUNT(*)
479 ,@DuplicateRecordCount = COUNT(*) - COUNT(DISTINCT(ava_NRICFIN))
480 ,@GeneratedExchangeID = COUNT(DISTINCT(ExchangeID))
481 ,@NullExchangeID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
482 ,@GeneratedSearchID = COUNT(DISTINCT(SearchID))
483 ,@NullSearchID = ISNULL(SUM(CASE WHEN ExchangeID IS NULL THEN 1 ELSE 0 END),0)
484 FROM MWC
485 PRINT(' Actual Record Count : ' + CAST(@ActualRecordCount AS VARCHAR))
486 PRINT(' Duplicate Record Count : ' + CAST(@DuplicateRecordCount AS VARCHAR))
487 PRINT(' Generated ExchangeID : ' + CAST(@GeneratedExchangeID AS VARCHAR))
488 PRINT(' Null ExchangeID : ' + CAST(@NullExchangeID AS VARCHAR))
489 PRINT(' Generated SearchID : ' + CAST(@GeneratedSearchID AS VARCHAR))
490 PRINT(' Null SearchID : ' + CAST(@NullSearchID AS VARCHAR))
491 PRINT('')
492
493 COMMIT
494 END TRY
495 BEGIN CATCH
496 ROLLBACK
497
498 INSERT INTO ErrorLogs (EncounteredOn,DateEncountered,ErrorEncountered)
499 SELECT @Process, GETDATE(), ERROR_MESSAGE();
500 END CATCH
501 END
502 GO