VIEW: APPS.PN_LEASE_CONTACT_ASSIGN_V

Source

View Text - Preformatted

SELECT pca.rowid ,pca.contact_assignment_id ,pca.last_update_date ,pca.last_updated_by ,pca.creation_date ,pca.created_by ,pca.last_update_login ,pca.company_id ,pnc.name company_name ,pca.company_site_id ,pcs.name site_name ,pcs.lease_role_type lease_role_type ,flv.meaning lease_role ,pca.lease_id ,pca.lease_change_id ,pca.location_id ,pca.status ,pca.attribute_category ,pca.attribute1 ,pca.attribute2 ,pca.attribute3 ,pca.attribute4 ,pca.attribute5 ,pca.attribute6 ,pca.attribute7 ,pca.attribute8 ,pca.attribute9 ,pca.attribute10 ,pca.attribute11 ,pca.attribute12 ,pca.attribute13 ,pca.attribute14 ,pca.attribute15 ,pca.org_id ,pnp_util_func.get_concatenated_address(addr.address_style ,addr.address_line1 ,addr.address_line2 ,addr.address_line3 ,addr.address_line4 ,addr.city ,addr.county ,addr.state ,addr.province ,addr.zip_code ,terr.territory_short_name) concatenated_address FROM pn_companies_all pnc, fnd_lookups flv, pn_company_sites_all pcs, pn_contact_assignments pca, pn_addresses_all addr, fnd_territories_vl terr WHERE pca.lease_id IS NOT NULL AND pca.location_id IS NULL AND pcs.company_site_id = pca.company_site_id AND flv.lookup_code = pcs.lease_role_type AND flv.lookup_type = 'PN_LEASE_ROLE_TYPE' AND pnc.company_id = pca.company_id AND pnc.company_id = pcs.company_id AND pcs.address_id = addr.address_id (+) AND addr.country = terr.territory_code (+)

View Text - HTML Formatted

SELECT PCA.ROWID
, PCA.CONTACT_ASSIGNMENT_ID
, PCA.LAST_UPDATE_DATE
, PCA.LAST_UPDATED_BY
, PCA.CREATION_DATE
, PCA.CREATED_BY
, PCA.LAST_UPDATE_LOGIN
, PCA.COMPANY_ID
, PNC.NAME COMPANY_NAME
, PCA.COMPANY_SITE_ID
, PCS.NAME SITE_NAME
, PCS.LEASE_ROLE_TYPE LEASE_ROLE_TYPE
, FLV.MEANING LEASE_ROLE
, PCA.LEASE_ID
, PCA.LEASE_CHANGE_ID
, PCA.LOCATION_ID
, PCA.STATUS
, PCA.ATTRIBUTE_CATEGORY
, PCA.ATTRIBUTE1
, PCA.ATTRIBUTE2
, PCA.ATTRIBUTE3
, PCA.ATTRIBUTE4
, PCA.ATTRIBUTE5
, PCA.ATTRIBUTE6
, PCA.ATTRIBUTE7
, PCA.ATTRIBUTE8
, PCA.ATTRIBUTE9
, PCA.ATTRIBUTE10
, PCA.ATTRIBUTE11
, PCA.ATTRIBUTE12
, PCA.ATTRIBUTE13
, PCA.ATTRIBUTE14
, PCA.ATTRIBUTE15
, PCA.ORG_ID
, PNP_UTIL_FUNC.GET_CONCATENATED_ADDRESS(ADDR.ADDRESS_STYLE
, ADDR.ADDRESS_LINE1
, ADDR.ADDRESS_LINE2
, ADDR.ADDRESS_LINE3
, ADDR.ADDRESS_LINE4
, ADDR.CITY
, ADDR.COUNTY
, ADDR.STATE
, ADDR.PROVINCE
, ADDR.ZIP_CODE
, TERR.TERRITORY_SHORT_NAME) CONCATENATED_ADDRESS
FROM PN_COMPANIES_ALL PNC
, FND_LOOKUPS FLV
, PN_COMPANY_SITES_ALL PCS
, PN_CONTACT_ASSIGNMENTS PCA
, PN_ADDRESSES_ALL ADDR
, FND_TERRITORIES_VL TERR
WHERE PCA.LEASE_ID IS NOT NULL
AND PCA.LOCATION_ID IS NULL
AND PCS.COMPANY_SITE_ID = PCA.COMPANY_SITE_ID
AND FLV.LOOKUP_CODE = PCS.LEASE_ROLE_TYPE
AND FLV.LOOKUP_TYPE = 'PN_LEASE_ROLE_TYPE'
AND PNC.COMPANY_ID = PCA.COMPANY_ID
AND PNC.COMPANY_ID = PCS.COMPANY_ID
AND PCS.ADDRESS_ID = ADDR.ADDRESS_ID (+)
AND ADDR.COUNTRY = TERR.TERRITORY_CODE (+)