PLSQL 自动增号

最新推荐文章于 2023-08-10 15:04:07 发布

cuiweigk19423

最新推荐文章于 2023-08-10 15:04:07 发布

阅读量258

点赞数

LCD预估申请单：自动增号过程。
-- 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;