Advanced Retail (UK) Ltd
Company Wiki

Until the Branch Specific functionality is added to Promotion setup, the branch specific promotions will need to be added by script, and in some cases missing ones will need to be added. The following query needs to be added for new stores.

all branch specific promo at newest stores

select pb.branch_no, pb.promo_num, branch_name, cast(h.promo_num as varchar) + ' - ' + h.description as promo into #existing_promos from [dbo].[promo_hdr] h
inner join promo_branch pb on pb.promo_num = h.promo_num
inner join branch b on b.branch_no = pb.branch_no
where branch_specific = 1 and valid_to > getdate()-1 and (b.branch_no = <new store no>)

identify barnes promos

select pb.branch_no, pb.promo_num, branch_name, cast(h.promo_num as varchar) + ' - ' + h.description as promo into #barnes_promos 
from [dbo].[promo_hdr] h
inner join promo_branch pb on pb.promo_num = h.promo_num
inner join branch b on b.branch_no = pb.branch_no
where branch_specific = 1 and valid_to > getdate()-1 and pb.branch_no = 214

identify all promos that the newest stores should have

drop table #all_promos
select b.branch_no, bp.promo_num  into #all_promos from branch b
cross apply (select promo_num from #barnes_promos) bp
where b.branch_no = <new store no> 

validation scripts - find missing promos for new stores

select * from #all_promos ap
where  not exists ( select * from #existing_promos ep where ep.promo_num = ap.promo_num and ep.branch_no = ap.branch_no)

select branch_no, count(*) from #all_promos group by branch_no
select branch_no, count(*) from #all_promos ap
where  not exists ( select * from #existing_promos ep where ep.promo_num = ap.promo_num and ep.branch_no = ap.branch_no)
group by branch_no

insert new store into promo_branch (check required fields)

begin transaction
insert into promo_branch (date_time, terminal_id, user_no, promo_num, branch_no, deleted)
select getdate(),1,1,promo_num, branch_no,0 from #all_promos ap
where  not exists ( select * from #existing_promos ep where ep.promo_num = ap.promo_num and ep.branch_no = ap.branch_no)
commit transaction 

resend all valid promotion details - all value promotions

select promo_num into #promos from promo_hdr where effective_from = '2016-09-01 00:00:00'
select * from promo_det where promo_num  in (select promo_num from #promos)

begin transaction 
update promo_hdr set exclusive = exclusive where promo_num  in (select promo_num from #promos)
update promo_det set condition_benefit = condition_benefit where promo_num  in (select promo_num from #promos)
update promo_parts set quantity = quantity where promo_num  in (select promo_num from #promos)
update promo_branch set terminal_id = terminal_id  where promo_num  in (select promo_num from #promos)
commit transaction