- /// | / // - // - - // - (@ @) - ┏┏━━━━━━━━ooo - (_) - oooo┓┓ - Detends Detends Detecting 2004 -07-16 cost trial storage process definition, calculation cost o - ┗┗--━━━━━ ━--- () ┛┛ - ()) / - / ((_ / - / _) Create Procedure SP_COSTING_Compute (@out_matnr varchar (18), @out_werks varchar (4), @out_datetime datetime) AS - inserted Declare @matnr varchar (18), @ Werks Varchar (4 ), @ Kriqi DateTime, @ Meins Varchar (10) Declare @bomatnr varchar (18), @ Bomsehl Varchar (10), @ menge floator @Price float, @ Danw varchar (20) DECLARE @umrez float, @ aaprice float, @ JJPRICE FLOAT, @ arate float, @ jrate float, @ ask @ Jsy floatdeclare @cpjg float, @ umren floatset @cpjg = 0 - Computing products cost Price Declare mycursor cursor for select meins, Aprice, JPRICE from TB_MAT nr where convert (varchar (10), kriqi, 120) = convert (varchar (10), @ out_datetime, 120) and matnr = @out_matnr and plant = @ out_werksopen mycursorfetch mycursor into @ meins, @ aaprice, @ jjpriceclose mycursordeallocate mycursordeclare 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 bccursorfetch 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 = 'question' where matnr = @out_matnr and plant = @out_werks and convert (varchar (10), kriqi, 120) = convert ( VARCHAR (10), @ out_datetime, 120) Break end - If the price unit and the BOM unit are different, you need to convert if @BOMSEHL <> @danw begin declare zhgxcursor cursor for select umrez from tb_switch where matnr = @Bomatnr and msehl = @ Danw open zhgxcursor fetch zhgxcursor @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 view finished units and sales of finished units, if different add the conversion relation 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 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 endupdate 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
