Delete customer records during merge process but save primary record with flag as Y in other table

I have a weird situation where I have one primary table where there is no flag to identify or mark the records for deletion, so I have to rely on the rowid. The table A looks like

Now when I search the details on my Oracle forms, I use ABCDEFGH and it will show me all the customers having matching records. So the output will be like above but with additional columns(This is my second table B) as

There is a third table C which stores only customer number and flags as below. Only one customer can be maintain.

I cannot delete the data from TableB as this is audit table and same applies for TableC.

I used min(rowid) to keep only the min rowid record for each customer_no||identity code||IdentityNumber where B.maintain = Yes and C.maintain = Y

cursor cr_save_Y is
      SELECT  y.n_cust_ref_no,y.v_iden_code,y.v_iden_no,MIN(z.rowid) row_idn
        FROM  TableC x,
              TableB   y,
              TableA z
        WHERE x.customer_no     = y.customer_no
        AND   x.n_merge_seq  = y.n_merge_seq
        AND   y.identitycode       = z.identitycode
        AND   y.identitynumber         = z.identitynumber
        AND   y.customer_no     = z.customer_no
        AND   NVL(y.v_maintain,'Y')='Y'     ---iden no to keep  
        AND   x.n_merge_cust_seq   = 1
GROUP BY y.customer_no,
      y.identitycode,
      y.identitynumber   ;

Then I am looping it like below

 for i in cr_save_Y
         loop
                  
            delete from TableA
            where customer_no = i.customer_no
            and identitycode = i.identitycode 
            and identityNumber = i.identityNumber 
            and rowid <> i.row_idn;
            
         end loop;  

Till here, its fine because if row_id does not matches, my records are saved. The issue comes with the next code of line. In the below code, I am deleting the data from TableA where if record is either maintain or remove=”Y”, fetch the record and delete the exact record.

delete from TableA
    where customer_no||identitycode||identitynumber in (select y.customer_no||y.identitycode||y.identitynumber
from TableC x,TableB y
where x.customer_no=y.customer_no
and x.n_merge_seq=y.n_merge_seq                                                 and nvl(y.maintain,'N')='N'                                                 and x.n_merge_seq=1 
and (x.maintain = 'Y' or x.remove="Y"); 

But as I cannot delete data from TableB, it still has record where 1234||passport||ABCDEFGH will be fetched as per the above query because tableB.maintain = N and TableC.maintain = Y. so this record will get matched to my tableA record 1234||passport||ABCDEFGH and this way, both the records are now deleted and I am left with nothing.

Lets assume, I write the code like below

delete from TableA
    where customer_no||identitycode||identitynumber in (select y.customer_no||y.identitycode||y.identitynumber
from TableC x,TableB y
where x.customer_no=y.customer_no
and x.n_merge_seq=y.n_merge_seq                                                 and nvl(y.maintain,'N')='N'                                                 and x.n_merge_seq=1 
and (x.remove="Y");

Then also, it will not solve the purpose in the below case

In this way, it will only fetch 5678||passport||ABCDEFGH and delete it, but my other record 1234||adhar||123456789123 will not be deleted.

Is there any way to satisfy both the scenerios? The above sql query fails because if I choose both maintain and remove, then I have fear of both records being deleted.

I need the output like below

Scenario 1:

Scenario 2:

You need to sign in to view this answers