· 6 years ago · Sep 10, 2019, 05:24 AM
1USE [Megavolt]
2GO
3/****** Object: StoredProcedure [pubsub].[BaseEnrollmentImporter] Script Date: 9/3/2019 8:53:15 AM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8/*
9begin tran
10set nocount on
11declare @e pubsub.Enrollment
12insert into @e
13(iEthicalId,FirstName,LastName,MailingAddress1,MailingAddress2,MailingAddressCity,MailingAddressState,MailingAddressZip,UtilityAccountNumber,UtilityPremiseId,ServiceAddress,ServiceAddressCity,ServiceAddressState,ServiceAddressZip,RatePlanType,MailInvoice,EmailInvoice)
14select
15EthicalId,
16FirstName,
17LastName,
18Address1,
19Address2,
20City,
21[State],
22Zip,
23AccountNumber,
24PremiseNumber,
25StreetAddress,
26sCity,
27sState,
28sZip,
29[Bill Template],
30case when BillSendType ='Paper' then 'True' else 'False' end,
31case when BillSendType = 'Email' then 'True' else 'False' end
32from
33zbremer.dbo.August2019Enrollment
34--select * from @e
35exec pubsub.BaseEnrollmentImporter @e
36rollback tran
37*/
38ALTER proc [pubsub].[BaseEnrollmentImporter]
39@pubenrollments pubsub.Enrollment readonly,
40@batchSize int = null,
41@fullDebug bit = null,
42@paymentpreferenceoverride bit = 0
43
44as
45begin
46
47SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
48set nocount on;
49
50set @batchSize =coalesce(@batchSize,1000)
51
52declare @StartedAt datetime = getdate()
53
54exec db.PrintNow '^^^^^^^^^^^^^^^ Megavolt''s Pub-Sub Community Solar Enrollment Flat File Importer version 1.0 ^^^^^^^^^^^^^^^'
55
56exec db.PrintNow'© 2019 CleanChoice Energy ™'
57
58select
59[MasterAccountType],
60[FirstName],
61[LastName],
62[EmailAddress],
63[BillingEnrollmentId],
64[IsPublished],
65[MailingAddressPhoneNumber],
66[MailingAddress1],
67[MailingAddress2],
68[MailingAddressCity],
69[MailingAddressState],
70[MailingAddressZip],
71[IsPickedUp],
72[iEthicalId],
73[AccountName],
74[MailingAddressFirstName],
75[MailingAddressLastName],
76[PortfolioId],
77[DateCreated],
78[PaymentMethodType],
79[PaymentProcessorToken],
80[Last4],
81[BillingAddress1],
82[BillingAddress2],
83[BillingCity],
84[BillingState],
85[BillingZip],
86[Default],
87[PaymentProcessorCustomerID],
88[BillingAddressCity],
89[BillingAddressState],
90[BillingAddressZip],
91[MailInvoice],
92[EmailInvoice],
93[AutoPay],
94[UtilityPremiseId],
95[UtilityAccountNumber],
96[ServiceAddress],
97[ServiceAddress2],
98[ServiceAddressCity],
99[ServiceAddressState],
100[ServiceAddressZip],
101[Utility],
102[UtilityMeterStatus],
103[StartDate],
104[UtilityMeterStatusId],
105[UtilityId],
106[SubscriberRate],
107[Escalator],
108[EscalatorUpdateDate],
109[PercentDiscountRate],
110[FlatDiscountAmount],
111[FixedFloor],
112[FlatLateFee],
113[PercentLateFee],
114[CancellationFee],
115[FlatFeeAmount],
116[RatePlanId],
117[ProductEnrollmentId],
118[EndDate],
119[RatePlanType],
120[UtilitySubscriberId],
121[NameKey], -- gonna use this as lockbox paymentId
122[PremiseId],
123[BillingAccountId],
124[BillingEnrollmentImportKey]
125into #ImportEnrollments
126from
127@pubenrollments
128option (recompile);
129
130declare @start int
131select
132*
133from
134#ImportEnrollments
135set @start=@@ROWCOUNT
136
137if @start > 0
138
139begin
140exec db.PrintNow 'Brought data into #ImportEnrollments table for the duration of this import session.'
141
142
143alter table #ImportEnrollments Add Id int identity(1,1)
144
145declare @MaxBillingEnrollmentId int = (select MAX(BillingEnrollmentId) from psbenrollment.MasterAccount)
146
147select @MaxBillingEnrollmentId
148
149
150/* Lets Do Inserts into the Tables */
151
152/* MasterAccount */
153
154exec db.PrintNow 'Data normalization and importing into psbenrollment.MasterAccount...'
155
156create table #x (IethicalId int,BillingEnrollmentId int,Id int identity(1,1))
157
158declare @mac int
159insert into psbenrollment.MasterAccount
160(
161MasterAccountType,
162FirstName,
163LastName,
164EmailAddress,
165IsPublished,
166MailingAddressPhoneNumber,
167MailingAddress1,
168MailingAddress2,
169MailingAddressCity,
170MailingAddressState,
171MailingAddressZip,
172iEthicalid
173)
174output inserted.BillingEnrollmentId,inserted.iEthicalid into #x (BillingEnrollmentId,IethicalId)
175
176select
177ie.MasterAccountType,
178case when ie.FirstName IS null then a.sFirstname else FirstName end,
179case when ie.LastName IS NULL then a.sLastname else LastName end ,
180case when ie.EmailAddress is null then a.sEmail else '' end,
1810 IsPublished,
182case when MailingAddressPhoneNumber IS null then a.sPhone1 else MailingAddressPhoneNumber end,
183case when MailingAddress1 is null then a.sBillingAddress1 else MailingAddress1 end,
184case when MailingAddress2 IS NULL then a.sBillingAddress1 else MailingAddress2 end ,
185case when MailingAddressCity is null then a.sBillingCity else MailingAddressCity end,
186case when MailingAddressState is null then a.sState else MailingAddressState end,
187case when MailingAddressZip is null then a.sZip else MailingAddressZip end,
188ie.iEthicalId
189from
190#ImportEnrollments ie
191inner join
192Megavolt.dbo.Accounts a on ie.iEthicalId = a.iEthicalID
193
194set @mac=@@ROWCOUNT
195
196exec db.PrintNow 'Just Imported {n0} records into the psbenrollment.MasterAccount table.',@mac
197
198declare @ieu int
199update ie
200set ie.BillingEnrollmentId = u.BillingEnrollmentId
201from
202#ImportEnrollments ie
203inner join
204#x u on ie.Id = u.Id
205
206set @ieu=@@ROWCOUNT
207
208exec db.PrintNow 'Just updated meta-data for the MasterAccount table to progress on : {n0} rows updated.',@ieu
209
210select iEthicalId,BillingEnrollmentId from #ImportEnrollments
211
212select
213PortfolioId,PortfolioName,LockboxDepositAccountId
214into #PortfolioLookup
215from
216[capacitor.cleanchoicelabs.io].ConductorReplica.dbo.Portfolio
217
218/*BillingAccount*/
219
220create table #bacholder (BillingEnrollmentId int,PortfolioId nvarchar(100))
221
222exec db.PrintNow 'Data normalization and importing into psbenrollment.BillingAccount...'
223
224declare @bac int
225
226
227insert into psbenrollment.BillingAccount
228(
229AccountName,
230EmailAddress,
231DateCreated,
232MailingAddressFirstName,
233MailingAddressLastName,
234MailingAddress1,
235MailingAddressCity,
236MailingAddressState,
237MailingAddressZip,
238BillingEnrollmentId,
239PortfolioId,
240IsPublished
241)
242output inserted.BillingEnrollmentId,inserted.PortfolioId into #bacholder(BillingEnrollmentId,PortfolioId)
243select
244case when ie.AccountName IS null then a.sFirstname +' '+a.sLastname else ie.AccountName end,
245case when ie.EmailAddress is null then a.sEmail else '' end,
246ie.DateCreated,
247case when ie.MailingAddressFirstName IS null then a.sFirstname end,
248case when ie.MailingAddressLastName IS null then a.sLastname end,
249case when ie.MailingAddress1 is null then a.sBillingAddress1 else ie.MailingAddress1 end,
250case when ie.MailingAddressCity is null then a.sBillingCity else ie.MailingAddressCity end,
251case when ie.MailingAddressState is null then a.sBillingState else ie.MailingAddressState end,
252case when ie.MailingAddressZip IS null then a.sBillingZip else ie.MailingAddressZip end,
253ie.BillingEnrollmentId,
254case when ie.PortfolioId IS NULL then pl.PortfolioId else NULL end,
255ie.IsPublished
256from
257#ImportEnrollments ie
258inner join
259Megavolt.dbo.Accounts a
260on ie.iEthicalId = a.iEthicalID
261left join #PortfolioLookup pl
262on ie.NameKey = pl.PortfolioName or ie.NameKey = pl.LockboxDepositAccountId
263where ie.BillingEnrollmentId is not null
264set @bac = @@ROWCOUNT
265
266exec db.PrintNow 'Just Imported {n0} records into the psbenrollment.BillingAccount table.',@Bac
267
268update ie
269set PortfolioId = u.PortfolioId
270from
271#ImportEnrollments ie
272inner join
273#bacholder u on ie.BillingEnrollmentId = u.BillingEnrollmentId
274
275/*PaymentMethod*/
276
277exec db.PrintNow 'Data normalization and importing into psbenrollment.PaymentMethod...'
278
279exec db.PrintNow 'note- this data is not required, checking to see if data exists first...'
280
281declare @pmcheck int
282select
283 [Type] = case when bt_stripe__Card_Id__c like 'ba%' then 'ACH' else 'CC' end
284 ,[PaymentProcessorToken] = bt.bt_stripe__Card_Id__c
285 ,PaymentProcessorCustomerID = bt.bt_stripe__Customer_Id__c
286 ,[Last4] = bt_stripe__Card_Last_4_Digit__c
287 ,[BillingAddress1] = bt.bt_stripe__Billing_Street__c
288 ,[BillingAddress2] = null
289 ,[BillingCity]= bt.bt_stripe__Billing_City__c
290 ,[BillingState] = bt.bt_stripe__Billing_State__c
291 ,[BillingZip] = bt.bt_stripe__Billing_Postal_Code__c
292 ,[Default] = null
293--into #PaymentMethod
294from
295#ImportEnrollments ie
296inner join
297Megavolt.dbo.Accounts a on ie.iEthicalId = a.iEthicalID
298inner join
299SFreplica.dbo.Account sa on a.iEthicalID=sa.Megavolt_Account_ID__c
300inner join
301SFreplica.dbo.bt_stripe__Payment_Method__c bt on sa.Id=bt.bt_stripe__account__c and bt_stripe__Payment_Method_Status__c in ('Valid','verified')
302where bt_stripe__Card_Id__c is null
303and bt_stripe__Customer_Id__c is null
304set @pmcheck=@@ROWCOUNT
305
306if @pmcheck = 0 goto paymentpreference
307
308else
309exec db.PrintNow 'No Payment Preferences were detected...'
310goto paymentmethod
311
312paymentmethod:
313
314
315declare @pmt int
316insert into psbenrollment.PaymentMethod
317(
318Type,
319PaymentProcessorToken,
320PaymentProcessorCustomerID,
321Last4,
322BillingAddress1,
323BillingAddress2,
324BillingCity,
325BillingState,
326BillingZip,
327[Default],
328BillingEnrollmentId,
329StartDate,
330IsPublished
331)
332select
333 [Type] = case when bt_stripe__Card_Id__c like 'ba%' then 'ACH' else 'CC' end
334 ,[PaymentProcessorToken] = bt.bt_stripe__Card_Id__c
335 ,PaymentProcessorCustomerID = bt.bt_stripe__Customer_Id__c
336 ,[Last4] = bt_stripe__Card_Last_4_Digit__c
337 ,[BillingAddress1] = bt.bt_stripe__Billing_Street__c
338 ,[BillingAddress2] = null
339 ,[BillingCity]= bt.bt_stripe__Billing_City__c
340 ,[BillingState] = bt.bt_stripe__Billing_State__c
341 ,[BillingZip] = bt.bt_stripe__Billing_Postal_Code__c
342 ,ie.[Default]
343 ,ie.BillingEnrollmentId,
344 GETDATE(),
345 0 IsPublished
346--into #PaymentMethod
347from
348#ImportEnrollments ie
349inner join
350Megavolt.dbo.Accounts a on ie.iEthicalId = a.iEthicalID
351inner join
352SFreplica.dbo.Account sa on a.iEthicalID=sa.Megavolt_Account_ID__c
353inner join
354SFreplica.dbo.bt_stripe__Payment_Method__c bt on sa.Id=bt.bt_stripe__account__c and bt_stripe__Payment_Method_Status__c in ('Valid','verified')
355
356set @pmt =@@ROWCOUNT
357
358exec db.PrintNow 'Just Imported {n0} records into the psbenrollment.PaymentMethod table.',@pmt
359
360--if @paymentpreferenceoverride = 0 goto manualpp else goto paymentpreference
361
362paymentpreference:
363
364/*PaymentPreference*/
365exec db.PrintNow 'Data normalization and importing into psbenrollment.PaymentPrference...'
366
367declare @ppi int
368
369insert into psbenrollment.PaymentPreference
370(
371EmailInvoice,
372MailInvoice,
373AutoPay,
374BillingEnrollmentId,
375IsPublished
376)
377
378select
379case when ie.EmailInvoice is NULL then case when sa.Bill_Type__c like 'Ebill' then 'True'
380 when sa.Bill_Type__c like 'Paper Bill' then 'False'
381 when Bill_Type__c is null and a.semail not like 'none@none.com' and isnull(a.semail,'') <> '' then 'True'
382 when Bill_Type__c is null and ( a.semail like 'none@none.com' or isnull(a.semail,'') = '' ) then 'False' end else ie.EmailInvoice end,
383case when ie.MailInvoice IS NULL then case when Bill_Type__c like 'Ebill' then 'False'
384 when Bill_Type__c like 'Paper Bill' then 'True'
385 when Bill_Type__c is null and a.semail not like 'none@none.com' and isnull(a.semail,'') <> '' then 'False'
386 when Bill_Type__c is null and ( a.semail like 'none@none.com' or isnull(a.semail,'') = '' ) then 'True' end else ie.MailInvoice end,
387case when ie.AutoPay IS NULL then case when sa.Bill_Type__c like 'Ebill' then 'True'
388 when Bill_Type__c like 'Paper Bill' then 'False'
389 when Bill_Type__c is null and a.semail not like 'none@none.com' and isnull(a.semail,'') <> '' then 'True'
390 when Bill_Type__c is null and ( a.semail like 'none@none.com' or isnull(a.semail,'') = '' ) then 'False' end else ie.AutoPay end ,
391ie.BillingEnrollmentId,
3920 as IsPublished
393
394from
395#ImportEnrollments ie
396inner join
397Megavolt.dbo.Accounts a on ie.iethicalId = a.iethicalId
398inner join
399SFReplica.dbo.account sa on sa.megavolt_account_id__c = ie.iEthicalId
400left join
401SFReplica.[dbo].[bt_stripe__Payment_Method__c] bt on sa.id = bt.bt_stripe__account__c and bt_stripe__Payment_Method_Status__c in ('Valid','verified')
402
403set @ppi =@@ROWCOUNT
404
405exec db.PrintNow 'Just Imported {n0} records into the psbenrollment.PaymentPreference table.',@ppi
406
407--manualpp:
408--begin
409--exec db.PrintNow 'You have chosen to manually over-ride the PaymentPreference fields, inserting those values now...'
410
411--declare @mpp int
412--insert into psbenrollment.PaymentPreference
413--(
414--EmailInvoice,
415--MailInvoice,
416--AutoPay,
417--BillingEnrollmentId,
418--IsPublished
419--)
420
421--select
422--ie.EmailInvoice,
423--ie.MailInvoice,
424--ie.AutoPay,
425--ie.BillingEnrollmentId,
426--0
427--from
428--#ImportEnrollments ie
429
430--set @mpp =@@ROWCOUNT
431
432--exec db.PrintNow 'Just manually over-wrote and Imported {n0} records into the psbenrollment.PaymentPreference table.',@mpp
433
434--end
435
436/*UtilitySubscriber*/
437
438exec db.PrintNow 'Data normalization and importing into psbenrollment.UtilitySubscriber...'
439
440
441
442declare @us int
443
444insert into [psbenrollment].[UtilitySubscriber]
445 ([StartDate]
446 ,[UtilityAccountNumber]
447 ,[EndDate]
448 ,[NameKey]
449 ,[BillingEnrollmentId]
450 ,[IsPublished]
451 )
452
453 select
454 [StartDate] = startdt
455 ,[UtilityAccountNumber] = case when a.iUtilityID = 185 then a.sUtilSuppAcntNum else a.sUtilAcntNum end
456 ,[EndDate] = null
457 ,[NameKey] = null
458 ,[BillingEnrollmentId] = ie.BillingEnrollmentId
459 ,0 IsPublished
460 from #ImportEnrollments ie
461 inner join
462 Megavolt.dbo.Accounts a on ie.iethicalid = a.iethicalid
463 inner join (select * ,row_number() over (partition by ethicalid order by enrollmentdate desc, solarstatusid) as rownum
464 from megavolt.dbo.SolarEnrollmentStatus) ses on a.iEthicalID = ses.EthicalID and ses.rownum = 1
465
466set @us=@@ROWCOUNT
467
468exec db.PrintNow 'Just Imported {n0} records into the psbenrollment.UtilitySubscriber table.',@us
469
470
471
472
473exec db.PrintNow 'Data normalization and importing into psbenrollment.Premise...'
474
475create table #utilityholder (BillingEnrollmentId int,UtilityId nvarchar(100),UtilityMeterStatus nvarchar(100))
476
477declare @premise int
478
479
480
481INSERT INTO [psbenrollment].[Premise]
482 (
483 [UtilityPremiseId]
484 ,[UtilityAccountNumber]
485 ,[ServiceAddress]
486 ,[ServiceCity]
487 ,[ServiceZip]
488 ,[Utility]
489 ,[UtilityMeterStatus]
490 ,[StartDate]
491 ,[BillingEnrollmentId]
492 ,[IsPublished]
493 )
494 output inserted.BillingEnrollmentId,inserted.Utility,inserted.UtilityMeterStatusId into #utilityholder(BillingEnrollmentId,UtilityId,UtilityMeterStatus)
495 Select
496 case when [UtilityPremiseId] IS null then a.sUtilAcntNum else UtilityPremiseId end,
497 case when UtilityAccountNumber IS NULL then a.sUtilAcntNum else UtilityAccountNumber end,
498 case when ServiceAddress IS NULL then RTRIM(ISNULL(a.sAddress1,'')+' '+ISNULL(a.saddress2,'')) else ServiceAddress end,
499 case when ServiceAddressCity IS NULL then a.sCity else ServiceAddressCity end,
500 case when ServiceAddressZip IS NULL then a.sZip else ServiceAddressZip end,
501 case when Utility IS NULL then bu.UtilityID else Utility end,
502 '25e01cc9-6312-4d55-9644-c52ed2b37fa8' as UtilityMeterStatus,--case when ie.UtilityMeterStatus IS NULL then ums.UtilityMeterStatusId else ie.UtilityMeterStatus end,
503 case when StartDate IS NULL then startdt else StartDate end,
504 BillingEnrollmentId,
505 0 IsPublished
506 from
507 #ImportEnrollments ie
508 inner join Megavolt.dbo.Accounts a on ie.iethicalid = a.iethicalid
509 inner join (select * ,row_number() over (partition by ethicalid order by enrollmentdate desc, solarstatusid) as rownum
510 from megavolt.dbo.SolarEnrollmentStatus) ses on a.iEthicalID = ses.EthicalID and ses.rownum = 1
511 inner join [capacitor.cleanchoicelabs.io].[ConductorReplica].[dbo].[UtilityMeterStatus] ums on ums.UtilityMeterStatus = 'Active'
512 inner join [capacitor.cleanchoicelabs.io].[ConductorReplica].[dbo].[Utility] bu on case when a.iUtilityID in (185) then 'XCel Energy'
513 when a.iUtilityID in (120) then 'Pepco DC'
514 when a.iUtilityID in (121) then 'Pepco MD'
515 when a.iUtilityID in (37,160,161) then 'ConEd' end = bu.companyName
516 set @premise=@@ROWCOUNT
517
518
519 exec db.PrintNow 'Just Imported {n0} records into the psbenrollment.Premise table.',@premise
520
521update ie
522set ie.Utility = u.UtilityId,
523 ie.UtilityId = u.UtilityId,
524 ie.UtilityMeterStatus = u.UtilityMeterStatus
525from
526#ImportEnrollments ie
527inner join
528#utilityholder u on ie.BillingEnrollmentId = u.BillingEnrollmentId
529
530
531 /*RatePlan*/
532 exec db.PrintNow 'Data normalization and importing into psbenrollment.RatePlan...'
533
534 create table #rateplancatcher (RatePlanId nvarchar(40), BillingEnrollmentId int,SubscriberRate nvarchar(100),Escalator nvarchar(100),)
535
536 declare @Rp int
537
538 insert into [psbenrollment].[RatePlan]
539 (
540 SubscriberRate,
541 Escalator,
542 BillingEnrollmentId,
543 IsPublished,
544 IsPickedUp,
545 RatePlanId
546 )
547 output inserted.RatePlanId, inserted.BillingEnrollmentId,inserted.SubscriberRate,inserted.Escalator into #rateplancatcher(RatePlanId,BillingEnrollmentId,SubscriberRate,Escalator)
548
549 select
550 case when ie.SubscriberRate IS NULL then sep.Rate else ie.SubscriberRate end,
551 case when ie.Escalator IS NULL then sep.EscalatorPercentage else ie.Escalator end,
552 ie.BillingEnrollmentId,
553 0 IsPublished,
554 0 IsPickedUp,
555 case when RatePlanType = 'Monthly' then '7de251c5-ca6e-4959-a49f-be9ca4966804'
556 when RatePlanType like 'FixedDiscount' then '98411e62-7810-4775-aca0-817765eaee30'
557 else '7de251c5-ca6e-4959-a49f-be9ca4966804' end
558 from
559 #ImportEnrollments ie
560 inner join Megavolt.dbo.Accounts a on a.iethicalid = ie.iEthicalID
561 inner join (select * ,row_number() over (partition by ethicalid order by enrollmentdate desc, solarstatusid) as rownum
562 from megavolt.dbo.SolarEnrollmentStatus) ses on a.iEthicalID = ses.EthicalID and ses.rownum = 1
563 inner join megavolt.dbo.SolarEnrollmentPlans sep on ses.SolarEnrollmentPlanID = sep.SolarEnrollmentPlanId
564 inner join Megavolt.dbo.SolarEnrollmentPlansType sept on sep.SolarPlanTypeID = sept.SolarPlanTypeID
565 --make dynamic later
566 set @Rp=@@ROWCOUNT
567
568 exec db.PrintNow 'Just Imported {n0} records into the psbenrollment.RatePlan table.',@Rp
569
570
571
572 update ie set RatePlanId = u.RatePlanId,
573 SubscriberRate = u.SubscriberRate,
574 Escalator = u.Escalator
575 from
576 #ImportEnrollments ie inner join
577 #rateplancatcher u on ie.BillingEnrollmentId = u.BillingEnrollmentId
578
579 /* Product Enrollment */
580exec db.PrintNow 'Data normalization and importing into the psbenrollment.ProductEnrollment table..'
581
582declare @pee int
583insert into psbenrollment.ProductEnrollment
584(
585BillingEnrollmentId,
586SubscriberRate,
587Escalator,
588EscalatorUpdateDate,
589PercentDiscountRate,
590FlatDiscountAmount,
591FixedFloor,
592FlatLateFee,
593PercentLateFee,
594CancellationFee,
595FlatFeeAmount,
596RatePlanId,
597StartDate,
598EndDate
599)
600
601select
602ie.BillingEnrollmentId,
603ie.SubscriberRate, -- has to be given for monthly when there is a FixedDiscount this will be 0
604case when RatePlanId ='98411e62-7810-4775-aca0-817765eaee30' then'0.00' else ie.Escalator end, --EscalatorPercent (Fixed Discount no Escalator)
605ie.EscalatorUpdateDate ,--EscalatorUpdateDate, this is likely incorrect for now
606case when RatePlanId = '98411e62-7810-4775-aca0-817765eaee30' then '0.05' else '0.00' end,--PercentDiscountRate,
607'0.00',--FlatDiscountAmount,
608'0.00',--FixedFloor,
609coalesce(sep.LatePaymentFee,null,'0.00'),--FlatLateFee,
610case when RatePlanId = '98411e62-7810-4775-aca0-817765eaee30' then'.02' else '.02' end,--PercentLateFee,
611case when ie.CancellationFee is null then sep.CancellationFeeFlat else ie.CancellationFee end, --CancellationFee ???
612'0.00',--FlatFeeAmount, --Subject to change in the future
613ie.RatePlanId,
614sep.AvailableStart, --Start,
615sep.AvailableEnd--EndDate
616from
617#ImportEnrollments ie
618inner join
619Megavolt.dbo.SolarEnrollmentStatus ses
620on ie.iEthicalId = ses.EthicalID
621inner join
622Megavolt.dbo.SolarEnrollmentPlans sep
623on ses.SolarEnrollmentPlanID = sep.SolarEnrollmentPlanID
624
625set @pee =@@ROWCOUNT
626
627 exec db.PrintNow 'Just Imported {n0} records into the psbenrollment.ProductEnrollment table.',@pee
628
629 /*BillingEnrollmentLookup*/
630 exec db.PrintNow 'Data normalization and importing into BillingEnrollmentLookup table...'
631
632 declare @bel int
633
634 insert into Megavolt.dbo.billingenrollmentlookup
635 (BillingEnrollmentId,iethicalid)
636 select
637 BillingEnrollmentId,
638 iEthicalId
639 from
640 #ImportEnrollments
641
642 set @bel=@@ROWCOUNT
643
644 exec db.PrintNow 'Just Imported {n0} records into the Megavolt.dbo.BillingEnrollmentLookup table.',@bel
645
646 select * from Megavolt.dbo.billingenrollmentlookup
647
648
649
650 /* checks */
651
652 declare @chkrp int
653 select * from #ImportEnrollments where RatePlanId is null
654 set @chkrp =@@ROWCOUNT
655 if @chkrp >0
656 exec db.PrintNow 'Warning! We are missing {n0} RatePlanIds',@chkrp
657 else
658 exec db.PrintNow 'We have RatePlans assigned! Let''s sure hope they are valid - I am sure it will work out!'
659
660
661 select * from #ImportEnrollments
662
663end
664
665else
666exec db.AssertFail 'You didn''t provide a query with data -FAIL!!!!!!'
667end
668cleanup:
669exec db.PrintNow '^^^^^^^^^^^^^^^ Megavolt''s Pub-Sub Community Solar Enrollment Flat File Importer version 1.0 took : {TD} ^^^^^^^^^^^^^^^',@StartedAt=@StartedAt