FND Design Data [Home] [Help]

View: JTF_PARTIES_V

Product: JTF - CRM Foundation
Description: Party (Organization or Group or Person) and relationship information
Implementation/DBA Data: ViewAPPS.JTF_PARTIES_V
View Text

SELECT PARTY.ROWID
, PARTY.PARTY_ID
, PARTY_REL.RELATIONSHIP_ID PARTY_RELATIONSHIP_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, SUB_PARTY.PARTY_ID
, SUB_PARTY.PARTY_NUMBER
, OBJ_PARTY.PARTY_ID
, OBJ_PARTY.PARTY_NUMBER
, OBJ_PARTY.PARTY_NAME
, PARTY.LAST_UPDATE_DATE
, PARTY.LAST_UPDATED_BY
, PARTY.LAST_UPDATE_LOGIN
, PARTY.CREATION_DATE
, PARTY.CREATED_BY
, OBJ_PARTY.PARTY_TYPE
, OBJ_PARTY.PARTY_NAME
, OBJ_PARTY.PARTY_NAME
, OBJ_PARTY.GROUP_TYPE
, OBJ_PARTY.PERSON_FIRST_NAME
, OBJ_PARTY.PERSON_MIDDLE_NAME
, OBJ_PARTY.PERSON_LAST_NAME
, OBJ_PARTY.PERSON_NAME_SUFFIX
, OBJ_PARTY.PERSON_PRE_NAME_ADJUNCT
, OBJ_PARTY.KNOWN_AS
, OBJ_PARTY.SIC_CODE
, ORG_PROFILE.ORGANIZATION_NAME
, ORG_PROFILE.ANALYSIS_FY
, ORG_PROFILE.FISCAL_YEAREND_MONTH
, ORG_PROFILE.EMPLOYEES_TOTAL
, ORG_PROFILE.CURR_FY_POTENTIAL_REVENUE
, ORG_PROFILE.NEXT_FY_POTENTIAL_REVENUE
, ORG_PROFILE.TAX_REFERENCE
, ORG_PROFILE.YEAR_ESTABLISHED
, ORG_PROFILE.INTERNAL_FLAG
, ORG_PROFILE.PUBLIC_PRIVATE_OWNERSHIP_FLAG
, ORG_PROFILE.DUNS_NUMBER
, ORG_PROFILE.JGZZ_FISCAL_CODE
, SUB_PARTY.PARTY_TYPE
, SUB_PARTY.PARTY_NAME
, SUB_PARTY.PERSON_FIRST_NAME
, SUB_PARTY.PERSON_MIDDLE_NAME
, SUB_PARTY.PERSON_LAST_NAME
, SUB_PARTY.PERSON_NAME_SUFFIX
, SUB_PARTY.PERSON_PRE_NAME_ADJUNCT
, PERSON_PROFILE_SUB.KNOWN_AS /* NICKNAME */
, PERSON_PROFILE_SUB.PERSON_NAME_PHONETIC
, ORG_CONT.ORG_CONTACT_ID
, ORG_CONT.JOB_TITLE
, ORG_CONT.DECISION_MAKER_FLAG
, ORG_CONT.JOB_TITLE_CODE
, ORG_CONT.MANAGED_BY
, ORG_CONT.NATIVE_LANGUAGE
, ORG_CONT.REFERENCE_USE_FLAG
, ORG_CONT.OTHER_LANGUAGE_1
, ORG_CONT.OTHER_LANGUAGE_2
, ORG_CONT.RANK
, SUB_PARTY.PERSON_PRE_NAME_ADJUNCT
, ORG_CONT.DEPARTMENT_CODE
, ORG_CONT.DEPARTMENT
, PERSON_PROFILE_SUB.DATE_OF_BIRTH
, PERSON_PROFILE_SUB.PERSONAL_INCOME
, PARTY_REL.RELATIONSHIP_CODE PARTY_RELATIONSHIP_TYPE
, PARTY.ADDRESS1||DECODE(PARTY.ADDRESS2
, NULL
, NULL
, ';'||PARTY.ADDRESS2|| DECODE(PARTY.ADDRESS3
, NULL
, NULL
, ';'||PARTY.ADDRESS3|| DECODE(PARTY.ADDRESS4
, NULL
, NULL
, ';'||PARTY.ADDRESS4))) ADDRESS
, PARTY.CITY
, PARTY.STATE
, PARTY.PROVINCE
, PARTY.POSTAL_CODE
, PARTY.COUNTRY
, PARTY.ATTRIBUTE_CATEGORY
, PARTY.ATTRIBUTE1
, PARTY.ATTRIBUTE2
, PARTY.ATTRIBUTE3
, PARTY.ATTRIBUTE4
, PARTY.ATTRIBUTE5
, PARTY.ATTRIBUTE6
, PARTY.ATTRIBUTE7
, PARTY.ATTRIBUTE8
, PARTY.ATTRIBUTE9
, PARTY.ATTRIBUTE10
, PARTY.ATTRIBUTE11
, PARTY.ATTRIBUTE12
, PARTY.ATTRIBUTE13
, PARTY.ATTRIBUTE14
, PARTY.ATTRIBUTE15
, PARTY.ATTRIBUTE16
, PARTY.ATTRIBUTE17
, PARTY.ATTRIBUTE18
, PARTY.ATTRIBUTE19
, PARTY.ATTRIBUTE20
, PARTY.ATTRIBUTE21
, PARTY.ATTRIBUTE22
, PARTY.ATTRIBUTE23
, PARTY.ATTRIBUTE24
, PARTY.GLOBAL_ATTRIBUTE_CATEGORY
, PARTY.GLOBAL_ATTRIBUTE1
, PARTY.GLOBAL_ATTRIBUTE2
, PARTY.GLOBAL_ATTRIBUTE3
, PARTY.GLOBAL_ATTRIBUTE4
, PARTY.GLOBAL_ATTRIBUTE5
, PARTY.GLOBAL_ATTRIBUTE6
, PARTY.GLOBAL_ATTRIBUTE7
, PARTY.GLOBAL_ATTRIBUTE8
, PARTY.GLOBAL_ATTRIBUTE9
, PARTY.GLOBAL_ATTRIBUTE10
, PARTY.GLOBAL_ATTRIBUTE11
, PARTY.GLOBAL_ATTRIBUTE12
, PARTY.GLOBAL_ATTRIBUTE13
, PARTY.GLOBAL_ATTRIBUTE14
, PARTY.GLOBAL_ATTRIBUTE15
, PARTY.GLOBAL_ATTRIBUTE16
, PARTY.GLOBAL_ATTRIBUTE17
, PARTY.GLOBAL_ATTRIBUTE18
, PARTY.GLOBAL_ATTRIBUTE19
, PARTY.GLOBAL_ATTRIBUTE20
, SUB_PARTY.LAST_UPDATE_DATE
, OBJ_PARTY.LAST_UPDATE_DATE
, PARTY_REL.LAST_UPDATE_DATE
, ORG_PROFILE.LAST_UPDATE_DATE
, ORG_CONT.LAST_UPDATE_DATE
, PERSON_PROFILE_SUB.LAST_UPDATE_DATE
, PERSON_PROFILE_OBJ.LAST_UPDATE_DATE
, PARTY.LAST_UPDATE_DATE
, PARTY.STATUS
, ORG_PROFILE.LINE_OF_BUSINESS
, ORG_PROFILE.DB_RATING
, ORG_CONT.STATUS
FROM HZ_PARTIES PARTY
, HZ_PARTIES SUB_PARTY
, HZ_PARTIES OBJ_PARTY
, HZ_RELATIONSHIPS PARTY_REL
, HZ_ORGANIZATION_PROFILES ORG_PROFILE
, HZ_PERSON_PROFILES PERSON_PROFILE_SUB
, HZ_PERSON_PROFILES PERSON_PROFILE_OBJ
, HZ_ORG_CONTACTS ORG_CONT
WHERE PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
AND PARTY.PARTY_ID = PARTY_REL.PARTY_ID
AND ((PARTY_REL.SUBJECT_ID = SUB_PARTY.PARTY_ID
AND PARTY_REL.OBJECT_ID = OBJ_PARTY.PARTY_ID) OR (PARTY_REL.OBJECT_ID = SUB_PARTY.PARTY_ID
AND PARTY_REL.SUBJECT_ID = OBJ_PARTY.PARTY_ID))
AND OBJ_PARTY.PARTY_ID = ORG_PROFILE.PARTY_ID(+)
AND OBJ_PARTY.PARTY_ID = PERSON_PROFILE_OBJ.PARTY_ID(+)
AND SUB_PARTY.PARTY_ID = PERSON_PROFILE_SUB.PARTY_ID(+)
AND ORG_CONT.PARTY_RELATIONSHIP_ID(+) = PARTY_REL.RELATIONSHIP_ID
AND ( PARTY_REL.START_DATE IS NULL OR (PARTY_REL.START_DATE IS NOT NULL
AND SYSDATE BETWEEN PARTY_REL.START_DATE
AND NVL(PARTY_REL.END_DATE
, SYSDATE)))
AND ( ORG_PROFILE.EFFECTIVE_START_DATE IS NULL OR (ORG_PROFILE.EFFECTIVE_START_DATE IS NOT NULL
AND SYSDATE BETWEEN ORG_PROFILE.EFFECTIVE_START_DATE
AND NVL(ORG_PROFILE.EFFECTIVE_END_DATE
, SYSDATE)))
AND ( PERSON_PROFILE_SUB.EFFECTIVE_START_DATE IS NULL OR (PERSON_PROFILE_SUB.EFFECTIVE_START_DATE IS NOT NULL
AND SYSDATE BETWEEN PERSON_PROFILE_SUB.EFFECTIVE_START_DATE
AND NVL(PERSON_PROFILE_SUB.EFFECTIVE_END_DATE
, SYSDATE)))
AND ( PERSON_PROFILE_OBJ.EFFECTIVE_START_DATE IS NULL OR (PERSON_PROFILE_OBJ.EFFECTIVE_START_DATE IS NOT NULL
AND SYSDATE BETWEEN PERSON_PROFILE_OBJ.EFFECTIVE_START_DATE
AND NVL(PERSON_PROFILE_OBJ.EFFECTIVE_END_DATE
, SYSDATE))) UNION SELECT PARTY.ROWID
, PARTY.PARTY_ID
, TO_NUMBER(NULL)
, PARTY.PARTY_NUMBER
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, DECODE(PARTY.PARTY_TYPE
, 'PERSON'
, PARTY.PARTY_ID
, TO_NUMBER(NULL))
, DECODE(PARTY.PARTY_TYPE
, 'PERSON'
, PARTY.PARTY_NUMBER
, NULL)
, PARTY.PARTY_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_NAME
, PARTY.LAST_UPDATE_DATE
, PARTY.LAST_UPDATED_BY
, PARTY.LAST_UPDATE_LOGIN
, PARTY.CREATION_DATE
, PARTY.CREATED_BY
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, PARTY.GROUP_TYPE
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_MIDDLE_NAME
, PARTY.PERSON_LAST_NAME
, PARTY.PERSON_NAME_SUFFIX
, PARTY.PERSON_PRE_NAME_ADJUNCT
, PARTY.KNOWN_AS
, PARTY.SIC_CODE
, ORG_PROFILE.ORGANIZATION_NAME
, ORG_PROFILE.ANALYSIS_FY
, ORG_PROFILE.FISCAL_YEAREND_MONTH
, ORG_PROFILE.EMPLOYEES_TOTAL
, ORG_PROFILE.CURR_FY_POTENTIAL_REVENUE
, ORG_PROFILE.NEXT_FY_POTENTIAL_REVENUE
, ORG_PROFILE.TAX_REFERENCE
, ORG_PROFILE.YEAR_ESTABLISHED
, ORG_PROFILE.INTERNAL_FLAG
, ORG_PROFILE.PUBLIC_PRIVATE_OWNERSHIP_FLAG
, ORG_PROFILE.DUNS_NUMBER
, ORG_PROFILE.JGZZ_FISCAL_CODE
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_MIDDLE_NAME
, PARTY.PERSON_LAST_NAME
, PARTY.PERSON_NAME_SUFFIX
, PARTY.PERSON_PRE_NAME_ADJUNCT
, PERSON_PROFILE.KNOWN_AS /* NICKNAME */
, PERSON_PROFILE.PERSON_NAME_PHONETIC
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, PARTY.PERSON_PRE_NAME_ADJUNCT
, NULL
, NULL
, PERSON_PROFILE.DATE_OF_BIRTH
, PERSON_PROFILE.PERSONAL_INCOME
, NULL
, PARTY.ADDRESS1||DECODE(PARTY.ADDRESS2
, NULL
, NULL
, ';'||PARTY.ADDRESS2||DECODE(PARTY.ADDRESS3
, NULL
, NULL
, ';'||PARTY.ADDRESS3||DECODE(PARTY.ADDRESS4
, NULL
, NULL
, ';'||PARTY.ADDRESS4))) ADDRESS
, PARTY.CITY
, PARTY.STATE
, PARTY.PROVINCE
, PARTY.POSTAL_CODE
, PARTY.COUNTRY
, PARTY.ATTRIBUTE_CATEGORY
, PARTY.ATTRIBUTE1
, PARTY.ATTRIBUTE2
, PARTY.ATTRIBUTE3
, PARTY.ATTRIBUTE4
, PARTY.ATTRIBUTE5
, PARTY.ATTRIBUTE6
, PARTY.ATTRIBUTE7
, PARTY.ATTRIBUTE8
, PARTY.ATTRIBUTE9
, PARTY.ATTRIBUTE10
, PARTY.ATTRIBUTE11
, PARTY.ATTRIBUTE12
, PARTY.ATTRIBUTE13
, PARTY.ATTRIBUTE14
, PARTY.ATTRIBUTE15
, PARTY.ATTRIBUTE16
, PARTY.ATTRIBUTE17
, PARTY.ATTRIBUTE18
, PARTY.ATTRIBUTE19
, PARTY.ATTRIBUTE20
, PARTY.ATTRIBUTE21
, PARTY.ATTRIBUTE22
, PARTY.ATTRIBUTE23
, PARTY.ATTRIBUTE24
, PARTY.GLOBAL_ATTRIBUTE_CATEGORY
, PARTY.GLOBAL_ATTRIBUTE1
, PARTY.GLOBAL_ATTRIBUTE2
, PARTY.GLOBAL_ATTRIBUTE3
, PARTY.GLOBAL_ATTRIBUTE4
, PARTY.GLOBAL_ATTRIBUTE5
, PARTY.GLOBAL_ATTRIBUTE6
, PARTY.GLOBAL_ATTRIBUTE7
, PARTY.GLOBAL_ATTRIBUTE8
, PARTY.GLOBAL_ATTRIBUTE9
, PARTY.GLOBAL_ATTRIBUTE10
, PARTY.GLOBAL_ATTRIBUTE11
, PARTY.GLOBAL_ATTRIBUTE12
, PARTY.GLOBAL_ATTRIBUTE13
, PARTY.GLOBAL_ATTRIBUTE14
, PARTY.GLOBAL_ATTRIBUTE15
, PARTY.GLOBAL_ATTRIBUTE16
, PARTY.GLOBAL_ATTRIBUTE17
, PARTY.GLOBAL_ATTRIBUTE18
, PARTY.GLOBAL_ATTRIBUTE19
, PARTY.GLOBAL_ATTRIBUTE20
, PARTY.LAST_UPDATE_DATE
, PARTY.LAST_UPDATE_DATE
, TO_DATE(NULL)
, ORG_PROFILE.LAST_UPDATE_DATE
, TO_DATE(NULL)
, PERSON_PROFILE.LAST_UPDATE_DATE
, PERSON_PROFILE.LAST_UPDATE_DATE
, PARTY.LAST_UPDATE_DATE
, PARTY.STATUS
, ORG_PROFILE.LINE_OF_BUSINESS
, ORG_PROFILE.DB_RATING
, NULL
FROM HZ_PARTIES PARTY
, HZ_ORGANIZATION_PROFILES ORG_PROFILE
, HZ_PERSON_PROFILES PERSON_PROFILE
WHERE PARTY.PARTY_ID = ORG_PROFILE.PARTY_ID(+)
AND PARTY.PARTY_ID = PERSON_PROFILE.PARTY_ID(+)
AND ( ORG_PROFILE.EFFECTIVE_START_DATE IS NULL OR (ORG_PROFILE.EFFECTIVE_START_DATE IS NOT NULL
AND SYSDATE BETWEEN ORG_PROFILE.EFFECTIVE_START_DATE
AND NVL(ORG_PROFILE.EFFECTIVE_END_DATE
, SYSDATE)))
AND ( PERSON_PROFILE.EFFECTIVE_START_DATE IS NULL OR (PERSON_PROFILE.EFFECTIVE_START_DATE IS NOT NULL
AND SYSDATE BETWEEN PERSON_PROFILE.EFFECTIVE_START_DATE
AND NVL(PERSON_PROFILE.EFFECTIVE_END_DATE
, SYSDATE)))
AND NOT EXISTS (SELECT 'X'
FROM HZ_RELATIONSHIPS PARTY_REL
WHERE PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
AND PARTY.PARTY_ID = PARTY_REL.PARTY_ID)

Columns

Name
ROW_ID
PARTY_ID
PARTY_RELATIONSHIP_ID
PARTY_NUMBER
PARTY_TYPE
PARTY_NAME
SUBJECT_ID
SUBJECT_NUMBER
OBJECT_ID
OBJECT_NUMBER
OBJECT_NAME
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
CREATION_DATE
CREATED_BY
OBJ_PARTY_TYPE
OBJ_COMPANY_NAME
OBJ_GROUP_NAME
OBJ_GROUP_TYPE
OBJ_FIRST_NAME
OBJ_MIDDLE_NAME
OBJ_LAST_NAME
OBJ_NAME_SUFFIX
OBJ_TITLE
OBJ_ALIAS
OBJ_SIC_CODE
OBJ_LONG_NAME
OBJ_ANALYSIS_FY
OBJ_FISCAL_YEAREND_MONTH
OBJ_NUM_OF_EMPLOYEES
OBJ_POT_REVENUE_CURR_FY
OBJ_POT_REVENUE_NEXT_FY
OBJ_TAX_REFERENCE
OBJ_YEAR_ESTABLISHED
OBJ_INTERNAL_FLAG
OBJ_PUB_PRIVATE_OWNER_FLAG
OBJ_DUNS_NUMBER
OBJ_JGZZ_FISCAL_CODE
SUB_PARTY_TYPE
SUB_PARTY_NAME
SUB_FIRST_NAME
SUB_MIDDLE_NAME
SUB_LAST_NAME
SUB_NAME_SUFFIX
SUB_TITLE
SUB_NICKNAME
SUB_PERSON_NAME_PHONETIC
SUB_ORG_CONTACT_ID
SUB_JOB_TITLE
SUB_DECISION_MAKER_FLAG
SUB_JOB_TITLE_CODE
SUB_MANAGED_BY
SUB_NATIVE_LANGUAGE
SUB_REFERENCE_USE_FLAG
SUB_OTHER_LANGUAGE_1
SUB_OTHER_LANGUAGE_2
SUB_RANK
SUB_SALUTATION
SUB_DEPARTMENT_CODE
SUB_DEPARTMENT
SUB_DATE_OF_BIRTH
SUB_PERSONAL_INCOME
RELATION
ADDRESS
CITY
STATE
PROVINCE
POSTAL_CODE
COUNTRY
ATTRIBUTE_CATEGORY
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE5
ATTRIBUTE6
ATTRIBUTE7
ATTRIBUTE8
ATTRIBUTE9
ATTRIBUTE10
ATTRIBUTE11
ATTRIBUTE12
ATTRIBUTE13
ATTRIBUTE14
ATTRIBUTE15
ATTRIBUTE16
ATTRIBUTE17
ATTRIBUTE18
ATTRIBUTE19
ATTRIBUTE20
ATTRIBUTE21
ATTRIBUTE22
ATTRIBUTE23
ATTRIBUTE24
GLOBAL_ATTRIBUTE_CATEGORY
GLOBAL_ATTRIBUTE1
GLOBAL_ATTRIBUTE2
GLOBAL_ATTRIBUTE3
GLOBAL_ATTRIBUTE4
GLOBAL_ATTRIBUTE5
GLOBAL_ATTRIBUTE6
GLOBAL_ATTRIBUTE7
GLOBAL_ATTRIBUTE8
GLOBAL_ATTRIBUTE9
GLOBAL_ATTRIBUTE10
GLOBAL_ATTRIBUTE11
GLOBAL_ATTRIBUTE12
GLOBAL_ATTRIBUTE13
GLOBAL_ATTRIBUTE14
GLOBAL_ATTRIBUTE15
GLOBAL_ATTRIBUTE16
GLOBAL_ATTRIBUTE17
GLOBAL_ATTRIBUTE18
GLOBAL_ATTRIBUTE19
GLOBAL_ATTRIBUTE20
SUB_LAST_UPDATE_DATE
OBJ_LAST_UPDATE_DATE
REL_LAST_UPDATE_DATE
ORGPR_LAST_UPDATE_DATE
ORGCT_LAST_UPDATE_DATE
PERSU_LAST_UPDATE_DATE
PEROB_LAST_UPDATE_DATE
PARTY_LAST_UPDATE_DATE
PARTY_STATUS
ORGPR_LINE_OF_BUSINESS
ORGPR_DB_RATING
ORGCT_STATUS