4 Replies Latest reply: Jan 9, 2013 6:07 AM by indra budiantho RSS

    FRM-40654 Record has been updated by another user

    indra budiantho
      Hi, there. I have a block with base table T..and it can be updated.
      In the trigger POST-FORMS-COMMIT i also update the table T. and i got that error message.

      How to solve the problem? tx in advance.
        • 1. Re: FRM-40654 Record has been updated by another user
          CraigB
          How are you saving the changes in your "table T" and what are you doing in your Post-Form-Commit trigger?

          My guess (bases on the small amount of information you have provided) is that you have conflicting updates occuring between your Post-Form-Commit trigger and the method you're using to save changes to your "table T."

          Please give us more details...it would also be helpful to know your Forms version as well as the client OS version.

          Craig...
          • 2. Re: FRM-40654 Record has been updated by another user
            indra budiantho
            10.12.0.2
            declare
                 v_TOT_SCORE_WARE NUMBER := 0; 
                 V_Score_Mean VARCHAR2(100);
                 V_CP NUMBER := 0;
            begin
                 IF :OM_TRN_APPL_HDR.BUSS_UNIT IN ('R2N','R2U') AND 'I' = :OM_TRN_APPL_HDR.CUST_TYPE THEN
                      V_CP := 0;
                      v_TOT_SCORE_WARE := ORDMGMT.Scoring_Pkg.Get_Score(:OM_TRN_APPL_HDR.APPL_NO);
                      V_CP := 1;
                      V_Score_Mean := ORDMGMT.Scoring_Pkg.Get_Score_Mean(v_TOT_SCORE_WARE,:OM_TRN_APPL_HDR.APPL_NO);
                      V_CP := 2;
                 ELSE
                      V_CP := 0;
                      v_TOT_SCORE_WARE := 9999;
                      V_CP := 1;
                      V_Score_Mean := 'LOW RISK';
                      V_CP := 2;
                 END IF;
                 
                 
                 
                 update ordmgmt.om_trn_appl_hdr
                 set TOT_SCORE_WARE = v_TOT_SCORE_WARE, Score_Mean = V_Score_Mean
                 where APPL_NO = :OM_TRN_APPL_HDR.APPL_NO;
                 
                 
                 V_CP := 3;
            
            EXCEPTION     
            WHEN OTHERS THEN 
             FIFAPPS.ibug.log_errors('SCORE ERROR OMTRNF133M'||SQLERRM||V_CP) ;
            
            end;
            the problem is that : ORDMGMT.Scoring_Pkg.Get_Score needs data in table ordmgmt.om_trn_appl_hdr after it is updated...so after updated, then i need to get the data and update again the ordmgmt.om_trn_appl_hdr . tx very much.
            • 3. Re: FRM-40654 Record has been updated by another user
              CraigB
              So, basically, you have standard update processing to record changes to the OEDMGMT.OM_TRN_APPL_HDR table and then in your Post-Form-Commit trigger you are updating the same table again. In other words, you are trying to update the table twice in the same transaction. This being the case, a more efficient method would be to assign all of the values first so there is only one update performed on your OM_TRN_APPL_HDR table. You should be able to do this in the Block Pre-Insert/Update or Pre-Commit trigger rather than use the Post-Form-Commit trigger.

              If you don't already, add the TOT_SCORE_WARE and SCORE_MEAN columns to your block and then move your code to the Pre-Commit trigger with the following change:
              declare
                   v_TOT_SCORE_WARE NUMBER := 0; 
                   V_Score_Mean VARCHAR2(100);
                   V_CP NUMBER := 0;
              begin
                   IF :OM_TRN_APPL_HDR.BUSS_UNIT IN ('R2N','R2U') AND 'I' = :OM_TRN_APPL_HDR.CUST_TYPE THEN
                        V_CP := 0;
                        v_TOT_SCORE_WARE := ORDMGMT.Scoring_Pkg.Get_Score(:OM_TRN_APPL_HDR.APPL_NO);
                        V_CP := 1;
                        V_Score_Mean := ORDMGMT.Scoring_Pkg.Get_Score_Mean(v_TOT_SCORE_WARE,:OM_TRN_APPL_HDR.APPL_NO);
                        V_CP := 2;
                   ELSE
                        V_CP := 0;
                        v_TOT_SCORE_WARE := 9999;
                        V_CP := 1;
                        V_Score_Mean := 'LOW RISK';
                        V_CP := 2;
                   END IF;
                        
              /*     update ordmgmt.om_trn_appl_hdr
                   set TOT_SCORE_WARE = v_TOT_SCORE_WARE, Score_Mean = V_Score_Mean
                   where APPL_NO = :OM_TRN_APPL_HDR.APPL_NO;  */
              
                   :OM_TRN_APPL_HDR.TOT_SCORE_WARE := v_TOT_SCORE_WARE;
                   :OM_TRN_APPL_HDR.SCORE_MEAN := V_Score_Mean;     
                   
                   V_CP := 3;
               
              EXCEPTION     
              WHEN OTHERS THEN 
               FIFAPPS.ibug.log_errors('SCORE ERROR OMTRNF133M'||SQLERRM||V_CP) ;
               
              end;
              Hope this helps,
              Craig...
              • 4. Re: FRM-40654 Record has been updated by another user
                indra budiantho
                Thank you, Craig. I have solve that by this three combination:
                1. POST-DATABASE-COMMIT, here i put.. the timer
                2. WHEN-TIMER-EXPIRED.. here i update and then commit...
                3. global variabel...just to flag..because when commit, it will go to the KEY-COMMIT, and will result into RECURSIVE...