VIEW: APPS.JTF_RS_PARTNERS_VL

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'