could you explain more detail how you define your structure to calling Procedure ? i believe XI support DB cursor, as long the DB cursor part of your Procedure.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[s_SEND_SO_MAIL_New] @CATAFLAG varchar(1) AS
Create table #t1 (RESWK varchar(5),WEB_SONO varchar(20), VBELN varchar(10), BSTKD varchar(20),KETDAT varchar(14),
MATNR varchar(18),KWMENG varchar(8),NETPR varchar(16),SAP_PONO varchar(10),DN_No varchar(20),Creator varchar(9),AUART varchar(10))
Declare @WEB_SONO varchar(20),@Msg varchar(7000),@VBELN varchar(10),@MATNR varchar(18),@KWMENG varchar(8),@NETPR varchar(16),@SAP_PONO varchar(20),@DN_NO varchar(20)
Declare @strTitle varchar(500),@BSTKD varchar(20),@KETDAT varchar(14),@RESWK varchar(5),@Creator varchar(9),@AUART varchar(10)
--Set @strTitle='Web SO Number' + char(9) + 'SAP SO Number' + char(9) + 'Quantity' + char(9) + 'Price' + char(10) + char(13)
Set @strTitle='<Html><Body><Table border=' + char(39) + '1' + char(39) + ' cellSpacing=' + char(39) + '0' + char(39) + ' cellPadding=' + char(39) + '1' + char(39) + ' >'
Set @strTitle=@strTitle + '<Tr><Td>Plant</Td><Td>Web SO Number</Td><Td>Order Type</Td><Td>SAP SO Number</Td><td>Customer PO</td><Td>Required Date</Td><td>Vendor PartNo</Td><Td>Quantity</Td><TD>Price</Td><TD>SAP PO Number</Td><TD>Delivery No</Td><TD>Creator</Td></Tr> '
Set @Msg=''
if @CATAFLAG >''
Begin
DECLARE so_cursor cursor local for
select a.WEB_SONO, b.VBELN,BSTKD,isnull(a.SAP_PONO,'NA') as 'SAP_PONO',isnull(a.DN_NO,'NA') as 'DN_NO', isnull(b.RESWK,'') as 'RESWK' ,isnull(Creator,'') as 'Creator', b.AUART FROM SEND_SO_MAIL a, ZSDS11 b
WHERE a.MAIL='N' and a.WEB_SONO=b.WEB_SONO AND b.CATAFLAG=@CATAFLAG
End
else
Begin
DECLARE so_cursor cursor local for
select a.WEB_SONO, b.VBELN,BSTKD,isnull(a.SAP_PONO,'NA') as 'SAP_PONO',isnull(a.DN_NO,'NA') as 'DN_NO', isnull(b.RESWK,'') as 'RESWK' ,isnull(Creator,'') as 'Creator', b.AUART FROM SEND_SO_MAIL a, ZSDS11 b
WHERE a.MAIL='N' and a.WEB_SONO=b.WEB_SONO and b.CATAFLAG IS NULL
End
OPEN so_cursor
Fetch Next from so_cursor into @WEB_SONO,@VBELN,@BSTKD,@SAP_PONO,@DN_NO,@RESWK,@Creator, @AUART
while @@fetch_status=0
begin
INSERT INTO #t1 Select @RESWK,@WEB_SONO,@VBELN,@BSTKD,left(KETDAT,4) + '/' + substring(KETDAT,5,2) + '/' + right(KETDAT,2) as 'KETDAT',
MATNR,KWMENG,isnull(NETPR,'') as NETPR,@SAP_PONO,@DN_NO,@Creator,@AUART from ZSDS13 WHERE WEB_SONO=@WEB_SONO
Fetch Next from so_cursor into @WEB_SONO,@VBELN,@BSTKD,@SAP_PONO,@DN_NO,@RESWK,@Creator, @AUART
end
close so_cursor
deallocate so_cursor
Update #t1 Set SAP_PONO='NA' where SAP_PONO=''
update #t1 Set DN_No='NA' where DN_No=''
update #t1 Set NETPR='' WHERE NETPR IS NULL
update #t1 Set RESWK='' WHERE RESWK IS NULL
DECLARE so_cursor cursor local for
select RESWK,WEB_SONO,VBELN, BSTKD,KETDAT,MATNR,KWMENG,NETPR,SAP_PONO,DN_No, Creator, AUART FROM #t1
OPEN so_cursor
Fetch Next from so_cursor into @RESWK,@WEB_SONO,@VBELN, @BSTKD,@KETDAT,@MATNR,@KWMENG,@NETPR,@SAP_PONO,@DN_NO,@Creator, @AUART
while @@fetch_status=0
begin
--Set @Msg=@Msg + @WEB_SONO + char(9) + @VBELN + char(9) + @MATNR + char(9) + @KWMENG + char(9) + @NETPR + char(10) + char(13)
Set @Msg=@Msg + '<TR><TD>' + @RESWK + + '</Td><Td>' + @WEB_SONO + '</Td><Td>' + @AUART + '</Td><Td>' + @VBELN + '</Td><Td>' + @BSTKD + '</Td><Td>'
+ @KETDAT + '</Td><TD>' + @MATNR + '</Td><Td>' + @KWMENG + '</Td><Td>' + @NETPR + '</Td><Td>' + @SAP_PONO + '</Td><Td>'
+ @DN_NO + '</Td><TD>' + @Creator + '</Td></Tr>'
Update SEND_SO_MAIL SET MAIL='Y' WHERE WEB_SONO=@WEB_SONO
Fetch Next from so_cursor into @RESWK,@WEB_SONO,@VBELN, @BSTKD,@KETDAT,@MATNR,@KWMENG,@NETPR,@SAP_PONO,@DN_NO,@Creator, @AUART
end
close so_cursor
deallocate so_cursor
declare @to varchar(1000),@cc varchar(1000),@subj nvarchar(200),@User_Email varchar(1000)
if len(@Msg)>0
begin
Set @Msg= @strTitle + @Msg + '</Table></Body></Html>'
IF @CATAFLAG>''
Begin
Set @subj='Sales Order List! (Created by Web) '
Set @to='Shi.Daisy@***. com;Hu.Zhi-yuan@****.***.cn;'
Declare t_cursor cursor local for
select User_Email from v_SO_Manually_Id
open t_cursor
fetch next from t_cursor into @User_Email
while @@fetch_status=0
begin
Set @to=@to + @User_Email + ';'
fetch next from t_cursor into @User_Email
end
close t_cursor
deallocate t_cursor
End
Else
Begin
Set @to='IESCSalesStaff@***;'
Set @to=@to + ';Rau.Anita@*****;'
Set @subj='CTO SAP Sales Order List! '
End
Set @cc='TAOERPIT@****.com'
-- Set @subj='CTO SAP Sales Order List! '
exec s_Send_Html_Mail @to,@cc,@subj,@Msg
end
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.