How to Manage Serial# through database Sequence

How to Manage Serial# through database Sequence

Create sequence invoice_serial
MINVALUE 1
maxvalue 99999999
increment by 1;
If you need to modify the sequence to start with particular number
Create sequence invoice_serial
start with 8
increment by 1
maxvalue 99999999;
Now write a PRE_INSERT trigger to access the sequence through form.
select invoice_serial.nextval 
into :invoice_details.serial#
from dual;
Note: In the above case database sequence updated and serial is displayed when you save the record.

How to Control the sequence through database Table:
Create a form as well as database table and sequence from where from where you want to handle the sequence.
CREATE TABLE INVOICE_SEQUENCE
(
SEQ_COMP NUMBER(1),
SEQ_YEAR NUMBER(4),
SEQ_TYPE NUMBER(2),
SEQ_SEQ NUMBER(10));
PRIMARY KEY: SEQ_COMP, SEQ_YEAR, SEQ_TYPE
CREATE SEQUENCE INVOICE_SEQ
   MINVALUE 1
   MAXVALUE 99999999
   START WITH 1
   INCREMENT BY 1
   CACHE 20;
Then create the required form to handle the sequence. Here in below example we have created the ‘INVOICE’ form in which we will handle the invoice# number automatically incremented with each transaction.
Then write a PRE-INSERT TRIGGER on block to update/insert the sequence.
---- update the sequence ----
BEGIN
      UPDATE INVOICE_SEQUENCE SET  SEQ_SEQ=SEQ_SEQ+1
      WHERE SEQ_YEAR=TO_NUMBER(TO_CHAR(:INVOICE_HEADER.INV_DATE,'YYYY'))
      AND SEQ_COMP = 1 AND SEQ_TYPE = 0;
exception
      when   others then
      show_message(2,2);
END;
BEGIN
  SELECT MAX(TO_NUMBER(TO_CHAR(:INVOICE_HEADER.INV_DATE, 'YY')))
  into   :INVOICE_HEADER.YY
  FROM   INVOICE_HEADER;
exception
      when   others then
      show_message(2,2);
END;
--Get the new sequence----
BEGIN
      select SEQ_SEQ
      into   :INVOICE_HEADER.INVOICE#
      from   INVOICE_SEQUENCE
WHERE SEQ_YEAR=TO_NUMBER(TO_CHAR(:INVOICE_HEADER.INV_DATE,'YYYY'))
      AND SEQ_COMP = 1 AND SEQ_TYPE = 0;
 exception
      when   no_data_found then
            show_message(1,'Could not retrieve the Serial Number!');
            raise form_trigger_failure;
      when   others then
             show_message(2,2);
END;

How Manage Form details auto Serial Number:
To display Master/Details Form serial number automatically in the form.
Just open the property of the item and change
item_name: serial#
item_type: display item
database item: yes
Calculation Mode: Formula
Formula: :system.trigger_record; or system.cursor_record;
-or- you can write the same things into POST_INSERT or WHEN_NEW_RECORD_INSTANCE trigger
:invoice_details.serial# = :system.trigger_record;
Note: It will display your serial automatically when inserting new record before save.