R12客户表结构
《R12客户表结构》由会员分享,可在线阅读,更多相关《R12客户表结构(6页珍藏版)》请在装配图网上搜索。
1、R12 客户表结构客户表/联系人/PARTY关联 HZ_PARTIES客户账户表 HZ_CUST_ACCOUNTS例子: SELECT hp.party_number -客户注册标识 , hp.party_name -组织名/客户 , hp.known_as -别名 , hp.organization_name_phonetic -名称拼音 , acc.account_number -帐号 , flv_sale.meaning sales_channel_code -销售渠道 , acc.account_name -账记说明 , flv_customer.meaning customer_cl
2、ass_code-分类 , acc.orig_system_reference -参考 , flv_status.meaning status -状态 , flv_type.meaning customer_type -账户类型 , acc.attribute_category -上下文 , acc.attribute1 -注册 , acc.attribute2 -人员推广 , acc.attribute3 -特殊要求 , acc.Attribute4 -发货单是否打印价格 , acc.Attribute5 -所属利润 FROM hz_parties hp , hz_cust_accounts
3、acc , fnd_lookup_values flv_sale -销售渠道 , fnd_lookup_values flv_customer-分类 , fnd_lookup_values flv_status -状态 , 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
4、(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_t
5、ype = 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 -客户头的该字段为空 -客户地点层为hz_cust_site_uses_all
6、.site_use_id配置文件金额 HZ_CUST_PROFILE_AMTS -客户头层/客户地点层 关联:hz_customer_profiles.cust_account_profile_id客户联系人 HZ_CUST_ACCOUNT_ROLES -客户头层/地点层 cust_account_id cust_acct_site_id -头层该字段为空 party_id -类型为 PARTY_RELATIONSHIP 的 PARTY_ID role_type -CONTACT 以头层的联系人为例 SELECT hp_per.* FROM hz_cust_account_roles rol
7、, 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 =
8、hz_cust_accounts.cust_account_id;联系方式 HZ_CONTACT_POINTS 字段 owner_table_name HZ_PARTIES/HZ_PARTY_SITES owner_table_id PARTY_ID/PARTY_SITE_ID 客户地点层的联系方式,直接用party_site_id 关联 owner_table_id 即可 客户头层的联系方式,要用 HZ_RELATIONSHIPS 表转换一下,与 hz_relationships.party_id 关联 客户联系人下面的联系方式,要用HZ_CUST_ACCOUNT_ROLES的PARTY_I
9、D关联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; 客户地点层 SEL
10、ECT * FROM hz_contact_points con WHERE con.owner_table_id = hz_party_sites.party_site_id; 客户联系人下的联系方式 SELECT * FROM hz_contact_points c WHERE c.owner_table_id = hz_cust_account_roles.party_id客户的税 HZ_CODE_ASSIGNMENTS 会计分类/客户头层/地点层 字段 OWNER_TABLE_NAME 关联表名/ZX_PARTY_TAX_PROFILE OWNER_TABLE_ID 关联表主键/PAR
11、TY_TAX_PROFILE_ID CLASS_CODE 会计分类代码 ZX_PARTY_TAX_PROFILE 供应商的税的配置文件 字段 PARTY_TYPE_CODE 类型 THIRD_PARTY/THIRD_PARTY_SITE PARTY_ID 关联表 HZ_PARTIES/HZ_PARTY_SITES 头层: PARTY_TYPE_CODE = THIRD_PARTY AND PARTY_ID = HZ_PARTIES.PARTY_ID 地点层: PARTY_TYPE_CODE = THIRD_PARTY_SITE AND PARTY_ID = HZ_PARTY_SITES.PAR
12、TY_SITE_ID REP_REGISTRATION_NUMBER 纳税登记编号 PARTY_TAX_PROFILE_ID 主键 HZ_CLASS_CODE_DENORM 会计分类描述 ZX_EXEMPTIONS 客户免税/ 客户头层/地点层 字段 PARTY_TAX_PROFILE_ID 关联ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID客户地点 HZ_PARTY_SITES地点地址 HZ_LOCATIONS客户地点帐户表 HZ_CUST_ACCT_SITES_ALL客户地点业务目的 HZ_CUST_SITE_USES_ALL滞纳费用 HZ_CUSTOM
13、ER_PROFILES由销售订单分析客户结构SELECT h.sold_from_org_id -业务实体/ORG ID , h.sold_to_org_id -客户 , h.ship_from_org_id -发货仓库 , h.ship_to_org_id -收货方 , h.invoice_to_org_id , h.sold_to_contact_idFROM oe_order_headers_all h ;-业务实体 SELECT org.NAMEFROM hr_organization_units orgWHERE org.organization_id = oe_order_head
14、ers_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; -发货仓库SELECT para.Organization_Code,para.*FROM mtl_parameters paraWHERE para.organization_id = oe_order_headers_all
15、.ship_from_org_id;SELECT *FROM org_organization_definitions orgWHERE org.organization_id = oe_order_headers_all.ship_from_org_id;-地点详细信息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;
16、-业务目的SELECT hp.party_name -客户 , hp.party_number -注册表标识 , uses.site_use_code , acnt.account_number -账号 , flv.meaning businesspurpose -业务目的 , uses.location -地点 , acnt.account_name -帐户说明 , decode(loc.address1,NULL,loc.address1,loc.address1 | ,) | decode(loc.city,NULL,loc.city,loc.city | ,) | decode(loc
17、.state,NULL,loc.state,loc.state | ,) | decode(loc.postal_code,NULL, ,loc.postal_code) address -地点地址 , hps.party_site_number -地点说明 , 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 ,
18、 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.locatio
19、n_id AND uses.site_use_code = flv.lookup_code AND flv.lookup_type = SITE_USE_CODE AND flv.LANGUAGE = userenv(LANG) AND hp.party_id = 5042 AND hps.party_site_id = 3023;-联系人电话/地点层SELECT phone.phone_numberFROM hz_contact_points phoneWHERE phone.owner_table_name = HZ_PARTY_SITES AND phone.owner_table_id
20、 = :hz_party_sites.party_sites_id -联系人/地点层SELECT hpsub.party_nameFROM hz_cust_account_roleshcar , hz_relationships hr , hz_parties hpsubWHERE hcar.party_id = hr.party_id AND hr.subject_id = hpsub.party_id AND hcar.role_type = CONTACT AND hr.directional_flag = F AND hcar.cust_account_role_id = e_order_headers_all.sold_to_contact_id AND hpsub.status = A;
- 温馨提示:
1: 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
2: 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
3.本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
5. 装配图网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。