Generate Invoice Number

Invoice Number

admin How to - Series, Oracle

Hi folks,
Hope you all are doing well. Recently, one of my friends was asked a question in his interview “How to generate 18-digit invoice number as below mentioned pattern?”

Let me explain a bit. First 4 digits are name of biller, billing date in ‘YYYYMMDD’ format and sequence number. The turning point is, sequence number gets reset to 1 when date gets changed. It means the first invoice number of the day should always start with ‘1’.
I came up with a solution so I thought, I should share it with you all. What we need to demonstrate are-

  1. A table to insert the invoice number to verify if everything is working correctly.
  2. A Sequence to generate sequence number.
  3. A procedure to reset a Sequence when a new day starts.
  4. A function to combine all scenarios and generate 18-digit invoice number.
  5. Another procedure to insert invoice number into the table.

Prepare the string using concate operator (first four digits are static). We need to use SYSDATE to add date and LPAD function to add required ‘0’ to the sequence. When a new day starts reset the sequence back to START WITH 1. We can compare current date and date of last inserted row to check if date has been changed or not. Let’s code this logic.
You can visit my another blog to know how we can reset sequence in Oracle.

Below is the script to build all these PL/SQL objects. I will try to explain as much as possible in the comments.

As we have coded our logic, let me insert 5 rows with current date and 5 rows after changing the system date. We can use below code to do that. Just run this code, change the system date and run this code again.

I have done it and here are the results.

Hope you enjoyed it.

If you have any questions please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..


Parentheses in SQL

Hey time travellers, Though Parentheses are used pretty much everywhere in SQL but in this blog, we will talk about […]

Logical Conditions

Oracle – Logical Conditions

Hey time travellers, In this article, we will be discussing Logical Conditions. It is a very important aspect of SQL. […]


Oracle – IN Condition

Hey people, In this Oracle tutorial, we will learn how to use IN condition in SQL. IN condition is used […]

Leave a Reply

Your email address will not be published. Required fields are marked *