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.
--A table to insert the invoice number to verify if everything is working correctly CREATE TABLE INVOICE_DETAIL ( INVOICE VARCHAR2(18 BYTE) /* a table with one column is enough*/ ) / --A Sequence to generate sequence number CREATE SEQUENCE GENE_INVOICE START WITH 1 INCREMENT BY 1; / --A procedure to reset a Sequence when a new day starts CREATE OR REPLACE PROCEDURE RESET_SEQ (seq VARCHAR2) IS last_val NUMBER(6); /*a variable to store the last value generated by sequence*/ BEGIN EXECUTE IMMEDIATE 'SELECT '||SEQ||'.NEXTVAL FROM DUAL ' INTO LAST_VAL; /*store last value into our variable*/ EXECUTE IMMEDIATE 'ALTER SEQUENCE '||SEQ||' INCREMENT BY -'||LAST_VAL||' MINVALUE 0'; /*alter the sequence with the increemnt by the negative last value and mininum value 0 */ EXECUTE IMMEDIATE 'SELECT '||SEQ||'.NEXTVAL FROM DUAL' INTO LAST_VAL; /*Again, store last value into our variable but this time .nextval will be 0 because we have already set INCREMENT BY in negative last value*/ EXECUTE IMMEDIATE 'ALTER SEQUENCE '||SEQ||' INCREMENT BY 1 MINVALUE 0'; /*alter the sequence in its actual state*/ END; / --A function to combine all scenarios and generate 18-digit invoice number. CREATE OR REPLACE FUNCTION FUN_INVOICE_NUMBER RETURN VARCHAR2 IS /*5 VARIABLES store different values*/ OLD_DATE DATE; TOTAL_ROW NUMBER(8); OUT1 VARCHAR2(18); OUT2 VARCHAR2(18); OUT3 VARCHAR2(18); PRAGMA AUTONOMOUS_TRANSACTION;/*it is needed because we are using DDL in RESET_SEQ procedure*/ BEGIN SELECT COUNT(*) INTO TOTAL_ROW FROM invoice_detail; IF TOTAL_ROW = 0 THEN/* if table has no rows then reset the sequence and store required string into OUT1 variable*/ RESET_SEQ ('GENE_INVOICE'); SELECT 'ABCD'||TO_CHAR(SYSDATE,'YYYYMMDD')||lpad(GENE_INVOICE.NEXTVAL,6,'0') INTO OUT1 FROM DUAL; RETURN OUT1; ELSE /* if table has some rows then take the last inserted invoice number and extract the data out of invoice number*/ SELECT TO_DATE(SUBSTR(INVOICE,5,8),'YYYYMMDD') INTO OLD_DATE FROM invoice_detail WHERE ROWID = (SELECT MAX(ROWID) FROM invoice_detail); END IF; /*compare the date in last inserted invoice number with current date*/ IF OLD_DATE != TO_DATE(SYSDATE) /*if last invoice date and current date are not same then reset the sequence and store required string into OUT2 variable*/ THEN RESET_SEQ ('GENE_INVOICE'); SELECT 'ABCD'||TO_CHAR(SYSDATE,'YYYYMMDD')||lpad(GENE_INVOICE.NEXTVAL,6,'0') INTO OUT2 FROM DUAL; RETURN OUT2; /*ELSE store the required string into OUT3*/ ELSE SELECT 'ABCD'||TO_CHAR(SYSDATE,'YYYYMMDD')||lpad(GENE_INVOICE.NEXTVAL,6,'0') INTO OUT3 FROM DUAL; RETURN OUT3; END IF; END; / --Another procedure to insert invoice number into the table. CREATE OR REPLACE PROCEDURE insert_into IS BEGIN INSERT INTO invoice_detail VALUES ( fun_invoice_number() ); COMMIT; END; /
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.
BEGIN FOR i IN 1..5 LOOP insert_into (); END LOOP; END;
I have done it and here are the results.
SELECT * FROM invoice_detail; --Output INVOICE ------------------ ABCD20181225000001 ABCD20181225000002 ABCD20181225000003 ABCD20181225000004 ABCD20181225000005 ABCD20181226000001 ABCD20181226000002 ABCD20181226000003 ABCD20181226000004 ABCD20181226000005 10 rows selected.
Hope you enjoyed it.
If you have any questions please comment below or write me an email.