create or replace procedure PM_P_OutStorAdd (IN_SHEET_NO IN VARCHAR2, --付印单号 IN_USER IN VARCHAR2, --操作人 IN_TAG IN VARCHAR2, --1 复核,2 复核后修改删除 RETURNCODE OUT NUMBER, RETURNSTR OUT VARCHAR2 ) IS /** 功能:单据复核后修改库存。 描述:在修改复核标志位前调用该过程,修改材料出库记录并完成材料库存的修改。 编写: 郑黎 2006-6-7 **/p_outno VARCHAR2(20); --出库单号 p_printno VARCHAR2(20); --承印厂 p_sprinsheeno VARCHAR2(20); --付印单号 p_operator VARCHAR2(20); --操作人 p_intime DATE; --操作时间 pi_smatesqueno VARCHAR2(20); --材料流水号 pi_sequno VARCHAR2(5); --出库顺序号 pi_onumbers NUMBER(12); --出库数量 pi_price NUMBER(8,3); --单价 pt_csubmit VARCHAR2(1); --0未复核,1已复核,2已交书 pt_npapeusing NUMBER; --正用数 pt_npapeadding NUMBER; --放数 pt_numbers NUMBER; --临时剩余库存 pt_smateno VARCHAR2(15); --材料编号 pt_smatename VARCHAR2(30); --材料名称 EX_LIB EXCEPTION; CURSOR list_material IS SELECT a.smatesqueno,smateno,smatename,npapeusing,npapeadding,nmateprice FROM pr_t_requmaterial a,pr_t_codematerial d WHERE sprinsheeno=IN_SHEET_NO AND a.smatesqueno=d.smatesqueno AND d.flag=0 ORDER BY a.sitemno;
BEGIN pi_sequno:=0; p_operator:=IN_USER; p_intime := sysdate; SELECT sprinsheeno,sprinwork,csubmit INTO p_sprinsheeno,p_printno,pt_csubmit FROM pr_t_prinsheet WHERE sprinsheeno=IN_SHEET_NO; IF IN_TAG='1' THEN --复核 IF pt_csubmit='0' THEN --新增出库主表 CO_P_BILLNO('CC',p_outno); INSERT INTO pm_t_out (outno,printno,sprinsheeno,operator,intime) VALUES (p_outno,p_printno,p_sprinsheeno,p_operator,p_intime); OPEN list_material; LOOP FETCH list_material INTO pi_smatesqueno,pt_smateno,pt_smatename,pt_npapeusing,pt_npapeadding,pi_price; EXIT WHEN list_material%NOTFOUND; pi_sequno:=pi_sequno+1; pi_onumbers:=pt_npapeusing+pt_npapeadding; --新增出库子表 INSERT INTO pm_t_outitem (outno,smatesqueno,sequno,onumbers,price) VALUES (p_outno,pi_smatesqueno,pi_sequno,pi_onumbers,pi_price); --检查库存是否足够 SELECT (numbers-pi_onumbers) INTO pt_numbers FROM pm_t_mlibrary WHERE printno=p_printno AND smatesqueno=pi_smatesqueno; IF (pt_numbers<0) THEN RETURNSTR := pt_smateno||pt_smatename||'库存不够' ; RAISE EX_LIB; END IF; --修改材料库存信息 UPDATE pm_t_mlibrary SET numbers=numbers-pi_onumbers WHERE printno=p_printno AND smatesqueno=pi_smatesqueno; END LOOP; CLOSE list_material; UPDATE pr_t_prinsheet SET csubmit='1',schecker=IN_USER WHERE sprinsheeno=IN_SHEET_NO; END IF; ELSIF IN_TAG='2' THEN --复核后修改删除 IF (pt_csubmit='1') THEN SELECT outno INTO p_outno FROM pm_t_out WHERE printno=p_printno AND smatesqueno=pi_smatesqueno; --删除出库主表 DELETE FROM pm_t_out WHERE outno=p_outno; --删除出库子表 DELETE FROM pm_t_outitem WHERE outno=p_outno; OPEN list_material; LOOP FETCH list_material INTO pi_smatesqueno,pt_smateno,pt_smatename,pt_npapeusing,pt_npapeadding,pi_price; EXIT WHEN list_material%NOTFOUND; --修改材料库存 UPDATE pm_t_mlibrary SET numbers=numbers+pi_onumbers WHERE printno=p_printno AND smatesqueno=pi_smatesqueno; END LOOP; CLOSE list_material; UPDATE pr_t_prinsheet SET csubmit='0',schecker='' WHERE sprinsheeno=IN_SHEET_NO; END IF; END IF; RETURNCODE:=0; RETURNSTR:='';
EXCEPTION WHEN EX_LIB THEN RETURNCODE := -1; ROLLBACK; WHEN OTHERS THEN RETURNCODE:=-2; RETURNSTR:=SQLERRM||RETURNSTR; ROLLBACK;
(IN_SHEET_NO IN VARCHAR2, --付印单号
IN_USER IN VARCHAR2, --操作人
IN_TAG IN VARCHAR2, --1 复核,2 复核后修改删除
RETURNCODE OUT NUMBER,
RETURNSTR OUT VARCHAR2
)
IS
/**
功能:单据复核后修改库存。
描述:在修改复核标志位前调用该过程,修改材料出库记录并完成材料库存的修改。
编写: 郑黎 2006-6-7
**/p_outno VARCHAR2(20); --出库单号
p_printno VARCHAR2(20); --承印厂
p_sprinsheeno VARCHAR2(20); --付印单号
p_operator VARCHAR2(20); --操作人
p_intime DATE; --操作时间
pi_smatesqueno VARCHAR2(20); --材料流水号
pi_sequno VARCHAR2(5); --出库顺序号
pi_onumbers NUMBER(12); --出库数量
pi_price NUMBER(8,3); --单价
pt_csubmit VARCHAR2(1); --0未复核,1已复核,2已交书
pt_npapeusing NUMBER; --正用数
pt_npapeadding NUMBER; --放数
pt_numbers NUMBER; --临时剩余库存
pt_smateno VARCHAR2(15); --材料编号
pt_smatename VARCHAR2(30); --材料名称
EX_LIB EXCEPTION;
CURSOR list_material IS
SELECT a.smatesqueno,smateno,smatename,npapeusing,npapeadding,nmateprice FROM pr_t_requmaterial a,pr_t_codematerial d
WHERE sprinsheeno=IN_SHEET_NO AND a.smatesqueno=d.smatesqueno AND d.flag=0 ORDER BY a.sitemno;
BEGIN pi_sequno:=0;
p_operator:=IN_USER;
p_intime := sysdate; SELECT sprinsheeno,sprinwork,csubmit
INTO p_sprinsheeno,p_printno,pt_csubmit
FROM pr_t_prinsheet WHERE sprinsheeno=IN_SHEET_NO;
IF IN_TAG='1' THEN --复核
IF pt_csubmit='0' THEN
--新增出库主表
CO_P_BILLNO('CC',p_outno);
INSERT INTO pm_t_out (outno,printno,sprinsheeno,operator,intime)
VALUES (p_outno,p_printno,p_sprinsheeno,p_operator,p_intime); OPEN list_material;
LOOP
FETCH list_material INTO pi_smatesqueno,pt_smateno,pt_smatename,pt_npapeusing,pt_npapeadding,pi_price;
EXIT WHEN list_material%NOTFOUND;
pi_sequno:=pi_sequno+1;
pi_onumbers:=pt_npapeusing+pt_npapeadding;
--新增出库子表
INSERT INTO pm_t_outitem (outno,smatesqueno,sequno,onumbers,price)
VALUES (p_outno,pi_smatesqueno,pi_sequno,pi_onumbers,pi_price);
--检查库存是否足够
SELECT (numbers-pi_onumbers) INTO pt_numbers
FROM pm_t_mlibrary WHERE printno=p_printno AND smatesqueno=pi_smatesqueno;
IF (pt_numbers<0) THEN
RETURNSTR := pt_smateno||pt_smatename||'库存不够' ;
RAISE EX_LIB;
END IF;
--修改材料库存信息
UPDATE pm_t_mlibrary SET numbers=numbers-pi_onumbers
WHERE printno=p_printno AND smatesqueno=pi_smatesqueno;
END LOOP;
CLOSE list_material;
UPDATE pr_t_prinsheet SET csubmit='1',schecker=IN_USER WHERE sprinsheeno=IN_SHEET_NO;
END IF;
ELSIF IN_TAG='2' THEN --复核后修改删除
IF (pt_csubmit='1') THEN
SELECT outno INTO p_outno FROM pm_t_out WHERE printno=p_printno AND smatesqueno=pi_smatesqueno;
--删除出库主表
DELETE FROM pm_t_out WHERE outno=p_outno;
--删除出库子表
DELETE FROM pm_t_outitem WHERE outno=p_outno; OPEN list_material;
LOOP
FETCH list_material INTO pi_smatesqueno,pt_smateno,pt_smatename,pt_npapeusing,pt_npapeadding,pi_price;
EXIT WHEN list_material%NOTFOUND;
--修改材料库存
UPDATE pm_t_mlibrary SET numbers=numbers+pi_onumbers
WHERE printno=p_printno AND smatesqueno=pi_smatesqueno;
END LOOP;
CLOSE list_material;
UPDATE pr_t_prinsheet SET csubmit='0',schecker='' WHERE sprinsheeno=IN_SHEET_NO;
END IF;
END IF;
RETURNCODE:=0;
RETURNSTR:='';
EXCEPTION
WHEN EX_LIB THEN
RETURNCODE := -1;
ROLLBACK;
WHEN OTHERS THEN
RETURNCODE:=-2;
RETURNSTR:=SQLERRM||RETURNSTR;
ROLLBACK;
END;