View: CS_SYSTEMS_CP_MAINT_V

View Text

SELECT CS.SYSTEM_ID
, CS.LAST_UPDATE_DATE
, CS.LAST_UPDATED_BY
, CS.CREATION_DATE
, CS.CREATED_BY
, CS.LAST_UPDATE_LOGIN
, CS.NAME
, CS.CUSTOMER_ID
, CS.SYSTEM_TYPE_CODE
, CS.DESCRIPTION
, CS.SERIAL_NUMBER
, CS.PARENT_SYSTEM_ID
, CS.TECHNICAL_CONTACT_ID
, CS.SERVICE_ADMIN_CONTACT_ID
, CS.INSTALL_SITE_USE_ID
, CS.BILL_TO_CONTACT_ID
, CS.BILL_TO_SITE_USE_ID
, CS.SHIP_TO_SITE_USE_ID
, CS.SHIP_TO_CONTACT_ID
, CS.COTERMINATE_DAY_MONTH
, CS.START_DATE_ACTIVE
, CS.END_DATE_ACTIVE
, CS.AUTOCREATED_FROM_SYSTEM_ID
, CS.ATTRIBUTE1
, CS.ATTRIBUTE2
, CS.ATTRIBUTE3
, CS.ATTRIBUTE4
, CS.ATTRIBUTE5
, CS.ATTRIBUTE6
, CS.ATTRIBUTE7
, CS.ATTRIBUTE8
, CS.ATTRIBUTE9
, CS.ATTRIBUTE10
, CS.ATTRIBUTE11
, CS.ATTRIBUTE12
, CS.ATTRIBUTE13
, CS.ATTRIBUTE14
, CS.ATTRIBUTE15
, CS.CONTEXT
, CUST.CUSTOMER_NAME
, L.MEANING SYSTEM_TYPE
, CUST1.CUSTOMER_NAME SHIP_TO_CUSTOMER
, SU1.LOCATION SHIP_TO_LOCATION
, ADDR1.ADDRESS1 ||DECODE(ADDR1.ADDRESS1
, ''
, ''
, DECODE(ADDR1.ADDRESS2
, ''
, ''
, '
, '))||ADDR1.ADDRESS2 SHIP_TO_ADDRESS1
, ADDR1.ADDRESS3 || DECODE(ADDR1.ADDRESS3
, ''
, ''
, DECODE(ADDR1.ADDRESS4
, ''
, ''
, '
, '))||ADDR1.ADDRESS4 SHIP_TO_ADDRESS2
, SUBSTR(ADDR1.CITY||DECODE(ADDR1.CITY
, ''
, ''
, DECODE(ADDR1.STATE|| ADDR1.COUNTRY||ADDR1.POSTAL_CODE
, ''
, ''
, '
, ')) || ADDR1.STATE|| ' '||ADDR1.POSTAL_CODE||' ' ||ADDR1.COUNTRY
, 1
, 220) SHIP_TO_ADDRESS3
, CUST2.CUSTOMER_NAME INSTALL_CUSTOMER
, SU2.LOCATION INSTALL_LOCATION
, ADDR2.ADDRESS1 ||DECODE(ADDR2.ADDRESS1
, ''
, ''
, DECODE(ADDR2.ADDRESS2
, ''
, ''
, '
, '))||ADDR2.ADDRESS2 INSTALL_ADDRESS1
, ADDR2.ADDRESS3 || DECODE(ADDR2.ADDRESS3
, ''
, ''
, DECODE(ADDR2.ADDRESS4
, ''
, ''
, '
, '))||ADDR2.ADDRESS4 INSTALL_ADDRESS2
, SUBSTR(ADDR2.CITY||DECODE(ADDR2.CITY
, ''
, ''
, DECODE(ADDR2.STATE|| ADDR2.COUNTRY||ADDR2.POSTAL_CODE
, ''
, ''
, '
, ')) || ADDR2.STATE|| ' '||ADDR2.POSTAL_CODE|| ' ' ||ADDR2.COUNTRY
, 1
, 220) INSTALL_ADDRESS3
, CUST3.CUSTOMER_NAME BILL_TO_CUSTOMER
, SU3.LOCATION BILL_TO_LOCATION
, ADDR3.ADDRESS1 ||DECODE(ADDR3.ADDRESS1
, ''
, ''
, DECODE(ADDR3.ADDRESS2
, ''
, ''
, '
, '))||ADDR3.ADDRESS2 BILL_TO_ADDRESS1
, ADDR3.ADDRESS3 || DECODE(ADDR3.ADDRESS3
, ''
, ''
, DECODE(ADDR3.ADDRESS4
, ''
, ''
, '
, '))||ADDR3.ADDRESS4 BILL_TO_ADDRESS2
, SUBSTR(ADDR3.CITY||DECODE(ADDR3.CITY
, ''
, ''
, DECODE(ADDR3.STATE|| ADDR3.COUNTRY||ADDR3.POSTAL_CODE
, ''
, ''
, '
, ')) || ADDR3.STATE|| ' '||ADDR3.POSTAL_CODE|| ' ' ||ADDR3.COUNTRY
, 1
, 220) BILL_TO_ADDRESS3
, CONT1.LAST_NAME||DECODE(CONT1.FIRST_NAME
, ''
, ''
, '
, ' || CONT1.FIRST_NAME) CS_TECHNICAL_CONTACT
, CONT2.LAST_NAME||DECODE(CONT2.FIRST_NAME
, ''
, ''
, '
, ' || CONT2.FIRST_NAME) CS_SERVICE_ADMIN_CONTACT
, CONT3.LAST_NAME||DECODE(CONT3.FIRST_NAME
, ''
, ''
, '
, ' || CONT3.FIRST_NAME) CS_BILL_TO_CONTACT
, CONT4.LAST_NAME||DECODE(CONT4.FIRST_NAME
, ''
, ''
, '
, ' || CONT4.FIRST_NAME) CS_SHIP_TO_CONTACT
FROM CS_SYSTEMS CS
, RA_CUSTOMERS CUST
, RA_CUSTOMERS CUST1
, RA_ADDRESSES ADDR1
, RA_SITE_USES SU1
, RA_CUSTOMERS CUST2
, RA_ADDRESSES ADDR2
, RA_SITE_USES SU2
, RA_CUSTOMERS CUST3
, RA_ADDRESSES ADDR3
, RA_SITE_USES SU3
, RA_CONTACTS CONT1
, RA_CONTACTS CONT2
, RA_CONTACTS CONT3
, RA_CONTACTS CONT4
, CS_LOOKUPS L
WHERE CS.CUSTOMER_ID = CUST.CUSTOMER_ID
AND CS.SYSTEM_TYPE_CODE = L.LOOKUP_CODE
AND L.LOOKUP_TYPE = 'SYSTEM_TYPE'
AND SU1.SITE_USE_ID(+) = CS.SHIP_TO_SITE_USE_ID
AND ADDR1.ADDRESS_ID(+) = SU1.ADDRESS_ID
AND CUST1.CUSTOMER_ID(+) = ADDR1.CUSTOMER_ID
AND SU2.SITE_USE_ID(+) = CS.INSTALL_SITE_USE_ID
AND ADDR2.ADDRESS_ID(+) = SU2.ADDRESS_ID
AND CUST2.CUSTOMER_ID(+) = ADDR2.CUSTOMER_ID
AND SU3.SITE_USE_ID(+) = CS.BILL_TO_SITE_USE_ID
AND ADDR3.ADDRESS_ID(+) = SU3.ADDRESS_ID
AND CUST3.CUSTOMER_ID(+) = ADDR3.CUSTOMER_ID
AND CONT1.CONTACT_ID(+) = CS.TECHNICAL_CONTACT_ID
AND CONT2.CONTACT_ID(+) = CS.SERVICE_ADMIN_CONTACT_ID
AND CONT3.CONTACT_ID(+) = CS.BILL_TO_CONTACT_ID
AND CONT4.CONTACT_ID(+) = CS.SHIP_TO_CONTACT_ID

Columns