WITH
      INV as (
         select INVOICE_CONTACT,
                to_char(regexp_substr(INVOICE_CONTACT,'[^;]+',1,LEVEL)) INVOICE_CONTACT FROM 
                (SELECT * FROM  XXPQ_IP_INVOICE_HEADER_EXT),table(cast(multiset(Select level from dual
connect by  level < regexp_count(INVOICE_CONTACT,';')-1)  as sys.odciNumberList)) where rownum =1 )
SELECT DISTINCT hdr.payer_number PAYER_NUMBER,ext.invoice_contact INVOICE_CONTACT
FROM XXPQ_IP_INVOICE_HEADER hdr,apps.ra_customer_trx_all ract,XXPQ_IP_INVOICE_HEADER_EXT ext
 WHERE  hdr.pq_customer_trx_id=ract.customer_trx_id
 AND    hdr.client_invoice_id=ext.client_invoice_id
 AND    RACT.CREATION_DATE>TRUNC(SYSDATE-1095)
 AND    ext.invoice_contact is not null
 and   hdr.payer_number='147690'

PAYER_NUMBER     INVOICE_CONTACT
 
12345            XXXX@gmail.com;YYY@gmail.com
12345            YYY@gmail.com