VIEW: APPS.AST_ORG_CONTACTS_V

Source

View Text - Preformatted

SELECT PARTY_REL.ROWID, PARTY_REL.relationship_id , PARTY_REL.subject_id, PARTY_REL.object_id, PARTY_REL.party_id, HP.party_number, PARTY_REL.relationship_type, PARTY_REL.directional_flag, PARTY_REL.comments, PARTY_REL.start_date, PARTY_REL.end_date, PARTY_REL.last_update_date, PARTY_REL.status, PARTY_REL.content_source_type, ORG_CONT.org_contact_id , ORG_CONT.comments , ORG_CONT.contact_number, ORG_CONT.department_code, ORG_CONT.department, LKUP1.meaning, ORG_CONT.title, ORG_CONT.job_title, LKUP2.meaning, ORG_CONT.decision_maker_flag, ORG_CONT.job_title_code, ORG_CONT.reference_use_flag, ORG_CONT.rank, ORG_CONT.last_update_date, ORG_CONT.party_site_id , ORG_CONT.orig_system_reference, HP.CATEGORY_CODE, LKUP3.MEANING, ORG_CONT.attribute_category, ORG_CONT.attribute1, ORG_CONT.attribute2, ORG_CONT.attribute3, ORG_CONT.attribute4, ORG_CONT.attribute5, ORG_CONT.attribute6, ORG_CONT.attribute7, ORG_CONT.attribute8, ORG_CONT.attribute9, ORG_CONT.attribute10, ORG_CONT.attribute11, ORG_CONT.attribute12, ORG_CONT.attribute13, ORG_CONT.attribute14, ORG_CONT.attribute15, ORG_CONT.attribute16, ORG_CONT.attribute17, ORG_CONT.attribute18, ORG_CONT.attribute19, ORG_CONT.attribute20, ORG_CONT.attribute21, ORG_CONT.attribute22, ORG_CONT.attribute23, ORG_CONT.attribute24, ORG_CONT.last_update_date, ORG_CONT.last_updated_by, ORG_CONT.creation_date, ORG_CONT.created_by, ORG_CONT.last_update_login, ORG_CONT.object_version_number, ORG_CONT.created_by_module, ORG_CONT.application_id, PARTY_REL.object_version_number, PARTY_REL.created_by_module, PARTY_REL.application_id, HP.object_version_number, HP.created_by_module, HP.application_id, ORG_CONT.CONTACT_KEY, ORG_CONT.MAILING_ADDRESS_ID, ORG_CONT.MAIL_STOP, ORG_CONT.MANAGED_BY, ORG_CONT.MATCH_GROUP_ID, ORG_CONT.NATIVE_LANGUAGE, ORG_CONT.STATUS, ORG_CONT.OTHER_LANGUAGE_1, ORG_CONT.OTHER_LANGUAGE_2 FROM HZ_RELATIONSHIPS PARTY_REL, HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES HP, AR_LOOKUPS LKUP1, AR_LOOKUPS LKUP2, AR_LOOKUPS LKUP3 WHERE 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_CONT.DEPARTMENT_CODE = LKUP1.LOOKUP_CODE(+) AND LKUP1.LOOKUP_TYPE(+) = 'DEPARTMENT_TYPE' AND ORG_CONT.JOB_TITLE_CODE = LKUP2.LOOKUP_CODE(+) AND LKUP2.LOOKUP_TYPE(+) = 'RESPONSIBILITY' AND HP.party_id = PARTY_REL.party_id AND HP.CATEGORY_CODE = LKUP3.LOOKUP_CODE(+) AND LKUP3.LOOKUP_TYPE(+) = 'CUSTOMER_CATEGORY' AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND PARTY_REL.DIRECTIONAL_FLAG = 'F'

View Text - HTML Formatted

SELECT PARTY_REL.ROWID
, PARTY_REL.RELATIONSHIP_ID
, PARTY_REL.SUBJECT_ID
, PARTY_REL.OBJECT_ID
, PARTY_REL.PARTY_ID
, HP.PARTY_NUMBER
, PARTY_REL.RELATIONSHIP_TYPE
, PARTY_REL.DIRECTIONAL_FLAG
, PARTY_REL.COMMENTS
, PARTY_REL.START_DATE
, PARTY_REL.END_DATE
, PARTY_REL.LAST_UPDATE_DATE
, PARTY_REL.STATUS
, PARTY_REL.CONTENT_SOURCE_TYPE
, ORG_CONT.ORG_CONTACT_ID
, ORG_CONT.COMMENTS
, ORG_CONT.CONTACT_NUMBER
, ORG_CONT.DEPARTMENT_CODE
, ORG_CONT.DEPARTMENT
, LKUP1.MEANING
, ORG_CONT.TITLE
, ORG_CONT.JOB_TITLE
, LKUP2.MEANING
, ORG_CONT.DECISION_MAKER_FLAG
, ORG_CONT.JOB_TITLE_CODE
, ORG_CONT.REFERENCE_USE_FLAG
, ORG_CONT.RANK
, ORG_CONT.LAST_UPDATE_DATE
, ORG_CONT.PARTY_SITE_ID
, ORG_CONT.ORIG_SYSTEM_REFERENCE
, HP.CATEGORY_CODE
, LKUP3.MEANING
, ORG_CONT.ATTRIBUTE_CATEGORY
, ORG_CONT.ATTRIBUTE1
, ORG_CONT.ATTRIBUTE2
, ORG_CONT.ATTRIBUTE3
, ORG_CONT.ATTRIBUTE4
, ORG_CONT.ATTRIBUTE5
, ORG_CONT.ATTRIBUTE6
, ORG_CONT.ATTRIBUTE7
, ORG_CONT.ATTRIBUTE8
, ORG_CONT.ATTRIBUTE9
, ORG_CONT.ATTRIBUTE10
, ORG_CONT.ATTRIBUTE11
, ORG_CONT.ATTRIBUTE12
, ORG_CONT.ATTRIBUTE13
, ORG_CONT.ATTRIBUTE14
, ORG_CONT.ATTRIBUTE15
, ORG_CONT.ATTRIBUTE16
, ORG_CONT.ATTRIBUTE17
, ORG_CONT.ATTRIBUTE18
, ORG_CONT.ATTRIBUTE19
, ORG_CONT.ATTRIBUTE20
, ORG_CONT.ATTRIBUTE21
, ORG_CONT.ATTRIBUTE22
, ORG_CONT.ATTRIBUTE23
, ORG_CONT.ATTRIBUTE24
, ORG_CONT.LAST_UPDATE_DATE
, ORG_CONT.LAST_UPDATED_BY
, ORG_CONT.CREATION_DATE
, ORG_CONT.CREATED_BY
, ORG_CONT.LAST_UPDATE_LOGIN
, ORG_CONT.OBJECT_VERSION_NUMBER
, ORG_CONT.CREATED_BY_MODULE
, ORG_CONT.APPLICATION_ID
, PARTY_REL.OBJECT_VERSION_NUMBER
, PARTY_REL.CREATED_BY_MODULE
, PARTY_REL.APPLICATION_ID
, HP.OBJECT_VERSION_NUMBER
, HP.CREATED_BY_MODULE
, HP.APPLICATION_ID
, ORG_CONT.CONTACT_KEY
, ORG_CONT.MAILING_ADDRESS_ID
, ORG_CONT.MAIL_STOP
, ORG_CONT.MANAGED_BY
, ORG_CONT.MATCH_GROUP_ID
, ORG_CONT.NATIVE_LANGUAGE
, ORG_CONT.STATUS
, ORG_CONT.OTHER_LANGUAGE_1
, ORG_CONT.OTHER_LANGUAGE_2
FROM HZ_RELATIONSHIPS PARTY_REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES HP
, AR_LOOKUPS LKUP1
, AR_LOOKUPS LKUP2
, AR_LOOKUPS LKUP3
WHERE 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_CONT.DEPARTMENT_CODE = LKUP1.LOOKUP_CODE(+)
AND LKUP1.LOOKUP_TYPE(+) = 'DEPARTMENT_TYPE'
AND ORG_CONT.JOB_TITLE_CODE = LKUP2.LOOKUP_CODE(+)
AND LKUP2.LOOKUP_TYPE(+) = 'RESPONSIBILITY'
AND HP.PARTY_ID = PARTY_REL.PARTY_ID
AND HP.CATEGORY_CODE = LKUP3.LOOKUP_CODE(+)
AND LKUP3.LOOKUP_TYPE(+) = 'CUSTOMER_CATEGORY'
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'