View: JTF_RS_PARTNERS_VL

Product:	JTF - CRM Foundation
Description:	The details of parners that can be imported as resource or are already present as resource
Implementation/DBA Data:	APPS.JTF_RS_PARTNERS_VL

View Text

SELECT PARTY.PARTY_ID PARTY_ID
, PARTY.PARTY_NUMBER PARTY_NUMBER
, PARTY.PARTY_NAME PARTY_NAME
, PARTY_SITE.PARTY_SITE_ID PARTY_SITE_ID
, LOC.LOCATION_ID PARTY_SITE_LOCATION_ID
, LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.CITY CITY
, LOC.POSTAL_CODE ZIP_CODE
, LOC.STATE STATE
, LOC.PROVINCE PROVINCE
, LOC.COUNTY COUNTY
, LOC.COUNTRY COUNTRY
, CT_POINT.EMAIL_ADDRESS EMAIL
, CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE
, CT_POINT1.PHONE_EXTENSION PHONE_EXTENSION
, CT_POINT1.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE
, REL.OBJECT_ID ORG_ID
, PARTY.PARTY_NAME ORG_NAME
FROM HZ_PARTIES PARTY
, HZ_PARTIES PARTY2
, HZ_PARTIES PARTY3
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_RELATIONSHIPS REL
, HZ_CONTACT_POINTS CT_POINT
, HZ_CONTACT_POINTS CT_POINT1
WHERE (PARTY.PARTY_TYPE = 'ORGANIZATION'
AND PARTY.PARTY_ID = REL.SUBJECT_ID)
AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF'
, 'VAD_OF'
, 'VAD_VENDOR_OF'
, 'THIRD_PARTY_FOR'
, 'INDIRECTLY_MANAGES_CUSTOMER'
, 'CUSTOMER_INDIRECTLY_MANAGED_BY')
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.STATUS = 'A'
AND PARTY.STATUS = 'A'
AND PARTY2.STATUS = 'A'
AND PARTY3.STATUS = 'A'
AND REL.SUBJECT_ID = PARTY2.PARTY_ID
AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
AND REL.OBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
AND PARTY_SITE.PARTY_ID (+) = PARTY.PARTY_ID
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID (+)
AND CT_POINT.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND CT_POINT.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
AND CT_POINT.PRIMARY_FLAG (+)= 'Y'
AND CT_POINT.STATUS (+)= 'A'
AND CT_POINT.CONTACT_POINT_TYPE (+)= 'EMAIL'
AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
AND CT_POINT1.STATUS (+)= 'A'
AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE' UNION SELECT PARTY.PARTY_ID PARTY_ID
, PARTY.PARTY_NUMBER PARTY_NUMBER
, PARTY.PARTY_NAME PARTY_NAME
, PARTY_SITE.PARTY_SITE_ID PARTY_SITE_ID
, LOC.LOCATION_ID PARTY_SITE_LOCATION_ID
, LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.CITY CITY
, LOC.POSTAL_CODE ZIP_CODE
, LOC.STATE STATE
, LOC.PROVINCE PROVINCE
, LOC.COUNTY COUNTY
, LOC.COUNTRY COUNTRY
, CT_POINT.EMAIL_ADDRESS EMAIL
, CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE
, CT_POINT1.PHONE_EXTENSION PHONE_EXTENSION
, CT_POINT1.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE
, REL.OBJECT_ID ORG_ID
, PARTY.PARTY_NAME ORG_NAME
FROM HZ_PARTIES PARTY
, HZ_PARTIES PARTY2
, HZ_PARTIES PARTY3
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_RELATIONSHIPS REL
, HZ_CONTACT_POINTS CT_POINT
, HZ_CONTACT_POINTS CT_POINT1
WHERE (PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND PARTY.PARTY_ID = REL.PARTY_ID )
AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF'
, 'VAD_OF'
, 'VAD_VENDOR_OF'
, 'THIRD_PARTY_FOR'
, 'INDIRECTLY_MANAGES_CUSTOMER'
, 'CUSTOMER_INDIRECTLY_MANAGED_BY')
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.STATUS = 'A'
AND PARTY.STATUS = 'A'
AND PARTY2.STATUS = 'A'
AND PARTY3.STATUS = 'A'
AND REL.SUBJECT_ID = PARTY2.PARTY_ID
AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
AND REL.OBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
AND PARTY_SITE.PARTY_ID (+) = PARTY.PARTY_ID
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID (+)
AND CT_POINT.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND CT_POINT.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
AND CT_POINT.PRIMARY_FLAG (+)= 'Y'
AND CT_POINT.STATUS (+)= 'A'
AND CT_POINT.CONTACT_POINT_TYPE (+)= 'EMAIL'
AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
AND CT_POINT1.STATUS (+)= 'A'
AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE'

Columns