VIEW: APPS.JTF_TASK_PARTY_CONTACTS_V

Source

View Text - Preformatted

SELECT PARTY_SUBJECT.PARTY_ID SUBJECT_PARTY_ID , PARTY_SUBJECT.PARTY_NUMBER SUBJECT_PARTY_NUMBER , PARTY_SUBJECT.PARTY_NAME SUBJECT_PARTY_NAME , PARTY_SUBJECT.VALIDATED_FLAG , PARTY_SUBJECT.PERSON_PRE_NAME_ADJUNCT , PARTY_SUBJECT.PERSON_FIRST_NAME , PARTY_SUBJECT.PERSON_MIDDLE_NAME , PARTY_SUBJECT.PERSON_LAST_NAME , PARTY_SUBJECT.PERSON_NAME_SUFFIX , PARTY_SUBJECT.PERSON_TITLE , PARTY_SUBJECT.PERSON_ACADEMIC_TITLE , PARTY_SUBJECT.PERSON_PREVIOUS_LAST_NAME , PARTY_SUBJECT.KNOWN_AS , PARTY_SUBJECT.PERSON_IDEN_TYPE , PARTY_SUBJECT.PERSON_IDENTIFIER , PARTY_SUBJECT.JGZZ_FISCAL_CODE , PARTY_SUBJECT.TAX_NAME , PARTY_SUBJECT.TAX_REFERENCE , REL.START_DATE , REL.END_DATE , REL.SUBJECT_ID , REL.RELATIONSHIP_TYPE , REL.RELATIONSHIP_ID , REL.PARTY_ID , REL.OBJECT_ID ,PARTY_REL.EMAIL_ADDRESS , REL.STATUS , PARTY_REL.ADDRESS1 , PARTY_REL.ADDRESS2 , PARTY_REL.ADDRESS3 , PARTY_REL.ADDRESS4 , PARTY_REL.CITY , PARTY_REL.POSTAL_CODE , PARTY_REL.STATE , PARTY_REL.COUNTRY , HCP.PHONE_COUNTRY_CODE , HCP.PHONE_AREA_CODE , HCP.PHONE_NUMBER , HCP.PHONE_EXTENSION ,ORG_CONTACT.JOB_TITLE FROM HZ_RELATIONSHIPS REL , HZ_PARTIES PARTY_SUBJECT ,HZ_PARTIES PARTY_REL ,HZ_CONTACT_POINTS HCP ,HZ_ORG_CONTACTS ORG_CONTACT WHERE REL.SUBJECT_ID = PARTY_SUBJECT.PARTY_ID AND REL.PARTY_ID = PARTY_REL.PARTY_ID AND DIRECTIONAL_FLAG = 'F' AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND EXISTS ( select 'Y' from hz_code_assignments code, hz_relationship_types reltype where reltype.relationship_type = rel.relationship_type and code.owner_table_id = reltype.relationship_type_id and code.class_category = 'RELATIONSHIP_TYPE_GROUP' and code.class_code = 'PARTY_REL_GRP_CONTACTS' and code.owner_table_name = 'HZ_RELATIONSHIP_TYPES') AND HCP.owner_table_id (+) = PARTY_REL.PARTY_ID AND HCP.owner_table_name (+) = 'HZ_PARTIES' AND HCP.contact_point_type (+) = 'PHONE' AND HCP.PRIMARY_FLAG (+) = 'Y' AND HCP.STATUS (+) = 'A' AND ORG_CONTACT.party_relationship_id (+) = rel.relationship_id

View Text - HTML Formatted

SELECT PARTY_SUBJECT.PARTY_ID SUBJECT_PARTY_ID
, PARTY_SUBJECT.PARTY_NUMBER SUBJECT_PARTY_NUMBER
, PARTY_SUBJECT.PARTY_NAME SUBJECT_PARTY_NAME
, PARTY_SUBJECT.VALIDATED_FLAG
, PARTY_SUBJECT.PERSON_PRE_NAME_ADJUNCT
, PARTY_SUBJECT.PERSON_FIRST_NAME
, PARTY_SUBJECT.PERSON_MIDDLE_NAME
, PARTY_SUBJECT.PERSON_LAST_NAME
, PARTY_SUBJECT.PERSON_NAME_SUFFIX
, PARTY_SUBJECT.PERSON_TITLE
, PARTY_SUBJECT.PERSON_ACADEMIC_TITLE
, PARTY_SUBJECT.PERSON_PREVIOUS_LAST_NAME
, PARTY_SUBJECT.KNOWN_AS
, PARTY_SUBJECT.PERSON_IDEN_TYPE
, PARTY_SUBJECT.PERSON_IDENTIFIER
, PARTY_SUBJECT.JGZZ_FISCAL_CODE
, PARTY_SUBJECT.TAX_NAME
, PARTY_SUBJECT.TAX_REFERENCE
, REL.START_DATE
, REL.END_DATE
, REL.SUBJECT_ID
, REL.RELATIONSHIP_TYPE
, REL.RELATIONSHIP_ID
, REL.PARTY_ID
, REL.OBJECT_ID
, PARTY_REL.EMAIL_ADDRESS
, REL.STATUS
, PARTY_REL.ADDRESS1
, PARTY_REL.ADDRESS2
, PARTY_REL.ADDRESS3
, PARTY_REL.ADDRESS4
, PARTY_REL.CITY
, PARTY_REL.POSTAL_CODE
, PARTY_REL.STATE
, PARTY_REL.COUNTRY
, HCP.PHONE_COUNTRY_CODE
, HCP.PHONE_AREA_CODE
, HCP.PHONE_NUMBER
, HCP.PHONE_EXTENSION
, ORG_CONTACT.JOB_TITLE
FROM HZ_RELATIONSHIPS REL
, HZ_PARTIES PARTY_SUBJECT
, HZ_PARTIES PARTY_REL
, HZ_CONTACT_POINTS HCP
, HZ_ORG_CONTACTS ORG_CONTACT
WHERE REL.SUBJECT_ID = PARTY_SUBJECT.PARTY_ID
AND REL.PARTY_ID = PARTY_REL.PARTY_ID
AND DIRECTIONAL_FLAG = 'F'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND EXISTS ( SELECT 'Y'
FROM HZ_CODE_ASSIGNMENTS CODE
, HZ_RELATIONSHIP_TYPES RELTYPE
WHERE RELTYPE.RELATIONSHIP_TYPE = REL.RELATIONSHIP_TYPE
AND CODE.OWNER_TABLE_ID = RELTYPE.RELATIONSHIP_TYPE_ID
AND CODE.CLASS_CATEGORY = 'RELATIONSHIP_TYPE_GROUP'
AND CODE.CLASS_CODE = 'PARTY_REL_GRP_CONTACTS'
AND CODE.OWNER_TABLE_NAME = 'HZ_RELATIONSHIP_TYPES')
AND HCP.OWNER_TABLE_ID (+) = PARTY_REL.PARTY_ID
AND HCP.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE (+) = 'PHONE'
AND HCP.PRIMARY_FLAG (+) = 'Y'
AND HCP.STATUS (+) = 'A'
AND ORG_CONTACT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID