Custom Statement Program
Hi,
We had a custom statement program built. We expected it to be emailed to customer site contacts with the role STMTS. However, we noticed it's pulling in contacts that have been removed at the site level and/or account level contacts and we're struggling to know what flag we may be missing to stop that from occurring
SELECT DISTINCTBU_NAME,Number_A,Name,OUTPUT_NAME,location, sitename,CURRENCY,trans_number,Transaction_DATE,Transaction_type,ORIGINAL_AMOUNT,Charges,Credits,(LISTAGG(EMAIL_ADDRESS, ',') WITHIN GROUP (ORDER BY EMAIL_ADDRESS)) AS EMAIL_ADDRESSFROM(SELECT DISTINCTFABU.BU_NAMEBU_NAME,hca.account_number Number_A,SUBSTR(hp.party_name,1,50) Name,hp.PARTY_NAME || '_' || hca.ACCOUNT_NUMBER || '_'||hps.PARTY_SITE_NUMBER OUTPUT_NAME,hps.party_site_number location,hps.party_site_name sitename,PS.INVOICE_CURRENCY_CODE CURRENCY,ps.TRX_NUMBERtrans_number,TO_CHAR(ps.TRX_DATE,'YYYY-MM-DD') Transaction_DATE,DECODE(ps.class, 'INV', 'Invoice','PMT', 'Payment','CM','Credit memo',NULL) Transaction_type, PS.AMOUNT_DUE_ORIGINALORIGINAL_AMOUNT,(CASE WHEN ps.class IN ('INV','CM') THEN (PS.AMOUNT_DUE_ORIGINAL) ELSE NULL END) Charges,(CASE WHEN ps.class='PMT' THEN (PS.AMOUNT_DUE_ORIGINAL) ELSE NULL END) Credits,hcp.EMAIL_ADDRESSFROMAR_PAYMENT_SCHEDULES_ALL ps, FUN_ALL_BUSINESS_UNITS_V FABU,hz_cust_accounts hca,hz_parties hp,hz_relationships hr,hz_contact_points hcp, hz_cust_account_roles hcar,HZ_ROLE_RESPONSIBILITY HRR,HZ_CUSTOMER_PROFILES_FHCPF,hz_cust_acct_sites_all hcsa,hz_cust_site_uses_allsite_uses, hz_party_sites