create PROCEDURE [dbo].[pro_Inbound]
@Barcode varchar(100),
@By varchar(50),
@Message int output --成功返回1,错误返回0
AS
DECLARE @_SkuCode varchar(100)
BEGIN
SET NOCOUNT ON;
set @Message=1
--查询条码的物料编号
select top 1 @_SkuCode=IDManageSkuCode from dbo.Task_IDManage where IDManageCode=@Barcode
--插入到库存表
begin transaction
insert into dbo.Task_LotLocSku(
[LotLocSkuWareHouse]
,[LotLocSkuLoc]
,[LotLocSkuSku]
,[LotLocSkuLot]
,[LotLocSkuIdCode]
,[LotLocSkuStorer]
,[LotLocSkuCQty]
,[LotLocSkuUom1]
,[LotLocSkuQty1]
,[LotLocSkuUom2]
,[LotLocSkuQty2]
,[LotLocSkuCheckQty1]
,[LotLocSkuCheckQty2]
,[LotLocSkuUser]
,[LotLocSkuDate]
,[LotLocSkuStatus]
,[LotLocSkuFreezeQty1]
,[LotLocSkuFreezeQty2] )
values('WH01','00',@_SkuCode,'',@Barcode,'HG',1,40,1,999999999,1,0,0,'Sys',getdate(),0,0,0)
if @@error<>0
begin
rollback transaction
set @Message=0
return
end
--插入到操作记录表
insert into dbo.Task_LotLocSkuOper(
[OperBillType]
,[OperBillStatus]
,[OperBillTypeCode]
,[OperStorer]
,[OperSku]
,[OperBillBarCode]
,[OperSourceStock]
,[OperSourceLoc]
,[OperStock]
,[OperLoc]
,[OperUomDefault]
,[OperCQty]
,[OperUom1]
,[OperQty1]
,[OperUom2]
,[OperQty2]
,[OperStatus]
,[OperStatus1]
,[OperType]
,[OperType1]
,[OperBy]
,[OperDate]
,[OperationId])
values(1,0,'01','HG',@_SkuCode,@Barcode,'WH01','00','WH01','00',0,1,40,1,999999999,1,0,0,0,0,@By,getdate(),1)
if @@error<>0
begin
rollback transaction
set @Message=0
return
end
commit transaction
END
@Barcode varchar(100),
@By varchar(50),
@Message int output --成功返回1,错误返回0
AS
DECLARE @_SkuCode varchar(100)
BEGIN
SET NOCOUNT ON;
set @Message=1
--查询条码的物料编号
select top 1 @_SkuCode=IDManageSkuCode from dbo.Task_IDManage where IDManageCode=@Barcode
--插入到库存表
begin transaction
insert into dbo.Task_LotLocSku(
[LotLocSkuWareHouse]
,[LotLocSkuLoc]
,[LotLocSkuSku]
,[LotLocSkuLot]
,[LotLocSkuIdCode]
,[LotLocSkuStorer]
,[LotLocSkuCQty]
,[LotLocSkuUom1]
,[LotLocSkuQty1]
,[LotLocSkuUom2]
,[LotLocSkuQty2]
,[LotLocSkuCheckQty1]
,[LotLocSkuCheckQty2]
,[LotLocSkuUser]
,[LotLocSkuDate]
,[LotLocSkuStatus]
,[LotLocSkuFreezeQty1]
,[LotLocSkuFreezeQty2] )
values('WH01','00',@_SkuCode,'',@Barcode,'HG',1,40,1,999999999,1,0,0,'Sys',getdate(),0,0,0)
if @@error<>0
begin
rollback transaction
set @Message=0
return
end
--插入到操作记录表
insert into dbo.Task_LotLocSkuOper(
[OperBillType]
,[OperBillStatus]
,[OperBillTypeCode]
,[OperStorer]
,[OperSku]
,[OperBillBarCode]
,[OperSourceStock]
,[OperSourceLoc]
,[OperStock]
,[OperLoc]
,[OperUomDefault]
,[OperCQty]
,[OperUom1]
,[OperQty1]
,[OperUom2]
,[OperQty2]
,[OperStatus]
,[OperStatus1]
,[OperType]
,[OperType1]
,[OperBy]
,[OperDate]
,[OperationId])
values(1,0,'01','HG',@_SkuCode,@Barcode,'WH01','00','WH01','00',0,1,40,1,999999999,1,0,0,0,0,@By,getdate(),1)
if @@error<>0
begin
rollback transaction
set @Message=0
return
end
commit transaction
END
解决方案 »
- 请教一个查询语句?
- 数据库全库冷备份的恢复问题
- 如此备份策略是否可行
- 存储过程中select into 语句 提示“ORA-01403:未找到数据”
- 专门配置一台透明网关服务器连接Sql Server 2K,跟Oracle数据库服务器分开,但是网关服务器重启监听器的时候老是提示“协议适配器错误”
- 高分请教:ora-12560:协议适配器错误
- Oracle单表的最大存储条数可以达到多少?
- 请教一个截取字符串的问题
- 请教关于oracle问题
- 如何得到记录中和最大的5条连续记录?
- oracle如何按周建分区表
- 用pl/sql 寫了一個生成html文件的procedure,有沒有辦法指定html的編碼?
create or replace PROCEDURE pro_Inbound
(Barcode varchar2(100),By varchar2(50),Message out number) --成功返回1,错误返回0
AS
DECLARE SkuCode varchar2(100);
BEGIN
--查询条码的物料编号
select IDManageSkuCode into skucode from dbo.Task_IDManage where IDManageCode=Barcode and rownum<=1;
--插入到库存表
insert into dbo.Task_LotLocSku(
LotLocSkuWareHouse
,LotLocSkuLoc
,LotLocSkuSku
,LotLocSkuLot
,LotLocSkuIdCode
,LotLocSkuStorer
,LotLocSkuCQty
,LotLocSkuUom1
,LotLocSkuQty1
,LotLocSkuUom2
,LotLocSkuQty2
,LotLocSkuCheckQty1
,LotLocSkuCheckQty2
,LotLocSkuUser
,LotLocSkuDate
,LotLocSkuStatus
,LotLocSkuFreezeQty1
,LotLocSkuFreezeQty2 )
values('WH01','00',SkuCode,'',Barcode,'HG',1,40,1,999999999,1,0,0,'Sys',sysdate,0,0,0);
--插入到操作记录表
insert into dbo.Task_LotLocSkuOper(
OperBillType
,OperBillStatus
,OperBillTypeCode
,OperStorer
,OperSku
,OperBillBarCode
,OperSourceStock
,OperSourceLoc
,OperStock
,OperLoc
,OperUomDefault
,OperCQty
,OperUom1
,OperQty1
,OperUom2
,OperQty2
,OperStatus
,OperStatus1
,OperType
,OperType1
,OperBy
,OperDate
,OperationId)
values(1,0,'01','HG',SkuCode,Barcode,'WH01','00','WH01','00',0,1,40,1,999999999,1,0,0,0,0,By,sysdate,1);
commit;
Message:=1;
exception when others then
rollback;
Message:=0;
END;
create PROCEDURE pro_Inbound(v_Barcode varchar2,
v_By varchar2,
v_Message out number--成功返回1,错误返回0)
AS
DECLARE v_SkuCode varchar2(100);
BEGIN
v_Message:=1
--查询条码的物料编号select IDManageSkuCode into v_SkuCode from Task_IDManage where IDManageCode=v_Barcode and rownum=1;
--插入到库存表
begin
savepoint sp1;
insert into Task_LotLocSku(
LotLocSkuWareHouse
,LotLocSkuLoc
,LotLocSkuSku
,LotLocSkuLot
,LotLocSkuIdCode
,LotLocSkuStorer
,LotLocSkuCQty
,LotLocSkuUom1
,LotLocSkuQty1
,LotLocSkuUom2
,LotLocSkuQty2
,LotLocSkuCheckQty1
,LotLocSkuCheckQty2
,LotLocSkuUser
,LotLocSkuDate
,LotLocSkuStatus
,LotLocSkuFreezeQty1
,LotLocSkuFreezeQty2)
values('WH01','00',v_SkuCode,'',v_Barcode,'HG',1,40,1,999999999,1,0,0,'Sys',sysdate,0,0,0)
exception
when others then
rollback to sp1;
v_Message:=0 ;
end ;
--插入到操作记录表
begin
insert into Task_LotLocSkuOper(
OperBillType
,OperBillStatus
,OperBillTypeCode
,OperStorer
,OperSku
,OperBillBarCode
,OperSourceStock
,OperSourceLoc
,OperStock
,OperLoc
,OperUomDefault
,OperCQty
,OperUom1
,OperQty1
,OperUom2
,OperQty2
,OperStatus
,OperStatus1
,OperType
,OperType1
,OperBy
,OperDate
,OperationId)
values(1,0,'01','HG',v_SkuCode,v_Barcode,'WH01','00','WH01','00',0,1,40,1,999999999,1,0,0,0,0,v_By,sysdate,1)
exception
when others then
rollback to sp1;
v_Message:=0 ;
end ;
END