USE [LOGO]

GO

/****** Object: UserDefinedFunction [dbo].[ITM_AMOUNT] Script Date: 4.12.2015 16:01:55 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERFUNCTION [dbo].[ITM_AMOUNT]

( @ITEMREF INT)

RETURNSFLOAT

AS

BEGIN

DECLARE @SONUC FLOAT

SELECT @SONUC=CAST(CAST(SUM(STL.AMOUNT *(2.5-STL.IOCODE)/ABS(2.5-STL.IOCODE)*(CASE STL.UINFO2 WHEN 0 THEN 1 ELSE STL.UINFO2 END)/(CASE STL.UINFO1 WHEN 0 THEN 1 ELSE STL.UINFO1 END))ASNUMERIC(28,6))ASFLOAT)

FROM LG_215_ITEMS ITM

INNERJOIN LG_215_UNITSETL UNL ON ITM.UNITSETREF=UNL.UNITSETREF AND UNL.MAINUNIT=1

INNERJOIN LG_215_ITMUNITA ITMA ON ITMA.ITEMREF=ITM.LOGICALREF AND ITMA.UNITLINEREF=UNL.LOGICALREF

LEFTOUTERJOIN LG_215_01_STLINE STL ON ITM.LOGICALREF=STL.STOCKREF AND STL.CANCELLED=0 AND STL.LPRODSTAT = 0

WHERE ITM.LOGICALREF=@ITEMREF

RETURN @SONUC

END