asked on

"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

Naveen Kumar

FOR UPDATE of ocr.chg_status_code NOWAIT; -- why did you put this ?

Thanks

Naveen Kumar

where is the update command in your code.

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ritesh_Garg

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

&quot;FRM:40501: ORACLE ERROR : Unable to reserve record for update or delete&quot;

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