sp_sys_ERPTrigger

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

巴特西

sp_sys_ERPTrigger_base

最新文章

热门文章