VIEW: APPS.IEX_LS_SIT_EXP_V

Source

View Text - Preformatted

SELECT site_uses.location location, party_site.party_site_number site_number, SUBSTR(loc.address1, 1, 25) || ' ' || SUBSTR(loc.address2, 1, 25) || ' ' || SUBSTR(loc.address3, 1, 25) || ' ' || SUBSTR(loc.address4, 1, 25) || ' ' || loc.city || ',' || ' ' || nvl(loc.state, loc.province) || ' ' || t.territory_short_name address, loc.address1 address1, loc.address2 address2, loc.address3 address3, loc.address4 address4, loc.city city, loc.province province, loc.state state, loc.country country, t.territory_short_name country_name, loc.postal_code postal_code, owner_party.email_address email_address, cont_point.phone_country_code phone_country_code, cont_point.phone_area_code phone_area_code, cont_point.phone_number phone_number, cont_point.phone_extension phone_extension, nvl(cont_point.phone_country_code, ' ') || '-' || nvl(cont_point.phone_area_code, ' ') || '-' || cont_point.phone_number || ' ' || cont_point.phone_extension full_phone_number, cont_point.phone_area_code || cont_point.phone_number phone_area_number, loc.location_id location_id, site_uses.primary_flag primary_flag, site_uses.site_use_id customer_site_use_id, site_uses.cust_acct_site_id address_id, site_uses.bill_to_site_use_id bill_to_site_use_id, site_uses.primary_salesrep_id primary_salesrep_id, site_uses.orig_system_reference orig_system_reference, site_uses.status status, arpt_sql_func_util.get_lookup_meaning('CODE_STATUS', site_uses.status ) status_meaning, site_uses.territory_id territory_id, site_uses.attribute_category attribute_category, site_uses.attribute1 attribute1, site_uses.attribute2 attribute2, site_uses.attribute3 attribute3, site_uses.attribute4 attribute4, site_uses.attribute5 attribute5, site_uses.attribute6 attribute6, site_uses.attribute7 attribute7, site_uses.attribute8 attribute8, site_uses.attribute9 attribute9, site_uses.attribute10 attribute10, lp.meaning business_purpose, lp2.meaning primary_flag_meaning, ca.cust_account_id cust_account_id, ca.account_number account_number, owner_party.person_first_name person_first_name, owner_party.person_last_name person_last_name, owner_party.party_id party_id, owner_party.party_name party_name, owner_party.category_code category_code, owner_party.url url FROM hz_cust_acct_sites acct_site, hz_party_sites party_site, hz_cust_accounts ca, hz_locations loc, hz_cust_site_uses site_uses, fnd_territories_vl t, ar_lookups lp, ar_lookups lp2, hz_parties owner_party, hz_contact_points cont_point WHERE acct_site.cust_account_id = ca.cust_account_id AND acct_site.party_site_id = party_site.party_site_id AND loc.location_id = party_site.location_id AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id AND loc.country = t.territory_code AND site_uses.site_use_code = lp.lookup_code AND lp.lookup_type = 'SITE_USE_CODE' AND site_uses.site_use_code = 'BILL_TO' AND site_uses.primary_flag = lp2.lookup_code AND lp2.lookup_type = 'YES/NO' AND owner_party.party_id = ca.party_id AND owner_party.party_type IN('ORGANIZATION', 'PERSON') AND cont_point.contact_point_type(+) = 'PHONE' AND cont_point.owner_table_name(+) = 'HZ_PARTY_SITES' AND cont_point.owner_table_id(+) = party_site.party_site_id AND cont_point.primary_flag(+) = 'Y'

View Text - HTML Formatted

SELECT SITE_USES.LOCATION LOCATION
, PARTY_SITE.PARTY_SITE_NUMBER SITE_NUMBER
, SUBSTR(LOC.ADDRESS1
, 1
, 25) || ' ' || SUBSTR(LOC.ADDRESS2
, 1
, 25) || ' ' || SUBSTR(LOC.ADDRESS3
, 1
, 25) || ' ' || SUBSTR(LOC.ADDRESS4
, 1
, 25) || ' ' || LOC.CITY || '
, ' || ' ' || NVL(LOC.STATE
, LOC.PROVINCE) || ' ' || T.TERRITORY_SHORT_NAME ADDRESS
, LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.CITY CITY
, LOC.PROVINCE PROVINCE
, LOC.STATE STATE
, LOC.COUNTRY COUNTRY
, T.TERRITORY_SHORT_NAME COUNTRY_NAME
, LOC.POSTAL_CODE POSTAL_CODE
, OWNER_PARTY.EMAIL_ADDRESS EMAIL_ADDRESS
, CONT_POINT.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE
, CONT_POINT.PHONE_AREA_CODE PHONE_AREA_CODE
, CONT_POINT.PHONE_NUMBER PHONE_NUMBER
, CONT_POINT.PHONE_EXTENSION PHONE_EXTENSION
, NVL(CONT_POINT.PHONE_COUNTRY_CODE
, ' ') || '-' || NVL(CONT_POINT.PHONE_AREA_CODE
, ' ') || '-' || CONT_POINT.PHONE_NUMBER || ' ' || CONT_POINT.PHONE_EXTENSION FULL_PHONE_NUMBER
, CONT_POINT.PHONE_AREA_CODE || CONT_POINT.PHONE_NUMBER PHONE_AREA_NUMBER
, LOC.LOCATION_ID LOCATION_ID
, SITE_USES.PRIMARY_FLAG PRIMARY_FLAG
, SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID
, SITE_USES.CUST_ACCT_SITE_ID ADDRESS_ID
, SITE_USES.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, SITE_USES.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID
, SITE_USES.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, SITE_USES.STATUS STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CODE_STATUS'
, SITE_USES.STATUS ) STATUS_MEANING
, SITE_USES.TERRITORY_ID TERRITORY_ID
, SITE_USES.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, SITE_USES.ATTRIBUTE1 ATTRIBUTE1
, SITE_USES.ATTRIBUTE2 ATTRIBUTE2
, SITE_USES.ATTRIBUTE3 ATTRIBUTE3
, SITE_USES.ATTRIBUTE4 ATTRIBUTE4
, SITE_USES.ATTRIBUTE5 ATTRIBUTE5
, SITE_USES.ATTRIBUTE6 ATTRIBUTE6
, SITE_USES.ATTRIBUTE7 ATTRIBUTE7
, SITE_USES.ATTRIBUTE8 ATTRIBUTE8
, SITE_USES.ATTRIBUTE9 ATTRIBUTE9
, SITE_USES.ATTRIBUTE10 ATTRIBUTE10
, LP.MEANING BUSINESS_PURPOSE
, LP2.MEANING PRIMARY_FLAG_MEANING
, CA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, CA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, OWNER_PARTY.PERSON_FIRST_NAME PERSON_FIRST_NAME
, OWNER_PARTY.PERSON_LAST_NAME PERSON_LAST_NAME
, OWNER_PARTY.PARTY_ID PARTY_ID
, OWNER_PARTY.PARTY_NAME PARTY_NAME
, OWNER_PARTY.CATEGORY_CODE CATEGORY_CODE
, OWNER_PARTY.URL URL
FROM HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_CUST_ACCOUNTS CA
, HZ_LOCATIONS LOC
, HZ_CUST_SITE_USES SITE_USES
, FND_TERRITORIES_VL T
, AR_LOOKUPS LP
, AR_LOOKUPS LP2
, HZ_PARTIES OWNER_PARTY
, HZ_CONTACT_POINTS CONT_POINT
WHERE ACCT_SITE.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE_USES.CUST_ACCT_SITE_ID
AND LOC.COUNTRY = T.TERRITORY_CODE
AND SITE_USES.SITE_USE_CODE = LP.LOOKUP_CODE
AND LP.LOOKUP_TYPE = 'SITE_USE_CODE'
AND SITE_USES.SITE_USE_CODE = 'BILL_TO'
AND SITE_USES.PRIMARY_FLAG = LP2.LOOKUP_CODE
AND LP2.LOOKUP_TYPE = 'YES/NO'
AND OWNER_PARTY.PARTY_ID = CA.PARTY_ID
AND OWNER_PARTY.PARTY_TYPE IN('ORGANIZATION'
, 'PERSON')
AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTY_SITES'
AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY_SITE.PARTY_SITE_ID
AND CONT_POINT.PRIMARY_FLAG(+) = 'Y'