创建存储过程

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;
/
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值