Select hpas1.party_site_number,hca.account_number,hca.party_id,hca.cust_account_id, hp.party_name, hp.party_number,hpas1.party_site_id,
  hcas1.cust_account_id,hcas1.cust_acct_site_id, hcsu.SITE_USE_CODE, hcsu.PRIMARY_FLAG, hcsu.STATUS, hca.ORIG_SYSTEM_REFERENCE,
  hpas1.location_id, hl.address1,hl.address2, hl.address3, hl.city,hl.postal_code,hl.state,
  hl.province,hl.country, hcsu.tax_code, hcsu.payment_term_id, rt.NAME payment_term_name,
  DECODE (   gcc.segment1
               || '.'
               || gcc.segment2
               || '.'
               || gcc.segment3
               || '.'
               || gcc.segment4
               || '.'
               || gcc.segment5
               || '.'
               || gcc.segment6
               || '.'
               || gcc.segment7,
               '......', NULL,
                  gcc.segment1
               || '-'
               || gcc.segment2
               || '-'
               || gcc.segment3
               || '-'
               || gcc.segment4
               || '-'
               || gcc.segment5
               || '-'
               || gcc.segment6
               || '-'
               || gcc.segment7
              ) receivable_account,
       NULL interco,
       DECODE (   gcc1.segment1
               || '.'
               || gcc1.segment2
               || '.'
               || gcc1.segment3
               || '.'
               || gcc1.segment4
               || '.'
               || gcc1.segment5
               || '.'
               || gcc1.segment6
               || '.'
               || gcc1.segment7,
               '......', NULL,
                  gcc1.segment1
               || '-'
               || gcc1.segment2
               || '-'
               || gcc1.segment3
               || '-'
               || gcc1.segment4
               || '-'
               || gcc1.segment5
               || '-'
               || gcc1.segment6
               || '-'
               || gcc1.segment7
              ) revenue_account,
       DECODE (  gcc2.segment1
               || '.'
               || gcc2.segment2
               || '.'
               || gcc2.segment3
               || '.'
               || gcc2.segment4
               || '.'
               || gcc2.segment5
               || '.'
               || gcc2.segment6
               || '.'
               || gcc2.segment7,
               '......', NULL,
                  gcc2.segment1
               || '-'
               || gcc2.segment2
               || '-'
               || gcc2.segment3
               || '-'
               || gcc2.segment4
               || '-'
               || gcc2.segment5
               || '-'
               || gcc2.segment6
               || '-'
               || gcc2.segment7
              ) freight_account

 
  from apps.hz_cust_accounts_all hca,
  apps.hz_parties hp,
  apps.hz_party_sites hpas1,
  apps.hz_cust_site_uses_all hcsu,
  apps.hz_cust_acct_sites_all hcas1,
  apps. hz_locations hl,
  apps.gl_code_combinations_kfv gcc,
       apps.gl_code_combinations_kfv gcc1,
       apps.gl_code_combinations_kfv gcc2,
       apps.ra_terms rt,
       apps.ra_terms rt1,
       apps.hz_customer_profiles hcp
      
   WHERE hca.party_id=hp.party_id
  AND hp.party_id=hpas1.party_id
  AND hpas1.party_id=hp.party_id
  AND hpas1.party_site_id=hcas1.party_site_id
  AND hcas1.cust_acct_site_id=hcsu.cust_acct_site_id
  AND hcsu.org_id='8069'
  AND hpas1.location_id=hl.location_id
  AND hcsu.gl_id_rec = gcc.code_combination_id(+)
   AND hcsu.gl_id_rev = gcc1.code_combination_id(+)
   AND hcsu.gl_id_freight = gcc2.code_combination_id(+)
  AND hcp.standard_terms = rt1.term_id(+)
  AND hcsu.payment_term_id = rt.term_id(+)
   AND hcsu.site_use_id = hcp.site_use_id(+)