VIEW: APPS.CS_CP_SYSTEMS_MAINT_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, HZPTY8.PARTY_NAME Customer_name, S.SYSTEM_TYPE_CODE, 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.NAME, S.DESCRIPTION, HZPTY1.PARTY_ID Ship_party_id, HZPTY1.PARTY_NAME Ship_party_name, HZPTY1.PARTY_TYPE Ship_party_type, HZPTY1.PERSON_LAST_NAME Ship_first_name, HZPTY1.PERSON_FIRST_NAME Ship_last_name, HZLOC1.LOCATION_ID Ship_location, HZLOC1.DESCRIPTION Ship_description, HZLOC1.ADDRESS1 Ship_address1, HZLOC1.ADDRESS2 Ship_address2, HZLOC1.ADDRESS3 Ship_address3, HZLOC1.ADDRESS4 Ship_address4, HZPTY2.PARTY_ID Install_party_id, HZPTY2.PARTY_NAME Install_party_name, 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, HZLOC2.DESCRIPTION Install_description, HZLOC2.ADDRESS1 Install_address1, HZLOC2.ADDRESS2 Install_address2, HZLOC2.ADDRESS3 Install_address3, HZLOC2.ADDRESS4 Install_address4, HZPTY3.PARTY_ID Bill_party_id, HZPTY3.PARTY_NAME Bill_party_name, HZPTY3.PARTY_TYPE Bill_party_type, HZPTY3.PERSON_LAST_NAME Bill_first_name, HZPTY3.PERSON_FIRST_NAME Bill_last_name, HZLOC3.LOCATION_ID Bill_location, HZLOC3.DESCRIPTION Bill_description, HZLOC3.ADDRESS1 Bill_address1, HZLOC3.ADDRESS2 Bill_address2, HZLOC3.ADDRESS3 Bill_address3, HZLOC3.ADDRESS4 Bill_address4, HZPTY4.PARTY_NAME Technical_Contact, HZPTY5.PARTY_NAME Administrative_Contact, HZPTY6.PARTY_NAME Ship_Contact, HZPTY7.PARTY_NAME Bill_Contact FROM HZ_CUST_ACCOUNTS HZA, 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, HZ_PARTIES HZPTY8, CS_SYSTEMS S WHERE S.SHIP_TO_SITE_USE_ID = HZSITE1.PARTY_SITE_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.CUSTOMER_ID = HZA.cust_account_id(+) and HZA.CUST_ACCOUNT_ID = HZPTY8.party_id(+)

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
, HZPTY8.PARTY_NAME CUSTOMER_NAME
, S.SYSTEM_TYPE_CODE
, 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.NAME
, S.DESCRIPTION
, HZPTY1.PARTY_ID SHIP_PARTY_ID
, HZPTY1.PARTY_NAME SHIP_PARTY_NAME
, HZPTY1.PARTY_TYPE SHIP_PARTY_TYPE
, HZPTY1.PERSON_LAST_NAME SHIP_FIRST_NAME
, HZPTY1.PERSON_FIRST_NAME SHIP_LAST_NAME
, HZLOC1.LOCATION_ID SHIP_LOCATION
, HZLOC1.DESCRIPTION SHIP_DESCRIPTION
, HZLOC1.ADDRESS1 SHIP_ADDRESS1
, HZLOC1.ADDRESS2 SHIP_ADDRESS2
, HZLOC1.ADDRESS3 SHIP_ADDRESS3
, HZLOC1.ADDRESS4 SHIP_ADDRESS4
, HZPTY2.PARTY_ID INSTALL_PARTY_ID
, HZPTY2.PARTY_NAME INSTALL_PARTY_NAME
, 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
, HZLOC2.DESCRIPTION INSTALL_DESCRIPTION
, HZLOC2.ADDRESS1 INSTALL_ADDRESS1
, HZLOC2.ADDRESS2 INSTALL_ADDRESS2
, HZLOC2.ADDRESS3 INSTALL_ADDRESS3
, HZLOC2.ADDRESS4 INSTALL_ADDRESS4
, HZPTY3.PARTY_ID BILL_PARTY_ID
, HZPTY3.PARTY_NAME BILL_PARTY_NAME
, HZPTY3.PARTY_TYPE BILL_PARTY_TYPE
, HZPTY3.PERSON_LAST_NAME BILL_FIRST_NAME
, HZPTY3.PERSON_FIRST_NAME BILL_LAST_NAME
, HZLOC3.LOCATION_ID BILL_LOCATION
, HZLOC3.DESCRIPTION BILL_DESCRIPTION
, HZLOC3.ADDRESS1 BILL_ADDRESS1
, HZLOC3.ADDRESS2 BILL_ADDRESS2
, HZLOC3.ADDRESS3 BILL_ADDRESS3
, HZLOC3.ADDRESS4 BILL_ADDRESS4
, HZPTY4.PARTY_NAME TECHNICAL_CONTACT
, HZPTY5.PARTY_NAME ADMINISTRATIVE_CONTACT
, HZPTY6.PARTY_NAME SHIP_CONTACT
, HZPTY7.PARTY_NAME BILL_CONTACT
FROM HZ_CUST_ACCOUNTS HZA
, 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
, HZ_PARTIES HZPTY8
, CS_SYSTEMS S
WHERE S.SHIP_TO_SITE_USE_ID = HZSITE1.PARTY_SITE_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.CUSTOMER_ID = HZA.CUST_ACCOUNT_ID(+)
AND HZA.CUST_ACCOUNT_ID = HZPTY8.PARTY_ID(+)