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?”
ABCD20181201000001
ABCD20181201000002
ABCD20181201000003
ABCD20181202000001
ABCD20181202000002
ABCD20181203000001

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.

Logic:
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..

NULLIF

Oracle SQL_Null Related functions-3_NULLIF

Hi Aliens, Today, we will talk about another Null Related Function, NULLIF. You can read my notes on NVL, NVL2 […]

WHERE

SQL – WHERE clause

Hi DBgeek Army, Welcome back to the world of Oracle! Where clause helps to select desired rows or helps to […]

comments

Comment in SQL and PL-SQL

Hi DBgeek Army, Welcome back to the world of Oracle! Comments are very important aspects of our code. No matter […]

Leave a Reply

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