VIEW: APPS.CS_SYSTEMS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'