日期:2014-05-16  浏览次数:20567 次

创建存储过程
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_FEED