I want to auto generate bill no in this format (INV1/23) and it have to increment with 1

Question

I want do auto generate bill no in this format (INV1/23) without using sequence in oracle apex. Kindly write query in plsql.

I used sequence for this process, but it is not working for this. and it have to show on interactive report with form. Like when i enter data about bill like order no, order date, credit terms, credit days and remarks so it should be generated by itself in my given format.

Answer 1

Just the code to generate that invoice number is not the solution. You will run into issues very soon.

One problem with generating an invoice number on the fly by looking at the last invoice number (instead of a sequence) is conflicts. User 1 opens the form, the code checks the last invoice number (INV1/23) and generates a new one - lets say INV2/23. Now user 2 opens the form in another session. The code will generate the same INV2/23 because the last saved value is INV1/23 and then when the 2nd use saves... there will be a unique constraint violation. My advice would be to create a table with generated invoice numbers of the format you want (your requirement is too vague to give you code) and have some code that decides which number is the next to pick up, so each value is only used once. This would also allow you to re-use orphan values (eg when an invoice number is used but then transaction is cancelled). I suggest watching this video from Connor that explains the solution I just described. It is for a sequence but you could use the same logic for an invoice number.

Here is the code.

create table invoice_numbers (
    id                             number generated by default on null as identity 
                                   constraint invoice_numbers_id_pk primary key,
    seq                            number,
    invoice_year                   number,
    state                          varchar2(10 char),
    invoice_num                    as ('INV'||seq||'/'||SUBSTR(invoice_year,3,2))

)
;

DECLARE
BEGIN
  FOR i IN 1..100 LOOP
    INSERT INTO invoice_numbers (seq,invoice_year, state) 
      values (i,2023,'free');
  END LOOP;
END;
/

select * from invoice_numbers;

CREATE OR REPLACE FUNCTION next_invoice_number (year_i NUMBER) RETURN VARCHAR2
IS
  l_invoice_num VARCHAR2(100);
  l_id NUMBER;
  CURSOR next_invoice_num IS
    SELECT invoice_num, id 
      FROM invoice_numbers
     WHERE invoice_year = year_i
       AND state = 'free'
       ORDER BY seq 
       FOR UPDATE SKIP LOCKED;       
BEGIN
  OPEN next_invoice_num;
  FETCH next_invoice_num INTO l_invoice_num, l_id;
  CLOSE next_invoice_num;
  UPDATE invoice_numbers SET state = 'used' WHERE id = l_id;
  RETURN l_invoice_num;
END;
/

Then in your apex form, create a before submit computation on the invoice number with source (pl/sql expression)

next_invoice_number(extract (year from sysdate))

Note that you should only compute the invoice number before submit - if it is done on the form then the invoice number is marked as 'used'. So if the user then closes his screen without saving the invoice you'll lose that number.

Note that you'll need some code to populate the invoice_numbers table every year and some code to check you're not running out of numbers.

Answer 2

Yes, You can generate a sequence number, But you need to main a separate number column only for the Bill number in numeric, So, you can add the bill number based on your latest or maximum number from existing table data. and you can write a code like below.,

declare
v_bill_number number;
v_bill_no varchar2(100);
begin
select max(bill_number) into v_bill_number from bill_master;
v_bill_no := 'INV'||NVL(v_bill_number,1)||TO_CHAR(SYSDATE,'YY');
return v_bill_no;
end;