ALTER TRIGGER [InsertStoreJITOnloadQuantity] ON [dbo].[Sourceing]
After INSERT
AS
--登記計劃數量(新增時YN=0)
Declare @ID int
Declare @MaterielCode varchar(50)
Declare @Typeofrefno varchar(50)
Declare @PlanQuantity float
Declare @NowQuantity float
Set @ID=0
Select @MaterielCode=MaterielCode,@Typeofrefno=Typeofrefno,@NowQuantity=Quantity from inserted where yn='' and typeofrefno<>'物料申請單'
IF @Typeofrefno<>'物料申請單' and @MaterielCode<>'' and @MaterielCode is not null
BEGIN
Set @PlanQuantity=@NowQuantity
Execute UpDateStoreJITQuantity 'Plan',@MaterielCode,'倉庫','','',@PlanQuantity,0,0,0
END
--登記到車頭薄
Declare @Refno varchar(20)
Declare @Date varchar(20)
Declare @Team varchar(20)
Declare @Routing varchar(50)
Declare @JobNo varchar(50)
Declare @FileNo varchar(20)
Declare @FullName varchar(254)
Declare @Account varchar(10)
Declare @ItemNo varchar(80)
Declare @ClientSort varchar(10)
Declare @EndTime varchar(20)
Declare @Deliverydate varchar(20)
Declare @UsedTime float
Declare @Model varchar(100)
Declare @Spectification varchar(254)
Declare @WorkShop varchar(50)
Declare @ThroughID int
Declare @RefrenceID int
Declare @ThroughtPutID int
Declare @MaterielName varchar(254)
Declare @Attribute varchar(20)
Declare @Quantity float
Declare @WBS varchar(20)
Declare @PoDeMID varchar(100)
Declare @MPSNo varchar(200)
Declare @DeliveryAdd varchar(100)
Declare @Account_P Varchar(20)
Declare @Provide varchar(100)
Declare @Country varchar(20)
Declare @ConName varchar(20)
Declare @SenderTel varchar(50)
Declare @PackMeans varchar(100)
Declare @CreationName varchar(20)
Declare @SenderName varchar(20)
Set @MPSNo=''
Set @ThroughID=0
Set @RefrenceID=0
Set @ThroughtPutID=0
Select @Refno=rtrim(isnull(Refno,'')),@Typeofrefno=isnull(Typeofrefno,''),@MaterielCode=rtrim(isnull(MaterielCode,0)),@MaterielName=isnull(MaterielName,''),@Spectification=isnull(Spectification,''),
@Model=isnull(Model,''),@Quantity=isnull(Quantity,0),@ClientSort=isnull(ClientSort,''),@JobNo=rtrim(isnull(JobNo,'')),@WBS=isnull(WBS,''),@Date=convert(varchar(20),Creationtime ,111 ),@FileNo=isnull(FileNo,''),
@FullName=isnull(FullName,''),@EndTime=convert(varchar(20),TragetRetrunTime,111),@Routing=rtrim(isnull(TypeofWork,'')),@Account=isnull(Account,''),@ItemNo=isnull(ItemNo,''),@ID=ID,@PoDeMID=isnull(PoDeMID,''),
@PackMeans=isnull(PackMeans,''),@Attribute=isnull(Attribute,''),@CreationName=isnull(CreationName,'') from inserted
Set @Team=''
IF @Typeofrefno='訂購單' or @Typeofrefno='訂箱單'
Begin
Set @Team='外購'
Set @Routing='備料'
End
IF @Typeofrefno='外發加工單'
Set @Team='外發'
IF @Typeofrefno='客供物品收貨單'
Begin
Set @Team='客供'
Set @Routing='客供'
End
Select @WorkShop=isnull(Account,''),@ThroughID=ID,@EndTime=case when (@EndTime is null or len(@EndTime)<=0 or datediff(hour,@Date,@EndTime)<=0) then convert(varchar(20),TragetRetrunTime,111) else @EndTime end,@DeliveryAdd=rtrim(DeliveryAdd),
@Account_P=rtrim(Account),@Provide=rtrim(Provide),@Country=Country,@ConName=ConName,@SenderTel=Provi_tele,@SenderName=Conname from Source_main where Refno=@Refno and TypeOfRefno=@Typeofrefno
IF len(@Team)>0
Begin
Set @UsedTime=datediff(hour,@Date,@EndTime)
IF cast(@UsedTime as float)<0
Set @UsedTime=8
Select @MPSNo=case when len(@MPSNo)>0 then (case when charindex(rtrim(MPSNo),@MPSNo)<=0 then @MPSNo+','+rtrim(MPSNo) else @MPSNo end) else MPSNo end,@Deliverydate=convert(varchar(20),CDeliveryDate ,111 ) from Po_list where charindex(convert(varchar(20),id),@PoDeMID)>0
IF len(@JobNo)>0
Begin
Select @RefrenceID=id from T_Throughput where jobno=@JobNo and Routing=@Routing and Refrenceid=0 and ID=TraceID
--UpDate T_Throughput set Quantity=isnull(Quantity,0)+@Quantity,fact='1',StartTime=@Date,EndTime=@EndTime,UsedTime=@UsedTime,WorkShop=@WorkShop from T_Throughput where jobno=@JobNo and Routing=@Routing and DynamicGroup='外發'
End
IF @RefrenceID is null or @Team='外發' or @Typeofrefno='訂箱單'
Set @RefrenceID=0
Select @ThroughtPutID=id from T_Throughput where Refno=@Refno and TypeofRefno=@Typeofrefno and MaterielCode=@MaterielCode-- and TargetCompleteDate=@EndTime
IF @ThroughtPutID=0 or @ThroughtPutID is null
BEGIN
Select @ThroughtPutID=id from T_Throughput where Refno=@Refno and TypeofRefno=@Typeofrefno and MaterielCode=@MaterielCode and JobNo=@Jobno and Routing=@Routing and Refrenceid=0 and ID=TraceID and len(rtrim(isnull(JobNo,'')))>0 and (ClientSort='工程單' or ClientSort='工作單')
END
--新增外發加工單時增加車頭簿 IF @ThroughtPutID=0 or @ThroughtPutID is null
Insert Into T_Throughput(Refno,TypeofRefno,MaterielCode,MaterielName,Spectification,Model,Quantity,ClientSort,JobNo,wbs,CreationName,StartTime,EndTime,UsedTime,StartTime1,EndTime1,UsedTime1,FactStartTime,Routing,FileNo,
FullName,Account,ItemNo,TJob3ID,ThroughID,Team,Device,TypeOfWork,TypeOfWorkNameList,WorkerNoList,WorkShop,RefrenceID,Categories,TargetCompleteDate,Deliverydate,TraceID,Attribute,DynamicGroup)
values (@Refno,@TypeofRefno,@MaterielCode,@MaterielName,@Spectification,@Model,@Quantity,@ClientSort,@JobNo,@WBS,User_Name(),@Date,@EndTime,@UsedTime,@Date,@EndTime,@UsedTime,@Date,
@Routing,@FileNo,@MaterielName,@Account, @ItemNo,@ID,@ThroughID,@Team+@WorkShop,@Team+@WorkShop,@Team+@WorkShop,@Routing,User_Name(),@WorkShop,@RefrenceID,@MPSNo,@EndTime,@Deliverydate,@RefrenceID,@Attribute,@Team+@WorkShop+'.生產') ELSE Update T_Throughput set Quantity=isnull(Quantity,0)+@Quantity,TargetCompleteDate=@EndTime,categories=case when charindex(@MPSNo,rtrim(isnull(categories,'')))>0 then rtrim(isnull(Categories,'')) else (case when len(rtrim(isnull(Categories,'')))>0 then @MPSNo +','+rtrim(isnull(Categories,'')) else @MPSNo end) end,
Refno=@Refno,TypeofRefno=@TypeofRefno,MaterielCode=@MaterielCode,ClientSort=@ClientSort,fact='',StartTime=@Date,EndTime=@EndTime,UsedTime=@UsedTime,WorkShop=@WorkShop,Team=@Team+@WorkShop,Device=@Team+@WorkShop where id=@ThroughtPutID End --入閘單(上貨單)
IF (@Typeofrefno='訂購單' or @Typeofrefno='外發加工單' or @Typeofrefno='客供物品收貨單')and (UPPER(@Account_P) not in('CHX','JDP','AC','WF0-XS','WH','FAT'))
Begin
Set @ThroughtPutID=0
Select @ThroughtPutID=id from Transportation where deliverygoodsno=@Refno and TypeofRefno=@Typeofrefno and FullName=@MaterielName
IF @ThroughtPutID=0 or @ThroughtPutID is null -- 插入
IF @CreationName in(select distinct workerno from T_employee)
Begin--本地產生入閘單
IF @Typeofrefno='訂購單' or @Typeofrefno='外發加工單'
Set @ClientSort='入閘單'
Else
Set @ClientSort='運輸通知單'
If @Typeofrefno='訂購單' or @Typeofrefno='客供物品收貨單' ---有運輸總表資料(外發無)
begin
Set @ThroughtPutID=0
Select @ThroughtPutID=id from TransportationDetial where Refno=@Refno and TypeofRefno=@ClientSort and Attribute=@TypeOfRefno
IF @ThroughtPutID=0 or @ThroughtPutID is null
Begin
Insert Into TransportationDetial(Refno,OutTime,ByPassWay,DeliverygoodsNo,Account,Creationtime,CreationName,Yn,TotalQuantity,Destination,RefnoP,TypeofRefno,Attribute,SenderTel,SenderName) values (@Refno,@EndTime,@Country,
@RefNo,@Account_P,GetDate(),User_Name(),0,@Quantity,@DeliveryAdd,@RefNo,@ClientSort,@TypeOfRefno,@SenderTel,@SenderName)
Set @ThroughtPutID=@@identity
End
ELSE
Begin
Update TransportationDetial set OutTime=@EndTime,ByPassWay=@Country,DeliverygoodsNo=@Refno,Account=@Account_P,Edittime=Getdate(),EditName=User_Name(),TotalQuantity=isnull(TotalQuantity,0)+@Quantity,Destination=@DeliveryAdd,RefnoP=@Refno,SenderTel=@SenderTel,Sendername=@Sendername where id=@ThroughtPutID
End
end
If @ThroughtPutID=0
Set @ThroughtPutID=Null
--子表
Insert into Transportation(ClientSort,deliverygoodsno,typeofrefno,fullname,packmeans,quantity,creationtime,creationname,toarea,destination,account,DeliveryAccount,CompanyName,occurid,OutTime,PID,WBS) values(
@ClientSort,@Refno,@Typeofrefno,@MaterielName,@PackMeans,@Quantity,getdate(),User_Name(),@Country,@DeliveryAdd,@Account,@Account_P,@Provide,@ID,@EndTime,@ThroughtPutID,@WBS)
End
Else--遠端產生上貨單
Begin
--子表
Insert into Transportation(ClientSort,deliverygoodsno,typeofrefno,fullname,packmeans,quantity,creationtime,creationname,toarea,destination,account,DeliveryAccount,CompanyName,occurid,OutTime,WBS) values(
'上貨單',@Refno,@Typeofrefno,@MaterielName,@PackMeans,@Quantity,getdate(),User_Name(),@Country,@DeliveryAdd,@Account,@Account_P,@Provide,@ID,@EndTime,@WBS)
End
Else -- 修改
IF @CreationName in(select distinct workerno from T_employee)
Begin
IF @Typeofrefno='訂購單' or @Typeofrefno='外發加工單'
Set @ClientSort='入閘單'
Else
Set @ClientSort='運輸通知單'
Update TransportationDetial set OutTime=@EndTime,ByPassWay=@Country,DeliverygoodsNo=@Refno,Account=@Account_P,Edittime=Getdate(),EditName=User_Name(),TotalQuantity=isnull(TotalQuantity,0)+@Quantity,Destination=@DeliveryAdd,RefnoP=@Refno where TypeofRefno=@ClientSort
and Refno=@Refno and Attribute=@TypeOfRefno
Update Transportation set ClientSort=@ClientSort,fullname=@MaterielName,packmeans=@Quantity,quantity=isnull(Quantity,0)+@Quantity,edittime=getdate(),editname=User_Name(),toarea=@Country,OutTime=@EndTime,
destination=@DeliveryAdd,account=@Account,DeliveryAccount=@Account_P,CompanyName=@Provide where id=@ThroughtPutID
End
Else
Begin
Update Transportation set ClientSort='上貨單',fullname=@MaterielName,packmeans=@Quantity,quantity=isnull(Quantity,0)+@Quantity,edittime=getdate(),editname=User_Name(),toarea=@Country,OutTime=@EndTime,
destination=@DeliveryAdd,account=@Account,DeliveryAccount=@Account_P,CompanyName=@Provide where id=@ThroughtPutID
End
End