732 lines
55 KiB
Transact-SQL
732 lines
55 KiB
Transact-SQL
/****** Script for SelectTopNRows command from SSMS ******/
|
||
|
||
-- delete from [t_wmsOutPickRequest];
|
||
-- delete from [t_wmsOutPickDetail];
|
||
|
||
/*
|
||
|
||
v_stockOutDetailByCust
|
||
|
||
|
||
SELECT a.orderNo, a.customerId, b.goodsId, b.batch, SUM(b.count) AS count, SUM(b.count) - FLOOR(SUM(b.count) / c.bigCount)
|
||
* c.bigCount AS bulkCount, FLOOR(SUM(b.count) / c.bigCount) * c.bigCount AS batch1Count, FLOOR(SUM(b.count)
|
||
/ c.bigCount) AS boxcnt, c.bigCount, a.customerName, b.whtype, b.productDate, b.validDate, GETDATE() AS lastmodified,
|
||
'true' AS dr, - 1 AS id, 0 AS batch2Count, '' AS pickOrderNo, 0 AS state, '' AS description, - 1 AS operater, GETDATE()
|
||
AS createtime
|
||
FROM dbo.t_wmsOutRequest AS a INNER JOIN
|
||
dbo.t_wmsOutDetail AS b ON a.orderNo = b.orderNo INNER JOIN
|
||
dbo.v_wmsGoods AS c ON b.goodsId = c.goodsId
|
||
WHERE (a.state = 0) AND (a.orderType = 0) AND (NOT EXISTS
|
||
(SELECT 1 AS Expr1
|
||
FROM dbo.t_wmsOutPickDetail
|
||
WHERE (orderNo = a.orderNo))) OR
|
||
-- (NOT EXISTS
|
||
-- (SELECT 1 AS Expr1
|
||
-- FROM dbo.t_wmsOutPickDetail AS t_wmsOutPickDetail_1
|
||
-- WHERE (orderNo = a.orderNo) AND (a.state = 1)))
|
||
GROUP BY a.orderNo, a.customerId, b.goodsId, b.batch, c.bigCount, a.customerName, b.whtype, b.productDate, b.validDate
|
||
|
||
|
||
*/
|
||
select * from sysprocesses where dbid in (select dbid from sysdatabases where name='deiniuWMS') order by status;
|
||
|
||
|
||
SELECT *
|
||
FROM [wmsuzhou2].[dbo].[t_wmsOutRequest]
|
||
|
||
s where not exists(select 1 from t_wmsOutRequestDetail where orderno=s.orderno)
|
||
|
||
|
||
|
||
----------------出入库数据初始化开始----------------------------------
|
||
use wmsuzhou2;
|
||
truncate table t_wmsinRequest; -- 销售订单主表
|
||
truncate table t_wmsinRequestDetail;-- 销售订单子表
|
||
|
||
truncate table t_wmsstockrecord; --库存日志表
|
||
|
||
update t_wmsstock set count =count+ countin ,countin=0 ;
|
||
update t_wmsstock set countOut =0,countin=0,countouting =0;
|
||
truncate table t_wmsStockpre;
|
||
truncate table [t_wmsOutPickRequest]; -- 出库分拣单主表
|
||
truncate table [t_wmsOutPickDetail]; --出库分拣单子表
|
||
truncate table t_wmsOutRequestDetail; --出库销售订单原始明细表
|
||
truncate table t_wmsOutRequest; --出库销售订单主表
|
||
truncate table t_wmsOutDetail;--出库销售订单子表
|
||
truncate table [t_wmsOutPickPort];--整库出库明细
|
||
truncate table t_tmsStock;
|
||
truncate table t_tmsTranRequest;
|
||
truncate table t_tmsTranRequestDetail;
|
||
truncate table t_wmsOutPickDetail_tmp;
|
||
truncate table t_wmsOutPickLable;
|
||
truncate table t_wmsOutPickLable_tmp;
|
||
truncate table t_wmsOutLog;
|
||
truncate table t_wmsInUpPort;
|
||
truncate table t_wmsflow;
|
||
truncate table t_wmsWave;
|
||
truncate table t_wmsStockLack;
|
||
truncate table t_wmsOrderRequest;
|
||
truncate table t_wmsOrderDetail;
|
||
truncate table t_erp_sale_tmp;
|
||
-- update [t_wmsStock] set productdate = getdate()-60 ;--where skuid =1991;
|
||
-- update [t_wmsStock] set [validDate] = getdate()+360;
|
||
-- update [t_wmsPlate] set waveno =null,pickorderno=null,partion=0,state =0,terminal =0
|
||
|
||
use [custErp_suzou2];
|
||
update [t_erp_sale] set wms_state =0;
|
||
|
||
update [t_erp_purch] set wms_state =0;
|
||
update [t_erp_purch_d] set wms_state =0,receiveCount =0,rejectCount =0,validCount =0,arriveCount=0;
|
||
truncate table [t_erp_receiveValidDetail];
|
||
truncate table [t_erp_purch_receive_log];
|
||
truncate table t_erp_purch_receive_pre;
|
||
update [t_erp_sale] set dueDate =GETDATE()+2,orderDate = getdate()-2, createtime =GETDATE();
|
||
update [t_erp_purch] set [deliveryDate] =GETDATE()-2, [shipDate] = getdate()+2, [endDate] = getdate()+10;
|
||
|
||
--update [custErp_suzou2].[dbo].[t_erp_sale_d] set count =count+(select top 1 bigCount from t_erpGoods where goodsId = t_erp_sale_d.goods_id);
|
||
--update [custErp_suzou2].[dbo].[t_erp_sale_d] set count =count-(select top 1 bigCount from t_erpGoods where goodsId = t_erp_sale_d.goods_id);
|
||
-----------------------------------------------------------出入库数据初始化 end-----------------------------------------------------------------
|
||
|
||
|
||
---- sync out request, some details missing
|
||
select * from t_wmsOutRequest r where not exists(select 1 from t_wmsOutRequestDetail where orderno = r.orderno);
|
||
|
||
select * from t_wmsOutRequest r where not exists(select 1 from t_wmsOutDetail where orderno = r.orderno);
|
||
|
||
use [custErp_suzou2];
|
||
select * FROM [t_erp_sale] r where not exists(select 1 from [t_erp_sale] where sale_order = r.sale_order);
|
||
|
||
-- delete from t_wmsOutRequest where not exists(select 1 from t_wmsOutDetail where orderno = t_wmsOutRequest.orderno);
|
||
|
||
-- delete from t_wmsOutRequest where not exists(select 1 from t_wmsOutRequestDetail where orderno = t_wmsOutRequest.orderno);
|
||
|
||
|
||
|
||
----------------------------分店订单初始化
|
||
update [deiniuWMS].[dbo].[t_wmsOutRequest] set orderDate =GETDATE()-1 ,dueDate =GETDATE()+1 where pickType =1
|
||
|
||
truncate table t_wmsWave;
|
||
truncate table [t_wmsOutPickRequest];
|
||
truncate table [t_wmsOutPickdetail]
|
||
truncate table t_wmsOutPickPort;
|
||
truncate table t_wmsStockRecord;
|
||
update t_wmsstock set countOut =0,countin=0,countouting =0;
|
||
update t_wmsoutrequest set state =0,pickOrderNo=null;
|
||
|
||
|
||
|
||
---------------------------------------------------------------
|
||
//待生成波次
|
||
select * from t_wmsOutPickPort a where 1=1
|
||
and exists(select 1 from t_wmsOutPickRequest where pickOrderNo =a.pickOrderNo and state=9)
|
||
and waveOrder is null and state = 0
|
||
order by tranArea,pickOrderNo,voltype,partion,locationid ;
|
||
|
||
|
||
|
||
|
||
|
||
truncate table t_Sku;
|
||
truncate table t_SkuValue;
|
||
truncate table t_erp_receiveValidDetail;
|
||
update t_wmsstock set skuid =0;
|
||
update t_erp_sale_d set skuId =( select top 1 skuId from [deiniuWMS].[dbo].[t_wmsStock] where goodsId = t_erp_sale_d.goods_id and batch = t_erp_sale_d.batch)
|
||
,skucode = ( select top 1 skucode from [deiniuWMS].[dbo].[t_wmsStock] where goodsId = t_erp_sale_d.goods_id and batch = t_erp_sale_d.batch);
|
||
|
||
---取短日期 2020-05-14
|
||
select convert(char(10),getdate()-30,120)
|
||
select convert(char(10),getdate()-100,120)
|
||
|
||
---update table exists in another table
|
||
update c set custType=1 from [custErp].[dbo].[t_erp_customer] c
|
||
where exists (select
|
||
count(*),custor_name
|
||
FROM [custErp].[dbo].[t_erp_sale] where c.custId = custor_id group by custor_name having count(*) >20)
|
||
|
||
|
||
select * from t_wmsOutRequestTmp;
|
||
SELECT *
|
||
FROM [deiniuWMS].[dbo].[v_stockOutDetailByCust];
|
||
|
||
SELECT * FROM dbo.v_stockGoodsSumBasic where skuid =4539;
|
||
|
||
SELECT * FROM [t_wmsOutDetail] a WHERE orderno ='SALPXAM00000213' ;
|
||
|
||
select * from v_stockOutDetailByCustStockNotAvailable;
|
||
|
||
|
||
SELECT * FROM [t_wmsOutRequest] a WHERE orderno ='SALPXAM00000842' ;
|
||
|
||
|
||
SELECT * FROM [t_wmsOutRequest] a WHERE pickOrderNo ='PICK080000012310';
|
||
|
||
SELECT * FROM [t_wmsOutDetail] a WHERE orderno ='SALPXAM00000213' ;
|
||
|
||
|
||
SELECT * FROM [t_wmsOutRequest]where pickOrderNo is null order by customerId;
|
||
|
||
|
||
SELECT * FROM t_wmsOutRequest r WHERE state = 3 and not exists(select 1 from v_stockOutDetailStockNotAvailable where orderno = r.orderno );
|
||
|
||
SELECT * FROM t_wmsOutRequest r WHERE state = 3 and not exists(select 1 from v_stockOutDetailStockNotAvailable where orderno = r.orderno ) order by customerId ;
|
||
|
||
SELECT * FROM t_wmsOutRequest r WITH(nolock) WHERE state = 3 and not exists(select 1 from v_stockOutDetailStockNotAvailable where orderno = r.orderno ) order by customerId ;
|
||
|
||
SELECT * FROM [t_wmsOutDetail] a WHERE state =1 and exists(select 1 from t_wmsOutRequest where orderno = a.orderNo and state =3);
|
||
|
||
select * from v_stockOutRequestDetail where orderState =3;
|
||
|
||
select * from v_stockOutDetailByCustStockNotAvailable;
|
||
|
||
|
||
select * from v_stockOutDetailStockNotAvailable where orderno ='SALPXAM00000213'
|
||
|
||
|
||
|
||
SELECT id, orderNo, goodsId, skuId, skuCode, batch, count, bulkCount, batch1Count, batch2Count, boxcnt, productDate,
|
||
validDate, state, whtype, description, operater, dr, createtime, lastmodified
|
||
FROM dbo.t_wmsOutDetail WITH (nolock)
|
||
WHERE
|
||
(state = 0) AND EXISTS
|
||
(SELECT 1 AS Expr1
|
||
FROM dbo.v_stockGoodsSumBasic
|
||
WHERE (skuId = dbo.t_wmsOutDetail.skuId) AND (dbo.t_wmsOutDetail.whtype = whType) AND
|
||
(avCount < dbo.t_wmsOutDetail.count) OR
|
||
(skuId = dbo.t_wmsOutDetail.skuId) AND (dbo.t_wmsOutDetail.whtype = whType) AND
|
||
(avBatchCount < dbo.t_wmsOutDetail.batch1Count))
|
||
|
||
|
||
|
||
|
||
|
||
truncate table t_wmsstockSum;
|
||
|
||
insert into t_wmsstockSum( [skuId]
|
||
,[skuCode]
|
||
,[goodsId]
|
||
,[batch]
|
||
,[productDate]
|
||
,[validDate]
|
||
|
||
,[whType]
|
||
,[volType]
|
||
,[count]
|
||
,[countLock]
|
||
,[countOut]
|
||
,[countIn]
|
||
,[countOuting])
|
||
|
||
SELECT [skuId]
|
||
,[skuCode]
|
||
,[goodsId]
|
||
,[batch]
|
||
,[productDate]
|
||
,[validDate]
|
||
|
||
,[whType]
|
||
,[volType]
|
||
,[count]
|
||
,[countLock]
|
||
,[countOut]
|
||
,[countIn]
|
||
,[countOuting]
|
||
FROM [deiniuWMS].[dbo].[v_stockGoodsSumByVolType] ;
|
||
|
||
|
||
|
||
|
||
|
||
SELECT TOP 100 * FROM [DNWMS_PROD].[DEINIUWMS].[DBO].[T_WMSSTOCK]
|
||
|
||
SELECT TOP 100 * FROM [DNWMS_PROD].[DEINIUWMS].[DBO].[V_LOCATION]
|
||
|
||
|
||
|
||
truncate table t_wmsstock;
|
||
|
||
|
||
INSERT INTO t_wmsstock([goodsId]
|
||
,[batch]
|
||
,[locationId]
|
||
,[productDate]
|
||
,[validDate]
|
||
,[validationTerm]
|
||
,[count]
|
||
,[countLock]
|
||
,[countOut]
|
||
,[countIn]
|
||
,[countOuting]
|
||
,[state]
|
||
,[maintainDate]
|
||
,[description]
|
||
,[operater]
|
||
,[lastmodified]
|
||
,[createtime]
|
||
,[dr])
|
||
|
||
SELECT [goodsId]
|
||
,[batch]
|
||
,[locationId]
|
||
,[productDate]
|
||
,[validDate]
|
||
,[validationTerm]
|
||
,[count]
|
||
,[countLock]
|
||
,[countOut]
|
||
,[countIn]
|
||
,[countOuting]
|
||
,[state]
|
||
,[maintainDate]
|
||
,[description]
|
||
,[operater]
|
||
,[lastmodified]
|
||
,[createtime]
|
||
,[dr]
|
||
FROM [DNWMS_PROD].[DEINIUWMS].[DBO].[T_WMSSTOCK];
|
||
|
||
|
||
|
||
EXEC sp_addlinkedserver
|
||
@server='DNWMS_PROD', --链接服务器别名
|
||
@srvproduct='',
|
||
@provider='SQLOLEDB',
|
||
@datasrc='121.42.142.11' --要访问的的数据库所在的服务器的ip
|
||
GO
|
||
EXEC sp_addlinkedsrvlogin
|
||
'DNWMS_PROD', --链接服务器别名
|
||
'false',
|
||
NULL,
|
||
'sa', --要访问的数据库的用户
|
||
'Es@cn09!' --要访问的数据库，用户的密码
|
||
GO
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
truncate table [custErp].[dbo].[t_erp_purch_d];
|
||
truncate table [custErp].[dbo].[t_erp_purch];
|
||
|
||
|
||
select * from power_manage.dbo.wms_rec_s
|
||
|
||
select *from t_wmsstock where locationid ='2B10713-45';
|
||
2B10713-21
|
||
|
||
|
||
insert into [custErp].[dbo].[t_erp_purch_d] select * from [ERP_WMS].[dbo].[erp_purch_s];
|
||
insert into [custErp].[dbo].[t_erp_purch] select * from [ERP_WMS].[dbo].[erp_purch_m];
|
||
|
||
|
||
|
||
update [custErp].[dbo].[t_erp_purch_d] set product_date = getdate() -100,save_date = GETDATE() +1000;
|
||
update [custErp].[dbo].[t_erp_purch_d] set batch = 'tmpbatch' where batch ='';
|
||
|
||
select * from [custErp].[dbo].[t_erp_purch_d] where batch !='';
|
||
|
||
SELECT *
|
||
FROM [dwms4].[dbo].[t_wmsOutPickRequest] ;-- where orderDate >= '2017/1/1' and orderDate <= '2017/5/24'
|
||
|
||
update [dwms4].[dbo].[t_wmsOutPickRequest] set state =3;
|
||
|
||
SELECT a.* FROM [t_wmsStockRecord] a where orderNo = 'PICK050000005500'
|
||
and exists (select 1 from t_wmslocation where locationId = a.locationId and volType =0)
|
||
|
||
SELECT getdate()
|
||
|
||
SELECT * FROM v_stockGoods WHERE 1=1
|
||
--- goodsid='G1XEIENT32Z' batch ='G1W9VFCSU9V'
|
||
ORDER BY voltype desc;
|
||
|
||
select * from t_wmslocation order by elabId desc
|
||
select * from t_wmslocation l where goodstype !=31 and not exists (select 1 from t_wmsStock where locationId = l.locationId);
|
||
|
||
select goodsType,volType,count(id) from t_wmslocation l where 1=1
|
||
-- and volType =2 and goodstype !=31
|
||
and not exists (select 1 from t_wmsStock where locationId = l.locationId)
|
||
group by goodsType,volType order by goodsType;
|
||
|
||
10397
|
||
38504
|
||
26743
|
||
37005
|
||
31141
|
||
68496
|
||
|
||
|
||
SELECT goodsid,batch,bulkMax ,[batchMax1],batchMax2,count ,volType FROM v_stockGoods
|
||
WHERE 1=1
|
||
--and goodstype = 116
|
||
--and goodsid ='G1W7KH0T8P7' and batch='11960502'
|
||
and volType =0 and bulkMax > count
|
||
--and volType =1-- and [batchMax1] > count
|
||
-- and volType =2 --and batchMax2 > count
|
||
order by goodsId,batch;
|
||
|
||
|
||
select * from t_wmsinRequest where state !=0;
|
||
|
||
|
||
|
||
select * from t_wmsInRequestDetail where orderno ='STTXAM00000061';
|
||
|
||
SELECT goodsid,batch,sum(count) FROM v_stockGoods group by goodsid,batch;
|
||
select goodsid,batch, sum(cnt) from t_wmsInRequestDetail
|
||
--where orderno in('STTXAM00000061','PDDXAM00003912','PDDXAM00002661')
|
||
group by goodsid,batch;
|
||
|
||
select count(*) from t_wmsStockRecord;
|
||
|
||
12 43 44
|
||
|
||
SELECT -1 as id,* FROM [v_stockOutRequest_Available] order by customerId;
|
||
|
||
|
||
|
||
SELECT * FROM [v_stockOutDetailByCust];
|
||
|
||
|
||
SELECT * FROM v_stockGoods
|
||
WHERE goodsid='G1WEM4N0BXV' and batch ='F6085504'
|
||
and voltype = 0
|
||
|
||
|
||
SELECT sum(count - countOut) FROM v_stockGoods WHERE goodsid='G1WEM4N0BXV' and batch ='F6085504'
|
||
--and voltype = 0
|
||
ORDER BY voltype desc;
|
||
|
||
PICK060000070260
|
||
select * from t_wmsOutPickRequest where waveOrder ='WAVE050000014883' ;;pickOrderNo='PICK050000014876';
|
||
select * FROM [dwms4].[dbo].[t_wmsOutPickLable_tmp] where waveOrder ='WAVE050000014889';
|
||
delete from [t_wmsOutPickLable_tmp];
|
||
|
||
select * from t_wmsLocation where elabAddress =0 and volType =0;
|
||
|
||
select * from t_wmsStockRecord where id in( 70451,70452,70453)
|
||
|
||
select color,elabId,elabAddress,count ,state FROM [dwms4].[dbo].[t_wmsOutPickLable_tmp] order by createtime desc
|
||
|
||
UPDATE t_wmsOutRequest SET STATE = b.state from t_wmsOutRequest a ,t_wmsOutPickRequest b where a.pickOrderNo = b.pickorderNo and WAVEORDER ='WAVE060000070599'
|
||
|
||
select * from t_wmsOutRequest a where exists (select 1 from t_wmsOutPickRequest where a.pickOrderNo = b.pickorderNo and WAVEORDER ='WAVE060000070599'
|
||
|
||
|
||
-- test thread
|
||
|
||
update t_wmsstock set countOut =0;
|
||
truncate table [t_wmsOutPickRequest];
|
||
truncate table [t_wmsOutPickDetail];
|
||
truncate table [t_wmsOutPickDetail_tmp];
|
||
truncate table t_wmsOutRequestDetail;
|
||
truncate table t_wmsOutRequest;
|
||
truncate table t_wmsOutDetail;
|
||
truncate table t_wmsOutpickPort;
|
||
truncate table t_wmsOutPickLable_tmp;
|
||
--select * from t_wmsOutpickPort
|
||
truncate table t_wmsOutLog;
|
||
--delete from t_wmsOutDesk
|
||
|
||
|
||
PTPXAM00000070 ,PTPXAM00000102
|
||
PICK060000070437 陕西盘龙医药物流有限公司
|
||
|
||
PICK060000070452
|
||
PICK060000070453
|
||
PICK060000070453
|
||
|
||
|
||
|
||
SELECT * FROM [v_stockOutPickDetail] where pickOrderNo= 'PICK060000070454' and state =3 and batchpickstate >=0
|
||
|
||
|
||
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutRequestDetail] where orderno ='PICK060000070437 ';
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutRequest] where orderno ='PTPXAM00000070 ';
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutRequest] where pickorderno ='"PICK060000070311"';
|
||
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutPickRequest] where 1=1
|
||
-- and waveorder is null
|
||
--and waveOrder ='WAVE060000070479'
|
||
and pickorderno ='PICK060000070462';;-- where state =0;
|
||
|
||
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutRequest] where pickorderno ='PICK060000070469';;-- where state =0;
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutRequestDetail] where orderno ='PICK060000070311 ';
|
||
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutPickDetail] where pickorderno ='PICK060000070462';
|
||
|
||
|
||
SELECT a.* FROM [v_stockOutWavePickDetail] a where pickOrderNo= 'PICK060000070469'
|
||
|
||
|
||
SELECT * FROM [v_stockOutPickDetail] where pickOrderNo= 'PICK060000070311' and state =3 and batchpickstate >=0
|
||
SELECT * FROM [v_stockOutWavePickDetail] where pickOrderNo= 'PICK060000070311' and state =6 and batchpickstate >=0
|
||
update t_wmsOutPickDetail set batchpickstate =6 where pickOrderNo= @pickOrderNo and batchpickstate =@oldBatchpickstate
|
||
|
||
select * from t_wmsOutPickDetail where pickOrderNo= 'PICK060000070217' and batchpickstate =1
|
||
|
||
SELECT * FROM [t_wmsOutPickRequest] where pickOrderNo= 'PICK060000070221' and state =6 and batchpickstate >=0
|
||
PICK060000070221 西安藻露堂药业集团库时代医药有限公司 1
|
||
|
||
PICK060000070176
|
||
"PICK060000070221"
|
||
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutRequest] ;
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutRequestDetail]
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutPickRequest] ;;-- where state =0;
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutPickDetail] ;--where pickOrderNo ='PICK050000025191' and goodsId ='G30F1R90GSZ' and batch='161208' ;
|
||
SELECT * FROM t_wmsOutPickLable_tmp where state =1 and elabId =2
|
||
|
||
select * from t_wmsOutpickPort order by waveOrder;
|
||
|
||
|
||
select * from t_wmsOutpickPort where recordid in ( select recordid from t_wmsOutpickPort group by recordId having count(*) > 1) order by recordId;
|
||
|
||
|
||
|
||
|
||
SELECT count( *) FROM [dwms4].[dbo].[t_wmsOutRequest] ;
|
||
SELECT count( *) FROM [dwms4].[dbo].[t_wmsOutPickRequest] ;
|
||
SELECT count( *) FROM [dwms4].[dbo].[t_wmsOutPickDetail] ;
|
||
SELECT count( *) FROM [dwms4].[dbo].[t_wmsOutPickDetail_error] ;
|
||
SELECT count( *) FROM t_wmsOutPickLable_tmp;
|
||
SELECT count(*) FROM [t_wmsOutPickRequest] ; -- where state =4 --order by createtime ;
|
||
SELECT * FROM t_wmsOutPickLable_tmp;
|
||
select * from [t_wmsOutPickRequest] where state =6;
|
||
|
||
select count(*), state from [t_wmsOutPickRequest]group by state;
|
||
|
||
|
||
select sum(count) from t_wmsOutDetail --a where not exists(select 1 from t_wmsOutPickDetail where orderNo = a.orderno)
|
||
select sum(count) from t_wmsOutPickDetail;
|
||
|
||
|
||
SELECT * FROM [t_wmsOutPickRequest] where state =6 and batchpickstate >=0
|
||
select * from t_wmsOutDesk
|
||
|
||
|
||
select * from t_wmsOutPickDetail where pickOrderNo in (
|
||
select pickOrderNo from t_wmsOutPickRequest a WHERE WAVEORDER ='WAVE060000070562'
|
||
and( not exists(select 1 from t_wmsOutPickDetail b where a.pickOrderNo = b.pickOrderNo and b.bulkcount !=b.count ))
|
||
);
|
||
|
||
select * from v_stockOutRequestPick
|
||
|
||
select * from t_wmsOutPickDetail a WHERE WAVEORDER ='WAVE060000070562'
|
||
and( not exists(select 1 from t_wmsOutPickDetail b where a.pickOrderNo = b.pickOrderNo and batchPickState in(-1,2) ));
|
||
|
||
|
||
|
||
UPDATE t_wmsOutPickRequest SET state =100 from t_wmsOutPickRequest a WHERE WAVEORDER ='WAVE060000070562'
|
||
and( not exists(select 1 from t_wmsOutPickDetail b where a.pickOrderNo = b.pickOrderNo and b.bulkcount !=b.count ));
|
||
|
||
|
||
goodsId batch
|
||
G1WEYPQL5ZB 1607311
|
||
|
||
SELECT top 1 1 FROM t_wmsOutPickLable_tmp
|
||
select * from v_stockGoods where goodsId ='G1WEYPQL5ZB' and batch='1607311' ;
|
||
|
||
SELECT * FROM [dwms4].[dbo].[t_wmsOutPickDetail] where pickOrderNo ='PICK050000025191' and goodsId ='G30F1R90GSZ' and batch='161208' ;
|
||
select * from t_wmsStockRecord where id = 42392
|
||
|
||
select * from t_wmsLocation where locationId = '2B1101-25' and elabAddress =0 and volType =0 and elabId >0;
|
||
|
||
SELECT count(*),elabId ,elabAddress FROM t_wmsOutPickLable_tmp group by elabId,elabAddress order by elabId;
|
||
SELECT * FROM t_wmsOutPickLable_tmp;
|
||
|
||
select pickOrderNo, elabId,elabAddress,count,color ,state from t_wmsOutPickLable_tmp where 1=1
|
||
--and state = 0
|
||
order by pickOrderNo;
|
||
|
||
|
||
select elabId,elabAddress,count,color ,state from t_wmsOutPickLable_tmp where 1=1
|
||
--and state = 0
|
||
order by elabId;
|
||
|
||
|
||
select count(*),elabId,elabAddress,color from t_wmsOutPickLable_tmp group by elabId,elabAddress,color;
|
||
|
||
select count(*),color from t_wmsOutPickLable_tmp group by color;
|
||
;
|
||
|
||
select * from t_wmsOutPickLable_tmp where elabId =5 order by elabaddress;
|
||
select recordID, count(*) from t_wmsOutPickLable_tmp group by recordID having count(*) > 1
|
||
|
||
|
||
SELECT elabid as ele_lab_id,elabAddress as ele_address,port,count as pick_count,count - pickCount as send_count,
|
||
color ,color as ele_order FROM [t_wmsOutPickLable_tmp] a where state =0 order by ele_order, color
|
||
|
||
select * from t_wmsOutPickRequest
|
||
|
||
|
||
select * from t_wmsOutRequest a where not exists(select 1 from t_wmsOutPickDetail where orderNo = a.orderno);
|
||
|
||
;
|
||
select * from t_wmsOutDetail a where not exists(select 1 from t_wmsOutPickDetail where orderNo = a.orderno);
|
||
|
||
select sum(count) from t_wmsOutDetail --a where not exists(select 1 from t_wmsOutPickDetail where orderNo = a.orderno)
|
||
select sum(count) from t_wmsOutPickDetail;
|
||
|
||
|
||
SELECT elabid as ele_lab_id,elabAddress as ele_address,port,count as pick_count,count - pickCount as send_count, color ,color as ele_order
|
||
FROM [t_wmsOutPickLable_tmp] a ;
|
||
|
||
SELECT elabid as ele_lab_id,elabAddress as ele_address,port,count as pick_count,count - pickCount as send_count, color ,color as ele_order
|
||
FROM [t_wmsOutPickLable_tmp] a order by port,color;;;
|
||
|
||
SELECT * FROM [v_stockOutDetailByCust] ;
|
||
|
||
|
||
select orderno, sum(count) from t_wmsOutDetail group by orderNo --a where not exists(select 1 from t_wmsOutPickDetail where orderNo = a.orderno)
|
||
select orderno, sum(count) from t_wmsOutPickDetail group by orderNo ;
|
||
|
||
select * from t_wmsOutDetail --a where not exists(select 1 from t_wmsOutPickDetail where orderNo = a.orderno)
|
||
select * from t_wmsOutPickDetail where orderno is null;
|
||
|
||
select * from t_wmsOutDetail where goodsId ='G30FUQZGXCB' and batch ='20170106 201812';
|
||
select * from t_wmsOutDetail where goodsId ='G30FUQZGXCB' and batch ='20161026 20181025';
|
||
|
||
select * from t_wmsOutPickDetail where goodsId ='G30FUQZGXCB' and batch ='20170106 201812'
|
||
select * from t_wmsOutPickDetail where goodsId ='G30FUQZGXCB' and batch ='20161026 20181025'
|
||
|
||
select * from t_wmsOutDetail where orderNo is null or customerId is null;
|
||
|
||
select state,sum(count) from t_wmsOutDetail group by state;
|
||
|
||
select * from t_wmsOutRequest where 1=1 and state !=0 and state!=2
|
||
|
||
|
||
select * from t_wmsOutPickDetail a where exists (select 1 from t_wmsOutPickRequest where pickOrderNo = a.pickorderNo and waveOrder ='WAVE060000060949')
|
||
select * from t_wmsOutPickRequest order by waveOrder desc;
|
||
|
||
|
||
|
||
select * from t_wmsOutPickDetail where orderno is null
|
||
|
||
|
||
select goodsId,batch, sum(count) from t_wmsOutDetail group by goodsId,batch --a where not exists(select 1 from t_wmsOutPickDetail where orderNo = a.orderno)
|
||
except
|
||
select goodsId,batch, sum(count) from t_wmsOutPickDetail group by goodsId,batch
|
||
|
||
select * from t_wmsOutDetail a where exists (select 1 from t_wmsOutRequest where orderNo = a.orderNo and state =2 ) ;
|
||
|
||
select orderNo,goodsId,batch, count from t_wmsOutDetail where goodsid = 'G30FUQZGXCB' and batch ='20161026 20181025'
|
||
;
|
||
select * from t_wmsOutRequest where 1=1
|
||
and state =2
|
||
and orderNo = 'PTPXAM00000014'
|
||
|
||
select pickOrderNo, orderNo,goodsId,batch, count from t_wmsOutPickDetail where goodsid = 'G30FUQZGXCB' and batch ='20161026 20181025'
|
||
|
||
|
||
|
||
|
||
/*
|
||
|
||
对比原始销售订单明细汇总 和 拼单后出库单汇总 数量，应该一致。
|
||
|
||
*/
|
||
|
||
select sum(count) from t_wmsOutDetail a where
|
||
not exists (select 1 from t_wmsOutRequest where orderNo = a.orderNo and state=2) ; -- 除去缺货的订单
|
||
|
||
select sum(count) from t_wmsOutPickDetail a where orderno is not null ; --除去补库单
|
||
|
||
|
||
select * from v_stockOutDetailByCust
|
||
|
||
|
||
SELECT * FROM v_stockOutValidationException order by waveOrder,desk desc;
|
||
|
||
|
||
SELECT * from [v_stockOutWavePickDetail] where pickOrderNo ='PICK050000057116';
|
||
|
||
|
||
SELECT dbo.t_wmsOutPickRequest.waveOrder, dbo.t_wmsOutPickRequest.pickOrderNo, dbo.v_wmsGoods.goodsName,
|
||
dbo.v_wmsGoods.manufacturer, dbo.v_wmsGoods.spec, dbo.v_wmsGoods.unit, dbo.v_wmsGoods.regeditCode,
|
||
dbo.v_wmsGoods.drug_code, dbo.v_wmsGoods.barCode, dbo.v_wmsGoods.type,
|
||
dbo.t_wmsOutPickRequest.customerName, dbo.t_wmsOutPickRequest.customerId, dbo.t_wmsStockRecord.count,
|
||
dbo.t_wmsStockRecord.locationId, dbo.t_wmsStockRecord.batch, dbo.t_wmsStockRecord.goodsId,
|
||
dbo.t_wmsStockRecord.validDate, dbo.t_wmsStockRecord.productDate, dbo.t_wmsLocation.volType,
|
||
dbo.t_wmsLocation.elabId, dbo.t_wmsLocation.elabAddress, dbo.t_wmsStockRecord.id,
|
||
dbo.t_wmsOutPickRequest.pickOrderNo,
|
||
dbo.t_wmsOutPickRequest.state
|
||
FROM dbo.t_wmsStockRecord LEFT OUTER JOIN
|
||
dbo.t_wmsLocation ON dbo.t_wmsStockRecord.locationId = dbo.t_wmsLocation.locationId LEFT OUTER JOIN
|
||
dbo.v_wmsGoods ON dbo.t_wmsStockRecord.goodsId = dbo.v_wmsGoods.goodsId left OUTER JOIN
|
||
dbo.t_wmsOutPickRequest ON dbo.t_wmsStockRecord.orderNo = dbo.t_wmsOutPickRequest.pickOrderNo
|
||
|
||
where orderno ='PICK050000057116';
|
||
|
||
select * from dbo.t_wmsStockRecord LEFT OUTER JOIN
|
||
dbo.t_wmsLocation ON dbo.t_wmsStockRecord.locationId = dbo.t_wmsLocation.locationId LEFT OUTER JOIN
|
||
dbo.v_wmsGoods ON dbo.t_wmsStockRecord.goodsId = dbo.v_wmsGoods.goodsId where orderno ='PICK050000057116';;
|
||
|
||
select * from dbo.t_wmsOutPickRequest order by pickorderno asc where pickorderno ='PICK050000057116';
|
||
|
||
|
||
select * from v_stockOutPickDetailValidation;
|
||
|
||
|
||
|
||
delete from t_wmsStock where count - countout <=0;
|
||
|
||
|
||
select *from t_wmsStockRecord where goodsid ='G1WPD68D7FN' and orderno ='PICK060000061128';
|
||
|
||
select *from t_wmsOutPickDetail where goodsid ='G1WPD68D7FN' and orderno ='SKPXAM00000765';
|
||
|
||
|
||
select *from t_wmsOutPickLable a left outer join t_wmsStockRecord b on a.pickOrderNo = b.orderNo where goodsid ='G1WPD68D7FN' and pickOrderNo ='PICK060000061128';
|
||
|
||
|
||
|
||
select * from v_stockOutRequestPick;
|
||
|
||
|
||
select * from t_wmsOutLog where orderno ='PTPXAM00000069 ';
|
||
select * from t_wmsOutRequest where orderno ='PTPXAM00000069 '
|
||
|
||
update t_wmsOutRequest set state =4 where orderno ='PTPXAM00000105 ';
|
||
|
||
|
||
select * from t_wmsOutLog where pickorderno ='PICK060000061939 ';
|
||
|
||
|
||
|
||
insert into t_wmsoutdetail([orderNo] ,[goodsId] ,[batch] ,[count] ,[bulkCount],[batch1Count],[boxcnt],[productDate],[state] ,[validDate],[whtype])
|
||
SELECT [orderNo] ,[goodsId] ,[batch] ,[count] ,[bulkCount],[batch1Count],[boxcnt],[productDate],[state] ,[validDate],[whtype] FROM [v_stockOutDetail_available] ;
|
||
|
||
insert into t_wmsOutRequestDetail([orderNo] ,[orderSn] ,[goodsId] ,[batch] ,[productDate] ,[validDate] ,[Count] ,[saleDate] ,[state] ,[whtype])
|
||
SELECT [orderNo] ,[orderSn] ,[goodsId] ,[batch] ,[productDate] ,[validDate] ,[Count] ,[saleDate] ,[state] ,[whtype] FROM [v_stockOutRequestDetail_available]
|
||
insert into t_wmsOutRequest([orderNo],[orderTypeName],[customerId],[customerName],[address] ,[delivery],[phone1],[phone2] ,[salesperson] ,[contactperson] ,[totalnumber],[remark] ,[orderDate])
|
||
SELECT [orderNo],[orderTypeName],[customerId],[customerName],[address] ,[delivery],[phone1],[phone2] ,[salesperson] ,[contactperson] ,[totalnumber],[remark] ,[orderDate] FROM [v_stockOutRequest_Available]
|
||
|
||
|
||
SELECT*
|
||
FROM [dwms4].[dbo].[t_wmsOutPickRequest];
|
||
|
||
|
||
SELECT *
|
||
FROM [dwms4].[dbo].[t_wmsOutRequest] where state =0;
|
||
|
||
select * from sysprocesses where dbid in (select dbid from sysdatabases where name='dwms4') order by status;
|
||
|
||
|
||
|
||
select * from v_stockOutWavePickDetail where pickOrderNo ='PICK060000070871', 'PICK060000071112'
|
||
|
||
select pickOrderNo,locationId ,count(*) from v_stockOutWavePickDetail group by pickOrderNo,locationId having count(*) >1; |