William

I'm not really sure about this but here's what you can try:

In the ARNE logic (instead of the lock logic)...

#LOCK = 0
SQL
BEGIN
SELECT 1 (will return 1 if record is not locked)
INTO :#LOCK
FROM <TABLE>
WHERE <CONDITION>
FOR UPDATE NOWAIT;
END;
ENDSQL
IF #LOCK = 0 (record is locked)
<WRITE YOUR STATEMENTS HERE...>
ENDIF

Thank you. I used your logic in solving my problem.

William,

There are a bunch of issues here, it's a bit hard to comment without seeing your application but..

I'm not quite clear if PROIV is configured to give you control in the event a lock is encountered, otherwise your lock logic may be ineffective (I think).

Is your lock logic being executed? Note that "Waiting for database response" is a PROIV built-in message, not an Oracle message and suggests that control is not returning to PROIV when a lock is encountered.

If not, one relevant piece of configuration might be the LOCKED_ROWS_RETURNED setting. BUT - you need to consult your colleagues because changing such a setting systemwide may well not be supported by your application.

IIRC, it is possible to set up alternative record-is-locked messages in a file definition, this should override the standard PROIV record-is-locked message for you but, again, it would be an application-wide change. Probably you can just generate a local MSG() in the lock logic.

No, the lock logic is not being executed. However I already found a work around. But thank you and appreciate your advise. Anyway as an alternative, I created this script which is called by my function to check for record locking. The work around is almost the same as what Magnus advised.

procedure check_record_locked(p_ccn ccn.ccn%type,
p_po_num po.po_num%type,
p_po_line po.po_line%type)
is
cursor check_lock is
select 'dummy' from po
where
po_num = p_po_num and
ccn = p_ccn and
po_line = p_po_line
for update nowait;

e_resource_busy exception;
pragma exception_init(e_resource_busy, -54);
begin
open check_lock;
close check_lock;
rollback;
exception
when e_resource_busy then
-- update lock table (C_FLLOCK), then PRO-IV deletes lock table before exiting the function ---
end check_record_locked;

Is this position already closed or are you still looking for applicants.

Do you provide sponsorship to a qualified candidate. Just asking before I send out my resume. Thanks.

Thank you, that was helpful.