Customer Invoice Payment

SELECT
    apsa.cash_receipt_id,
    (
        SELECT
            party_name
        FROM
            hz_cust_accounts,
            hz_parties
        WHERE
                hz_cust_accounts.party_id = hz_parties.party_id
            AND cust_account_id = apsa.customer_id
    )                                                   customer_name,
    (
        SELECT
            account_number
        FROM
            hz_cust_accounts
        WHERE
            cust_account_id = apsa.customer_id
    )                                                   customer_account_number,
    trx.trx_number                                      invoice_num,
    trx.trx_date                                        invoice_date,
    apsa.gl_date_closed                                 payment_date,
    (
        SELECT
            personname.display_name
        FROM
            hz_customer_profiles_f hcp,
            per_person_names_f_v   personname
        WHERE
                hcp.cust_account_id = apsa.customer_id
            AND hcp.credit_analyst_id = personname.person_id
            AND trunc(sysdate) BETWEEN hcp.effective_start_date AND hcp.effective_end_date
            AND hcp.site_use_id IS NULL
            AND ROWNUM < 2
    )                                                   credit_analyst,
    ar_receivable_applications_all.amount_applied * - 1 cash_collected
FROM
    ar_payment_schedules_all apsa,
    ra_customer_trx_all,
    ar_receivable_applications_all,
    ra_customer_trx_all      trx
WHERE
        1 = 1
    AND apsa.customer_trx_id = ra_customer_trx_all.customer_trx_id (+)
    AND ar_receivable_applications_all.cash_receipt_id = apsa.cash_receipt_id
    AND apsa.status = 'CL'
    AND apsa.class = 'PMT'
    AND ar_receivable_applications_all.display = 'Y'
    AND ar_receivable_applications_all.status = 'APP'
    AND ar_receivable_applications_all.applied_customer_trx_id = trx.customer_trx_id