Home » Developer & Programmer » Forms » Multi records block
Multi records block [message #608143] Mon, 17 February 2014 00:18 Go to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

I have multi records displayed block and if i select the 3 records from the 14 records and click on the find button , i want to show the 3 records detail ,means how to display the 3 records(how could i know?)

But in code only takes 1 item_number, how can i take 3 records and displayed specific details please help me.

BEGIN 
    SELECT Count(1) 
    INTO   v_count1 
    FROM   regal_inv_landed_cost_tab ril, 
           mtl_system_items mtk 
    WHERE  ril.inventory_item_id = mtk.inventory_item_id 
           AND ril.organization_id = mtk.organization_id 
           AND ril.organization_id IN( '81', '82' ) 
           AND Nvl(mtk.inventory_item_status_code, 'Inactive') = 'Inactive' 
           AND ril.item_number = :RHCLANDEDCOSTDET.item_number; 

    IF ( v_count1 > 0 ) THEN 
      v_alert := Show_alert('ACTIVE'); 

      RAISE form_trigger_failure; 
    END IF; 
END; 


Thank You
Re: Multi records block [message #608150 is a reply to message #608143] Mon, 17 February 2014 00:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One option is to create a (global temporary) table (let's call it GTT_ITEMS). Clicking the "Find" button would first insert ITEM_NUMBER that belong to selected items into that table; query you wrote would be then modified to
... and ri1.item_number in (select item_number from gtt_items)
Re: Multi records block [message #608158 is a reply to message #608150] Mon, 17 February 2014 01:23 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Thank You Littlefoot, it is working and how can i know what records are processed , suppose if i select 2 records , i want to show what records , mean 1 or both are processed .

I don't know very much i think use system.cursor_record. How to write a code.Please help
Re: Multi records block [message #608162 is a reply to message #608143] Mon, 17 February 2014 01:41 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
IF ( v_count1 > 0 ) THEN 
      v_alert := Show_alert('ACTIVE');
      :global.goitem := :SYSTEM.CURRENT_ITEM;
      --v_message:=('The Item Number is:'||:global.goitem); ----> Checking
      fnd_message.Set_string('The Item Number is:'||:global.goitem);--testing
      fnd_message.show;

      RAISE form_trigger_failure; 
    END IF;  


How this work for me? Please explain.
Re: Multi records block [message #608167 is a reply to message #608162] Mon, 17 February 2014 02:30 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What has that code got to do with what Littlefoot suggested?
Re: Multi records block [message #608168 is a reply to message #608167] Mon, 17 February 2014 02:38 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
What has that code got to do with what Littlefoot suggested?


What has that code got to do with what Littlefoot suggested?
Thaat is fine cookiemonster and i want to get the cursor item name when i click on the button and what items are selected or in the Inactive mode
Re: Multi records block [message #608198 is a reply to message #608150] Mon, 17 February 2014 05:26 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi ,

Can u please help me on this query.
I have only regal_inv_landed_cost_tab where organization_id=81 records.Columns are(organization_id,inventory_id)

I have mtl_system_items , with having records in organization_id=81 and 82 also. columns are (inventory_item_status_code,invenotry_id, organization_id)

My requirement is,i want Inactive,not exists and not in the both organization_id  in(81,82).

Then after i want to display the Alerts.


Re: Multi records block [message #608202 is a reply to message #608198] Mon, 17 February 2014 05:32 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't post the same question in mutiple threads.
Re: Multi records block [message #608203 is a reply to message #608202] Mon, 17 February 2014 05:33 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
sorry i forgot.. Smile
Re: Multi records block [message #608344 is a reply to message #608150] Tue, 18 February 2014 21:56 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Littlefoot,

SELECT count(1) 
        INTO    v_count1 
        FROM   mtl_system_items_kfv 
        WHERE  inventory_item_id IN(SELECT inventory_item_id 
                                    FROM   regal_inv_landed_cost_tab 
                                    WHERE  item_number = :RHCLANDEDCOSTDET.item_number 
                                           AND file_number = :RHCLANDEDCOSTHEADER.file_number
                                           AND a.inventory_item_id=b.inventory_item_id 
                                           AND selected_flag = 'Y') 
               AND organization_id = 207; 


1):RHCLANDEDCOSTDET.item_number , it takes only the current record, Suppose i want to take 3 or 4 records at a time , how can i modify this query.
2)If i remove this line ,item_number = :RHCLANDEDCOSTDET.item_number , it takes all the items.

But how it will works for the 4 or 5 records etc...

Thank You
Re: Multi records block [message #608348 is a reply to message #608344] Wed, 19 February 2014 00:04 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess that you have to mark these "3 or 4 records" somehow (for example, including a non-database checkbox item which would make your code switch from SQL to PL/SQL (a loop instead of a single SELECT).

What do you mean by "i want to show the 3 records detail"? Where would you show these details?
Re: Multi records block [message #608360 is a reply to message #608348] Wed, 19 February 2014 01:32 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Littlefoot,


item_number = :RHCLANDEDCOSTDET.item_number
AND file_number = :RHCLANDEDCOSTHEADER.file_number

If i pass the file_number it fetches the all the items related to the file_number

But, user pass file_number it fetches the all the items, but if i select the 3 or 4 items then, it shows only current item right?
Re: Multi records block [message #608369 is a reply to message #608360] Wed, 19 February 2014 04:13 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
SELECT count(1) 
        INTO    v_count 
        FROM   mtl_system_items_kfv a
        WHERE  inventory_item_id IN(SELECT inventory_item_id 
                                    FROM   regal.regal_inv_landed_cost_tab b
                                    WHERE  --item_number = :RHCLANDEDCOSTDET.item_number 
                                             file_number = :RHCLANDEDCOSTHEADER.file_number 
                                           AND selected_flag = 'Y') 
               AND organization_id = '82'; 
               
         

        IF v_count=0 THEN 
     
          fnd_message.Set_string('The'||' '||:RHCLANDEDCOSTDET.item_number ||'  '||'Item Number is not assigned into the BRW'); --testing
          fnd_message.show; 
          --RAISE form_trigger_failure;
        END IF; 



I want to validate all the selected items and print the messages , so and so Item Number is not assigned into the BRW'

Please help how to do this?
Re: Multi records block [message #608371 is a reply to message #608369] Wed, 19 February 2014 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
loop through the records using first_record/next_record.
Re: Multi records block [message #608384 is a reply to message #608371] Wed, 19 February 2014 05:30 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
loop through the records using first_record/next_record.


Could you please help me how to do this ?
Re: Multi records block [message #608385 is a reply to message #608384] Wed, 19 February 2014 05:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
first_record;

loop

<do stuff>

exit when :system.last_record = true;

next_record;

end loop;
Re: Multi records block [message #608387 is a reply to message #608385] Wed, 19 February 2014 05:36 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
So i have to write in the When-Button-Pressed Trigger right?
Re: Multi records block [message #608389 is a reply to message #608387] Wed, 19 February 2014 05:39 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
yes
Re: Multi records block [message #608413 is a reply to message #608389] Wed, 19 February 2014 08:40 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
BEGIN 
    first_record; 
    LOOP  
            SELECT Count(1) 
            INTO   v_count 
            FROM   mtl_system_items_kfv a 
            WHERE  inventory_item_id IN(SELECT inventory_item_id 
                                        FROM   xxc_inv_landed_cost_tab b 
                                        WHERE item_number = :Blockname.item_number  
                                         AND file_number = :Blockname.file_number 
                                         AND selected_flag = 'Y') 
                   AND organization_id = '82'; 

            IF v_count = 0 THEN 
              fnd_message.Set_string('This'||:Blockname.item_number||' Number is not assigned into the THS'); --testing 
              fnd_message.show; 
              --raise form_trigger_failure;
            --END IF; 
        --END; 
        Exit WHEN :System.last_record = 'TRUE'; 
        END IF;
        next_record; 
    END LOOP; 
END; 


I have 3 items per 1 file_number and i selected the all 3 and click on the Button.
It shows the last means(current slected) record only . I want to validate to all and display the message

Suppose in my 3 items ,2 items are not assigned into the THS, i want to show the alert the both(2 items are not assigned )

Validate one after the one shows the alerts.

Please help
  • Attachment: image1.png
    (Size: 9.11KB, Downloaded 729 times)

[Updated on: Wed, 19 February 2014 08:41]

Report message to a moderator

Re: Multi records block [message #608417 is a reply to message #608413] Wed, 19 February 2014 08:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
is the button in the datablock that's being validated? If not you need to add a go_block before the first_record.
Re: Multi records block [message #608452 is a reply to message #608417] Wed, 19 February 2014 22:15 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
is the button in the datablock that's being validated? If not you need to add a go_block before the first_record.


Yes it is working, But i want to show the Alerts to only selected items only & it validates all the items.

For examples, in my form i have 5 items and i selected 3 items and i want to validate these items only and show the Alerts.
Re: Multi records block [message #608465 is a reply to message #608452] Thu, 20 February 2014 02:37 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're looping through all the records in the block. Each record contains an item that tells you if the record needs checking.
You really shouldn't need me to point out the code you need to add.
Previous Topic: Print all records retrieved by the query find form
Next Topic: insert/update the values into the database tables automatically
Goto Forum:
  


Current Time: Thu Mar 28 20:14:59 CDT 2024