· 8 years ago · May 02, 2018, 06:08 AM
1If exists (select * from sys.objects where object_id = OBJECT_ID(N'FormatBalanceWithSign') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
2Begin
3 Execute sp_executesql @Cmd = N'Drop Function FormatBalanceWithSign'
4End
5Execute sp_Executesql @Cmd = N'Create Function FormatBalanceWithSign(@Amount Decimal(15,2),@NumberOfPaddedChars Int)
6Returns Varchar(MAX)
7Begin
8
9
10Declare @ReturnValue Varchar(MAX)
11Declare @Sign Varchar(1)
12
13Set @ReturnValue = CAST(@Amount As Varchar(MAX))
14
15If(SUBSTRING(@ReturnValue,1,1) <> ''-'')
16 Begin
17 Set @Sign = ''+''
18 End
19Else
20 Begin
21 Set @Sign = ''-''
22 Set @ReturnValue = REPLACE(@ReturnValue,''-'','''')
23 End
24
25
26Declare @DecimalPosition Int
27Declare @StringBeforeDecimal Varchar(MAX)
28Declare @LengthOfString Int
29
30Set @DecimalPosition = CHARINDEX(''.'',@ReturnValue)
31Set @StringBeforeDecimal = SUBSTRING(@ReturnValue,1,@DecimalPosition - 1)
32Set @LengthOfString = LEN(@StringBeforeDecimal)
33
34If(@LengthOfString < @NumberOfPaddedChars)
35 Begin
36 Declare @Length Int
37 Declare @Filler Varchar(20)
38
39 Set @Length = @LengthOfString
40 Set @Filler = ''''
41
42 While(@Length < @NumberOfPaddedChars)
43 Begin
44 Set @Length = @Length + 1
45 Set @Filler = ''0'' + @Filler
46 End
47
48 Set @ReturnValue = @Sign + @Filler + @ReturnValue
49
50 End
51
52Return CAST(@ReturnValue As Varchar(MAX))
53
54End
55
56
57'
58
59If exists (select * from sys.objects where object_id = OBJECT_ID(N'FormatTelephoneNumber ') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
60Begin
61 Execute sp_executesql @Cmd = N'Drop Function FormatTelephoneNumber'
62End
63Execute sp_Executesql @Cmd =N'Create Function FormatTelephoneNumber (@RHAREA Decimal(3,0),@RHPHON Decimal(11,0))
64Returns Varchar(60)
65As
66
67Begin
68
69Declare @AreaCode Varchar(20)
70Declare @Phone Varchar(20)
71Declare @FormattedNumber Varchar(60)
72
73Set @AreaCode = CAST(@RHAREA As Varchar(20))
74Set @Phone = CAST(@RHPHON As Varchar(20))
75Set @FormattedNumber = @AreaCode + ''-'' + SUBSTRING(@Phone,1,3) + ''-'' + SUBSTRING(@Phone,4,8)
76
77Return CAST(@FormattedNumber As Varchar(60))
78
79End
80'
81
82If Exists (Select * from sys.tables where name = 'Base_RR')
83Begin
84 Exec sp_ExecuteSql @Cmd = N'Drop Table Base_RR'
85End
86CREATE TABLE Base_RR(
87 [CUST_SKEY] [varchar](30) Not NULL,
88 [RMKEY] [Varchar](20) Not Null,
89 [ACCT_SKEY] [Varchar](30) Not Null,
90 [CCCARD] [Numeric](19,0) Not Null,
91 [Counter] Int Not Null
92) ON [PRIMARY]
93
94If Exists(Select * from sys.views where name = 'v_Base_RR')
95Begin
96 Execute sp_ExecuteSql @Cmd = N'Drop View v_Base_RR'
97End
98Execute sp_ExecuteSql @Cmd = N'Create View v_Base_RR
99As
100Select CUST_SKEY,RMKEY,ACCT_SKEY,CCCARD
101 From Base_RR
102Where Counter = 1
103'
104
105
106IF EXISTS (Select * from sys.tables where name = 'RMADDR_Customer_0001')
107Begin
108 Exec sp_ExecuteSql @Cmd = N'Drop Table RMADDR_Customer_0001'
109End
110CREATE TABLE RMADDR_Customer_0001(
111 [CUST_SKEY] [varchar](30) Not NULL,
112 [Counter] Int Not Null,
113 [RAST#1] [varchar](40) NULL,
114 [RAST#2] [varchar](40) NULL,
115 [RAST#3] [varchar](40) NULL,
116 [RASTN1] [varchar](40) NULL,
117 [RASTN2] [varchar](40) NULL,
118 [RASTN3] [varchar](40) NULL,
119 [RACITY] [varchar](26) NULL,
120 [RASTA] [varchar](20) NULL,
121 [RAZIP] [varchar](5) NULL,
122 [RAFRNC] [varchar](20) NULL
123) ON [PRIMARY]
124
125If Exists(Select * From sys.views where name = 'v_RMADDR_Customer_0001')
126Begin
127 Exec sp_ExecuteSql @Cmd = N'Drop View v_RMADDR_Customer_0001'
128End
129Execute sp_ExecuteSql @Cmd = N'Create View v_RMADDR_Customer_0001
130As
131Select CUST_SKEY,RAST#1,RAST#2,RAST#3,RASTN1,RASTN2,RASTN3,RACITY,RASTA,RAFRNC,RAZIP
132From RMADDR_Customer_0001
133Where Counter = 1
134'
135
136IF EXISTS (Select * from sys.tables where name = 'RMPHON_Home_Customer_0001')
137Begin
138 Exec sp_ExecuteSql @Cmd = N'Drop Table RMPHON_Home_Customer_0001'
139End
140
141CREATE TABLE RMPHON_Home_Customer_0001
142(
143 [CUST_SKEY] [varchar](20) Not NULL,
144 [Counter] Int Not Null,
145 [RHAREA] [decimal](3,0) NOT NULL,
146 [RHPHON] [decimal](11, 0) NOT NULL
147
148) ON [PRIMARY]
149
150if Exists(Select * from sys.views where name = 'v_RMPHON_Home_Customer_0001')
151Begin
152 Exec sp_ExecuteSql @Cmd = N'Drop View v_RMPHON_Home_Customer_0001'
153End
154Exec sp_ExecuteSql @Cmd = N'Create View v_RMPHON_Home_Customer_0001
155As
156Select CUST_SKEY,RHAREA,RHPHON
157From RMPHON_Home_Customer_0001
158Where Counter = 1
159'
160
161IF EXISTS (Select * from sys.tables where name = 'RMPHON_Work_Customer_0001')
162Begin
163 Exec sp_ExecuteSql @Cmd = N'Drop Table RMPHON_Work_Customer_0001'
164End
165
166CREATE TABLE RMPHON_Work_Customer_0001
167(
168 [CUST_SKEY] [varchar](20) Not NULL,
169 [Counter] Int Not Null,
170 [RHAREA] [decimal](3,0) NOT NULL,
171 [RHPHON] [decimal](11, 0) NOT NULL
172
173) ON [PRIMARY]
174
175if Exists(Select * from sys.views where name = 'v_RMPHON_Work_Customer_0001')
176Begin
177 Exec sp_ExecuteSql @Cmd = N'Drop View v_RMPHON_Work_Customer_0001'
178End
179Exec sp_ExecuteSql @Cmd = N'Create View v_RMPHON_Work_Customer_0001
180As
181Select CUST_SKEY,RHAREA,RHPHON
182From RMPHON_Work_Customer_0001
183Where Counter = 1
184'
185
186If exists(Select * from sys.tables where name = 'RMINET_Customer_0001')
187Begin
188 Exec sp_ExecuteSql @Cmd = N'Drop table RMINET_Customer_0001'
189End
190CREATE TABLE RMINET_Customer_0001
191(
192 [CUST_SKEY] [varchar](16) Not NULL,
193 [Counter] Int Not Null,
194 [RIIADR] [varchar](50) NULL,
195
196) ON [PRIMARY]
197
198if exists(Select * from sys.views where name = 'v_RMINET_Customer_0001')
199Begin
200 Exec sp_ExecuteSql @Cmd = N'Drop View v_RMINET_Customer_0001'
201End
202Exec sp_ExecuteSql @Cmd = N'Create View v_RMINET_Customer_0001
203As
204Select CUST_SKEY,RIIADR
205From RMINET_Customer_0001
206Where Counter = 1
207'
208
209If Exists(Select * from sys.tables where name = 'Fact_Summary_Deposits_Customer_0001')
210Begin
211 Execute sp_ExecuteSql @Cmd = N'Drop Table Fact_Summary_Deposits_Customer_0001'
212End
213Create Table Fact_Summary_Deposits_Customer_0001
214(
215 [CUST_SKEY] [Varchar](30) Not Null,
216 [CurrentBalance] [Decimal](20,2) Null,
217 [NmbrOfAccts] [Int] Null
218)
219
220If Exists(Select * from sys.tables where name = 'Fact_Summary_Loans_Customer_0001')
221Begin
222 Execute sp_ExecuteSql @Cmd = N'Drop Table Fact_Summary_Loans_Customer_0001'
223End
224Create Table Fact_Summary_Loans_Customer_0001
225(
226 [CUST_SKEY] [Varchar](30) Not Null,
227 [CurrentLoanAmount] [Decimal](20,2) Null,
228 [NmbrOfAccts] [Int] Null
229)
230
231
232
233If exists(Select * from sys.tables where name = 'Base_DDSV_Combined')
234Begin
235 execute sp_ExecuteSql @Cmd = N'Drop Table Base_DDSV_Combined'
236End
237CREATE TABLE [dbo].[Base_DDSV_Combined](
238 [CUST_SKEY] [Varchar](30) Not Null,
239 [ATDS] [varchar](256) NULL,
240 [RMKEY] [varchar](20) NOT NULL,
241 [CCCARD] [numeric](19, 0) NOT NULL,
242 [DBACCT] [numeric](10, 0) NULL,
243 [DBSTAT] [varchar](1) NULL,
244 [DBDTOP] [decimal](9, 0) NULL,
245 [DBDTCL] [decimal](9, 0) NULL,
246 [DBABMD] [decimal](11, 0) NULL,
247 [DBCURB] [decimal](13, 2) NULL,
248 [DBOPDP] [decimal](13, 2) NULL,
249 [RXAPPL] [Varchar](5) NULL
250) ON [PRIMARY]
251
252
253If exists(Select * from sys.tables where name = 'Base_Transactions_Checking_0002')
254begin
255 execute sp_executesql @Cmd = N'Drop Table Base_Transactions_Checking_0002'
256end
257Create Table Base_Transactions_Checking_0002
258(
259 [CUST_SKEY] [Varchar](30) Not Null,
260 [TotalMonthlyDepositAmt] [Decimal](20,2) Null,
261 [TotalMonthlyDeposits] [Int] Null
262)
263
264If exists(Select * from sys.tables where name = 'Base_Transactions_Savings_0003')
265begin
266 execute sp_executesql @Cmd = N'Drop Table Base_Transactions_Savings_0003'
267end
268Create Table Base_Transactions_Savings_0003
269(
270 [CUST_SKEY] [Varchar](30) Not Null,
271 [TotalMonthlyDepositAmt] [Decimal](20,2) Null,
272 [TotalMonthlyDeposits] [Int] Null
273)
274
275
276
277
278If exists(Select * from sys.tables where name = 'Base_Investment_0004')
279begin
280 execute sp_ExecuteSql @Cmd = N'Drop Table Base_Investment_0004'
281end
282CREATE TABLE [dbo].[Base_Investment_0004](
283 [CUST_SKEY] [varchar](30) NOT NULL,
284 [TMPROD] [varchar](3) NULL,
285 [RMKEY] [varchar](20) NOT NULL,
286 [CCCARD] [numeric](19, 0) NOT NULL,
287 [TMACCT] [numeric](15, 0) NULL,
288 [TMSTAT] [varchar](1) NULL,
289 [TMDTOP] [decimal](9, 0) NULL,
290 [TMDTRD] [decimal](9, 0) NULL,
291 [TMBALM] [decimal](17, 2) NULL,
292 [TMABAL] [decimal](15, 2) NULL,
293 [TMOPNB] [decimal](15, 2) NULL
294) ON [PRIMARY]
295
296If Exists(Select * from sys.tables where name = 'Base_Transactions_Investment_0004')
297Begin
298 Execute sp_ExecuteSql @Cmd = N'Drop Table Base_Transactions_Investment_0004'
299End
300Create Table Base_Transactions_Investment_0004
301(
302 [CUST_SKEY] [Varchar](30) Not Null,
303 [TotalMonthlyDepositAmt] [Decimal](20,2) Null,
304 [NmbrOfDeposits] [Int] Null
305)
306
307
308If exists(Select * from sys.tables where name = 'Base_Loans_0005')
309begin
310 execute sp_ExecuteSql @Cmd = N'Drop Table Base_Loans_0005'
311end
312CREATE TABLE [dbo].[Base_Loans_0005](
313 [ATDS] [varchar](256) NULL,
314 [CUST_SKEY] [varchar](30) NOT NULL,
315 [RMKEY] [varchar](20) NOT NULL,
316 [CCCARD] [numeric](19, 0) NOT NULL,
317 [LMACCT] [numeric](10, 0) NULL,
318 [LMSTAT] [varchar](1) NULL,
319 [LMNOTD] [decimal](9, 0) NULL,
320 [LMDTCL] [decimal](9, 0) NULL,
321 [LMCBAL] [decimal](15, 2) NULL,
322 [LMCBAL2] [decimal](15, 2) NULL,
323 [LMTTLO] [decimal](15, 2) NULL,
324 [LMDTCL2] [decimal](9, 0) NULL,
325 [LMPDPR] [decimal](15, 2) NULL,
326 [LMDYSL] [numeric](19,0) NULL,
327 [LMMATO] [decimal] (9,0) NULL,
328) ON [PRIMARY]
329
330If exists(Select * from sys.tables where name = 'Base_DebitCard_0011')
331begin
332 execute sp_executesql @Cmd = N'Drop Table Base_DebitCard_0011'
333End
334CREATE TABLE [dbo].[Base_DebitCard_0011](
335 [CUST_SKEY] [varchar](30) NOT NULL,
336 [CCTYPE] [varchar](3) NULL,
337 [RMKEY] [varchar](20) NOT NULL,
338 [CCCARD] [numeric](19, 0) NOT NULL,
339 [CCCARD2] [numeric](19, 0) NULL,
340 [CCSTAT] [varchar](1) NULL,
341 [CCOPNDT] [char](10) NULL,
342 [CCCLSDT] [char](10) NULL
343) ON [PRIMARY]
344
345
346If Exists(Select * from sys.tables where name = 'Base_Transactions_DebitCard_0011')
347Begin
348 Execute sp_ExecuteSql @Cmd = N'Drop Table Base_Transactions_DebitCard_0011'
349End
350Create Table Base_Transactions_DebitCard_0011
351(
352 [CUST_SKEY] [Varchar](30) Not Null,
353 [TotalMonthlyTranAmt] [Decimal](20,2) Null,
354 [TotalMonthlyTran] [Int] Null,
355-- [TotalCycleTranAmt] [Decimal](20,2) Null,
356-- [TotalCycleTran] [Int] Null
357)
358
359If Exists(Select * from sys.tables where name = 'Base_RR_RecordCounts')
360begin
361 execute sp_Executesql @Cmd = N'Drop Table Base_RR_RecordCounts'
362end
363CREATE TABLE Base_RR_RecordCounts
364(
365 CustomerRecords Int NOT NULL,
366 CheckingRecords Int NOT NULL,
367 SavingsRecords Int NOT NULL,
368 InvestmentRecords Int NOT NULL,
369 LoansRecords Int NOT NULL,
370 DebitCardRecords Int NOT NULL
371
372) ON [PRIMARY]
373
374
375If exists(Select * from sys.views where name = 'v_Base_FileHeader')
376Begin
377 Execute sp_ExecuteSql @Cmd = N'Drop View v_Base_FileHeader'
378End
379Execute sp_ExecuteSql @Cmd = N'Create View v_Base_FileHeader
380As
381Select ''0000000000'' AS [Header ID], REPLACE(CAST(GETDATE() AS DATE), ''-'', '''') AS [Creation Date], ''0090'' AS [Segment Number],
382''0000000000'' AS [Sequence Number],''N'' AS Flag, ''RELAT'' AS Relat, NULL AS [Agent ID]
383'
384
385If exists(Select * from sys.views where name = 'v_Base_0001_Customer')
386Begin
387 Execute sp_ExecuteSql @Cmd = N'Drop View v_Base_0001_Customer'
388End
389Execute sp_ExecuteSql @Cmd = N'Create View v_Base_0001_Customer
390AS
391SELECT [RR Base].CUST_SKEY, ''0001'' AS [Product Code], NULL AS [Product ID], [RR Base].RMKEY AS [Relationship Account], [RR Base].CCCARD AS [Customer Card Number],
392 [RM Master].RMTIN AS [Customer Social Security Number], NULL AS [Customer Client Identifier], [RM Master].RMPFX AS [Customer Name Prefix],
393 [RM Master].RMFRST AS [Customer First Name], [RM Master].RMMIDL AS [Customer Middle Initial], [RM Master].RMLAST AS [Customer Last Name],
394 [RM Master].RMSFX AS [Customer Name Suffix], CASE LEN([RM Address].RASTN1)
395 WHEN 0 THEN [RM Address].RASTN1 ELSE [RM Address].RAST#1 + '' '' + [RM Address].RASTN1 END AS [Customer Address Line 1], CASE LEN([RM Address].RASTN2)
396 WHEN 0 THEN [RM Address].RASTN2 ELSE [RM Address].RAST#2 + '' '' + [RM Address].RASTN2 END AS [Customer Address Line 2], CASE LEN([RM Address].RASTN3)
397 WHEN 0 THEN [RM Address].RASTN3 ELSE [RM Address].RAST#3 + '' '' + [RM Address].RASTN3 END AS [Customer Address Line 3], NULL
398 AS [Customer Address Line 4], [RM Address].RACITY AS [Customer City], [RM Address].RASTA AS [Customer State/Province],
399 [RM Address].RAFRNC AS [Customer Country], [RM Address].RAZIP AS [Customer Zip Code], [RM Phone Home].RHAREA AS [Customer Home Phone Number 1],
400 [RM Phone Home].RHPHON AS [Customer Home Phone Number 2], [RM Phone Work].RHAREA AS [Customer Work Phone Number 1],
401 [RM Phone Work].RHPHON AS [Customer Work Phone Number 2], [RM Email].RIIADR AS [Customer Email Address],
402 [RM Demo].RPDOB AS [Customer Account Holders Birthday], [RM Master].RMADDT AS [Customer Join Date], [RM Master].RMOBRN AS [Customer Branch],
403 [Fact Summary Deposits].NmbrOfAccts AS [Customer Total Deposit Accounts], [Fact Summary Deposits].CurrentBalance AS [Customer Total Deposit Amount], [Fact Summary Loans].NmbrOfAccts AS [Customer Total Loan Accounts],
404 [Fact Summary Loans].CurrentLoanAmount AS [Customer Total Loan Amount]
405FROM dbo.v_Base_RR AS [RR Base] INNER JOIN
406 dbo.ods_RMMAST AS [RM Master] ON [RR Base].CUST_SKEY = [RM Master].CUST_SKEY LEFT OUTER JOIN
407 dbo.v_RMADDR_Customer_0001 AS [RM Address] ON [RR Base].CUST_SKEY = [RM Address].CUST_SKEY LEFT OUTER JOIN
408 dbo.v_RMPHON_Home_Customer_0001 AS [RM Phone Home] ON [RR Base].CUST_SKEY = [RM Phone Home].CUST_SKEY LEFT OUTER JOIN
409 dbo.v_RMPHON_Work_Customer_0001 AS [RM Phone Work] ON [RR Base].CUST_SKEY = [RM Phone Work].CUST_SKEY LEFT OUTER JOIN
410 dbo.v_RMINET_Customer_0001 AS [RM Email] ON [RR Base].CUST_SKEY = [RM Email].CUST_SKEY LEFT OUTER JOIN
411 dbo.ods_RMPDEM AS [RM Demo] ON [RR Base].CUST_SKEY = [RM Demo].CUST_SKEY INNER JOIN
412 dbo.Fact_Summary_Deposits_Customer_0001 AS [Fact Summary Deposits] ON [RR Base].CUST_SKEY = [Fact Summary Deposits].CUST_SKEY LEFT OUTER JOIN
413 dbo.Fact_Summary_Loans_Customer_0001 AS [Fact Summary Loans] ON [RR Base].CUST_SKEY = [Fact Summary Loans].CUST_SKEY
414'
415
416If exists(Select * from sys.views where name = 'v_Base_0002_Checking')
417Begin
418 Execute sp_ExecuteSql @Cmd = N'Drop View v_Base_0002_Checking'
419End
420Execute sp_ExecuteSql @Cmd = N'Create View v_Base_0002_Checking
421AS
422SELECT [Base Checking].CUST_SKEY, ''0002'' AS [Product Code], [Base Checking].ATDS AS [Product ID], [Base Checking].RMKEY AS [Relationship Account],
423 [Base Checking].CCCARD AS [Card Number], [Base Checking].DBACCT AS [Checking Account Number], [Base Checking].DBSTAT AS [Checking Account Status],
424 [Base Checking].DBDTOP AS [Checking Account Open Date], [Base Checking].DBDTCL AS [Checking Account Close Date],
425 [Base Checking].DBABMD AS [Checking Average Balance], 0.00 AS [Checking Ledger Balance], [Base Checking].DBCURB
426 AS [Checking Actual Balance], [Base Checking].DBOPDP AS [Checking Initial Deposit],
427 [Base Transactions].TotalMonthlyDepositAmt AS [Checking Total Deposit Amount - Month], 0.00 AS [Checking Direct Deposit Amount – Month], NULL
428 AS [Checking Direct Deposit Enrollment Date], NULL AS [Checking Direct Deposit End Date], NULL AS [Checking Direct Deposit Enrolled],
429 [Base Transactions].TotalMonthlyDeposits AS [Checking Deposits - Month], NULL AS [Checking Debit Card Issue Date], NULL AS [Checking Overdraft Protection], NULL
430 AS Filler
431FROM dbo.Base_DDSV_Combined AS [Base Checking] LEFT OUTER JOIN
432 dbo.Base_Transactions_Checking_0002 AS [Base Transactions] ON [Base Checking].CUST_SKEY = [Base Transactions].CUST_SKEY
433WHERE ([Base Checking].RXAPPL = ''DD'')
434
435'
436
437if exists(Select * from sys.views where name = 'v_Base_0003_Savings')
438begin
439 execute sp_executesql @cmd = N'Drop View v_Base_0003_Savings'
440end
441execute sp_executesql @cmd = N'Create View v_Base_0003_Savings
442AS
443SELECT [Base Savings].CUST_SKEY, ''0002'' AS [Product Code], [Base Savings].ATDS AS [Product ID], [Base Savings].RMKEY AS [Relationship Account],
444 [Base Savings].CCCARD AS [Card Number], [Base Savings].DBACCT AS [Savings Account Number], [Base Savings].DBSTAT AS [Savings Account Status],
445 [Base Savings].DBDTOP AS [Savings Account Open Date], [Base Savings].DBDTCL AS [Savings Account Close Date],
446 [Base Savings].DBABMD AS [Savings Average Balance], 0.00 AS [Savings Ledger Balance], [Base Savings].DBCURB AS [Savings Actual Balance],
447 [Base Savings].DBOPDP AS [Savings Initial Deposit], [Base Transactions].TotalMonthlyDepositAmt AS [Savings Total Deposit Amount - Month],
448 0.00 AS [Savings Direct Deposit Amount – Month], NULL AS [Savings Direct Deposit Enrollment Date], NULL AS [Savings Direct Deposit End Date], NULL
449 AS [Savings Direct Deposit Enrolled], [Base Transactions].TotalMonthlyDeposits AS [Savings Deposits - Month], NULL AS [Savings Debit Card Issue Date], NULL
450 AS [Savings Overdraft Protection], NULL AS Filler
451FROM dbo.Base_DDSV_Combined AS [Base Savings] LEFT OUTER JOIN
452 dbo.Base_Transactions_Savings_0003 AS [Base Transactions] ON [Base Savings].CUST_SKEY = [Base Transactions].CUST_SKEY
453WHERE ([Base Savings].RXAPPL = ''SV'')
454'
455
456If exists(Select * from sys.views where name = 'v_Base_0004_Investment')
457Begin
458 Execute sp_ExecuteSql @Cmd = N'Drop View v_Base_0004_Investment'
459End
460Execute sp_executesql @Cmd = N'Create View v_Base_0004_Investment AS
461SELECT [Investment Base].CUST_SKEY, ''0004'' AS [Product Code], [Investment Base].TMPROD AS [Product ID], [Investment Base].RMKEY AS [Relationship Account],
462 [Investment Base].CCCARD AS [Card Number], [Investment Base].TMACCT AS [Investment Account Number],
463 [Investment Base].TMSTAT AS [Investment Account Status], [Investment Base].TMDTOP AS [Investment Account Open Date],
464 [Investment Base].TMDTRD AS [Investment Account Close Date], [Investment Base].TMBALM AS [Investment Average Balance], 0.00 AS [Investment Ledger Balance],
465 [Investment Base].TMABAL AS [Investment Actual Balance], 0.00 AS [Investment Minimum Balance], [Investment Base].TMOPNB AS [Investment Initial Deposit],
466 [Transactions Base].TotalMonthlyDepositAmt AS [Investment Total Deposit Amount - Month], NULL AS [Investment Direct Deposit Amount - Month], NULL
467 AS [Investment Direct Deposit Enrollment Date], NULL AS [Investment Direct Deposit Enrollment End Date], NULL AS [Investment Direct Deposit Enrolled],
468 [Transactions Base].NmbrOfDeposits AS [Investment Deposits - Month], NULL AS [Investment Debit Card Issue Date], NULL AS Filler
469FROM dbo.Base_Investment_0004 AS [Investment Base] LEFT OUTER JOIN
470 dbo.Base_Transactions_Investment_0004 AS [Transactions Base] ON [Investment Base].CUST_SKEY = [Transactions Base].CUST_SKEY
471'
472
473If exists(Select * from sys.views where name = 'v_Base_0005_Loans')
474begin
475 execute sp_executesql @Cmd = N'Drop View v_Base_0005_Loans'
476end
477Execute sp_executesql @Cmd = N'Create View v_Base_0005_Loans As
478SELECT CUST_SKEY, ''0005'' AS [Product Code], ATDS AS [Product ID], RMKEY AS [Relationship Account], CCCARD AS [Card Number], LMACCT AS [Loan Account Number],
479 LMSTAT AS [Loan Account Status], LMNOTD AS [Loan Account Open Date], LMDTCL AS [Loan Account Close Date], 0.00 AS [Loan Average Balance],
480 LMCBAL AS [Loan Ledger Balance], LMCBAL AS [Loan Actual Balance], LMTTLO AS [Loan Original Amount], NULL AS [Loan Auto Pay Enrollment Date], NULL
481 AS [Loan Auto Pay End Date], NULL AS [Loan Auto Pay Enrolled], LMDTCL AS [Loan Satisfied Date], LMPDPR AS [Loan Account Outstanding],
482 LMDYSL AS [Loan Timely Payment], LMMATO AS [Loan Term], NULL AS Filler
483FROM dbo.Base_Loans_0005 AS [Base Loans]
484'
485
486If exists(select * from sys.views where name = 'v_Base_0011_DebitCard')
487begin
488 execute sp_executesql @cmd = N'Drop View v_Base_0011_DebitCard'
489end
490execute sp_Executesql @cmd = N'Create View v_Base_0011_DebitCard
491AS
492SELECT [Base Debit Card].CUST_SKEY, ''0011'' AS [Product Code], [Base Debit Card].CCTYPE AS [Product ID], [Base Debit Card].RMKEY AS [Relationship Account],
493 [Base Debit Card].CCCARD AS [Card Number], [Base Debit Card].CCCARD2 AS [Debit Card Number], [Base Debit Card].CCSTAT AS [Debit Account Status],
494 [Base Debit Card].CCOPNDT AS [Debit Account Open Date], [Base Debit Card].CCCLSDT AS [Debit Account Close Date],
495 [Base Transactions].TotalMonthlyTranAmt AS [Debit Transaction Amount – Month], [Base Transactions].TotalMonthlyTran AS [Debit Transactions – Month], NULL
496 AS [Debit Transaction Amount – Cycle], NULL AS [Debit Transactions – Cycle], NULL AS Filler
497FROM dbo.Base_DebitCard_0011 AS [Base Debit Card] LEFT OUTER JOIN
498 dbo.Base_Transactions_DebitCard_0011 AS [Base Transactions] ON [Base Debit Card].CUST_SKEY = [Base Transactions].CUST_SKEY
499
500'
501
502
503If exists(Select * from sys.views where name = 'v_Base_FileTrailer')
504begin
505 execute sp_executesql @Cmd = N'Drop View v_Base_FileTrailer'
506end
507Execute sp_executesql @Cmd =N'Create View v_Base_FileTrailer
508AS
509Select NULL As [TrailerID], Cast(CustomerRecords + CheckingRecords + SavingsRecords + InvestmentRecords + LoansRecords + DebitCardRecords As VARCHAR(MAX)) As [Detail Record Count],Null As [Filler]
510 From Base_RR_RecordCounts
511'
512
513If exists(Select * from sys.procedures where name = 's_RR_PreProcess')
514Begin
515 Execute sp_executesql @Cmd = N'Drop Procedure s_RR_PreProcess'
516End
517Execute sp_ExecuteSql @Cmd = N'Create Procedure s_RR_PreProcess
518As
519
520Begin
521
522--Get MonthStart and End Dates.
523Declare @MonthStartDate Decimal(9,0)
524Declare @MonthEndDate Decimal(9,0)
525
526Select @MonthStartDate = Cast(MonthStartDate As Decimal(9,0)),
527 @MonthEndDate = Cast(MonthEndDate As Decimal(9,0))
528 From fi_Date_ReferenceME
529
530
531--Customer_0001
532
533 --Generate a base table that contains the CUST_SKEY,Acct_Skey,Card Number, and [Counter] fields.
534--The records populated on this table contains a Savings/Savings account that meets the following criteria:
535 --Debit Card is Active.
536 --Primary Debit Card on Account.
537 --Savings Account is Active.
538 --Primary Customer.
539 --CC is the lowest card number with the lowest CC open date.
540
541Truncate Table Base_RR
542
543Insert Into Base_RR(CUST_SKEY,RMKEY,ACCT_SKEY,CCCARD,[Counter])
544Select [RM Reference].CUST_SKEY,[RM Master].RMKEY,[DD Master].ACCT_SKEY,[Debit Card].CCCARD,
545 ROW_NUMBER() Over (Partition By [RM Reference].CUST_SKEY Order By [RM Reference].CUST_SKEY,
546 [Debit Card].CCOPNDT,[Debit Card].CCCARD) As [Counter]
547 From ods_CACARD [Debit Card]
548 Inner Join ods_CARELACT [Card Relationship]
549 On [Debit Card].ACCT_SKEY = [Card Relationship].ACCT_SKEY
550 And [Debit Card].CCSTAT = ''A''
551 And [Card Relationship].CRPRI = ''Y''
552 Inner Join ods_DD_Master [DD Master]
553 On [Card Relationship].REL_ACCT_SKEY = [DD Master].ACCT_SKEY
554 And [DD Master].DBSTAT = ''1''
555 And [DD Master].APPL In (''DD'',''SV'')
556 Inner Join ods_RMXREF [RM Reference]
557 On [Card Relationship].REL_ACCT_SKEY = [RM Reference].ACCT_SKEY
558 And [RM Reference].RXPRIM = ''Y''
559 Inner Join ods_RMMAST [RM Master]
560 On [RM Reference].CUST_SKEY = [RM Master].CUST_SKEY
561
562Truncate table RMADDR_Customer_0001
563
564Insert Into RMADDR_Customer_0001(CUST_SKEY,[Counter],RAST#1,RAST#2,RAST#3,RASTN1,RASTN2,RASTN3,RACITY,RASTA,RAFRNC,RAZIP)
565Select CUST_SKEY,ROW_NUMBER() Over (Partition By CUST_SKEY Order By CUST_SKEY) As [Counter],
566 RAST#1,RAST#2,RAST#3,RASTN1,RASTN2,RASTN3,RACITY,RASTA,RAFRNC,RAZIP
567 From ods_RMADDR
568Order By CUST_SKEY
569
570Truncate Table RMPHON_Home_Customer_0001
571
572Insert Into RMPHON_Home_Customer_0001(CUST_SKEY,[Counter],RHAREA,RHPHON)
573Select CUST_SKEY,ROW_NUMBER() Over (Partition By CUST_SKEY Order By CUST_SKEY) As [Counter],RHAREA,RHPHON
574 From ods_RMPHON
575 Where RHPTYP = ''HOM''
576Order By CUST_SKEY
577
578Truncate Table RMPHON_Work_Customer_0001
579
580Insert Into RMPHON_Work_Customer_0001(CUST_SKEY,[Counter],RHAREA,RHPHON)
581Select CUST_SKEY,ROW_NUMBER() Over (Partition By CUST_SKEY Order By CUST_SKEY) As [Counter],RHAREA,RHPHON
582 From ods_RMPHON
583 Where RHPTYP = ''BUS''
584Order By CUST_SKEY
585
586Truncate Table RMINET_Customer_0001
587
588Insert Into RMINET_Customer_0001(CUST_SKEY,[Counter],RIIADR)
589Select CUST_SKEY,ROW_NUMBER() Over (Partition By CUST_SKEY Order By CUST_SKEY) As [Counter],RIIADR
590 From ods_RMINET
591 Where RIITYP = ''EML''
592Order By CUST_SKEY
593
594Truncate Table Fact_Summary_Deposits_Customer_0001
595
596Insert Into Fact_Summary_Deposits_Customer_0001(CUST_SKEY,CurrentBalance,NmbrOfAccts)
597Select [RM Reference].CUST_SKEY,
598 ISNULL(SUM([Fact Summary].CurrentBalance),0.00) As CurrentBalance,
599 ISNULL(COUNT(Distinct([RM Reference].RXACCT)),0) As NumOfAccts
600 From ods_RMXREF [RM Reference]
601 Inner Join v_Base_RR [RR Base]
602 On [RR Base].CUST_SKEY = [RM Reference].CUST_SKEY
603 Inner Join ods_Fact_Summary [Fact Summary]
604 On [RM Reference].ACCT_SKEY = [Fact Summary].AccountLKey
605 Where [RM Reference].RXPRIM = ''Y''
606 And [RM Reference].RXAPPL In (''DD'',''SV'',''CD'',''IR'')
607Group By [RM Reference].CUST_SKEY
608Order By [RM Reference].CUST_SKEY
609
610Truncate Table Fact_Summary_Loans_Customer_0001
611
612Insert Into Fact_Summary_Loans_Customer_0001(CUST_SKEY,CurrentLoanAmount,NmbrOfAccts)
613Select [RM Reference].CUST_SKEY,
614 ISNULL(SUM([Fact Summary].CurrentLoanAmount),0.00) As CurrentLoanAmount,
615 ISNULL(COUNT(Distinct([RM Reference].RXACCT)),0) As NumOfAccts
616 From ods_RMXREF [RM Reference]
617 Inner Join v_Base_RR [RR Base]
618 On [RR Base].CUST_SKEY = [RM Reference].CUST_SKEY
619 Inner Join ods_Fact_Summary [Fact Summary]
620 On [RM Reference].ACCT_SKEY = [Fact Summary].AccountLKey
621 Where [RM Reference].RXAPPL In (''LN'',''ML'',''RR'')
622 And [RM Reference].RXPRIM = ''Y''
623Group By [RM Reference].CUST_SKEY
624Order By [RM Reference].CUST_SKEY
625
626--Savings_0002 And Savings_0003 Records Combined
627Truncate Table Base_DDSV_Combined
628
629Insert Into Base_DDSV_Combined(CUST_SKEY,ATDS,RMKEY,CCCARD,DBACCT,DBSTAT,DBDTOP,DBDTCL,DBABMD,DBCURB,DBOPDP,RXAPPL)
630Select [RR Base].[CUST_SKEY],[FI Core Product].[Product Description] As ATDS,[RR Base].RMKEY,[RR Base].CCCARD,
631 [DD Master].DBACCT,[DD Master].DBSTAT,
632 [DD Master].DBDTOP,[DD Master].DBDTCL,[DD Master].DBABMD,[DD Master].DBCURB,
633 [DD Master].DBOPDP,[RM Reference].RXAPPL
634 From v_Base_RR [RR Base]
635Inner Join ods_RMXREF [RM Reference]
636 On [RR Base].CUST_SKEY = [RM Reference].CUST_SKEY
637 And [RM Reference].RXPRIM = ''Y''
638 And [RM Reference].RXAPPL In (''DD'',''SV'')
639Inner Join ods_DD_Master [DD Master]
640 On [RM Reference].ACCT_SKEY = [DD Master].ACCT_SKEY
641 And [DD Master].DBSTAT = ''1''
642Inner Join fi_Core_Product [FI Core Product]
643 On [DD Master].PROD_SKEY = [FI Core Product].[Product Key]
644Order By [RR Base].[CUST_SKEY],[RM Reference].RXAPPL
645
646Truncate Table Base_Transactions_Checking_0002
647
648Insert Into Base_Transactions_Checking_0002(CUST_SKEY,TotalMonthlyDepositAmt,TotalMonthlyDeposits)
649Select [Base RR].CUST_SKEY,
650 ISNULL(SUM([DD History].DDTAMT),0.00) As [TotalDepositAmt],
651 ISNULL(COUNT([DD History].ACCT_SKEY),0) As [TotalMonthlyDeposits]
652 From v_Base_RR [Base RR]
653 Inner Join ods_RMXREF [RM Reference]
654 On [Base RR].CUST_SKEY = [RM Reference].CUST_SKEY
655 And [RM Reference].RXPRIM = ''Y''
656 And [RM Reference].RXAPPL In (''DD'')
657 Left Join ods_DDAHIS [DD History]
658 On [RM Reference].ACCT_SKEY = [DD History].ACCT_SKEY
659 And [DD History].DDTRAN In (20,21,23)
660 And [DD History].DDTXDT Between @MonthStartDate And @MonthEndDate
661Group By [Base RR].CUST_SKEY
662Order By [Base RR].CUST_SKEY
663
664Truncate Table Base_Transactions_Savings_0003
665
666Insert Into Base_Transactions_Savings_0003(CUST_SKEY,TotalMonthlyDepositAmt,TotalMonthlyDeposits)
667Select [Base RR].CUST_SKEY,
668 ISNULL(SUM([DD History].DDTAMT),0.00) As [TotalDepositAmt],
669 ISNULL(COUNT([DD History].ACCT_SKEY),0) As [TotalMonthlyDeposits]
670 From v_Base_RR [Base RR]
671 Inner Join ods_RMXREF [RM Reference]
672 On [Base RR].CUST_SKEY = [RM Reference].CUST_SKEY
673 And [RM Reference].RXPRIM = ''Y''
674 And [RM Reference].RXAPPL In (''SV'')
675 Left Join ods_DDAHIS [DD History]
676 On [RM Reference].ACCT_SKEY = [DD History].ACCT_SKEY
677 And [DD History].DDTRAN In (20,21,23)
678 And [DD History].DDTXDT Between @MonthStartDate And @MonthEndDate
679Group By [Base RR].CUST_SKEY
680Order By [Base RR].CUST_SKEY
681
682
683--Investment_0004
684
685Truncate Table Base_Investment_0004
686
687Insert Into Base_Investment_0004(CUST_SKEY,TMPROD,RMKEY,CCCARD,TMACCT,TMSTAT,TMDTOP,TMDTRD,TMBALM,TMABAL,TMOPNB)
688Select [RR Base].CUST_SKEY,[TD Master].TMPROD,[RR Base].RMKEY,[RR Base].CCCARD,
689 [TD Master].TMACCT,[TD Master].TMSTAT,[TD Master].TMDTOP,
690 [TD Master].TMDTRD,[TD Master].TMBALM,[TD Master].TMABAL,[TD Master].TMOPNB
691 From v_Base_RR [RR Base]
692Inner Join ods_RMXREF [RM Reference]
693 On [RR Base].CUST_SKEY = [RM Reference].CUST_SKEY
694 And [RM Reference].RXPRIM = ''Y''
695 And [RM Reference].RXAPPL In (''CD'',''IR'')
696Inner Join ods_TD_Master [TD Master]
697 On [RM Reference].ACCT_SKEY = [TD Master].ACCT_SKEY
698 And [TD Master].TMSTAT = ''O''
699Order By [RR Base].CUST_SKEY
700
701Truncate Table Base_Transactions_Investment_0004
702
703Insert Into Base_Transactions_Investment_0004(CUST_SKEY,TotalMonthlyDepositAmt,NmbrOfDeposits)
704Select [Base RR].CUST_SKEY,
705 ISNULL(SUM([TD Activity].TACURB),0.00) As [TotalMonthlyDepositAmt],
706 ISNULL(COUNT([TD Activity].ACCT_SKEY),0) As [NmbrOfDeposits]
707 From v_Base_RR [Base RR]
708 Inner Join ods_RMXREF [RM Reference]
709 On [Base RR].CUST_SKEY = [RM Reference].CUST_SKEY
710 And [RM Reference].RXPRIM = ''Y''
711 And [RM Reference].RXAPPL In (''CD'',''IR'')
712 Left Join ods_TDACTV [TD Activity]
713 On [RM Reference].ACCT_SKEY = [TD Activity].ACCT_SKEY
714 And [TD Activity].TATXCD In (20,21)
715 And [TD Activity].TAPRDT Between @MonthStartDate And @MonthEndDate
716 Group By [Base RR].CUST_SKEY
717 Order By [Base RR].CUST_SKEY
718
719
720--Loans_0005
721Truncate Table Base_Loans_0005
722
723Insert Into Base_Loans_0005(ATDS,CUST_SKEY,RMKEY,CCCARD,LMACCT,LMSTAT,LMNOTD,LMDTCL,LMCBAL,LMCBAL2,LMTTLO,LMDTCL2,LMPDPR,LMDYSL,LMMATO)
724Select [FI Core Product].[Product Description] As ATDS,[RR Base].[CUST_SKEY],[RR Base].RMKEY,[RR Base].CCCARD,
725 [LN Master].LMACCT,[LN Master].LMSTAT,
726 [LN Master].LMNOTD,[LN Master].LMDTCL,[LN Master].LMCBAL,
727 [LN Master].LMCBAL As LMCBAL2,[LN Master].LMTTLO,[LN Master].LMDTCL As LMDTCL2,
728 [LN Master].LMPDPR,[LN Master].LMDYSL,[LN MASTER].LMMATO
729 From v_Base_RR [RR Base]
730Inner Join ods_RMXREF [RM Reference]
731 On [RR Base].CUST_SKEY = [RM Reference].CUST_SKEY
732 And [RM Reference].RXPRIM = ''Y''
733 And [RM Reference].RXAPPL = ''LN''
734Inner Join ods_LN_Master [LN Master]
735 On [RM Reference].ACCT_SKEY = [LN Master].ACCT_SKEY
736 And [LN Master].LMSTAT In (''A'',''M'')
737Inner Join fi_Core_Product [FI Core Product]
738 On [LN Master].PROD_SKEY = [FI Core Product].[Product Key]
739
740--DebitCard_0011
741Truncate Table Base_DebitCard_0011
742
743Insert Into Base_DebitCard_0011(CUST_SKEY,CCTYPE,RMKEY,CCCARD,CCCARD2,CCSTAT,CCOPNDT,CCCLSDT)
744Select [RR Base].CUST_SKEY,[Debit Card].CCTYPE,[RR Base].RMKEY,[RR Base].CCCARD,[Debit Card].CCCARD As CCCARD2,
745 [Debit Card].CCSTAT,[Debit Card].CCOPNDT,[Debit Card].CCCLSDT
746 From v_Base_RR [RR Base]
747 Inner Join ods_RMXREF [RM Reference]
748 On [RR Base].CUST_SKEY = [RM Reference].CUST_SKEY
749 And [RM Reference].RXPRIM = ''Y''
750 And [RM Reference].RXAPPL = ''CA''
751 Inner Join ods_CACARD [Debit Card]
752 On [RM Reference].ACCT_SKEY = [Debit Card].ACCT_SKEY
753 And [Debit Card].CCSTAT = ''A''
754Order By [RR Base].CUST_SKEY
755
756Truncate Table Base_Transactions_DebitCard_0011
757
758Insert Into Base_Transactions_DebitCard_0011(CUST_SKEY,TotalMonthlyTranAmt,TotalMonthlyTran)
759Select [Base RR].CUST_SKEY,
760 ISNULL(SUM([DD History].DDTAMT),0.00) As [TotalMonthlyTranAmt],
761 ISNULL(COUNT([DD History].DDTAMT),0) As [TotalMonthlyTran]
762 From v_Base_RR [Base RR]
763 Inner Join ods_RMXREF [RM Reference]
764 On [Base RR].CUST_SKEY = [RM Reference].CUST_SKEY
765 And [RM Reference].RXPRIM = ''Y''
766 And [RM Reference].RXAPPL In (''DD'',''SV'')
767 Left Join ods_DDAHIS [DD History]
768 On [RM Reference].ACCT_SKEY = [DD History].ACCT_SKEY
769 And [DD History].DDTRAN = 64
770 And [DD History].DDTXDT Between @MonthStartDate And @MonthEndDate
771Group By [Base RR].CUST_SKEY
772Order By [Base RR].CUST_SKEY
773
774Declare @CustomerRecords Int
775Declare @CheckingRecords Int
776Declare @SavingsRecords Int
777Declare @InvestmentRecords Int
778Declare @LoansRecords Int
779Declare @DebitCardRecords Int
780
781Select @CustomerRecords = COUNT(*) From v_Base_0001_Customer
782Select @CheckingRecords = COUNT(*) From v_Base_0002_Checking
783Select @SavingsRecords = COUNT(*) From v_Base_0003_Savings
784Select @InvestmentRecords = COUNT(*) From v_Base_0004_Investment
785Select @LoansRecords = COUNT(*) From v_Base_0005_Loans
786Select @DebitCardRecords = COUNT(*) From v_Base_0011_DebitCard
787
788Truncate Table Base_RR_RecordCounts
789Insert Into Base_RR_RecordCounts Values(@CustomerRecords,@CheckingRecords,@SavingsRecords,@InvestmentRecords,@LoansRecords,@DebitCardRecords)
790
791End
792'