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.

[Home] [Help]

View: ICX_CUSTOMER_CONTACTS_V

Product:	ICX - Oracle iProcurement
Description:	Web Customers Salesrep Customer Contacts List View
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT CUST.CUSTOMER_ID
, CONT.CONTACT_ID
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, CUST.FISCAL_YEAREND_MONTH
, CUST.NET_WORTH
, CUST.NUM_OF_EMPLOYEES
, CUST.TAX_CODE
, CUST.YEAR_ESTABLISHED
, CUST.SHIP_VIA
, CUST.FREIGHT_TERM
, CUST.PAYMENT_TERM_ID
, CUST.COMPETITOR_FLAG
, CUST.JGZZ_FISCAL_CODE
, CUST.DO_NOT_MAIL_FLAG
, CUST_ADDR.ADDRESS_ID
, CUST_ADDR.COUNTRY
, CUST.CUSTOMER_NAME || DECODE(CUST_ADDR.ADDRESS1
, NULL
, NULL
, '
') || CUST_ADDR.ADDRESS1 || DECODE(CUST_ADDR.ADDRESS2
, NULL
, NULL
, '
') || CUST_ADDR.ADDRESS2 || DECODE(CUST_ADDR.ADDRESS3
, NULL
, NULL
, '
')||CUST_ADDR.ADDRESS3 || DECODE(CUST_ADDR.ADDRESS4
, NULL
, NULL
, '
')||CUST_ADDR.ADDRESS4 || DECODE(CUST_ADDR.CITY
, NULL
, NULL
, '
') || CUST_ADDR.CITY || DECODE(CUST_ADDR.PROVINCE
, NULL
, NULL
, '
') || CUST_ADDR.PROVINCE || DECODE(CUST_ADDR.COUNTY
, NULL
, NULL
, '
') || CUST_ADDR.COUNTY || DECODE(CUST_ADDR.STATE || ' ' ||CUST_ADDR.POSTAL_CODE
, NULL
, NULL
, '
') || CUST_ADDR.STATE || ' ' || CUST_ADDR.POSTAL_CODE || DECODE(NVL(TERR1.TERRITORY_SHORT_NAME
, CUST_ADDR.COUNTRY)
, NULL
, NULL
, '
') || NVL(TERR1.TERRITORY_SHORT_NAME
, CUST_ADDR.COUNTRY)
, CUST_ADDR.ADDRESS1
, CUST_ADDR.ADDRESS2
, CUST_ADDR.ADDRESS3
, CUST_ADDR.ADDRESS4
, CUST_ADDR.CITY
, CUST_ADDR.POSTAL_CODE
, CUST_ADDR.STATE
, CUST_ADDR.PROVINCE
, CUST_ADDR.COUNTY
, CUST_ADDR.BILL_TO_FLAG
, CUST_ADDR.MARKET_FLAG
, CUST_ADDR.SHIP_TO_FLAG
, CUST_ADDR.ADDRESS_STYLE
, CUST_ADDR.KEY_ACCOUNT_FLAG
, CUST_ADDR.REFERENCE_USE_FLAG
, CUST_PHON.PHONE_ID
, CUST_PHON.PHONE_NUMBER
, CUST_PHON.PHONE_TYPE
, CUST_PHON.AREA_CODE
, CUST_PHON.EXTENSION
, CUST_PHON.PRIMARY_FLAG
, CONT.LAST_NAME || '
, ' || CONT.FIRST_NAME
, CONT.LAST_NAME
, CONT.FIRST_NAME
, CONT.TITLE
, CONT.JOB_TITLE
, CONT.MAIL_STOP
, CONT.JOB_TITLE_CODE
, CONT.PRIMARY_ROLE
, CONT.DO_NOT_MAIL_FLAG
, CONT.SUFFIX
, CONT.EMAIL_ADDRESS
, CONT.SEX_CODE
, CONT.SALUTATION
, CONT.DEPARTMENT_CODE
, CONT.DEPARTMENT
, CONT_PHON.PHONE_ID
, CONT_PHON.PHONE_NUMBER
, CONT_PHON.PHONE_TYPE
, CONT_PHON.AREA_CODE
, CONT_PHON.EXTENSION
, CONT_PHON.PRIMARY_FLAG
, CONT_ADDR.ADDRESS_ID
, CONT_ADDR.COUNTRY
, CONT.FIRST_NAME || ' ' || CONT.LAST_NAME || DECODE(CONT_PHON.PHONE_NUMBER || ' ' || CONT_PHON.EXTENSION
, NULL
, NULL
, '
') || CONT_PHON.PHONE_NUMBER || ' ' || CONT_PHON.EXTENSION || DECODE(CONT_ADDR.ADDRESS1
, NULL
, NULL
, '
') || CONT_ADDR.ADDRESS1 || DECODE(CONT_ADDR.ADDRESS2
, NULL
, NULL
, '
') || CONT_ADDR.ADDRESS2 || DECODE(CONT_ADDR.ADDRESS3
, NULL
, NULL
, '
')||CONT_ADDR.ADDRESS3 || DECODE(CONT_ADDR.ADDRESS4
, NULL
, NULL
, '
')||CONT_ADDR.ADDRESS4 || DECODE(CONT_ADDR.CITY
, NULL
, NULL
, '
') || CONT_ADDR.CITY || DECODE(CONT_ADDR.PROVINCE
, NULL
, NULL
, '
') || CONT_ADDR.PROVINCE || DECODE(CONT_ADDR.COUNTY
, NULL
, NULL
, '
') || CONT_ADDR.COUNTY || DECODE(CONT_ADDR.STATE || ' ' ||CONT_ADDR.POSTAL_CODE
, NULL
, NULL
, '
') || CONT_ADDR.STATE || ' ' || CONT_ADDR.POSTAL_CODE || DECODE(NVL(TERR2.TERRITORY_SHORT_NAME
, CONT_ADDR.COUNTRY)
, NULL
, NULL
, '
') || NVL(TERR2.TERRITORY_SHORT_NAME
, CONT_ADDR.COUNTRY)
, CONT_ADDR.ADDRESS1
, CONT_ADDR.ADDRESS2
, CONT_ADDR.ADDRESS3
, CONT_ADDR.ADDRESS4
, CONT_ADDR.CITY
, CONT_ADDR.POSTAL_CODE
, CONT_ADDR.STATE
, CONT_ADDR.PROVINCE
, CONT_ADDR.COUNTY
, CUST_ADDR.ORG_ID
FROM AR_LOOKUPS LOOK1
, AR_LOOKUPS LOOK2
, FND_TERRITORIES_TL TERR1
, FND_TERRITORIES_TL TERR2
, RA_CUSTOMERS CUST
, RA_ADDRESSES CUST_ADDR
, RA_PHONES CUST_PHON
, RA_CONTACTS CONT
, RA_PHONES CONT_PHON
, RA_ADDRESSES CONT_ADDR
WHERE CUST_ADDR.CUSTOMER_ID = CUST.CUSTOMER_ID
AND CUST_ADDR.ADDRESS_ID = CONT.ADDRESS_ID
AND CUST_ADDR.CUSTOMER_ID = CONT.CUSTOMER_ID
AND CUST_ADDR.ADDRESS_ID = CUST_PHON.ADDRESS_ID(+)
AND CUST_ADDR.CUSTOMER_ID = CUST_PHON.CUSTOMER_ID(+)
AND CUST_PHON.CONTACT_ID(+) IS NULL
AND CUST_PHON.PHONE_TYPE = LOOK1.LOOKUP_CODE(+)
AND CUST_ADDR.COUNTRY = TERR1.TERRITORY_CODE(+)
AND CUST.CUSTOMER_PROSPECT_CODE = LOOK2.LOOKUP_CODE(+)
AND CONT.MAILING_ADDRESS_ID = CONT_ADDR.ADDRESS_ID(+)
AND CONT_ADDR.COUNTRY = TERR2.TERRITORY_CODE(+)
AND CONT.MAILING_ADDRESS_ID = CONT_PHON.ADDRESS_ID(+)
AND LOOK1.LOOKUP_TYPE(+) = 'COMMUNICATION_TYPE'
AND LOOK2.LOOKUP_TYPE(+) = 'CUSTOMER_PROPECT_CODE'
AND CUST_PHON.PRIMARY_FLAG(+) = 'Y'
AND CONT_PHON.PRIMARY_FLAG(+) = 'Y'
AND CONT_PHON.STATUS(+) = 'A' /*
AND CONT.CONTACT_ID = CONT_PHON.CONTACT_ID(+) */
AND TERR1.LANGUAGE(+) = USERENV('LANG')
AND TERR2.LANGUAGE(+) = USERENV('LANG')
AND CUST.STATUS = 'A'
AND CONT.STATUS = 'A'

Columns

Name
CUSTOMER_ID
CONTACT_ID
CUSTOMER_NAME
CUSTOMER_NUMBER
FISCAL_YEAREND_MONTH
NET_WORTH
NUM_OF_EMPLOYEES
TAX_CODE
YEAR_ESTABLISHED
SHIP_VIA
FREIGHT_TERM
PAYMENT_TERM_ID
COMPETITOR_FLAG
JGZZ_FISCAL_CODE
DO_NOT_MAIL_FLAG
ADDRESS_ID
COUNTRY
ADDRESS
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
CITY
POSTAL_CODE
STATE
PROVINCE
COUNTY
BILL_TO_FLAG
MARKET_FLAG
SHIP_TO_FLAG
ADDRESS_STYLE
KEY_ACCOUNT_FLAG
REFERENCE_USE_FLAG
PHONE_ID
PHONE_NUMBER
PHONE_TYPE
AREA_CODE
EXTENSION
PRIMARY_FLAG
FULL_NAME
LAST_NAME
FIRST_NAME
TITLE
JOB_TITLE
MAIL_STOP
JOB_TITLE_CODE
PRIMARY_ROLE
CON_DO_NOT_MAIL_FLAG
SUFFIX
EMAIL_ADDRESS
SEX_CODE
SALUTATION
DEPARTMENT_CODE
DEPARTMENT
CONTACT_PHONE_ID
CONTACT_PHONE_NUMBER
CONTACT_PHONE_TYPE
CONTACT_AREA_CODE
CONTACT_EXTENSION
CONTACT_PRIMARY_FLAG
CONT_MAILING_ADDRESS_ID
CONT_COUNTRY
CONT_ADDRESS
CONT_ADDRESS1
CONT_ADDRESS2
CONT_ADDRESS3
CONT_ADDRESS4
CONT_CITY
CONT_POSTAL_CODE
CONT_STATE
CONT_PROVINCE
CONT_COUNTY
ORG_ID