View: XLE_LEGAL_CONTACTS_V

Product:	XLE - Legal Entity Configurator
Description:
Implementation/DBA Data:	APPS.XLE_LEGAL_CONTACTS_V

View Text

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