A period of time ago written about the cost calculation of the stored procedures!

Source: Internet
Author: User
Tags datetime
Stored Procedures
--                              \\\|///
--                             \\  - -  //
--                             (  @ @  )
--┏━━━━━━━━━oooo-(_)-oooo━┓
--┃ defined in 2004-07-16 ┃
--┃ Costing stored procedure definition, calculating cost ┃
--┃oooo┃
--┗━━━━━━━━━oooo━-() ━┛
--                                           (   )   ) /
--                                            \ (   (_/
--                                             \_)
CREATE PROCEDURE Sp_costing_compute
(@out_matnr varchar (18),
@out_werks varchar (4),
@out_datetime datetime)
As
--Insert Gift
DECLARE @matnr varchar, @werks varchar (4), @kriqi datetime, @meins varchar (10)
DECLARE @bomatnr varchar (varchar), @bomsehl (a), @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
--Calculate cost price of finished product
Declare mycursor Cursor FOR select Meins,aprice,jprice from TB_MATNR where convert (varchar), kriqi,120) =convert ( varchar (@out_datetime) 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 Co Nvert (varchar), kriqi,120 =convert (varchar), @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 ( kriqi,120) =convert (varchar), @out_datetime, 120)
Open Jgcursor
Fetch jgcursor into @price, @danw
Close Jgcursor
Deallocate jgcursor
If @price = 0
Begin
Update Tb_matnr set mstatus= ' suspect ' where Matnr = @out_matnr and plant = @out_werks and CONVERT (varchar (a), kriqi,120) =conver T (varchar, @out_datetime, 120)
Break
End
--If the price unit and 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 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
--View BOM finished units and finished sales units, if different add conversion relationship
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), kriqi,120 =convert (varchar), @out_datetime, 120)
Go



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.