Used for resnew, so that you can add months for a preset. It does things in bulk. This can happen because all months need to be created for each agreement type (or preset). So presets can be found at: select * from splr_preset, which defines what are the standard details for this "preset" or agreement type. Related to this should be a month list…

ExtendDeal.sql

begin tran
 declare @origDealId int
 set @origDealId=44439
 select * from vw_active_contract_deal_meters where deal_ik=@origDealId
-- declare variables for stored procedure
declare @contract_ik int  
declare @deal_agent_ik int
declare @deal_affiliate_usr_ik int
declare @deal_splr_preset_ik int 
declare @deal_splr_srvcplan_ik int
declare @deal_prop_typ_ik int
declare @deal_dsc  varchar(255)
declare @deal_index_addr [decimal](18, 4)
declare @deal_fixed_price [decimal](18, 2)  
declare @deal_percent_off [decimal](18, 2)  
declare @deal_report_fee [decimal](18, 2)  
declare @deal_accept_dt smalldatetime  
declare @deal_start_dt smalldatetime  
declare @deal_end_dt smalldatetime  
declare @cstmr_addr_ik varchar(4096) -- Meter in Deal '<ROOT><cstmr_addr ik = "1683"/><cstmr_addr ik = "1706"/></ROOT>'  
declare @deal_month varchar(8000) -- '<ROOT><deal_month ik="1" mnth="08/01/2005" fixed_price="10.00" ></deal_month><deal_month ik="2" mnth="09/01/2005" fixed_price="11.00"></deal_month></ROOT>'  
declare @deal_month_pricing varchar(8000)

 -- set most variables from view, because we are extending an existing one
 select @contract_ik=contract_ik,@deal_agent_ik=agent_ik,@deal_affiliate_usr_ik=0,
	@deal_splr_preset_ik = splr_preset_ik, @deal_splr_srvcplan_ik=splr_srvcplan_ik, @deal_prop_typ_ik=prop_typ_ik,
	@deal_dsc=dsc,@deal_index_addr=index_addr, @deal_fixed_price=fixed_price,
	@deal_percent_off=percent_off,@deal_report_fee=report_fee,@deal_accept_dt=GETDATE(),
	@deal_start_dt=deal_end_dt, @deal_end_dt=DATEADD(month,12,deal_end_dt),@cstmr_addr_ik=deal_cstmr_addr_ik
	  from  vw_active_contract_deal_meters where deal_ik= @origDealId

-- get all the  meters
set @cstmr_addr_ik=(select cstmr_addr_ik as [@ik] from vw_active_contract_deal_meters where deal_ik=@origDealId
	  for xml path ('cstmr_addr'), root('ROOT'))

-- create xml for the next 12 months
set @deal_month = (select n as [@ik],cast(dateadd(MONTH,n,mnth) as date) as [@mnth], fixed_price as [@fixed_price], prior_ind as [@prior_ind] from (
select ROW_NUMBER() over (partition by deal_ik order by mnth desc) as rn,*from deal_month dm where dm.deal_ik=@origDealId) A
join (Values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) as Num(n) on 1=1
where rn=1
for xml path ('deal_month'), root('ROOT'))
                                  
-- create xml for the price for the next 12 months, based on the last month's settings
set @deal_month_pricing=(select n as [@dm_ik], cog_component_ik as [@cc_ik] , chrg as[@cost],cast(dateadd(month,n,A.from_dt) as date) as [@mnth] from (
select ROW_NUMBER() over (partition by cog_component_ik order by from_dt desc) as rn, ccp.* from cog_component_period ccp join deal_month dm on dm.deal_month_ik=ccp.deal_month_ik and dm.deal_ik=@origDealId
) A
join (Values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) as Num(n) on 1=1
where rn=1
for xml path ('dmp'), root('ROOT'))
select @deal_month_pricing
declare @retval int

-- go ahead and execute Res_new Stored proc that should extend the deal
exec rgsp_insert_next_contract_deal @contract_ik=@contract_ik, @deal_agent_ik=@deal_agent_ik, @deal_affiliate_usr_ik=@deal_affiliate_usr_ik,@deal_splr_preset_ik=@deal_splr_preset_ik,
		@deal_splr_srvcplan_ik=@deal_splr_srvcplan_ik,@deal_prop_typ_ik=@deal_prop_typ_ik,@deal_dsc=@deal_dsc,
		@deal_index_addr=@deal_index_addr,@deal_fixed_price=@deal_fixed_price,@deal_percent_off=@deal_percent_off,@deal_report_fee=@deal_report_fee,
		@deal_accept_dt=@deal_accept_dt, @deal_start_dt=@deal_start_dt, @deal_end_dt=@deal_end_dt,@cstmr_addr_ik=@cstmr_addr_ik,@deal_month=@deal_month,@deal_month_pricing=@deal_month_pricing
		,@retval = @retval output

-- newly created dealId
		select @retval
-- what we have now created and should be "active"
select * from vw_active_contract_deal_meters where deal_ik=@retval
-- what used to be "active" and now should not be                           
select * from vw_active_contract_deal_meters where deal_ik=@origDealId
rollback tran

InsertPresetMonths.sql

/*
	Want to insert some periods' worth of presets for splr_preset_ik
	Warning:
	It seems that splr_Preset_mnth does not have any unique constraints preventing
	duplicate months for a preset. I'm thinking that this would cause an error.
  
  See: onenote documentation for more on this.
*/
begin tran
declare @PresetId int = (select top 1 splr_preset_ik from splr_preset where preset_nm='Nipsco-A')
declare @myT as table(
splr_preset_ik	int,
dt	smalldatetime,
basis	decimal(18,4),
ops	decimal(18,4),
qhedge	decimal(18,4),
buffer	decimal(18,4),
basis_float	decimal(18,4),
client_fixed decimal(18,4),
client_float	decimal(18,4)
)
insert into @myT
values (@PresetId , '2019-1-1' ,.2500 , .1500 , 0.0,0.0, 0.0, 0.0, 0.0 )
--select * from @myT
;with mycte as(
select * from @myT
union all
select t.splr_preset_ik,DATEADD(month,1, c.dt),t.basis,t.ops,t.qhedge,t.buffer,t.basis_float,t.client_fixed,t.client_float from @myT t join mycte c on t.splr_preset_ik=c.splr_preset_ik where c.dt < '12/1/2040'
)
insert into splr_preset_mnth
select * from mycte

 
 OPTION (Maxrecursion 10000) -- this isn't required; I believe default is 100
 select top 20 * from splr_preset_mnth order by dt desc
 rollback tran

WhatDealsNeedExtending.sql

 ; with mycte as(
 select ENROLL_SUBMIT,acct_nbr,New_Acct_Num,New_Meter,md.GCS_ID, am.cstmr_addr_ik,am.deal_cstmr_addr_ik,cstmr_ik from vw_active_contract_deal_meters am join
 OperationsV1.dbo.METER_DEFN md on left( replace(acct_nbr,'-',''),9) = md.New_Acct_Num and mtr_nbr=md.New_Meter
 join OperationsV1.dbo.METERS_IN_DEAL mid on mid.gcs_id=md.gcs_id --where New_Acct_Num='179861007'
 join deal_cstmr_addr dcsa on dcsa.deal_ik=am.deal_ik
   -- this date range needs to be updated, how should we do this?
 where deal_end_dt between '5/1/2018' and '5/3/2018' and am.active_ind=1 and ENROLL_SUBMIT=0 and dcsa.enroll_submit_ind=1
 ) 
 select distinct ad.active_ind,m.*
	--acct_nbr,mtr_nbr,New_Acct_Num,New_Meter,GCS_ID  
	from vw_active_contract_deal_meters ad join mycte m on m.acct_nbr=ad.acct_nbr and active_ind=1
	order by deal_ik desc