VIEW: APPS.CS_SR_INCIDENT_ADDRESS_V

Source

View Text - Preformatted

SELECT p.party_id ,p.party_name,p.party_number,p.party_type, s.party_site_id, decode(s.party_Site_id,null,l.location_id,s.party_Site_id) location_id, decode(s.party_Site_id,null,'HZ_LOCATION','HZ_PARTY_SITE') location_type_code , l.address1, l.address2, l.address3, l.address4, 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, s.created_by_module, s.status site_status,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' and cp.status = 'A' ) 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(+) and p.status(+) ='A'

View Text - HTML Formatted

SELECT P.PARTY_ID
, P.PARTY_NAME
, P.PARTY_NUMBER
, P.PARTY_TYPE
, S.PARTY_SITE_ID
, DECODE(S.PARTY_SITE_ID
, NULL
, L.LOCATION_ID
, S.PARTY_SITE_ID) LOCATION_ID
, DECODE(S.PARTY_SITE_ID
, NULL
, 'HZ_LOCATION'
, 'HZ_PARTY_SITE') LOCATION_TYPE_CODE
, L.ADDRESS1
, L.ADDRESS2
, L.ADDRESS3
, L.ADDRESS4
, 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
, S.CREATED_BY_MODULE
, S.STATUS SITE_STATUS
, 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'
AND CP.STATUS = 'A' ) 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(+)
AND P.STATUS(+) ='A'