Query to find letters sent to customers by Advanced collections/Dunning

Below query can be used for fetching the mails that were sent out to customers by advanced collections. You can add more columns if you need and change the date accordingly:

SELECT ixr.status,
       ixr.destination,
       ixr.creation_date,
       hp.party_name,
       hca.account_number,
       hcsa.cust_acct_site_id
  FROM IEX_XML_REQUEST_HISTORIES ixr,
       hz_cust_site_uses_all hcsua,
       hz_cust_acct_sites_all hcsa,
       hz_cust_accounts hca,
       hz_parties hp
 WHERE     ixr.creation_date > '10-AUG-2013'
       AND ixr.method = 'EMAIL'
       AND ixr.cust_site_use_id = hcsua.site_use_id
       AND hcsua.cust_acct_site_id = hcsa.cust_acct_site_id
       AND hcsa.cust_account_id = hca.cust_account_id
       AND hca.party_id = hp.party_id;