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-
- A table to insert the invoice number to verify if everything is working correctly.
- A Sequence to generate sequence number.
- A procedure to reset a Sequence when a new day starts.
- A function to combine all scenarios and generate 18-digit invoice number.
- 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.