You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register
Skip to content
  • Register

    • Custom Statement Program

    in Receivables & Collections 2 comments

    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

    Tagged:

    Howdy, Stranger!

    Log In

    To view full details, sign in.

    Register

    Don't have an account? Click here to get started!