Issue with Reading All Order Numbers for Conditional Email Notification

Hello,

As a system requirement, I want to create an email that works as follows:

• If wanted receipt date - today = 1, the system should read all order numbers and send an email to the requesters of the orders where this condition is met.

However, I couldn’t figure out how to make the system read all order numbers.

The second approach I tried was to create an email directly using the following code, but it still doesn’t read all order numbers:

DECLARE 
      text_        VARCHAR2(32767);
      subject_     VARCHAR2(2000) := 'Teslim Tarihi Yaklaşıyor';
      attach_      VARCHAR2(2000); 
      wanted_date_ DATE;
      diff_days_   NUMBER;

BEGIN 

      -- Safely convert the date
      wanted_date_ := TRUNC(
         TO_DATE(SUBSTR('&NEW:WANTED_RECEIPT_DATE',1,10),'YYYY-MM-DD')
      );

      -- Calculate day difference
      diff_days_ := wanted_date_ - TRUNC(SYSDATE);

      -- If 1 day remains
      IF diff_days_ >= 1 THEN

            text_ := 'Sayın İlgili,';
            text_ := text_ || '<br><br>';
            text_ := text_ || '&NEW:REQUISITION_NO' || 
                     ' numaralı satın alma talebinin teslim tarihi yaklaşmaktadır.';
            text_ := text_ || '<br>';
            text_ := text_ || 'Teslim tarihine 3 gün kalmıştır.';
            text_ := text_ || '<br><br>';
            text_ := text_ || 'Bilginize sunulur.';

            command_sys.mail(
                        sender_      => 'Satın Alma',
                        from_        => '--',
                        to_list_     => 'barancelik@ahsap.com.tr',
                        subject_     => subject_,
                        text_        => text_,
                        attach_      => attach_,
                        rowkey_      => '',
                        mail_sender_ => 'MAIL_SENDER_HTML');

      END IF;

END;

My question is: How can I make the system read all order numbers and select only the ones that meet the condition?

Regards,
Baran