Query to find Customer Billto/Shipto Address and contact email id and Phone number and information

SELECT distinct

    hp.party_id,

    hca.cust_account_id,

    hcsua.site_use_id,

    hcasa.org_id,

    hca.account_number     customer_number,

    hp.party_name          customer_name,

    hca.attribute5,

    hcp.send_statements,

    hcp.dunning_letters    send_dunning_letters,

    hps.party_site_number  billto_shipto_location,

    hcsua.site_use_code,

    decode(hcsua.site_use_code,'BILL_TO',hl.address1

    || ' '

    || hl.address2

    || ' '

    || hl.city

    || ' '

    || hl.state

    || ' '

    || hl.postal_code

    || ' '

    || hl.country)     bill_to_address,

    decode(hcsua.site_use_code,'SHIP_TO',hl.address1

    || ' '

    || hl.address2

    || ' '

    || hl.city

    || ' '

    || hl.state

    || ' '

    || hl.postal_code

    || ' '

    || hl.country)     ship_to_address,

    translate(TRIM(hcasa.attribute3), 'x'

                                           || CHR(13)

                                           || CHR(9)

                                           || '|',

              'x')         invoice_billto_email_address,

    decode(hcasa.attribute2, 'E', decode(hcasa.attribute3, NULL, 'Postal Mail', 'Email'), 'Postal Mail')   delivery_method,          

    (

        SELECT

            LISTAGG(hcp.email_address, ' , ') WITHIN GROUP(

                ORDER BY

                    hcar.cust_account_id

            ) emild_list

        FROM

            hz_relationships        rel,

            hz_contact_points       hcp,

            hz_cust_account_roles   hcar,

            hz_role_responsibility  hrr

        WHERE

                1 = 1

            AND rel.object_id = hp.party_id

            AND rel.party_id = hcp.owner_table_id

            AND rel.relationship_type = 'CONTACT'

            AND rel.directional_flag = 'F'

            AND hcp.owner_table_name = 'HZ_PARTIES'

            AND hcar.cust_account_id = hca.cust_account_id

            AND hcar.party_id = rel.party_id

            AND hrr.cust_account_role_id = hcar.cust_account_role_id

            AND hrr.responsibility_type = 'DUN'

            AND hcp.contact_point_type = 'EMAIL'

    )                      dunning_email_address,

    (

        SELECT

            LISTAGG(hcp.raw_phone_number, ' , ') WITHIN GROUP(

                ORDER BY

                    hcar.cust_account_id

            ) phone_list

        FROM

            hz_relationships        rel,

            hz_contact_points       hcp,

            hz_cust_account_roles   hcar,

            hz_role_responsibility  hrr

        WHERE

                1 = 1

            AND rel.object_id = hp.party_id

            AND rel.party_id = hcp.owner_table_id

            AND rel.relationship_type = 'CONTACT'

            AND rel.directional_flag = 'F'

            AND hcp.owner_table_name = 'HZ_PARTIES'

            AND hcar.cust_account_id = hca.cust_account_id

            AND hcar.party_id = rel.party_id

            AND hrr.cust_account_role_id = hcar.cust_account_role_id

            AND hrr.responsibility_type = 'DUN'

            AND hcp.contact_point_type = 'PHONE'

    )                      dunning_phone

FROM

    hz_cust_accounts        hca,

    hz_parties              hp,

    hz_cust_acct_sites_all  hcasa,

    hz_cust_site_uses_all   hcsua,

    hz_party_sites          hps,

    hz_customer_profiles    hcp,

    hz_locations            hl

WHERE

        1 = 1

    AND hp.party_id = hca.party_id

    AND hca.status = 'A'

    AND hca.cust_account_id = hcasa.cust_account_id

    AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

    AND hcasa.org_id = hcsua.org_id

    AND hcasa.party_site_id = hps.party_site_id

    AND hp.party_id = hps.party_id

    AND hps.location_id = hl.location_id

    AND hca.cust_account_id = hcp.cust_account_id

    AND hcp.site_use_id IS NULL

    AND hcsua.site_use_code in ('BILL_TO','SHIP_TO')

    AND hcasa.status = 'A'

    AND hcsua.status = 'A'

    AND hps.status = 'A'

    AND hp.party_type = 'ORGANIZATION'

    AND hcasa.org_id IN ( 103 )

    --AND hca.account_number in ('1432072','1498470','1448442')--'2408'

    ;

Query to find the functional and usd amount for the ar invoice

 SELECT
    hca.account_number                                                       customer_number,
    hp.party_name                                                            customer_name,
    hca.attribute5                                                           customer_key,
    rcta.trx_number,
    rcta.trx_date,
    ar.amount_due_original,
    round(ar.amount_due_original * nvl(ar.exchange_rate, 1), 2)              functional_amount,
   --  ar.*
    round(ar.amount_due_original * decode(rcta.invoice_currency_code, 'USD', 1, nvl((
        SELECT
            gld.conversion_rate
        FROM
            gl_daily_rates gld
        WHERE
                gld.from_currency = ar.invoice_currency_code
            AND gld.to_currency = 'USD'
            AND gld.conversion_type = 'Corporate'
            AND trunc(rcta.trx_date) = trunc(gld.conversion_date)
            AND ROWNUM = 1
    ),
                                                                                    1)),
          2)                                                                 usd_amount
FROM
    hz_parties                hp,
    hz_cust_accounts          hca,
    ra_customer_trx_all       rcta,
    ar_payment_schedules_all  ar
WHERE
        1 = 1
    AND hp.party_id = hca.party_id
    AND rcta.bill_to_customer_id = hca.cust_account_id
    AND rcta.customer_trx_id = ar.customer_trx_id
    AND ar.class IN ( 'INV', 'CM', 'DM' )
    AND hp.status = 'A'
    AND hca.status = 'A'
    AND hp.party_type = 'ORGANIZATION'
    AND rcta.trx_date BETWEEN '01-JUL-2025' AND '30-JUN-2026'
    --AND rcta.trx_number = '40047438'
    AND hca.account_number IN ( '13357' );-- '1416594' );