--1 HZ_CUST_ACCOUNTS(Cust_account_id & account_number & orig_system_reference) 客户编号SELECT * FROM HZ_CUST_ACCOUNTS HCA WHERE HCA.ACCOUNT_NUMBER = 'B80022701';--2 HZ_PARTIES(PARTY_ID & PARTY_NUMBER) 客户名称--根据1找到PARTY_IDSELECT * FROM HZ_PARTIES HP WHERE HP.PARTY_ID = 16367;--客户编号/名称信息获取SELECT HCA.CUST_ACCOUNT_ID      ,HCA.PARTY_ID      ,HCA.ACCOUNT_NUMBER      ,HP.PARTY_ID      ,HP.PARTY_NUMBER      ,HP.PARTY_NAME  FROM HZ_CUST_ACCOUNTS HCA      ,HZ_PARTIES       HP WHERE HCA.PARTY_ID = HP.PARTY_ID   AND HCA.ACCOUNT_NUMBER = 'B80022701';--3 HZ_PARTY_SITES(PARTY_SITE_ID & PARTY_SITE_NUMBER) 客户地点编号--根据1找到PARTY_IDSELECT * FROM HZ_PARTY_SITES HPS WHERE HPS.PARTY_ID = 16367;--4 HZ_LOCATIONS(LOCATION_ID) 客户详细地址--根据3找到LOCATION_ID然后与HZ_LOCATIONS关联得到详细地址SELECT * FROM HZ_LOCATIONS A;--客户编号/名称/地点/地址详细信息获取SELECT HCA.CUST_ACCOUNT_ID      ,HCA.PARTY_ID      ,HCA.ACCOUNT_NUMBER      ,HP.PARTY_ID      ,HP.PARTY_NUMBER      ,HP.PARTY_NAME      ,HPS.PARTY_SITE_ID      ,HPS.PARTY_SITE_NUMBER      ,HPS.LOCATION_ID      ,HL.ADDRESS1      ,HL.ADDRESS2      ,HL.ADDRESS3      ,HL.ADDRESS4  FROM HZ_CUST_ACCOUNTS HCA      ,HZ_PARTIES       HP      ,HZ_PARTY_SITES   HPS      ,HZ_LOCATIONS     HL WHERE HCA.PARTY_ID = HP.PARTY_ID   AND HP.PARTY_ID = HPS.PARTY_ID   AND HPS.LOCATION_ID = HL.LOCATION_ID   AND HCA.ACCOUNT_NUMBER = 'B80022701';--6 HZ_CUST_ACCT_SITES_ALL(CUST_ACCT_SITE_ID & ORIG_SYSTEM_REFERENCE/ORG_ID & TP_HEADER_ID) 客户收单收货方地点ID--根据1获取CUST_ACCOUNT_IDSELECT *  FROM HZ_CUST_ACCT_SITES_ALL HCASA WHERE HCASA.CUST_ACCOUNT_ID = 8343;--7 HZ_CUST_SITE_USES_ALL(SITE_USER_ID)  客户收单收货方详细信息--根据6获取CUST_ACCT_SITE_ID,同一CUST_ACCT_SITE_ID会得到收单方收货方两条记录，根据HZ_CUST_SITE_USES_ALL.SITE_USE_CODE来得到收单方（BILL_TO)/SHIP_TO(收货方）SELECT *  FROM HZ_CUST_SITE_USES_ALL HCSUA WHERE HCSUA.CUST_ACCT_SITE_ID = 10391;--8 根据收货地点获取当前收货地点的收单地点--根据7的收货地点中的BILL_TO_SITE_USE_ID关联HZ_CUST_SITE_USES_ALL的SITE_USE_ID获取收货地点收单方地点 SELECT * FROM HZ_CUST_SITE_USES_ALL HCSUA WHERE HCSUA.SITE_USE_ID = 12836;--客户编号/名称/地点/地址/收货地点/收单地点详细信息获取SELECT HCA.CUST_ACCOUNT_ID      ,HCA.PARTY_ID      ,HCA.ACCOUNT_NUMBER      ,HP.PARTY_ID      ,HP.PARTY_NUMBER      ,HP.PARTY_NAME      ,HPS.PARTY_SITE_ID      ,HPS.PARTY_SITE_NUMBER      ,HPS.LOCATION_ID      ,HL.ADDRESS1      ,HL.ADDRESS2      ,HL.ADDRESS3      ,HL.ADDRESS4      ,HCASA.CUST_ACCT_SITE_ID      ,HCSUA.CUST_ACCT_SITE_ID      ,HCSUA.SITE_USE_ID      ,HCSUA.SITE_USE_CODE      ,HCSUA.LOCATION --收货或收单地点      ,HCSUA1.LOCATION --收货方的收单方地点  FROM HZ_CUST_ACCOUNTS       HCA      ,HZ_PARTIES             HP      ,HZ_PARTY_SITES         HPS      ,HZ_LOCATIONS           HL      ,HZ_CUST_ACCT_SITES_ALL HCASA      ,HZ_CUST_SITE_USES_ALL  HCSUA      ,HZ_CUST_SITE_USES_ALL  HCSUA1 WHERE HCA.PARTY_ID = HP.PARTY_ID   AND HP.PARTY_ID = HPS.PARTY_ID   AND HPS.LOCATION_ID = HL.LOCATION_ID   AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID   AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID   AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID   AND HCSUA.BILL_TO_SITE_USE_ID = HCSUA1.SITE_USE_ID(+)   AND HCA.ACCOUNT_NUMBER = 'B80022701';--根据客户ID获取 客户层 的联系人信息   SELECT ACV.*  FROM AR_CONTACTS_V    ACV      ,HZ_CUST_ACCOUNTS HCA WHERE HCA.CUST_ACCOUNT_ID = ACV.CUSTOMER_ID   AND HCA.ACCOUNT_NUMBER = 'B80022701' ORDER BY ACV.STATUS         ,ACV.LAST_NAME;--根据客户编号获取客户层联系人及联系人电话信息SELECT ACV.REL_PARTY_ID      ,ACV.LAST_NAME      ,APV.OWNER_TABLE_ID      ,APV.COUNTRY_CODE      ,APV.AREA_CODE      ,APV.PHONE_ID      ,APV.PHONE_TYPE      ,APV.PHONE_TYPE_MEANING      ,APV.PHONE_NUMBER  FROM AR_CONTACTS_V    ACV      ,HZ_CUST_ACCOUNTS HCA      ,AR_PHONES_V      APV WHERE HCA.CUST_ACCOUNT_ID = ACV.CUSTOMER_ID   AND ACV.REL_PARTY_ID = APV.OWNER_TABLE_ID   AND HCA.ACCOUNT_NUMBER = 'B80022701' ORDER BY ACV.STATUS         ,ACV.LAST_NAME;--根据客户编号/名称信息获取客户层电话信息SELECT HCA.CUST_ACCOUNT_ID      ,HCA.PARTY_ID      ,HCA.ACCOUNT_NUMBER      ,HP.PARTY_ID      ,HP.PARTY_NUMBER      ,HP.PARTY_NAME      ,APV.*  FROM HZ_CUST_ACCOUNTS HCA      ,HZ_PARTIES       HP      ,AR_PHONES_V      APV WHERE HCA.PARTY_ID = HP.PARTY_ID   AND APV.OWNER_TABLE_ID(+) = HP.PARTY_ID   AND APV.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'   AND APV.PHONE_TYPE(+) NOT IN ('WEB'                                ,'EMAIL')   AND HCA.ACCOUNT_NUMBER = 'B80022701';--根据客户编号/名称/地点/地址详细信息获取地点层电话信息SELECT HCA.CUST_ACCOUNT_ID      ,HCA.PARTY_ID      ,HCA.ACCOUNT_NUMBER      ,HP.PARTY_ID      ,HP.PARTY_NUMBER      ,HP.PARTY_NAME      ,HPS.PARTY_SITE_ID      ,HPS.PARTY_SITE_NUMBER      ,HPS.LOCATION_ID      ,HL.ADDRESS1      ,HL.ADDRESS2      ,HL.ADDRESS3      ,HL.ADDRESS4      ,APV.PHONE_ID      ,APV.PHONE_NUMBER      ,APV.PHONE_TYPE      ,APV.PHONE_TYPE_MEANING  FROM HZ_CUST_ACCOUNTS HCA      ,HZ_PARTIES       HP      ,HZ_PARTY_SITES   HPS      ,HZ_LOCATIONS     HL      ,AR_PHONES_V      APV WHERE HCA.PARTY_ID = HP.PARTY_ID   AND HP.PARTY_ID = HPS.PARTY_ID   AND HPS.LOCATION_ID = HL.LOCATION_ID   AND APV.OWNER_TABLE_ID(+) = HPS.PARTY_SITE_ID   AND APV.OWNER_TABLE_NAME(+) = 'HZ_PARTY_SITES'   AND APV.PHONE_TYPE(+) NOT IN ('EMAIL'                                ,'WEB')   AND HCA.ACCOUNT_NUMBER = 'B80022701';--客户编号/名称/地点/地址/收货地点/收单地点详细信息获取SELECT HCA.CUST_ACCOUNT_ID      ,HCA.PARTY_ID      ,HCA.ACCOUNT_NUMBER      ,HP.PARTY_ID      ,HP.PARTY_NUMBER      ,HP.PARTY_NAME      ,HPS.PARTY_SITE_ID      ,HPS.PARTY_SITE_NUMBER      ,HPS.LOCATION_ID      ,HL.ADDRESS1      ,HL.ADDRESS2      ,HL.ADDRESS3      ,HL.ADDRESS4      ,HCASA.CUST_ACCT_SITE_ID      ,HCSUA.CUST_ACCT_SITE_ID      ,HCSUA.SITE_USE_ID      ,HCSUA.SITE_USE_CODE      ,HCSUA.LOCATION --收货或收单地点      ,HCSUA1.LOCATION --收货方的收单方地点  FROM HZ_CUST_ACCOUNTS       HCA      ,HZ_PARTIES             HP      ,HZ_PARTY_SITES         HPS      ,HZ_LOCATIONS           HL      ,HZ_CUST_ACCT_SITES_ALL HCASA      ,HZ_CUST_SITE_USES_ALL  HCSUA      ,HZ_CUST_SITE_USES_ALL  HCSUA1 WHERE HCA.PARTY_ID = HP.PARTY_ID   AND HP.PARTY_ID = HPS.PARTY_ID   AND HPS.LOCATION_ID = HL.LOCATION_ID   AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID   AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID   AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID   AND HCSUA.BILL_TO_SITE_USE_ID = HCSUA1.SITE_USE_ID(+)   AND HCA.ACCOUNT_NUMBER = 'B80022701';--根据客户编号/名称/地点获取地点层联系人信息SELECT HCA.CUST_ACCOUNT_ID      ,HCA.PARTY_ID      ,HCA.ACCOUNT_NUMBER      ,HP.PARTY_ID      ,HP.PARTY_NUMBER      ,HP.PARTY_NAME      ,HPS.PARTY_SITE_ID      ,HPS.PARTY_SITE_NUMBER      ,HPS.LOCATION_ID      ,HL.ADDRESS1      ,HL.ADDRESS2      ,HL.ADDRESS3      ,HL.ADDRESS4      ,HCASA.CUST_ACCT_SITE_ID      ,ACV.ADDRESS_ID      ,ACV.LAST_NAME  FROM HZ_CUST_ACCOUNTS       HCA      ,HZ_PARTIES             HP      ,HZ_PARTY_SITES         HPS      ,HZ_LOCATIONS           HL      ,HZ_CUST_ACCT_SITES_ALL HCASA      ,AR_CONTACTS_V          ACV WHERE HCA.PARTY_ID = HP.PARTY_ID   AND HP.PARTY_ID = HPS.PARTY_ID   AND HPS.LOCATION_ID = HL.LOCATION_ID   AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID   AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID   AND ACV.ADDRESS_ID(+) = HCASA.CUST_ACCT_SITE_ID   AND HCA.ACCOUNT_NUMBER = 'B80022701';--根据客户编号/名称/地点获取地点层联系人电话信息SELECT HCA.CUST_ACCOUNT_ID      ,HCA.PARTY_ID      ,HCA.ACCOUNT_NUMBER      ,HP.PARTY_ID      ,HP.PARTY_NUMBER      ,HP.PARTY_NAME      ,HPS.PARTY_SITE_ID      ,HPS.PARTY_SITE_NUMBER      ,HPS.LOCATION_ID      ,HL.ADDRESS1      ,HL.ADDRESS2      ,HL.ADDRESS3      ,HL.ADDRESS4      ,HCASA.CUST_ACCT_SITE_ID      ,ACV.ADDRESS_ID      ,ACV.LAST_NAME      ,ACV.REL_PARTY_ID      ,APV.OWNER_TABLE_ID      ,APV.PHONE_ID      ,APV.PHONE_NUMBER      ,APV.PHONE_TYPE      ,APV.PHONE_TYPE_MEANING      ,APV.PRIMARY_FLAG  FROM HZ_CUST_ACCOUNTS       HCA      ,HZ_PARTIES             HP      ,HZ_PARTY_SITES         HPS      ,HZ_LOCATIONS           HL      ,HZ_CUST_ACCT_SITES_ALL HCASA      ,AR_CONTACTS_V          ACV      ,AR_PHONES_V            APV WHERE HCA.PARTY_ID = HP.PARTY_ID   AND HP.PARTY_ID = HPS.PARTY_ID   AND HPS.LOCATION_ID = HL.LOCATION_ID   AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID   AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID   AND ACV.ADDRESS_ID(+) = HCASA.CUST_ACCT_SITE_ID   AND ACV.REL_PARTY_ID = APV.OWNER_TABLE_ID   AND APV.OWNER_TABLE_NAME = 'HZ_PARTIES'   AND APV.PHONE_TYPE NOT IN ('EMAIL'                             ,'WEB')   AND HCA.ACCOUNT_NUMBER = 'B80022701';--客户与销售订单头关联SELECT HCSUA.LOCATION SHIP_TO_LOCATION --OM界面 "收货地点"      ,HL.ADDRESS1 SHIP_TO_ADDRESS1 --OM界面 收货地点一      ,HL.ADDRESS2 SHIP_TO_ADDRESS2 --OM界面 收货地点二      ,DECODE(HL.CITY             ,NULL             ,NULL             ,HL.CITY || ', ') ||       DECODE(HL.STATE             ,NULL             ,HL.PROVINCE || ', '             ,HL.STATE || ', ') ||       DECODE(HL.POSTAL_CODE             ,NULL             ,NULL             ,HL.POSTAL_CODE || ', ') ||       DECODE(HL.COUNTRY             ,NULL             ,NULL             ,HL.COUNTRY) SHIP_TO_ADDRESS5 --OM界面 收单地点五             ,HCSUA1.LOCATION INVOICE_TO_LOCATION --OM界面 "收单地点"      ,HL1.ADDRESS1 INVOICE_TO_ADDRESS1 --OM界面 收单地点一      ,HL1.ADDRESS2 INVOICE_TO_ADDRESS2 --OM界面 收单地点二      ,DECODE(HL1.CITY             ,NULL             ,NULL             ,HL1.CITY || ', ') ||       DECODE(HL1.STATE             ,NULL             ,HL1.PROVINCE || ', '             ,HL1.STATE || ', ') ||       DECODE(HL1.POSTAL_CODE             ,NULL             ,NULL             ,HL1.POSTAL_CODE || ', ') ||       DECODE(HL1.COUNTRY             ,NULL             ,NULL             ,HL1.COUNTRY) INVOICE_TO_ADDRESS5 --OM界面 收单地点五  FROM OE_ORDER_HEADERS_ALL   OOH      ,HZ_CUST_SITE_USES_ALL  HCSUA      ,HZ_CUST_ACCT_SITES_ALL HCASA      ,HZ_PARTY_SITES         HPS      ,HZ_LOCATIONS           HL      ,HZ_CUST_SITE_USES_ALL  HCSUA1      ,HZ_CUST_ACCT_SITES_ALL HCASA1      ,HZ_PARTY_SITES         HPS1      ,HZ_LOCATIONS           HL1 WHERE OOH.ORDER_NUMBER = '833023055'   AND OOH.SHIP_TO_ORG_ID = HCSUA.SITE_USE_ID   AND HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID   AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID   AND HPS.LOCATION_ID = HL.LOCATION_ID   AND OOH.INVOICE_TO_ORG_ID = HCSUA1.SITE_USE_ID   AND HCSUA1.CUST_ACCT_SITE_ID = HCASA1.CUST_ACCT_SITE_ID   AND HCASA1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID   AND HPS1.LOCATION_ID = HL1.LOCATION_ID;--客户信息与应收发票头关联  直接查标准VIEW： RA_CUSTOMER_TRX_PARTIAL_V 看关联关系，当前VIEW中有几个标准包获取相关信息的比较实用