12 Replies Latest reply: Mar 28, 2013 10:32 AM by MST RSS

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

    Gurujothi
      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çois Degrelle
          Hello,
          rec := get_Block_Property( 'blk_name' CURRENT_RECORD);
          Francois
          • 2. Re: How to get the original record Number in Multi-Record Block
            Gurujothi
            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çois Degrelle
              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
                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çois Degrelle
                  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
                    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
                      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çois Degrelle
                        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
                          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
                            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
                              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
                                Please check below links

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