Refine a WHERE condition on ORACLE query to staging tables

I am looking for some assistance with refining a WHERE condition on a SQL query to a set of linked Oracle data staging tables.

One table is for case data, containing meta-data about the record. There is only one record per case on this table. There is a flag column on this table that marks the case data as Processed once the data is read and sent to another database. case_staging_table

CASE_NUMBER CASE_DETAILS PARENT_ID_NUMBER PROCESSED_FLAG
0001        First Case   X0001            NULL
0002        Second Case  X0002            NULL
0003        Third Case   X0003            Processed

The second table is for attachment data, and contains meta-data about the file attachments that are linked to the case data. There are possibly multiple records per case on this table. There is a flag column on this table that marks the attachment as Sent if the resulting file is transmitted to a server. attachment_staging_table

ID_NUMBER PARENT_ID_NUMBER ATTACHMENT_NAME               ATTACHMENT_SENT_FLAG
A0001     X0001            First Case Attachment 1.docx  N
A0002     X0001            First Case Attachment 2.docx  N
A0003     X0001            First Case Attachment 3.docx  Y
A0004     X0003            Third Case Attachment.docx    Y

What I need to do is construct a WHERE condition on a query where I only pull in case records where the case has NOT been Processed, and ALL attachment records contain a Y or a NULL in the ATTACHMENT_SENT_FLAG column (a NULL would be present if the case record contains no attachments). If any one of the related records in the attachment staging table has a N in the ATTACHMENT_SENT_FLAG column, I don’t want the case record to appear in the query results.

In this scenario :

• Case Number 0001 has not been processed, has three attachments associated with it, and only one of the attachments has been sent to the server.
• Case Number 0002 has not been processed, and has no attachments associated with it.
• Case Number 0003 has already been processed.

The results of the query at this stage should only include Case Number 0002, as it has not been processed and does not have to wait for any attachments. The query results should not contain Case Number 0001, as two of the attachments are not yet ready, and should not contain Case Number 0003, as it has already been processed.

My current query has the following WHERE condition:

SELECT 
    case.staging_table.CASE_NUMBER,
    case_staging_table.CASE_DETAILS,
    attachment_staging_table.ATTACHMENT_NAME
FROM
    case_staging_table
    LEFT JOIN attachment_staging_table 
        ON case_staging_table.PARENT-ID = attachment_staging_table.PARENT_ID
WHERE 
    case_staging_table.PROCESSED_FLAG IS NULL
    AND (
        attachment_staging_table.ATTACHMENT_SENT_FLAG = ‘Y’
        OR attachment_staging_table.ATTACHMENT_SENT_FLAG IS NULL
    )

This WHERE condition however would still process Case Number 0001 and flag it as Processed as soon as it hits the third attachment that was sent to the server.

Thank you in advance for your help!

Filter attachment_staging_table records which have ATTACHMENT_SENT_FLAG = 'N', like this:

SELECT 
    case_staging_table.CASE_NUMBER,
    case_staging_table.CASE_DETAILS,
    attachment_staging_table.ATTACHMENT_NAME
FROM
    case_staging_table
    LEFT JOIN attachment_staging_table 
        ON case_staging_table.PARENT_ID_NUMBER = attachment_staging_table.PARENT_ID_NUMBER
WHERE 
    case_staging_table.PROCESSED_FLAG IS NULL
    AND case_staging_table.PARENT_ID_NUMBER not in 
        ( select x.PARENT_ID_NUMBER
          from attachment_staging_table x      
          where x.ATTACHMENT_SENT_FLAG = 'N'
            )