Select Hp.party_name "Customer Name",--Customer name Hca.account_number "Customer Number",--customer number HCA.CUST_ACCOUNT_ID "Customer ID",--Customer CUST_ACC _id decode (Hca.customer_type, ' I ', ' internal customers ', ' R ', ' External clients ', Hca.customer_type) "Customer Type", Hca.customer_class_code "customer classification code", flv.meaning "Customer category", Hca.price_list_i D "Price List ID", cux_om_converged_pub.get_price_list_name (p_list_header_id = hca.price_list_id) "Price list", HP S.attribute1 "Stop", Hps.attribute2 "route", hps.party_site_id "Customer party_site_id",--Customer party_site_id H Csu.site_use_code "Customer location Use", Decode (hl.country, ' CN ', ' China ', hl.country) "Customer Country",--state hl.state "Customer province",--province Hl.city "Customer City",--City Hl.address1 "customer Address 1",--Address line 1 Hl.address2 "customer Address 2",--Address line 2 Amt.overall_credit_limit "letter With quotas ", hou.name" business entity ",----hp2.party_name" contact name ", decode (hl3.country, ' CN ', ' China ', Hl3.cou ntry) "Contact Country",--country Hl3.state "Contact Province",--province hl3.city "Contact City",--City Hl3.address1 "contact address 1",--Address line 1 Hl3.address2 "Contact Address 2"--Address Line 2 Hp3.primary_phone_number Contact Phone/* COUNT (1) */from-Customer hz_party_sites HPS,--Customer party_sites Hz_parties HP,--Customer party Hz_locations HL,--Customer locations hz_cust_accounts HCA, Hz_cust_site_uses_all HCSU, Hz_cust_acct_sites_all HCAs, hr_operating_units hou,----customer contact, contact us Hz_cust_account_roles Hcar, Hz_parties hp2,--Customer contact Party hz_parties hp3,--Contact us Party hz_relationships HR, Hz_locations hl3,--Customer contact Locations Hz_party_sites HPS 3,--Customer contact Party_sites hz_org_contacts Hoc,--only for display titles--Client Profile Hz_cust_profile_amts amt, H Z_customer_profiles Pro,---fnd_lookup_values_vl flv WHERE--Customer Information association hps.party_id = hp.party_id and Hps.locati on_id = hl.location_id AND hca.party_id = hps.party_id and hcsu.cust_acct_site_id = hcas.cust_acct_site_id and hcas.party_site_id = Hps.party_sit e_id and hcsu.org_id = hou.organization_id--Customer contact, contact Association and hca.cust_account_id = hcar.cust_account_id and Hcas.cust_ acct_site_id = hcar.cust_acct_site_id and Hcar.role_type = ' contact ' and hcar.party_id = hr.party_id and hp3.party_id = hr . party_id and hr.subject_id = hp2.party_id and Hr.directional_flag = ' F ' and hp2.party_type = ' person ' and Hoc.party_relat ionship_id = hr.relationship_id and hps3.party_id = hp3.party_id and hps3.location_id = hl3.location_id--Client Profile Association and AMT . cust_account_profile_id = pro.cust_account_profile_id and pro.cust_account_id = hca.cust_account_id---and flv.lookup _type = ' CUSTOMER CLASS ' and trunc (sysdate) between NVL (Flv.start_date_active, Trunc (sysdate)-1) and NVL (Flv.end_date_ac tive, sysdate) and Flv.enabled_flag = ' Y ' and flv.lookup_code = hca.customer_class_code;/* SELECT * from Hz_cust_accoun t_roles*/
Customer Information Full SQL