Asset Module : Asset Depreciation Schedule Listing

Its budgeting time...!!! I was asked by our Dubai Finance Manager, if the e-Globe can produce asset depreciation schedule per month, group by cost center and asset group. My answer is No. Although in Asset Module, (through Asset maintain, then open one asset, then click 'forecast button') it can give you depreciation schedule by providing all the informations needed, but you need to open all your assets one-by-one then repeat the same process, which is very burdensome.

The Solution :

    Exact Globe Add-ins in Excel.

The Process :

** I created an SQL view to  format the columns needed for asset depreciation schedule

CREATE VIEW [dbo].[zUVw_Asset_DepreciationSchedule_2011_2012]

AS

SELECT TOP (100) PERCENT dbo.gbkmut.kstplcode AS CostCenter, dbo.gbkmut.bkjrcode AS Year, dbo.gbkmut.facode AS AssetCode, 

dbo.ItemNumbers.Description AS AssetDescription, dbo.ItemNumbers.AssetGroup AS AssetGroup, SUM(dbo.gbkmut.bdr_hfl) AS Total, 

SUM(CASE WHEN periode = 1 THEN bdr_hfl ELSE 0 END) AS January, SUM(CASE WHEN periode = 2 THEN bdr_hfl ELSE 0 END) AS February, 

SUM(CASE WHEN periode = 3 THEN bdr_hfl ELSE 0 END) AS March, SUM(CASE WHEN periode = 4 THEN bdr_hfl ELSE 0 END) AS April, 

SUM(CASE WHEN periode = 5 THEN bdr_hfl ELSE 0 END) AS May, SUM(CASE WHEN periode = 6 THEN bdr_hfl ELSE 0 END) AS June, 

SUM(CASE WHEN periode = 7 THEN bdr_hfl ELSE 0 END) AS July, SUM(CASE WHEN periode = 8 THEN bdr_hfl ELSE 0 END) AS August, 

SUM(CASE WHEN periode = 9 THEN bdr_hfl ELSE 0 END) AS September, SUM(CASE WHEN periode = 10 THEN bdr_hfl ELSE 0 END) AS October, 

SUM(CASE WHEN periode = 11 THEN bdr_hfl ELSE 0 END) AS November, SUM(CASE WHEN periode = 12 THEN bdr_hfl ELSE 0 END) AS December

FROM dbo.gbkmut INNER JOIN dbo.grtbk ON RTRIM(LTRIM(dbo.grtbk.reknr)) = RTRIM(LTRIM(dbo.gbkmut.reknr)) 

INNER JOIN dbo.ItemNumbers ON RTRIM(LTRIM(dbo.ItemNumbers.Number)) = RTRIM(LTRIM(dbo.gbkmut.facode))

WHERE(dbo.gbkmut.transtype = 'B') AND (dbo.gbkmut.transsubtype = 'V') AND (dbo.gbkmut.bkjrcode = 2011) AND (dbo.gbkmut.periode IN (10, 11, 12))

AND (dbo.grtbk.bal_vw = 'W') OR (dbo.gbkmut.transtype = 'B') AND (dbo.gbkmut.transsubtype = 'V') 

AND (dbo.gbkmut.bkjrcode = 2012) AND (dbo.gbkmut.periode BETWEEN 1 AND 9) AND (dbo.grtbk.bal_vw = 'W')

GROUP BY dbo.gbkmut.bkjrcode, dbo.gbkmut.facode, dbo.gbkmut.kstplcode, dbo.ItemNumbers.Description, dbo.ItemNumbers.AssetGroup

Note: the financial year is from Oct. (current year) To Sept. (next year)

** This second SQL script is for retrieving the data from SQL View via Exact Globe Excel Add-ins

SELECT  CostCenter, Year, AssetCode, AssetDescription, AssetGroup, Total, 

January, February,  March,  April, May, June, July, August, September, October, November, December

FROM dbo.zUVw_Asset_DepreciationSchedule_2011_2012

Where AssetCode = ?1

ORDER BY AssetCode, Year, CostCenter

Note: ?1 will be the Parameter for Exact Globe Excel Add-ins

 ** How to use Exact Globe Excel Add-ins

• Open Excel and go to Exact globe menu, select 'Reports', then select your company

• On Reports Screen, under Generate, scroll down and choice 'Select'

• Write the second script here

Note: if you want to use the Parameter field here or on the Excel row (see sample below)

That's all Folks..!!!!