View: WF_HZ_PARTY_ROLES

View Text

SELECT /*+ ORDERED USE_HASH(INLINE
, PL
, L1) */ 'HZ_PARTY:'||TO_CHAR(INLINE.PER_ID)
, NVL(INLINE.SUBJ_NAME
, INLINE.PER_NAME)
, INLINE.PER_NAME
, 'MAILTEXT'
, L1.NLS_LANGUAGE
, L1.NLS_TERRITORY
, INLINE.EMAIL_ADDRESS
, NULL
, 'HZ_PARTY'
, INLINE.PER_ID
, TO_DATE(NULL)
, DECODE(INLINE.STATUS
, 'A'
, 'ACTIVE'
, 'INACTIVE')
, TO_DATE(NULL)
, NULL
, 'Y'
, 9
FROM ( SELECT /*+ NO_MERGE ORDERED USE_HASH(P
, PR
, OC
, SUBJ) INDEX_FFS(OC
, HZ_ORG_CONTACTS_N3) */ NVL2(PR.PARTY_ID
, SUBJ.PARTY_ID
, P.PARTY_ID) PARTY
, P.PARTY_TYPE PARTY_TYPE
, PR.PARTY_ID REL_PARTY_ID
, P.PARTY_ID PER_ID
, P.PARTY_NAME PER_NAME
, SUBJ.PARTY_NAME SUBJ_NAME
, P.EMAIL_ADDRESS EMAIL_ADDRESS
, P.STATUS
FROM HZ_PARTIES P
, HZ_PARTY_RELATIONSHIPS PR
, HZ_ORG_CONTACTS OC
, HZ_PARTIES SUBJ
WHERE P.PARTY_TYPE IN ('PERSON'
, 'PARTY_RELATIONSHIP')
AND P.STATUS IN ('A'
, 'I')
AND PR.PARTY_ID(+) = P.PARTY_ID
AND PR.STATUS(+) = 'A'
AND PR.SUBJECT_ID = SUBJ.PARTY_ID(+)
AND PR.PARTY_RELATIONSHIP_ID = OC.PARTY_RELATIONSHIP_ID(+)
AND (P.PARTY_TYPE = 'PERSON' OR OC.PARTY_RELATIONSHIP_ID IS NOT NULL) ) INLINE
, HZ_PERSON_LANGUAGE PL
, FND_LANGUAGES L1
WHERE INLINE.PARTY = PL.PARTY_ID(+)
AND PL.PRIMARY_LANGUAGE_INDICATOR(+) = 'Y'
AND PL.STATUS(+) = 'A'
AND PL.LANGUAGE_NAME = L1.LANGUAGE_CODE(+)

Columns