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_class_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 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('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 --客户头的该字段为空
--客户地点层为hz_cust_site_uses_all.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
, 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;
联系方式
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_ID关联owner_table_id
例子:
客户头层
SELECT con.*
FROM hz_parties hp
, hz_relationships rel
, 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;
客户地点层
SELECT *
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 关联表主键/PARTY_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.PARTY_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_CUSTOMER_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_id
FROM oe_order_headers_all h ;
--业务实体
SELECT org.NAME
FROM hr_organization_units org
WHERE org.organization_id = oe_order_headers_all.sold_from_org_id;
--客户
SELECT hz.party_name
FROM hz_cust_accounts acc
, hz_parties hz
WHERE 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 para
WHERE para.organization_id = oe_order_headers_all.ship_from_org_id;
SELECT *
FROM org_organization_definitions org
WHERE org.organization_id = oe_order_headers_all.ship_from_org_id;
--地点详细信息
SELECT loc.*
FROM hz_parties hp
, hz_party_sites hps
, hz_locations loc
WHERE hp.party_id = hps.party_id
AND hps.location_id = loc.location_id
AND hp.party_id = 5042;
--业务目的
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.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
, hz_cust_site_uses_all uses
, hz_party_sites hps
, hz_locations loc
, fnd_lookup_values flv
WHERE 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 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_number
FROM hz_contact_points phone
WHERE phone.owner_table_name = 'HZ_PARTY_SITES'
AND phone.owner_table_id = :hz_party_sites.party_sites_id
--联系人/地点层
SELECT hpsub.party_name
FROM hz_cust_account_roles hcar
, hz_relationships hr
, hz_parties hpsub
WHERE 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';
相关推荐
非常完整,根据ebs各个模块整理而成的数据字典,共Oracle ebs二次开发人员参考,不希望到处乱传,更不希望被CSDN拿来赚钱
ORACLE EBS R12 安装步骤详解,讲述ORACLE EBS R12的详细安装步骤,感觉不错,和大家一块分享
Oracle EBS R12中的配置文件及中文说明
Oracle EBS R12 升级 流程图
Oracle EBS R12系统管理员,内部培训交流
Oracle EBS R12与11i文件系统差异
EBS R12 解决方案: •案例介绍及Oracle工程制造整体解决方案; •Oracle项目制造解决方案; •Oracle采购解决方案; •Oracle设备管理解决方案; •Oracle财务解决方案; •Oracle R12产品趋势
oracle官方技术文档,trm文档,详细的ebs表结构描述
Oracle EBS R12 FIN,MFG,OM 三個模塊的新功能
Oracle EBS R12 FIN,MFG,OM 三個模塊的新功能介紹
oracle ebs r12 redhat as 4 安装.doc
ORACLE EBS R12.1.3 克隆详细步骤 该文档描述了Oracle 应用R12系统的克隆过程。 文档内容适用于所有12.x.x版本,例如12.0,12.0.4和12.1.x。在适用的部分,12.0.x系列统称为12.0版本,而12.1.x系列统称为12.1版本。...
ORACLE EBS R12各个模块的表结构
Oracle EBS R12安装手册 Solaris,作者:Nolem
oracle ebs r12 PRODϵͳװoracle ebs r12 PRODϵͳװ
1.1 采购物料接收 1.1.1 采购入库 1.1.2 补货入库 1.1.3 备品入库 1.1.4 还料入库 1.1.5 退补货入库 1.1.6 其它入库
Oracle EBS R12 DBA 原厂教材
LINUX下配置ORACLE EBS R12 LINUX下配置ORACLE EBS R12
oracle EBS 11i OM表结构说明(English)
Oracle EBS 财务模块表结构 PPT