-- 1、  委外的材料出库单升级对应U8材料出库单，并在备注中增加说明，标记为委外出库，并记录委外订单号，清除与委外订单的关联
-- 
-- 2、  委外产成品入库单升级对应U8其他入库单，并在备注中增加说明，标记为委外入库，并记录委外订单号，清除与委外订单的关联

if exists (select 1 where object_id('tempdb..#rdww') is not null ) drop table #rdww

select id into #rdww from @t6db..rdrecord where ( cvouchtype='10' and cbustype='委外入库' ) or ( cvouchtype='11' and cbustype='委外出库' )

update a set cbustype =case cvouchtype when '10' then '成品入库' when '11' then '领料' end ,
	 csource ='库存',cmemo =convert(varchar(60),isnull(cmemo,'')+'委外:'+isnull(cmpocode,'')),
	vt_id=b.vt_id,iproorderid=null
	from @t6db..rdrecord a left join
		(select top 1 '10' as vtype,vt_id from @t6db..vouchertemplates where vt_cardnumber='0411' and vt_templatemode =0 
			union all
		 select top 1 '11' as vtype,vt_id from @t6db..vouchertemplates where vt_cardnumber='0412' and vt_templatemode =0 
		    ) b on a.cvouchtype =b.vtype
	inner join #rdww c on a.id =c.id
GO
delete a from @u8db..voucheritems  a 
where cardnum='03' and FieldName in ('ccusdefine1','ccusdefine2','ccusdefine3')

and  exists(select 1 from @u8db..voucheritems b where a.CardNum=b.CardNum and a.VT_ID =b.VT_ID 
and a.FieldName =b.FieldName 
and a.CardSection =b.CardSection and b.AutoId <a.AutoId and cardnum='03' and b.FieldName in ('ccusdefine1','ccusdefine2','ccusdefine3') )
go
USE @t6db
GO
if exists (select * from @t6db.dbo.sysobjects where name = N'NPrint_GetFldName') 
DROP FUNCTION [dbo].[NPrint_GetFldName]

GO
DECLARE @sql nvarchar(4000)
SET @sql=''
SELECT @sql=@sql+'EXEC sp_unbindefault ''['+so1.name+'].[' +C.Name+']'';'
FROM syscomments M WITH (NOLOCK),syscolumns C WITH (NOLOCK),sysobjects O WITH (NOLOCK), sysobjects so1 WITH (NOLOCK), sysusers u WITH (NOLOCK) 
WHERE O.type='D' AND M.id=C.cdefault AND M.id=O.id AND so1.id=c.id AND u.uid=O.uid
AND O.name='om_purbillvouchdefault'
EXEC(@sql)
GO

if exists (select * from @t6db.dbo.sysobjects where name = N'om_purbillvouchdefault') 
DROP DEFAULT [dbo].[om_purbillvouchdefault]

GO

delete from @t6db..aa_busobject where cbusobid in ('26','27','88') and csub_id is null
delete from @t6db..aa_busobject where cbusobid in ('26','27','88') and iauthtype=2
delete from @t6db..aa_busobject where cbusobid in ('01','07','16','17') and csub_id='sa'
update @t6db..aa_busobject  set csub_id='SA' where cbusobid in ('01','07','16','17','02','05','09','14','15') and csub_id is null
update @t6db..aa_busobject  set csub_id='PU' where cbusobid in ('25','99') and csub_id is null
update @t6db..aa_busobject  set csub_id='IA' where cbusobid in ('ia24','ia30') and csub_id is null

GO

--
delete @t6db..vouchtype where cvouchname like '委外%'
delete @t6db..AP_vouchtype where cTypeName like '委外%'
delete @t6db..AA_BusObject where cBusObID='cb_potoclrb'
delete @t6db..AA_BusObject where cBusObID='cb_potocpcost'
delete @t6db..AA_BusObject where cBusObID='cb_potogsrb'
delete @t6db..AA_BusObject where cBusObID='cb_potoqtfy'
delete @t6db..AA_BusObject where cBusObID='cb_potorgfy'
delete @t6db..AA_BusObject where cBusObID='cb_potoyergfy'
delete @t6db..AA_BusObject where cBusObID='cb_potozzfy'
delete @t6db..AA_BusObject where cBusObID='cb_potozzppd'
delete @t6db..AA_BusObject where cBusObID='cb_rdtoclfy'
delete @t6db..AA_BusObject where cBusObID='CB05'
delete @t6db..AA_BusObject where cBusObID='CB10'
delete @t6db..AA_BusObject where cBusObID='CB15'
delete @t6db..AA_BusObject where cBusObID='CB20'
delete @t6db..AA_BusObject where cBusObID='CB25'
delete @t6db..AA_BusObject where cBusObID='CB30'
delete @t6db..AA_BusObject where cBusObID='CB35'
delete @t6db..AA_BusObject where cBusObID='CB40'
delete @t6db..AA_BusObject where cBusObID='CB45'
delete @t6db..AA_BusObject where cBusObID='CB50'
delete @t6db..AA_BusObject where cBusObID='JDYPGL_LM'
delete @t6db..AA_BusObject where cBusObID='OM_JSXD'
delete @t6db..AA_BusObject where cBusObID='OM_JSXDselck'
delete @t6db..AA_BusObject where cBusObID='OM_ProductPO_LM_4'
delete @t6db..AA_BusObject where cBusObID='OM02'
delete @t6db..AA_BusObject where cBusObID='OM03'
delete @t6db..AA_BusObject where cBusObID='OM08'
delete @t6db..AA_BusObject where cBusObID='OM09'
delete @t6db..AA_BusObject where cBusObID='pp_productpo_LM_4'
delete @t6db..AA_BusObject where cBusObID='pp25'
delete @t6db..AA_BusObject where cBusObID='pp27'
delete @t6db..AA_BusObject where cBusObID='pp28'
delete @t6db..AA_BusObject where cBusObID='pp32'
delete @t6db..AA_BusObject where cBusObID='pp33'
delete @t6db..AA_BusObject where cBusObID='pp34'
delete @t6db..AA_BusObject where cBusObID='pp35'
delete @t6db..AA_BusObject where cBusObID='pp36'
delete @t6db..AA_BusObject where cBusObID='puArr_LM_4'
delete @t6db..AA_BusObject where cBusObID='QM_NBFV21_LM'
delete @t6db..AA_BusObject where cBusObID='QM_NBHGTHV22_LM'
delete @t6db..AA_BusObject where cBusObID='QM_NCV10_LM'
delete @t6db..AA_BusObject where cBusObID='QM_NCV12_LM'
delete @t6db..AA_BusObject where cBusObID='QM_NCV14_LM'
delete @t6db..AA_BusObject where cBusObID='QM_NCV16_LM'
delete @t6db..AA_BusObject where cBusObID='QM_NCVR23_LM_4'
delete @t6db..AA_BusObject where cBusObID='QM_NCVR24_LM_4'
delete @t6db..AA_BusObject where cBusObID='qmbhgclfs'
delete @t6db..AA_BusObject where cBusObID='qmbhgyy'
delete @t6db..AA_BusObject where cBusObID='RetDisptchlist_LM_4'
delete @t6db..AA_BusObject where cBusObID='saordertrace01'
delete @t6db..AA_BusObject where cBusObID='saordertrace02'
delete @t6db..AA_BusObject where cBusObID='saordertrace03'
delete @t6db..AA_BusObject where cBusObID='saordertrace04'
delete @t6db..AA_BusObject where cBusObID='saordertrace11'
delete @t6db..AA_BusObject where cBusObID='saordertrace12'
delete @t6db..AA_BusObject where cBusObID='saordertrace13'
delete @t6db..AA_BusObject where cBusObID='saordertrace14'
delete @t6db..accinformation where cSysID='AA' and cName='iRefPageRecord'
delete @t6db..accinformation where cSysID='FA' and cName='bCurMonthMakeAddDepr'
delete @t6db..accinformation where cSysID='FA' and cName='bCurMonthMakeDownDepr'
delete @t6db..accinformation where cSysID='OM' and cName='bDepartmentCheck'
delete @t6db..accinformation where cSysID='OM' and cName='bInventoryCheck'
delete @t6db..accinformation where cSysID='OM' and cName='bNormalOnCheck'
delete @t6db..accinformation where cSysID='OM' and cName='bNormalOnDispatch'
delete @t6db..accinformation where cSysID='OM' and cName='bNormalOnTransOut'
delete @t6db..accinformation where cSysID='OM' and cName='bNormalOnTransWay'
delete @t6db..accinformation where cSysID='OM' and cName='bOMDCAutoVerify'
delete @t6db..accinformation where cSysID='OM' and cName='bOperatorCheck'
delete @t6db..accinformation where cSysID='OM' and cName='bWarehouseCheck'
delete @t6db..accinformation where cSysID='OM' and cName='bWasteRate'
delete @t6db..accinformation where cSysID='OM' and cName='dOMFirstDate'
delete @t6db..accinformation where cSysID='OM' and cName='dOMNatureDate'
delete @t6db..accinformation where cSysID='OM' and cName='dOMPeriodDate'
delete @t6db..accinformation where cSysID='OM' and cName='dOMStartDate'
delete @t6db..accinformation where cSysID='OM' and cName='iOMBJDayCount'
delete @t6db..accinformation where cSysID='OM' and cName='iOMYJDayCount'
delete @t6db..accinformation where cSysID='PP' and cName='bAutoYJBJ'
delete @t6db..accinformation where cSysID='PP' and cName='bCalMode'
delete @t6db..accinformation where cSysID='PP' and cName='bChF'
delete @t6db..accinformation where cSysID='PP' and cName='bchP'
delete @t6db..accinformation where cSysID='PP' and cName='bchPr'
delete @t6db..accinformation where cSysID='PP' and cName='bchS'
delete @t6db..accinformation where cSysID='PP' and cName='bchWW'
delete @t6db..accinformation where cSysID='PP' and cName='bRep'
delete @t6db..accinformation where cSysID='PP' and cName='bSaleDelay'
delete @t6db..accinformation where cSysID='PP' and cName='bSCWWDelay'
delete @t6db..accinformation where cSysID='PP' and cName='bSimpleStyle'
delete @t6db..accinformation where cSysID='PP' and cName='bUnChF'
delete @t6db..accinformation where cSysID='PP' and cName='bUnchWW'
delete @t6db..accinformation where cSysID='PP' and cName='bUseXCL'
delete @t6db..accinformation where cSysID='PP' and cName='bXE'
delete @t6db..accinformation where cSysID='PP' and cName='bYCDelay'
delete @t6db..accinformation where cSysID='PP' and cName='iOverDay'
delete @t6db..accinformation where cSysID='PP' and cName='iPreDay'
delete @t6db..accinformation where cSysID='PP' and cName='SCDDWGHB_Beyond_SCDDHB'
delete @t6db..accinformation where cSysID='PP' and cName='SCJDHB_Beyond_PGDHB'
delete @t6db..accinformation where cSysID='SA' and cName='bHideClientName'
delete @t6db..accinformation where cSysID='ST' and cName='bCheckMakeNoUse'
delete @t6db..accinformation where cSysID='ST' and cName='bCheckOmNoUse'
delete @t6db..accinformation where cSysID='ST' and cName='bCheckOnOMOrder'
delete @t6db..accinformation where cSysID='ST' and cName='bMakeNoUse'
delete @t6db..accinformation where cSysID='ST' and cName='bOMInNeedOut'
delete @t6db..accinformation where cSysID='ST' and cName='bOmNoUse'
delete @t6db..accinformation where cSysID='ST' and cName='bOnOMOrder'
delete @t6db..accinformation where cSysID='ST' and cName='bPPInNeedOut'
delete @t6db..accinformation where cSysID='ST' and cName='bReMaterialOutAutoVerify'
--vouchers
delete @t6db..voucherHistory where cardnumber in('1801','CB15','CB20','CB25','CB30','CB35','CB40','CB45','CB50','OM02','
OM03','OM04','OM08','OM09','OM10','pp25','pp32','pp33','pp34','pp35','QM10','QM11','QM12','QM13','QM14','QM15',
'QM16','QM17','QM21','QM22','OM03','1701','1702','1802','CB05','CB10','OM01','OM05','OM06','OM07','OM13','OM14',
'pp27','pp28','pp30','pp31','pp36','QM23','QM24')
Go
delete @t6db..voucherFiexdLabel where VT_ID in (select VT_ID from voucherTemplates where VT_Cardnumber in('1801','CB15',
'CB20','CB25','CB30','CB35','CB40','CB45','CB50','OM02','OM03','OM04','OM08','OM09','OM10','pp25','pp32','pp33',
'pp34','pp35','QM10','QM11','QM12','QM13','QM14','QM15','QM16','QM17','QM21','QM22','OM03','1701','1702','1802',
'CB05','CB10','OM01','OM05','OM06','OM07','OM13','OM14','pp27','pp28','pp30','pp31','pp36','QM23','QM24'))
Go
--reports
delete @t6db..Rpt_fundef where Id between 2304 and 2378
delete @t6db..Rpt_funpere where Id between 2304 and 2378
delete @t6db..Rpt_FetchObject where reportId between 2304 and 2378
delete @t6db..Rpt_handle where reportId between 2304 and 2378
delete @t6db..rpt_itmdef where tabledef like '委外%'
delete @t6db..rpt_grpdef where tabledefine like '委外%'

if not exists(select 1 from @t6db..EA_Object where EAO_ID in(12,13,15,17,18,19))
begin
insert into @t6db..EA_Object values (12,'报账单',NULL,11,0,23)
insert into @t6db..EA_Object values (13,'资金预算申报表',NULL,11,0,24)
insert into @t6db..EA_Object values (15,'资金使用计划申报表',NULL,11,0,25)
insert into @t6db..EA_Object values (17,'贷款申请单',NULL,11,0,26)
insert into @t6db..EA_Object values (18,'采购不良品处理单',NULL,11,0,27)
insert into @t6db..EA_Object values (19,'成品不良品处理单',NULL,11,0,28)
end
go
update @t6db..CA_MenuDef set cNewMenu=NULL where autoid between 1 and 3
if not exists(select 1 from @t6db..AA_BusObject where cBusObId in('cgcld','cgjyd','cpcld','cpjyd','cgjyd','cpcld','cpjyd'))
begin
insert @t6db..AA_BusObject(cBusObId,cBusObName,iAuthType,bAuthControl,cSub_ID) values('cgcld','采购不良品处理单生单列表',1,0,'ST')
insert @t6db..AA_BusObject(cBusObId,cBusObName,iAuthType,bAuthControl,cSub_ID) values('cgjyd','采购检验单生单列表',1,0,'ST')
insert @t6db..AA_BusObject(cBusObId,cBusObName,iAuthType,bAuthControl,cSub_ID) values('cpcld','成品不良品处理单生单列表',1,0,'ST')
insert @t6db..AA_BusObject(cBusObId,cBusObName,iAuthType,bAuthControl,cSub_ID) values('cpjyd','成品检验单生单列表',1,0,'ST')insert @t6db..AA_BusObject(cBusObId,cBusObName,iAuthType,bAuthControl,cSub_ID) values('cgcld','采购不良品处理单生单列表',1,0,'ST')
insert @t6db..AA_BusObject(cBusObId,cBusObName,iAuthType,bAuthControl,cSub_ID) values('cgjyd','采购检验单生单列表',1,0,'ST')
insert @t6db..AA_BusObject(cBusObId,cBusObName,iAuthType,bAuthControl,cSub_ID) values('cpcld','成品不良品处理单生单列表',1,0,'ST')
insert @t6db..AA_BusObject(cBusObId,cBusObName,iAuthType,bAuthControl,cSub_ID) values('cpjyd','成品检验单生单列表',1,0,'ST')
end
go
if exists(Select 1 From @t6db..assemvouch where (cvouchtype= '13' or cvouchtype= '14'))
update @t6db..accinformation  set cValue='True' where cSysID='ST' and cName='bAssmble'
GO
alter table @u8db..QMInspectVouchers alter column cbatch nvarchar(60)
alter table @u8db..QMCheckVoucher alter column cbatch nvarchar(60)
If  exists (select c.name,c.id from @u8db..syscolumns c,@u8db..sysobjects o 
            where c.id=o.id and o.xtype='U' and o.name='QMRejectVoucherS' and c.name ='CDIMBATCH')
alter table @u8db..QMRejectVoucherS alter column CDIMBATCH nvarchar(60)
alter table @u8db..QMRejectVoucher alter column cbatch nvarchar(60)
IF EXISTS(SELECT ID,XTYPE FROM @u8db..SYSOBJECTS WHERE NAME='QMKEEPSAMPLE' AND XTYPE='u')
alter table @u8db..QMKEEPSAMPLE alter column cbatch nvarchar(60)
GO
--GSP
alter table @u8db..GSP_VouchsQC alter column cbatch nvarchar(60)
alter table @u8db..GSP_VouchsNote alter column cbatch nvarchar(60)
alter table @u8db..GSP_VouchInform alter column cbatch nvarchar(60)
alter table @u8db..GSP_VouchInspect alter column cbatch nvarchar(60)
alter table @u8db..GSP_VouchUQAudit alter column cbatch nvarchar(60)
If  exists (select c.name,c.id from @u8db..syscolumns c,@u8db..sysobjects o 
            where c.id=o.id and o.xtype='U' and o.name='GSP_VouchZYYHFiles' and c.name ='cbatchb')
alter table @u8db..GSP_VouchZYYHFiles alter column cbatchb nvarchar(60)
alter table @u8db..GSP_VouchZYYHFile alter column cbatch nvarchar(60)
alter table @u8db..GSP_VouchDInforms alter column cbatch nvarchar(60)
alter table @u8db..GSP_VouchDRecords alter column cbatch nvarchar(60)
alter table @u8db..GSP_VouchQIFeedback alter column cbatch nvarchar(60)
alter table @u8db..GSP_VOUCHARCHIEVE alter column cbatch nvarchar(60)
alter table @u8db..GSP_VOUCHSREGISTER alter column cbatch nvarchar(60)
alter table @u8db..GSP_SPECIALOUTS alter column cbatch nvarchar(60)
alter table @u8db..GSP_VOUCHQQINSTANCE alter column cbatch nvarchar(60)
alter table @u8db..GSP_VOUCHSUNSALABLE alter column cbatch nvarchar(60)
GO
--销售管理
alter table @u8db..dispatchlists alter column cbatch nvarchar(60)
alter table @u8db..salebillvouchs alter column cbatch nvarchar(60)
alter table @u8db..sa_settlevouchs alter column cbatch nvarchar(60)
GO
--采购、委外
IF EXISTS(SELECT ID,XTYPE FROM @u8db..SYSOBJECTS WHERE NAME='Pu_ArrivalVouchs' AND XTYPE='u')
	ALTER TABLE @u8db..Pu_ArrivalVouchs 
  		ALTER COLUMN cBatch nvarchar(60) NULL
GO
IF EXISTS(SELECT ID,XTYPE FROM @u8db..SYSOBJECTS WHERE NAME='OM_MOMaterials' AND XTYPE='u')
	ALTER TABLE @u8db..OM_MOMaterials 
  		ALTER COLUMN cBatch nvarchar(60) NULL
GO
IF EXISTS(SELECT ID,XTYPE FROM @u8db..SYSOBJECTS WHERE NAME='OM_MatSettleVouchs' AND XTYPE='u')
	ALTER TABLE @u8db..OM_MatSettleVouchs 
  		ALTER COLUMN cBatch nvarchar(60) NULL
GO
--库存管理

alter table @u8db..AdjustPVouchs alter column cbatch nvarchar(60)
go
alter table @u8db..AssemVouchs alter column cavbatch nvarchar(60)
go
alter table @u8db..CheckVouch alter column ccvbatch nvarchar(60)  
go
alter table @u8db..CheckVouchs alter column ccvbatch nvarchar(60)  
go
alter table @u8db..MatchVouch alter column cprobatch nvarchar(60)  
go
alter table @u8db..MatchVouchs alter column cbatch nvarchar(60)  
go
-- alter table @u8db..RdRecord alter column cprobatch nvarchar(60)  
-- go
alter table @u8db..RdRecords alter column cbatch nvarchar(60)  
go
-- alter table @u8db..scrapvouch alter column cprobatch nvarchar(60)  
-- go
alter table @u8db..ScrapVouchs alter column cbatch nvarchar(60)  
go
if exists (select name from @u8db..sysobjects where name ='ST_AppTransVouchs' and xtype='U')
alter table @u8db..ST_AppTransVouchs alter column ctvbatch nvarchar(60)  
go
alter table @u8db..TransVouchs alter column ctvbatch nvarchar(60)  
go

IF  EXISTS (SELECT * FROM @u8db..sysobjects WHERE name=N'con11' AND type = 'D')
BEGIN
ALTER TABLE @u8db..[InvPosition] DROP CONSTRAINT [con11]
END
go

use @u8db
go

IF  EXISTS (SELECT * FROM @u8db..sysindexes WHERE id = ( select id from @u8db..sysobjects where name ='InvPosition'
		and xtype ='U')
	AND name = N'cbatch')
DROP INDEX [InvPosition].[cbatch] 
go

IF  EXISTS (SELECT * FROM @u8db..sysindexes WHERE id = ( select id from @u8db..sysobjects where name ='InvPosition'
		and xtype ='U')
	AND name = N'InvInd2')
DROP INDEX [InvPosition].[InvInd2]
go

alter table @u8db..InvPosition alter column cbatch nvarchar(60)
go

ALTER TABLE @u8db..[InvPosition] ADD  CONSTRAINT [con11]  DEFAULT ('') FOR [cBatch]
go
CREATE NONCLUSTERED INDEX [cbatch] ON @u8db..[InvPosition] 
(
	[cBatch])
CREATE NONCLUSTERED INDEX [InvInd2] ON @u8db..[InvPosition] 
(
	[cPosCode] ,
	[cInvCode] ,
	[cWhCode] ,
	[cBatch] ,
	[cFree1] ,
	[cFree2] ,
	[cFree3] ,
	[cFree4] ,
	[cFree5] ,
	[cFree6] ,
	[cFree7] ,
	[cFree8] ,
	[cFree9] ,
	[cFree10] 
)
go

alter table @u8db..InventoryBarCodeSet alter column cbatch nvarchar(60)
go
if exists (select name from @u8db..sysobjects where name='PreYear_RdRecords' and xtype='U')
alter table @u8db..PreYear_RdRecords alter column cbatch nvarchar(60)
go
--ia
alter table @u8db..ia_subsidiary alter column cBatchCode nvarchar(60)
alter table @u8db..ia_ensubsidiary alter column cBatchCode nvarchar(60)
go
--生产制造
if exists (select name from @u8db..sysobjects where name ='mom_orderdetail' and xtype='U')
alter table @u8db..mom_orderdetail    alter column MoLotCode            nvarchar(60)         null
if exists (select name from @u8db..sysobjects where name ='mom_moallocate' and xtype='U')
alter table @u8db..mom_moallocate     alter column LotNo                nvarchar(60)         null
GO

if exists (select 1 from @u8db..sysobjects where name ='tempcurrentstock' and xtype ='u')
	ALTER TABLE @u8db..tempcurrentstock ALTER COLUMN cBatch nvarchar(60)
go
if exists (select 1 from @u8db..sysobjects where name ='ST_TempStopQuantity' and xtype ='u')
alter table @u8db..ST_TempStopQuantity alter column cbatch nvarchar(60)
go
--删除价格表中供应类型是委外的价格记录
--if  columnproperty(object_id('@t6db..ven_inv_price_rule'),'isupplytype','AllowsNull') is not null
if exists (select 1 from @t6db..syscolumns where name ='isupplytype' and id =(select ID from 
		@t6db..sysobjects where name ='ven_inv_price_rule' and xtype='U'))
begin
	exec('delete @t6db..ven_inv_price_ruleitem where ID in (select ID from @t6db..ven_inv_price_rule where isupplytype=2)')
	exec('delete @t6db..ven_inv_price_rule where isupplytype=2')
end 
GO

--Begin 质量管理 
--单据编号设置信息不全问题qm08--qm20
delete @t6db..VoucherNumber where cardnumber like 'QM%'
delete @t6db..VoucherPrefabricate where cardnumber like 'QM%'
delete @t6db..Vouchers where cardnumber like 'QM%'
delete @t6db..VoucherHistory where cardnumber like 'QM%'
delete @t6db..VoucherTemplates where vt_cardnumber like 'QM%'
delete @t6db..VoucherItems where cardnum like 'QM%'
delete @t6db..VoucherItems_Prn where cardnum like 'QM%'
go
--End 质量管理