eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AST_LM_ORG_SMARTSEARCH_V

Source

View Text - Preformatted


SELECT party_name, hz.party_id, hz.party_number, 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 ), LOC.ADDRESS1||' '||LOC.ADDRESS2||' '||LOC.ADDRESS3||' '||LOC.ADDRESS4 Address, nvl(LOC.CITY,hz.CITY), nvl(LOC.COUNTY,hz.COUNTY), nvl(LOC.STATE,hz.STATE), nvl(LOC.POSTAL_CODE,hz.POSTAL_CODE), nvl(LOC.PROVINCE, hz.PROVINCE), NVL(LOC.COUNTRY_NAME,FTT.TERRITORY_SHORT_NAME), 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, nvl(CPTS.PHONE_AREA_CODE,primphone.phone_area_code)|| nvl(CPTS.PHONE_NUMBER,primphone.phone_number) PHONE_AREA_NUMBER, nvl(CPTS.PHONE_AREA_CODE,primphone.phone_area_code), nvl(CPTS.PHONE_COUNTRY_CODE ,primphone.phone_country_code), nvl(CPTS.PHONE_NUMBER,primphone.phone_number), nvl(CPTS.PHONE_EXTENSION,primphone.phone_extension), HZ.STATUS, alk1.meaning status_mean, HZ.URL, hz.certification_level, alk2.meaning cert_level_mean, null account_number, to_number(null) cust_account_id, hz.party_type, smartparty.search_context_id from hz_parties hz, hz_contact_points primphone, 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, 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 ALK1, AR_LOOKUPS ALK2 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 ALK1.LOOKUP_TYPE='REGISTRY_STATUS' AND ALK1.LOOKUP_CODE=HZ.STATUS AND ALK2.LOOKUP_TYPE(+)='HZ_PARTY_CERT_LEVEL' AND ALK2.LOOKUP_CODE(+)=HZ.CERTIFICATION_LEVEL

View Text - HTML Formatted

SELECT PARTY_NAME
, HZ.PARTY_ID
, HZ.PARTY_NUMBER
, 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 )
, LOC.ADDRESS1||' '||LOC.ADDRESS2||' '||LOC.ADDRESS3||' '||LOC.ADDRESS4 ADDRESS
, NVL(LOC.CITY
, HZ.CITY)
, NVL(LOC.COUNTY
, HZ.COUNTY)
, NVL(LOC.STATE
, HZ.STATE)
, NVL(LOC.POSTAL_CODE
, HZ.POSTAL_CODE)
, NVL(LOC.PROVINCE
, HZ.PROVINCE)
, NVL(LOC.COUNTRY_NAME
, FTT.TERRITORY_SHORT_NAME)
, 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
, NVL(CPTS.PHONE_AREA_CODE
, PRIMPHONE.PHONE_AREA_CODE)|| NVL(CPTS.PHONE_NUMBER
, PRIMPHONE.PHONE_NUMBER) PHONE_AREA_NUMBER
, NVL(CPTS.PHONE_AREA_CODE
, PRIMPHONE.PHONE_AREA_CODE)
, NVL(CPTS.PHONE_COUNTRY_CODE
, PRIMPHONE.PHONE_COUNTRY_CODE)
, NVL(CPTS.PHONE_NUMBER
, PRIMPHONE.PHONE_NUMBER)
, NVL(CPTS.PHONE_EXTENSION
, PRIMPHONE.PHONE_EXTENSION)
, HZ.STATUS
, ALK1.MEANING STATUS_MEAN
, HZ.URL
, HZ.CERTIFICATION_LEVEL
, ALK2.MEANING CERT_LEVEL_MEAN
, NULL ACCOUNT_NUMBER
, TO_NUMBER(NULL) CUST_ACCOUNT_ID
, HZ.PARTY_TYPE
, SMARTPARTY.SEARCH_CONTEXT_ID
FROM HZ_PARTIES HZ
, HZ_CONTACT_POINTS PRIMPHONE
, 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
, 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 ALK1
, AR_LOOKUPS ALK2
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 ALK1.LOOKUP_TYPE='REGISTRY_STATUS'
AND ALK1.LOOKUP_CODE=HZ.STATUS
AND ALK2.LOOKUP_TYPE(+)='HZ_PARTY_CERT_LEVEL'
AND ALK2.LOOKUP_CODE(+)=HZ.CERTIFICATION_LEVEL