Query to find customer Profile and Profile amount information

 SELECT DISTINCT
    hp.party_id,
    hp.party_number,
    hca.account_number,
    hp.party_name,
    hp.party_type,
    ac.name           collector_name,
    ac.description    collector_description,
    hcp.send_statements,
    hcp.statement_cycle_id,
    ascs.name         statement_cycle_name,
    hcp.dunning_letters,
    hcp.dunning_letter_set_id,
    adls.name         dunning_letter_set,
    hcpa.min_statement_amount,
    hcpa.min_dunning_amount,
    hcpa.currency_code,
    hca.cust_account_id
FROM
    hz_parties              hp,
    hz_cust_accounts        hca,
    hz_customer_profiles    hcp,
    hz_cust_profile_amts    hcpa,
    ar_statement_cycles     ascs,
    ar_dunning_letter_sets  adls,
    ar_collectors           ac
WHERE
        1 = 1
    AND hp.party_id = hca.party_id
    AND hca.cust_account_id = hcp.cust_account_id
    AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id (+)
    AND hcp.collector_id = ac.collector_id (+)
    AND hcp.statement_cycle_id = ascs.statement_cycle_id (+)
    AND hcp.dunning_letter_set_id = adls.dunning_letter_set_id (+)
    AND hcp.site_use_id IS NULL
    AND hcpa.site_use_id IS NULL
    AND hp.status = 'A'
    AND hca.status = 'A'
    AND hcp.status = 'A'
    AND hp.party_type = 'ORGANIZATION'
    AND hca.account_number = '1111111';