Oracle 11g: how to generate numbers like in chart of accounts

Question

In oracle 11g (SQL or PL/SQL), for chart of accounts I want to generate numbers like: start from level 2: (level 1 a/c nos. are 1,2,3,4,5)

if it is in head 1 which is Asset then it will generate First number as 101 if it is in head 2 which is Liability then it will generate First number as 201

for level 3;

if parent is selected 101 then it will generate First a/c number as 10101 if parent is selected 102 then it will generate First a/c number as 10201

and so on....

for level 4:

if the parent is selected 10101 then it will generate First a/c number as 10101001 if the parent is selected 10201 then it will generate First a/c number as 10201001

and so on...

and same for all rest of head accounts ( 2=Liability, 3=Expense, 4=Revenue 5=Capital)

Please help.

Answer 1

The query below generates almost 4000 accounts using the account structure you defined. You can adjust the number of accounts generated by modifying CONNECT BY LEVEL <= 5 and changing 5 to a lower or higher number in num_accts_to_generate.

Query

WITH
    num_accts_to_generate
    AS
        (    SELECT LEVEL     AS gen_num
               FROM DUAL
         CONNECT BY LEVEL <= 5),
    level_1_accts
    AS
        (SELECT 1 AS account_level, 'Asset' AS account_type, TO_CHAR (gen_num) AS account_number
           FROM num_accts_to_generate),
    level_2_accts
    AS
        (SELECT 2                AS account_level,
                'Liability'      AS account_type,
                   l1.account_number
                || LPAD (ROW_NUMBER () OVER (PARTITION BY l1.account_number ORDER BY ROWNUM),
                         2,
                         '0')    AS account_number
           FROM level_1_accts l1, num_accts_to_generate),
    level_3_accts
    AS
        (SELECT 3                AS account_level,
                'Expense'        AS account_type,
                   l2.account_number
                || LPAD (ROW_NUMBER () OVER (PARTITION BY l2.account_number ORDER BY ROWNUM),
                         2,
                         '0')    AS account_number
           FROM level_2_accts l2, num_accts_to_generate),
    level_4_accts
    AS
        (SELECT 4                AS account_level,
                'Revenue'        AS account_type,
                   l3.account_number
                || LPAD (ROW_NUMBER () OVER (PARTITION BY l3.account_number ORDER BY ROWNUM),
                         3,
                         '0')    AS account_number
           FROM level_3_accts l3, num_accts_to_generate),
    level_5_accts
    AS
        (SELECT 5                AS account_level,
                'Capital'        AS account_type,
                   l4.account_number
                || LPAD (ROW_NUMBER () OVER (PARTITION BY l4.account_number ORDER BY ROWNUM),
                         3,
                         '0')    AS account_number
           FROM level_4_accts l4, num_accts_to_generate)
SELECT * FROM level_1_accts
UNION ALL
SELECT * FROM level_2_accts
UNION ALL
SELECT * FROM level_3_accts
UNION ALL
SELECT * FROM level_4_accts
UNION ALL
SELECT * FROM level_5_accts
ORDER BY 3;

Result

   ACCOUNT_LEVEL    ACCOUNT_TYPE    ACCOUNT_NUMBER
________________ _______________ _________________
               1 Asset           1
               2 Liability       101
               3 Expense         10101
               4 Revenue         10101001
               5 Capital         10101001001
               5 Capital         10101001002
               5 Capital         10101001003
               5 Capital         10101001004
               5 Capital         10101001005
               4 Revenue         10101002
               5 Capital         10101002001
               5 Capital         10101002002
               5 Capital         10101002003
               5 Capital         10101002004
...

3,905 rows selected.

Answer 2

thanks very much EJ Egyed for your interest to help, i have written function below which works fine for me.

create or replace function get_value_no( v_value_level in number,
                                         v_value_abbreviation in varchar2,
                                         v_value_parent in number)
 return number is
 --
 -- create trigger or function, in case of function pass following columns as parameters
 -- value_leve, value_abbreviation, value_parent_acct
 v_value_level number := 3;
 v_value_abbreviation char(1) := 'L'; -- (A)sset (L)iability (E)xpense (R)evenue (C)apital
 v_valno number;
 v_parent number := 203; -- parent account which user will select 
 v_char  varchar2(20);
begin

 -- ======================
 if v_value_level in (2, 3) then
   select max(nvl(value_no,0))
    into v_valno
    from value_set
   where value_level = v_value_level
    and value_abbreviation = v_value_abbreviation
    and value_type = 'NA'
    and value_parent_acct = v_value_parent;
   if v_valno is null or v_valno <= 0 then
    v_char := to_char(v_value_parent)||'01';
    v_valno := v_char;
   else
    v_valno := v_valno+1;
   end if;
 end if;
 -- ======================
 if v_value_level = 4 then
   select max(nvl(value_no,0))
    into v_valno
    from value_set
   where value_level = v_value_level
    and value_abbreviation = v_value_abbreviation
    and value_type = 'NA'
    and value_parent_acct = v_value_parent;
   if v_valno is null or v_valno <= 0 then
    v_char := to_char(v_value_parent)||'001';
    v_valno := v_char;
   else
    v_valno := v_valno+1;
   end if;
 end if;
 -- ======================
return v_valno;
-- 
end;