GENERATE TRANSACTION / SERIAL / SEQUENCE NUMBER WITH A NICE FORMAT.

Generate a number with Year+Month+Day+APP_USER+TRANID    format.

Declare v_trid varchar2(20) := Null;
Begin
SELECT TO_CHAR (SYSDATE, 'RR')
       || TO_CHAR (SYSDATE, 'MM')
       || TO_CHAR (SYSDATE, 'DD')
       || :APP_USER
       ||
  LPAD (NVL  (MAX (SUBSTR(TRANID,10,length(TRANID)) ), 0)+1, 6, 0) trid
  INTO v_trid
  FROM TABLE_NAME;
:P80_TRANID := v_trid;
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Failed to Generate Transaction ID...! '||SQLERRM);
End;