EBS中客户表的关系

.EBS中客户表的关系 注：包括销售的客户，采购的供应商。hz_parties是比较基本的表，里面可以是组织，也可以是人，这里面只是存储了基本的信息，这些信息会被其它表shared, hz_relations表里是这些hz_parties之前的关系，里面用subject_id和object_id形成对应关系。HZ_PARTY_SITES里面有关于hz_parties的细节联系信息，如mailstop, addressee,但注意，这个表里面没有电话，邮箱等这些信息，这些信息是放在HZ_CONTACT_POINTS表里面的，在 HZ_CONTACT_POINTS表里用字段OWNER_TABLE_NAME/OWNER_TABLE_ID进行对应 在，OWNER_TABLE_NAME可能是hz_party_sites，也可能是hz_parties，这时候OWNER_TABLE_ID与之对应 的可能是PARTY_SITE_ID，也可能是party_id, hz_locations存有具体的address信息，hz_party_sites通过LOCATION_ID与之相关联。HZ_CUST_ACCOUNTS存有客户account信息，一个hz_party可能有多个accounts, hz_party和account的有关系用表HZ_CUST_ACCOUNT_ROLES表记录，HZ_CUST_ACCT_SITES_ALL存了account的所有site信息，我理解这个表是存了这些site可以有哪些作用，然后通过其 字段PARTY_SITE_ID关联到hz_party_sites, HZ_CUST_SITE_USES_ALL这个表是比较实用的，好像做OM时ship_to,bill_to，最终是用的这个表里面的信息，包括 account 科目信息,这个表里面同样没有address具体信息，是通过CUST_ACCT_SITE_ID关联到HZ_CUST_ACCT_SITES_ALL， 进而再关联到hz_party_sites-hz_locationsHZ_PERSON_PROFILES和HZ_ORGANIZATION_PROFILES分别是hz_parties里为person和organization时的补充信息，都是通过party_id相关联。我觉得这些表之前的关系还是比较复杂，还有其他一些表，在这里就不说了，觉得比较搞笑的是hz_contact_points这个表设计的比较精巧啊。我觉得搞这么多表，最主要应该是两个目的：1，灵活，2，减少冗余。整理了从Oracle官网上的表说明如下：/* -*/* - AR -*/* -*/*-客户题头表,The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person. This table focuses on business relationships and how transactions are conducted in the relationship.Since a party can have multiple customer accounts, this table might contain several records for a single party. For example, an individual person can establish a personal account, family account, and a professional account for a consulting practice. */select * from AR.HZ_CUST_ACCOUNTS;/*-客户地址表The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. */select * from AR.HZ_CUST_ACCT_SITES_ALL;/*-客户业务目的表 The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites.*/select * from AR.HZ_CUST_SITE_USES_ALL;/*The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. For example, you can store information such as Building 300, 500 Oracle Parkway, 94065, and Take the Ralston Avenue exit from highway 101, go east on Twin Dolphins Drive, turn left on Oracle Parkway, watch for the Building 300 sign on your right. Records in the HZ_LOCATIONS table can store delivery and postal information about a location through columns such as the LOCATION_DIRECTIONS, POST_OFFICE, and TIME_ZONE columns. You can also use the HZ_LOCATIONS table to store latitude and longitude information. Data in the HZ_LOCATIONS table is also used to determine the appropriate tax authority and tax rates for sales tax and VAT calculations.The primary key for this table is LOCATION_ID. */select * from AR.hz_locations;/*The HZ_PARTY_SITES table links a party (see HZ_PARTIES) and a location (see HZ_LOCATIONS. in hz_locations, there is no column of part_id) and stores location-specific party information such as MAILSTOP and ADDRESSEE. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. For example, 500 Oracle Parkway can be specified as a party site for Oracle Corporation. This party site can then be used for multiple customer accounts within the same party.The primary key for this table is PARTY_SITE_ID.*/select * from AR.HZ_PARTY_SITES;/* The HZ_PERSON_PROFILES table stores personal and family information about a party of the Person type. For example, this table could contain the correct spelling and phonetic pronunciation of the persons name. Note that some information in this table may also be entered into the HZ_PARTIES table.The primary key for this table is PERSON_PROFILE_ID. */select * from AR.HZ_PERSON_PROFILES;/*The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created. This table also contains data retrieved from Dun & Bradstreet using either the Dun & Bradstreet online or batch download methods. Historical data for the organization can also be stored in this table. Each time organization information is changed, the effective end date column for the original record is updated and a new record that contains the updated information is created.The primary key for this table is ORGANIZATION_PROFILE_ID.*/select * from AR.HZ_ORGANIZATION_PROFILES;/*The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name. The parties can be one of four types:bul:1Organization-for example, Oracle Corporationbul:1Person-for example, Jane Doebul:1Group-for example, World Wide Web Consortiumbul:1Relationship-for example, Jane Doe at Oracle Corporation.bodytextParty records can be created and updated using third party data sources such as Dun & Bradstreets Global Data Products. The HZ_PARTIES table contains denormalized information from the HZ_LOCATIONS, HZ_PERSON_PROFILES, HZ_CONTACT_POINTS, HZ_ORGANIZATION_PROFILES, and HZ_PERSON_LANGUAGE tables. The identifying address contained in the HZ_PARTIES table is denormalized from the HZ_LOCATIONS table.The primary key for this table is PARTY_ID. */select * from AR.HZ_PARTIES;/*The HZ_RELATIONSHIPS table stores information about relationships between one party and another party. The SUBJECT_ID and OBJECT_ID columns specify the relationship that exists between two parties. For example, if the party relationship is of the Parent Of type, a holding company could be the SUBJECT_ID in the relationship while one of its subsidiaries could be the OBJECT_ID. Creating a party contact causes a party relationship to be created. A party can have different relationships with one or more other parties. A party relationship can be specified with specific start and end dates so that the subject or object of a relationship can change over time.The primary key for this table is RELATIONSHIP_ID. */select * from AR.hz_relations;/*The HZ_ORG_CONTACTS table stores information about the position of the contact for a party or party site. The records in this table provide information about a contact position such as JOB_TITLE, RANK, and general contact information. This table is not used to store information about a specific person or organization, such as name and identification codes. That information is stored in the HZ_PARTIES table. For example, this table may include a record for the position of vice president of manufacturing that indicates that the contact is a senior executive, but it would not include the name of the person in that position, In this table, it contain the clumn PARTY_RELATIONSHIP_ID,which Identifies relationship that contact person has with an organization or person. Foreign key to the HZ_RELATIONSHIPS table.The primary key for this table is ORG_CONTACT_ID.*/ select * from AR.hz_org_cotacts;/*The HZ_CUST_ACCOUNT_ROLES table stores information about a role or function that a party performs in relation to a customer account. For example, Jane Doe might be a legal contact for Vision Corporation.The primary key f