Oracle EBS R12 客戶表結(jié)構(gòu).doc

R12 客戶表結(jié)構(gòu)客戶表/聯(lián)系人/PARTY關(guān)聯(lián) HZ_PARTIES客戶賬戶表 HZ_CUST_ACCOUNTS例子： SELECT hp.party_number -客戶注冊標(biāo)識 , hp.party_name -組織名/客戶 , hp.known_as -別名 , anization_name_phonetic -名稱拼音 , acc.account_number -帳號 , flv_sale.meaning sales_channel_code -銷售渠道 , acc.account_name -賬記說明 , flv_customer.meaning customer_class_code-分類 , acc.orig_system_reference -參考 , flv_status.meaning status -狀態(tài) , flv_type.meaning customer_type -賬戶類型 , acc.attribute_category -上下文 , acc.attribute1 -注冊 , acc.attribute2 -人員推廣 , acc.attribute3 -特殊要求 , acc.Attribute4 -發(fā)貨單是否打印價格 , acc.Attribute5 -所屬利潤 FROM hz_parties hp , hz_cust_accountsacc , fnd_lookup_values flv_sale -銷售渠道 , fnd_lookup_values flv_customer-分類 , fnd_lookup_values flv_status -狀態(tài) , fnd_lookup_values flv_type -賬戶類型 WHERE hp.party_id = acc.party_id AND acc.sales_channel_code = flv_sale.lookup_code AND flv_sale.lookup_type = SALES_CHANNEL AND flv_sale.LANGUAGE = userenv(LANG) AND acc.customer_class_code= flv_customer.lookup_code AND flv_customer.lookup_type = CUSTOMER CLASS AND flv_customer.LANGUAGE = userenv(LANG) AND acc.status = flv_status.lookup_code AND flv_status.lookup_type = HZ_CPUI_REGISTRY_STATUS AND flv_status.LANGUAGE = userenv(LANG) AND acc.customer_type = flv_type.lookup_code AND flv_type.lookup_type = CUSTOMER_TYPE AND flv_type.LANGUAGE = userenv(LANG) AND hp.party_id = hz_parties.party_id;帳戶配置文件 HZ_CUSTOMER_PROFILES 字段 cust_account_role_id-oe_order_headers.sold_to_contract_id cust_account_id site_use_id -客戶頭的該字段為空 -客戶地點(diǎn)層為hz_cust_site_uses_all.site_use_id配置文件金額 HZ_CUST_PROFILE_AMTS -客戶頭層/客戶地點(diǎn)層 關(guān)聯(lián)：hz_customer_profiles.cust_account_profile_id客戶聯(lián)系人 HZ_CUST_ACCOUNT_ROLES -客戶頭層/地點(diǎn)層 cust_account_id cust_acct_site_id -頭層該字段為空 party_id -類型為 PARTY_RELATIONSHIP 的 PARTY_ID role_type -CONTACT 以頭層的聯(lián)系人為例 SELECT hp_per.* FROM hz_cust_account_roles rol , hz_parties hp_rel , hz_relationships rel , hz_parties hp_per WHERE rol.party_id = hp_rel.party_id AND hp_rel.party_id = rel.party_id AND rel.object_type = PERSON AND rel.relationship_code = CONTACT AND rel.object_id = hp_per.party_id AND rol.cust_acct_site_id IS NULL -頭層 AND rol.cust_account_id = hz_cust_accounts.cust_account_id;聯(lián)系方式 HZ_CONTACT_POINTS 字段 owner_table_name HZ_PARTIES/HZ_PARTY_SITES owner_table_id PARTY_ID/PARTY_SITE_ID 客戶地點(diǎn)層的聯(lián)系方式，直接用party_site_id 關(guān)聯(lián) owner_table_id 即可 客戶頭層的聯(lián)系方式，要用 HZ_RELATIONSHIPS 表轉(zhuǎn)換一下，與 hz_relationships.party_id 關(guān)聯(lián) 客戶聯(lián)系人下面的聯(lián)系方式，要用HZ_CUST_ACCOUNT_ROLES的PARTY_ID關(guān)聯(lián)owner_table_id 例子： 客戶頭層 SELECT con.* FROM hz_parties hp , hz_relationshipsrel , hz_contact_points con WHERE hp.party_id = rel.subject_id AND rel.subject_type = ORGANIZATION AND rel.party_id = con.owner_table_id AND con.owner_table_name = HZ_PARTIES AND hp.party_id = hz_parties.party_id; 客戶地點(diǎn)層 SELECT * FROM hz_contact_points con WHERE con.owner_table_id = hz_party_sites.party_site_id; 客戶聯(lián)系人下的聯(lián)系方式 SELECT * FROM hz_contact_points c WHERE c.owner_table_id = hz_cust_account_roles.party_id客戶的稅 HZ_CODE_ASSIGNMENTS 會計(jì)分類/客戶頭層/地點(diǎn)層 字段 OWNER_TABLE_NAME 關(guān)聯(lián)表名/ZX_PARTY_TAX_PROFILE OWNER_TABLE_ID 關(guān)聯(lián)表主鍵/PARTY_TAX_PROFILE_ID CLASS_CODE 會計(jì)分類代碼 ZX_PARTY_TAX_PROFILE 供應(yīng)商的稅的配置文件 字段 PARTY_TYPE_CODE 類型 THIRD_PARTY/THIRD_PARTY_SITE PARTY_ID 關(guān)聯(lián)表 HZ_PARTIES/HZ_PARTY_SITES 頭層: PARTY_TYPE_CODE = THIRD_PARTY AND PARTY_ID = HZ_PARTIES.PARTY_ID 地點(diǎn)層: PARTY_TYPE_CODE = THIRD_PARTY_SITE AND PARTY_ID = HZ_PARTY_SITES.PARTY_SITE_ID REP_REGISTRATION_NUMBER 納稅登記編號 PARTY_TAX_PROFILE_ID 主鍵 HZ_CLASS_CODE_DENORM 會計(jì)分類描述 ZX_EXEMPTIONS 客戶免稅/ 客戶頭層/地點(diǎn)層 字段 PARTY_TAX_PROFILE_ID 關(guān)聯(lián)ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID客戶地點(diǎn) HZ_PARTY_SITES地點(diǎn)地址 HZ_LOCATIONS客戶地點(diǎn)帳戶表 HZ_CUST_ACCT_SITES_ALL客戶地點(diǎn)業(yè)務(wù)目的 HZ_CUST_SITE_USES_ALL滯納費(fèi)用 HZ_CUSTOMER_PROFILES由銷售訂單分析客戶結(jié)構(gòu)SELECT h.sold_from_org_id -業(yè)務(wù)實(shí)體/ORG ID , h.sold_to_org_id -客戶 , h.ship_from_org_id -發(fā)貨倉庫 , h.ship_to_org_id -收貨方 , h.invoice_to_org_id , h.sold_to_contact_idFROM oe_order_headers_all h ;-業(yè)務(wù)實(shí)體 SELECT org.NAMEFROM hr_organization_units orgWHERE anization_id = oe_order_headers_all.sold_from_org_id; -客戶SELECT hz.party_nameFROM hz_cust_accounts acc , hz_parties hzWHERE acc.party_id = hz.party_id AND acc.cust_account_id = oe_order_headers_all.sold_to_org_id; -發(fā)貨倉庫SELECT para.Organization_Code,para.*FROM mtl_parameters paraWHERE anization_id = oe_order_headers_all.ship_from_org_id;SELECT *FROM org_organization_definitions orgWHERE anization_id = oe_order_headers_all.ship_from_org_id;-地點(diǎn)詳細(xì)信息SELECT loc.*FROM hz_parties hp , hz_party_sites hps , hz_locations locWHERE hp.party_id = hps.party_id AND hps.location_id = loc.location_id AND hp.party_id = 5042;-業(yè)務(wù)目的SELECT hp.party_name -客戶 , hp.party_number -注冊表標(biāo)識 , uses.site_use_code , acnt.account_number -賬號 , flv.meaning businesspurpose -業(yè)務(wù)目的 , uses.location -地點(diǎn) , acnt.account_name -帳戶說明 , decode(loc.address1,NULL,loc.address1,loc.address1 | ,) | decode(loc.city,NULL,loc.city,loc.city | ,) | decode(loc.state,NULL,loc.state,loc.state | ,) | decode(loc.postal_code,NULL, ,loc.postal_code) address -地點(diǎn)地址 , hps.party_site_number -地點(diǎn)說明 , uses.payment_term_id -付款條件 , site.cust_acct_site_id , acnt.cust_account_id , uses.site_use_id FROM hz_parties hp , hz_cust_accounts acnt , hz_cust_acct_sites_all site , hz_cust_site_uses_alluses , hz_party_sites hps , hz_locations loc , fnd_lookup_values flvWHERE hp.party_id = acnt.party_id AND acnt.cust_account_id = site.cust_account_id AND site.cust_acct_site_id = uses.cust_acct_site_id AND hps.party_site_id = site.party_site_id AND loc.location_id = hps.location_id AND uses.site_use_code = flv.lookup_code AND