VIEW: APPS.HZ_PARTY_WF_ROLES_V

Source

View Text - Preformatted

SELECT 'HZ_PARTY:'||to_char(inline.per_id) NAME, nvl(inline.subj_name, inline.per_name) DISPLAY_NAME, inline.per_name DESCRIPTION, nvl2(inline.email_address, nvl(substrb(cp.email_format, 1,8), 'MAILTEXT'), 'QUERY') NOTIFICATION_PREFERENCE, l1.nls_language LANGUAGE, l1.nls_territory TERRITORY, substrb(inline.email_address,1,320) EMAIL_ADDRESS, NULL FAX, 'HZ_PARTY' ORIG_SYSTEM, inline.per_id ORIG_SYSTEM_ID, inline.START_DATE START_DATE, DECODE(inline.status,'A','ACTIVE','INACTIVE') STATUS, inline.EXPIRATION_DATE EXPIRATION_DATE, NULL SECURITY_GROUP_ID, 'Y' USER_FLAG, 9 PARTITION_ID FROM ( SELECT 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, p.status, pr.START_DATE START_DATE, pr.end_date EXPIRATION_DATE FROM HZ_PARTIES p, HZ_RELATIONSHIPS pr, HZ_PARTIES subj WHERE p.party_type in ('PERSON','PARTY_RELATIONSHIP') and pr.subject_table_name(+) = 'HZ_PARTIES' and pr.object_table_name(+) = 'HZ_PARTIES' and ((pr.subject_type IS NOT NULL AND (pr.subject_type = 'PERSON' ) AND (pr.object_type = 'ORGANIZATION')) OR (pr.subject_type IS NOT NULL AND (pr.subject_type = 'PERSON') AND (pr.object_type = 'PERSON') AND ( pr.DIRECTIONAL_FLAG = 'F')) OR (pr.subject_type IS NULL)) AND p.status IN ('A', 'I') AND pr.party_id(+) = p.party_id AND pr.status(+) <> 'M' AND pr.status(+) <> 'D' AND pr.subject_id = subj.party_id(+) ) inline, HZ_PERSON_LANGUAGE pl, FND_LANGUAGES l1 , HZ_CONTACT_POINTS cp WHERE cp.owner_table_id(+) = inline.per_id AND cp.owner_table_name(+) = 'HZ_PARTIES' AND cp.contact_point_type(+) = 'EMAIL' AND cp.email_address(+) = inline.email_address and cp.primary_flag(+) = 'Y' AND cp.status(+) = 'A' AND pl.party_id(+) = inline.party and pl.primary_language_indicator(+) = 'Y' and pl.status(+) = 'A' and l1.language_code(+) = pl.language_name

View Text - HTML Formatted

SELECT 'HZ_PARTY:'||TO_CHAR(INLINE.PER_ID) NAME
, NVL(INLINE.SUBJ_NAME
, INLINE.PER_NAME) DISPLAY_NAME
, INLINE.PER_NAME DESCRIPTION
, NVL2(INLINE.EMAIL_ADDRESS
, NVL(SUBSTRB(CP.EMAIL_FORMAT
, 1
, 8)
, 'MAILTEXT')
, 'QUERY') NOTIFICATION_PREFERENCE
, L1.NLS_LANGUAGE LANGUAGE
, L1.NLS_TERRITORY TERRITORY
, SUBSTRB(INLINE.EMAIL_ADDRESS
, 1
, 320) EMAIL_ADDRESS
, NULL FAX
, 'HZ_PARTY' ORIG_SYSTEM
, INLINE.PER_ID ORIG_SYSTEM_ID
, INLINE.START_DATE START_DATE
, DECODE(INLINE.STATUS
, 'A'
, 'ACTIVE'
, 'INACTIVE') STATUS
, INLINE.EXPIRATION_DATE EXPIRATION_DATE
, NULL SECURITY_GROUP_ID
, 'Y' USER_FLAG
, 9 PARTITION_ID
FROM ( SELECT 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
, P.STATUS
, PR.START_DATE START_DATE
, PR.END_DATE EXPIRATION_DATE
FROM HZ_PARTIES P
, HZ_RELATIONSHIPS PR
, HZ_PARTIES SUBJ
WHERE P.PARTY_TYPE IN ('PERSON'
, 'PARTY_RELATIONSHIP')
AND PR.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND PR.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND ((PR.SUBJECT_TYPE IS NOT NULL
AND (PR.SUBJECT_TYPE = 'PERSON' )
AND (PR.OBJECT_TYPE = 'ORGANIZATION')) OR (PR.SUBJECT_TYPE IS NOT NULL
AND (PR.SUBJECT_TYPE = 'PERSON')
AND (PR.OBJECT_TYPE = 'PERSON')
AND ( PR.DIRECTIONAL_FLAG = 'F')) OR (PR.SUBJECT_TYPE IS NULL))
AND P.STATUS IN ('A'
, 'I')
AND PR.PARTY_ID(+) = P.PARTY_ID
AND PR.STATUS(+) <> 'M'
AND PR.STATUS(+) <> 'D'
AND PR.SUBJECT_ID = SUBJ.PARTY_ID(+) ) INLINE
, HZ_PERSON_LANGUAGE PL
, FND_LANGUAGES L1
, HZ_CONTACT_POINTS CP
WHERE CP.OWNER_TABLE_ID(+) = INLINE.PER_ID
AND CP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CP.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND CP.EMAIL_ADDRESS(+) = INLINE.EMAIL_ADDRESS
AND CP.PRIMARY_FLAG(+) = 'Y'
AND CP.STATUS(+) = 'A'
AND PL.PARTY_ID(+) = INLINE.PARTY
AND PL.PRIMARY_LANGUAGE_INDICATOR(+) = 'Y'
AND PL.STATUS(+) = 'A'
AND L1.LANGUAGE_CODE(+) = PL.LANGUAGE_NAME