XLE_LEGAL_CONTACTS

Details

Schema: FUSION
Object owner: XLE
Object type: VIEW

Columns

Name
ENTITY_ID ENTITY_TYPE ENTITY_PARTY_ID CONTACT_PARTY_ID CONTACT_NAME CONTACT_LEGAL_ID ROLE TITLE CONTACT_NUMBER JOB_TITLE

Name

ENTITY_ID

ENTITY_TYPE

ENTITY_PARTY_ID

CONTACT_PARTY_ID

CONTACT_NAME

CONTACT_LEGAL_ID

ROLE

TITLE

CONTACT_NUMBER

JOB_TITLE

Query

SQL_Statement
SELECT xep.legal_entity_id entity_id, 'LEGAL_ENTITY' entity_type, rel.object_id entity_party_id, rel.subject_id CONTACT_PARTY_ID, per.party_name contact_name, per.jgzz_fiscal_code contact_legal_id, 'LEGAL CONTACT' role, hzpp.person_pre_name_adjunct title, per.party_number contact_number, XLE_CONTACT_GRP.concat_contact_roles (rel.subject_id, rel.object_id) job_title FROM HZ_PARTIES per, xle_entity_profiles xep, HZ_RELATIONSHIPS rel, hz_person_profiles hzpp, HZ_ORG_CONTACTS con WHERE rel.relationship_code = 'CONTACT_OF' AND rel.object_id = xep.party_id AND per.party_id = hzpp.party_id AND TRUNC(SYSDATE) BETWEEN hzpp.effective_start_date AND hzpp.effective_end_date AND hzpp.effective_latest_change = 'Y' AND rel.relationship_type = 'CONTACT' AND rel.directional_flag = 'F' AND rel.subject_table_name = 'HZ_PARTIES' AND rel.subject_type = 'PERSON' AND rel.subject_id = per.party_id AND rel.object_table_name = 'HZ_PARTIES' AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE) AND rel.relationship_id = con.party_relationship_id AND rel.object_type = 'ORGANIZATION' UNION SELECT etb.establishment_id entity_id, 'ESTABLISHMENT' entity_type, rel.object_id entity_party_id, rel.subject_id CONTACT_PARTY_ID, per.party_name contact_name, per.jgzz_fiscal_code contact_legal_id, 'LEGAL CONTACT' role, hzpp.person_pre_name_adjunct title, per.party_number contact_number, XLE_CONTACT_GRP.concat_contact_roles (rel.subject_id, rel.object_id) job_title FROM HZ_PARTIES per, xle_etb_profiles etb, HZ_RELATIONSHIPS rel, hz_person_profiles hzpp, HZ_ORG_CONTACTS con WHERE rel.relationship_code = 'CONTACT_OF' AND rel.object_id = etb.party_id AND per.party_id = hzpp.party_id AND TRUNC(SYSDATE) BETWEEN hzpp.effective_start_date AND hzpp.effective_end_date AND hzpp.effective_latest_change = 'Y' AND rel.relationship_type = 'CONTACT' AND rel.directional_flag = 'F' AND rel.subject_table_name = 'HZ_PARTIES' AND rel.subject_type = 'PERSON' AND rel.subject_id = per.party_id AND rel.object_table_name = 'HZ_PARTIES' AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE) AND rel.relationship_id = con.party_relationship_id AND rel.object_type = 'ORGANIZATION'

SQL_Statement

SELECT xep.legal_entity_id entity_id,

'LEGAL_ENTITY' entity_type,

rel.object_id entity_party_id,

rel.subject_id CONTACT_PARTY_ID,

per.party_name contact_name,

per.jgzz_fiscal_code contact_legal_id,

'LEGAL CONTACT' role,

hzpp.person_pre_name_adjunct title,

per.party_number contact_number,

XLE_CONTACT_GRP.concat_contact_roles

(rel.subject_id,

rel.object_id) job_title

FROM HZ_PARTIES per,

xle_entity_profiles xep,

HZ_RELATIONSHIPS rel,

hz_person_profiles hzpp,

HZ_ORG_CONTACTS con

WHERE rel.relationship_code = 'CONTACT_OF'

AND rel.object_id = xep.party_id

AND per.party_id = hzpp.party_id

AND TRUNC(SYSDATE) BETWEEN hzpp.effective_start_date AND

hzpp.effective_end_date

AND hzpp.effective_latest_change = 'Y'

AND rel.relationship_type = 'CONTACT'

AND rel.directional_flag = 'F'

AND rel.subject_table_name = 'HZ_PARTIES'

AND rel.subject_type = 'PERSON'

AND rel.subject_id = per.party_id

AND rel.object_table_name = 'HZ_PARTIES'

AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE)

AND rel.relationship_id = con.party_relationship_id

AND rel.object_type = 'ORGANIZATION'

UNION

SELECT etb.establishment_id entity_id,

'ESTABLISHMENT' entity_type,