VIEW: APPS.CS_SR_FIND_INCIDENT_ADDRESS_V

Source

View Text - Preformatted

SELECT s.party_id , p.party_name, p.party_number, p.party_type, s.party_site_id, l.location_id, decode(s.party_Site_id,null,'HZ_LOCATION','HZ_PARTY_SITE') location_type_code , l.address1|| DECODE(l.address2,NULL,NULL,';'||l.address2)|| DECODE(l.address3,NULL,NULL,';'||l.address3) || DECODE(l.address4,NULL,NULL,';'||l.address4) address, l.city,l.state,l.country,l.province,l.county,l.postal_code, s.party_site_name,s.party_site_number,s.addressee, site_phone_v.phone_number site_phone FROM hz_party_sites s, hz_locations l , hz_parties p, (Select owner_table_id, decode(phone_country_code,'','',phone_country_code || '-' ) || decode(phone_area_code,'','',phone_area_code || '-' ) || phone_number phone_number from hz_contact_points cp,hz_party_sites ps where cp.owner_table_id = ps.party_site_id and cp.owner_table_name = 'HZ_PARTY_SITES' and cp.contact_point_type = 'PHONE' ) site_phone_v WHERE s.location_id(+) = l.location_id AND p.party_id(+) = s.party_id and s.party_site_id = site_phone_v.owner_table_id(+)

View Text - HTML Formatted

SELECT S.PARTY_ID
, P.PARTY_NAME
, P.PARTY_NUMBER
, P.PARTY_TYPE
, S.PARTY_SITE_ID
, L.LOCATION_ID
, DECODE(S.PARTY_SITE_ID
, NULL
, 'HZ_LOCATION'
, 'HZ_PARTY_SITE') LOCATION_TYPE_CODE
, L.ADDRESS1|| DECODE(L.ADDRESS2
, NULL
, NULL
, ';'||L.ADDRESS2)|| DECODE(L.ADDRESS3
, NULL
, NULL
, ';'||L.ADDRESS3) || DECODE(L.ADDRESS4
, NULL
, NULL
, ';'||L.ADDRESS4) ADDRESS
, L.CITY
, L.STATE
, L.COUNTRY
, L.PROVINCE
, L.COUNTY
, L.POSTAL_CODE
, S.PARTY_SITE_NAME
, S.PARTY_SITE_NUMBER
, S.ADDRESSEE
, SITE_PHONE_V.PHONE_NUMBER SITE_PHONE
FROM HZ_PARTY_SITES S
, HZ_LOCATIONS L
, HZ_PARTIES P
, (SELECT OWNER_TABLE_ID
, DECODE(PHONE_COUNTRY_CODE
, ''
, ''
, PHONE_COUNTRY_CODE || '-' ) || DECODE(PHONE_AREA_CODE
, ''
, ''
, PHONE_AREA_CODE || '-' ) || PHONE_NUMBER PHONE_NUMBER
FROM HZ_CONTACT_POINTS CP
, HZ_PARTY_SITES PS
WHERE CP.OWNER_TABLE_ID = PS.PARTY_SITE_ID
AND CP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
AND CP.CONTACT_POINT_TYPE = 'PHONE' ) SITE_PHONE_V
WHERE S.LOCATION_ID(+) = L.LOCATION_ID
AND P.PARTY_ID(+) = S.PARTY_ID
AND S.PARTY_SITE_ID = SITE_PHONE_V.OWNER_TABLE_ID(+)