declare @rgn varchar(3),

@usr_cd varchar (5000),

@usr_nm varchar (5000),

@eml_id varchar (5000),

@vc_mesgheading varchar(8000),

@vc_message varchar(8000),

@cntr_nm varchar(5000),

@rcpnt_cc varchar(8000)

select @rgn=rgn_cd from ccl_mst_cntr where cntr_cd=@srcicd and src_icd='zzzzz'

set @rcpnt_cc = ''

select @rcpnt_cc = @rcpnt_cc + ltrim(rtrim(a.email_id)) + '; '

from mst_user a,mst_user_role_link b where a.src_icd=b.src_icd and a.usr_empl_cd=b.usr_empl_cd

and b.role_cd='ROCSO'and a.actv='Y' and rgn_cd=@rgn

and isnull(email_id,' ')<>' ' and isnull(email_id,'.')<>'.'

--while exists(select 1 from #tempmail)

--begin

select top 1 @eml_id =email_id, @usr_nm=usr_empl_nm,@usr_cd=a.usr_empl_cd from mst_user a,mst_user_role_link b where a.src_icd=b.src_icd and a.usr_empl_cd=b.usr_empl_cd

and b.role_cd='SAPMM'and a.actv='Y' and rgn_cd=@rgn

and isnull(email_id,' ')<>' ' and isnull(email_id,'.')<>'.'

select @cntr_nm=cntr_nm from ccl_mst_cntr where cntr_cd=@srcicd

SELECT @vc_mesgheading = 'Dear '+ isnull( @usr_nm,'Friend')+',' + CHAR(13) + CHAR(13)

SELECT @vc_message = @vc_mesgheading + CHAR(13) +@cntr_nm

+ '(Storage location - "'+ isnull(@strloc,' ') +'") has received material against ' + CHAR(13)+

+ 'SAP STO number : '+ isnull(@sto,' ') + ' and Delivery Challan No. '+ isnull(@DCcd,' ') +''+ CHAR(13)+

+ 'dated '+ convert(varchar,@DCdt,103) + ' which have been entered in ENCORE stock ledger through'+ CHAR(13)+

+ 'MRR No. ' + isnull(@MMR,' ') +'.'+' dated '+ convert(varchar,@MMRdt,103) +' .'+ CHAR(13)+CHAR(13)

+ 'Please enter Goods Receipt in SAP.'+ CHAR(13)+ CHAR(13)+

'Regards, ' + CHAR(13) +

'Admin '

if((isnull(@eml_id,' ')<>' ') and (isnull(@rcpnt_cc,' ')<>' '))

begin

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Vistadb1',

@recipients = @eml_id,

@copy_recipients = @rcpnt_cc,

@blind_copy_recipients = 'ronyroyr@xxx.com;mail2_rony@xxx.com;',

@body = @vc_message,

@subject = 'Received Details' ;

end