VIEW: APPS.CSI_CP_FORM_ADDRESS_V

Source

View Text - Preformatted

SELECT CIA.ROWID ROW_ID , CII.INSTANCE_ID CUSTOMER_PRODUCT_ID, CII.OBJECT_VERSION_NUMBER CP_OBJECT_VERSION_NUMBER, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID, CIP.OBJECT_VERSION_NUMBER IP_OBJECT_VERSION_NUMBER, CIA.IP_ACCOUNT_ID IP_ACCOUNT_ID, CIA.SHIP_TO_ADDRESS SHIP_TO_SITE_USE_ID, CIA.BILL_TO_ADDRESS BILL_TO_SITE_USE_ID, CIA.OBJECT_VERSION_NUMBER IPA_OBJECT_VERSION_NUMBER, HZSITE1.PARTY_SITE_NAME SHIP_TO_PARTY_SITE_NAME, HZPTY1.PARTY_ID SHIP_TO_PARTY_ID, HZPTY1.PARTY_NAME SHIP_TO_PARTY_NAME, HZPTY1.PARTY_TYPE SHIP_TO_PARTY_TYPE, HZPTY1.PERSON_LAST_NAME SHIP_TO_PARTY_LAST_NAME, HZPTY1.PERSON_FIRST_NAME SHIP_TO_PARTY_FIRST_NAME, HZLOC1.LOCATION_ID SHIP_TO_LOCATION_ID, HZLOC1.DESCRIPTION SHIP_TO_LOCATION_DESC, HZLOC1.ADDRESS1 SHIP_TO_ADDRESS1, HZLOC1.ADDRESS2 SHIP_TO_ADDRESS2, HZLOC1.ADDRESS3 SHIP_TO_ADDRESS3, HZLOC1.ADDRESS4 SHIP_TO_ADDRESS4, HZSITE1.MAILSTOP SHIP_TO_MAILSTOP, HZLOC1.CITY SHIP_TO_CITY, HZLOC1.POSTAL_CODE SHIP_TO_POSTAL_CODE, HZLOC1.STATE SHIP_TO_STATE, HZLOC1.PROVINCE SHIP_TO_PROVINCE, HZLOC1.COUNTY SHIP_TO_COUNTY, HZLOC1.COUNTRY SHIP_TO_COUNTRY, HZSITE2.PARTY_SITE_NAME INSTALL_TO_PARTY_SITE_NAME, HZPTY2.PARTY_ID INSTALL_TO_PARTY_ID, HZPTY2.PARTY_NAME INSTALL_TO_PARTY_NAME, HZPTY2.PARTY_TYPE INSTALL_TO_PARTY_TYPE, HZPTY2.PERSON_LAST_NAME INSTALL_TO_PARTY_LAST_NAME, HZPTY2.PERSON_FIRST_NAME INSTALL_TO_PARTY_FIRST_NAME, HZLOC2.LOCATION_ID INSTALL_TO_LOCATION_ID, HZLOC2.DESCRIPTION INSTALL_TO_LOCATION_DESC, HZLOC2.ADDRESS1 INSTALL_TO_ADDRESS1, HZLOC2.ADDRESS2 INSTALL_TO_ADDRESS2, HZLOC2.ADDRESS3 INSTALL_TO_ADDRESS3, HZLOC2.ADDRESS4 INSTALL_TO_ADDRESS4, HZSITE2.MAILSTOP INSTALL_TO_MAILSTOP, HZLOC2.CITY INSTALL_TO_CITY, HZLOC2.POSTAL_CODE INSTALL_TO_POSTAL_CODE, HZLOC2.STATE INSTALL_TO_STATE, HZLOC2.PROVINCE INSTALL_TO_PROVINCE, HZLOC2.COUNTY INSTALL_TO_COUNTY, HZLOC2.COUNTRY INSTALL_TO_COUNTRY, HZSITE3.PARTY_SITE_NAME BILL_TO_PARTY_SITE_NAME, HZPTY3.PARTY_ID BILL_TO_PARTY_ID, HZPTY3.PARTY_NAME BILL_TO_PARTY_NAME, HZPTY3.PARTY_TYPE BILL_TO_PARTY_TYPE, HZPTY3.PERSON_LAST_NAME BILL_TO_PARTY_LAST_NAME, HZPTY3.PERSON_FIRST_NAME BILL_TO_PARTY_FIRST_NAME, HZLOC3.LOCATION_ID BILL_TO_LOCATION_ID, HZLOC3.DESCRIPTION BILL_TO_LOCATION_DESC, HZLOC3.ADDRESS1 BILL_TO_ADDRESS1, HZLOC3.ADDRESS2 BILL_TO_ADDRESS2, HZLOC3.ADDRESS3 BILL_TO_ADDRESS3, HZLOC3.ADDRESS4 BILL_TO_ADDRESS4, HZSITE3.MAILSTOP BILL_TO_MAILSTOP, HZLOC3.CITY BILL_TO_CITY, HZLOC3.POSTAL_CODE BILL_TO_POSTAL_CODE, HZLOC3.STATE BILL_TO_STATE, HZLOC3.PROVINCE BILL_TO_PROVINCE, HZLOC3.COUNTY BILL_TO_COUNTY, HZLOC3.COUNTRY BILL_TO_COUNTRY, HZSITE1.PARTY_SITE_NUMBER SHIP_TO_PARTY_SITE_NUMBER, HZSITE2.PARTY_SITE_NUMBER INSTALL_TO_PARTY_SITE_NUMBER, HZSITE3.PARTY_SITE_NUMBER BILL_TO_PARTY_SITE_NUMBER, HZA.PARTY_ID PARTY_ID FROM CSI_ITEM_INSTANCES CII, CSI_I_PARTIES CIP, CSI_IP_ACCOUNTS CIA, CSI_IPA_RELATION_TYPES CIRT, HZ_CUST_SITE_USES_ALL HCSU1, HZ_CUST_ACCT_SITES_ALL HZACCTSITE1, HZ_PARTY_SITES HZSITE1, HZ_LOCATIONS HZLOC1, HZ_PARTIES HZPTY1, HZ_PARTY_SITES HZSITE2, HZ_LOCATIONS HZLOC2, HZ_PARTIES HZPTY2, HZ_CUST_SITE_USES_ALL HCSU3, HZ_CUST_ACCT_SITES_ALL HZACCTSITE3, HZ_PARTY_SITES HZSITE3, HZ_LOCATIONS HZLOC3, HZ_PARTIES HZPTY3, HZ_CUST_ACCOUNTS HZA, HZ_PARTIES HZP WHERE CII.INSTANCE_ID = CIP.INSTANCE_ID AND CIP.INSTANCE_PARTY_ID = CIA.INSTANCE_PARTY_ID AND CIA.PARTY_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID AND CIA.RELATIONSHIP_TYPE_CODE = 'OWNER' AND CIA.ACTIVE_END_DATE IS NULL AND HZA.PARTY_ID = HZP.PARTY_ID AND CIA.SHIP_TO_ADDRESS = HCSU1.SITE_USE_ID (+) AND HCSU1.CUST_ACCT_SITE_ID = HZACCTSITE1.CUST_ACCT_SITE_ID(+) AND HZACCTSITE1.PARTY_SITE_ID = HZSITE1.PARTY_SITE_ID(+) AND HZSITE1.LOCATION_ID = HZLOC1.LOCATION_ID(+) AND HZSITE1.PARTY_ID = HZPTY1.PARTY_ID(+) AND CII.INSTALL_LOCATION_ID = HZSITE2.PARTY_SITE_ID(+) AND HZSITE2.LOCATION_ID = HZLOC2.LOCATION_ID(+) AND HZSITE2.PARTY_ID = HZPTY2.PARTY_ID(+) AND CIA.BILL_TO_ADDRESS = HCSU3.SITE_USE_ID (+) AND HCSU3.CUST_ACCT_SITE_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 CIP.RELATIONSHIP_TYPE_CODE = CIRT.IPA_RELATION_TYPE_CODE(+) AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES' AND CIP.INSTANCE_PARTY_ID = CIA.INSTANCE_PARTY_ID (+) AND NOT EXISTS (SELECT 1 FROM CSI_INSTALL_PARAMETERS CIPM WHERE CIPM.INTERNAL_PARTY_ID = CIP.PARTY_ID )

View Text - HTML Formatted

SELECT CIA.ROWID ROW_ID
, CII.INSTANCE_ID CUSTOMER_PRODUCT_ID
, CII.OBJECT_VERSION_NUMBER CP_OBJECT_VERSION_NUMBER
, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.OBJECT_VERSION_NUMBER IP_OBJECT_VERSION_NUMBER
, CIA.IP_ACCOUNT_ID IP_ACCOUNT_ID
, CIA.SHIP_TO_ADDRESS SHIP_TO_SITE_USE_ID
, CIA.BILL_TO_ADDRESS BILL_TO_SITE_USE_ID
, CIA.OBJECT_VERSION_NUMBER IPA_OBJECT_VERSION_NUMBER
, HZSITE1.PARTY_SITE_NAME SHIP_TO_PARTY_SITE_NAME
, HZPTY1.PARTY_ID SHIP_TO_PARTY_ID
, HZPTY1.PARTY_NAME SHIP_TO_PARTY_NAME
, HZPTY1.PARTY_TYPE SHIP_TO_PARTY_TYPE
, HZPTY1.PERSON_LAST_NAME SHIP_TO_PARTY_LAST_NAME
, HZPTY1.PERSON_FIRST_NAME SHIP_TO_PARTY_FIRST_NAME
, HZLOC1.LOCATION_ID SHIP_TO_LOCATION_ID
, HZLOC1.DESCRIPTION SHIP_TO_LOCATION_DESC
, HZLOC1.ADDRESS1 SHIP_TO_ADDRESS1
, HZLOC1.ADDRESS2 SHIP_TO_ADDRESS2
, HZLOC1.ADDRESS3 SHIP_TO_ADDRESS3
, HZLOC1.ADDRESS4 SHIP_TO_ADDRESS4
, HZSITE1.MAILSTOP SHIP_TO_MAILSTOP
, HZLOC1.CITY SHIP_TO_CITY
, HZLOC1.POSTAL_CODE SHIP_TO_POSTAL_CODE
, HZLOC1.STATE SHIP_TO_STATE
, HZLOC1.PROVINCE SHIP_TO_PROVINCE
, HZLOC1.COUNTY SHIP_TO_COUNTY
, HZLOC1.COUNTRY SHIP_TO_COUNTRY
, HZSITE2.PARTY_SITE_NAME INSTALL_TO_PARTY_SITE_NAME
, HZPTY2.PARTY_ID INSTALL_TO_PARTY_ID
, HZPTY2.PARTY_NAME INSTALL_TO_PARTY_NAME
, HZPTY2.PARTY_TYPE INSTALL_TO_PARTY_TYPE
, HZPTY2.PERSON_LAST_NAME INSTALL_TO_PARTY_LAST_NAME
, HZPTY2.PERSON_FIRST_NAME INSTALL_TO_PARTY_FIRST_NAME
, HZLOC2.LOCATION_ID INSTALL_TO_LOCATION_ID
, HZLOC2.DESCRIPTION INSTALL_TO_LOCATION_DESC
, HZLOC2.ADDRESS1 INSTALL_TO_ADDRESS1
, HZLOC2.ADDRESS2 INSTALL_TO_ADDRESS2
, HZLOC2.ADDRESS3 INSTALL_TO_ADDRESS3
, HZLOC2.ADDRESS4 INSTALL_TO_ADDRESS4
, HZSITE2.MAILSTOP INSTALL_TO_MAILSTOP
, HZLOC2.CITY INSTALL_TO_CITY
, HZLOC2.POSTAL_CODE INSTALL_TO_POSTAL_CODE
, HZLOC2.STATE INSTALL_TO_STATE
, HZLOC2.PROVINCE INSTALL_TO_PROVINCE
, HZLOC2.COUNTY INSTALL_TO_COUNTY
, HZLOC2.COUNTRY INSTALL_TO_COUNTRY
, HZSITE3.PARTY_SITE_NAME BILL_TO_PARTY_SITE_NAME
, HZPTY3.PARTY_ID BILL_TO_PARTY_ID
, HZPTY3.PARTY_NAME BILL_TO_PARTY_NAME
, HZPTY3.PARTY_TYPE BILL_TO_PARTY_TYPE
, HZPTY3.PERSON_LAST_NAME BILL_TO_PARTY_LAST_NAME
, HZPTY3.PERSON_FIRST_NAME BILL_TO_PARTY_FIRST_NAME
, HZLOC3.LOCATION_ID BILL_TO_LOCATION_ID
, HZLOC3.DESCRIPTION BILL_TO_LOCATION_DESC
, HZLOC3.ADDRESS1 BILL_TO_ADDRESS1
, HZLOC3.ADDRESS2 BILL_TO_ADDRESS2
, HZLOC3.ADDRESS3 BILL_TO_ADDRESS3
, HZLOC3.ADDRESS4 BILL_TO_ADDRESS4
, HZSITE3.MAILSTOP BILL_TO_MAILSTOP
, HZLOC3.CITY BILL_TO_CITY
, HZLOC3.POSTAL_CODE BILL_TO_POSTAL_CODE
, HZLOC3.STATE BILL_TO_STATE
, HZLOC3.PROVINCE BILL_TO_PROVINCE
, HZLOC3.COUNTY BILL_TO_COUNTY
, HZLOC3.COUNTRY BILL_TO_COUNTRY
, HZSITE1.PARTY_SITE_NUMBER SHIP_TO_PARTY_SITE_NUMBER
, HZSITE2.PARTY_SITE_NUMBER INSTALL_TO_PARTY_SITE_NUMBER
, HZSITE3.PARTY_SITE_NUMBER BILL_TO_PARTY_SITE_NUMBER
, HZA.PARTY_ID PARTY_ID
FROM CSI_ITEM_INSTANCES CII
, CSI_I_PARTIES CIP
, CSI_IP_ACCOUNTS CIA
, CSI_IPA_RELATION_TYPES CIRT
, HZ_CUST_SITE_USES_ALL HCSU1
, HZ_CUST_ACCT_SITES_ALL HZACCTSITE1
, HZ_PARTY_SITES HZSITE1
, HZ_LOCATIONS HZLOC1
, HZ_PARTIES HZPTY1
, HZ_PARTY_SITES HZSITE2
, HZ_LOCATIONS HZLOC2
, HZ_PARTIES HZPTY2
, HZ_CUST_SITE_USES_ALL HCSU3
, HZ_CUST_ACCT_SITES_ALL HZACCTSITE3
, HZ_PARTY_SITES HZSITE3
, HZ_LOCATIONS HZLOC3
, HZ_PARTIES HZPTY3
, HZ_CUST_ACCOUNTS HZA
, HZ_PARTIES HZP
WHERE CII.INSTANCE_ID = CIP.INSTANCE_ID
AND CIP.INSTANCE_PARTY_ID = CIA.INSTANCE_PARTY_ID
AND CIA.PARTY_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID
AND CIA.RELATIONSHIP_TYPE_CODE = 'OWNER'
AND CIA.ACTIVE_END_DATE IS NULL
AND HZA.PARTY_ID = HZP.PARTY_ID
AND CIA.SHIP_TO_ADDRESS = HCSU1.SITE_USE_ID (+)
AND HCSU1.CUST_ACCT_SITE_ID = HZACCTSITE1.CUST_ACCT_SITE_ID(+)
AND HZACCTSITE1.PARTY_SITE_ID = HZSITE1.PARTY_SITE_ID(+)
AND HZSITE1.LOCATION_ID = HZLOC1.LOCATION_ID(+)
AND HZSITE1.PARTY_ID = HZPTY1.PARTY_ID(+)
AND CII.INSTALL_LOCATION_ID = HZSITE2.PARTY_SITE_ID(+)
AND HZSITE2.LOCATION_ID = HZLOC2.LOCATION_ID(+)
AND HZSITE2.PARTY_ID = HZPTY2.PARTY_ID(+)
AND CIA.BILL_TO_ADDRESS = HCSU3.SITE_USE_ID (+)
AND HCSU3.CUST_ACCT_SITE_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 CIP.RELATIONSHIP_TYPE_CODE = CIRT.IPA_RELATION_TYPE_CODE(+)
AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND CIP.INSTANCE_PARTY_ID = CIA.INSTANCE_PARTY_ID (+)
AND NOT EXISTS (SELECT 1
FROM CSI_INSTALL_PARAMETERS CIPM
WHERE CIPM.INTERNAL_PARTY_ID = CIP.PARTY_ID )