AR Customer Master Query

SELECT   hca.account_number   --,cust.party_id
       --  (select ORGANIZATION_NAME from org_organization_definitions where OPERATING_UNIT = hcsu.org_id) org
        ,customer_name,
         (   hl.address1
          || '  '
          || hl.address2
          || ' '
          || hl.address3
          || ' '
          || hl.address4)
            ship_to,
         hl.city,
         hl.country,
         (SELECT   hcp.raw_phone_number
            FROM   hz_contact_points hcp, hz_relationships hr, hz_parties hzp
           WHERE       hcp.owner_table_id = hr.party_id
                   AND hr.object_id = NVL (cust.party_id, hr.object_id)
                   AND hr.status = 'A'
                   AND hcp.status = 'A'
                   AND hcp.primary_flag = 'Y'
                   AND hzp.party_id = hr.party_id
                   AND hcp.phone_line_type = 'EMAIL'
                   AND hcp.owner_table_name = 'HZ_PARTIES')
            email,
         (SELECT   description
            FROM   ar_customer_profiles_v a, ra_terms b
           WHERE       customer_id = NVL (f.customer_id, customer_id)
                   AND status = 'A'
                   AND site_use_id IS NULL
                   AND a.standard_terms = b.term_id)
            cust_profile,
        -- hcsu.org_id,
         xyka_custom_yka.get_ar_balance_yka (customer_id) balance,
         xyka_custom_yka.get_ar_overall_cr_limit (customer_id) cr_limit,
         xyka_custom_yka.get_ar_customer_category_name (customer_id)
            customer_category,
         xyka_custom_yka.get_ar_credit_check (customer_id, 126) credit_check, -- org_id
         xyka_custom_yka.get_ar_collector_code (customer_id, 126) collector,
         (SELECT   MAX (name) collector_id
            --into l_collector_code
            FROM   ar_customers a, hz_customer_profiles b, ar_collectors c
           WHERE       a.customer_id = b.cust_account_id
                   AND a.customer_id = b.cust_account_id
                   AND b.site_use_id IS NULL
                   AND b.collector_id = c.collector_id
                   AND a.customer_id = f.customer_id)
            collector_name,
         (SELECT   MAX (credit_checking)
            FROM   hz_customer_profiles hcp
           WHERE   hcp.cust_account_id = f.customer_id)            cr_chk ,
           (select segment5 from gl_code_combinations where code_combination_id = hcsu.GL_ID_REC ) gcc
  FROM   hz_parties cust,
         hz_cust_accounts hca,
         hz_cust_acct_sites_all hcas,
         hz_cust_site_uses_all hcsu,
         hz_party_sites hps,
         hz_locations hl,
         ar_customers f
 WHERE       1 = 1
         AND hca.cust_account_id = hcas.cust_account_id
         AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
         AND hps.location_id(+) = hl.location_id
         AND hps.party_site_id = hcas.party_site_id
         AND cust.party_id = hca.party_id
         AND f.customer_id = hca.cust_account_id
         --and hcsu.primary_flag = 'y'
         AND hcsu.status = 'A'
         AND hcsu.site_use_code = 'BILL_TO'
        -- AND hca.cust_account_id = 7168
         AND hcsu.org_id = NVL (:org_id, hcsu.org_id)
         and hca.account_number like 'S%'
         order by 1