Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5511
  • Last Modified:

"FRM:40501: ORACLE ERROR : Unable to reserve record for update or delete"

I have written in the following code in custom.pll ---

 BEGIN    
          If (form_name = 'OKEKAUWB') Then
           If (event_name = 'WHEN-NEW-BLOCK-INSTANCE') Then
                If (block_name = 'K_HEADER') Then
                    -- fnd_message.set_string('Change Request Status: '||l_chg_req_num||' '||g_chg_status);
                 --  fnd_message.show;
                BEGIN
                      
                      l_contract_id := To_Number(Name_In('K_HEADER.K_HEADER_ID'));
                      l_contract_vers := To_Number(Name_In('K_HEADER.MAJOR_VERSION'));
                      l_last_form     := To_Number(name_in('system.last_form'));
                 
                  SELECT ocr.chg_request_num, ocr.chg_status_code
                  INTO   l_chg_req_num, l_chg_status
                  FROM   OKE.oke_chg_requests ocr
                  WHERE  ocr.k_header_id = l_contract_id
                  AND    ocr.chg_status_code = 'IN PROGRESS'
                  FOR UPDATE of ocr.chg_status_code NOWAIT;
                 
                     fnd_message.set_string('chg_request_num ->'||l_chg_req_num);
                     fnd_message.show;                   
                     
                      If ( l_chg_req_num IS NOT NULL AND l_chg_status = 'IN PROGRESS') Then
                             set_item_property('K_HEADER.K_VALUE',ENABLED,PROPERTY_FALSE);
                             fnd_message.set_string('Contract Value has been Disabled');
                                              fnd_message.show;
                      Else
                             set_item_property('K_HEADER.K_VALUE',ENABLED,PROPERTY_TRUE);
                              fnd_message.set_string('Contract Value has been Enabled');
                                              fnd_message.show;  
                      End If;
                      
          EXCEPTION
                 WHEN OTHERS THEN
                  FND_MESSAGE.SET_STRING('SQLMSG: '||SQLERRM );
                  FND_MESSAGE.SHOW;
                  RAISE FORM_TRIGGER_FAILURE;    
          END;      
            End If;
        End If;
    End If;      
    EXCEPTION
      WHEN OTHERS THEN
               FND_MESSAGE.SET_STRING('SQLMSG: '||SQLERRM );
               FND_MESSAGE.SHOW;
               RAISE FORM_TRIGGER_FAILURE;  
    END; --- WHEN-NEW-BLOCK-INSTANCE  

I am trying to update the same record on a standard Oracle APPS form called (OKECHGRQ) which is based on the table ( OKE.oke_chg_requests) which changes CHG_STATUS_CODE to "AMENDED", When I click button labelled "CHG STATUS", I am getting the Error "FRM:40501: Unable to reserve record for update or delete" .

I would greatly appreciate your quick response asap.

Thanks!!
Kalyan
0
Kalyan_Thalakoti
Asked:
Kalyan_Thalakoti
  • 2
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
FOR UPDATE of ocr.chg_status_code NOWAIT; -- why did you put this ?

Thanks
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
where is the update command in your code.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Do you need this line in your query:
"FOR UPDATE of ocr.chg_status_code NOWAIT"?

I agree with nav_kum_v, there is no update in this procedure, so your question is somewhat confusing.

I am not familiar with Oracle Apps, but I am very familiar with Oracle Forms.  In Oracle Forms, this message usually means that another user or process has locked or changed the record, so it must be re-queried before this form can lock it.
0
 
Ritesh_GargCommented:
This trigger is getting fired at WHEN-NEW-BLOCK-INSTANCE.  It looks like you are just want to reserve/lock this record and display the message.

In Oracle Apps, there are program units to handle DML Operations (Lock / Insert / update / delete) and the program unit is a package with same name as data block name.
In order to reserve record, just copy value of any field into it.  E.g.
  copy('KHEADER.chg_status_code', 'KHEADER.chg_status_code');


Thanks,
Ritesh
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now