Created
April 28, 2010 18:17
-
-
Save srkirkland/382485 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
IF object_id('tempdb..#records') IS NOT NULL | |
BEGIN | |
DROP TABLE #records | |
END | |
CREATE TABLE #records | |
( | |
new_id int IDENTITY(1,1), | |
old_id int, | |
[type] char(1), | |
[month] int, | |
[year] int, | |
userid uniqueidentifier, | |
statusid int, | |
reviewComment varchar(512), | |
salary float, | |
adjustment float, | |
adjustmentComment varchar(512) | |
) | |
IF object_id('tempdb..#entries') IS NOT NULL | |
BEGIN | |
DROP TABLE #entries | |
END | |
CREATE TABLE #entries | |
( | |
new_id int IDENTITY(1,1), -- new entry id | |
old_id int, -- old entry id | |
[type] char(1), | |
new_record_id int, -- the new id of the sheet it should be associated with | |
fundtypeid int, | |
projectid int, | |
financeid int, | |
comment varchar(256), | |
[date] int, [hours] float, activitytypeid int, adjustmentdate datetime, -- timesheet entries | |
expensetype int, expenseamount int, [description] varchar(128) -- expense sheet entries | |
) | |
-- transfer the information from the time/expense sheets | |
begin transaction | |
begin try | |
insert into #records (old_id, [type], [month], [year], userid, statusid, reviewComment, salary, adjustment, adjustmentComment) | |
select ID, 't', [month], [year], userid, statusid, reviewcomment, salary, adjustment, adjustmentComment | |
from timesheets | |
insert into #records (old_id, [type], [month], [year], userid, statusid, reviewComment) | |
select ID, 'e', [month], [year], userid, statusid, reviewcomment | |
from ExpenseSheets | |
insert into #entries (old_id, [type], new_record_id, fundtypeid, projectid, financeid, comment, [date], [hours], activitytypeid,adjustmentdate) | |
select ID, 't', r.new_id, fundtypeid, projectid, financeaccountid, comment, [date], [hours], activitytypeid, adjustmentdate | |
from timesheetentries tse | |
inner join #records r on tse.TimeSheetID = r.old_id and r.type = 't' | |
insert into #entries (old_id, [type], new_record_id, fundtypeid, projectid, financeid, comment, expensetype, expenseamount, [description]) | |
select ID, 'e', r.new_id, fundtypeid, projectid, financeaccountid, comment, expensetypeid, expenseamount, [description] | |
from ExpenseSheetEntries ese | |
inner join #records r on ese.ExpenseSheetID = r.old_id and r.type = 'e' | |
set identity_insert fsnep2.dbo.records on | |
insert into FSNEP2.dbo.Records (ID, [Month], [Year], UserId, StatusID, ReviewComment) | |
select new_id, [month], [year], userid, statusid, reviewComment | |
from #records | |
-- copy expense into the cost share records table | |
insert into FSNEP2.dbo.CostShareRecords (ID) | |
select new_id from #records where [type] = 'e' | |
-- copy expense into the time sheet records table | |
insert into FSNEP2.dbo.TimeRecords(ID, Salary) | |
select new_id, salary from #records where [type] = 't' | |
set identity_insert fsnep2.dbo.records off | |
set identity_insert fsnep2.dbo.entries on | |
insert into FSNEP2.dbo.Entries (ID, recordID, fundtypeid, projectid, financeaccountid, comment) | |
select new_id, new_record_id, fundtypeid, projectid, financeid, comment | |
from #entries | |
-- copy info to timerecordentries table | |
insert into FSNEP2.dbo.TimeRecordEntries (ID, [Date], [Hours], ActivityTypeID, AdjustmentDate) | |
select new_id, [date], [hours], activitytypeid, adjustmentdate | |
from #entries | |
where [type] = 't' | |
-- copy info to costshareentries table | |
insert into FSNEP2.dbo.CostShareRecordEntries(ID, ExpenseTypeID, ExpenseAmount, [description], Exclude) | |
select new_id, expensetype, expenseamount, [description], 0 | |
from #entries | |
where [type] = 'e' | |
set identity_insert fsnep2.dbo.entries off | |
-- insert the tracking information | |
insert into FSNEP2.dbo.RecordTracking (RecordID, StatusID, ActionDate, UserName) | |
select new_id, st.statusid, st.ActionDate, st.UserId | |
from SheetTracking st | |
inner join #records r on st.TimesheetID = r.old_id and r.[type] = 't' | |
where TimesheetID is not null and ExpenseSheetID is null | |
insert into FSNEP2.dbo.RecordTracking (RecordID, StatusID, ActionDate, UserName) | |
select new_id, st.statusid, st.ActionDate, st.UserId | |
from SheetTracking st | |
inner join #records r on st.ExpenseSheetID = r.old_id and r.[type] = 'e' | |
where TimesheetID is null and ExpenseSheetID is not null | |
commit transaction | |
end try | |
begin catch | |
rollback transaction | |
--Clean up and make sure the identity inserting is off | |
set identity_insert fsnep2.dbo.records off | |
set identity_insert fsnep2.dbo.entries off | |
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int | |
SELECT @ErrMsg = ERROR_MESSAGE(), | |
@ErrSeverity = ERROR_SEVERITY() | |
RAISERROR(@ErrMsg, @ErrSeverity, 1) | |
end catch | |
DROP TABLE #records | |
DROP TABLE #entries |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment