Query to find customer number, site number, transaction type and other details from AR Invoice

 SELECT

    hou.name              ou_name,

    rcta.org_id,

    hp.party_id,

    --hp.party_number,

    hp.party_name,

    hp.party_type,

    hca.account_number    customer_number,

    hps.party_site_number,

    rcta.customer_trx_id,

    rcta.trx_number,

    rcta.trx_date,

    apsa.class,

    rctta.name            transaction_type_name,

    rctta.type            transaction_type,

    apsa.status,

    rcta.printing_original_date,

    rcta.printing_last_printed,

    rcta.printing_count,

    rcta.last_printed_sequence_num,

    rcta.creation_date,

    rcta.last_update_date,

    rcta.printing_pending,

    rcta.complete_flag,

    apsa.amount_due_original,

    apsa.amount_due_remaining,

    apsa.invoice_currency_code,

    hca.cust_account_id,

    rcta.bill_to_site_use_id

   -- apsa.*

FROM

    ra_customer_trx_all       rcta,

    ar_payment_schedules_all  apsa,

    ra_cust_trx_types_all     rctta,

    hz_parties                hp,

    hz_cust_accounts          hca,

    hz_cust_acct_sites_all    hcasa,

    hz_cust_site_uses_all     hcsua,

    hz_party_sites            hps,

    hr_operating_units        hou

WHERE

        1 = 1

--AND trx_number IN ( '50833855' )

    AND rcta.org_id = hou.organization_id

    AND apsa.customer_trx_id = rcta.customer_trx_id

    AND rcta.cust_trx_type_id = rctta.cust_trx_type_id

    AND rcta.org_id = rctta.org_id

    AND rcta.bill_to_customer_id = hca.cust_account_id

    AND rcta.bill_to_customer_id = hcasa.cust_account_id

    AND hca.cust_account_id = hcasa.cust_account_id

    AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

    AND rcta.bill_to_site_use_id = hcsua.site_use_id

    AND hcasa.party_site_id = hps.party_site_id

    AND hp.party_id = hca.party_id

    AND hp.party_id = hps.party_id

    AND hcsua.site_use_code = 'BILL_TO'

ORDER BY

rcta.org_id,

    rcta.creation_date DESC