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; 

... and ri1.item_number in (select item_number from gtt_items)

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;  

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.

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; 

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; 

first_record;

loop

<do stuff>

exit when :system.last_record = true;

next_record;

end loop;

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;