VIEW: APPS.CSF_M_PARTY_SITES_V

Source

View Text - Preformatted

SELECT acc.access_id, hz_ps.party_site_id, acc.user_id, party.party_id, party.party_number, party.party_name, location.country, location.address1, location.address2, location.address3, location.address4, location.city, location.postal_code, location.state, location.address1 || DECODE(location.address1,NULL,'',',') || location.city || DECODE(location.city,NULL,'',',') || location.state AS location, phone_site.phone_country_code || ' ' || phone_site.phone_area_code || ' ' || phone_site.phone_number || decode(phone_site.phone_extension, null, null, ' (') || phone_site.phone_extension || decode(phone_site.phone_extension, null, null, ')') as PHONE, party.party_type as PARTY_TYPE, hz_ps.START_DATE_ACTIVE , hz_ps.END_DATE_ACTIVE, location.ADDRESS_LINES_PHONETIC,location.COUNTY, location.PROVINCE, hz_ps.party_site_number, hz_ps.IDENTIFYING_ADDRESS_FLAG AS PRIMARY_FLAG, party.KNOWN_AS, hz_ps.location_id, hz_ps.MAILSTOP as EMAIL_ADDRESS, hz_ps.PARTY_SITE_NAME from CSM_PARTY_SITES_ACC acc, HZ_PARTY_SITES hz_ps, HZ_PARTIES party, HZ_LOCATIONS location, HZ_CONTACT_POINTS phone_site WHERE acc.user_id = asg_base.get_user_id and hz_ps.party_site_id = acc.party_site_id and party.party_id = acc.party_id and location.location_id = hz_ps.location_id and phone_site.owner_table_id (+) = hz_ps.party_site_id and phone_site.status (+) = 'A' and phone_site.primary_flag (+) = 'Y' and phone_site.contact_point_type (+) = 'PHONE' and phone_site.owner_table_name (+) = 'HZ_PARTY_SITES'

View Text - HTML Formatted

SELECT ACC.ACCESS_ID
, HZ_PS.PARTY_SITE_ID
, ACC.USER_ID
, PARTY.PARTY_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_NAME
, LOCATION.COUNTRY
, LOCATION.ADDRESS1
, LOCATION.ADDRESS2
, LOCATION.ADDRESS3
, LOCATION.ADDRESS4
, LOCATION.CITY
, LOCATION.POSTAL_CODE
, LOCATION.STATE
, LOCATION.ADDRESS1 || DECODE(LOCATION.ADDRESS1
, NULL
, ''
, '
, ') || LOCATION.CITY || DECODE(LOCATION.CITY
, NULL
, ''
, '
, ') || LOCATION.STATE AS LOCATION
, PHONE_SITE.PHONE_COUNTRY_CODE || ' ' || PHONE_SITE.PHONE_AREA_CODE || ' ' || PHONE_SITE.PHONE_NUMBER || DECODE(PHONE_SITE.PHONE_EXTENSION
, NULL
, NULL
, ' (') || PHONE_SITE.PHONE_EXTENSION || DECODE(PHONE_SITE.PHONE_EXTENSION
, NULL
, NULL
, ')') AS PHONE
, PARTY.PARTY_TYPE AS PARTY_TYPE
, HZ_PS.START_DATE_ACTIVE
, HZ_PS.END_DATE_ACTIVE
, LOCATION.ADDRESS_LINES_PHONETIC
, LOCATION.COUNTY
, LOCATION.PROVINCE
, HZ_PS.PARTY_SITE_NUMBER
, HZ_PS.IDENTIFYING_ADDRESS_FLAG AS PRIMARY_FLAG
, PARTY.KNOWN_AS
, HZ_PS.LOCATION_ID
, HZ_PS.MAILSTOP AS EMAIL_ADDRESS
, HZ_PS.PARTY_SITE_NAME
FROM CSM_PARTY_SITES_ACC ACC
, HZ_PARTY_SITES HZ_PS
, HZ_PARTIES PARTY
, HZ_LOCATIONS LOCATION
, HZ_CONTACT_POINTS PHONE_SITE
WHERE ACC.USER_ID = ASG_BASE.GET_USER_ID
AND HZ_PS.PARTY_SITE_ID = ACC.PARTY_SITE_ID
AND PARTY.PARTY_ID = ACC.PARTY_ID
AND LOCATION.LOCATION_ID = HZ_PS.LOCATION_ID
AND PHONE_SITE.OWNER_TABLE_ID (+) = HZ_PS.PARTY_SITE_ID
AND PHONE_SITE.STATUS (+) = 'A'
AND PHONE_SITE.PRIMARY_FLAG (+) = 'Y'
AND PHONE_SITE.CONTACT_POINT_TYPE (+) = 'PHONE'
AND PHONE_SITE.OWNER_TABLE_NAME (+) = 'HZ_PARTY_SITES'