创建存储过程
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)ASVAE_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;EXCEPTIONWHEN 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;/
