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'