if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cp_IT_alerts_update_Announce]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cp_IT_alerts_update_Announce]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cp_IT_get_returncodes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cp_IT_get_returncodes]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/*
SP Name:cp_IT_Alerts_Update_Announce
Purpose: called in maintenance site alert-form.asp
Date:         Author:       Description:
-----         -------        -------------
04/16/02   Rajini M.   update announcements.
02/03/2003   Rajini M.    Modified to get the language_code from dbo.tblReference_languages instead of dbo.tblBrowser_lgs
05/20/2003   Rajini M.	Modified to  accept delete_flag to activate and inactivate announcements.
*/
CREATE procedure dbo.cp_IT_alerts_update_Announce (
@node_id INT,
@parent_id INT,
@prod_code_id INT=null,
@BU nvarchar(25),
@Description nvarchar(4000),
@exp_date datetime =null,
@lg_code varchar(10)=null,
@del_flag bit =null
 )
AS

Update    dbo.tblAlerts
set   keyword= @BU,delete_flag=@del_flag,
   expiration_date=@exp_date
where    node_id = @node_id

update    dbo.tblAlerts_HTML
set node_HTML=@Description,
 language_code=@lg_code
where node_id = @node_id

update    dbo.tblAlertsXProducts
set product_code_id=@prod_code_id
where node_id = @node_id
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cp_IT_alerts_update_Announce]  TO [webuser]
GO

GRANT  EXECUTE  ON [dbo].[cp_IT_alerts_update_Announce]  TO [skyline]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE [dbo].[cp_IT_get_returncodes] 
@code nvarchar(10)=null,
@lg_code tinyint=null
--RM--Added language to sql statement code on 2/10/2003
--RM--Added distinct to sql statement 05/20/2003
As

declare @tempcode nvarchar(10)

select @tempcode = 'v_error' + COALESCE(@code,'') + '%'

if @lg_code is null 
	select @lg_code=1

select 	distinct keyword,errorcode,description,Resolution  
from 	dbo.tblMessages M
	left outer join tblErrorCodes_Resolution R on R.message_code=M.keyword  
where  	M.language_code=1 and keyword like @tempcode and errorcode is not null-- RM--Added the language code on 2/10/2003
order by keyword
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cp_IT_get_returncodes]  TO [webuser]
GO

GRANT  EXECUTE  ON [dbo].[cp_IT_get_returncodes]  TO [skyline]
GO