This discussion is archived
12 Replies Latest reply: Mar 28, 2013 8:32 AM by MST RSS

How to get the original record Number in Multi-Record Block

Gurujothi Explorer
Currently Being Moderated
Hello Everyone,


I know how to find the duplicate item in the multi-Record block,

For Ex:
Line_Num            Item_Name             Quantity
1                           AA                      10
2                           BB                      20
3                           AA
Here 3rd record Item_name is duplicated, I can able to check and display the message that 'Item is duplicated' ,I found from [sheikyerbouti.developpez.com/duplicates/duplicates.htm] .

but I want to show along with original line number i.e 1 when the item_name is entered .

Here I want to check the original Line_Num and want to display the message

'Item is duplicated,Update quantity in Original Line 1'

Can anyone help me to get this?
Thank You.

Regards,
Guru.
  • 1. Re: How to get the original record Number in Multi-Record Block
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,
    rec := get_Block_Property( 'blk_name' CURRENT_RECORD);
    Francois
  • 2. Re: How to get the original record Number in Multi-Record Block
    Gurujothi Explorer
    Currently Being Moderated
    Hi Francois,

    Thank you for your reply,but I couldn't understand.Can you explain how it can be done.

    Regards,
    Guru.
  • 3. Re: How to get the original record Number in Multi-Record Block
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    The code I gave you returns in the rec variable the current record number.

    Francois
  • 4. Re: How to get the original record Number in Multi-Record Block
    Gurujothi Explorer
    Currently Being Moderated
    Hi Francois,

    I understood that but where I can use that code to check for the duplicated item's original line number.

    Thank You.

    Regards,
    Guru.
  • 5. Re: How to get the original record Number in Multi-Record Block
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    In the When-Validate-Record, the sample dialog use the message() built-in to display the error.

    Francois
  • 6. Re: How to get the original record Number in Multi-Record Block
    Gurujothi Explorer
    Currently Being Moderated
    Hi Francois,

    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
    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; 
    But I am getting the following error,
    FRM-40737:Illegal Restricted Procedure
    FIRST_RECORD in WHEN-VALIDATE-TRIGGER
    and then
    its showing for first line itself.
    
    Duplicate Item found.Update QTY in Original line number 1
    so I put the condition
    If :Lines.Line_num > 1 then --Only to check when the block having more than one record.
    but now it checking from second record and displaying,
    Duplicate Item found.Update QTY in Original line number 2 --(instead of Update QTY in Original line number 1)
    Can you tell me how can I change the above code for my requirement?

    Thank you.

    Edited by: Gurujothi on 27 Mar, 2013 5:20 PM
  • 7. Re: How to get the original record Number in Multi-Record Block
    indra budiantho Expert
    Currently Being Moderated
    hi, could be WHEN-TIMER-EXPIRED is the solution:

    http://www.orafaq.com/forum/t/91070/2/
  • 8. Re: How to get the original record Number in Multi-Record Block
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Look at the sample dialog shiiped with the article. You don't have to loop through the records. Code your message in the When-Validate-Record trigger.

    Francois
  • 9. Re: How to get the original record Number in Multi-Record Block
    O.Developer Journeyer
    Currently Being Moderated
    HI Gurujothi,

    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'
    LIke Below

    method 1:
    Let us take Scott Schema EMp Table.
    Declare

    Ex_nam Varchar2(100)l
    Begin

    select ename into ex_nam from emp where ename =:ename;

    If Ex_Nam is not null then Message('This Name is already available');
    -----your action
    End If;

    End;
    --Method 2; -  With Muli Record
    You have to add a Checkbox in the Emp block as non Database filed.
    Declare
         
         Ex_Nam varchar2(25);
         
    Begin
         Ex_Nam := :Emp.Ename;
         Go_Block('EMP');
         First_Record;
         Loop
              If :Ename = Ex_Nam
                   Then
                   Message('Name Already exist');
                   eXIT;
              End If;
              If :SYSTEM.last_record ='TRUE'
                   Then
                   exit;
              End if;
         End Loop;
         
    End;

    --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.
  • 10. Re: How to get the original record Number in Multi-Record Block
    user346369 Expert
    Currently Being Moderated
    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.
  • 11. Re: How to get the original record Number in Multi-Record Block
    879159 Newbie
    Currently Being Moderated
    In key next item trigger write code like this i think it will help you

    declare
         v_Text varchar2(20);
         V_REC NUMBER;
    BEGIN
         v_Text := :test;
         V_REC := :SYSTEM.CURSOR_RECORD;
    first_record;
    loop
         if v_text = :test AND V_REC <> :SYSTEM.CURSOR_RECORD then
         
              GO_RECORD(:SYSTEM.CURSOR_RECORD);
              RAISE FORM_TRIGGER_FAILURE;
         END IF;
         EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
         NEXT_RECORD;
    END LOOP;
    GO_RECORD(V_REC);NEXT_RECORD;
    --     v_Text := NULL;
    EXCEPTION
         WHEN FORM_TRIGGER_FAILURE THEN
         RAISE FORM_TRIGGER_FAILURE;
    END;
  • 12. Re: How to get the original record Number in Multi-Record Block
    MST Newbie
    Currently Being Moderated
    Please check below links

    duplicate records in  a multi record block
    http://sheikyerbouti.developpez.com/duplicates/duplicates.htm

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points