VIEW: APPS.JTF_PARTIES_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)