View: PN_SPACE_ASSIGN_CUST_V

Product:	PN - Property Manager
Description:
Implementation/DBA Data:	APPS.PN_SPACE_ASSIGN_CUST_V

View Text

SELECT PSAC.ROWID ROW_ID
, PSAC.CUST_SPACE_ASSIGN_ID CUST_SPACE_ASSIGN_ID
, PSAC.LOCATION_ID LOCATION_ID
, PL.LOCATION_ID LOCATION_LOCATION_ID
, PL.LOCATION_TYPE_LOOKUP_CODE LOCATION_TYPE_LOOKUP_CODE
, PL.LOCATION_CODE LOCATION_CODE
, PL.SUITE LOCATION_SUITE_NAME
, PL.ACTIVE_START_DATE ACTIVE_START_DATE
, PL.ACTIVE_END_DATE ACTIVE_END_DATE
, PSAC.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, HP.PARTY_NAME CUSTOMER_NAME
, PSAC.SITE_USE_ID SITE_USE_ID
, HCSU.LOCATION CUSTOMER_SITE
, L_CAT.MEANING CUSTOMER_CATEGORY
, HP.SIC_CODE SIC_CODE
, L_CLASS.MEANING CUSTOMER_CLASS
, HCPC.NAME PROFILE_CLASS
, PSAC.EXPENSE_ACCOUNT_ID EXPENSE_ACCOUNT_ID
, HCA.STATUS STATUS
, PSAC.PROJECT_ID PROJECT_ID
, PSAC.TASK_ID TASK_ID
, PSAC.CUST_ASSIGN_START_DATE CUST_ASSIGN_START_DATE
, PSAC.CUST_ASSIGN_END_DATE CUST_ASSIGN_END_DATE
, PSAC.ALLOCATED_AREA_PCT ALLOCATED_AREA_PCT
, PSAC.ALLOCATED_AREA ALLOCATED_AREA
, PL.UOM_CODE UOM_CODE
, PSAC.UTILIZED_AREA UTILIZED_AREA
, PSAC.CUST_SPACE_COMMENTS CUST_SPACE_COMMENTS
, PSAC.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PSAC.LAST_UPDATED_BY LAST_UPDATED_BY
, PSAC.CREATION_DATE CREATION_DATE
, PSAC.CREATED_BY CREATED_BY
, PSAC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PSAC.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PSAC.ATTRIBUTE1 ATTRIBUTE1
, PSAC.ATTRIBUTE2 ATTRIBUTE2
, PSAC.ATTRIBUTE3 ATTRIBUTE3
, PSAC.ATTRIBUTE4 ATTRIBUTE4
, PSAC.ATTRIBUTE5 ATTRIBUTE5
, PSAC.ATTRIBUTE6 ATTRIBUTE6
, PSAC.ATTRIBUTE7 ATTRIBUTE7
, PSAC.ATTRIBUTE8 ATTRIBUTE8
, PSAC.ATTRIBUTE9 ATTRIBUTE9
, PSAC.ATTRIBUTE10 ATTRIBUTE10
, PSAC.ATTRIBUTE11 ATTRIBUTE11
, PSAC.ATTRIBUTE12 ATTRIBUTE12
, PSAC.ATTRIBUTE13 ATTRIBUTE13
, PSAC.ATTRIBUTE14 ATTRIBUTE14
, PSAC.ATTRIBUTE15 ATTRIBUTE15
, PSAC.LEASE_ID LEASE_ID
, PL.ORG_ID ORG_ID
, PLE.NAME LEASE_NAME
, SPST.MEANING RECOVERY_SPACE_STD_DESC
, PSAC.RECOVERY_SPACE_STD_CODE RECOVERY_SPACE_STD_CODE
, RCTY.MEANING RECOVERY_TYPE_DESC
, PSAC.RECOVERY_TYPE_CODE RECOVERY_TYPE_CODE
, PSAC.FIN_OBLIG_END_DATE FIN_OBLIG_END_DATE
, PSAC.TENANCY_ID TENANCY_ID
FROM HZ_CUST_ACCOUNTS_ALL HCA
, HZ_PARTIES HP
, HZ_CUST_SITE_USES_ALL HCSU
, AR_LOOKUPS L_CAT
, AR_LOOKUPS L_CLASS
, HZ_CUST_PROFILE_CLASSES HCPC
, HZ_CUSTOMER_PROFILES HCP
, PN_LOCATIONS PL
, PN_SPACE_ASSIGN_CUST PSAC
, PN_LEASES PLE
, FND_LOOKUPS SPST
, FND_LOOKUPS RCTY
WHERE HCA.CUST_ACCOUNT_ID(+) = PSAC.CUST_ACCOUNT_ID
AND HP.PARTY_ID(+) = HCA.PARTY_ID
AND HCSU.SITE_USE_ID(+) = PSAC.SITE_USE_ID
AND L_CAT.LOOKUP_CODE(+) = HP.CATEGORY_CODE
AND L_CAT.LOOKUP_TYPE(+) = 'CUSTOMER_CATEGORY'
AND L_CLASS.LOOKUP_CODE(+) = HCA.CUSTOMER_CLASS_CODE
AND L_CLASS.LOOKUP_TYPE(+) = 'CUSTOMER CLASS'
AND HCP.CUST_ACCOUNT_ID(+) = HCA.CUST_ACCOUNT_ID
AND HCP.SITE_USE_ID IS NULL
AND HCPC.PROFILE_CLASS_ID(+) = HCP.PROFILE_CLASS_ID
AND NVL(PL.COMMON_AREA_FLAG
, 'N')= 'N'
AND PL.LOCATION_ID = PSAC.LOCATION_ID(+)
AND PLE.LEASE_ID(+) = PSAC.LEASE_ID
AND SPST.LOOKUP_TYPE(+) = 'PN_RECOVERY_SPACE_STD_TYPE'
AND SPST.LOOKUP_CODE(+) = PSAC.RECOVERY_SPACE_STD_CODE
AND RCTY.LOOKUP_TYPE(+) = 'PN_RECOVERY_TYPE'
AND RCTY.LOOKUP_CODE(+) = PSAC.RECOVERY_TYPE_CODE UNION ALL SELECT PSAC.ROWID ROW_ID
, PSAC.CUST_SPACE_ASSIGN_ID CUST_SPACE_ASSIGN_ID
, PSAC.LOCATION_ID LOCATION_ID
, PL.LOCATION_ID LOCATION_LOCATION_ID
, PL.LOCATION_TYPE_LOOKUP_CODE LOCATION_TYPE_LOOKUP_CODE
, PL.LOCATION_CODE LOCATION_CODE
, PL.SUITE LOCATION_SUITE_NAME
, PL.ACTIVE_START_DATE ACTIVE_START_DATE
, PL.ACTIVE_END_DATE ACTIVE_END_DATE
, PSAC.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, NULL CUSTOMER_NAME
, PSAC.SITE_USE_ID SITE_USE_ID
, NULL CUSTOMER_SITE
, NULL CUSTOMER_CATEGORY
, NULL SIC_CODE
, NULL CUSTOMER_CLASS
, NULL PROFILE_CLASS
, PSAC.EXPENSE_ACCOUNT_ID EXPENSE_ACCOUNT_ID
, NULL STATUS
, PSAC.PROJECT_ID PROJECT_ID
, PSAC.TASK_ID TASK_ID
, PSAC.CUST_ASSIGN_START_DATE CUST_ASSIGN_START_DATE
, PSAC.CUST_ASSIGN_END_DATE CUST_ASSIGN_END_DATE
, PSAC.ALLOCATED_AREA_PCT ALLOCATED_AREA_PCT
, PSAC.ALLOCATED_AREA ALLOCATED_AREA
, PL.UOM_CODE UOM_CODE
, PSAC.UTILIZED_AREA UTILIZED_AREA
, PSAC.CUST_SPACE_COMMENTS CUST_SPACE_COMMENTS
, PSAC.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PSAC.LAST_UPDATED_BY LAST_UPDATED_BY
, PSAC.CREATION_DATE CREATION_DATE
, PSAC.CREATED_BY CREATED_BY
, PSAC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PSAC.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PSAC.ATTRIBUTE1 ATTRIBUTE1
, PSAC.ATTRIBUTE2 ATTRIBUTE2
, PSAC.ATTRIBUTE3 ATTRIBUTE3
, PSAC.ATTRIBUTE4 ATTRIBUTE4
, PSAC.ATTRIBUTE5 ATTRIBUTE5
, PSAC.ATTRIBUTE6 ATTRIBUTE6
, PSAC.ATTRIBUTE7 ATTRIBUTE7
, PSAC.ATTRIBUTE8 ATTRIBUTE8
, PSAC.ATTRIBUTE9 ATTRIBUTE9
, PSAC.ATTRIBUTE10 ATTRIBUTE10
, PSAC.ATTRIBUTE11 ATTRIBUTE11
, PSAC.ATTRIBUTE12 ATTRIBUTE12
, PSAC.ATTRIBUTE13 ATTRIBUTE13
, PSAC.ATTRIBUTE14 ATTRIBUTE14
, PSAC.ATTRIBUTE15 ATTRIBUTE15
, PSAC.LEASE_ID LEASE_ID
, PL.ORG_ID ORG_ID
, NULL LEASE_NAME
, NULL RECOVERY_SPACE_STD_DESC
, PSAC.RECOVERY_SPACE_STD_CODE RECOVERY_SPACE_STD_CODE
, NULL RECOVERY_TYPE_DESC
, PSAC.RECOVERY_TYPE_CODE RECOVERY_TYPE_CODE
, PSAC.FIN_OBLIG_END_DATE FIN_OBLIG_END_DATE
, PSAC.TENANCY_ID TENANCY_ID
FROM PN_LOCATIONS PL
, PN_SPACE_ASSIGN_CUST PSAC
WHERE PSAC.LOCATION_ID(+) = PL.LOCATION_ID * (-1)
AND EXISTS (SELECT NULL
FROM PN_SPACE_ASSIGN_CUST CUST1
WHERE CUST1.LOCATION_ID = PL.LOCATION_ID)

Columns