Automating Alphanumeric Serial Number Generation in APEX.

Asset management modules rely heavily on unique, perfectly formatted serial numbers or barcodes. Relying on users to type these manually is a recipe for data corruption. Let's look at how to automate complex serial number generation directly within the database using PL/SQL.

The Requirement We need a serial number format like this: AST-2026-0001, AST-2026-0002. It includes a prefix, the current year, and a sequential 4-digit number that resets every year.

The PL/SQL Function We can create a robust function to handle this logic. It checks the maximum existing sequence for the current year and increments it.

CREATE OR REPLACE FUNCTION generate_asset_serial RETURN VARCHAR2 IS
l_current_year VARCHAR2(4);
l_prefix       VARCHAR2(3) := 'AST';
l_next_seq     NUMBER;
l_new_serial   VARCHAR2(50);

BEGIN
-- Get the current year
l_current_year := TO_CHAR(SYSDATE, 'YYYY');
-- Find the highest sequence number for this year
SELECT NVL(MAX(TO_NUMBER(SUBSTR(serial_number, -4))), 0) + 1
INTO l_next_seq
FROM assets
WHERE serial_number LIKE l_prefix || '-' || l_current_year || '-%';

-- Format the new serial number with padded zeros
l_new_serial := l_prefix || '-' || l_current_year || '-' || LPAD(l_next_seq, 4, '0');

RETURN l_new_serial;

END generate_asset_serial; / Integrating with APEX To use this in APEX, simply go to your Asset Form page. On the P1_SERIAL_NUMBER item, set the Default Value:

Type: PL/SQL Expression

PL/SQL Expression: generate_asset_serial()

Now, every time a user opens the form to create a new asset, a perfectly formatted, unique serial number is ready to go.