EBS R12客戶資訊

--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_ID
SELECT * 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_ID
SELECT * 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_ID
SELECT *
  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中有幾個标準包擷取相關資訊的比較實用
EBS R12客戶資訊

繼續閱讀

Oracle EBS開發查找資料源（續）

FORM主從塊頭行金額彙總

ebs 11i利用DNS實作應用雙節點負載均衡

關于FND_PROFILE與FND_GLOBLE

AP 通過XLA 到 GL

虛拟機不能啟動，E_FAIL(0x80004005) 錯誤極其處理

AP invoice導入——擷取導入錯誤資訊

EBS中調用fnd_profile.value(GL_ACCESS_SET_ID)取不到值的情況

GL_BALANCE科目餘額表

EBS檢視EXCEL檔案亂碼，字元集問題

EBS值集,彈性域常用表

FRM-10102錯誤解決

EBS 12.2.6 應用層克隆過程

Oracle EBS 11i 表結構——PO子產品

EBS 此責任無可用函數，請聯系管理管理者
