--                              \\\|///
--                             \\  - -  //
--                             (  @ @  )
--┏━━━━━━━━━oOOo-(_)-oOOo━┓
--┃   定义于2004-07-16                                  ┃
--┃  成本试算存储过程定义，计算成本  ┃
--┃                                                  Oooo   ┃
--┗━━━━━━━━━ oooO━-(   )━┛
--                                           (   )   ) /
--                                            \ (   (_/
--                                             \_)
CREATE PROCEDURE sp_costing_compute
    ( @out_matnr varchar(18),
     @out_werks varchar(4),
     @out_datetime datetime)
AS
--插入赠品
declare @matnr varchar(18),@werks varchar(4),@kriqi datetime,@meins varchar(10)
declare @bomatnr varchar(18),@bomsehl varchar(10),@menge float
declare @price float,@danw varchar(20)
declare @umrez float,@aaprice float,@jjprice float,@arate float,@jrate float,@asy float,@jsy float
declare @cpjg float,@umren float
set @cpjg = 0
--计算产成品成本价格
Declare mycursor cursor for select meins,aprice,jprice from tb_matnr where convert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)  and matnr = @out_matnr and plant=@out_werks
open mycursor
fetch mycursor into @meins,@aaprice,@jjprice
close mycursor
deallocate mycursor
declare  bccursor cursor for select bomatnr,bomsehl,menge from tb_bom where cpmatnr=@out_matnr and werks=@out_werks and convert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
open bccursor
fetch bccursor into @bomatnr,@bomsehl,@menge
   while @@fetch_status = 0
   begin
       declare jgcursor cursor for select price,meins from tb_bcjg where matnr=@bomatnr and plant=@out_werks and convert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
       open jgcursor
       fetch jgcursor into @price,@danw
       close jgcursor
       deallocate jgcursor
                   if @price = 0
                               begin
                                       update tb_matnr set mstatus='置疑' where matnr = @out_matnr and plant = @out_werks and convert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
                                       break
                               end
--如果价格单位和bom单位不同，需要进行转换
       if @bomsehl <> @danw
              begin
                   declare zhgxcursor cursor for select umrez from tb_switch where matnr = @bomatnr and msehl = @danw
                   open zhgxcursor
                   fetch zhgxcursor into @umrez
                   close zhgxcursor
                   deallocate zhgxcursor                 
              end    
      if @umrez is null
                 set @umrez = 1
      set @cpjg = @cpjg + @menge * @price / @umrez                     
     fetch next from bccursor into @bomatnr,@bomsehl,@menge
   end  
   close bccursor
   deallocate bccursor
--查看bom成品单位与成品销售单位，如果不同添加转换关系
     declare cpzhcursor cursor for select umren from tb_switch  where matnr = @out_matnr and msehl = @meins
     open cpzhcursor
     fetch cpzhcursor into @umren
     close cpzhcursor
     deallocate cpzhcursor
     if @umren is null
                 set @umren = 1
     if @cpjg <> 0
                 set @cpjg = @cpjg / @umren
  if not @aaprice is null      
          begin
             set @arate = @aaprice - @cpjg
             if @aaprice <> 0
                 set @asy=@arate / @aaprice
          end
     else
          begin
               set @arate=0
               set @asy=0
          end
     if not @jjprice is null
           begin
              set @jrate = @jjprice - @cpjg
              if @jjprice <> 0
                  set @jsy = @jrate / @jjprice
           end
     else
          begin
             set  @jrate = 0
             set  @jsy = 0
          end  

   update tb_matnr set cbprice = @cpjg,apeyoff=@arate,jpayoff=@jrate,arate=@asy,jrate=@jsy where matnr=@out_matnr and plant=@out_werks and convert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
GO