USE [GalaxyPointDB24]
GO
/****** Object: StoredProcedure [dbo].[sp_zy_Process_scrap] Script Date: 11/26/2015 08:47:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--2015-08-24 Ht 建立
--功能：BPM_scrap 数据同步功能
--exec sp_zy_Process_scrap 219
ALTER procedure [dbo].[sp_zy_Process_scrap]
@OwnerUserAccount nvarchar(36), --流程申请人帐号
@SubFlowGuid nvarchar(36), --流程Guid
@scrap_id nvarchar(36), --scrap_id
@scrap_version nvarchar(20) --scrap_version
as
DECLARE @SubTaskId int --子流程TaskId
DECLARE @FlowCatId int --流程分类Id
DECLARE @FlowCatName nvarchar(100) --流程分类名称
DECLARE @FlowName nvarchar(100) --流程名称
DECLARE @OwnerUserId int --子流程申请人Id
DECLARE @OwnerUserName nvarchar(100) --申请人姓名
DECLARE @CompanyId int --申请人公司Id
DECLARE @NodeId int --节点Id
DECLARE @NodeName nvarchar(200) --节点名称
BEGIN
BEGIN TRANSACTION
SET NOCOUNT ON;
-- scrap Get
--select * into ZYSCRBC from ZY_scrap_m where RBC01 = @scrap_id
--select * into ZYSCRBD from ZY_scrap_s where RBD01 = @scrap_id
-- scrap Get
insert into ZY_scrap_m ([TaskId],[ACondom],[COMPANY],[CREATOR],[RBC01]
,[RBC02],[RBC03],[RBC04],[RBC05],[RBC06],[RBC07],[RBC08],[RBC09]
,[RBC10],[RBC11],[RBC12],[RBC13],[RBC14],[RBC15],[RBC16],[RBC17]
,[RBC18],[RBC19],[RBC20],[RBC21],[RBC22],[RBC23]
,[CONFORM_DATE],[CONFORM_NAME],[CONFORM_CODE]
,[LOCK_CODE],[LOCK_NAME],[LOCK_DATE])
select [TaskId],[ACondom],[COMPANY],[CREATOR],[RBC01]
,[RBC02],[RBC03],[RBC04],[RBC05],[RBC06],[RBC07],[RBC08],[RBC09]
,[RBC10],[RBC11],[RBC12],[RBC13],[RBC14],[RBC15],[RBC16],[RBC17]
,[RBC18],[RBC19],[RBC20],[RBC21],[RBC22],[RBC23]
,[CONFORM_DATE],[CONFORM_NAME],[CONFORM_CODE]
,[LOCK_CODE],[LOCK_NAME],[LOCK_DATE]
from ZYSCRBC where RBC01 = @scrap_id and RBC17 = @scrap_version
insert into ZY_scrap_s ([TaskId]
,[GridOrder],[COMPANY],[CREATOR],[RBD01],[RBD02]
,[RBD03],[RBD04],[RBD05],[RBD06],[RBD07] ,[RBD08],[RBD09],[RBD10]
,[RBD11],[RBD12],[RBD13],[RBD14] ,[RBD15],[RBD16],[RBD17],[RBD18]
,[RBD19],[RBD20],[RBD21],[RBD22],[RBD23]
,[RBD24],[RBD25],[RBD26],[RBD27],[RBD28],[RBD29],[BFL])
select [TaskId]
,[GridOrder],[COMPANY],[CREATOR],[RBD01],[RBD02]
,[RBD03],[RBD04],[RBD05],[RBD06],[RBD07] ,[RBD08],[RBD09],[RBD10]
,[RBD11],[RBD12],[RBD13],[RBD14] ,[RBD15],[RBD16],[RBD17],[RBD18]
,[RBD19],[RBD20],[RBD21],[RBD22],[RBD23]
,[RBD24],[RBD25],[RBD26],[RBD27],[RBD28],[RBD29],[BFL]
from ZYSCRBD where RBD01 = @scrap_id and RBD23 = @scrap_version
--获取流程信息
SELECT @FlowCatId = a.FlowCatId,@FlowCatName = a.FlowCatName,@FlowName = b.FlowName from SysBpmDProcessCategory a inner join
(SELECT FlowCatId,FlowGuid,FlowName from SYSBPMDProcess where FlowGuid = @SubFlowGuid) b on a.FlowCatId = b.FlowCatId ORDER BY a.FlowCatId DESC
--获取节点信息
SELECT @NodeId = NodeId,@NodeName = NodeName FROM SYSBPMDNode WHERE FlowGuid = @SubFlowGuid AND NodeType = 0 ORDER BY FlowGuid DESC
--获取提交人信息(必须使用UserId)
SELECT @OwnerUserId = UserId, @OwnerUserName = UserDspName,@CompanyId = CompanyId FROM SYS_USer WHERE UserAccount = @OwnerUserAccount ORDER BY UserId DESC
IF (@OwnerUserId IS NULL)
BEGIN
RAISERROR ('报废系统 触发工作流存储过程中 ''@OwnerUserId''变量没有获取到发起人ID无法触发流程' , 16, 1) WITH NOWAIT
ROLLBACK TRANSACTION
RETURN -1
END
--插入任务表数据
INSERT INTO SYSBPMITask(TaskName,FlowCatId,FlowCatName,FlowGuid,FlowName,OwnerUserId,OwnerUserName,OperatorUserId,OperatorUserName,SubmissionDate,
FinishDate,[Description],SerialNo,[Status],Priority,CompanyId,Active,Creater,CreateTime,Updater,UpdateTime)
VALUES(@FlowName,@FlowCatId,@FlowCatName,@SubFlowGuid,@FlowName,@OwnerUserId,@OwnerUserName,@OwnerUserId,@OwnerUserName,GETDATE(),NULL,'报废系统自动触发流程',
'',0,0,@CompanyId,1,@OwnerUserId,GETDATE(),@OwnerUserId,GETDATE())
--set @SubTaskId = @@identity
SET @SubTaskId=SCOPE_IDENTITY()
--插入过程表数据
INSERT INTO SYSBPMIProc(TaskId,FlowCatId,FlowCatName,FlowGuid,FlowName,NodeId,NodeName,NodeType,ActionLinkId,ActionLinkName,HandlerUserId,HandlerUserName,
ArrivalDate,OpenDate,FinishDate,Finished,FormFile,IsShare,IsRecede,IsAsk,RecedeFromProcId,Comment,AgentUserId,AgentUserName,OperatorUserId,OperatorUserName,
OwnerUserId,OwnerUserName,CompanyId,Active,Creater,CreateTime,Updater,UpdateTime)
VALUES(@SubTaskId,@FlowCatId,@FlowCatName,@SubFlowGuid,@FlowName,@NodeId,@NodeName,0,0,'',@OwnerUserId,@OwnerUserName,GETDATE(),NULL,NULL,0,'',0,0,0,0,'',0,'',@OwnerUserId,
@OwnerUserName,@OwnerUserId,@OwnerUserName,@CompanyId,1,@OwnerUserId,GETDATE(),@OwnerUserId,GETDATE())
-- Update
--Update ZY_scrap_m set TaskId = @SubTaskId, ACondom = 'DEMO', COMPANY = 'DEMO', CREATOR ='Auto_001' where RBC01 = @scrap_id
--Update ZY_scrap_s set TaskId = @SubTaskId, COMPANY = 'DEMO', CREATOR ='Auto_001' where RBD01 = @scrap_id
Update ZY_scrap_m set TaskId = @SubTaskId, ACondom = 'DEMO', COMPANY = 'DEMO' where RBC01 = @scrap_id and RBC17 = @scrap_version
Update ZY_scrap_s set TaskId = @SubTaskId, COMPANY = 'DEMO' where RBD01 = @scrap_id and RBD23 = @scrap_version
-- Used setting
declare @RBC14 nvarchar(10)
declare @RBC15 nvarchar(10)
declare @RBC16 nvarchar(10)
declare @RBC19 nvarchar(50) --type
declare @RBC20 nvarchar(50) --reason
declare @RBC21 nvarchar(50)
declare @RBC23 nvarchar(50)
select @RBC14 = RBC14, @RBC15 = RBC15, @RBC16 = RBC16, @RBC21 = RBC21, @RBC23 = RBC23 from ZY_scrap_m where RBC01 = @scrap_id and RBC17 = @scrap_version
set @RBC19 = (select top 1 case RBC19
when 'C' then '成品'
when 'B' then '半成品'
when 'Y' then '原料'
when 'H' then '耗材'
END
from ZY_scrap_m where RBC01 = @scrap_id and RBC17 = @scrap_version)
set @RBC20 = (select top 1 case RBC20
when 'C' then '正常损耗'
when 'E' then '工程损耗'
when 'P' then '品质异常'
END
from ZY_scrap_m where RBC01 = @scrap_id and RBC17 = @scrap_version)
Update ZY_scrap_m set
RBC14 = (select top 1 UserId from Sys_User where UserAccount = @RBC14),
RBC15 = (select top 1 UserId from Sys_User where UserAccount = @RBC15),
RBC16 = (select top 1 UserId from Sys_User where UserAccount = @RBC16),
RBC19 = @RBC19,
RBC20 = @RBC20,
RBC21 = (select top 1 UserId from Sys_User where UserAccount = @RBC21),
RBC23 = (select top 1 UserId from Sys_User where UserAccount = @RBC23)
where RBC01 = @scrap_id and RBC17 = @scrap_version
-- Update TaskName
UPDATE SysBpmITask SET [TaskName] = [TaskName] +(@scrap_id + '-' + @scrap_version) WHERE [TaskId] = @SubTaskId
-- fLag setting
UPDATE ZYSCRBC Set Flag_ID = 'Y' WHERE RBC01 = @scrap_id and RBC17 = @scrap_version
IF @@error <> 0 --发生错误
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN @SubTaskId --执行成功
END
COMMIT TRANSACTION
END