DECLARE 
    alert_button     NUMBER; 
    v_alert          NUMBER; 
    v_message        VARCHAR2(1000); 
   v_count1         NUMBER; -->Added on 14-Feb-2014 
    v_count2         NUMBER; -->Added on 14-Feb-2014 
    v_count          NUMBER := 0; 
    --v_flag           VARCHAR2(1):='N';
    v_onhandquantity NUMBER;
   v_incrval     NUMBER;
     v_itemnumber  VARCHAR2(5000);
BEGIN
	 /**Checking for Item Number is assigned to BRW Or NOT*****/
	 BEGIN 
    Go_block('RHCLANDEDCOSTDET'); 
    first_record; 
    LOOP 
        IF :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN  
          SELECT Count(1) 
          INTO   v_count 
          FROM   regal.regal_inv_landed_cost_tab a1 
          WHERE  organization_id = 81 
                 AND inventory_item_id IN (SELECT inventory_item_id 
                                           FROM   mtl_system_items_kfv a2 
                                           WHERE a1.item_number = a2.concatenated_segments 
                                           --AND a1.inventory_item_id = a2.inventory_item_id 
                                           AND a2.organization_id = 82)
                 AND file_number = :RHCLANDEDCOSTDET.file_number 
                 AND item_number = :RHCLANDEDCOSTDET.item_number; 

          IF v_count = 0 THEN v_incrval := 1; 
            v_itemnumber := v_itemnumber ||:RHCLANDEDCOSTDET.item_number ||','; 
          END IF; 
        END IF; 
        next_record; 
        EXIT WHEN :SYSTEM.last_record = 'TRUE'; 
    END LOOP; 
    first_record; 

    IF v_incrval = 1 THEN 
      fnd_message.Set_string('This' ||Rtrim(v_itemnumber) ||' Number is not assigned into the BRW'); --testing  
      fnd_message.show; 
    END IF;   
	 END; 

    /****Checking for Item Number is Active or Not*******/
    BEGIN 
    Go_block('RHCLANDEDCOSTDET'); 
    first_record; 
    LOOP 
        IF :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN 
          SELECT Count(1) 
          INTO   v_count2 
          FROM   regal.regal_inv_landed_cost_tab a1 
          WHERE  organization_id = 81 
                 AND inventory_item_id IN (SELECT inventory_item_id 
                                           FROM   mtl_system_items_kfv a2 
                                           WHERE a1.item_number = a2.concatenated_segments 
                                           AND a2.organization_id = 82 
                                           AND Nvl(a2.inventory_item_status_code, 'Active' ) = 'Active') 
                 AND file_number = :RHCLANDEDCOSTDET.file_number 
                 AND item_number = :RHCLANDEDCOSTDET.item_number; 

          IF v_count2 = 0 THEN v_incrval := 1; 
            v_itemnumber := v_itemnumber ||:RHCLANDEDCOSTDET.item_number ||','; 
          END IF; 
        END IF; 
        next_record; 
        EXIT WHEN :SYSTEM.last_record = 'TRUE'; 
    END LOOP; 
    first_record; 

    IF v_incrval = 1 THEN 
      fnd_message.Set_string('This' ||Rtrim(v_itemnumber) ||' is in the Inactive Mode Please Active '); --testing  
      fnd_message.show; 
    END IF; 
	 end;	 
	  /****Checking ONHANDQUANTITY in the RHA(81 organization)*******/
	 BEGIN 
    Go_block('RHCLANDEDCOSTDET'); 
    first_record; 
    LOOP 
           IF :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN 
              SELECT NVL(SUM(transaction_quantity),0) ONHANDQUANTITY 
              INTO   v_onhandquantity 
              FROM   mtl_onhand_quantities 
              WHERE  inventory_item_id = :RHCLANDEDCOSTDET.inventory_item_id 
              AND organization_id = 81 
              AND subinventory_code = 'Vessel'; 
          IF v_onhandquantity = 0 THEN v_incrval := 1; 
            v_itemnumber := v_itemnumber ||:RHCLANDEDCOSTDET.item_number ||','; 
          END IF; 
        END IF; 
        next_record; 
        EXIT WHEN :SYSTEM.last_record = 'TRUE'; 
    END LOOP; 
    first_record; 

    IF v_incrval = 1 THEN       
      fnd_message.Set_string('This' ||Rtrim(v_itemnumber) ||' Does not have ONHANDQUANTITY in RHC');
      fnd_message.show; 
    END IF; 
	 end;	 
    	 
    --IF NVL(v_flag,'E') ='N' THEN 
    	  IF nvl(v_incrval,0)=0 then
            v_message := 'Are you sure,you want to move data into Brooklyn Warehouse?'; 

            Set_alert_property('BRW', alert_message_text, v_message); 

            alert_button := Show_alert('BRW'); 

            IF alert_button = alert_button1 THEN 
             commit_form; 

               BEGIN 
                  SELECT Count(1) 
                  INTO   v_count 
                  FROM   regal.regal_inv_landed_cost_tab 
                  WHERE  file_number = :RHCLANDEDCOSTHEADER.file_number 
                  AND selected_flag = 'Y' 
                  AND brw_processed = 'Y' 
                  AND orig_quantity_received = quantity_received; 
             END; 

               IF v_count != 0 THEN 
                  fnd_message.Set_string('You are selecting Processed Records. Please Check '); --testing  
                  fnd_message.show; 
               ELSE 
                  submit_inv_request; 
               END IF; 
           END IF;
       ELSE
          RAISE FORM_TRIGGER_FAILURE;
  END IF;

END;
   

v_message := 'Are you sure,you want to move data into Brooklyn Warehouse?'; 

IF v_count = 0 THEN v_incrval := 1; 
            v_itemnumber := v_itemnumber ||:RHCLANDEDCOSTDET.item_number ||','; 
END IF; 

IF v_count2 = 0 THEN v_incrval := 1; 
            v_itemnumber := v_itemnumber ||:RHCLANDEDCOSTDET.item_number ||',';   
    IF v_incrval = 1 THEN 
      fnd_message.Set_string('This' ||Rtrim(v_itemnumber) ||' is in the Inactive Mode Please Active '); --testing  
      fnd_message.show; 
    END IF; 

   v_itemnumber := v_itemnumber ||:RHCLANDEDCOSTDET.item_number ||',';   

v_itemnumber := :RHCLANDEDCOSTDET.item_number;