5 Replies Latest reply: Jul 3, 2013 12:33 AM by Ramani_apex RSS

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

    Ramani_apex

      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

          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_apex

            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

              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_apex

                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