View: ICX_CUSTOMER_CONTACT_LOV

View Text

SELECT PARTY.PARTY_NAME
, PARTY.PARTY_NUMBER
, PARTY.PARTY_ID
, LOOKUP.MEANING AS IS_CONTACT_FLAG
, NULL AS ORG_PARTY_NAME
FROM HZ_PARTIES PARTY
, FND_LOOKUPS LOOKUP
WHERE PARTY.STATUS = 'A'
AND PARTY.PARTY_TYPE = 'PERSON'
AND LOOKUP.LOOKUP_TYPE = 'YES_NO'
AND LOOKUP.LOOKUP_CODE = 'N' UNION SELECT PARTY3.PARTY_NAME
, PARTY3.PARTY_NUMBER
, PARTY5.PARTY_ID
, LOOKUP.MEANING AS IS_CONTACT_FLAG
, PARTY4.PARTY_NAME AS ORG_PARTY_NAME
FROM HZ_PARTY_RELATIONSHIPS PREL
, HZ_PARTIES PARTY3
, HZ_PARTIES PARTY4
, HZ_PARTIES PARTY5
, FND_LOOKUPS LOOKUP
WHERE LOOKUP.LOOKUP_TYPE = 'YES_NO'
AND LOOKUP.LOOKUP_CODE = 'Y'
AND PREL.PARTY_ID = PARTY5.PARTY_ID
AND PARTY5.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND PARTY5.STATUS = 'A'
AND TRUNC( PREL.START_DATE) <= TRUNC(SYSDATE)
AND TRUNC(NVL(PREL.END_DATE
, SYSDATE)) >= TRUNC(SYSDATE)
AND PREL.SUBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'PERSON'
AND PARTY3.STATUS = 'A'
AND PREL.OBJECT_ID = PARTY4.PARTY_ID
AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
AND PARTY4.STATUS = 'A'
AND PREL.PARTY_RELATIONSHIP_ID IN (SELECT PARTY_RELATIONSHIP_ID
FROM HZ_ORG_CONTACTS ORG_CON
WHERE PREL.PARTY_RELATIONSHIP_ID = ORG_CON.PARTY_RELATIONSHIP_ID
AND ORG_CON.STATUS ='A' ) ORDER BY 1
, 2
, 4

Columns