CREATE OR REPLACE PACKAGE sample_param_pkg
IS
   PROCEDURE proc1 (p_order_no IN VARCHAR2 DEFAULT NULL);

   PROCEDURE xxc_item_id (v_inventory_item_id   IN     NUMBER,
                                         v_item_id                OUT NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY sample_param_pkg
IS
   PROCEDURE proc1 (p_order_no IN VARCHAR2 DEFAULT NULL)
   IS
      CURSOR c_1 ( v_order_number VARCHAR2)
      IS
           SELECT b.header_id, a.order_number, b.inventory_item_id
             FROM oe_order_headers_all a, 
                       oe_order_lines_all b
            WHERE   a.header_id = b.header_id
                  AND a.OPEN_FLAG = 'Y'
                  AND TO_CHAR (a.order_number) = v_order_number
         GROUP BY b.header_id, order_number, b.inventory_item_id
           HAVING COUNT (*) > 18;
   BEGIN
      FOR c_rec IN c_1 (p_order_no)
      LOOP
         xxc_item_id (c_rec.inventory_item_id, v_item_id);

         IF NVL (v_item_id, 0) > 0
         THEN
            DBMS_OUTPUT.put_line ('Item Id is already Existing');
         END IF;
      END LOOP;
   END ;
PROCEDURE  xxc_item_id (v_inventory_item_id  in   NUMBER,v_item_id out NUMBER)
IS
BEGIN
    SELECT inventory_item_id
      INTO v_item_id
      FROM mtl_system_items
     WHERE inventory_item_id = v_inventory_item_id;
End;
END;

Execute command
--------------------
SQL> exec sample_param_pkg.proc1

PL/SQL procedure successfully completed.

SQL>  SELECT  b.header_id, a.order_number
  2     FROM  oe_order_headers_all a, 
  3               oe_order_lines_all b
  4    WHERE a.header_id = b.header_id
  5       AND  a.OPEN_FLAG = 'Y'
  6   -- AND TO_CHAR (a.order_number) = v_order_number
  7     GROUP BY b.header_id, order_number
  8        HAVING COUNT (*) > 15;

 HEADER_ID ORDER_NUMBER
---------- ------------
    171366        80034
     49522        85040
    171379        80038
     49520        85038

SQL> SELECT  b.header_id, a.order_number,b.inventory_item_id
  2     FROM  oe_order_headers_all a, 
  3               oe_order_lines_all b
  4    WHERE a.header_id = b.header_id
  5       AND  a.OPEN_FLAG = 'Y'
  6   -- AND TO_CHAR (a.order_number) = v_order_number
  7     GROUP BY b.header_id, order_number,b.inventory_item_id
  8        HAVING COUNT (*) > 15;

no rows selected

   PROCEDURE proc1 (p_order_no IN VARCHAR2 DEFAULT NULL);

 exec sample_param_pkg.proc1 , no arguments passing , so it will call xxc_item_id  procedure and display the message is this correct??

select b.header_id, a.order_number, b.inventory_item_id
FROM oe_order_headers_all a, 
  oe_order_lines_all b
WHERE a.header_id = b.header_id
  AND a.OPEN_FLAG = 'Y'
  AND (b.header_id, order_number) in (<the first aggregate query>);