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-------------------------------------------------------------------*//*---------------------------------------------------------*//*--客户题头表,TheHZ_CUST_ACCOUNTStablestoresinformationaboutcustomeraccounts,orbusinessrelationshipsthatthedeployingcompanyestablisheswithapartyoftypeOrganizationorPerson.Thistablefocusesonbusinessrelationshipsandhowtransactionsareconductedintherelationship.Sinceapartycanhavemultiplecustomeraccounts,thistablemightcontainseveralrecordsforasingleparty.Forexample,anindividualpersoncanestablishapersonalaccount,familyaccount,andaprofessionalaccountforaconsultingpractice.*/select*fromAR.HZ_CUST_ACCOUNTS;/*--客户地址表TheHZ_CUST_ACCT_SITES_ALLtablestoresallcustomeraccountsitesacrossalloperatingunits.Customeraccountsitesareaddresses,forcustomeraccounts,wherethedeployingcompanydoesbusinesswithitscustomers.Onecustomeraccountcanhavemultiplecustomeraccountsites,andcustomeraccountsitesforonecustomeraccountcanbelongtomultipleoperatingunits.*/select*fromAR.HZ_CUST_ACCT_SITES_ALL;/*--客户业务目的表TheHZ_CUST_SITE_USES_ALLtablestoresbusinesspurposesassignedtocustomeraccountsites,forexampleBill-To,Ship-To,andStatements.Eachcustomeraccountsitecanhaveoneormorepurposes.ThistableisachildoftheHZ_CUST_ACCT_SITES_ALLtable,withtheforeignkeyCUST_ACCT_SITE_ID.TheHZ_CUST_SITE_USES_ALLtablealsostoresoperatingunitidentifier,thoughtheHZ_CUST_ACCT_SITES_ALLtableitselfstorestheoperatingunitforcustomeraccountsites.*/select*fromAR.HZ_CUST_SITE_USES_ALL;/*TheHZ_LOCATIONStablestoresinformationaboutadeliveryorpostaladdresssuchasbuildingnumber,streetaddress,postalcode,anddirectionstoalocation.Thistableprovidesphysicallocationinformationaboutparties(organizationsandpeople)andcustomeraccounts.Forexample,youcanstoreinformationsuchasBuilding300,500OracleParkway,94065,andTaketheRalstonAvenueexitfromhighway101,goeastonTwinDolphinsDrive,turnleftonOracleParkway,watchfortheBuilding300signonyourright.RecordsintheHZ_LOCATIONStablecanstoredeliveryandpostalinformationaboutalocationthroughcolumnssuchastheLOCATION_DIRECTIONS,POST_OFFICE,andTIME_ZONEcolumns.YoucanalsousetheHZ_LOCATIONStabletostorelatitudeandlongitudeinformation.DataintheHZ_LOCATIONStableisalsousedtodeterminetheappropriatetaxauthorityandtaxratesforsalestaxandVATcalculations..TheprimarykeyforthistableisLOCATION_ID.*/select*fromAR.hz_locations;/*TheHZ_PARTY_SITEStablelinksaparty(seeHZ_PARTIES)andalocation(seeHZ_LOCATIONS.inhz_locations,thereisnocolumnofpart_id)andstoreslocation-specificpartyinformationsuchasMAILSTOPandADDRESSEE.Onepartycanoptionallyhaveoneormorepartysites.Onelocationcanoptionallybeusedbyoneormoreparties.Forexample,500OracleParkwaycanbespecifiedasapartysiteforOracleCorporation.Thispartysitecanthenbeusedformultiplecustomeraccountswithinthesameparty.TheprimarykeyforthistableisPARTY_SITE_ID.*/select*fromAR.HZ_PARTY_SITES;/*TheHZ_PERSON_PROFILEStablestorespersonalandfamilyinformationaboutapartyofthePersontype.Forexample,thistablecouldcontainthecorrectspellingandphoneticpronunciationoftheperson'sname.NotethatsomeinformationinthistablemayalsobeenteredintotheHZ_PARTIEStable.TheprimarykeyforthistableisPERSON_PROFILE_ID.*/select*fromAR.HZ_PERSON_PROFILES;/*TheHZ_ORGANIZATION_PROFILEStablestoresavarietyofinformationaboutaparty.ThistablegetspopulatedwhenapartyoftheOrganizationtypeiscreated.ThistablealsocontainsdataretrievedfromDun&BradstreetusingeithertheDun&Bradstreetonlineorbatchdownloadmethods.Historicaldatafortheorganizationcanalsobestoredinthistable.Eachtimeorganizationinformationischanged,theeffectiveenddatecolumnfortheoriginalrecordisupdatedandanewrecordthatcontainstheupdatedinformationiscreated.TheprimarykeyforthistableisORGANIZATION_PROFILE_ID.*/select*fromAR.HZ_ORGANIZATION_PROFILES;/*TheHZ_PARTIEStablestoresbasici