销售账户关联地区
SELECT MT.NAME TERR_NAME,
ZA.SALES_ACCOUNT_NAME SA_NAME,
ZA.SALES_ACCOUNT_NAME_ALIAS SA_NAME_ALIAS,
ZA.SALES_ACCOUNT_UNIQUE_NAME SA_UNIQUE_NAME,
ZA.CREATED_BY SA_CB,
ZA.CREATION_DATE SA_CD,
ZA.LAST_UPDATED_BY SA_LUB,
ZA.LAST_UPDATE_DATE SA_LUD,
ZT.START_DATE_ACTIVE SDA,
ZT.END_DATE_ACTIVE EDA,
ZT.SALES_ACCOUNT_ID SA_ID,
ZT.TERRITORY_ID TERR_ID
FROM ZCA_S_ACCT_TERRITORIES ZT ,
ZCA_SALES_ACCOUNTS ZA ,
MOT_TERRITORIES MT
WHERE ZT.SALES_ACCOUNT_ID=ZA.SALES_ACCOUNT_ID
AND ZT.TERRITORY_ID=MT.TERRITORY_ID
--地区层级
SELECT TOP_LEVEL_TERR_ID ,
LEVEL16_TERR_ID ,
LEVEL15_TERR_ID ,
LEVEL14_TERR_ID ,
LEVEL13_TERR_ID ,
LEVEL12_TERR_ID ,
BASE_TERR_ID ,
FIXED_HIERARCHY_LEVEL ,
HIERARCHY_START_DATE ,
HIERARCHY_END_DATE
FROM MOT_TERR_HIERARCHY_CF_DN
--地区层级补充名称
SELECT TH.TOP_LEVEL_TERR_ID ,
TH.LEVEL16_TERR_ID ,
TH.LEVEL15_TERR_ID ,
TH.LEVEL14_TERR_ID ,
TH.LEVEL13_TERR_ID ,
TH.LEVEL12_TERR_ID ,
TH.BASE_TERR_ID ,
TT.NAME TT_NAME,
T16.NAME T16_NAME,
T15.NAME T15_NAME,
T14.NAME T14_NAME,
T13.NAME T13_NAME,
T12.NAME T12_NAME,
TB.NAME TB_NAME,
TH.FIXED_HIERARCHY_LEVEL ,
TH.HIERARCHY_START_DATE ,
TH.HIERARCHY_END_DATE
FROM MOT_TERR_HIERARCHY_CF_DN TH,
(SELECT TERRITORY_ID ,
NAME
FROM MOT_TERRITORIES
WHERE EFFECTIVE_END_DATE>CURRENT_DATE) TB,
(SELECT TERRITORY_ID ,
NAME
FROM MOT_TERRITORIES
WHERE EFFECTIVE_END_DATE>CURRENT_DATE) TT,
(SELECT TERRITORY_ID ,
NAME
FROM MOT_TERRITORIES
WHERE EFFECTIVE_END_DATE>CURRENT_DATE) T16,
(SELECT TERRITORY_ID ,
NAME
FROM MOT_TERRITORIES
WHERE EFFECTIVE_END_DATE>CURRENT_DATE) T15,
(SELECT TERRITORY_ID ,
NAME
FROM MOT_TERRITORIES
WHERE EFFECTIVE_END_DATE>CURRENT_DATE) T14,
(SELECT TERRITORY_ID ,
NAME
FROM MOT_TERRITORIES
WHERE EFFECTIVE_END_DATE>CURRENT_DATE) T13,
(SELECT TERRITORY_ID ,
NAME
FROM MOT_TERRITORIES
WHERE EFFECTIVE_END_DATE>CURRENT_DATE) T12
WHERE TH.TOP_LEVEL_TERR_ID=TT.TERRITORY_ID
AND TH.LEVEL16_TERR_ID=T16.TERRITORY_ID
AND TH.LEVEL15_TERR_ID=T15.TERRITORY_ID
AND TH.LEVEL14_TERR_ID=T14.TERRITORY_ID
AND TH.LEVEL13_TERR_ID=T13.TERRITORY_ID
AND TH.LEVEL12_TERR_ID=T12.TERRITORY_ID
AND TH.BASE_TERR_ID=TB.TERRITORY_ID
--实体-组织&联系
--获取客户的组织信息以及其关联的联系人
--尚未知道如何过滤掉软删除的数据
SELECT SP.PARTY_ID SP_PARTY_ID
,SP.ORIG_SYSTEM_REFERENCE SP_ORIG_SYSTEM_REFERENCE
,SP.PARTY_NAME SP_PARTY_NAME
,SP.VALIDATED_FLAG SP_VALIDATED_FLAG
,SP.UNIQUE_NAME_ALIAS
,SP.ORGANIZATION_SIZE
,SP.STATUS
,SP.SALES_PROFILE_STATUS
,SP.EXTN_ATTRIBUTE_CHAR003
,OP.PARTY_ID OP_PARTY_ID
,OP.ORIG_SYSTEM_REFERENCE OP_ORIG_SYSTEM_REFERENCE
,OP.PARTY_NAME OP_PARTY_NAME
,OP.VALIDATED_FLAG OP_VALIDATED_FLAG
FROM HZ_RELATIONSHIPS TR
LEFT JOIN
(SELECT HZP.PARTY_ID
,HZP.ORIG_SYSTEM_REFERENCE
,HZP.PARTY_NAME
,HZP.VALIDATED_FLAG
,HZOP.UNIQUE_NAME_ALIAS
,HZOP.ORGANIZATION_SIZE
,HZOP.STATUS
,HZOP.SALES_PROFILE_STATUS
,HZOP.EXTN_ATTRIBUTE_CHAR003
FROM HZ_PARTIES HZP,
HZ_ORGANIZATION_PROFILES HZOP
WHERE HZP.PARTY_ID=HZOP.PARTY_ID ) SP ON (SP.PARTY_ID=TR.SUBJECT_ID)
LEFT JOIN
(SELECT HZP.PARTY_ID
,HZP.ORIG_SYSTEM_REFERENCE
,HZP.PARTY_NAME
,HZP.VALIDATED_FLAG
FROM HZ_PARTIES HZP) OP ON (OP.PARTY_ID=TR.OBJECT_ID)
WHERE RELATIONSHIP_CODE='CONTACT'
AND SP.VALIDATED_FLAG!='N'
