kittenchenUSE [asrs37db]
GO
/****** Object: StoredProcedure [dbo].[ASRS_SPPR02AA] Script Date: 04/19/2023 08:42:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ASRS_SPPR02AA] @iWk_no varchar(2),
@iUSer_id varchar(10),
@oRtn_Code int output,
@oMessage nvarchar(255) OutPut
AS
Declare @Count int,
@LoopCount int,
@LocCnt int,
@Opno int,
@LOC_STATUS VARCHAR(1),
@STO_CLASS VARCHAR(1),
@Warehouse varchar(2),
@MATNR VARCHAR(18),
@QC varchar(1),
@crane varchar(2),
@Loc_NO Varchar(7),
@LOC_SEQ int,
@Factory Varchar(4),
@KCW VARCHAR(4),
@Prod_Line varchar(8),
@Prod_Date varchar(8),
@Prod_Datetime varchar(14),
@Case_per_Pal int,
@LOT_NO varchar(10),
@DOC_type varchar(2),
@DOC_NO varchar(12),
@MATNR_TYPE VARCHAR(1),
@doc_item int,
@STG_QTY int,
@FIRST_IN_DATE varchar(14),
@Floc_no varchar(7),
@tloc_no varchar(7),
@REMARK NVARCHAR(50),
@PLC_NO VARCHAR(4),
@ErrorNo Int,
@ErrorMessage Varchar(255),
@Trace_Rec varchar(1000)
Begin
Set @Trace_REc = 'asrs_sppr02AA start !'
exec add_trace @Trace_REc ,'1'
Set @oRtn_Code = 2;
Select @Count=Count(*)
From asrs_PR02AA x
where x.WK_NO = @IWK_NO;
IF @COUNT = 0 BEGIN
Set @oMessage = '无指定库格搬移资料!';
Return;
END;
Set @LocCnt = 0;
Declare c_Loc Cursor For
SELECT b.crane, a.loc_no,a.loc_no_to,b.warehouse,b.STO_CLASS,
c.LOC_SEQ, c.MATNR, c.FACTORY, c.QC, c.LOT_NO,
c.PROD_LINE, c.DOC_TYPE, c.DOC_NO, c.STG_QTY, c.KCW,
c.FIRST_IN_DATE, c.PROD_DATETIME, c.PROD_DATE
FROM ASRS_PR02aa a
INNER JOIN ASRS_LOCFL b
on b.WAREHOUSE = a.WAREHOUSE
and b.LOC_NO = a.loc_no
and b.LOC_STATUS = '.'
and b.FORBID_FLAG = 'N'
inner join asrs_stgfl c
on c.WAREHOUSE = b.WAREHOUSE
and c.LOC_NO = b.LOC_NO
inner join asrs_locfl d
on d.WAREHOUSE = a.WAREHOUSE
and d.LOC_NO = a.LOC_NO_to
and d.LOC_STATUS = '0'
and d.FORBID_FLAG = 'N'
where a.WK_NO = @iWk_no
order by a.crane,a.loc_no desc;
Begin try
open c_loc;
fetch c_loc into @crane, @floc_no,@tloc_no,@warehouse,@STO_CLASS,
@LOC_SEQ,@MATNR, @FACTORY, @QC, @LOT_NO,
@PROD_LINE, @DOC_TYPE, @DOC_NO, @STG_QTY, @KCW,
@FIRST_IN_DATE, @PROD_DATETIME, @PROD_DATE;
while @@FETCH_STATUS = 0 begin
Set @OPNO = dbo.fn_opno_nextval();
Begin Transaction
Set @Trace_REc = 'asrs_sppr02a create 指定搬移库格['+@fLOC_NO+'] to ['+@tloc_no+']'
exec add_trace @Trace_REc ,'0'
UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;
UPDATE ASRS_LOCFL
SET LOC_STATUS = 'Q',
TRAN_TIME = dbo.f_get_sysdate()
WHERE WAREHOUSE = '00'
AND LOC_NO = @FLOC_NO;
UPDATE ASRS_LOCFL
SET LOC_STATUS = 'P',
TRAN_TIME = dbo.f_get_sysdate()
--STO_CLASS=@STO_CLASS ---20230418 陈德显 ,张锴说库格间搬完产品状态不对,这个我加的
WHERE WAREHOUSE = '00'
AND LOC_NO = @TLOC_NO;
SET @REMARK = '库格搬移['+@FLOC_NO+'-》'+@TLOC_NO+']';
Set @PLC_NO = SUBSTRING(CAST(@OPNO AS VARCHAR),5,4)
INSERT INTO dbo.ASRS_WOKFL
(OPNO, IDNO, STO_IN_OUT, PLC_NO,
WORK_STATUS, STNO, CRANE, LOC_NO,
FLOC_NO, TLOC_NO, TO_PT, IN_STNO, OUT_STNO,
CAR1_TO_PT, CAR2_TO_PT,
F_OR_N, E_OR_N, FULL_OR_PICK,
FACTORY, KCW, STO_CLASS,
Remark, LED_TEXT,
WK_NO, USER_ID, TRAN_TIME)
VALUES (@OPNO, '2A', 'L', @PLC_NO,
'A', '0000', @CRANE, @FLOC_NO,
@FLOC_NO, @TLOC_NO, NULL, NULL, NULL,
Null, NULL,
'N', 'N', 'F',
@FACTORY, @KCW,@STO_CLASS,-- @FACTORY, @KCW,@MATNR_TYPE,这是旧的，20230418 陈德显 ,张锴说库格间搬完产品状态不对,这个我加的
@Remark, @Remark,
@iWK_NO, @iUSER_ID, dbo.f_get_sysdate());
INSERT INTO dbo.ASRS_WOKDT
(OPNO, WareHouse, LOC_NO, LOC_SEQ,
DOC_TYPE, DOC_NO, DOC_ITEM,
PALLET_ID, FACTORY, PROD_LINE, LOT_NO,
MATNR, QC, PROCESS_RESN,
STG_QTY, ALOC_QTY, TRAN_QTY,
SFTID, FIRST_IN_DATE,
ORG_WH, ORG_LOC_NO, ORG_LOC_SEQ,
DOC_TYPE_IN, DOC_NO_IN, TRAN_TIME, PROD_DATE, PROD_DATETIME )
VALUES (@OPNO, @WareHouse, @FLOC_NO, 1,
Null, Null, Null,
Null, @FACTORY, @PROD_LINE, @LOT_NO,
@MATNR, @QC, '库格搬移',
@STG_QTY, 0, 0,
0, @FIRST_IN_DATE,
@Warehouse, @FLOC_NO, 1,
@DOC_TYPE, @DOC_NO, dbo.f_get_sysdate(), @PROD_DATE,@PROD_DATETIME);
if @@TranCount > 0
Commit Transaction;
Set @LocCnt = @LocCnt + 1;
fetch c_loc into @crane, @floc_no,@tloc_no,@warehouse,@STO_CLASS,
@LOC_SEQ,@MATNR, @FACTORY, @QC, @LOT_NO,
@PROD_LINE, @DOC_TYPE, @DOC_NO, @STG_QTY, @KCW,
@FIRST_IN_DATE, @PROD_DATETIME, @PROD_DATE;
end;
CLOSE c_loc;
IF @LocCnt > 0 BEGIN
Set @oRtn_Code = 1;
Set @oMessage='库格搬移命令生成成功!'+char(13)+char(10)
+'指定搬移库格数='+CAST(@COUNT AS VARCHAR)+char(13)+char(10)
+'搬移命令生成数='+CAST(@LOCCNT AS VARCHAR)
END
ELSE BEGIN
Set @oMessage='库格搬移命令生成失败!'+char(13)+char(10)
+'指定库格已异动!'
END;
exec add_trace @oMessage ,'0'
End Try
Begin Catch
Select @ErrorMessage = Error_Message(), @ErrorNo = Error_Number()
---Print 'xxxx'
if @@TranCount > 0
Rollback Transaction;
Set @oRtn_Code = 2
Set @oMessage = '库格搬移命令生成失败 ! Error_no ='+Cast(@ErrorNo as varchar)
+',ErrorMessage='+@ErrorMessage
Set @Trace_REc = 'ASRS_SPPR02A Location Move Process Fail !' + @oMessage
exec add_trace @Trace_REc ,'1'
End Catch
deallocate c_loc;
End;这个正在搬：
这个搬完，也显示产品了
