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..!!!!

Exact Globe : The following data doesn't exist: Financial Entry

Today July 14, we encountered an error in Exact Globe during passing of simple financial entry (see below)

I tried looking the error message from customer portal, but I could not find any documents related to this problem. So, trial and error starts.

Error Message :

          The following data doesn't exist: Financial Entry

Observation:

     Upon checking on our Journal settings ( \System \Finance \Journals) select and open 1 journal, under General Tab, I saw a field "variable exchange rate" with tick mark. So, I go then to my currency and put the tick mark on "variable exchange rate". Alas..!!! it works, error is gone. (note: I was puzzled why this tick mark is required, since during passing of entries, I did not change the rate or the xrate field is diabled)

Solution:

        * go to \System \General \Countries \Currencies

        * select and open the currency used during passing of Journal Entries (in my case, EUR)

        * on General Tab, put tick mark on "variable exchange rate" , then save

        * pass again the same entries from scratch

Sending email for new Item: Inventory Module

Senior Accountants from one of our company investments told me that they want to know or to be alerted if an item is added in Inventory Module by Logistics Department automatically. Sad to say, this cannot be addressed by the system itself.

The problem :

     Adding item in Inventory will automatically select the first item group in (Item Card Finance Tab);. in most cases, the “miscellaneous group”. Logistics Staff normally doesn't care about this “tab”, because they don’t know the GL accounts to use for the item, unless told by the Accounting Department.

     When they (Logistics Staff) add a new item, they just fill-in the necessary information on Basic, Purchase/Sales and Inventory Tabs, then click Save. In most cases, the Finance Tab is left out. Sometimes, they forgot to phone Accounting Dept to inform of the new item and when there are fulfillments of the item, the effect in GL Accounts is wrong. See example below

New Item : Lipton Tea

Correct Item group

Item Group : Beans and Teas

Revenue Account : 4130 – Beans and Teas Sales

COGS Account : 5210 – Cost of Beans and Teas

Stock Account : 1710 – Inventory Beverages

System default group

Item Group : Small Wares or Miscellaneous

Revenue Account : 4100 – Miscellaneous Sales

COGS Account : 5200 – Cost of Miscellaneous

Stock Account : 1700 – Inventory Miscellaneous

The Solutions:

* Create SQL Trigger

* Set-up SQL Database Mail

* The SQL Script for the Trigger

USE [111]

GO

/****** Object:  Trigger [dbo].[Trg_ItemInsert_SendMail]    Script Date: 06/15/2011 12:07:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE trigger [dbo].[Trg_ItemInsert_SendMail] on [dbo].[Items]

--Instead of Insert

After Insert

As

Declare @ItemCode nchar(30), @ItemDesc nchar(60), @DBname nvarchar(50)

Declare @SysCreated nchar(30), @SysCreator int, @CreatorName nchar(50)

Declare @SubjectMsg nvarchar(100), @BodyMsg nchar(3000)

--

select @ItemCode = ItemCode from Inserted

select @ItemDesc = Description_0 from Inserted

select @SysCreated = convert(nchar(30),Syscreated) from Inserted

select @SysCreator = Syscreator from Inserted

select @CreatorName= ltrim(rtrim(first_name)) + ' ' + ltrim(rtrim(sur_name))

 from Humres where res_id = @syscreator

--variable

set @DBname = (select db_name())

Set @SubjectMsg = 'New Item is Created by - ' + @CreatorName

Set @BodyMsg =          'Dear Accounting,

            Please check the GL inventory account used by this new Item. Thank you.

                        Item Code : ' + @Itemcode +

                        'Item Description : ' + @ItemDesc

If @dbname = '111'

            Begin

            EXEC msdb.dbo.sp_send_dbmail

                        @profile_name = 'e-Globe Administrator',

                        @recipients = 'AcctgStaff1@yahoo.com;AcctgStaff2@yahoo.com',           

                        @copy_recipients = 'LogisticsStaff@yahoo.com',

                        @blind_copy_recipients = 'anthony@yahoo.com',

                        @subject = @SubjectMsg,

                        @body = @BodyMsg

            End

else

            Return

* Configuring Database Mail 

   How to: http://www.mssqltips.com/tip.asp?tip=1438

Here, whenever a new item is added by the Logistics Staff, a trigger will fire and will send an email to the Accounting Staff informing them that a new item is being added. The Accounting Staff will then edit the Item Card Finance Tab. Please note that the profile name used above should exist in the Database Mail.