SQL to import budget into Epicor p21

------SAVE LAST YEAR's DATA -----------

select * into newbudget2016 from newbudget

----DELETE DATA IN LAST YEAR TABLES ---------

delete from newbudget
delete from newbudget2


--------COPY AND PASTE FROM NEW EXEL SPREADSHEET INTO NEWBUDGET--------



-------ARE ALL ACCOUNTS VALID IN G/L ??-------------------

select a.account_no from newbudget as a
left join
p21_view_chart_of_accts as b
on a.account_no=b.account_no
where b.account_no is null
group by a.account_no



---------------------STEP 1 ---ZERO OUT EXISTING BUDGET ENTRIES---------------

--5 records
update balances set budget_1=0, cumulative_budget_1=0, date_budget_changed=GETDATE(), date_last_modified=GETDATE() from balances
inner join
(select top 100 percent a.* from balances as a
where budget_1<>0 and year_for_period=2017
and company_no='cpc'
) as b
on balances.company_no=b.company_no and balances.account_no=b.account_no and balances.period=b.period
and balances.year_for_period=b.year_for_period


select  * from balances where year_for_period=2017 and (budget_1<>0 or cumulative_budget_1<>0)


------------DATA CLEANUP ------------

update newbudget set Period1=0 where CHARINDEX('-',period1)>0
update newbudget set Period2=0 where CHARINDEX('-',period2)>0
update newbudget set Period3=0 where CHARINDEX('-',period3)>0
update newbudget set Period4=0 where CHARINDEX('-',period4)>0
update newbudget set Period5=0 where CHARINDEX('-',period5)>0
update newbudget set Period6=0 where CHARINDEX('-',period6)>0
update newbudget set Period7=0 where CHARINDEX('-',period7)>0
update newbudget set Period8=0 where CHARINDEX('-',period8)>0
update newbudget set Period9=0 where CHARINDEX('-',period9)>0
update newbudget set Period10=0 where CHARINDEX('-',period10)>0
update newbudget set Period11=0 where CHARINDEX('-',period11)>0
update newbudget set Period12=0 where CHARINDEX('-',period12)>0

update newbudget set Period1 = REPLACE(Period1,',','')
update newbudget set Period1 = REPLACE(Period1,'(','-')
update newbudget set Period1 = REPLACE(Period1,')','')

update newbudget set period2 = REPLACE(period2,',','')
update newbudget set period2 = REPLACE(period2,'(','-')
update newbudget set period2 = REPLACE(period2,')','')

update newbudget set period3 = REPLACE(period3,',','')
update newbudget set period3 = REPLACE(period3,'(','-')
update newbudget set period3 = REPLACE(period3,')','')

update newbudget set period4 = REPLACE(period4,',','')
update newbudget set period4 = REPLACE(period4,'(','-')
update newbudget set period4 = REPLACE(period4,')','')

update newbudget set period5 = REPLACE(period5,',','')
update newbudget set period5 = REPLACE(period5,'(','-')
update newbudget set period5 = REPLACE(period5,')','')


update newbudget set period6 = REPLACE(period6,',','')
update newbudget set period6 = REPLACE(period6,'(','-')
update newbudget set period6 = REPLACE(period6,')','')

update newbudget set period7 = REPLACE(period7,',','')
update newbudget set period7 = REPLACE(period7,'(','-')
update newbudget set period7 = REPLACE(period7,')','')


update newbudget set period8 = REPLACE(period8,',','')
update newbudget set period8 = REPLACE(period8,'(','-')
update newbudget set period8 = REPLACE(period8,')','')

update newbudget set period9 = REPLACE(period9,',','')
update newbudget set period9 = REPLACE(period9,'(','-')
update newbudget set period9 = REPLACE(period9,')','')

update newbudget set period10 = REPLACE(period10,',','')
update newbudget set period10 = REPLACE(period10,'(','-')
update newbudget set period10 = REPLACE(period10,')','')

update newbudget set period11 = REPLACE(period11,',','')
update newbudget set period11 = REPLACE(period11,'(','-')
update newbudget set period11 = REPLACE(period11,')','')


update newbudget set period12 = REPLACE(period12,',','')
update newbudget set period12 = REPLACE(period12,'(','-')
update newbudget set period12 = REPLACE(period12,')','')



-----------STEP 2 --POPULATE-NEW BUDGET-----------------

insert into newbudget2
select account_no, 1 as period, period1  
from newbudget 


insert into newbudget2
select account_no, 2 as period, period2 as amount 
from newbudget

--next

insert into newbudget2
select account_no, 3 as period, period3 as amount 
from newbudget

insert into newbudget2
select account_no, 4 as period, period4 as amount 
from newbudget

insert into newbudget2
select account_no, 5 as period, period5 as amount 
from newbudget

insert into newbudget2
select account_no, 6 as period, period6 as amount 
from newbudget

insert into newbudget2
select account_no, 7 as period, period7 as amount 
from newbudget

insert into newbudget2
select account_no, 8 as period, period8 as amount 
from newbudget

insert into newbudget2
select account_no, 9 as period, period9 as amount 
from newbudget

insert into newbudget2
select account_no, 10 as period, period10 as amount 
from newbudget

insert into newbudget2
select account_no, 11 as period, period11 as amount 
from newbudget

insert into newbudget2
select account_no, 12 as period, period12 as amount 
from newbudget


--check totals
select account_no, sum(amount) from newbudget2 group by account_no order by account_no


---CREATE CUMULATIVE BALANCE


select a.* from balances as a
inner join
(SELECT  top 100 percent a.account_no, a.period, a.amount,
(select sum(b.amount) from newbudget2 as b where b.period<=a.period and b.account_no=a.account_no) as cumulativebudget
from newbudget2 as a
order by a.account_no, a.period) as b
on a.account_no=b.account_no and a.period=b.period
and a.company_no='CPC' and a.year_for_period=2017

select a.* from balances as a
where a.company_no='CPC' and a.year_for_period=2016

--------ARE ALL BALANCES ALREADY IN BALANCES TABLE?

--21792 records not in existing balances table
--828 records in balances table already

select top 100 percent a.* from 
(SELECT  top 100 percent a.account_no, a.period, a.amount,
(select sum(b.amount) from newbudget2 as b where b.period<=a.period and b.account_no=a.account_no) as cumulativebudget
from newbudget2 as a
order by a.account_no, a.period) as a
left join 
(select a.* from balances as a
where a.company_no='CPC' and a.year_for_period=2017) as b
on a.account_no=b.account_no and a.period=b.period
where b.account_no is not null
order by a.account_no, a.period





------------UPDATE BUDGET WHEN RECORD FOR GL ACCOUNT ALREADY EXISTS IN BALANCES TABLE----------------

--828 rows

update balances set budget_1=a.newbudget, cumulative_budget_1=a.cumulativebudget, date_budget_changed=GETDATE(), date_last_modified=GETDATE() from balances
inner join
(select a.company_no, a.account_no,a.period, a.year_for_period, b.newbudget, b.cumulativebudget  from balances as a
inner join
(SELECT  top 100 percent a.account_no, a.period, a.amount as newbudget,
(select sum(b.amount) from newbudget2 as b where b.period<=a.period and b.account_no=a.account_no) as cumulativebudget
from newbudget2 as a
order by a.account_no, a.period) as b
on a.account_no=b.account_no and a.period=b.period
and a.company_no='CPC' and a.year_for_period=2017) as a
on balances.company_no=a.company_no and balances.year_for_period=a.year_for_period and balances.period=a.period and balances.account_no=a.account_no






------------------INSERT NEW RECORDS ------------------------------------


--21792


insert into balances (company_no,delete_flag,currency_id, cumulative_balance,period_balance, budget_2,budget_3, account_no,period, year_for_period, budget_1, date_created, date_last_modified, last_maintained_by, date_budget_changed,cumulative_budget_1)
select top 100 percent 'CPC','N',1,0,0,0,0,a.account_no, a.period, 2017, a.amount, GETDATE(),GETDATE(),'djohnson',GETDATE(),isnull(a.cumulativebudget,0)   from 
(SELECT  top 100 percent a.account_no, a.period, a.amount,
(select sum(b.amount) from newbudget2 as b where b.period<=a.period and b.account_no=a.account_no) as cumulativebudget
from newbudget2 as a
order by a.account_no, a.period) as a
left join 
(select a.* from balances as a
where a.company_no='XXX' and a.year_for_period=2017) as b
on a.account_no=b.account_no and a.period=b.period
where b.account_no is null
order by a.account_no, a.period


select distinct currency_id from balances


------------------TIE OUT FINAL BALANCE ----------------------------------------

select sum(budget_1) 
from balances as a
inner join
newbudget2 as b
on a.account_no=b.account_no
and a.period=b.period
and a.year_for_period=2017 and a.company_no='CPC'


select sum(budget_1) from balances where year_for_period=2017 and period=11
select sum(amount) from newbudget2 where period=11


select * from balances where last_maintained_by='djohnson'
Comments