Step : 1
======
CREATE TABLE AUTO_NUMBER_GENERATE
( ID NUMBER ,
TYPE VARCHAR2(25 CHAR) NOT NULL ENABLE,
AUTO_NUMBER NUMBER,
PREFIX VARCHAR2(25 CHAR),
CREATE_BY NUMBER,
CREATE_DATE DATE,
UPDATE_BY NUMBER,
UPDATE_DATE DATE,
COM_ID NUMBER,
CONSTRAINT PK_AUTO_NUMBER_GENERATE_ID PRIMARY KEY (ID)
USING INDEX ENABLE
) ;
Add new Column "AUTO_NUMBER" Where would-be PREFIX+AUTO_NUMBER customize set . (My Table Name : HAWB_MST)
Step : 2
======
Process :
DECLARE
V_START_SEQ NUMBER ;
V_SEQ NUMBER ;
V_HAWB_PREFIX VARCHAR2(20);
BEGIN
SELECT AUTO_NUMBER+1 , PREFIX
INTO V_START_SEQ, V_HAWB_PREFIX
FROM AUTO_NUMBER_GENERATE
Where type= 'AIR EXPORT HAWB'
and com_id=:gcom_id;
SELECT NVL(MAX(AUTO_NUMBER)+1,V_START_SEQ) HAWB_NULL
INTO V_SEQ
FROM HAWB_MST
WHERE COM_ID=:GCOM_ID;
UPDATE HAWB_MST SET
AUTO_NUMBER = V_SEQ ,
HAWB_NUMBER = V_HAWB_PREFIX||V_SEQ
WHERE ID =:P44_ID
AND COM_ID =:GCOM_ID;
---DBMS_OUTPUT.PUT_LINE(V_HAWB_PREFIX||V_SEQ);
END;
*** When Button Pressed : CREATE
=============================OR======================================
DECLARE
V_NULL NUMBER;
V_MAX NUMBER;
V_MAX_AUTO_NUMBER NUMBER;
V_HAWB_PREFIX VARCHAR2(25);
V_PKID NUMBER;
BEGIN
----- NULL/0 FINDING COM WISE
SELECT SUM(DECODE (AUTO_NUMBER,NULL,0,1)) HAWB_NULL
INTO V_NULL
FROM HAWB_MST
WHERE COM_ID=:GCOM_ID;
---- INSER MAX VALUE VARIABLE
SELECT PREFIX HAWB_PREFIX
INTO V_HAWB_PREFIX
FROM AUTO_NUMBER_GENERATE
where type= 'AIR EXPORT HAWB'
and com_id=:gcom_id;
SELECT NVL(MAX(AUTO_NUMBER),0)+1 HAWB_NUMBER_BY_AUTO
INTO V_MAX
FROM AUTO_NUMBER_GENERATE
where type= 'AIR EXPORT HAWB'
and com_id=:gcom_id;
SELECT NVL(MAX(AUTO_NUMBER),0)+1 HAWB_MAX
INTO V_MAX_AUTO_NUMBER
FROM HAWB_MST
where com_id=:gcom_id;
IF V_NULL = 0 THEN
UPDATE HAWB_MST SET
AUTO_NUMBER = V_MAX ,
HAWB_NUMBER = V_HAWB_PREFIX||'-'||V_MAX
WHERE ID=:P44_ID
AND COM_ID=:GCOM_ID;
ELSE
UPDATE HAWB_MST SET
AUTO_NUMBER = V_MAX_AUTO_NUMBER ,
HAWB_NUMBER = V_HAWB_PREFIX||'-'||V_MAX_AUTO_NUMBER
WHERE ID=:P44_ID
AND COM_ID=:GCOM_ID;
END IF;
END;