VIEW: APPS.OKI_QTO_V

Source

View Text - Preformatted

SELECT shd.chr_id , shd.contract_number , shd.contract_number_modifier , shd.complete_contract_number , shd.quote_to_contact_id , shd.quote_to_site_id , p.party_id , car.cust_account_id , shd.quote_to_email_id , shd.quote_to_phone_id , shd.quote_to_fax_id , pc.party_name customer_name , 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 , shd.creation_date , shd.last_update_date FROM OKI_SALES_K_HDRS SHD,HZ_CUST_ACCOUNT_ROLES CAR,HZ_CUST_ACCOUNTS CA,HZ_RELATIONSHIPS RQTO,HZ_PARTIES P,HZ_PARTIES PC,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 ca.cust_account_id = car.cust_account_id and pc.party_id = ca.party_id 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
, SHD.CONTRACT_NUMBER
, SHD.CONTRACT_NUMBER_MODIFIER
, SHD.COMPLETE_CONTRACT_NUMBER
, SHD.QUOTE_TO_CONTACT_ID
, SHD.QUOTE_TO_SITE_ID
, P.PARTY_ID
, CAR.CUST_ACCOUNT_ID
, SHD.QUOTE_TO_EMAIL_ID
, SHD.QUOTE_TO_PHONE_ID
, SHD.QUOTE_TO_FAX_ID
, PC.PARTY_NAME CUSTOMER_NAME
, 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
, SHD.CREATION_DATE
, SHD.LAST_UPDATE_DATE
FROM OKI_SALES_K_HDRS SHD
, HZ_CUST_ACCOUNT_ROLES CAR
, HZ_CUST_ACCOUNTS CA
, HZ_RELATIONSHIPS RQTO
, HZ_PARTIES P
, HZ_PARTIES PC
, 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 CA.CUST_ACCOUNT_ID = CAR.CUST_ACCOUNT_ID
AND PC.PARTY_ID = CA.PARTY_ID
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