EBS R12客户信息

--1 HZ_CUST_ACCOUNTS(Cust_account_id & account_number & orig_system_reference) 客户编号 SELECT * FROM HZ_CUST_ACCOUNTS HCA WHERE HCA.ACCOUNT_NUMBER = 'B80022701'; --2 HZ_PARTIES(PARTY_ID & PARTY_NUMBER) 客户称呼 --依据1找到PARTY_ID SELECT * FROM HZ_PARTIES HP WHERE HP.PARTY_ID = 16367; --客户编号/称呼信息获与 SELECT HCA.CUST_ACCOUNT_ID ,HCA.PARTY_ID ,HCA.ACCOUNT_NUMBER ,HP.PARTY_ID ,HP.PARTY_NUMBER ,HP.PARTY_NAME FROM HZ_CUST_ACCOUNTS HCA ,HZ_PARTIES HP WHERE HCA.PARTY_ID = HP.PARTY_ID AND HCA.ACCOUNT_NUMBER = 'B80022701'; --3 HZ_PARTY_SITES(PARTY_SITE_ID & PARTY_SITE_NUMBER) 客户地点编号 --依据1找到PARTY_ID SELECT * FROM HZ_PARTY_SITES HPS WHERE HPS.PARTY_ID = 16367; --4 HZ_LOCATIONS(LOCATION_ID) 客户具体地址 --依据3找到LOCATION_ID而后取HZ_LOCATIONS联系干系获得具体地址 SELECT * FROM HZ_LOCATIONS A; --客户编号/称呼/地点/地址具体信息获与 SELECT HCA.CUST_ACCOUNT_ID ,HCA.PARTY_ID ,HCA.ACCOUNT_NUMBER ,HP.PARTY_ID ,HP.PARTY_NUMBER ,HP.PARTY_NAME ,HPS.PARTY_SITE_ID ,HPS.PARTY_SITE_NUMBER ,HPS.LOCATION_ID ,HL.ADDRESS1 ,HL.ADDRESS2 ,HL.ADDRESS3 ,HL.ADDRESS4 FROM HZ_CUST_ACCOUNTS HCA ,HZ_PARTIES HP ,HZ_PARTY_SITES HPS ,HZ_LOCATIONS HL WHERE HCA.PARTY_ID = HP.PARTY_ID AND HP.PARTY_ID = HPS.PARTY_ID AND HPS.LOCATION_ID = HL.LOCATION_ID AND HCA.ACCOUNT_NUMBER = 'B80022701'; --6 HZ_CUST_ACCT_SITES_ALL(CUST_ACCT_SITE_ID & ORIG_SYSTEM_REFERENCE/ORG_ID & TP_HEADER_ID) 客户支单支货方地点ID --依据1获与CUST_ACCOUNT_ID SELECT * FROM HZ_CUST_ACCT_SITES_ALL HCASA WHERE HCASA.CUST_ACCOUNT_ID = 8343; --7 HZ_CUST_SITE_USES_ALL(SITE_USER_ID) 客户支单支货方具体信息 --依据6获与CUST_ACCT_SITE_ID,同一CUST_ACCT_SITE_ID会获得支双方支货方两条记录&#Vff0c;依据HZ_CUST_SITE_USES_ALL.SITE_USE_CODE来获得支双方&#Vff08;BILL_TO)/SHIP_TO(支货方&#Vff09; SELECT * FROM HZ_CUST_SITE_USES_ALL HCSUA WHERE HCSUA.CUST_ACCT_SITE_ID = 10391; --8 依据支货地点获与当前支货地点的支单地点 --依据7的支货地点中的BILL_TO_SITE_USE_ID联系干系HZ_CUST_SITE_USES_ALL的SITE_USE_ID获与支货地点支双方地点 SELECT * FROM HZ_CUST_SITE_USES_ALL HCSUA WHERE HCSUA.SITE_USE_ID = 12836; --客户编号/称呼/地点/地址/支货地点/支单地点具体信息获与 SELECT HCA.CUST_ACCOUNT_ID ,HCA.PARTY_ID ,HCA.ACCOUNT_NUMBER ,HP.PARTY_ID ,HP.PARTY_NUMBER ,HP.PARTY_NAME ,HPS.PARTY_SITE_ID ,HPS.PARTY_SITE_NUMBER ,HPS.LOCATION_ID ,HL.ADDRESS1 ,HL.ADDRESS2 ,HL.ADDRESS3 ,HL.ADDRESS4 ,HCASA.CUST_ACCT_SITE_ID ,HCSUA.CUST_ACCT_SITE_ID ,HCSUA.SITE_USE_ID ,HCSUA.SITE_USE_CODE ,HCSUA.LOCATION --支货或支单地点 ,HCSUA1.LOCATION --支货方的支双方地点 FROM HZ_CUST_ACCOUNTS HCA ,HZ_PARTIES HP ,HZ_PARTY_SITES HPS ,HZ_LOCATIONS HL ,HZ_CUST_ACCT_SITES_ALL HCASA ,HZ_CUST_SITE_USES_ALL HCSUA ,HZ_CUST_SITE_USES_ALL HCSUA1 WHERE HCA.PARTY_ID = HP.PARTY_ID AND HP.PARTY_ID = HPS.PARTY_ID AND HPS.LOCATION_ID = HL.LOCATION_ID AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID AND HCSUA.BILL_TO_SITE_USE_ID = HCSUA1.SITE_USE_ID(+) AND HCA.ACCOUNT_NUMBER = 'B80022701'; --依据客户ID获与 客户层 的联络人信息 SELECT ACx.* FROM AR_CONTACTS_x ACx ,HZ_CUST_ACCOUNTS HCA WHERE HCA.CUST_ACCOUNT_ID = ACx.CUSTOMER_ID AND HCA.ACCOUNT_NUMBER = 'B80022701' ORDER BY ACx.STATUS ,ACx.LAST_NAME; --依据客户编号获与客户层联络人及联络人电话信息 SELECT ACx.REL_PARTY_ID ,ACx.LAST_NAME ,APx.OWNER_TABLE_ID ,APx.COUNTRY_CODE ,APx.AREA_CODE ,APx.PHONE_ID ,APx.PHONE_TYPE ,APx.PHONE_TYPE_MEANING ,APx.PHONE_NUMBER FROM AR_CONTACTS_x ACx ,HZ_CUST_ACCOUNTS HCA ,AR_PHONES_x APx WHERE HCA.CUST_ACCOUNT_ID = ACx.CUSTOMER_ID AND ACx.REL_PARTY_ID = APx.OWNER_TABLE_ID AND HCA.ACCOUNT_NUMBER = 'B80022701' ORDER BY ACx.STATUS ,ACx.LAST_NAME; --依据客户编号/称呼信息获与客户层电话信息 SELECT HCA.CUST_ACCOUNT_ID ,HCA.PARTY_ID ,HCA.ACCOUNT_NUMBER ,HP.PARTY_ID ,HP.PARTY_NUMBER ,HP.PARTY_NAME ,APx.* FROM HZ_CUST_ACCOUNTS HCA ,HZ_PARTIES HP ,AR_PHONES_x APx WHERE HCA.PARTY_ID = HP.PARTY_ID AND APx.OWNER_TABLE_ID(+) = HP.PARTY_ID AND APx.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND APx.PHONE_TYPE(+) NOT IN ('WEB' ,'EMAIL') AND HCA.ACCOUNT_NUMBER = 'B80022701'; --依据客户编号/称呼/地点/地址具体信息获与地点层电话信息 SELECT HCA.CUST_ACCOUNT_ID ,HCA.PARTY_ID ,HCA.ACCOUNT_NUMBER ,HP.PARTY_ID ,HP.PARTY_NUMBER ,HP.PARTY_NAME ,HPS.PARTY_SITE_ID ,HPS.PARTY_SITE_NUMBER ,HPS.LOCATION_ID ,HL.ADDRESS1 ,HL.ADDRESS2 ,HL.ADDRESS3 ,HL.ADDRESS4 ,APx.PHONE_ID ,APx.PHONE_NUMBER ,APx.PHONE_TYPE ,APx.PHONE_TYPE_MEANING FROM HZ_CUST_ACCOUNTS HCA ,HZ_PARTIES HP ,HZ_PARTY_SITES HPS ,HZ_LOCATIONS HL ,AR_PHONES_x APx WHERE HCA.PARTY_ID = HP.PARTY_ID AND HP.PARTY_ID = HPS.PARTY_ID AND HPS.LOCATION_ID = HL.LOCATION_ID AND APx.OWNER_TABLE_ID(+) = HPS.PARTY_SITE_ID AND APx.OWNER_TABLE_NAME(+) = 'HZ_PARTY_SITES' AND APx.PHONE_TYPE(+) NOT IN ('EMAIL' ,'WEB') AND HCA.ACCOUNT_NUMBER = 'B80022701'; --客户编号/称呼/地点/地址/支货地点/支单地点具体信息获与 SELECT HCA.CUST_ACCOUNT_ID ,HCA.PARTY_ID ,HCA.ACCOUNT_NUMBER ,HP.PARTY_ID ,HP.PARTY_NUMBER ,HP.PARTY_NAME ,HPS.PARTY_SITE_ID ,HPS.PARTY_SITE_NUMBER ,HPS.LOCATION_ID ,HL.ADDRESS1 ,HL.ADDRESS2 ,HL.ADDRESS3 ,HL.ADDRESS4 ,HCASA.CUST_ACCT_SITE_ID ,HCSUA.CUST_ACCT_SITE_ID ,HCSUA.SITE_USE_ID ,HCSUA.SITE_USE_CODE ,HCSUA.LOCATION --支货或支单地点 ,HCSUA1.LOCATION --支货方的支双方地点 FROM HZ_CUST_ACCOUNTS HCA ,HZ_PARTIES HP ,HZ_PARTY_SITES HPS ,HZ_LOCATIONS HL ,HZ_CUST_ACCT_SITES_ALL HCASA ,HZ_CUST_SITE_USES_ALL HCSUA ,HZ_CUST_SITE_USES_ALL HCSUA1 WHERE HCA.PARTY_ID = HP.PARTY_ID AND HP.PARTY_ID = HPS.PARTY_ID AND HPS.LOCATION_ID = HL.LOCATION_ID AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID AND HCSUA.BILL_TO_SITE_USE_ID = HCSUA1.SITE_USE_ID(+) AND HCA.ACCOUNT_NUMBER = 'B80022701'; --依据客户编号/称呼/地点获与地点层联络人信息 SELECT HCA.CUST_ACCOUNT_ID ,HCA.PARTY_ID ,HCA.ACCOUNT_NUMBER ,HP.PARTY_ID ,HP.PARTY_NUMBER ,HP.PARTY_NAME ,HPS.PARTY_SITE_ID ,HPS.PARTY_SITE_NUMBER ,HPS.LOCATION_ID ,HL.ADDRESS1 ,HL.ADDRESS2 ,HL.ADDRESS3 ,HL.ADDRESS4 ,HCASA.CUST_ACCT_SITE_ID ,ACx.ADDRESS_ID ,ACx.LAST_NAME FROM HZ_CUST_ACCOUNTS HCA ,HZ_PARTIES HP ,HZ_PARTY_SITES HPS ,HZ_LOCATIONS HL ,HZ_CUST_ACCT_SITES_ALL HCASA ,AR_CONTACTS_x ACx WHERE HCA.PARTY_ID = HP.PARTY_ID AND HP.PARTY_ID = HPS.PARTY_ID AND HPS.LOCATION_ID = HL.LOCATION_ID AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID AND ACx.ADDRESS_ID(+) = HCASA.CUST_ACCT_SITE_ID AND HCA.ACCOUNT_NUMBER = 'B80022701'; --依据客户编号/称呼/地点获与地点层联络人电话信息 SELECT HCA.CUST_ACCOUNT_ID ,HCA.PARTY_ID ,HCA.ACCOUNT_NUMBER ,HP.PARTY_ID ,HP.PARTY_NUMBER ,HP.PARTY_NAME ,HPS.PARTY_SITE_ID ,HPS.PARTY_SITE_NUMBER ,HPS.LOCATION_ID ,HL.ADDRESS1 ,HL.ADDRESS2 ,HL.ADDRESS3 ,HL.ADDRESS4 ,HCASA.CUST_ACCT_SITE_ID ,ACx.ADDRESS_ID ,ACx.LAST_NAME ,ACx.REL_PARTY_ID ,APx.OWNER_TABLE_ID ,APx.PHONE_ID ,APx.PHONE_NUMBER ,APx.PHONE_TYPE ,APx.PHONE_TYPE_MEANING ,APx.PRIMARY_FLAG FROM HZ_CUST_ACCOUNTS HCA ,HZ_PARTIES HP ,HZ_PARTY_SITES HPS ,HZ_LOCATIONS HL ,HZ_CUST_ACCT_SITES_ALL HCASA ,AR_CONTACTS_x ACx ,AR_PHONES_x APx WHERE HCA.PARTY_ID = HP.PARTY_ID AND HP.PARTY_ID = HPS.PARTY_ID AND HPS.LOCATION_ID = HL.LOCATION_ID AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID AND ACx.ADDRESS_ID(+) = HCASA.CUST_ACCT_SITE_ID AND ACx.REL_PARTY_ID = APx.OWNER_TABLE_ID AND APx.OWNER_TABLE_NAME = 'HZ_PARTIES' AND APx.PHONE_TYPE NOT IN ('EMAIL' ,'WEB') AND HCA.ACCOUNT_NUMBER = 'B80022701'; --客户取销售订单头联系干系 SELECT HCSUA.LOCATION SHIP_TO_LOCATION --OM界面 "支货地点" ,HL.ADDRESS1 SHIP_TO_ADDRESS1 --OM界面 支货地点一 ,HL.ADDRESS2 SHIP_TO_ADDRESS2 --OM界面 支货地点二 ,DECODE(HL.CITY ,NULL ,NULL ,HL.CITY || ', ') || DECODE(HL.STATE ,NULL ,HL.PROxINCE || ', ' ,HL.STATE || ', ') || DECODE(HL.POSTAL_CODE ,NULL ,NULL ,HL.POSTAL_CODE || ', ') || DECODE(HL.COUNTRY ,NULL ,NULL ,HL.COUNTRY) SHIP_TO_ADDRESS5 --OM界面 支单地点五 ,HCSUA1.LOCATION INxOICE_TO_LOCATION --OM界面 "支单地点" ,HL1.ADDRESS1 INxOICE_TO_ADDRESS1 --OM界面 支单地点一 ,HL1.ADDRESS2 INxOICE_TO_ADDRESS2 --OM界面 支单地点二 ,DECODE(HL1.CITY ,NULL ,NULL ,HL1.CITY || ', ') || DECODE(HL1.STATE ,NULL ,HL1.PROxINCE || ', ' ,HL1.STATE || ', ') || DECODE(HL1.POSTAL_CODE ,NULL ,NULL ,HL1.POSTAL_CODE || ', ') || DECODE(HL1.COUNTRY ,NULL ,NULL ,HL1.COUNTRY) INxOICE_TO_ADDRESS5 --OM界面 支单地点五 FROM OE_ORDER_HEADERS_ALL OOH ,HZ_CUST_SITE_USES_ALL HCSUA ,HZ_CUST_ACCT_SITES_ALL HCASA ,HZ_PARTY_SITES HPS ,HZ_LOCATIONS HL ,HZ_CUST_SITE_USES_ALL HCSUA1 ,HZ_CUST_ACCT_SITES_ALL HCASA1 ,HZ_PARTY_SITES HPS1 ,HZ_LOCATIONS HL1 WHERE OOH.ORDER_NUMBER = '833023055' AND OOH.SHIP_TO_ORG_ID = HCSUA.SITE_USE_ID AND HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND HPS.LOCATION_ID = HL.LOCATION_ID AND OOH.INxOICE_TO_ORG_ID = HCSUA1.SITE_USE_ID AND HCSUA1.CUST_ACCT_SITE_ID = HCASA1.CUST_ACCT_SITE_ID AND HCASA1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID AND HPS1.LOCATION_ID = HL1.LOCATION_ID; --客户信息取应支发票头联系干系 间接查范例xIEW&#Vff1a; RA_CUSTOMER_TRX_PARTIAL_x 看联系干系干系&#Vff0c;当前xIEW中有几多个范例包获与相关信息的比较真用