View: CSI_SYSTEMS_V

Product:	CSI - Install Base
Description:	System Details with foreign key resolutions.
Implementation/DBA Data:	APPS.CSI_SYSTEMS_V

View Text

SELECT S.ROWID ROW_ID
, S.SYSTEM_ID SYSTEM_ID
, S.OPERATING_UNIT_ID OPERATING_UNIT_ID
, S.LAST_UPDATE_DATE LAST_UPDATE_DATE
, S.LAST_UPDATED_BY LAST_UPDATED_BY
, S.CREATION_DATE CREATION_DATE
, S.CREATED_BY CREATED_BY
, S.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, S.CUSTOMER_ID CUSTOMER_ID
, HP.PARTY_NAME CUSTOMER_NAME
, HP.PARTY_NUMBER CUSTOMER_PARTY_NUMBER
, HZA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, S.SYSTEM_TYPE_CODE SYSTEM_TYPE_CODE
, LK.MEANING SYSTEM_TYPE
, S.SYSTEM_NUMBER SYSTEM_NUMBER
, S.PARENT_SYSTEM_ID PARENT_SYSTEM_ID
, S.TECHNICAL_CONTACT_ID TECHNICAL_CONTACT_ID
, S.SERVICE_ADMIN_CONTACT_ID SERVICE_ADMIN_CONTACT_ID
, S.INSTALL_SITE_USE_ID INSTALL_SITE_USE_ID
, S.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID
, S.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, S.SHIP_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID
, S.SHIP_TO_CONTACT_ID SHIP_TO_CONTACT_ID
, S.COTERMINATE_DAY_MONTH COTERMINATE_DAY_MONTH
, S.START_DATE_ACTIVE START_DATE_ACTIVE
, S.END_DATE_ACTIVE END_DATE_ACTIVE
, S.AUTOCREATED_FROM_SYSTEM_ID AUTOCREATED_FROM_SYSTEM
, S.ATTRIBUTE1 ATTRIBUTE1
, S.ATTRIBUTE2 ATTRIBUTE2
, S.ATTRIBUTE3 ATTRIBUTE3
, S.ATTRIBUTE4 ATTRIBUTE4
, S.ATTRIBUTE5 ATTRIBUTE5
, S.ATTRIBUTE6 ATTRIBUTE6
, S.ATTRIBUTE7 ATTRIBUTE7
, S.ATTRIBUTE8 ATTRIBUTE8
, S.ATTRIBUTE9 ATTRIBUTE9
, S.ATTRIBUTE10 ATTRIBUTE10
, S.ATTRIBUTE11 ATTRIBUTE11
, S.ATTRIBUTE12 ATTRIBUTE12
, S.ATTRIBUTE13 ATTRIBUTE13
, S.ATTRIBUTE14 ATTRIBUTE14
, S.ATTRIBUTE15 ATTRIBUTE15
, S.CONTEXT CONTEXT
, S.CONFIG_SYSTEM_TYPE CONFIG_SYSTEM_TYPE
, S.NAME NAME
, SUBSTR(S.DESCRIPTION
, 1
, 240) DESCRIPTION
, S.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, HP_S.PARTY_ID SHIP_TO_CUSTOMER_ID
, HP_S.PARTY_NAME SHIP_TO_CUSTOMER
, HP_S.PARTY_NUMBER SHIP_TO_CUSTOMER_NUMBER
, HP_S.PARTY_TYPE SHIP_PARTY_TYPE
, HP_S.PERSON_LAST_NAME SHIP_FIRST_NAME
, HP_S.PERSON_FIRST_NAME SHIP_LAST_NAME
, HPS_S.PARTY_SITE_NUMBER SHIP_TO_SITE_NUMBER
, HL_S.LOCATION_ID SHIP_TO_LOCATION_ID
, HL_S.DESCRIPTION SHIP_DESCRIPTION
, HL_S.ADDRESS1||DECODE(HL_S.ADDRESS1
, ''
, ''
, DECODE(HL_S.ADDRESS2
, ''
, ''
, '
, '))||HL_S.ADDRESS2 SHIP_TO_ADDRESS1
, HL_S.ADDRESS3|| DECODE(HL_S.ADDRESS3
, ''
, ''
, DECODE(HL_S.ADDRESS4
, ''
, ''
, '
, '))||HL_S.ADDRESS4 SHIP_TO_ADDRESS2
, SUBSTR(HL_S.CITY||DECODE(HL_S.CITY
, ''
, ''
, DECODE(HL_S.STATE||HL_S.COUNTRY||HL_S.POSTAL_CODE
, ''
, ''
, '
, ')) || HL_S.STATE||''|| HL_S.POSTAL_CODE|| ' '|| HL_S.COUNTRY
, 1
, 220) SHIP_TO_ADDRESS3
, HL_S.ADDRESS4 SHIP_TO_ADDRESS4
, HL_S.CITY SHIP_TO_LOCATION
, HL_S.STATE SHIP_STATE
, HL_S.POSTAL_CODE SHIP_POSTAL_CODE
, HL_S.COUNTRY SHIP_COUNTRY
, HP_I.PARTY_ID INSTALL_CUSTOMER_ID
, HP_I.PARTY_NUMBER INSTALL_CUSTOMER_NUMBER
, HP_I.PARTY_NAME INSTALL_CUSTOMER
, HP_I.PARTY_TYPE INSTALL_PARTY_TYPE
, HP_I.PERSON_FIRST_NAME INSTALL_FIRST_NAME
, HP_I.PERSON_LAST_NAME INSTALL_LAST_NAME
, HPS_I.PARTY_SITE_NUMBER INSTALL_SITE_NUMBER
, HL_I.LOCATION_ID INSTALL_LOCATION_ID
, HL_I.DESCRIPTION INSTALL_DESCRIPTION
, HL_I.ADDRESS1||DECODE(HL_I.ADDRESS1
, ''
, ''
, DECODE(HL_I.ADDRESS2
, ''
, ''
, '
, '))||HL_I.ADDRESS2 INSTALL_ADDRESS1
, HL_I.ADDRESS3|| DECODE(HL_I.ADDRESS3
, ''
, ''
, DECODE(HL_I.ADDRESS4
, ''
, ''
, '
, '))||HL_I.ADDRESS4 INSTALL_ADDRESS2
, SUBSTR(HL_I.CITY||DECODE(HL_I.CITY
, ''
, ''
, DECODE(HL_I.STATE||HL_I.COUNTRY||HL_I.POSTAL_CODE
, ''
, ''
, '
, ')) || HL_I.STATE||''||HL_I.POSTAL_CODE|| ' '|| HL_I.COUNTRY
, 1
, 220) INSTALL_ADDRESS3
, HL_I.ADDRESS4 INSTALL_ADDRESS4
, HL_I.CITY INSTALL_LOCATION
, HL_I.STATE INSTALL_STATE
, HL_I.POSTAL_CODE INSTALL_POSTAL_CODE
, HL_I.COUNTRY INSTALL_COUNTRY
, HP_B.PARTY_ID BILL_TO_CUSTOMER_ID
, HP_B.PARTY_NUMBER BILL_TO_CUSTOMER_NUMBER
, HP_B.PARTY_NAME BILL_TO_CUSTOMER
, HP_B.PARTY_TYPE BILL_PARTY_TYPE
, HP_B.PERSON_FIRST_NAME BILL_FIRST_NAME
, HP_B.PERSON_LAST_NAME BILL_LAST_NAME
, HPS_B.PARTY_SITE_NUMBER BILL_TO_SITE_NUMBER
, HL_B.LOCATION_ID BILL_TO_LOCATION_ID
, HL_B.DESCRIPTION BILL_DESCRIPTION
, HL_B.ADDRESS1||DECODE(HL_B.ADDRESS1
, ''
, ''
, DECODE(HL_B.ADDRESS2
, ''
, ''
, '
, '))||HL_B.ADDRESS2 BILL_TO_ADDRESS1
, HL_B.ADDRESS3|| DECODE(HL_B.ADDRESS3
, ''
, ''
, DECODE(HL_B.ADDRESS4
, ''
, ''
, '
, '))||HL_B.ADDRESS4 BILL_TO_ADDRESS2
, SUBSTR(HL_B.CITY||DECODE(HL_B.CITY
, ''
, ''
, DECODE(HL_B.STATE||HL_B.COUNTRY||HL_B.POSTAL_CODE
, ''
, ''
, '
, ')) || HL_B.STATE||''||HL_B.POSTAL_CODE|| ' '|| HL_B.COUNTRY
, 1
, 220) BILL_TO_ADDRESS3
, HL_B.ADDRESS4 BILL_TO_ADDRESS4
, HL_B.CITY BILL_TO_LOCATION
, HL_B.STATE BILL_STATE
, HL_B.POSTAL_CODE BILL_POSTAL_CODE
, HL_B.COUNTRY BILL_COUNTRY
, HP_TC.PARTY_NUMBER TECHNICAL_CONTACT_NUMBER
, HP_TC.PARTY_NAME TECHNICAL_CONTACT
, HP_TC.PERSON_LAST_NAME TECHNICAL_CONTACT_LAST_NAME
, HP_TC.PERSON_FIRST_NAME TECHNICAL_CONTACT_FIRST_NAME
, HP_SAC.PARTY_NUMBER SERVICE_ADMIN_CONTACT_NUMBER
, HP_SAC.PARTY_NAME SERVICE_ADMIN_CONTACT
, HP_SAC.PERSON_LAST_NAME ADMINISTRATIVE_LAST_NAME
, HP_SAC.PERSON_FIRST_NAME ADMINISTRATIVE_FIRST_NAME
, HP_SC.PARTY_NUMBER SHIP_TO_CONTACT_NUMBER
, HP_SC.PARTY_NAME SHIP_TO_CONTACT
, HP_BC.PARTY_NUMBER BILL_TO_CONTACT_NUMBER
, HP_BC.PARTY_NAME BILL_TO_CONTACT
, HZA.PARTY_ID PARTY_ID
, PARENT.NAME PARENT_NAME
, PARENT.DESCRIPTION PARENT_DESCRIPTION
, PARENT.SYSTEM_NUMBER PARENT_NUMBER
, HROU.NAME OPERATING_UNIT_NAME
FROM HZ_CUST_ACCOUNTS HZA
, HZ_PARTIES HP
, HZ_CUST_ACCT_SITES_ALL HCAS_S
, HZ_CUST_SITE_USES_ALL HCSU_S
, HZ_PARTY_SITES HPS_S
, HZ_PARTIES HP_S
, HZ_LOCATIONS HL_S
, HZ_PARTY_SITES HPS_I
, HZ_PARTIES HP_I
, HZ_LOCATIONS HL_I
, HZ_CUST_ACCT_SITES_ALL HCAS_B
, HZ_CUST_SITE_USES_ALL HCSU_B
, HZ_PARTY_SITES HPS_B
, HZ_PARTIES HP_B
, HZ_LOCATIONS HL_B
, HZ_PARTIES HP_TC
, HZ_PARTIES HP_SAC
, HZ_PARTIES HP_SC
, HZ_PARTIES HP_BC
, CSI_LOOKUPS LK
, CSI_SYSTEMS_VL S
, CSI_SYSTEMS_VL PARENT
, HR_OPERATING_UNITS HROU
WHERE S.CUSTOMER_ID = HZA.CUST_ACCOUNT_ID AND HZA.PARTY_ID = HP.PARTY_ID AND S.SHIP_TO_SITE_USE_ID = HCSU_S.SITE_USE_ID (+) AND HCSU_S.CUST_ACCT_SITE_ID = HCAS_S.CUST_ACCT_SITE_ID (+) AND HCAS_S.PARTY_SITE_ID = HPS_S.PARTY_SITE_ID(+) AND HPS_S.LOCATION_ID = HL_S.LOCATION_ID(+) AND HPS_S.PARTY_ID = HP_S.PARTY_ID(+) AND S.INSTALL_SITE_USE_ID = HPS_I.PARTY_SITE_ID(+) AND HPS_I.PARTY_ID = HP_I.PARTY_ID(+) AND HPS_I.LOCATION_ID = HL_I.LOCATION_ID(+) AND S.BILL_TO_SITE_USE_ID = HCSU_B.SITE_USE_ID (+) AND HCSU_B.CUST_ACCT_SITE_ID = HCAS_B.CUST_ACCT_SITE_ID(+) AND HCAS_B.PARTY_SITE_ID = HPS_B.PARTY_SITE_ID(+) AND HPS_B.LOCATION_ID = HL_B.LOCATION_ID(+) AND HPS_B.PARTY_ID = HP_B.PARTY_ID(+) AND S.TECHNICAL_CONTACT_ID = HP_TC.PARTY_ID(+) AND S.SERVICE_ADMIN_CONTACT_ID = HP_SAC.PARTY_ID(+) AND S.SHIP_TO_CONTACT_ID = HP_SC.PARTY_ID(+) AND S.BILL_TO_CONTACT_ID = HP_BC.PARTY_ID(+) AND S.SYSTEM_TYPE_CODE = LK.LOOKUP_CODE(+) AND LK.LOOKUP_TYPE (+) = 'CSI_SYSTEM_TYPE' AND PARENT.SYSTEM_ID(+) = S.PARENT_SYSTEM_ID AND S.OPERATING_UNIT_ID = HROU.ORGANIZATION_ID(+)

Columns