stk_ordered_undeliveriedlist_mb.sql

文件包含程序源原文件

drop proc stk_ordered_undeliveriedlist
go

create procedure stk_ordered_undeliveriedlist                    
@fdate datetime,                    
@edate datetime                           
as                            
                            
-- select * from sozai_sunpo                
                            
/** 已订货到期但未交货历史 **/                            
SELECT KO.rec_id,KO.seihin_rec_id,KO.buhin_rec_id,KO.hiyo_kubun_rec_id,                            
       KO.shizai_rec_id,KO.unyo_rec_id,KO.hatchu_bango AS hatchu_id,                            
       KO.denpyo_bango AS hatchu_code,KO.st,KO.kikai_shitei AS hatchu_sha,                            
       KO.sagyosha_shitei AS irai_sha, KO.shiiresaki_shitei AS hatchu_saki,                            
       KO.chakushu_shitei AS tks_shitei, KO.kanryo_shitei AS knr_shitei ,                            
       KO.chakushu_bi, KO.kanryo_bi,KO.suryo,KO.tanka,KO.kingaku,KO.jisseki_gokei,                            
       KO.status AS ko_status,KO.try_kai,KO.flag2,KO.kotei_number,KO.maker_shitei AS maker,                            
       KO.shizai_meisho,KO.biko1,KO.biko2,KO.zaishitsu,KO.kikaku,KO.kodo,KO.jk_cd,                            
       KO.miss_flag,KO.suryo_tani,SH.seihin_bango,SH.seihin_meisho,SH.kanri_tsuban AS sequal_no,                            
       SH.seizo_noki,SH.keikaku_noki,SH.koji_kubun,SH.kishu,                            
       SH.kokyaku,SH.sonota_tanto,BH.buhin_bango,BH.buhin_meisho,BH.zumen_bango,                            
       BH.zumen_meisho,BH.status,BH.buhin_id, BH.tehai_kubun, BH.biko AS bh_biko,                            
       SZ.code as shizai_code, SZ.material AS shizai_zaishitsu,HY.genka_kubun,                            
       HY.hiyo_kubun,SS.rec_id AS sunpo_rec_id,SS.keijo_kubun,width=isnull(SS.width,0),                            
       SS.w_upper_limit,SS.w_lower_limit,length=isnull(SS.length,0),SS.l_upper_limit,                            
       SS.l_lower_limit,height=isnull(SS.height,0),SS.h_upper_limit,SS.h_lower_limit,                            
       SS.pr_width,SS.pr_length,SS.pr_height,SS.st_almighty1,SS.st_almighty2,                            
       SS.zairyo_cost,SS.kenma_cost,      
       weight= case (isnull(SS.weight,0)*1.00/100000)      
                 when 0 then 1 else (isnull(SS.weight,0)*1.00/100000)      
               end                             
INTO #ordered_UnDeliveryList                            
FROM kotei AS KO                             
JOIN hiyo_kubun AS HY ON(KO.hiyo_kubun_rec_id=HY.rec_id)                             
LEFT OUTER JOIN seihin AS SH                             
ON (KO.seihin_rec_id=SH.rec_id and SH.k_status=0 and SH.k_visible>0)                             
LEFT OUTER JOIN buhin AS BH                             
ON (KO.buhin_rec_id=BH.rec_id and BH.visible>0)                             
LEFT OUTER JOIN sozai_sunpo AS SS ON (SS.kotei_rec_id=KO.rec_id and                          
     SS.seihin_rec_id=KO.seihin_rec_id and SS.buhin_rec_id=KO.buhin_rec_id)                                 
LEFT OUTER JOIN shizai AS SZ                             
ON (KO.shizai_rec_id = SZ.rec_id AND SZ.visible>0)                            
WHERE KO.hatchu_bango>0 AND KO.jk_cd=1 AND KO.visible>0 AND KO.unyo_rec_id<=0 and                    
      (KO.kanryo_shitei between @fdate and @edate)                       
                            
/** 订单已交货的历史 **/                            
         
select hatchu_bango=order_no,            
       suryo=isnull(sum(quantity),0),            
       kingaku=isnull(sum(total_price),0)            
into #tmp_end               
from inventory_sheet (nolock)          
where kind in (0,2) and order_no in (select hatchu_id from #ordered_UnDeliveryList)          
group by order_no            
order by order_no   
  
                       
                   
-- select * from #tmp_end                            
                            
select t1.rec_id,t1.hatchu_id,t1.seihin_rec_id,t1.buhin_rec_id,                            
   t1.hiyo_kubun_rec_id,t1.shizai_rec_id,t1.shizai_meisho,t1.kikaku,                            
       t1.width,t1.length,t1.height,                            
       t1.unyo_rec_id,               
       t1.hatchu_sha,t1.irai_sha,                            
       t1.tks_shitei,t1.knr_shitei,                            
       t1.chakushu_bi,t1.kanryo_bi,                    
       t1.suryo,t1.weight,t1.kenma_cost,                    
       t1.tanka,t1.kingaku,                            
       t1.ko_status,t1.try_kai,t1.hatchu_saki,t1.maker,                            
       t1.biko1,t1.biko2,                            
       deliveried_suryo = isnull(t2.suryo,0),                            
       deliveried_kingaku = isnull(t2.kingaku,0),                     
       un_deliveried_suryo = isnull((round(isnull(t1.suryo,0),2)-round(isnull(t2.suryo,0),2)),0),                            
       un_deliberied_weight= isnull((round(isnull(t1.weight,0),2)*round(isnull(t1.suryo,0),2)-round(isnull(t2.suryo,0),2)),0),                
       t1.hatchu_code,                
       t1.sequal_no,        
       t1.suryo_tani   
into #tmp_ordered_undelivery                 
from #ordered_UnDeliveryList t1,                            
     #tmp_end t2                      
where t1.hatchu_id *= t2.hatchu_bango                     
order by t1.hatchu_id     
  
-- drop table #tmp_updateinfo  
select rec_id,hatchu_id,shizai_rec_id  
into #tmp_updateinfo   
from #tmp_ordered_undelivery  
where round(isnull(un_deliberied_weight,0),2)<=0.01  
  
  
/* 定单已入库量>=采购量 还在已订货的数据更新到已交货  */   
update t1  
  set jk_cd=3  
from kotei t1 (nolock),  
     #tmp_updateinfo t2 (nolock)  
where t1.rec_id = t2.rec_id and  
      t1.hatchu_bango = t2.hatchu_id and  
      t1.shizai_rec_id = t2.shizai_rec_id and  
      t1.jk_cd=1 and  
      t1.unyo_rec_id<=0   
  
  
  
/* 最终结果 */  
select *  
from #tmp_ordered_undelivery  
order by hatchu_id                    
                            
                            
/**                            
drop table #ordered_UnDeliveryList                             
exec stk_ordered_undeliveriedlist '2000/01/01 00:00:00','2006/10/01 01:00:00'                           
exec stk_ordered_undeliveriedlist '2005/06/27 00:00:00','2005/11/30 23:59:59'          
                            
select * from kotei                          
                            
**/