VIEW: APPS.AST_REL_V

Source

View Text - Preformatted

SELECT party.rowid, party.party_id rel_party_id, party.party_number, party.party_type, per.party_id person_party_id, org.party_id org_party_id, per.party_type, org.party_type, org.party_name, per.person_first_name, per.person_middle_name, per.person_last_name, per.person_pre_name_adjunct, per.person_name_suffix, per.salutation, party.email_address, party.creation_date, party.created_by, party.last_updated_by, party.last_update_login, party.last_update_date, phone.phone_number, phone.phone_country_code, phone.phone_area_code, phone.phone_extension, lkup1.lookup_code, lkup1.meaning, phone.last_update_date, party.address1, party.address2, party.address3, party.address4, party.city, party.state, party.county, party.province, party.postal_code, party.country, party.status, 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.orig_system_reference, orgcont.contact_number, orgcont.department_code, orgcont.department, orgcont.job_title, orgcont.mail_stop, orgcont.decision_maker_flag, orgcont.managed_by, orgcont.reference_use_flag, orgcont.rank, orgcont.native_language, orgcont.other_language_1, orgcont.other_language_2 FROM hz_parties party, hz_parties org, hz_parties per, hz_relationships rel, hz_org_contacts orgcont, hz_contact_points phone, ar_lookups lkup1, ar_lookups lkup2, ar_lookups lkup3, ar_lookups lkup4 WHERE rel.relationship_id = orgcont.party_relationship_id and rel.directional_flag = 'F' and rel.subject_id = per.party_id and rel.object_id = org.party_id and party.party_id = rel.party_id and rel.relationship_code = lkup4.lookup_code and lkup4.lookup_type = 'PARTY_RELATIONS_TYPE' and party.party_type = 'PARTY_RELATIONSHIP' and lkup2.lookup_type(+) = 'CONTACT_TITLE' and lkup2.lookup_code(+) = party.person_pre_name_adjunct and party.status in ('A','I') and lkup3.lookup_code = party.status and lkup3.lookup_type = 'REGISTRY_STATUS' and phone.owner_table_name(+) = 'HZ_PARTIES' and phone.primary_flag(+) = 'Y' and phone.phone_line_type = lkup1.lookup_code(+) and lkup1.lookup_type(+) = 'PHONE_LINE_TYPE' and phone.contact_point_type(+) = 'PHONE' and phone.owner_table_id(+) = party.party_id and rel.subject_table_name = 'HZ_PARTIES' and rel.object_table_name = 'HZ_PARTIES'

View Text - HTML Formatted

SELECT PARTY.ROWID
, PARTY.PARTY_ID REL_PARTY_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_TYPE
, PER.PARTY_ID PERSON_PARTY_ID
, ORG.PARTY_ID ORG_PARTY_ID
, PER.PARTY_TYPE
, ORG.PARTY_TYPE
, ORG.PARTY_NAME
, PER.PERSON_FIRST_NAME
, PER.PERSON_MIDDLE_NAME
, PER.PERSON_LAST_NAME
, PER.PERSON_PRE_NAME_ADJUNCT
, PER.PERSON_NAME_SUFFIX
, PER.SALUTATION
, PARTY.EMAIL_ADDRESS
, PARTY.CREATION_DATE
, PARTY.CREATED_BY
, PARTY.LAST_UPDATED_BY
, PARTY.LAST_UPDATE_LOGIN
, PARTY.LAST_UPDATE_DATE
, PHONE.PHONE_NUMBER
, PHONE.PHONE_COUNTRY_CODE
, PHONE.PHONE_AREA_CODE
, PHONE.PHONE_EXTENSION
, LKUP1.LOOKUP_CODE
, LKUP1.MEANING
, PHONE.LAST_UPDATE_DATE
, PARTY.ADDRESS1
, PARTY.ADDRESS2
, PARTY.ADDRESS3
, PARTY.ADDRESS4
, PARTY.CITY
, PARTY.STATE
, PARTY.COUNTY
, PARTY.PROVINCE
, PARTY.POSTAL_CODE
, PARTY.COUNTRY
, PARTY.STATUS
, 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.ORIG_SYSTEM_REFERENCE
, ORGCONT.CONTACT_NUMBER
, ORGCONT.DEPARTMENT_CODE
, ORGCONT.DEPARTMENT
, ORGCONT.JOB_TITLE
, ORGCONT.MAIL_STOP
, ORGCONT.DECISION_MAKER_FLAG
, ORGCONT.MANAGED_BY
, ORGCONT.REFERENCE_USE_FLAG
, ORGCONT.RANK
, ORGCONT.NATIVE_LANGUAGE
, ORGCONT.OTHER_LANGUAGE_1
, ORGCONT.OTHER_LANGUAGE_2
FROM HZ_PARTIES PARTY
, HZ_PARTIES ORG
, HZ_PARTIES PER
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORGCONT
, HZ_CONTACT_POINTS PHONE
, AR_LOOKUPS LKUP1
, AR_LOOKUPS LKUP2
, AR_LOOKUPS LKUP3
, AR_LOOKUPS LKUP4
WHERE REL.RELATIONSHIP_ID = ORGCONT.PARTY_RELATIONSHIP_ID
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.SUBJECT_ID = PER.PARTY_ID
AND REL.OBJECT_ID = ORG.PARTY_ID
AND PARTY.PARTY_ID = REL.PARTY_ID
AND REL.RELATIONSHIP_CODE = LKUP4.LOOKUP_CODE
AND LKUP4.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE'
AND PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND LKUP2.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND LKUP2.LOOKUP_CODE(+) = PARTY.PERSON_PRE_NAME_ADJUNCT
AND PARTY.STATUS IN ('A'
, 'I')
AND LKUP3.LOOKUP_CODE = PARTY.STATUS
AND LKUP3.LOOKUP_TYPE = 'REGISTRY_STATUS'
AND PHONE.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHONE.PRIMARY_FLAG(+) = 'Y'
AND PHONE.PHONE_LINE_TYPE = LKUP1.LOOKUP_CODE(+)
AND LKUP1.LOOKUP_TYPE(+) = 'PHONE_LINE_TYPE'
AND PHONE.CONTACT_POINT_TYPE(+) = 'PHONE'
AND PHONE.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'