错误&;光标X不存在&;运行存储过程时
本文关键字：存储过程 运行 光标 错误 不存在 | 更新日期: 2023-09-27 18:15:44
当运行SQL存储过程时，我得到这个错误:
<>之前名称为'V_OPEN_INV_REC'的游标不存在之前请问，有人能帮我吗?ALTER PROCEDURE [dbo].[HRI_FIFO_WITH_ASSIGNED_TO_INV]
@USERID INT,
@CUSTOMERID INT,
@ZONEID INT,
@SUBCUSTOMER VARCHAR(20),
@OK_TO_PROCEED INT
AS
BEGIN
SET @OK_TO_PROCEED = 1;
DECLARE @V_CURRENT_PART VARCHAR (50)
SET @V_CURRENT_PART = ''
DECLARE @V_CURRENT_LOT VARCHAR (50)
SET @V_CURRENT_LOT = ''
DECLARE @V_CURRENT_SERIAL VARCHAR (50)
SET @V_CURRENT_SERIAL = ''
DECLARE @V_WITH_CF_QTY INT
DECLARE @V_INVENTORY_METHOD VARCHAR (50)
DECLARE @V_UNSATISFIED_REMOVAL_QTY INT
DECLARE @V_QTY_REM_ON_THIS_LAYER INT
DECLARE @V_QTY_UNASGN_ON_THIS_LAYER INT
DECLARE @V_WATCHES_PROCESSED INT
DECLARE @IMT_RULE VARCHAR (50)
DECLARE @V_CURRENT_DATE DATETIME
SET @V_CURRENT_DATE=GETDATE()
DECLARE @V_CURRENT_END_USE VARCHAR (50)
DECLARE @V_CURRENT_QUALITY VARCHAR (50)
DECLARE @V_CURRENT_SIZE INT
DECLARE @V_CURRENT_GENDER VARCHAR (50)
DECLARE @END_USE_RULE VARCHAR (50)
DECLARE @V_CURRENT_PART_ORIG VARCHAR (50)
DECLARE @V_CURRENT_WD_TYPE VARCHAR (50)
DECLARE @BYPASS_WATCH_AUDIT VARCHAR (50)
SET @IMT_RULE = DBO.EZFTZ_HRI_IMT_RULE('Withdraw from Previous Layers Only',@ZONEID,@CUSTOMERID)
SET @END_USE_RULE = DBO.EZFTZ_HRI_IMT_RULE('Handle End-Use Processing',@ZONEID,@CUSTOMERID)
SET @BYPASS_WATCH_AUDIT = DBO.EZFTZ_HRI_IMT_RULE('Bypass Watch Breakout Audit', @ZONEID,@CUSTOMERID)
SET @V_WATCHES_PROCESSED = DBO.EZFTZ_Fn_HAS_WATCH_PARTS(@ZONEID, @CUSTOMERID)
SET @V_INVENTORY_METHOD = (SELECT ISNULL(InventoryMethod,'FIFO') FROM EZFTZ_tbl_CustomerInfo WHERE CustomerID = @CUSTOMERID AND ZoneNumber=@ZONEID)
DECLARE @PRODUCT_ID VARCHAR(50)
DECLARE @LOT VARCHAR(50)
DECLARE @WITHDRAWAL_ID INT
DECLARE @UNASSIGNED_QTY INT
DECLARE @SERIAL_NUMBER VARCHAR(50)
DECLARE @ENFORCE_SERIAL_MATCH INT
DECLARE @WITH_CF_QTY INT
DECLARE @WITHDRAWAL_DT DATETIME
DECLARE @END_USE VARCHAR(10)
DECLARE @QUALITY VARCHAR(10)
DECLARE @SIZE_CODE INT
DECLARE @GENDER_CODE VARCHAR(10)
DECLARE @FED_SPI VARCHAR(20)
DECLARE @WITHDRAWAL_TYPE VARCHAR(20)
DECLARE @ORIG_PRODUCT VARCHAR(50)
DECLARE @FTZ_WITH_ID INT
DECLARE @ENTRY_NO INT
DECLARE @LAYER_ID INT
DECLARE @UNASGN_QTY INT
DECLARE @RMN_QTY_ON_LAYER INT
DECLARE GET_REMOVAL_CONFIGS_WITH_INV CURSOR LOCAL FOR
SELECT RC.PROD_CD PRODUCT_ID,
RC.LOT_NUMBER LOT,
RC.FTZ_WITH_CF_ID WITHDRAWAL_ID,
RC.WITH_CF_UNASGN_QTY UNASSIGNED_QTY,
RC.SERIAL_NUMBER,
RC.ENFORCE_SERIAL_MATCH,
RC.WITH_CF_QTY,
R.WITHDRAWAL_DT,
RC.END_USE,
RC.QUALITY,
RC.SIZE_CODE,
RC.GENDER_CODE,
RC.FED_SPI,
R.WITHDRAWAL_TYPE,
R.PROD_CD ORIG_PRODUCT,
R.FTZ_WITH_ID,
R.ENTRY_NO FROM EZFTZ_tbl_Wdt_Removal_Config RC
INNER JOIN EZFTZ_tbl_Wdt_Removal R ON RC.ZONE_ID = R.ZONE_ID AND RC.CUSTOMER_ID = R.CUSTOMER_ID AND RC.FTZ_WITH_ID = R.FTZ_WITH_ID
WHERE ISNULL(RC.WITH_CF_UNASGN_QTY,0) > 0
AND ( RC.WITH_CF_STATUS = 'NEW' OR RC.WITH_CF_STATUS = 'UNFIL' )
AND RC.CUSTOMER_ID=@CUSTOMERID AND RC.ZONE_ID=@ZONEID
ORDER BY RC.PROD_CD , RC.WITH_CF_BOM_EFF_DT, R.WITHDRAWAL_DT , RC.FTZ_WITH_CF_ID
OPEN GET_REMOVAL_CONFIGS_WITH_INV
FETCH NEXT FROM GET_REMOVAL_CONFIGS_WITH_INV INTO @PRODUCT_ID,@LOT, @WITHDRAWAL_ID, @UNASSIGNED_QTY, @SERIAL_NUMBER, @ENFORCE_SERIAL_MATCH, @WITH_CF_QTY, @WITHDRAWAL_DT,
@END_USE,@QUALITY, @SIZE_CODE, @GENDER_CODE, @FED_SPI, @WITHDRAWAL_TYPE, @ORIG_PRODUCT, @FTZ_WITH_ID, @ENTRY_NO
---OPEN V_OPEN_INV_REC
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @V_WITH_CF_QTY = @WITH_CF_QTY
SET @V_UNSATISFIED_REMOVAL_QTY = @UNASSIGNED_QTY
IF @V_CURRENT_PART != @PRODUCT_ID OR @V_CURRENT_PART IS NULL OR @V_CURRENT_LOT != @LOT OR @V_CURRENT_LOT IS NULL
OR @V_CURRENT_SERIAL != @SERIAL_NUMBER OR @V_CURRENT_SERIAL IS NULL OR @V_CURRENT_DATE != @WITHDRAWAL_DT OR @V_CURRENT_DATE IS NULL
OR @V_CURRENT_END_USE IS NULL OR @V_CURRENT_END_USE != @END_USE OR @V_CURRENT_QUALITY IS NULL OR @V_CURRENT_QUALITY != @QUALITY
OR @V_CURRENT_SIZE IS NULL OR @V_CURRENT_SIZE != @SIZE_CODE OR @V_CURRENT_GENDER IS NULL OR @V_CURRENT_GENDER != @GENDER_CODE
OR @V_CURRENT_PART_ORIG IS NULL OR @V_CURRENT_PART_ORIG != @ORIG_PRODUCT OR @V_CURRENT_WD_TYPE IS NULL OR @V_CURRENT_WD_TYPE != @WITHDRAWAL_TYPE
BEGIN
SET @V_CURRENT_PART = @PRODUCT_ID
SET @V_CURRENT_LOT = @LOT
SET @V_CURRENT_SERIAL = @SERIAL_NUMBER
SET @V_CURRENT_DATE = @WITHDRAWAL_DT
SET @V_CURRENT_END_USE = @END_USE
SET @V_CURRENT_QUALITY = @QUALITY
SET @V_CURRENT_SIZE = @SIZE_CODE
SET @V_CURRENT_GENDER = @GENDER_CODE
SET @V_CURRENT_PART_ORIG= @ORIG_PRODUCT
SET @V_CURRENT_WD_TYPE = @WITHDRAWAL_TYPE
DECLARE V_OPEN_INV_REC CURSOR LOCAL FOR
SELECT LAYER_ID,QTY_REMAINING RMN_QTY_ON_LAYER,UNASGN_QTY FROM EZFTZ_tbl_Inventory_Layer
WHERE COMPONENT_PART_NO = @V_CURRENT_PART
AND ZONE_ID= @ZONEID
AND CUSTOMERID = @CUSTOMERID
AND SERIAL_NUMBER = @V_CURRENT_SERIAL
AND QTY_REMAINING > 0
AND CF214_RELEASED_BY_CUSTOMS != 0
AND ( CONVERT(DATE,RECEIPT_DATE,101) <= CONVERT(DATE,@V_CURRENT_DATE,101) OR @IMT_RULE = 'NO' )
AND ( @END_USE_RULE = 'NO' OR ( END_USE = @V_CURRENT_END_USE AND QUALITY = @V_CURRENT_QUALITY AND SIZE_CODE = @V_CURRENT_SIZE AND GENDER_CODE = @V_CURRENT_GENDER ) )
AND ( ZONE_STATUS != 'Z' OR ( @V_CURRENT_WD_TYPE != 'CF7501' AND @V_CURRENT_PART_ORIG = @V_CURRENT_PART ) )
ORDER BY RECEIPT_DATE ASC, LAYER_ID ASC
OPEN V_OPEN_INV_REC
FETCH NEXT FROM V_OPEN_INV_REC INTO @LAYER_ID,@RMN_QTY_ON_LAYER,@UNASGN_QTY
IF NOT (@@FETCH_STATUS <> -1)
BEGIN
SET @V_QTY_REM_ON_THIS_LAYER = 0
END
ELSE
BEGIN
SET @V_QTY_REM_ON_THIS_LAYER = @RMN_QTY_ON_LAYER
END
SET @V_QTY_REM_ON_THIS_LAYER = DBO.EZFTZ_Fn_GET_QTY_REM_FOR_LAYER( 1, @V_INVENTORY_METHOD, @V_CURRENT_PART, @ZONEID, @CUSTOMERID, @SUBCUSTOMER, @V_CURRENT_SERIAL, @V_CURRENT_DATE, @IMT_RULE, @V_CURRENT_END_USE, @V_CURRENT_QUALITY, @V_CURRENT_SIZE, @V_CURRENT_GENDER, @END_USE_RULE, @V_CURRENT_LOT, @V_CURRENT_PART_ORIG, @V_CURRENT_WD_TYPE)
END
WHILE ISNULL(@V_QTY_REM_ON_THIS_LAYER,0) < @V_UNSATISFIED_REMOVAL_QTY AND ISNULL(@V_QTY_REM_ON_THIS_LAYER,0) > 0 AND @OK_TO_PROCEED = 1
BEGIN --(
IF ABS(ISNULL(@V_UNSATISFIED_REMOVAL_QTY,0) - ISNULL(@V_QTY_REM_ON_THIS_LAYER,0))< ISNULL(@V_WITH_CF_QTY,0) * 0.0000001 OR ABS(ISNULL(@V_UNSATISFIED_REMOVAL_QTY,0) - ISNULL(@V_QTY_REM_ON_THIS_LAYER,0))< 0.0000000001
BEGIN
SET @V_UNSATISFIED_REMOVAL_QTY = @V_QTY_REM_ON_THIS_LAYER
END
IF ISNULL(@V_QTY_REM_ON_THIS_LAYER,0) < ISNULL(@V_UNSATISFIED_REMOVAL_QTY,0)
BEGIN
EXEC EZFTZ_Wdt_HRI_FIFO_CREATE_RL @WITHDRAWAL_ID,@LAYER_ID,@FTZ_WITH_ID,@V_QTY_REM_ON_THIS_LAYER,@PRODUCT_ID,@USERID,@CUSTOMERID,@SUBCUSTOMER,@V_CURRENT_END_USE,@V_CURRENT_QUALITY,@V_CURRENT_GENDER,@V_CURRENT_SIZE,@FED_SPI,@ZONEID,@OK_TO_PROCEED , @ENTRY_NO , @WITHDRAWAL_DT
IF @V_WATCHES_PROCESSED = 1
BEGIN
EXEC EZFTZ_Wdt_HRI_FIFO_CREATE_WATCH_RL @USERID , @WITHDRAWAL_ID, @LAYER_ID, @BYPASS_WATCH_AUDIT,@ZONEID,@CUSTOMERID,@SUBCUSTOMER ,@OK_TO_PROCEED
END
SET @LAYER_ID=ISNULL(@LAYER_ID,0)
UPDATE EZFTZ_tbl_Inventory_Layer SET QTY_REMAINING = 0,UNASGN_QTY = 0,LAST_WD_DATE = DBO.EZFTZ_fn_Com_GET_LAST_DATE(LAST_WD_DATE, @WITHDRAWAL_DT)
WHERE LAYER_ID = @LAYER_ID AND ZONE_ID=@ZONEID AND CUSTOMERID=@CUSTOMERID
SET @V_UNSATISFIED_REMOVAL_QTY = ISNULL(@V_UNSATISFIED_REMOVAL_QTY,0) - @V_QTY_REM_ON_THIS_LAYER
UPDATE EZFTZ_tbl_Wdt_Removal_Config SET WITH_CF_UNASGN_QTY = @V_UNSATISFIED_REMOVAL_QTY,MFG_UNASGN_QTY = @V_UNSATISFIED_REMOVAL_QTY
WHERE FTZ_WITH_CF_ID = @WITHDRAWAL_ID AND ZONE_ID=@ZONEID AND CUSTOMER_ID=@CUSTOMERID
SET @V_QTY_REM_ON_THIS_LAYER = DBO.EZFTZ_Fn_GET_QTY_REM_FOR_LAYER( 0, @V_INVENTORY_METHOD, @V_CURRENT_PART, @ZONEID, @CUSTOMERID, @SUBCUSTOMER, @V_CURRENT_SERIAL, @V_CURRENT_DATE, @IMT_RULE, @V_CURRENT_END_USE, @V_CURRENT_QUALITY, @V_CURRENT_SIZE, @V_CURRENT_GENDER, @END_USE_RULE, @V_CURRENT_LOT, @V_CURRENT_PART_ORIG, @V_CURRENT_WD_TYPE)
FETCH NEXT FROM V_OPEN_INV_REC INTO @LAYER_ID,@RMN_QTY_ON_LAYER,@UNASGN_QTY
IF (@@FETCH_STATUS = -1)
BEGIN
SET @V_QTY_REM_ON_THIS_LAYER = 0
END
ELSE
BEGIN
SET @V_QTY_REM_ON_THIS_LAYER = @RMN_QTY_ON_LAYER
END
END
END
IF @OK_TO_PROCEED = 1
BEGIN
IF ABS(@V_UNSATISFIED_REMOVAL_QTY - @V_QTY_REM_ON_THIS_LAYER)< @V_WITH_CF_QTY * 0.0000001 OR ABS(@V_UNSATISFIED_REMOVAL_QTY - @V_QTY_REM_ON_THIS_LAYER)< .0000000001
BEGIN
SET @V_UNSATISFIED_REMOVAL_QTY = @V_QTY_REM_ON_THIS_LAYER
END
IF ( ISNULL(@V_QTY_REM_ON_THIS_LAYER,0) = 0 )
BEGIN
SET @V_UNSATISFIED_REMOVAL_QTY = 0
CLOSE V_OPEN_INV_REC
DEALLOCATE V_OPEN_INV_REC
END
ELSE
BEGIN
EXEC EZFTZ_Wdt_HRI_FIFO_CREATE_RL @WITHDRAWAL_ID , @LAYER_ID , @FTZ_WITH_ID , @V_UNSATISFIED_REMOVAL_QTY , @PRODUCT_ID , @USERID , @CUSTOMERID , @SUBCUSTOMER , @V_CURRENT_END_USE , @V_CURRENT_QUALITY , @V_CURRENT_GENDER , @V_CURRENT_SIZE , @FED_SPI ,@ZONEID, @OK_TO_PROCEED , @ENTRY_NO , @WITHDRAWAL_DT
IF @V_WATCHES_PROCESSED = 1
BEGIN
EXEC EZFTZ_WDT_HRI_FIFO_CREATE_WATCH_RL @USERID , @WITHDRAWAL_ID , @LAYER_ID , @BYPASS_WATCH_AUDIT ,@ZONEID,@CUSTOMERID,@SUBCUSTOMER ,@OK_TO_PROCEED
END
SET @V_QTY_REM_ON_THIS_LAYER = @V_QTY_REM_ON_THIS_LAYER - @V_UNSATISFIED_REMOVAL_QTY
SET @V_QTY_UNASGN_ON_THIS_LAYER = @UNASGN_QTY
IF ISNULL(@V_QTY_UNASGN_ON_THIS_LAYER,0) > ISNULL(@V_QTY_REM_ON_THIS_LAYER,0)
BEGIN
SET @V_QTY_UNASGN_ON_THIS_LAYER = @V_QTY_REM_ON_THIS_LAYER
END
UPDATE EZFTZ_tbl_Inventory_Layer
SET QTY_REMAINING = DBO.EZFTZ_Fn_Com_HRI_ROUND(@V_QTY_REM_ON_THIS_LAYER, 11),
UNASGN_QTY = DBO.EZFTZ_Fn_Com_HRI_ROUND(@V_QTY_UNASGN_ON_THIS_LAYER, 11),
LAST_WD_DATE = DBO.EZFTZ_fn_Com_GET_LAST_DATE(LAST_WD_DATE, @WITHDRAWAL_DT)
WHERE LAYER_ID = @LAYER_ID AND ZONE_ID=@ZONEID AND CUSTOMERID=@CUSTOMERID
UPDATE EZFTZ_tbl_Wdt_Removal_Config SET WITH_CF_UNASGN_QTY = 0, MFG_UNASGN_QTY = 0
WHERE FTZ_WITH_CF_ID = @WITHDRAWAL_ID AND ZONE_ID=@ZONEID AND CUSTOMER_ID=@CUSTOMERID
SET @V_UNSATISFIED_REMOVAL_QTY = 0
IF ( ISNULL(@V_QTY_REM_ON_THIS_LAYER,0) = 0 )
BEGIN
SET @V_QTY_REM_ON_THIS_LAYER = DBO.EZFTZ_Fn_GET_QTY_REM_FOR_LAYER( 0, @V_INVENTORY_METHOD, @V_CURRENT_PART, @ZONEID, @CUSTOMERID, @SUBCUSTOMER, @V_CURRENT_SERIAL, @V_CURRENT_DATE, @IMT_RULE, @V_CURRENT_END_USE, @V_CURRENT_QUALITY, @V_CURRENT_SIZE, @V_CURRENT_GENDER, @END_USE_RULE, @V_CURRENT_LOT, @V_CURRENT_PART_ORIG, @V_CURRENT_WD_TYPE)
END
IF ( @V_QTY_REM_ON_THIS_LAYER = 0 )
BEGIN
FETCH NEXT FROM V_OPEN_INV_REC INTO @LAYER_ID,@RMN_QTY_ON_LAYER,@UNASGN_QTY
IF ( (@@FETCH_STATUS = -1) )
BEGIN
SET @V_QTY_REM_ON_THIS_LAYER = 0
END
ELSE
BEGIN
SET @V_QTY_REM_ON_THIS_LAYER = @RMN_QTY_ON_LAYER
END
END
ELSE
BEGIN
CLOSE V_OPEN_INV_REC
DEALLOCATE V_OPEN_INV_REC
END
END
END
FETCH NEXT FROM GET_REMOVAL_CONFIGS_WITH_INV INTO @PRODUCT_ID,@LOT, @WITHDRAWAL_ID, @UNASSIGNED_QTY, @SERIAL_NUMBER, @ENFORCE_SERIAL_MATCH, @WITH_CF_QTY, @WITHDRAWAL_DT,
@END_USE,@QUALITY, @SIZE_CODE, @GENDER_CODE, @FED_SPI, @WITHDRAWAL_TYPE, @ORIG_PRODUCT, @FTZ_WITH_ID, @ENTRY_NO
END
CLOSE GET_REMOVAL_CONFIGS_WITH_INV
DEALLOCATE GET_REMOVAL_CONFIGS_WITH_INV
SET @OK_TO_PROCEED = 1
END
我可能是错的，但似乎大约2/3的方式，你关闭光标，然后尝试再次使用它而不重新打开它。抱歉，如果我错过了什么-那里有很多代码!
CLOSE V_OPEN_INV_REC
DEALLOCATE V_OPEN_INV_REC
END
ELSE
BEGIN
EXEC EZFTZ_Wdt_HRI_FIFO_CREATE_RL @WITHDRAWAL_ID , @LAYER_ID , @FTZ_WITH_ID , @V_UNSATISFIED_REMOVAL_QTY , @PRODUCT_ID , @USERID , @CUSTOMERID , @SUBCUSTOMER , @V_CURRENT_END_USE , @V_CURRENT_QUALITY , @V_CURRENT_GENDER , @V_CURRENT_SIZE , @FED_SPI ,@ZONEID, @OK_TO_PROCEED , @ENTRY_NO , @WITHDRAWAL_DT
IF @V_WATCHES_PROCESSED = 1
BEGIN
表示您已经关闭了它，然后稍后再尝试使用它。
FETCH NEXT FROM V_OPEN_INV_REC INTO @LAYER_ID,@RMN_QTY_ON_LAYER,@UNASGN_QTY