Downstream mining database archivelog management based on DBA_REGISTERED_ARCHIVED_LOG.purgeable colu

I have multiple downstream extract for different databases, The source archivelogs are kept out of FRA.

we see the DBA_REGISTERED_ARCHIVED_LOG.purgeable  is not being papulated to "YES", that is failing our purging of archivelogs.

I use following SQL to find out the archivelogs to be deleted.

select trim(r.name)

        FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c

        WHERE r.CONSUMER_NAME = c.CAPTURE_NAME

        and r.source_database = c.source_database

        and r.next_scn < (select  min(required_checkpoint_scn) as MINSCN

         from dba_capture D where capture_type='DOWNSTREAM' and D.source_database=C.source_database