oracle客户信息

最新推荐文章于 2022-08-16 09:01:00 发布

weixin_30920513

最新推荐文章于 2022-08-16 09:01:00 发布

阅读量163

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/fengyu1706/archive/2013/04/27/3048009.html

版权

--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会得到收单方收货方两条记录，根据HZ_CUST_SITE_USES_ALL.SITE_USE_CODE来得到收单方（BILL_TO)/SHIP_TO(收货方）

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 ACV.*

FROM AR_CONTACTS_V ACV, HZ_CUST_ACCOUNTS HCA

WHERE HCA.CUST_ACCOUNT_ID = ACV.CUSTOMER_ID

AND HCA.ACCOUNT_NUMBER = 'B80022701'

ORDER BY ACV.STATUS, ACV.LAST_NAME;

--根据客户编号获取客户层联系人及联系人电话信息

SELECT ACV.REL_PARTY_ID,

ACV.LAST_NAME,

APV.OWNER_TABLE_ID,

APV.COUNTRY_CODE,

APV.AREA_CODE,

APV.PHONE_ID,

APV.PHONE_TYPE,

APV.PHONE_TYPE_MEANING,

APV.PHONE_NUMBER

FROM AR_CONTACTS_V ACV, HZ_CUST_ACCOUNTS HCA, AR_PHONES_V APV

WHERE HCA.CUST_ACCOUNT_ID = ACV.CUSTOMER_ID

AND ACV.REL_PARTY_ID = APV.OWNER_TABLE_ID

AND HCA.ACCOUNT_NUMBER = 'B80022701'

ORDER BY ACV.STATUS, ACV.LAST_NAME;

--根据客户编号/名称信息获取客户层电话信息

SELECT HCA.CUST_ACCOUNT_ID,

HCA.PARTY_ID,

HCA.ACCOUNT_NUMBER,

HP.PARTY_ID,

HP.PARTY_NUMBER,

HP.PARTY_NAME,

APV.*

FROM HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP, AR_PHONES_V APV

WHERE HCA.PARTY_ID = HP.PARTY_ID

AND APV.OWNER_TABLE_ID(+) = HP.PARTY_ID

AND APV.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'

AND APV.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,

APV.PHONE_ID,

APV.PHONE_NUMBER,

APV.PHONE_TYPE,

APV.PHONE_TYPE_MEANING

FROM HZ_CUST_ACCOUNTS HCA,

HZ_PARTIES HP,

HZ_PARTY_SITES HPS,

HZ_LOCATIONS HL,

AR_PHONES_V APV

WHERE HCA.PARTY_ID = HP.PARTY_ID

AND HP.PARTY_ID = HPS.PARTY_ID

AND HPS.LOCATION_ID = HL.LOCATION_ID

AND APV.OWNER_TABLE_ID(+) = HPS.PARTY_SITE_ID

AND APV.OWNER_TABLE_NAME(+) = 'HZ_PARTY_SITES'

AND APV.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,

ACV.ADDRESS_ID,

ACV.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_V ACV

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 ACV.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,

ACV.ADDRESS_ID,

ACV.LAST_NAME,

ACV.REL_PARTY_ID,

APV.OWNER_TABLE_ID,

APV.PHONE_ID,

APV.PHONE_NUMBER,

APV.PHONE_TYPE,

APV.PHONE_TYPE_MEANING,

APV.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_V ACV,

AR_PHONES_V APV

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 ACV.ADDRESS_ID(+) = HCASA.CUST_ACCT_SITE_ID

AND ACV.REL_PARTY_ID = APV.OWNER_TABLE_ID

AND APV.OWNER_TABLE_NAME = 'HZ_PARTIES'

AND APV.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.PROVINCE || ', ' , HL.STATE || ', ' ) ||

DECODE (HL.POSTAL_CODE, NULL , NULL , HL.POSTAL_CODE || ', ' ) ||

DECODE (HL.COUNTRY, NULL , NULL , HL.COUNTRY) SHIP_TO_ADDRESS5 --OM界面收单地点五

HCSUA1.LOCATION INVOICE_TO_LOCATION --OM界面 "收单地点"

HL1.ADDRESS1 INVOICE_TO_ADDRESS1 --OM界面收单地点一

HL1.ADDRESS2 INVOICE_TO_ADDRESS2 --OM界面收单地点二

DECODE (HL1.CITY, NULL , NULL , HL1.CITY || ', ' ) ||

DECODE (HL1.STATE, NULL , HL1.PROVINCE || ', ' , HL1.STATE || ', ' ) ||

DECODE (HL1.POSTAL_CODE, NULL , NULL , HL1.POSTAL_CODE || ', ' ) ||

DECODE (HL1.COUNTRY, NULL , NULL , HL1.COUNTRY) INVOICE_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.INVOICE_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;

--客户信息与应收发票头关联

--直接查标准VIEW： RA_CUSTOMER_TRX_PARTIAL_V 看关联关系，当前VIEW中有几个标准包获取相关信息的比较实用

转载于:https://www.cnblogs.com/fengyu1706/archive/2013/04/27/3048009.html

weixin_30920513

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle客户信息

--1 HZ_CUST_ACCOUNTS(Cust_account_id & account_number & orig_system_reference) 客户编号SELECT*FROMHZ_CUST_ACCOUNTS HCAWHEREHCA.ACCOUNT_NUMBER ='B80022701';--2 HZ_PARTIES(PARTY_ID...
复制链接

扫一扫