Sequences-advanced!!!

hi , as ur project codes r complicated like - BBL-001,u can not use a sequence to generate the whole project code,cos charecters like - BBL can not be generated with a sequence.u can use a sequence to generate the number portion of ur project code such as 1 or 2 or 3 etc.then u have to concate  two or one  "0" before the sequence value to produce a char value which will look like - 001.if ur "next" sequence value goes to two digit then u have to concate one "0" to produce a char value like - 014(of this value the last two charecter "14" will come from the sequence).then u can concate the customer abbreviation "BBL" and the charecter "-" with the generated sequence value(obviously u have to concate the "0" before the sequence value to get ur full project code like BBL-001).so u see u can get only the number value from a sequence which can range from 1 to 100 or more(what ever u wish).and u have to manually concate  the customer abbreviation ,the charecter "-" and leading "0" to the "next" sequence value to get the full project code.
u can do this job with a function.here is a point for u that if u want to generate the last three digit of ur  project code with a sequence than u have to create one sequence per ur customer.cos, suppose u have a sequence which already generated three values -1,2,3 for a customer -FIN-001,FIN-002,FIN-003.now u want to get a new value from the sequence for the customer -BBL ,u will get a value of 4 from the sequence as it already generated the values 1,2,3 earlier.so for BBL u will get the code like BBL-004.so for BBL u can not get the codes like BBL-001,
BBL-002 because the sequence will not give u the value 1,2 again.so u have to have one sequence for each customer abbreviation or u can use another table to keep values that already generated by the sequence.but all these process will complicate ur job.here i am giving u a function which will not use any sequence but it will give u, ur expected project code.here u have to give the three charecter abbreviation for customer as parameter to the function for which u want to generate code  ---
(here i am taking ur project_code column of ur project table as char datatype - u have to have char data type for this column as "BBL-001" is a charecter string.)

create or replace function gen_pro_code(cust_abbv char)
return char
is
max_code number(3);
new_code char(6);
begin
 select max(to_number(substr(project_code,5)))
 into max_code
 from project
 where upper(substr(project_code,1,3)) = upper(cust_abbv);
 if max_code = 0 then
  new_code := upper(cust_abbv) || "-"||"001";
 elsif max_code <= 9 then
  new_code := upper(cust_abbv)||"-"||"0"||"0"
             ||to_char(max_code);
 elsif max_code <= 99 then
  new_code := upper(cust_abbv) || "-"||"0"||
              to_char(max_code);
 else
 new_code := upper(cust_abbv) || "-" ||to_char(max_code);
 end if;
 return new_code;
end ;

SQL > select gen_pro_code('bbl') from dual;

GEN_PRO_CODE('BBL')
----------------------
BBL-001

After u get a value from this function u insert it into the table project.then u again use the function to get a new value for bbl.no matter u use the function for bbl or fin u will get the starting code as BBL-001 and FIN-001.dont forget to make the datatype of ur project_code column as char(6),if u have not already made it like this.