View: AS_TAP_ACCOUNTS_V

Product:	AS - Sales Foundation
Description:	For TAP use
Implementation/DBA Data:	APPS.AS_TAP_ACCOUNTS_V

View Text

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
, SITE.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
, 'HZ_PARTY_SITES' OWNER_TABLE_NAME
, SITE.PARTY_SITE_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 UNION ALL SELECT /*+ PARALLEL(PARTY) */ TO_NUMBER(NULL) PARTY_SITE_ID
, PARTY.PARTY_ID PARTY_ID
, TO_CHAR(NULL) CITY
, TO_CHAR(NULL) COUNTRY
, TO_CHAR(NULL) COUNTY
, TO_CHAR(NULL) STATE
, TO_CHAR(NULL) PROVINCE
, TO_CHAR(NULL) 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
, 'HZ_PARTIES' OWNER_TABLE_NAME
, PARTY.PARTY_ID OWNER_TABLE_ID
, PARTY.DUNS_NUMBER_C
FROM HZ_PARTIES PARTY
WHERE PARTY.PARTY_TYPE IN ('PERSON'
, 'ORGANIZATION')
AND PARTY.STATUS = 'A' ) 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

Columns