NOVOTS KMS 词汇表 Glossary    联系我们 Contact Us
查询 Search  
   
按类别浏览 Browse by Category
NOVOTS KMS .: 数据库 .: 刷新其他调整

刷新其他调整

--取数时间段             
             
   
declare  @BeginDate datetime, @EndDate datetime
set  @BeginDate='2011-08-27'
set @EndDate='2011-10-07'
             
-- 获取EDI下的经销             
SELECT d_r.distributorid,ResellerID,ProductGroupID              
INTO #EDI_Reseller             
from dbo.e04_Relation_D_R as d_r              
inner join (SELECT DISTINCT distributorID FROM dbo.TS_DistributorRights WHERE [TYPE]=1 AND [State]=1) edi on d_r.distributorid=edi.distributorid               
               
--清空数据               
truncate table reseller_luckyid               
delete from reseller_lostluckyidHistory where indate >=@BeginDate and indate<=@EndDate               
               
--插入:所有分销上报出货给经销商幸运号失败的数据              
insert into reseller_luckyid --select * from reseller_luckyid             
SELECT DISTINCT a.Check_Code AS LuckyID,'' ModelName,c.ModelID,c.ProductGroupID,c.ProductGroupName,               
       ISNULL(g.DistributorID,RTRIM(e.KUNAG)) AS DistributorID, RTRIM(e.AUGRU) AS OrderReason               
      ,xx.ToChannelid AS RessellerID,'' AS SalePlaceID,xx.uploaddate AS indate,'0' typeid,getdate() systemtime,             
      '0' mstatus_id,'需要调整' mstatus_name,xx.createdtime             
      ,NULL matnr,NULL ChannelID,NULL Meno,xx.ToChannelSubAreaID,xx.OutType,xx.DataSource,e.VTWEG     
         
FROM dbo.Tbl_Machine_Sequence_All_View AS a    --主机号与幸运号             
INNER JOIN dbo.CC_Sale_Barcode AS b ON b.machine_seq = a.Machine_No              --主机号             
LEFT  JOIN dbo.TS_ItemInfo AS c ON c.matnr = RIGHT('000000000000000000' + b.item_code, 18) --物料             
INNER JOIN dbo.ts_i_0201 AS e ON e.VGBEL = b.bill AND e.SPART = c.ProductGroupID --订单             
INNER JOIN dbo.TS_InChannelError xx  on a.Check_Code = xx.luckyid                --上报错误数据                
LEFT  JOIN dbo.Distributor_info AS g ON g.DistributorID = RTRIM(e.rkunnr) AND g.Valid = 1  --分销             
LEFT  JOIN dbo.Distributor_info AS h ON h.DistributorID = RTRIM(e.KUNAG)  AND h.Valid = 1  --分销             
WHERE xx.uploaddate >=@BeginDate and xx.uploaddate <=@EndDate             
AND (RTRIM(e.VTWEG) = '11' OR RTRIM(e.VTWEG) = '61' OR RTRIM(e.VTWEG) = '31')                    
AND (RTRIM(e.AUART) = 'ZOR')              
AND xx.[message] like '%幸运号不存在;%'               
             
--插入:经销商上报STI幸运号不存在(除去EDI上报)取VD(61)+FD(11)下的商用产品组的数据           
insert into reseller_luckyid --select * from reseller_luckyid               
SELECT DISTINCT a.Check_Code AS LuckyID,'', c.ModelID, c.ProductGroupID, c.ProductGroupName,               
       ISNULL(g.DistributorID, RTRIM(e.KUNAG)) AS DistributorID, RTRIM(e.AUGRU) AS OrderReason               
       ,xx.resellerid,'' AS SalePlaceID,xx.indate,'1' typeid,getdate() systemtime,             
       '0' mstatus_id,'需要调整' mstatus_name,xx.createdtime             
       ,NULL matnr,NULL ChannelID,NULL Meno,r.SubAreaID as ToChannelSubAreaID,null outType,NULL DataSource,e.VTWEG               
FROM dbo.Tbl_Machine_Sequence_All_View AS a              
INNER JOIN dbo.CC_Sale_Barcode AS b  ON b.machine_seq = a.Machine_No             
LEFT  JOIN dbo.TS_ItemInfo AS c      ON c.matnr = RIGHT('000000000000000000' + b.item_code, 18)              
INNER JOIN dbo.ts_i_0201 AS e        ON e.VGBEL = b.bill AND e.SPART = c.ProductGroupID             
inner join (dbo.TS_ResellerInError xx LEFT JOIN dbo.e04_Reseller R ON xx.ResellerID=r.ResellerID) ON a.Check_Code = xx.lackyid                  
LEFT  JOIN dbo.Distributor_info AS g ON g.DistributorID = RTRIM(e.rkunnr) AND g.Valid = 1              
LEFT  JOIN dbo.Distributor_info AS h ON h.DistributorID = RTRIM(e.KUNAG)  AND h.Valid = 1             
left join #EDI_Reseller yy on yy.resellerid=xx.resellerid and yy.productgroupid=c.productgroupid               
WHERE xx.indate >=@BeginDate and xx.indate <=@EndDate        
AND (RTRIM(e.VTWEG)<>'31')   --排除31通路       
AND (RTRIM(e.AUART) = 'ZOR') --联想正常订单                
AND (RTRIM(e.VTWEG) = '61' OR (RTRIM(e.VTWEG) = '11' AND e.SPART IN(SELECT productGroupID from ProductGroupAbout WHERE AboutChannelID=4)))--61+11下商用产品组      
and xx.errormessage like '%幸运号不存在;%'                
and yy.distributorid is NULL                
             
/*=========================================================修改数据===============================================================*/                         
             
--3 标注后上报的               
select luckyid,min(createdtime) createdtime INTO #reseller_luckyidTemp             
from dbo.reseller_luckyid              
where mstatus_id='0'              
group by luckyid             
             
update A set a.mstatus_id='1',a.mstatus_name='后上报'               
from dbo.reseller_luckyid A             
INNER JOIN #reseller_luckyidTemp B ON A.luckyid = B.luckyid              
WHERE A.createdtime > B.createdtime and a.mstatus_id='0'               
               
--4 标注之前已经调整的记录               
--更新直接进货渠道和自营店               
update  A set a.mstatus_id='2',a.mstatus_name='调整过'               
from reseller_luckyid a               
INNER JOIN reseller_lostluckyidHistory b on a.luckyid=b.luckyid                 
where a.mstatus_id='0'             
               
--5 编辑已经上报成功的               
--更新直接进货渠道对经销商和自营店出货               
UPDATE a SET  a.mstatus_id='3',a.mstatus_name='已经上报成功过'               
FROM reseller_luckyid a                
INNER JOIN TS_InChannelHistory b ON a.luckyid=b.luckyid               
where a.typeid IN('0','3') and a.mstatus_id='0'             
               
--更新经销商SI               
UPDATE a set a.mstatus_id='3',a.mstatus_name='已经上报成功过'               
FROM reseller_luckyid a                
INNER JOIN TS_ResellerHistory b ON a.luckyid=b.luckyid               
where a.typeid='1' and a.mstatus_id='0'             
               
--6 去掉特单--特单对所有的渠道都有效               
update  A set a.mstatus_id='4',a.mstatus_name='特单'               
from reseller_luckyid a               
inner join TS_SpecialOrderReason b  on a.orderreason=b.orderreason and a.ProductGroupID=b.ProductGroupID               
where a.mstatus_id='0'               
             
--7去掉上报日期超过出库日期半年的幸运号(手动操作)--对以上四种情况有效               
update  A set a.mstatus_id='5',a.mstatus_name='幸运号出库超过半年'                
from reseller_luckyid a              
inner join dbo.Tbl_Machine_Sequence_All_View b on a.luckyid=b.check_code               
INNER JOIN dbo.CC_Sale_Barcode AS c ON c.machine_seq = b.Machine_no             
WHERE a.mstatus_id='0' AND DATEADD(day, - 1, c.scan_date) <= CONVERT(char(10), DATEADD(month,- 6,a.indate), 111)             
             
--8 标注已经调整过DOA机器的               
update  A set a.mstatus_id='6',a.mstatus_name='调整过DOA'               
from reseller_luckyid a               
INNER join reseller_DOAluckyidHistory b on a.luckyid=b.luckyid             
where a.mstatus_id='0'             
             
--标记2922事件               
UPDATE a SET a.mstatus_id='7',a.mstatus_name='2922事件'               
FROM dbo.reseller_luckyid a              
inner JOIN dbo.TS_MACHINE_ID b ON a.luckyid=b.luckyid               
WHERE a.mstatus_id='0'             
             
--标记为网络时间               
UPDATE a SET a.mstatus_id='8',mstatus_name='IntelEvents'               
FROM dbo.reseller_luckyid_DOA a              
INNER JOIN dbo.reseller_luckyid_IntelEvents b ON a.luckyid=b.check_code               
WHERE a.mstatus_id='0'                
               
-- 9 更新机型名称--对以上几种情况有效               
update a set a.modelname=b.modelname              
from reseller_luckyid a             
INNER JOIN ts_iteminfo b on a.modelid=b.modelid              
where a.mstatus_id='0'               
               
--补入物料,通路,原因 --20110126by huangbz               
---幸运号不存在=========================================================================               
--第一步 更新物料               
UPDATE a SET a.matnr=RIGHT(RTRIM('000000000000000000000000000'+b.Material_No),18)               
FROM reseller_luckyid a              
inner JOIN dbo.Tbl_Machine_Sequence_All_View b ON b.Check_Code=a.luckyid               
WHERE a.mstatus_id='0'               
               
--第二步 刷新通路,所刷通路可能与订单中的通路不符,导致看上去取的数据错误               
--'EDI上报出货-主机号不存在' and '经销商上报STI(剔除EDI下的渠道)-主机号不存在'                
UPDATE a SET a.channelid=b.lenovochanneltype               
FROM reseller_luckyid a              
inner JOIN  dbo.e04_Relation_D_R b ON a.distributorid=b.distributorid AND a.productgroupid=b.ProductGroupid               
WHERE a.typeid='0' AND a.mstatus_id='0'              
               
--刷新通路,when '1' then '经销商上报STI(剔除EDI下的渠道)-主机号不存在'                
UPDATE a SET a.channelid=b.lenovochanneltype               
FROM reseller_luckyid a              
inner JOIN dbo.e04_Relation_D_R b ON a.resellerid=b.ResellerID AND a.distributorid=b.DistributorID AND a.productgroupid=b.productgroupid               
WHERE a.typeid='1' AND a.mstatus_id='0'               
              
--原因详情               
UPDATE reseller_luckyid SET meno='工厂数据' WHERE mstatus_id='0'               
               
--9 插入到历史表备份               
insert into reseller_lostluckyidHistory              
select distinct datepart(week,getdate()), * from reseller_luckyid               
               
               
END

-- =============================================                 
-- Author:  黄炳正                 
-- Create date: 2010-07-27                 
-- Description: 每周提供曾璐手工补入的DOA机器                 
-- 每周三执行     exec [SP_TS_GetReseller_DOAluckyid]            
-- =============================================                 
CREATE PROCEDURE [dbo].[SP_TS_GetReseller_DOAluckyid]                    
AS                 
BEGIN                 
                 
declare @BeginDate datetime, @EndDate DATETIME             
set @BeginDate=convert(varchar(10),getdate()-9,120)   --周三                 
set @EndDate=convert(varchar(10),getdate()-3,120)     --周三              
                  
--set @BeginDate=convert(varchar(10),getdate()-10,120)--周四                 
--set @EndDate=convert(varchar(10),getdate()-4,120)   --周四                 
                 
--set @BeginDate=convert(varchar(10),getdate()-11,120)--周五                 
--set @EndDate=convert(varchar(10),getdate()-5,120)   --周五              
                   
--set @BeginDate=convert(varchar(10),getdate()-12,120)--周六                 
--set @EndDate=convert(varchar(10),getdate()-6,120)   --周六                
             
-- 获取EDI下的经销             
SELECT d_r.distributorid,ResellerID,ProductGroupID              
INTO #EDI_Reseller             
from dbo.e04_Relation_D_R as d_r              
inner join (SELECT DISTINCT distributorID FROM dbo.TS_DistributorRights WHERE [TYPE]=1 AND [State]=1) edi on d_r.distributorid=edi.distributorid               
             
--1 步骤 清空数据                
truncate table dbo.reseller_luckyid_DOA                 
delete from dbo.reseller_DOAluckyidHistory                   
where  indate >=@BeginDate and  indate <=@EndDate                 
                 
--2 步骤 ,收集数据              
--插入:所有分销上报幸运号不存在,订单中没有交货单号的数据视为DOA机器             
insert into reseller_luckyid_DOA                 
select DISTINCT xx.luckyid,f.modelname,f.modelid,f.productgroupid,f.ProductGroupName,'' DistributorID,'' OrderReason,             
       xx.tochannelid ResellerID,'' saleplaceno,xx.uploaddate indate,'0' typeid,getdate() Systemtime,             
       '0' mstatus_id,'需要调整' mstatus_name,'0' outflag,xx.createdtime,             
       NULL matnr,NULL channelID,NULL Meno,xx.ToChannelSubAreaID,xx.OutType,xx.DataSource,e.VTWEG                 
from TS_InChannelError xx              
inner JOIN dbo.Tbl_Machine_Sequence_All_View a on xx.luckyid =a.check_code                 
inner join CC_Sale_Barcode b on a.machine_no=b.machine_seq                 
left  join (SELECT VGBEL,VTWEG FROM dbo.ts_i_0201 WHERE RTRIM(VTWEG) IN ('11','61','31') AND (RTRIM(AUART) = 'ZOR')) AS e ON e.VGBEL = b.bill                   
left  join ts_iteminfo f ON f.matnr = RIGHT('000000000000000000' + b.item_code, 18) --物料                  
where xx.uploaddate >=@BeginDate and xx.uploaddate <=@EndDate             
and xx.[message] like '%幸运号不存在;%'              
and e.VGBEL is NULL  --不存在交货单号的数据为DOA机器               
             
--插入:经销商上报STI的DOA机器(除去EDI上报)              
insert into reseller_luckyid_DOA   
select DISTINCT xx.lackyid,f.modelname,f.modelid,f.productgroupid,f.ProductGroupName,'' DistributorID,'' OrderReason,             
       xx.resellerid,'' saleplaceno,xx.indate,'1' typeid,getdate() Systemtime,             
       '0' mstatus_id,'需要调整' mstatus_name,'0' outflag,xx.createdtime,             
       NULL matnr,NULL channelID,NULL Meno,r.SubAreaID as ToChannelSubAreaID,NULL OutType,NULL DataSource,e.VTWEG                
from TS_ResellerInError xx --经销进货错误数据             
inner join dbo.Tbl_Machine_Sequence_All_View a on xx.lackyid =a.check_code                 
inner join CC_Sale_Barcode b on a.machine_no=b.machine_seq                 
left  join (SELECT VGBEL,VTWEG,SPART FROM dbo.ts_i_0201      
            WHERE (RTRIM(VTWEG)<>'31') --排除31通路       
              AND (RTRIM(AUART) = 'ZOR') --联想正常订单                  
              AND (RTRIM(VTWEG) = '61' OR (RTRIM(VTWEG) = '11' AND SPART IN(SELECT productGroupID from ProductGroupAbout WHERE AboutChannelID=4)))     
           ) AS e ON e.VGBEL = b.bill         
left  join ts_iteminfo f ON f.matnr = RIGHT('000000000000000000' + b.item_code, 18) --物料                  
left  join #EDI_Reseller yy on yy.resellerid=xx.resellerid and yy.productgroupid=f.productgroupid              
LEFT  JOIN dbo.e04_Reseller R ON xx.ResellerID=r.ResellerID   
where xx.indate >=@BeginDate and xx.indate <=@EndDate                 
and xx.errormessage like '%幸运号不存在;%'                  
and e.VGBEL is null  --不存在交货单号的数据为DOA机器      
and yy.distributorid is NULL   
               
                  
-----------------------------------------------------修改数据--------------------------------------------------------------------               
             
--标记为后上报               
select luckyid,min(createdtime) createdtime INTO #DOATemp              
from dbo.reseller_luckyid_DOA              
where mstatus_id='0'              
group by luckyid              
             
update  A set a.mstatus_id='1',a.mstatus_name='后上报'              
from dbo.reseller_luckyid_DOA A             
INNER JOIN #DOATemp B ON A.luckyid = B.luckyid                  
where A.createdtime > B.createdtime and a.mstatus_id='0'                 
                  
--标记为已经调整过               
update A set a.mstatus_id='2',a.mstatus_name='已经调整过'                 
from dbo.reseller_luckyid_DOA a                 
INNER join reseller_DOAluckyidHistory b on a.luckyid=b.luckyid                   
where a.mstatus_id='0'             
             
--标记为已经调整过               
update  A set a.mstatus_id='3',a.mstatus_name='调整过幸运号'                 
from dbo.reseller_luckyid_DOA a                 
INNER join (SELECT luckyid FROM reseller_lostluckyidHistory WHERE mstatus_id='0') b on a.luckyid=b.luckyid                   
where a.mstatus_id='0'             
             
--标注2922事件                 
UPDATE a SET a.mstatus_id='4' ,mstatus_name='2922事件'                 
FROM dbo.reseller_luckyid_DOA a              
INNER JOIN dbo.TS_MACHINE_ID b ON a.luckyid=b.luckyid                 
WHERE a.mstatus_id='0'                  
               
--标记为已出库超过半年             
update A set a.mstatus_id='5',a.mstatus_name='出库超过半年'                
from reseller_luckyid_DOA a              
inner join dbo.Tbl_Machine_Sequence_All_View b on a.luckyid=b.check_code               
INNER JOIN dbo.CC_Sale_Barcode AS c ON c.machine_seq = b.Machine_no             
WHERE a.mstatus_id='0' AND DATEADD(day, - 1, c.scan_date) <= CONVERT(char(10), DATEADD(month,- 6,a.indate), 111)              
             
-----------------------------------跟新基础信息--对以上几种情况有效------------------------------------                
--更新机型名称              
update a set a.modelname=b.modelname              
from reseller_luckyid_DOA a             
INNER JOIN ts_iteminfo b ON a.modelid=b.modelid               
WHERE a.mstatus_id='0'              
             
--更新上级物流                
update a set a.distributorid=b.distributorid              
from reseller_luckyid_DOA a             
INNER JOIN dbo.e04_Relation_D_R b ON a.resellerid=b.resellerid and a.productgroupid=b.productgroupid                
where a.mstatus_id='0'                 
                 
update a set a.distributorid=b.distributorid              
from reseller_luckyid_DOA a             
INNER JOIN dbo.e04_Relation_D_S b on a.resellerid=b.shopid and a.productgroupid=b.productid               
where a.mstatus_id='0'                 
                 
-- 更新物料                 
UPDATE a SET  a.matnr=RIGHT(RTRIM('000000000000000000000000000'+b.Material_No),18)                 
FROM reseller_luckyid_DOA a              
inner JOIN dbo.Tbl_Machine_Sequence_All_View b ON b.Check_Code=a.luckyid                 
WHERE a.mstatus_id='0'                 
              
--更新分销对应经销所属通路-主机号不存在'                  
UPDATE a SET a.channelid=b.lenovochanneltype                 
FROM reseller_luckyid_DOA a              
inner JOIN dbo.e04_Relation_D_R b ON a.distributorid=b.distributorid AND a.ResellerID=b.ResellerID AND a.productgroupid=b.ProductGroupid                 
WHERE a.mstatus_id='0'              
             
--更新分销对应店面所属通路-主机号不存在'                     
UPDATE a SET a.channelid=b.lenovochanneltype                 
FROM reseller_luckyid_DOA a              
inner JOIN dbo.e04_Relation_D_S b ON a.distributorid=b.distributorid AND a.resellerid=b.shopid AND a.productgroupid=b.Productid                 
WHERE a.mstatus_id='0'              
             
--原因详情                 
UPDATE reseller_luckyid_DOA SET meno='订单不存在'                  
WHERE mstatus_id='0'                 
             
/*-------------------------------------------------备份到历史表中--------------------------------------------------------------*/                 
insert into dbo.reseller_DOAluckyidHistory  --select * From reseller_DOAluckyidHistory            
select distinct datepart(week,getdate()),luckyid,modelname,modelid,max(productgroupid) as productgroupid,ProductGroupName,             
        distributorid,orderreason,resellerid,saleplaceno,indate,typeid,systemtime,mstatus_id,mstatus_name,outflag,createdtime,             
        matnr,channelid,meno,ToChannelSubAreaID,outType,DataSource,VTWEG                 
from reseller_luckyid_DOA  --计算时,要把条件加上,不加上比较一下 where isnull(distributorid,'')<>''   
WHERE typeid=0 OR (typeid=1 and channelid='61' or (channelid='11' and productgroupid IN(SELECT productGroupID from ProductGroupAbout WHERE AboutChannelID=4)))--61+11商用产品组                                
group by luckyid,modelname,modelid,distributorid,orderreason,resellerid,saleplaceno,indate,typeid,systemtime,             
         mstatus_id,mstatus_name,createdtime,ProductGroupName,matnr,channelid,meno,outflag,ToChannelSubAreaID,outType,DataSource,VTWEG     
             
/*------------------------------------------------------------获取经销商的OUT数据---------------------------------------------------------------*/             
--获取经销报出货同一个LUCKYNO只获得最早出库,锁定关系正确的数据                 
SELECT lackyid,MIN(id) id INTO #TS_ResellerOutError_front              
FROM TS_ResellerOutError a             
INNER JOIN (SELECT luckyid,ResellerID,ProductGroupID              
            FROM dbo.reseller_DOAluckyidHistory              
            WHERE mstatus_id='0'  and outflag='0'             
            ) b ON a.lackyid=b.luckyid AND a.ResellerID=b.ResellerID AND a.ProductGroupID = b.ProductGroupID               
INNER JOIN dbo.e04_Relation_R_S r_s ON a.resellerid=r_s.ResellerID AND a.saleplaceid=r_s.ShopID AND a.productgroupid=r_s.ProductGroupID             
WHERE a.outdate >=@BeginDate and  a.outdate <=@EndDate and ISNULL(a.ErrorMessage2,'')=''                      
GROUP BY lackyid                   
               
--获取分销出给经销与经销商上报进,幸运号不存在的数据,在经销报OUT里同样失败的数据               
SELECT DISTINCT a.* INTO #TS_ResellerOutError                 
FROM #TS_ResellerOutError_front front             
INNER JOIN dbo.TS_ResellerOutError a ON front.LackyID=a.LackyID AND front.id=a.id             
             
--将需要调整的OUT数据插入到历史表中  select * From reseller_DOAluckyidHistory             
INSERT INTO dbo.reseller_DOAluckyidHistory                 
SELECT 0,b.[luckyid], b.[ModelName], b.[ModelID], b.[ProductGroupID], b.[ProductGroupName], b.[DistributorID], b.[OrderReason], b.[ResellerID]             
,a.[saleplaceid],a.outdate,'2' typeid,GETDATE() systemtime,b.[mstatus_id],b.[mstatus_name],1 outflag,a.[createdtime],           
b.[matnr],b.[ChannelID],b.[Meno],b.ToChannelSubAreaID,b.outType,b.DataSource,b.VTWEG             
FROM #TS_ResellerOutError a              
INNER JOIN (SELECT * FROM dbo.reseller_DOAluckyidHistory WHERE mstatus_id='0' and outflag='0') b              
ON a.lackyid=b.luckyid AND a.resellerid=b.ResellerID AND a.ProductGroupID = b.ProductGroupID                
                 
            
/*            
--更新当期调整过OUT的STI数据             
UPDATE a SET a.outflag='1'                 
FROM reseller_DOAluckyidHistory  a     
INNER JOIN #TS_ResellerOutError b ON a.lackyid=b.luckyid AND a.resellerid=b.ResellerID AND a.ProductGroupID = b.ProductGroupID               
WHERE a.mstatus_id='0' AND LEFT(ResellerID,1)='k' and outflag='0'             
*/           
             
--删除临时表             
DROP TABLE #TS_ResellerOutError;             
DROP TABLE #TS_ResellerOutError_front;             
DROP TABLE #EDI_Reseller;             
             
END


这篇文章对你多有用?

用户评语

添加评语
当前还没有评语.


.: .: .: .: .:
[ 登陆 ]
北京护航科技有限公司 2006

Novots Technologies Limited