VIEW: APPS.PN_LEASE_CHANGES_V

Source

View Text - Preformatted

SELECT lchange.ROWID row_id, lchange.lease_change_id lease_change_id, lchange.lease_id lease_id, lchange.lease_transaction_id lease_transaction_id, lchange.last_update_date last_update_date, lchange.last_updated_by last_updated_by, lchange.creation_date creation_date, lchange.created_by created_by, lchange.last_update_login last_update_login, lchange.lease_change_number lease_change_number, lchange.lease_change_name lease_change_name, lchange.abstracted_by_user abstracted_by_user_id, fna.user_name user_abstracted, lchange.responsible_user responsible_user_id, fnr.user_name user_responsible, lchange.change_execution_date change_execution_date, lchange.change_commencement_date change_commencement_date, lchange.change_termination_date change_termination_date, lchange.change_type_lookup_code change_type_lookup_code, (TRUNC(lchange.change_termination_date) - TRUNC(lchange.change_commencement_date) + 1) amendment_term, lchange.attribute_category attribute_category, lchange.attribute1 attribute1, lchange.attribute2 attribute2, lchange.attribute3 attribute3, lchange.attribute4 attribute4, lchange.attribute5 attribute5, lchange.attribute6 attribute6, lchange.attribute7 attribute7, lchange.attribute8 attribute8, lchange.attribute9 attribute9, lchange.attribute10 attribute10, lchange.attribute11 attribute11, lchange.attribute12 attribute12, lchange.attribute13 attribute13, lchange.attribute14 attribute14, lchange.attribute15 attribute15, lease.status status, nvl(pdh.lease_status, lease.lease_status) lease_status, lease.lease_num lease_number, lease.name lease_name, plt.transaction_type transaction_type_code, flv.meaning transaction_type, fll.meaning lease_type, lease.lease_type_code lease_type_code, lease.lease_class_code lease_class_code, flc.meaning lease_class, lease.location_id location_id, lease.customer_id customer_id, hzp.party_name customer_name, hca.account_number customer_number, lease.org_id org_id, lease.cal_start calendar_year_start_date FROM fnd_user fna, fnd_user fnr, fnd_lookups flv, fnd_lookups fll, fnd_lookups flc, hz_parties hzp, hz_cust_accounts hca, pn_leases lease, pn_lease_transactions_all plt, pn_lease_changes_all lchange, pn_lease_details_history pdh WHERE fna.user_id (+) = lchange.abstracted_by_user AND fnr.user_id (+) = lchange.responsible_user AND lease.lease_id = lchange.lease_id AND plt.lease_transaction_id = lchange.lease_transaction_id AND lchange.lease_change_number IS NOT NULL AND flv.lookup_code = plt.transaction_type AND flv.lookup_type = 'PN_LEASE_CHANGE_TYPE' AND fll.lookup_code = lease.lease_type_code AND fll.lookup_type = 'PN_LEASE_TYPE' AND flc.lookup_code = lease.lease_class_code AND flc.lookup_type = 'PN_LEASE_CLASS' AND hca.cust_account_id (+) = lease.customer_id AND hzp.party_id (+) = hca.party_id AND pdh.lease_change_id(+) = lchange.lease_change_id ORDER BY lchange.lease_change_id desc

View Text - HTML Formatted

SELECT LCHANGE.ROWID ROW_ID
, LCHANGE.LEASE_CHANGE_ID LEASE_CHANGE_ID
, LCHANGE.LEASE_ID LEASE_ID
, LCHANGE.LEASE_TRANSACTION_ID LEASE_TRANSACTION_ID
, LCHANGE.LAST_UPDATE_DATE LAST_UPDATE_DATE
, LCHANGE.LAST_UPDATED_BY LAST_UPDATED_BY
, LCHANGE.CREATION_DATE CREATION_DATE
, LCHANGE.CREATED_BY CREATED_BY
, LCHANGE.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, LCHANGE.LEASE_CHANGE_NUMBER LEASE_CHANGE_NUMBER
, LCHANGE.LEASE_CHANGE_NAME LEASE_CHANGE_NAME
, LCHANGE.ABSTRACTED_BY_USER ABSTRACTED_BY_USER_ID
, FNA.USER_NAME USER_ABSTRACTED
, LCHANGE.RESPONSIBLE_USER RESPONSIBLE_USER_ID
, FNR.USER_NAME USER_RESPONSIBLE
, LCHANGE.CHANGE_EXECUTION_DATE CHANGE_EXECUTION_DATE
, LCHANGE.CHANGE_COMMENCEMENT_DATE CHANGE_COMMENCEMENT_DATE
, LCHANGE.CHANGE_TERMINATION_DATE CHANGE_TERMINATION_DATE
, LCHANGE.CHANGE_TYPE_LOOKUP_CODE CHANGE_TYPE_LOOKUP_CODE
, (TRUNC(LCHANGE.CHANGE_TERMINATION_DATE) - TRUNC(LCHANGE.CHANGE_COMMENCEMENT_DATE) + 1) AMENDMENT_TERM
, LCHANGE.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, LCHANGE.ATTRIBUTE1 ATTRIBUTE1
, LCHANGE.ATTRIBUTE2 ATTRIBUTE2
, LCHANGE.ATTRIBUTE3 ATTRIBUTE3
, LCHANGE.ATTRIBUTE4 ATTRIBUTE4
, LCHANGE.ATTRIBUTE5 ATTRIBUTE5
, LCHANGE.ATTRIBUTE6 ATTRIBUTE6
, LCHANGE.ATTRIBUTE7 ATTRIBUTE7
, LCHANGE.ATTRIBUTE8 ATTRIBUTE8
, LCHANGE.ATTRIBUTE9 ATTRIBUTE9
, LCHANGE.ATTRIBUTE10 ATTRIBUTE10
, LCHANGE.ATTRIBUTE11 ATTRIBUTE11
, LCHANGE.ATTRIBUTE12 ATTRIBUTE12
, LCHANGE.ATTRIBUTE13 ATTRIBUTE13
, LCHANGE.ATTRIBUTE14 ATTRIBUTE14
, LCHANGE.ATTRIBUTE15 ATTRIBUTE15
, LEASE.STATUS STATUS
, NVL(PDH.LEASE_STATUS
, LEASE.LEASE_STATUS) LEASE_STATUS
, LEASE.LEASE_NUM LEASE_NUMBER
, LEASE.NAME LEASE_NAME
, PLT.TRANSACTION_TYPE TRANSACTION_TYPE_CODE
, FLV.MEANING TRANSACTION_TYPE
, FLL.MEANING LEASE_TYPE
, LEASE.LEASE_TYPE_CODE LEASE_TYPE_CODE
, LEASE.LEASE_CLASS_CODE LEASE_CLASS_CODE
, FLC.MEANING LEASE_CLASS
, LEASE.LOCATION_ID LOCATION_ID
, LEASE.CUSTOMER_ID CUSTOMER_ID
, HZP.PARTY_NAME CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, LEASE.ORG_ID ORG_ID
, LEASE.CAL_START CALENDAR_YEAR_START_DATE
FROM FND_USER FNA
, FND_USER FNR
, FND_LOOKUPS FLV
, FND_LOOKUPS FLL
, FND_LOOKUPS FLC
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HCA
, PN_LEASES LEASE
, PN_LEASE_TRANSACTIONS_ALL PLT
, PN_LEASE_CHANGES_ALL LCHANGE
, PN_LEASE_DETAILS_HISTORY PDH
WHERE FNA.USER_ID (+) = LCHANGE.ABSTRACTED_BY_USER
AND FNR.USER_ID (+) = LCHANGE.RESPONSIBLE_USER
AND LEASE.LEASE_ID = LCHANGE.LEASE_ID
AND PLT.LEASE_TRANSACTION_ID = LCHANGE.LEASE_TRANSACTION_ID
AND LCHANGE.LEASE_CHANGE_NUMBER IS NOT NULL
AND FLV.LOOKUP_CODE = PLT.TRANSACTION_TYPE
AND FLV.LOOKUP_TYPE = 'PN_LEASE_CHANGE_TYPE'
AND FLL.LOOKUP_CODE = LEASE.LEASE_TYPE_CODE
AND FLL.LOOKUP_TYPE = 'PN_LEASE_TYPE'
AND FLC.LOOKUP_CODE = LEASE.LEASE_CLASS_CODE
AND FLC.LOOKUP_TYPE = 'PN_LEASE_CLASS'
AND HCA.CUST_ACCOUNT_ID (+) = LEASE.CUSTOMER_ID
AND HZP.PARTY_ID (+) = HCA.PARTY_ID
AND PDH.LEASE_CHANGE_ID(+) = LCHANGE.LEASE_CHANGE_ID ORDER BY LCHANGE.LEASE_CHANGE_ID DESC