set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
alter proc [dbo].[CK_Flow_Sms]
@ObjType int,
@FTaskID int,
@FDataBase Nvarchar(50)
AS
begin
declare @Table nvarchar (20)
declare @TableName nvarchar (50)
declare @Fdocentry int
declare @FStep int
declare @FlowName nvarchar (50)
declare @Content nvarchar (200)
declare @Sql nvarchar (4000)
Select @TableName =Case @ObjType When 23 then N'报价单'
When 17 then N'订单'
When 15 then N'交货单'
When 13 then N'发票'
When 14 then N'贷项凭证'
When 16 then N'退货'
When 22 then N'采购订单'
When 20 then N'采购交货'
When 18 then N'采购发票'
When 19 then N'采购贷项凭证'
When 21 then N'采购退货'
When 67 then N'库存转储'
end ,
@Table =Case @ObjType When 23 then N'OQUT'
When 17 then N'ORDR'
When 15 then N'ODLN'
When 13 then N'OINV'
When 14 then N'ORIN'
When 16 then N'ORDN'
When 22 then N'OPOR'
When 20 then N'OPDN'
When 18 then N'OPCH'
When 19 then N'ORPC'
When 21 then N'ORPD'
When 67 then N'OWTR'
end
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select Top 1 @Fdocentry= FDocKey,@FlowName=FCheckFlowName,@FStep=FNextStep
from ck_flow_task
where FtaskId=@FTaskID
select @Sql= N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED if exists(select top 1 1 from sysobjects where name=N''FTemp'') drop table FTemp '
select @Sql=@Sql + N' Select Top 1 CardCode,CardName,DocNum,U_Opname,DocTotal into FTemp From ' +@FDataBase+N'..odrf'
+N' where docentry=' +cast(@Fdocentry as nvarchar)
exec(@Sql)
if exists(select top 1 1 from sysobjects where name=N'FTemp')
begin
select @Content=N'审批流程 '+''''+@FlowName+''''+N':由' +isnull(U_Opname,N'') +N'做的,合作伙伴 '+isnull(CardCode,N'')+N' '+ isnull(CardName,N'') +
N',金额:'+ cast( DocTotal as nvarchar)+N' 单号:' + cast(DocNum as nvarchar) +N'的' + isnull(@TableName,N'') +N'要求你审批.请回复'+cast(@FTaskID as nvarchar) +N'SP 加字母Y或N 进行审批'
from FTemp
drop table FTemp
end
declare @j int
declare @s nvarchar(100)
select @j=0
while @j< len(@Content)/69 +1
begin
select @s=substring(@Content,@j*69+1,@j*69+69)
insert T_SMSSendList( S_ReceiveCardNo ,S_SendContent )
select U.Fphone ,@s
from userinfo U ,ck_flow_step S
where CharIndex(U.FUserCode, S.FCheckers) > 0
and S.FstepId= @FStep
and isnull(@s,'')<>''
and isnull(Fphone,'')<>''
select @j=@j+1
end
end