View: ICX_CUSTOMER_ACCOUNT_V

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
, SHIP_ADDR.ADDRESS_ID
, SHIP_ADDR.COUNTRY
, CUST.CUSTOMER_NAME || DECODE(SHIP_ADDR.ADDRESS1
, NULL
, NULL
, '
') || SHIP_ADDR.ADDRESS1 || DECODE(SHIP_ADDR.ADDRESS2
, NULL
, NULL
, '
') || SHIP_ADDR.ADDRESS2 || DECODE(SHIP_ADDR.ADDRESS3
, NULL
, NULL
, '
')||SHIP_ADDR.ADDRESS3 || DECODE(SHIP_ADDR.ADDRESS4
, NULL
, NULL
, '
')||SHIP_ADDR.ADDRESS4 || DECODE(SHIP_ADDR.CITY
, NULL
, NULL
, '
') || SHIP_ADDR.CITY || DECODE(SHIP_ADDR.PROVINCE
, NULL
, NULL
, '
') || SHIP_ADDR.PROVINCE || DECODE(SHIP_ADDR.COUNTY
, NULL
, NULL
, '
') || SHIP_ADDR.COUNTY || DECODE(SHIP_ADDR.STATE || ' ' ||SHIP_ADDR.POSTAL_CODE
, NULL
, NULL
, '
') || SHIP_ADDR.STATE || ' ' || SHIP_ADDR.POSTAL_CODE || DECODE(NVL(TERR1.TERRITORY_SHORT_NAME
, SHIP_ADDR.COUNTRY)
, NULL
, NULL
, '
') || NVL(TERR1.TERRITORY_SHORT_NAME
, SHIP_ADDR.COUNTRY)
, SHIP_ADDR.ADDRESS1
, SHIP_ADDR.ADDRESS2
, SHIP_ADDR.ADDRESS3
, SHIP_ADDR.ADDRESS4
, SHIP_ADDR.CITY
, SHIP_ADDR.POSTAL_CODE
, SHIP_ADDR.STATE
, SHIP_ADDR.PROVINCE
, SHIP_ADDR.COUNTY
, BILL_ADDR.ADDRESS_ID
, BILL_ADDR.COUNTRY
, BILL_ADDR.ADDRESS1 || DECODE(BILL_ADDR.ADDRESS2
, NULL
, NULL
, '
') || BILL_ADDR.ADDRESS2 || DECODE(BILL_ADDR.ADDRESS3
, NULL
, NULL
, '
')||BILL_ADDR.ADDRESS3 || DECODE(BILL_ADDR.ADDRESS4
, NULL
, NULL
, '
')||BILL_ADDR.ADDRESS4 || DECODE(BILL_ADDR.CITY
, NULL
, NULL
, '
') || BILL_ADDR.CITY || DECODE(BILL_ADDR.PROVINCE
, NULL
, NULL
, '
') || BILL_ADDR.PROVINCE || DECODE(BILL_ADDR.COUNTY
, NULL
, NULL
, '
') || BILL_ADDR.COUNTY || DECODE(BILL_ADDR.STATE || ' ' || BILL_ADDR.POSTAL_CODE
, NULL
, NULL
, '
') || BILL_ADDR.STATE || ' ' || BILL_ADDR.POSTAL_CODE || DECODE(NVL(TERR2.TERRITORY_SHORT_NAME
, BILL_ADDR.COUNTRY)
, NULL
, NULL
, '
') || NVL(TERR2.TERRITORY_SHORT_NAME
, BILL_ADDR.COUNTRY)
, BILL_ADDR.ADDRESS1
, BILL_ADDR.ADDRESS2
, BILL_ADDR.ADDRESS3
, BILL_ADDR.ADDRESS4
, BILL_ADDR.CITY
, BILL_ADDR.POSTAL_CODE
, BILL_ADDR.STATE
, BILL_ADDR.PROVINCE
, BILL_ADDR.COUNTY
, BILL_ADDR.BILL_TO_FLAG
, SHIP_ADDR.MARKET_FLAG
, SHIP_ADDR.SHIP_TO_FLAG
, SHIP_ADDR.ADDRESS_STYLE
, SHIP_ADDR.KEY_ACCOUNT_FLAG
, SHIP_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(TERR3.TERRITORY_SHORT_NAME
, CONT_ADDR.COUNTRY)
, NULL
, NULL
, '
') || NVL(TERR3.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
, SHIP_ADDR.ORG_ID
FROM AR_LOOKUPS LOOK1
, AR_LOOKUPS LOOK2
, RA_CUSTOMERS CUST
, FND_TERRITORIES_TL TERR1
, FND_TERRITORIES_TL TERR2
, FND_TERRITORIES_TL TERR3
, RA_PHONES CUST_PHON
, RA_CONTACTS CONT
, RA_PHONES CONT_PHON
, RA_ADDRESSES CONT_ADDR
, RA_ADDRESSES SHIP_ADDR
, RA_ADDRESSES BILL_ADDR
, RA_SITE_USES RASIT
, RA_SITE_USES RASIT2
WHERE SHIP_ADDR.CUSTOMER_ID = CUST.CUSTOMER_ID
AND CUST.CUSTOMER_ID = CONT.CUSTOMER_ID
AND SHIP_ADDR.ADDRESS_ID = RASIT.ADDRESS_ID
AND RASIT2.ADDRESS_ID = BILL_ADDR.ADDRESS_ID(+)
AND NVL(RASIT.BILL_TO_SITE_USE_ID
, RASIT.SITE_USE_ID) = RASIT2.SITE_USE_ID(+)
AND SHIP_ADDR.ADDRESS_ID = CUST_PHON.ADDRESS_ID(+)
AND SHIP_ADDR.CUSTOMER_ID = CUST_PHON.CUSTOMER_ID(+)
AND CUST_PHON.CONTACT_ID(+) IS NULL
AND CUST_PHON.PHONE_TYPE = LOOK1.LOOKUP_CODE(+)
AND LOOK1.LOOKUP_TYPE(+) = 'COMMUNICATION_TYPE'
AND CUST_PHON.PRIMARY_FLAG(+) = 'Y'
AND SHIP_ADDR.COUNTRY = TERR1.TERRITORY_CODE(+)
AND BILL_ADDR.COUNTRY = TERR2.TERRITORY_CODE(+)
AND CUST.CUSTOMER_PROSPECT_CODE = LOOK2.LOOKUP_CODE(+)
AND LOOK2.LOOKUP_TYPE(+) = 'CUSTOMER_PROPECT_CODE'
AND CONT.MAILING_ADDRESS_ID = CONT_ADDR.ADDRESS_ID(+)
AND CONT_ADDR.COUNTRY = TERR3.TERRITORY_CODE(+)
AND CONT_PHON.STATUS(+) = 'A'
AND CONT.MAILING_ADDRESS_ID = CONT_PHON.ADDRESS_ID(+)
AND CONT_PHON.PRIMARY_FLAG(+) = 'Y'
AND CUST.STATUS = 'A'
AND CONT.STATUS = 'A'
AND TERR1.LANGUAGE(+) = USERENV('LANG')
AND TERR2.LANGUAGE(+) = USERENV('LANG')
AND TERR3.LANGUAGE(+) = USERENV('LANG')
AND RASIT.STATUS = 'A'
AND RASIT.SITE_USE_CODE = 'SHIP_TO'
AND SHIP_ADDR.STATUS = 'A'

Columns