U8已经启用批次管理如何取消

1. U8启用了批次，保质期，已经有历史数据，想要不进行批次管理，保质期管理并要清空历史批次，保质期数据。
2. 操作步骤（系统应用平台界面）：

2.1、现存量整理；

2.2、软件批改进行取消保质期管理，批次管理；

2.3、执行下方“清空批次”脚本（数据库后台执行）；

2.4、检查历史单据情况。

SQL执行语句

找到对应data数据库

begin tran

update a set a.cbatch ='',a.dVDate =null,a.dPDate =null ,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null

```
    from pu_arrivalvouchs  a  inner join inventory i on a.cinvcode = i.cinvcode
```

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新采购到货单子表

update a set a.cbatch ='',a.dVDate=null ,a.dMadeDate =null, a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
           a.cMassUnit =null ,a.iMassDate =null 
```

from rdrecords01  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新采购入库单子表

update a set a.cbatch ='',a.dVDate=null ,a.dMadeDate =null, a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
           a.cMassUnit =null ,a.iMassDate =null 
```

from rdrecords08  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新其他入库单子表

update a set a.cbatch ='',a.dVDate=null ,a.dMadeDate =null, a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
           a.cMassUnit =null ,a.iMassDate =null 
```

from rdrecords09  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新其他出库单子表

update a set a.cbatch ='',a.dVDate=null ,a.dMadeDate =null, a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
           a.cMassUnit =null ,a.iMassDate =null 
```

from rdrecords10  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新产成品入库单子表

图片

图片
update a set a.cbatch ='',a.dVDate=null ,a.dMadeDate =null, a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
           a.cMassUnit =null ,a.iMassDate =null 
```

from rdrecords11  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新材料出库单子表

update a set a.cbatch ='',a.dVDate=null ,a.dMadeDate =null, a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
           a.cMassUnit =null ,a.iMassDate =null 
```

from rdrecords32  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新销售出库单子表

update a set a.cbatch ='',a.dVDate=null ,a.dMadeDate =null, a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
           a.cMassUnit =null ,a.iMassDate =null 
```

from rdrecords34  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新库存期初子表

update a set a.cbatch ='',a.dVDate =null,a.dmdate =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

   from st_monthaccount  a  inner join inventory i on a.cinvcode = i.cinvcode

where   isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    ------更新库存月度账表

update a set a.cbatch ='',a.dVDate =null,a.dmdate =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

   from st_monthaccounts  a  inner join inventory i on a.cinvcode = i.cinvcode

where   isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新库存月度账表

update a set a.cbatch ='',a.dVDate =null,a.dmdate =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

   from st_monthaccountcheck  a  inner join inventory i on a.cinvcode = i.cinvcode

where   isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新库存月度账表 

update a set a.cbatch ='',a.dVDate =null,a.dmdate =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

   from st_monthaccountcheck  a  inner join inventory i on a.cinvcode = i.cinvcode

where   isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新库存月度账表 

update a set a.cbatch ='',a.dVDate =null,a.dmdate =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

from st_monthaccountv  a  inner join inventory i on a.cinvcode = i.cinvcode

where isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''      -----更新库存月度账表

update a set a.cbatch ='',a.dVDate =null,a.dmdate =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

from st_monthaccountvs  a  inner join inventory i on a.cinvcode = i.cinvcode

where isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''      -----更新库存月度账表

update a set a.cbatch ='',a.dVDate =null,a.dMadeDate  =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

from mainbatch  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''   -----更新出入库跟踪表

update a set a.cbatch ='',a.dVDate =null,a.dMadeDate  =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

from st_totalvensum  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''   -----更新供应商结存表

update a set a.cbatch ='',a.dVDate =null,a.dMDate  =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

from currentstockstqc a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''

update a set a.cbatch ='',a.dVDate =null,a.dMadeDate  =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

from invposition  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''   -----更新存货货位记录表

update a set a.cbatch ='',a.dVDate =null,a.dMDate  =null ,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null

```
    from dispatchlists  a  inner join inventory i on a.cinvcode = i.cinvcode
```

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''   -----更新发货退货单子表

update a set a.cbatch ='',a.dVDate =null,a.dMDate  =null ,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null

```
    from SaleBillVouchs   a  inner join inventory i on a.cinvcode = i.cinvcode
```

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''   -----更新销售发票子表

update a set a.ccvbatch ='',a.dDisDate  =null,a.dMadeDate  =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

from checkvouchs a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.ccvbatch,'')<>'' -----更新盘点单子表

update a set a.ctvbatch ='',a.dDisDate  =null,a.dMadeDate   =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

from TransVouchs a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.ctvbatch,'')<>'' -----更新库存调拨单子表

update a set a.cbatch ='',a.dvDate  =null,a.dMadeDate   =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

from ScrapVouchs  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''   -----更新报废单子表

update a set a.cbatch =''

from ST_TempStopQuantity  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''   -----年结时记录没有结转下来的质检冻结量，无数据可不执行

--是否有质量模块，若有需执行以下

update a set a.cbatch ='' 

from QMInspectVouchers  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''   -----更新报检单子表

update a set a.cbatch ='' 

from QMCheckVoucher  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''   -----更新检验单主表

update a set a.cbatch ='' 

from QMRejectVoucher  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''   -----更新不良品处理单

update a set a.CDIMBATCH ='' 

from QMRejectVouchers  a  inner join inventory i on a.CDIMINVCODE = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.CDIMBATCH,'')<>''-----更新不良品处理明细

------

exec SP_ClearCurrentStock_ST   -----整理现存量表 ，整理上年年结中未能转入下年的现存量，并转入下年

truncate table InvPositionSum ----删除清空并重新更新存货货位存量表

insert into InvPositionSum(iQuantity,inum,cWhCode,cPosCode,cInvCOde,cBatch,

cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,cvmivencode,iTrackid,cInvouchtype)

select  sum((case when Inv.brdflag=1 then 1 when Inv.brdflag=0 then -1 else 0 end)*Inv.iquantity),

  sum((case when Inv.brdflag=1 then 1 when Inv.brdflag=0 then -1 else 0 end)*Inv.inum),

Inv.cWhCode,Inv.cPosCode,Inv.cInvCode,isnull(Inv.cBatch,N''),isnull(Inv.cFree1,N''),isnull(Inv.cFree2,N''),isnull(Inv.cFree3,N''),isnull(Inv.cFree4,N''),

isnull(Inv.cFree5,N''),isnull(Inv.cFree6,N''),isnull(Inv.cFree7,N''),isnull(Inv.cFree8,N''),isnull(Inv.cFree9,N''),isnull(Inv.cFree10,N''),isnull(Inv.cvmivencode,N''),

case when isnull(i.btrack,0)=1 then isnull(Inv.iTrackid,0) else 0 end,

case when isnull(i.btrack,0)=1 then isnull(Inv.cInVouchType,N'') else N'' end

from InvPosition Inv

inner join inventory I on Inv.cinvcode=I.cinvcode

group by Inv.cWhCode,Inv.cPosCode,Inv.cInvCOde,isnull(Inv.cBatch,N''),isnull(Inv.cFree1,N''),isnull(Inv.cFree2,N''),

isnull(Inv.cFree3,N''),isnull(Inv.cFree4,N''),isnull(Inv.cFree5,N''),isnull(Inv.cFree6,N''),isnull(Inv.cFree7,N''),

isnull(Inv.cFree8,N''),isnull(Inv.cFree9,N''),isnull(Inv.cFree10,N''),isnull(Inv.cvmivencode,N''),

case when isnull(i.btrack,0)=1 then isnull(Inv.iTrackid,0) else 0 end,

case when isnull(i.btrack,0)=1 then isnull(Inv.cInVouchType,N'') else N'' end

update Inv set cMassUnit=CS.cMassUnit,iMassDate=CS.iMassDate,dMadedate=CS.dmdate,dvdate=CS.dvdate,

iExpiratDateCalcu=CS.iExpiratDateCalcu,cExpirationdate=CS.cExpirationdate,dExpirationdate=CS.dExpirationdate

from InvPositionSum Inv inner join currentstock CS on

Inv.cinvcode=Cs.cinvcode and Inv.cWhcode=CS.cWhCode and isnull(Inv.cBatch,'')=isnull(Cs.cBatch,'') and

isnull(Inv.cfree1,'')=isnull(CS.cfree1,'') and isnull(Inv.cfree2,'')=isnull(CS.cfree2,'') and isnull(Inv.cfree3,'')=isnull(CS.cfree3,'')

and isnull(Inv.cfree4,'')=isnull(CS.cfree4,'') and isnull(Inv.cfree5,'')=isnull(CS.cfree5,'') and isnull(Inv.cfree6,'')=isnull(CS.cfree6,'')

and isnull(Inv.cfree7,'')=isnull(CS.cfree7,'') and isnull(Inv.cfree8,'')=isnull(CS.cfree8,'') and isnull(Inv.cfree9,'')=isnull(CS.cfree9,'')

and isnull(Inv.cfree10,'')=isnull(CS.cfree10,'') and isnull(Inv.cvmivencode,'')=isnull(CS.cvmivencode,'')

go                      -----更新存货货位存量表和现存量汇总表数据

update a set a.cavbatch ='',a.dDisDate  =null,a.dMadeDate   =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

from AssemVouchs  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cavbatch,'')<>''  -----更新组装拆卸形态转换单子表

update a set a.cbatch ='',a.dDisDate  =null,a.dMadeDate   =null,a.cExpirationdate =null,a.iExpiratDateCalcu =null,a.dExpirationdate =null,

```
         a.cMassUnit ='',a.iMassDate =null
```

from AdjustPVouchs  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新货位调整单子表

update a set a.cbatch ='',a.dvDate  =null,a.dMDate   =null            

from st_snstate  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新序列号状态表

update a set a.cbatch ='',a.dvdate  =null,a.dMDate   =null

from ST_SNDetail_MaOut  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新材料出库单序列号子表

update a set a.cbatch ='',a.dvdate  =null,a.dMDate   =null

from ST_SNDetail_OtherIN  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新其他入库单序列号子表　

update a set a.cbatch ='',a.dvdate  =null,a.dMDate   =null

from ST_SNDetail_OtherOut  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新其他出库单序列号子表　

update a set a.cbatch ='',a.dvdate  =null,a.dMDate   =null

from ST_SNDetail_PROIN  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新成品入库单序列号子表

update a set a.cbatch ='',a.dvdate  =null,a.dMDate   =null

from ST_SNDetail_PUIN  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新采购入库单序列号子表

update a set a.cbatch ='',a.dvdate  =null,a.dMDate   =null

from ST_SNDetail_SaleOut  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新销售出库单序列号子表

update a set a.cbatch ='',a.dvdate  =null,a.dMDate   =null

from ST_SNDetail_QC  a  inner join inventory i on a.cinvcode = i.cinvcode

where  isnull(i.binvbatch,0) =0 and isnull(a.cbatch,'')<>''    -----更新期初序列号

commit tran