1 2 Previous Next 19 Replies Latest reply: Apr 24, 2012 6:57 AM by Amatu Allah Neveen Ebrahim RSS

    updating a column

    914232
      hi all
      i have master-detail form which i use to send and receive material.sending is ok but while iam receiving i use the sending details with just rec_qty blank.i enter rec_qty and save it.
      the problem is if i receive qty less than send_qty.suppose we receive 2 qty while the send_Qty is 4.i just put 2 in rec_qty and save it.but when i receive the next to items then also i put 2 in rec_qty and save it.while doing do my form is not updating rec_Qty in my detail table with sum.its just saving the value which iam entering.i write update on WHEN BUTTON PRESS TRIGGER.

      IF :ship_dtl.rec_Qty is not null then
      update SHIP_DTL
           set rec_qty=nvl(rec_qty,0)+nvl(:ship_dtl.rec_qty,0)
           where bill_id=:ship_mstr.bill_id
      and MAT_CODE=:ship_dtl.mat_code;
      end if;

      its not giving any error but also not effecting rec_Qty.just saving the value which iam entering in rec_Qty.

      also tried....

      declare
      A number := 1 ;

      v_count number;
      begin
      loop

      A := A + 1 ;      
      select count(*) into v_count from SHIP_DTL
      where
      bill_id=:ship_dtl.bill_id
      and MAT_CODE=:ship_dtl.mat_code;
      if v_count =0 then
      INSERT INTO SHIP_DTL(REC_QTY)
                VALUES (
      :ship_dtl.rec_qty)


      else
           update SHIP_DTL
           set rec_qty=nvl(rec_qty,0)+nvl(:ship_dtl.rec_qty,0)
           where bill_id=:ship_mstr.bill_id
      and MAT_CODE=:ship_dtl.mat_code;
      end if;

      EXIT WHEN :ship_dtl.mat_code is not null ;
      end loop ;

      end;
      still this is also not effection rec_qty with summing up the old value with new value.

      please suggest.thanks in advance.
        • 1. Re: updating a column
          Amatu Allah Neveen Ebrahim
          Hi

          Pls after the Update Statement commit the changes u have made...
           
          FORMS_DLL('COMMIT');
          
          or
          
           COMMIT;
          Hope this helps...

          Regards,

          Amatu Allah
          • 2. Re: updating a column
            914232
            forms_dll('commit'); is not working.iam using form 10g
            commit; also no effect.please sir help me...
            • 3. Re: updating a column
              Amatu Allah Neveen Ebrahim
              Pls do the in caps lock as > 'COMMIT'

              Hope this helps...

              Regards,

              Amatu Allah
              • 4. Re: updating a column
                914232
                still the same sir..
                • 5. Re: updating a column
                  Amatu Allah Neveen Ebrahim
                  Hi

                  Pls for testing purposes only u can run the update statement on Toad or SQL or Remove the IF ... Statement Conditions i suspect in it's execution it may not True i guess

                  If you want to commit the whole form, use COMMIT. If you want to commit some extra code in the database, but without committing the form, use forms_ddl('commit'); if a space exists with in the ' commit' it won't work u can use...
                   
                  
                  COMMIT_FORM  ;     -- Writes all changed records in all blocks to the database ... performs a commit
                  FORMS_DDL('COMMIT');      --Commit in the database independent of the form
                  Pls compile , Save and Generate the form for the changes to take effects and make sure ur compiled form generated is on the same path.

                  Otherwise share us the code and in which Trigger r u using it WHEN-BUTTON-PRESSED no...? u can also use ...
                   
                  POST;
                  Hope this helps...

                  Regards,

                  Amatu Allah

                  Edited by: Amatu Allah on Apr 24, 2012 1:19 AM
                  • 6. Re: updating a column
                    indra budiantho
                    check your criteria, may be there is no record :
                    select *
                    from...
                    where bill_id=:ship_mstr.bill_id
                    and MAT_CODE=:ship_dtl.mat_code
                    • 7. Re: updating a column
                      914232
                      THIS IS MY CODE ON WHEN BUTTON PRESSED
                      declare
                      v_count4 number;
                      begin
                      select count(*) into v_count4 from ship_dtl
                      where bill_id=:ship_mstr.bill_id and
                      mat_code=:ship_dtl.mat_code and
                      send_qty is not null;
                      if v_count4 > 0 then

                      update ship_dtl set
                      rec_qty=nvl(rec_qty,0)+nvl(:ship_dtl.rec_qty,0);
                      end if;
                      end;

                      and also iam generating an alert on this button to save.in that i write commit_form before but now change it to forms_ddl('commit');

                      please check my code.
                      • 8. Re: updating a column
                        indra budiantho
                        how if you just hard code:
                        update ship_dtl set
                        rec_qty=100;
                        commit;
                        does it work?
                        • 9. Re: updating a column
                          Amatu Allah Neveen Ebrahim
                          Hi

                          Did u Run or Try to read the post i update no ? pls do.
                          and also iam generating an alert on this button to save.in that i write commit_form before but now change it to forms_ddl('commit');
                          where is this code and it's bettter to encountered ur code with
                           
                          to format it to be better readable...

                          Regards,

                          Amatu Allah
                          • 10. Re: updating a column
                            914232
                            i made few changes in my code,but its only inserting and updating last record in my :ship_dtl.

                            [/declare
                                 A number := 1 ;
                            v_count4 number;
                            begin
                                 loop
                                      A := A + 1 ;      
                            select count(*) into v_count4 from ship_dtl
                            where bill_id=:ship_mstr.bill_id and
                            mat_code=:ship_dtl.mat_code and
                            send_qty is not null;
                            if v_count4 > 0 then
                                 

                            update ship_dtl set
                            rec_qty=nvl(rec_qty,0)+nvl(:ship_dtl.rec_qty,0)
                            where bill_id=:ship_mstr.bill_id and
                            mat_code=:ship_dtl.mat_code and
                            send_qty is not null;
                            --FORMS_DDL('COMMIT');
                            end if;

                            EXIT WHEN :ship_dtl.bill_id is not null ;

                            end loop ;
                            end;]
                            • 11. Re: updating a column
                              Amatu Allah Neveen Ebrahim
                              u need to use the Built-in
                              FIRST_RECORD;
                              Before loop and
                              NEXT_RECORD;
                              Before exit the loop

                              Regards,

                              Amatu Allah
                              • 12. Re: updating a column
                                HamidHelal
                                IF :ship_dtl.rec_Qty is not null then
                                update SHIP_DTL
                                     set rec_qty=nvl(rec_qty,0)+nvl(:ship_dtl.rec_qty,0)
                                     where bill_id=:ship_mstr.bill_id
                                and MAT_CODE=:ship_dtl.mat_code;
                                end if;
                                Above code not give you the exact result what u trying to achieve. Like above code will work it you use database trigger.

                                also tried....

                                declare
                                A number := 1 ;

                                v_count number;
                                begin
                                loop

                                A := A + 1 ;      
                                select count(*) into v_count from SHIP_DTL
                                where
                                bill_id=:ship_dtl.bill_id
                                and MAT_CODE=:ship_dtl.mat_code;
                                if v_count =0 then
                                INSERT INTO SHIP_DTL(REC_QTY)
                                          VALUES (
                                :ship_dtl.rec_qty)


                                else
                                     update SHIP_DTL
                                     set rec_qty=nvl(rec_qty,0)+nvl(:ship_dtl.rec_qty,0)
                                     where bill_id=:ship_mstr.bill_id
                                and MAT_CODE=:ship_dtl.mat_code;
                                end if;

                                EXIT WHEN :ship_dtl.mat_code is not null ;
                                end loop ;

                                end;
                                Above also don't give you spectated result and the update command is same. You need little bit change in update statement. Like
                                --- after else
                                select nvl(rec_qty,0) into abc ---here abc is a variable with number data type
                                from ship_dtl
                                where bill_id=:ship_mstr.bill_id
                                and MAT_CODE=:ship_dtl.mat_code;
                                /* now update */
                                update SHIP_DTL
                                set rec_qty=abc +nvl(:ship_dtl.rec_qty,0)
                                where bill_id=:ship_mstr.bill_id
                                and MAT_CODE=:ship_dtl.mat_code;
                                Hope this helps

                                Hamid
                                • 13. Re: updating a column
                                  914232
                                  ohhh....really thank you all sirs.it is solved now.

                                  i put first record and next record.

                                  declare
                                       A number := 1 ;
                                  v_count4 number;
                                  begin
                                  first_record;     
                                  loop
                                            A := A + 1 ;
                                       
                                  select count(*) into v_count4 from ship_dtl
                                  where bill_id=:ship_mstr.bill_id and
                                  mat_code=:ship_dtl.mat_code and
                                  send_qty is not null;
                                  if v_count4 > 0 then
                                       

                                  update ship_dtl set
                                  rec_qty=nvl(rec_qty,0)+nvl(:ship_dtl.rec_qty,0)
                                  where bill_id=:ship_mstr.bill_id and
                                  mat_code=:ship_dtl.mat_code and
                                  send_qty is not null;
                                  --FORMS_DDL('COMMIT');
                                  end if;
                                  next_record;
                                  EXIT WHEN :ship_dtl.bill_id is not null ;

                                  end loop ;
                                  end;
                                  • 14. Re: updating a column
                                    HamidHelal
                                    911229 wrote:
                                    ohhh....really thank you all sirs.it is solved now.
                                    If your question is solved then marks as answered.


                                    Hamid
                                    1 2 Previous Next