View: CS_SYSTEMS_V

Product:	CS - Service
Description:
Implementation/DBA Data:	APPS.CS_SYSTEMS_V

View Text

SELECT S.ROW_ID
, S.SYSTEM_ID
, S.LAST_UPDATE_DATE
, S.LAST_UPDATED_BY
, S.CREATION_DATE
, S.CREATED_BY
, S.LAST_UPDATE_LOGIN
, S.CUSTOMER_ID
, HZP.PARTY_NAME CUSTOMER_NAME
, HZA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, S.SYSTEM_TYPE_CODE
, LK.MEANING SYSTEM_TYPE
, S.SERIAL_NUMBER
, S.PARENT_SYSTEM_ID
, S.TECHNICAL_CONTACT_ID
, S.SERVICE_ADMIN_CONTACT_ID
, S.INSTALL_SITE_USE_ID
, S.BILL_TO_CONTACT_ID
, S.BILL_TO_SITE_USE_ID
, S.SHIP_TO_SITE_USE_ID
, S.SHIP_TO_CONTACT_ID
, S.COTERMINATE_DAY_MONTH
, S.START_DATE_ACTIVE
, S.END_DATE_ACTIVE
, S.AUTOCREATED_FROM_SYSTEM_ID
, S.ATTRIBUTE1
, S.ATTRIBUTE2
, S.ATTRIBUTE3
, S.ATTRIBUTE4
, S.ATTRIBUTE5
, S.ATTRIBUTE6
, S.ATTRIBUTE7
, S.ATTRIBUTE8
, S.ATTRIBUTE9
, S.ATTRIBUTE10
, S.ATTRIBUTE11
, S.ATTRIBUTE12
, S.ATTRIBUTE13
, S.ATTRIBUTE14
, S.ATTRIBUTE15
, S.CONTEXT
, S.CONFIG_SYSTEM_TYPE
, S.ORG_ID
, S.NAME
, SUBSTR(S.DESCRIPTION
, 1
, 240) DESCRIPTION
, HZPTY1.PARTY_ID SHIP_TO_CUSTOMER_ID
, HZPTY1.PARTY_NAME SHIP_TO_CUSTOMER
, HZPTY1.PARTY_TYPE SHIP_PARTY_TYPE
, HZPTY1.PERSON_LAST_NAME SHIP_FIRST_NAME
, HZPTY1.PERSON_FIRST_NAME SHIP_LAST_NAME
, HZLOC1.LOCATION_ID SHIP_TO_LOCATION_ID
, HZLOC1.DESCRIPTION SHIP_DESCRIPTION
, HZLOC1.ADDRESS1||DECODE(HZLOC1.ADDRESS1
, ''
, ''
, DECODE(HZLOC1.ADDRESS2
, ''
, ''
, '
, '))||HZLOC1.ADDRESS2 SHIP_TO_ADDRESS1
, HZLOC1.ADDRESS2|| DECODE(HZLOC1.ADDRESS3
, ''
, ''
, DECODE(HZLOC1.ADDRESS4
, ''
, ''
, '
, '))||HZLOC1.ADDRESS4 SHIP_TO_ADDRESS2
, SUBSTR(HZLOC1.CITY||DECODE(HZLOC1.CITY
, ''
, ''
, DECODE(HZLOC1.STATE|| HZLOC1.COUNTRY||HZLOC1.POSTAL_CODE
, ''
, ''
, '
, ')) || HZLOC1.STATE||' '||HZLOC1.POSTAL_CODE|| ' '|| HZLOC1.COUNTRY
, 1
, 220) SHIP_TO_ADDRESS3
, HZLOC1.ADDRESS4 SHIP_TO_ADDRESS4
, HZLOC1.CITY SHIP_TO_LOCATION
, HZLOC1.STATE SHIP_STATE
, HZLOC1.POSTAL_CODE SHIP_POSTAL_CODE
, HZLOC1.COUNTRY SHIP_COUNTRY
, HZPTY2.PARTY_ID INSTALL_CUSTOMER_ID
, HZPTY2.PARTY_NAME INSTALL_CUSTOMER
, HZPTY2.PARTY_TYPE INSTALL_PARTY_TYPE
, HZPTY2.PERSON_LAST_NAME INSTALL_FIRST_NAME
, HZPTY2.PERSON_FIRST_NAME INSTALL_LAST_NAME
, HZLOC2.LOCATION_ID INSTALL_LOCATION_ID
, HZLOC2.DESCRIPTION INSTALL_DESCRIPTION
, HZLOC2.ADDRESS1||DECODE(HZLOC2.ADDRESS1
, ''
, ''
, DECODE(HZLOC2.ADDRESS2
, ''
, ''
, '
, '))||HZLOC2.ADDRESS2 INSTALL_ADDRESS1
, HZLOC2.ADDRESS2|| DECODE(HZLOC2.ADDRESS3
, ''
, ''
, DECODE(HZLOC2.ADDRESS4
, ''
, ''
, '
, '))||HZLOC2.ADDRESS4 INSTALL_ADDRESS2
, SUBSTR(HZLOC2.CITY||DECODE(HZLOC2.CITY
, ''
, ''
, DECODE(HZLOC2.STATE|| HZLOC2.COUNTRY||HZLOC2.POSTAL_CODE
, ''
, ''
, '
, ')) || HZLOC2.STATE||' '||HZLOC2.POSTAL_CODE|| ' '|| HZLOC2.COUNTRY
, 1
, 220) INSTALL_ADDRESS3
, HZLOC2.ADDRESS4 INSTALL_ADDRESS4
, HZLOC2.CITY INSTALL_LOCATION
, HZLOC2.STATE INSTALL_STATE
, HZLOC2.POSTAL_CODE INSTALL_POSTAL_CODE
, HZLOC2.COUNTRY INSTALL_COUNTRY
, HZPTY3.PARTY_ID BILL_TO_CUSTOMER_ID
, HZPTY3.PARTY_NAME BILL_TO_CUSTOMER
, HZPTY3.PARTY_TYPE BILL_PARTY_TYPE
, HZPTY3.PERSON_LAST_NAME BILL_FIRST_NAME
, HZPTY3.PERSON_FIRST_NAME BILL_LAST_NAME
, HZLOC3.LOCATION_ID BILL_TO_LOCATION_ID
, HZLOC3.DESCRIPTION BILL_DESCRIPTION
, HZLOC3.ADDRESS1||DECODE(HZLOC3.ADDRESS1
, ''
, ''
, DECODE(HZLOC3.ADDRESS2
, ''
, ''
, '
, '))||HZLOC3.ADDRESS2 BILL_TO_ADDRESS1
, HZLOC3.ADDRESS2|| DECODE(HZLOC3.ADDRESS3
, ''
, ''
, DECODE(HZLOC3.ADDRESS4
, ''
, ''
, '
, '))||HZLOC3.ADDRESS4 BILL_TO_ADDRESS2
, SUBSTR(HZLOC3.CITY||DECODE(HZLOC3.CITY
, ''
, ''
, DECODE(HZLOC3.STATE|| HZLOC3.COUNTRY||HZLOC3.POSTAL_CODE
, ''
, ''
, '
, ')) || HZLOC3.STATE||' '||HZLOC3.POSTAL_CODE|| ' '|| HZLOC3.COUNTRY
, 1
, 220) BILL_TO_ADDRESS3
, HZLOC3.ADDRESS4 BILL_TO_ADDRESS4
, HZLOC3.CITY BILL_TO_LOCATION
, HZLOC3.STATE BILL_STATE
, HZLOC3.POSTAL_CODE BILL_POSTAL_CODE
, HZLOC3.COUNTRY BILL_COUNTRY
, HZPTY4.PARTY_NAME TECHNICAL_CONTACT
, HZPTY4.PERSON_LAST_NAME TECHNICAL_CONTACT_LAST_NAME
, HZPTY4.PERSON_FIRST_NAME TECHNICAL_CONTACT_FIRST_NAME
, HZPTY5.PARTY_NAME SERVICE_ADMIN_CONTACT
, HZPTY5.PERSON_LAST_NAME ADMINISTRATIVE_LAST_NAME
, HZPTY5.PERSON_FIRST_NAME ADMINISTRATIVE_FIRST_NAME
, HZPTY6.PARTY_NAME SHIP_TO_CONTACT
, HZPTY7.PARTY_NAME BILL_TO_CONTACT
, HZA.PARTY_ID PARTY_ID
FROM HZ_CUST_ACCOUNTS HZA
, HZ_PARTIES HZP
, HZ_PARTY_SITES HZSITE1
, HZ_PARTIES HZPTY1
, HZ_LOCATIONS HZLOC1
, HZ_PARTY_SITES HZSITE2
, HZ_PARTIES HZPTY2
, HZ_LOCATIONS HZLOC2
, HZ_CUST_ACCT_SITES_ALL HZACCTSITE3
, HZ_PARTY_SITES HZSITE3
, HZ_PARTIES HZPTY3
, HZ_LOCATIONS HZLOC3
, HZ_PARTIES HZPTY4
, HZ_PARTIES HZPTY5
, HZ_PARTIES HZPTY6
, HZ_PARTIES HZPTY7
, CS_LOOKUPS LK
, CS_SYSTEMS S
WHERE S.SHIP_TO_SITE_USE_ID = HZSITE1.PARTY_SITE_ID(+)
AND S.CUSTOMER_ID = HZA.CUST_ACCOUNT_ID
AND HZA.PARTY_ID = HZP.PARTY_ID
AND HZSITE1.LOCATION_ID = HZLOC1.LOCATION_ID(+)
AND HZSITE1.PARTY_ID = HZPTY1.PARTY_ID(+)
AND S.INSTALL_SITE_USE_ID = HZSITE2.PARTY_SITE_ID(+)
AND HZSITE2.LOCATION_ID = HZLOC2.LOCATION_ID(+)
AND HZSITE2.PARTY_ID = HZPTY2.PARTY_ID(+)
AND S.BILL_TO_SITE_USE_ID = HZACCTSITE3.CUST_ACCT_SITE_ID(+)
AND HZACCTSITE3.PARTY_SITE_ID = HZSITE3.PARTY_SITE_ID(+)
AND HZSITE3.LOCATION_ID = HZLOC3.LOCATION_ID(+)
AND HZSITE3.PARTY_ID = HZPTY3.PARTY_ID(+)
AND S.TECHNICAL_CONTACT_ID = HZPTY4.PARTY_ID(+)
AND S.SERVICE_ADMIN_CONTACT_ID = HZPTY5.PARTY_ID(+)
AND S.SHIP_TO_CONTACT_ID = HZPTY6.PARTY_ID(+)
AND S.BILL_TO_CONTACT_ID = HZPTY7.PARTY_ID(+)
AND S.SYSTEM_TYPE_CODE = LK.LOOKUP_CODE(+)
AND LK.LOOKUP_TYPE (+) = 'SYSTEM_TYPE'

Columns