This content has been marked as final. Show 12 replies
Actually I want to check and show the message when the item_name is entered i.e WHEN-VALIDATE-ITEM TRIGGER.
I put the following code in WHEN-VALIDATE-ITEM TRIGGER
But I am getting the following error,
Declare curnum number; dupnum number; cur_item varchar2(100); v_alert_no number; p_linerec varchar2(100); Begin curnum := TO_NUMBER(:System.Trigger_Record); cur_item := :Lines.Item_number; First_Record; p_linerec := :Lines.Item_number; LOOP If p_linerec = cur_item then dupnum := :Lines.Line_num; set_alert_property('ALERT_STOP',ALERT_MESSAGE_TEXT, 'Duplicate Item Found,Update QTY in Original line number '||dupnum); V_ALERT_NO := show_alert('ALERT_STOP'); :LINES.ITEM_NUMBER := NULL; :LINES.ITEM_DESCRIPTION:= NULL; :LINES.ITEM_REVISION:= NULL; :LINES.ITEM_CATEGORY:= NULL; elsIF (:System.Last_Record = 'TRUE') THEN Go_Record(curnum); EXIT; ELSE Next_Record; END IF; END LOOP; End;
FRM-40737:Illegal Restricted Procedure FIRST_RECORD in WHEN-VALIDATE-TRIGGER
so I put the condition
its showing for first line itself. Duplicate Item found.Update QTY in Original line number 1
but now it checking from second record and displaying,
If :Lines.Line_num > 1 then --Only to check when the block having more than one record.
Can you tell me how can I change the above code for my requirement?
Duplicate Item found.Update QTY in Original line number 2 --(instead of Update QTY in Original line number 1)
Edited by: Gurujothi on 27 Mar, 2013 5:20 PM
If i understood your requirement properly, your aim to avoid the duplicate name ,
one way is you can use single record block , so directly you can use 'When-Validate-Item-Trigger'
Let us take Scott Schema EMp Table.
select ename into ex_nam from emp where ename =:ename;
If Ex_Nam is not null then Message('This Name is already available');
--Method 2; - With Muli Record
You have to add a Checkbox in the Emp block as non Database filed.
Ex_Nam := :Emp.Ename;
If :Ename = Ex_Nam
Message('Name Already exist');
If :SYSTEM.last_record ='TRUE'
--Note : When validate Item - is restricted trigger you cannot use Navigational Trigger'
You modify your code as per above sample, ,in case if still you have not completed, post here.
Francois, I don't think Kevin Clarke’s method will return the record number where the other duplicate value lies.
That function only returns 0 or 1. And in the When-Validate-Item trigger, you only have the current Trigger_Record value.
In order to make the record number of the other row available, the function testing for duplicates needs to be passed the value to compare AND the number of the record containing the value. It would be simple if the COMPARE function could detect the record. However, I tried this once, and found that Trigger_Record remains that of the one where the When-Validate trigger is running. Calculation functions are not aware of the record on which they are running.
So, Gurujothi would need to keep a non-base-table numeric item in each record that actually holds the record number. This could be populated by Post-Query using :System.trigger_record. (Unfortuatley, the values would be thrown off if records are inserted by the user doing a "Create Record". I have no solution for that.)
The COMPARE function would need to be passed three values: The value in the single-row block, the value of the column being compared, and the record number corresponding to the column being compared. Then if the comparison values are equal, the function could return the record number, instead of 1. If values are unequal, it would return zero.
Then, the result would be zero if values are unique, or the record number of the other like value if the values are duplicate.
This would all work, until the user inserted a record in between existing rows, thereby making the record numbers in each row incorrect.
In key next item trigger write code like this i think it will help you
v_Text := :test;
V_REC := :SYSTEM.CURSOR_RECORD;
if v_text = :test AND V_REC <> :SYSTEM.CURSOR_RECORD then
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
-- v_Text := NULL;
WHEN FORM_TRIGGER_FAILURE THEN