This discussion is archived
5 Replies Latest reply: Jul 2, 2013 10:33 PM by Ramani_vadakadu RSS

Different serial number generating based on the login user and flow type

Ramani_vadakadu Journeyer
Currently Being Moderated

i have select list based on the table value as FLow type : Incoming/Out going

i have table like

sl #user nameincoming flow #out going flow #
1USER AIN/450/13OT/950/13
2USER BIN/550/13OT/1050/13

 

  how can generate the incoming and outgoing number ,based on the select list from above that table value and user also!

  • 1. Re: Different serial number generating based on the login user and flow type
    Mike Kutz Expert
    Currently Being Moderated

    insufficient information

     

    return 'IN/' || inflow_seq.nextval || '/' || to_char(sysdate, 'RR')

     

    That will generate unique values of the form:

    IN/{number}/{2-digit year}

  • 2. Re: Different serial number generating based on the login user and flow type
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    hai, my requirement is partially done but i got the ajax error. please login my page: 

    user password  for this application  USER_A/apex 

                                                                     USER_B/apex

     

    http://apex.oracle.com/pls/otn/f?p=29288:4:115323397383316:::::

     

    workspace         : ram_r&d

    user/password : aramani/apex

     

    i thought i missed in pl/sql code is if condition!

  • 4. Re: Different serial number generating based on the login user and flow type
    Mike Kutz Expert
    Currently Being Moderated

    My coffee comes first.

     

    If P4_FLOW is suppose to match the SL_NUM column, then write the code as such.

    No need for an IF/ELSIF blocks of code.

     

    Your logic is very flawed.

    eg WHERE USER_NAME='USER_A' and USER_NAME=:APP_USER

    This will always generate a 'no data found' error when I'm 'USER_B'

     

    You do realize, if >1 person is using this at the same time, they can (and will) generate identical numbers?

     

    Again, look up sequences and use that.

    Your code should be:

    Declare

         Ref_val_ varchar2(16);

      Seq_Val_ integer;

    begin

      IF :P4_FLOW=1 -- IN

      THEN

        select in_flow_sequence.nextval into seq_val_ from dual;

        ref_val_ := 'IN/' || lpad( seq_val_, 3, '0') || '/' || to_char(sysdate,'YY');

      elsif :P4_FLOW=2 -- OUT

      THEN

        select out_flow_sequence.nextval into seq_val_ from dual;

        ref_val_ := 'OUT/' || lpad( seq_val_, 3, '0') || '/' || to_char(sysdate,'YY');

      else -- oops, bad selection.  throw an error

        raise_application_error(-20002,'Bad FLOW Selection.');

      end if;

      return ref_val_;

    end;

  • 5. Re: Different serial number generating based on the login user and flow type
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    hai mikekutz,

     

    thank you for point out my missed code,also you code is not based on my request due to the sequence number generating from specified number from end user,coz they need change every month or week or year ( this is their business) that's i put flow type with number.also this is not master table this value to be store in master table with reference number column only there is no relation between this table and master table.

     

    BTW: i fixed my self for this issue. how?

     

    1. Declare
    2.      Ref_val_ varchar2(16):= Null;
    3.       Seq_Val_ Number;
    4. begin  
    5. if  :P4_FLOW=1 and :P4_GENERATE_NUM IS NULL THEN     -- for IN Flow
    6.              Select nvl(IN_COM_FLOW,0)+1
    7.                    into Seq_Val_
    8.                          from  MY_SRL_TABLE where SL_NUM=1  AND USER_NAME=:APP_USER
    9.                              or ( SL_NUM=2  AND USER_NAME=:APP_USER);
    10.           select 'IN'||'/'||lpad(Seq_Val_,5,0)||'/'||to_char(sysdate,'YY')
    11.               into Ref_val_
    12.                  from dual;
    13.                     -- Populate ref #
    14.                       return  Ref_val_;
    15.      elsif  :P4_FLOW=2 and :P4_GENERATE_NUM IS NULL THEN  --for Out Flow
    16.                    Select Nvl(OUT_GO_FLOW,0) + 1
    17.                         into Seq_Val_
    18.                             from  MY_SRL_TABLE where SL_NUM=2  AND USER_NAME=:APP_USER
    19.                                or ( SL_NUM=1  AND  USER_NAME=:APP_USER  ) ;
    20.        select 'OUT'||'/'||lpad(Seq_Val_,3,0)||'/'||to_char(sysdate,'YY')
    21.            into Ref_val_
    22.               from dual;
    23.                 -- ref number
    24.        return  Ref_val_;
    25.     else
    26.         return Ref_val_;
    27. end if;
    28. :P4_GENERATE_NUM:=:Ref_val_;
    29. end;

    now you can see my demo page with User login(USER_A,USER_B) only.

     

    Thanx,

    Ram