Query To Get Customer Details with Additional Information

 This query will help you to get Customer Ship to and Bill to site address details along with Additional Information of Sites like Vat Registration No, Service Tax Reg. No etc...

SELECT   hou.NAME ou_name,hou.organization_id, hp.party_name customer_name,
         hp.party_number customer_number, hp.address1 cust_address1,
         hp.address2 cust_address2, hp.address3 cust_address3,
         hp.address4 cust_address4, hp.city cust_city, hp.state cust_state,
         hp.province cust_province, hca.pin_number cust_pin,
         hca.account_number, hca.customer_class_code customer_class,
         hcsu.LOCATION, hcsu.orig_system_reference site_number,
         hcsu.site_use_code,hcsua.cust_acct_site_id, hl.address1 site_address1,
         hl.address2 site_address2, hl.address3 site_address3,
         hl.address4 site_address4, hl.city site_city, hl.state site_state,
         hl.province site_province, hl.postal_code site_postal_code,
         jcca.excise_duty_range, jcca.excise_duty_division, jcca.excise_duty_reg_no,
         jcca.excise_duty_zone, jcca.excise_duty_region, jcca.excise_duty_circle,
         jcca.excise_duty_comm, jcca.ec_code, jcca.cst_reg_no, jcca.st_reg_no, jcca.vat_reg_no,
         jcca.service_tax_regno, jcca.pan_no, jcca.confirm_pan, jcca.tan_no
    FROM hz_parties hp,
         hz_party_sites hps,
         hz_cust_accounts hca,
         hz_locations hl,
         hz_cust_acct_sites_all hcsua,
         hz_cust_site_uses_all hcsu,
         hr_operating_units hou,
         jai_cmn_cus_addresses jcca
   WHERE hp.party_id = hca.party_id
     AND hps.party_id = hp.party_id
     AND hl.location_id = hps.location_id
     AND hps.party_site_id = hcsua.party_site_id
     AND hca.cust_account_id = hcsua.cust_account_id
     AND hcsua.cust_acct_site_id = hcsu.cust_acct_site_id
     AND hp.party_type = 'ORGANIZATION'
     AND hcsu.status = 'A'
     AND hcsu.org_id = hou.organization_id
     AND jcca.address_id(+) = hcsua.cust_acct_site_id
ORDER BY hcsu.LOCATION