VIEW: APPS.IEX_LS_ACC_EXP_V

Source

View Text - Preformatted

SELECT PARTY.PARTY_ID PARTY_ID, PARTY.PARTY_NAME PARTY_NAME , PARTY.ADDRESS1||' '||PARTY.ADDRESS2||' '||PARTY.ADDRESS3||' '||PARTY.ADDRESS4 ADDRESS, PARTY.ADDRESS1 ADDRESS1, PARTY.ADDRESS2 ADDRESS2, PARTY.ADDRESS3 ADDRESS3, PARTY.ADDRESS4 ADDRESS4, PARTY.CITY CITY, PARTY.STATE STATE, PARTY.POSTAL_CODE POSTAL_CODE, PARTY.PROVINCE PROVINCE, PARTY.COUNTRY COUNTRY, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME, TO_CHAR(NULL) COUNTY_NAME, 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, ACC.STATUS STATUS, ALK.MEANING STATUS_MEAN, PARTY.URL URL, PARTY.EMPLOYEES_TOTAL NUMBER_OF_EMPLOYEE, PARTY.CATEGORY_CODE CATEGORY_CODE, PARTY.CURR_FY_POTENTIAL_REVENUE CURRENT_FY_REVENUE, PARTY.DUNS_NUMBER DUNS_NUMBER, PARTY.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH, PARTY.SIC_CODE SIC, PARTY.SIC_CODE SIC_CODE, PARTY.TAX_REFERENCE TAX_REFERENCE, ACC.ATTRIBUTE1 ATTRIBUTE1, ACC.ATTRIBUTE2 ATTRIBUTE2, ACC.ATTRIBUTE3 ATTRIBUTE3, ACC.ATTRIBUTE4 ATTRIBUTE4, ACC.ATTRIBUTE5 ATTRIBUTE5, ACC.ATTRIBUTE6 ATTRIBUTE6, ACC.ATTRIBUTE7 ATTRIBUTE7, ACC.ATTRIBUTE8 ATTRIBUTE8, ACC.ATTRIBUTE9 ATTRIBUTE9, ACC.ATTRIBUTE10 ATTRIBUTE10, ACC.ATTRIBUTE11 ATTRIBUTE11, ACC.ATTRIBUTE12 ATTRIBUTE12, ACC.ATTRIBUTE13 ATTRIBUTE13, ACC.ATTRIBUTE14 ATTRIBUTE14, ACC.ATTRIBUTE15 ATTRIBUTE15, ACC.ATTRIBUTE16 ATTRIBUTE16, ACC.ATTRIBUTE17 ATTRIBUTE17, ACC.ATTRIBUTE18 ATTRIBUTE18, ACC.ATTRIBUTE19 ATTRIBUTE19, ACC.ATTRIBUTE20 ATTRIBUTE20, PARTY.PERSON_FIRST_NAME FIRST_NAME, PARTY.PERSON_LAST_NAME LAST_NAME, TO_CHAR(NULL) ROLE_TYPE, TO_CHAR(NULL) MEANING, ACC.ACCOUNT_NUMBER ACCOUNT_NUMBER, ACC.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, ACC.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE FROM HZ_PARTIES PARTY, HZ_CONTACT_POINTS CONT_POINT, FND_TERRITORIES_TL FTT, AR_LOOKUPS ALK, HZ_CUST_ACCOUNTS ACC WHERE PARTY.PARTY_TYPE in ( 'ORGANIZATION','PERSON') AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'PHONE' AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID AND CONT_POINT.PRIMARY_FLAG(+) = 'Y' AND PARTY.COUNTRY = FTT.TERRITORY_CODE(+) AND FTT.LANGUAGE(+) = 'US' AND FTT.SOURCE_LANG(+) = 'US' AND ALK.LOOKUP_TYPE = 'CODE_STATUS' AND ALK.LOOKUP_CODE = ACC.STATUS AND ACC.PARTY_ID = PARTY.PARTY_ID UNION ALL SELECT RELP.PARTY_ID PARTY_ID, PARTY.PARTY_NAME PARTY_NAME, LOCATIONS.ADDRESS1||' '||LOCATIONS.ADDRESS2||' '||LOCATIONS.ADDRESS3||' '||LOCATIONS.ADDRESS4 ADDRESS, LOCATIONS.ADDRESS1 ADDRESS1, LOCATIONS.ADDRESS2 ADDRESS2, LOCATIONS.ADDRESS3 ADDRESS3, LOCATIONS.ADDRESS4 ADDRESS4, LOCATIONS.CITY CITY, LOCATIONS.STATE STATE, LOCATIONS.POSTAL_CODE POSTAL_CODE, LOCATIONS.PROVINCE PROVINCE, LOCATIONS.COUNTRY COUNTRY, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME, TO_CHAR(NULL) COUNTY_NAME, 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, ROLE.STATUS STATUS, ALK.MEANING STATUS_MEAN, PARTY.URL URL, PARTY.EMPLOYEES_TOTAL NUMBER_OF_EMPLOYEE, PARTY.CATEGORY_CODE CATEGORY_CODE, PARTY.CURR_FY_POTENTIAL_REVENUE CURRENT_FY_REVENUE, PARTY.DUNS_NUMBER DUNS_NUMBER, PARTY.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH, PARTY.SIC_CODE SIC, PARTY.SIC_CODE SIC_CODE, PARTY.TAX_REFERENCE TAX_REFERENCE, PARTY.ATTRIBUTE1 ATTRIBUTE1, PARTY.ATTRIBUTE2 ATTRIBUTE2, PARTY.ATTRIBUTE3 ATTRIBUTE3, PARTY.ATTRIBUTE4 ATTRIBUTE4, PARTY.ATTRIBUTE5 ATTRIBUTE5, PARTY.ATTRIBUTE6 ATTRIBUTE6, PARTY.ATTRIBUTE7 ATTRIBUTE7, PARTY.ATTRIBUTE8 ATTRIBUTE8, PARTY.ATTRIBUTE9 ATTRIBUTE9, PARTY.ATTRIBUTE10 ATTRIBUTE10, PARTY.ATTRIBUTE11 ATTRIBUTE11, PARTY.ATTRIBUTE12 ATTRIBUTE12, PARTY.ATTRIBUTE13 ATTRIBUTE13, PARTY.ATTRIBUTE14 ATTRIBUTE14, PARTY.ATTRIBUTE15 ATTRIBUTE15, PARTY.ATTRIBUTE16 ATTRIBUTE16, PARTY.ATTRIBUTE17 ATTRIBUTE17, PARTY.ATTRIBUTE18 ATTRIBUTE18, PARTY.ATTRIBUTE19 ATTRIBUTE19, PARTY.ATTRIBUTE20 ATTRIBUTE20, PARTY.PERSON_FIRST_NAME FIRST_NAME, PARTY.PERSON_LAST_NAME LAST_NAME, ROLE.ROLE_TYPE ROLE_TYPE, LKUP.MEANING MEANING, ACC.ACCOUNT_NUMBER ACCOUNT_NUMBER, ACC.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, ACC.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS RELP, HZ_CUST_ACCOUNT_ROLES ROLE, HZ_CONTACT_POINTS CONT_POINT, FND_TERRITORIES_TL FTT, AR_LOOKUPS ALK, HZ_CUST_ACCT_SITES sites, HZ_LOCATIONS locations, HZ_PARTY_SITES party_sites, HZ_CUST_ACCOUNTS acc, AR_LOOKUPS LKUP WHERE PARTY.PARTY_TYPE in ( 'ORGANIZATION','PERSON') AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'PHONE' AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID AND CONT_POINT.PRIMARY_FLAG(+) = 'Y' AND PARTY.COUNTRY = FTT.TERRITORY_CODE(+) AND FTT.LANGUAGE(+) = 'US' AND FTT.SOURCE_LANG(+) = 'US' AND ALK.LOOKUP_TYPE = 'CODE_STATUS' AND ALK.LOOKUP_CODE =ACC.STATUS AND ROLE.PARTY_ID = RELP.PARTY_ID AND RELP.SUBJECT_ID = PARTY.PARTY_ID AND RELP.OBJECT_ID = ACC.PARTY_ID AND locations.location_id (+) = party_sites.location_id and party_sites.party_site_id (+) = sites.party_site_id AND role.cust_acct_site_id = sites.cust_acct_site_id (+) AND role.cust_account_id = sites.cust_account_id (+) AND role.cust_account_id = acc.cust_account_id AND ROLE.ROLE_TYPE = LKUP.LOOKUP_CODE AND LKUP.LOOKUP_TYPE = 'ACCT_ROLE_TYPE'

View Text - HTML Formatted

SELECT PARTY.PARTY_ID PARTY_ID
, PARTY.PARTY_NAME PARTY_NAME
, PARTY.ADDRESS1||' '||PARTY.ADDRESS2||' '||PARTY.ADDRESS3||' '||PARTY.ADDRESS4 ADDRESS
, PARTY.ADDRESS1 ADDRESS1
, PARTY.ADDRESS2 ADDRESS2
, PARTY.ADDRESS3 ADDRESS3
, PARTY.ADDRESS4 ADDRESS4
, PARTY.CITY CITY
, PARTY.STATE STATE
, PARTY.POSTAL_CODE POSTAL_CODE
, PARTY.PROVINCE PROVINCE
, PARTY.COUNTRY COUNTRY
, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME
, TO_CHAR(NULL) COUNTY_NAME
, 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
, ACC.STATUS STATUS
, ALK.MEANING STATUS_MEAN
, PARTY.URL URL
, PARTY.EMPLOYEES_TOTAL NUMBER_OF_EMPLOYEE
, PARTY.CATEGORY_CODE CATEGORY_CODE
, PARTY.CURR_FY_POTENTIAL_REVENUE CURRENT_FY_REVENUE
, PARTY.DUNS_NUMBER DUNS_NUMBER
, PARTY.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH
, PARTY.SIC_CODE SIC
, PARTY.SIC_CODE SIC_CODE
, PARTY.TAX_REFERENCE TAX_REFERENCE
, ACC.ATTRIBUTE1 ATTRIBUTE1
, ACC.ATTRIBUTE2 ATTRIBUTE2
, ACC.ATTRIBUTE3 ATTRIBUTE3
, ACC.ATTRIBUTE4 ATTRIBUTE4
, ACC.ATTRIBUTE5 ATTRIBUTE5
, ACC.ATTRIBUTE6 ATTRIBUTE6
, ACC.ATTRIBUTE7 ATTRIBUTE7
, ACC.ATTRIBUTE8 ATTRIBUTE8
, ACC.ATTRIBUTE9 ATTRIBUTE9
, ACC.ATTRIBUTE10 ATTRIBUTE10
, ACC.ATTRIBUTE11 ATTRIBUTE11
, ACC.ATTRIBUTE12 ATTRIBUTE12
, ACC.ATTRIBUTE13 ATTRIBUTE13
, ACC.ATTRIBUTE14 ATTRIBUTE14
, ACC.ATTRIBUTE15 ATTRIBUTE15
, ACC.ATTRIBUTE16 ATTRIBUTE16
, ACC.ATTRIBUTE17 ATTRIBUTE17
, ACC.ATTRIBUTE18 ATTRIBUTE18
, ACC.ATTRIBUTE19 ATTRIBUTE19
, ACC.ATTRIBUTE20 ATTRIBUTE20
, PARTY.PERSON_FIRST_NAME FIRST_NAME
, PARTY.PERSON_LAST_NAME LAST_NAME
, TO_CHAR(NULL) ROLE_TYPE
, TO_CHAR(NULL) MEANING
, ACC.ACCOUNT_NUMBER ACCOUNT_NUMBER
, ACC.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, ACC.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
FROM HZ_PARTIES PARTY
, HZ_CONTACT_POINTS CONT_POINT
, FND_TERRITORIES_TL FTT
, AR_LOOKUPS ALK
, HZ_CUST_ACCOUNTS ACC
WHERE PARTY.PARTY_TYPE IN ( 'ORGANIZATION'
, 'PERSON')
AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
AND CONT_POINT.PRIMARY_FLAG(+) = 'Y'
AND PARTY.COUNTRY = FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = 'US'
AND FTT.SOURCE_LANG(+) = 'US'
AND ALK.LOOKUP_TYPE = 'CODE_STATUS'
AND ALK.LOOKUP_CODE = ACC.STATUS
AND ACC.PARTY_ID = PARTY.PARTY_ID UNION ALL SELECT RELP.PARTY_ID PARTY_ID
, PARTY.PARTY_NAME PARTY_NAME
, LOCATIONS.ADDRESS1||' '||LOCATIONS.ADDRESS2||' '||LOCATIONS.ADDRESS3||' '||LOCATIONS.ADDRESS4 ADDRESS
, LOCATIONS.ADDRESS1 ADDRESS1
, LOCATIONS.ADDRESS2 ADDRESS2
, LOCATIONS.ADDRESS3 ADDRESS3
, LOCATIONS.ADDRESS4 ADDRESS4
, LOCATIONS.CITY CITY
, LOCATIONS.STATE STATE
, LOCATIONS.POSTAL_CODE POSTAL_CODE
, LOCATIONS.PROVINCE PROVINCE
, LOCATIONS.COUNTRY COUNTRY
, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME
, TO_CHAR(NULL) COUNTY_NAME
, 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
, ROLE.STATUS STATUS
, ALK.MEANING STATUS_MEAN
, PARTY.URL URL
, PARTY.EMPLOYEES_TOTAL NUMBER_OF_EMPLOYEE
, PARTY.CATEGORY_CODE CATEGORY_CODE
, PARTY.CURR_FY_POTENTIAL_REVENUE CURRENT_FY_REVENUE
, PARTY.DUNS_NUMBER DUNS_NUMBER
, PARTY.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH
, PARTY.SIC_CODE SIC
, PARTY.SIC_CODE SIC_CODE
, PARTY.TAX_REFERENCE TAX_REFERENCE
, PARTY.ATTRIBUTE1 ATTRIBUTE1
, PARTY.ATTRIBUTE2 ATTRIBUTE2
, PARTY.ATTRIBUTE3 ATTRIBUTE3
, PARTY.ATTRIBUTE4 ATTRIBUTE4
, PARTY.ATTRIBUTE5 ATTRIBUTE5
, PARTY.ATTRIBUTE6 ATTRIBUTE6
, PARTY.ATTRIBUTE7 ATTRIBUTE7
, PARTY.ATTRIBUTE8 ATTRIBUTE8
, PARTY.ATTRIBUTE9 ATTRIBUTE9
, PARTY.ATTRIBUTE10 ATTRIBUTE10
, PARTY.ATTRIBUTE11 ATTRIBUTE11
, PARTY.ATTRIBUTE12 ATTRIBUTE12
, PARTY.ATTRIBUTE13 ATTRIBUTE13
, PARTY.ATTRIBUTE14 ATTRIBUTE14
, PARTY.ATTRIBUTE15 ATTRIBUTE15
, PARTY.ATTRIBUTE16 ATTRIBUTE16
, PARTY.ATTRIBUTE17 ATTRIBUTE17
, PARTY.ATTRIBUTE18 ATTRIBUTE18
, PARTY.ATTRIBUTE19 ATTRIBUTE19
, PARTY.ATTRIBUTE20 ATTRIBUTE20
, PARTY.PERSON_FIRST_NAME FIRST_NAME
, PARTY.PERSON_LAST_NAME LAST_NAME
, ROLE.ROLE_TYPE ROLE_TYPE
, LKUP.MEANING MEANING
, ACC.ACCOUNT_NUMBER ACCOUNT_NUMBER
, ACC.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, ACC.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
FROM HZ_PARTIES PARTY
, HZ_RELATIONSHIPS RELP
, HZ_CUST_ACCOUNT_ROLES ROLE
, HZ_CONTACT_POINTS CONT_POINT
, FND_TERRITORIES_TL FTT
, AR_LOOKUPS ALK
, HZ_CUST_ACCT_SITES SITES
, HZ_LOCATIONS LOCATIONS
, HZ_PARTY_SITES PARTY_SITES
, HZ_CUST_ACCOUNTS ACC
, AR_LOOKUPS LKUP
WHERE PARTY.PARTY_TYPE IN ( 'ORGANIZATION'
, 'PERSON')
AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
AND CONT_POINT.PRIMARY_FLAG(+) = 'Y'
AND PARTY.COUNTRY = FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = 'US'
AND FTT.SOURCE_LANG(+) = 'US'
AND ALK.LOOKUP_TYPE = 'CODE_STATUS'
AND ALK.LOOKUP_CODE =ACC.STATUS
AND ROLE.PARTY_ID = RELP.PARTY_ID
AND RELP.SUBJECT_ID = PARTY.PARTY_ID
AND RELP.OBJECT_ID = ACC.PARTY_ID
AND LOCATIONS.LOCATION_ID (+) = PARTY_SITES.LOCATION_ID
AND PARTY_SITES.PARTY_SITE_ID (+) = SITES.PARTY_SITE_ID
AND ROLE.CUST_ACCT_SITE_ID = SITES.CUST_ACCT_SITE_ID (+)
AND ROLE.CUST_ACCOUNT_ID = SITES.CUST_ACCOUNT_ID (+)
AND ROLE.CUST_ACCOUNT_ID = ACC.CUST_ACCOUNT_ID
AND ROLE.ROLE_TYPE = LKUP.LOOKUP_CODE
AND LKUP.LOOKUP_TYPE = 'ACCT_ROLE_TYPE'