CREATE OR REPLACE PROCEDURE METERIAL.MATE_SCRAP_ACTION(
MESAGE OUT TCURSOR.curvar_type,
VAR_FEEDNUM IN VARCHAR2,
VAR_ITEMNAME IN VARCHAR2,
VAR_FOLDADDRESS IN VARCHAR2,
VAR_TOLOCATION IN VARCHAR2,
VAR_SCRAPCOUNT IN INTEGER,
VAR_UNIT IN VARCHAR2,
VAR_ITEMMOMENT IN VARCHAR2,
VAR_USERID IN VARCHAR2,
VAR_USERNAME IN VARCHAR2,
VAR_SCRAPREASON IN VARCHAR2,
VAR_CREDENCENUM IN VARCHAR2,
VAR_REAMRK IN VARCHAR2,
VAR_STATE IN VARCHAR2,
VAR_CLEARER IN VARCHAR2,
VAR_ACTION IN VARCHAR2
)
AS
VAE_REDATA INTEGER;
VAR_COUNT INTEGER;
VAR_EXIT INTEGER;
VAR_ID1 INTEGER;
VAR_SQL VARCHAR2(500);
ERROR_MESAGE VARCHAR2(100);
VAR_TABLE VARCHAR2(50);
VAR_OPACTION VARCHAR2(50);
VAR_MOVECOUNT MATE_LOCATION_INFO.MOVECOUNT%TYPE;
VAR_TEMPLOCKCOUNT MATE_SUB_LOCATION_INFO.LOCKCOUNT%TYPE;
VAR_TEMPSTATE MATE_LOCATION_SCRAP_INFO.STATE%TYPE;
VAR_TEMPSCRAPCOUNT MATE_LOCATION_SCRAP_INFO.SCRAPCOUNT%TYPE;
VAR_MATERIAL_DESC MATE_LOCATION_INFO.DEPICT%TYPE;
VAR_TEMPCREDENCENUM MATE_LOCATION_SCRAP_INFO.CREDENCENUM%TYPE;
VAR_TEMPFEEDNUM MATE_LOCATION_SCRAP_INFO.FEEDNUM%TYPE;
VAR_QUALITYSTATE VARCHAR2(50);
ERAISE EXCEPTION;
BEGIN
/*
state 状态 ‘1’ : 代表建立报废申请
‘2’ : 代表报废申请退回
‘0’ : 代表报废申请核准
*/
/* 申请报废 */
IF VAR_ACTION='0010' THEN
VAR_TABLE:='MATE_LOCATION_SCRAP_INFO';
VAR_OPACTION:='报废处理';
VAE_REDATA:=DATACHECK(VAR_FEEDNUM,VAR_UNIT,VAR_ITEMNAME,VAR_TOLOCATION,VAR_ITEMMOMENT,VAR_TABLE,VAR_USERNAME,VAR_OPACTION);
IF VAE_REDATA<=0 THEN
ERROR_MESAGE:=VAR_FEEDNUM||' 相关数据核实有误';
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERID,VAR_OPACTION,VAR_TABLE);
goto re_point;
END IF;
select COUNT(*) INTO VAR_COUNT from MATE_LOCATION_SCRAP_INFO WHERE CREDENCENUM=VAR_CREDENCENUM;
IF VAR_COUNT>0 THEN
ERROR_MESAGE:=VAR_FEEDNUM||' 操作失败,凭证号已存在,请重新生成';
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERNAME,VAR_OPACTION,VAR_TABLE);
goto re_point;
END IF;
SELECT COUNT(*) INTO VAR_COUNT FROM MATE_LOCATION_INFO
WHERE FEEDNUM=VAR_FEEDNUM
AND ITEMNAME=VAR_ITEMNAME
AND FOLDADDRESS=VAR_FOLDADDRESS
--AND (instr(TOLOCATION,'报废品')>0 or TOLOCATION='报废品库')
AND TOLOCATION=VAR_TOLOCATION
AND STATE='0';
begin
select sum(to_number(lockcount)) into VAR_TEMPLOCKCOUNT from MATE_SUB_LOCATION_INFO
WHERE FEEDNUM=VAR_FEEDNUM
AND ITEMNAME=VAR_ITEMNAME
AND FOLDADDRESS=VAR_FOLDADDRESS
AND TOLOCATION=VAR_TOLOCATION
group by FEEDNUM,ITEMNAME,FOLDADDRESS,TOLOCATION ;
exception
when others then
VAR_TEMPLOCKCOUNT:='0';
end;
IF VAR_COUNT<=0 THEN
ERROR_MESAGE:=VAR_FEEDNUM||' 操作失败,没有对应库存,';
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERNAME,VAR_OPACTION,VAR_TABLE);
goto re_point;
END IF;
ERROR_MESAGE:=VAR_FEEDNUM||' 操作失败,没有得到报废库存';
SELECT MOVECOUNT,DEPICT INTO VAR_MOVECOUNT ,VAR_MATERIAL_DESC FROM MATE_LOCATION_INFO
WHERE FEEDNUM=VAR_FEEDNUM
AND ITEMNAME=VAR_ITEMNAME
AND FOLDADDRESS=VAR_FOLDADDRESS
--AND (instr(TOLOCATION,'报废品')>0 or TOLOCATION='报废品库')
AND TOLOCATION=VAR_TOLOCATION
AND STATE='0';
VAR_EXIT:=TO_NUMBER(VAR_MOVECOUNT)-VAR_SCRAPCOUNT-TO_NUMBER(VAR_TEMPLOCKCOUNT);
IF VAR_EXIT<0 THEN
ERROR_MESAGE:=VAR_FEEDNUM||' 操作失败,报废数量不能超过库存数量,';
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERNAME,VAR_OPACTION,VAR_TABLE);
goto re_point;
END IF;
select count(*) into VAR_COUNT from mate_sub_location_info
where FEEDNUM=VAR_FEEDNUM
AND ITEMNAME=VAR_ITEMNAME
AND FOLDADDRESS=VAR_FOLDADDRESS
AND CREDENCENUM=VAR_CREDENCENUM;
SELECT MATE_LOCATION_SCRAP_INFO_S.nextval INTO VAR_ID1 FROM DUAL;
IF VAR_COUNT>0 THEN
ERROR_MESAGE:=VAR_FEEDNUM||' 更新表mate_sub_location_info失败';
UPDATE mate_sub_location_info SET MOVECOUNT=TO_NUMBER(MOVECOUNT)+VAR_SCRAPCOUNT,LOCKCOUNT=TO_NUMBER(LOCKCOUNT)+VAR_SCRAPCOUNT
where FEEDNUM=VAR_FEEDNUM
AND ITEMNAME=VAR_ITEMNAME
AND FOLDADDRESS=VAR_FOLDADDRESS
AND CREDENCENUM=VAR_CREDENCENUM;
ELSE
IF INSTR(VAR_TOLOCATION,'报废品')>0 THEN
VAR_QUALITYSTATE:='报废品';
ELSIF INSTR(VAR_TOLOCATION,'不良品')>0 THEN
VAR_QUALITYSTATE:='不良品';
ELSE
VAR_QUALITYSTATE:='良品';
END IF;
ERROR_MESAGE:=VAR_FEEDNUM||' 插入表mate_sub_location_info失败';
INSERT INTO mate_sub_location_info
SELECT VAR_ID1,LTRIM(RTRIM(VAR_FEEDNUM)),LTRIM(RTRIM(VAR_ITEMNAME)),LTRIM(RTRIM(VAR_FOLDADDRESS)),'',LTRIM(RTRIM(VAR_TOLOCATION)),
VAR_SCRAPCOUNT,VAR_SCRAPCOUNT,VAR_UNIT,VAR_USERID,VAR_USERNAME,
sysdate,VAR_CREDENCENUM,
VAR_QUALITYSTATE,'','',''
FROM DUAL;
END IF;
ERROR_MESAGE:=VAR_FEEDNUM||' 插入表MATE_LOCATION_SCRAP_INFO失败';
INSERT INTO MATE_LOCATION_SCRAP_INFO
SELECT VAR_ID1,
LTRIM(RTRIM(VAR_FEEDNUM)),LTRIM(RTRIM(VAR_ITEMNAME)),LTRIM(RTRIM(VAR_FOLDADDRESS)),LTRIM(RTRIM(VAR_TOLOCATION)),
VAR_SCRAPCOUNT,VAR_UNIT,VAR_ITEMMOMENT,VAR_USERID,
VAR_USERNAME,VAR_SCRAPREASON,SYSDATE,VAR_CREDENCENUM,
VAR_REAMRK,VAR_STATE,VAR_MATERIAL_DESC,VAR_CLEARER FROM DUAL;
ERROR_MESAGE:=VAR_FEEDNUM||' 操作,申请报废成功';
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERNAME,VAR_OPACTION,VAR_TABLE);
END IF;
/* 报废处理 核准*/
IF VAR_ACTION='0020' THEN
VAR_TABLE:='MATE_LOCATION_SCRAP_INFO';
VAR_OPACTION:='报废处理核准';
ERROR_MESAGE:=VAR_CREDENCENUM||' 操作失败,该凭证号已有申请';
select STATE,FEEDNUM INTO VAR_TEMPSTATE,VAR_TEMPFEEDNUM from MATE_LOCATION_SCRAP_INFO where CREDENCENUM=VAR_CREDENCENUM;
IF VAR_TEMPSTATE='0' THEN
ERROR_MESAGE:=VAR_TEMPFEEDNUM||' 操作失败,已经退回或核准';
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERNAME,VAR_OPACTION,VAR_TABLE);
goto re_point;
else
select SCRAPCOUNT INTO VAR_TEMPSCRAPCOUNT from MATE_LOCATION_SCRAP_INFO where CREDENCENUM=VAR_CREDENCENUM and rownum<=1;
END IF;
select count(*) into VAR_COUNT from mate_location_info a
where STATE='0'
and exists(
select * from MATE_LOCATION_SCRAP_INFO b
where b.CREDENCENUM=VAR_CREDENCENUM
and a.FEEDNUM=b.FEEDNUM
and a.ITEMNAME=b.ITEMNAME
and a.FOLDADDRESS=b.FOLDADDRESS
and a.TOLOCATION=B.TOLOCATION
);
IF VAR_COUNT<=0 THEN
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 操作失败,没有库存无法核准';
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERNAME,VAR_OPACTION,VAR_TABLE);
goto re_point;
ELSE
select MOVECOUNT into VAR_MOVECOUNT from mate_location_info a
where STATE='0'
and exists(
select * from MATE_LOCATION_SCRAP_INFO b
where b.CREDENCENUM=VAR_CREDENCENUM
and a.FEEDNUM=b.FEEDNUM
and a.ITEMNAME=b.ITEMNAME
and a.FOLDADDRESS=b.FOLDADDRESS
and a.TOLOCATION=B.TOLOCATION
)AND ROWNUM<=1;
END IF;
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 操作失败,得不到冻结数量';
select lockcount into VAR_TEMPLOCKCOUNT from MATE_SUB_LOCATION_INFO where credencenum=VAR_CREDENCENUM;
VAR_EXIT:=to_number(VAR_MOVECOUNT)-VAR_TEMPSCRAPCOUNT;
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 更新表mate_location_info失败';
update mate_location_info a set a.MOVECOUNT=VAR_EXIT,a.STATE=DECODE(VAR_EXIT,0,'1','0')
where a.STATE='0'
and exists(
select * from MATE_LOCATION_SCRAP_INFO b
where b.CREDENCENUM=VAR_CREDENCENUM
and a.FEEDNUM=b.FEEDNUM
and a.ITEMNAME=b.ITEMNAME
and a.FOLDADDRESS=b.FOLDADDRESS
and a.TOLOCATION=B.TOLOCATION);
IF (TO_NUMBER(VAR_TEMPLOCKCOUNT)-VAR_TEMPSCRAPCOUNT)<=0 THEN
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 删除表mate_sub_location_info失败';
DELETE MATE_SUB_LOCATION_INFO
where credencenum=VAR_CREDENCENUM;
ELSE
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 更新表mate_sub_location_info失败';
update mate_sub_location_info set MOVECOUNT=to_number(MOVECOUNT)-VAR_TEMPSCRAPCOUNT,LOCKCOUNT=TO_NUMBER(LOCKCOUNT)-VAR_TEMPSCRAPCOUNT
where credencenum=VAR_CREDENCENUM;
END IF;
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 更新表MATE_LOCATION_SCRAP_INFO失败';
UPDATE MATE_LOCATION_SCRAP_INFO SET STATE='0',REAMRK=TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
where CREDENCENUM=VAR_CREDENCENUM;
ERROR_MESAGE:=VAR_CREDENCENUM||' 凭证号操作,核实报废成功';
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERNAME,VAR_OPACTION,VAR_TABLE);
END IF;
/* 报废处理 退回*/
IF VAR_ACTION='0030' THEN
VAR_TABLE:='MATE_LOCATION_SCRAP_INFO';
VAR_OPACTION:='报废处理退回';
ERROR_MESAGE:=VAR_CREDENCENUM||' 操作失败,该凭证号已经存在多笔';
select STATE,FEEDNUM INTO VAR_TEMPSTATE,VAR_TEMPFEEDNUM from MATE_LOCATION_SCRAP_INFO where CREDENCENUM=VAR_CREDENCENUM;
IF VAR_TEMPSTATE='2' THEN
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 操作失败,已经退回';
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERNAME,VAR_OPACTION,VAR_TABLE);
goto re_point;
else
select SCRAPCOUNT INTO VAR_TEMPSCRAPCOUNT from MATE_LOCATION_SCRAP_INFO where CREDENCENUM=VAR_CREDENCENUM and rownum<=1;
END IF;
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 操作失败,得不到冻结数量';
select lockcount into VAR_TEMPLOCKCOUNT from MATE_SUB_LOCATION_INFO where credencenum=VAR_CREDENCENUM;
IF (TO_NUMBER(VAR_TEMPLOCKCOUNT)-VAR_TEMPSCRAPCOUNT)<=0 THEN
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 删除表mate_sub_location_info失败';
DELETE MATE_SUB_LOCATION_INFO
where credencenum=VAR_CREDENCENUM;
ELSE
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 更新表mate_sub_location_info失败';
update mate_sub_location_info a set MOVECOUNT=to_number(MOVECOUNT)-VAR_TEMPSCRAPCOUNT,LOCKCOUNT=TO_NUMBER(LOCKCOUNT)-VAR_TEMPSCRAPCOUNT
where credencenum=VAR_CREDENCENUM;
END IF;
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 更新表MATE_LOCATION_SCRAP_INFO失败';
UPDATE MATE_LOCATION_SCRAP_INFO SET STATE='2',REAMRK=TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
where CREDENCENUM=VAR_CREDENCENUM;
ERROR_MESAGE:=VAR_TEMPFEEDNUM||'对应凭证号:'||VAR_CREDENCENUM||' 凭证号操作,撤销报废成功';
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERNAME,VAR_OPACTION,VAR_TABLE);
END IF;
ERROR_MESAGE:='OK';
<<re_point>>
COMMIT;
VAR_SQL:='SELECT '''||ERROR_MESAGE||''' remesage FROM DUAL';
OPEN MESAGE FOR VAR_SQL;
EXCEPTION
WHEN ERAISE THEN
ROLLBACK;
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERNAME,VAR_OPACTION,VAR_TABLE);
VAR_SQL:='SELECT '''||ERROR_MESAGE||''' remesage FROM DUAL';
OPEN MESAGE FOR VAR_SQL;
WHEN OTHERS THEN
ROLLBACK;
VAE_REDATA:=GETLOG(ERROR_MESAGE,VAR_USERNAME,VAR_OPACTION,VAR_TABLE);
VAR_SQL:='SELECT '''||ERROR_MESAGE||''' remesage FROM DUAL';
OPEN MESAGE FOR VAR_SQL;
END MATE_SCRAP_ACTION;
/
分享到:
相关推荐
创建存储过程.rar创建存储过程.rar创建存储过程.rar
存储过程概述 创建存储过程 执行存储过程 查看、重命名和删除存储过程 创建带有参数的存储过程
plsql创建存储过程并创建job定时任务执行-详细笔记文档总结
.net创建存储过程函数, 注意只是文档,不是代码!!
本文将为大家介绍了DB2数据库创建存储过程时遇到的错误。
c#用command代码创建存储过程代码。
主要介绍了MySQL实现创建存储过程并循环添加记录的方法,涉及基本的mysql存储过程创建、调用相关操作技巧,需要的朋友可以参考下
创建数据库的存储过程 要求: 实现增,删,查,改!!
MySQL创建存储过程批量插入10万条数据 存储过程 1、首先防止主键冲突,我们清空表。 TRUNCATE table A_student; 2、编写存储过程 delimiter ‘$’; CREATE PROCEDURE batchInsert(in args int) BEGIN declare i int ...
1.了解存储过程的功能特点 2.掌握 SQL Server 创建和管理存储过程的方法,存储过程的参数定义和存储过程的执行 2.掌握 SQLServer 触发器的
创建存储过程 执行存储过程 查看和修改存储过程 重命名和删除存储过程 创建触发器 查看、修改和删除触发器 触发器的应用
有关oracle的一些创建存储过程
本实验的目的是让学生掌握创建存储过程的基本方法,并学会使用 Transact-SQL 编写存储过程的方法。实验中,学生需要创建多个存储过程来完成不同的数据库操作,例如检索学生基本信息、检索成绩、插入记录、输出学生...
CLR创建存储过程.docx ,网上收集,绝对的经典。
access数据库创建存储过程,通过查询分析器创建查询完成“存储过程”
Oracle数据库创建存储过程和触发器,详细过程和错误分析
创建存储过程和事务方法示例,可以一看。
dwb创建存储过程(1)