ALTER PROCEDURE kpi_UploadData

AS

BEGIN TRANSACTION

SELECT * INTO #zarinvreg
FROM OpenDataSource(
'Microsoft.Jet.OLEDB.4.0','Data Source="c:\KPIUpload\Data.xls";Extended properties=Excel 8.0')...zarinvreg_CURR$
IF @@ERROR <> 0  	BEGIN
				ROLLBACK TRANSACTION
				PRINT 'Rolled back'
				RETURN
			END

SELECT * INTO #zarageddebt
FROM OpenDataSource(
'Microsoft.Jet.OLEDB.4.0','Data Source="c:\KPIUpload\Data.xls";Extended properties=Excel 8.0')...zarageddebt_CURR$
IF @@ERROR <> 0  	BEGIN
				ROLLBACK TRANSACTION
				PRINT 'Rolled back'
				RETURN
			END

SELECT * INTO #baddebt
FROM OpenDataSource(
'Microsoft.Jet.OLEDB.4.0','Data Source="c:\KPIUpload\Data.xls";Extended properties=Excel 8.0')...baddebt_CURR$
IF @@ERROR <> 0  	BEGIN
				ROLLBACK TRANSACTION
				PRINT 'Rolled back'
				RETURN
			END

DECLARE @currentmonth varchar(20), @currentyear varchar(4)
set @currentmonth = datename(mm,dateadd(mm,-1,getdate())) 	--because they always run this on the 6th working day of month
								--Therefore run in December is November's data
set @currentyear =  datepart(yyyy,dateadd(mm,-1,getdate()))

INSERT INTO MonthData (MonthDataInt, MonthName, [Year], Published)
VALUES			(1, @currentmonth, @currentyear, 0) --it is updated to current month, i.e. month 0, when data is published. In meantime all tables have MonthData = 1

INSERT INTO zarinvreg ([Profit ctr], Customer, [Name 1], Docno, [Year], Itm, Type, [Inv ref], [Sales doc], Curr, [Clearing Doc], CoCd, [Doc date], 
[Amtin loccur], [Count],
			[Clearing Date], [LC2 amount], MonthData)
SELECT	[Profit ctr], Customer, [Name 1], [Doc#no#], [Year], Itm, Type, [Inv# ref#], [Sales doc#], [Curr#], [Clearing Doc], CoCd, [Doc# date], 
[Amt#in loc#cur#], [Count],
	[Clearing Date], [LC2 amount], 1 AS MonthData
FROM #zarinvreg
IF @@ERROR <> 0  	BEGIN
				ROLLBACK TRANSACTION
				PRINT 'Rolled back'
				RETURN
			END

--update division codes?
INSERT INTO zarageddebt (Branch, Customer, [Name 1], [Profit ctr], [Inv ref], Docno, Type, [Project Desc], [Sales doc], CoCd, [Doc date], 
Total, [1To90], [91To120], [121To150],
			[151To180], [181To365], MoreThan366, MonthData)
SELECT			Branch, Customer, [Name 1], [Profit ctr], [Inv# ref#], [Doc#no#], Type, [Project Desc], [Sales doc#], CoCd, [Doc# date], 
Total, [ 1  - 90], [91  - 120], [121 - 150],
			[151 - 180], [181 - 365], [>= 366], 1 AS MonthData
FROM #zarageddebt
IF @@ERROR <> 0  	BEGIN
				ROLLBACK TRANSACTION
				PRINT 'Rolled back'
				RETURN
			END 

--update division codes?
INSERT INTO baddebt (	CoCd, Customer, [Name 1], [Profit ctr], Docno, [Sales doc], [Project Desc], [Doc date], Total, Nett, [Bad Debt], MonthData)
SELECT			CoCd, Customer, [Name 1], [Profit ctr], [Doc#no#], [Sales doc#], [Project Desc], [Doc# date], Total, Nett, [O/S Bad Debt], 1 AS MonthData
FROM #baddebt
IF @@ERROR <> 0  	BEGIN
				ROLLBACK TRANSACTION
				PRINT 'Rolled back'
				RETURN 
			END

--update division codes?

DROP TABLE #zarinvreg
DROP TABLE #zarageddebt
DROP TABLE #baddebt

EXEC bonus_DataCorrection
EXEC kpi_UpdateData

COMMIT TRANSACTION

GO