--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中有几个标准包获取相关信息的比较实用