VIEW: APPS.XLE_LEGAL_CONTACTS_V

Source

View Text - Preformatted

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    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

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    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 

View Text - HTML Formatted

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 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 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 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