VIEW: APPS.ASF_SMART_ORG_V

Source

View Text - Preformatted

SELECT party_name, hz.party_id, hz.party_number, alk.meaning, ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL ( Null, nvl(LOC.ADDRESS1,hz.ADDRESS1), nvl(LOC.ADDRESS2, hz.ADDRESS2), nvl(LOC.ADDRESS3, hz.ADDRESS3), nvl(LOC.ADDRESS4, hz.ADDRESS4), nvl(LOC.CITY, hz.CITY), nvl(LOC.COUNTY, hz.COUNTY), nvl(LOC.STATE, hz.STATE), nvl(LOC.PROVINCE, hz.PROVINCE), nvl(LOC.POSTAL_CODE,hz.POSTAL_CODE), nvl(LOC.COUNTRY, hz.COUNTRY), nvl(LOC.COUNTRY_NAME, FTT.TERRITORY_SHORT_NAME), Null, Null, Null, Null, Null, FND_PROFILE.Value('AST_DEFAULT_COUNTRY'), NULL, NULL, 2000, 1, 1 ), smartparty.score score, nvl(cpts.email_Address, hz.email_address), NVL(nvl(CPTS.PHONE_COUNTRY_CODE ,primphone.phone_country_code), '   ')||'-'|| NVL(nvl(CPTS.PHONE_AREA_CODE,primphone.phone_area_code), '  ')||'-'|| nvl(CPTS.PHONE_NUMBER,primphone.phone_number) ||''|| nvl(CPTS.PHONE_EXTENSION,primphone.phone_extension) FULL_PHONE_NUMBER, HZ.URL, NVL(loc.LOCATION_ID, hzsites.location_id), NVL(loc.PARTY_SITE_ID, hzsites.party_site_id), smartparty.search_context_id from hz_parties hz, hz_contact_points primphone, hz_party_sites hzsites, hz_matched_parties_gt smartparty, (select search_context_id, c.party_id, a.email_address, b.PHONE_AREA_CODE, b.PHONE_COUNTRY_CODE , b.PHONE_NUMBER, b.PHONE_EXTENSION, c.score cpts_score from hz_contact_points a, hz_contact_points b, hz_matched_cpts_gt c WHERE a.owner_table_id(+)=c.party_id and a.owner_table_name(+)='HZ_PARTIES' and a.contact_point_id(+)=c.contact_point_id and a.contact_point_type='EMAIL' and b.owner_table_id(+)=c.party_id and b.owner_table_name(+)='HZ_PARTIES' and b.contact_point_id(+)=c.contact_point_id and b.contact_point_type(+)='PHONE' and b.status(+)='A' and a.status in ('A','I') ) cpts, (select c.search_context_id, c.party_id, a.location_id, a.party_site_id, b.ADDRESS1, b.ADDRESS2, b.ADDRESS3, b.ADDRESS4, b.CITY, b.COUNTY, b.STATE, b.PROVINCE, b.POSTAL_CODE, b.COUNTRY COUNTRY, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME, c.score sites_score from hz_party_sites a, hz_locations b, FND_TERRITORIES_TL FTT, hz_matched_party_sites_gt c where a.party_id=c.party_id and a.party_site_id=c.party_site_id and b.location_id=a.location_id AND b.COUNTRY = FTT.TERRITORY_CODE(+) AND FTT.LANGUAGE(+) = userenv('LANG') AND FTT.SOURCE_LANG(+) = userenv('LANG') ) loc, FND_TERRITORIES_TL FTT, AR_LOOKUPS ALK where hz.party_id=smartparty.party_id and hz.party_type='ORGANIZATION' and cpts.search_context_id(+)=smartparty.search_context_id and cpts.party_id(+)= smartparty.party_id and primphone.owner_table_id(+)=smartparty.party_id and primphone.owner_table_name(+)='HZ_PARTIES' and primphone.contact_point_type(+)='PHONE' and primphone.primary_flag(+)='Y' and loc.search_context_id(+)=smartparty.search_context_id and loc.party_id(+)=smartparty.party_id AND hz.COUNTRY = FTT.TERRITORY_CODE(+) AND FTT.LANGUAGE(+) = userenv('LANG') AND FTT.SOURCE_LANG(+) = userenv('LANG') AND ALK.lookup_type(+)='CUSTOMER_CATEGORY' AND ALK.LOOKUP_CODE(+)=hz.category_code AND ALK.ENABLED_FLAG(+)='Y' AND hzsites.party_id(+)=hz.party_id AND hzsites.identifying_address_flag(+)='Y'

View Text - HTML Formatted

SELECT PARTY_NAME
, HZ.PARTY_ID
, HZ.PARTY_NUMBER
, ALK.MEANING
, ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL ( NULL
, NVL(LOC.ADDRESS1
, HZ.ADDRESS1)
, NVL(LOC.ADDRESS2
, HZ.ADDRESS2)
, NVL(LOC.ADDRESS3
, HZ.ADDRESS3)
, NVL(LOC.ADDRESS4
, HZ.ADDRESS4)
, NVL(LOC.CITY
, HZ.CITY)
, NVL(LOC.COUNTY
, HZ.COUNTY)
, NVL(LOC.STATE
, HZ.STATE)
, NVL(LOC.PROVINCE
, HZ.PROVINCE)
, NVL(LOC.POSTAL_CODE
, HZ.POSTAL_CODE)
, NVL(LOC.COUNTRY
, HZ.COUNTRY)
, NVL(LOC.COUNTRY_NAME
, FTT.TERRITORY_SHORT_NAME)
, NULL
, NULL
, NULL
, NULL
, NULL
, FND_PROFILE.VALUE('AST_DEFAULT_COUNTRY')
, NULL
, NULL
, 2000
, 1
, 1 )
, SMARTPARTY.SCORE SCORE
, NVL(CPTS.EMAIL_ADDRESS
, HZ.EMAIL_ADDRESS)
, NVL(NVL(CPTS.PHONE_COUNTRY_CODE
, PRIMPHONE.PHONE_COUNTRY_CODE)
, ' ')||'-'|| NVL(NVL(CPTS.PHONE_AREA_CODE
, PRIMPHONE.PHONE_AREA_CODE)
, ' ')||'-'|| NVL(CPTS.PHONE_NUMBER
, PRIMPHONE.PHONE_NUMBER) ||''|| NVL(CPTS.PHONE_EXTENSION
, PRIMPHONE.PHONE_EXTENSION) FULL_PHONE_NUMBER
, HZ.URL
, NVL(LOC.LOCATION_ID
, HZSITES.LOCATION_ID)
, NVL(LOC.PARTY_SITE_ID
, HZSITES.PARTY_SITE_ID)
, SMARTPARTY.SEARCH_CONTEXT_ID
FROM HZ_PARTIES HZ
, HZ_CONTACT_POINTS PRIMPHONE
, HZ_PARTY_SITES HZSITES
, HZ_MATCHED_PARTIES_GT SMARTPARTY
, (SELECT SEARCH_CONTEXT_ID
, C.PARTY_ID
, A.EMAIL_ADDRESS
, B.PHONE_AREA_CODE
, B.PHONE_COUNTRY_CODE
, B.PHONE_NUMBER
, B.PHONE_EXTENSION
, C.SCORE CPTS_SCORE
FROM HZ_CONTACT_POINTS A
, HZ_CONTACT_POINTS B
, HZ_MATCHED_CPTS_GT C
WHERE A.OWNER_TABLE_ID(+)=C.PARTY_ID
AND A.OWNER_TABLE_NAME(+)='HZ_PARTIES'
AND A.CONTACT_POINT_ID(+)=C.CONTACT_POINT_ID
AND A.CONTACT_POINT_TYPE='EMAIL'
AND B.OWNER_TABLE_ID(+)=C.PARTY_ID
AND B.OWNER_TABLE_NAME(+)='HZ_PARTIES'
AND B.CONTACT_POINT_ID(+)=C.CONTACT_POINT_ID
AND B.CONTACT_POINT_TYPE(+)='PHONE'
AND B.STATUS(+)='A'
AND A.STATUS IN ('A'
, 'I') ) CPTS
, (SELECT C.SEARCH_CONTEXT_ID
, C.PARTY_ID
, A.LOCATION_ID
, A.PARTY_SITE_ID
, B.ADDRESS1
, B.ADDRESS2
, B.ADDRESS3
, B.ADDRESS4
, B.CITY
, B.COUNTY
, B.STATE
, B.PROVINCE
, B.POSTAL_CODE
, B.COUNTRY COUNTRY
, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME
, C.SCORE SITES_SCORE
FROM HZ_PARTY_SITES A
, HZ_LOCATIONS B
, FND_TERRITORIES_TL FTT
, HZ_MATCHED_PARTY_SITES_GT C
WHERE A.PARTY_ID=C.PARTY_ID
AND A.PARTY_SITE_ID=C.PARTY_SITE_ID
AND B.LOCATION_ID=A.LOCATION_ID
AND B.COUNTRY = FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG') ) LOC
, FND_TERRITORIES_TL FTT
, AR_LOOKUPS ALK
WHERE HZ.PARTY_ID=SMARTPARTY.PARTY_ID
AND HZ.PARTY_TYPE='ORGANIZATION'
AND CPTS.SEARCH_CONTEXT_ID(+)=SMARTPARTY.SEARCH_CONTEXT_ID
AND CPTS.PARTY_ID(+)= SMARTPARTY.PARTY_ID
AND PRIMPHONE.OWNER_TABLE_ID(+)=SMARTPARTY.PARTY_ID
AND PRIMPHONE.OWNER_TABLE_NAME(+)='HZ_PARTIES'
AND PRIMPHONE.CONTACT_POINT_TYPE(+)='PHONE'
AND PRIMPHONE.PRIMARY_FLAG(+)='Y'
AND LOC.SEARCH_CONTEXT_ID(+)=SMARTPARTY.SEARCH_CONTEXT_ID
AND LOC.PARTY_ID(+)=SMARTPARTY.PARTY_ID
AND HZ.COUNTRY = FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG')
AND ALK.LOOKUP_TYPE(+)='CUSTOMER_CATEGORY'
AND ALK.LOOKUP_CODE(+)=HZ.CATEGORY_CODE
AND ALK.ENABLED_FLAG(+)='Y'
AND HZSITES.PARTY_ID(+)=HZ.PARTY_ID
AND HZSITES.IDENTIFYING_ADDRESS_FLAG(+)='Y'