DB GEEK

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.

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

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!