VIEW: APPS.AMS_ORG_CONTACT_DETAILS_V

Source

View Text - Preformatted

SELECT C.ORG_CONTACT_ID CONTACT_ID ,O.PARTY_ID ORGANIZATION_ID ,R.PARTY_ID ,C.CONTACT_NUMBER ,P.PARTY_NUMBER PARTY_CONTACT_NUMBER ,P.PARTY_NAME PARTY_CONTACT_NAME ,PR.PARTY_TYPE ,P.VALIDATED_FLAG ,C.PARTY_SITE_ID ,P.STATUS ,R.RELATIONSHIP_TYPE ,R.RELATIONSHIP_CODE ,PR.EMAIL_ADDRESS ,C.DECISION_MAKER_FLAG ,C.REFERENCE_USE_FLAG ,C.RANK ,P.CUSTOMER_KEY ,O.SIC_CODE ,O.SIC_CODE_TYPE ,decode(pr.person_title, NULL, pr.person_title,AL.MEANING) TITLE ,C.JOB_TITLE ,C.JOB_TITLE_CODE ,C.DEPARTMENT ,C.DEPARTMENT_CODE ,C.MANAGED_BY ,C.MAILING_ADDRESS_ID ,C.MAIL_STOP ,O.URL ,P.SALUTATION , P.PERSON_PRE_NAME_ADJUNCT ,P.PERSON_FIRST_NAME ,P.PERSON_MIDDLE_NAME ,P.PERSON_LAST_NAME ,P.PERSON_NAME_SUFFIX ,P.PERSON_TITLE ,P.PERSON_ACADEMIC_TITLE ,P.PERSON_PREVIOUS_LAST_NAME ,P.KNOWN_AS ,P.PERSON_IDEN_TYPE ,P.PERSON_IDENTIFIER ,PR.ADDRESS1 ,PR.ADDRESS2 ,PR.ADDRESS3 ,PR.ADDRESS4 ,PR.CITY ,PR.STATE ,PR.POSTAL_CODE ,PR.PROVINCE ,PR.COUNTY ,PR.COUNTRY ,' ' ,O.DUNS_NUMBER ,O.HQ_BRANCH_IND ,O.TAX_NAME ,O.TAX_REFERENCE ,P.GROUP_TYPE ,C.MATCH_GROUP_ID ,C.NATIVE_LANGUAGE ,C.OTHER_LANGUAGE_1 ,C.OTHER_LANGUAGE_2 ,O.JGZZ_FISCAL_CODE ,R.RELATIONSHIP_ID ,P.ORIG_SYSTEM_REFERENCE ,P.CREATION_DATE ,P.CREATED_BY ,p.created_by CREATED_BY_NAME ,P.LAST_UPDATE_DATE ,P.LAST_UPDATED_BY ,p.last_updated_by LAST_UPDATED_BY_NAME ,P.LAST_UPDATE_LOGIN ,p.last_update_login LAST_UPDATE_LOGIN_NAME ,P.REQUEST_ID ,P.PROGRAM_APPLICATION_ID ,P.PROGRAM_ID ,P.PROGRAM_UPDATE_DATE ,P.WH_UPDATE_DATE ,P.ATTRIBUTE_CATEGORY ,P.ATTRIBUTE1 ,P.ATTRIBUTE2 ,P.ATTRIBUTE3 ,P.ATTRIBUTE4 ,P.ATTRIBUTE5 ,P.ATTRIBUTE6 ,P.ATTRIBUTE7 ,P.ATTRIBUTE8 ,P.ATTRIBUTE9 ,P.ATTRIBUTE10 ,P.ATTRIBUTE11 ,P.ATTRIBUTE12 ,P.ATTRIBUTE13 ,P.ATTRIBUTE14 ,P.ATTRIBUTE15 ,P.ATTRIBUTE16 ,P.ATTRIBUTE17 ,P.ATTRIBUTE18 ,P.ATTRIBUTE19 ,P.ATTRIBUTE20 ,P.ATTRIBUTE21 ,P.ATTRIBUTE22 ,P.ATTRIBUTE23 ,P.ATTRIBUTE24 ,P.GLOBAL_ATTRIBUTE_CATEGORY ,P.GLOBAL_ATTRIBUTE1 ,P.GLOBAL_ATTRIBUTE2 ,P.GLOBAL_ATTRIBUTE4 ,P.GLOBAL_ATTRIBUTE3 ,P.GLOBAL_ATTRIBUTE5 ,P.GLOBAL_ATTRIBUTE6 ,P.GLOBAL_ATTRIBUTE7 ,P.GLOBAL_ATTRIBUTE8 ,P.GLOBAL_ATTRIBUTE9 ,P.GLOBAL_ATTRIBUTE10 ,P.GLOBAL_ATTRIBUTE11 ,P.GLOBAL_ATTRIBUTE12 ,P.GLOBAL_ATTRIBUTE13 ,P.GLOBAL_ATTRIBUTE14 ,P.GLOBAL_ATTRIBUTE15 ,P.GLOBAL_ATTRIBUTE16 ,P.GLOBAL_ATTRIBUTE17 ,P.GLOBAL_ATTRIBUTE18 ,P.GLOBAL_ATTRIBUTE19 ,P.GLOBAL_ATTRIBUTE20 ,hpp.gender ,hcp.phone_country_code ,hcp.phone_area_code ,hcp.phone_number ,hcp.phone_extension ,hocr.role_type ,p.party_id person_party_id ,o.party_name organization_party_name FROM HZ_RELATIONSHIPS R, HZ_PARTIES PR, HZ_PARTIES P, HZ_ORG_CONTACTS C, HZ_PARTIES O, HZ_PERSON_PROFILES HPP, hz_contact_points hcp, hz_org_contact_roles hocr, AR_LOOKUPS AL WHERE R.OBJECT_ID = O.PARTY_ID AND R.SUBJECT_ID = P.PARTY_ID AND C.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID AND al.LOOKUP_TYPE= 'CONTACT_TITLE' AND al.LOOKUP_CODE= nvl(pr.person_title,'MR.') AND R.DIRECTIONAL_FLAG = 'F' AND R.SUBJECT_TYPE = 'PERSON' AND R.OBJECT_TYPE = 'ORGANIZATION' AND P.PARTY_TYPE = 'PERSON' AND R.RELATIONSHIP_CODE IN ('CONTACT','CONTACT_OF','EMPLOYEE_OF') AND R.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED' and P.STATUS = 'A' and R.STATUS = 'A' and O.STATUS = 'A' and PR.party_id = R.party_id and hpp.party_id = p.party_id and hpp.effective_end_date is null AND HPP.ACTUAL_CONTENT_SOURCE = 'SST' and hcp.contact_point_type(+) = 'PHONE' and hcp.owner_table_name(+) = 'HZ_PARTIES' and hcp.owner_table_id (+) = pr.party_id and hcp.status (+) = 'A' and hocr.org_contact_id(+) = c.org_contact_id and hcp.primary_flag (+) = 'Y' and hocr.primary_flag (+ ) = 'Y'

View Text - HTML Formatted

SELECT C.ORG_CONTACT_ID CONTACT_ID
, O.PARTY_ID ORGANIZATION_ID
, R.PARTY_ID
, C.CONTACT_NUMBER
, P.PARTY_NUMBER PARTY_CONTACT_NUMBER
, P.PARTY_NAME PARTY_CONTACT_NAME
, PR.PARTY_TYPE
, P.VALIDATED_FLAG
, C.PARTY_SITE_ID
, P.STATUS
, R.RELATIONSHIP_TYPE
, R.RELATIONSHIP_CODE
, PR.EMAIL_ADDRESS
, C.DECISION_MAKER_FLAG
, C.REFERENCE_USE_FLAG
, C.RANK
, P.CUSTOMER_KEY
, O.SIC_CODE
, O.SIC_CODE_TYPE
, DECODE(PR.PERSON_TITLE
, NULL
, PR.PERSON_TITLE
, AL.MEANING) TITLE
, C.JOB_TITLE
, C.JOB_TITLE_CODE
, C.DEPARTMENT
, C.DEPARTMENT_CODE
, C.MANAGED_BY
, C.MAILING_ADDRESS_ID
, C.MAIL_STOP
, O.URL
, P.SALUTATION
, P.PERSON_PRE_NAME_ADJUNCT
, P.PERSON_FIRST_NAME
, P.PERSON_MIDDLE_NAME
, P.PERSON_LAST_NAME
, P.PERSON_NAME_SUFFIX
, P.PERSON_TITLE
, P.PERSON_ACADEMIC_TITLE
, P.PERSON_PREVIOUS_LAST_NAME
, P.KNOWN_AS
, P.PERSON_IDEN_TYPE
, P.PERSON_IDENTIFIER
, PR.ADDRESS1
, PR.ADDRESS2
, PR.ADDRESS3
, PR.ADDRESS4
, PR.CITY
, PR.STATE
, PR.POSTAL_CODE
, PR.PROVINCE
, PR.COUNTY
, PR.COUNTRY
, ' '
, O.DUNS_NUMBER
, O.HQ_BRANCH_IND
, O.TAX_NAME
, O.TAX_REFERENCE
, P.GROUP_TYPE
, C.MATCH_GROUP_ID
, C.NATIVE_LANGUAGE
, C.OTHER_LANGUAGE_1
, C.OTHER_LANGUAGE_2
, O.JGZZ_FISCAL_CODE
, R.RELATIONSHIP_ID
, P.ORIG_SYSTEM_REFERENCE
, P.CREATION_DATE
, P.CREATED_BY
, P.CREATED_BY CREATED_BY_NAME
, P.LAST_UPDATE_DATE
, P.LAST_UPDATED_BY
, P.LAST_UPDATED_BY LAST_UPDATED_BY_NAME
, P.LAST_UPDATE_LOGIN
, P.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN_NAME
, P.REQUEST_ID
, P.PROGRAM_APPLICATION_ID
, P.PROGRAM_ID
, P.PROGRAM_UPDATE_DATE
, P.WH_UPDATE_DATE
, P.ATTRIBUTE_CATEGORY
, P.ATTRIBUTE1
, P.ATTRIBUTE2
, P.ATTRIBUTE3
, P.ATTRIBUTE4
, P.ATTRIBUTE5
, P.ATTRIBUTE6
, P.ATTRIBUTE7
, P.ATTRIBUTE8
, P.ATTRIBUTE9
, P.ATTRIBUTE10
, P.ATTRIBUTE11
, P.ATTRIBUTE12
, P.ATTRIBUTE13
, P.ATTRIBUTE14
, P.ATTRIBUTE15
, P.ATTRIBUTE16
, P.ATTRIBUTE17
, P.ATTRIBUTE18
, P.ATTRIBUTE19
, P.ATTRIBUTE20
, P.ATTRIBUTE21
, P.ATTRIBUTE22
, P.ATTRIBUTE23
, P.ATTRIBUTE24
, P.GLOBAL_ATTRIBUTE_CATEGORY
, P.GLOBAL_ATTRIBUTE1
, P.GLOBAL_ATTRIBUTE2
, P.GLOBAL_ATTRIBUTE4
, P.GLOBAL_ATTRIBUTE3
, P.GLOBAL_ATTRIBUTE5
, P.GLOBAL_ATTRIBUTE6
, P.GLOBAL_ATTRIBUTE7
, P.GLOBAL_ATTRIBUTE8
, P.GLOBAL_ATTRIBUTE9
, P.GLOBAL_ATTRIBUTE10
, P.GLOBAL_ATTRIBUTE11
, P.GLOBAL_ATTRIBUTE12
, P.GLOBAL_ATTRIBUTE13
, P.GLOBAL_ATTRIBUTE14
, P.GLOBAL_ATTRIBUTE15
, P.GLOBAL_ATTRIBUTE16
, P.GLOBAL_ATTRIBUTE17
, P.GLOBAL_ATTRIBUTE18
, P.GLOBAL_ATTRIBUTE19
, P.GLOBAL_ATTRIBUTE20
, HPP.GENDER
, HCP.PHONE_COUNTRY_CODE
, HCP.PHONE_AREA_CODE
, HCP.PHONE_NUMBER
, HCP.PHONE_EXTENSION
, HOCR.ROLE_TYPE
, P.PARTY_ID PERSON_PARTY_ID
, O.PARTY_NAME ORGANIZATION_PARTY_NAME
FROM HZ_RELATIONSHIPS R
, HZ_PARTIES PR
, HZ_PARTIES P
, HZ_ORG_CONTACTS C
, HZ_PARTIES O
, HZ_PERSON_PROFILES HPP
, HZ_CONTACT_POINTS HCP
, HZ_ORG_CONTACT_ROLES HOCR
, AR_LOOKUPS AL
WHERE R.OBJECT_ID = O.PARTY_ID
AND R.SUBJECT_ID = P.PARTY_ID
AND C.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
AND AL.LOOKUP_TYPE= 'CONTACT_TITLE'
AND AL.LOOKUP_CODE= NVL(PR.PERSON_TITLE
, 'MR.')
AND R.DIRECTIONAL_FLAG = 'F'
AND R.SUBJECT_TYPE = 'PERSON'
AND R.OBJECT_TYPE = 'ORGANIZATION'
AND P.PARTY_TYPE = 'PERSON'
AND R.RELATIONSHIP_CODE IN ('CONTACT'
, 'CONTACT_OF'
, 'EMPLOYEE_OF')
AND R.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED'
AND P.STATUS = 'A'
AND R.STATUS = 'A'
AND O.STATUS = 'A'
AND PR.PARTY_ID = R.PARTY_ID
AND HPP.PARTY_ID = P.PARTY_ID
AND HPP.EFFECTIVE_END_DATE IS NULL
AND HPP.ACTUAL_CONTENT_SOURCE = 'SST'
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.OWNER_TABLE_ID (+) = PR.PARTY_ID
AND HCP.STATUS (+) = 'A'
AND HOCR.ORG_CONTACT_ID(+) = C.ORG_CONTACT_ID
AND HCP.PRIMARY_FLAG (+) = 'Y'
AND HOCR.PRIMARY_FLAG (+ ) = 'Y'