USE [eerp]
GO
/****** Object: StoredProcedure [dbo].[mfworkorderAutoconvert_sp] Script Date: 2018-06-29 08:28:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[mfworkorderAutoconvert_sp]
@trantype varchar(20) = null,
@workorderno varchar(20) =null
AS
declare @pproductfamily varchar(20)
declare @pworkorderdate varchar(20)
declare @pscheduledate varchar(20)
declare @pskuno varchar(20)
declare @pskuversion varchar(20)
declare @pskuname varchar(20)
declare @pcustpartno varchar(20)
declare @prouteid varchar(50)
declare @pworkorderqty int
declare @pworkordertype varchar(20)
declare @pproductionline varchar(20)
declare @pshift varchar(20)
declare @wotype varchar(20)
declare @kpskuno varchar(20)
declare @category varchar(20)
declare @vssntype varchar(20)
declare @vNextEvent varchar(20)
declare @vCurrentEvent varchar(20)
declare @nTargetQty int
declare @vPar1 varchar(20)
declare @vPar2 varchar(20)
declare @vPar3 varchar(20)
declare @vPar4 varchar(20)
declare @vPar5 varchar(20)
declare @vPar6 varchar(20)
declare @vPar7 varchar(20)
declare @vPar8 varchar(20)
declare @nPar1 varchar(20)
declare @nPar2 int
declare @nPar3 int
declare @vsysserialno varchar(20)
declare @vWeekofYear varchar(2)
declare @vYear varchar(2)
declare @nBaseNo int
declare @nBuildNo int
declare @bNew bit
declare @bFirsttime bit
declare @vindex varchar(20)
declare @vLabelFrom varchar(20)
declare @vLabelTo varchar(20)
declare @vConvertToBase34 varchar(4)
declare @seqno int
declare @skuno varchar(20)
declare @rohsstatus varchar(15)
Declare @kpcategoryskuno varchar(20)
declare @replaceno varchar(12)
declare @qty int
declare @Originalqty int
--for ecn valid project by lvliang 2011/10/31
declare @oldversion varchar(3)
declare @skuno73 varchar(20)
declare @version73 varchar(3)
declare @oldver73 varchar(3)
declare @cmcode varchar(10)
set @cmcode=''
declare @sapwotype varchar(10)
set @sapwotype=''
declare @controllevel varchar(10)
set @controllevel=''
IF @trantype IS NULL
BEGIN
RAISERROR 99999 'Transaction type can not be NULL.'
RETURN 100
END
if exists(select * From mfworkorder,sfb_file where mfworkorder.workorderdate='1900-01-01' and mfworkorder.scheduledate='1900-01-01'
and mfworkorder.workorderqty<>mfworkorder.finishedqty
and mfworkorder.workorderno=sfb_file.sfb01)
begin
update mfworkorder set mfworkorder.workorderdate=sfb_file.sfb81,mfworkorder.scheduledate=sfb_file.sfb13,mfworkorder.releaseddate=sfb_file.sfb13,mfworkorder.startdate=sfb_file.sfb13
from sfb_file where mfworkorder.workorderdate='1900-01-01' and mfworkorder.scheduledate='1900-01-01' and mfworkorder.workorderqty<>mfworkorder.finishedqty
and sfb_file.sfb01=mfworkorder.workorderno
end
IF @trantype='Convert'
BEGIN
BEGIN transaction
select @sapwotype=sfb02,@pworkorderdate=sfb81,@pscheduledate=sfb13,@pskuno=RTRIM(sfb05),@pskuversion=RTRIM(sfb07),@rohsstatus=RTRIM(rohsstatus),
@pcustpartno=sfb05,@pworkorderqty=sfb08 from sfb_file (nolock) where sfb01=@workorderno
select @pproductfamily=codevalue,@pskuname=PrintlabelDesc,@prouteid=sfcRoute from sfccodelike
(nolock) where skuno=@pskuno
if @pworkorderdate is null or len(@pworkorderdate)=0 or @pworkorderdate=''
begin
set @pworkorderdate=convert(char(10),getdate(),121) + ' 00:00:00'
end
--Added by Benin Xu 2006-07-28 22:30 begin
/* IF EXISTS(SELECT 1 FROM dbo.sfcroutedefb WITH(NOLOCK) WHERE routeid=@prouteid AND eventpoint='CELL-ASSY') BEGIN
IF NOT EXISTS(SELECT 1 FROM dbo.mfkpcategoryinfo WITH(NOLOCK) WHERE mainskuno=@pskuno) BEGIN
RAISERROR 99997 'Not found keypart in mfkpcategoryinfo'
GOTO accept
END
END*/
--Added by Benin Xu 2006-07-28 22:30 end
---add by don 2012-08-21 begin
IF LEN(@sapwotype) =0 or @sapwotype is null
Begin
RAISERROR 99999 'The Workorder type Is NULL﹐Please Check'
RETURN 100
End
select @wotype=controltype,@controllevel=controllevel From econtrolvalue where controlname ='workordertype' and controlvalue=@sapwotype
---add by don 2012-08-21 end
-- IF left(@workorderno,6) in( '000011','002248')
-- SET @wotype='PCBA'
-- ELSE IF left(@workorderno,6)='000012' or left(@workorderno,6)='000055' or left(@workorderno,6)='002249'
-- SET @wotype='WO-NORMAL-SA'
-- ELSE
-- SET @wotype='WO-REWORK-SA'
------------Add by CHIBL 2008/05/23 BEGIN
IF LEN(@pskuversion) =0
Begin
RAISERROR 99999 'The Workorder Version Is NULL﹐Please Check'
RETURN 100
End
------------Add by CHIBL 2008/05/23 END
--
IF not exists(select 1 from c_sap_station(nolock) where skuno=@pskuno)
Begin
RAISERROR 99996 'The skuno not mapping SAP﹐Please Config firstly'
GOTO accept
End
----------add by wyh 20170708 for WO Verison Control 不容許新PCBA 正常工單開工單version 低于此前工單 BEGIN
/*****
1 U58 機種 和95 ﹐96 ODM 不在管控范圍內
2 只限制前段PCBA 正常工單﹐ rework 和RMA 工單不考慮
3 只有A0 ,b0 這種版本管控﹐ 01,02 NPI 版本不管控
4 version 有effectivedate 生效時間的要求
****/
--PRINT @wotype
IF @wotype='PCBA REGULAR' and isnumeric(LEFT(@pskuversion,2))<>1 and (@pskuno not like 'U%' AND @pskuno not like '95%'AND @pskuno not like '96%')
and EXISTS(select top 1 1 from [192.168.88.50].eerp.dbo.SfcAgileChangeList(NOLock) where type='ECN' and skuno=@pskuno
AND (ASCII(SUBSTRING(UPPER(version),PATINDEX('%[A-Z]%',UPPER(version)),1))+SUBSTRING(UPPER(version),PATINDEX('%[0-9]%',UPPER(version)),1)
>ASCII(SUBSTRING(UPPER(@pskuversion),PATINDEX('%[A-Z]%',UPPER(@pskuversion)),1))+SUBSTRING(UPPER(@pskuversion),PATINDEX('%[0-9]%',UPPER(@pskuversion)),1))
---ascii(version)>ascii(@pskuversion)
and effectivedate<convert(datetime,@pworkorderdate,121) order by releasedate desc)
BEGIN
declare @agileversion varchar(10)
SELECT top 1 @agileversion=version FROM [192.168.88.50].eerp.dbo.SfcAgileChangeList WHERE type='ECN' and skuno=@pskuno ORDER BY effectivedate desc
RAISERROR 99997 '該工單版本低于Agile已經發行生效版本,無法Convert Wo,請通知PM,'
IF NOT EXISTS(SELECT TOP 1 1 FROM SFCWOVersionControl(NOLOCK) WHERE WO=@workorderno AND LOCK=0 AND version=@pskuversion) BEGIN
--select top 1 1 from [192.168.88.50].eerp.dbo.SfcAgileChangeList(NOLock) where type='ECN' and skuno=@pskuno
insert INTO SFCWOVersionControl
select Wo=@workorderno, version=RTRIM(@pskuversion) ,agileversion=@agileversion, skuno=RTRIM(@pskuno), Remark='該工單版本低于Agile已經發行生效版本,無法Convert Wo,請通知PM', LOCK=0,LASTEDITDT=GETDATE(), EDITBY='SFCSYSTEM'
END
GOTO accept
END
else
begin
-------------如果
IF EXISTS(SELECT TOP 1 1 FROM SFCWOVersionControl(NOLOCK) WHERE WO=@workorderno AND LOCK=0 ) BEGIN
UPDATE SFCWOVersionControl SET LOCK=1 WHERE WO=@workorderno AND LOCK=0
END
end
----------add by wyh 20170708 for WO Verison Control 不容許新PCBA 正常工單開工單version 低于此前工單 END
--add by elain for check whether the stations in c_sap_station are in the sfcroute 20110803
IF exists(select * from c_sap_station a,sfccodelike b
where a.skuno=b.skuno and a.sfcstation not in(
select eventpoint from sfcroutedefb c where b.sfcroute=c.routeid)
and a.skuno=@pskuno)
Begin
RAISERROR 99999 'There exist stations in SFC-SAP mapping(c_sap_station) but not in sfcroute. Please Config it firstly'
RETURN 100
End
--add by elain for check whether the stations in c_sap_station are in the sfcroute 20110803
--
---------add by chibl For Rohs WorkOrder--------
IF (@rohsstatus='R5' or @rohsstatus='R6')
begin
IF EXISTS(select skuno from convert_skumapping(nolock) where skuno=@pskuno and flag=1)
--IF EXISTS(select top 1 1 from convert_skumapping(nolock) where @skuno>=skuno and left(@skuno,9)=left(skuno,9) and flag=1)
begin
-- if (
-- (left(@workorderno,6) in ('000011','000012','002248','002249'))
-- or
-- (left(@workorderno,6) in ('000014','000015','000053','000054','002250','002251','002252','002253')
-- and exists(select top 1 1 From sfccontrol where controlname='Rohs+WO'
-- and (ltrim(rtrim(controlvalue))=@workorderno or '00'+ltrim(rtrim(controlvalue))=@workorderno or '0000'+ltrim(rtrim(controlvalue))=@workorderno) and ltrim(rtrim(comment))='+'))
-- )
if ((@wotype in ('PCBA REGULAR','MODEL REGULAR'))
or
(@wotype not in ('PCBA REGULAR','MODEL REGULAR')
and exists(select top 1 1 From sfccontrol where controlname='Rohs+WO'
and (ltrim(rtrim(controlvalue))=@workorderno or '00'+ltrim(rtrim(controlvalue))=@workorderno or '0000'+ltrim(rtrim(controlvalue))=@workorderno) and ltrim(rtrim(comment))='+'))
)
begin
SET @pskuversion=@pskuversion+'+'
end
else
begin
SET @pskuversion=@pskuversion+'+'
end
end
else
BEGIN
insert into convert_skumapping values(@pskuno,'system',getdate(),'1')
SET @pskuversion=@pskuversion+'+'
END
end
----------------------end------------------------------------
IF (SELECT count(*) FROM mfworkorder (nolock) WHERE workorderno = @workorderno) > 0
BEGIN
RAISERROR 99997 'This Record Already Exists In Table mfworkorder.'
GOTO accept
END
Else BEGIN
--add by lvliang 2011/10/31 for valid ecn to online
select @skuno73=skuno,@version73=version from wo_file where workorderno=@workorderno
select top 1 @oldversion=rtrim(skuversion) from mfworkorder where skuno=@pskuno order by workorderdate desc
if @oldversion is null
insert into ecn_wo values(@workorderno,@pskuno,@pskuversion,'NewSkuno','',@skuno73,@version73,0,'SYSTEM',getdate())
else if @oldversion<>@pskuversion
insert into ecn_wo values(@workorderno,@pskuno,@pskuversion,'VersionChange',@oldversion,@skuno73,@version73,0,'SYSTEM',getdate())
else if @skuno73 is not null
begin
select top 1 @oldver73=version from wo_file where workorderno<>@workorderno and skuno=@skuno73 order by lasteditdt desc
if @oldver73 is null
insert into ecn_wo values(@workorderno,@pskuno,@pskuversion,'73-NewSkuno','',@skuno73,@version73,1,'TEST',getdate())
else if @oldver73<>@version73
insert into ecn_wo values(@workorderno,@pskuno,@pskuversion,'73-VersionChange',@oldver73,@skuno73,@version73,1,'TEST',getdate())
end
-- if (left(@workorderno,6) in ('002248','002249','002250','002251','002252','002253')
-- or exists(select * From sfccontrol where controlname='E6SKUNO' and controlvalue=@pskuno ))
if (@controllevel='USE FOR E-AREA' or exists(select * From sfccontrol where controlname='E6SKUNO' and controlvalue=@pskuno ))
begin
set @cmcode='E6'
end
insert mfworkorder with(ROWLOCK)(
workorderno,
factoryid,
workorderdate,
scheduledate,
workordertype,
productiontype,
skuno,
skuversion,
skuname,
skudesc,
custpartno,
eeecode,
cmcode,
upccode,
productfamily,
productlevel,
productcolor,
productlangulage,
prioritycode,
shipcountry,
productdesc,
routeid,
orderno,
custpono,
compcode,
released,
releaseddate,
jobstarted,
startdate,
mrpartial,
mrcompleted,
cancelled,
closed,
closedate,
workorderqty,
finishedqty,
stockinrequestqty,
stockinprocessqty,
batchno,
batchdate,
batchseqno,
jobnote1,
jobnote2,
field1,
field2,
field3,
packageno,
lineseqnofrom,
lasteditby,
lasteditdt,
ssntype)
values ( @workorderno,
'ACGA',--ACEA
convert(datetime,@pworkorderdate),
convert(datetime,@pscheduledate),
@wotype,
'BTS',
@pskuno,
@pskuversion,
@pskuname,
'',
LTrim(RTrim(@pcustpartno)),
'',
@cmcode,
'',
LTrim(RTrim(@pproductfamily)),
'',
'',
'',
'99',
'',
'',
@prouteid,
'',
'',
'',
0,
'',
0,
'',
0,
0,
0,
0,
'',
@pworkorderqty,
0,
0,
0,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
getdate(),
'')
END
/********add by TanHao For Solve No Keypart Transfer into MfsysComponent 2010/02/02 Begin**********/
if EXISTS (select TOP 1 1 from sfcstationkpset(nolock) where partno not in (select partno from mfworkdetail where workorderno=@workorderno ) and skuno=@pskuno and eventpoint in ('SI-ASSY','CELL-ASSY','VI','PACKING','ASSY1','ASSY2','ASSY3') )
begin
declare cur_kpcategoryskuno cursor for
select partno,qty from sfcstationkpset(nolock) where skuno=@pskuno and partno not in (select partno from mfworkdetail where workorderno=@workorderno ) and eventpoint in ('SI-ASSY','CELL-ASSY','VI','PACKING','ASSY1','ASSY2','ASSY3')
open cur_kpcategoryskuno
fetch next from cur_kpcategoryskuno into @kpcategoryskuno,@qty
while (@@fetch_status) <> -1
begin
select @replaceno =max(replaceno)+1 from mfworkdetail where workorderno=@workorderno
insert into mfworkdetail(
workorderno,
parentpartno,
partno,
seqno,
version,
replaceno,
replacetopartno,
custpartno,
requestqty,
issuedqty,
installedqty,
rtnrequestqty,
returnqty,
scrapqty,
qtybase,
qtyuom,
ismaterial,
consumptionitem,
keypart,
packmaterial,
unitcost,
field1,
field2,
categoryname,
prodcategoryname,
prodtype,
originalqty,
fromwhid,
lasteditby,
lasteditdt
)
values
(
@workorderno,
@pskuno,
@kpcategoryskuno,
'0000',
@pskuversion,
@replaceno,
'',
'',
@pworkorderqty,
'0.00000',
'0.00000',
'0.00000',
'0.00000',
'0.00000',
0,
'',
1,
0,
1,
0,
0.00,
'',
'',
'',
'',
'add',
@qty,
'',
'',
getdate()
)
fetch next from cur_kpcategoryskuno into @kpcategoryskuno,@qty
end
close cur_kpcategoryskuno
deallocate cur_kpcategoryskuno
end
/********add by TanHao For Solve No Keypart Transfer into MfsysComponent 2010/02/02 END**********/
IF (SELECT count(*) FROM mfworklayout (nolock) WHERE workorderno = @workorderno) > 0
BEGIN
RAISERROR 99996 'This Record Already Exists In Table mfworklayout.'
GOTO accept
END
Else BEGIN
insert mfworklayout with(ROWLOCK)(
workorderno,
assigndate,
factoryid,
productionline,
shift,
released,
releasedate,
releaseby,
targetqty,
finishedqty,
lasteditby,
lasteditdt)
values ( @workorderno,
convert(datetime,@pworkorderdate),
'ACGA',--ACEA
'LINE 1',
'SHIFT 1',
0,
'',
'',
@pworkorderqty,
0,
'',
getdate())
END
--Modified by Alex Xu 2005-10-09 begin
select @skuno=sfb05 from sfb_file where sfb01=@workorderno
---MarK by WYH 2010/02/06
-- if left(@workorderno,6)='000011' or left(@workorderno,6)='000012' or left(@workorderno,6)='000055' --Commented by Benin Xu 2006-07-24
-- if left(@workorderno,6)='000011' or left(@workorderno,6)='000012' or left(@workorderno,6)='000015' or left(@workorderno,6)='000055' --Added by Benin Xu 2006-07-24
if EXISTS(select TOP 1 1 from sfcstationkpset(nolock) where skuno=@skuno and eventpoint in ('SI-ASSY','CELL-ASSY','VI','PACKING','ASSY1','ASSY2','ASSY3'))
begin
if(@pworkorderqty=0 or @pworkorderqty is null) begin
set @pworkorderqty=1
end
declare cur_normal cursor for
select PARTNO from sfcstationkpset(nolock) where skuno=@skuno and eventpoint in ('SI-ASSY','CELL-ASSY','VI','PACKING','ASSY1','ASSY2','ASSY3')
open cur_normal
fetch next from cur_normal into @kpskuno
while (@@fetch_status) <> -1
begin
select @category=Prodcategory,@Originalqty=qty from sfcstationkpset(nolock) where PARTNO=@kpskuno and SKUNO=@skuno
update mfworkdetail with(rowlock) set
categoryname=@category,
prodcategoryname=@category,
ismaterial=1,
keypart=1,
requestqty=@pworkorderqty,
Originalqty=@Originalqty
where partno=@kpskuno and workorderno=@workorderno
fetch next from cur_normal into @kpskuno
end
close cur_normal
deallocate cur_normal
end
ELSE
begin
declare cur_rework cursor for
select distinct partno from mfworkdetail(nolock) where workorderno=@workorderno and seqno='kkkk'
set @seqno=1
open cur_rework
fetch next from cur_rework into @kpskuno
while (@@fetch_status) <> -1
begin
select @category=prodcategory from sfcstationkpset(nolock) where partno=@kpskuno and skuno=@skuno
update mfworkdetail with(rowlock) set
seqno=@seqno,
categoryname=@category,
prodcategoryname=@category,
ismaterial=1,
keypart=1
where partno=@kpskuno
set @seqno=1+@seqno
fetch next from cur_rework into @kpskuno
end
close cur_rework
deallocate cur_rework
end
--Modified by Alex Xu 2005-10-09 end
IF NOT EXISTS (select top 1 1 from mfworkorder (nolock) where workorderno=@workorderno)
RAISERROR 99995 'NOTFOUND: Work Order.'
ELSE
BEGIN
UPDATE mfworkorder WITH (ROWLOCK) SET released=1,
releaseddate=getdate(),lasteditdt=getdate()
WHERE workorderno=@workorderno
END
BEGIN
select @vssntype=ssntype,@prouteid=routeid from mfworkorder(nolock) where workorderno=@workorderno
if @@rowcount=0
begin
RAISERROR 99999 'ERR:SSN type is invalid!'
GOTO accept
end
select @vNextEvent=(select top 1 eventpoint from sfcroutedefb(nolock) where routeid=@prouteid order by eventseqno asc)
if @@rowcount=0
begin
RAISERROR 99999 'ERR:Route is invalid!'
GOTO accept
end
select @nTargetQty=TargetQty from mfworklayout(nolock) where workorderno=@workorderno and factoryid='ACGA' and productionline='LINE 1'
and shift='SHIFT 1' and assigndate=convert(datetime,@pworkorderdate) and released=0 --ACEA
if @@rowcount=0
begin
RAISERROR 99999 'ERR:The schedule is invalid!'
GOTO accept
end
select @vCurrentEvent=controlvalue from econtrolvalue(nolock) where controlname='MF_STARTWO_INIT_EVENTPOINT'
if @@rowcount=0
select @vCurrentEvent='START'
END
set @vWeekofYear = right('0'+ltrim(str(DatePart(ww, getdate()))),2)
set @vYear = Right('00'+ltrim(Str(Year(getdate()) - 1996)), 2)
IF @vssntype is null or @vssntype=''
SET @vssntype='CISCO'
if @vssntype='NSG'
SET @vssntype='NSG'
else if @vssntype='CISCO'
SET @vssntype='CSO'
else
begin
RAISERROR 99999 'ERR:The ssntype is invalid!'
GOTO accept
end
select @nBaseNo=convert(int,Seqserialno) from mfsysssnlookup(nolock) where weekno=@vWeekofYear and yearcode=@vYear and eeecode=@vssntype and plantcode='J'
if @@rowcount=0
begin
set @nBaseNo = 1
set @bNew=1
end
If (@nBaseNo + @nTargetQty) > (power(34,4) - 1)
begin
RAISERROR 99999 'ERR:The targetQty is too large!'
GOTO accept
end
------------------Added by ChiBl 2006-08-09 Begin ----------
--Mark by ben 061028 If left(@workorderno,6)='000014' or left(@workorderno,6)='000015' or left(@workorderno,6)='000053' or left(@workorderno,6)='000054'
--If left(@workorderno,6)='000014' or left(@workorderno,6)='000053' or left(@workorderno,6) in ('002250','002252')
IF @wotype IN ('PCBA REWORK','PCBA RMA')
Declare cur_workdetail cursor scroll for select partno,version,seqno,custpartno,Replaceno,Replacetopartno,Originalqty,
Categoryname,Prodcategoryname,left(Prodtype,2) FROM mfworkdetail (nolock)
WHERE workorderno = @workorderno AND ismaterial = 1 and requestqty > 0
and keypart=1 and categoryname<>'FAN'
Else
Declare cur_workdetail cursor scroll for select partno,version,seqno,custpartno,Replaceno,Replacetopartno,Originalqty,
Categoryname,Prodcategoryname,left(Prodtype,2) FROM mfworkdetail (nolock)
WHERE workorderno = @workorderno AND ismaterial = 1 and requestqty > 0
and keypart=1
------------------Added by ChiBl 2006-08-09 End------------
open cur_workdetail
set @nBuildNo=1
set @bFirsttime=1
while(@nTargetQty>0)
begin
declare @nRemainder int
declare @nTemp int
declare @vBase34 varchar(34)
set @vBase34 = '0123456789ABCDEFGHJKLMNPQRSTUVWXYZ'
set @vConvertToBase34 = ''
set @nTemp=@nBaseNo
set @nRemainder = @nBaseNo % 34
If convert(int,@nBaseNo/34) = 0
set @vConvertToBase34 = substring(@vBase34,@nRemainder+1,1)
Else
begin
While convert(int,@nBaseNo/34) > 0
begin
set @nRemainder = @nBaseNo % 34
set @nBaseNo=convert(int,@nBaseNo/34)
set @vConvertToBase34 = substring(@vBase34,@nRemainder+1,1)+@vConvertToBase34
end
set @vConvertToBase34 = substring(@vBase34,@nBaseNo+1,1)+@vConvertToBase34
end
set @nBaseNo=@nTemp
set @vIndex=@vConvertToBase34
if @vssntype='NSG'
set @vsysserialno = 'FOC' + @vYear + @vWeekofYear + Right('0000' + @vIndex, 4)
else if @vssntype='CSO'
set @vsysserialno = '*PCBA-' + @vYear +'-'+ @vWeekofYear +'J-'+ Right('000000' + @vIndex, 6)
if @bFirsttime=1
begin
set @vLabelFrom=@vsysserialno
set @bFirsttime=0
end
fetch first from cur_workdetail into @vPar1,@vPar2,@nPar1,@vPar3,@nPar2,@vPar4,@nPar3,@vPar5,@vPar6,@vPar7
while(@@fetch_status=0)
begin
select @vPar8=isnull(eeecode,'') from mmprodmaster(nolock) where partno=@vPar1 and version=@vPar2
insert mfsyscomponent with(rowlock) (sysserialno,Partno,version,Seqno,CustPartNo,Replaceno,Replacetopartno,keypart,Eeecode,
Lasteditby,Qty,Categoryname,Prodcategoryname,Prodtype)
values(@vsysserialno,@vPar1,@vPar2,@nPar1,@vPar3,@nPar2,@vPar4,1,@vPar8,'',@nPar3,@vPar5,@vPar6,@vPar7)
fetch next from cur_workdetail into @vPar1,@vPar2,@nPar1,@vPar3,@nPar2,@vPar4,@nPar3,@vPar5,@vPar6,@vPar7
end
insert mfworkstatus with(rowlock)(sysserialno,assigndate,workorderno,factoryid,productionline,shift,CurrentEvent,NextEvent,buildno,routeid,lasteditby,lasteditdt)
values(@vsysserialno,convert(datetime,@pworkorderdate),@workorderno,'ACGA','LINE 1','SHIFT 1',@vCurrentEvent,@vNextEvent,@nBuildNo,
@prouteid,'',getdate()) --ACEA
select @vPar1=LTrim(RTrim(skuno)),@vPar2=LTrim(RTrim(custpartno)),@vPar3=Eeecode,@vPar4=upccode,@vPar5=LTrim(RTrim(Productfamily)),@vPar6=Prioritycode
from mfworkorder(nolock) where workorderno=@workorderno
insert mfsysproduct with(rowlock)(sysserialno,Seqno,Factoryid,Workorderno,Skuno,CustPartNo,Eeecode,Upccode,Productfamily,Prioritycode,Lasteditby,LineSeqNo,
orderno,location,whid,areaid)
values(@vsysserialno,@nBuildNo,'ACGA',@Workorderno,@vPar1,@vPar2,@vPar3,@vPar4,@vPar5,@vPar6,'',@nBuildNo,
'','','','') --ACEA
--================================================================================================================================
-- Added by Benin Xu 2006-04-12 begin
-- Machete 機種的復合貼紙 ( 47-18431-01 ) 上的以下三個 Label 作為 Keypart﹐這些 Keypart 的料號與 workdetail 中的不相同﹐
--導至這些 Keypart 無法轉到 mfsyscomponent﹐故執行補 Keypart 的存儲過程﹐來補救。
-- PPID 47-18431-01-01
-- REV 47-18431-01-02
-- IOS 47-18431-01-03
IF EXISTS(SELECT top 1 1 FROM dbo.sfccodelike WITH(NOLOCK) WHERE skuno=@vPar1 AND LEFT(codename,7)='MACHETE' AND category='MODEL') BEGIN
INSERT INTO dbo.mfsyscomponent
VALUES(
@vsysserialno-- sysserialno
,'47-18431-01-01'--partno
,''--version
,'2'--seqno
,'1'--qty
,''--custpartno
,'0'--replaceno
,''--replacetopartno
,'1'--keypart
,'0'--installed
,'0'--installedqty
,''--eeecode
,''--cserialno1
,''--cserialno2
,''--cserialno3
,''--cserialno4
,'PPID'--categoryname
,'PPID'--productcategoryname
,''--prodtype
,'0'--originalqty
,CAST('0' AS MONEY)--unitcost
,'SYSTEM'--lasteditby
,GETDATE()
)
INSERT INTO dbo.mfsyscomponent
VALUES(
@vsysserialno-- sysserialno
,'47-18431-01-02'--partno
,''--version
,'3'--seqno
,'1'--qty
,''--custpartno
,'0'--replaceno
,''--replacetopartno
,'1'--keypart
,'0'--installed
,'0'--installedqty
,''--eeecode
,''--cserialno1
,''--cserialno2
,''--cserialno3
,''--cserialno4
,'REV'--categoryname
,'REV'--productcategoryname
,''--prodtype
,'0'--originalqty
,CAST('0' AS MONEY)--unitcost
,'SYSTEM'--lasteditby
,GETDATE()
)
INSERT INTO dbo.mfsyscomponent
VALUES(
@vsysserialno-- sysserialno
,'47-18431-01-03'--partno
,''--version
,'3'--seqno
,'1'--qty
,''--custpartno
,'0'--replaceno
,''--replacetopartno
,'1'--keypart
,'0'--installed
,'0'--installedqty
,''--eeecode
,''--cserialno1
,''--cserialno2
,''--cserialno3
,''--cserialno4
,'IOS'--categoryname
,'IOS'--productcategoryname
,''--prodtype
,'0'--originalqty
,CAST('0' AS MONEY)--unitcost
,'SYSTEM'--lasteditby
,GETDATE()
)
--PRINT '執行補 Keypart 的存儲過程'
-- EXEC correct_keypart_sp @workorderno
END
-- Added by Benin Xu 2006-04-12 end
--================================================================================================================================
-- Added by Benin Xu 2007-02-13
-- Falchion 機種的復合貼紙 ( 47-18431-01 ) 上的 IOS Label 作為 Keypart﹐這些 Keypart 的料號與 workdetail 中的不相同﹐
--導至這些 Keypart 無法轉到 mfsyscomponent﹐故執行補 Keypart 的存儲過程﹐來補救。
-- IOS 47-18431-01-03
IF EXISTS(SELECT top 1 1 FROM dbo.sfccodelike WITH(NOLOCK) WHERE skuno=@vPar1 AND LEFT(codename,8)='FALCHION' AND category='MODEL') BEGIN
INSERT INTO dbo.mfsyscomponent
VALUES(
@vsysserialno-- sysserialno
,'47-18431-01-03'--partno
,''--version
,'3'--seqno
,'1'--qty
,''--custpartno
,'0'--replaceno
,''--replacetopartno
,'1'--keypart
,'0'--installed
,'0'--installedqty
,''--eeecode
,''--cserialno1
,''--cserialno2
,''--cserialno3
,''--cserialno4
,'IOS'--categoryname
,'IOS'--productcategoryname
,''--prodtype
,'0'--originalqty
,CAST('0' AS MONEY)--unitcost
,'SYSTEM'--lasteditby
,GETDATE()
)
--PRINT '執行補 Keypart 的存儲過程'
EXEC correct_keypart_sp @workorderno ,'SFCSYSTEM'
END
-- Added by Benin Xu 2007-02-13 END
--================================================================================================================================
set @nBaseNo = @nBaseNo + 1
set @nBuildNo=@nBuildNo + 1
set @nTargetQty=@nTargetQty-1
end
set @vLabelTo=@vsysserialno
if @bNew=1
begin
insert mfsysssnlookup with(rowlock)(Weekno,Eeecode,Plantcode,Yearcode,Seqserialno,Lasteditby,lasteditdt)
values(@vWeekofYear,@vssntype,'J', @vYear,ltrim(str(@nBaseNo)),'',getdate())
end
else
begin
update mfsysssnlookup with(rowlock) set Seqserialno=ltrim(str(@nBaseNo)),Lasteditby='',lasteditdt=getdate()
where weekno=@vWeekofYear and yearcode=@vYear and eeecode=@vssntype and plantcode='J'
end
update mfworklayout with(rowlock)set labelfrom=@vLabelFrom,labelTo=@vLabelTo,released=1,releasedate=getdate(),releaseby=''
where workorderno=@workorderno and factoryid='ACGA' and productionline='LINE 1' and assigndate=@pworkorderdate --ACEA
and shift='SHIFT 1' and assigndate=@pworkorderdate and released=0
update mfworkorder with(rowlock)set jobstarted=1,startdate=getdate(),lasteditby='',lasteditdt=getdate()
where workorderno=@workorderno and jobstarted=0
close cur_workdetail
deallocate cur_workdetail
--add by don 2012-08-24 begin
if exists(select * From mfworkorder,sfb_file where mfworkorder.workorderdate='1900-01-01' and mfworkorder.scheduledate='1900-01-01'
and mfworkorder.workorderqty<>mfworkorder.finishedqty
and mfworkorder.workorderno=sfb_file.sfb01)
begin
update mfworkorder set mfworkorder.workorderdate=sfb_file.sfb81,mfworkorder.scheduledate=sfb_file.sfb13,mfworkorder.releaseddate=sfb_file.sfb13,mfworkorder.startdate=sfb_file.sfb13
from sfb_file where mfworkorder.workorderdate='1900-01-01' and mfworkorder.scheduledate='1900-01-01' and mfworkorder.workorderqty<>mfworkorder.finishedqty
and sfb_file.sfb01=mfworkorder.workorderno
end
--add by don 2012-08-24 end
accept:
if @@error=0
commit transaction
else
rollback transaction
END
网友评论