VIEW: APPS.AMS_LT_ORG_CONTACT_DETAILS_V

Source

View Text - Preformatted

SELECT O.PARTY_ID ORGANIZATION_ID , R.PARTY_ID , P.PARTY_NUMBER PARTY_CONTACT_NUMBER , P.PARTY_NAME PARTY_CONTACT_NAME , P.PARTY_TYPE , R.RELATIONSHIP_TYPE , R.RELATIONSHIP_CODE , PR.EMAIL_ADDRESS , P.CUSTOMER_KEY , P.SIC_CODE , P.SIC_CODE_TYPE , P.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 , R.RELATIONSHIP_ID, PR.ADDRESS1, PR.ADDRESS2, PR.ADDRESS3, PR.ADDRESS4, PR.COUNTY, PR.COUNTRY, PR.CITY, PR.POSTAL_CODE, PR.STATE, PR.PROVINCE, O.DUNS_NUMBER_C DUNS_NUMBER, O.REFERENCE_USE_FLAG, O.TAX_REFERENCE, O.ORIG_SYSTEM_REFERENCE, O.JGZZ_FISCAL_CODE FROM HZ_RELATIONSHIPS R , HZ_PARTIES PR , HZ_PARTIES P , HZ_PARTIES O WHERE R.OBJECT_ID = O.PARTY_ID AND R.SUBJECT_ID = P.PARTY_ID 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 P.STATUS = 'A' and R.STATUS = 'A' and O.STATUS = 'A' and PR.party_id = R.party_id

View Text - HTML Formatted

SELECT O.PARTY_ID ORGANIZATION_ID
, R.PARTY_ID
, P.PARTY_NUMBER PARTY_CONTACT_NUMBER
, P.PARTY_NAME PARTY_CONTACT_NAME
, P.PARTY_TYPE
, R.RELATIONSHIP_TYPE
, R.RELATIONSHIP_CODE
, PR.EMAIL_ADDRESS
, P.CUSTOMER_KEY
, P.SIC_CODE
, P.SIC_CODE_TYPE
, P.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
, R.RELATIONSHIP_ID
, PR.ADDRESS1
, PR.ADDRESS2
, PR.ADDRESS3
, PR.ADDRESS4
, PR.COUNTY
, PR.COUNTRY
, PR.CITY
, PR.POSTAL_CODE
, PR.STATE
, PR.PROVINCE
, O.DUNS_NUMBER_C DUNS_NUMBER
, O.REFERENCE_USE_FLAG
, O.TAX_REFERENCE
, O.ORIG_SYSTEM_REFERENCE
, O.JGZZ_FISCAL_CODE
FROM HZ_RELATIONSHIPS R
, HZ_PARTIES PR
, HZ_PARTIES P
, HZ_PARTIES O
WHERE R.OBJECT_ID = O.PARTY_ID
AND R.SUBJECT_ID = P.PARTY_ID
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 P.STATUS = 'A'
AND R.STATUS = 'A'
AND O.STATUS = 'A'
AND PR.PARTY_ID = R.PARTY_ID