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.