Skip to Content
0
Oct 01, 2012 at 08:56 AM

help with a query using Cursors

18 Views

hello to all!

i have created a function in SBO Transaction notification that is used to check

if the user types a location in a UDF

if the location is valid according to locations in specific table(UDT)

if the quantity that is typed for the location is available in the specific location

the code is the following

IF @object_type = N'15' AND (@transaction_type = N'A')

BEGIN

SELECT @LOC=U_Location FROM DLN1 WHERE DOCENTRY=@LIST_OF_COLS_VAL_TAB_DEL

SELECT @ITEMCODE=itemcode FROM DLN1 WHERE DOCENTRY=@LIST_OF_COLS_VAL_TAB_DEL

SELECT @QUANTITY=QUANTITY FROM DLN1 WHERE DOCENTRY=@LIST_OF_COLS_VAL_TAB_DEL

SELECT @WHS=WHSCODE FROM DLN1 WHERE DOCENTRY=@LIST_OF_COLS_VAL_TAB_DEL

SELECT @SERIES1=SERIES FROM ODLN WHERE DOCENTRY=@LIST_OF_COLS_VAL_TAB_DEL

IF @SERIES1=1282 and (select @LOC) IS NULL

BEGIN

SET @error=2

SET @error_message='Please Fill The Location Field'

END

If @SERIES1=1282 and (select @LOC) not in( select U_BarCode from [@CHM_WHS1] )

BEGIN

SET @error=2

SET @error_message='Enter The Valid Location'

END

if @SERIES1=1282 and (@QUANTITY)>(SELECT SUM(ISNULL(J0.InQty,0)-ISNULL(J0.OutQty,0)) AS Balance

FROM

(SELECT T0.Warehouse, T0.DocDate,T0.ItemCode,

CASE WHEN ISNULL(T0.BlockNum,'N/A')='' THEN 'N/A'

ELSE ISNULL(T0.BlockNum,'N/A') END AS 'BlockNum'

, ISNULL(T0.InQty,0) as InQty, ISNULL(T0.OutQty,0) AS OutQty FROM OINM T0 ) AS J0

LEFT JOIN

(SELECT T1.ITEMCODE AS ItemCode, ISNULL(SUM(ISNULL(T1.INQTY,0)-ISNULL(T1.OUTQTY,0)),0) AS Balance

FROM OINM T1 WHERE T1.WAREHOUSE=@WHS GROUP BY T1.ITEMCODE) AS J1 ON J0.ITEMCODE=J1.ITEMCODE

LEFT JOIN OITM J2 ON J0.ITEMCODE=J2.ITEMCODE

WHERE J0.Warehouse = @WHS AND (J2.ItemCode = @ITEMCODE)

and J0.BlockNum=@LOC

GROUP BY J0.ItemCode, J0.BlockNum, J1.Balance

HAVING SUM(ISNULL(J0.InQty,0)-ISNULL(J0.OutQty,0))<>0)

BEGIN

SET @error=2

SET @error_message='The Quantity is Lower Than The Available Stock or The Location is Wrong'

end

END

unfortunatelly this code does not work well...it actually does not any loop in lines of delivery notes. i tried to modify it by using cursor but i cannot

could you please help me how to change this function in order to make loops in lines by using Cursors?

i really thank you for your time