VIEW: APPS.AS_TAP_ACCOUNTS_V

Source

View Text - Preformatted

SELECT /*+ parallel(ORGPRO) parallel(CNTPNT) parallel(X) use_hash(ORGPRO CNTPNT X) */ X.party_id, X.party_site_id, X.party_site_id, X.party_id, UPPER(CNTPNT.phone_area_code), UPPER(X.category_code), UPPER(ORGPRO.pref_functional_currency), UPPER(X.city), ORGPRO.curr_fy_potential_revenue, UPPER(X.country), UPPER(X.county), X.party_id, UPPER(X.party_name_substring), UPPER(X.party_name), X.employees_total, UPPER(X.postal_code), UPPER(X.province), X.party_id, UPPER(X.sic_code_type||': '||X.sic_code), UPPER(X.state), UPPER(X.duns_number_c) from HZ_ORGANIZATION_PROFILES ORGPRO , HZ_CONTACT_POINTS CNTPNT, (select /*+ parallel(PARTY) parallel(SITE) parallel(LOC) use_hash(SITE) use_hash(PARTY) use_hash(LOC) */ SITE.party_site_id party_site_id, NVL(Site.party_id,PARTY.party_id) party_id, LOC.city city, LOC.country country, LOC.county county, LOC.state state, LOC.province province, LOC.postal_code postal_code, PARTY.employees_total employees_total, PARTY.sic_code_type sic_code_type, PARTY.sic_code sic_code, upper(substr(PARTY.party_name,1,1)) party_name_substring, upper(PARTY.party_name) party_name, PARTY.category_code category_code, decode(SITE.party_site_id,NULL,'HZ_PARTIES','HZ_PARTY_SITES') owner_table_name, NVL(SITE.party_site_id,PARTY.party_id) owner_table_id, PARTY.duns_number_c from HZ_PARTY_SITES SITE, HZ_LOCATIONS LOC, HZ_PARTIES PARTY where SITE.status (+) = 'A' and SITE.party_id (+) = PARTY.party_id and PARTY.party_type in ('PERSON', 'ORGANIZATION') and PARTY.status = 'A' and LOC.location_id (+) = SITE.location_id ) X WHERE CNTPNT.owner_table_name(+) = X.owner_table_name and CNTPNT.owner_table_id(+) = X.owner_table_id and CNTPNT.status(+)='A' and CNTPNT.primary_flag(+)='Y' and CNTPNT.contact_point_type(+)='PHONE' and ORGPRO.party_id(+) = X.party_id and nvl(ORGPRO.effective_end_date(+),sysdate+1) > sysdate

View Text - HTML Formatted

SELECT /*+ PARALLEL(ORGPRO) PARALLEL(CNTPNT) PARALLEL(X) USE_HASH(ORGPRO CNTPNT X) */ X.PARTY_ID
, X.PARTY_SITE_ID
, X.PARTY_SITE_ID
, X.PARTY_ID
, UPPER(CNTPNT.PHONE_AREA_CODE)
, UPPER(X.CATEGORY_CODE)
, UPPER(ORGPRO.PREF_FUNCTIONAL_CURRENCY)
, UPPER(X.CITY)
, ORGPRO.CURR_FY_POTENTIAL_REVENUE
, UPPER(X.COUNTRY)
, UPPER(X.COUNTY)
, X.PARTY_ID
, UPPER(X.PARTY_NAME_SUBSTRING)
, UPPER(X.PARTY_NAME)
, X.EMPLOYEES_TOTAL
, UPPER(X.POSTAL_CODE)
, UPPER(X.PROVINCE)
, X.PARTY_ID
, UPPER(X.SIC_CODE_TYPE||': '||X.SIC_CODE)
, UPPER(X.STATE)
, UPPER(X.DUNS_NUMBER_C)
FROM HZ_ORGANIZATION_PROFILES ORGPRO
, HZ_CONTACT_POINTS CNTPNT
, (SELECT /*+ PARALLEL(PARTY) PARALLEL(SITE) PARALLEL(LOC) USE_HASH(SITE) USE_HASH(PARTY) USE_HASH(LOC) */ SITE.PARTY_SITE_ID PARTY_SITE_ID
, NVL(SITE.PARTY_ID
, PARTY.PARTY_ID) PARTY_ID
, LOC.CITY CITY
, LOC.COUNTRY COUNTRY
, LOC.COUNTY COUNTY
, LOC.STATE STATE
, LOC.PROVINCE PROVINCE
, LOC.POSTAL_CODE POSTAL_CODE
, PARTY.EMPLOYEES_TOTAL EMPLOYEES_TOTAL
, PARTY.SIC_CODE_TYPE SIC_CODE_TYPE
, PARTY.SIC_CODE SIC_CODE
, UPPER(SUBSTR(PARTY.PARTY_NAME
, 1
, 1)) PARTY_NAME_SUBSTRING
, UPPER(PARTY.PARTY_NAME) PARTY_NAME
, PARTY.CATEGORY_CODE CATEGORY_CODE
, DECODE(SITE.PARTY_SITE_ID
, NULL
, 'HZ_PARTIES'
, 'HZ_PARTY_SITES') OWNER_TABLE_NAME
, NVL(SITE.PARTY_SITE_ID
, PARTY.PARTY_ID) OWNER_TABLE_ID
, PARTY.DUNS_NUMBER_C
FROM HZ_PARTY_SITES SITE
, HZ_LOCATIONS LOC
, HZ_PARTIES PARTY
WHERE SITE.STATUS (+) = 'A'
AND SITE.PARTY_ID (+) = PARTY.PARTY_ID
AND PARTY.PARTY_TYPE IN ('PERSON'
, 'ORGANIZATION')
AND PARTY.STATUS = 'A'
AND LOC.LOCATION_ID (+) = SITE.LOCATION_ID ) X
WHERE CNTPNT.OWNER_TABLE_NAME(+) = X.OWNER_TABLE_NAME
AND CNTPNT.OWNER_TABLE_ID(+) = X.OWNER_TABLE_ID
AND CNTPNT.STATUS(+)='A'
AND CNTPNT.PRIMARY_FLAG(+)='Y'
AND CNTPNT.CONTACT_POINT_TYPE(+)='PHONE'
AND ORGPRO.PARTY_ID(+) = X.PARTY_ID
AND NVL(ORGPRO.EFFECTIVE_END_DATE(+)
, SYSDATE+1) > SYSDATE