VIEW: APPS.OKI_HQTCONT_DTL_V

Source

View Text - Preformatted

SELECT SHD.CHR_ID , SUBSTR( P.PERSON_LAST_NAME || ', ' || P.PERSON_FIRST_NAME, 1, 120 ) CONTACT , EMAIL.EMAIL_ADDRESS EMAIL , PHONE.PHONE_NUMBER PHONE , FAX.PHONE_NUMBER FAX , SUBSTR( ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS( NULL, L.ADDRESS1, L.ADDRESS2, L.ADDRESS3, L.ADDRESS4, L.CITY, L.COUNTY, L.STATE, L.PROVINCE, L.POSTAL_CODE, NULL, L.COUNTRY, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', 'N', 80, 1, 1 ), 1, 80 ) ADDRESS FROM OKS_K_HEADERS_B SHD , HZ_CUST_ACCOUNT_ROLES CAR , HZ_RELATIONSHIPS RQTO , HZ_PARTIES P , HZ_CONTACT_POINTS EMAIL , HZ_CONTACT_POINTS PHONE , HZ_CONTACT_POINTS FAX , HZ_CUST_ACCT_SITES_ALL CAS , HZ_PARTY_SITES PS , HZ_LOCATIONS L WHERE CAR.CUST_ACCOUNT_ROLE_ID = SHD.QUOTE_TO_CONTACT_ID AND CAR.ROLE_TYPE = 'CONTACT' AND RQTO.PARTY_ID = CAR.PARTY_ID AND RQTO.CONTENT_SOURCE_TYPE = 'USER_ENTERED' AND RQTO.SUBJECT_TYPE = 'PERSON' AND RQTO.RELATIONSHIP_CODE = 'CONTACT_OF' AND RQTO.SUBJECT_ID = P.PARTY_ID AND EMAIL.CONTACT_POINT_ID(+) = SHD.QUOTE_TO_EMAIL_ID AND PHONE.CONTACT_POINT_ID(+) = SHD.QUOTE_TO_PHONE_ID AND FAX.CONTACT_POINT_ID(+) = SHD.QUOTE_TO_FAX_ID AND CAS.CUST_ACCT_SITE_ID(+) = SHD.QUOTE_TO_SITE_ID AND PS.PARTY_SITE_ID(+) = CAS.PARTY_SITE_ID AND L.LOCATION_ID(+) = PS.LOCATION_ID

View Text - HTML Formatted

SELECT SHD.CHR_ID
, SUBSTR( P.PERSON_LAST_NAME || '
, ' || P.PERSON_FIRST_NAME
, 1
, 120 ) CONTACT
, EMAIL.EMAIL_ADDRESS EMAIL
, PHONE.PHONE_NUMBER PHONE
, FAX.PHONE_NUMBER FAX
, SUBSTR( ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS( NULL
, L.ADDRESS1
, L.ADDRESS2
, L.ADDRESS3
, L.ADDRESS4
, L.CITY
, L.COUNTY
, L.STATE
, L.PROVINCE
, L.POSTAL_CODE
, NULL
, L.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 80
, 1
, 1 )
, 1
, 80 ) ADDRESS
FROM OKS_K_HEADERS_B SHD
, HZ_CUST_ACCOUNT_ROLES CAR
, HZ_RELATIONSHIPS RQTO
, HZ_PARTIES P
, HZ_CONTACT_POINTS EMAIL
, HZ_CONTACT_POINTS PHONE
, HZ_CONTACT_POINTS FAX
, HZ_CUST_ACCT_SITES_ALL CAS
, HZ_PARTY_SITES PS
, HZ_LOCATIONS L
WHERE CAR.CUST_ACCOUNT_ROLE_ID = SHD.QUOTE_TO_CONTACT_ID
AND CAR.ROLE_TYPE = 'CONTACT'
AND RQTO.PARTY_ID = CAR.PARTY_ID
AND RQTO.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
AND RQTO.SUBJECT_TYPE = 'PERSON'
AND RQTO.RELATIONSHIP_CODE = 'CONTACT_OF'
AND RQTO.SUBJECT_ID = P.PARTY_ID
AND EMAIL.CONTACT_POINT_ID(+) = SHD.QUOTE_TO_EMAIL_ID
AND PHONE.CONTACT_POINT_ID(+) = SHD.QUOTE_TO_PHONE_ID
AND FAX.CONTACT_POINT_ID(+) = SHD.QUOTE_TO_FAX_ID
AND CAS.CUST_ACCT_SITE_ID(+) = SHD.QUOTE_TO_SITE_ID
AND PS.PARTY_SITE_ID(+) = CAS.PARTY_SITE_ID
AND L.LOCATION_ID(+) = PS.LOCATION_ID