-- AUTOmatic NUMBER
PROCEDURE GET_NUMBER(EVENT VARCHAR2) IS
V_APPLY_NUMBER NUMBER ;
BEGIN
IF (EVENT ='WHEN-NEW-RECORD-INSTANCE') THEN
SELECT MAX(APPLY_NUMBER) INTO V_APPLY_NUMBER
FROM SGD_QOT_CST_APPLY_HEADERS_ALL
WHERE DEPT_ID = :SGDLCD_QUO_CST.DEPT_ID ;
IF (SUBSTR(V_APPLY_NUMBER,4,4) = TO_CHAR(SYSDATE,'YYMM')) THEN
:SGDLCD_QUO_CST.APPLY_NUMBER :=SUBSTR(:SGDLCD_QUO_CST.DEPT_NO,-3,3) || SUBSTR(V_APPLY_NUMBER,4) +1 ;
ELSE
:SGDLCD_QUO_CST.APPLY_NUMBER:= SUBSTR(:SGDLCD_QUO_CST.DEPT_NO,-3,3) || TO_CHAR(SYSDATE,'YYMM') || '001' ;
END IF ;
END IF;
END GET_NUMBER;
在 TEXT层的TRIGGER 下[@more@]
在DATA BLOCK 层 ON-INSERT 的TRIGGER
下调用： HEADERS.GET_NUMBER('ON-INSERT');
在HEADERS过程包
里详细过程代码如下：
-- Automatic number for quotation number .
PROCEDURE GET_NUMBER(EVENT VARCHAR2) IS
V_NUMBER VARCHAR2(12);
BEGIN
IF (EVENT = 'ON-INSERT') THEN
BEGIN
SELECT MAX(QOT_NUMBER)
INTO V_NUMBER
FROM SGD_QOT_HEADERS_ALL
WHERE SUBSTR(QOT_NUMBER,3,4) = TO_CHAR(SYSDATE,'YYMM');
EXCEPTION
WHEN OTHERS THEN
V_NUMBER := NULL;
END;
IF --(SUBSTR(V_NUMBER,3,4) = TO_CHAR(SYSDATE,'YYMM'))
V_NUMBER IS NOT NULL THEN
:SGD_QUOTATION.QOT_NUMBER := 'QP'||TO_CHAR(SUBSTR(V_NUMBER,3) +1);
ELSE
:SGD_QUOTATION.QOT_NUMBER := 'QP'|| TO_CHAR(SYSDATE,'YYMM') ||'0001';
END IF;
-- END;
--EXECUTE_QUERY;
END IF;
END GET_NUMBER;
新增时自动增号。点击“保存”按钮时执行此（GET_NUMBER）过程。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/729024/viewspace-1045832/ ，如需转载，请注明出处，否则将追究法律责任。
